75 lines
2.3 KiB
SQL
75 lines
2.3 KiB
SQL
CREATE TABLE IF NOT EXISTS rubric_versions (
|
|
id TEXT PRIMARY KEY,
|
|
version TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
dimensions TEXT NOT NULL,
|
|
formula TEXT NOT NULL,
|
|
is_active INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS scoring_runs (
|
|
id TEXT PRIMARY KEY,
|
|
job_id TEXT NOT NULL,
|
|
revision INTEGER NOT NULL,
|
|
rubric_version_id TEXT NOT NULL,
|
|
dimension_scores TEXT NOT NULL,
|
|
composite_score REAL NOT NULL,
|
|
rationale TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
FOREIGN KEY (job_id, revision)
|
|
REFERENCES optimized_articles(job_id, revision) ON DELETE CASCADE,
|
|
FOREIGN KEY (rubric_version_id) REFERENCES rubric_versions(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS publication_records (
|
|
id TEXT PRIMARY KEY,
|
|
job_id TEXT NOT NULL,
|
|
revision INTEGER NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
published_at TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
notes TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (job_id, revision)
|
|
REFERENCES optimized_articles(job_id, revision) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS performance_snapshots (
|
|
id TEXT PRIMARY KEY,
|
|
publication_id TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
window_label TEXT NOT NULL,
|
|
metrics TEXT NOT NULL,
|
|
feedback_summary TEXT NOT NULL,
|
|
raw_reference TEXT,
|
|
snapshot_at TEXT NOT NULL,
|
|
FOREIGN KEY (publication_id) REFERENCES publication_records(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS calibration_events (
|
|
id TEXT PRIMARY KEY,
|
|
publication_id TEXT NOT NULL,
|
|
scoring_run_id TEXT NOT NULL,
|
|
performance_snapshot_id TEXT NOT NULL,
|
|
direction TEXT NOT NULL,
|
|
observations TEXT NOT NULL,
|
|
recommended_action TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
FOREIGN KEY (publication_id) REFERENCES publication_records(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (scoring_run_id) REFERENCES scoring_runs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (performance_snapshot_id)
|
|
REFERENCES performance_snapshots(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scoring_runs_job_revision
|
|
ON scoring_runs(job_id, revision);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_publication_records_job_revision
|
|
ON publication_records(job_id, revision);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_performance_snapshots_publication
|
|
ON performance_snapshots(publication_id);
|