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

@suaveplan/api-db-adapter — Bridge from @suaveplan/db to plugin DatabaseLike

Package: @suaveplan/api-db-adapter Status: PROPOSED Type: new-package Stage: 4 (Base adapters) — see umbrella openspec/changes/api-plugin-foundation/

Why

Plugins consume ctx.db typed as either AdminDatabaseLike (in PluginContext — for migrations + admin work) or TenantScopedDatabaseLike (in RequestContext — same field, narrower type). Per design doc §5.1, the tenant-scoped wrapper injects WHERE tenant_id = $1 on reads and tenant_id on writes — plugins literally cannot read another tenant's rows from a route handler. Admin access from a route is the explicit exception via the db.unsafe-queries capability flag in the plugin's manifest; every such call emits plugin.unsafe_query_total.

api-db-adapter is the bridge between the genesis @suaveplan/db adapter foundation and the plugin host. It implements:

• The AdminDatabaseLike surface (raw SQL/SurQL plus prepared statements).
• The tenant-scoped wrapper (rejects unscoped queries at the boundary).
• A per-engine migration runner (Postgres / SQLite / Surreal) consuming each plugin's manifest.database.migrations directory.
• The unsafe escape hatch — db.unsafe.query() — gated by manifest capability declaration.

Stage 4 invariant: ships direct AND worker-proxy surfaces; both pass runDbAdapterConformance (§5.2a, §13.4). The proxy uses transferable ReadableStream for cursor streaming (large query results survive the boundary without buffering).

What changes

Net-new package depending on @suaveplan/api-db (contract), @suaveplan/api-types, @suaveplan/api-worker-rpc, @suaveplan/db, @suaveplan/error, zod.

• ADDED: createDbAdapter(opts) — direct-surface factory; returns { admin: AdminDatabaseLike; scoped(tenantId): TenantScopedDatabaseLike }.
• ADDED: createDbAdapterWorkerProxy(port, opts) — proxy-surface factory.
• ADDED: DbAdapterOptions — Zod schema with engine: "postgres" | "sqlite" | "surrealdb", url: string, pool: { min, max, idleMs }, tenantColumn?: string (default tenant_id), migrationsDir?: string.
• ADDED: createTenantScopedWrapper(admin, tenantId, options) — injects WHERE tenantColumn = $1 on every read; rejects writes whose payload lacks tenantColumn (or auto-injects, configurable).
• ADDED: runMigrations(plugin, capabilities) — per-engine migration runner consuming manifest.database.migrations directory; tracks applied versions in __plugin_migrations table.
• ADDED: db.unsafe.query(sql, params) — capability-gated; throws DB_UNSAFE_NOT_PERMITTED if plugin.capabilities lacks db.unsafe-queries. On allowed calls emits plugin.unsafe_query_total{plugin} counter.
• ADDED: DB_ADAPTER_* error codes (DB_ADAPTER_TENANT_SCOPE_VIOLATION, DB_ADAPTER_UNSAFE_NOT_PERMITTED, DB_ADAPTER_MIGRATION_FAILED, DB_ADAPTER_ENGINE_UNSUPPORTED, DB_ADAPTER_INVALID_OPTIONS).

Out of scope

• The @suaveplan/db adapter foundation itself (already exists at v0).
• Surreal-specific features beyond CRUD + migrations — api-db-surreal-graph future work.
• Connection pooling internals — delegated to @suaveplan/db.
• 2PC participant role — api-2pc Stage 6.
• Saga compensation — api-saga Stage 6.

Source mapping

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

Requirement	Design doc section
Same field, different type per ctx	§5.1
Tenant-scoped wrapper rejects unscoped reads	§5.1 (paragraph after RequestContext)
db.unsafe-queries capability gating	§5.1 (last paragraph), §2.2 (sandbox row)
plugin.unsafe_query_total counter	§5.1 (last paragraph)
Per-engine migration runner	§3 (manifest database.migrations), §4 (FSM installing step)
Worker-proxy variant + transferable streams	§5.2a
Conformance both surfaces	§13.4

Verification approach

• src/factory/create-db-adapter.test.ts — admin surface against in-memory SQLite (real bun:sqlite); read/write/transaction.
• src/factory/create-db-adapter.spec-conformance.test.ts — runDbAdapterConformance("db-direct", ...).
• src/proxy/create-db-adapter-worker-proxy.test.ts — proxy round-trip; ReadableStream cursor preserved across MessageChannel.
• src/proxy/create-db-adapter-worker-proxy.spec-conformance.test.ts — runDbAdapterConformance("db-worker-proxy", ...).
• src/tenant-scoping/create-tenant-scoped-wrapper.test.ts — SELECT injection, INSERT injection, cross-tenant read rejection.
• src/migrations/run-migrations.test.ts — per-engine: Postgres (testcontainer), SQLite (memory), Surreal (testcontainer). Forward + idempotent re-run.
• src/unsafe/unsafe-query-gate.test.ts — capability check + counter emission.
• tests/integration/postgres.test.ts — real Postgres via @suaveplan/testing-integration-postgres.

Migration impact

None — additive only.