db/apply.py

"""Apply every SQL file in db/schema/ in numeric order to the Supabase database.

Usage:
    python db/apply.py                # apply all pending
    python db/apply.py --dry-run      # print files only
    python db/apply.py --status       # show which files have run

Each file is expected to be idempotent (every CREATE uses IF NOT EXISTS
and policies use DROP POLICY IF EXISTS + CREATE). The script therefore
does not need a migrations ledger — re-running it is safe.

Connection: reads SUPABASE_URL and SUPABASE_SERVICE_KEY from .env. The
service role key is required because we run DDL. If you only have the
anon key, run the same SQL through the Supabase SQL editor.
"""

import argparse
import os
import sys
from pathlib import Path

try:
    from dotenv import load_dotenv
except ImportError:
    print("Missing dependency: python-dotenv. Run `pip install python-dotenv`.")
    sys.exit(1)

try:
    import httpx
except ImportError:
    print("Missing dependency: httpx. Run `pip install httpx`.")
    sys.exit(1)


ROOT = Path(__file__).resolve().parent.parent
SCHEMA_DIR = Path(__file__).resolve().parent / "schema"
ENV_PATH = ROOT / ".env"


def _load_env() -> None:
    if ENV_PATH.exists():
        load_dotenv(ENV_PATH)
    else:
        print(f"⚠️  {ENV_PATH} not found — relying on ambient environment")


def _sql_files() -> list[Path]:
    """Return schema files in numeric order: 000_*, 001_*, …"""
    files = sorted(
        p for p in SCHEMA_DIR.glob("*.sql")
        if p.name[:3].isdigit()
    )
    return files


def _postgres_url() -> str:
    """Return the Supabase PostgREST root URL."""
    base = os.environ.get("SUPABASE_URL", "").rstrip("/")
    if not base:
        sys.exit("SUPABASE_URL is not set in the environment or .env")
    return base


def _service_key() -> str:
    key = os.environ.get("SUPABASE_SERVICE_KEY") or os.environ.get("SUPABASE_SERVICE_ROLE_KEY", "")
    if not key:
        sys.exit("SUPABASE_SERVICE_KEY is not set (required for DDL).")
    return key


def _exec_sql(client: httpx.Client, sql: str, label: str) -> None:
    """Run a single SQL file via the PostgREST ``rpc`` endpoint.

    Supabase exposes a `pg_dump`/SQL-exec function only on hosted
    instances, so for a generic DDL apply we use the project's REST
    endpoint with a custom RPC named ``exec_sql`` if present, otherwise
    we fall back to the SQL editor. Here we POST the raw SQL to the
    ``/rest/v1/rpc/exec_sql`` endpoint. The companion function lives
    in db/optional/exec_sql_fn.sql (not bundled).
    """
    url = f"{_postgres_url()}/rest/v1/rpc/exec_sql"
    r = client.post(url, json={"query": sql})
    if r.status_code == 404:
        sys.exit(
            "exec_sql RPC not found. Create it once in the Supabase SQL editor:\n"
            "  CREATE OR REPLACE FUNCTION public.exec_sql(query text) RETURNS void\n"
            "    LANGUAGE plpgsql AS $$ BEGIN EXECUTE query; END $$;\n"
            "  GRANT EXECUTE ON FUNCTION public.exec_sql(text) TO service_role;\n"
            "Re-run this script afterwards."
        )
    if r.status_code not in (200, 204):
        sys.exit(f"❌ {label}: HTTP {r.status_code} — {r.text[:300]}")
    print(f"✅ {label}")


def main() -> int:
    parser = argparse.ArgumentParser(description="Apply db/schema/*.sql in order.")
    parser.add_argument("--dry-run", action="store_true", help="list files only")
    parser.add_argument("--status", action="store_true", help="list files only (alias)")
    args = parser.parse_args()

    _load_env()
    files = _sql_files()
    if not files:
        print(f"No .sql files found in {SCHEMA_DIR}")
        return 0

    print(f"Found {len(files)} schema files in {SCHEMA_DIR}:")
    for f in files:
        print(f"  - {f.name}")

    if args.dry_run or args.status:
        return 0

    key = _service_key()
    with httpx.Client(timeout=60, headers={
        "apikey": key,
        "Authorization": f"Bearer {key}",
        "Content-Type": "application/json",
    }) as client:
        for f in files:
            sql = f.read_text(encoding="utf-8")
            _exec_sql(client, sql, f.name)

    print("\nAll schema files applied.")
    return 0


if __name__ == "__main__":
    sys.exit(main())