001_initial.sql (9622B)
1 -- 001_initial.sql - base schema for conductor (postgres) 2 -- 3 -- Conventions, kept identical to the sqlite and mysql dialects: 4 -- timestamps are BIGINT epoch milliseconds 5 -- booleans are SMALLINT 0 or 1, deliberately not the native BOOLEAN type, 6 -- so that a row read back looks the same on every dialect 7 -- json documents are TEXT, not JSONB, since nothing queries inside them 8 -- secrets are TEXT produced by src/lib/secretbox.js 9 -- 10 -- Vocabulary. A trigger creates a job. Compiling the repository's pipeline 11 -- turns that job into one or more tasks, and a task is the standalone unit 12 -- a worker actually runs: one container, one script, one result. Workers 13 -- deal only in tasks and never learn which project or job a task came from. 14 -- 15 -- Tables are declared in dependency order, since a referenced table must 16 -- exist already. 17 18 CREATE TABLE users ( 19 id TEXT NOT NULL PRIMARY KEY, 20 username TEXT NOT NULL UNIQUE, 21 password_hash TEXT NOT NULL, 22 -- Two roles: 'admin' and 'user'. A user is not read only; they may own 23 -- projects and workers. Administration is the thing being gated, not 24 -- participation. 25 role TEXT NOT NULL DEFAULT 'user', 26 disabled SMALLINT NOT NULL DEFAULT 0, 27 created_at BIGINT NOT NULL, 28 last_login_at BIGINT, 29 -- With OIDC the provider owns identity, but the conductor still needs a 30 -- local row per person: projects and worker tokens reference users(id), 31 -- so a user who exists only inside a token cannot own anything. The 32 -- account is created on first sight of a valid token, keyed by issuer 33 -- and subject rather than by the display name, which a provider is free 34 -- to change. 35 external_id TEXT 36 ); 37 38 CREATE UNIQUE INDEX idx_users_external ON users (external_id); 39 40 CREATE TABLE projects ( 41 id TEXT NOT NULL PRIMARY KEY, 42 name TEXT NOT NULL, 43 repo_url TEXT NOT NULL, 44 default_branch TEXT NOT NULL DEFAULT 'main', 45 -- The preferred pipeline file. When it is one of the conventional names 46 -- the other extension is accepted as a fallback, so a repository may 47 -- spell it .conductor.yml or .conductor.yaml without being configured. 48 config_path TEXT NOT NULL DEFAULT '.conductor.yml', 49 -- Shared secret for trigger HMAC verification, encrypted at rest. 50 trigger_secret TEXT, 51 enabled SMALLINT NOT NULL DEFAULT 1, 52 job_counter BIGINT NOT NULL DEFAULT 0, 53 created_at BIGINT NOT NULL, 54 updated_at BIGINT NOT NULL, 55 -- A user may register their own projects and their own workers. A worker 56 -- that has an owner is only ever offered tasks belonging to that owner's 57 -- projects, so lending someone build capacity exposes nothing else. A 58 -- worker with no owner is shared and can run any project's tasks. 59 -- 60 -- Deleting a user deletes what they owned, cascading on to their jobs, 61 -- tasks and artifacts. Leaving the rows behind unowned would be worse 62 -- than losing them, since an unowned worker token is shared capacity: a 63 -- deleted account would quietly widen a worker's reach, not remove it. 64 owner_id TEXT REFERENCES users(id) ON DELETE CASCADE, 65 visibility TEXT NOT NULL DEFAULT 'private', 66 -- Retention. NULL is "no opinion", and the server default applies. Zero 67 -- means keep forever, which has to be distinguishable from unset or a 68 -- project could not opt out of a server default that deletes things. 69 -- 70 -- Artifacts have two rules and an artifact survives if either wants it: 71 -- the last artifact_keep_jobs jobs are kept however old they are, and 72 -- anything younger than artifact_keep_days is kept however many jobs 73 -- have followed it. The artifacts of the most recent successful job are 74 -- kept regardless, so a project that has gone quiet still has something 75 -- to download. A task that set artifacts.expire in the pipeline overrides 76 -- all of it with an exact deadline, in either direction, since a bulky 77 -- intermediate is worth dropping early even from a green build. 78 -- 79 -- Logs are simpler: they go by age alone. 80 artifact_keep_jobs INTEGER, 81 artifact_keep_days INTEGER, 82 log_keep_days INTEGER, 83 -- Where a task's tree is unpacked inside its container. Three answers, 84 -- most specific first: the workdir key in the repository's pipeline, 85 -- this column, and failing both the server default of /work. The 86 -- repository wins because the path belongs with the code: an image that 87 -- expects to build in /usr/src/app knows that, and whoever registered 88 -- the project should not have to. 89 workdir TEXT 90 ); 91 92 CREATE INDEX idx_projects_owner ON projects (owner_id); 93 94 CREATE TABLE worker_tokens ( 95 id TEXT NOT NULL PRIMARY KEY, 96 name TEXT NOT NULL, 97 token_hash CHAR(64) NOT NULL UNIQUE, 98 enabled SMALLINT NOT NULL DEFAULT 1, 99 created_at BIGINT NOT NULL, 100 last_seen_at BIGINT, 101 last_ip TEXT, 102 owner_id TEXT REFERENCES users(id) ON DELETE CASCADE 103 ); 104 105 CREATE INDEX idx_worker_tokens_owner ON worker_tokens (owner_id); 106 107 CREATE TABLE jobs ( 108 id TEXT NOT NULL PRIMARY KEY, 109 project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, 110 number BIGINT NOT NULL, 111 ref TEXT, 112 base_sha TEXT, 113 head_sha TEXT NOT NULL, 114 -- Named trigger_type rather than trigger, which is reserved in MySQL. 115 trigger_type TEXT NOT NULL DEFAULT 'push', 116 actor TEXT, 117 title TEXT, 118 state TEXT NOT NULL DEFAULT 'pending', 119 -- Resolved pipeline as scheduled, retained so a job stays explainable 120 -- after the repository moves on. 121 pipeline TEXT, 122 error TEXT, 123 created_at BIGINT NOT NULL, 124 started_at BIGINT, 125 finished_at BIGINT, 126 -- A job is private unless it says otherwise. The value comes from the 127 -- project, and the pipeline at the built commit may override it, so a 128 -- repository decides whether its own results are public. 129 visibility TEXT NOT NULL DEFAULT 'private' 130 ); 131 132 CREATE UNIQUE INDEX idx_jobs_project_number ON jobs (project_id, number); 133 CREATE INDEX idx_jobs_created ON jobs (created_at); 134 CREATE INDEX idx_jobs_state ON jobs (state); 135 CREATE INDEX idx_jobs_visibility ON jobs (visibility); 136 137 CREATE TABLE tasks ( 138 -- Opaque and time ordered. A task id deliberately carries no structure: 139 -- a worker holding one learns nothing about the job or project it 140 -- belongs to, and the id stays usable in a URL, a container name and a 141 -- storage key without escaping. 142 id TEXT NOT NULL PRIMARY KEY, 143 job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, 144 -- Expanded name, for example 'package:pkg=musl'. 145 name TEXT NOT NULL, 146 -- Template name before matrix and arch expansion. 147 base_name TEXT NOT NULL, 148 arch TEXT, 149 image TEXT NOT NULL, 150 -- json array of worker feature names this task needs. 151 requires TEXT NOT NULL, 152 -- json document: script, env, services, artifacts, matrix values, the 153 -- resolved needs and depth, and the workdir settled when the job was 154 -- created so a retry cannot land somewhere else. 155 spec TEXT NOT NULL, 156 state TEXT NOT NULL DEFAULT 'queued', 157 allow_failure SMALLINT NOT NULL DEFAULT 0, 158 attempt INTEGER NOT NULL DEFAULT 0, 159 max_attempts INTEGER NOT NULL DEFAULT 1, 160 timeout INTEGER NOT NULL, 161 exit_code INTEGER, 162 error TEXT, 163 log_key TEXT, 164 log_size BIGINT NOT NULL DEFAULT 0, 165 -- Distinguishes a log that was swept from one that never existed, so the 166 -- interface can say which without guessing from an empty log_key. 167 log_expired_at BIGINT, 168 worker_token_id TEXT, 169 worker_name TEXT, 170 claimed_at BIGINT, 171 heartbeat_at BIGINT, 172 created_at BIGINT NOT NULL, 173 started_at BIGINT, 174 finished_at BIGINT 175 ); 176 177 CREATE UNIQUE INDEX idx_tasks_job_name ON tasks (job_id, name); 178 CREATE INDEX idx_tasks_job ON tasks (job_id); 179 CREATE INDEX idx_tasks_state ON tasks (state); 180 -- Supports the reaper scan for claimed tasks that stopped reporting. 181 CREATE INDEX idx_tasks_heartbeat ON tasks (state, heartbeat_at); 182 -- The sweep walks finished tasks oldest first, per project. 183 CREATE INDEX idx_tasks_log_sweep ON tasks (log_expired_at, finished_at); 184 185 CREATE TABLE task_deps ( 186 task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, 187 depends_on_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, 188 PRIMARY KEY (task_id, depends_on_id) 189 ); 190 191 CREATE INDEX idx_task_deps_reverse ON task_deps (depends_on_id); 192 193 CREATE TABLE artifacts ( 194 id TEXT NOT NULL PRIMARY KEY, 195 task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, 196 -- Denormalized so the retention sweep and the download path can filter 197 -- by job without joining through tasks. 198 job_id TEXT NOT NULL, 199 path TEXT NOT NULL, 200 storage_key TEXT NOT NULL, 201 size BIGINT NOT NULL, 202 sha256 CHAR(64) NOT NULL, 203 created_at BIGINT NOT NULL, 204 expires_at BIGINT 205 ); 206 207 CREATE INDEX idx_artifacts_task ON artifacts (task_id); 208 CREATE INDEX idx_artifacts_job ON artifacts (job_id); 209 CREATE INDEX idx_artifacts_expires ON artifacts (expires_at); 210 211 CREATE TABLE project_variables ( 212 project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, 213 name TEXT NOT NULL, 214 value TEXT NOT NULL, 215 masked SMALLINT NOT NULL DEFAULT 1, 216 created_at BIGINT NOT NULL, 217 PRIMARY KEY (project_id, name) 218 );