Database Schema

Overview

26 tables across 11 migrations. All tables live in the default public schema. No row-level security — access is enforced at the application layer via the Supabase service-role client.

Migration 001 — Enums and utilities

No tables. Defines 6 enums and 2 utility functions.

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`

Functions

Function	Trigger	Notes
`set_updated_at()`	`BEFORE UPDATE` on most tables	Sets `updated_at = now()`
`cleanup_expired_sessions()`	Never called in code	Orphaned maintenance function

Migration 002 — Users and organizations

`users`

Column	Type	Notes
`id`	BIGINT PK	GitHub user ID
`login`	TEXT UNIQUE
`name`	TEXT NULLABLE
`avatar_url`	TEXT NOT NULL
`is_bot`	BOOLEAN DEFAULT false
`created_at`, `updated_at`	TIMESTAMPTZ

`organizations`

Column	Type	Notes
`id`	BIGINT PK	GitHub org ID
`login`	TEXT UNIQUE
`name`	TEXT NULLABLE
`avatar_url`	TEXT NULLABLE
`created_at`, `updated_at`	TIMESTAMPTZ

`organization_memberships`

PK: (user_id, organization_id). FKs: user_id → users(id) CASCADE, organization_id → organizations(id) CASCADE.

Column	Type	Notes
`role`	TEXT DEFAULT ‘member’
`created_at`	TIMESTAMPTZ

Migration 003 — Repositories and teams

`repositories`

Column	Type	Notes
`id`	BIGINT PK	GitHub repo ID
`full_name`	TEXT UNIQUE	`owner/repo` format
`name`	TEXT
`owner_login`	TEXT
`organization_id`	FK → `organizations(id)` CASCADE, NULLABLE
`is_private`	BOOL DEFAULT false
`is_archived`	BOOL DEFAULT false
`is_fork`	BOOL DEFAULT false
`default_branch`	TEXT NULLABLE
`html_url`	TEXT NULLABLE
`github_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

`teams`

Column	Type	Notes
`id`	BIGINT PK	GitHub team ID
`organization_id`	FK → `organizations(id)` CASCADE
`slug`	TEXT
`name`	TEXT
`description`	TEXT NULLABLE
`privacy`	`team_privacy` DEFAULT ‘closed’
`github_created_at`, `github_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

UNIQUE constraint: (organization_id, slug).

Migration 004 — Relationships

`team_members`

PK: (team_id, user_id). FKs: team_id → teams(id) CASCADE, user_id → users(id) CASCADE.

Column	Type
`role`	TEXT DEFAULT ‘member’
`created_at`, `updated_at`	TIMESTAMPTZ

`team_repositories`

PK: (team_id, repository_id). FKs: team_id → teams(id) CASCADE, repository_id → repositories(id) CASCADE.

Column	Type
`permission`	TEXT DEFAULT ‘push’
`created_at`, `updated_at`	TIMESTAMPTZ

Migration 005 — GitHub installations

`github_installations`

Column	Type	Notes
`installation_id`	BIGINT PK
`app_id`	BIGINT
`organization_id`	FK → `organizations(id)` CASCADE NULLABLE
`user_account_id`	FK → `users(id)` CASCADE NULLABLE
`account_type`	`account_type` DEFAULT ‘organization’
`repository_selection`	TEXT DEFAULT ‘all’
`permissions`	JSONB DEFAULT ''
`repository_count`	INT DEFAULT 0
`suspended_at`	TIMESTAMPTZ NULLABLE
`suspended_by`	TEXT NULLABLE
`gh_created_at`, `gh_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

CHECK constraint: exactly one of organization_id or user_account_id must be non-null.

`installation_repositories`

PK: (installation_id, repository_id). FKs: installation_id → github_installations(installation_id) CASCADE, repository_id → repositories(id) CASCADE.

Migration 006 — Auth sessions

`auth_sessions`

Column	Type	Notes
`id`	TEXT PK	64-character hex string
`user_id`	FK → `users(id)` CASCADE
`github_token_encrypted`	TEXT	AES-256-GCM ciphertext
`github_token_iv`	TEXT	Initialisation vector
`github_token_tag`	TEXT	GCM authentication tag
`github_token_type`	TEXT
`github_token_scope`	TEXT
`tokens_encrypted`	BOOL DEFAULT true	CHECK: must be 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`, `updated_at`	TIMESTAMPTZ
`expires_at`	TIMESTAMPTZ	Session expiry

`session_installations`

PK: (session_id, installation_id). FKs: session_id → auth_sessions(id) CASCADE, installation_id → github_installations(installation_id) CASCADE.

Functions added in 006

Function	Called by	Notes
`purge_expired_auth_sessions()`	Never called in code	Orphaned maintenance function

Migration 007 — Scoring presets

Adds 3 new enums and 8 tables.

New enums (007, not 001)

Enum	Values
`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`

`scoring_presets`

Column	Type	Notes
`id`	BIGSERIAL PK
`installation_id`	FK → `github_installations` CASCADE
`preset_name`	TEXT
`is_active`	BOOL DEFAULT false
`created_at`, `updated_at`	TIMESTAMPTZ

UNIQUE: (installation_id, preset_name). Partial unique index on (installation_id) WHERE is_active = true enforces one active preset per installation.

`scoring_preset_rules`

PK: preset_id FK → scoring_presets CASCADE.

Column	Default
`weekly_threshold`	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`

PK: (preset_id, signal_type). points NUMERIC(10,4), sort_order INT DEFAULT 0.

`scoring_preset_multipliers`

PK: id BIGSERIAL. preset_id FK, kind multiplier_kind, factor NUMERIC(10,4), sort_order INT DEFAULT 0. UNIQUE (preset_id, kind).

`scoring_preset_multiplier_signal_types`

PK: (multiplier_id, signal_type). FK: multiplier_id → scoring_preset_multipliers(id) CASCADE.

`scoring_preset_review_state_weights`

PK: (preset_id, state). weight NUMERIC(10,4), sort_order INT DEFAULT 0.

`scoring_preset_daily_quotas`

PK: (preset_id, signal_type). quota_limit INT, sort_order INT DEFAULT 0.

`scoring_preset_zero_point_conditions`

PK: (preset_id, kind). enabled BOOL DEFAULT true, sort_order INT DEFAULT 0.

Migration 008 — Signals

`signals` (partitioned)

RANGE PARTITIONED BY event_timestamp → monthly partitions named signals_YYYY_MM.

Column	Type	Notes
`id`	BIGINT GENERATED	Part of composite PK with `event_timestamp`
`event_timestamp`	TIMESTAMPTZ	Partition key
`installation_id`	FK → `github_installations` RESTRICT	Prevents accidental installation deletion
`user_id`	FK → `users` RESTRICT
`repository_id`	FK → `repositories` RESTRICT
`type`	`signal_type`
`value`	NUMERIC(10,4) DEFAULT 1
`content_hash`	CHAR(64)	First 32 chars of SHA-256 for dedup
`metadata`	JSONB DEFAULT ''	CHECK: `octet_length ≤ 2048`
`created_at`	TIMESTAMPTZ

UNIQUE dedup index: (user_id, type, repository_id, event_timestamp, content_hash). BRIN index on event_timestamp for range scans. Additional btree indexes on FK columns.

`signals_default`

Default partition. Captures out-of-range inserts. Should normally be empty. Presence of rows here indicates that ensure_signals_monthly_partitions() was not called before an insert.

`signals_partition_health`

Singleton row. Columns: default_partition_row_count INT, last_default_partition_hit_at TIMESTAMPTZ.

Functions added in 008

Function	Called by	Notes
`ensure_signals_monthly_partitions(p_start, p_end)`	`lib/supabase/signals.ts` on every upsert	Creates monthly partitions on demand
`track_signals_default_usage()`	`AFTER INSERT` trigger on `signals_default`	Increments health counters
`drop_old_signals_partitions(p_keep_months=12)`	Never called in code	Orphaned — no scheduled GC

Migration 009 — Sync state

`ingest_log`

Column	Type	Notes
`id`	BIGSERIAL PK
`installation_id`	FK → `github_installations` CASCADE
`organization_id`	FK → `organizations` CASCADE
`team_id`	FK → `teams` CASCADE NULLABLE
`repository_id`	FK → `repositories` CASCADE NULLABLE
`last_successful_ingest_at`	TIMESTAMPTZ DEFAULT ‘1970-01-01’
`ingest_preset`	TEXT DEFAULT ‘all_time’
`repo_count`	INT DEFAULT 0
`signal_count`	INT DEFAULT 0
`created_at`, `updated_at`	TIMESTAMPTZ

Partial unique indexes per scope combination (org-only, org+team, org+repo).

`repository_sync_state`

Column	Type	Notes
`id`	BIGSERIAL PK
`repository_id`	FK → `repositories` CASCADE UNIQUE	One row per repo
`organization_id`	FK → `organizations` CASCADE
`repo_updated_at`	TIMESTAMPTZ
`commits_etag`, `pulls_etag`, `issues_etag`, `comments_etag`, `reviews_etag`	TEXT	HTTP ETags for conditional GitHub requests
`pr_ids_hash`	TEXT	Hash of known PR IDs for change detection
`last_fetched_at`	TIMESTAMPTZ DEFAULT now()
`created_at`	TIMESTAMPTZ

Migration 010 — Leaderboard materializations

`leaderboard_materializations`

Column	Type	Notes
`id`	BIGSERIAL PK
`preset_id`	FK → `scoring_presets` CASCADE
`organization_id`	FK → `organizations` CASCADE
`team_id`	FK → `teams` CASCADE NULLABLE
`repository_id`	FK → `repositories` CASCADE NULLABLE
`scope_type`	`score_scope_type`
`entity_type`	`score_entity_type`
`time_period`	`score_time_period`
`version`	INT DEFAULT 1
`row_count`	INT DEFAULT 0
`signal_count`	INT DEFAULT 0
`retention_state`	TEXT	`current`, `superseded`, `expired`, `purged` — DEFAULT `current`
`superseded_at`	TIMESTAMPTZ NULLABLE
`retention_expires_at`	TIMESTAMPTZ NULLABLE
`purged_at`	TIMESTAMPTZ NULLABLE
`source_updated_at`	TIMESTAMPTZ NULLABLE
`redis_cache_key`	TEXT NULLABLE
`computed_at`	TIMESTAMPTZ DEFAULT now()
`created_at`, `updated_at`	TIMESTAMPTZ

Partial unique indexes per scope type combination.

Functions added in 010

Function	Called by	Notes
`purge_superseded_materializations(p_before)`	Never called in code	Orphaned — retention not enforced

Migration 011 — Computed scores

`computed_scores`

Column	Type	Notes
`id`	BIGSERIAL PK
`materialization_id`	FK → `leaderboard_materializations(id)` CASCADE
`entity_type`	`score_entity_type`
`user_id`	FK → `users` NULLABLE	Set for `contributor` entity type
`scored_team_id`	FK → `teams` NULLABLE	Set for `team` entity type
`scored_repo_id`	FK → `repositories` NULLABLE	Set for `repository` entity type
`time_period`	`score_time_period`
`score`	NUMERIC(12,4) DEFAULT 0
`breakdown`	JSONB DEFAULT ''	Per-signal-type point breakdown
`counts`	JSONB DEFAULT ''	Signal counts per type
`additions`	INT DEFAULT 0	Code additions
`deletions`	INT DEFAULT 0	Code deletions
`rank`	INT DEFAULT 0
`member_count`	INT NULLABLE	Team entries only
`computed_at`	TIMESTAMPTZ DEFAULT now()
`version`	INT DEFAULT 1

CHECK valid_score_entity: exactly one of user_id, scored_team_id, scored_repo_id must be set, and it must match entity_type.

UNIQUE: (materialization_id, entity_type, user_id, scored_team_id, scored_repo_id).

Orphaned SQL functions summary

These functions exist in the database but are never called by application code. They are maintenance utilities that were written but not wired up to any scheduler.

Function	Migration	Purpose
`cleanup_expired_sessions()`	001	Delete expired auth sessions
`purge_expired_auth_sessions()`	006	Delete expired auth sessions
`drop_old_signals_partitions(p_keep_months=12)`	008	Drop signal partitions older than N months
`purge_superseded_materializations(p_before)`	010	Delete old materialization rows

Overview

26 tables across 11 migrations. All tables live in the default public schema. No row-level security — access is enforced at the application layer via the Supabase service-role client.

Migration 001 — Enums and utilities

No tables. Defines 6 enums and 2 utility functions.

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`

Functions

Function	Trigger	Notes
`set_updated_at()`	`BEFORE UPDATE` on most tables	Sets `updated_at = now()`
`cleanup_expired_sessions()`	Never called in code	Orphaned maintenance function

Migration 002 — Users and organizations

`users`

Column	Type	Notes
`id`	BIGINT PK	GitHub user ID
`login`	TEXT UNIQUE
`name`	TEXT NULLABLE
`avatar_url`	TEXT NOT NULL
`is_bot`	BOOLEAN DEFAULT false
`created_at`, `updated_at`	TIMESTAMPTZ

`organizations`

Column	Type	Notes
`id`	BIGINT PK	GitHub org ID
`login`	TEXT UNIQUE
`name`	TEXT NULLABLE
`avatar_url`	TEXT NULLABLE
`created_at`, `updated_at`	TIMESTAMPTZ

`organization_memberships`

PK: (user_id, organization_id). FKs: user_id → users(id) CASCADE, organization_id → organizations(id) CASCADE.

Column	Type	Notes
`role`	TEXT DEFAULT ‘member’
`created_at`	TIMESTAMPTZ

Migration 003 — Repositories and teams

`repositories`

Column	Type	Notes
`id`	BIGINT PK	GitHub repo ID
`full_name`	TEXT UNIQUE	`owner/repo` format
`name`	TEXT
`owner_login`	TEXT
`organization_id`	FK → `organizations(id)` CASCADE, NULLABLE
`is_private`	BOOL DEFAULT false
`is_archived`	BOOL DEFAULT false
`is_fork`	BOOL DEFAULT false
`default_branch`	TEXT NULLABLE
`html_url`	TEXT NULLABLE
`github_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

`teams`

Column	Type	Notes
`id`	BIGINT PK	GitHub team ID
`organization_id`	FK → `organizations(id)` CASCADE
`slug`	TEXT
`name`	TEXT
`description`	TEXT NULLABLE
`privacy`	`team_privacy` DEFAULT ‘closed’
`github_created_at`, `github_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

UNIQUE constraint: (organization_id, slug).

Migration 004 — Relationships

`team_members`

PK: (team_id, user_id). FKs: team_id → teams(id) CASCADE, user_id → users(id) CASCADE.

Column	Type
`role`	TEXT DEFAULT ‘member’
`created_at`, `updated_at`	TIMESTAMPTZ

`team_repositories`

PK: (team_id, repository_id). FKs: team_id → teams(id) CASCADE, repository_id → repositories(id) CASCADE.

Column	Type
`permission`	TEXT DEFAULT ‘push’
`created_at`, `updated_at`	TIMESTAMPTZ

Migration 005 — GitHub installations

`github_installations`

Column	Type	Notes
`installation_id`	BIGINT PK
`app_id`	BIGINT
`organization_id`	FK → `organizations(id)` CASCADE NULLABLE
`user_account_id`	FK → `users(id)` CASCADE NULLABLE
`account_type`	`account_type` DEFAULT ‘organization’
`repository_selection`	TEXT DEFAULT ‘all’
`permissions`	JSONB DEFAULT ''
`repository_count`	INT DEFAULT 0
`suspended_at`	TIMESTAMPTZ NULLABLE
`suspended_by`	TEXT NULLABLE
`gh_created_at`, `gh_updated_at`	TIMESTAMPTZ
`created_at`, `updated_at`	TIMESTAMPTZ

CHECK constraint: exactly one of organization_id or user_account_id must be non-null.

`installation_repositories`

PK: (installation_id, repository_id). FKs: installation_id → github_installations(installation_id) CASCADE, repository_id → repositories(id) CASCADE.

Migration 006 — Auth sessions

`auth_sessions`

Column	Type	Notes
`id`	TEXT PK	64-character hex string
`user_id`	FK → `users(id)` CASCADE
`github_token_encrypted`	TEXT	AES-256-GCM ciphertext
`github_token_iv`	TEXT	Initialisation vector
`github_token_tag`	TEXT	GCM authentication tag
`github_token_type`	TEXT
`github_token_scope`	TEXT
`tokens_encrypted`	BOOL DEFAULT true	CHECK: must be 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`, `updated_at`	TIMESTAMPTZ
`expires_at`	TIMESTAMPTZ	Session expiry

`session_installations`

PK: (session_id, installation_id). FKs: session_id → auth_sessions(id) CASCADE, installation_id → github_installations(installation_id) CASCADE.

Functions added in 006

Function	Called by	Notes
`purge_expired_auth_sessions()`	Never called in code	Orphaned maintenance function

Migration 007 — Scoring presets

Adds 3 new enums and 8 tables.

New enums (007, not 001)

Enum	Values
`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`

`scoring_presets`

Column	Type	Notes
`id`	BIGSERIAL PK
`installation_id`	FK → `github_installations` CASCADE
`preset_name`	TEXT
`is_active`	BOOL DEFAULT false
`created_at`, `updated_at`	TIMESTAMPTZ

UNIQUE: (installation_id, preset_name). Partial unique index on (installation_id) WHERE is_active = true enforces one active preset per installation.

`scoring_preset_rules`

PK: preset_id FK → scoring_presets CASCADE.

Column	Default
`weekly_threshold`	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`

PK: (preset_id, signal_type). points NUMERIC(10,4), sort_order INT DEFAULT 0.

`scoring_preset_multipliers`

PK: id BIGSERIAL. preset_id FK, kind multiplier_kind, factor NUMERIC(10,4), sort_order INT DEFAULT 0. UNIQUE (preset_id, kind).

`scoring_preset_multiplier_signal_types`

PK: (multiplier_id, signal_type). FK: multiplier_id → scoring_preset_multipliers(id) CASCADE.

`scoring_preset_review_state_weights`

PK: (preset_id, state). weight NUMERIC(10,4), sort_order INT DEFAULT 0.

`scoring_preset_daily_quotas`

PK: (preset_id, signal_type). quota_limit INT, sort_order INT DEFAULT 0.

`scoring_preset_zero_point_conditions`

PK: (preset_id, kind). enabled BOOL DEFAULT true, sort_order INT DEFAULT 0.

Migration 008 — Signals

`signals` (partitioned)

RANGE PARTITIONED BY event_timestamp → monthly partitions named signals_YYYY_MM.

Column	Type	Notes
`id`	BIGINT GENERATED	Part of composite PK with `event_timestamp`
`event_timestamp`	TIMESTAMPTZ	Partition key
`installation_id`	FK → `github_installations` RESTRICT	Prevents accidental installation deletion
`user_id`	FK → `users` RESTRICT
`repository_id`	FK → `repositories` RESTRICT
`type`	`signal_type`
`value`	NUMERIC(10,4) DEFAULT 1
`content_hash`	CHAR(64)	First 32 chars of SHA-256 for dedup
`metadata`	JSONB DEFAULT ''	CHECK: `octet_length ≤ 2048`
`created_at`	TIMESTAMPTZ

UNIQUE dedup index: (user_id, type, repository_id, event_timestamp, content_hash). BRIN index on event_timestamp for range scans. Additional btree indexes on FK columns.

`signals_default`

Default partition. Captures out-of-range inserts. Should normally be empty. Presence of rows here indicates that ensure_signals_monthly_partitions() was not called before an insert.

`signals_partition_health`

Singleton row. Columns: default_partition_row_count INT, last_default_partition_hit_at TIMESTAMPTZ.

Functions added in 008

Function	Called by	Notes
`ensure_signals_monthly_partitions(p_start, p_end)`	`lib/supabase/signals.ts` on every upsert	Creates monthly partitions on demand
`track_signals_default_usage()`	`AFTER INSERT` trigger on `signals_default`	Increments health counters
`drop_old_signals_partitions(p_keep_months=12)`	Never called in code	Orphaned — no scheduled GC

Migration 009 — Sync state

`ingest_log`

Column	Type	Notes
`id`	BIGSERIAL PK
`installation_id`	FK → `github_installations` CASCADE
`organization_id`	FK → `organizations` CASCADE
`team_id`	FK → `teams` CASCADE NULLABLE
`repository_id`	FK → `repositories` CASCADE NULLABLE
`last_successful_ingest_at`	TIMESTAMPTZ DEFAULT ‘1970-01-01’
`ingest_preset`	TEXT DEFAULT ‘all_time’
`repo_count`	INT DEFAULT 0
`signal_count`	INT DEFAULT 0
`created_at`, `updated_at`	TIMESTAMPTZ

Partial unique indexes per scope combination (org-only, org+team, org+repo).

`repository_sync_state`

Column	Type	Notes
`id`	BIGSERIAL PK
`repository_id`	FK → `repositories` CASCADE UNIQUE	One row per repo
`organization_id`	FK → `organizations` CASCADE
`repo_updated_at`	TIMESTAMPTZ
`commits_etag`, `pulls_etag`, `issues_etag`, `comments_etag`, `reviews_etag`	TEXT	HTTP ETags for conditional GitHub requests
`pr_ids_hash`	TEXT	Hash of known PR IDs for change detection
`last_fetched_at`	TIMESTAMPTZ DEFAULT now()
`created_at`	TIMESTAMPTZ

Migration 010 — Leaderboard materializations

`leaderboard_materializations`

Column	Type	Notes
`id`	BIGSERIAL PK
`preset_id`	FK → `scoring_presets` CASCADE
`organization_id`	FK → `organizations` CASCADE
`team_id`	FK → `teams` CASCADE NULLABLE
`repository_id`	FK → `repositories` CASCADE NULLABLE
`scope_type`	`score_scope_type`
`entity_type`	`score_entity_type`
`time_period`	`score_time_period`
`version`	INT DEFAULT 1
`row_count`	INT DEFAULT 0
`signal_count`	INT DEFAULT 0
`retention_state`	TEXT	`current`, `superseded`, `expired`, `purged` — DEFAULT `current`
`superseded_at`	TIMESTAMPTZ NULLABLE
`retention_expires_at`	TIMESTAMPTZ NULLABLE
`purged_at`	TIMESTAMPTZ NULLABLE
`source_updated_at`	TIMESTAMPTZ NULLABLE
`redis_cache_key`	TEXT NULLABLE
`computed_at`	TIMESTAMPTZ DEFAULT now()
`created_at`, `updated_at`	TIMESTAMPTZ

Partial unique indexes per scope type combination.

Functions added in 010

Function	Called by	Notes
`purge_superseded_materializations(p_before)`	Never called in code	Orphaned — retention not enforced

Migration 011 — Computed scores

`computed_scores`

Column	Type	Notes
`id`	BIGSERIAL PK
`materialization_id`	FK → `leaderboard_materializations(id)` CASCADE
`entity_type`	`score_entity_type`
`user_id`	FK → `users` NULLABLE	Set for `contributor` entity type
`scored_team_id`	FK → `teams` NULLABLE	Set for `team` entity type
`scored_repo_id`	FK → `repositories` NULLABLE	Set for `repository` entity type
`time_period`	`score_time_period`
`score`	NUMERIC(12,4) DEFAULT 0
`breakdown`	JSONB DEFAULT ''	Per-signal-type point breakdown
`counts`	JSONB DEFAULT ''	Signal counts per type
`additions`	INT DEFAULT 0	Code additions
`deletions`	INT DEFAULT 0	Code deletions
`rank`	INT DEFAULT 0
`member_count`	INT NULLABLE	Team entries only
`computed_at`	TIMESTAMPTZ DEFAULT now()
`version`	INT DEFAULT 1

CHECK valid_score_entity: exactly one of user_id, scored_team_id, scored_repo_id must be set, and it must match entity_type.

UNIQUE: (materialization_id, entity_type, user_id, scored_team_id, scored_repo_id).

Orphaned SQL functions summary

These functions exist in the database but are never called by application code. They are maintenance utilities that were written but not wired up to any scheduler.

Function	Migration	Purpose
`cleanup_expired_sessions()`	001	Delete expired auth sessions
`purge_expired_auth_sessions()`	006	Delete expired auth sessions
`drop_old_signals_partitions(p_keep_months=12)`	008	Drop signal partitions older than N months
`purge_superseded_materializations(p_before)`	010	Delete old materialization rows

Overview

Migration 001 — Enums and utilities

Enums

Functions

Migration 002 — Users and organizations

users

organizations

organization_memberships

Migration 003 — Repositories and teams

repositories

teams

Migration 004 — Relationships

team_members

team_repositories

Migration 005 — GitHub installations

github_installations

installation_repositories

Migration 006 — Auth sessions

auth_sessions

session_installations

Functions added in 006

Migration 007 — Scoring presets

New enums (007, not 001)

scoring_presets

scoring_preset_rules

scoring_preset_base_points

scoring_preset_multipliers

scoring_preset_multiplier_signal_types

scoring_preset_review_state_weights

scoring_preset_daily_quotas

scoring_preset_zero_point_conditions

Migration 008 — Signals

signals (partitioned)

signals_default

signals_partition_health

Functions added in 008

Migration 009 — Sync state

ingest_log

repository_sync_state

Migration 010 — Leaderboard materializations

leaderboard_materializations

Functions added in 010

Migration 011 — Computed scores

computed_scores

Orphaned SQL functions summary

Overview

Migration 001 — Enums and utilities

Enums

Functions

Migration 002 — Users and organizations

users

organizations

organization_memberships

Migration 003 — Repositories and teams

repositories

teams

Migration 004 — Relationships

team_members

team_repositories

Migration 005 — GitHub installations

github_installations

installation_repositories

Migration 006 — Auth sessions

auth_sessions

session_installations

Functions added in 006

Migration 007 — Scoring presets

New enums (007, not 001)

scoring_presets

scoring_preset_rules

scoring_preset_base_points

scoring_preset_multipliers

scoring_preset_multiplier_signal_types

scoring_preset_review_state_weights

scoring_preset_daily_quotas

scoring_preset_zero_point_conditions

Migration 008 — Signals

signals (partitioned)

signals_default

signals_partition_health

`users`

`organizations`

`organization_memberships`

`repositories`

`teams`

`team_members`

`team_repositories`

`github_installations`

`installation_repositories`

`auth_sessions`

`session_installations`

`scoring_presets`

`scoring_preset_rules`

`scoring_preset_base_points`

`scoring_preset_multipliers`

`scoring_preset_multiplier_signal_types`

`scoring_preset_review_state_weights`

`scoring_preset_daily_quotas`

`scoring_preset_zero_point_conditions`

`signals` (partitioned)

`signals_default`

`signals_partition_health`

`ingest_log`

`repository_sync_state`

`leaderboard_materializations`

`computed_scores`

`users`

`organizations`

`organization_memberships`

`repositories`

`teams`

`team_members`

`team_repositories`

`github_installations`

`installation_repositories`

`auth_sessions`

`session_installations`

`scoring_presets`

`scoring_preset_rules`

`scoring_preset_base_points`

`scoring_preset_multipliers`

`scoring_preset_multiplier_signal_types`

`scoring_preset_review_state_weights`

`scoring_preset_daily_quotas`

`scoring_preset_zero_point_conditions`

`signals` (partitioned)

`signals_default`

`signals_partition_health`

`ingest_log`

`repository_sync_state`

`leaderboard_materializations`

`computed_scores`