Repository Layer

Admin client

lib/supabase/server.ts — exports createSupabaseAdminClient().

Uses the Supabase service-role key (SUPABASE_SERVICE_ROLE_KEY). No session persistence; every call is authenticated as the service role. All repository functions obtain a client via this factory. Never use the anon key for server-side DB operations.

Why RLS is disabled

All tables have no row-level security policies. Access control is enforced at the application layer:

getRequestSession() validates the caller’s session from auth_sessions.
Authorization guards check that the session’s installations include the requested installationId before any DB query runs.

Using the service-role client with application-layer guards avoids the complexity of Supabase RLS policies while maintaining the same security boundary. Every code path that touches installation-scoped data goes through an authorization check first.

Module reference

`user-repository.ts`

Tables: users

Export	Description
`upsertUser(user)`	Insert or update a user row by GitHub ID
`getUser(userId)`	Fetch a single user by ID

`organization-repository.ts`

Tables: organizations, organization_memberships

Export	Description
`upsertOrganization(org)`	Insert or update a single organization
`upsertOrganizations(orgs[])`	Batch upsert organizations
`getOrganization(orgId)`	Fetch a single organization by ID
`upsertMemberships(orgId, memberships[])`	Sync organization_memberships rows
`getUserOrganizations(userId)`	List organizations the user belongs to

`repository.ts`

Tables: repositories

Export	Description
`upsertRepositories(repos[])`	Batch insert or update repository rows

`installation-repository.ts`

Tables: github_installations, installation_repositories

Export	Description
`getInstallation(installationId)`	Single installation by PK
`upsertInstallation(data)`	Insert or update an installation
`getInstallationsByIds(ids[])`	Batch fetch installations
`getInstallationByOrganizationId(orgId)`	Installation for a given organization
`getOrgInstallationsByOrganizationIds(orgIds[])`	Batch fetch by org IDs
`getInstallationByOrgLogin(login)`	Installation lookup by org login
`upsertInstallationRepositories(installationId, repoIds[])`	Sync `installation_repositories` join rows
`getInstallationRepositoryIds(installationId)`	All repository IDs for an installation
`deleteInstallation(installationId)`	Remove installation and cascade
`getOrgInstallations()`	List all organization-type installations

`ingest-state.ts`

Tables: ingest_log

Export	Description
`getLastSuccessfulIngestAt(installationId, scope)`	Read the most recent successful ingest timestamp for a scope
`setLastSuccessfulIngestAt(installationId, scope, data)`	Upsert ingest completion record; includes upsert-race retry on conflict

The upsert-race retry exists because two processes can simultaneously attempt to insert the first ingest_log row for a new scope. On conflict, the function retries once.

`signals.ts`

Tables: signals, users (via ensureUsersExist), repositories

Export	Description
`getSignalsForOrg(installationId, repos, opts?)`	Read signals for all repos in an installation. `opts.since`/`opts.until` add date filters. No filter = all time.
`ensureUsersExist(users[])`	Upsert user rows before writing signals (FK requirement)
`upsertSignals(installationId, signals[], repoNameToId)`	Write normalized signals. Calls `ensure_signals_monthly_partitions(start, end)` RPC before insert to guarantee the correct partition exists. Idempotent via unique index.
`deleteSignalsForInstallation(installationId)`	Hard-delete all signals for an installation (used before installation removal)

getSignalsForOrg is the only function that reads from the signals table. Both the SCORE phase and custom date range serve path call it.

`repo-cache.ts`

Tables: repository_sync_state, repositories, organizations

All functions in this module use a Redis caching layer in front of DB reads. Cache keys are keyed by repository ID or org + repo name.

Export	Description
`getRepoFetchLog(repoId)`	Read sync state for a repo by ID (Redis → DB fallback)
`upsertRepoFetchLog(repoId, data)`	Write sync state by ID; invalidates Redis cache
`getRepoFetchLogByName(orgLogin, repoName)`	Read sync state by name (Redis → DB fallback)
`upsertRepoFetchLogByName(orgLogin, repoName, data)`	Write sync state by name
`getStaleReposForOrg(orgId, staleBefore)`	List repositories not fetched since `staleBefore`
`clearRepoFetchLogForOrg(orgId)`	Delete all sync state rows for an org; clears Redis keys

repository_sync_state stores HTTP ETags per data type (commits_etag, pulls_etag, issues_etag, comments_etag, reviews_etag) and a pr_ids_hash for change detection. The ingest layer uses these to make conditional GitHub API requests and skip unchanged repositories.

`team-cache-repository.ts`

Tables: teams, team_members, team_repositories

Export	Description
`getOrgTeams(orgId)`	List teams for an organization (cached)
`upsertOrgTeams(orgId, teams[])`	Sync teams; invalidates cache
`clearOrgTeams(orgId)`	Delete all team rows for an org
`getTeamRepositories(teamId)`	List repositories for a team (cached)
`getTeamMemberships(teamId)`	List members for a team (cached)
`upsertTeamRepositories(teamId, repoIds[])`	Sync `team_repositories` join rows
`clearTeamRepositories(teamId)`	Delete team_repositories for a team

Team data is fetched from GitHub during ingest via fetchOrgTeamsDataGraphQL and persisted here. The scoring engine reads team memberships from this module to build the teamMemberships map passed to aggregateByTeamSignals().

`leaderboard-db.ts`

Tables: scoring_presets (and all 7 config sub-tables), leaderboard_materializations, computed_scores

This is the largest module in the repository layer.

Preset CRUD:

Export	Description
`getScoringRules(presetId)`	Load complete preset config from all 8 tables
`listScoringRulesPresets(installationId)`	List presets for an installation
`getActiveScoringRulesPreset(installationId)`	Return the single `is_active = true` preset
`ensureDefaultPreset(installationId)`	Create ‘Default’ preset with `is_active = true` if none exists
`createScoringRulesPreset(installationId, data)`	Create preset across all 8 tables
`updateScoringRulesPreset(presetId, data)`	Update preset configuration
`setActiveScoringRulesPreset(installationId, presetId)`	Atomically swap active preset
`deleteScoringRulesPreset(presetId)`	Delete (enforces last-preset and active-preset constraints)

Materialization reads/writes:

Export	Description
`getPresetComputedScores(presetId, timePeriod, scope)`	Read current computed scores for a preset+period+scope
`writePresetComputedScores(presetId, scopeType, org, team, repo, entityType, period, entries)`	Write scored entries to `computed_scores` + update `leaderboard_materializations`
`getComputedScoresForAllTimePeriods(presetId, scope)`	Batch read for all 6 periods

writePresetComputedScores acquires a per-preset Redis advisory lock before writing to prevent concurrent writes producing interleaved rows.