feat: watch Google Docs for changes and sync to channels

[bokelley]

Summary

Enable Addie to monitor Google Docs for changes and automatically update channel knowledge when foundational documents are modified. This would allow working groups to maintain living documents that Addie stays current on.

Use Case

Working groups often have foundational Google Docs (charters, frameworks, guidelines) that evolve over time. Currently, Addie can read these documents when asked, but has no way to know when they've been updated. This feature would let channels "watch" specific Google Docs, with Addie automatically re-indexing content when changes occur.

Technical Approach

Option 1: Google Drive Push Notifications (Recommended)

• Use Google Drive API's push notifications (webhooks)
• Register a webhook endpoint (e.g., /api/google/drive-webhook)
• Google POSTs to the endpoint when watched files change
• Limitation: Notification channels expire after max 7 days, requiring automated renewal

Option 2: Polling

• Scheduled job polls files.get with fields=modifiedTime for watched documents
• Simpler implementation, no webhook infrastructure needed
• Less real-time, but adequate for document updates (hourly polling likely sufficient)

Required Components

1. Database table - Track watched documents:

   CREATE TABLE watched_google_docs (
     id SERIAL PRIMARY KEY,
     doc_id TEXT NOT NULL UNIQUE,
     doc_url TEXT NOT NULL,
     channel_id TEXT,  -- Associated notification channel (optional)
     last_modified TIMESTAMPTZ,
     last_synced TIMESTAMPTZ,
     watch_expiry TIMESTAMPTZ,  -- For push notification renewal
     created_at TIMESTAMPTZ DEFAULT NOW()
   );

2. Watch management:

   • Tool for admins to add/remove watched docs
   • Link docs to specific channels for context

3. Change detection:

   • Webhook endpoint OR polling job
   • Re-fetch and re-index content on change

4. Notification renewal (if using push):

   • Scheduled job to renew expiring watch subscriptions

API Scope

Current OAuth scopes (drive.readonly, documents.readonly) are sufficient for watching files.

Questions to Resolve

• Should this be admin-only, or can channel leads watch docs for their channels?
• Should Addie post a summary of changes to the channel when a watched doc updates?
• How many documents should we support watching? (Affects polling vs push decision)

Related

• PR feat: add Google Docs integration for Addie #685 - Google Docs read integration (prerequisite)