From 3660486153a16760d2b980e546bbbd29408fb8d4 Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Sun, 16 Aug 2026 00:03:51 +0000 Subject: Replace Google Tasks service-account auth with real OAuth Service-account auth structurally cannot see a regular user's personal task lists (no equivalent of Calendar's per-item sharing model) -- confirmed via GetTaskLists returning exactly the service account's own empty "My Tasks" list, never the real user's three lists. Zero rows were ever cached in production as a result. Adds a standard 3-legged OAuth flow: /settings/google-tasks/connect redirects to Google's consent screen (AccessTypeOffline+ApprovalForce so a refresh_token is always issued), /callback exchanges the code and persists the token (new oauth_tokens table), /disconnect clears it. GoogleTasksClient now takes an option.ClientOption instead of a credentials file path; NewGoogleTasksOAuthClient wraps it with a dbTokenSource that reloads/refreshes from the DB on each access-token expiry and re-persists -- carefully preserving the original refresh_token when Google's refresh response omits one (it usually does), which would otherwise silently and permanently break future refreshes. Settings page shows connection status and a Connect/Disconnect button. Calendar keeps using service-account auth (that one actually works). Requires a one-time manual step: create an OAuth 2.0 Client ID in Google Cloud Console and set GOOGLE_OAUTH_CLIENT_ID/SECRET in .env -- documented in .env.example. --- migrations/029_oauth_tokens.sql | 12 ++++++++++++ 1 file changed, 12 insertions(+) create mode 100644 migrations/029_oauth_tokens.sql (limited to 'migrations') diff --git a/migrations/029_oauth_tokens.sql b/migrations/029_oauth_tokens.sql new file mode 100644 index 0000000..c3b0364 --- /dev/null +++ b/migrations/029_oauth_tokens.sql @@ -0,0 +1,12 @@ +-- OAuth 2.0 token storage for integrations that need real user-consent auth +-- (starting with Google Tasks, whose API has no service-account-friendly +-- sharing model the way Calendar does). One row per source; access_token +-- and expiry get overwritten on every refresh, refresh_token persists. +CREATE TABLE IF NOT EXISTS oauth_tokens ( + source TEXT PRIMARY KEY, + access_token TEXT NOT NULL, + refresh_token TEXT NOT NULL, + token_type TEXT NOT NULL DEFAULT 'Bearer', + expiry DATETIME, + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP +); -- cgit v1.2.3