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. And on the 2026-11-02 run, PRAGMA foreign_keys = ON stuck 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. That persistence is a dated observation, not a durable property: it did not survive the next engine build, and the Correction at the end of this entry withdraws it.
One Mechanism Explains Both Apps
Replicator's verdict probe ran PRAGMA foreign_keys = ON before testing the cascade, and on the engine build then current 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 carried the one-line check per environment and the remediation options, led at publication by setting the pragma at client construction. The Correction below withdraws that remediation: on current engine builds the line does nothing.
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 did carry over on the build measured that day, the stateless-HTTP worry was wrong, and the database-wide stickiness, stronger than predicted, is the part the Correction withdraws. 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.
Correction (2026-11-03): The Persistence Claim Did Not Survive the Next Engine Build
One day after publication, the slx-customer-portal agent, applying this study's hand-off, could not reproduce the persistence claim on its own engine databases: after PRAGMA foreign_keys = ON via plain execute, the same client read 0 and a fresh client read 0, in both the libsql:// and https:// schemes. The bench's same-day reproduction on a fresh engine database, same sandbox group and same CLI creation path as the study's run, confirms the non-reproduction: before 0, same-client-after-set 0, fresh client 0, and no cascade through fresh clients, where one day earlier the same pins measured pragma 1 and full enforcement. The likely mechanism is the cloud engine's rolling build: tursodb PR #11095, merged 2026-10-31, makes PRAGMA foreign_keys ignored while a transaction or savepoint is open, as in SQLite, and a hrana batch is a BEGIN plus COMMIT, so even the measured batch path has an expiry. What survives: the engine kind does not enforce by default (reproduced on 2026-11-03), the classic kind always enforces, the pragma reading predicts behavior on the stream that asks, and all three gates as registered. What is withdrawn: set once per database and it enforces from then on, and with it the pragma-at-client-construction remediation, which does nothing on current engine builds. The durable guidance, converged with the portal agent's response brief: on engine-kind databases treat foreign-key enforcement as unavailable, handle every delete's children explicitly and atomically, validate parent ids in code, and keep the one-line pragma probe in ops checks as a change detector only, so a future database-level setting gets noticed. Behavior measured against a managed service is a snapshot of a rolling build, not a contract: date every claim, and keep the probe that found it.
Resolution (2026-11-04): One Rule, Three Codebases
The coordinated response the correction called for shipped across all three application repos under one authority document with five amendments. The customer portal shipped the reference implementation in its 0.37.1 release: a foreign-key graph printed from Drizzle metadata (never hand-written, after hand lists lost three to nothing against metadata, including this bench's own brief), an orphan scan that cross-checks that metadata against the live catalog, a dry-run-first repair tool that found and fixed 28 authorship orphans from old manual purges, and a release gate on the scan. Replicator retracted its October enforcement claim in its own changelog, shipped a CI guard that classifies every parent table as handled, never deleted, or refused (verified against code, not declared), explicit deepest-first handlers honoring each edge's declared action, a daily five-key probe across its three environments, and refusals on its organization and user deletes (47 and 76 child tables) until their teardowns ship, by Kevin's decision. Stem Shovel's cascade.ts, the 441 lines from September, stands as the pattern the rule prescribes, with kind and probe commands added to its ops. The bench's weekly fk-watch guards the platform signals (driver support, client-library majors, the docs' default sentence, a live sandbox probe), and Study AX is queued to re-measure the pragma surface once the rolling build settles. The floor rule fired live on 2026-11-03: a clean demo database correctly failed the portal's census because ten pending-migration edges went unexamined. The full story is told in the follow-up article, Three Agents, One Rule.