database-postgres

Postgres, SqlClient, schema, migrations, mappers

When to use: Drizzle schema, repositories, RLS, SqlClient wiring, Postgres migrations, psql / reset, or platform mappers (toDomain* / toInsertRow).

Database patterns (Postgres)

• Postgres adapter stack uses Drizzle ORM in packages/platform/db-postgres
• Domain models are independent from table/row shapes
• Mapping from DB rows to domain objects belongs in platform adapters
• Apps use SqlClient for all DB access: Boundaries provide SqlClientLive with organization context for RLS enforcement

SqlClient and row-level security (RLS)

All Postgres access flows through SqlClient—a domain-level service that abstracts database operations and enforces organization scoping via RLS.

Architecture:

• Domain Layer (@domain/shared): SqlClient interface with transaction() and query() methods
• Platform Layer (@platform/db-postgres): SqlClientLive implementation with automatic RLS context setting
• App Layer (apps/*): Boundaries provide SqlClientLive with the request's organization context

Key behaviors:

• Every transaction automatically sets app.current_organization_id session variable
• RLS policies filter all queries by this organization ID at the database level
• Nested transactions share the same connection (pass-through proxy—no nested transaction overhead)
• Domain errors propagate through Effect error channel; database errors become RepositoryError
• On effect failure, SqlClientLive still awaits the Drizzle transaction promise so the connection returns to the pool; if the driver surfaces a different error than the Effect failure (for example rollback/commit), that secondary error is logged via @repo/observability while the original failure remains the propagated error

Usage in boundaries (apps):

// packages/operations/src/operations/projects.ts
import { SqlClientLive } from "@platform/db-postgres"
import { ProjectRepositoryLive } from "@platform/db-postgres"

app.openapi(createProjectRoute, async (c) => {
  const project = await Effect.runPromise(
    createProjectUseCase(input).pipe(
      Effect.provide(ProjectRepositoryLive),
      Effect.provide(SqlClientLive(c.var.postgresClient, c.var.organization.id)),
    ),
  )
  return c.json(toProjectResponse(project), 201)
})