dogfood
Jump to…
snowcorepublic2 bookmarks
snowinitial commitqoxwzsukwmkx1mo
mainroot / migrations / 0001_initial.sqlBlameRaw
1-- Dogfood initial schema (spec §5).
2--
3-- Target: PostgreSQL 17 (the provided instance; the spec said 16, the extra
4-- version is harmless — nothing here depends on 17-only behaviour).
5--
6-- UUIDv7 primary keys are generated in the application, not by the database, so
7-- that IDs are known before insert and a row can reference itself in the same
8-- transaction. Time-ordered, index-friendly, and they do not leak counts.
9
10CREATE EXTENSION IF NOT EXISTS citext;
11CREATE EXTENSION IF NOT EXISTS pg_trgm;
12
13-- ─── identity ────────────────────────────────────────────────────────────────
14
15CREATE TABLE users (
16 id uuid PRIMARY KEY,
17 subject text NOT NULL UNIQUE, -- OIDC sub claim (Kratos identity id)
18 handle citext NOT NULL UNIQUE,
19 display_name text,
20 email citext,
21 avatar_url text,
22 is_admin boolean NOT NULL DEFAULT false,
23 created_at timestamptz NOT NULL DEFAULT now(),
24 -- NOTE the ::text cast. `handle` is citext, and citext makes the `~`
25 -- operator case-INSENSITIVE, so without the cast this constraint accepts
26 -- 'Bob' despite the lowercase-only character class. Verified.
27 CONSTRAINT handle_format CHECK (handle::text ~ '^[a-z0-9][a-z0-9-]{0,38}$')
28);
29
30CREATE TABLE orgs (
31 id uuid PRIMARY KEY,
32 handle citext NOT NULL UNIQUE,
33 display_name text,
34 description text,
35 created_at timestamptz NOT NULL DEFAULT now(),
36 -- NOTE the ::text cast. `handle` is citext, and citext makes the `~`
37 -- operator case-INSENSITIVE, so without the cast this constraint accepts
38 -- 'Bob' despite the lowercase-only character class. Verified.
39 CONSTRAINT handle_format CHECK (handle::text ~ '^[a-z0-9][a-z0-9-]{0,38}$')
40);
41
42-- Users and orgs share a handle namespace so that /{handle} is unambiguous.
43-- Enforced with triggers on both tables rather than a CHECK, which cannot see
44-- another table.
45CREATE FUNCTION assert_handle_unused() RETURNS trigger AS $$
46BEGIN
47 IF TG_TABLE_NAME = 'users' THEN
48 IF EXISTS (SELECT 1 FROM orgs WHERE handle = NEW.handle) THEN
49 RAISE EXCEPTION 'handle % is already taken by an organization', NEW.handle
50 USING ERRCODE = 'unique_violation';
51 END IF;
52 ELSE
53 IF EXISTS (SELECT 1 FROM users WHERE handle = NEW.handle) THEN
54 RAISE EXCEPTION 'handle % is already taken by a user', NEW.handle
55 USING ERRCODE = 'unique_violation';
56 END IF;
57 END IF;
58 RETURN NEW;
59END;
60$$ LANGUAGE plpgsql;
61
62CREATE TRIGGER users_handle_namespace
63 BEFORE INSERT OR UPDATE OF handle ON users
64 FOR EACH ROW EXECUTE FUNCTION assert_handle_unused();
65
66CREATE TRIGGER orgs_handle_namespace
67 BEFORE INSERT OR UPDATE OF handle ON orgs
68 FOR EACH ROW EXECUTE FUNCTION assert_handle_unused();
69
70-- Reserved handles: these are top-level routes, so a user or org holding one
71-- would shadow them (spec §7 puts /settings, /new, /search at the root).
72CREATE TABLE reserved_handles (handle citext PRIMARY KEY);
73INSERT INTO reserved_handles (handle) VALUES
74 ('new'), ('settings'), ('search'), ('login'), ('logout'), ('auth'),
75 ('setup'), ('admin'), ('api'), ('static'), ('assets'), ('healthz'),
76 ('readyz'), ('metrics'), ('about'), ('help'), ('docs'), ('status'),
77 ('explore'), ('notifications'), ('dashboard'), ('repos'), ('orgs'),
78 ('users'), ('git'), ('jj'), ('www'), ('mail'), ('root');
79
80CREATE TYPE org_role AS ENUM ('member', 'admin');
81
82CREATE TABLE org_members (
83 org_id uuid NOT NULL REFERENCES orgs ON DELETE CASCADE,
84 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
85 role org_role NOT NULL DEFAULT 'member',
86 created_at timestamptz NOT NULL DEFAULT now(),
87 PRIMARY KEY (org_id, user_id)
88);
89CREATE INDEX org_members_user_idx ON org_members (user_id);
90
91CREATE TABLE ssh_keys (
92 id uuid PRIMARY KEY,
93 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
94 name text NOT NULL,
95 key_type text NOT NULL,
96 fingerprint text NOT NULL UNIQUE, -- SHA256:… — the auth lookup key
97 public_key text NOT NULL,
98 last_used_at timestamptz,
99 created_at timestamptz NOT NULL DEFAULT now()
100);
101CREATE INDEX ssh_keys_fingerprint_idx ON ssh_keys (fingerprint);
102CREATE INDEX ssh_keys_user_idx ON ssh_keys (user_id);
103
104CREATE TABLE access_tokens (
105 id uuid PRIMARY KEY,
106 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
107 name text NOT NULL,
108 token_hash text NOT NULL, -- argon2id PHC string; plaintext shown once
109 prefix text NOT NULL, -- first 8 chars, for identification in UI
110 scopes text[] NOT NULL DEFAULT '{}',
111 expires_at timestamptz,
112 last_used_at timestamptz,
113 created_at timestamptz NOT NULL DEFAULT now()
114);
115CREATE INDEX access_tokens_user_idx ON access_tokens (user_id);
116-- Token auth looks up by prefix, then verifies the argon2 hash. Hashing is
117-- deliberately slow, so we must not hash against every row in the table.
118CREATE INDEX access_tokens_prefix_idx ON access_tokens (prefix);
119
120CREATE TABLE sessions (
121 id uuid PRIMARY KEY,
122 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
123 expires_at timestamptz NOT NULL,
124 user_agent text,
125 ip inet,
126 created_at timestamptz NOT NULL DEFAULT now()
127);
128CREATE INDEX sessions_user_idx ON sessions (user_id);
129CREATE INDEX sessions_expiry_idx ON sessions (expires_at);
130
131-- In-flight OIDC authorization code flows. PKCE verifier and nonce are held
132-- server-side and keyed by the opaque state parameter; nothing sensitive is
133-- round-tripped through the browser.
134CREATE TABLE auth_flows (
135 state text PRIMARY KEY,
136 pkce_verifier text NOT NULL,
137 nonce text NOT NULL,
138 redirect_after text,
139 created_at timestamptz NOT NULL DEFAULT now(),
140 expires_at timestamptz NOT NULL
141);
142CREATE INDEX auth_flows_expiry_idx ON auth_flows (expires_at);
143
144-- ─── access control (decided: invite/allowlist only) ─────────────────────────
145
146-- A successful OIDC login is not sufficient to get a Dogfood account. The
147-- identity must additionally match an allowlist entry or an open invitation.
148CREATE TABLE invitations (
149 id uuid PRIMARY KEY,
150 email citext NOT NULL UNIQUE,
151 invited_by uuid REFERENCES users ON DELETE SET NULL,
152 accepted_at timestamptz,
153 accepted_by uuid REFERENCES users ON DELETE SET NULL,
154 created_at timestamptz NOT NULL DEFAULT now()
155);
156
157-- One-time site-admin bootstrap (decided). The web process mints a token on
158-- first boot when no admin exists, logs it once, and stores only its hash.
159CREATE TABLE setup_tokens (
160 id uuid PRIMARY KEY,
161 token_hash text NOT NULL,
162 consumed_at timestamptz,
163 consumed_by uuid REFERENCES users ON DELETE SET NULL,
164 created_at timestamptz NOT NULL DEFAULT now()
165);
166
167-- ─── repositories ────────────────────────────────────────────────────────────
168
169CREATE TYPE owner_kind AS ENUM ('user', 'org');
170CREATE TYPE visibility AS ENUM ('public', 'private');
171
172CREATE TABLE repos (
173 id uuid PRIMARY KEY,
174 owner_kind owner_kind NOT NULL,
175 owner_user_id uuid REFERENCES users ON DELETE CASCADE,
176 owner_org_id uuid REFERENCES orgs ON DELETE CASCADE,
177 name citext NOT NULL,
178 description text,
179 visibility visibility NOT NULL DEFAULT 'private',
180 default_bookmark text NOT NULL DEFAULT 'main',
181 -- Forks use separate storage (decided): this records provenance only, and
182 -- never causes two repos to share an object database.
183 fork_of_repo_id uuid REFERENCES repos ON DELETE SET NULL,
184 size_bytes bigint NOT NULL DEFAULT 0,
185 pushed_at timestamptz,
186 archived boolean NOT NULL DEFAULT false,
187 created_at timestamptz NOT NULL DEFAULT now(),
188 CONSTRAINT one_owner CHECK (num_nonnulls(owner_user_id, owner_org_id) = 1),
189 CONSTRAINT owner_kind_matches CHECK (
190 (owner_kind = 'user' AND owner_user_id IS NOT NULL) OR
191 (owner_kind = 'org' AND owner_org_id IS NOT NULL)
192 ),
193 CONSTRAINT name_format CHECK (name ~ '^[A-Za-z0-9][A-Za-z0-9._-]{0,99}$' AND name !~ '\.\.')
194);
195CREATE UNIQUE INDEX repos_owner_name_idx ON repos (COALESCE(owner_user_id, owner_org_id), name);
196CREATE INDEX repos_visibility_idx ON repos (visibility) WHERE archived = false;
197
198CREATE TYPE repo_role AS ENUM ('read', 'write', 'maintain', 'admin');
199
200CREATE TABLE repo_collaborators (
201 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
202 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
203 role repo_role NOT NULL,
204 created_at timestamptz NOT NULL DEFAULT now(),
205 PRIMARY KEY (repo_id, user_id)
206);
207CREATE INDEX repo_collaborators_user_idx ON repo_collaborators (user_id);
208
209CREATE TABLE bookmarks (
210 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
211 name text NOT NULL,
212 target text NOT NULL, -- RevId
213 protected boolean NOT NULL DEFAULT false,
214 updated_at timestamptz NOT NULL DEFAULT now(),
215 PRIMARY KEY (repo_id, name)
216);
217
218-- ─── changes ─────────────────────────────────────────────────────────────────
219
220CREATE TYPE change_state AS ENUM ('draft', 'open', 'merged', 'abandoned');
221
222CREATE TABLE changes (
223 id uuid PRIMARY KEY,
224 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
225 change_id text NOT NULL, -- jj change id, canonical letter encoding
226 synthetic boolean NOT NULL DEFAULT false,
227 number bigint NOT NULL, -- per-repo display number
228 title text NOT NULL,
229 description text NOT NULL DEFAULT '',
230 -- 'draft' is set by the author in the UI and is never inferred from pushed
231 -- metadata (decided). The indexer may move a change to merged/abandoned but
232 -- must not overwrite draft.
233 state change_state NOT NULL DEFAULT 'open',
234 conflicted boolean NOT NULL DEFAULT false,
235 author_user_id uuid REFERENCES users ON DELETE SET NULL,
236 target_bookmark text NOT NULL,
237 head_revision_id uuid, -- FK added after revisions
238 merged_at timestamptz,
239 created_at timestamptz NOT NULL DEFAULT now(),
240 updated_at timestamptz NOT NULL DEFAULT now(),
241 UNIQUE (repo_id, change_id),
242 UNIQUE (repo_id, number)
243);
244CREATE INDEX changes_list_idx ON changes (repo_id, state, updated_at DESC);
245
246-- Prefix lookup: users type a short prefix, exactly as in the CLI.
247CREATE INDEX changes_prefix_idx ON changes (repo_id, change_id text_pattern_ops);
248
249CREATE TABLE revisions (
250 id uuid PRIMARY KEY,
251 change_id_fk uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
252 rev text NOT NULL, -- RevId
253 seq int NOT NULL, -- 1-based, per change
254 parents text[] NOT NULL DEFAULT '{}',
255 author_name text NOT NULL,
256 author_email text NOT NULL,
257 authored_at timestamptz NOT NULL,
258 message text NOT NULL,
259 conflicted boolean NOT NULL DEFAULT false,
260 conflict_data jsonb, -- sides, base, paths
261 pushed_by uuid REFERENCES users ON DELETE SET NULL,
262 pushed_at timestamptz NOT NULL DEFAULT now(),
263 UNIQUE (change_id_fk, rev),
264 UNIQUE (change_id_fk, seq)
265);
266CREATE INDEX revisions_rev_idx ON revisions (rev);
267
268ALTER TABLE changes ADD CONSTRAINT changes_head_fk
269 FOREIGN KEY (head_revision_id) REFERENCES revisions ON DELETE SET NULL;
270
271-- Stack edges: parent_change is immediately below child_change in a stack.
272-- Computed at index time; never recomputed on page render (spec §4).
273CREATE TABLE change_edges (
274 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
275 parent_change uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
276 child_change uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
277 PRIMARY KEY (parent_change, child_change),
278 CONSTRAINT no_self_edge CHECK (parent_change <> child_change)
279);
280CREATE INDEX change_edges_child_idx ON change_edges (repo_id, child_change);
281
282-- ─── discussion ──────────────────────────────────────────────────────────────
283
284CREATE TYPE anchor_state AS ENUM ('current', 'outdated', 'orphaned');
285
286CREATE TABLE comments (
287 id uuid PRIMARY KEY,
288 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
289 change_id_fk uuid REFERENCES changes ON DELETE CASCADE,
290 issue_id uuid, -- FK below
291 parent_id uuid REFERENCES comments ON DELETE CASCADE,
292 author_user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
293 body text NOT NULL,
294
295 -- inline anchor, null for top-level comments
296 anchor_revision uuid REFERENCES revisions ON DELETE SET NULL,
297 anchor_path text,
298 anchor_line int,
299 anchor_side text CHECK (anchor_side IN ('old','new')),
300 anchor_state anchor_state NOT NULL DEFAULT 'current',
301 anchor_context text, -- the line's content when written
302
303 resolved_at timestamptz,
304 resolved_by uuid REFERENCES users ON DELETE SET NULL,
305 edited_at timestamptz,
306 created_at timestamptz NOT NULL DEFAULT now(),
307 CONSTRAINT one_target CHECK (num_nonnulls(change_id_fk, issue_id) = 1)
308);
309CREATE INDEX comments_change_idx ON comments (change_id_fk, created_at);
310CREATE INDEX comments_issue_idx ON comments (issue_id, created_at);
311-- Anchor rebasing loads every inline comment on a change by its anchored file.
312CREATE INDEX comments_anchor_idx ON comments (change_id_fk, anchor_path)
313 WHERE anchor_path IS NOT NULL;
314
315CREATE TYPE review_verdict AS ENUM ('approve', 'request_changes', 'comment');
316
317CREATE TABLE reviews (
318 id uuid PRIMARY KEY,
319 change_id_fk uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
320 revision_id uuid NOT NULL REFERENCES revisions ON DELETE CASCADE,
321 reviewer_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
322 verdict review_verdict NOT NULL,
323 body text,
324 created_at timestamptz NOT NULL DEFAULT now()
325);
326CREATE INDEX reviews_change_idx ON reviews (change_id_fk, created_at DESC);
327
328-- ─── issues ──────────────────────────────────────────────────────────────────
329
330CREATE TYPE issue_state AS ENUM ('open', 'closed');
331
332CREATE TABLE issues (
333 id uuid PRIMARY KEY,
334 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
335 number bigint NOT NULL,
336 title text NOT NULL,
337 body text NOT NULL DEFAULT '',
338 state issue_state NOT NULL DEFAULT 'open',
339 author_user_id uuid REFERENCES users ON DELETE SET NULL,
340 closed_at timestamptz,
341 created_at timestamptz NOT NULL DEFAULT now(),
342 updated_at timestamptz NOT NULL DEFAULT now(),
343 UNIQUE (repo_id, number)
344);
345CREATE INDEX issues_list_idx ON issues (repo_id, state, updated_at DESC);
346
347ALTER TABLE comments ADD CONSTRAINT comments_issue_fk
348 FOREIGN KEY (issue_id) REFERENCES issues ON DELETE CASCADE;
349
350CREATE TABLE labels (
351 id uuid PRIMARY KEY,
352 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
353 name text NOT NULL,
354 color text NOT NULL,
355 UNIQUE (repo_id, name)
356);
357
358CREATE TABLE issue_labels (
359 issue_id uuid NOT NULL REFERENCES issues ON DELETE CASCADE,
360 label_id uuid NOT NULL REFERENCES labels ON DELETE CASCADE,
361 PRIMARY KEY (issue_id, label_id)
362);
363
364CREATE TABLE issue_assignees (
365 issue_id uuid NOT NULL REFERENCES issues ON DELETE CASCADE,
366 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
367 PRIMARY KEY (issue_id, user_id)
368);
369
370-- Per-repo issue and change numbering. A sequence per repo would be cleaner but
371-- cannot be created dynamically without DDL on every repo creation; this row is
372-- locked with SELECT … FOR UPDATE when allocating.
373CREATE TABLE repo_counters (
374 repo_id uuid PRIMARY KEY REFERENCES repos ON DELETE CASCADE,
375 next_change bigint NOT NULL DEFAULT 1,
376 next_issue bigint NOT NULL DEFAULT 1
377);
378
379-- ─── activity ────────────────────────────────────────────────────────────────
380
381-- Timeline events. Also the substrate a future activity feed would read from —
382-- written correctly now even though nothing renders a feed in v1.
383CREATE TABLE events (
384 id uuid PRIMARY KEY,
385 repo_id uuid REFERENCES repos ON DELETE CASCADE,
386 actor_id uuid REFERENCES users ON DELETE SET NULL,
387 kind text NOT NULL, -- pushed, rebased, conflicted, resolved,
388 -- merged, abandoned, reviewed, commented, …
389 subject_type text NOT NULL, -- change | issue | repo | bookmark
390 subject_id uuid,
391 payload jsonb NOT NULL DEFAULT '{}',
392 created_at timestamptz NOT NULL DEFAULT now()
393);
394CREATE INDEX events_repo_idx ON events (repo_id, created_at DESC);
395CREATE INDEX events_subject_idx ON events (subject_type, subject_id, created_at);
396
397CREATE TABLE audit_log (
398 id uuid PRIMARY KEY,
399 actor_id uuid REFERENCES users ON DELETE SET NULL,
400 action text NOT NULL,
401 target text NOT NULL,
402 ip inet,
403 metadata jsonb NOT NULL DEFAULT '{}',
404 created_at timestamptz NOT NULL DEFAULT now()
405);
406CREATE INDEX audit_log_actor_idx ON audit_log (actor_id, created_at DESC);
407CREATE INDEX audit_log_created_idx ON audit_log (created_at DESC);
408
409-- ─── rendering cache ─────────────────────────────────────────────────────────
410
411-- Syntax highlighting is expensive and its input is content-addressed, so the
412-- blob OID is a perfect cache key (spec §8). Highlighting on every request is
413-- the easiest performance mistake to make here.
414CREATE TABLE highlight_cache (
415 blob_oid text PRIMARY KEY,
416 language text,
417 html text NOT NULL,
418 bytes int NOT NULL,
419 created_at timestamptz NOT NULL DEFAULT now()
420);
421
422-- ─── jobs ────────────────────────────────────────────────────────────────────
423
424CREATE TABLE jobs (
425 id uuid PRIMARY KEY,
426 kind text NOT NULL,
427 payload jsonb NOT NULL,
428 run_at timestamptz NOT NULL DEFAULT now(),
429 attempts int NOT NULL DEFAULT 0,
430 max_attempts int NOT NULL DEFAULT 5,
431 locked_at timestamptz,
432 locked_by text,
433 last_error text,
434 created_at timestamptz NOT NULL DEFAULT now()
435);
436CREATE INDEX jobs_ready_idx ON jobs (run_at) WHERE locked_at IS NULL;
437
438-- Wake the worker on insert so it does not poll on the hot path. The worker
439-- also polls every 5s as a fallback for missed notifications (spec §5).
440CREATE FUNCTION notify_job() RETURNS trigger AS $$
441BEGIN
442 PERFORM pg_notify('dogfood_jobs', NEW.kind);
443 RETURN NEW;
444END;
445$$ LANGUAGE plpgsql;
446
447CREATE TRIGGER jobs_notify AFTER INSERT ON jobs
448 FOR EACH ROW EXECUTE FUNCTION notify_job();

448 lines · SQL