0010_orgs.sql70 lines · main

1	-- Move from user-owned to org-owned data model.
2	-- Runs in one transaction; rollback on any error leaves the DB untouched.
3
4	BEGIN;
5
6	-- 1. New tables
7	CREATE TABLE "organizations" (
8	"id" text PRIMARY KEY NOT NULL,
9	"slug" text NOT NULL,
10	"name" text NOT NULL,
11	"personal" boolean DEFAULT false NOT NULL,
12	"created_by" text NOT NULL REFERENCES "users"("id"),
13	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
14	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
15	"deleted_at" timestamp with time zone
16	);
17	CREATE UNIQUE INDEX "organizations_slug_idx" ON "organizations" ("slug");
18
19	CREATE TABLE "org_members" (
20	"org_id" text NOT NULL REFERENCES "organizations"("id") ON DELETE CASCADE,
21	"user_id" text NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
22	"role" text DEFAULT 'developer' NOT NULL,
23	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
24	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
25	PRIMARY KEY ("org_id", "user_id")
26	);
27	CREATE INDEX "org_members_user_id_idx" ON "org_members" ("user_id");
28
29	-- 2. Seed one personal org per existing user.
30	-- Slug derived from email local-part, lowercased, non-alnum → '-'.
31	-- For today's single dev user there is no slug collision; for any future
32	-- pre-migration collision the INSERT will fail and the whole transaction
33	-- rolls back — safer than silently salting.
34	INSERT INTO "organizations" ("id", "slug", "name", "personal", "created_by", "created_at", "updated_at")
35	SELECT
36	'org_' || u."id",
37	lower(regexp_replace(split_part(u."email", '@', 1), '[^a-z0-9]+', '-', 'gi')),
38	COALESCE(NULLIF(u."name", ''), split_part(u."email", '@', 1)),
39	true,
40	u."id",
41	now(),
42	now()
43	FROM "users" u;
44
45	-- 3. Make each user the owner of their personal org.
46	INSERT INTO "org_members" ("org_id", "user_id", "role", "created_at", "updated_at")
47	SELECT 'org_' || u."id", u."id", 'owner', now(), now()
48	FROM "users" u;
49
50	-- 4. Add nullable org_id FK columns.
51	ALTER TABLE "subscriptions" ADD COLUMN "org_id" text REFERENCES "organizations"("id") ON DELETE CASCADE;
52	ALTER TABLE "projects" ADD COLUMN "org_id" text REFERENCES "organizations"("id") ON DELETE CASCADE;
53
54	-- 5. Backfill — each row points to its owner's personal org.
55	UPDATE "subscriptions" SET "org_id" = 'org_' || "owner_id";
56	UPDATE "projects" SET "org_id" = 'org_' || "owner_id";
57
58	-- 6. Lock it down.
59	ALTER TABLE "subscriptions" ALTER COLUMN "org_id" SET NOT NULL;
60	ALTER TABLE "subscriptions" ADD CONSTRAINT "subscriptions_org_idx" UNIQUE ("org_id");
61	ALTER TABLE "projects" ALTER COLUMN "org_id" SET NOT NULL;
62	CREATE INDEX "projects_org_idx" ON "projects" ("org_id");
63
64	-- 7. Drop the old unique-per-owner constraint on subscriptions, and the old owner_id columns.
65	DROP INDEX IF EXISTS "subscriptions_owner_idx";
66	DROP INDEX IF EXISTS "projects_owner_idx";
67	ALTER TABLE "subscriptions" DROP COLUMN "owner_id";
68	ALTER TABLE "projects" DROP COLUMN "owner_id";
69
70	COMMIT;