6 Offline-First Sync

A sync engine like PowerSync solves the transport problem (getting rows from Supabase Postgres onto an iPad and local writes back) and it stops there. The semantics problem stays with you: which data belongs on the device, who wins when two reps sell the same jacket, and what "saved" means when there is no network. This chapter builds the trade show order-taking client end to end: sync rules that scope each device's dataset, a create-only write model that keeps inventory truthful, a visible upload queue, and deterministic tests for the ugly interleavings.

What you need to know first

This chapter has two computers in it, and almost every hard idea here comes from one fact: they are separated by air, and the air is not reliable.

The two computers: client and server

The server is a computer in a data center that never sleeps. It runs the Postgres database you have been building since chapter 1, holding the styles, the SKUs (stock-keeping units: one style in one color in one size), the inventory ledger, and the orders. It holds the copy of the data the whole company shares.

The client is the program in front of an actual human. Here that is an app on a sales rep's iPad, on the floor of a trade show, in a concrete convention hall. Client asks, server decides.

Why an ordinary app dies when the internet does

Every screen you have ever built is really a question sent over the network. "Show me this customer" is an HTTP request that travels to the server, hits Postgres, and travels back. The list of jackets on screen is not in the app. It was borrowed a moment ago and will be borrowed again on the next screen.

So when the network goes, the app has nothing to show. No network, no question. No question, no answer. The spinner turns forever. In a hall packed with thousands of people all fighting for the same cell tower, that happens most Saturday afternoons. A rep cannot tell a buyer "hold that thought, my ERP is loading." (ERP = enterprise resource planning: the system of record for the business.) So we build an app that works with no network at all, and treat the network as a bonus.

SQLite: a whole database in one file on the device

SQLite is how. It is a real database engine (tables, rows, indexes, SQL, transactions, the exact concepts from chapters 1 and 2), except that the entire database lives in a single ordinary file on the device's own storage. No server to connect to, no port, no network hop. The app opens the file and runs SQL against it. A query typically returns in a millisecond or less, and it returns in airplane mode.

You already know how to use it, because it is SQL: SELECT * FROM skus WHERE style_id = ? means the same thing on the iPad as in Postgres. SQLite is a serious engine: it ships inside phones, browsers, and countless desktop applications, and it is one of the most widely deployed pieces of software in the world. The one thing it does not do is share. This file is this iPad's private copy.

Sync: two copies that drift

Putting a copy on the device creates the real problem of this chapter. There are now two copies of the same information, and they start identical, then drift. The server drifts because a warehouse ships a carton. The device drifts because a rep writes an order into local SQLite while offline.

Sync is the engineering job of reconciling those drifting copies so they tell the same story again. Keep its two halves mentally separate: download (server changes flow onto the device) and upload (device changes flow back). They are different problems with different answers, and much of this design works because we refuse to treat them as symmetric.

A conflict: two checks, one account, no phone

Picture forty units of one jacket. Rep A, offline at the north end of the hall, sells all forty to a Denver boutique. Rep B, offline at the south end, sells all forty to an Austin chain. Neither did anything wrong. Each looked at their own copy, saw forty, and wrote a valid order.

This is two people writing checks against one bank account with no way to phone each other. Each check is fine alone. Together they are impossible. That is a conflict: two changes that are individually valid but cannot both be true. Note the cruel timing. The conflict is created at 11:02 by two confident people, and not discovered until both iPads find a signal hours later. This whole chapter is about making that discovery fast, loud, and actionable by a human.

Why you cannot just use the clock

The obvious fix is to compare timestamps and let the earlier order win. It does not work. Every device carries its own clock, and clocks lie: they drift by minutes over weeks, time zones shift when a rep flies to Las Vegas, people set the date by hand, a dead battery resets it to 1970. There is no shared "now" across a fleet of iPads, only independent guesses.

If a device timestamp decided who got the inventory, a wrong clock could jump the queue by accident and a dishonest one could jump it on purpose. So the rule for the rest of the chapter is this: use device clocks for display only. Ordering decisions use the one clock we control: the server's, read at the moment the change lands.

Server-authoritative: one referee

Server-authoritative is the name for that arrangement: one designated computer is the referee and its verdict is final. The device may propose ("I would like to commit two hundred units to Fjällräv Trading"), but only the server decides, and its decision is pushed back down to every device, overwriting whatever they had guessed.

The alternative is peer-to-peer merging, where devices negotiate among themselves with nobody in charge. Fine for a shared design brief. Bad for inventory, because inventory has a hard rule (you cannot sell what you do not have) and hard rules need a referee. So the SQLite copy is two things glued together: a fast, disposable cache of what the server last said, plus an outbox of proposals awaiting judgment. The rest of the chapter is the details of making that true.

Local-first: the transport is the easy half

Local-first is the name for the discipline we just described. It was codified in a 2019 essay by Martin Kleppmann, Adam Wiggins, Peter van Hardenberg, and Mark McGranaghan at the research lab Ink & Switch, which laid out seven ideals for software built this way.

The idea is that the copy of the data on the user's device is a first-class replica: reads and writes hit local storage with zero latency, and the network only makes things faster. ("Replica" means a full working copy, complete enough to run the app on its own.)

For your trade show app this matters commercially. A rep standing in a concrete convention hall, sharing one overloaded cell tower with the whole show, will be offline at the exact moment a buyer says "I'll take two hundred units." If the app cannot capture that order instantly and locally, you lost the sale to a paper linesheet. (A linesheet is the printed style-and-price list reps sell from, the paper thing your app is replacing.)

Where sync engines stop

The trap is that modern sync engines make the transport so smooth that teams ship them as if they were the whole answer. PowerSync will faithfully replicate a Postgres subset into SQLite and faithfully queue local writes back. It will also faithfully replicate a disaster. If two iPads both edit the same sku_inventory row offline, the engine will deliver both writes — in some order — and your database will end the day confidently wrong. The engine moved the bytes correctly. Nobody designed what the bytes mean under concurrency.

Two words carry the weight of that distinction, and they need precise definitions. Convergence means every copy of the data eventually agrees on the same values. The drift closes. The related term eventual consistency is the guarantee that if everyone stops writing for a moment, all copies will finish agreeing. It says nothing about when, and nothing about whether the value they agree on is any good. That second question is validity: whether the agreed-upon state obeys your business rules. A system can converge beautifully on a number that would bankrupt you.

So the discipline splits into four design decisions, and the rest of the chapter takes them in order:

1. which subset of tenant data lives on each device,
2. what write semantics the client is allowed,
3. how the server commits those writes into shared truth,
4. and how all of this is made visible and testable.

PowerSync on Supabase Postgres: the shape of the system

Start with the shape. PowerSync sits beside your database. It is a separate service that watches Postgres from the side, and your ordinary web requests never travel through it.

It watches through logical replication. Postgres writes every change to a write-ahead log (the WAL) before it touches the table files. The WAL is an append-only diary of "row 4 in skus changed color to navy," written first so a crash can be replayed. Logical replication lets an outside program read that diary as a live stream of row changes rather than raw disk blocks.

The reader registers a replication slot, Postgres's bookmark for it: the slot remembers how far this reader has got and refuses to delete entries it has not seen. That is what makes the stream survive a service restart, and why an abandoned slot eventually fills your disk.

The PowerSync Service connects to Supabase Postgres as a logical-replication client: it consumes the write-ahead log through a dedicated publication, evaluates your sync configuration against every change, and maintains pre-computed buckets of row data. A bucket is a named pile of rows that belong together for syncing purposes: "everything tenant 42's reps need," "rep Dana's own orders." The service keeps each pile up to date as changes stream in, so that when a device connects, there is nothing to compute: it just ships the piles that device is entitled to.

How a device proves which buckets it may have

Clients hold a SQLite database, connect to the service over a streaming connection authenticated with the Supabase JWT, and receive exactly the buckets their token entitles them to. A JWT (JSON Web Token, pronounced "jot") is a small signed blob of facts about the logged-in user (user id, tenant id, expiry) signed by the auth server so that anyone holding the matching key can verify it was not tampered with.

JWT auth for sync means the device presents that token when it opens the sync connection, and the service reads the tenant and user claims straight out of the verified token to decide which buckets to send. The client cannot lie about who it is, because it cannot forge the signature.

The upload queue: engine downloads, application uploads

The write path is entirely separate: local mutations go into a durable queue inside SQLite, and the SDK hands them to your code to deliver to your backend, in our case a Supabase RPC (remote procedure call: a function that lives in the database and that the client invokes by name).

That queue is the upload queue: an ordered, on-disk list of every local change that has not yet been accepted by the server. Durable means it is written to the same SQLite file as your data, so it survives the app being killed, the iPad rebooting, or the battery dying mid-show.

PowerSync never writes to your Postgres. That asymmetry, engine-managed downloads and application-managed uploads, is precisely why it fits an ERP: the server-side write path stays fully under your control.

Supabase setup: publication, role, JWT trust

Supabase setup is three moves:

• a publication scoped to the tables you actually sync,
• a replication role for the service,
• and telling PowerSync to trust Supabase's JWT signing keys (in the dashboard: enable "Use Supabase Auth", which verifies tokens against your project's JWKS, the JSON Web Key Set, which is the public list of keys your auth server publishes so others can check its signatures).

Note BYPASSRLS: replication reads raw tables, so row-level security (RLS, the per-row access policies from chapter 5) does not filter what PowerSync can see. Your sync configuration is the read-side access control, and it must be written with the same care as an RLS policy.

-- In the Supabase SQL editor. Scope the publication: replicating
-- tables you never sync (audit logs, invoices) is pure overhead.
create publication powersync for table
  public.styles, public.skus, public.customers,
  public.price_list_entries, public.ats_snapshots,
  public.orders, public.order_lines;

-- Role the PowerSync Service connects as. BYPASSRLS is required:
-- logical replication does not run through row-level security.
create role powersync_role with replication bypassrls login
  password 'generate-a-real-one';
grant select on all tables in schema public to powersync_role;

Reading the publication and the role

Two statements, and both are security decisions dressed up as configuration.

A publication is Postgres saying "these are the tables whose changes I am willing to broadcast." This one names seven tables and only seven. Audit logs, invoices, payments, the whole back office: not on the wire.

PowerSync's own Supabase guide starts you off with create publication powersync for all tables, which is quicker to set up. Narrowing it is a deliberate tightening, and the cost of not doing it is real. Every published table means every change to it gets written into the replication stream and evaluated against your sync configuration, and, if any bucket matches, stored in that bucket, whether or not a device ever asks for it.

The second statement creates the login the service connects as. with replication grants the right to open a replication stream and read the WAL. bypassrls grants the right to see rows regardless of row-level security, and it is not optional, because the WAL is raw row changes that never passed a policy check.

Read the consequence slowly: all your careful RLS work from chapter 5 does not protect this path. Sync a table with a badly scoped bucket and tenant A's rows land on tenant B's iPad with no policy to stop them. The sync configuration below is your read-side access control now. Treat every line of it with the suspicion you would give an RLS policy.

The final grant select lets the role read tables normally, which the service uses for the initial snapshot: the first full copy, taken before the change stream takes over.

Sync rules: deciding what lives on each iPad

The first real design decision is scope. The instinct of a developer raised on REST (representational state transfer, the ordinary "call an endpoint, get JSON" style of API) is "sync whatever the screens need." The instinct of a first-time local-first developer is "sync everything, SQLite is fast." Both are wrong for an ERP. The device dataset is a capability statement: this iPad can do everything required to write a wholesale order with zero connectivity, and nothing more.

• Yes: the style/SKU catalog (a season runs anywhere from a few hundred to a few thousand SKUs depending on the brand, trivial for SQLite at either end of that range), the tenant's customers, price lists, and an advisory ATS snapshot per SKU. (ATS = available-to-sell, the number from chapter 3: on-hand minus what is already committed. "Advisory" means the device treats it as a hint, not a promise.)
• No: full order history, invoices, payments, other reps' orders, anything back-office. A tenant with five years of transactions would push gigabytes to a device that needs none of it to take an order. Every synced row is also a liability on a device that can be left in a taxi.
• Scoped: the rep's own recent orders, so they can see and reference what they wrote this week.

Bucket definitions, parameter queries, data queries

In PowerSync this is expressed as sync rules: a configuration file, written in YAML (YAML Ain't Markup Language, an indentation-based format for config, easier on the eye than JSON), that you deploy to the service. It is the single place that answers "which rows go to which device," and the service evaluates it against every change flowing off the write-ahead log.

The file contains bucket_definitions. Each definition has an optional parameter query, a query run against the connecting client's JWT that decides which bucket instances that client gets, plus one or more data queries, run against replicated rows to decide which bucket instances each row lands in. The parameter query answers "who are you, and therefore which piles are yours?" The data queries answer "given a row, which piles does it belong in?"

Parameterization is how multi-tenancy happens: one installation serving many separate customer companies, each seeing only its own data. One YAML file, one bucket definition, thousands of per-tenant bucket instances.

# sync-rules.yaml — deployed to the PowerSync instance
bucket_definitions:
  # Everything a rep needs to WRITE an order offline: catalog,
  # customers, price lists, advisory ATS. One instance per tenant.
  tenant_showfloor:
    # `org_id` is a custom claim we add to the Supabase JWT with a
    # Custom Access Token hook. request.jwt() is the token payload.
    parameters: SELECT request.jwt() ->> 'org_id' AS tenant_id
    data:
      - SELECT * FROM styles
          WHERE tenant_id = bucket.tenant_id AND active = true
      - SELECT * FROM skus
          WHERE tenant_id = bucket.tenant_id AND active = true
      - SELECT * FROM customers WHERE tenant_id = bucket.tenant_id
      - SELECT * FROM price_list_entries
          WHERE tenant_id = bucket.tenant_id
      - SELECT id, tenant_id, sku_id, ats, as_of
          FROM ats_snapshots WHERE tenant_id = bucket.tenant_id

  # A rep syncs their OWN recent orders — never the tenant's history.
  rep_orders:
    parameters: SELECT request.user_id() AS rep_id
    data:
      - SELECT * FROM orders
          WHERE sales_rep_id = bucket.rep_id AND archived = false
      # No joins in legacy data queries: sales_rep_id is denormalized
      # onto order_lines specifically so this bucket can exist.
      - SELECT * FROM order_lines
          WHERE sales_rep_id = bucket.rep_id AND archived = false

What lands on Dana's iPad

Read that file as an answer to one question: when rep Dana, who works for tenant 42, opens the app in Las Vegas, exactly which rows land in the SQLite file on her iPad? bucket_definitions: opens the list, and there are two definitions here, each becoming many instances at runtime.

tenant_showfloor: is the first definition, the shared working set for a whole tenant. Its parameters line runs SELECT request.jwt() ->> 'org_id' AS tenant_id. request.jwt() hands you the verified contents of Dana's login token. ->> is Postgres's "pull this key out as text" operator.

So this line reads Dana's org_id claim (say 42) and produces a single row, tenant_id = 42. That one row selects one bucket instance: tenant_showfloor[42]. A rep from tenant 99 running the identical file gets tenant_showfloor[99]. One definition, one file, thousands of tenants, no per-tenant configuration. (That org_id claim is not there by default. You add it with Supabase's Custom Access Token hook, a database function that runs just before a token is issued and can put extra fields into it.)

Underneath it, data: lists five queries, and each says "a row of this table belongs in the bucket whose tenant_id matches the row's own tenant_id." Concretely, on Dana's iPad: every active style for tenant 42, every active SKU for tenant 42, all of tenant 42's customers, all of its price-list entries, and one ATS snapshot row per SKU.

That is the full kit for writing an order at a booth: she can browse the line, pick a customer, see the right contract price for that customer's price list, and see roughly what is available. Note active = true on styles and SKUs: discontinued product from three seasons ago is dead weight and a source of ordering mistakes, so it never leaves the server.

The fifth query is different in a way worth copying. SELECT id, tenant_id, sku_id, ats, as_of FROM ats_snapshots names its columns instead of using *. The server's snapshot table may carry cost, reserved quantities, warehouse breakdowns, or supplier notes. None of that is needed to take an order, and none of it belongs on a device the company does not physically control. Sync less than the server knows whenever the device does not need it.

Scoping a bucket to one rep

rep_orders: is the second definition, and it is scoped to a person rather than a company. Its parameter query is SELECT request.user_id() AS rep_id, which is Dana's own user id from the verified token (PowerSync reads it from the token's sub, or subject, claim), and it produces the bucket instance rep_orders[dana].

Its two data queries put an order (and an order line) into the bucket belonging to the rep who wrote it, as long as archived = false. So Dana's iPad receives Dana's recent orders and nothing else: not the tenant's five-year history, not the orders her colleague Marco wrote at the next booth. If Marco's iPad is stolen, the thief gets Marco's week, not the company's decade.

Four reasons not to sync everything

Which is the answer to "why not just sync everything." Four reasons, and only the first is about speed:

• Volume: years of transactions is gigabytes onto a device that needs none of it to do its job.
• Time: the first sync after install happens on convention-hall Wi-Fi, and a rep will not wait out a gigabyte.
• Exposure: every synced row is a row that walks out of the building in someone's bag.
• And churn: a busy tenant's order history changes constantly, so a device subscribed to all of it would burn battery downloading updates to rows nobody will ever open.

Three details in that file carry more weight than they look like they do. First, request.jwt() and request.user_id() come from the verified token. This is your access control. (PowerSync also supports client-supplied parameters via request.parameters(), but a client can send anything there, so they are for conveniences like pagination, never for tenancy.)

Second, data queries in this legacy dialect are single-table with a restricted operator set and must be deterministic: no joins, and no functions whose answer changes over time, such as now(). That is why order_lines carries a denormalized sales_rep_id, and why "recent" is implemented as an archived flag flipped by a nightly server job rather than a date comparison the sync layer cannot evaluate.

(Denormalized means the same fact is stored in two places on purpose: the rep id lives on the order and on each line, so the line can be scoped without a join.) You will bend your schema to your sync scope. Better to do it knowingly.

Third, the ATS query names its five columns instead of taking *, so the cost, reserve and warehouse fields the server keeps on that table never leave it.

Sync Streams, the recommended replacement

As of July 2026 this system is mid-evolution. PowerSync's documentation now marks sync rules as legacy ("supported for existing projects but considered legacy") and presents Sync Streams as the recommended way to define what syncs. Same bucket machinery underneath, but stream queries support subqueries, INNER JOIN, and common table expressions, and clients can subscribe on demand with parameters and a TTL (time to live: how long the cached data sticks around before it is dropped) instead of receiving everything at connect time.

Streams solve the one awkward gap in our scope decision. A buyer at the booth asks "what did we order last season?" That is history we deliberately keep off the device. With a stream, the rep pulls that customer's history while online, it stays cached for the TTL, and it evaporates instead of accreting.

config:
  edition: 3        # the Sync Streams dialect

streams:
  # Auto-subscribed: the offline working set, same scope as before.
  showfloor:
    auto_subscribe: true
    queries:
      - SELECT * FROM styles
          WHERE tenant_id = auth.parameter('org_id') AND active = true
      - SELECT * FROM customers
          WHERE tenant_id = auth.parameter('org_id')

  # On-demand, parameterized per subscription: one customer's history,
  # fetched at the booth while connected, cached for an hour.
  customer_order_history:
    accept_potentially_dangerous_queries: true
    query: |
      SELECT * FROM orders
      WHERE customer_id = subscription.parameter('customer_id')
        AND tenant_id = auth.parameter('org_id')

Same job, newer dialect, one new capability. config: edition: 3 at the top tells the service to read this file as Sync Streams rather than legacy sync rules. That single line switches the whole grammar.

The showfloor stream is the working set you already understand, rewritten. auto_subscribe: true means every client gets it the moment it connects, without asking, exactly like a bucket definition. (Without that line a stream ships nothing until the client explicitly subscribes. The default is false.)

A stream can carry one query or a list under queries, and the list is the cheaper option here: the client manages a single subscription and PowerSync merges the results. The tenant filter now sits inline as auth.parameter('org_id'), right in the WHERE clause where you can see it. That function reads a claim out of the verified JWT. It is the streams-dialect replacement for the older request.jwt() ->> 'org_id', and auth.user_id() replaces request.user_id().

One customer's history, fetched on demand

customer_order_history is the new trick, and the reason to care about streams at all. It has no auto_subscribe, so it ships nothing by default. Its filter reads subscription.parameter('customer_id'), a value the client supplies at subscribe time. Dana taps a customer card, the app subscribes with that customer's id, and only that customer's orders come down.

Notice the second condition is still auth.parameter('org_id'), from the verified token. That pairing is the whole security model of on-demand streams: the client picks which customer, the token picks which tenant, and a malicious client passing someone else's customer id gets nothing, because the tenant clause it cannot control filters the row out.

PowerSync raises a security warning on any query fed by a client-controlled parameter. Writing accept_potentially_dangerous_queries: true is you signing off that you have checked this one, and you should only write that line after you have.

// Client side: subscribe when the rep opens the customer card.
const history = await db
  .syncStream('customer_order_history', { customer_id: customer.id })
  .subscribe({ ttl: 3600 });        // seconds; default is 24 hours

await history.waitForFirstSync();   // resolves when data is queryable

// ...and when the rep closes the card again:
history.unsubscribe();

Three statements, and they are the client half of the stream above. db.syncStream(...) names the stream from the YAML and fills in its one parameter with the customer whose card the rep just opened. .subscribe({ ttl: 3600 }) asks for the data and sets a time to live of 3,600 seconds.

The TTL is the grace period after you unsubscribe: the rows stay in local SQLite for an hour in case the rep reopens the same card, then they are dropped. (Leave it out and you get PowerSync's default of 24 hours.) That expiry is the point: the device gets history when the conversation needs it, then forgets it, so the working set never quietly grows into the gigabytes we refused to sync.

await history.waitForFirstSync() pauses until the rows have actually arrived and are queryable. Only then do you run your local SELECT and render. Without it you would query an empty table and render "no previous orders" a heartbeat before the real answer landed. That is the kind of small lie that destroys a rep's trust in the app. With no signal the subscribe simply never completes, so this is a feature you offer when connected and design the screen to live without.

Conflict semantics are a design decision

Now the heart of the chapter. When two offline replicas write and later converge, someone decides the outcome. If you don't decide, your tools decide for you, and the default nearly everywhere is last-write-wins (LWW): whichever write arrives last (or carries the latest timestamp) silently overwrites the other, and the loser is simply gone with no error and no record. Whether that is fine or catastrophic depends entirely on what the field means.

One row in the table below mentions CRDTs, which we should define before you meet them in a table cell. A CRDT is a conflict-free replicated data type: a cleverly designed data structure whose merge rule is built into the type itself, so any two copies can be combined in any order and always land on the same answer.

A "grow-only set" is the simplest example: merging is just union, so order cannot matter. Counters and collaborative text editors work the same way. CRDTs are genuinely brilliant, and they guarantee convergence. What they cannot do is enforce a rule that spans replicas, like "this number must never go below zero," because each replica merges without ever consulting the others.

Read row two again, because it is the trap that catches real teams. Per-field last-write-wins on inventory does not produce an error. Both iPads compute 100 and both write 100, so the database ends up holding a number that looks completely plausible and is completely wrong. Nothing is flagged. Nobody is paged. You find out in six weeks when the warehouse cannot ship.

The last row is the ERP pattern, and it rests on one distinction: documents versus shared truth. An order is a document, a statement of fact about the world: "Fjällräv Trading committed to 200 units of SS26-DEN-JKT at $48." Two reps creating two orders produces two facts, and both must survive. ATS and inventory are shared truth: derived, contended state that many parties read and whose invariants (never oversold beyond policy, never negative) are the business. From that distinction, the write rules follow:

Staging and the review queue

Two terms in that callout deserve their own definitions, because the whole design hangs on them. Staging means accepting an incoming change into a holding area rather than applying it straight to shared truth. The order is recorded, permanently and safely, but its effect on inventory is not yet final. A review queue is the human-facing half of that: a list of staged items waiting for a person to decide.

Every parked order line lands in a queue that a sales-operations coordinator works through, the same way a warehouse works through exceptions. A conflict between two humans about physical goods is a negotiation, and no automatic rule produces the right answer to a negotiation. Software's job is to surface it fast enough that the negotiation is still possible.

Two reps, three hundred units

Walk the failure scenario properly, because it is the one this design is for. Magic Las Vegas, Saturday. ATS for style SS26-DEN-JKT, size M: 300 units, synced to every iPad that morning. Rep A sells 200 to a Denver boutique chain at 11:02. Rep B sells 200 to an Austin chain at 11:15. Both offline.

Under any mutate-inventory scheme (LWW, CRDT decrement, it doesn't matter) the replicas converge to a number that hides the problem, invoicing later discovers 400 commitments against 300 units, and two customers get surprise short-ships (fewer units delivered than ordered) at the worst possible time.

Under create-only: both orders upload as documents. The server allocates in arrival order inside a locked transaction. The first upload allocates 200 of 300. The second finds 100 available, records the order anyway with its lines parked in review status, and opens a ticket. Sales ops sees it within minutes, while the buyer is still at the show and options still exist: split the sizes, substitute the black colorway (the same style in a different color), or air-freight the second production run.

From document to inventory commitment

One function is the commitment point. The connector is the small piece of client code that stands between the sync engine and your backend: the SDK hands it queued local changes and it decides how to deliver them. It sends each order as one RPC call, and this function is the only code path that turns a rep's document into an inventory commitment. It is written in PL/pgSQL, Postgres's built-in procedural language, and takes the order as jsonb, Postgres's binary JSON type:

-- The single authoritative write path from synced orders to inventory.
create or replace function public.submit_order(p_order jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order_id uuid := (p_order->>'id')::uuid;   -- generated on the iPad
  v_tenant   uuid := (p_order->>'tenant_id')::uuid;
  v_line     jsonb;
  v_avail    int;
  v_qty      int;
  v_sku      uuid;
  v_status   text := 'confirmed';
begin
  -- The caller can only submit as themselves.
  if (p_order->>'sales_rep_id')::uuid is distinct from auth.uid() then
    raise exception 'sales_rep_id does not match authenticated user';
  end if;

  -- Idempotency: uploads are retried after crashes and timeouts. The
  -- client-generated UUID makes every retry a no-op.
  if exists (select 1 from orders where id = v_order_id) then
    return jsonb_build_object('id', v_order_id, 'status', 'duplicate');
  end if;

  insert into orders (id, tenant_id, customer_id, sales_rep_id,
                      status, placed_at)
  values (v_order_id, v_tenant, (p_order->>'customer_id')::uuid,
          auth.uid(), 'received', now());   -- server clock, not device

  for v_line in select * from jsonb_array_elements(p_order->'lines') loop
    v_sku := (v_line->>'sku_id')::uuid;
    v_qty := (v_line->>'qty')::int;

    -- FOR UPDATE serializes allocation per SKU: concurrent uploads from
    -- two reps are forced into a total order right here.
    select on_hand - allocated into v_avail
      from sku_inventory
     where tenant_id = v_tenant and sku_id = v_sku
     for update;

    if v_avail >= v_qty then
      update sku_inventory set allocated = allocated + v_qty
       where tenant_id = v_tenant and sku_id = v_sku;
      insert into order_lines (id, order_id, sales_rep_id, sku_id, qty,
                               unit_price, line_status)
      values ((v_line->>'id')::uuid, v_order_id, auth.uid(), v_sku, v_qty,
              (v_line->>'unit_price')::numeric, 'allocated');
    else
      -- Never invent inventory; never reject the document. Park it.
      insert into order_lines (id, order_id, sales_rep_id, sku_id, qty,
                               unit_price, line_status)
      values ((v_line->>'id')::uuid, v_order_id, auth.uid(), v_sku, v_qty,
              (v_line->>'unit_price')::numeric, 'review');
      insert into oversell_review (tenant_id, order_id, sku_id,
                                   requested, available)
      values (v_tenant, v_order_id, v_sku, v_qty, greatest(v_avail, 0));
      v_status := 'review';
    end if;
  end loop;

  update orders set status = v_status where id = v_order_id;
  return jsonb_build_object('id', v_order_id, 'status', v_status);
end;
$$;

Reading submit_order clause by clause

This is the referee. Everything else in the chapter is plumbing that leads here. Take it in order, then run both reps through it.

The signature. One argument, p_order jsonb: the whole order, header and lines, as a single JSON value. One document, one call. security definer runs the function with its creator's privileges rather than its caller's, which is how a rep with almost no table permissions can still move inventory: only through this door, on these terms. set search_path = public pins where the unqualified table names resolve, so nobody can point the function at a lookalike table.

The identity check. Every field in the payload came from a device, so treat all of it as a claim awaiting proof. auth.uid() is the user id from the verified token. If the body claims the order belongs to someone else, the function refuses. Same instinct as the sync rules: trust the token, never the body.

The idempotency check. An idempotent upload is one you can send twice with no extra effect the second time. That is chapter 4's property, applied to a network that will absolutely make you send things twice. The iPad minted the order's UUID (universally unique identifier: a 128-bit random id, unique with no coordination) before it ever saw a network, so it is a stable name for this order forever. If a row with that id already exists, the function returns duplicate and stops. Without that check, one flaky connection double-commits real goods.

The header insert. Status goes in as 'received' (the server's word, not the device's) and placed_at = now() uses the server's clock at commit. That is the clock rule from the opening section, enforced in exactly one place.

The row lock that serializes allocation

The allocation loop. The for loop walks each line, reading availability with select on_hand - allocated ... for update. (on_hand is the physical stock, allocated is the part already promised to other orders, and the difference is the ATS number.)

That for update is the most important clause in the function. It takes a row lock (chapter 2) on the SKU's inventory row and holds it to commit. While Rep A's transaction holds it, Rep B's identical query waits. It does not read a stale number and does not proceed in parallel. That is what turns "two people writing checks with no way to phone each other" into "two people queued at one teller window."

The two branches. If v_avail >= v_qty, increment allocated and write the line as 'allocated'. Otherwise, and this is the design decision the whole chapter rests on, the function neither rejects the order nor pretends the stock exists. It writes the line with line_status = 'review', records requested-versus-available in oversell_review, and flips the order to 'review'. The document survives, the inventory stays honest, and a human gets a ticket.

greatest(v_avail, 0) is a small kindness: a negative availability reports as zero rather than a confusing minus figure. It also covers the case where the SKU has no inventory row at all, which leaves v_avail null and sends the line down the same review path.

The finish. update orders set status = v_status stamps the order confirmed or review, and the verdict comes back as JSON.

Both reps through the referee

Run Saturday through it. Both iPads hold an order for 200 units against a synced ATS of 300. Rep B passes a working access point first, so B's upload arrives first, even though A's device clock reads thirteen minutes earlier. B takes the row lock, sees 300, allocates 200, commits confirmed.

A's upload waits for the lock, then reads the post-commit number: 100. Too few, so A's order is recorded in full, its line marked review, and a ticket opened saying "requested 200, available 100." Inventory reads 200 allocated against 300 on hand, which is still true. Two orders exist, so no fact is lost. One ticket exists, so the problem is visible, with a name on it, minutes after it happened.

Notice what flows back down through sync: the server rewrote orders.status from the client's value to confirmed or review, and ats_snapshots (refreshed from sku_inventory by a trigger or short-interval job) drops on every device in the tenant. Rep B's iPad learns about Rep A's sale because shared truth has exactly one home and every device syncs from it, with no peer-to-peer cleverness anywhere in the path.

Modeling the client SQLite database

The client schema is a deliberate subset mirror of the server schema, plus local-only working state. Schema mirroring means the tables on the device are declared to look like the server's tables (same names, same columns, same meanings) so the SQL you write locally reads like the SQL you write against Postgres. "Subset" is the load-bearing word: the device declares only the tables it syncs, and often only some of their columns.

PowerSync's client SDKs define that schema in code. Under the hood the service syncs schemaless row data and the SDK projects your declared schema over it as SQLite views, adding a text id column to every table automatically (which is why every server table you sync needs a stable id: your UUID primary keys). Columns are one of text, integer, or real. Timestamps travel as ISO-8601 text (the 2026-07-25T11:02:00Z format, sortable as plain text, which is why it is worth insisting on).

import { column, Schema, Table } from '@powersync/web';

// ---- Synced: read-mostly reference data (server-owned) ----
const styles = new Table({
  tenant_id: column.text,
  style_number: column.text,       // "SS26-DEN-JKT"
  name: column.text,
  category: column.text,
  active: column.integer
}, { indexes: { number: ['style_number'] } });

const skus = new Table({
  tenant_id: column.text,
  style_id: column.text,
  color: column.text,
  size: column.text,
  upc: column.text,
  active: column.integer
}, { indexes: { style: ['style_id'] } });

const customers = new Table({
  tenant_id: column.text,
  name: column.text,
  price_list_id: column.text,
  terms: column.text
});

const price_list_entries = new Table({
  tenant_id: column.text,
  price_list_id: column.text,
  style_id: column.text,
  currency: column.text,
  unit_price: column.real
}, { indexes: { lookup: ['price_list_id', 'style_id'] } });

// Advisory only. The UI says "as of", never "guaranteed".
const ats_snapshots = new Table({
  tenant_id: column.text,
  sku_id: column.text,
  ats: column.integer,
  as_of: column.text
}, { indexes: { sku: ['sku_id'] } });

// ---- Synced: documents this device creates ----
const orders = new Table({
  tenant_id: column.text,
  customer_id: column.text,
  sales_rep_id: column.text,
  status: column.text,             // client writes 'pending_sync' once;
  placed_at: column.text,          // the server owns it afterwards
  notes: column.text
}, { indexes: { rep: ['sales_rep_id'] } });

const order_lines = new Table({
  order_id: column.text,
  sales_rep_id: column.text,       // denormalized for bucket scoping
  sku_id: column.text,
  qty: column.integer,
  unit_price: column.real,
  line_status: column.text
}, { indexes: { order: ['order_id'] } });

// ---- Local-only: never uploaded, never synced, survives restarts ----
const draft_orders = new Table({
  customer_id: column.text,
  cart_json: column.text,          // in-progress line items
  updated_at: column.text
}, { localOnly: true });

export const AppSchema = new Schema({
  styles, skus, customers, price_list_entries,
  ats_snapshots, orders, order_lines, draft_orders
});

Three tiers in one schema file

This file is the iPad's whole world, and it comes in three deliberate tiers marked by the comment banners.

Tier one: synced reference data. styles, skus, customers, price_list_entries, and ats_snapshots are server-owned. The device reads them and never writes them. Each new Table({...}) lists columns and storage types, and the second argument declares indexes, chapter 1's idea for chapter 1's reason: { style: ['style_id'] } makes "every size and color of this jacket" instant rather than a scan, which matters when a buyer is watching the rep scroll. Note active: column.integer: SQLite has no boolean type, so true and false travel as 1 and 0.

The as_of column on ats_snapshots is interface design hiding in a schema. It lets the screen say "about 300 available as of 07:40" instead of "300 available." One is honest about a number that was true at last sync. The other is a promise the device has no authority to make.

Tier two: synced documents. orders and order_lines are the tables this device creates rows in. The comment on status states the ownership rule exactly: the client writes 'pending_sync' once, at creation, and after that the field belongs to the server. order_lines carries a sales_rep_id that duplicates the one on the header. That is the denormalization the sync rules needed, now visible on the client side too.

Tier three: local-only. draft_orders is declared with { localOnly: true }. A local-only table is a real SQLite table with one difference: the sync engine ignores it completely. Nothing in it is ever uploaded, nothing about it is ever compared to the server, and it is not part of any bucket. It still lives in the same durable file, so it survives the app being force-quit or the iPad rebooting. It is private, permanent scratch space.

Finally, new Schema({...}) collects all eight tables into the object the SDK uses to build the local views. If a table is not in this list, your app cannot query it, no matter what the sync rules send.

Why drafts stay local

The localOnly drafts table is doing quiet but important work. A rep builds an order over twenty minutes of booth conversation, with quantities changing and lines added and removed. If drafts lived in the synced orders table, every keystroke would enqueue an upload, your server would receive forty versions of a half-decided order, and "create, don't mutate" would degenerate into "mutate constantly."

Instead the cart churns locally, and placing the order is one atomic promotion into the synced tables. Doing it in a single writeTransaction matters for a second reason: PowerSync preserves transaction boundaries into the upload queue, so the order header and its lines arrive in uploadData as one unit you can submit atomically.

export async function placeOrder(
  draft: Draft, repId: string, tenantId: string
) {
  const orderId = crypto.randomUUID();     // idempotency key, forever
  const cart = JSON.parse(draft.cart_json) as Cart;

  await db.writeTransaction(async (tx) => {
    await tx.execute(
      `INSERT INTO orders (id, tenant_id, customer_id,
                           sales_rep_id, status, placed_at)
       VALUES (?, ?, ?, ?, 'pending_sync', ?)`,
      [orderId, tenantId, draft.customer_id, repId,
       new Date().toISOString()]
    );
    for (const line of cart.lines) {
      await tx.execute(
        `INSERT INTO order_lines (id, order_id, sales_rep_id, sku_id,
                                  qty, unit_price, line_status)
         VALUES (?, ?, ?, ?, ?, ?, 'pending_sync')`,
        [crypto.randomUUID(), orderId, repId,
         line.skuId, line.qty, line.unitPrice]
      );
    }
    await tx.execute(`DELETE FROM draft_orders WHERE id = ?`, [draft.id]);
  });
  return orderId;
}

This function runs the instant the rep taps "Place order." It touches no network at all and returns in a millisecond or two, signal or no signal.

crypto.randomUUID() mints the order's identity on the device, before anything else happens. That is the id the server will use for its duplicate check, so it must be generated exactly once and never regenerated on retry, hence "idempotency key, forever."

Then db.writeTransaction wraps everything that follows in one SQLite transaction, chapter 2's all-or-nothing guarantee running on an iPad. Three things happen inside it:

• the header is inserted with status = 'pending_sync' (the client's one and only claim on that column),
• each cart line is inserted with its own fresh UUID pointing at the same orderId,
• and the draft is deleted now that it has been promoted into real documents.

The ? placeholders are parameterized SQL, exactly as in Postgres: the values travel separately from the query text, so a customer name containing a quote mark can never change what the statement does.

The transaction boundary is doing double duty, and the second duty is easy to miss. Locally it guarantees you can never end up with a header and no lines if the app is killed halfway.

Remotely it groups those writes in the upload queue: PowerSync records that they belonged to one transaction, so when the connector drains the queue the header and its lines are handed over together. One local transaction becomes one upload becomes one server transaction, which is what makes the allocation atomic across every line. If the rep places the order at 11:02 and the iPad finds signal at 13:52, nothing here changes. The queue simply waits.

The oplog and the checkpoint

The path back from the server starts with the device's own write queue. Every local write is applied to SQLite and appended to an internal queue (a table called ps_crud, which PowerSync's own docs describe as a blocking first-in-first-out queue) in the same transaction, so the queue can never diverge from local state.

That internal queue is an oplog, an operation log: an ordered record of the operations themselves ("insert this row," "patch this column") rather than a snapshot of results. Storing the operation instead of the outcome is what makes replay possible after a crash. CRUD, by the way, is just create/read/update/delete, the four shapes any row change can take.

Your optimistic UI write stays visible locally until it has been uploaded and a subsequent server checkpoint arrives. "Optimistic" means the interface shows the change immediately, assuming it will succeed, instead of graying out and waiting for confirmation. The rep sees the order in the list the moment they tap, because on a trade show floor any other behavior is unusable.

A checkpoint is the sync engine's version marker. PowerSync's docs define it as "a single point-in-time on the server ... with a consistent state: only fully committed transactions are part of the state," so a device can say "I have everything through checkpoint 8,412" and receive only what changed since. Checkpoints are applied whole, never half, which is what keeps the local database internally consistent rather than showing an order header whose lines have not arrived.

When that checkpoint lands, server state wins for synced tables. This is the mechanism that makes server-authoritative semantics feel natural in the UI: the client wrote status = 'pending_sync'. After the round trip, the row re-materializes with the server's verdict, confirmed or review, and the UI simply renders what is in SQLite.

You never write reconciliation code, because there is no negotiation: the server's value replaces yours, and your components just re-render.

One consequence to internalize now: the docs state that "while mutations are present in the upload queue, the client does not advance to a new checkpoint." That is deliberate: it preserves causal consistency, meaning you can never be shown a version of the world in which your own confirmed write has not happened. It also means a permanently failing upload loses one write and freezes the device's whole view of the world along with it.

The upload queue: uploadData is your write API

PowerSync's client connects through a backend connector with two methods: fetchCredentials() (hand the SDK a JWT and endpoint) and uploadData() (drain the queue). uploadData is the function the SDK calls to say "here is the next batch of local changes. You deliver them, and tell me if it worked." It is your write API in the most literal sense: no local change reaches your server except through this function.

The SDK calls uploadData whenever there are queued entries (after a local write, on reconnect, and on a periodic check while connected), and if the function throws, it waits (five seconds by default) and calls it again, indefinitely. Inside it you read the next transaction, translate CRUD entries into calls your backend accepts, and complete() the transaction to dequeue it. This hook is where "create, don't mutate" is enforced in running code: the connector is a policy chokepoint that refuses to ship any operation outside the contract, even if a bug elsewhere in the app produced one.

import {
  AbstractPowerSyncDatabase, CrudEntry,
  PowerSyncBackendConnector, UpdateType
} from '@powersync/web';
import { supabase } from './supabase';

class PermanentRejection extends Error {}

export class ShowFloorConnector implements PowerSyncBackendConnector {
  async fetchCredentials() {
    const { data } = await supabase.auth.getSession();
    if (!data.session) return null;
    return {
      endpoint: import.meta.env.VITE_POWERSYNC_URL,
      token: data.session.access_token    // verified via Supabase JWKS
    };
  }

  async uploadData(db: AbstractPowerSyncDatabase): Promise<void> {
    const txn = await db.getNextCrudTransaction();
    if (!txn) return;
    try {
      await this.apply(txn.crud);
      await txn.complete();               // dequeue on success
    } catch (err) {
      if (err instanceof PermanentRejection) {
        // Retrying can never succeed. Complete to unblock the queue,
        // and record it where a human will see it.
        console.error('Dropped invalid upload', err, txn.crud);
        await txn.complete();
      } else {
        throw err;                        // transient: SDK will retry
      }
    }
  }

  private async apply(crud: CrudEntry[]) {
    // Case 1: a placed order — the header and its lines arrive together
    // because placeOrder() wrote them in one transaction.
    const header = crud.find(
      (e) => e.table === 'orders' && e.op === UpdateType.PUT
    );
    if (header) {
      const lines = crud
        .filter((e) => e.table === 'order_lines' && e.op === UpdateType.PUT)
        .map((e) => ({ id: e.id, ...e.opData }));
      const { error } = await supabase.rpc('submit_order', {
        p_order: { id: header.id, ...header.opData, lines }
      });
      if (error) throw classify(error);   // bad request -> permanent
      return;
    }
    // Case 2: the one mutation reps may make — editing their own notes.
    for (const e of crud) {
      const patchesNotesOnly =
        e.op === UpdateType.PATCH &&
        Object.keys(e.opData ?? {}).every((k) => k === 'notes');
      if (e.table === 'orders' && patchesNotesOnly) {
        const { error } = await supabase
          .from('orders').update({ notes: e.opData?.notes }).eq('id', e.id);
        if (error) throw classify(error);
      } else {
        throw new PermanentRejection(
          `disallowed offline op: ${e.op} ${e.table}`
        );
      }
    }
  }
}

Walking the connector

This class is the whole client-side write path. Follow it top to bottom, then walk Saturday through it end to end.

fetchCredentials() runs whenever the SDK opens or refreshes the sync connection. It asks Supabase for the current session and returns the sync endpoint plus that session's access token, the JWT. Returning null tells the SDK "nobody is logged in, do not connect." That same token is what the service verifies against Supabase's JWKS and reads org_id and the user id out of, which is how the sync rules know which buckets to send. Reads and writes authenticate with one token.

uploadData() is the drain loop. db.getNextCrudTransaction() returns the oldest un-uploaded transaction from the oplog: exactly one transaction, in the order it was written. Nothing queued, we exit. Otherwise this.apply(txn.crud) tries to deliver it, and on success txn.complete() removes it from the queue permanently.

The catch block is the most consequential ten lines in the chapter, and it turns on a single question: can time fix this? A dead network, a server-side 5xx error (the 500-range HTTP codes, meaning the server itself failed), a timeout: those are transient, so the code rethrows, and the SDK backs off and retries forever. That is correct: the order really will upload once the rep walks past a working access point.

But a malformed payload, a rejected identity, or a schema violation (the 4xx-range problems, where the request itself was wrong) will fail identically on attempt one and attempt ten thousand. Those become a PermanentRejection: the code logs loudly and calls complete() anyway, deliberately dropping the change to keep the queue moving.

PowerSync's own guidance says the same thing from the server's side: return a success code for validation errors and write conflicts, and save error responses for transient failures.

The policy chokepoint in apply()

apply() is the policy chokepoint. Case 1 looks for a PUT (an insert) into orders. If it finds one, it gathers every order_lines insert from the same transaction (they are guaranteed to be there, because placeOrder() wrote them together), assembles one JSON document, and sends it to submit_order as a single RPC.

Case 2 allows exactly one kind of mutation: a PATCH (an update to some of a row's columns) on orders that touches nothing but the notes column, checked by Object.keys(e.opData ?? {}).every((k) => k === 'notes'). Anything else (an update to inventory, a delete, a patch to somebody else's order) hits the else and throws PermanentRejection.

Read that as the "create, don't mutate" rule compiled into runtime code. A future teammate who adds an inventory write to a screen will not corrupt your database. Their write will be refused at this line, and the error message names the exact table and operation.

One order's journey, 11:02 to 13:52

Now the full Saturday, end to end. 11:02, Rep A's booth, no signal. The rep taps "Place order," and placeOrder() writes one header and one line for 200 units into SQLite in one transaction, and the same transaction appends those two operations to the oplog. The screen shows the order immediately with a Pending chip (a small status label on the row). That is optimistic UI.

The SDK calls uploadData(), which fetches the transaction and tries the RPC. The call fails with a network error. The code rethrows because that is transient, and the SDK schedules a retry. This repeats quietly for hours while the rep keeps selling, orders stacking up in the queue in the order they were written.

At 11:15, Rep B does exactly the same thing at the other end of the hall, for 200 units of the same SKU. Neither device knows the other exists.

Both uploads land, hours later

13:40, Rep B walks to the loading dock and catches a signal. The SDK calls uploadData(), getNextCrudTransaction() returns B's order, apply() assembles the document and calls submit_order. On the server, the function takes the row lock, sees 300 available, allocates 200, and returns confirmed. No error comes back, so txn.complete() dequeues it. Moments later a checkpoint arrives carrying the server's rewritten row, local SQLite is updated, and B's chip flips from Pending to Confirmed with no code of yours involved.

13:52, Rep A finds signal. Same path, but now submit_order waits for the lock, reads 100 available, records the order anyway, parks the line in review, and opens the oversell ticket. It returns success, because the upload did succeed and only the business outcome differs, so complete() dequeues normally. The next checkpoint brings back status = 'review', and A's chip reads Needs review. A sales-ops coordinator already has the ticket. The rep is still twenty feet from the buyer.

Now the ugly variant. Suppose A's connection dies after the RPC commits but before the response arrives. The await rejects with a network error, the code rethrows, nothing is dequeued, and the SDK retries the identical payload with the same order UUID, because placeOrder() minted it once and the queue kept it. The server's idempotency check finds the order already present and returns duplicate without allocating a single additional unit. That is why uploadData and submit_order have to be designed as one contract: retry-forever on the client is only safe because the server is idempotent.

Note that the wholesale-order upload goes up as one RPC call carrying the whole document. That keeps the server's allocation transaction atomic across all lines and keeps the idempotency contract simple: one document, one UUID, one commitment decision.

Sync-status UX: make the queue visible

Everything so far is invisible machinery, and invisible is the problem. A rep who taps "Place order," sees a checkmark, and later discovers the order never left the iPad will not trust the app again, and will be right not to.

Worse is the inverse: a rep who doesn't realize an order is still local might "helpfully" re-enter it on a colleague's device, and now your idempotency keys don't match and you have a genuine duplicate. (Two different UUIDs, two different documents, 400 units committed. The server cannot tell it is the same sale, because as far as the data is concerned it isn't.)

An offline-first app cannot pretend the network doesn't exist. Its job is to make the state of the network boring and legible to the person holding the iPad.

Two layers of status, from two different sources of truth. Global state comes from the SDK's SyncStatus: connected, dataFlowStatus.uploading/downloading, uploadError/downloadError, and a persisted lastSyncedAt. Derive per-document state from the data itself instead, because the server's echo is the only proof of receipt: a row still reading pending_sync has not round-tripped. confirmed or review means the server has spoken. Deriving state this way means the badge can never lie about an order that a bug quietly failed to upload.

import { useQuery, useStatus } from '@powersync/react';

export function SyncBadge() {
  const status = useStatus();
  // Reactive: re-renders when synced rows change under it.
  const { data } = useQuery<{ n: number }>(
    `SELECT COUNT(*) AS n FROM orders WHERE status = 'pending_sync'`
  );
  const pending = data?.[0]?.n ?? 0;

  if (status.dataFlowStatus?.uploadError) {
    return <Badge tone="error">Sync issue — tap for details</Badge>;
  }
  if (!status.connected) {
    return <Badge tone="offline">Offline — {pending} queued safely</Badge>;
  }
  if (pending > 0 || status.dataFlowStatus?.uploading) {
    return <Badge tone="busy">Syncing {pending} order(s)…</Badge>;
  }
  const t = fmtTime(status.lastSyncedAt);
  return <Badge tone="ok">All synced · {t}</Badge>;
}

Reading the badge component

This is a React component: a function that returns what should appear on screen and re-runs when its inputs change. It draws one small badge, from the two sources of truth just described.

useStatus() subscribes to the SDK's connection state. useQuery(...) runs a live SQL query against local SQLite and re-runs it automatically whenever the underlying rows change. So when a checkpoint flips three orders from pending_sync to confirmed, this component re-renders on its own. Nobody wires up an event.

Then four cases in priority order. An uploadError is stated plainly with a way to see detail. This is the poison-pill situation, and hiding it is how a device stays silently frozen all weekend. Not connected shows the count as queued safely. Anything pending or in flight shows progress. Otherwise, all clear with a timestamp.

Note how pending is worked out: a COUNT(*) of rows whose status the server has not yet rewritten, recomputed on every render. If a bug in your connector silently failed to upload an order, this number stays stubbornly at 1 and the badge keeps saying so, whereas a hand-maintained "isSyncing" flag would have been cleared by the same bug.

Design notes that matter more than the code:

• "Offline — queued safely" is deliberately reassuring language, because the correct rep behavior when offline is keep selling. Panic-inducing status text trains people to stop trusting the capture flow.
• Each order row in the list gets its own chip (Pending / Confirmed / Needs review) straight from status, so the review outcome from the oversell scenario surfaces here, on the device of the rep who needs to walk back to the buyer.
• And treat lastSyncedAt as display metadata, not logic: it persists across restarts, so "recently synced" on a warm start does not mean "synced during this session."

Testing offline scenarios deterministically

Offline bugs are concurrency bugs, and concurrency bugs do not reproduce on demand unless you make time and ordering explicit inputs to the test. "Deterministic" is the goal: the same test, run a thousand times, either always passes or always fails, never sometimes.

You get there by taking the two things that normally vary (the clock, and the order events arrive in) out of the environment's hands and putting them in the test's. The strategy is to test at three levels, fastest first, and to push every semantic question down to the cheapest level that can answer it.

Level 1: server semantics against real Postgres. submit_order is where validity lives, so test it against a disposable database: supabase start runs one locally, and Testcontainers (a library that starts a throwaway database in a container for the length of a test run) does the same inside CI, the continuous-integration server that runs your tests on every commit. Cover concurrent submissions from two connections, retries of the same payload, and boundary quantities. No sync machinery is involved. These tests answer one question: "is the commitment logic right?"

Level 2: sync semantics with a scripted queue. One fact makes this level deterministic: getNextCrudTransaction() is a public method on the database. You don't need to connect() to a real PowerSync service or simulate flaky Wi-Fi with network tools. An unconnected database is an offline device, its queue accumulating durably. That is the trick worth stealing: offline is simply a database you never connected.

The test owns the interleaving (the specific order in which two devices' operations land) by pumping each device's queue explicitly, in whatever order it wants to probe, against either a fake backend (a pure-TypeScript reimplementation of the allocation rules) or the Level-1 Postgres. The example below uses Vitest, a common JavaScript test runner:

import { describe, expect, it } from 'vitest';

// Pump exactly one queued transaction from one device to the backend.
async function flushOne(device: TestDevice, backend: Backend) {
  const txn = await device.db.getNextCrudTransaction();
  if (!txn) return false;
  await backend.submitOrder(toOrderPayload(txn.crud));
  await txn.complete();
  return true;
}

describe('two reps, one style, Saturday at Magic', () => {
  it('records both orders and exactly one review ticket', async () => {
    const backend = await Backend.withInventory({
      'SKU-DEN-JKT-M': 300
    });
    const repA = await TestDevice.create('rep-a');  // never connect()ed:
    const repB = await TestDevice.create('rep-b');  // offline by design

    // Device clocks skewed 40 minutes apart — deliberately.
    repA.clock.set('2026-07-25T11:02:00Z');
    repB.clock.set('2026-07-25T10:22:00Z');

    const orderA = await repA.placeOrder({
      sku: 'SKU-DEN-JKT-M', qty: 200
    });
    const orderB = await repB.placeOrder({
      sku: 'SKU-DEN-JKT-M', qty: 200
    });

    // B reaches the network first, despite A's later device clock.
    await flushOne(repB, backend);
    await flushOne(repA, backend);
    // Crash-retry: A re-uploads after completing was interrupted.
    await backend.submitOrder(await repA.lastPayload());

    // Invariants after convergence:
    const inv = await backend.inventory('SKU-DEN-JKT-M');
    expect(inv.allocated).toBeLessThanOrEqual(inv.onHand);   // no oversell
    expect(await backend.orderCount()).toBe(2);              // nothing lost
    expect(await backend.reviewTickets()).toHaveLength(1);   // one parked
    expect((await backend.order(orderA)).status).toBe('review');
    expect((await backend.order(orderB)).status).toBe('confirmed');
  });
});

Reading the test

This is Saturday at Magic Las Vegas, written down as an executable file that runs in about a second.

flushOne is the whole harness in five lines of body: pull exactly one queued transaction off one device, hand it to the backend, complete it. Because the test calls it by hand, the test decides who uploads first, not the weather and not the Wi-Fi. The setup then creates a backend holding 300 units and two devices that are never connected. There is no network mocking anywhere in this file. An unconnected PowerSync database behaves like an iPad in a dead zone because that is what it is.

The clock lines are the deliberately cruel part. A's device thinks it is 11:02 and B's thinks it is 10:22: forty minutes of drift, which is not an exotic scenario, just an iPad. Both reps then place an order for 200 units against the same 300-unit SKU, and the interleaving is scripted: flushOne(repB, ...) before flushOne(repA, ...).

B uploads first even though A's device timestamp is later, so any part of the system secretly sorting by device time would fail here. The extra backend.submitOrder(await repA.lastPayload()) replays A's exact payload, simulating a crash between the RPC committing and complete() running, the nastiest real-world case, reproduced on purpose in one line.

The five assertions are the chapter's promises, checked. allocated <= onHand: no oversell. orderCount() === 2: no document lost, even though the goods could not cover both. Exactly one review ticket: the conflict surfaced once, and the duplicate replay added nothing, which is the idempotency check doing its job. And the two status assertions nail down which order lost: A, the late arriver, despite its earlier device clock. Change nothing but the flush order and those last two expectations swap. The test pins down the design, right down to who loses.

Device time never orders anything

The clock-skew lines encode a rule worth stating in prose: device time never orders anything. iPads drift, reps cross time zones, and a wrong clock must not be able to jump the allocation queue, which is why submit_order stamps placed_at with now() at commit and keeps the client's timestamp as display metadata (captured_at) only. The test asserts arrival order decides, by making device timestamps disagree with arrival order and expecting the "earlier" order to lose.

Extend the same harness into property territory: N devices, M orders, a seeded RNG (random number generator started from a fixed seed, so the "random" choices replay identically) choosing the flush interleaving, duplicate flushes injected randomly, and the same three invariants asserted for every seed. When a seed fails, it fails forever, which is the whole point.

Level 3: the real pipeline, sparingly. Self-host the PowerSync service in Docker Compose (a tool that starts several containers together from one config file) next to Postgres and run a handful of true end-to-end flows: place an order offline, connect, and watch confirmed replicate back; kill the process mid-upload and restart it. These are slow and exist to validate wiring and configuration (sync-stream scoping, auth, checkpoint behavior) rather than business rules, which levels 1 and 2 already pinned down.

Choosing an engine: honest alternatives

Server-authoritative, create-only sync is a pattern. PowerSync is one implementation substrate for it. The options as of July 2026, honestly assessed:

One abbreviation in that table, since it is shop-talk rather than a concept: DIY is do-it-yourself, meaning you write the machinery from scratch.

The through-line: for ERP data, the winning designs all keep Postgres as the single source of truth and route writes through server-side code you own. The engines differ in how much transport, durability, and partial-replication machinery they take off your plate. PowerSync currently takes the most while leaving the write path fully yours, which is exactly the division of labor this chapter argued for.

Field notes & further reading

• Ink & Switch — Local-first software: you own your data, in spite of the cloud: the 2019 essay by Kleppmann, Wiggins, van Hardenberg and McGranaghan that named the discipline. Read it for the seven ideals and for why CRDTs alone don't finish the job.
• PowerSync docs — Sync Streams and Sync Rules (legacy): stream and bucket definitions, parameters, and the option tables for auto_subscribe, priority and TTL.
• PowerSync docs — migrating from Sync Rules to Sync Streams: the syntax mapping, including request.jwt() ->> 'claim' becoming auth.parameter('claim').
• PowerSync docs — Integrating with your backend: the authoritative reference for uploadData, CRUD transactions, complete(), and the retry behavior that makes poison pills possible.
• PowerSync docs — Consistency: checkpoints, causal+ consistency, and why a pending upload stops the client advancing.
• PowerSync + Supabase integration guide: publication, replication role with BYPASSRLS, and Supabase-auth JWT setup.
• Supabase docs — Custom Access Token hook: how to put an org_id claim into the JWT that your sync config reads.
• ElectricSQL — Writes guide: the clearest articulation anywhere of read-path/write-path separation, plus its four write patterns: online writes, optimistic state, shared persistent optimistic state, and through-the-database sync.
• Zero docs — Offline: a candid explanation of why a sync engine might refuse offline writes. It is the best short read on the constraint-versus-convergence tension.
• Replicache: the predecessor, now in maintenance mode, with its authors' own note pointing users to Zero.