A tester opened NetStacks on a Mac that had been running the app since spring and got this in the sidecar log:
Failed to initialize database: Migration("error returned from database: (code: 1) no such column: execution_id")
The same build started cleanly on a Linux box, on a fresh macOS install, and in every test run. Only a database that had been created by an older version failed. That is the shape of a migration ordering bug, and it is worth walking through because the fix was not "add a migration".
How the agent builds its database
The NetStacks agent is a Rust process that owns a SQLite file. On startup it runs schema.sql, which is a plain list of CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements, and then runs a list of migrations that alter existing tables: add a column here, rename one there. New installs get the current schema in one shot. Old installs get the old tables and then the migrations bring them forward.
A recent change added an execution_id column to the snapshots table so that a config snapshot can be tied to the MOP execution that produced it. The schema file got the column in the table definition and an index on it, and a migration added the column to existing databases.
Why it only failed on the tester's machine
On a fresh install, CREATE TABLE creates the table with the column, then CREATE INDEX on that column succeeds, then the migration sees the column already exists and skips. Fine.
On an old install, CREATE TABLE IF NOT EXISTS does nothing because the table exists without the new column. Then CREATE INDEX ... (execution_id) runs, and there is no such column yet, because the migration that adds it has not run. SQLite refuses. The agent aborts. The app shows a spinner forever.
The two mechanisms were correct on their own and wrong in that order. Index statements in the schema file assume the table is already at the current shape, which is only true when the file created the table.
The first fix, and why we did not keep it
The obvious patch is to move the CREATE INDEX into the migration that adds the column. That works, and it is what shipped first. But it means every future column-plus-index change has to remember the rule, and the schema file stops being the full description of the database. Someone would forget, and we would be back here.
The fix that stayed
Startup now splits the schema file into two passes. Everything that is not a CREATE INDEX runs first, then the migrations run, then every CREATE INDEX runs. Indexes stay declared next to their tables in the one file, and they always execute against a table that is at the current shape.
The splitter is deliberately dumb: it looks for lines that start with CREATE INDEX or CREATE UNIQUE INDEX, and a test asserts that every index statement in the file was held back. If someone writes an index in a way the splitter does not recognise, the test fails before the app does.
What we would still change
The migration that adds the column now checks whether the column exists before trying to add it. It should have done that from the start. We also want the test suite to run against a snapshot of a genuinely old database file, not just a fresh one with the new schema; that would have caught this before a tester did.
If you keep a schema file and a migrations list in the same project, check which one your indexes believe in.