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 --- internal/store/ics_feeds.go | 95 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 95 insertions(+) create mode 100644 internal/store/ics_feeds.go (limited to 'internal/store/ics_feeds.go') diff --git a/internal/store/ics_feeds.go b/internal/store/ics_feeds.go new file mode 100644 index 0000000..6495e13 --- /dev/null +++ b/internal/store/ics_feeds.go @@ -0,0 +1,95 @@ +package store + +import ( + "database/sql" + "time" +) + +// ICSFeed is a subscribed iCalendar feed. +type ICSFeed struct { + ID int64 + Name string + URL string + Enabled bool + LastFetchedAt *time.Time + LastError string + EventCount int + CreatedAt time.Time +} + +// AddICSFeed stores a feed. Re-adding an existing URL updates its name and +// re-enables it rather than failing on the unique constraint — "add it again" +// is the obvious way to try to fix a feed that was turned off. +func (s *Store) AddICSFeed(name, url string) error { + _, err := s.db.Exec(` + INSERT INTO ics_feeds (name, url, enabled) + VALUES (?, ?, 1) + ON CONFLICT(url) DO UPDATE SET name = excluded.name, enabled = 1 + `, name, url) + return err +} + +// GetICSFeeds returns every feed, enabled or not, newest name-order first. +func (s *Store) GetICSFeeds() ([]ICSFeed, error) { + return s.queryICSFeeds(` + SELECT id, name, url, enabled, last_fetched_at, COALESCE(last_error, ''), event_count, created_at + FROM ics_feeds ORDER BY name`) +} + +// GetEnabledICSFeeds returns only the feeds that should be fetched. +func (s *Store) GetEnabledICSFeeds() ([]ICSFeed, error) { + return s.queryICSFeeds(` + SELECT id, name, url, enabled, last_fetched_at, COALESCE(last_error, ''), event_count, created_at + FROM ics_feeds WHERE enabled = 1 ORDER BY name`) +} + +func (s *Store) queryICSFeeds(query string) ([]ICSFeed, error) { + rows, err := s.db.Query(query) + if err != nil { + return nil, err + } + defer func() { _ = rows.Close() }() + + var feeds []ICSFeed + for rows.Next() { + var f ICSFeed + var lastFetched sql.NullTime + if err := rows.Scan(&f.ID, &f.Name, &f.URL, &f.Enabled, &lastFetched, + &f.LastError, &f.EventCount, &f.CreatedAt); err != nil { + return nil, err + } + if lastFetched.Valid { + t := lastFetched.Time + f.LastFetchedAt = &t + } + feeds = append(feeds, f) + } + return feeds, rows.Err() +} + +// SetICSFeedEnabled turns a feed on or off without losing it. +func (s *Store) SetICSFeedEnabled(id int64, enabled bool) error { + _, err := s.db.Exec(`UPDATE ics_feeds SET enabled = ? WHERE id = ?`, enabled, id) + return err +} + +// DeleteICSFeed removes a feed entirely. +func (s *Store) DeleteICSFeed(id int64) error { + _, err := s.db.Exec(`DELETE FROM ics_feeds WHERE id = ?`, id) + return err +} + +// RecordICSFeedResult stores the outcome of a fetch. A successful fetch clears +// any previous error, so a feed that recovers stops being reported as broken. +func (s *Store) RecordICSFeedResult(id int64, eventCount int, fetchErr error) error { + errText := "" + if fetchErr != nil { + errText = fetchErr.Error() + } + _, err := s.db.Exec(` + UPDATE ics_feeds + SET last_fetched_at = CURRENT_TIMESTAMP, last_error = ?, event_count = ? + WHERE id = ? + `, errText, eventCount, id) + return err +} -- cgit v1.2.3