P0 — every Orb upgrade past 0208 crash-loops on boot
migrations/0209_service_status_samples.sql:21 declares:
id INTEGER PRIMARY KEY AUTOINCREMENT,
AUTOINCREMENT is SQLite-only. On a self-host Orb (Postgres) runSelfHostMigrations aborts:
error: syntax error at or near "AUTOINCREMENT"
code: '42601', position: '1514'
at async runSelfHostMigrations (server.mjs:210857)
at async withPgMigrationLock
The process exits, so the container crash-loops. Any Orb pulling an image built after 0209 landed cannot boot. Reproduced on edge-nl-01 deploying orb-v3.7.0-beta.6; rolled back to orb-v3.7.0-beta.5, healthy.
No data damage — migrations apply inside a transaction, so service_status_samples was never created and _selfhost_migrations still ends at 0208. This fails closed, which is the one good thing about it.
Why the dialect layer did not catch it
Migrations are authored in SQLite dialect and translated by translateDdl (src/selfhost/pg-dialect.ts), applied at pg-adapter.ts:138/150. It covers SQLite date/json functions and INSERT OR IGNORE, and its doc comment states the assumption that makes this a gap:
Column types (TEXT/INTEGER/REAL) are PG-native
True for types — but AUTOINCREMENT is a constraint, not a type, and nothing translates it. 0209 is the only migration in the repo that uses it; every other table uses plain INTEGER PRIMARY KEY, which parses on both dialects, which is why the gap stayed latent.
Why simply stripping the keyword is wrong
Plain INTEGER PRIMARY KEY in Postgres is not auto-assigning. The writer inserts no id:
INSERT INTO service_status_samples (component, status, sampled_at) VALUES (?, ?, ?)
so a strip-only fix trades a boot-time syntax error for a runtime NOT NULL violation on every sample write — worse, because it fails open and silently (the writer is best-effort and swallows errors, so the status board would just quietly record nothing).
Fix
Teach translateDdl to render the construct as a Postgres identity column, so the SQLite-dialect source keeps working on both backends:
INTEGER PRIMARY KEY AUTOINCREMENT -> BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
BY DEFAULT (not ALWAYS) preserves SQLite's behaviour of allowing an explicit id, and BIGINT matches SQLite's 64-bit rowid.
The migration file is deliberately not edited — shipped migrations are immutable (db:migrations:immutable:check), and 0209 may already be applied on D1 where the SQLite syntax is valid; changing its bytes would trip the content hash there.
The real gap
Nothing runs the migration set against a real Postgres in CI. The dialect layer is exercised by unit tests over SQL strings, so a construct nobody thought to translate is invisible until an Orb boots. A migrations-apply smoke test against Postgres would have caught this before it shipped, and is the durable fix — filed separately.
P0 — every Orb upgrade past 0208 crash-loops on boot
migrations/0209_service_status_samples.sql:21declares:AUTOINCREMENTis SQLite-only. On a self-host Orb (Postgres)runSelfHostMigrationsaborts:The process exits, so the container crash-loops. Any Orb pulling an image built after 0209 landed cannot boot. Reproduced on edge-nl-01 deploying
orb-v3.7.0-beta.6; rolled back toorb-v3.7.0-beta.5, healthy.No data damage — migrations apply inside a transaction, so
service_status_sampleswas never created and_selfhost_migrationsstill ends at0208. This fails closed, which is the one good thing about it.Why the dialect layer did not catch it
Migrations are authored in SQLite dialect and translated by
translateDdl(src/selfhost/pg-dialect.ts), applied atpg-adapter.ts:138/150. It covers SQLite date/json functions andINSERT OR IGNORE, and its doc comment states the assumption that makes this a gap:True for types — but
AUTOINCREMENTis a constraint, not a type, and nothing translates it.0209is the only migration in the repo that uses it; every other table uses plainINTEGER PRIMARY KEY, which parses on both dialects, which is why the gap stayed latent.Why simply stripping the keyword is wrong
Plain
INTEGER PRIMARY KEYin Postgres is not auto-assigning. The writer inserts no id:so a strip-only fix trades a boot-time syntax error for a runtime
NOT NULLviolation on every sample write — worse, because it fails open and silently (the writer is best-effort and swallows errors, so the status board would just quietly record nothing).Fix
Teach
translateDdlto render the construct as a Postgres identity column, so the SQLite-dialect source keeps working on both backends:BY DEFAULT(notALWAYS) preserves SQLite's behaviour of allowing an explicit id, andBIGINTmatches SQLite's 64-bit rowid.The migration file is deliberately not edited — shipped migrations are immutable (
db:migrations:immutable:check), and0209may already be applied on D1 where the SQLite syntax is valid; changing its bytes would trip the content hash there.The real gap
Nothing runs the migration set against a real Postgres in CI. The dialect layer is exercised by unit tests over SQL strings, so a construct nobody thought to translate is invisible until an Orb boots. A migrations-apply smoke test against Postgres would have caught this before it shipped, and is the durable fix — filed separately.