From 3754f11c33a6b4ac756a0d89426764fd9032cf53 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sun, 13 Jul 2025 01:32:23 +0900 Subject: feat(backend): implement db schema migration --- backend/db/migrations/001_initial_schema.sql | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 backend/db/migrations/001_initial_schema.sql (limited to 'backend/db/migrations/001_initial_schema.sql') diff --git a/backend/db/migrations/001_initial_schema.sql b/backend/db/migrations/001_initial_schema.sql new file mode 100644 index 0000000..6f0b75e --- /dev/null +++ b/backend/db/migrations/001_initial_schema.sql @@ -0,0 +1,27 @@ +-- Initial schema + +-- Feeds +CREATE TABLE IF NOT EXISTS feeds ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + url TEXT NOT NULL, + title TEXT NOT NULL, + fetched_at TEXT NOT NULL +); + +-- Articles +CREATE TABLE IF NOT EXISTS articles ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + feed_id INTEGER NOT NULL, + guid TEXT NOT NULL, + title TEXT NOT NULL, + url TEXT NOT NULL, + is_read INTEGER NOT NULL DEFAULT 0, + FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE +); + +-- Indice +CREATE INDEX IF NOT EXISTS idx_articles_feed_id ON articles(feed_id); + +CREATE INDEX IF NOT EXISTS idx_articles_feed_guid ON articles(feed_id, guid); + +CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read); \ No newline at end of file -- cgit v1.2.3-70-g09d2