Data Layer

All database access is centralized in packages/db. The key design: Drizzle manages the schema, Kysely runs the queries.

Architecture Overview

graph LR
  Schema["Drizzle Schema<br/>(tables, relations, enums)"] -->|type bridge| Types["drizzle-to-kysely.ts<br/>(snake_case types)"]
  Types --> Kysely["Kysely Instance<br/>(runtime queries)"]
  Kysely --> Repos["Repositories<br/>(createDbRepo)"]
  Repos -->|"Safe&lt;T&gt;"| Consumers["tRPC / Auth / Services"]
  DrizzleKit["Drizzle Kit"] -->|generate/migrate| PG["PostgreSQL"]
  Kysely --> PG

Schema (Drizzle)

Tables are defined in packages/db/src/drizzle/schema/tables/, each in its own folder:

packages/db/src/drizzle/schema/
├── schema.ts               # Barrel: re-exports all tables, relations, enums
├── enums/                   # PostgreSQL enums
│   └── *.enum.ts
├── tables/
│   ├── patients/
│   │   ├── patients.table.ts       # pgTable definition + types
│   │   └── patients.relations.ts   # Drizzle relations
│   ├── audit-templates/
│   │   ├── audit-templates.table.ts
│   │   └── audit-templates.relations.ts
│   └── ...
└── auth.schema.ts           # Supabase auth schema helpers
Copy

A table definition looks like:

// packages/db/src/drizzle/schema/tables/patients/patients.table.ts
import { pgTable, text, timestamp, integer } from "drizzle-orm/pg-core";

export type PatientId = Id<string, "PatientId">;
export const parsePatientId = createIdParser<PatientId>("uuid");
export const zPatientId = zId(parsePatientId);

export const patients = pgTable("patient", {
  patient_id: text("patient_id").$type<PatientId>().primaryKey(),
  first_name: text("first_name").notNull(),
  last_name: text("last_name").notNull(),
  entity_id: text("entity_id").$type<EntityId>().notNull().references(() => entities.entity_id),
  // ...
});
Copy

Key conventions:

• Table columns use snake_case (matching PostgreSQL).
• IDs use branded types (e.g. PatientId) via the Id<Type, TagName> utility.
• Each ID has a parser (parsePatientId) and Zod schema (zPatientId).
• RLS policies can be defined inline with pgPolicy where needed.

Type Bridge: Drizzle to Kysely

Since Drizzle defines the schema and Kysely runs the queries, types must be bridged. This is handled in packages/db/src/drizzle/drizzle-to-kysely.ts:

• Table keys are PostgreSQL table names (snake_case).
• Row types are snake_cased versions of Drizzle's $inferSelect.
• The resulting DrizzleToKyesely type is used to create the Kysely instance.

Helper types in packages/db/src/db.types.ts:

export type TableRow<K extends keyof DrizzleToKyesely> = Selectable<DrizzleToKyesely[K]>;
export type TableInsert<K extends keyof DrizzleToKyesely> = Insertable<DrizzleToKyesely[K]>;
export type TableUpdate<K extends keyof DrizzleToKyesely> = Updateable<DrizzleToKyesely[K]>;
Copy

Branded IDs

IDs are branded to prevent accidentally passing a PatientId where a TemplateId is expected:

import type { Id } from "@repo/db/db.types";

type PatientId = Id<string, "PatientId">;
type TemplateId = Id<string, "TemplateId">;

// Compile error: Type 'PatientId' is not assignable to type 'TemplateId'
const templateId: TemplateId = patientId;
Copy

Each branded ID comes with:

• Parser: parsePatientId(rawValue) -- coerces to the branded type.
• Zod schema: zPatientId -- validates and transforms in Zod pipelines (used in tRPC inputs).

Repository Pattern

All database queries live in repositories created with createDbRepo():

// packages/db/src/repos/patients.repo.ts
import { createDbRepo } from "../lib/db-repo";

export const patientsRepo = createDbRepo("patients", ({ db }) => ({
  getById: async (id: PatientId) => {
    return await db
      .selectFrom("patient")
      .selectAll()
      .where("patient_id", "=", id)
      .executeTakeFirstOrThrow();
  },

  create: async (params: PatientInsertParams) => {
    return await db
      .insertInto("patient")
      .values(params)
      .returningAll()
      .executeTakeFirstOrThrow();
  },

  listInOrganization: async (params: ListPatientsInOrganizationParams) => {
    let query = db
      .selectFrom("patient")
      .leftJoin("profiles", (join) =>
        join.onRef("patient.primary_provider_id", "=", "profiles.id"))
      .where("patient.entity_id", "=", params.entityId);

    // Dynamic filtering
    if (params.patientSearch?.trim()) {
      const pattern = `%${params.patientSearch.trim()}%`;
      query = query.where((eb) =>
        eb.or([
          eb("patient.first_name", "ilike", pattern),
          eb("patient.last_name", "ilike", pattern),
        ]),
      );
    }

    return await query.select([/* columns */]).execute();
  },
}));
Copy

How createDbRepo Works

1. You define plain methods that receive { db, supabase } and return data directly (throw on error).
2. createDbRepo wraps every method to return Safe<T> automatically.
3. Errors are mapped:
   • NoResultError (Kysely) -> DbNotFoundError
   • Connection errors -> DbConnectionError (re-thrown, not wrapped in Safe)
   • PostgrestError -> UnhandledPostgresError
   • Other errors -> UnhandledError
4. The withOptions({ supabase }) method lets you swap the Supabase client (useful in tests).

Consuming Repos

// In a tRPC procedure
const { data: patient, error } = await patientsRepo.getById(input.id);

if (error) {
  throw new TRPCError({ code: "NOT_FOUND", message: "Patient not found" });
}

return patient;
Copy

Package Exports

Migrations

Migrations use Drizzle Kit and live in packages/db/src/drizzle/codegen/:

# Generate a migration from schema changes
pnpm --filter @repo/db generate

# Apply pending migrations
pnpm --filter @repo/db migrate
Copy

The Drizzle Kit config (packages/db/drizzle.config.ts) points at the local Supabase instance by default.

Rules

• Never call database operations client-side. Repos are server-only.
• Use Kysely for queries, not the Drizzle query API. Drizzle is for schema and migrations only.
• All repo methods return Safe<T>. Check error before using data.
• Use branded IDs for type safety. Never pass raw strings where an ID type is expected.

Next: tRPC