db-schema

name	db-schema
description	Database schema RAG system. Activates when working with database tables, migrations, queries, or schema changes.
allowed-tools	Read, Grep

Database Schema RAG System

You have access to database schema in ai/schema.jsonl with index in ai/schema_index.json.

CRITICAL: Never Load Entire Schema

The schema file contains ALL tables. Always use the index first.

Retrieval Protocol

Step 1: Identify Target Tables

From user request, identify table names:

• Explicit: ops.tasks, hr.employees
• Implicit: "employee data" → public.users or hr.employees

Step 2: Open Index First

Read: ai/schema_index.json

Find candidate table IDs by matching:

• Keys: table:ops.tasks
• Keywords field

Step 3: Load Relevant Lines Only

From ai/schema.jsonl, load:

• Target table (required)
• Related tables via fks and refs_in (optional)
• Cap at 20 tables maximum

Step 4: For Precise SQL

Use line-range references from index (refs field):

Read: supabase/reference_schema.sql:780-845

Schema Index Structure

ai/schema_index.json:

{
  "table:public.users": {
    "keywords": ["user", "employee", "staff"],
    "refs": "supabase/reference_schema.sql:100-150",
    "fks": ["table:public.departments"],
    "refs_in": ["table:ops.tasks"]
  }
}

Schema JSONL Structure

Each line in ai/schema.jsonl:

{
  "id": "table:schema.table_name",
  "type": "table",
  "schema": "schema_name",
  "name": "table_name",
  "columns": [...],
  "fks": [...],
  "refs_in": [...]
}

Data Warehouse Rules

NO Foreign Keys for Analytics Tables

Tables cards and time_logs must NOT have FK constraints:

• Why: Analytical replica needs raw data
• Solution: Use LEFT JOIN in queries

RLS Required for New Tables

All new tables must have:

ALTER TABLE schema.table ENABLE ROW LEVEL SECURITY;
CREATE POLICY "policy_name" ON schema.table ...;

Migration Standards

Output format for new migrations:

-- Migration: descriptive_name
-- File: supabase/migrations/YYYYMMDDHHMMSS_name.sql

CREATE TABLE ...;
COMMENT ON TABLE ...;
ALTER TABLE ... ENABLE ROW LEVEL SECURITY;
CREATE POLICY ...;

Reference Files

• ai/schema_index.json — Table index (read first)
• ai/schema.jsonl — Detailed schema (read selectively)
• supabase/reference_schema.sql — Full SQL schema
• ai/schema_overrides.json — Manual descriptions
• types/database.types.ts — Generated TypeScript types