Repository Public
1-- CLIP — initial schema2CREATE EXTENSION IF NOT EXISTS pgcrypto;3CREATE EXTENSION IF NOT EXISTS pg_trgm;45-- ───────────── identity ─────────────6CREATE TABLE users (7 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),8 email text NOT NULL UNIQUE,9 name text NOT NULL DEFAULT '',10 role text NOT NULL CHECK (role IN ('owner','editor','viewer')),11 password_hash text NOT NULL,12 must_change_password boolean NOT NULL DEFAULT true,13 totp_secret text,14 totp_enabled boolean NOT NULL DEFAULT false,15 created_at timestamptz NOT NULL DEFAULT now(),16 last_login_at timestamptz17);18CREATE TABLE sessions (19 token_hash text PRIMARY KEY,20 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,21 created_at timestamptz NOT NULL DEFAULT now(),22 last_seen_at timestamptz NOT NULL DEFAULT now(),23 expires_at timestamptz NOT NULL,24 mfa_passed boolean NOT NULL DEFAULT false,25 user_agent text26);27CREATE INDEX sessions_user ON sessions(user_id);28CREATE TABLE audit_log (29 id bigserial PRIMARY KEY,30 at timestamptz NOT NULL DEFAULT now(),31 user_id uuid REFERENCES users(id) ON DELETE SET NULL,32 action text NOT NULL,33 entity text,34 entity_id text,35 detail jsonb NOT NULL DEFAULT '{}'36);37CREATE INDEX audit_at ON audit_log(at DESC);38CREATE TABLE rate_limits (39 key text NOT NULL,40 window_start timestamptz NOT NULL,41 count int NOT NULL DEFAULT 0,42 PRIMARY KEY (key, window_start)43);4445-- ───────────── editorial ─────────────46CREATE TABLE series (47 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),48 slug text NOT NULL UNIQUE,49 title_en text NOT NULL, title_fr text NOT NULL,50 description_en text NOT NULL, description_fr text NOT NULL,51 kind text NOT NULL CHECK (kind IN ('fiction','factual','experimental')),52 template jsonb NOT NULL DEFAULT '{}',53 accent text NOT NULL DEFAULT '#e8c27a',54 sort int NOT NULL DEFAULT 055);56CREATE TABLE tags (57 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),58 slug text NOT NULL UNIQUE,59 kind text NOT NULL CHECK (kind IN ('genre','mood','topic','technique','style')),60 label_en text NOT NULL, label_fr text NOT NULL61);62CREATE TABLE collections (63 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),64 slug text NOT NULL UNIQUE,65 title_en text NOT NULL, title_fr text NOT NULL,66 description_en text NOT NULL DEFAULT '', description_fr text NOT NULL DEFAULT '',67 sort int NOT NULL DEFAULT 068);6970-- ───────────── production (private) ─────────────71CREATE TABLE projects (72 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),73 title text NOT NULL,74 series_id uuid REFERENCES series(id) ON DELETE SET NULL,75 brief jsonb NOT NULL,76 language text NOT NULL CHECK (language IN ('en','fr','bilingual')),77 mode text NOT NULL CHECK (mode IN ('manual','assisted','autonomous')),78 status text NOT NULL DEFAULT 'draft' CHECK (status IN79 ('draft','pitched','scripted','storyboarded','keyframes','voiced','filmed','assembled','qa_passed','qa_failed','approved','published','archived')),80 running_stage text,81 blocked_reason text,82 error text,83 video_model text,84 dedupe_hash text,85 origin text NOT NULL DEFAULT 'studio' CHECK (origin IN ('studio','scheduler','api')),86 created_by uuid REFERENCES users(id) ON DELETE SET NULL,87 created_at timestamptz NOT NULL DEFAULT now(),88 updated_at timestamptz NOT NULL DEFAULT now()89);90CREATE INDEX projects_status ON projects(status, updated_at DESC);91CREATE INDEX projects_dedupe ON projects(dedupe_hash);9293CREATE TABLE concept_candidates (94 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),95 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,96 idx int NOT NULL,97 pitch jsonb NOT NULL,98 scores jsonb NOT NULL,99 total numeric NOT NULL,100 selected boolean NOT NULL DEFAULT false,101 created_at timestamptz NOT NULL DEFAULT now(),102 UNIQUE (project_id, idx)103);104CREATE TABLE script_versions (105 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),106 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,107 version int NOT NULL,108 script jsonb NOT NULL,109 research jsonb,110 author text NOT NULL DEFAULT 'ai',111 created_at timestamptz NOT NULL DEFAULT now(),112 UNIQUE (project_id, version)113);114CREATE TABLE bibles (115 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),116 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,117 version int NOT NULL,118 bible jsonb NOT NULL,119 created_at timestamptz NOT NULL DEFAULT now(),120 UNIQUE (project_id, version)121);122CREATE TABLE storyboards (123 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),124 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,125 version int NOT NULL,126 plan jsonb NOT NULL,127 created_at timestamptz NOT NULL DEFAULT now(),128 UNIQUE (project_id, version)129);130CREATE TABLE assets (131 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),132 sha256 text NOT NULL,133 ext text NOT NULL,134 mime text NOT NULL,135 bytes bigint NOT NULL,136 kind text NOT NULL CHECK (kind IN ('keyframe','shot_video','shot_audio','narration','master','delivery_low','poster','captions','thumb','intermediate')),137 width int, height int, duration numeric,138 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,139 public boolean NOT NULL DEFAULT false,140 meta jsonb NOT NULL DEFAULT '{}',141 created_at timestamptz NOT NULL DEFAULT now()142);143CREATE INDEX assets_sha ON assets(sha256);144CREATE INDEX assets_project ON assets(project_id);145CREATE TABLE shots (146 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),147 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,148 idx int NOT NULL,149 title text NOT NULL DEFAULT '',150 narration text NOT NULL DEFAULT '',151 narration_alt text NOT NULL DEFAULT '',152 camera text NOT NULL DEFAULT '',153 action text NOT NULL DEFAULT '',154 continuity text NOT NULL DEFAULT '',155 transition text NOT NULL DEFAULT 'cut',156 planned_duration numeric NOT NULL DEFAULT 6,157 image_prompt text NOT NULL DEFAULT '',158 video_prompt text NOT NULL DEFAULT '',159 status text NOT NULL DEFAULT 'planned' CHECK (status IN ('planned','keyframe','voiced','filmed','failed','needs_approval')),160 image_asset_id uuid REFERENCES assets(id) ON DELETE SET NULL,161 audio_asset_id uuid REFERENCES assets(id) ON DELETE SET NULL,162 video_asset_id uuid REFERENCES assets(id) ON DELETE SET NULL,163 narration_duration numeric,164 timestamps jsonb,165 image_rev int NOT NULL DEFAULT 0,166 video_rev int NOT NULL DEFAULT 0,167 voice_rev int NOT NULL DEFAULT 0,168 regen_count int NOT NULL DEFAULT 0,169 regen_approved boolean NOT NULL DEFAULT false,170 error text,171 updated_at timestamptz NOT NULL DEFAULT now(),172 UNIQUE (project_id, idx)173);174CREATE TABLE prompt_versions (175 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),176 shot_id uuid NOT NULL REFERENCES shots(id) ON DELETE CASCADE,177 kind text NOT NULL CHECK (kind IN ('image','video','voice')),178 version int NOT NULL,179 prompt text NOT NULL,180 author text NOT NULL DEFAULT 'ai',181 parent_id uuid REFERENCES prompt_versions(id) ON DELETE SET NULL,182 created_at timestamptz NOT NULL DEFAULT now(),183 UNIQUE (shot_id, kind, version)184);185CREATE TABLE provider_jobs (186 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),187 idem_key text NOT NULL UNIQUE,188 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,189 shot_id uuid REFERENCES shots(id) ON DELETE SET NULL,190 stage text NOT NULL,191 kind text NOT NULL CHECK (kind IN ('llm','image','video','tts','search')),192 provider text NOT NULL,193 model text NOT NULL,194 settings jsonb NOT NULL DEFAULT '{}',195 prompt text,196 input_hash text NOT NULL,197 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','submitted','done','failed')),198 request_id text,199 attempts int NOT NULL DEFAULT 0,200 error text,201 est_usd numeric,202 cost_usd numeric,203 cost_source text CHECK (cost_source IN ('ticks','computed','unknown')),204 asset_id uuid REFERENCES assets(id) ON DELETE SET NULL,205 output jsonb,206 created_at timestamptz NOT NULL DEFAULT now(),207 started_at timestamptz,208 finished_at timestamptz209);210CREATE INDEX provider_jobs_project ON provider_jobs(project_id, created_at DESC);211CREATE INDEX provider_jobs_status ON provider_jobs(status, created_at DESC);212CREATE TABLE cost_entries (213 id bigserial PRIMARY KEY,214 at timestamptz NOT NULL DEFAULT now(),215 job_id uuid REFERENCES provider_jobs(id) ON DELETE SET NULL,216 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,217 shot_id uuid,218 kind text NOT NULL,219 model text NOT NULL,220 usd numeric NOT NULL,221 source text NOT NULL,222 failed boolean NOT NULL DEFAULT false223);224CREATE INDEX cost_at ON cost_entries(at DESC);225CREATE INDEX cost_project ON cost_entries(project_id);226CREATE TABLE stage_runs (227 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),228 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,229 stage text NOT NULL,230 status text NOT NULL CHECK (status IN ('queued','running','done','failed','blocked')),231 attempts int NOT NULL DEFAULT 0,232 error text,233 detail jsonb NOT NULL DEFAULT '{}',234 queued_at timestamptz NOT NULL DEFAULT now(),235 started_at timestamptz,236 finished_at timestamptz237);238CREATE INDEX stage_runs_project ON stage_runs(project_id, queued_at DESC);239CREATE INDEX stage_runs_status ON stage_runs(status, queued_at DESC);240CREATE TABLE film_versions (241 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),242 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,243 version int NOT NULL,244 manifest jsonb NOT NULL,245 master_asset_id uuid REFERENCES assets(id),246 low_asset_id uuid REFERENCES assets(id),247 poster_asset_id uuid REFERENCES assets(id),248 duration numeric NOT NULL,249 created_at timestamptz NOT NULL DEFAULT now(),250 UNIQUE (project_id, version)251);252CREATE TABLE audio_tracks (253 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),254 film_version_id uuid NOT NULL REFERENCES film_versions(id) ON DELETE CASCADE,255 kind text NOT NULL CHECK (kind IN ('narration','bed','mix')),256 language text,257 voice_id text,258 asset_id uuid REFERENCES assets(id),259 license text NOT NULL260);261CREATE TABLE subtitle_tracks (262 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),263 film_version_id uuid NOT NULL REFERENCES film_versions(id) ON DELETE CASCADE,264 language text NOT NULL,265 kind text NOT NULL CHECK (kind IN ('spoken','translation')),266 asset_id uuid NOT NULL REFERENCES assets(id),267 UNIQUE (film_version_id, language)268);269CREATE TABLE quality_reviews (270 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),271 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,272 film_version_id uuid NOT NULL REFERENCES film_versions(id) ON DELETE CASCADE,273 technical jsonb NOT NULL,274 critique jsonb,275 score numeric,276 passed boolean NOT NULL,277 created_at timestamptz NOT NULL DEFAULT now()278);279CREATE TABLE approvals (280 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),281 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,282 film_version_id uuid REFERENCES film_versions(id) ON DELETE SET NULL,283 user_id uuid REFERENCES users(id) ON DELETE SET NULL,284 decision text NOT NULL CHECK (decision IN ('approve','reject','override_qa','approve_regeneration','auto')),285 note text NOT NULL DEFAULT '',286 at timestamptz NOT NULL DEFAULT now()287);288289-- ───────────── public ─────────────290CREATE TABLE films (291 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),292 slug text NOT NULL UNIQUE,293 project_id uuid UNIQUE REFERENCES projects(id) ON DELETE SET NULL,294 film_version_id uuid REFERENCES film_versions(id) ON DELETE SET NULL,295 series_id uuid REFERENCES series(id) ON DELETE SET NULL,296 episode int,297 title_en text NOT NULL, title_fr text NOT NULL,298 synopsis_en text NOT NULL, synopsis_fr text NOT NULL,299 takeaway_en text NOT NULL DEFAULT '', takeaway_fr text NOT NULL DEFAULT '',300 language text NOT NULL,301 kind text NOT NULL CHECK (kind IN ('fiction','factual','experimental')),302 duration numeric NOT NULL,303 width int NOT NULL, height int NOT NULL,304 master_sha text NOT NULL, master_bytes bigint NOT NULL,305 low_sha text,306 poster_sha text NOT NULL,307 thumb_sha text,308 captions jsonb NOT NULL DEFAULT '{}',309 provenance jsonb NOT NULL DEFAULT '{}',310 sources jsonb NOT NULL DEFAULT '[]',311 status text NOT NULL DEFAULT 'published' CHECK (status IN ('published','scheduled','unpublished','hold')),312 featured boolean NOT NULL DEFAULT false,313 published_at timestamptz,314 scheduled_for timestamptz,315 quality_score numeric,316 views int NOT NULL DEFAULT 0,317 likes int NOT NULL DEFAULT 0,318 embedding real[] NOT NULL DEFAULT '{}',319 search tsvector,320 created_at timestamptz NOT NULL DEFAULT now()321);322CREATE INDEX films_published ON films(published_at DESC) WHERE status = 'published';323CREATE INDEX films_series ON films(series_id, episode);324CREATE INDEX films_search ON films USING gin(search);325CREATE INDEX films_trgm ON films USING gin((title_en || ' ' || title_fr) gin_trgm_ops);326CREATE TABLE film_tags (327 film_id uuid NOT NULL REFERENCES films(id) ON DELETE CASCADE,328 tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,329 PRIMARY KEY (film_id, tag_id)330);331CREATE INDEX film_tags_tag ON film_tags(tag_id);332CREATE TABLE collection_films (333 collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE,334 film_id uuid NOT NULL REFERENCES films(id) ON DELETE CASCADE,335 sort int NOT NULL DEFAULT 0,336 PRIMARY KEY (collection_id, film_id)337);338CREATE TABLE publications (339 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),340 film_id uuid NOT NULL REFERENCES films(id) ON DELETE CASCADE,341 action text NOT NULL CHECK (action IN ('publish','schedule','unpublish','hold')),342 user_id uuid REFERENCES users(id) ON DELETE SET NULL,343 note text NOT NULL DEFAULT '',344 at timestamptz NOT NULL DEFAULT now()345);346CREATE TABLE reactions (347 film_id uuid NOT NULL REFERENCES films(id) ON DELETE CASCADE,348 visitor text NOT NULL,349 kind text NOT NULL CHECK (kind IN ('like')),350 at timestamptz NOT NULL DEFAULT now(),351 PRIMARY KEY (film_id, visitor, kind)352);353CREATE TABLE analytics_events (354 id bigserial PRIMARY KEY,355 at timestamptz NOT NULL DEFAULT now(),356 film_id uuid REFERENCES films(id) ON DELETE CASCADE,357 kind text NOT NULL CHECK (kind IN ('view','complete','share')),358 visitor text NOT NULL,359 locale text360);361CREATE INDEX analytics_film ON analytics_events(film_id, at DESC);362CREATE UNIQUE INDEX analytics_dedupe ON analytics_events(film_id, kind, visitor);363CREATE TABLE reports (364 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),365 at timestamptz NOT NULL DEFAULT now(),366 film_id uuid REFERENCES films(id) ON DELETE SET NULL,367 reason text NOT NULL,368 detail text NOT NULL DEFAULT '',369 contact text NOT NULL DEFAULT '',370 status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','resolved','dismissed')),371 resolved_by uuid REFERENCES users(id) ON DELETE SET NULL372);373374-- ───────────── control ─────────────375CREATE TABLE budget_policy (376 id int PRIMARY KEY CHECK (id = 1),377 kill_switch boolean NOT NULL DEFAULT false,378 per_shot_usd numeric NOT NULL DEFAULT 2.50,379 per_film_usd numeric NOT NULL DEFAULT 8.00,380 per_day_usd numeric NOT NULL DEFAULT 25.00,381 per_month_usd numeric NOT NULL DEFAULT 150.00,382 max_regenerations int NOT NULL DEFAULT 2,383 concurrency int NOT NULL DEFAULT 3,384 auto_publish boolean NOT NULL DEFAULT false,385 scheduler_enabled boolean NOT NULL DEFAULT false,386 films_per_day int NOT NULL DEFAULT 1,387 default_mode text NOT NULL DEFAULT 'assisted',388 qa_threshold numeric NOT NULL DEFAULT 6.5,389 updated_at timestamptz NOT NULL DEFAULT now()390);391INSERT INTO budget_policy (id) VALUES (1);392CREATE TABLE schedules (393 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),394 series_id uuid NOT NULL REFERENCES series(id) ON DELETE CASCADE,395 theme text NOT NULL DEFAULT '',396 language text NOT NULL DEFAULT 'en',397 weekdays int[] NOT NULL DEFAULT '{1}',398 hour int NOT NULL DEFAULT 9,399 mode text NOT NULL DEFAULT 'autonomous',400 enabled boolean NOT NULL DEFAULT true,401 last_run_on date,402 created_at timestamptz NOT NULL DEFAULT now()403);404CREATE TABLE settings (405 key text PRIMARY KEY,406 value jsonb NOT NULL,407 updated_at timestamptz NOT NULL DEFAULT now()408);409