Study AW · Whole-system baselines
Does Turso enforce foreign keys? The two database kinds are opposites, and one mechanism explains both apps
Whole-system baselines
Study Overview
Two Apps, Opposite Answers
Stem Shovel measured in September that Turso runs with PRAGMA foreign_keys = 0 and the schema's onDelete rules never fire, and shipped 441 lines of hand-written cascades plus an orphan sweep. Replicator measured in October that cascade, set null and violation rejection all pass with no pragma set anywhere, and 159 FK actions in production depend on that. At least one had to be wrong, or they measured different things. A verified inconsistency sharpened the design before registration: Replicator's own probe script runs PRAGMA foreign_keys = ON before testing the cascade, while its verdict note says the cascade fired with no pragma set. So pragma state entered the matrix as a measured axis, never an assumption. Study AW, registered at b718e0b from the two-app handoff, is the series' first database-behavior study: no LLM anywhere in it, $0 of model spend.
A Self-Proving Instrument on Throwaway Databases
The instrument is one connection-shaped probe that reads PRAGMA foreign_keys first, then measures three behaviors on its own prefixed throwaway tables: ON DELETE cascade, ON DELETE set null, and violating-insert rejection. Before it touched anything remote, its tests proved it detects both states on a local file database, under both client versions the apps pin (0.17.4 and 0.18.0). The matrix crossed database kind, client version, four connection usages and three pragma arms at 3 repetitions, plus a drizzle arm wired exactly as both apps wire it; 48 first-connection probes on 12 fresh databases separated cold-start claims from steady state. Two harness fixes landed before any data: regional hostnames, and the discovery that the platform API cannot create engine-kind databases at all, only the CLI's --tursodb flag can. Every database was created by the runner, named aw-fk-*, and destroyed at the end; zero errors across the run.
All Three Gates Pass
AW-G1, coherence: in all 148 cells the three behaviors move together, all enforced or none. AW-G2, prediction: in every steady-state cell the PRAGMA foreign_keys reading predicted the observed behavior exactly. AW-G3, determinism: no cell flapped across repetitions. The pragma never lies; the apps' assumptions about it did.
The Kinds Are Opposites
On the classic libsql kind, foreign_keys reads 1 and enforces on every connection, every usage, both clients, and PRAGMA foreign_keys = OFF does not stick: it reads back 1 and still enforces, even inside an interactive transaction. Enforcement is on and effectively immutable. On the engine (TursoDB/MVCC) kind, foreign_keys reads 0 and does not enforce: no cascade, no set null, violating inserts accepted. All 24 first-connection probes on 6 fresh engine databases read 0 and meant it, on connection one and on every later connection, so the claimed cold-start artifact does not exist. But PRAGMA foreign_keys = ON sticks at the database level: set through one connection, it read 1 and enforced through every subsequent fresh connection, both clients, including the fresh-client-per-statement usage where the pragma and the probe never share a connection. Durability across engine restarts and long idle is the one unmeasured question.
One Mechanism Explains Both Apps
Replicator's verdict probe ran PRAGMA foreign_keys = ON before testing the cascade, and on an engine database that ON persisted: the six subsequent fresh connections reading 1 were the probe's own footprint, and the verdict note misdescribes evidence produced with the pragma set. Stem Shovel's September observation, pragma 0 and onDelete rules never firing, is exactly the engine kind's default behavior; its hand-written cascades are load-bearing on an engine database and redundant on a classic one. The exposure is Replicator's: it has run engine databases in production since 2026-10-24 with 159 FK actions and no pragma anywhere in the app, so per this study those actions are unenforced unless some probe already flipped each database's persistent setting. The hand-off carries the one-line check per environment and the remediation options, led by setting the pragma at client construction, which is cheap and correct on both kinds.
Predictions Scored Honestly: 1 of 3
P1 is refuted: enforcement is not on by default on the engine kind, so the bench predicted the wrong winner and says so. P2 is refuted: the engine's first connection reads 0 AND does not enforce, so the cold-start reporting artifact the prediction banked on does not exist. P3 is half right: a pragma set in a prior separate execute does carry over, the stateless-HTTP worry was wrong, and on the engine it sticks database-wide, which is stronger than predicted. P4 stands as the disclosed limit: September's database kind was never recorded, so the verdict is about the stack as it stands, never about what Stem Shovel's database did then. A side finding rides along for the release notes: the platform API has no engine parameter, so which kind an app gets is decided at creation by tooling, and the kind is a fact worth recording.