Spaces:
Sleeping
Sleeping
File size: 2,727 Bytes
4a90ac1 | 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 | -- backend/db/schema.sql
-- Run once via: uv run python backend/db/apply_schema.py
-- batches first (no FK to users yet — circular dependency resolved below)
CREATE TABLE IF NOT EXISTS batches (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
instructor_id UUID NOT NULL,
class_code TEXT NOT NULL UNIQUE DEFAULT upper(substring(gen_random_uuid()::text, 1, 8)),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- users references batches
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
role TEXT NOT NULL CHECK (role IN ('student', 'instructor')),
batch_id UUID REFERENCES batches(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- add FK from batches.instructor_id → users.id (deferred until users exists)
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'batches_instructor_id_fkey'
AND table_name = 'batches'
) THEN
ALTER TABLE batches
ADD CONSTRAINT batches_instructor_id_fkey
FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE;
END IF;
END $$;
CREATE TABLE IF NOT EXISTS refresh_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash TEXT NOT NULL,
expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS topics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
batch_id UUID NOT NULL REFERENCES batches(id) ON DELETE CASCADE,
name TEXT NOT NULL,
is_unlocked BOOLEAN NOT NULL DEFAULT FALSE,
order_index INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS questions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
question_text TEXT NOT NULL,
difficulty TEXT NOT NULL CHECK (difficulty IN ('easy', 'medium', 'hard')) DEFAULT 'medium',
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS interview_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
status TEXT NOT NULL CHECK (status IN ('active', 'completed')) DEFAULT 'active',
score INTEGER CHECK (score >= 0 AND score <= 100),
feedback JSONB,
started_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ
);
|