db/README.md

# db/ — Schema, seeds, and migration runner

This folder owns **everything database-related** for CyberArena:

```
db/
├── schema/   ← numbered, idempotent SQL migrations (run in order)
├── seed/     ← per-type example JSON (the real seeds live in app/generators/)
├── apply.py  ← run every schema/*.sql against Supabase
└── README.md (this file)
```

## Schema files

Numbered with a 3-digit prefix so a directory listing shows the run
order:

```
000_extensions.sql                              — pgcrypto
001_users.sql                                   — users + leaderboard view
002_encryption_challenges.sql                   — crypto (red+blue)
003_(removed)                                   — was web exploitation
004_code_fixing_challenges.sql                  — code-fixing (blue)
005_log_analysis_challenges.sql                 — log-analysis (blue) + storage bucket
006_vulnerability_hunter_challenges.sql         — vulnerability-hunter (blue)
007_onevone.sql                                 — 1v1 mode (4 tables + claim_win RPC)
008_certificates.sql                            — certificate table + RLS
009_user_completions.sql                        — per-category completion counter
```

Each file is **idempotent**:

  * `CREATE TABLE IF NOT EXISTS …`
  * `DROP POLICY IF EXISTS …` followed by `CREATE POLICY …`
  * `CREATE OR REPLACE FUNCTION …` for the one RPC

Re-running `db/apply.py` after the schema is already in place is a
no-op.

## Running the migrations

```bash
# Dry-run (list the files in order, do not run)
python db/apply.py --dry-run

# Apply to Supabase (requires SUPABASE_SERVICE_KEY in .env)
python db/apply.py
```

The script POSTs every file's contents to the
`/rest/v1/rpc/exec_sql` endpoint. If the `exec_sql` RPC is not yet
defined in your project, create it once via the Supabase SQL editor:

```sql
CREATE OR REPLACE FUNCTION public.exec_sql(query text)
  RETURNS void LANGUAGE plpgsql AS $$
BEGIN EXECUTE query; END $$;
GRANT EXECUTE ON FUNCTION public.exec_sql(text) TO service_role;
```

> The `service_role` key is required because DDL bypasses RLS.

## Seeds

The actual curated seeds (Tier 4 safety net in the AI fallback chain)
live in the Python generator modules under `app/generators/`. The
files in `db/seed/` are **templates** — one example per challenge
type that documents the JSON shape the corresponding Supabase table
expects. Use them as a starting point if you want to manually insert
a custom challenge.

## Checking live state

```bash
python scripts/check_state.py     # one-page health report
python scripts/seed_pools.py --check
```

`check_state.py` reports row counts for every challenge table, the
1v1 tables, certificates, and `user_completions` (per category).