001_initial.sql (10421B)
1 -- 001_initial.sql - base schema for conductor (mysql and tidb) 2 -- 3 -- Conventions, kept identical to the sqlite and postgres dialects: 4 -- timestamps are BIGINT epoch milliseconds 5 -- booleans are TINYINT 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 -- Column widths are chosen so that every indexed column stays within the 15 -- InnoDB 3072 byte key limit under utf8mb4. Tables are declared in 16 -- dependency order, since a referenced table must exist already. 17 18 CREATE TABLE users ( 19 id VARCHAR(64) NOT NULL PRIMARY KEY, 20 username VARCHAR(191) 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 VARCHAR(32) NOT NULL DEFAULT 'user', 26 disabled TINYINT 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 VARCHAR(255), 36 UNIQUE KEY idx_users_external (external_id) 37 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 38 39 CREATE TABLE projects ( 40 id VARCHAR(64) NOT NULL PRIMARY KEY, 41 name VARCHAR(255) NOT NULL, 42 repo_url TEXT NOT NULL, 43 default_branch VARCHAR(255) 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 VARCHAR(255) NOT NULL DEFAULT '.conductor.yml', 48 -- Shared secret for trigger HMAC verification, encrypted at rest. 49 trigger_secret TEXT, 50 enabled TINYINT NOT NULL DEFAULT 1, 51 job_counter BIGINT NOT NULL DEFAULT 0, 52 created_at BIGINT NOT NULL, 53 updated_at BIGINT 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 VARCHAR(64), 64 visibility VARCHAR(16) 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 INT, 80 artifact_keep_days INT, 81 log_keep_days INT, 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 VARCHAR(1024), 89 KEY idx_projects_owner (owner_id), 90 CONSTRAINT fk_projects_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE 91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 92 93 CREATE TABLE worker_tokens ( 94 id VARCHAR(64) NOT NULL PRIMARY KEY, 95 name VARCHAR(255) NOT NULL, 96 token_hash CHAR(64) NOT NULL UNIQUE, 97 enabled TINYINT NOT NULL DEFAULT 1, 98 created_at BIGINT NOT NULL, 99 last_seen_at BIGINT, 100 last_ip VARCHAR(64), 101 owner_id VARCHAR(64), 102 KEY idx_worker_tokens_owner (owner_id), 103 CONSTRAINT fk_worker_tokens_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE 104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 105 106 CREATE TABLE jobs ( 107 id VARCHAR(64) NOT NULL PRIMARY KEY, 108 project_id VARCHAR(64) NOT NULL, 109 number BIGINT NOT NULL, 110 ref VARCHAR(255), 111 base_sha VARCHAR(64), 112 head_sha VARCHAR(64) NOT NULL, 113 -- Named trigger_type rather than trigger, which is reserved in MySQL. 114 trigger_type VARCHAR(32) NOT NULL DEFAULT 'push', 115 actor VARCHAR(255), 116 title TEXT, 117 state VARCHAR(16) NOT NULL DEFAULT 'pending', 118 -- Resolved pipeline as scheduled, retained so a job stays explainable 119 -- after the repository moves on. 120 pipeline MEDIUMTEXT, 121 error TEXT, 122 created_at BIGINT NOT NULL, 123 started_at BIGINT, 124 finished_at BIGINT, 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 VARCHAR(16) NOT NULL DEFAULT 'private', 129 CONSTRAINT fk_jobs_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, 130 UNIQUE KEY idx_jobs_project_number (project_id, number), 131 KEY idx_jobs_created (created_at), 132 KEY idx_jobs_state (state), 133 KEY idx_jobs_visibility (visibility) 134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 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 VARCHAR(64) NOT NULL PRIMARY KEY, 142 job_id VARCHAR(64) NOT NULL, 143 -- Expanded name, for example 'package:pkg=musl'. 144 name VARCHAR(191) NOT NULL, 145 -- Template name before matrix and arch expansion. 146 base_name VARCHAR(191) NOT NULL, 147 arch VARCHAR(32), 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 MEDIUMTEXT NOT NULL, 155 state VARCHAR(16) NOT NULL DEFAULT 'queued', 156 allow_failure TINYINT NOT NULL DEFAULT 0, 157 attempt INT NOT NULL DEFAULT 0, 158 max_attempts INT NOT NULL DEFAULT 1, 159 timeout INT NOT NULL, 160 exit_code INT, 161 error TEXT, 162 log_key VARCHAR(255), 163 log_size BIGINT 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 BIGINT, 167 worker_token_id VARCHAR(64), 168 worker_name VARCHAR(255), 169 claimed_at BIGINT, 170 heartbeat_at BIGINT, 171 created_at BIGINT NOT NULL, 172 started_at BIGINT, 173 finished_at BIGINT, 174 CONSTRAINT fk_tasks_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, 175 UNIQUE KEY idx_tasks_job_name (job_id, name), 176 KEY idx_tasks_job (job_id), 177 KEY idx_tasks_state (state), 178 -- Supports the reaper scan for claimed tasks that stopped reporting. 179 KEY idx_tasks_heartbeat (state, heartbeat_at), 180 -- The sweep walks finished tasks oldest first, per project. 181 KEY idx_tasks_log_sweep (log_expired_at, finished_at) 182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 183 184 CREATE TABLE task_deps ( 185 task_id VARCHAR(64) NOT NULL, 186 depends_on_id VARCHAR(64) NOT NULL, 187 PRIMARY KEY (task_id, depends_on_id), 188 KEY idx_task_deps_reverse (depends_on_id), 189 CONSTRAINT fk_task_deps_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE, 190 CONSTRAINT fk_task_deps_target FOREIGN KEY (depends_on_id) REFERENCES tasks(id) ON DELETE CASCADE 191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 192 193 CREATE TABLE artifacts ( 194 id VARCHAR(64) NOT NULL PRIMARY KEY, 195 task_id VARCHAR(64) NOT NULL, 196 -- Denormalized so the retention sweep and the download path can filter 197 -- by job without joining through tasks. 198 job_id VARCHAR(64) NOT NULL, 199 path VARCHAR(1024) NOT NULL, 200 storage_key VARCHAR(1024) NOT NULL, 201 size BIGINT NOT NULL, 202 sha256 CHAR(64) NOT NULL, 203 created_at BIGINT NOT NULL, 204 expires_at BIGINT, 205 CONSTRAINT fk_artifacts_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE, 206 KEY idx_artifacts_task (task_id), 207 KEY idx_artifacts_job (job_id), 208 KEY idx_artifacts_expires (expires_at) 209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 210 211 CREATE TABLE project_variables ( 212 project_id VARCHAR(64) NOT NULL, 213 name VARCHAR(191) NOT NULL, 214 value TEXT NOT NULL, 215 masked TINYINT NOT NULL DEFAULT 1, 216 created_at BIGINT NOT NULL, 217 PRIMARY KEY (project_id, name), 218 CONSTRAINT fk_project_variables_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE 219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;