0045_support_tickets.sql56 lines · main

1	-- 0045_support_tickets — turn tagged /contact submissions into support tickets.
2	--
3	-- Extends the existing contact_messages intake (0036/0037) with a ticket
4	-- lifecycle: a submission becomes a ticket only when the sender tagged it
5	-- with a routing tag (#support/#billing/#technical/#self-hosting). Ticketed
6	-- rows get a human-facing ticket_number (e.g. SUP260629-000001, stored
7	-- WITHOUT the leading '#'), a topic_code (SUP/BIL/TEC/SLF), and a status
8	-- that starts at 'no_response'. Non-ticketed rows leave ticket_number /
9	-- topic_code NULL and keep the default status (never surfaced for them).
10	--
11	-- ticket_counters drives the daily, per-topic sequence: one row per
12	-- (topic_code, day), incremented atomically by INSERT ... ON CONFLICT DO
13	-- UPDATE so concurrent submissions can never collide on a number, and the
14	-- counter resets to 1 each new UTC day per code.
15	--
16	-- contact_message_replies is the append-only ticket thread (operator/user),
17	-- cascading off the parent contact_messages row.
18
19	-- Ticket columns on the existing intake table.
20	ALTER TABLE "contact_messages" ADD COLUMN IF NOT EXISTS "status" text DEFAULT 'no_response' NOT NULL;--> statement-breakpoint
21	ALTER TABLE "contact_messages" ADD COLUMN IF NOT EXISTS "ticket_number" text;--> statement-breakpoint
22	ALTER TABLE "contact_messages" ADD COLUMN IF NOT EXISTS "topic_code" text;--> statement-breakpoint
23	ALTER TABLE "contact_messages" ADD COLUMN IF NOT EXISTS "assigned_to" text;--> statement-breakpoint
24	ALTER TABLE "contact_messages" ADD COLUMN IF NOT EXISTS "operator_notes" text;--> statement-breakpoint
25
26	-- Nullable-unique ticket number: many non-ticket rows stay NULL (Postgres
27	-- allows multiple NULLs in a unique index), ticketed rows stay globally unique.
28	CREATE UNIQUE INDEX IF NOT EXISTS "contact_messages_ticket_number_idx"
29	ON "contact_messages" USING btree ("ticket_number");--> statement-breakpoint
30
31	-- Daily, per-topic-code sequence. PK (topic_code, day) is what the
32	-- ON CONFLICT increment keys on.
33	CREATE TABLE IF NOT EXISTS "ticket_counters" (
34	"topic_code" text NOT NULL,
35	"day" date NOT NULL,
36	"counter" integer DEFAULT 0 NOT NULL,
37	CONSTRAINT "ticket_counters_topic_code_day_pk" PRIMARY KEY("topic_code","day")
38	);--> statement-breakpoint
39
40	-- Append-only ticket thread.
41	CREATE TABLE IF NOT EXISTS "contact_message_replies" (
42	"id" text PRIMARY KEY NOT NULL,
43	"message_id" text NOT NULL,
44	-- 'operator' (admin reply) | 'user' (inbound reply).
45	"author" text NOT NULL,
46	"body" text NOT NULL,
47	"created_at" timestamp with time zone DEFAULT now() NOT NULL
48	);--> statement-breakpoint
49
50	ALTER TABLE "contact_message_replies"
51	ADD CONSTRAINT "contact_message_replies_message_id_contact_messages_id_fk"
52	FOREIGN KEY ("message_id") REFERENCES "contact_messages"("id")
53	ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
54
55	CREATE INDEX IF NOT EXISTS "contact_message_replies_message_idx"
56	ON "contact_message_replies" USING btree ("message_id");