Cloud Storage for Connections and Tabs (Without Storing Passwords)

[dory-finn]

Desktop Cloud Assets Without Cloud Credentials

Summary

Implement a Desktop-only cloud-first model for connections, tabs, and saved queries.

• Desktop CRUD for connection metadata, tabs, saved queries, and saved-query folders goes through cloud APIs by default.
• Sensitive credentials never leave the device.
• Desktop stores secrets only in OS keychain, keyed by a stable cloud connection identifier.
• On a new device, synced connections appear immediately but enter a needs_credentials state until the user supplies local secrets once.

Key Changes

Data model and sync contract

• Introduce a strict cloud-safe connection payload for Desktop.
• Allow only non-sensitive fields in cloud storage:
  name, type, engine, host, port, httpPort, database, path, username, display metadata, non-secret connection settings, non-secret SSH metadata.
• Explicitly exclude from cloud payloads and sync payloads:
  DB passwords, tokens, connection-string embedded secrets, SSH password, private key, passphrase, and any secret-like values inside options.
• Treat current syncOperations.payload as unsafe for Desktop and replace it with a sanitized payload builder before enqueueing or sending.
• Use a stable cloud connection ID as the canonical identifier for cloud resources, local Desktop cache rows, and keychain secret lookup.

Desktop secret storage

• Add a Desktop secret store abstraction under the runtime helper layer.
• Desktop implementation uses OS keychain.
• Secret store covers all sensitive fields:
  DB password, driver-specific tokens/secrets, SSH password/private key/passphrase, and extracted secrets from connection strings.
• Secret records are stored and loaded by stable cloud connection ID plus identity ID when relevant.
• Editing a connection updates cloud metadata and local keychain independently in one client flow.
• Deleting a connection removes cloud metadata first, then best-effort deletes local keychain entries.

Connection lifecycle and UX

• Add connection readiness states for Desktop:
  ready, needs_credentials, error.
• After cloud fetch on Desktop:
  if metadata exists and matching local secrets exist, mark usable;
  otherwise mark needs_credentials.
• New-device behavior:
  show synced connections immediately;
  block connect/query actions until credentials are supplied;
  open the existing connection dialog prefilled with cloud metadata and empty secret fields.
• Connect/query codepaths in Desktop resolve secrets from local keychain, not from cloud DB rows.
• Test connection in Desktop combines cloud metadata with local unsaved secret inputs or stored keychain secrets.

Tabs and saved queries

• Keep tabs, saved queries, and saved-query folders cloud-backed for Desktop.
• Move remaining Desktop-only restoration state out of local-only storage where it affects visible continuity:
  active tab per connection, and optionally current connection selection if exact session restoration is desired.
• Keep truly device-local transient state local:
  ephemeral session IDs and non-portable editor/runtime caches.
• Define conflict policy:
  connections metadata and saved queries use last-write-wins by server updatedAt;
  tab ordering/content also use last-write-wins;
  local unsynced credential edits never merge to cloud.

Runtime boundary

• Apply this behavior only when runtime is Desktop via the shared runtime helper layer.
• Web self-hosting behavior remains unchanged.
• Do not add scattered runtime env checks in feature code; centralize Desktop branching in runtime/storage/service adapters.

Public Interfaces / Contract Changes

• Add a Desktop cloud-safe connection DTO distinct from the existing full connection payload.
• Add a Desktop secret store interface with methods equivalent to:
  loadSecrets, saveSecrets, deleteSecrets, hasSecrets.
• Add or derive a connection usability field exposed to the connections UI:
  needsCredentials: boolean or equivalent status enum.
• Ensure cloud connection records expose a stable cloud ID usable on every Desktop device.

Test Plan

• Create Desktop connection with password and SSH secret:
  cloud request contains no secrets;
  local keychain entry is created.
• Edit Desktop connection metadata only:
  cloud metadata updates;
  keychain secrets remain unchanged.
• Edit Desktop secrets only:
  keychain updates;
  no secret fields appear in cloud traffic or sync outbox.
• Sign in on a second Desktop:
  connection metadata appears;
  status is needs_credentials;
  connect/query is blocked until secrets are entered.
• Supply credentials on second Desktop:
  connection becomes usable without changing cloud metadata.
• Delete connection:
  cloud record is deleted;
  local keychain entry is removed best-effort.
• Tabs and saved queries created on Desktop A appear on Desktop B automatically.
• Active tab restoration behaves per chosen product rule for cloud-restored vs device-local transient state.
• Regression test that syncOperations and any cloud API payloads never contain sensitive fields.

Assumptions and Defaults

• Scope is Desktop runtime only.
• All sensitive fields remain local, not just DB password.
• OS keychain is the required local secret backend.
• New devices show synced connections in a needs_credentials state rather than hiding them.
• Stable cloud connection ID is the canonical key for secret lookup and cross-device mapping.
• Existing Web runtime behavior is unchanged in this phase.

[solarhell]

Implementation Proposal

@dory-finn Please review this implementation plan. Happy to discuss any concerns or adjustments.

Architecture Decisions

• Secret Store: Electron IPC bridge — main process manages keychain via safeStorage, Next.js child process accesses secrets through process.send/process.on('message') IPC
• Cloud API: Sync API endpoints implemented in this repo (apps/web), shared by Desktop and Web
• Sync Trigger: Push immediately on local changes + pull on app startup/window focus + periodic pull every 60s (user may use Desktop and Web simultaneously)
• Conflict Policy: Last-write-wins by server updatedAt

---

Phase 1: Schema Migrations

Add sync tracking columns to tabs, saved queries, and saved query folders. Add credentialState to connections.

Modify:

• apps/web/lib/database/postgres/schemas/tabs.ts — add cloudId (text, nullable, unique when not null), syncStatus (text, default 'local_only'), lastSyncedAt, remoteUpdatedAt, syncError
• apps/web/lib/database/postgres/schemas/saved-queries.ts — same sync columns
• apps/web/lib/database/postgres/schemas/saved-query-folders.ts — same sync columns
• apps/web/lib/database/postgres/schemas/connections.ts — add credentialState (text, default 'ready', values: ready | needs_credentials | error)
• apps/web/lib/database/postgres/schemas/sync-operations.ts — extend SyncEntityType with 'tab' | 'saved_query' | 'saved_query_folder'

Create:

• apps/web/lib/database/postgres/migrations/0006_cloud_sync_fields.sql

---

Phase 2: Electron IPC Secret Store

Manage secrets in Electron main process using safeStorage + encrypted JSON file. Next.js child process communicates via IPC message passing.

Electron Main Process
  ├── safeStorage.encryptString() / decryptString()
  ├── Encrypted JSON file storage
  └── IPC handlers: 'secrets:get', 'secrets:set', 'secrets:delete', 'secrets:has'
        ↕ (child_process message passing)
Next.js Server (forked child process)
  └── SecretStoreService → process.send / process.on('message')

Create:

• apps/electron/main/secret-store.ts — safeStorage encryption + IPC handler registration
• apps/web/lib/secret-store/index.ts — SecretStoreService interface (getSecrets, setSecrets, deleteSecrets, hasSecrets)
• apps/web/lib/secret-store/desktop-secret-store.ts — IPC-based implementation
• apps/web/lib/secret-store/noop-secret-store.ts — No-op for Web/Docker (secrets stay in DB encrypted columns)
• apps/web/lib/secret-store/factory.ts — Factory: Desktop → DesktopSecretStore, others → NoopSecretStore

Modify:

• apps/electron/main/server.ts — register IPC handlers after fork()
• apps/web/lib/database/postgres/impl/connections/index.ts — getIdentityPlainPassword() and getSshPlainSecrets() check secret store first (by cloudId) on Desktop, fallback to DB encrypted columns

---

Phase 3: Cloud-Safe Payload Sanitizer

Ensure sync payloads never contain sensitive fields. Uses allowlist approach (new fields are excluded by default).

Create:

• apps/web/lib/sync/payload-sanitizer.ts
  • sanitizeConnectionPayload() — allowlist: name, type, engine, host, port, httpPort, database, path, description, environment, tags, options (secret keys stripped), status, configVersion; identity: name, username, role, isDefault, database, options, enabled; SSH: enabled, host, port, username, authMethod
  • Explicitly excludes: password, passwordEncrypted, privateKey, privateKeyEncrypted, passphrase, passphraseEncrypted, vaultRef, secretRef
  • sanitizeTabPayload(), sanitizeSavedQueryPayload(), sanitizeSavedQueryFolderPayload() — pass through (no secrets)
• apps/web/lib/sync/types.ts — cloud payload type definitions

Modify:

• apps/web/app/api/connection/route.ts — sanitize payload before enqueuing sync operations in POST/PATCH/DELETE
• apps/web/lib/database/postgres/impl/sync-operations/index.ts — extend entityType to support new types

---

Phase 4: Cloud Sync API Endpoints

Server-side sync API endpoints in apps/web, used by both Desktop (via cloud proxy) and Web.

Create:

• apps/web/app/api/sync/connections/route.ts — POST (upsert by cloudId), GET ?since=<timestamp> (changes since), DELETE ?cloudId=xxx
• apps/web/app/api/sync/tabs/route.ts — same pattern
• apps/web/app/api/sync/saved-queries/route.ts — same pattern
• apps/web/app/api/sync/saved-query-folders/route.ts — same pattern
• apps/web/lib/database/postgres/impl/sync/index.ts — CloudSyncRepository with upsertConnection(), listChangedSince(), deleteByCloudId(), etc.

---

Phase 5: Sync Client (Desktop Side)

Desktop sync worker that handles push/pull logic.

Create:

• apps/web/lib/sync/sync-client.ts — CloudSyncClient using getCloudApiBaseUrl(), reuses auth/cookie forwarding pattern from cloud-ai-proxy.ts
• apps/web/lib/sync/sync-worker.ts — SyncWorker: processes pending queue → pushes to cloud API; pulls remote changes → merges locally; marks connections as needs_credentials when pulled without local secrets
• apps/web/lib/sync/sync-scheduler.ts — orchestrates triggers: immediate push after changes, pull on startup, pull on window focus, 60s periodic pull. Only active when isDesktopRuntime() && getCloudApiBaseUrl() is truthy
• apps/web/lib/sync/conflict-resolver.ts — last-write-wins comparison by updatedAt

Modify:

• apps/web/app/api/sql-console/tabs/route.ts — enqueue sync operations on POST/PATCH/DELETE (Desktop only)
• apps/web/app/api/sql-console/saved-queries/route.ts — same
• apps/web/app/api/sql-console/saved-query-folders/route.ts — same
• apps/web/lib/database/index.ts (or appropriate init point) — start SyncScheduler in Desktop mode

---

Phase 6: Connection Lifecycle & UX

Synced connections on new devices appear in needs_credentials state. Users supply credentials to make them usable.

Create:

• apps/web/app/(app)/[organization]/connections/components/credential-prompt-dialog.tsx — modal showing connection name/host/type, prompting for password and optional SSH credentials
• apps/web/app/api/connection/credentials/route.ts — PATCH /api/connection/credentials?id=xxx, stores secrets in keychain (Desktop) or DB (Web), updates credentialState → 'ready'

Modify:

• apps/web/types/connections.ts — add credentialState to Connection / ConnectionListItem
• apps/web/lib/database/postgres/impl/connections/index.ts — return credentialState in list()/getById(); add createFromCloud() and updateCredentialState() methods
• apps/web/app/(app)/[organization]/connections/hooks/use-connections.ts — add useSupplyCredentials() mutation
• apps/web/app/(app)/[organization]/connections/api.ts — add supplyCredentials() API call
• Connection list UI — show badge for needs_credentials, open credential dialog on click, disable Test/Connect buttons

---

Phase 7: Frontend Sync Status & Polish

Create:

• apps/web/app/(app)/[organization]/connections/components/sync-status-badge.tsx — synced/syncing/failed status icon

Modify:

• apps/web/lib/runtime/runtime.ts — add isSyncEnabled() helper
• Connection list/detail pages — display sync status badge

---

Testing Strategy

No new test dependencies. Uses existing Playwright + Node.js test module.

Unit Tests (apps/web/scripts/tests/):

• payload-sanitizer.test.ts — verify sensitive fields are stripped, allowlist fields preserved, edge cases (null, empty, nested secrets in options), new fields excluded by default
• conflict-resolver.test.ts — last-write-wins logic, edge cases (same timestamp, null timestamp)
• secret-store.test.ts — NoopSecretStore returns null, DesktopSecretStore IPC message format (mock process.send/on)

E2E Tests (tests/e2e/):

• cloud-sync.spec.ts — with local mock API endpoint (apps/web/app/api/sync/mock/route.ts): create connection → verify sync_operations has pending record with no password in payload; create tab/saved query → verify sync records; credential supply flow
• payload-security.spec.ts — regression: scan all sync_operations payloads, assert no password, Encrypted, privateKey, passphrase keywords

Regression: All existing E2E tests must pass unchanged (they run in Web runtime with PGlite, sync logic should not be triggered).

---

Implementation Order

Phase 1 (Schema) ──────────────────┐
Phase 2 (Secret Store IPC) ────────┤ parallelizable
Phase 3 (Payload Sanitizer) ───────┘
        │
Phase 4 (Cloud Sync API) ─── depends on 1, 3
Phase 5 (Sync Client) ────── depends on 1, 2, 3, 4
Phase 6 (Connection UX) ──── depends on 1, 2, 5
Phase 7 (Frontend Polish) ── depends on 6