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 byscan).
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);