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 - **Never write to an RLS table through `postgresClient.pool` or `.db`.** `app.current_organization_id` is set with `set_config(..., true)`, so it is transaction-local and a raw pool query has none: `get_current_organization_id()` returns `NULL`, the policy matches nothing, and the statement **updates zero rows and raises no error**. The runtime role (`latitude_app`) is not the table owner and no table uses `FORCE ROW LEVEL SECURITY`, so this passes locally as the owner and silently no-ops in every deployed environment. Add a repository method and go through `SqlClient`. Raw `pool` access is for the admin client (`getAdminPostgresClient()`), scripts, seeds and tables with no RLS (`outbox_events`) **Usage in boundaries (apps):** ```typescript // 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) }) ``` ```typescript // apps/web/src/domains/projects/projects.functions.ts import { getPostgresClient } from "../../server/clients.ts" export const createProject = createServerFn({ method: "POST" }) .handler(async ({ data }) => { const { organizationId } = await requireSession() const client = getPostgresClient() const project = await Effect.runPromise( createProjectUseCase({...}).pipe( Effect.provide(ProjectRepositoryLive), Effect.provide(SqlClientLive(client, organizationId)), ) ) return toRecord(project) }) ``` **Usage in use-cases (multi-operation transactions):** ```typescript // packages/domain/auth/src/use-cases/complete-auth-intent.ts export const completeAuthIntentUseCase = (input) => Effect.gen(function* () { const sqlClient = yield* SqlClient yield* sqlClient.transaction(handleIntentByType(intent, input.session)) }) const handleSignup = (intent, session) => Effect.gen(function* () { const users = yield* UserRepository const memberships = yield* MembershipRepository const organization = yield* createOrganizationUseCase({...}) yield* memberships.save(createMembership({...})) yield* users.setNameIfMissing({...}) }) ``` **Usage in repositories (single operations):** Repository methods must resolve `SqlClient` inside each call — never capture it at layer build. See the "Never capture scope-bound services at layer build" rule in the **Effect and errors** skill. ```typescript // packages/platform/db-postgres/src/repositories/project-repository.ts export const ProjectRepositoryLive = Layer.effect( ProjectRepository, Effect.gen(function* () { return { findById: (id) => Effect.gen(function* () { const sqlClient = (yield* SqlClient) as SqlClientShape<Operator> return yield* sqlClient .query((db, organizationId) => db .select() .from(projects) .where(and(eq(projects.organizationId, organizationId), eq(projects.id, id))) .limit(1), ) .pipe(Effect.flatMap(...)) }), save: (project) => Effect.gen(function* () { const sqlClient = (yield* SqlClient) as SqlClientShape<Operator> yield* sqlClient.query((db, organizationId) => db.insert(projects).values({ ...row, organizationId }).onConflictDoUpdate({...}) ) }), } }) ) ``` The layer-build effect doesn't `yield* SqlClient` at all — the dependency is declared via each method's `R` channel, and resolved per call. A build-time yield is redundant and (if captured) re-introduces the very bug this pattern avoids. ### Pull `organizationId` from the RLS context, not from method params The `query((db, organizationId) => …)` callback receives the active organization id from the `SqlClient`'s RLS context. **Use that value** in `WHERE` predicates and `INSERT … VALUES` rows. Don't accept `organizationId` as a parameter on the repository method just to re-thread it into the SQL. Why: - **Consistency** — every repo call is scoped the same way regardless of which caller invokes it. Use-cases don't get to pick a different org from the one their request authenticated against. - **Defense in depth alongside RLS** — RLS already filters rows by `app.current_organization_id`, but the explicit predicate makes intent obvious in the query plan and catches accidental "I forgot RLS is on" mistakes during code review. - **Insert safety** — for `create`/`save` methods, writing the RLS-supplied org id (instead of trusting `entity.organizationId`) prevents a caller from fabricating an entity for a different org and inserting it through the right org's transaction. ```ts // Good — orgId comes from RLS, name reflects the actual action. findMemberByEmail: (email: string) => Effect.gen(function* () { const sqlClient = (yield* SqlClient) as SqlClientShape<Operator> return yield* sqlClient.query((db, organizationId) => db .select({ id: members.id }) .from(members) .where(and(eq(members.organizationId, organizationId), eq(members.email, email.toLowerCase()))) .limit(1), ) }), create: (invitation: Invitation) => Effect.gen(function* () { const sqlClient = (yield* SqlClient) as SqlClientShape<Operator> yield* sqlClient.query((db, organizationId) => db.insert(invitations).values({ ...row, organizationId }), ) }), // Bad — orgId is a redundant input the caller could mis-pass. findMemberByEmail: ({ email, organizationId }: { email: string; organizationId: OrganizationId }) => /* … query((db) => …where(eq(members.organizationId, organizationId))) */, // Bad — trusts the entity for the inserted org id; nothing stops a caller // from passing an entity for a different org through this org's transaction. create: (invitation: Invitation) => /* … query((db) => db.insert(invitations).values({ ...invitation })) */, ``` Naming: if dropping the explicit param makes the method's name redundant (e.g. `listPendingByOrganizationId` → `listPending`), rename it. The repository contract should describe what the method *does*, not which scope it's bound to — the scope is the RLS context by construction. **Exceptions** — methods that legitimately operate outside the current RLS org are rare and should be obvious from the name and a comment: - `findPublicPendingPreviewById(invitationId)` — invite landing pages query before the invitee has authenticated, so there is no RLS context to lean on. Document the cross-org scope explicitly. - Admin/maintenance scripts that go through `withAdmin(...)` rather than `withPostgres(...)`. The repository port's method signatures must list `SqlClient` in their `R` channel: ```typescript // packages/domain/projects/src/ports/project-repository.ts export interface ProjectRepositoryShape { findById(id: ProjectId): Effect.Effect<Project, NotFoundError | RepositoryError, SqlClient> save(project: Project): Effect.Effect<void, RepositoryError, SqlClient> } ``` `SqlClient` is marked `@effect-leakable-service` in `@domain/shared`, so the Effect linter accepts this intentional leak. Callers already have `SqlClient` in their `R` (via `withPostgres(...)` at the boundary), so the leak is invisible to them. ## Postgres management Connect to the development database: ```bash docker compose exec postgres psql -U latitude -d latitude_development ``` Reset only the Postgres volume (without affecting other services): ```bash pnpm --filter @platform/db-postgres pg:reset ``` This runs `docker/reset-postgres.sh` which stops postgres, removes the `data-llm_postgres_data` volume, restarts postgres, waits for it to be ready, runs migrations, and seeds the database. ## Postgres schema conventions All Drizzle table definitions in `packages/platform/db-postgres/src/schema/` **must** follow these rules. Shared helpers live in `schemaHelpers.ts`. Organization-scoped Postgres tables must use the repository RLS conventions. 1. **Use `latitudeSchema`** — never create a local `pgSchema("latitude")`. Import `latitudeSchema` from `../schemaHelpers.ts`. 2. **Use `cuid("id").primaryKey()`** — every table's primary key must use the `cuid()` helper (`varchar(24)` with auto-generated CUID2). 3. **Use `tzTimestamp(name)`** — never use raw `timestamp(name, { withTimezone: true })`. Import `tzTimestamp` from the helpers. 4. **Use `...timestamps()`** — every table that has `createdAt`/`updatedAt` must spread the `timestamps()` helper (includes `$onUpdateFn` on `updatedAt`). 5. **Use `organizationRLSPolicy(tableName)`** — every table with an `organization_id` column must include this helper in its third argument to enable row-level security. 6. **No foreign keys** — new Postgres tables must not add foreign key constraints. Do not use `.references()` or manually create `FOREIGN KEY` constraints. Referential integrity is enforced at the application/domain layer. Use indexes on relationship columns instead (e.g. `index().on(t.datasetId)` rather than `.references(() => datasets.id)`). ```typescript // ✅ Good - follows all conventions export const projects = latitudeSchema.table( "projects", { id: cuid("id").primaryKey(), organizationId: text("organization_id").notNull(), name: varchar("name", { length: 256 }).notNull(), deletedAt: tzTimestamp("deleted_at"), ...timestamps(), }, () => [organizationRLSPolicy("projects")], ) ``` ## Database migrations (Drizzle Kit) ### Migration execution safety (agents) Do **not** run Postgres migration commands (`pg:generate`, `pg:generate:custom`, `pg:migrate`, etc.) unless the user explicitly asked in this conversation. If migrations are needed but not requested, explain and wait for confirmation. ClickHouse / Weaviate follow the same policy in their respective skills. **Always use drizzle-kit for migrations.** Never create manual SQL files in the drizzle folder. **Schema changes:** ```bash # Generate migration from schema changes pnpm --filter @platform/db-postgres pg:generate "<name>" # Create empty migration for custom SQL (RLS policies, seed data, etc.) pnpm --filter @platform/db-postgres pg:generate:custom "<name>" # Apply migrations pnpm --filter @platform/db-postgres pg:migrate ``` **Key points:** - Name is slugified automatically; always quote multi-word names (e.g. `"add users table"` → `add-users-table`) - Postgres migration history is append-only in this repository. Do not edit existing Drizzle migration files; change the schema and generate a new migration instead. - For additive changes to existing tables, prefer ordinary generated `ALTER TABLE` migrations over bespoke backfill choreography unless the change truly requires data rewriting. - Never manually create SQL files in the drizzle folder - Use `IF NOT EXISTS` in custom SQL for idempotency - Migrations are tracked in `drizzle.__drizzle_migrations` table ## Repository port naming Domain repository ports and method naming conventions (including Effect result shapes and when to use `listBy`* vs `findBy*`) live in [dev-docs/repositories.md](../../../dev-docs/repositories.md). Prefer that vocabulary for new Postgres-backed ports and when renaming existing methods. ## Mapper conventions When writing `toDomain*` and `toInsertRow` functions in platform repositories: - **Never hardcode field values.** Every field on the domain entity must be read from the DB row (`row.fieldName`), not assigned a literal (`null`, `""`, `new Date()`). If a field has no backing column, that is a schema gap — add the column or remove the field from the domain type. - **Never use `as EntityType` casts** on mapper return values. These bypass TypeScript's structural check and hide type mismatches. Let the return type be inferred or explicitly annotated — the compiler will catch missing or incompatible fields. - **Never coerce nullable columns** with `?? fallback` to satisfy a non-nullable domain type. Surface the mismatch: either make the column `notNull()` or make the domain field nullable. - `**toInsertRow` must round-trip.** Every field written by `toInsertRow` should be readable by `toDomain`*, and vice versa. A field present in the domain type but absent from `toInsertRow` means data is silently discarded on write.