freedakgmail
76 lines
2.3 KiB
SQL
76 lines
2.3 KiB
SQL
PRAGMA journal_mode = WAL;
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
canonical_url TEXT NOT NULL UNIQUE,
|
|
source TEXT NOT NULL DEFAULT 'hongcan',
|
|
title TEXT,
|
|
author TEXT,
|
|
published_at TEXT,
|
|
summary TEXT,
|
|
content TEXT,
|
|
content_hash TEXT,
|
|
crawl_status TEXT NOT NULL DEFAULT 'pending',
|
|
retry_count INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT,
|
|
discovered_at TEXT NOT NULL,
|
|
crawled_at TEXT,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_published_at
|
|
ON articles(published_at);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_crawl_status
|
|
ON articles(crawl_status, retry_count);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_content_hash
|
|
ON articles(content_hash);
|
|
|
|
CREATE TABLE IF NOT EXISTS crawl_runs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
started_at TEXT NOT NULL,
|
|
finished_at TEXT,
|
|
status TEXT NOT NULL DEFAULT 'running',
|
|
discovered_count INTEGER NOT NULL DEFAULT 0,
|
|
inserted_count INTEGER NOT NULL DEFAULT 0,
|
|
updated_count INTEGER NOT NULL DEFAULT 0,
|
|
failed_count INTEGER NOT NULL DEFAULT 0,
|
|
message TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_analyses (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
article_id INTEGER NOT NULL,
|
|
model TEXT NOT NULL,
|
|
analysis_type TEXT NOT NULL DEFAULT 'editorial',
|
|
result_json TEXT NOT NULL,
|
|
content_hash TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE(article_id, model, analysis_type),
|
|
FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ai_analyses_article
|
|
ON ai_analyses(article_id, updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS industry_signals (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
signal_date TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
trend TEXT NOT NULL DEFAULT 'emerging',
|
|
confidence REAL NOT NULL DEFAULT 0.5,
|
|
implications_json TEXT NOT NULL DEFAULT '[]',
|
|
article_ids_json TEXT NOT NULL DEFAULT '[]',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
model TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE(signal_date, title)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_industry_signals_date
|
|
ON industry_signals(signal_date DESC, confidence DESC);
|
|
|