1 files changed, 28 insertions, 0 deletions
diff --git a/migrations/033_ics_feeds.sql b/migrations/033_ics_feeds.sql
new file mode 100644
index 0000000..b7f2d75
--- /dev/null
+++ b/migrations/033_ics_feeds.sql
@@ -0,0 +1,28 @@
+-- Subscribed iCalendar (.ics) feeds.
+--
+-- doot reads Google Calendar with a service account, by calendar ID. That
+-- cannot consume an .ics URL, and it cannot see a calendar subscribed to inside
+-- a personal Google account either, because Google does not allow re-sharing a
+-- subscribed calendar. Published schedules — sports fixtures being the case
+-- that prompted this — are distributed almost exclusively as .ics.
+--
+-- Feeds are fetched on the normal calendar refresh and their events are merged
+-- into calendar_events alongside Google ones, so they appear on the timeline
+-- without the timeline needing to know they came from a different place.
+--
+-- last_error records the most recent failure and is cleared on success, so a
+-- feed that has quietly stopped resolving is visible in settings rather than
+-- just showing no events.
+
+CREATE TABLE IF NOT EXISTS ics_feeds (
+    id              INTEGER PRIMARY KEY AUTOINCREMENT,
+    name            TEXT NOT NULL,
+    url             TEXT NOT NULL UNIQUE,
+    enabled         INTEGER NOT NULL DEFAULT 1,
+    last_fetched_at DATETIME,
+    last_error      TEXT,
+    event_count     INTEGER NOT NULL DEFAULT 0,
+    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE INDEX IF NOT EXISTS idx_ics_feeds_enabled ON ics_feeds(enabled);