| 1 | -- This Source Code Form is subject to the terms of the Mozilla Public |
| 2 | -- License, v. 2.0. If a copy of the MPL was not distributed with this |
| 3 | -- file, You can obtain one at https://mozilla.org/MPL/2.0/. |
| 4 | |
| 5 | -- Initial schema (PostgreSQL dialect). |
| 6 | -- |
| 7 | -- This must stay structurally equivalent to migrations/sqlite/0001_init.sql; |
| 8 | -- the only permitted differences are the boolean column type (BOOLEAN here, |
| 9 | -- INTEGER 0/1 there) and dialect punctuation. Timestamps are BIGINT Unix |
| 10 | -- milliseconds UTC; identifiers are 26-char ULID TEXT (lexicographically |
| 11 | -- sortable — order by them). Case-insensitive uniqueness is enforced with a |
| 12 | -- normalized `*_lower` column plus a unique index, never collation. |
| 13 | |
| 14 | CREATE TABLE users ( |
| 15 | id TEXT PRIMARY KEY, |
| 16 | username TEXT NOT NULL, |
| 17 | username_lower TEXT NOT NULL UNIQUE, |
| 18 | email TEXT NOT NULL, |
| 19 | email_lower TEXT NOT NULL UNIQUE, |
| 20 | display_name TEXT, |
| 21 | password_hash TEXT, -- NULL until an invite is completed |
| 22 | must_change_password BOOLEAN NOT NULL DEFAULT FALSE, |
| 23 | is_admin BOOLEAN NOT NULL DEFAULT FALSE, |
| 24 | disabled_at BIGINT, |
| 25 | created_at BIGINT NOT NULL, |
| 26 | updated_at BIGINT NOT NULL |
| 27 | ); |
| 28 | |
| 29 | CREATE TABLE invites ( |
| 30 | id TEXT PRIMARY KEY, |
| 31 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 32 | token_hash TEXT NOT NULL UNIQUE, -- SHA-256 of the emailed token |
| 33 | purpose TEXT NOT NULL, -- 'activate' | 'reset' |
| 34 | expires_at BIGINT NOT NULL, |
| 35 | used_at BIGINT, |
| 36 | created_at BIGINT NOT NULL |
| 37 | ); |
| 38 | |
| 39 | CREATE TABLE sessions ( |
| 40 | id TEXT PRIMARY KEY, -- SHA-256 of the cookie value |
| 41 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 42 | user_agent TEXT, |
| 43 | ip TEXT, |
| 44 | created_at BIGINT NOT NULL, |
| 45 | last_seen_at BIGINT NOT NULL, |
| 46 | expires_at BIGINT NOT NULL |
| 47 | ); |
| 48 | |
| 49 | CREATE TABLE api_tokens ( |
| 50 | id TEXT PRIMARY KEY, -- the JWT `jti` |
| 51 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 52 | name TEXT NOT NULL, |
| 53 | scopes TEXT NOT NULL, -- comma-separated |
| 54 | expires_at BIGINT, |
| 55 | revoked_at BIGINT, |
| 56 | last_used_at BIGINT, |
| 57 | created_at BIGINT NOT NULL |
| 58 | ); |
| 59 | |
| 60 | CREATE TABLE keys ( |
| 61 | id TEXT PRIMARY KEY, |
| 62 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 63 | kind TEXT NOT NULL, -- 'ssh' | 'gpg' |
| 64 | name TEXT, |
| 65 | fingerprint TEXT NOT NULL, -- SHA256:… for ssh, full hex fp for gpg |
| 66 | public_key TEXT NOT NULL, -- authorized_keys line or armored pgp block |
| 67 | ordinal INTEGER NOT NULL, -- stable 1-based index per (user, kind) |
| 68 | last_used_at BIGINT, |
| 69 | created_at BIGINT NOT NULL, |
| 70 | UNIQUE (kind, fingerprint), |
| 71 | UNIQUE (user_id, kind, ordinal) |
| 72 | ); |
| 73 | |
| 74 | CREATE TABLE groups ( |
| 75 | id TEXT PRIMARY KEY, |
| 76 | owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 77 | parent_id TEXT REFERENCES groups(id) ON DELETE CASCADE, |
| 78 | name TEXT NOT NULL, |
| 79 | path TEXT NOT NULL, -- materialized 'group/sub/leaf' |
| 80 | description TEXT, |
| 81 | created_at BIGINT NOT NULL, |
| 82 | UNIQUE (owner_id, path) |
| 83 | ); |
| 84 | |
| 85 | CREATE TABLE repos ( |
| 86 | id TEXT PRIMARY KEY, |
| 87 | owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 88 | group_id TEXT REFERENCES groups(id) ON DELETE SET NULL, |
| 89 | name TEXT NOT NULL, |
| 90 | path TEXT NOT NULL, -- materialized 'group/sub/repo' (== name when ungrouped) |
| 91 | description TEXT, |
| 92 | is_private BOOLEAN NOT NULL DEFAULT TRUE, |
| 93 | default_branch TEXT NOT NULL, |
| 94 | archived_at BIGINT, |
| 95 | size_bytes BIGINT NOT NULL DEFAULT 0, |
| 96 | pushed_at BIGINT, |
| 97 | created_at BIGINT NOT NULL, |
| 98 | updated_at BIGINT NOT NULL, |
| 99 | UNIQUE (owner_id, path) |
| 100 | ); |
| 101 | |
| 102 | CREATE TABLE repo_collaborators ( |
| 103 | repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 104 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 105 | permission TEXT NOT NULL, -- 'read' | 'write' | 'admin' |
| 106 | created_at BIGINT NOT NULL, |
| 107 | PRIMARY KEY (repo_id, user_id) |
| 108 | ); |
| 109 | |
| 110 | -- Reserved and inert until the CI ships (phase 18+). No behaviour reads these |
| 111 | -- yet; they exist so the seams are stable. |
| 112 | CREATE TABLE runs ( |
| 113 | id TEXT PRIMARY KEY, |
| 114 | repo_id TEXT NOT NULL, |
| 115 | ref TEXT NOT NULL, |
| 116 | commit_sha TEXT NOT NULL, |
| 117 | status TEXT NOT NULL, |
| 118 | started_at BIGINT, |
| 119 | finished_at BIGINT, |
| 120 | created_at BIGINT NOT NULL |
| 121 | ); |
| 122 | |
| 123 | CREATE TABLE jobs ( |
| 124 | id TEXT PRIMARY KEY, |
| 125 | run_id TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE, |
| 126 | stage TEXT NOT NULL, |
| 127 | name TEXT NOT NULL, |
| 128 | status TEXT NOT NULL, |
| 129 | log_path TEXT, |
| 130 | started_at BIGINT, |
| 131 | finished_at BIGINT |
| 132 | ); |
| 133 | |
| 134 | -- Indexes. The UNIQUE(owner_id, path) constraint on repos already provides the |
| 135 | -- (owner_id, path) lookup index, so it is not repeated here. |
| 136 | CREATE INDEX idx_repos_visibility_pushed ON repos (is_private, pushed_at DESC); |
| 137 | CREATE INDEX idx_groups_owner_parent ON groups (owner_id, parent_id); |
| 138 | CREATE INDEX idx_keys_user_kind ON keys (user_id, kind); |
| 139 | CREATE INDEX idx_sessions_user ON sessions (user_id); |
| 140 | CREATE INDEX idx_sessions_expires ON sessions (expires_at); |
| 141 | CREATE INDEX idx_api_tokens_user ON api_tokens (user_id); |