Database Schema

Navigate Supabase database schema, 86+ migrations, and type definitions.

Overview

PostgreSQL schema via Supabase with RLS policies. See docs/database/SCHEMA.md.

Database Access

⚠️ CRITICAL: Must be in Railway shell for all database operations

# Verify environment first
echo $RAILWAY_ENVIRONMENT  # Must be non-empty

# If empty, enter Railway shell:
railway shell

Common Queries:

# Quick introspection
psql "$DATABASE_URL" -c "\dt"  # List all tables
psql "$DATABASE_URL" -c "\d users"  # Describe users table
psql "$DATABASE_URL" -c "\d+ accounts"  # Detailed table info with indexes

# Core tables
psql "$DATABASE_URL" -c "SELECT * FROM users LIMIT 5;"
psql "$DATABASE_URL" -c "SELECT * FROM accounts WHERE user_id = 'user_xxx';"
psql "$DATABASE_URL" -c "SELECT * FROM holdings WHERE account_id = 'acc_xxx';"

Migrations:

# Inside railway shell + supabase directory
cd supabase
supabase db push  # Apply local migrations (see health check below)
supabase db diff -f new_migration_name  # Generate migration from changes

See AGENTS.md "Database Access" section for complete guide.

Migrations

• supabase/migrations/ - All 86+ migrations (sequential)
• Format: YYYYMMDD_description.sql
• Key prefixes: *clerk*, *plaid*, *eodhd*

End-to-end Checklist (schema changes)

Any time you change schema (tables, columns, indexes, RLS) or add Supabase migrations:

1. Work in Railway dev shell

   • Confirm: echo $RAILWAY_ENVIRONMENT is non-empty.
   • Use the dev DATABASE_URL and SUPABASE_PROJECT_ID.

2. Apply migrations to dev

   cd supabase
   supabase db push
   

   • If this fails on old migrations, follow the health check flow below (registry repair or idempotent DDL) before proceeding.

3. Regenerate types and manifest

   # From repo root, still in Railway shell
   export SUPABASE_PROJECT_ID=klrrntdswlvjdqusahdk  # or injected value
   make schema:generate
   

   • This must update:
     • supabase/types/database.types.ts
     • supabase/generated/schema_manifest.json
     • backend/schemas/generated/**

4. Verify schema parity

   cd backend
   PYTHONPATH=. poetry run python ../scripts/verify_generated_schemas.py
   

   • This is the same check Tier 2 Auth Stub uses in CI.
   • Fix any reported mismatches (missing columns, wrong nullability) before committing.

5. Commit everything together

   • In a feature branch:
     • supabase/migrations/** changes
     • supabase/schemas/** changes
     • Regenerated types and manifests
     • Updated backend/schemas/generated/**
   • Do not split schema SQL and generated artifacts into separate feature branches; they must land atomically.

Migration Health Check (bd-k1c learnings)

Before adding or merging new migrations:

1. Verify registry vs schema

   • Run in Railway shell:

     cd supabase
     supabase db push
     

   • If it fails on old migrations (tables/triggers already exist), it means the schema was initialized by golden_schema.sql / all_migrations.sql / manual SQL and the migration registry is behind.

2. If db push fails on old migrations

   • Do not hack the schema via ad‑hoc SQL.
   • Instead, repair the registry or make old migrations idempotent:
     • Option A (registry repair): mark older versions as applied in supabase_migrations.schema_migrations (see supabase/scripts/fix_migration_registry_bd_k1c.sql for the bd‑k1c repair).
     • Option B (idempotent migrations): wrap non‑idempotent DDL (e.g. CREATE TRIGGER) in IF NOT EXISTS blocks so replaying them is safe.
   • Re‑run supabase db push after repair; only then add/merge new migrations.

3. New migrations (forward-only rule)

   • Prefer CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, and CREATE INDEX IF NOT EXISTS when possible to make replays safe.
   • For new migrations you add from now on, use a unique timestamp prefix per file (e.g. 20251206152000_...).
   • RPC Functions: ALWAYS provide default values for arguments (e.g. filter jsonb DEFAULT '{}') to avoid signature mismatches with standard backends.
   • Standard Scrape/Doc Schema:
     • raw_scrapes: MUST have storage_uri (text).
     • documents: MUST have source (text).

4. Dev/test-data migrations hygiene

   • Dev/test-data migrations must live under supabase/dev_migrations/, NOT supabase/migrations/
   • Schema migrations (DDL, RLS, indexes, FKs) go in supabase/migrations/
   • Test-data seeding goes in scripts/db-commands/ or supabase/dev_migrations/
   • Do not use supabase db push --include-all on historical migrations; treat it as debugging tool only
   • See supabase/dev_migrations/README.md for usage

Migration Registry Repair (When CLI Fails)

If supabase db push fails with "Remote migration versions not found" (Drift), do NOT run manual SQL in Dashboard. This creates a vicious cycle.

Fix:

1. Ensure DATABASE_URL is set in Railway (required for CLI).
2. Run repair to sync registry with local files:

   # In Railway shell
   supabase migration repair --status applied <version_id>
   # Or for batch:
   supabase migration repair --status applied 20251129... 20251204...
   

3. Then run supabase db push for new migrations.

Schema Definitions

• supabase/schemas/public/ - Table definitions
• supabase/schemas/public/tables/ - Individual table files

Type Generation

• supabase/types/database.types.ts - Generated TypeScript types
• Generate via: supabase gen types typescript

Backend Types

• backend/schemas/generated/ - Generated Python types (if any)

Scripts

• scripts/db-commands/ - Database utilities
• backend/migrations/versions/ - Alembic migrations (if used)

Key Tables and Recent Changes

Holdings Table (public.holdings)

Core columns:

• id, account_id, security_id, quantity, cost_basis
• created_at, updated_at, closed_at

Active vs Closed Holdings (bd-k1c.4):

• Active holdings: closed_at IS NULL - current portfolio positions
• Closed positions: closed_at IS NOT NULL, quantity conventionally 0
• Plaid pipeline soft-closes positions that disappear from broker snapshots (doesn't delete)
• Manual holdings are NOT auto-closed by provider sync

Index:

• Partial index idx_holdings_closed_at ON holdings(closed_at) WHERE closed_at IS NULL for efficient active holdings queries

Current portfolio views: Filter with WHERE closed_at IS NULL

Holdings Snapshots Table (public.holdings_snapshots)

Purpose: Append-only time-series snapshots for historical portfolio analytics (bd-k1c.6)

Core columns:

• snapshot_at (TIMESTAMPTZ) - snapshot time, typically daily at market close
• user_id, account_id, security_id
• quantity, cost_basis, market_value, price_source

Key constraints:

• UNIQUE (snapshot_at, account_id, security_id) for idempotency
• Indexes on (user_id, snapshot_at DESC), (account_id, snapshot_at DESC), (security_id, snapshot_at DESC)

Relationship to holdings:

• Snapshots derived from active holdings + price data
• Snapshot job: backend/scripts/create_holdings_snapshot.py

Provider Security Mappings (public.provider_security_mappings)

Purpose: Map provider security IDs to canonical securities

Natural key (bd-k1c.3):

• UNIQUE (brokerage_connection_id, provider_security_id)
• provider_security_id VARCHAR(255) - stable provider-side identifier (e.g., Plaid security_id)
• provider_payload JSONB - retained for audit, NOT in uniqueness constraint

Index:

• idx_provider_security_mappings_provider_security_id for fast lookups

Used by:

• RawDataService.get_existing_security_mapping
• SecurityResolver._link_provider_mapping (upserts on natural key)

Recent bd-k1c Changes

The bd-k1c epic (Plaid portfolio pipeline hardening) introduced several schema enhancements:

• Holdings soft-close semantics (closed_at column) - distinguishes active vs closed positions
• Time-series snapshots (holdings_snapshots table) - enables historical analytics
• Provider mapping refinement (provider_security_id natural key) - more robust brokerage integrations

See docs/bd-k1c/EPIC_OVERVIEW.md for full context and child features.

Documentation

• Internal: docs/database/SCHEMA.md

Related Areas

• See context-clerk-integration for RLS patterns
• See context-plaid-integration for plaid_prices table and provider mappings
• See context-symbol-resolution for securities table
• See context-portfolio for holdings views and analytics