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):

// 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)
})

// 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):

// 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.

// 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.

// 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:

// 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:

docker compose exec postgres psql -U latitude -d latitude_development

Reset only the Postgres volume (without affecting other services):

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.