# Capability Spec Delta: api-db-adapter (api-db-adapter) **Package**: `@suaveplan/api-db-adapter` **Status**: PROPOSED ## ADDED Requirements ### Requirement: Admin surface exposes raw DatabaseLike The package SHALL expose `AdminDatabaseLike` (per `@suaveplan/api-types`) with `query`, `execute`, `transaction`, `prepare` (for engines that support it). Admin surface is used for migrations and operator tooling; it does NOT enforce tenant scoping. #### Scenario: admin query returns full result set - **WHEN** admin runs `SELECT * FROM users` against a fixture seeded with rows across two tenants - **THEN** the result contains rows from both tenants (no filter applied) **Verified by:** `src/factory/create-db-adapter.test.ts::"admin surface returns rows across all tenants"` --- ### Requirement: Tenant-scoped wrapper rejects unscoped queries `createTenantScopedWrapper(admin, tenantId, options)` SHALL return a `TenantScopedDatabaseLike` that: 1. Injects `WHERE tenantColumn = $1` (default column `tenant_id`) on every SELECT/UPDATE/DELETE. 2. Injects `tenantColumn` on every INSERT — if the payload lacks the column AND `autoInjectInserts: true`, inject the value; if `autoInjectInserts: false`, reject with `DB_ADAPTER_TENANT_SCOPE_VIOLATION`. 3. Rejects raw SQL that doesn't reference the tenant column on a tenant-scoped table. #### Scenario: SELECT auto-scopes to current tenant - **WHEN** a route handler runs `SELECT * FROM users` against the scoped wrapper for `tenant="acme"` - **THEN** the executed SQL contains `WHERE tenant_id = 'acme'` (or equivalent prepared parameter) AND no rows from other tenants are returned #### Scenario: cross-tenant write is rejected - **WHEN** the scoped wrapper for `tenant="acme"` is asked to `INSERT INTO users (tenant_id) VALUES ('bigcorp')` - **THEN** the call throws `DB_ADAPTER_TENANT_SCOPE_VIOLATION` **Verified by:** `src/tenant-scoping/create-tenant-scoped-wrapper.test.ts::"SELECT auto-scopes to current tenant"` AND `src/tenant-scoping/create-tenant-scoped-wrapper.test.ts::"INSERT with conflicting tenant_id is rejected"` --- ### Requirement: db.unsafe escape hatch is capability-gated The scoped wrapper SHALL expose `db.unsafe.query(sql, params)` which bypasses tenant injection. Calls MUST be allowed ONLY if the plugin's manifest declares the `db.unsafe-queries` capability; otherwise throw `DB_ADAPTER_UNSAFE_NOT_PERMITTED`. Every allowed call MUST emit `plugin.unsafe_query_total{plugin}`. #### Scenario: plugin without declaration is blocked - **WHEN** a route handler whose plugin's manifest lacks `db.unsafe-queries` calls `ctx.db.unsafe.query(...)` - **THEN** the call throws `DB_ADAPTER_UNSAFE_NOT_PERMITTED` AND nothing is executed against the engine #### Scenario: plugin with declaration is allowed and counted - **WHEN** a plugin declaring `db.unsafe-queries` calls `ctx.db.unsafe.query("SELECT * FROM audit_log")` - **THEN** the query runs AND `plugin.unsafe_query_total{plugin=}` increments by 1 **Verified by:** `src/unsafe/unsafe-query-gate.test.ts::"throws when capability not declared"` AND `src/unsafe/unsafe-query-gate.test.ts::"emits plugin.unsafe_query_total when allowed"` --- ### Requirement: Per-engine migration runner `runMigrations(plugin, capabilities)` SHALL apply pending migrations from `manifest.database.migrations` in lexicographic order, recording applied versions in `__plugin_migrations(plugin, version, applied_at)`. Re-running with no new files MUST be a no-op (idempotent). Supported engines: Postgres, SQLite, Surreal. Each engine SHALL apply migrations within a transaction where the engine supports it; on failure the migration rolls back AND `DB_ADAPTER_MIGRATION_FAILED` is thrown with the failing file path. #### Scenario: forward migration applies once - **WHEN** `runMigrations` is called with migrations `[001_init.sql, 002_add_invoices.sql]` AND `__plugin_migrations` is empty - **THEN** both files run AND `__plugin_migrations` records two rows AND `api.db.migrations_applied_total{plugin}` records 2 #### Scenario: idempotent re-run - **WHEN** `runMigrations` is called twice in a row with the same file set - **THEN** the second call applies zero migrations AND `api.db.migrations_applied_total` does not increment on the second call #### Scenario: failure rolls back - **WHEN** `002_add_invoices.sql` contains a syntax error - **THEN** the call throws `DB_ADAPTER_MIGRATION_FAILED` with `details.file = "002_add_invoices.sql"` AND `__plugin_migrations` does NOT record version 002 **Verified by:** `src/migrations/run-migrations.test.ts::"applies pending migrations in lexicographic order"` AND `src/migrations/run-migrations.test.ts::"is idempotent on re-run"` AND `src/migrations/run-migrations.test.ts::"rolls back and throws on migration failure"` --- ### Requirement: Direct and worker-proxy surfaces both pass conformance The worker-proxy variant MUST preserve `ReadableStream`-based cursors across the `MessagePort` boundary (transferable, zero-copy where the runtime supports it). #### Scenario: direct conforms - **WHEN** `runDbAdapterConformance("db-direct", () => createDbAdapter(...))` runs against an in-memory SQLite - **THEN** every invariant passes #### Scenario: worker-proxy conforms with streaming cursors - **WHEN** `runDbAdapterConformance("db-worker-proxy", ...)` runs over a real `MessageChannel` AND a query returns a cursor of 10_000 rows - **THEN** every invariant passes AND the cursor is consumed on the proxy side without buffering all rows in memory at once **Verified by:** `src/factory/create-db-adapter.spec-conformance.test.ts::"db-direct conforms to Db contract"` AND `src/proxy/create-db-adapter-worker-proxy.spec-conformance.test.ts::"db-worker-proxy conforms to Db contract with streaming cursors"` --- ### Requirement: Engine selection is Zod-validated `DbAdapterOptions.engine` MUST be one of `"postgres" | "sqlite" | "surrealdb"`. Any other value MUST throw `DB_ADAPTER_ENGINE_UNSUPPORTED` at construction. #### Scenario: unsupported engine throws - **WHEN** `createDbAdapter({ engine: "mongodb" as never, url: "..." })` - **THEN** the factory throws `DB_ADAPTER_ENGINE_UNSUPPORTED` synchronously **Verified by:** `src/options/db-adapter-options.test.ts::"throws on unsupported engine value"`