platform-operations/content-strategy/scripts/schema.sql

-- Citations Database Schema v2
-- Source of truth: populated by parse-citations.ts, parse-excerpts.ts, parse-blog-links.ts
-- Exported to citations.json by export-citations.ts

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS citations (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL CHECK(type IN ('academic','report','filing','news','industry','policy','platform','internal','community')),
  title TEXT NOT NULL,
  year TEXT NOT NULL,
  publisher TEXT,
  url TEXT,
  doi TEXT,
  arxiv TEXT,
  venue TEXT,
  notes TEXT,
  calculated INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS citation_authors (
  citation_id TEXT NOT NULL REFERENCES citations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  institutional TEXT,
  position INTEGER DEFAULT 0,
  PRIMARY KEY (citation_id, position)
);

CREATE TABLE IF NOT EXISTS citation_themes (
  citation_id TEXT NOT NULL REFERENCES citations(id) ON DELETE CASCADE,
  theme TEXT NOT NULL CHECK(theme IN ('anti-extraction','inverse-capitalism','body-sovereignty','privacy','permanent-software','human-work','ai-philosophy','slutology','cooperative-future','open-source')),
  PRIMARY KEY (citation_id, theme)
);

CREATE TABLE IF NOT EXISTS claims (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  citation_id TEXT NOT NULL REFERENCES citations(id) ON DELETE CASCADE,
  text TEXT NOT NULL,
  category TEXT NOT NULL,
  year TEXT NOT NULL,
  excerpt TEXT
);

CREATE TABLE IF NOT EXISTS excerpts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  citation_id TEXT NOT NULL REFERENCES citations(id) ON DELETE CASCADE,
  text TEXT NOT NULL,
  page TEXT,
  context TEXT
);

CREATE TABLE IF NOT EXISTS articles (
  citation_id TEXT PRIMARY KEY REFERENCES citations(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  content_type TEXT DEFAULT 'markdown',
  fetched_at TEXT DEFAULT (datetime('now')),
  source_url TEXT
);

CREATE TABLE IF NOT EXISTS content_references (
  citation_id TEXT NOT NULL REFERENCES citations(id) ON DELETE CASCADE,
  content_slug TEXT NOT NULL,
  content_path TEXT NOT NULL,
  content_id TEXT,
  footnote_number INTEGER NOT NULL,
  PRIMARY KEY (citation_id, content_slug, footnote_number)
);

-- ─── Content Pieces ───

CREATE TABLE IF NOT EXISTS content_pieces (
  id TEXT PRIMARY KEY,
  content_type TEXT NOT NULL CHECK(content_type IN ('blog','reddit','linkedin','twitter','fediverse','press','academic','community','merch')),
  title TEXT NOT NULL,
  author TEXT,
  status TEXT CHECK(status IN ('draft','published','revised')),
  date TEXT,
  idea TEXT,
  published_on TEXT,
  word_count INTEGER NOT NULL DEFAULT 0,
  word_count_target INTEGER,
  reading_time_minutes INTEGER NOT NULL DEFAULT 0,
  section_count INTEGER NOT NULL DEFAULT 0,
  has_tables INTEGER NOT NULL DEFAULT 0,
  has_code_blocks INTEGER NOT NULL DEFAULT 0,
  has_footnotes INTEGER NOT NULL DEFAULT 0,
  has_figures INTEGER NOT NULL DEFAULT 0,
  has_galleries INTEGER NOT NULL DEFAULT 0,
  file_path TEXT UNIQUE NOT NULL,
  body_markdown TEXT
);

CREATE TABLE IF NOT EXISTS content_seo (
  content_id TEXT PRIMARY KEY REFERENCES content_pieces(id) ON DELETE CASCADE,
  primary_keyword TEXT,
  secondary_keywords TEXT,
  meta_title TEXT,
  meta_description TEXT,
  schema TEXT
);

CREATE TABLE IF NOT EXISTS content_metadata (
  content_id TEXT NOT NULL REFERENCES content_pieces(id) ON DELETE CASCADE,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  PRIMARY KEY (content_id, key)
);

CREATE TABLE IF NOT EXISTS content_sections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id TEXT NOT NULL REFERENCES content_pieces(id) ON DELETE CASCADE,
  level INTEGER NOT NULL,
  text TEXT NOT NULL,
  slug TEXT NOT NULL,
  position INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS content_assets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id TEXT NOT NULL REFERENCES content_pieces(id) ON DELETE CASCADE,
  type TEXT NOT NULL CHECK(type IN ('image','figure','gallery-image')),
  src TEXT NOT NULL,
  alt TEXT,
  caption TEXT,
  position TEXT,
  width TEXT,
  gallery_id TEXT,
  order_in_doc INTEGER NOT NULL DEFAULT 0
);

CREATE VIRTUAL TABLE IF NOT EXISTS content_fts USING fts5(title, body_markdown, content='content_pieces', content_rowid='rowid');

-- Full-text search across articles and excerpts
CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(content, content='articles', content_rowid='rowid');
CREATE VIRTUAL TABLE IF NOT EXISTS excerpts_fts USING fts5(text, content='excerpts', content_rowid='rowid');

-- FTS sync triggers
CREATE TRIGGER IF NOT EXISTS articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, content) VALUES (new.rowid, new.content);
END;

CREATE TRIGGER IF NOT EXISTS articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, content) VALUES ('delete', old.rowid, old.content);
END;

CREATE TRIGGER IF NOT EXISTS articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, content) VALUES ('delete', old.rowid, old.content);
  INSERT INTO articles_fts(rowid, content) VALUES (new.rowid, new.content);
END;

CREATE TRIGGER IF NOT EXISTS excerpts_ai AFTER INSERT ON excerpts BEGIN
  INSERT INTO excerpts_fts(rowid, text) VALUES (new.rowid, new.text);
END;

CREATE TRIGGER IF NOT EXISTS excerpts_ad AFTER DELETE ON excerpts BEGIN
  INSERT INTO excerpts_fts(excerpts_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
END;

CREATE TRIGGER IF NOT EXISTS excerpts_au AFTER UPDATE ON excerpts BEGIN
  INSERT INTO excerpts_fts(excerpts_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
  INSERT INTO excerpts_fts(rowid, text) VALUES (new.rowid, new.text);
END;

-- Content FTS sync triggers
CREATE TRIGGER IF NOT EXISTS content_fts_ai AFTER INSERT ON content_pieces BEGIN
  INSERT INTO content_fts(rowid, title, body_markdown) VALUES (new.rowid, new.title, new.body_markdown);
END;

CREATE TRIGGER IF NOT EXISTS content_fts_ad AFTER DELETE ON content_pieces BEGIN
  INSERT INTO content_fts(content_fts, rowid, title, body_markdown) VALUES ('delete', old.rowid, old.title, old.body_markdown);
END;

CREATE TRIGGER IF NOT EXISTS content_fts_au AFTER UPDATE ON content_pieces BEGIN
  INSERT INTO content_fts(content_fts, rowid, title, body_markdown) VALUES ('delete', old.rowid, old.title, old.body_markdown);
  INSERT INTO content_fts(rowid, title, body_markdown) VALUES (new.rowid, new.title, new.body_markdown);
END;