Production SQLite adapter sets no pragmas: FK enforcement rides on driver compile defaults, WAL missing

[edrpls]

Summary

The production SQLite runtime adapter opens its connection with no pragmas at all — no journal_mode = WAL and no foreign_keys = ON. The pragma-setting path exists, but only in createDatabase() (packages/core/src/database/connection.ts:50-53), which production sites configured via sqlite({ url }) never go through: their connection is created by createDialect() in packages/core/src/db/sqlite.ts:16-24, which is a bare new BetterSqlite3(filePath).

Why it matters

• FK enforcement is inherited from driver defaults. better-sqlite3 currently compiles with SQLITE_DFLT_FOREIGN_KEYS=1, so FKs happen to be enforced today — but that's an implicit dependency on a third-party compile flag. A driver swap (or a distro build with different flags) would silently turn off every REFERENCES ... ON DELETE in the schema. An explicit PRAGMA foreign_keys = ON at connection setup is cheap insurance.
• No WAL on the production path. connection.ts's own comment says WAL prevents FTS5 shadow-table corruption on process kill during content writes — yet the production adapter runs in the default rollback-journal mode. The protection exists exactly where it matters least (tests/CLI) and is missing where it matters most.
• db/libsql.ts has the same gap for the file-backed local case.

Suggested shape

Mirror connection.ts in createDialect():

const database = new BetterSqlite3(filePath);
database.pragma("journal_mode = WAL");
database.pragma("foreign_keys = ON");

Filed as an issue rather than a PR because enabling FK enforcement on existing databases is a behavior change worth an explicit maintainer decision: a long-lived production DB that accumulated orphaned rows under (hypothetically) unenforced FKs could start failing deletes after the upgrade, and switching journal modes changes the on-disk file set (-wal/-shm), which some deployment setups (backup scripts, litestream configs) may care about.

Found during a measured database audit of a production deployment (the Macabro festival site, emdash 0.31.1).

This issue was drafted with AI assistance (Claude Fable 5, Claude Code).