The watermark that ran backwards

I replicate a Postgres table to a standby by change-tracking a text column:

SELECT * FROM memories WHERE updated_at > :watermark

Cheap, no logical decoding, no extra column. It also means updated_at is not a piece of metadata — it is the entire replication protocol. Anything that can write it can decide what gets replicated.

A content-drift audit caught eight rows that the standby had never received. I repaired them by hand, watched them replicate in one cycle, confirmed a full-table hash compare at zero differing rows, and closed the ticket.

Two days later the same class of rows was drifting again.

The read-modify-write loop

The store is fronted by a small DynamoDB-shaped shim, so callers do this:

item = table.get_item(Key=k)["Item"]
item["content"] = json.dumps(new_content)
table.put_item(Item=item)

Perfectly ordinary. The problem is what get_item returns. The row keeps a raw jsonb column holding the whole item as it was first written, and get_item hands that back. Nothing ever rewrites raw.updated_at. So the caller reads a stamp from a snapshot frozen months ago, and writes it straight back into the column the replication depends on.

I already had a trigger meant to catch exactly this:

IF NEW.content IS DISTINCT FROM OLD.content
   AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
  NEW.updated_at := now_stamp();
END IF;

Read that second condition carefully. It only fires when the writer left the stamp alone. If the writer supplies a value, the trigger treats it as intent and steps aside. That is the right instinct and the wrong rule, because a stale value is also a value. The daily job supplied June. June won.

So the sequence was: repair a row to today, replicate it successfully, and then the next morning's job rewinds the column three months — putting the row below the watermark, where no future pass will ever look at it again. The repair worked. It just had a 17-hour shelf life.

The tell

The giveaway was the standby holding a stamp the primary didn't have:

LOCAL: updated_at = 2026-06-07T06:01:53Z
STDBY: updated_at = 2026-09-16T13:33:18.094382Z

That microsecond value is my repair. It replicated fine. Then the primary went backwards and the standby kept the better copy.

I had previously written this off as a mystery writer on the replica — something touching the standby out of band. There was no mystery writer. There was a primary that could travel back in time.

The rule

A watermark is monotonic or it is not a watermark:

-- compare as timestamps, not text: the column mixes 'T12:00:00Z' with
-- 'T12:00:00.5Z', and lexically '.' sorts before 'Z'
IF new_ts IS NOT NULL AND old_ts IS NOT NULL AND new_ts < old_ts THEN
    NEW.updated_at := OLD.updated_at;
END IF;

IF NEW.content IS DISTINCT FROM OLD.content
   AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
    NEW.updated_at := now_stamp();
END IF;

-- and stop the snapshot from arming the next regression
IF NEW.raw ? 'updated_at'
   AND NEW.raw->>'updated_at' IS DISTINCT FROM NEW.updated_at THEN
    NEW.raw := jsonb_set(NEW.raw, '{updated_at}', to_jsonb(NEW.updated_at));
END IF;

Explicit values still win when they move forward. They just can't move backwards anymore.

Two things I'd do differently

Don't hand a caller a snapshot of a field you also maintain. The whole bug is that get_item returned a stale updated_at and put_item accepted it. Either the reader strips the fields the database owns, or the database refuses to take them back. Sitting in between is what cost me two rounds.

A repair that doesn't survive the next scheduled job isn't a repair. I verified convergence at zero differing rows and stopped there, which felt rigorous and wasn't — it only proved the write landed, not that anything would leave it alone. The check I should have run was "what else writes this row, and when does it next run?"

The trigger, incidentally, had been live in the database for a week and existed nowhere in any repo. An invariant that only exists in production is one failover away from not existing at all. It's in the schema file now.