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

13 models. There is no separate Membership model — User has a direct org_id and role (OWNER/ADMIN/EDITOR/VIEWER). There is no separate Clause model either — clauses are a JSON array (content: {clauses: [...]}) on DocumentVersion. Signer decisions land in SigningRequest.assent_map, a {clause_id: boolean} JSON map — there’s no SigningResponse model.

Organisation ──┬── User (org_id + role directly on User, no join table)
               ├── Container
               │     └── Document
               │           └── DocumentVersion (content JSON holds clauses)
               │                 └── SigningRequest (assent_map JSON holds decisions)
               │                       └── SignatureEvent (audit trail: VIEWED/SUBMITTED/VOIDED/EXPIRED)
               ├── Invitation (team invites)
               ├── ApiKey
               ├── Webhook
               │     └── WebhookDelivery
               ├── BatchJob (bulk CSV signing invites)
               └── OrgMonthlyUsage (usage tracking for tier limits)

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" });