Database

Sessiq stores everything in one PostgreSQL 15 database, accessed through Prisma 5. All tenants share the schema; every tenant-owned row carries a tenant_id, and the database itself enforces that a request only sees its own tenant's rows.

Everything database-related lives in packages/db:

Path	What it is
prisma/schema.prisma	The schema: about 120 models and 37 enums
migrations/*.sql	Custom SQL that Prisma cannot express
scripts/legacy-migrations.list	The ordered list of custom SQL files to apply
scripts/apply-custom-sql.sh	Applies every file on that list
scripts/bootstrap-dev-db.sh	One-command dev database setup (pnpm db:dev:bootstrap)
scripts/reset-test-db.sh	Test database schema reset (pnpm db:test:reset)
seeds/	Demo data (seed_demo.sql) and reference-data seeds
src/rls-context.ts	The tenant-scoped client: withTenant()
src/dal/	Shared data-access helpers used by the API and worker

How the schema is applied

The schema is applied with prisma db push, not with Prisma migration files. The same two steps run in development, in the test database and in production:

1. prisma db push makes the tables, columns, indexes and enums match schema.prisma.
2. apply-custom-sql.sh applies each file named in legacy-migrations.list, in order. These carry what db push cannot: row-level security policies, grants, SQL functions, triggers, views and CHECK constraints.

Environment	Command
Development	pnpm migrate:dev
Test	pnpm db:test:reset (see Testing)
Production	Run by the deploy

The old migration folders under prisma/migrations/ are kept for history; nothing applies them.

Changing the schema

1. Edit packages/db/prisma/schema.prisma.
2. Run pnpm migrate:dev. It also regenerates the Prisma client.
3. If you need custom SQL (a new tenant-scoped table always does, for its row-level security policy and grants), add packages/db/migrations/<YYYYMMDD>_<name>.sql and append <YYYYMMDD>_<name> to scripts/legacy-migrations.list with a short comment explaining it.
4. Run pnpm db:test:reset and the integration tests.

Custom SQL files must be idempotent: they run on every deploy and every reset. Use CREATE OR REPLACE, IF NOT EXISTS, and DROP POLICY IF EXISTS before CREATE POLICY.

db push runs with --accept-data-loss. Renaming a column in schema.prisma drops the old column and adds a new, empty one. To rename or reshape data, add the new column first, copy the data in custom SQL, and remove the old column in a later change.

Naming conventions

Prisma models and fields are camelCase in TypeScript and snake_case in the database:

• Every model maps to a snake_case table with @@map("snake_case").
• Every field maps to a snake_case column with @map("snake_case_column").

model GiftCardPurchase {
  id        String   @id @default(cuid())
  tenantId  String   @map("tenant_id")
  createdAt DateTime @default(now()) @map("created_at")

  @@map("gift_card_purchase")
}

Table names are singular (booking, site, payment_transaction). A few older models are declared with a snake_case name directly (for example resource) and need no @@map.

Money

Money is stored as Decimal, never as a float:

priceAmount Decimal? @map("price_amount") @db.Decimal(10, 2)

In TypeScript, use Prisma's Decimal for arithmetic and comparison. Do not convert to number and back. Currency is stored alongside the amount (EUR by default).

Tenant-scoped access

Tenant data is always read and written inside withTenant:

import { withTenant } from '@booking/db/rls-context';

const booking = await withTenant(tenantId, async (tx) => {
  return tx.booking.findFirst({ where: { id: bookingId, tenantId } });
});

• withTenant opens a transaction and sets the tenant for it. PostgreSQL row-level security policies on every tenant-scoped table then compare each row's tenant_id with that tenant, so rows from other tenants are invisible and cannot be written.
• The client refuses model queries made outside withTenant, with a "Tenant context not set" error.
• Keep the explicit tenantId filter in your queries too. Isolation is enforced at both the application and the database layer.
• Pass { reusable: true } when a service method may be called inside another withTenant transaction for the same tenant, so it joins that transaction instead of opening a nested one.
• Do not create your own PrismaClient.

In unit tests, mock this module with createMockRlsContext(). See Testing.

Core tables

Area	Tables
Tenancy	tenant, tenant_settings, site, user, membership, site_membership
Access control	permission, role, role_permission, role_assignment, api_key
Catalog	product, variant, resource, capacity_pool, addon, addon_group, checkout_offer
Scheduling	schedule_template, schedule_override, blackout_window, slot, capacity_override
Bookings	booking, booking_slot, booking_participant, booking_addon, booking_line_item, reservation_hold
Money	payment_transaction, refund_request, price_ruleset, price_rule, promotion, voucher, gift_card_purchase, tax_configuration
Customers	customer, waitlist_entry, review, waiver_signature
Messaging	message_outbox, message_history, notification_template, notification_automation_rule
Audit	audit_event, booking_audit_log

booking has two independent status columns, lifecycle_status and payment_status. See Status field conventions. booking, payment_transaction and the booking child tables (booking_addon, booking_line_item, booking_participant, booking_note) are soft-deleted with deleted_at. The client filters deleted rows out of reads automatically.

message_outbox holds side effects (emails, webhooks, analytics events) written in the same transaction as the change that caused them. The worker delivers them. See Outbox pattern.