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 production operations, create packages/database/.env.production from the example:

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

Variable	Required	Description
`DATABASE_URL`	Yes	PostgreSQL connection string (local: `postgresql://postgres:postgres@127.0.0.1:54322/postgres`)
`SUPABASE_WORKDIR`	Yes	Supabase working directory (`./supabase`)
`SUPABASE_PROJECT_ID_PROD`	No	Production Supabase project ID (for deployment)
`SUPABASE_DB_URL_PROD`	No	Production database URL (for migrations)

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:

Table	Purpose
`boards_index`	Lightweight scalars for filtering, sorting, pagination
`boards_payload`	Heavy JSONB content (cards, auction, play) for detail views
`board_auction_search`	Derived ngrams for auction sequence search

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 db:migrate:prod      # Apply migrations to production

Testing

Integration tests use a standalone Postgres (no Supabase dependency):

# Run all tests (unit + integration)
pnpm test                 # Unit tests only (excludes integration)
pnpm test:integration     # Full pipeline: docker up → migrate → test → docker down

The integration test infrastructure:

docker-compose.test.yml — Postgres 16 on port 5433 (tmpfs, no persistence)
withTestTransaction() helper — each test runs in a rolled-back transaction
pnpm test:integration — single command for the full pipeline

Drizzle schema conventions

JSONB columns use typed helpers from schema/columns.ts: allHands(), 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

On this page