Mirror on a server-stamped change cursor, not creation time

If you keep a local mirror of a mutable upstream, you have to page on a field the server bumps on every write. Creation time and insert order feel like they work, and they do until a row gets edited in place after it was inserted. Then your cursor sails right past the change and never sees it.

The trap

The lcos CLI mirrors Convex daily_features into local SQLite. Convex rows carry _creationTime, which is the insert moment and never moves. There is no updatedAt on that table — the daily rollup is patched in place every time it recomputes. So a high-water cursor on _creationTime is correct exactly once: the first sync. After that, every recompute is invisible to it.

It gets worse. A single settings change (source priority) recomputes a 90-day range at once. So the rows that go stale aren't the recent ones you might re-pull out of habit; they're a three-month block that all changed without moving any insert timestamp.

The tempting patch is a trailing window: "re-pull the last N days every sync." That's a guess dressed as a fix. Too small and you miss the 90-day recompute; too big and you burn bandwidth re-pulling rows that didn't change. There is no N that's both correct and cheap, because the thing you're trying to track isn't time-shaped.

The fix

Stamp the change yourself. Add a monotonic field the server writes on every insert and every patch, index it, and page on that. Now "changed since" means what it says.

In lcos that field is derivedAt. The mirror drains daily-features-since?cursor=<derivedAt>, advancing to the page's max. A pre-deploy fallback covers the window before the field exists everywhere, then retires.

Validation against code (2026-06-26)

• packages/backend/convex/derivation.ts stamps a single derivedAt = Date.now() on both the patch and the insert of daily_features (the only two writes in the file).
• schema.ts adds the by_user_derivedAt index; http.ts serves /api/ios/daily-features-since.
• apps/cli/crates/sync drains by derivedAt and falls back to a trailing window when a row's derivedAt is null.

Why this generalises

Any local cache, read replica, or ETL of a system that recomputes rows in place needs this: CRM mirrors, analytics rollups, anything with a "recompute" button. The smell is "I'll just sync everything since the last creation time" — which only holds if the upstream is append-only and never edits a row after insert. The moment it patches, you need a stamp it controls.

See also

• Stale Registries Need Local Authority — the other half of mirror correctness: when the local copy is the fresher truth
• Cache the Facts, Not the Verdict — what the mirror is allowed to answer once it's fresh
• Lcos — the mirror this came from