OmniRoute/docs/architecture/sqlite-coupling-inventory.md

title, status, lastUpdated

title	status	lastUpdated
SQLite coupling inventory	measured-snapshot	2026-07-23

SQLite coupling inventory

• Tracking issue: #8075
• Snapshot revision: 9a3b605f3420ae3ab08bd93d6443034f03a1bcbc
• Scanned-corpus SHA-256: 72334620a7a18a42bcede1643fb2fdf95da6eae9ffa66a891ae14ed633ad43f6
• Purpose: Measure the current persistence cut lines before proposing repository interfaces
• Runtime impact: None; this document and its audit script do not change database behavior

How to reproduce

From the repository root:

node scripts/check/audit-sqlite-coupling.mjs
node scripts/check/audit-sqlite-coupling.mjs --json
node --test scripts/check/audit-sqlite-coupling.test.mjs

The script reads tracked files from Git, scans non-test source under src/, open-sse/, electron/, and bin/, and scans migration SQL under src/lib/db/migrations/. It excludes the top-level test tree, co-located test directories, test/spec source files, and paths outside those configured source roots (including documentation and scripts).

The script refuses to run if tracked files in those source roots differ from HEAD. It reports both the audit-tool revision and a SHA-256 over the ordered path/content corpus. The snapshot above was taken from the listed source revision; this PR changes only excluded documentation and script paths, so rerunning from the clean PR branch produces the same corpus digest.

This is a lexical inventory, not a TypeScript or SQL semantic analysis:

• counts are occurrences of defined patterns, not counts of distinct SQL statements;
• adapter-call and direct-singleton patterns mask comments and literal contents first;
• template-literal contents, including embedded expressions, are excluded from those code-syntax counts;
• the lightweight masker is not a JavaScript parser, so unusual regular-expression literal syntax can still require manual review;
• comments and string literals can contribute to dialect-signal counts, which intentionally search raw text for embedded SQL;
• a .prepare() match outside src/lib/db/ is a review lead, not proof that the call should move;
• calls hidden behind a differently named wrapper may not be counted;
• file counts are deduplicated, while occurrence counts are not.

The JSON output includes every matching path so reviewers can inspect or reclassify individual results rather than trusting totals alone.

Snapshot scope

At the recorded revision, the script scanned:

• 3,830 tracked non-test source files;
• 129 migration SQL files.

The source-file count is intentionally broad because the goal is to find persistence coupling that has escaped the nominal database directory, including CLI and proxy/runtime code.

Boundary signals

Signal	Files	Occurrences
Direct getDbInstance() call syntax outside comments/literals and src/lib/db/	45	150
localDb import consumers	211	—
SqliteAdapter type consumers outside comments/literals and src/lib/db/	3	—

The localDb barrel already gives many callers a domain-function seam, but src/lib/localDb.ts remains a re-export layer rather than a backend contract. The 45 direct singleton consumers are the clearest first review set because they bypass that logical seam and hold an adapter-shaped handle directly.

The three non-test source files outside src/lib/db/ that mention the SqliteAdapter type in code syntax are:

• src/app/api/db-backups/import/route.ts;
• src/lib/compliance/index.ts;
• src/lib/compliance/noLog.ts.

These are not equivalent migration tasks. Backup import is capability-specific; compliance persistence may be portable domain state. The future boundary should classify them rather than moving all three mechanically.

Adapter-shaped call syntax

Signal	Occurrences	Files	Outside src/lib/db/ occurrences	Outside files
.prepare()	1,219	163	252	52
.transaction()	62	40	12	10
.immediate()	3	3	0	0
.pragma()	39	11	6	4
.backup()	6	5	3	3
.checkpoint()	0	0	0	0
lastInsertRowid	15	7	1	1

This table shows why SqliteAdapter is a SQLite runtime compatibility layer rather than a portable backend abstraction. Its synchronous statement and transaction shape is widely used, and some of that shape is visible outside the nominal database layer.

The top direct getDbInstance() consumers outside src/lib/db/ at this revision are:

File	Occurrences
src/lib/proxySubscription/subscriptionService.ts	12
src/lib/semanticCache.ts	10
src/lib/usage/callLogs.ts	9
src/lib/cloudAgent/db.ts	8
src/lib/memory/store.ts	8
src/lib/memory/vectorStore.ts	8
src/lib/modelsDevSync.ts	8
src/lib/gamification/badges.ts	5
src/lib/memory/retrieval.ts	5
src/lib/pricingSync.ts	5
src/lib/skills/registry.ts	5
src/lib/usage/usageHistory.ts	5

The list spans control-plane configuration, usage/audit data, cache, memory/vector search, skills, gamification, and CLI/provider support. A single generic SQL adapter would preserve this spread; domain repositories provide a way to reduce it slice by slice.

SQLite dialect and lifecycle signals

Signal	Occurrences	Files
PRAGMA text	97	41
sqlite_master	14	11
BEGIN IMMEDIATE	2	2
INSERT OR REPLACE	83	45
AUTOINCREMENT	34	24
datetime('now')	171	68
VACUUM	39	10
wal_checkpoint	13	7
fts5	43	8
vec0	7	1
last_insert_rowid()	1	1

These values are text signals and include comments where present. They are useful for locating portability work, not for estimating implementation effort by multiplication.

Verified high-coupling areas include:

• src/lib/db/core.ts: singleton lifecycle, SQLite file paths, WAL checkpoint, recovery, schema, compaction, and backup creation;
• src/lib/db/migrationRunner.ts: numbered SQL migration execution, sqlite_master, PRAGMA table_info, transaction behavior, and optional FTS5 handling;
• src/lib/db/optimizationSettings.ts: page/cache settings, auto-vacuum, WAL transitions, and VACUUM;
• src/lib/db/backup.ts: database backup and restore lifecycle;
• src/lib/db/schemaColumns.ts: SQLite schema introspection and compatibility columns;
• src/lib/memory/vectorStore.ts and src/lib/memory/retrieval.ts: vec0 and FTS5 behavior;
• src/lib/db/adapters/: compatibility implementations for the supported SQLite runtimes.

These areas should not be forced through a lowest-common-denominator repository interface. They need explicit SQLite capabilities or separate backend implementations.

Migration coupling

The snapshot contains 129 tracked migration SQL files. src/lib/db/migrationRunner.ts does more than execute ordered files: it owns migration discovery, version history, duplicate-version safety, schema probes, FTS5 capability checks, pre-migration safety, and SQLite transaction execution.

Consequently:

• another SQL dialect cannot safely reuse the migration files unchanged;
• external backends need their own migration implementation and schema history;
• logical migration milestones may be shared, but physical SQL and capability probes remain backend-specific;
• multi-replica operation requires migration ownership or locking before an external backend is considered ready.

Recommended cut lines

1. Keep SQLite runtime compatibility intact

Do not replace SqliteAdapter or the driver cascade in the first repository PR. Keep file recovery, WAL, backup, optimization, FTS5, and vector behavior behind the current SQLite implementation.

2. Start with direct singleton consumers

Use the 45-file direct-consumer list as the initial review queue. Classify each file as:

• portable domain state;
• backend-specific maintenance or search;
• process-local or rebuildable state;
• legacy access that should call an existing domain module.

Classification must precede interface design. A path appearing in the inventory is not, by itself, a mandate to create a repository.

3. Prove repositories with SQLite first

For one bounded domain:

1. define behavior-oriented repository operations;
2. adapt current SQLite queries behind that repository;
3. run behavior and transaction conformance tests against SQLite;
4. migrate callers without changing the default runtime;
5. only then implement the same repository for an external backend.

4. Separate portable control-plane state from capability-specific data

Provider connections, API keys, combos, and routing configuration are candidates for the first portable slice, subject to maintainer approval and a table-ownership review. Memory vector search, SQLite file backup/recovery, and database optimization are poor first slices because their behavior is deliberately SQLite-specific.

5. Treat usage, quota, affinity, and audit as a later coordination slice

These domains have concurrency and volume semantics beyond CRUD. Their repository contracts should be designed together with multi-replica transaction, lease, retention, and failure-mode tests rather than copied mechanically from current SQL.

What this inventory does not decide

This inventory does not:

• approve PostgreSQL or MySQL support;
• define repository TypeScript interfaces;
• choose the first table or domain to migrate;
• claim every lexical match is a defect;
• claim the current module boundaries are ineffective;
• change SQLite, migrations, backup, search, or runtime behavior.

Its purpose is to make the next design discussion evidence-based and reproducible.