dogfood
Jump to…
snowcorepublic2 bookmarks
snowinitial commitqoxwzsukwmkx1mo
mainroot / migrations / 0001_initial.sqlHide BlameRaw
Matt W1-- Dogfood initial schema (spec §5).
Matt W2--
Matt W3-- Target: PostgreSQL 17 (the provided instance; the spec said 16, the extra
Matt W4-- version is harmless — nothing here depends on 17-only behaviour).
Matt W5--
Matt W6-- UUIDv7 primary keys are generated in the application, not by the database, so
Matt W7-- that IDs are known before insert and a row can reference itself in the same
Matt W8-- transaction. Time-ordered, index-friendly, and they do not leak counts.
Matt W9
Matt W10CREATE EXTENSION IF NOT EXISTS citext;
Matt W11CREATE EXTENSION IF NOT EXISTS pg_trgm;
Matt W12
Matt W13-- ─── identity ────────────────────────────────────────────────────────────────
Matt W14
Matt W15CREATE TABLE users (
Matt W16 id uuid PRIMARY KEY,
Matt W17 subject text NOT NULL UNIQUE, -- OIDC sub claim (Kratos identity id)
Matt W18 handle citext NOT NULL UNIQUE,
Matt W19 display_name text,
Matt W20 email citext,
Matt W21 avatar_url text,
Matt W22 is_admin boolean NOT NULL DEFAULT false,
Matt W23 created_at timestamptz NOT NULL DEFAULT now(),
Matt W24 -- NOTE the ::text cast. `handle` is citext, and citext makes the `~`
Matt W25 -- operator case-INSENSITIVE, so without the cast this constraint accepts
Matt W26 -- 'Bob' despite the lowercase-only character class. Verified.
Matt W27 CONSTRAINT handle_format CHECK (handle::text ~ '^[a-z0-9][a-z0-9-]{0,38}$')
Matt W28);
Matt W29
Matt W30CREATE TABLE orgs (
Matt W31 id uuid PRIMARY KEY,
Matt W32 handle citext NOT NULL UNIQUE,
Matt W33 display_name text,
Matt W34 description text,
Matt W35 created_at timestamptz NOT NULL DEFAULT now(),
Matt W36 -- NOTE the ::text cast. `handle` is citext, and citext makes the `~`
Matt W37 -- operator case-INSENSITIVE, so without the cast this constraint accepts
Matt W38 -- 'Bob' despite the lowercase-only character class. Verified.
Matt W39 CONSTRAINT handle_format CHECK (handle::text ~ '^[a-z0-9][a-z0-9-]{0,38}$')
Matt W40);
Matt W41
Matt W42-- Users and orgs share a handle namespace so that /{handle} is unambiguous.
Matt W43-- Enforced with triggers on both tables rather than a CHECK, which cannot see
Matt W44-- another table.
Matt W45CREATE FUNCTION assert_handle_unused() RETURNS trigger AS $$
Matt W46BEGIN
Matt W47 IF TG_TABLE_NAME = 'users' THEN
Matt W48 IF EXISTS (SELECT 1 FROM orgs WHERE handle = NEW.handle) THEN
Matt W49 RAISE EXCEPTION 'handle % is already taken by an organization', NEW.handle
Matt W50 USING ERRCODE = 'unique_violation';
Matt W51 END IF;
Matt W52 ELSE
Matt W53 IF EXISTS (SELECT 1 FROM users WHERE handle = NEW.handle) THEN
Matt W54 RAISE EXCEPTION 'handle % is already taken by a user', NEW.handle
Matt W55 USING ERRCODE = 'unique_violation';
Matt W56 END IF;
Matt W57 END IF;
Matt W58 RETURN NEW;
Matt W59END;
Matt W60$$ LANGUAGE plpgsql;
Matt W61
Matt W62CREATE TRIGGER users_handle_namespace
Matt W63 BEFORE INSERT OR UPDATE OF handle ON users
Matt W64 FOR EACH ROW EXECUTE FUNCTION assert_handle_unused();
Matt W65
Matt W66CREATE TRIGGER orgs_handle_namespace
Matt W67 BEFORE INSERT OR UPDATE OF handle ON orgs
Matt W68 FOR EACH ROW EXECUTE FUNCTION assert_handle_unused();
Matt W69
Matt W70-- Reserved handles: these are top-level routes, so a user or org holding one
Matt W71-- would shadow them (spec §7 puts /settings, /new, /search at the root).
Matt W72CREATE TABLE reserved_handles (handle citext PRIMARY KEY);
Matt W73INSERT INTO reserved_handles (handle) VALUES
Matt W74 ('new'), ('settings'), ('search'), ('login'), ('logout'), ('auth'),
Matt W75 ('setup'), ('admin'), ('api'), ('static'), ('assets'), ('healthz'),
Matt W76 ('readyz'), ('metrics'), ('about'), ('help'), ('docs'), ('status'),
Matt W77 ('explore'), ('notifications'), ('dashboard'), ('repos'), ('orgs'),
Matt W78 ('users'), ('git'), ('jj'), ('www'), ('mail'), ('root');
Matt W79
Matt W80CREATE TYPE org_role AS ENUM ('member', 'admin');
Matt W81
Matt W82CREATE TABLE org_members (
Matt W83 org_id uuid NOT NULL REFERENCES orgs ON DELETE CASCADE,
Matt W84 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W85 role org_role NOT NULL DEFAULT 'member',
Matt W86 created_at timestamptz NOT NULL DEFAULT now(),
Matt W87 PRIMARY KEY (org_id, user_id)
Matt W88);
Matt W89CREATE INDEX org_members_user_idx ON org_members (user_id);
Matt W90
Matt W91CREATE TABLE ssh_keys (
Matt W92 id uuid PRIMARY KEY,
Matt W93 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W94 name text NOT NULL,
Matt W95 key_type text NOT NULL,
Matt W96 fingerprint text NOT NULL UNIQUE, -- SHA256:… — the auth lookup key
Matt W97 public_key text NOT NULL,
Matt W98 last_used_at timestamptz,
Matt W99 created_at timestamptz NOT NULL DEFAULT now()
Matt W100);
Matt W101CREATE INDEX ssh_keys_fingerprint_idx ON ssh_keys (fingerprint);
Matt W102CREATE INDEX ssh_keys_user_idx ON ssh_keys (user_id);
Matt W103
Matt W104CREATE TABLE access_tokens (
Matt W105 id uuid PRIMARY KEY,
Matt W106 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W107 name text NOT NULL,
Matt W108 token_hash text NOT NULL, -- argon2id PHC string; plaintext shown once
Matt W109 prefix text NOT NULL, -- first 8 chars, for identification in UI
Matt W110 scopes text[] NOT NULL DEFAULT '{}',
Matt W111 expires_at timestamptz,
Matt W112 last_used_at timestamptz,
Matt W113 created_at timestamptz NOT NULL DEFAULT now()
Matt W114);
Matt W115CREATE INDEX access_tokens_user_idx ON access_tokens (user_id);
Matt W116-- Token auth looks up by prefix, then verifies the argon2 hash. Hashing is
Matt W117-- deliberately slow, so we must not hash against every row in the table.
Matt W118CREATE INDEX access_tokens_prefix_idx ON access_tokens (prefix);
Matt W119
Matt W120CREATE TABLE sessions (
Matt W121 id uuid PRIMARY KEY,
Matt W122 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W123 expires_at timestamptz NOT NULL,
Matt W124 user_agent text,
Matt W125 ip inet,
Matt W126 created_at timestamptz NOT NULL DEFAULT now()
Matt W127);
Matt W128CREATE INDEX sessions_user_idx ON sessions (user_id);
Matt W129CREATE INDEX sessions_expiry_idx ON sessions (expires_at);
Matt W130
Matt W131-- In-flight OIDC authorization code flows. PKCE verifier and nonce are held
Matt W132-- server-side and keyed by the opaque state parameter; nothing sensitive is
Matt W133-- round-tripped through the browser.
Matt W134CREATE TABLE auth_flows (
Matt W135 state text PRIMARY KEY,
Matt W136 pkce_verifier text NOT NULL,
Matt W137 nonce text NOT NULL,
Matt W138 redirect_after text,
Matt W139 created_at timestamptz NOT NULL DEFAULT now(),
Matt W140 expires_at timestamptz NOT NULL
Matt W141);
Matt W142CREATE INDEX auth_flows_expiry_idx ON auth_flows (expires_at);
Matt W143
Matt W144-- ─── access control (decided: invite/allowlist only) ─────────────────────────
Matt W145
Matt W146-- A successful OIDC login is not sufficient to get a Dogfood account. The
Matt W147-- identity must additionally match an allowlist entry or an open invitation.
Matt W148CREATE TABLE invitations (
Matt W149 id uuid PRIMARY KEY,
Matt W150 email citext NOT NULL UNIQUE,
Matt W151 invited_by uuid REFERENCES users ON DELETE SET NULL,
Matt W152 accepted_at timestamptz,
Matt W153 accepted_by uuid REFERENCES users ON DELETE SET NULL,
Matt W154 created_at timestamptz NOT NULL DEFAULT now()
Matt W155);
Matt W156
Matt W157-- One-time site-admin bootstrap (decided). The web process mints a token on
Matt W158-- first boot when no admin exists, logs it once, and stores only its hash.
Matt W159CREATE TABLE setup_tokens (
Matt W160 id uuid PRIMARY KEY,
Matt W161 token_hash text NOT NULL,
Matt W162 consumed_at timestamptz,
Matt W163 consumed_by uuid REFERENCES users ON DELETE SET NULL,
Matt W164 created_at timestamptz NOT NULL DEFAULT now()
Matt W165);
Matt W166
Matt W167-- ─── repositories ────────────────────────────────────────────────────────────
Matt W168
Matt W169CREATE TYPE owner_kind AS ENUM ('user', 'org');
Matt W170CREATE TYPE visibility AS ENUM ('public', 'private');
Matt W171
Matt W172CREATE TABLE repos (
Matt W173 id uuid PRIMARY KEY,
Matt W174 owner_kind owner_kind NOT NULL,
Matt W175 owner_user_id uuid REFERENCES users ON DELETE CASCADE,
Matt W176 owner_org_id uuid REFERENCES orgs ON DELETE CASCADE,
Matt W177 name citext NOT NULL,
Matt W178 description text,
Matt W179 visibility visibility NOT NULL DEFAULT 'private',
Matt W180 default_bookmark text NOT NULL DEFAULT 'main',
Matt W181 -- Forks use separate storage (decided): this records provenance only, and
Matt W182 -- never causes two repos to share an object database.
Matt W183 fork_of_repo_id uuid REFERENCES repos ON DELETE SET NULL,
Matt W184 size_bytes bigint NOT NULL DEFAULT 0,
Matt W185 pushed_at timestamptz,
Matt W186 archived boolean NOT NULL DEFAULT false,
Matt W187 created_at timestamptz NOT NULL DEFAULT now(),
Matt W188 CONSTRAINT one_owner CHECK (num_nonnulls(owner_user_id, owner_org_id) = 1),
Matt W189 CONSTRAINT owner_kind_matches CHECK (
Matt W190 (owner_kind = 'user' AND owner_user_id IS NOT NULL) OR
Matt W191 (owner_kind = 'org' AND owner_org_id IS NOT NULL)
Matt W192 ),
Matt W193 CONSTRAINT name_format CHECK (name ~ '^[A-Za-z0-9][A-Za-z0-9._-]{0,99}$' AND name !~ '\.\.')
Matt W194);
Matt W195CREATE UNIQUE INDEX repos_owner_name_idx ON repos (COALESCE(owner_user_id, owner_org_id), name);
Matt W196CREATE INDEX repos_visibility_idx ON repos (visibility) WHERE archived = false;
Matt W197
Matt W198CREATE TYPE repo_role AS ENUM ('read', 'write', 'maintain', 'admin');
Matt W199
Matt W200CREATE TABLE repo_collaborators (
Matt W201 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W202 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W203 role repo_role NOT NULL,
Matt W204 created_at timestamptz NOT NULL DEFAULT now(),
Matt W205 PRIMARY KEY (repo_id, user_id)
Matt W206);
Matt W207CREATE INDEX repo_collaborators_user_idx ON repo_collaborators (user_id);
Matt W208
Matt W209CREATE TABLE bookmarks (
Matt W210 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W211 name text NOT NULL,
Matt W212 target text NOT NULL, -- RevId
Matt W213 protected boolean NOT NULL DEFAULT false,
Matt W214 updated_at timestamptz NOT NULL DEFAULT now(),
Matt W215 PRIMARY KEY (repo_id, name)
Matt W216);
Matt W217
Matt W218-- ─── changes ─────────────────────────────────────────────────────────────────
Matt W219
Matt W220CREATE TYPE change_state AS ENUM ('draft', 'open', 'merged', 'abandoned');
Matt W221
Matt W222CREATE TABLE changes (
Matt W223 id uuid PRIMARY KEY,
Matt W224 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W225 change_id text NOT NULL, -- jj change id, canonical letter encoding
Matt W226 synthetic boolean NOT NULL DEFAULT false,
Matt W227 number bigint NOT NULL, -- per-repo display number
Matt W228 title text NOT NULL,
Matt W229 description text NOT NULL DEFAULT '',
Matt W230 -- 'draft' is set by the author in the UI and is never inferred from pushed
Matt W231 -- metadata (decided). The indexer may move a change to merged/abandoned but
Matt W232 -- must not overwrite draft.
Matt W233 state change_state NOT NULL DEFAULT 'open',
Matt W234 conflicted boolean NOT NULL DEFAULT false,
Matt W235 author_user_id uuid REFERENCES users ON DELETE SET NULL,
Matt W236 target_bookmark text NOT NULL,
Matt W237 head_revision_id uuid, -- FK added after revisions
Matt W238 merged_at timestamptz,
Matt W239 created_at timestamptz NOT NULL DEFAULT now(),
Matt W240 updated_at timestamptz NOT NULL DEFAULT now(),
Matt W241 UNIQUE (repo_id, change_id),
Matt W242 UNIQUE (repo_id, number)
Matt W243);
Matt W244CREATE INDEX changes_list_idx ON changes (repo_id, state, updated_at DESC);
Matt W245
Matt W246-- Prefix lookup: users type a short prefix, exactly as in the CLI.
Matt W247CREATE INDEX changes_prefix_idx ON changes (repo_id, change_id text_pattern_ops);
Matt W248
Matt W249CREATE TABLE revisions (
Matt W250 id uuid PRIMARY KEY,
Matt W251 change_id_fk uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
Matt W252 rev text NOT NULL, -- RevId
Matt W253 seq int NOT NULL, -- 1-based, per change
Matt W254 parents text[] NOT NULL DEFAULT '{}',
Matt W255 author_name text NOT NULL,
Matt W256 author_email text NOT NULL,
Matt W257 authored_at timestamptz NOT NULL,
Matt W258 message text NOT NULL,
Matt W259 conflicted boolean NOT NULL DEFAULT false,
Matt W260 conflict_data jsonb, -- sides, base, paths
Matt W261 pushed_by uuid REFERENCES users ON DELETE SET NULL,
Matt W262 pushed_at timestamptz NOT NULL DEFAULT now(),
Matt W263 UNIQUE (change_id_fk, rev),
Matt W264 UNIQUE (change_id_fk, seq)
Matt W265);
Matt W266CREATE INDEX revisions_rev_idx ON revisions (rev);
Matt W267
Matt W268ALTER TABLE changes ADD CONSTRAINT changes_head_fk
Matt W269 FOREIGN KEY (head_revision_id) REFERENCES revisions ON DELETE SET NULL;
Matt W270
Matt W271-- Stack edges: parent_change is immediately below child_change in a stack.
Matt W272-- Computed at index time; never recomputed on page render (spec §4).
Matt W273CREATE TABLE change_edges (
Matt W274 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W275 parent_change uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
Matt W276 child_change uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
Matt W277 PRIMARY KEY (parent_change, child_change),
Matt W278 CONSTRAINT no_self_edge CHECK (parent_change <> child_change)
Matt W279);
Matt W280CREATE INDEX change_edges_child_idx ON change_edges (repo_id, child_change);
Matt W281
Matt W282-- ─── discussion ──────────────────────────────────────────────────────────────
Matt W283
Matt W284CREATE TYPE anchor_state AS ENUM ('current', 'outdated', 'orphaned');
Matt W285
Matt W286CREATE TABLE comments (
Matt W287 id uuid PRIMARY KEY,
Matt W288 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W289 change_id_fk uuid REFERENCES changes ON DELETE CASCADE,
Matt W290 issue_id uuid, -- FK below
Matt W291 parent_id uuid REFERENCES comments ON DELETE CASCADE,
Matt W292 author_user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W293 body text NOT NULL,
Matt W294
Matt W295 -- inline anchor, null for top-level comments
Matt W296 anchor_revision uuid REFERENCES revisions ON DELETE SET NULL,
Matt W297 anchor_path text,
Matt W298 anchor_line int,
Matt W299 anchor_side text CHECK (anchor_side IN ('old','new')),
Matt W300 anchor_state anchor_state NOT NULL DEFAULT 'current',
Matt W301 anchor_context text, -- the line's content when written
Matt W302
Matt W303 resolved_at timestamptz,
Matt W304 resolved_by uuid REFERENCES users ON DELETE SET NULL,
Matt W305 edited_at timestamptz,
Matt W306 created_at timestamptz NOT NULL DEFAULT now(),
Matt W307 CONSTRAINT one_target CHECK (num_nonnulls(change_id_fk, issue_id) = 1)
Matt W308);
Matt W309CREATE INDEX comments_change_idx ON comments (change_id_fk, created_at);
Matt W310CREATE INDEX comments_issue_idx ON comments (issue_id, created_at);
Matt W311-- Anchor rebasing loads every inline comment on a change by its anchored file.
Matt W312CREATE INDEX comments_anchor_idx ON comments (change_id_fk, anchor_path)
Matt W313 WHERE anchor_path IS NOT NULL;
Matt W314
Matt W315CREATE TYPE review_verdict AS ENUM ('approve', 'request_changes', 'comment');
Matt W316
Matt W317CREATE TABLE reviews (
Matt W318 id uuid PRIMARY KEY,
Matt W319 change_id_fk uuid NOT NULL REFERENCES changes ON DELETE CASCADE,
Matt W320 revision_id uuid NOT NULL REFERENCES revisions ON DELETE CASCADE,
Matt W321 reviewer_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W322 verdict review_verdict NOT NULL,
Matt W323 body text,
Matt W324 created_at timestamptz NOT NULL DEFAULT now()
Matt W325);
Matt W326CREATE INDEX reviews_change_idx ON reviews (change_id_fk, created_at DESC);
Matt W327
Matt W328-- ─── issues ──────────────────────────────────────────────────────────────────
Matt W329
Matt W330CREATE TYPE issue_state AS ENUM ('open', 'closed');
Matt W331
Matt W332CREATE TABLE issues (
Matt W333 id uuid PRIMARY KEY,
Matt W334 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W335 number bigint NOT NULL,
Matt W336 title text NOT NULL,
Matt W337 body text NOT NULL DEFAULT '',
Matt W338 state issue_state NOT NULL DEFAULT 'open',
Matt W339 author_user_id uuid REFERENCES users ON DELETE SET NULL,
Matt W340 closed_at timestamptz,
Matt W341 created_at timestamptz NOT NULL DEFAULT now(),
Matt W342 updated_at timestamptz NOT NULL DEFAULT now(),
Matt W343 UNIQUE (repo_id, number)
Matt W344);
Matt W345CREATE INDEX issues_list_idx ON issues (repo_id, state, updated_at DESC);
Matt W346
Matt W347ALTER TABLE comments ADD CONSTRAINT comments_issue_fk
Matt W348 FOREIGN KEY (issue_id) REFERENCES issues ON DELETE CASCADE;
Matt W349
Matt W350CREATE TABLE labels (
Matt W351 id uuid PRIMARY KEY,
Matt W352 repo_id uuid NOT NULL REFERENCES repos ON DELETE CASCADE,
Matt W353 name text NOT NULL,
Matt W354 color text NOT NULL,
Matt W355 UNIQUE (repo_id, name)
Matt W356);
Matt W357
Matt W358CREATE TABLE issue_labels (
Matt W359 issue_id uuid NOT NULL REFERENCES issues ON DELETE CASCADE,
Matt W360 label_id uuid NOT NULL REFERENCES labels ON DELETE CASCADE,
Matt W361 PRIMARY KEY (issue_id, label_id)
Matt W362);
Matt W363
Matt W364CREATE TABLE issue_assignees (
Matt W365 issue_id uuid NOT NULL REFERENCES issues ON DELETE CASCADE,
Matt W366 user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
Matt W367 PRIMARY KEY (issue_id, user_id)
Matt W368);
Matt W369
Matt W370-- Per-repo issue and change numbering. A sequence per repo would be cleaner but
Matt W371-- cannot be created dynamically without DDL on every repo creation; this row is
Matt W372-- locked with SELECT … FOR UPDATE when allocating.
Matt W373CREATE TABLE repo_counters (
Matt W374 repo_id uuid PRIMARY KEY REFERENCES repos ON DELETE CASCADE,
Matt W375 next_change bigint NOT NULL DEFAULT 1,
Matt W376 next_issue bigint NOT NULL DEFAULT 1
Matt W377);
Matt W378
Matt W379-- ─── activity ────────────────────────────────────────────────────────────────
Matt W380
Matt W381-- Timeline events. Also the substrate a future activity feed would read from —
Matt W382-- written correctly now even though nothing renders a feed in v1.
Matt W383CREATE TABLE events (
Matt W384 id uuid PRIMARY KEY,
Matt W385 repo_id uuid REFERENCES repos ON DELETE CASCADE,
Matt W386 actor_id uuid REFERENCES users ON DELETE SET NULL,
Matt W387 kind text NOT NULL, -- pushed, rebased, conflicted, resolved,
Matt W388 -- merged, abandoned, reviewed, commented, …
Matt W389 subject_type text NOT NULL, -- change | issue | repo | bookmark
Matt W390 subject_id uuid,
Matt W391 payload jsonb NOT NULL DEFAULT '{}',
Matt W392 created_at timestamptz NOT NULL DEFAULT now()
Matt W393);
Matt W394CREATE INDEX events_repo_idx ON events (repo_id, created_at DESC);
Matt W395CREATE INDEX events_subject_idx ON events (subject_type, subject_id, created_at);
Matt W396
Matt W397CREATE TABLE audit_log (
Matt W398 id uuid PRIMARY KEY,
Matt W399 actor_id uuid REFERENCES users ON DELETE SET NULL,
Matt W400 action text NOT NULL,
Matt W401 target text NOT NULL,
Matt W402 ip inet,
Matt W403 metadata jsonb NOT NULL DEFAULT '{}',
Matt W404 created_at timestamptz NOT NULL DEFAULT now()
Matt W405);
Matt W406CREATE INDEX audit_log_actor_idx ON audit_log (actor_id, created_at DESC);
Matt W407CREATE INDEX audit_log_created_idx ON audit_log (created_at DESC);
Matt W408
Matt W409-- ─── rendering cache ─────────────────────────────────────────────────────────
Matt W410
Matt W411-- Syntax highlighting is expensive and its input is content-addressed, so the
Matt W412-- blob OID is a perfect cache key (spec §8). Highlighting on every request is
Matt W413-- the easiest performance mistake to make here.
Matt W414CREATE TABLE highlight_cache (
Matt W415 blob_oid text PRIMARY KEY,
Matt W416 language text,
Matt W417 html text NOT NULL,
Matt W418 bytes int NOT NULL,
Matt W419 created_at timestamptz NOT NULL DEFAULT now()
Matt W420);
Matt W421
Matt W422-- ─── jobs ────────────────────────────────────────────────────────────────────
Matt W423
Matt W424CREATE TABLE jobs (
Matt W425 id uuid PRIMARY KEY,
Matt W426 kind text NOT NULL,
Matt W427 payload jsonb NOT NULL,
Matt W428 run_at timestamptz NOT NULL DEFAULT now(),
Matt W429 attempts int NOT NULL DEFAULT 0,
Matt W430 max_attempts int NOT NULL DEFAULT 5,
Matt W431 locked_at timestamptz,
Matt W432 locked_by text,
Matt W433 last_error text,
Matt W434 created_at timestamptz NOT NULL DEFAULT now()
Matt W435);
Matt W436CREATE INDEX jobs_ready_idx ON jobs (run_at) WHERE locked_at IS NULL;
Matt W437
Matt W438-- Wake the worker on insert so it does not poll on the hot path. The worker
Matt W439-- also polls every 5s as a fallback for missed notifications (spec §5).
Matt W440CREATE FUNCTION notify_job() RETURNS trigger AS $$
Matt W441BEGIN
Matt W442 PERFORM pg_notify('dogfood_jobs', NEW.kind);
Matt W443 RETURN NEW;
Matt W444END;
Matt W445$$ LANGUAGE plpgsql;
Matt W446
Matt W447CREATE TRIGGER jobs_notify AFTER INSERT ON jobs
Matt W448 FOR EACH ROW EXECUTE FUNCTION notify_job();

448 lines · SQL