migrations/033_ics_feeds.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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);