Database (packages/infra/db)

The database package is the only package that may import drizzle-orm. It manages the PostgreSQL schema, migrations, repositories, Effect schemas, and test factories.

Structure

packages/infra/db/
  src/
    schema.ts              # Drizzle table definitions
    json-schema.ts         # JSON/JSONB type helpers shared by effect-schemas
    effect-schemas/        # One Effect schema per table
    factories/             # One test factory per table
    repositories/          # Concrete persistence implementations
    env.ts                 # Database connection config
  drizzle/
    migrations/            # SQL migration files
  pgtap/                   # pgTAP SQL test suites

Schema

All tables are defined using Drizzle's pgTable in source files under src/. Each table must be declared as an exported constant:

export const organizations = pgTable('organizations', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  billingEmail: text('billing_email'),
  isSubscribed: boolean('is_subscribed').notNull().default(false),
  subscriptionStatus: subscriptionStatusEnum('subscription_status').notNull().default('inactive'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow()
})

Aliasing pgTable or wrapping it in local variables is forbidden for invariant safety.

Effect Schemas (Parity-Enforced)

Each database table must have a corresponding file in src/effect-schemas/:

src/effect-schemas/
  organizations.ts # OrganizationsRowSchema + OrganizationsRowShape
  users.ts         # UsersRowSchema + UsersRowShape
  invitations.ts   # InvitationsRowSchema + InvitationsRowShape
  index.ts         # Re-exports all schemas

Each schema file must import from effect/Schema, export a *RowSchema constant, and export a *RowShape type.

The structural invariant checker validates 1:1 parity between tables and effect schema files.

Factories (Parity-Enforced)

Each table must have a corresponding factory in src/factories/:

src/factories/
  organizations.factory.ts
  users.factory.ts
  invitations.factory.ts
  index.ts

Each factory must export a create*Factory function. The invariant checker validates parity between tables and factory files.

Repositories

Concrete persistence implementations live in src/repositories/. These are the only files in the system that execute database queries:

// Repositories must:
// 1. Import matching Effect schema decoders
// 2. Decode DB row results via Effect schema
// 3. Execute queries through the Effect DB provider (provideDB)
// 4. Not use Promise chaining (.then())

Repository implementations satisfy the abstract port contracts defined in packages/core/src/domains/*/ports/.

Domain Event Transaction Helper

Repositories that write domain events as part of a business operation must use the shared insertDomainEventInTransaction helper exported from repositories/domain-events.ts. The helper accepts a transaction handle and an event input, auto-incrementing the per-aggregate sequenceNumber:

import { insertDomainEventInTransaction } from './domain-events.js'

// Inside a transaction:
yield* insertDomainEventInTransaction(trx, {
  eventType: input.event.eventType,
  aggregateType: input.event.aggregateType,
  aggregateId: org.id,
  payload: input.event.payload,
  correlationId: input.event.correlationId ?? null
})

Inline .insert(domainEvents).values(...) calls in any repository file other than domain-events.ts are banned. This is enforced by the structural lint rule Rule 12 in scripts/lint/enforce-domain-event-contracts.mjs.

System Settings

The system_settings table stores application-wide configuration as typed JSONB values. It was introduced in migration 0029_system_settings.sql, and boilerplate defaults are now reconciled through the generated desired-state schema packages/infra/db/schemas/system-settings/reconcile_retention_settings.sql:

export const systemSettings = pgTable('system_settings', {
  key: text('key').primaryKey(),
  value: jsonb('value').$type<JsonObject>().notNull(),
  description: text('description'),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull()
})

The systemSettingsRepository in src/repositories/system-settings.ts provides three methods:

Data Retention

Several repositories expose prune* methods that delete rows older than a given threshold. These methods are designed to be called from scheduled Temporal workflows or maintenance scripts:

Retention thresholds are configured via the retention_settings key in the system_settings table. The committed desired-state value is generated from packages/infra/db/src/system-settings-defaults.ts. Each entry maps a logical table name to an enabled flag and a retention_days number:

{
  "audit_events": { "enabled": true, "retention_days": 90 },
  "invitations": { "enabled": true, "retention_days": 180 },
  "domain_events": { "enabled": true, "retention_days": 30 }
}

Use systemSettingsRepository.getRetentionSettings() to read the current configuration.

Migrations

Database migrations are managed by Drizzle and stored in drizzle/migrations/. Run migrations with:

pnpm db:migrate
pnpm db:schemas:apply

pgTAP Trigger Suites

Database triggers defined in migrations must have corresponding pgTAP test coverage in pgtap/*.sql. The invariant checker scans all CREATE TRIGGER statements in migrations and verifies each trigger name appears in the pgTAP suites.

# Run pgTAP suites
pnpm test:db:pgtap

Drizzle Isolation

The drizzle-orm import is restricted to this package only. All other packages and apps must interact with the database through the repository layer. This is enforced by ESLint's no-restricted-imports rule.