Problem
sandboxes.parent_snapshot_id is a bare text column with no foreign-key constraint to snapshots.id. Referential integrity is enforced only by an index, not by the database. A forked sandbox row can point at a snapshot id that no longer exists (e.g. after out-of-band deletion), and nothing in the schema catches it.
Evidence
crates/sandboxwich-api/migrations/20260704000100_initial.sql:9 — parent_snapshot_id text with no references snapshots(id), unlike the sibling commands.sandbox_id column two lines earlier which does declare references sandboxes(id) on delete cascade.crates/sandboxwich-api/migrations/20260704000500_snapshots.sql:20-21 — only create index if not exists idx_sandboxes_parent_snapshot_id on sandboxes(parent_snapshot_id); is added later; no constraint accompanies it.
Suggested approach
Add the foreign key in both backends:
- SQLite: SQLite can't
ALTER TABLE ... ADD CONSTRAINT, so this needs the standard rebuild pattern (create a new sandboxes table with the FK, copy rows, drop old table, rename) in a new migration.
- PostgreSQL: a straightforward
ALTER TABLE sandboxes ADD CONSTRAINT fk_sandboxes_parent_snapshot_id FOREIGN KEY (parent_snapshot_id) REFERENCES snapshots(id) ... with an explicit decision on ON DELETE behavior (likely SET NULL, since snapshot expiry/cleanup should not cascade-delete the sandbox that forked from it).
Both SQLite and PostgreSQL contract-test suites should assert the constraint is enforced.
Status
A PR addressing this is in progress today (parallel effort) — not yet numbered.
Problem
sandboxes.parent_snapshot_idis a baretextcolumn with no foreign-key constraint tosnapshots.id. Referential integrity is enforced only by an index, not by the database. A forked sandbox row can point at a snapshot id that no longer exists (e.g. after out-of-band deletion), and nothing in the schema catches it.Evidence
crates/sandboxwich-api/migrations/20260704000100_initial.sql:9—parent_snapshot_id textwith noreferences snapshots(id), unlike the siblingcommands.sandbox_idcolumn two lines earlier which does declarereferences sandboxes(id) on delete cascade.crates/sandboxwich-api/migrations/20260704000500_snapshots.sql:20-21— onlycreate index if not exists idx_sandboxes_parent_snapshot_id on sandboxes(parent_snapshot_id);is added later; no constraint accompanies it.Suggested approach
Add the foreign key in both backends:
ALTER TABLE ... ADD CONSTRAINT, so this needs the standard rebuild pattern (create a newsandboxestable with the FK, copy rows, drop old table, rename) in a new migration.ALTER TABLE sandboxes ADD CONSTRAINT fk_sandboxes_parent_snapshot_id FOREIGN KEY (parent_snapshot_id) REFERENCES snapshots(id) ...with an explicit decision onON DELETEbehavior (likelySET NULL, since snapshot expiry/cleanup should not cascade-delete the sandbox that forked from it).Both SQLite and PostgreSQL contract-test suites should assert the constraint is enforced.
Status
A PR addressing this is in progress today (parallel effort) — not yet numbered.