135 lines
4.1 KiB
SQL
135 lines
4.1 KiB
SQL
CREATE TABLE IF NOT EXISTS optimization_cases (
|
|
id TEXT PRIMARY KEY,
|
|
case_type TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
customer_name TEXT NOT NULL DEFAULT '',
|
|
brand_name TEXT NOT NULL DEFAULT '',
|
|
project_tags TEXT NOT NULL DEFAULT '[]',
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
publish_target TEXT NOT NULL DEFAULT '',
|
|
source_excerpt TEXT NOT NULL DEFAULT '',
|
|
result_excerpt TEXT NOT NULL DEFAULT '',
|
|
latest_result_version_id TEXT,
|
|
latest_version_number INTEGER,
|
|
last_error_stage TEXT,
|
|
last_error_summary TEXT,
|
|
archived_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS case_inputs (
|
|
case_id TEXT PRIMARY KEY,
|
|
case_type TEXT NOT NULL,
|
|
article_job_id TEXT,
|
|
payload TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (case_id) REFERENCES optimization_cases(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (article_job_id) REFERENCES article_jobs(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS optimization_result_versions (
|
|
id TEXT PRIMARY KEY,
|
|
case_id TEXT NOT NULL,
|
|
case_type TEXT NOT NULL,
|
|
version INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
article_job_id TEXT,
|
|
article_revision INTEGER,
|
|
result_summary TEXT NOT NULL,
|
|
payload TEXT NOT NULL,
|
|
process_summary TEXT NOT NULL,
|
|
llm_audit_summary TEXT NOT NULL,
|
|
error_stage TEXT,
|
|
error_summary TEXT,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (case_id, version),
|
|
FOREIGN KEY (case_id) REFERENCES optimization_cases(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (article_job_id) REFERENCES article_jobs(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_optimization_cases_updated_at
|
|
ON optimization_cases(updated_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_optimization_cases_case_type
|
|
ON optimization_cases(case_type);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_optimization_cases_status
|
|
ON optimization_cases(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_optimization_result_versions_case
|
|
ON optimization_result_versions(case_id, version DESC);
|
|
|
|
ALTER TABLE article_jobs ADD COLUMN case_id TEXT;
|
|
|
|
CREATE TABLE IF NOT EXISTS publication_records_next (
|
|
id TEXT PRIMARY KEY,
|
|
result_version_id TEXT,
|
|
job_id TEXT,
|
|
revision INTEGER,
|
|
publish_target 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 (result_version_id)
|
|
REFERENCES optimization_result_versions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
INSERT INTO publication_records_next (
|
|
id, result_version_id, job_id, revision, publish_target, url,
|
|
published_at, status, notes, created_at, updated_at
|
|
)
|
|
SELECT
|
|
id, NULL, job_id, revision, platform, url,
|
|
published_at, status, notes, created_at, updated_at
|
|
FROM publication_records;
|
|
|
|
DROP TABLE publication_records;
|
|
ALTER TABLE publication_records_next RENAME TO publication_records;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_publication_records_result_version
|
|
ON publication_records(result_version_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_publication_records_job_revision
|
|
ON publication_records(job_id, revision);
|
|
|
|
CREATE TABLE IF NOT EXISTS scoring_runs_next (
|
|
id TEXT PRIMARY KEY,
|
|
result_version_id TEXT,
|
|
case_type TEXT NOT NULL DEFAULT 'article',
|
|
job_id TEXT,
|
|
revision INTEGER,
|
|
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 (result_version_id)
|
|
REFERENCES optimization_result_versions(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (rubric_version_id) REFERENCES rubric_versions(id)
|
|
);
|
|
|
|
INSERT INTO scoring_runs_next (
|
|
id, result_version_id, case_type, job_id, revision, rubric_version_id,
|
|
dimension_scores, composite_score, rationale, created_at
|
|
)
|
|
SELECT
|
|
id, NULL, 'article', job_id, revision, rubric_version_id,
|
|
dimension_scores, composite_score, rationale, created_at
|
|
FROM scoring_runs;
|
|
|
|
DROP TABLE scoring_runs;
|
|
ALTER TABLE scoring_runs_next RENAME TO scoring_runs;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scoring_runs_result_version
|
|
ON scoring_runs(result_version_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scoring_runs_job_revision
|
|
ON scoring_runs(job_id, revision);
|