AGENTS.md

1# src/lib/db/ — SQLite Persistence Layer
2 
3**Purpose**: Domain-driven SQLite persistence. Each module owns a specific table set. Schema migrations are versioned and idempotent. No raw SQL in routes — all ops go through `src/lib/db/` modules.
4 
5Live count: `ls src/lib/db/*.ts | wc -l` (currently 95). Migrations: `ls src/lib/db/migrations/*.sql | wc -l` (currently 110).
6 
7---
8 
9## Core Infrastructure
10 
11- **`core.ts`** — `getDbInstance()` returns singleton `better-sqlite3` with WAL journaling. Exports `rowToCamel()` (snake_case → camelCase), `encryptConnectionFields()` for provider credentials at rest. `SCHEMA_SQL` defines **17 base tables** (verify: `grep -c "CREATE TABLE" src/lib/db/core.ts` minus 1 for `_omniroute_migrations`).
12- **`migrationRunner.ts`** — Applies versioned SQL files from `db/migrations/` inside transactions. Tracks applied migrations in `_omniroute_migrations`. Each migration is idempotent.
13- **`db/migrations/`** — 110 SQL files (`001_initial_schema.sql` → `110_*.sql`). Each runs in a transaction, never fails partially.
14- **`localDb.ts`** — Re-export layer only. Never add logic here.
15 
16## Key Domain Modules
17 
18| Module                 | Tables / Scope            | Responsibility                                      |
19| ---------------------- | ------------------------- | --------------------------------------------------- |
20| `providers.ts`         | `provider_connections`    | OAuth/API key provider registration and credentials |
21| `models.ts`            | `models`                  | Model definitions, capabilities, pricing            |
22| `combos.ts`            | `combos`, `combo_targets` | Combo routing configs, target ordering              |
23| `apiKeys.ts`           | `api_keys`                | API key lifecycle, scopes, quota tracking           |
24| `settings.ts`          | `settings`                | KV store for system configuration                   |
25| `secrets.ts`           | `secrets`                 | Encrypted secret storage (API keys at rest)         |
26| `quotaSnapshots.ts`    | `quota_snapshots`         | Historical quota usage for analytics                |
27| `quotaPools.ts`        | `quota_pools`             | Quota-Share pool management                         |
28| `creditBalance.ts`     | `credit_balance`          | Per-provider credit tracking                        |
29| `compression.ts`       | compression settings      | Prompt compression pipeline config                  |
30| `compressionCombos.ts` | `compression_combos`      | Per-combo compression pipeline assignments          |
31| `evals.ts`             | eval tables               | Eval framework persistence                          |
32| `webhooks.ts`          | `webhooks`                | Event-driven webhook subscriptions and logs         |
33| `reasoningCache.ts`    | reasoning cache           | Hybrid in-memory + SQLite reasoning replay          |
34| `skills.ts`            | `skills`                  | Skill registration and metadata                     |
35| `plugins.ts`           | `plugins`                 | Plugin marketplace state                            |
36| `gamification.ts`      | gamification tables       | Levels, badges, leaderboard                         |
37| `notion.ts`            | notion tables             | Notion integration state                            |
38| `obsidian.ts`          | obsidian tables           | Obsidian vault integration state                    |
39| `files.ts`             | file storage              | Uploaded file management                            |
40| `batches.ts`           | batch processing          | Batch job tracking                                  |
41| `featureFlags.ts`      | feature flags             | Runtime feature flag overrides                      |
42| `backup.ts`            | backup ops                | Serialize/deserialize entire DB state               |
43| `cleanup.ts`           | cleanup ops               | Stale data purging                                  |
44| `healthCheck.ts`       | health ops                | DB health monitoring                                |
45| `databaseSettings.ts`  | database settings         | DB-level configuration                              |
46 
47Full list: `ls src/lib/db/*.ts | wc -l` (95 files). Drift detection: `npm run check:docs-counts`.
48 
49## Encryption & Security
50 
51- **Sensitive fields** (API keys, OAuth tokens, connection strings) encrypted at rest using AES-256-GCM
52- **`encryptConnectionFields()`** in `core.ts` — automatic encryption when storing provider credentials
53- **`secrets.ts`** — dedicated encrypted store for long-term secret handling
54- **Never log** SQLite encryption keys or raw secrets; always use redacted values in logs
55 
56## Adding a New Domain Module
57 
581. Create `src/lib/db/[module].ts` with CRUD functions
592. Export from `src/lib/localDb.ts` (add re-export)
603. If new tables: create migration in `db/migrations/NNN_[description].sql`
614. Migration runs automatically at startup via `migrationRunner.ts`
625. Add unit tests in `tests/unit/db/`
63 
64## Anti-Patterns
65 
66- Raw SQL in routes — always use domain module functions
67- Direct `prepare()` statements outside `db/` — breaks modularity
68- Adding logic to `localDb.ts` — re-export layer only
69- Barrel-importing from `localDb.ts` — import specific modules instead
70- Skipping migrations for schema changes — all changes go through `db/migrations/`
71