| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S14 push processing pipeline. |
| 4 | -- |
| 5 | -- Three tables + one column: |
| 6 | -- * jobs — Postgres-backed work queue with |
| 7 | -- FOR UPDATE SKIP LOCKED dispatch. |
| 8 | -- * push_events — one row per ref pushed, written by the |
| 9 | -- post-receive hook; consumed by the |
| 10 | -- push:process job. |
| 11 | -- * webhook_events_pending — accumulator drained by the S33 webhook |
| 12 | -- deliverer; kept separate from the |
| 13 | -- generic job table so S33 controls its |
| 14 | -- own delivery cadence. |
| 15 | -- * repos.default_branch_oid — set by push:process the first time the |
| 16 | -- repo's default branch receives a |
| 17 | -- commit; the home page reads it to |
| 18 | -- decide whether to render the empty or |
| 19 | -- populated view (S11/S17). |
| 20 | -- |
| 21 | -- Why a Postgres queue instead of Redis: keeps the runtime dependency |
| 22 | -- surface to one engine; FOR UPDATE SKIP LOCKED gives us safe concurrent |
| 23 | -- dispatch out of the box; LISTEN/NOTIFY gives us idle wake-ups without |
| 24 | -- polling. If we ever need cross-region or millions-of-jobs throughput |
| 25 | -- we'll revisit, but that's well past MVP. |
| 26 | |
| 27 | -- +goose Up |
| 28 | |
| 29 | CREATE TABLE jobs ( |
| 30 | id bigserial PRIMARY KEY, |
| 31 | kind text NOT NULL, |
| 32 | payload jsonb NOT NULL DEFAULT '{}', |
| 33 | run_at timestamptz NOT NULL DEFAULT now(), |
| 34 | attempts int NOT NULL DEFAULT 0, |
| 35 | max_attempts int NOT NULL DEFAULT 5, |
| 36 | last_error text, |
| 37 | locked_by text, |
| 38 | locked_at timestamptz, |
| 39 | completed_at timestamptz, |
| 40 | failed_at timestamptz, |
| 41 | created_at timestamptz NOT NULL DEFAULT now(), |
| 42 | |
| 43 | CONSTRAINT jobs_kind_length CHECK (char_length(kind) BETWEEN 1 AND 100), |
| 44 | CONSTRAINT jobs_attempts_nonneg CHECK (attempts >= 0), |
| 45 | CONSTRAINT jobs_max_attempts_pos CHECK (max_attempts > 0) |
| 46 | ); |
| 47 | |
| 48 | -- The dispatch index: workers query for the oldest runnable row of a given |
| 49 | -- kind. Partial because only un-completed and un-failed rows are dispatchable. |
| 50 | CREATE INDEX jobs_dispatch_idx |
| 51 | ON jobs (kind, run_at) |
| 52 | WHERE completed_at IS NULL AND failed_at IS NULL; |
| 53 | |
| 54 | -- Visibility: which jobs are currently held by which worker. |
| 55 | CREATE INDEX jobs_locked_idx |
| 56 | ON jobs (locked_by, locked_at) |
| 57 | WHERE locked_by IS NOT NULL; |
| 58 | |
| 59 | |
| 60 | CREATE TABLE push_events ( |
| 61 | id bigserial PRIMARY KEY, |
| 62 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 63 | pusher_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 64 | before_sha text NOT NULL, |
| 65 | after_sha text NOT NULL, |
| 66 | ref text NOT NULL, |
| 67 | protocol text NOT NULL, |
| 68 | request_id text NOT NULL DEFAULT '', |
| 69 | processed_at timestamptz, |
| 70 | created_at timestamptz NOT NULL DEFAULT now(), |
| 71 | |
| 72 | CONSTRAINT push_events_protocol CHECK (protocol IN ('http', 'ssh')), |
| 73 | CONSTRAINT push_events_ref_length CHECK (char_length(ref) BETWEEN 1 AND 255), |
| 74 | CONSTRAINT push_events_sha_length CHECK ( |
| 75 | char_length(before_sha) BETWEEN 1 AND 64 AND |
| 76 | char_length(after_sha) BETWEEN 1 AND 64 |
| 77 | ) |
| 78 | ); |
| 79 | |
| 80 | CREATE INDEX push_events_repo_id_idx ON push_events (repo_id, created_at DESC); |
| 81 | CREATE INDEX push_events_unprocessed_idx |
| 82 | ON push_events (created_at) |
| 83 | WHERE processed_at IS NULL; |
| 84 | |
| 85 | |
| 86 | CREATE TABLE webhook_events_pending ( |
| 87 | id bigserial PRIMARY KEY, |
| 88 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 89 | event_kind text NOT NULL, |
| 90 | payload jsonb NOT NULL, |
| 91 | created_at timestamptz NOT NULL DEFAULT now(), |
| 92 | |
| 93 | CONSTRAINT webhook_events_pending_kind_length CHECK (char_length(event_kind) BETWEEN 1 AND 100) |
| 94 | ); |
| 95 | |
| 96 | CREATE INDEX webhook_events_pending_repo_id_idx |
| 97 | ON webhook_events_pending (repo_id, created_at); |
| 98 | |
| 99 | |
| 100 | -- default_branch_oid is the OID at HEAD of repos.default_branch. NULL |
| 101 | -- until the first push lands; the repo home view checks this to fork |
| 102 | -- between empty and populated layouts (refs-on-disk fallback in S11). |
| 103 | ALTER TABLE repos ADD COLUMN default_branch_oid text; |
| 104 | |
| 105 | -- +goose Down |
| 106 | ALTER TABLE repos DROP COLUMN IF EXISTS default_branch_oid; |
| 107 | DROP TABLE IF EXISTS webhook_events_pending; |
| 108 | DROP TABLE IF EXISTS push_events; |
| 109 | DROP TABLE IF EXISTS jobs; |
| 110 |