skills/plugins/document-navigator/docs/api-stack/api/api-db-adapter/README.md

@suaveplan/api-db-adapter — bridge from @suaveplan/db engine adapters to the plugin DatabaseLike surface. Provides AdminDatabaseLike + TenantScopedDatabaseLike factories, a per-engine migration runner, the db.unsafe-queries capability gate, an open DatabaseEngineRegistry, and a worker-proxy variant for the worker host. Both direct + proxy surfaces pass the same conformance suite.

Stage-4 bridge from the @suaveplan/db engine adapters to the plugin AdminDatabaseLike / TenantScopedDatabaseLike surfaces exposed by @suaveplan/api-types/db.

The package contains five orthogonal pieces:

1. options — Zod-validated DbAdapterOptions (engine, url, pool?, tenantColumn, migrationsDir?, autoInjectInserts).
2. engine-registry — open registry of engine factories. The package itself ships no engines pre-installed; host code (or a downstream adapter package) calls registerEngine("sqlite" | "postgres" | "surrealdb" | <merged>, factory) once at boot. The shape keeps the package universally importable.
3. tenant-scoped — the SQL rewriter that injects WHERE tenant_id = ? on every SELECT/UPDATE/DELETE, verifies / auto-injects the tenant column on INSERT, and rejects DDL or unsupported SQL with DB_ADAPTER.TENANT_SCOPE_VIOLATION. Strict / lenient INSERT mode is autoInjectInserts on the options.
4. migrations — per-engine migration runner consuming a directory of *.sql files; tracks applied versions in __plugin_migrations and is idempotent on re-run. Failed migrations roll back where the engine supports transactions.
5. unsafe — the db.unsafe escape hatch, gated on the db.unsafe-queries capability flag in the plugin manifest. Every permitted call increments plugin.unsafe_query_total{plugin}.

The package also ships a worker-side counterpart (createDbAdapterWorkerProxy) that wires the worker-side api-db-worker-proxy client into the same surface so direct + proxy variants are interchangeable in plugin code.

Documentation tree

• engine-registry — The engine registry is the extension point that lets api-db-adapter stay universally importable while still supportin…
  • engine-registry — The engine registry is the extension point that lets api-db-adapter stay universally importable while still supportin…
• error-codes — This module is the single source of truth for every public failure code raised by @suaveplan/api-db-adapter. Codes ar…
  • error-codes — This module is the single source of truth for every public failure code raised by @suaveplan/api-db-adapter. Codes ar…
• factory — createDbAdapter is the public entry point for host code that wants to wire the @suaveplan/db engine adapter…
  • create-db-adapter — createDbAdapter is the public entry point for host code that wants to wire the @suaveplan/db engine adapter…
• migrations — runMigrations applies a plugin's pending migrations from a directory of *.sql files, recording applied versions in…
  • run-migrations — runMigrations applies a plugin's pending migrations from a directory of *.sql files, recording applied versions in…
• options — DbAdapterOptions is the single config object accepted by every public factory in this package — createDbAdapter, `c…
  • db-adapter-options — DbAdapterOptions is the single config object accepted by every public factory in this package — createDbAdapter, `c…
• proxy — createDbAdapterWorkerProxy is the worker-side counterpart to createDbAdapter. Inside an api-worker-host worker, p…
  • create-db-adapter-worker-proxy — createDbAdapterWorkerProxy is the worker-side counterpart to createDbAdapter. Inside an api-worker-host worker, p…
• tenant-scoped — This module is the load-bearing safety boundary between a plugin route handler and the underlying admin database surfac…
  • create-tenant-scoped-wrapper — This module is the load-bearing safety boundary between a plugin route handler and the underlying admin database surfac…
• unsafe — createUnsafeGate builds the ctx.db.unsafe API exposed on every tenant- scoped surface. The gate is the only sanctio…
  • unsafe-query-gate — createUnsafeGate builds the ctx.db.unsafe API exposed on every tenant- scoped surface. The gate is the only sanctio…

Quick start

import { createDbAdapter } from "@suaveplan/api-db-adapter/adapter";
import { registerEngine } from "@suaveplan/api-db-adapter/engine-registry";
import { runMigrations } from "@suaveplan/api-db-adapter/migrations";

registerEngine("sqlite", async (opts) => myBunSqliteFactory(opts));

const db = await createDbAdapter({ engine: "sqlite", url: ":memory:" });
await runMigrations({
    plugin: "audit",
    capabilities: [],
    admin: db.admin,
    migrationsDir: "./db/migrations",
    fs: nodeFsReader,
});

const scoped = db.scoped("acme" as TenantId);
const rows = await scoped.query<User[]>("SELECT * FROM users");
//   actual SQL: SELECT * FROM users WHERE tenant_id = ?
//   bound:      ["acme"]

Tenant-scope semantics

The wrapper rewrites SQL based on the leading keyword (case-insensitive):

Shape	Rewrite
SELECT ... [WHERE ...]	append WHERE/AND tenant_id = ? before any ORDER BY / LIMIT / RETURNING / GROUP BY tail
UPDATE ... [WHERE ...]	same
DELETE ... [WHERE ...]	same
INSERT INTO t (cols) VALUES (vals) w/o tenant_id and autoInjectInserts: true	inject column + value
same w/o tenant_id and autoInjectInserts: false	throw TENANT_SCOPE_VIOLATION
INSERT ... w/ conflicting tenant_id (literal or bound)	throw TENANT_SCOPE_VIOLATION
anything else (DDL, INSERT...SELECT, multi-row VALUES, record-shaped params)	throw TENANT_SCOPE_VIOLATION — use a migration or db.unsafe

db.unsafe escape hatch

createUnsafeGate({ plugin, capabilities, admin }) returns an UnsafeQueryApi (query, execute). The capability check is array membership on db.unsafe-queries; absent → throw DB_ADAPTER.UNSAFE_NOT_PERMITTED. Permitted calls increment plugin.unsafe_query_total{plugin}.

Migration directory layout

Files are read from migrationsDir and applied lexicographically. The recommended naming convention is NNN_<short-description>.sql:

db/migrations/
    001_init.sql
    002_add_invoices.sql
    003_index_invoices_on_org.sql

The runner records each applied file in __plugin_migrations(plugin, version, applied_at) and is idempotent — re-running with no new files is a no-op.

Error codes

• DB_ADAPTER.TENANT_SCOPE_VIOLATION — scoped wrapper saw conflicting tenant value, DDL, or unsupported SQL shape.
• DB_ADAPTER.UNSAFE_NOT_PERMITTED — db.unsafe.* invoked without db.unsafe-queries capability.
• DB_ADAPTER.MIGRATION_FAILED — migration body threw; rolled back where supported.
• DB_ADAPTER.ENGINE_UNSUPPORTED — engine value has no registered factory.
• DB_ADAPTER.INVALID_OPTIONS — Zod validation failed; issues attached to error.context.issues.