Note (2026-04-24): After this document was written, legal_entity was renamed to tenant and the old tenant was renamed to organization. Read references to these terms with the pre-rename meaning.

Google Drive File Store Integration

Summary

Replace the admin-configured file store with a user self-service Google Drive integration. Users connect their Google Drive via OAuth on the settings page, select a folder via Google Picker, and the app reads files from that folder for FP&A analysis. Each legal entity gets its own Google Drive connection.

Data Model

DB Migration

Rename legal_entity_integration → legal_entity_datev_integration
- Update all indexes, constraints, and references
- Add nullable connected_by_user_id UUID REFERENCES orcha_user(id)
New enum oauth_integration_type with value 'google_drive'
New table legal_entity_oauth_integration:

Column	Type	Constraints
id	UUID	PK, DEFAULT gen_random_uuid()
legal_entity_id	UUID	NOT NULL, FK → legal_entity(id) ON DELETE CASCADE
integration_type	oauth_integration_type	NOT NULL
is_active	BOOLEAN	NOT NULL DEFAULT true
connected_by_user_id	UUID	NOT NULL, FK → orcha_user(id)
refresh_token_encrypted	BYTEA
refresh_token_expires_at	TIMESTAMPTZ
config	JSONB	NOT NULL DEFAULT '{}'
metadata	JSONB	NOT NULL DEFAULT '{}'
disconnect_reason	TEXT
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
		UNIQUE(legal_entity_id, integration_type)

Data Storage

legal_entity_oauth_integration.config: {"folder_id": "abc123", "folder_name": "FP&A Data"}
legal_entity_oauth_integration.metadata: {"email": "user@example.com"}
legal_entity_oauth_integration.refresh_token_encrypted: KMS-encrypted Google OAuth refresh token
legal_entity.fpna_data_source: Set to {"protocol": "google-drive"} on connect, cleared on disconnect

Local Dev File Store

The resolve-file-store function checks for a dev override in config before querying the DB. Config example:

;; config.edn
{:dev-file-store {:protocol "file" :path "/data/acme/"}}

OAuth Flow

GCP Setup

Google Cloud project with Drive API and Picker API enabled
OAuth 2.0 client ID (web application type)
Scope: https://www.googleapis.com/auth/drive.readonly
Client ID, client secret, and API key (for Picker) stored in AWS SSM

Flow

User clicks "Connect Google Drive"
  → GET /settings/integrations/google-drive/authorize?legal_entity_id=...
  → Build Google OAuth URL with HMAC-signed state (legal_entity_id + user_id)
  → Redirect to Google consent screen
  → User grants drive.readonly
  → GET /settings/integrations/google-drive/callback?code=...&state=...
  → Exchange code for tokens
  → Encrypt refresh token with KMS
  → Store in legal_entity_oauth_integration (no folder yet)
  → Set fpna_data_source = {"protocol": "google-drive"}
  → Redirect to settings page
  → Settings page detects "connected but no folder" state
  → Renders Google Picker inline
  → User selects a folder
  → POST /settings/integrations/google-drive/select-folder {folder_id, folder_name}
  → Store folder in config JSONB
  → Section updates to show "Connected — folder: X"

Connection States

Not connected — "Connect Google Drive" button
Connected, no folder — Google Picker shown inline for folder selection
Connected with folder — Shows folder name, "Change folder" and "Disconnect" actions

Disconnect

Clear refresh_token_encrypted, config, metadata
Set fpna_data_source to nil on the legal entity
Optionally revoke token via Google's revocation endpoint

FileStore Implementation

New Namespace: `com.getorcha.link.mcp.file-store.google-drive`

Implements FileStore protocol for "google-drive" protocol:

list-files — Calls Google Drive API files.list with '<folder_id>' in parents query. Maps results to FileStore format (:name, :path, :size, :modified, :type, :directory?). Supports subdirectory traversal and :file-type filtering.
read-file — Calls Drive API files.get with alt=media, returns InputStream.

Constructor receives a map with :folder-id and :access-token.

`resolve-file-store`

New function in com.getorcha.link.mcp.file-store. Receives the full MCP tool context map.

Reads fpna_data_source from the legal entity
If protocol is "google-drive": queries legal_entity_oauth_integration, decrypts refresh token, exchanges for a fresh access token via Google's token endpoint, passes access token + folder ID to make-file-store
If protocol is "file": checks dev config override first, falls through to make-file-store
MCP tools call resolve-file-store instead of make-file-store directly

Settings Page UI

Per-Legal-Entity Google Drive Section

Added to the Integrations area of /settings/integrations. Unlike email/DATEV (which use primary-le-id), Google Drive renders a card per legal entity since each LE gets its own connection.

Google Picker

External script: https://apis.google.com/js/api.js
Loaded on the settings page when needed
Receives a short-lived access token (not the refresh token)
Triggered in "connected, no folder" state or when user clicks "Change folder"
On selection, HTMX POST to select-folder endpoint

Routes

GET  /settings/integrations/google-drive/authorize       — Initiate OAuth
GET  /settings/integrations/google-drive/callback         — OAuth callback
POST /settings/integrations/google-drive/select-folder    — Store selected folder
POST /settings/integrations/google-drive/disconnect       — Remove connection

Config & Infrastructure

SSM Parameters

Parameter	Purpose
`/orcha/{env}/google-drive/client-id`	OAuth client ID
`/orcha/{env}/google-drive/client-secret`	OAuth client secret
`/orcha/{env}/google-drive/api-key`	Google Picker API key

Infrastructure Changes

infra/: CDK stack adds the three SSM parameters
dev/init_aws.clj: Seeds SSM parameters in LocalStack for local development
Test fixtures: Seeds SSM parameters in test LocalStack setup

config.edn

New :google-drive key with SSM references for client-id, client-secret, api-key
New :dev-file-store key for local development override

Codebase Changes Summary

Migration: Rename legal_entity_integration → legal_entity_datev_integration, create legal_entity_oauth_integration table + enum
DATEV references: Update all code referencing legal-entity-integration to legal-entity-datev-integration
Google Drive OAuth: New routes + handlers in com.getorcha.app.http.settings.integrations (or new namespace)
Google Drive FileStore: New com.getorcha.link.mcp.file-store.google-drive namespace
resolve-file-store: New function in com.getorcha.link.mcp.file-store
MCP tools: Update fpna/list_files and fpna/excel to use resolve-file-store
Settings page UI: Google Drive connection section per legal entity
SSM/config/infra: CDK stack, init_aws.clj, test fixtures
Admin panel: Leave as-is (read-only view of file store config)