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

# 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:

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

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