mirror of
https://github.com/harvard-edge/cs249r_book.git
synced 2026-11-09 10:39:41 -06:00
Vijay Janapa Reddi
4ea354b235
Questions gain optional taxonomy_subject and taxonomy_concepts (identities from shared/concepts). The model checks their format and vault check --strict checks they exist, naming the replacement for a retired concept. They join the content hash (CANON_VERSION 3), SQLite and D1 (questions.taxonomy_subject, question_concepts), and the site corpus, and vault build writes the labels the site needs.
98 lines
3.2 KiB
SQL
98 lines
3.2 KiB
SQL
CREATE TABLE questions (
|
|
id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
topic TEXT NOT NULL,
|
|
track TEXT NOT NULL,
|
|
level TEXT NOT NULL,
|
|
zone TEXT NOT NULL,
|
|
competency_area TEXT,
|
|
bloom_level TEXT,
|
|
phase TEXT,
|
|
taxonomy_subject TEXT,
|
|
status TEXT NOT NULL,
|
|
scenario TEXT NOT NULL,
|
|
common_mistake TEXT,
|
|
realistic_solution TEXT NOT NULL,
|
|
napkin_math TEXT,
|
|
provenance TEXT NOT NULL,
|
|
created_at TEXT,
|
|
last_modified TEXT,
|
|
human_review_status TEXT,
|
|
human_review_by TEXT,
|
|
human_review_date TEXT,
|
|
file_path TEXT NOT NULL,
|
|
content_hash TEXT NOT NULL,
|
|
authors_json TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_questions_topic ON questions(topic);
|
|
CREATE INDEX idx_questions_track_level ON questions(track, level);
|
|
CREATE INDEX idx_questions_zone ON questions(zone);
|
|
CREATE INDEX idx_questions_status ON questions(status);
|
|
CREATE INDEX idx_questions_human_review ON questions(human_review_status);
|
|
CREATE INDEX idx_questions_taxonomy_subject ON questions(taxonomy_subject);
|
|
|
|
CREATE TABLE chains (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT,
|
|
topic TEXT
|
|
);
|
|
|
|
-- v1.0: a question may belong to multiple chains; PK is (chain_id, question_id)
|
|
-- rather than (chain_id, position) to allow non-contiguous/0-indexed positions.
|
|
CREATE TABLE chain_questions (
|
|
chain_id TEXT NOT NULL,
|
|
question_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL,
|
|
PRIMARY KEY (chain_id, question_id),
|
|
FOREIGN KEY (chain_id) REFERENCES chains(id),
|
|
FOREIGN KEY (question_id) REFERENCES questions(id)
|
|
);
|
|
CREATE INDEX idx_chain_questions_qid ON chain_questions(question_id);
|
|
|
|
CREATE TABLE tags (
|
|
question_id TEXT NOT NULL,
|
|
tag TEXT NOT NULL,
|
|
FOREIGN KEY (question_id) REFERENCES questions(id)
|
|
);
|
|
|
|
-- ML Systems Taxonomy concept tags (2026-10): position 0 is the most central.
|
|
CREATE TABLE question_concepts (
|
|
question_id TEXT NOT NULL,
|
|
concept_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL,
|
|
PRIMARY KEY (question_id, concept_id),
|
|
FOREIGN KEY (question_id) REFERENCES questions(id)
|
|
);
|
|
CREATE INDEX idx_question_concepts_concept ON question_concepts(concept_id);
|
|
|
|
CREATE TABLE release_metadata (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
-- Full-text search (B.5). Content-table pattern keeps the FTS index in sync
|
|
-- with INSERTs/UPDATEs/DELETEs via triggers. Cold-start fingerprint check
|
|
-- includes triggers — see vault-worker/src/index.ts.
|
|
CREATE VIRTUAL TABLE questions_fts USING fts5(
|
|
title, scenario, realistic_solution,
|
|
content='questions', content_rowid='rowid'
|
|
);
|
|
|
|
CREATE TRIGGER questions_ai AFTER INSERT ON questions BEGIN
|
|
INSERT INTO questions_fts(rowid, title, scenario, realistic_solution)
|
|
VALUES (new.rowid, new.title, new.scenario, new.realistic_solution);
|
|
END;
|
|
|
|
CREATE TRIGGER questions_ad AFTER DELETE ON questions BEGIN
|
|
INSERT INTO questions_fts(questions_fts, rowid, title, scenario, realistic_solution)
|
|
VALUES('delete', old.rowid, old.title, old.scenario, old.realistic_solution);
|
|
END;
|
|
|
|
CREATE TRIGGER questions_au AFTER UPDATE ON questions BEGIN
|
|
INSERT INTO questions_fts(questions_fts, rowid, title, scenario, realistic_solution)
|
|
VALUES('delete', old.rowid, old.title, old.scenario, old.realistic_solution);
|
|
INSERT INTO questions_fts(rowid, title, scenario, realistic_solution)
|
|
VALUES (new.rowid, new.title, new.scenario, new.realistic_solution);
|
|
END;
|