schema.sql

-- Media library: footage, stills and audio the user uploads. Stored in R2
-- under `key`; an edit references them as `asset:<id>`.
CREATE TABLE IF NOT EXISTS assets (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  key TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
  size INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  -- Edit-service staging pointer: uploaded once, reused by every export.
  -- Staged copies expire (~30 days); exports re-stage transparently when the
  -- pointer is missing or stale, so this is a cache, not a source of truth.
  service_key TEXT,
  service_key_expires_at TEXT,
  -- Media length in seconds, probed client-side at upload (the browser reads
  -- it from the local file instantly). Data, not a runtime probe — the
  -- timeline needs it synchronously, and moov-at-end files make network
  -- probing arbitrarily slow.
  duration REAL,
  -- Small 360p transcode used for AI analysis (models take base64 with a hard
  -- request cap; full-res footage doesn't fit). Made once via the edit
  -- service, cached here in app storage.
  proxy_key TEXT
);

-- Footage edit projects: the EDL (edit decision list) JSON is the document.
-- Clips reference media-library assets as "asset:<id>"; exports resolve them
-- to staged sources and run on the managed edit service.
CREATE TABLE IF NOT EXISTS edit_projects (
  id TEXT PRIMARY KEY DEFAULT (
    lower(hex(randomblob(4))) || '-' ||
    lower(hex(randomblob(2))) || '-4' ||
    substr(lower(hex(randomblob(2))), 2) || '-' ||
    substr('89ab', abs(random()) % 4 + 1, 1) ||
    substr(lower(hex(randomblob(2))), 2) || '-' ||
    lower(hex(randomblob(6)))
  ),
  name TEXT NOT NULL,
  edl TEXT NOT NULL,
  -- The video's purpose ("30s product teaser for Instagram, energetic").
  -- Anchors every AI call — cuts are only "effective" relative to a goal.
  brief TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Export jobs: one row per export of an edit project. The MP4 is copied into
-- this app's storage and served from output_url. status: exporting | completed | failed.
CREATE TABLE IF NOT EXISTS export_jobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'exporting',
  output_url TEXT,
  error TEXT,
  duration REAL,
  size INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_export_jobs_project ON export_jobs(project_id);

-- A project's share link: anyone with /s/<token> can watch the export it is
-- pinned to, without signing in. A later export (a draft, say) never reaches
-- viewers until someone moves the pin. The token is the capability, so turning
-- the link off deletes the row and turning it on again mints a new one.
CREATE TABLE IF NOT EXISTS share_links (
  token TEXT PRIMARY KEY,
  project_id TEXT NOT NULL UNIQUE,
  export_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- App-wide settings, one row per key. Today only `drive_folder`: the Google
-- Drive folder the picker is limited to, stored as JSON {"id","name"}. Absent
-- means the whole Drive is browsable.
CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Long footage lives on the managed media service instead of this app's
-- storage; the row then carries the service's id and `key` holds no object.
ALTER TABLE assets ADD COLUMN media_uid TEXT;

-- The media service's transcript of a clip (WebVTT, cue-timed), fetched once
-- it is ready. NULL: not fetched yet. '': known to have no speech. Captions are
-- worked out from it on every preview and export, never stored per caption.
ALTER TABLE assets ADD COLUMN transcript TEXT;
ALTER TABLE assets ADD COLUMN transcript_lang TEXT;

-- A video upload in flight from a browser to the media service, from the
-- moment it is opened until it joins the library as an asset, or is
-- cancelled or expires. It is how the app knows the video is its own.
CREATE TABLE IF NOT EXISTS media_uploads (
  uid TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  content_type TEXT NOT NULL,
  size INTEGER NOT NULL,
  duration REAL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- An export renders in the background on the edit service; this is its job
-- there. The row stays 'exporting' until a read of the job finds the outcome
-- and settles it, so a closed tab or a long render never loses the export.
ALTER TABLE export_jobs ADD COLUMN service_job_id TEXT;

-- Google Drive folders a project takes its footage from: shared "with the
-- link", so nothing is connected and anyone with the link could read them.
-- `language` is what the clips' speech is transcribed in.
CREATE TABLE IF NOT EXISTS footage_sources (
  project_id TEXT NOT NULL,
  folder_id TEXT NOT NULL,
  name TEXT NOT NULL,
  language TEXT NOT NULL DEFAULT 'en',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (project_id, folder_id)
);

-- One row per source video in a project. Drive-folder copies belong to the
-- project and are deleted with it; library references (source_kind below)
-- never own the video and do not hide it from the library. Work advances (see
-- src/server/footage.ts):
--   status:     waiting | importing | ready | failed | removed
--   log_status: NULL (not started) | preparing | running | done | failed
-- `log` is the analysis's log of the clip as JSON (ClipLog), times in seconds.
-- `folder` is the path below the shared folder, its own name first ("Day 1/Cam B").
CREATE TABLE IF NOT EXISTS project_footage (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  project_id TEXT NOT NULL,
  drive_file_id TEXT NOT NULL,
  name TEXT NOT NULL,
  folder TEXT NOT NULL DEFAULT '',
  language TEXT NOT NULL DEFAULT 'en',
  status TEXT NOT NULL DEFAULT 'waiting',
  error TEXT,
  asset_id TEXT,
  log_status TEXT,
  log_job TEXT,
  log TEXT,
  log_error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (project_id, drive_file_id)
);

CREATE INDEX IF NOT EXISTS idx_project_footage_asset ON project_footage(asset_id);

-- When the project's next footage step is booked on the platform queue
-- (ISO time). NULL: none booked.
ALTER TABLE edit_projects ADD COLUMN footage_step_at TEXT;

-- Highlights (src/server/highlights.ts): the selects an editor looks at
-- first, judged from each clip's log and transcript against the brief.
-- `highlights_at`: when the project asked for them; NULL, it hasn't. Clips
-- logged after that join in by themselves.
ALTER TABLE edit_projects ADD COLUMN highlights_at TEXT;
-- When a person stopped the reading; NULL, it runs. Stopped, clips in line go
-- back and clips logged later don't join, until highlights are asked again.
ALTER TABLE edit_projects ADD COLUMN highlights_stopped_at TEXT;
-- Per clip: NULL (not asked) | waiting | running | done | failed. A clip done
-- with a skip_reason was judged not worth an editor's time, and why.
ALTER TABLE project_footage ADD COLUMN highlights_status TEXT;
ALTER TABLE project_footage ADD COLUMN highlights_error TEXT;
ALTER TABLE project_footage ADD COLUMN skip_reason TEXT;
-- The original's frame rate and size, as the media service measured them:
-- what a timeline for the editor's own software is laid out with.
ALTER TABLE project_footage ADD COLUMN fps REAL;
ALTER TABLE project_footage ADD COLUMN width INTEGER;
ALTER TABLE project_footage ADD COLUMN height INTEGER;

-- One row per pick: a soundbite (speech that stands on its own) or a stretch
-- of b-roll, `src_in`..`src_out` seconds into its clip. `pick` is a person's
-- call: NULL until reviewed, then keep | drop. Finding again replaces only
-- unreviewed picks; `origin` is ai, or person for one a person added.
CREATE TABLE IF NOT EXISTS footage_highlights (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  project_id TEXT NOT NULL,
  footage_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  src_in REAL NOT NULL,
  src_out REAL NOT NULL,
  text TEXT NOT NULL DEFAULT '',
  speaker TEXT NOT NULL DEFAULT '',
  score INTEGER NOT NULL DEFAULT 3,
  reason TEXT NOT NULL DEFAULT '',
  pick TEXT,
  origin TEXT NOT NULL DEFAULT 'ai',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_footage_highlights_project ON footage_highlights(project_id, score);
CREATE INDEX IF NOT EXISTS idx_footage_highlights_clip ON footage_highlights(footage_id);

-- A clip Google Drive refuses to hand over (its download limit for the file,
-- or for the owner's shared files, is used up) waits until `retry_at` (ISO
-- time) and is tried again, further apart each time; `drive_tries` counts the
-- refusals. NULL retry_at: it can be tried now.
ALTER TABLE project_footage ADD COLUMN retry_at TEXT;
ALTER TABLE project_footage ADD COLUMN drive_tries INTEGER NOT NULL DEFAULT 0;

-- How a clip comes in when the org has a Drive connection: 0 downloaded
-- through it; 1 too big for that, so from a copy the connection makes (see
-- copy_id); 2 neither worked, so by the shared link.
ALTER TABLE project_footage ADD COLUMN link_only INTEGER NOT NULL DEFAULT 0;

-- A file too big to download through the Drive connection comes in from a
-- copy the connection makes in its own account, shared with the link: this
-- is that copy, deleted once the import is over.
ALTER TABLE project_footage ADD COLUMN copy_id TEXT;

-- A source the media service's video host refuses for its bitrate (over
-- 200 Mbps: all-intra camera files) is re-encoded on the way in: `transcode`
-- 1 marks the clip for it, 2 once the host has the re-encode; `transcode_job`
-- is the re-encode under way. Only a refused re-encode fails the clip: any
-- other refusal sends it back to be re-encoded.
ALTER TABLE project_footage ADD COLUMN transcode INTEGER NOT NULL DEFAULT 0;
ALTER TABLE project_footage ADD COLUMN transcode_job TEXT;

-- A file Google Drive won't hand over whole (its download limit) still serves
-- byte ranges, so it comes in a piece at a time, into an upload on the media
-- service (src/server/relay.ts). Set while that upload is open: its id and
-- link, when the link expires, the file's size, and how much is in so far
-- (for showing; the upload itself says where it stands).
ALTER TABLE project_footage ADD COLUMN upload_uid TEXT;
ALTER TABLE project_footage ADD COLUMN upload_url TEXT;
ALTER TABLE project_footage ADD COLUMN upload_expires TEXT;
ALTER TABLE project_footage ADD COLUMN upload_size INTEGER;
ALTER TABLE project_footage ADD COLUMN upload_done INTEGER;

-- How many highlights the project wants; NULL: every pick worth a look. Once
-- every clip is read, one more model call puts the picks in order for the
-- brief, the best set of that many first (src/server/highlights.ts). The
-- ordering: NULL | waiting | running | done | failed, with the error, and
-- when it was last claimed (a claim older than five minutes is taken over).
ALTER TABLE edit_projects ADD COLUMN highlights_wanted INTEGER;
ALTER TABLE edit_projects ADD COLUMN highlights_rank_status TEXT;
ALTER TABLE edit_projects ADD COLUMN highlights_rank_error TEXT;
ALTER TABLE edit_projects ADD COLUMN highlights_rank_at TEXT;
-- A pick's place in that order, 1 the best. The best are the kept picks,
-- then the first in this order; NULL: not placed yet.
ALTER TABLE footage_highlights ADD COLUMN rank INTEGER;

-- Standalone library imports. Upload URLs are private capabilities, never API output.
CREATE TABLE IF NOT EXISTS drive_imports (
  id TEXT PRIMARY KEY,
  file_id TEXT NOT NULL,
  service TEXT NOT NULL,
  account TEXT,
  source_version TEXT NOT NULL,
  name TEXT NOT NULL,
  content_type TEXT NOT NULL,
  size INTEGER NOT NULL,
  duration REAL,
  state TEXT NOT NULL DEFAULT 'queued',
  received INTEGER NOT NULL DEFAULT 0,
  upload_uid TEXT,
  upload_url TEXT,
  upload_expires_at TEXT,
  attempts INTEGER NOT NULL DEFAULT 0,
  failures INTEGER NOT NULL DEFAULT 0,
  cancel_requested INTEGER NOT NULL DEFAULT 0,
  cleanup_pending INTEGER NOT NULL DEFAULT 0,
  error_code TEXT,
  asset_id TEXT,
  next_step_at INTEGER NOT NULL DEFAULT 0,
  lease_token TEXT,
  lease_until INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS drive_imports_active_file ON drive_imports(file_id)
  WHERE state IN ('queued', 'uploading', 'processing', 'cancelling');
CREATE INDEX IF NOT EXISTS drive_imports_due ON drive_imports(state, next_step_at);

-- Project footage can reference an existing library video without owning it.
-- drive_file_id retains its legacy name: a real Drive ID for drive sources,
-- or the stable asset:<id> source key for library references. source_kind,
-- never the key's spelling, determines ownership and import behavior.
ALTER TABLE project_footage ADD COLUMN source_kind TEXT NOT NULL DEFAULT 'drive';
CREATE INDEX IF NOT EXISTS idx_project_footage_source ON project_footage(project_id, source_kind, status);

-- Durable deletion intent: an asset being removed cannot gain new analysis
-- references while its media deletion is in flight. A failed cleanup can be retried.
ALTER TABLE assets ADD COLUMN deleting INTEGER NOT NULL DEFAULT 0;

-- Reusable source selections. Project rankings remain in footage_highlights;
-- these ranges and their media survive project deletion and regeneration.
CREATE TABLE IF NOT EXISTS library_highlights (
  id TEXT PRIMARY KEY,
  asset_id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('soundbite', 'broll')),
  src_in REAL NOT NULL,
  src_out REAL NOT NULL,
  text TEXT NOT NULL DEFAULT '',
  speaker TEXT NOT NULL DEFAULT '',
  source_project_id TEXT,
  deleted_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(asset_id, kind, src_in, src_out)
);
CREATE INDEX IF NOT EXISTS library_highlights_created ON library_highlights(created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS library_highlights_asset ON library_highlights(asset_id, deleted_at);

-- Publish in the same transaction as the suggestion: an asset deletion claim
-- or detached footage cannot race a background result into the shared catalog.
CREATE TRIGGER IF NOT EXISTS publish_highlight_insert AFTER INSERT ON footage_highlights
BEGIN
  INSERT OR IGNORE INTO library_highlights (id, asset_id, kind, src_in, src_out, text, speaker, source_project_id, created_at)
  SELECT NEW.id, a.id, NEW.kind, NEW.src_in, NEW.src_out, NEW.text, NEW.speaker, NEW.project_id, NEW.created_at
  FROM project_footage f JOIN assets a ON a.id = f.asset_id
  WHERE f.id = NEW.footage_id AND f.project_id = NEW.project_id AND f.status = 'ready' AND a.deleting = 0;
END;
CREATE TRIGGER IF NOT EXISTS publish_highlight_edit AFTER UPDATE OF src_in, src_out, text, speaker ON footage_highlights
BEGIN
  INSERT INTO library_highlights (id, asset_id, kind, src_in, src_out, text, speaker, source_project_id)
  SELECT lower(hex(randomblob(8))), a.id, NEW.kind, NEW.src_in, NEW.src_out, NEW.text, NEW.speaker, NEW.project_id
  FROM project_footage f JOIN assets a ON a.id = f.asset_id
  WHERE f.id = NEW.footage_id AND f.project_id = NEW.project_id AND f.status = 'ready' AND a.deleting = 0
  ON CONFLICT(asset_id, kind, src_in, src_out) DO UPDATE SET text = excluded.text, speaker = excluded.speaker;
END;

-- A retry of Create portrait clips returns the projects already created.
ALTER TABLE edit_projects ADD COLUMN creation_batch TEXT;
ALTER TABLE edit_projects ADD COLUMN highlight_id TEXT;
CREATE UNIQUE INDEX IF NOT EXISTS edit_projects_creation ON edit_projects(creation_batch, highlight_id);

-- Enforce source lifetime for every writer, including Ask and automatic cuts.
CREATE TRIGGER IF NOT EXISTS project_assets_insert BEFORE INSERT ON edit_projects
WHEN EXISTS (SELECT 1 FROM json_tree(NEW.edl) j WHERE j.key = 'src' AND j.type = 'text' AND j.value LIKE 'asset:%'
  AND NOT EXISTS (SELECT 1 FROM assets a WHERE a.id = substr(j.value, 7) AND a.deleting = 0))
BEGIN
  SELECT RAISE(ABORT, 'source_unavailable');
END;

-- Enforce source lifetime for every writer, including Ask and automatic cuts.
CREATE TRIGGER IF NOT EXISTS project_assets_update BEFORE UPDATE OF edl ON edit_projects
WHEN EXISTS (SELECT 1 FROM json_tree(NEW.edl) j WHERE j.key = 'src' AND j.type = 'text' AND j.value LIKE 'asset:%'
  AND NOT EXISTS (SELECT 1 FROM assets a WHERE a.id = substr(j.value, 7) AND a.deleting = 0))
BEGIN
  SELECT RAISE(ABORT, 'source_unavailable');
END;

-- Request identity outlives the generated projects. Completed requests never
-- recreate a clip someone later deleted, including a retry after lost response.
CREATE TABLE IF NOT EXISTS highlight_creations (
  request_id TEXT PRIMARY KEY,
  selection TEXT NOT NULL,
  result TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TRIGGER IF NOT EXISTS complete_highlight_creation AFTER INSERT ON edit_projects
WHEN NEW.creation_batch IS NOT NULL
  AND (SELECT COUNT(*) FROM edit_projects WHERE creation_batch = NEW.creation_batch) =
    (SELECT json_array_length(selection) FROM highlight_creations WHERE request_id = NEW.creation_batch)
BEGIN
  UPDATE highlight_creations SET result = (
    SELECT json_group_array(json_object('id',id,'name',name,'highlight_id',highlight_id))
    FROM edit_projects WHERE creation_batch = NEW.creation_batch
  ) WHERE request_id = NEW.creation_batch AND result IS NULL;
END;

-- Shared advice is source-time data; chosen framing stays on each editable clip.
CREATE TABLE IF NOT EXISTS video_reframes (
  id TEXT PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  revision INTEGER NOT NULL,
  source_start REAL NOT NULL,
  source_end REAL NOT NULL,
  status TEXT NOT NULL,
  attempt TEXT NOT NULL,
  job_id TEXT,
  result TEXT,
  error TEXT,
  width INTEGER NOT NULL,
  height INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE(asset_id, revision, source_start, source_end)
);

-- Which of the project's formats an export renders (src/shared/formats.ts):
-- a ratio such as '9:16', or NULL for the project's main format.
ALTER TABLE export_jobs ADD COLUMN format TEXT;

-- Whether people watching the share link can leave comments: 0 (watch only)
-- until someone on the team turns it on for that link.
ALTER TABLE share_links ADD COLUMN comments INTEGER NOT NULL DEFAULT 0;

-- A comment left on a shared video. `export_id` is the cut it was made on, and
-- `at` the seconds into that export (NULL: about the whole video). `author` is
-- the name the viewer typed: share-link viewers are not signed in, so it is
-- what they say, not who they are. Resolved by someone on the team.
CREATE TABLE IF NOT EXISTS review_comments (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  project_id TEXT NOT NULL,
  export_id INTEGER NOT NULL,
  at REAL,
  body TEXT NOT NULL,
  author TEXT NOT NULL,
  resolved_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_review_comments_project ON review_comments(project_id, created_at);

-- The model's own reply to a framing analysis, kept so a suggestion that fell
-- back to the whole shot can be traced (src/server/reframe.ts).
ALTER TABLE video_reframes ADD COLUMN answer TEXT;

-- The project's AI conversation: what people (and agents) asked and what the
-- editor answered, oldest first. The model reads the last few turns so a
-- follow-up ("shorter", "a square one too") knows what it refers to. `applied`
-- is a JSON list of the changes made; `asked` is 1 when the answer was a
-- question back instead of a change (src/server/instruct.ts).
CREATE TABLE IF NOT EXISTS ask_messages (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  project_id TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
  text TEXT NOT NULL,
  author TEXT,
  applied TEXT,
  asked INTEGER NOT NULL DEFAULT 0,
  failed INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_ask_messages_project ON ask_messages(project_id, created_at);

-- A request that watches more footage than a quick answer allows runs in the
-- background (src/server/ask-jobs.ts), a few steps per queue delivery. `proposed`: told what it would watch,
-- waiting for a yes. `queued` -> `running` -> `applied` (the cut was changed),
-- `stale` (the cut changed while it ran, so it was kept aside: `result_edl`,
-- applied only on request), `failed` or `cancelled`. `base_edl` is the cut it
-- started from, so it never overwrites an edit made meanwhile.
CREATE TABLE IF NOT EXISTS ask_jobs (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  project_id TEXT NOT NULL,
  instruction TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('proposed', 'queued', 'running', 'applied', 'stale', 'failed', 'cancelled')),
  clips INTEGER NOT NULL DEFAULT 0,
  seconds REAL,
  base_edl TEXT,
  result_edl TEXT,
  said TEXT,
  applied TEXT,
  error TEXT,
  author TEXT,
  -- The request in progress (src/server/instruct.ts InstructState), saved
  -- after every step, and what it is doing now in words.
  state TEXT,
  doing TEXT,
  -- A delivery holds the job while it runs a step; past this time another may.
  leased_until TEXT,
  -- When the next step was last booked on the queue.
  step_booked_at TEXT,
  started_at TEXT,
  finished_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_ask_jobs_project ON ask_jobs(project_id, created_at);

-- The background job an AI message belongs to, when it does.
ALTER TABLE ask_messages ADD COLUMN job_id TEXT;

-- Projects an AI message made (clips cut from a long video), as JSON [{id, name}].
ALTER TABLE ask_messages ADD COLUMN made TEXT;

-- When an applied AI change was put back to the cut before it (POST …/ask/jobs/{job}/revert).
ALTER TABLE ask_jobs ADD COLUMN reverted_at TEXT;

-- Videos of one project: every edit made from a project's footage (a cut-down,
-- a teaser) is its own row, pointing at the row that holds the footage, its
-- highlights and its conversation. NULL: a project (and its first video).
ALTER TABLE edit_projects ADD COLUMN parent_id TEXT;
CREATE INDEX IF NOT EXISTS idx_edit_projects_parent ON edit_projects(parent_id);