max / makenotwork

9.8 KB · 217 lines History Blame Raw

1	-- no-transaction
2	--
3	-- Sando ships more than one product. Design: wiki [[sando-bento-boundary]].
4	--
5	-- Every table here was implicitly about the MNW server. `versions` keyed a
6	-- version string with no room for two products releasing 0.4.1 in the same
7	-- week; `tiers` keyed a tier name, so pom's `host` and MNW's `host` would be
8	-- one row; `tier_state` recorded what is deployed with no way to say deployed
9	-- of what. None of that was wrong while there was one product. All of it is
10	-- wrong the moment there are two, and each collision would read as ordinary
11	-- state rather than as an error.
12	--
13	-- So `app` joins the key of everything that was keyed, and rides along on
14	-- everything that was merely recorded. Every one of them defaults to 'mnw',
15	-- which says the same thing the config says: a statement that does not name a
16	-- product is about the one product Sando had. That default is what lets the
17	-- daemon's queries be threaded one file at a time instead of in a single
18	-- unreviewable commit.
19	--
20	-- Existing rows backfill to 'mnw'. That is not a guess: this daemon has only
21	-- ever deployed the MNW server, and a config with no [app.*] tables loads as
22	-- that same id (see config.rs), so the rows and the config agree without
23	-- anybody editing either.
24	--
25	-- Four tables need their PRIMARY KEY widened, which SQLite cannot do in place,
26	-- so they are rebuilt. That is the 12-step rebuild from the SQLite docs, and it
27	-- wants `PRAGMA foreign_keys = OFF` around the whole thing.
28	--
29	-- `defer_foreign_keys` is NOT a substitute, which is what this migration was
30	-- written on and what broke it. Deferral does not skip enforcement, it moves
31	-- the check to COMMIT and keeps a counter of outstanding violations in the
32	-- meantime. With foreign keys on, `DROP TABLE` performs an implicit DELETE of
33	-- every parent row, and each child row still pointing at one increments that
34	-- counter. Nothing ever decrements it: the counter falls only when DML resolves
35	-- a violation, and re-creating the parent by DDL is not DML. So COMMIT fails
36	-- with error 787 over a database whose final state is consistent —
37	-- `foreign_key_check` afterwards reports nothing.
38	--
39	-- The bug could only ever fire in production. It needs rows in the child
40	-- tables, and every test starts from an empty schema. The live daemon had 58
41	-- deploys and 418 gate_runs when this was found, on 2026-08-09, by the restart
42	-- that was supposed to apply it.
43	--
44	-- `PRAGMA foreign_keys` is a no-op inside a transaction, so this migration
45	-- carries `-- no-transaction` and opens its own instead. The atomicity is the
46	-- same; what changes is that the pragma lands before the transaction begins.
47	--
48	-- The order below is unchanged and still load-bearing. A composite
49	-- `REFERENCES tiers(app, name)` is a schema error — "foreign key mismatch" —
50	-- until `tiers` actually has a unique index on those two columns, raised when
51	-- the statement runs rather than at COMMIT, and no pragma turns that off. So
52	-- `tiers` is rebuilt and renamed first, alone, and only then do the tables that
53	-- reference it get created.
54
55	PRAGMA foreign_keys = OFF;
56	BEGIN;
57
58	-- Tiers are per product. MNW's `a`/`b` and pom's tiers are different pipelines
59	-- that happen to be spelled alike.
60	CREATE TABLE tiers_new (
61	app TEXT NOT NULL DEFAULT 'mnw',
62	name TEXT NOT NULL,
63	ord INTEGER NOT NULL,
64	provisioned INTEGER NOT NULL DEFAULT 0,
65	canary TEXT NOT NULL DEFAULT 'sequential',
66	PRIMARY KEY (app, name)
67	);
68	INSERT INTO tiers_new (app, name, ord, provisioned, canary)
69	SELECT 'mnw', name, ord, provisioned, canary FROM tiers;
70	DROP TABLE tiers;
71	ALTER TABLE tiers_new RENAME TO tiers;
72
73	-- A node name is only unique within its product: two products may each deploy
74	-- to a node they both call `prod-1`, and they are not the same machine role.
75	CREATE TABLE nodes_new (
76	app TEXT NOT NULL DEFAULT 'mnw',
77	name TEXT NOT NULL,
78	tier TEXT NOT NULL,
79	ssh_target TEXT NOT NULL,
80	release_root TEXT NOT NULL,
81	PRIMARY KEY (app, name),
82	FOREIGN KEY (app, tier) REFERENCES tiers(app, name)
83	);
84	INSERT INTO nodes_new (app, name, tier, ssh_target, release_root)
85	SELECT 'mnw', name, tier, ssh_target, release_root FROM nodes;
86	DROP TABLE nodes;
87	ALTER TABLE nodes_new RENAME TO nodes;
88	CREATE INDEX nodes_by_tier ON nodes(app, tier);
89
90	-- Two products releasing the same version string are two artifacts.
91	CREATE TABLE versions_new (
92	app TEXT NOT NULL DEFAULT 'mnw',
93	version TEXT NOT NULL,
94	git_sha TEXT NOT NULL,
95	built_at TEXT NOT NULL,
96	artifact_path TEXT NOT NULL,
97	PRIMARY KEY (app, version)
98	);
99	INSERT INTO versions_new (app, version, git_sha, built_at, artifact_path)
100	SELECT 'mnw', version, git_sha, built_at, artifact_path FROM versions;
101	DROP TABLE versions;
102	ALTER TABLE versions_new RENAME TO versions;
103
104	-- What is deployed, per product per tier. Columns added since 001 are carried
105	-- across explicitly rather than by SELECT *, so a future column cannot be
106	-- silently dropped by this migration.
107	CREATE TABLE tier_state_new (
108	app TEXT NOT NULL DEFAULT 'mnw',
109	tier TEXT NOT NULL,
110	current_version TEXT,
111	previous_version TEXT,
112	burn_in_started_at TEXT,
113	partial_reason TEXT,
114	current_build_id INTEGER REFERENCES build_runs(id),
115	previous_build_id INTEGER REFERENCES build_runs(id),
116	advanced_at TEXT,
117	PRIMARY KEY (app, tier),
118	FOREIGN KEY (app, tier) REFERENCES tiers(app, name)
119	);
120	INSERT INTO tier_state_new (
121	app, tier, current_version, previous_version, burn_in_started_at,
122	partial_reason, current_build_id, previous_build_id, advanced_at
123	)
124	SELECT 'mnw', tier, current_version, previous_version, burn_in_started_at,
125	partial_reason, current_build_id, previous_build_id, advanced_at
126	FROM tier_state;
127	DROP TABLE tier_state;
128	ALTER TABLE tier_state_new RENAME TO tier_state;
129
130	-- The append-only tables keep their integer primary keys; `app` is a column,
131	-- not part of an identity they already had. `backups` and `build_runs`
132	-- reference nothing that moved, so a plain ADD COLUMN is enough.
133	ALTER TABLE backups ADD COLUMN app TEXT NOT NULL DEFAULT 'mnw';
134	ALTER TABLE build_runs ADD COLUMN app TEXT NOT NULL DEFAULT 'mnw';
135
136	-- `deploys` and `gate_runs` cannot be, even though their keys are unchanged:
137	-- each carries `REFERENCES versions(version)` / `tiers(name)` / `nodes(name)`
138	-- clauses written when those were single-column keys. Those clauses are now
139	-- schema errors ("foreign key mismatch") on the next INSERT, and SQLite has no
140	-- way to alter a foreign key in place. So both are rebuilt to point at the
141	-- composite keys.
142	--
143	-- A composite foreign key with any NULL column is not checked, which is exactly
144	-- right for `deploys.node`: a tier-level deploy row names no node.
145	CREATE TABLE deploys_new (
146	id INTEGER PRIMARY KEY AUTOINCREMENT,
147	app TEXT NOT NULL DEFAULT 'mnw',
148	version TEXT NOT NULL,
149	tier TEXT NOT NULL,
150	node TEXT,
151	started_at TEXT NOT NULL,
152	finished_at TEXT,
153	outcome TEXT NOT NULL DEFAULT 'in_progress',
154	hotfix INTEGER NOT NULL DEFAULT 0,
155	reset_burn_in INTEGER NOT NULL DEFAULT 0,
156	outcome_json TEXT,
157	build_id INTEGER REFERENCES build_runs(id),
158	FOREIGN KEY (app, version) REFERENCES versions(app, version),
159	FOREIGN KEY (app, tier) REFERENCES tiers(app, name),
160	FOREIGN KEY (app, node) REFERENCES nodes(app, name)
161	);
162	INSERT INTO deploys_new (
163	id, app, version, tier, node, started_at, finished_at, outcome, hotfix,
164	reset_burn_in, outcome_json, build_id
165	)
166	SELECT id, 'mnw', version, tier, node, started_at, finished_at, outcome,
167	hotfix, reset_burn_in, outcome_json, build_id
168	FROM deploys;
169	DROP TABLE deploys;
170	ALTER TABLE deploys_new RENAME TO deploys;
171
172	CREATE TABLE gate_runs_new (
173	id INTEGER PRIMARY KEY AUTOINCREMENT,
174	app TEXT NOT NULL DEFAULT 'mnw',
175	version TEXT NOT NULL,
176	tier TEXT NOT NULL,
177	gate_kind TEXT NOT NULL,
178	started_at TEXT NOT NULL,
179	finished_at TEXT,
180	build_id INTEGER REFERENCES build_runs(id),
181	status TEXT,
182	outcome_json TEXT,
183	log_ref TEXT,
184	FOREIGN KEY (app, version) REFERENCES versions(app, version),
185	FOREIGN KEY (app, tier) REFERENCES tiers(app, name)
186	);
187	INSERT INTO gate_runs_new (
188	id, app, version, tier, gate_kind, started_at, finished_at, build_id,
189	status, outcome_json, log_ref
190	)
191	SELECT id, 'mnw', version, tier, gate_kind, started_at, finished_at,
192	build_id, status, outcome_json, log_ref
193	FROM gate_runs;
194	DROP TABLE gate_runs;
195	ALTER TABLE gate_runs_new RENAME TO gate_runs;
196
197	-- Every lookup that used to be by (tier, version) or by name is now by product
198	-- first. Left as separate indexes from the 001 ones, which are dropped: an
199	-- index that omits the leading column of the predicate cannot serve it.
200	-- The rebuilt tables took their indexes to the grave with them; the two that
201	-- survive are dropped so every one of these is recreated app-first.
202	DROP INDEX IF EXISTS backups_name_fetched;
203	DROP INDEX IF EXISTS build_runs_by_sha;
204	CREATE INDEX deploys_by_build ON deploys(build_id);
205	CREATE INDEX gate_runs_by_build ON gate_runs(build_id, gate_kind);
206	CREATE INDEX deploys_by_tier_version ON deploys(app, tier, version);
207	CREATE INDEX gate_runs_lookup ON gate_runs(app, tier, version, gate_kind);
208	CREATE INDEX backups_name_fetched ON backups(app, name, fetched_at);
209	CREATE INDEX build_runs_by_sha ON build_runs(app, sha);
210
211	COMMIT;
212
213	-- Back on for the connection that ran this. The pragma is per connection rather
214	-- than stored in the file, so leaving it off would only affect this one, but
215	-- this one goes on to serve the daemon.
216	PRAGMA foreign_keys = ON;
217