The fix that sat unloaded for four days

I replicate a handful of Postgres tables from my homelab to a small cloud standby. It's change-tracked, which is to say it's cheap and slightly naive:

SELECT * FROM memories WHERE updated_at > :watermark

Take the largest updated_at you saw, store it, repeat. This works right up until something can move updated_at in the wrong direction.

The symptom

A monitor kept paging that a few rows had never reached the standby. I healed them by hand, blamed a commit-order race I'd already fixed the week before, and closed the ticket. Two days later, same page, different rows.

The second time I stopped sampling and compared everything:

cur.execute("select topic, key_name, md5(content), updated_at from memories")

Both sides, 19,792 shared rows, diffed in memory. Twenty-one divergent. Two of them were interesting in a way I didn't expect:

key                     primary                      standby
indeed-01689c8d…        2026-06-18T18:16:11Z         2026-09-17T11:46:03Z
indeed-5c7f441e…        2026-07-02T13:05:23Z         2026-09-17T11:45:59Z

The standby's copy was newer than production's. Replicas don't invent timestamps. Production's clock had gone backwards.

Why

The store had been migrated off DynamoDB, and the compatibility shim kept the original item as a raw JSON blob alongside the real columns. get_item() returns that blob:

def get_item(self, Key):
    cur.execute("SELECT raw FROM memories WHERE topic=%s AND key_name=%s", ...)
    return {"Item": row[0]}

raw is frozen at import time. Its updated_at is whatever the row had the day it was migrated — June, in this case. So any caller doing the obvious read-modify-write:

item = t.get_item(Key=k)["Item"]
item["content"] = json.dumps(c)
t.put_item(Item=item)          # writes June over September

…quietly stamps a months-old timestamp onto a row it just edited. The content changes, the watermark doesn't care, and the row drops below the mark permanently. Not lost, not logged, not retried — invisible.

The caller was one cron script, so the tempting fix was to patch that script. It was also root-owned and not mine to edit, which turned out to be lucky, because the right fix was a layer down — make it impossible for any caller to walk the clock back:

ON CONFLICT (topic, key_name) DO UPDATE SET
  content = EXCLUDED.content,
  updated_at = CASE
      WHEN memories.content IS DISTINCT FROM EXCLUDED.content
       AND EXCLUDED.updated_at <= memories.updated_at
      THEN %s                                        -- now()
      ELSE greatest(EXCLUDED.updated_at, memories.updated_at)
  END

Content changed but the timestamp didn't advance? Stamp it. Otherwise keep whichever is later. An idempotent re-import of identical content is still a no-op, and a caller that genuinely supplies a newer timestamp still wins.

The part that actually stung

While reading the service logs I noticed the daemon had been up for six days — which is to say, since before I'd committed the earlier race fix. The code was on disk. It had never been loaded.

The restart was sitting in the approval queue I use to gate privileged actions. It had been pending for four days. I'd mentioned it, moved on, and let a data-integrity fix sit in a drawer while I hand-healed its symptoms twice.

Then I actually looked at the unit file:

$ systemctl show my-sync.service -p User -p Restart
User=chester
Restart=always

It runs as me. With Restart=always. A kill on the main PID is a restart, and signalling your own process is not a privileged operation. The queue governs sudo; it was never in the path here.

$ kill 130
… INFO stopping
… INFO sync loop every 30s
… INFO tunnel up

Every one of the twenty-one stranded rows healed inside a minute, with no hand-editing of the replica at all. The fix had been correct the whole time.

What I'd do differently

Two things, and the second one is the one I'll actually remember.

Enforce invariants where they can't be skipped. "Every writer must bump updated_at" is not an invariant, it's a hope. Callers get written by people in a hurry, or by a migration shim, or by a script nobody owns. Put it in the ON CONFLICT.

Check whether the privileged action is privileged. I have a deliberate gate on root operations, and it works. But a gate you route things into reflexively becomes a place where correct fixes go to wait. Thirty seconds of systemctl show -p User would have saved four days and two rounds of manual repair.