Database packages: @repo/database and @perform/db

The backend has two Prisma packages that describe the same database. Each owns a different half of the job.

	`@repo/database` (`packages/database`)	`@perform/db` (`packages/db`)
Schema file	`prisma/schema.prisma`	`prisma/schema.prisma`
Postgres schemas	`auth` and `public` (multi schema)	`public` only
Models	79: better-auth tables plus every domain model, each tagged with `@@schema(...)`	65: domain models only
Generator	`prisma-client` (output `./generated`, ESM), plus `prisma-zod-generator`	`prisma-client-js`
Owns	The runtime client and all types	Migrations and seed
Migrations folder	none	`prisma/migrations/`
Datasource URL	`DATABASE_URL`	`DIRECT_URL`, derived from `DATABASE_URL` when unset

@perform/db’s source is a single re-export:

export {
  createPrismaClient,
  db,
  Prisma,
  PrismaClient,
  PrismaClientInitializationError,
  PrismaClientKnownRequestError,
} from '@repo/database';
export type { CreatePrismaClientOptions, PrismaLogger } from '@repo/database';

So import { PrismaClient } from '@perform/db' in core-api is the client generated from packages/database. The client that packages/db generates for itself is only used by the Prisma CLI when it runs migrations.

A model change must be made in both schema files. Change only packages/db and the migration runs but the types do not know the column. Change only packages/database and the code compiles against a column the database does not have.

Changing the schema

Edit the migration schema

Change the model in packages/db/prisma/schema.prisma.

Write the migration

Add packages/db/prisma/migrations/<timestamp>_<name>/migration.sql. Migrations in this repo are hand written and applied with migrate deploy. Match Prisma’s naming so the schema and the SQL agree: quoted camelCase column names, indexes as <table>_<column>_idx, foreign keys as <table>_<column>_fkey.

Mirror the model

Make the same change in packages/database/prisma/schema.prisma. Keep the @@schema("public") line on the model. Declare every index in both files.

Regenerate and rebuild

pnpm --filter @repo/database build

That runs prisma generate, bundles with tsup and emits declarations. prisma generate does not need a database connection.

Type check the API

pnpm --filter @perform/core-api typecheck

Apply the migration

pnpm db:migrate:status
pnpm db:migrate

db:migrate is prisma migrate deploy. It applies to whatever database the env points at. Confirm the target first. Replay the full migration folder on a throwaway local Postgres before applying to a shared database.

Root scripts:

Script	Runs
`pnpm db:generate`	`prisma generate` in both packages
`pnpm db:migrate`, `pnpm db:deploy`	`prisma migrate deploy` in `@perform/db`
`pnpm db:migrate:dev`	`prisma migrate dev` in `@perform/db`
`pnpm db:migrate:status`	`prisma migrate status`
`pnpm db:seed`	`tsx prisma/seed.ts` in `@perform/db`

`createPrismaClient`

packages/database/prisma/client.ts.

export type CreatePrismaClientOptions = {
  databaseUrl: string;
  logQueries?: boolean;
  logger?: PrismaLogger;
};

export function createPrismaClient(options: CreatePrismaClientOptions): PrismaClient;

It uses Prisma 7’s driver adapter model: a pg Pool wrapped in PrismaPg.

Pool

Setting	Value
`max`	10 connections
`keepAlive`	true
`idleTimeoutMillis`	30,000
`connectionTimeoutMillis`	10,000

A pool error event (an idle client that died) is logged and does not crash the process.

Retry on transient errors

The client is extended with a $allOperations hook that retries a failed operation when the error is a transient connection problem:

Prisma codes P1001, P1002, P1008, P1017.
Messages matching “can’t reach database server”, “connection closed”, ECONNRESET, “terminating connection”, “server closed the connection”, “connection terminated”.

Delays are 200, 500, 1000 and 2000 ms, so an operation is tried up to five times. Any other error is thrown at once.

The retry wraps single operations. An interactive transaction that loses its connection partway is not replayed as a whole. Keep transactions short.

Log filtering

Prisma warn and error events go to the supplied logger, with two exceptions that are logged at debug: transient connection errors (the retry handles them) and Unique constraint failed (expected where a constraint is used for dedup).

Two client instances in one process

Instance	Created by	Logger	Used by
`ctx.prisma`	`createPrismaClient({ databaseUrl: env.DATABASE_URL, logger: log })` in `server.ts`	pino	Everything in `apps/core-api`
`db`	A singleton created when `@repo/database` is first imported, from `process.env.DATABASE_URL`	consola	`@repo/auth`, `@repo/api`, `@repo/payments`, `@repo/notifications`

Both are the same generated client class on the same database, each with its own pool of 10. Outside production the db singleton is cached on globalThis so hot reloads do not open new pools. Consequences:

Importing @repo/database without DATABASE_URL throws DATABASE_URL is not set.
A transaction on one instance cannot include a write on the other.
The process can hold up to 20 connections.

Schemas in Postgres

datasource db {
  provider = "postgresql"
  schemas  = ["auth", "public"]
}

model User {
  // ...
  @@map("user")
  @@schema("auth")
}

The models in auth are User, Session, Account, Verification, Passkey, TwoFactor, Organization, Member, Invitation, Purchase, Notification and UserNotificationPreference. Domain tables are in public. The domain model itself is documented in the data model section of these docs.

Exports of `@repo/database`

Client

createPrismaClient, db, Prisma, PrismaClient, PrismaClientInitializationError, PrismaClientKnownRequestError, and the types CreatePrismaClientOptions, PrismaLogger. Also the enums NotificationTarget and NotificationType. Catch Prisma errors by class when you need the code:

import { PrismaClientKnownRequestError } from '@perform/db';

try {
  await prisma.automationFlowRun.create({ data });
} catch (error) {
  if (error instanceof PrismaClientKnownRequestError && error.code === 'P2002') return 'skipped';
  throw error;
}

Zod schemas

prisma/zod/index.ts is generated by prisma-zod-generator and exported from the package root. It provides a Zod schema per model, for example UserSchema and PurchaseSchema, which the auth tier packages use for input types.

Query helpers

prisma/queries/ holds functions used by the auth tier. They all use the db singleton.

File	Functions
`users.ts`	`getUsers`, `countAllUsers`, `getUserById`, `getUserByEmail`, `createUser`, `getAccountById`, `createUserAccount`, `updateUser`
`organizations.ts`	`getOrganizations`, `countAllOrganizations`, `getOrganizationById`, `getOrganizationBySlug`, `getInvitationById`, `getMemberById`, `getOrganizationMembership`, `getOrganizationWithPurchasesAndMembersCount`, `countActiveCoachesByOrganizationId`, `countPlanTraineesByOrganizationId`, `getPendingInvitationByEmail`, `updateOrganization`
`purchases.ts`	`getPurchaseById`, `getPurchasesByOrganizationId`, `getPurchasesByUserId`, `getPurchaseBySubscriptionId`, `createPurchase`, `updatePurchase`, `deletePurchaseBySubscriptionId`
`notifications.ts`	`getDisabledNotificationPreferences`, `isNotificationDisabled`, `setNotificationDisabled`, `insertNotification`, `getUserEmailLocaleForNotifications`, `markNotificationAsRead`, `markNotificationsAsRead`, `markAllNotificationsAsReadForUser`, `listNotificationRowsForUser`, `countUnreadNotificationsForUser`
`admin-panel.ts`	`getAdminStudios`, `getAdminStudioDetail`, `getAdminPlatformMetrics`, `getAdminSystemHealth`, `setAdminStudioManualPlan`, and the constant `MANUAL_PURCHASE_CUSTOMER_ID`
`admin-deletion.ts`	`listStudioRows`, `getStudioDeletionPreview`, `findCrossStudioReferences`, `quiesceStudio`, `deleteStudioAndOrg`, `purgeStudio`, `getAdminDeletion`, `getUserDeletionPreview`, `deleteUserAccount`, `parkedSlug`, `AdminDeletionRefusedError` and related types

countActiveCoachesByOrganizationId and countPlanTraineesByOrganizationId read domain tables (Coach, Client) through the organization link. They are how the billing procedures learn the team and trainee counts without calling core-api.

Admin deletion

admin-deletion.ts implements the platform admin’s delete studio and delete user flows as a staged process recorded in a deletion row with status QUIESCED, PURGING, DONE or FAILED:

A preview lists what would be deleted and any blockers (for example a live billed subscription).
quiesceStudio stops the studio first: sign-in closes, pushes, API keys, hooks and automations stop.
purgeStudio deletes the data, and can be resumed if it fails partway.

A refusal throws AdminDeletionRefusedError. The oRPC procedures in @repo/api translate it for the admin panel.

Scripts in the packages

packages/db/prisma/seed.ts: the seed.
packages/db/scripts/seed-demo/: a reversible demo seeder with a teardown and a verify step.
packages/database/scripts/: one-off import tools for the food and exercise catalogs and the AutoFit migration, run through the db:import:* root scripts.

These write to whatever database the env points at. Read a script before running it.

​Changing the schema

​createPrismaClient

​Pool

​Retry on transient errors

​Log filtering

​Two client instances in one process

​Schemas in Postgres

​Exports of @repo/database

​Client

​Zod schemas

​Query helpers

​Admin deletion

​Scripts in the packages

Changing the schema

`createPrismaClient`

Pool

Retry on transient errors

Log filtering

Two client instances in one process

Schemas in Postgres

Exports of `@repo/database`

Client

Zod schemas

Query helpers

Admin deletion

Scripts in the packages