> ## Content Index
> Fetch the complete content index at: https://stalin-pereira.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Offline Sync Without Lost Edits: Stale-Write Triggers and Partial Unique Indexes
- URL: https://stalin-pereira.com/offline-sync-without-lost-edits-stale-write-triggers-and-partial-unique-indexes/
- Published: 2026-10-26T08:30:00.000Z
- Updated: 2026-10-26T08:30:00.000Z
- Description: Order conflicts by when the edit happened, let a Postgres trigger drop stale writes, keep balances in a ledger, and make 'only once' a partial unique index.
- Author: Stalin Pereira
- Tags: Distributed Systems

I've been building a habit-tracking app that works offline first. Every tap writes to a database on the phone, and a sync job pushes the changes to the server later. The happy path was easy. What took longer were two bugs that only show up when several writers act at once: an old edit beating a new one, and a reward meant to be handed out once being handed out six times.

Both had the same fix. I stopped asking the clients to be careful and made the database refuse the bad write.

## The setup: dumb pushes

I wanted the sync code on the device to be as simple as possible. It doesn't diff anything or ask the server what changed. It pushes its whole local state as upserts, every time, and then pulls. A replay is harmless because every row has a client-generated id, and a failed push simply runs again on the next sync.

That simplicity has a hole. Imagine the same habit open on a phone and a tablet, both offline:

- The phone renames it to "Run 5k".
- A bit later, the tablet renames it to "Run 10k".
- The tablet comes online first and pushes. The server now has "Run 10k".
- The phone comes online and pushes its full state, including "Run 5k".

A plain upsert accepts that last push, so the older edit wins just because it arrived later. It gets worse. When the tablet next pulls, its merge sees a row it already has, so nothing changes on its side. The phone keeps "Run 5k" and the tablet keeps "Run 10k", and each one thinks it's in sync. Nothing errors, and nothing ever fixes it.

## Order by when the edit happened, not when it arrived

The obvious answer is last-write-wins, but you have to decide what "last" means. If it's the server's receive time, whoever syncs last wins, and that's exactly the bug above. The useful ordering is **when the user made the edit**. So every editable row has an `updated_at` that the device sets at the moment of the edit, and that timestamp travels with the row.

Then the server has to enforce it. I first thought about putting the check in each client ("only push if you're newer"), but a client that hasn't pulled can't know whether it's newer. The server is the one place that can see both versions at the same moment, so a `BEFORE UPDATE` trigger compares them there:

```sql
create or replace function reject_stale_update() returns trigger
language plpgsql as $$
begin
  if new.updated_at < old.updated_at then
    return null;  -- skip this row: the stored version is newer
  end if;
  return new;
end;
$$;

create trigger items_reject_stale
  before update on items
  for each row execute function reject_stale_update();
```

In Postgres, a row-level `BEFORE` trigger that returns `NULL` skips the operation for that row without raising an error. The client's upsert "succeeds", the stale copy is dropped, and on the next pull the phone receives "Run 10k" and applies it. Pushes stay dumb and idempotent, and every client gets the same protection at once, including old app versions already installed on people's phones.

Phone

Server

Tablet

1. Phone**Edit offline**"Run 5k", edited at t1
2. Tablet**Edit offline**"Run 10k", edited at t2 (later)
3. Server**Tablet syncs first**stores "Run 10k" @ t2
4. Server**Phone pushes t1: dropped**trigger: t1 < t2, keep stored row
5. Phone**Pull and merge**now has "Run 10k" @ t2

The older edit arrives last. Ordering by receive time would let it win; ordering by edit time lets the trigger drop it, and both devices end up on the same version.

One thing to watch: the trigger is only as good as the timestamp it compares. If something else on the server rewrites `updated_at` to `now()` on every update (a common convenience trigger), you're back to ordering by arrival. The edit time has to come from the device and be left alone.

## The cost: device clocks

Using the device's clock means trusting it. If a phone's clock runs ten minutes fast, its edits win against anything made in those ten minutes on another device. If it runs slow, its edits can lose to older ones. I accepted that on purpose. For a personal app where conflicts are rare and usually involve one person on two devices, a skewed clock occasionally picking the wrong winner is much better than the server picking whoever synced last, every time. If conflicts were common or several people edited the same data, I'd want hybrid logical clocks or a per-field merge rather than a plain timestamp.

Ties go to the version already stored, and on the device a tie keeps the local row. A device should never undo its own edit because another one has the same timestamp.

## Things that shouldn't be last-write-wins at all

The app has a small reward balance: you're given a few tokens up front, earn more over time, and spend them to protect a streak. The first instinct is a `balance` column. Under last-write-wins that's a disaster. Two devices each spend one from a balance of 3, each writes 2, and one spend disappears. Or a device that missed a spend writes its old balance back and brings the token back.

So there's no balance column. There's an append-only ledger where each grant, earn or spend is its own row, and the balance is calculated when it's needed:

```sql
select greatest(0, sum(delta)) as balance
  from ledger
 where account_id = $1;
```

Rows don't conflict, they add up. Two devices spending at once produce two rows, and both are kept. Most rows also get an id derived from what they mean (for example kind plus date), so if two devices compute the same daily reward, the second insert matches the first row and does nothing.

## The race: count, then insert

One row type didn't fit that pattern: the one-time welcome grant. It intentionally gets a random id, so grants from two different devices stay separate and the server can reject the second. The server already had that protection. The device's own database didn't. Locally, the only guard was this:

```
if (count(kind = 'welcome_grant') == 0) {
  insert(welcome_grant)
}
```

That's a classic check-then-act race. The app runs a catch-up pass when it starts, when it comes to the foreground and from a background job, and those can overlap. Each one counts zero, and each one inserts. In a test with eight concurrent passes I got **six** grants. Nobody noticed, because the displayed balance is capped, so the extra tokens stayed hidden until the user spent down and found tokens nobody had given them.

Adding a lock in application code would cover only the code paths I remembered to wrap. The structural fix is a **partial unique index**:

```sql
create unique index ledger_one_welcome_grant
  on ledger (account_id)
  where kind = 'welcome_grant';
```

The `where` clause is what makes this work. A plain unique index on `kind` would break the ledger, because earn and spend rows have to repeat. The partial index applies only to welcome-grant rows and leaves every other row type alone. Postgres and SQLite both support it, so the device and the server can enforce exactly the same rule. Now a second grant fails in the database, no matter which code path tried to write it.

Two details mattered when I shipped it.

**Existing duplicates come first.** You can't build a unique index over rows that already break it, so the migration deletes the extras first. I kept the *oldest* grant, since that's the one the server most likely accepted. That way the device and server agree on *which* grant is real, not just on how many there are.

```sql
delete from ledger
 where kind = 'welcome_grant'
   and exists (
     select 1 from ledger as older
      where older.kind = 'welcome_grant'
        and older.account_id = ledger.account_id
        and (older.created_at, older.id) < (ledger.created_at, ledger.id)
   );
```

**New installs don't run migrations.** The index had to go into the migration for existing installs *and* into the schema that creates a fresh database. Forget the second and every upgraded user is protected while every new user isn't, which is about the hardest version of this bug to notice.

The test taught me something too. The concurrent version caught the bug reliably on one platform and passed without the fix on the other, because each pass takes about a millisecond and the passes rarely overlapped. A test that passes by luck isn't protecting anything. I added a deterministic version that has both writers read before either one writes, which is the whole bug, and kept the concurrent test as a second, weaker check.

## What this doesn't solve

There's a gap I haven't closed. Un-completing a habit doesn't sync. Completions are inserted and ignored when they already exist, so deleting one on a device just leaves a missing row, and the server can't tell "deleted here" from "not synced yet". The next pull brings it back. Fixing that needs tombstones (a `deleted_at` column that syncs like any other edit), which is a different mechanism from last-write-wins on updates. Until then, the stale-write trigger handles edits well and does nothing for deletes.

## Takeaway

When many writers share data, don't count on each client to do the right thing. Decide what "newer" means (edit time, not arrival time), let a trigger enforce it where both versions are visible, store events instead of totals, and turn "only once" from an `if` into a partial unique index.