From 3754f11c33a6b4ac756a0d89426764fd9032cf53 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
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

