The app uses Supabase as its primary database and authentication session store. All tables live in the public schema.
Migration strategy
Migrations are managed through the Supabase CLI and stored in supabase/migrations/. The schema is split into 11 focused migrations applied in numeric order:
supabase/migrations/
├── 001_enums_and_utilities.sql
├── 002_users_and_organizations.sql
├── 003_repositories_and_teams.sql
├── 004_relationships.sql
├── 005_github_installations.sql
├── 006_auth_sessions.sql
├── 007_scoring_presets.sql
├── 008_signals.sql
├── 009_sync_state.sql
├── 010_leaderboard_materializations.sql
└── 011_computed_scores.sqlApplying migrations
Remote (recommended):
npx supabase db pushLocal (if running Supabase with Docker):
npx supabase start
npx supabase db resetChecking migration status
npx supabase migration listSchema design principles
-
Normalized over embedded — Users, organizations, repositories, and teams are separate tables with foreign keys, not JSONB stuffed into session rows.
-
Encrypted at rest — GitHub OAuth tokens are encrypted with AES-256-GCM before hitting the database. Separate columns for the IV and auth tag.
-
Join tables for M:N relationships —
session_installations,installation_repositories,team_members,team_repositories— all with proper FKs. -
Partitioned signals — The
signalstable is monthly range-partitioned byevent_timestampfor query performance and data retention. -
Typed polymorphic schema — Instead of text polymorphic columns, typed nullable FK columns with CHECK constraints ensure referential integrity for leaderboard scopes and entities.
Key tables
| Table | Purpose | Section |
|---|---|---|
users | GitHub user profiles | Canonical identity |
organizations | GitHub org profiles | Canonical identity |
repositories | GitHub repos with org FK | Canonical identity |
teams | GitHub teams with org FK | Canonical identity |
organization_memberships | User-org relationships | Relationships |
team_members | User-team relationships | Relationships |
team_repositories | Team-repo access | Relationships |
github_installations | App installation metadata | Installations & auth |
installation_repositories | Install-repo access | Installations & auth |
auth_sessions | Encrypted OAuth sessions | Installations & auth |
session_installations | Session-installation join | Installations & auth |
scoring_presets | Named scoring rule-sets | Scoring config |
scoring_preset_rules | Scalar rule values | Scoring config |
scoring_preset_base_points | Per-type base points | Scoring config |
scoring_preset_multipliers | Multiplier definitions | Scoring config |
scoring_preset_review_state_weights | Review state weights | Scoring config |
scoring_preset_daily_quotas | Daily quota limits | Scoring config |
scoring_preset_zero_point_conditions | Zero-point toggles | Scoring config |
signals | Partitioned activity events | Signals |
ingest_log | Ingest tracking with cooldown | Sync state |
repository_sync_state | Per-repo ETag cache | Sync state |
leaderboard_materializations | Snapshot metadata | Materializations |
computed_scores | Precomputed leaderboard rows | Materializations |
For complete column-level documentation, see the Database Schema reference.
Row-Level Security (RLS)
Currently, RLS is not enabled on any tables. The app uses a server-side Supabase admin client with the service role key for all database operations. Session validation happens in the Next.js API routes before any database queries touch the data.
If you ever need to expose tables directly to the client (for real-time subscriptions, for example), enable RLS and create appropriate policies.
Related
- Database Schema — complete column and index reference
- Local Development — step-by-step setup guide
- Authentication — how sessions use the database