38 lines
1.2 KiB
SQL
38 lines
1.2 KiB
SQL
-- Initialisation de la base de données
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
url TEXT NOT NULL UNIQUE,
|
|
source TEXT,
|
|
source_language TEXT DEFAULT 'fr',
|
|
language TEXT DEFAULT 'fr',
|
|
published_at DATETIME,
|
|
summary TEXT,
|
|
categories TEXT, -- JSON array
|
|
status TEXT NOT NULL DEFAULT 'pending', -- pending, published, hidden, rejected
|
|
created_at DATETIME DEFAULT (datetime('now')),
|
|
updated_at DATETIME DEFAULT (datetime('now')),
|
|
collected_by TEXT DEFAULT 'rss' -- rss, ai
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_published_at ON articles(published_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_url ON articles(url);
|
|
|
|
CREATE TABLE IF NOT EXISTS sources (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
url TEXT,
|
|
enabled INTEGER DEFAULT 1,
|
|
last_collected_at DATETIME,
|
|
created_at DATETIME DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS activity_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
action TEXT NOT NULL, -- collect, validate, hide, reject, delete, publish
|
|
article_id TEXT,
|
|
details TEXT,
|
|
created_at DATETIME DEFAULT (datetime('now'))
|
|
);
|