Skip to content
Thunderbolt

PowerSync, Account & Device Management

This document consolidates documentation for:

  • PowerSync: multi-device sync (synced tables, local dev, adding tables)
  • Account: deletion flow and how other devices reset
  • Devices: registration, list, revoke, and how a revoked device resets

1. PowerSync Overview

PowerSync provides offline-first sync between the backend (PostgreSQL) and clients (SQLite). Data is scoped by user_id from the JWT. The backend issues PowerSync JWTs and can apply client uploads (PUT/PATCH/DELETE) to Postgres. Production runs the self-hosted PowerSync service on Render (image: ghcr.io/thunderbird/thunderbolt/thunderbolt-powersync); local development uses the Docker stack in powersync-service/. The frontend never hard-codes the PowerSync URL — the backend returns it in /powersync/token, so URL changes are transparent to clients.

For the sync data transformation middleware and custom SharedWorker (E2E encryption pipeline), see docs/powersync-sync-middleware.md.

2. Synced Tables

Requirements

Current Tables

Defined in shared/powersync-tables.ts:

settings, chat_threads, chat_messages, tasks, models, prompts, skills, triggers, model_profiles, devices, agents, projects.

Indexes and Foreign Keys

Backend (PostgreSQL) uses a minimal index strategy:

  • ✅ Primary keys (required)
  • ✅ Single user_id index on every table (required for PowerSync sync rules)
  • ❌ No composite foreign key constraints
  • ❌ No active indexes (WHERE deletedAt IS NULL)
  • ❌ No foreign key indexes

Rationale: The backend is primarily a sync server, not a query engine. Complex queries and JOINs happen on the frontend (SQLite). With E2E encryption planned, backend indexes on encrypted data would be useless. Minimal indexes reduce storage overhead and improve write performance during sync operations.

Frontend (SQLite) can use any indexes needed for local query optimization since queries happen there.

See docs/composite-primary-keys-and-default-data.md for detailed explanation.

Adding a New Synced Table

  1. Create the table in both src/db/tables.ts and backend/src/db/powersync-schema.ts (include user_id).
  2. Backend schema: Add only a user_id index: index('idx_[table]_user_id').on(table.userId). Do not add composite foreign keys or other indexes (see above).
  3. Register in src/db/powersync/schema.ts (drizzleSchema).
  4. Add the table name and query keys in shared/powersync-tables.ts (powersyncTableNames and powersyncTableToQueryKeys). The query-key entry is required by the map’s type but has no runtime consumer — reactivity comes from PowerSync itself; see the note on the map.
  5. Update all three sync-rule configs so local, preview, prod, and enterprise-k8s stay in parity:
    • powersync-service/config/config.yaml — local docker-compose.
    • deploy/config/powersync-config.yaml — baked into the ghcr.io/thunderbird/thunderbolt/thunderbolt-powersync image; used by preview stacks (Pulumi) and prod on Render.
    • deploy/k8s/templates/configmaps.yaml — Helm-rendered config for the enterprise k8s deploy path. Add a line under sync_rules.content → the appropriate bucket in each: bucket_definitions.user_essentials.data for latency-sensitive tables (loaded first, priority 1), or bucket_definitions.user_data.data for the rest (priority 2). Example: - SELECT * FROM powersync.my_table WHERE user_id = bucket.user_id.
  6. Run migrations for frontend and backend as needed.

PR Flow for Adding Tables

Split the work into two PRs to avoid sync rule mismatches:

  1. PR 1 – Backend schemas, migrations, and sync rules

    • Backend: table in backend/src/db/powersync-schema.ts, migration, shared/powersync-tables.ts, and all three sync-rule configs (see step 5 above).
    • Merge this PR first. On merge, .github/workflows/images-publish.yml rebuilds ghcr.io/thunderbird/thunderbolt/thunderbolt-powersync with the updated sync rules baked in.
    • Roll the Render powersync service to the new image tag before merging PR 2. Preview stacks pick up the new image on their next Pulumi apply.

  2. PR 2 – Frontend and remaining changes

    • Frontend: table in src/db/tables.ts, src/db/powersync/schema.ts, and any UI/feature code.
    • Merge after PR 1’s image is live on Render.

Adding Columns to an Existing Synced Table

A new column on a SELECT * table (all current sync rules) carries the same silent-failure risk as a new table: the backend migration must deploy and the PowerSync Cloud sync rules must be refreshed before the column replicates. If the frontend schema ships first, the column stays null across devices while local tests pass. When backend and frontend land in the same PR (e.g. devices.node_id / node_id_attested_at, migration 0021), splitting is unnecessary if the feature tolerates a null value, but the deployer must still run the migration and refresh the dashboard rules before relying on the column cross-device.

3. Local Development (PowerSync Docker)

See powersync-service/README.md for full steps. Summary:

  • From the repo root: make up (or from powersync-service/: docker compose up -d)
  • PowerSync API: http://localhost:8080
  • Postgres: localhost:5433 (use this for the backend so PowerSync and app share one database)
  • Backend .env: set DATABASE_DRIVER=postgres, DATABASE_URL=postgresql://postgres:postgres@localhost:5433/postgres, and PowerSync vars (see below)
  • Sync rules in powersync-service/config/config.yaml must match backend tables; when you add/change tables, update that file. The backend’s upload validator (validTables in backend/src/dal/powersync.ts) is derived from powersyncTableNames in shared/powersync-tables.ts automatically — no manual sync needed there.

Backend PowerSync Env Vars (Local)

Terminal window
POWERSYNC_URL=http://localhost:8080
POWERSYNC_JWT_SECRET=powersync-dev-secret-change-in-production
POWERSYNC_JWT_KID=powersync-dev
POWERSYNC_TOKEN_EXPIRY_SECONDS=3600

The local config/config.yaml uses HS256 with the same secret (base64) and kid so backend-issued tokens are accepted.

4. Account Deletion

  • Where: Settings > Preferences → “Delete my account” (with confirmation).
  • Request: Frontend calls DELETE /v1/account with the current auth token.
  • Backend: Deletes the user and all related data (settings, chats, models, devices, etc.).
  • Other devices: When PowerSync refreshes the token, the backend returns 410 Gone with code: 'ACCOUNT_DELETED'. The app treats this as credentials invalid and runs the reset flow (see section 7).

5. Device Management

Devices Table

  • Backend: devices table: id, user_id, name, status (APPROVAL_PENDING | TRUSTED | REVOKED), public_key, mlkem_public_key, last_seen, created_at, revoked_at. Synced via PowerSync.
  • Frontend: Same schema in the local DB; used for Settings > Devices and for “current device revoked?” checks.
  • See e2e-encryption.md for how status and public_key are used in the encryption setup and device approval flows.

Listing Devices

  • Where: Settings > Devices.
  • Data: Devices from the local DB (synced devices table) via getAllDevices() and React Query key ['devices'].
  • UI: Name, last seen, “This device” for current device, “Revoked” when revoked_at is set. “Revoke” only for other, non-revoked devices.

Revoking a Device

  1. User chooses “Revoke” on another device (with confirmation). Frontend calls POST /v1/account/devices/:id/revoke.
  2. Backend runs a transaction: deletes the device’s envelope from the envelopes table, then sets status to REVOKED and revoked_at on the device row. The wrapped CK is permanently removed, preventing future CK recovery even if the device’s private key is compromised. PowerSync syncs the updated devices table.
  3. On the revoked device:
    • Immediate: The app watches the current device’s row via React Query (getDevice(deviceId)). When the synced row has status === ‘REVOKED’ or revoked_at set, the app runs the reset flow.
    • On token refresh: Backend returns 403 Forbidden with code: ‘DEVICE_DISCONNECTED’; the connector dispatches credentials invalid and the app resets.

CLI Devices

The CLI uses account-first onboarding and a stable cli-<uuid> installation. See CLI Device Registration and Logout for the registration, binding, logout, and revocation contract.

CLI devices are account/revocation records, not PowerSync clients. The backend rejects cli- IDs from PowerSync token and upload flows. The cli- namespace is server-reserved, so a device_type = 'cli' row cannot reach these flows under another ID. CLI provider profiles, model selection, account tokens, and confidential cache material stay in the CLI’s local state root and do not sync to the web app or another CLI installation.

A trusted web device can revoke a CLI device through the regular device list. The revoked CLI cannot continue using the bound session and must complete web login again. Personal access tokens are separate: THUNDERBOLT_TOKEN supports headless direct managed inference only, is not device-bound, and must be revoked through the PAT lifecycle rather than CLI logout. Confidential models require a web session unless the operator sets CONFIDENTIAL_API_KEYS_ENABLED=true; otherwise a PAT request fails with WEB_LOGIN_REQUIRED without fallback or replay. See backend/docs/pat-lifecycle.md for why that gate is an authorization choice rather than a property of the confidential transport.

Auth Token and Device ID

  • Auth token: In localStorage (fixed key). Cleared on reset via localStorage.clear().
  • Device id: In localStorage. Sent as X-Device-ID (and optional X-Device-Name) on PowerSync token requests so the backend can register/update the device and enforce revoke.

6. Backend API

PowerSync Token (GET /powersync/token)

  • With X-Device-ID:
    • Backend checks the devices row for that id. If status === 'REVOKED' or revoked_at is set → 403 with { code: 'DEVICE_DISCONNECTED' }, no token.
    • Otherwise: issues a PowerSync JWT and upserts the device (id, user_id, name, last_seen, created_at).
  • Bearer token only (e.g. credential refresh):
    • If the user no longer exists (account deleted) → 410 Gone with { code: 'ACCOUNT_DELETED' }.
    • Otherwise may return 401 (invalid/expired token).

PowerSync Upload (PUT /powersync/upload)

  • Requires authenticated user and X-Device-ID header.
  • Same device validation as token: if device is revoked → 403 with { code: 'DEVICE_DISCONNECTED' }. If X-Device-ID is missing → 400 with { code: 'DEVICE_ID_REQUIRED' }.
  • Only non-revoked devices can upload data.

Summary for client:

  • 410 → account deleted (reset).
  • 403 with DEVICE_DISCONNECTED → this device revoked (reset).
  • 409 with DEVICE_ID_TAKEN → device id already registered to another user; reset to get a fresh device id.
  • 401 → generic auth failure.

Revoke Device (POST /v1/account/devices/:id/revoke)

  • Requires authenticated user (session).
  • Runs in a transaction: deletes the device’s envelope, then sets status to REVOKED and revoked_at for the device that belongs to the current user.
  • 204 on success (idempotent for already-revoked devices).

CLI Device Registration and Logout

  • PUT /v1/account/devices/cli requires a valid non-anonymous persisted web session plus canonical CLI device, device-name, and app-version headers. It registers or touches the installation and binds that session to the device.
  • POST /v1/account/devices/cli/logout is remote-first: it revokes the bound CLI device and all of its sessions before returning 204.
  • Revoked devices return DEVICE_DISCONNECTED; invalid or expired sessions return 401. Clients do not replay a failed inference request after login.

Managed Catalog Privacy

GET /v1/config publishes managed models through defaults.models: versioned SharedModel rows without apiKey, plus defaultModelId. Price tables, quota internals, credentials, and other deployment secrets remain backend-only.

For the mandatory old-client-safe rollout order, see CLI Device Rollout.

Encryption API Endpoints

The following endpoints handle encryption setup, device approval, and key recovery. See e2e-encryption.md for full documentation.

  • POST /devices — register device with public key (encryption setup)
  • POST /devices/:deviceId/envelope — store wrapped content key
  • GET /devices/me/envelope — fetch own wrapped content key
  • GET /encryption/canary — fetch canary for recovery key verification

7. Frontend: Credentials-Invalid and Reset

When the app should reset (account deleted or device revoked), it runs a single flow:

  1. setSyncEnabled(false) – disconnect from PowerSync.
  2. localStorage.clear() – remove auth token and device id.
  3. resetAppDir() – clear the app directory (DB and related files).
  4. window.location.reload() – reload to a clean, signed-out state.

Triggered in two ways:

  1. Event powersyncCredentialsInvalid
    Dispatched when the token request returns 410 or 403 with body code: 'DEVICE_DISCONNECTED'.
  2. Devices table (current device revoked)
    usePowerSyncCredentialsInvalidListener uses React Query getDevice(deviceId) and key ['devices', deviceId]. When the synced devices row has revoked_at set for the current device, the hook runs the same reset flow (immediate, without waiting for next token refresh).

After revoke, the Settings > Devices list is updated by invalidating ['devices'] so the list reflects the new state after sync.

8. Summary

Action Where Backend / sync behavior Other device behavior
Delete account Preferences User and data deleted; 410 on token refresh Reset when 410 received or when sync reflects deletion
Revoke device Devices Set revoked_at; 403 on that device’s refresh Revoked device resets when it sees revoked_at (useQuery) or gets 403 on refresh

Both paths use the same reset: disable sync, clear localStorage, reset app dir, reload.