Database setup

Set up Drizzle without coupling modules to your database

Use Drizzle as the PostgreSQL migration and query layer while keeping ContractSpec modules portable. Compose schemas from module contributions, apply migrations explicitly, seed deterministic fixtures, and bind AuthOS, BillingOS, and RBAC through host-owned adapters.

Install the database boundary

bun add drizzle-orm drizzle-kit @lssm-tech/lib.schema @lssm-tech/integration.provider-database @lssm-tech/lib.identity-rbac

Drizzle owns SQL schema and migrations. ContractSpec owns portable entities, domain contracts, RBAC policy semantics, and governed database operation envelopes.

Recommended setup flow

• Compose database entities from module schema contributions before writing Drizzle tables by hand.

• Generate or maintain a Drizzle PostgreSQL schema from the composed model, then keep the Drizzle schema as migration input.

• Use drizzle-kit generate for migration files, drizzle-kit migrate for applying them, and custom migrations for RLS, seed prerequisites, or unsupported DDL.

• Bind runtime reads and writes through @lssm-tech/integration.provider-database instead of importing Drizzle into modules such as AuthOS or BillingOS.

• Seed tenants, users, roles, permissions, and minimal domain fixtures in deterministic order after migrations and before UI demos/tests.

• Fail closed on RBAC data access: tenant id, actor id, policy decision, idempotency key, audit refs, and expected write sets must be explicit for governed writes.

Drizzle configuration

Keep drizzle.config.ts, src/db/schema.ts, and the drizzle/ migration folder in the host app or deployment package. Do not place provider credentials or migration side effects inside reusable modules.

1// drizzle.config.ts
2import { defineConfig } from "drizzle-kit";
3
4export default defineConfig({
5  dialect: "postgresql",
6  schema: "./src/db/schema.ts",
7  out: "./drizzle",
8  dbCredentials: { url: process.env.DATABASE_URL! },
9  strict: true,
10});

Compose module schemas

Start with reusable contributions such as identityRbacSchemaContribution, then add host-owned entities for AuthOS adapters and BillingOS persistence. Generate Drizzle tables from that composed source or keep a reviewed generated schema checked in.

1import { composeModuleDatabaseSchemas } from "@lssm-tech/lib.schema";
2import { identityRbacSchemaContribution } from "@lssm-tech/lib.identity-rbac/entities";
3
4const schemaSource = composeModuleDatabaseSchemas([
5  identityRbacSchemaContribution,
6  authHostSchemaContribution,
7  billingHostSchemaContribution,
8]);
9
10await writeFile("src/db/schema.ts", schemaSource);

Bind modules through providers and ports

Use @lssm-tech/integration.provider-database for tenant-scoped providers, governed reads, and governed mutations. AuthOS and BillingOS should receive host adapters, not raw Drizzle clients.

1import { createTenantScopedDatabaseProvider } from "@lssm-tech/integration.provider-database/impls";
2
3const tenantDb = createTenantScopedDatabaseProvider({
4  provider: postgresDatabaseProvider,
5  tenantId: session.tenantId,
6});
7
8export const billingPorts = {
9  searchCustomers: (query) => customerRepository.search(tenantDb, query),
10  searchSellableItems: (query) => priceBookRepository.search(tenantDb, query),
11};

Adopt the unified database runtime

For new surfaces, prefer createDatabaseRuntime() from @lssm-tech/integration.runtime-managed/database. It binds pooling, observability, RLS tenant scoping, AuthOS principal carriage, and pre-bound governed query/mutation resolvers into a single adopt-once handle — so consumers get the safe path with nothing extra to configure.

• Connection pooling (transaction/session/direct modes; transaction-pooler sets prepare:false for pgBouncer/Supabase).

• Zero-config observability: every governed read/write emits an OTel span + duration metric via the database.query / database.mutation SignalSpecs — nothing to wire per call.

• Automatic RLS tenant scoping: withTenant(tenantId) issues set_config('app.tenant_id', …, true) tx-local before every statement, and fails closed when no tenant is resolvable.

• AuthOS principal carriage: TransportContext.principal is forwarded unstripped; policy decisions stay in identity-rbac handlers (the runtime never bypasses the PDP).

• Typed, N+1-safe relational reads: parent→children resolve in one governed JOIN statement (database.query.relational), never an ungoverned raw query.

1// server-only — import from the ./database subpath, never the package root
2import { createDatabaseRuntime } from "@lssm-tech/integration.runtime-managed/database";
3import { createInstrumentation } from "@lssm-tech/lib.observability";
4
5const runtime = createDatabaseRuntime({
6  databaseUrl: process.env.DATABASE_URL!,
7  pooler: "transaction", // prepare:false, sized for pgBouncer/Supabase
8  instrumentation: createInstrumentation({ serviceName: "companyos" }),
9});
10
11// known-tenant server path: RLS app.tenant_id is set automatically, fail-closed
12const tenantDb = runtime.withTenant(session.tenantId);
13const page = await tenantDb.governedQuery(workspacesDescriptor, { principal: session.userId });
14// page.data + page.pageInfo.cursor / hasNextPage (cursor pagination by default)

createDatabaseRuntime pulls node-only postgres (net/tls/perf_hooks). Import it ONLY from the server-only @lssm-tech/integration.runtime-managed/database subpath in RSC, route handlers, or server modules — never from the package root barrel, which client components import.

Stream reads to the client (SSR bridge)

Prefetch a governed query in a Server Component, dehydrate it, and hydrate the existing client data engine so the first client read is a cache hit with no second fetch. Loading, empty, and error states are driven by the shared query engine.

1// RSC (server): prefetch through the governed resolver, dehydrate the cache
2import { prefetchGovernedQuery, dehydrateGovernedQuery } from "@lssm-tech/lib.presentation-runtime-next/data";
3
4const result = await prefetchGovernedQuery(resolver, envelope, { principal: userId });
5const dehydrated = dehydrateGovernedQuery(result, envelope); // stamps a future staleAt
6
7// Client island ('use client'): seed the SAME CacheStore → zero second fetch
8// <HydrationBoundary state={dehydrated}> wraps your ContractDataEngineProvider.
9// Build the engine with createDataEngine({ localOffline: 'offline-capable', advanced: { cacheStore } }).

Seed in dependency order

• Tenant/workspace rows and environment metadata.

• Identity rows: users, accounts, sessions, organizations, members, teams.

• RBAC rows: roles, permissions, policy bindings, and deny fixtures.

• Domain fixtures: BillingOS customers, price books, quotes, invoices, and payment evidence.

• Audit and replay rows used by governed mutation tests.