Spaces:
Running
Running
| -- EvalBot platform schema (PostgreSQL / Supabase). Idempotent: safe to run on every start. | |
| create extension if not exists pgcrypto; | |
| create table if not exists companies ( | |
| id uuid primary key default gen_random_uuid(), | |
| name text not null, | |
| website text, | |
| about text, | |
| created_by uuid not null, | |
| created_at timestamptz not null default now() | |
| ); | |
| create table if not exists profiles ( | |
| id uuid primary key, -- = auth.users.id | |
| email text, | |
| full_name text, | |
| role text not null default 'candidate' check (role in ('candidate', 'recruiter')), | |
| company_id uuid references companies(id) on delete set null, | |
| headline text, | |
| target_track text, | |
| locale text not null default 'en', | |
| created_at timestamptz not null default now() | |
| ); | |
| create table if not exists jobs ( | |
| id uuid primary key default gen_random_uuid(), | |
| company_id uuid not null references companies(id) on delete cascade, | |
| created_by uuid not null, | |
| title text not null, | |
| track text not null, | |
| level text not null, | |
| description text, | |
| questions jsonb not null default '[]'::jsonb, -- [{"text": "...", "kind": "technical|behavioral|custom"}] | |
| answer_seconds int not null default 120, | |
| status text not null default 'open' check (status in ('draft', 'open', 'closed')), | |
| share_token text not null unique default encode(gen_random_bytes(9), 'hex'), | |
| created_at timestamptz not null default now() | |
| ); | |
| create index if not exists jobs_company_idx on jobs(company_id); | |
| create table if not exists sessions ( | |
| id uuid primary key default gen_random_uuid(), | |
| user_id uuid not null, | |
| job_id uuid references jobs(id) on delete set null, | |
| mode text not null check (mode in ('mock', 'job', 'upload')), | |
| track text, | |
| level text, | |
| interview_type text, | |
| language text not null default 'en', | |
| status text not null default 'in_progress' check (status in ('in_progress', 'processing', 'completed', 'failed')), | |
| context jsonb not null default '{}'::jsonb, -- cv / job description summary used for questions | |
| overall_score real, | |
| report jsonb, | |
| pdf_path text, | |
| error text, | |
| created_at timestamptz not null default now(), | |
| completed_at timestamptz | |
| ); | |
| create index if not exists sessions_user_idx on sessions(user_id, created_at desc); | |
| create index if not exists sessions_job_idx on sessions(job_id); | |
| create table if not exists answers ( | |
| id uuid primary key default gen_random_uuid(), | |
| session_id uuid not null references sessions(id) on delete cascade, | |
| idx int not null, | |
| question text not null, | |
| kind text, | |
| is_followup boolean not null default false, | |
| audio_path text, | |
| duration_s real, | |
| transcript text, | |
| voice jsonb, | |
| presence jsonb, -- camera metrics computed in the browser (optional) | |
| feedback jsonb, | |
| score int, | |
| created_at timestamptz not null default now(), | |
| unique (session_id, idx) | |
| ); | |
| create table if not exists reviews ( | |
| session_id uuid primary key references sessions(id) on delete cascade, | |
| reviewer_id uuid not null, | |
| status text not null default 'new' check (status in ('new', 'shortlisted', 'rejected', 'hired')), | |
| rating int check (rating between 1 and 5), | |
| notes text, | |
| updated_at timestamptz not null default now() | |
| ); | |
| -- v2: job board, richer job posts and company profiles (idempotent migrations) | |
| alter table companies add column if not exists industry text; | |
| alter table companies add column if not exists size text; | |
| alter table companies add column if not exists location text; | |
| alter table jobs add column if not exists location text; | |
| alter table jobs add column if not exists work_mode text not null default 'onsite'; -- onsite | remote | hybrid | |
| alter table jobs add column if not exists employment_type text not null default 'full_time'; -- full_time | part_time | internship | contract | |
| alter table jobs add column if not exists salary text; | |
| alter table jobs add column if not exists skills jsonb not null default '[]'::jsonb; | |
| alter table jobs add column if not exists deadline date; | |
| alter table jobs add column if not exists visibility text not null default 'public'; -- public (job board) | link (only via link) | |
| alter table jobs add column if not exists views int not null default 0; | |
| create index if not exists jobs_board_idx on jobs(status, visibility, created_at desc); | |
| -- Lock tables against Supabase's public Data API: the app server connects as the database owner | |
| -- (which bypasses RLS), while the anon/authenticated API roles get no rows at all (no policies). | |
| alter table companies enable row level security; | |
| alter table profiles enable row level security; | |
| alter table jobs enable row level security; | |
| alter table sessions enable row level security; | |
| alter table answers enable row level security; | |
| alter table reviews enable row level security; | |
| -- v3: camera analysis, proctoring, video answers, CV profiles | |
| alter table jobs add column if not exists require_camera boolean not null default false; | |
| alter table jobs add column if not exists record_video boolean not null default false; | |
| alter table answers add column if not exists media text not null default 'audio'; -- audio | video | |
| alter table profiles add column if not exists cv jsonb; | |
| alter table profiles add column if not exists cv_text text; | |
| alter table profiles add column if not exists cv_updated_at timestamptz; | |