0036_contact_messages.sql29 lines · main

1	-- 0036_contact_messages — public /contact form intake.
2	-- The /contact marketing page writes one row per submission from an
3	-- unauthenticated visitor. The sender's email is collected + stored
4	-- here so an operator can reply privately — it is never rendered back
5	-- to the website. Triaged out-of-band; `handled_at` is stamped once an
6	-- operator has actioned the message. Lands on the control DB (Postgres).
7
8	CREATE TABLE IF NOT EXISTS "contact_messages" (
9	"id" text PRIMARY KEY NOT NULL,
10	"name" text NOT NULL,
11	-- Collected so the operator can reply privately. NEVER echoed back to
12	-- the website in any state.
13	"email" text NOT NULL,
14	-- Routing hint chosen by the sender. Values: general | support | sales
15	-- | security | privacy | other.
16	"topic" text NOT NULL,
17	"message" text NOT NULL,
18	-- Abuse signals — hashed IP + raw user-agent, both nullable when the
19	-- request arrives without them.
20	"ip_hash" text,
21	"user_agent" text,
22	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
23	-- Stamped once an operator has actioned the message.
24	"handled_at" timestamp with time zone
25	);
26
27	-- Triage queue: operator reviews newest-first.
28	CREATE INDEX IF NOT EXISTS "contact_messages_created_idx"
29	ON "contact_messages" USING btree ("created_at" DESC);