# `@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.