Sync

PowerSync is the sync layer between the client (local SQLite via wa-sqlite / IndexedDB) and the server (Supabase Postgres). Local SQLite is the single source of truth for the UI - stores read only from SQLite, never from the network directly.

How It Works

Supabase Postgres (server)
  ↕  logical replication (powersync_role, powersync publication)
PowerSync service (self-hosted, :8080)
  ↕  WebSocket sync stream (Sync Streams edition 3, per-user + global)
Local SQLite (client, IDBBatchAtomicVFS / IndexedDB)
  ↕  watchTable subscriptions
Pinia stores (UI)

On connect, PowerSync replays the server state into local SQLite. After that, every server change streams in via the open WebSocket. Local writes go to SQLite immediately (optimistic) and are queued for upload.

Sync Streams

Sync rules are defined in app/powersync/config/sync-config.yaml using Sync Streams edition 3.

Per-user stream (user_data, auto_subscribe):

SELECT * FROM categories  WHERE "userId" = auth.user_id()
SELECT * FROM wallets     WHERE "userId" = auth.user_id()
SELECT * FROM trns        WHERE "userId" = auth.user_id()
SELECT * FROM user_settings WHERE "userId" = auth.user_id()

Global stream (rates): all rows go to every authenticated user; rows are written server-side by the fetch-rates edge function, never by clients.

SELECT * FROM rates

auth.user_id() resolves to the Supabase user UUID from the validated JWT. The PowerSync service validates Supabase JWTs (ES256) via the JWKS endpoint, so no custom token logic is required on the client.

watchTable - the Single Subscription Pattern

watchTable(sql, params, onRows, throttleMs?) in app/services/powersync/db.ts:

• Executes the SQL query immediately and calls onRows with the current local rows.
• Subscribes to table-change events. Whenever any row in the queried table changes (local write or incoming sync), onRows is called again with fresh rows.
• throttleMs coalesces rapid bursts of changes (trailing edge). Default: 30ms. Transactions use 120ms to handle the large burst of rows during initial sync without thrashing the UI.
• Returns an AbortController - call .abort() to unsubscribe (stores do this on teardown).

This single mechanism replaces the old init + realtime-subscription split. There is no separate "load from cache, then subscribe" step.

Echo Suppression (Transactions)

When a store writes optimistically and then watchTable fires for that same write, the UI would flicker. useTrnsStore avoids this with reconcileTrns(prev, rows) (app/app/components/trns/reconcile.ts):

• Compares incoming rows to the previous state by updatedAt.
• If nothing has changed, returns the same prev ref (Vue sees no change, no re-render).
• For changed rows, only runs rowToTrn() for the delta - unchanged row objects are reused.

Upload Queue

Local writes are queued by PowerSync automatically. SupabaseConnector.uploadData() (app/services/powersync/connector.ts) drains the queue:

The connector runs as the authenticated Supabase user, so RLS policies apply on every upload. This is the write-path security layer - the read path uses the powersync_role (BYPASSRLS) for replication, with per-user partitioning enforced by sync rules.

Fatal vs Retryable Upload Errors

Discarded ops are classified by planDivergence() (app/services/powersync/uploadReconcile.ts) and reconciled automatically:

• Rejected INSERTs - the local rows are deleted (ids are client-generated, the server never accepted them), with a cascade to local trns referencing a reverted wallet/category. Toast: sync.errors.uploadReverted.
• Rejected UPDATE/DELETE - the server still holds a prior version that PowerSync can't re-pull per row, so the user is offered a full re-sync (wipe + re-pull). Toast: sync.errors.uploadDiverged.

Deletion Handling

PowerSync syncs hard deletes natively - when a row is deleted on the server the PowerSync service streams a delete event to the client and SQLite removes the row. No hash comparison or full-refetch is needed.

Cascade deletes (client-side): deleteWallet and deleteCategory also delete the entity's trns from local SQLite before returning. Without this, the watchTable subscription on trns would re-add the orphaned rows to the UI after the parent entity is gone.

First Sync

waitForFirstSync(timeoutMs=30000) (app/services/powersync/db.ts) resolves true once the initial server state has fully streamed into local SQLite, false on timeout. The boot does not block on it - cached/local data paints immediately and the first sync settles in the background (awaitInitialSync in useInitApp.ts). Its result only gates the onboarding/error screens, so a fresh login never flashes onboarding while data is still arriving.

Key Files

Next Steps

• Offline-first - offline writes, upload queue, and fatal error handling
• Architecture - app initialization and store pattern