File size: 5,504 Bytes
1079ca3 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | """
Database setup script — creates Supabase tables for KaushalMitra.
Run once: python scripts/setup_db.py
Tables created:
- candidates
- sessions
- integrity_events
- scores
"""
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import supabase
from config import settings
SCHEMA_SQL = """
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- ── candidates ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS candidates (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
trade TEXT NOT NULL,
district TEXT NOT NULL,
language TEXT NOT NULL DEFAULT 'kn',
fitment_category TEXT,
composite_score NUMERIC(5,2),
integrity_score NUMERIC(5,2),
domain_score NUMERIC(5,2),
communication_score NUMERIC(5,2),
is_flagged BOOLEAN DEFAULT FALSE,
flag_reason TEXT,
reason_card_en TEXT,
reason_card_kn TEXT,
face_embedding_hash TEXT,
duplicate_similarity NUMERIC(4,3),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── sessions ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT UNIQUE NOT NULL,
candidate_name TEXT NOT NULL,
trade TEXT NOT NULL,
district TEXT NOT NULL,
preferred_language TEXT DEFAULT 'kn',
status TEXT DEFAULT 'created',
turn_count INT DEFAULT 0,
transcript_json JSONB,
audio_storage_path TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── integrity_events ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS integrity_events (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT NOT NULL REFERENCES sessions(session_id),
timestamp_ms INT NOT NULL,
event_type TEXT NOT NULL,
face_detected BOOLEAN,
multiple_faces BOOLEAN,
face_coverage NUMERIC(4,3),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── scores ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS scores (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT NOT NULL REFERENCES sessions(session_id),
stage INT NOT NULL,
score_data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── Row Level Security ────────────────────────────────────
ALTER TABLE candidates ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE integrity_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE scores ENABLE ROW LEVEL SECURITY;
-- Policy: service role has full access (backend uses service role key)
CREATE POLICY IF NOT EXISTS "service_role_all" ON candidates
FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY IF NOT EXISTS "service_role_all" ON sessions
FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY IF NOT EXISTS "service_role_all" ON integrity_events
FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY IF NOT EXISTS "service_role_all" ON scores
FOR ALL USING (auth.role() = 'service_role');
-- Index for admin dashboard queries
CREATE INDEX IF NOT EXISTS idx_candidates_district ON candidates(district);
CREATE INDEX IF NOT EXISTS idx_candidates_trade ON candidates(trade);
CREATE INDEX IF NOT EXISTS idx_candidates_fitment ON candidates(fitment_category);
"""
def setup():
if not settings.SUPABASE_URL or not settings.SUPABASE_SERVICE_ROLE_KEY:
print("❌ SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY must be set in .env")
print(" Get them from: Supabase Dashboard → Settings → API")
sys.exit(1)
try:
from supabase import create_client
sb = create_client(settings.SUPABASE_URL, settings.SUPABASE_SERVICE_ROLE_KEY)
# Execute schema via Supabase's SQL editor (rpc)
# Note: For initial setup, you can also paste SCHEMA_SQL directly into
# Supabase Dashboard → SQL Editor
print("ℹ️ Schema SQL generated. Choose how to apply:")
print()
print(" Option A (Recommended): Paste the SQL into Supabase Dashboard")
print(" → https://supabase.com/dashboard → your project → SQL Editor")
print()
print(" Option B: Use psycopg2 with direct connection string")
print(" → Settings → Database → Connection string → Direct")
print()
print("─" * 60)
print(SCHEMA_SQL)
print("─" * 60)
print()
print("✅ Copy the SQL above into Supabase SQL Editor and run it.")
except Exception as e:
print(f"❌ Error: {e}")
sys.exit(1)
if __name__ == "__main__":
setup()
|