shithub Public

Watch 1 Fork 0 Star 0
MySQL · 9034 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S30 — Organizations.
4 --
5 -- Schema overview:
6 --
7 -- orgs — first-class principals that can own repos.
8 -- org_members — (org, user) ↔ role; PK on the pair so a user
9 -- holds exactly one role per org.
10 -- org_invitations — pending invites by username OR email; one of
11 -- target_user_id / target_email is set.
12 -- principals — single source of truth for /{slug} resolution.
13 -- Maintained by triggers on users + orgs so a
14 -- slug collision is structurally impossible.
15 --
16 -- The `repos.owner_org_id` column already exists from 0017 with the
17 -- right XOR CHECK constraint; this migration just adds the FK once
18 -- the orgs table exists.
19
20 -- +goose Up
21
22 -- ─── orgs ───────────────────────────────────────────────────────────
23 CREATE TYPE org_plan AS ENUM ('free', 'team', 'enterprise');
24
25 CREATE TABLE orgs (
26 id bigserial PRIMARY KEY,
27 slug citext NOT NULL UNIQUE,
28 display_name text NOT NULL DEFAULT '',
29 description text NOT NULL DEFAULT '',
30 avatar_object_key text,
31 location text NOT NULL DEFAULT '',
32 website text NOT NULL DEFAULT '',
33 billing_email text NOT NULL DEFAULT '',
34 plan org_plan NOT NULL DEFAULT 'free',
35 allow_member_repo_create boolean NOT NULL DEFAULT true,
36 created_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
37 suspended_at timestamptz,
38 suspended_reason text,
39 deleted_at timestamptz,
40 created_at timestamptz NOT NULL DEFAULT now(),
41 updated_at timestamptz NOT NULL DEFAULT now(),
42
43 CONSTRAINT orgs_slug_length CHECK (char_length(slug::text) BETWEEN 1 AND 39),
44 CONSTRAINT orgs_description_length CHECK (char_length(description) <= 350)
45 );
46
47 CREATE INDEX orgs_deleted_at_idx ON orgs (deleted_at) WHERE deleted_at IS NOT NULL;
48 CREATE INDEX orgs_suspended_at_idx ON orgs (suspended_at) WHERE suspended_at IS NOT NULL;
49
50 CREATE TRIGGER set_updated_at BEFORE UPDATE ON orgs
51 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
52
53 -- ─── org_members ───────────────────────────────────────────────────
54 CREATE TYPE org_role AS ENUM ('owner', 'member');
55
56 CREATE TABLE org_members (
57 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
58 user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
59 role org_role NOT NULL DEFAULT 'member',
60 invited_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
61 joined_at timestamptz NOT NULL DEFAULT now(),
62
63 PRIMARY KEY (org_id, user_id)
64 );
65
66 CREATE INDEX org_members_user_idx ON org_members (user_id);
67 CREATE INDEX org_members_role_idx ON org_members (org_id, role);
68
69 -- ─── org_invitations ───────────────────────────────────────────────
70 CREATE TABLE org_invitations (
71 id bigserial PRIMARY KEY,
72 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
73 invited_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
74 target_user_id bigint REFERENCES users(id) ON DELETE CASCADE,
75 target_email citext,
76 role org_role NOT NULL DEFAULT 'member',
77 token_hash bytea NOT NULL UNIQUE,
78 expires_at timestamptz NOT NULL,
79 accepted_at timestamptz,
80 declined_at timestamptz,
81 canceled_at timestamptz,
82 created_at timestamptz NOT NULL DEFAULT now(),
83
84 CONSTRAINT org_invites_target_xor CHECK (
85 (target_user_id IS NOT NULL AND target_email IS NULL)
86 OR (target_user_id IS NULL AND target_email IS NOT NULL)
87 )
88 );
89
90 CREATE INDEX org_invites_org_pending_idx
91 ON org_invitations (org_id)
92 WHERE accepted_at IS NULL AND declined_at IS NULL AND canceled_at IS NULL;
93
94 CREATE INDEX org_invites_target_user_idx
95 ON org_invitations (target_user_id)
96 WHERE target_user_id IS NOT NULL;
97
98 CREATE INDEX org_invites_target_email_idx
99 ON org_invitations (target_email)
100 WHERE target_email IS NOT NULL;
101
102 -- ─── principals ────────────────────────────────────────────────────
103 -- Unifies the /{slug} URL space across users and orgs. Maintained by
104 -- AFTER triggers on users + orgs so the row is always coherent.
105 -- One row per (slug, kind, id); slug PK enforces global uniqueness
106 -- across both tables — a slug collision is structurally impossible.
107 CREATE TYPE principal_kind AS ENUM ('user', 'org');
108
109 CREATE TABLE principals (
110 slug citext PRIMARY KEY,
111 kind principal_kind NOT NULL,
112 id bigint NOT NULL,
113
114 -- Sanity index for "all orgs" / "all users" sweeps.
115 CONSTRAINT principals_id_kind_idx UNIQUE (kind, id)
116 );
117
118 -- Backfill from existing users.
119 INSERT INTO principals (slug, kind, id)
120 SELECT username, 'user'::principal_kind, id
121 FROM users
122 WHERE deleted_at IS NULL
123 ON CONFLICT (slug) DO NOTHING;
124
125 -- +goose StatementBegin
126 CREATE OR REPLACE FUNCTION tg_principals_user_sync() RETURNS trigger AS $$
127 BEGIN
128 IF TG_OP = 'DELETE' THEN
129 DELETE FROM principals WHERE kind = 'user' AND id = OLD.id;
130 RETURN OLD;
131 END IF;
132 IF TG_OP = 'INSERT' THEN
133 INSERT INTO principals (slug, kind, id)
134 VALUES (NEW.username, 'user', NEW.id);
135 RETURN NEW;
136 END IF;
137 -- UPDATE: handle username change + soft-delete flip.
138 IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
139 DELETE FROM principals WHERE kind = 'user' AND id = NEW.id;
140 RETURN NEW;
141 END IF;
142 IF NEW.deleted_at IS NULL AND OLD.deleted_at IS NOT NULL THEN
143 INSERT INTO principals (slug, kind, id)
144 VALUES (NEW.username, 'user', NEW.id)
145 ON CONFLICT (slug) DO UPDATE SET kind = EXCLUDED.kind, id = EXCLUDED.id;
146 RETURN NEW;
147 END IF;
148 IF NEW.username <> OLD.username THEN
149 UPDATE principals SET slug = NEW.username
150 WHERE kind = 'user' AND id = NEW.id;
151 END IF;
152 RETURN NEW;
153 END;
154 $$ LANGUAGE plpgsql;
155 -- +goose StatementEnd
156
157 -- +goose StatementBegin
158 CREATE OR REPLACE FUNCTION tg_principals_org_sync() RETURNS trigger AS $$
159 BEGIN
160 IF TG_OP = 'DELETE' THEN
161 DELETE FROM principals WHERE kind = 'org' AND id = OLD.id;
162 RETURN OLD;
163 END IF;
164 IF TG_OP = 'INSERT' THEN
165 INSERT INTO principals (slug, kind, id)
166 VALUES (NEW.slug, 'org', NEW.id);
167 RETURN NEW;
168 END IF;
169 IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
170 DELETE FROM principals WHERE kind = 'org' AND id = NEW.id;
171 RETURN NEW;
172 END IF;
173 IF NEW.deleted_at IS NULL AND OLD.deleted_at IS NOT NULL THEN
174 INSERT INTO principals (slug, kind, id)
175 VALUES (NEW.slug, 'org', NEW.id)
176 ON CONFLICT (slug) DO UPDATE SET kind = EXCLUDED.kind, id = EXCLUDED.id;
177 RETURN NEW;
178 END IF;
179 IF NEW.slug <> OLD.slug THEN
180 UPDATE principals SET slug = NEW.slug
181 WHERE kind = 'org' AND id = NEW.id;
182 END IF;
183 RETURN NEW;
184 END;
185 $$ LANGUAGE plpgsql;
186 -- +goose StatementEnd
187
188 CREATE TRIGGER tg_principals_user_sync_iud
189 AFTER INSERT OR UPDATE OR DELETE ON users
190 FOR EACH ROW EXECUTE FUNCTION tg_principals_user_sync();
191
192 CREATE TRIGGER tg_principals_org_sync_iud
193 AFTER INSERT OR UPDATE OR DELETE ON orgs
194 FOR EACH ROW EXECUTE FUNCTION tg_principals_org_sync();
195
196 -- ─── repos.owner_org_id FK ─────────────────────────────────────────
197 -- The column existed from 0017 with the XOR CHECK already in place;
198 -- adding the actual FK only now that the target table exists.
199 ALTER TABLE repos
200 ADD CONSTRAINT repos_owner_org_id_fkey
201 FOREIGN KEY (owner_org_id) REFERENCES orgs(id) ON DELETE CASCADE;
202
203 -- +goose Down
204 ALTER TABLE repos DROP CONSTRAINT IF EXISTS repos_owner_org_id_fkey;
205 DROP TRIGGER IF EXISTS tg_principals_org_sync_iud ON orgs;
206 DROP TRIGGER IF EXISTS tg_principals_user_sync_iud ON users;
207 DROP FUNCTION IF EXISTS tg_principals_org_sync();
208 DROP FUNCTION IF EXISTS tg_principals_user_sync();
209 DROP TABLE IF EXISTS principals;
210 DROP TYPE IF EXISTS principal_kind;
211 DROP TABLE IF EXISTS org_invitations;
212 DROP TABLE IF EXISTS org_members;
213 DROP TYPE IF EXISTS org_role;
214 DROP TABLE IF EXISTS orgs;
215 DROP TYPE IF EXISTS org_plan;
216