0001_init.sql

-- Schema v1: the full V1 domain model. See packages/core/src/db/README.md for
-- why these 8 tables and not the larger candidate entity list from the plan.

CREATE TABLE commits (
  sha TEXT PRIMARY KEY,
  author_name TEXT NOT NULL,
  author_email TEXT NOT NULL,
  authored_at TEXT NOT NULL,
  message TEXT NOT NULL,
  insertions INTEGER NOT NULL,
  deletions INTEGER NOT NULL,
  files_changed INTEGER NOT NULL,
  parent_shas TEXT NOT NULL, -- JSON array of parent SHAs
  branch TEXT,
  imported_at TEXT NOT NULL
);

CREATE TABLE analysis_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  from_sha TEXT NOT NULL,
  to_sha TEXT NOT NULL,
  base_ref TEXT,
  triggered_by TEXT NOT NULL, -- 'manual' | 'cli'
  status TEXT NOT NULL,       -- 'running' | 'succeeded' | 'failed'
  pipeline_version TEXT NOT NULL,
  started_at TEXT NOT NULL,
  finished_at TEXT,
  error TEXT
);

CREATE TABLE content_opportunities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  analysis_run_id INTEGER NOT NULL REFERENCES analysis_runs(id),
  title TEXT NOT NULL,
  summary TEXT NOT NULL,
  story_type TEXT NOT NULL, -- feature | fix | refactor | milestone | architecture | performance | release
  evidence_json TEXT NOT NULL,
  score REAL NOT NULL,
  score_breakdown_json TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'new', -- new | dismissed | drafted | archived
  created_at TEXT NOT NULL
);

CREATE TABLE content_drafts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  opportunity_id INTEGER NOT NULL REFERENCES content_opportunities(id),
  platform TEXT NOT NULL,
  format TEXT NOT NULL,
  body_md TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft', -- draft | approved | exported | archived
  model_used TEXT NOT NULL,
  prompt_version TEXT NOT NULL,
  revision_of INTEGER REFERENCES content_drafts(id),
  is_current INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE assets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  draft_id INTEGER REFERENCES content_drafts(id),
  opportunity_id INTEGER REFERENCES content_opportunities(id),
  type TEXT NOT NULL, -- screenshot | diagram | video | gif | code_image
  file_path TEXT NOT NULL,
  mime_type TEXT NOT NULL,
  generation_params_json TEXT,
  source TEXT NOT NULL, -- manual | playwright | remotion | ffmpeg | renderer
  created_at TEXT NOT NULL
);

CREATE TABLE generation_jobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL,
  stage TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'queued', -- queued | running | succeeded | failed | retrying
  payload_json TEXT NOT NULL,
  result_ref TEXT,
  attempts INTEGER NOT NULL DEFAULT 0,
  last_error_code TEXT,
  last_error TEXT,
  created_at TEXT NOT NULL,
  started_at TEXT,
  finished_at TEXT,
  locked_by INTEGER,
  locked_at TEXT
);

CREATE TABLE provider_configs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider TEXT NOT NULL,
  model TEXT NOT NULL,
  base_url TEXT,
  api_key_ref TEXT NOT NULL,
  label TEXT NOT NULL,
  is_default INTEGER NOT NULL DEFAULT 0,
  stage_overrides_json TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE publications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  draft_id INTEGER NOT NULL REFERENCES content_drafts(id),
  action TEXT NOT NULL, -- copied | exported_file | exported_clipboard
  destination TEXT,
  exported_at TEXT NOT NULL
);

CREATE INDEX idx_content_opportunities_analysis_run ON content_opportunities(analysis_run_id);
CREATE INDEX idx_content_drafts_opportunity ON content_drafts(opportunity_id);
CREATE INDEX idx_generation_jobs_status ON generation_jobs(status);