PRAGMA data_version: SQLite's Global Commit Counter as a Cross-Process Polling Primitive

PRAGMA data_version is a long-standing SQLite feature that exposes a per-database commit counter. Every commit and every WAL checkpoint increments the counter, and any process that opens the database file sees the same global value. A query returns the counter in roughly 3 microseconds when issued through a prepared statement — non-blocking, with no reads against user data and no contention against writers. This makes the pragma a viable foundation for change-detection polling at frequencies that would otherwise be cost-prohibitive. A 1 kHz polling loop consumes around 3.5 ms of CPU time per second — about 0.35% of one core — and can wake subscribers within a millisecond of a remote commit. The technique is what the Honker: SQLite Extension for NOTIFY/LISTEN, Task Queues, and Cron in One .db File extension uses to emulate Postgres-style NOTIFY/LISTEN across processes sharing a SQLite database. The pragma is monotonic, immune to clock skew, and correctly handles cases that defeat naive stat(2) polling on the WAL file: a WAL checkpoint truncates the WAL but still bumps the counter; a rolled-back transaction leaves no spurious increment. A crucial subtlety: the C-level SQLITE_FCNTL_DATA_VERSION file-control would appear to be the more efficient path, but it is per-connection cached — it only updates when the polling connection itself does a read, so it cannot see commits from other connections. PRAGMA data_version is the only mechanism that is global across all connections to the file. Implementers reaching for FCNTL as an optimization will silently miss external writes. Limitations: the pragma does not detect file replacement (atomic rename, Litestream restore, volume remount) because it polls through the open file descriptor, which follows the original inode. A second layer comparing `(dev, ino)` against startup values is needed to catch swaps. The pragma also does not propagate across hosts; cross-server change detection over Litestream is bounded by the replica sync interval (default 1 second), not the local polling rate.