Overview
The database uses 11 focused migrations in supabase/migrations/, applied in numeric order. All tables live in the public schema. Apply with:
npx supabase db pushFor schema design principles, see Supabase Configuration.
001_enums_and_utilities.sql
ENUMs
| ENUM | Values |
|---|---|
account_type | organization, user |
signal_type | commit, pr_open, pr_merge, pr_close_no_merge, issue_open, issue_close, review, review_comment, comment, spam |
score_scope_type | organization, team, repository |
score_entity_type | contributor, team, repository |
score_time_period | today, week, month, quarter, half_year, all_time |
team_privacy | secret, closed, open |
review_state | approved, changes_requested, commented |
multiplier_kind | merged_pr_commit, pr_linked_to_issue, first_activity |
zero_point_kind | self_review, self_merge, bot_activity, issue_closed_no_pr, pr_closed_no_merge |
Utility functions
| Function | Purpose |
|---|---|
set_updated_at() | Trigger function — sets updated_at = now() on row update |
cleanup_expired_sessions() | Deletes expired auth_sessions rows |
ensure_signals_monthly_partitions(p_start, p_end) | Creates monthly partition tables for signals within a date range |
track_signals_default_usage() | Trigger — logs inserts into the default signals partition for health monitoring |
purge_expired_auth_sessions() | Deletes expired auth sessions (admin maintenance) |
purge_superseded_materializations(p_before) | Cleans up old leaderboard materializations after retention window |
drop_old_signals_partitions(p_keep_months) | Drops signal partitions older than N months |
Canonical identity tables
users
GitHub user profiles. PK is the GitHub numeric user ID.
| Column | Type | Constraints |
|---|---|---|
id | bigint | Primary key |
login | text | Not null, unique |
name | text | Nullable |
avatar_url | text | Not null |
is_bot | boolean | Default false — synthetic bot user IDs |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
Indexes: idx_users_login (unique), idx_users_is_bot (partial where is_bot = true)
organizations
GitHub organization profiles.
| Column | Type | Constraints |
|---|---|---|
id | bigint | Primary key |
login | text | Not null, unique |
name | text | Nullable |
avatar_url | text | Nullable |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
Indexes: idx_organizations_login (unique)
repositories
GitHub repositories with organization linkage.
| Column | Type | Constraints |
|---|---|---|
id | bigint | Primary key |
full_name | text | Not null, unique (owner/repo) |
name | text | Not null |
owner_login | text | Not null |
organization_id | bigint | FK → organizations(id), nullable |
is_private | boolean | Default false |
is_archived | boolean | Default false |
is_fork | boolean | Default false |
default_branch | text | Nullable |
html_url | text | Nullable |
github_updated_at | timestamptz | Nullable |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
Indexes: idx_repositories_full_name (unique), idx_repositories_owner, idx_repositories_org (partial)
teams
GitHub teams linked to an organization.
| Column | Type | Constraints |
|---|---|---|
id | bigint | Primary key |
organization_id | bigint | FK → organizations(id), not null, cascade |
slug | text | Not null |
name | text | Not null |
description | text | Nullable |
privacy | team_privacy | Default closed |
github_created_at | timestamptz | Nullable |
github_updated_at | timestamptz | Nullable |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
Unique: (organization_id, slug)
Relationship tables
organization_memberships
Many-to-many junction between users and organizations.
| Column | Type | Constraints |
|---|---|---|
user_id | bigint | FK → users(id), cascade |
organization_id | bigint | FK → organizations(id), cascade |
role | text | Default member |
created_at | timestamptz | Default now() |
PK: (user_id, organization_id)
team_members
Many-to-many between teams and users.
| Column | Type | Constraints |
|---|---|---|
team_id | bigint | FK → teams(id), cascade |
user_id | bigint | FK → users(id), cascade |
role | text | Default member |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
PK: (team_id, user_id)
team_repositories
Many-to-many between teams and repositories with permission level.
| Column | Type | Constraints |
|---|---|---|
team_id | bigint | FK → teams(id), cascade |
repository_id | bigint | FK → repositories(id), cascade |
permission | text | Default push |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
PK: (team_id, repository_id)
Installations and auth
github_installations
GitHub App installation metadata. Uses a CHECK constraint to ensure exactly one account FK is set.
| Column | Type | Constraints |
|---|---|---|
installation_id | bigint | Primary key |
app_id | bigint | Not null |
organization_id | bigint | FK → organizations(id), nullable |
user_account_id | bigint | FK → users(id), nullable |
account_type | account_type | Default organization |
repository_selection | text | Default all |
permissions | jsonb | Default {} |
repository_count | integer | Default 0 |
suspended_at | timestamptz | Nullable |
suspended_by | text | Nullable |
gh_created_at | timestamptz | Nullable |
gh_updated_at | timestamptz | Nullable |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
Check: exactly one of organization_id / user_account_id is set.
installation_repositories
Join table linking installations to their accessible repositories.
| Column | Type | Constraints |
|---|---|---|
installation_id | bigint | FK → github_installations, cascade |
repository_id | bigint | FK → repositories(id), cascade |
created_at | timestamptz | Default now() |
PK: (installation_id, repository_id)
auth_sessions
Encrypted OAuth sessions. Tokens are encrypted at rest with AES-256-GCM. No embedded user data — resolved via joins.
| Column | Type | Constraints |
|---|---|---|
id | text | Primary key (64-char hex random) |
user_id | bigint | FK → users(id), cascade |
github_token_encrypted | text | Not null |
github_token_iv | text | Not null |
github_token_tag | text | Not null |
github_token_type | text | Not null |
github_token_scope | text | Not null |
tokens_encrypted | boolean | Default true |
github_refresh_token_encrypted | text | Nullable |
github_refresh_token_iv | text | Nullable |
github_refresh_token_tag | text | Nullable |
github_refresh_token_expires_at | timestamptz | Nullable |
github_access_token_expires_at | timestamptz | Nullable |
created_at | timestamptz | Default now() |
expires_at | timestamptz | Not null |
updated_at | timestamptz | Default now() |
Installations are stored in session_installations join table (not an array column).
session_installations
Join table — replaces the old installation_ids bigint[] array on auth_sessions.
| Column | Type | Constraints |
|---|---|---|
session_id | text | FK → auth_sessions(id), cascade |
installation_id | bigint | FK → github_installations, cascade |
created_at | timestamptz | Default now() |
PK: (session_id, installation_id)
Scoring configuration
Scoring rules are stored in normalized relational tables (not JSONB).
scoring_presets
Named rule-sets per installation.
| Column | Type | Constraints |
|---|---|---|
id | bigserial | Primary key |
installation_id | bigint | FK → github_installations, cascade |
preset_name | text | Not null |
is_active | boolean | Default false |
Unique: (installation_id, preset_name)
Partial unique index: (installation_id) where is_active = true — only one active per installation.
scoring_preset_rules
Scalar rule configuration (diminishing returns thresholds, penalties).
| Column | Type | Default |
|---|---|---|
preset_id | bigint | PK, FK → scoring_presets, cascade |
weekly_threshold | integer | 9 |
decay_factor | numeric(6,4) | 0.11 |
floor_fraction | numeric(6,4) | 0.2 |
spam_penalty | numeric(10,4) | -12 |
pr_closed_no_merge_penalty | numeric(10,4) | -10 |
scoring_preset_base_points
Per-signal-type base point values.
PK: (preset_id, signal_type)
scoring_preset_multipliers
Multiplier definitions with scoring_preset_multiplier_signal_types storing applicable signal types per multiplier.
scoring_preset_review_state_weights
PK: (preset_id, state)
scoring_preset_daily_quotas
PK: (preset_id, signal_type)
scoring_preset_zero_point_conditions
PK: (preset_id, kind)
Signals (monthly partitioned)
signals
Monthly range-partitioned activity event log. App code creates month partitions on demand via ensure_signals_monthly_partitions().
| Column | Type | Constraints |
|---|---|---|
id | bigint | Generated by default as identity |
installation_id | bigint | FK → github_installations, cascade |
user_id | bigint | FK → users(id), restrict |
repository_id | bigint | FK → repositories(id), restrict |
type | signal_type | Not null |
value | numeric(10,4) | Default 1 |
event_timestamp | timestamptz | Not null |
content_hash | char(64) | SHA-256 hex digest for dedup |
metadata | jsonb | Default {}; max 2048 bytes |
PK: (id, event_timestamp)
Partitioned by: RANGE (event_timestamp)
Indexes (created per partition via ensure_signals_monthly_partitions()):
- Unique dedup:
(user_id, type, repository_id, event_timestamp, content_hash) (repository_id)(user_id)(installation_id, repository_id, event_timestamp DESC)(installation_id, user_id, event_timestamp DESC)(installation_id, type)- BRIN on
(event_timestamp) (event_timestamp DESC)
Health tracking: signals_partition_health table logs when the default partition is hit.
Sync state
ingest_log
Tracks last successful ingest per scope with 24-hour minimum cooldown.
| Column | Type | Notes |
|---|---|---|
id | bigserial | PK |
installation_id | bigint | FK, cascade |
organization_id | bigint | FK, cascade |
team_id | bigint | FK, nullable |
repository_id | bigint | FK, nullable |
last_successful_ingest_at | timestamptz | Default epoch |
ingest_preset | text | Default all_time |
repo_count | integer | Default 0 |
signal_count | integer | Default 0 |
Partial unique indexes for org/team/repo scope (handles NULLs correctly).
repository_sync_state
Per-repo GitHub API sync state with ETag caching.
| Column | Type | Notes |
|---|---|---|
id | bigserial | PK |
repository_id | bigint | FK, cascade, unique |
organization_id | bigint | FK, cascade |
repo_updated_at | timestamptz | Nullable |
commits_etag / pulls_etag / issues_etag / comments_etag / reviews_etag | text | Conditional request ETags |
pr_ids_hash | text | Hash of current PR IDs for change detection |
last_fetched_at | timestamptz | Default now() |
Leaderboard materializations and scores
leaderboard_materializations
Durable snapshot metadata for leaderboard reads with versioning and retention.
| Column | Type | Notes |
|---|---|---|
id | bigserial | PK |
preset_id | bigint | FK, cascade |
scope_type | score_scope_type | Not null |
organization_id | bigint | FK, cascade |
team_id | bigint | FK, nullable |
repository_id | bigint | FK, nullable |
entity_type | score_entity_type | Not null |
time_period | score_time_period | Not null |
version | integer | Default 1 |
row_count | integer | Default 0 |
signal_count | integer | Default 0 |
retention_state | text | current, superseded, expired, or purged |
superseded_at | timestamptz | Nullable |
retention_expires_at | timestamptz | Nullable |
redis_cache_key | text | Nullable |
computed_at | timestamptz | Default now() |
Partial unique indexes per scope type. Retention lifecycle enforced via CHECK constraints.
computed_scores
Precomputed leaderboard rows keyed by durable materialization ID for fast Redis-backed reads.
| Column | Type | Notes |
|---|---|---|
id | bigserial | PK |
materialization_id | bigint | FK → leaderboard_materializations, cascade |
entity_type | score_entity_type | Not null |
user_id | bigint | FK, nullable (contributor entity) |
scored_team_id | bigint | FK, nullable (team entity) |
scored_repo_id | bigint | FK, nullable (repo entity) |
time_period | score_time_period | Not null |
score | numeric(12,4) | Default 0 |
breakdown | jsonb | Per-category score breakdown |
counts | jsonb | Raw contribution counts |
additions / deletions | integer | Default 0 |
rank | integer | Not null |
member_count | integer | Nullable |
version | integer | Default 1 |
Check: valid_score_entity ensures exactly one entity FK is set matching entity_type.
Entity relationship diagram
users (1) ───< organization_memberships >─── (1) organizations
│
├──< auth_sessions (1:N) ──< session_installations >── github_installations (N:1)
│
├──< team_members >─── teams (N:1)
│
└──< computed_scores (N:1, contributor)
github_installations (1)
├──< installation_repositories >── repositories (N:1)
├──< scoring_presets (1:N)
│ └── scoring_preset_rules (1:1)
│ └── scoring_preset_base_points (1:N)
│ └── scoring_preset_multipliers (1:N)
│ └── etc.
├──< signals (1:N, partitioned)
└──< ingest_log (1:N)
repositories (1)
├──< team_repositories >─── teams (N:1)
├──< signals (1:N, partitioned)
└──< repository_sync_state (1:1)
leaderboard_materializations (1) ──< computed_scores (1:N)Related
- Scoring Reference — scoring-specific tables and types
- Supabase Configuration — migration strategy and schema design principles