Database

Prisma 7 with automatic multi-tenant isolation on Supabase PostgreSQL.

Configuration

HeartCo uses Prisma 7 as its ORM with two connection URLs:

# Pooled connection (via pgBouncer) — for the app's queries
DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT].supabase.co:6543/postgres?pgbouncer=true"
 
# Direct connection — for migrations only
DIRECT_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT].supabase.co:5432/postgres"

Prisma client

The client is generated in generated/prisma/ (not in node_modules):

// prisma/schema.prisma
generator client {
  provider      = "prisma-client-js"
  output        = "../generated/prisma"
  binaryTargets = ["native", "rhel-openssl-3.0.x"]  // Vercel + local
}

Commands

# Generate the client after a schema change
npx prisma generate
 
# Create a migration
npx prisma migrate dev --name descriptive-name
 
# Validate the schema (without migrating)
npx prisma validate
 
# Graphical database browser
npx prisma studio
 
# Reset the database (WARNING: data loss)
npx prisma migrate reset

Multi-tenant: the heart of the architecture

Principle

Each organization has its own data, isolated from the others. Every multi-tenant model has an organizationId field that acts as a logical partition key.

ctx.orgDb vs ctx.db

HeartCo provides two Prisma clients in the tRPC context:

Client	Usage	Scope
`ctx.orgDb`	Reads scoped to the organization	Auto-filters `organizationId` on `findMany`, `findFirst`, `findFirstOrThrow`
`ctx.db`	Writes and cross-org access	No automatic filter — webhooks, admin, crons

File: src/lib/prisma-org-scope.ts

How it works

ctx.orgDb uses Prisma.$extends() to automatically inject organizationId into read queries:

// Under the hood — what withOrgScope() does
const orgDb = prisma.$extends({
  query: {
    $allModels: {
      findMany({ args, query }) {
        args.where = { ...args.where, organizationId };
        return query(args);
      },
      findFirst({ args, query }) {
        args.where = { ...args.where, organizationId };
        return query(args);
      },
    },
  },
});

125 scoped models

The following models are automatically filtered by ctx.orgDb:

Invoice, Quote, Client, Product, CrmDeal, CrmContact, WorkOrder, WorkReport, Supplier, Team, Membership, Notification, Subscription, ExpenseReport, LeaveRequest, Payslip, TimeEntry, Appointment, Calendar, BankAccount, BankTransaction, EmailCampaign, AutomationRule, AuditLog, and ~100 others.

The full list lives in ORG_SCOPED_MODELS in src/lib/prisma-org-scope.ts.

Security rules — MANDATORY

1. Always include `organizationId` in writes

// ✅ Correct
await ctx.db.invoice.update({
  where: { id: input.id, organizationId: ctx.session.user.organizationId },
  data: { status: "PAID" },
});
 
// ❌ FORBIDDEN — no organizationId in the where
await ctx.db.invoice.update({
  where: { id: input.id },
  data: { status: "PAID" },
});

2. `findFirst` instead of `findUnique`

When filtering by organizationId + id, use findFirst:

// ✅ Correct — findFirst with organizationId
const invoice = await ctx.orgDb.invoice.findFirst({
  where: { id: input.id },  // organizationId injected automatically
});
 
// ❌ FORBIDDEN — findUnique does not support the composite filter
const invoice = await ctx.db.invoice.findUnique({
  where: { id: input.id },  // No org filter → data leak!
});

3. Sub-entities (without a direct organizationId)

For models that don't have organizationId directly but belong to a scoped parent:

// ✅ Nested filter to the parent
const line = await ctx.db.invoiceLine.findFirst({
  where: {
    id: input.lineId,
    invoice: { organizationId: ctx.session.user.organizationId },
  },
});

4. tRPC errors (never `throw new Error`)

// ✅ Correct
throw new TRPCError({ code: "NOT_FOUND", message: "Invoice not found" });
 
// ❌ FORBIDDEN
throw new Error("Invoice not found");

Adding a new model

1. Define the model in `prisma/schema.prisma`

model NewModel {
  id             String       @id @default(cuid())
  name           String
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt
 
  @@index([organizationId])
}

2. Add it to `ORG_SCOPED_MODELS`

// src/lib/prisma-org-scope.ts
export const ORG_SCOPED_MODELS = new Set([
  // ... existing models
  "NewModel",
]);

3. Generate and migrate

npx prisma generate
npx prisma migrate dev --name add-new-model

Common issues

EPERM on Windows

The EPERM: operation not permitted error on query_engine-windows.dll.node is normal while the dev server is running (the file is locked).

Solution: Stop pnpm dev → npx prisma generate → restart pnpm dev.

Migration conflict

If a migration fails due to a conflict:

# View the migration status
npx prisma migrate status
 
# In development only — full reset
npx prisma migrate reset
npx prisma migrate dev

NEVER edit manually

The files in prisma/migrations/ — they are generated by Prisma
The generated/prisma/ folder — it is regenerated on every prisma generate

◀ Authentication · Contents · Modules ▶