suaveplan/openspec/archive/2026-05-26-api-db-sqlite-memory/api-db-sqlite-memory/proposal.md

@suaveplan/api-db-sqlite-memory — In-memory SQLite DB adapter for tests

Package: @suaveplan/api-db-sqlite-memory Status: PROPOSED Type: new-package Stage: 5 (Testing kit + conformance suites) — see umbrella openspec/changes/api-plugin-foundation/

Why

Per design doc §13.2, createTestHost({ adapters: { db: { engine: "sqlite-memory" } } }) boots a plugin against a real bun:sqlite :memory: database — production-grade adapter that happens to keep state in process memory. Migrations run, tenant-scoping enforces, prepared statements work; the only thing missing is durability. Tests run in milliseconds.

This adapter implements the same AdminDatabaseLike + TenantScopedDatabaseLike surfaces as api-db-adapter (Stage 4) and passes the same conformance suite — proving the contract is engine-agnostic at the SQLite layer.

What changes

Net-new package depending on @suaveplan/api-db (contract + ./testing conformance), @suaveplan/api-types, @suaveplan/error, zod, and bun:sqlite (Bun built-in — no external dep).

• ADDED: createSqliteMemoryDb(opts) — factory returning { admin: AdminDatabaseLike; scoped(tenantId): TenantScopedDatabaseLike; close(): void }.
• ADDED: SqliteMemoryDbOptions — Zod schema (tenantColumn?: string (default tenant_id), migrationsDir?: string, pragmas?: Record<string, string> (default { journal_mode: "WAL", foreign_keys: "ON" })).
• ADDED: Tenant-scoped wrapper — same semantics as api-db-adapter (WHERE tenant_id injection on read, payload injection on write, DB_SQLITE_MEMORY_TENANT_SCOPE_VIOLATION on cross-tenant access).
• ADDED: Migration runner — applies SQL files from migrationsDir in lexicographic order, records applied versions in __plugin_migrations table; idempotent on re-run.
• ADDED: DB_SQLITE_MEMORY_* error codes (DB_SQLITE_MEMORY_TENANT_SCOPE_VIOLATION, DB_SQLITE_MEMORY_MIGRATION_FAILED, DB_SQLITE_MEMORY_INVALID_OPTIONS).
• ADDED: Registered in @suaveplan/api-host/testing's adapter table as engine: "sqlite-memory".

Out of scope

• Persistence — :memory: only at this stage. Operators wanting on-disk SQLite use api-db-adapter with engine: "sqlite".
• WAL replication — N/A for :memory:.
• Surreal-flavoured queries — wrong engine.

Source mapping

Genesis-original. Source of design intent: docs/superpowers/specs/2026-05-13-api-plugin-foundation-design.md.

Requirement	Design doc section
Real SQLite in tests, not a double	§13.2
Same conformance suite as Stage 4 db	§13.4
Registered in api-host/testing adapter table	§15 Stage 5

Verification approach

• src/factory/create-sqlite-memory-db.test.ts — admin SELECT/INSERT/transaction, prepared statements, pragmas applied.
• src/factory/create-sqlite-memory-db.spec-conformance.test.ts — runDbAdapterConformance("sqlite-memory-direct", ...).
• src/tenant-scoping/tenant-scoping.test.ts — read injection, write injection, cross-tenant rejection.
• src/migrations/migrations.test.ts — forward, idempotent, failure rollback.
• tests/integration/integration.test.ts — via createTestHost.

Migration impact

None — additive only.