Database Setup

Overview

The database package (packages/database) manages the PostgreSQL schema via Drizzle ORM and provides repository implementations. It uses Supabase for local development (Docker-based Postgres).

Prerequisites

• Docker (for local Supabase)
• pnpm 10+

Environment Variables

Create packages/database/.env:

# Supabase CLI configuration
SUPABASE_WORKDIR=./supabase

# Local database (Supabase local Docker)
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres

For drizzle-kit against production (migrations, schema introspection), create packages/database/.env.production from the example. Operations on production data (grants, imports, repairs) are apps/cli commands and read apps/cli/.env.production.local instead: see apps/cli/README.md.

cp packages/database/.env.production.example packages/database/.env.production

The tooling of the package (the drizzle-kit configs, the seed scripts and the integration tests) reads DATABASE_URL through packages/database/env.ts, which rejects a missing value or anything but a postgres:// or postgresql:// URL before opening a connection.

Local setup

cd packages/database

# Start local Supabase (Postgres + Auth + Edge Functions)
pnpm local:start

# Apply Drizzle migrations + seed data
pnpm local:reset

This creates:

• PostgreSQL on port 54322
• Supabase Studio on port 54323
• Seed data: 5 auction systems, 62 themes, 182+ rows

Schema

The board entity is split across three tables for performance:

Other tables: accounts, auction_systems, themes, board_themes, board_bookmarks, board_votes, impersonation_log.

Commands

All commands run from packages/database/. Environment variables are loaded automatically via with:env (dev) or with:env:prod (production).

# Local infrastructure
pnpm local:start          # Start local Supabase (requires Docker)
pnpm local:stop           # Stop local Supabase
pnpm local:reset          # Reset DB + migrate + seed
pnpm local:seed           # Seed local database

# Schema management
pnpm db:generate          # Generate migration from schema changes
pnpm db:migrate           # Apply migrations (dev)
pnpm db:studio            # Open Drizzle Studio (visual DB browser)

# Production
pnpm with:env:prod drizzle-kit migrate   # Apply migrations to production (.env.production)

Testing

Integration tests run against their own database, never the development one: a second local Supabase project with only the database enabled (packages/database/__tests__/supabase, port 54332). It matches production's Postgres version, Supabase roles and extensions (pg_cron included).

pnpm test                 # Unit tests only (excludes integration)
pnpm test:integration     # Prepares the integration database once, runs every suite concurrently
pnpm test:db:prepare      # Start (if needed), reset, migrate and seed its catalogs (packages/database)
pnpm test:db:start        # Start the integration database on its own (packages/database)
pnpm test:db:stop         # Stop it

How the suites use it:

• Production wiring. API tests build the real router on a real pool, as src/router.ts does: every request reads and writes on the pool as the database owner, with no transaction spanning the request.
• A fresh database per run, isolated data per test. test:integration resets the integration database and applies the branch's migrations once, so a run never inherits another branch's schema; a package's own test:integration does the same before its suite. Tests commit their fixtures with unique identifiers and assert only on their own rows, which lets test files and suites run in parallel; jobs that work across every owner (the daily rollup, the standings) get a day or a period of their own.
• No Postgres role. No app takes a role, and no suite does either: an account is the userId of the ServiceContext passed to the services. The tables keep row-level security enabled with no policy, so a role other than the owner reads and writes nothing; one suite pins that posture (row-level-security.test.ts).
• Reference catalogs seeded once. The preparation seeds the auction systems, the plans and the system themes (seedReferenceData) before any file runs, so no suite writes them concurrently; a test that needs an empty plans catalogue empties it in a rolled-back transaction (emptyPlansCatalogue). Migrations themselves get no dedicated test: the replay from scratch covers them, and a data backfill is checked against production with counting queries in its PR.

Non-leak suites

Authorization is decided in the services, so a read that forgets its owner predicate over-answers. The non-leak suites (packages/database/__tests__/integration/non-leak) make that systematic: for each entity, actor A calls every public method of its service on actor B's data and must neither read nor write any of it. A refusal passes, whatever its shape; an answer passes only when it carries none of B's rows; a write passes only when B's rows read the same afterwards. Methods made for an operator acting on another owner's behalf, which take that owner explicitly, are left out.

A new entity, or a new public service method, comes with its non-leak test.

Drizzle schema conventions

• JSONB columns use typed helpers from schema/columns.ts: dealHands(), dealAuction(), dealPlay(), dealAnalysis(), localizedRichText()
• FK references use UUID columns (not text identifiers)
• Enums for stable value sets, text for evolving ones
• All JSONB data is validated with Zod schemas at read time