schema.sql

1-- Kepr Postgres schema — version 1.2--3-- A faithful translation of the cumulative SQLite schema (migrations 1..39,4-- as materialised by db.applyAll) into idiomatic Postgres. It is NOT derived5-- from prose: it was produced from `.schema` of a database with all 396-- migrations applied, so every column, constraint, CHECK, and index matches7-- what the live SQLite database actually holds.8--9-- Translation rules (chosen for an exact, value-preserving migration):10--   * SQLite INTEGER  -> BIGINT          (SQLite ints are 64-bit; size_bytes11--                                          and byte counts exceed int32)12--   * SQLite REAL     -> DOUBLE PRECISION13--   * SQLite TEXT     -> TEXT14--   * Integer 0/1 booleans are KEPT as BIGINT 0/1 — not converted to BOOLEAN —15--     so the migration is a verbatim value copy and db.zig reads them exactly16--     as it does today (i64, != 0 == true).17--   * INTEGER PRIMARY KEY AUTOINCREMENT -> BIGINT GENERATED BY DEFAULT AS18--     IDENTITY (BY DEFAULT, not ALWAYS, so the migration tool can insert the19--     existing ids; the sequence is advanced past MAX(id) after bulk load).20--   * All timestamps remain Unix epoch seconds (BIGINT), unconverted.21--22-- Foreign keys are declared as in SQLite. SQLite did not enforce them at23-- runtime, so the one-time migration tool loads with24-- `SET session_replication_role = replica` to bypass FK/trigger checks, then25-- the constraints guard all subsequently-written data.2627BEGIN;2829CREATE TABLE schema_version (30    version INTEGER NOT NULL31);32INSERT INTO schema_version (version) VALUES (1);3334-- ── Repositories and saves ───────────────────────────────────────────3536CREATE TABLE repos (37    name               TEXT PRIMARY KEY,38    created_at         BIGINT NOT NULL,39    description        TEXT,40    languages          TEXT DEFAULT '',41    details            TEXT,42    categories         TEXT DEFAULT '',43    count_extensions   TEXT DEFAULT '',44    contributor_handle TEXT,45    issues_enabled     BIGINT NOT NULL DEFAULT 1,46    languages_cloud    TEXT,47    project_id         TEXT,48    trusted            BIGINT NOT NULL DEFAULT 0,49    trust_computed_at  BIGINT,50    private            BIGINT NOT NULL DEFAULT 0,51    builds_enabled     BIGINT NOT NULL DEFAULT 1,52    display_languages  TEXT53);5455CREATE TABLE saves (56    id            TEXT NOT NULL,57    repo          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,58    lane          TEXT NOT NULL CHECK (lane IN ('main','dev')),59    message       TEXT NOT NULL,60    timestamp     BIGINT NOT NULL,61    size_bytes    BIGINT NOT NULL,62    parent_id     TEXT,63    is_checkpoint BIGINT NOT NULL DEFAULT 0,64    checksum      TEXT NOT NULL,65    manifest_path TEXT NOT NULL,66    file_count    BIGINT NOT NULL DEFAULT 0,67    line_count    BIGINT NOT NULL DEFAULT 0,68    session       TEXT,69    agent         TEXT,70    PRIMARY KEY (id, repo)71);72CREATE INDEX saves_repo_lane_ts ON saves(repo, lane, timestamp);7374CREATE TABLE object_refs (75    hex_hash TEXT NOT NULL,76    save_id  TEXT NOT NULL,77    repo     TEXT NOT NULL,78    PRIMARY KEY (hex_hash, save_id, repo)79);80CREATE INDEX object_refs_hash ON object_refs(hex_hash);8182-- Reserved advisory-lock id: Postgres advisory locks (pg_advisory_xact_lock /83-- pg_advisory_xact_lock_shared) are a single flat bigint namespace per84-- cluster, not scoped to any table, but the id kepr reserves here exists to85-- protect this table specifically. storage GC's sweep (storage_admin.gc)86-- holds it exclusive around deciding which objects/ files are unreferenced;87-- every offer-complete transaction that promotes rows into object_refs88-- (src/web/api.zig: handleComplete, acceptPending) takes it89-- shared first, so a commit that references an object already on disk can90-- never race a concurrent sweep into deleting that object (review F12,91-- decision D3). Shared holders never block each other. See92-- db_mod.STORAGE_GC_ADVISORY_LOCK_ID in src/db.zig for the actual id and the93-- lockGcExclusive/lockGcShared helpers — if kepr ever reserves another94-- advisory-lock id, pick a value nowhere near this one and document it here95-- too.9697-- Content-addressed cache of a countable file's line count, keyed by its98-- Blake3 hash. A hash's line count never changes, so once computed it never99-- needs decompressing again on a later save that re-references the same100-- unchanged file.101CREATE TABLE object_line_counts (102    hex_hash TEXT PRIMARY KEY,103    lines    BIGINT NOT NULL104);105106-- Operator-purged paths: the server-side equivalent of .kohignore (which kepr107-- strips from manifests, so it cannot be edited server-side). A path here was108-- stripped from every save's manifest by an operator; future offers that109-- re-introduce it are filtered on ingest. Matches a file exactly or as a dir.110CREATE TABLE repo_purged_paths (111    repo_id   TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,112    path      TEXT NOT NULL,113    purged_at BIGINT NOT NULL,114    PRIMARY KEY (repo_id, path)115);116117CREATE TABLE offer_tokens (118    offer_id    TEXT PRIMARY KEY,119    repo        TEXT NOT NULL,120    manifest_id TEXT NOT NULL,121    missing     TEXT NOT NULL,122    manifest    TEXT NOT NULL,123    key_type    TEXT NOT NULL CHECK (key_type IN ('operator','contributor')),124    expires_at  BIGINT NOT NULL,125    -- Account id that negotiated this token; complete must be performed by the126    -- same account (§8.9). NULL only for tokens predating protocol 2.0.127    negotiated_account TEXT128);129130CREATE TABLE pending_offers (131    offer_id      TEXT PRIMARY KEY,132    repo          TEXT NOT NULL,133    manifest_id   TEXT NOT NULL,134    manifest      TEXT NOT NULL,135    contributor   TEXT,136    submitted_at  BIGINT NOT NULL,137    status        TEXT NOT NULL DEFAULT 'pending'138        CHECK (status IN ('draft','pending','accepted','declined')),139    reviewed_at   BIGINT,140    review_note   TEXT,141    agent_verdict TEXT,142    agent_status  TEXT DEFAULT 'pending'143        CHECK (agent_status IN ('pending','running','complete','failed','unavailable')),144    findings_json TEXT,145    scan_status   TEXT CHECK (scan_status IN ('ok','failed')),146    -- Contributor's human-readable note to the reviewer (separate from save147    -- messages); 'draft' status holds an offer out of the review queue until148    -- the contributor promotes it.149    description   TEXT150);151152CREATE TABLE pending_offer_identity_quarantine (153    offer_id           TEXT PRIMARY KEY REFERENCES pending_offers(offer_id) ON DELETE CASCADE,154    legacy_fingerprint TEXT NOT NULL,155    reason             TEXT NOT NULL,156    recorded_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT157);158CREATE INDEX pending_offer_identity_quarantine_fp159    ON pending_offer_identity_quarantine(legacy_fingerprint);160161CREATE TABLE steals (162    id        TEXT PRIMARY KEY,163    repo      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,164    timestamp BIGINT NOT NULL165);166CREATE INDEX steals_repo_ts ON steals(repo, timestamp);167168CREATE TABLE storage_snapshots (169    date           TEXT PRIMARY KEY,170    total_bytes    BIGINT NOT NULL,171    object_count   BIGINT NOT NULL,172    repo_count     BIGINT NOT NULL,173    save_count     BIGINT NOT NULL,174    languages      TEXT,175    first_received BIGINT176);177178CREATE TABLE instance_images (179    filename     TEXT PRIMARY KEY,180    created_at   BIGINT NOT NULL,181    size_bytes   BIGINT NOT NULL,182    checksum     TEXT NOT NULL,183    repo_count   BIGINT NOT NULL,184    save_count   BIGINT NOT NULL,185    kepr_version TEXT NOT NULL186);187188CREATE TABLE diff_cache (189    repo        TEXT NOT NULL,190    save_id     TEXT NOT NULL,191    diff_json   TEXT NOT NULL,192    computed_at BIGINT NOT NULL,193    PRIMARY KEY (repo, save_id)194);195196CREATE TABLE ops_credentials (197    key   TEXT PRIMARY KEY,198    value TEXT NOT NULL199);200201-- ── Contributor handles ──────────────────────────────────────────────202203CREATE TABLE handles (204    id            TEXT PRIMARY KEY,205    handle        TEXT UNIQUE NOT NULL,206    created_at    BIGINT NOT NULL,207    recovery_hash TEXT NOT NULL208);209210-- ── Assets, dependencies, builds, releases ───────────────────────────211212CREATE TABLE repo_assets (213    id          TEXT PRIMARY KEY,214    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,215    filename    TEXT NOT NULL,216    mime_type   TEXT NOT NULL,217    size_bytes  BIGINT NOT NULL,218    uploaded_at BIGINT NOT NULL219);220221CREATE TABLE repo_dependencies (222    from_repo       TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,223    to_repo         TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,224    first_stolen_at BIGINT NOT NULL,225    last_stolen_at  BIGINT NOT NULL,226    PRIMARY KEY (from_repo, to_repo)227);228229CREATE TABLE build_runs (230    id               TEXT PRIMARY KEY,231    repo_id          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,232    save_id          TEXT NOT NULL,233    version          TEXT NOT NULL,234    trigger          TEXT NOT NULL,235    status           TEXT NOT NULL,236    started_at       BIGINT NOT NULL,237    finished_at      BIGINT,238    log              TEXT,239    release_notes    TEXT,240    platform_results TEXT NOT NULL DEFAULT '{}',241    duration_ms      BIGINT,242    lane             TEXT NOT NULL DEFAULT 'main'243);244245CREATE TABLE release_artifacts (246    id          TEXT PRIMARY KEY,247    build_run   TEXT NOT NULL REFERENCES build_runs(id),248    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,249    version     TEXT NOT NULL,250    platform    TEXT NOT NULL,251    filename    TEXT NOT NULL,252    size_bytes  BIGINT NOT NULL,253    sha256      TEXT NOT NULL,254    blake3      TEXT NOT NULL,255    signature   TEXT NOT NULL DEFAULT '',256    built_at    BIGINT NOT NULL257);258CREATE UNIQUE INDEX release_artifacts_repo_version_platform259    ON release_artifacts (repo_id, version, platform);260261-- ── Issues ───────────────────────────────────────────────────────────262263CREATE TABLE issues (264    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,265    repo          TEXT    NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,266    title         TEXT    NOT NULL,267    description   TEXT    NOT NULL,268    contact       TEXT,269    filed_at      BIGINT  NOT NULL,270    filed_save    TEXT    NOT NULL,271    filed_ver     TEXT,272    status        TEXT    NOT NULL DEFAULT 'open',273    closed_at     BIGINT,274    closed_save   TEXT,275    ip_hash       TEXT,276    number        BIGINT  NOT NULL DEFAULT 0,277    type          TEXT    NOT NULL DEFAULT 'bug',278    severity      TEXT,279    face_id       TEXT,280    resolution_face TEXT,281    account_id    TEXT,282    contact_email TEXT,283    updated_at    BIGINT  NOT NULL DEFAULT 0,284    reporter_name TEXT285);286CREATE UNIQUE INDEX issues_repo_number ON issues(repo, number);287CREATE INDEX issues_repo_status_created288    ON issues(repo, status, filed_at DESC);289CREATE INDEX issues_repo_type_status_created290    ON issues(repo, type, status, filed_at DESC);291292CREATE TABLE issue_notes (293    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,294    issue_id  BIGINT  NOT NULL REFERENCES issues(id),295    body      TEXT    NOT NULL,296    posted_at BIGINT  NOT NULL,297    is_op     BIGINT  NOT NULL DEFAULT 0298);299300CREATE TABLE issue_entries (301    id         TEXT PRIMARY KEY,302    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,303    account_id TEXT,304    entry_type TEXT NOT NULL CHECK (entry_type IN ('reply','status_change','face_linked')),305    body       TEXT,306    created_at BIGINT NOT NULL307);308CREATE INDEX issue_entries_issue ON issue_entries(issue_id, created_at);309310CREATE TABLE issue_attachments (311    id          TEXT PRIMARY KEY,312    issue_id    BIGINT REFERENCES issues(id) ON DELETE CASCADE,313    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,314    filename    TEXT NOT NULL,315    mime_type   TEXT NOT NULL,316    size_bytes  BIGINT NOT NULL,317    path        TEXT NOT NULL,318    tmp_token   TEXT,319    tmp_expires BIGINT,320    created_at  BIGINT NOT NULL321);322CREATE INDEX issue_attachments_issue ON issue_attachments(issue_id, created_at);323CREATE INDEX issue_attachments_repo ON issue_attachments(repo_id, created_at);324325-- ── Caches and challenges ────────────────────────────────────────────326327CREATE TABLE graph_cache (328    repo         TEXT NOT NULL,329    head_save_id TEXT NOT NULL,330    json         TEXT NOT NULL,331    computed_at  BIGINT NOT NULL,332    PRIMARY KEY (repo, head_save_id)333);334335CREATE TABLE archive_cache (336    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,337    save_id    TEXT NOT NULL,338    path       TEXT NOT NULL,339    size_bytes BIGINT NOT NULL,340    created_at BIGINT NOT NULL,341    -- Vestigial: live archive nar hashes are kept in archive_hashes now. The342    -- column still exists in the SQLite source schema, so it is mirrored here so343    -- the one-time SQLite→Postgres migration (which copies every source column344    -- via PRAGMA table_info) does not fail on a missing target column.345    nar_hash   TEXT,346    PRIMARY KEY (repo_id, save_id)347);348349CREATE TABLE archive_hashes (350    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,351    save_id     TEXT NOT NULL,352    blake3      TEXT NOT NULL,353    sha256      TEXT NOT NULL,354    nar_hash    TEXT,355    computed_at BIGINT NOT NULL,356    PRIMARY KEY (repo_id, save_id)357);358359CREATE TABLE pow_challenges (360    id         TEXT PRIMARY KEY,361    nonce      TEXT NOT NULL,362    difficulty BIGINT NOT NULL DEFAULT 4,363    created_at BIGINT NOT NULL,364    expires_at BIGINT NOT NULL,365    used       BIGINT NOT NULL DEFAULT 0366);367368-- ── Accounts, sessions, auth ─────────────────────────────────────────369370CREATE TABLE accounts (371    id                   TEXT PRIMARY KEY,372    username             TEXT NOT NULL UNIQUE,373    bio                  TEXT,374    location             TEXT,375    website              TEXT,376    email                TEXT,377    recovery_hash        TEXT NOT NULL,378    created_at           BIGINT NOT NULL,379    tier                 TEXT NOT NULL DEFAULT 'contributor'380        CHECK (tier IN ('operator', 'team', 'contributor')),381    -- Operator web-login credentials (bcrypt password + base32 TOTP secret).382    -- NULL until the operator enrols at /login; only the operator-tier row uses them.383    password_hash        TEXT,384    totp_secret          TEXT,385    notif_offer_accepted BIGINT NOT NULL DEFAULT 1,386    notif_offer_rejected BIGINT NOT NULL DEFAULT 1,387    notif_issue_response BIGINT NOT NULL DEFAULT 1,388    notif_issue_resolved BIGINT NOT NULL DEFAULT 1,389    notif_issue_status   BIGINT NOT NULL DEFAULT 1390);391CREATE INDEX accounts_username ON accounts(username);392-- Partial unique: one account per non-null email, enforced at the database393-- so the join handler's check-then-act can't lose a race under concurrent394-- registrations (review F4). Existing databases get this via db.zig's395-- ensureUniqueEmailIndex idempotent migration instead, which must detect396-- pre-existing duplicates first (D4).397CREATE UNIQUE INDEX accounts_email_unique ON accounts(email) WHERE email IS NOT NULL;398399-- ── Internal tickets ─────────────────────────────────────────────────400-- Operator/team-only ticket tracker. created_by/assigned_to reference401-- accounts(id) (TEXT, not the int ids the original brief assumed).402-- resolving_save_id is a plain TEXT column with no FK: saves has a403-- composite PRIMARY KEY (id, repo), so a single-column FK to saves(id)404-- is not expressible; integrity is enforced in the application layer.405406CREATE TABLE tickets (407    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,408    title             TEXT NOT NULL,409    body              TEXT NOT NULL DEFAULT '',410    state             TEXT NOT NULL DEFAULT 'open'411        CHECK (state IN ('open', 'in_progress', 'closed')),412    repo              TEXT REFERENCES repos(name) ON UPDATE CASCADE ON DELETE SET NULL,413    resolving_save_id TEXT,414    created_by        TEXT NOT NULL REFERENCES accounts(id),415    assigned_to       TEXT REFERENCES accounts(id) ON DELETE SET NULL,416    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,417    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,418    closed_at         BIGINT419);420CREATE INDEX tickets_state ON tickets(state, created_at DESC);421CREATE INDEX tickets_repo ON tickets(repo);422CREATE INDEX tickets_assigned ON tickets(assigned_to);423CREATE INDEX tickets_created_by ON tickets(created_by);424425CREATE TABLE ticket_attachments (426    id          TEXT PRIMARY KEY,427    ticket_id   BIGINT REFERENCES tickets(id) ON DELETE CASCADE,428    repo_id     TEXT REFERENCES repos(name) ON UPDATE CASCADE,429    filename    TEXT NOT NULL,430    mime_type   TEXT NOT NULL,431    size_bytes  BIGINT NOT NULL,432    path        TEXT NOT NULL,433    created_at  BIGINT NOT NULL434);435CREATE INDEX ticket_attachments_ticket ON ticket_attachments(ticket_id, created_at);436CREATE INDEX ticket_attachments_repo ON ticket_attachments(repo_id, created_at);437438-- ── Collaboration layer + notifications ──────────────────────────────439-- author/account columns reference accounts(id) (TEXT). Comment ids are440-- BIGINT identity to match the project convention (the brief assumed441-- SQLite-era INTEGER identity). save_comments carries both save_id and442-- repo so a composite FK to saves(id, repo) is expressible; the other443-- save-keyed tables hold only save_id (plain TEXT, no FK — saves has a444-- composite PK) and rely on application-layer integrity. Boolean-ish445-- columns are BIGINT 0/1 to match the existing schema (accounts.notif_*,446-- repos.*), since the pg shim binds/reads integers, not SQL BOOLEAN.447448CREATE TABLE save_comments (449    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,450    save_id           TEXT NOT NULL,451    repo              TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,452    author_account_id TEXT NOT NULL REFERENCES accounts(id),453    body              TEXT NOT NULL,454    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,455    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,456    edited            BIGINT NOT NULL DEFAULT 0,457    FOREIGN KEY (save_id, repo) REFERENCES saves(id, repo) ON DELETE CASCADE458);459CREATE INDEX save_comments_save_id ON save_comments(save_id, created_at);460CREATE INDEX save_comments_repo ON save_comments(repo, created_at DESC);461CREATE INDEX save_comments_author ON save_comments(author_account_id);462463CREATE TABLE save_acknowledgements (464    save_id    TEXT NOT NULL,465    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,466    kind       TEXT NOT NULL DEFAULT 'ack'467        CHECK (kind IN ('ack', 'flag')),468    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,469    PRIMARY KEY (save_id, account_id)470);471CREATE INDEX save_acks_save_id ON save_acknowledgements(save_id);472473CREATE TABLE draft_comments (474    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,475    offer_id          TEXT NOT NULL REFERENCES pending_offers(offer_id) ON DELETE CASCADE,476    author_account_id TEXT NOT NULL REFERENCES accounts(id),477    body              TEXT NOT NULL,478    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT479);480CREATE INDEX draft_comments_offer ON draft_comments(offer_id, created_at);481482CREATE TABLE notifications (483    id           BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,484    account_id   TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,485    kind         TEXT NOT NULL CHECK (kind IN (486        'save_comment', 'save_comment_reply', 'save_mention', 'save_ack',487        'save_flag', 'contribution_accepted', 'contribution_declined',488        'contribution_pending', 'draft_comment', 'issue_filed', 'issue_reply',489        'issue_response', 'issue_resolved', 'issue_status',490        'ticket_assigned', 'ticket_state_changed', 'team_promoted',491        'milestone_saves', 'milestone_downloads', 'milestone_first_steal',492        'milestone_first_contributor', 'milestone_trusted'493    )),494    payload_json TEXT NOT NULL DEFAULT '{}',495    preview      TEXT NOT NULL DEFAULT '',496    href         TEXT NOT NULL DEFAULT '/',497    read         BIGINT NOT NULL DEFAULT 0,498    created_at   BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,499    read_at      BIGINT500);501CREATE INDEX notifications_account_unread ON notifications(account_id, read, created_at DESC);502CREATE INDEX notifications_account_all ON notifications(account_id, created_at DESC);503CREATE INDEX notifications_created_at ON notifications(created_at DESC);504505CREATE TABLE notification_preferences (506    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,507    kind       TEXT NOT NULL,508    in_app     BIGINT NOT NULL DEFAULT 1,509    email      BIGINT NOT NULL DEFAULT 0,510    PRIMARY KEY (account_id, kind)511);512513CREATE TABLE notification_category_prefs (514    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,515    category   TEXT NOT NULL CHECK (category IN (516        'contributions', 'discussion', 'issues', 'tickets', 'milestones'517    )),518    in_app     BIGINT NOT NULL DEFAULT 1,519    email      BIGINT NOT NULL DEFAULT 0,520    PRIMARY KEY (account_id, category)521);522523CREATE TABLE sessions (524    id         TEXT PRIMARY KEY,525    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,526    created_at BIGINT NOT NULL,527    expires_at BIGINT NOT NULL,528    last_used  BIGINT NOT NULL,529    user_agent TEXT530);531CREATE INDEX sessions_account ON sessions(account_id);532CREATE INDEX sessions_expiry ON sessions(expires_at);533534CREATE TABLE account_recovery_codes (535    id          TEXT PRIMARY KEY,536    account_id  TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,537    code_hash   TEXT NOT NULL,538    created_at  BIGINT NOT NULL,539    expires_at  BIGINT NOT NULL,540    used        BIGINT NOT NULL DEFAULT 0541);542CREATE INDEX account_recovery_codes_account ON account_recovery_codes(account_id, expires_at);543544-- ── Health, safety, trust ────────────────────────────────────────────545546CREATE TABLE repo_health (547    repo_id        TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,548    save_id        TEXT NOT NULL,549    computed_at    BIGINT NOT NULL,550    secrets_status TEXT NOT NULL DEFAULT 'clean',551    secrets_detail TEXT,552    deps_status    TEXT NOT NULL DEFAULT 'none',553    deps_detail    TEXT,554    builds_status  TEXT NOT NULL DEFAULT 'none',555    builds_detail  TEXT,556    agents_status  TEXT NOT NULL DEFAULT 'none',557    agents_detail  TEXT,558    license_status TEXT NOT NULL DEFAULT 'none',559    license_detail TEXT,560    trust_status   TEXT NOT NULL DEFAULT 'clean',561    trust_detail   TEXT,562    collapsed_html TEXT NOT NULL DEFAULT ''563);564565CREATE TABLE secret_scan_results (566    id          TEXT PRIMARY KEY,567    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,568    save_id     TEXT NOT NULL,569    file_path   TEXT NOT NULL,570    line_number BIGINT,571    pattern     TEXT NOT NULL,572    entropy     DOUBLE PRECISION,573    severity    TEXT NOT NULL,574    status      TEXT NOT NULL,575    detected_at BIGINT NOT NULL576);577CREATE INDEX secret_scan_results_repo_save578    ON secret_scan_results(repo_id, save_id, detected_at DESC);579580CREATE TABLE secret_false_positives (581    repo_id   TEXT NOT NULL,582    file_path TEXT NOT NULL,583    marked_at BIGINT NOT NULL,584    PRIMARY KEY (repo_id, file_path)585);586587CREATE TABLE code_finding_false_positives (588    repo_id   TEXT NOT NULL,589    code      TEXT NOT NULL,590    path      TEXT NOT NULL,591    marked_at BIGINT NOT NULL,592    PRIMARY KEY (repo_id, code, path)593);594595-- Operator-dismissed safety findings. A suppressed finding is filtered from596-- the public repo page and the admin panel; the dismissed list remains597-- visible and reversible. Keyed by (repo_id, file_path, rule, fingerprint)598-- so a suppression only matches when the same content fires the same rule.599CREATE TABLE safety_suppressions (600    id           TEXT PRIMARY KEY,601    repo_id      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,602    file_path    TEXT NOT NULL,603    rule         TEXT NOT NULL,604    fingerprint  TEXT NOT NULL,605    dismissed_at BIGINT NOT NULL,606    dismissed_by TEXT NOT NULL REFERENCES accounts(id),607    note         TEXT608);609CREATE INDEX safety_suppressions_repo ON safety_suppressions(repo_id, file_path, rule);610611CREATE TABLE dep_advisories (612    id          TEXT PRIMARY KEY,613    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,614    save_id     TEXT NOT NULL,615    dep_name    TEXT NOT NULL,616    dep_version TEXT NOT NULL,617    cve_id      TEXT,618    severity    TEXT NOT NULL,619    summary     TEXT NOT NULL,620    fix_version TEXT,621    detected_at BIGINT NOT NULL622);623CREATE INDEX dep_advisories_repo_save624    ON dep_advisories(repo_id, save_id, detected_at DESC);625626CREATE TABLE binary_sizes (627    id         TEXT PRIMARY KEY,628    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,629    save_id    TEXT NOT NULL,630    platform   TEXT NOT NULL,631    size_bytes BIGINT NOT NULL,632    built_at   BIGINT NOT NULL633);634CREATE INDEX binary_sizes_repo_save635    ON binary_sizes(repo_id, save_id, built_at DESC);636637CREATE TABLE trust_results (638    repo_id             TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,639    save_id             TEXT NOT NULL,640    trusted             BIGINT NOT NULL,641    secrets_clean       BIGINT NOT NULL,642    no_binary_blobs     BIGINT NOT NULL,643    build_scripts_clean BIGINT NOT NULL,644    no_trojan_source    BIGINT NOT NULL,645    supply_chain_stable BIGINT NOT NULL,646    licenses_compatible BIGINT NOT NULL,647    has_attribution     BIGINT NOT NULL,648    detail_json         TEXT NOT NULL DEFAULT '{}',649    computed_at         BIGINT NOT NULL650);651652-- ── Engagement and notifications ─────────────────────────────────────653654CREATE TABLE engagement_events (655    id          TEXT PRIMARY KEY,656    event_type  TEXT NOT NULL,657    repo_id     TEXT,658    save_id     TEXT,659    platform    TEXT,660    occurred_at BIGINT NOT NULL661);662CREATE INDEX eng_repo_type ON engagement_events(repo_id, event_type, occurred_at DESC);663CREATE INDEX eng_type_time ON engagement_events(event_type, occurred_at DESC);664665CREATE TABLE notification_queue (666    id         TEXT PRIMARY KEY,667    account_id TEXT,668    type       TEXT NOT NULL,669    payload    TEXT NOT NULL,670    status     TEXT NOT NULL DEFAULT 'pending',671    attempts   BIGINT NOT NULL DEFAULT 0,672    next_retry BIGINT,673    created_at BIGINT NOT NULL,674    sent_at    BIGINT675);676CREATE INDEX notification_queue_pending677    ON notification_queue(status, next_retry);678679-- ┌─────────────────────────────────────────────────────────────────┐680-- │  JOB QUEUE — Postgres-native, SELECT FOR UPDATE SKIP LOCKED.      │681-- │  New in the Postgres architecture; has no SQLite predecessor.     │682-- └─────────────────────────────────────────────────────────────────┘683684CREATE TABLE jobs (685    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,686    kind         TEXT NOT NULL,687    payload_json TEXT NOT NULL DEFAULT '{}',688    status       TEXT NOT NULL DEFAULT 'pending'689        CHECK (status IN ('pending','running','done','failed')),690    -- Only one pending/running job per (kind, dedup_key) — collapses the691    -- thundering herd of a 1000-save offer into one job of each kind per repo.692    dedup_key    TEXT,693    priority     BIGINT NOT NULL DEFAULT 5,694    attempts     BIGINT NOT NULL DEFAULT 0,695    max_attempts BIGINT NOT NULL DEFAULT 3,696    created_at   BIGINT NOT NULL,697    started_at   BIGINT,698    completed_at BIGINT,699    error_text   TEXT,700    run_after    BIGINT NOT NULL DEFAULT 0701);702703-- Worker poll path: lowest priority then oldest, pending only.704CREATE INDEX jobs_pending ON jobs(priority, created_at)705    WHERE status = 'pending';706707-- Deduplication: enforce one live job per (kind, dedup_key).708CREATE UNIQUE INDEX jobs_dedup ON jobs(kind, dedup_key)709    WHERE status IN ('pending','running') AND dedup_key IS NOT NULL;710711-- Cleanup scan for pruning finished jobs.712CREATE INDEX jobs_completed_at ON jobs(completed_at)713    WHERE status IN ('done','failed');714715COMMIT;