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.

Create-only writes (ifAbsent)

An operation may carry ifAbsent: true. Only PUT honours it: the row is inserted when absent and left untouched when present, instead of the usual upsert.

The client sets it on every PUT it uploads before the device has completed its first sync (isCreateOnlyWrite in src/db/powersync/connector.ts, gated on currentStatus.hasSynced). Until that first sync the device has never seen the account’s state, so every row it holds is one it invented locally — the bundled defaults and the seeded settings keys, all with deterministic ids that collide with whatever another device already stored. Those writes are guesses, and a guess must not beat a stored value.

This is GH #1299: sync is off by default, so a second device seeds user_has_completed_onboarding = false at boot, and the moment the user enables sync that seed uploaded straight over the already-onboarded value on every other device.

Two properties keep the rule safe:

  • The op is still sent. It is not dropped client-side. When the account genuinely lacks the row — the common case, since sync is off by default and most accounts have never uploaded anything — the row is created. Dropping it instead would leave the device holding rows the server has never heard of, and the first PATCH against one of those would miss, return 400, and wedge the upload queue permanently.
  • Only PUT is constrained, because PUT is the op with no user intent behind it. PowerSync emits PUT from INSERT statements and PATCH from UPDATE statements (UpdateType in @powersync/common), so a PUT is a row the device made up while a PATCH is someone editing a row in front of them. That is what keeps resetting a setting to its default (resetSettingToDefault) working — it rewrites the row to content byte-identical to a fresh seed, so any content-based rule would swallow it.

Known gap — pre-first-sync PATCHes are not fully informed either. The rule guards writes that carry no intent; it does not, and deliberately cannot, guard writes that do. Before its first sync a device is editing its own seeded copy of a bundled default rather than the account’s version, so any PATCH it issues against a deterministic id lands on whatever the account actually stored there. Two instances:

  • Auto-seeded settings — fixed. language (useAppLanguage) and the four unit settings (useUnitDefaults) ship as null and are filled in from the browser or the region at boot. Reconcile used to pre-create those rows, which made the fill-in an UPDATE → PATCH and put it outside the guard, so a new device could overwrite an established device’s chosen language or units. Reconcile now skips inserting null-valued defaults — absent is the target state for them — so the first write is an INSERT → PUT and the guard covers it. Two things depend on that row and were adjusted with it: everyBundleRowAtTarget treats an absent null-default row as at target (so the version marker still advances), and isSettingModified treats an unstamped row for a null-default key as modified (the pre-created row was what carried the defaultHash stamp behind the reset affordance). Only new installs benefit — devices that already created those rows keep them, and their next seed is still a PATCH.
  • Edits and soft-deletes of bundled defaults. Soft-delete is update(...).set({ deletedAt }), so removing a default skill or model on a fresh device before enabling sync propagates that deletedAt onto the account’s row — possibly a customized version the user has on another device. Accepted: constraining PATCH would lose real offline edits, which is worse than propagating one made against a stale view.

Which device runs onboarding

Sync is off by default, so a device signing in to an account that already exists cannot read the synced user_has_completed_onboarding setting — all it has is the false reconcile seeded at boot. markOnboardedForReturningUser (src/lib/returning-user-onboarding.ts) treats “the account already existed” as “onboarding already happened” and writes the setting locally.

It must be called from every sign-in entry point. Reaching only the sign-in modal and the waitlist page is GH #1299: the magic-link path left the seeded false in place, so the user redid onboarding there and then uploaded that false over the real value on every other device.

The isNew flag it reads has to come from the sign-in response, never from useSession(). The backend retires the flag inside the sign-in request (markUserNotNew in the auth after hook), and Better Auth’s session atom is fed solely by /get-session — so every session read reports false and cannot distinguish a fresh signup from a returning sign-in.

Not covered: SSO. It leaves via a full page navigation and returns through a browser reload, so there is no in-app sign-in response to read, and markUserNotNew never runs on that path anyway (it is gated to /sign-in/email-otp), leaving the flag stuck at its true default for SSO accounts. An SSO user therefore still sees onboarding on each new device until they enable sync.

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.