بنقرة واحدة
database-postgres
// Drizzle schema, repositories, RLS, SqlClient wiring, Postgres migrations, psql / reset, or platform mappers (toDomain* / toInsertRow).
// Drizzle schema, repositories, RLS, SqlClient wiring, Postgres migrations, psql / reset, or platform mappers (toDomain* / toInsertRow).
Preparing a production release, pushing a vX.Y.Z release tag, running scripts/release.sh, or updating CHANGELOG.md with the changes that are about to be deployed to production.
apps/web UI — routes, @repo/ui, TanStack Start server functions and collections, navigation (Link vs useNavigate), forms (useForm + createFormSubmitHandler + fieldErrorsAsStrings for Zod field errors), Tailwind layout rules, design-system updates, and useEffect / useMountEffect policy.
Multi-channel notifications. Adding a new notification kind, group, or channel; in-app + email delivery; per-user prefs; project-level gates; idempotency.
Review the current conversation context and git changes, then persist durable repository knowledge into `dev-docs/*.md` by domain and into `AGENTS.md` for cross-cutting repo rules. Use after features, fixes, refactors, architecture changes, schema changes, or when the user mentions docs, documentation, design, architecture, business logic, conventions, or `AGENTS.md`.
ClickHouse queries, Goose migrations, chdb test schema, or telemetry storage paths.
Adding or changing routes in `apps/api`. One source of truth (`defineApiEndpoint` + a Zod schema) becomes an HTTP endpoint, an OpenAPI operation, an MCP tool, and a TS SDK method — descriptions and contracts must be written with all four readers in mind.
| name | database-postgres |
| description | Drizzle schema, repositories, RLS, SqlClient wiring, Postgres migrations, psql / reset, or platform mappers (toDomain* / toInsertRow). |
When to use: Drizzle schema, repositories, RLS, SqlClient wiring, Postgres migrations, psql / reset, or platform mappers (toDomain* / toInsertRow).
packages/platform/db-postgresSqlClientLive with organization context for RLS enforcementAll Postgres access flows through SqlClient—a domain-level service that abstracts database operations and enforces organization scoping via RLS.
Architecture:
@domain/shared): SqlClient interface with transaction() and query() methods@platform/db-postgres): SqlClientLive implementation with automatic RLS context settingapps/*): Boundaries provide SqlClientLive with the request's organization contextKey behaviors:
app.current_organization_id session variableRepositoryErrorSqlClientLive 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 errorUsage in boundaries (apps):
// apps/api/src/routes/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.
organizationId from the RLS context, not from method paramsThe 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:
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.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.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.
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.
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.
latitudeSchema — never create a local pgSchema("latitude"). Import latitudeSchema from ../schemaHelpers.ts.cuid("id").primaryKey() — every table's primary key must use the cuid() helper (varchar(24) with auto-generated CUID2).tzTimestamp(name) — never use raw timestamp(name, { withTimezone: true }). Import tzTimestamp from the helpers....timestamps() — every table that has createdAt/updatedAt must spread the timestamps() helper (includes $onUpdateFn on updatedAt).organizationRLSPolicy(tableName) — every table with an organization_id column must include this helper in its third argument to enable row-level security..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)).// ✅ 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")],
)
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:
# 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:
"add users table" → add-users-table)ALTER TABLE migrations over bespoke backfill choreography unless the change truly requires data rewriting.IF NOT EXISTS in custom SQL for idempotencydrizzle.__drizzle_migrations tableDomain repository ports and method naming conventions (including Effect result shapes and when to use listBy* vs findBy*) live in dev-docs/repositories.md. Prefer that vocabulary for new Postgres-backed ports and when renaming existing methods.
When writing toDomain* and toInsertRow functions in platform repositories:
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.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.?? 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.