62 lines
1.7 KiB
SQL
62 lines
1.7 KiB
SQL
CREATE TABLE IF NOT EXISTS brand_templates (
|
|
id TEXT PRIMARY KEY,
|
|
brand_name TEXT NOT NULL,
|
|
company_full_name TEXT NOT NULL,
|
|
company_short_names TEXT NOT NULL,
|
|
product_names TEXT NOT NULL,
|
|
target_industries TEXT NOT NULL,
|
|
target_audience TEXT NOT NULL,
|
|
verified_claims TEXT NOT NULL,
|
|
forbidden_claims TEXT NOT NULL,
|
|
tone_rules TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS article_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
brand_template_id TEXT,
|
|
source_title TEXT NOT NULL,
|
|
source_body TEXT NOT NULL,
|
|
image_inputs TEXT NOT NULL,
|
|
publish_platform TEXT NOT NULL,
|
|
user_instructions TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
export_paths TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (brand_template_id) REFERENCES brand_templates(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS fact_cards (
|
|
job_id TEXT PRIMARY KEY,
|
|
source TEXT NOT NULL,
|
|
fact_card TEXT NOT NULL,
|
|
confirmed_by_user INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (job_id) REFERENCES article_jobs(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS optimized_articles (
|
|
job_id TEXT NOT NULL,
|
|
revision INTEGER NOT NULL,
|
|
article TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (job_id, revision),
|
|
FOREIGN KEY (job_id) REFERENCES article_jobs(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS qa_reports (
|
|
job_id TEXT NOT NULL,
|
|
revision INTEGER NOT NULL,
|
|
report TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (job_id, revision),
|
|
FOREIGN KEY (job_id, revision)
|
|
REFERENCES optimized_articles(job_id, revision) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_article_jobs_updated_at
|
|
ON article_jobs(updated_at);
|