Files
2026-10-15 07:34:44 +00:00

9 lines
1.7 KiB
SQL

CREATE TABLE support_conversations (id UUID NOT NULL, user_id UUID, anonymous_browser_id VARCHAR(64), entry_path VARCHAR(512), product_slugs JSONB, topics JSONB, unanswered BOOLEAN NOT NULL DEFAULT false, needs_followup BOOLEAN NOT NULL DEFAULT false, escalation_signal BOOLEAN NOT NULL DEFAULT false, summary TEXT, summary_status VARCHAR(20) NOT NULL DEFAULT 'pending', created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMPTZ NOT NULL, CONSTRAINT support_conversations_pkey PRIMARY KEY (id));
CREATE TABLE support_conversation_messages (id UUID NOT NULL, conversation_id UUID NOT NULL, role VARCHAR(20) NOT NULL, content TEXT NOT NULL, sequence INTEGER NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT support_conversation_messages_pkey PRIMARY KEY (id));
CREATE UNIQUE INDEX support_conversation_messages_conversation_id_sequence_key ON support_conversation_messages(conversation_id, sequence);
CREATE INDEX support_conversations_user_id_updated_at_idx ON support_conversations(user_id, updated_at);
CREATE INDEX support_conversations_anonymous_browser_id_updated_at_idx ON support_conversations(anonymous_browser_id, updated_at);
CREATE INDEX support_conversation_messages_conversation_id_created_at_idx ON support_conversation_messages(conversation_id, created_at);
ALTER TABLE support_conversations ADD CONSTRAINT support_conversations_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE support_conversation_messages ADD CONSTRAINT support_conversation_messages_conversation_id_fkey FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE ON UPDATE CASCADE;