integration.provider-database

@lssm-tech/integration.provider-database

Website: https://contractspec.io

Database provider implementations for ContractSpec.

Usage

Import the root entrypoint for the domain surface, or import a concrete implementation from the ./impls/* subpaths. Use ./governed-read when a contracts-spec DataViewQueryGenerator emits a database.query.readonly descriptor and you need to execute that governed read through a DatabaseProvider. The helper builds parameterized SQL from field mappings and keeps SQL construction inside this adapter package. Governed reads support offset descriptors and cursor descriptors from DataViewDatabaseBinding.pagination. Cursor descriptors add a parameterized cursorField > cursor predicate only when the cursor field is present in the contract-owned select mapping; unknown cursor fields fail before provider execution, and maxPageSize is enforced before the query reaches the provider. executeQuery exposes the governed read as a Transport-shaped resolver for the data-fetching engine: it executes a canonical QueryEnvelope and returns a ContractResult<QueryResultEnvelope> carrying a real versionToken (read from the entity's version column with versionTokenFromColumn()) for the engine's etag / optimistic-concurrency path.

Use ./governed-mutation for first-class database writes after the owning domain command has planned a portable database.mutation.execute@2.0.0 envelope. The helper validates actor, tenant, domain-command authority, policy decision, idempotency key, transaction boundary, expected write set, audit refs, replay ref, and correlation ref before running the provider transaction. Drizzle/PostgreSQL rendering remains in this integration package, not in contracts-spec core.

Version-governed entities

Declare the entities whose writes need optimistic concurrency once, at the composition root, and pass the registry to every governed mutation:

const versioning = createGovernedVersionRegistry([
	{ entity: 'workspace', table: 'workspaces' }, // schema public, column version
]);
await applyVersionColumnMigration(provider, buildVersionColumnMigration(versioning));

await executeGovernedMutation({
	descriptor: { ...renameDescriptor, expectedVersionToken: ctx.queryEnvelope?.versionToken },
	provider,
	versioning,
});

• buildVersionColumnMigration(registry) returns idempotent up statements and down statements. up adds a missing column as bigint NOT NULL DEFAULT 1 (existing rows start at 1) and marks it with a column comment, or normalizes an existing smallint/integer/bigint column (NULLs backfilled to 1, NOT NULL DEFAULT 1; any other type fails the migration). It then installs a BEFORE UPDATE trigger that sets NEW.<column> = OLD.<column> + 1, so writers that bypass the store still advance the version. down removes the triggers and functions and drops only the columns up added. Building the migration fails when two entities of one schema would share a trigger function name.
• A descriptor without a schema matches the one registered schema that governs its table and fails when several do; the governed SQL always names the registered schema, so it never depends on the connection's search_path.
• On a governed table, insert returns the new versionToken; update and delete must carry expectedVersionToken, check it in the same statement (AND <column> = $n::bigint ... RETURNING <column>) and return the new token (update) or none (delete). Their where must name one row by its key: the store refuses a write that returns more than one row, but it does not see rows the version filter excluded.
• A missing, stale or non-integer token (BIGINT range only), or a row that no longer matches, throws ContractSpecError with the typed Core version conflict (CONFLICT 409, metadata.versionConflict.reason = missing, stale or gone, and the stored version in versionToken when the row exists). The transaction rolls back and the idempotency key is not stored. The data engine resolves it with the mutation's conflict policy; under client-wins a gone row fails the mutation and rolls the optimistic write back.
• A write that returns more than one row, a value for the version column itself, or an expectedVersionToken on a table that is not in the registry fails with an error.
• Clients send the expected token as MutationRequest.versionToken; the engine forwards it as QueryEnvelope.versionToken and REST carries it as $versionToken, so handlers read ctx.queryEnvelope?.versionToken.

Managed and BYOK Postgres/Supabase deployments are the production posture. Local/PgLite remains a provider-binding compatibility mode for deterministic tests and demos, not a separate ownership mode.

Postgres statement parameters preserve the shared DatabaseProvider contract: Date values are serialized as ISO-8601 timestamps before reaching postgres.js, while bigint, byte, JSON-object, and array parameters retain their existing normalized representations.

Public subpaths

• . re-exports the provider surface plus governed read/mutation helpers.
• ./database re-exports the shared DatabaseProvider contracts.
• ./governed-read executes portable DataView read descriptors through a provider.
• ./governed-mutation executes governed mutation descriptors with transaction, idempotency, audit hooks and, for version-governed tables, the version precondition.
• ./governed-versioning declares version-governed entities (createGovernedVersionRegistry).
• ./version-column-migration builds and applies the per-entity version-column rollout (buildVersionColumnMigration, applyVersionColumnMigration, VERSION_COLUMN_ROLLOUT_MARKER).
• ./version-token-source maps a stored version column to a VersionToken (versionTokenFromColumn, versionTokenFromValue) and a token back to its BIGINT bind value (parseVersionCounter).
• ./impls and ./impls/supabase-psql expose concrete PostgreSQL/Supabase implementations.
• ./postgres-json-semantics exports withPgJsonSemantics(client), which makes a raw postgres.js client pass JSON text through to json/jsonb parameters instead of double-encoding it (pg/PGlite semantics). It has no imports, so any runtime that builds its own postgres.js client can use it.

Compatibility

This package owns the database implementation slice split out of @lssm-tech/integration.providers-impls. The legacy package continues to re-export these subpaths for existing consumers.