Database & drizzle-kit

The data layer has four jobs, each in its own file, and it pays to keep them separate in your head:

1. Describe the tables — Drizzle schema in packages/db/src/schema/.
2. Decide who may read/write each row — Postgres RLS policies (shipped as migration SQL) plus the withRls runtime helper in packages/db/src/rls.ts.
3. Connect to Postgres from Effect — the PgDrizzle layer in packages/db/src/effect/layer.ts, driven by Config.
4. Query safely — one Effect repo per table in packages/db/src/effect/repos/, wrapping Drizzle query builders.

Then there's the operational half: drizzle-kit turns your schema into migrations and applies them. That's where the sharp edges live, so it gets its own section at the end.

1. The Drizzle schema

A table is a plain pgTable call. Here is the whole todo table (packages/db/src/schema/todo.ts):

export const todo = pgTable("todo", {
  id: serial("id").primaryKey(),
  text: text("text").notNull(),
  completed: boolean("completed").default(false).notNull(),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  // Optional trip these todos belong to; the todo outlives the trip (SET NULL).
  travelId: integer("travel_id").references(() => travel.id, { onDelete: "set null" }),
});

Read it column by column:

• serial("id").primaryKey() — an auto-incrementing integer PK.
• .notNull() / .default(false) map straight to the SQL constraints, so the guarantee lives in the database, not in app code.
• .references(() => user.id, { onDelete: "cascade" }) is a real foreign key. The () => thunk is deliberate — it defers resolving user so two tables can reference each other without an import cycle. cascade deletes a user's todos with the user; set null on travelId keeps the todo when its trip is deleted (the comment in the file spells out that intent).

Relations are declared separately with defineRelations, which is what powers Drizzle's typed relational queries:

export const todoRelations = defineRelations({ todo, travel }, (r) => ({
  travel: {
    todos: r.many.todo(),
  },
  todo: {
    travel: r.one.travel({
      from: r.todo.travelId,
      to: r.travel.id,
    }),
  },
}));

from/to name the join columns explicitly — todo.travelId → travel.id — so a travel row exposes .todos and a todo exposes its .travel.

2. Row-Level Security — two halves

RLS is where this repo's Supabase heritage shows. There are two pieces and they are easy to conflate:

The policies (Postgres SQL, in migrations)

The actual policies are plain SQL, shipped as a migration. This is from packages/db/src/migrations/20260413170640_rls_anon_and_auth_tables/migration.sql:

-- auth_user_id(): read sub claim set by withRls
CREATE OR REPLACE FUNCTION auth_user_id() RETURNS text AS $$
  SELECT current_setting('request.jwt.claims', true)::jsonb ->> 'sub'
$$ LANGUAGE sql STABLE;

CREATE POLICY "order: insert authenticated" ON "order"
  FOR INSERT WITH CHECK (
    auth_role() = 'authenticated' AND user_id = auth_user_id()
  );

The policy reads request.jwt.claims — a per-transaction Postgres setting — and compares the sub (user id) claim against the row's user_id. Postgres itself enforces this on every query; app code cannot forget to add a WHERE user_id = …. That's the whole point of RLS: the guarantee is in the database.

The runtime helper that sets the claims

For a policy to see a user, something must set request.jwt.claims before the query runs. That's withRls in packages/db/src/rls.ts:

export async function withRls<T>(
  database: NodePgDatabase,
  userId: null | string,
  callback: (tx: NodePgDatabase) => Promise<T>
): Promise<T> {
  return database.transaction(async (tx) => {
    const role = resolveRlsRole(userId);
    assertRlsRole(role);
    const claims = userId
      ? JSON.stringify({ sub: userId, role: "authenticated" })
      : JSON.stringify({ role: "anon" });
    await tx.execute(sql`SET LOCAL ROLE ${sql.raw(role)}`);
    await tx.execute(sql`SELECT set_config('request.jwt.claims', ${claims}, true)`);
    return callback(tx);
  });
}

Why every line matters:

• Everything runs inside database.transaction(...) because the settings must be transaction-local — the true third argument to set_config (and SET LOCAL ROLE) scopes them to this transaction only, so a pooled connection never leaks one user's identity into the next request's query.
• resolveRlsRole maps a nullable userId to "authenticated" or "anon", and assertRlsRole is an asserts role is RlsRole guard — a type-safe whitelist, so sql.raw(role) (raw string interpolation) can never inject an arbitrary role name.
• The JSON claims mirror exactly what the SQL functions above read back: sub → auth_user_id(), role → auth_role().

3. The Effect DB layer — Config-driven connection

The Effect side connects through packages/db/src/effect/layer.ts. The connection string is read via Effect's Config, not process.env:

export const PgLive = PgClient.layerConfig({
  url: Config.redacted("DATABASE_URL"),
  idleTimeout: Config.succeed(PG_POOL_RESILIENCE.idleTimeout),
  connectionTTL: Config.succeed(PG_POOL_RESILIENCE.connectionTTL),
  connectTimeout: Config.succeed(PG_POOL_RESILIENCE.connectTimeout),
  maxConnections: Config.succeed(PG_POOL_RESILIENCE.maxConnections),
});

• Config.redacted("DATABASE_URL") reads the env var through Effect's config system and wraps it in a Redacted so the connection string never lands in a log line or error dump by accident. Same pattern as everywhere else in the Effect codebase — no raw process.env in service code.
• The pool tuning is not decoration. The comment in the file explains it: the runtime's PgClient pool is a long-lived globalThis singleton, so without idleTimeout / connectionTTL recycling it eventually hands a query a socket the Supavisor pooler already dropped, surfacing as "connection terminated". And maxConnections: 8 is capped so this pool plus the better-auth pool stay under the pooler's session-mode pool_size of 15 — oversubscribe it and every authed call hits EMAXCONNSESSION.

The Drizzle surface itself is a canonical Effect service:

export class PgDrizzle extends Context.Service<PgDrizzle>()("@repo/db/PgDrizzle", {
  make: makeWithDefaults(),
}) {
  static readonly layer = Layer.effect(this, this.make).pipe(Layer.provide(PgLive));
}

The Context.Service + static layer shape is the house pattern (see the Effect v4 guide). The payoff is in the doc comment: because it's backed by drizzle-orm/effect-postgres, the query builders (db.select()…, db.insert()…) are Effects — they extend Effect.Effect<Row[], …> directly, so you never wrap a query in Effect.tryPromise. PgDrizzle.layer wires the Drizzle service on top of PgLive so consumers get both from one layer.

4. An Effect repo wrapping Drizzle

Each table gets a repo — a Context.Service whose make yields PgDrizzle and returns a bag of query functions. From packages/db/src/effect/repos/todo-repo.ts:

export class TodoRepo extends Context.Service<TodoRepo>()("@repo/db/TodoRepo", {
  make: Effect.gen(function* () {
    const db = yield* PgDrizzle;

    const findById = Effect.fnUntraced(function* (id: number) {
      const rows = yield* db.select().from(todo).where(eq(todo.id, id)).limit(1);
      if (rows[0] === undefined) {
        return yield* new DbNotFoundError({ table: "todo", id: String(id) });
      }
      return rows[0];
    }, retryOnStaleConnection);
    // …create, toggle, deleteById, listForUser …
    return { create, deleteById, findById /* … */ } as const;
  }),
}) {
  static readonly layerWith = pgRepoLayerWith(this);
  static readonly layer = this.layerWith(PgDrizzle.layer);
}

What each part is doing:

• const db = yield* PgDrizzle pulls the Drizzle service out of context once, at construction, and closes over it. Every query function reuses it.
• yield* db.select()… — no Effect.tryPromise. The query builder is already an Effect, so yield* runs it and hands back typed rows.
• A missing row is a typed error, not a thrown exception or a silent undefined: return yield* new DbNotFoundError({ … }). DbNotFoundError is a Schema.TaggedErrorClass, so a consumer handles it with Effect.catchTag("DbNotFoundError", …) and TypeScript knows it can occur.
• Every function is wrapped in retryOnStaleConnection (from packages/db/src/effect/connection-retry.ts). That retries only connection-class failures (a dead pooled socket, EMAXCONN, "connection terminated") with bounded exponential backoff — a deterministic failure like a unique-constraint violation is left to fail immediately, because retrying it would just fail identically. This is the query-time complement to the pool recycling in layer.ts.

The layerWith boilerplate is factored out once in packages/db/src/effect/repos/pg-repo-layer.ts — every PgDrizzle-only repo's layer wiring was byte-identical apart from the class name:

export const pgRepoLayerWith =
  <Self, Id extends string, Shape>(
    service: Context.ServiceClass<Self, Id, Shape> & {
      readonly make: Effect.Effect<Shape, never, PgDrizzle>;
    }
  ) =>
  <PE, PR>(pgDrizzleLayer: Layer.Layer<PgDrizzle, PE, PR>): Layer.Layer<Self, PE, PR> =>
    Layer.effect(service, service.make).pipe(Layer.provide(pgDrizzleLayer));

Passing this as a value (pgRepoLayerWith(this) in the class body) lets Self/Shape be inferred from the argument, which sidesteps the explicit-type friction a type-parameter-only helper would hit.

5. The drizzle-kit workflow

drizzle.config.ts tells drizzle-kit where the schema is and where migrations go (packages/db/drizzle.config.ts):

export default defineConfig({
  schema: [
    "./src/schema/agent-plan.ts",
    // … every schema file listed explicitly …
    "./src/schema/todo.ts",
    "./src/schema/travel.ts",
  ],
  out: "./src/migrations",
  dialect: "postgresql",
  dbCredentials: { url: keys().DATABASE_URL },
});

Note the schema files are listed one by one, not via a glob. The comment in the config explains why blog.ts is deliberately absent — it's a pure re-export module, and listing it would make drizzle-kit re-scan its re-exported tables as duplicates. When you add a new schema file, add it to this array.

Three commands, three very different jobs (run from the repo root; they proxy to @repo/db via Turbo):

# 1. Diff schema against the last migration -> write a NEW migration SQL file.
#    Reviews as a normal code change. Applies nothing.
pnpm db:generate

# 2. Apply pending migration files to the database in order, recording each in
#    drizzle.__drizzle_migrations. This is what prod runs.
pnpm db:migrate

# 3. Diff schema against the LIVE database and mutate it in place. No migration
#    file. DEV ONLY — rapid local iteration on a throwaway DB.
pnpm db:push

The normal loop is: edit a schema file → pnpm db:generate → read the generated SQL → commit it → pnpm db:migrate applies it. db:push is a shortcut for a local database you don't mind rewriting; it never produces a reviewable artifact.

See also

• How a request flows — watch a repo call run inside a real end-to-end request.
• Learn Effect v4 — the Context.Service, Config, and typed-error patterns this layer is built on.