Migration files

All migrations live in supabase/migrations/. They are numbered sequentially and must be applied in order.

Applying migrations to a remote project

First-time setup

npx supabase login
npx supabase link --project-ref <your-project-ref>

The project ref is the random string in your Supabase URL: https://<ref>.supabase.co.

Apply all pending migrations

npx supabase db push

Check migration status

npx supabase migration list

Local development

Requires Docker.

# Start the local Supabase stack
npx supabase start
 
# Apply all migrations from scratch
npx supabase db reset

db reset drops and recreates the local database, then applies all migrations in order. Use this to verify a new migration applies cleanly.

Manual application (no CLI)

If the Supabase CLI is not available, run each migration file in the SQL Editor in the Supabase dashboard, in order from 001 to 011. The files are idempotent where possible (IF NOT EXISTS, CREATE OR REPLACE), but running them out of order will fail due to FK dependencies.

Adding a new migration

1. Create supabase/migrations/012_<description>.sql.
2. Write idempotent SQL. Use IF NOT EXISTS for tables and indexes, CREATE OR REPLACE for functions. Avoid destructive operations in forward migrations.
3. Apply with npx supabase db push (remote) or npx supabase db reset (local full reset).
4. If the migration adds tables, enums, or functions: update content/database/schema.mdx.
5. Create TypeScript types in types/db/ for any new tables.
6. Add repository functions in lib/supabase/ for application access.

Key schema design decisions

Normalized, not embedded

User, organization, and team data live in their own tables with stable integer primary keys (GitHub IDs). No user or org data is embedded as JSONB in session or installation rows. This keeps references consistent and avoids update anomalies.

Tokens encrypted at rest

Auth tokens use AES-256-GCM. Each encrypted token has three columns: _encrypted (ciphertext), _iv (initialisation vector), _tag (GCM authentication tag). The CHECK constraint tokens_encrypted = true prevents plaintext tokens from being stored.

Join tables for M:N relationships

session_installations, installation_repositories, team_members, team_repositories are all explicit join tables with timestamps. Parent rows never store arrays of related IDs.

RESTRICT FK on signals

signals.installation_id, signals.user_id, and signals.repository_id use ON DELETE RESTRICT. Attempting to delete a user or repository that owns signal rows will fail. Call deleteSignalsForInstallation() before removing an installation.

Partitioned signals table

signals is range-partitioned by event_timestamp with monthly partitions named signals_YYYY_MM. Partitions are created on demand by the ensure_signals_monthly_partitions(p_start, p_end) SQL function, which is called by upsertSignals() before every write. signals_default is the catch-all partition; rows there indicate a partition was not pre-created.

Typed polymorphic foreign keys

computed_scores uses three nullable FK columns (user_id, scored_team_id, scored_repo_id) plus a CHECK constraint that enforces exactly one is set and it matches entity_type. This is stronger than text-based polymorphism and lets the query planner use individual FK indexes.

One active preset per installation

Enforced by a partial unique index: UNIQUE (installation_id) WHERE is_active = true. No trigger or serializable transaction needed — the index makes the constraint atomic.