From 4d160762aeef531208f8cf6094a0e58a9e7d53ec Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Sun, 20 Sep 2026 07:57:36 +0000 Subject: Add .ics calendar feed support MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit doot reads Google Calendar through 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, school terms, bin collections — are distributed almost exclusively as .ics, so none of them could be added at all. Feeds are now fetched on the normal calendar refresh and merged into calendar_events alongside Google events, so the timeline, widget and everything downstream keep seeing one list and needed no changes. Failure handling is deliberately asymmetric. A Google error is still returned as an error, unchanged, so an outage there surfaces. A single failing feed is recorded against that feed and skipped, because one dead URL should not take the whole calendar down — and last_error is shown in settings, since a feed that has quietly stopped resolving otherwise looks identical to a team with no games left. The parser handles what real feeds actually contain: line unfolding, CRLF and LF, UTC / TZID / VALUE=DATE times, and escaped text. Two details worth naming: a TZID time is wall-clock in that zone, and reading it as UTC would put a 1:15pm Pacific kickoff on the timeline at 1:15pm local — wrong by the offset but entirely plausible-looking. And non-calendar content is a hard error rather than zero events, because an HTML error page from a moved feed URL is the common failure and "no events" reads as success. RRULE is deliberately unsupported: every schedule feed this targets expands recurrence itself, one VEVENT per fixture, and a correct expansion is a large amount of subtle code. A recurring event appears once rather than wrongly. Settings gains an add/enable/disable/remove UI. webcal:// links are accepted and rewritten, since that is how feeds are usually advertised. 16 parser tests, 5 store tests. Verified against the real Seattle Seahawks feed: 21 events, all with valid times. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_018AwnALZL6bD4VBtATDVv3B --- migrations/033_ics_feeds.sql | 28 ++++++++++++++++++++++++++++ migrations/README.md | 3 ++- 2 files changed, 30 insertions(+), 1 deletion(-) create mode 100644 migrations/033_ics_feeds.sql (limited to 'migrations') 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); diff --git a/migrations/README.md b/migrations/README.md index a98efa2..1564b48 100644 --- a/migrations/README.md +++ b/migrations/README.md @@ -28,10 +28,11 @@ Migration 007 was reserved during development but never implemented. The gap is Migrations are applied automatically on application startup via `store.RunMigrations()`. The `schema_migrations` table tracks which migrations have been applied. | 032 | `032_import_trello_shopping_items.sql` | One-time import of Trello Shopping-board cards (Hardware Store + unlisted) into user items, ahead of removing Trello as a shopping source | +| 033 | `033_ics_feeds.sql` | Subscribed iCalendar (.ics) feeds, merged into calendar_events | ## Creating New Migrations -1. Create file: `migrations/NNN_description.sql` (next number is 033) +1. Create file: `migrations/NNN_description.sql` (next number is 034) 2. Write idempotent SQL (use `IF NOT EXISTS` where possible) 3. Test locally: `go run cmd/dashboard/main.go` 4. Verify: Check `schema_migrations` table for new entry -- cgit v1.2.3