Supabase Setup

Oversight uses Supabase for its database, authentication, and real-time subscriptions. This guide walks through creating a project, running migrations, and configuring the auth provider.

Create a Supabase Project

1. Go to supabase.com/dashboard and create a new project.
2. Choose a region close to where you will run the oversight server.
3. Copy your project credentials from Settings → API:
   • Project URL → SUPABASE_URL
   • anon public key → SUPABASE_ANON_KEY
   • service_role secret key → SUPABASE_SERVICE_ROLE_KEY

Run Migrations

Oversight ships numbered SQL migrations in supabase/migrations/. Apply them using the Supabase CLI or the SQL Editor in the dashboard.

Option A: Supabase CLI

# Link to your project
supabase link --project-ref <your-project-ref>

# Push all migrations
supabase db push

Option B: SQL Editor

Open the SQL Editor in the Supabase dashboard and run each migration file in order:

1. 001_initial.sql — Creates repos, analyses, analysis_items, and tasks tables with RLS policies and indexes.
2. 002_tasks_notion_fields.sql — Adds Notion page tracking columns to tasks.
3. 003_user_settings.sql — Creates the user_settings table.
4. 004_merge_status.sql — Adds merge status tracking to analysis items.
5. 005_tasks_posted_tracking.sql — Tracks when reviews are posted to GitHub.
6. 006_shared_data.sql — Converts per-user data to shared model with new RLS policies.
7. 007_repos_case_insensitive.sql — Makes repo name lookups case-insensitive.
8. 008_tasks_params.sql — Adds a params JSONB column to tasks.
9. 009_staleness_defaults_30.sql — Updates default staleness thresholds to 30 days.
10. 010_audit_log_and_output.sql — Creates the audit_log table and adds output logging.
11. 011_runner_heartbeats.sql — Creates the runner_heartbeats table.
12. 012_engagement_status.sql — Adds engagement signal tracking.
13. 013_runner_commands.sql — Creates the runner_commands table for remote restart.
14. 014_repo_steward.sql — Adds steward assignment and nightly refresh tracking to repos.

Database Schema

After running all migrations, the schema includes these core tables:

RLS Policies

Oversight uses a shared data model. After migration 006, all tables use authenticated-user RLS policies — any logged-in user can read and write all data. The service_role key bypasses RLS entirely for the server and runner.

Key policy pattern:

CREATE POLICY "Authenticated users can view all repos"
    ON repos FOR SELECT
    USING (auth.role() = 'authenticated');

Enable Realtime

The tasks table is added to the Supabase Realtime publication so the dashboard can show live progress updates as the runner executes reviews. This is done automatically by migration 001:

ALTER PUBLICATION supabase_realtime ADD TABLE tasks;

If you need to verify this manually, check the Supabase dashboard under Database → Replication and confirm tasks is listed under the supabase_realtime publication.

GitHub OAuth Provider

Oversight uses GitHub as its Supabase auth provider. Configure it in Authentication → Providers → GitHub:

1. Create a GitHub OAuth App at github.com/settings/developers.
2. Set the Authorization callback URL to https://<your-project-ref>.supabase.co/auth/v1/callback.
3. Copy the Client ID and Client Secret into the Supabase GitHub provider settings.
4. Also set them as GITHUB_OAUTH_CLIENT_ID and GITHUB_OAUTH_CLIENT_SECRET in your .env for the server-side token refresh endpoint.

Getting Credentials

Quick reference for where to find each value: