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:

Work in Railway dev shell
- Confirm: echo $RAILWAY_ENVIRONMENT is non-empty.
- Use the dev DATABASE_URL and SUPABASE_PROJECT_ID.
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.
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/**
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.
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:

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.
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.
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).
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:

Ensure DATABASE_URL is set in Railway (required for CLI).

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...

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