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