Design: Google Drive folder two-way sync (Phase 10)

[Jason-jo17]

Design / tracking issue for the Google Drive folder two-way sync feature.

Summary

Let a workspace connect Google Drive (bring-your-own OAuth app, like the Voyage/Gemini keys) and link a Mnema folder to a Drive folder. Files added under a linked folder in Mnema appear in the Drive folder, and files added in Drive appear in Mnema — for an allow-listed set of file types. Modelled on the existing Google Calendar integration (routes/calendar.ts, lib/google-calendar.ts, lib/secret-box.ts, workspace_members.calendar_refresh_token) but wired to core tables (folders/docs/attachments) — no enterprise coupling, so verify-core-only stays green.

Goals

• Per-user OAuth connect (offline refresh token, encrypted at rest via secret-box).
• Link a Mnema folder ⇄ a Drive folder (pick existing, or create one in Drive from a Mnema folder).
• Configurable accepted file types per link.
• Two-way sync: text (.md/.txt) → Mnema docs; binaries (.pdf/.docx/images) → attachments (R2).
• Settings → Integrations UI + a fleshed-out connections/drive page.
• Self-host docs: Google Cloud OAuth-app setup + env + sync behaviour.

Non-goals (v1)

• Syncing a doc's live collaborative body into a Google Doc and back (CRDT ⇄ Google Docs is out of scope; we sync files and export docs to .md).
• Real-time (<1s) sync — near-real-time via the Drive push webhook + a worker.
• Google Shared Drives.

Data model (new, core)

• workspace_members.drive_refresh_token (encrypted) — mirrors calendar_refresh_token.
• drive_folder_links — Mnema⇄Drive folder pairing + direction + accepted types + conflict policy + push-channel + sync status.
• drive_file_mappings — per-file idempotency + conflict tracking (Drive fileId ⇄ doc/attachment + md5/hash).

Migration 0073_drive_sync.sql, idempotent, applied by the psql migrate service (and registered in the drizzle journal for CI's db:migrate).

OAuth + sync

• lib/google-drive.ts — OAuth (offline) + Drive REST via the googleapis SDK.
• lib/drive-sync.ts — pull (Drive→Mnema) + push (Mnema→Drive), idempotent by md5/content-hash; manual conflict policy never clobbers un-pushed local edits.
• routes/drive.ts — connect / callback / status / folder picker / links CRUD / sync-now / conflicts / push webhook.
• BullMQ drive-sync queue + worker; triggered by "Sync now" and the Drive push webhook.

Decisions taken

• Dependency: googleapis SDK (approved — Drive changes + resumable-upload surface).
• Scope: configurable — GOOGLE_DRIVE_SCOPE=drive.file (default, least privilege) or drive (full, to pull pre-existing folders; needs Google's restricted-scope review in production).
• Credentials: bring-your-own — each operator supplies a Google Cloud OAuth client; nothing is baked into the repo.

Implementation (stacked PRs)

• feat(drive): Google Drive folder sync — backend (Phase 10) #96 — backend (schema/migration/OAuth/sync/routes/worker) · merge first
• feat(drive): Google Drive folder sync — frontend (Phase 10) #97 — frontend (Settings → Integrations UI) · depends on feat(drive): Google Drive folder sync — backend (Phase 10) #96
• docs(drive): Google Drive setup + connectivity guide (Phase 10) #98 — docs (docs/connect/drive.md, .env.example, README) · independent

Verification

@boppl/api typechecks/builds clean; schema ↔ migration 0073 verified column-for-column (no table mismatch). Live OAuth + sync needs an operator's Google Cloud OAuth app + a running stack (runbook in #98). The @boppl/api test suite runs in CI.