Skip to content

Add Google Sheets as a second backend #64

Description

@xiaomi7732

Goal

Let users sign in with a Google account and use Google Sheets as their data backend, alongside the existing Microsoft Calendar backend. The storage abstraction landed in #63 makes the storage side cheap; the new work is mostly the auth abstraction and the Sheets store implementation.

Scope decisions (locked in via planning session)

Decision Choice
Account model One provider per user per session — sign out to switch
Provider mix in same session No — single active provider
Drive scope drive.file (app sees only its own files; user can manage them normally in Drive)
File layout One spreadsheet per book; one TODOs sheet; UUID id column for stable row identity
MSAL fate Kept; runs alongside Google Identity Services behind a new AuthClient abstraction
PR delivery 3 phased PRs

Phases

Phase 1 — AuthClient abstraction (no behavior change)

Refactor existing MSAL auth behind an AuthClient interface. Microsoft remains the only active provider. Zero user-visible change.

  • lib/auth/types.ts: AuthClient interface (acquireToken, login, logout, getUser, isAuthenticated, busy)
  • lib/auth/microsoft/MicrosoftAuthClient.ts: wraps existing MSAL logic
  • lib/auth/AuthContext.tsx + useAuthClient hook: exposes the active client based on stored provider preference
  • Replace useGraphToken usage across pages with useAuthClient; delete useGraphToken
  • useStore injects useAuthClient().acquireToken (no MSAL coupling in the store)

Phase 2 — Google support behind feature flag

Add Google Identity Services + Google Sheets backend. Feature-flagged off; developer mode only.

  • lib/auth/google/GoogleAuthClient.ts: GIS token client with silent refresh, login popup, revoke on logout
  • lib/store/sheets/GoogleSheetsStore.ts implements TodoStore:
    • listBooks → list app-created .gsheet files in Drive
    • createBook → create spreadsheet with TODOs worksheet + header
    • CRUD on items via spreadsheets.values (append/update/get) and batchUpdate for deletes
    • 16-column schema (id, subject, etsDateTime, etaDateTime, status, urgent, important, categories, checklist, remarks, startDateTime, finishDateTime, originalEts/EtaDateTime, createdAt, updatedAt)
  • New sheet: prefix in MultiBackendStore; BackendKind adds 'google'
  • Date-bump logic doesn't apply (Sheets has no calendarView-style window); originalEts/EtaDateTime columns retained for parity
  • Feature flag: NEXT_PUBLIC_ENABLE_GOOGLE_PROVIDER

Cloud Console paperwork (one-time, owner does)

  1. GCP project + enable Sheets API and Drive API
  2. OAuth 2.0 web client (redirect URI = prod origin + localhost)
  3. Consent screen with scopes drive.file + spreadsheets (non-sensitive — no Google verification needed for production)
  4. NEXT_PUBLIC_GOOGLE_CLIENT_ID env var in deployment

Phase 3 — Provider picker UI + flip flag default-on

  • Landing page: two sign-in buttons (Microsoft / Google)
  • Provider preference persisted in localStorage; auto-restored on return visits
  • Cross-provider redirect: URL with prefix that doesn't match active provider → /books
  • Book list shows backend badge (📅 / 📊)
  • Flip the feature flag default-on

Out of scope

  • Multi-provider in the same session (both Microsoft AND Google visible together)
  • Server-side OAuth / refresh tokens (this is a static SPA)
  • Service worker / offline support
  • Concurrent edit conflict resolution (same limitation as Microsoft Calendar today)

Sequencing

Phase 1 of this work touches useStore.ts, which #63 just modified. Waiting for #63 to merge into main before starting Phase 1 from a fresh branch.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions