# Capability Spec Delta: api-db-sqlite-memory (api-db-sqlite-memory) **Package**: `@suaveplan/api-db-sqlite-memory` **Status**: PROPOSED ## ADDED Requirements ### Requirement: Backed by real bun:sqlite :memory: The package SHALL open a real `bun:sqlite` `Database(":memory:")` and route every query/execute/prepare through it. No SQL emulation, no in-process query interpreter. #### Scenario: PRAGMA foreign_keys is honoured - **WHEN** the adapter is constructed with default pragmas AND a parent/child table pair has a `FOREIGN KEY` constraint AND a child insert references a non-existent parent - **THEN** the insert fails with the standard SQLite foreign-key violation (proving real SQLite is in the loop) **Verified by:** `src/factory/create-sqlite-memory-db.test.ts::"PRAGMA foreign_keys is honoured by the real bun:sqlite database"` --- ### Requirement: Admin and tenant-scoped surfaces The factory SHALL return `{ admin, scoped(tenantId), close }`. The `scoped` wrapper enforces `WHERE tenant_id = $1` on reads and rejects writes whose payload's `tenant_id` differs from the wrapper's bound tenant (or auto-injects when `autoInjectInserts: true`). #### Scenario: admin reads across tenants - **WHEN** admin runs `SELECT * FROM users` against a multi-tenant fixture - **THEN** rows from all tenants are returned #### Scenario: scoped rejects cross-tenant insert - **WHEN** `scoped("acme")` attempts `INSERT INTO users (tenant_id) VALUES ('bigcorp')` - **THEN** the call throws `DB_SQLITE_MEMORY_TENANT_SCOPE_VIOLATION` **Verified by:** `src/tenant-scoping/tenant-scoping.test.ts::"admin reads across tenants"` AND `src/tenant-scoping/tenant-scoping.test.ts::"scoped rejects cross-tenant insert"` --- ### Requirement: Migration runner The adapter SHALL run migrations from `migrationsDir` in lexicographic order, record applied versions in `__plugin_migrations`, and remain idempotent on re-run. Failures throw `DB_SQLITE_MEMORY_MIGRATION_FAILED` with the failing file path. #### Scenario: forward migration applies once - **WHEN** `runMigrations` is called with a 2-file directory AND `__plugin_migrations` is empty - **THEN** both files run AND `__plugin_migrations` has 2 rows #### Scenario: idempotent re-run - **WHEN** `runMigrations` is called a second time - **THEN** zero new migrations run AND no error is thrown **Verified by:** `src/migrations/migrations.test.ts::"applies pending migrations in lexicographic order"` AND `src/migrations/migrations.test.ts::"is idempotent on re-run"` --- ### Requirement: Conforms to Db contract #### Scenario: conformance suite passes - **WHEN** `runDbAdapterConformance("sqlite-memory-direct", () => createSqliteMemoryDb(...))` runs - **THEN** every invariant passes **Verified by:** `src/factory/create-sqlite-memory-db.spec-conformance.test.ts::"sqlite-memory-direct conforms to Db contract"` --- ### Requirement: Registered in `api-host/testing` adapter table #### Scenario: createTestHost resolves the engine sentinel - **WHEN** `createTestHost({ adapters: { db: { engine: "sqlite-memory" } } })` - **THEN** the resulting `host.db` is an instance produced by this factory **Verified by:** `tests/integration/integration.test.ts::"createTestHost resolves engine:\"sqlite-memory\" to this adapter"`