0027_migration_requests.sql57 lines · main

1	-- 0027_migration_requests — customer-initiated migration intake.
2	-- The dashboard wizard at /dashboard/projects/new/migrate writes one
3	-- row per source-platform import request. During beta, every row is
4	-- triaged by an operator via /dashboard/admin/migrations and either
5	-- handled by the (forthcoming) automated adapter pipeline or migrated
6	-- by hand for free. Once the adapter for a source ships, status
7	-- transitions through `scheduled` → `in_progress` → `completed`
8	-- without an operator in the loop.
9
10	CREATE TABLE IF NOT EXISTS "migration_requests" (
11	"id" text PRIMARY KEY NOT NULL,
12	-- Requester. user_id is always set; org_id is the org the migrated
13	-- project should land in, defaulting to the user's personal org.
14	"user_id" text NOT NULL,
15	"org_id" text,
16	-- Source platform. Open vocabulary so we can add adapters without a
17	-- migration; the wizard restricts the picker to known sources.
18	"source" text NOT NULL,
19	"source_url" text,
20	"source_notes" text NOT NULL DEFAULT '',
21	-- Rough scale signals the operator uses to triage queue order.
22	"estimated_tables" integer,
23	"estimated_rows" bigint,
24	"estimated_functions" integer,
25	-- Urgency lets the customer self-report their timeline so we don't
26	-- have to ask twice. Values: exploring | this_week | this_month | this_quarter.
27	"urgency" text NOT NULL DEFAULT 'exploring',
28	-- Lifecycle. new → contacted → scheduled → in_progress → completed
29	-- (terminal) | cancelled (terminal).
30	"status" text NOT NULL DEFAULT 'new',
31	"contact_email" text NOT NULL,
32	-- Operator-only fields. Customer never sees these.
33	"assigned_to" text,
34	"operator_notes" text NOT NULL DEFAULT '',
35	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
36	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
37	CONSTRAINT "migration_requests_user_fk"
38	FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE,
39	CONSTRAINT "migration_requests_org_fk"
40	FOREIGN KEY ("org_id") REFERENCES "organizations"("id") ON DELETE SET NULL,
41	CONSTRAINT "migration_requests_assigned_fk"
42	FOREIGN KEY ("assigned_to") REFERENCES "users"("id") ON DELETE SET NULL
43	);
44
45	-- Triage queue: operator opens the admin page sorted newest-first.
46	CREATE INDEX IF NOT EXISTS "migration_requests_created_idx"
47	ON "migration_requests" USING btree ("created_at" DESC);
48
49	-- Hot path for the customer's own list ("my migration requests").
50	CREATE INDEX IF NOT EXISTS "migration_requests_user_idx"
51	ON "migration_requests" USING btree ("user_id", "created_at" DESC);
52
53	-- "How many open requests do we have?" — drives the admin nav badge
54	-- and the operator's at-a-glance load.
55	CREATE INDEX IF NOT EXISTS "migration_requests_open_idx"
56	ON "migration_requests" USING btree ("created_at" DESC)
57	WHERE "status" NOT IN ('completed', 'cancelled');