Skip to content

Database schema

Two SQLite files live in db/:

  • db/premal.sqlite — the curated and extracted details (tracked in git).
  • db/fulltext.sqlite — page text, FTS5 index and API cache (not tracked; rebuilt by scan).
erDiagram
  papers ||--o{ files : "paper_id"
  papers ||--o{ paper_details : "paper_id"
  papers ||--o{ mentions : "paper_id"
  vocab ||--o{ mentions : "vocab_id"
  papers ||--o{ paper_authors : "paper_id"
  papers ||--o{ paper_relations : "paper_id / related_id"
  papers ||--o| schemes : "paper_id"
  papers ||--o| systems : "paper_id"
  papers ||--o| sok_references : "paper_id"
  schemes ||--o{ scheme_libs : "scheme_id"
  schemes ||--o{ scheme_followups : "scheme_id"
  collections ||--o{ topics : "collection"

db/schema.sql

-- PREMAL_Bench paper database schema (SQLite).
-- Applied by: python3 tools/paperdb.py init
-- Main database: db/premal.sqlite (tracked in git).
-- Derived full text + API cache: db/fulltext.sqlite (not tracked; rebuilt by `scan`).

PRAGMA foreign_keys = ON;

-- Key/value bookkeeping (schema version, last run timestamps).
CREATE TABLE IF NOT EXISTS meta (
  key   TEXT PRIMARY KEY,
  value TEXT
);

-- Where PDFs live under papers/. One row per collection folder.
CREATE TABLE IF NOT EXISTS collections (
  name        TEXT PRIMARY KEY,      -- sok | sok-references | catalogue | schemes | ppml
  title       TEXT NOT NULL,
  description TEXT,
  folder      TEXT NOT NULL,         -- relative to papers/
  sort        INTEGER NOT NULL DEFAULT 0
);

-- Sub-folders of a collection (used by ppml/<topic>/).
CREATE TABLE IF NOT EXISTS topics (
  collection  TEXT NOT NULL REFERENCES collections(name),
  name        TEXT NOT NULL,
  title       TEXT,
  description TEXT,
  PRIMARY KEY (collection, name)
);

-- One row per distinct work (paper, thesis, report, web page).
-- A paper may have 0..n PDF files (see files.paper_id).
CREATE TABLE IF NOT EXISTS papers (
  id              INTEGER PRIMARY KEY,
  key             TEXT NOT NULL UNIQUE,   -- stable slug, used for wiki page names
  title           TEXT NOT NULL,
  title_norm      TEXT,                   -- normalised title used for matching
  title_source    TEXT,                   -- curated | crossref | arxiv | s2 | pdf-meta | pdf-layout | filename | manual
  authors         TEXT,                   -- "Given Family; Given Family; ..."
  authors_source  TEXT,
  year            INTEGER,
  year_source     TEXT,                   -- curated | crossref | arxiv | s2 | pdf | pdf-weak | arxiv-id | eprint-id | filename | manual
  venue           TEXT,
  publisher       TEXT,
  doi             TEXT,
  arxiv_id        TEXT,
  eprint_id       TEXT,                   -- IACR ePrint "YYYY/NNN"
  url             TEXT,
  s2_id           TEXT,                   -- Semantic Scholar paper id
  abstract        TEXT,
  abstract_source TEXT,                   -- s2 | arxiv | crossref | pdf | curated | manual
  summary         TEXT,                   -- short summary (LLM-extracted or manual)
  summary_source  TEXT,
  paper_type      TEXT,                   -- scheme | technique | system | library | compiler | accelerator | survey | thesis | textbook | application | dataset | benchmark | attack | theory | other
  paper_type_source TEXT,
  citation_count  INTEGER,
  citation_count_date TEXT,
  tldr            TEXT,                   -- Semantic Scholar TLDR
  primary_file_id INTEGER,                -- files.id of the canonical PDF (no FK to avoid cycles)
  review_status   TEXT NOT NULL DEFAULT 'auto',   -- auto | reviewed
  notes           TEXT,
  created_at      TEXT,
  updated_at      TEXT
);
CREATE INDEX IF NOT EXISTS idx_papers_doi    ON papers(doi);
CREATE INDEX IF NOT EXISTS idx_papers_arxiv  ON papers(arxiv_id);
CREATE INDEX IF NOT EXISTS idx_papers_eprint ON papers(eprint_id);
CREATE INDEX IF NOT EXISTS idx_papers_tnorm  ON papers(title_norm);
CREATE INDEX IF NOT EXISTS idx_papers_year   ON papers(year);

-- Author list, one row per author position (derived from papers.authors).
CREATE TABLE IF NOT EXISTS paper_authors (
  paper_id  INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  position  INTEGER NOT NULL,
  name      TEXT NOT NULL,
  PRIMARY KEY (paper_id, position)
);
CREATE INDEX IF NOT EXISTS idx_paper_authors_name ON paper_authors(name);

-- One row per PDF file under papers/ (path relative to papers/).
CREATE TABLE IF NOT EXISTS files (
  id             INTEGER PRIMARY KEY,
  path           TEXT NOT NULL UNIQUE,
  collection     TEXT NOT NULL,
  topic          TEXT,
  paper_id       INTEGER REFERENCES papers(id) ON DELETE SET NULL,
  sha256         TEXT NOT NULL,
  size_bytes     INTEGER,
  pages          INTEGER,
  text_chars     INTEGER,
  pdf_title      TEXT,                    -- PDF metadata title
  pdf_author     TEXT,                    -- PDF metadata author
  pdf_created    TEXT,
  guess_title    TEXT,                    -- largest-font text on page 1
  guess_year     INTEGER,
  guess_abstract TEXT,
  found_doi      TEXT,                    -- DOI printed on page 1 (validated later by enrich)
  found_arxiv    TEXT,                    -- arXiv id from the page-1 stamp
  hint_eprint    TEXT,                    -- ePrint id from the file name
  original_name  TEXT,                    -- file name before organize renamed it
  duplicate_of   INTEGER REFERENCES files(id) ON DELETE SET NULL,  -- byte-identical earlier file
  missing        INTEGER NOT NULL DEFAULT 0,   -- 1 if the file disappeared from disk
  mismatch       INTEGER NOT NULL DEFAULT 0,   -- 1 if the PDF holds a different document than its name/curated entry claims
  mismatch_note  TEXT,
  added_at       TEXT,
  scanned_at     TEXT
);
CREATE INDEX IF NOT EXISTS idx_files_sha   ON files(sha256);
CREATE INDEX IF NOT EXISTS idx_files_paper ON files(paper_id);

-- The 107 references of the SoK (docs/data/sok_references.js).
CREATE TABLE IF NOT EXISTS sok_references (
  n        INTEGER PRIMARY KEY,
  title    TEXT,
  year     TEXT,
  citation TEXT,
  link     TEXT,
  local    INTEGER,
  file     TEXT,
  paper_id INTEGER REFERENCES papers(id) ON DELETE SET NULL
);

-- The 56 schemes of the main table (docs/data/schemes.json).
CREATE TABLE IF NOT EXISTS schemes (
  id         INTEGER PRIMARY KEY,
  "group"    TEXT,
  name       TEXT,
  kind       TEXT,
  year       INTEGER,
  venue      TEXT,
  authors    TEXT,
  title      TEXT,
  paper_url  TEXT,
  assumption TEXT,
  plaintext  TEXT,
  simd       TEXT,
  boot       TEXT,
  pq         TEXT,
  llm        TEXT,
  followups  TEXT,
  status     TEXT,
  lesson     TEXT,                        -- docs/learn/<lesson>.html
  paper_id   INTEGER REFERENCES papers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS scheme_libs (
  scheme_id INTEGER NOT NULL REFERENCES schemes(id) ON DELETE CASCADE,
  name      TEXT NOT NULL,
  url       TEXT,
  PRIMARY KEY (scheme_id, name)
);
CREATE TABLE IF NOT EXISTS scheme_followups (
  scheme_id INTEGER NOT NULL REFERENCES schemes(id) ON DELETE CASCADE,
  paper_id  INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  file_path TEXT,
  PRIMARY KEY (scheme_id, paper_id)
);

-- The 137 systems of the systems page (docs/data/methods.json).
CREATE TABLE IF NOT EXISTS systems (
  id          INTEGER PRIMARY KEY,
  "group"     TEXT,
  name        TEXT,
  year        INTEGER,
  venue       TEXT,
  authors     TEXT,
  title       TEXT,
  paper_url   TEXT,
  code        TEXT,
  family      TEXT,
  arith       TEXT,
  interaction TEXT,
  scale       TEXT,
  status      TEXT,
  rel         TEXT,
  note        TEXT,
  hardware    TEXT,
  library     TEXT,
  packing     TEXT,
  latency     TEXT,
  open_source TEXT,
  sok_ref     TEXT,
  paper_id    INTEGER REFERENCES papers(id) ON DELETE SET NULL
);

-- Extracted details: one row per (paper, field, value), with provenance.
-- Fields used: schemes, libraries, models, datasets, tasks, hardware, security_level,
-- threat_model, interaction, key_result, code_url, limitation, contribution, llm_relevance ...
CREATE TABLE IF NOT EXISTS paper_details (
  id         INTEGER PRIMARY KEY,
  paper_id   INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  field      TEXT NOT NULL,
  value      TEXT NOT NULL,
  source     TEXT NOT NULL,               -- llm | pdf-regex | curated | manual
  confidence REAL,
  evidence   TEXT,
  created_at TEXT,
  UNIQUE (paper_id, field, value, source)
);
CREATE INDEX IF NOT EXISTS idx_details_field ON paper_details(field, value);

-- Keyword vocabulary (db/vocab.json) and how often each term occurs in each paper's full text.
CREATE TABLE IF NOT EXISTS vocab (
  id       INTEGER PRIMARY KEY,
  category TEXT NOT NULL,                 -- scheme | library | model | dataset | hardware | technique | assumption | task | topic
  term     TEXT NOT NULL,
  pattern  TEXT NOT NULL,
  flags    TEXT,
  UNIQUE (category, term)
);
CREATE TABLE IF NOT EXISTS mentions (
  paper_id   INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  vocab_id   INTEGER NOT NULL REFERENCES vocab(id) ON DELETE CASCADE,
  count      INTEGER NOT NULL,
  first_page INTEGER,
  PRIMARY KEY (paper_id, vocab_id)
);

-- Relations between papers.
CREATE TABLE IF NOT EXISTS paper_relations (
  paper_id   INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  related_id INTEGER NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  relation   TEXT NOT NULL,               -- follow_up_of | version_of | extends | similar_title
  note       TEXT,
  PRIMARY KEY (paper_id, related_id, relation)
);

-- Convenience views.
CREATE VIEW IF NOT EXISTS v_papers AS
SELECT p.*,
       (SELECT COUNT(*) FROM files f WHERE f.paper_id = p.id AND f.missing = 0) AS n_files,
       (SELECT GROUP_CONCAT(DISTINCT f.collection) FROM files f WHERE f.paper_id = p.id AND f.missing = 0) AS collections,
       (SELECT MIN(f.path) FROM files f WHERE f.paper_id = p.id AND f.missing = 0) AS a_path
FROM papers p;

CREATE VIEW IF NOT EXISTS v_mentions AS
SELECT m.paper_id, p.key, v.category, v.term, m.count, m.first_page
FROM mentions m JOIN vocab v ON v.id = m.vocab_id JOIN papers p ON p.id = m.paper_id;

Full-text database

CREATE TABLE IF NOT EXISTS pages (
  id INTEGER PRIMARY KEY, sha256 TEXT NOT NULL, page_no INTEGER NOT NULL, text TEXT,
  UNIQUE (sha256, page_no));
CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(text, content='pages', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS pages_ai AFTER INSERT ON pages BEGIN
  INSERT INTO pages_fts(rowid, text) VALUES (new.id, new.text); END;
CREATE TRIGGER IF NOT EXISTS pages_ad AFTER DELETE ON pages BEGIN
  INSERT INTO pages_fts(pages_fts, rowid, text) VALUES ('delete', old.id, old.text); END;
CREATE TRIGGER IF NOT EXISTS pages_au AFTER UPDATE ON pages BEGIN
  INSERT INTO pages_fts(pages_fts, rowid, text) VALUES ('delete', old.id, old.text);
  INSERT INTO pages_fts(rowid, text) VALUES (new.id, new.text); END;
CREATE TABLE IF NOT EXISTS api_cache (key TEXT PRIMARY KEY, fetched_at TEXT, status INTEGER, body TEXT);