Database

Overview

TheTerms uses Prisma 6 with PostgreSQL. The schema is defined in packages/db/prisma/schema.prisma. All database access goes through the Prisma client — no raw SQL.

Schema

The core entities and their relationships:

Organisation ──┬── User (via Membership with Role)
               ├── Container
               │     └── Document
               │           └── DocumentVersion
               │                 ├── Clause
               │                 └── SigningRequest
               │                       └── SigningResponse (per clause)
               ├── Invitation (team invites)
               └── ApiKey

Running Migrations

For development (schema changes you’ll commit)

pnpm db:migrate --name describe-your-change

This generates a SQL migration file in packages/db/prisma/migrations/. Always commit migration files with your PR.

Caution

Never use db:push for changes meant to be committed. db:push applies schema changes directly without creating a migration file. It’s fine for local experimentation, but production deployments rely on migration files.

For initial local setup

pnpm db:push

This applies the schema directly to your local database. Suitable for first-time setup.

For production (inside Docker)

Migrations run automatically on container startup via the docker-entrypoint.sh script, which calls:

prisma migrate deploy --schema=./packages/db/prisma/schema.prisma

Prisma Studio

To visually browse your local database:

cd packages/db
npx prisma studio

This opens a web interface at http://localhost:5555.

Common Patterns

Double-cast for JSON fields

Prisma’s Json type doesn’t overlap with specific TypeScript interfaces. When reading JSON fields, use a double-cast:

const content = document.content as unknown as DocumentContent;

findUnique over findUniqueOrThrow

Prefer findUnique + explicit TRPCError over findUniqueOrThrow for better error messages:

const container = await prisma.container.findUnique({ where: { id } });
if (!container) throw new TRPCError({ code: "NOT_FOUND", message: "Container not found" });