Nate Swenson

Reconcile instead of upsert when your source of truth can shrink

Local Fitness · No. 122 · 13 min read ·v0.55.0

Shipped

This release put a training plan onto Google Calendar and kept it there: every prescribed session from today to race day as an all-day event, refreshed on a nightly job and again whenever the plan is edited. The interesting part was not writing events, it was what happens the second time you run it, and the fifth, and after someone deletes one by hand.

The general problem shows up anywhere you push a local source of truth into somebody else’s API: calendars, ticket trackers, DNS records, feature flags, status pages. If your local set can ever get smaller, an upsert loop is not enough, and I want to walk through the shape that is.

The thing an upsert cannot do

An upsert says “make this exist, with these values.” Run it over your rows and you get everything you have, correctly. Run it after deleting a row and you get everything you have, correctly, plus the deleted one, still sitting there.

That gap is not cosmetic. In my case a day that changed from a run to a rest day left the calendar confidently telling me to run. The remote had no way to hear “this one is gone,” because nothing in the loop was ever going to mention it again.

Kubernetes controllers name the alternative. The docs describe a control loop that watches state and makes changes where needed, where a spec field represents desired state and the controller is responsible for “making the current state come closer to that desired state” (Kubernetes controllers). Comparing the full desired set against the full current set is what surfaces absence. Nothing else does.

Setup: what you need before writing any sync code

Three things, and the first one is the one people skip:

  1. A stable notion of identity for each record, independent of its content. Usually a natural key you already have, such as (plan, date, slot) or (repo, environment).
  2. A way to list only the objects your sync created, on the remote. If you cannot scope the listing, you cannot safely compute deletions.
  3. A window. Decide what your sync is responsible for. Mine is “today forward”; everything older is out of scope permanently.

Step 1: derive the remote ID from identity, never from content

Most APIs let the client supply an object ID. Google Calendar does, with a specific constraint: characters must come from base32hex encoding, “i.e. lowercase letters a-v and digits 0-9”, and IDs run 5 to 1024 characters (Events: insert). A SHA-256 hex digest is already inside that alphabet, which makes this a two-line function.

Note the alphabet stops at v. A prefix like sync is rejected because y is out of range, which is a five-minute debugging detour I would rather you skip.

import hashlib

# Google requires event IDs to use base32hex: lowercase a-v and digits 0-9,
# 5-1024 characters. Note 'v' is the last legal letter, so a prefix like
# "sync" is rejected ('y' is out of range). "cal" and a hex digest are safe.
ID_PREFIX = "cal"
DIGEST_CHARS = 26


def stable_id(*identity) -> str:
    """A deterministic remote ID derived from what a record IS, never what it says."""
    raw = "|".join(str(part) for part in identity).encode()
    return ID_PREFIX + hashlib.sha256(raw).hexdigest()[:DIGEST_CHARS]

This is the same idea as a client-generated idempotency key. Stripe describes a key that “the server uses to recognize subsequent retries of the same request” so a repeat cannot create a second object (Stripe idempotent requests). The difference is lifetime: an idempotency key covers one retry window, while an identity-derived ID covers the object forever, so tomorrow’s run finds today’s object without storing anything locally.

Hash the identity, not the payload. If you hash the content, every edit produces a new ID, the old object stays behind, and you accumulate one stale copy per revision. That is the failure that gets an integration muted within a week.

Step 2: build desired state, and bound it

Desired state is a pure function of your records. Keep it free of network calls so you can test it, and put a ceiling on it so a bad query cannot write a thousand objects into somebody’s account.

from datetime import date, timedelta

MAX_SYNC = 200


class TooManyRecords(ValueError):
    """The batch is larger than this sync is willing to write."""


def build_desired(records, start):
    """Your local records -> the exact remote objects you want to exist.

    `records` are your rows: {"date", "seq", "title", "body", "skip"}.
    Only records on or after `start` are included; the past is out of scope.
    """
    rows = [r for r in records if r["date"] >= start and not r.get("skip")]
    rows.sort(key=lambda r: (r["date"], r["seq"]))
    if len(rows) > MAX_SYNC:
        raise TooManyRecords(f"{len(rows)} records over the {MAX_SYNC} cap")

    events = []
    for r in rows:
        day = r["date"]
        end = (date.fromisoformat(day) + timedelta(days=1)).isoformat()
        events.append({
            "id": stable_id(r["date"], r["seq"]),
            "summary": r["title"],
            "description": r["body"],
            "start": {"date": day},
            "end": {"date": end},
            "transparency": "transparent",
            "reminders": {"useDefault": False, "overrides": []},
            "extendedProperties": {"private": {"source": "my-sync"}},
        })
    return events

Two details worth pausing on. The cap refuses rather than truncating, because a half-written remote is worse than an unwritten one: you would trust the objects that made it. And end is the day after the last covered day, since the API documents the end as “The (exclusive) end time of the event” (Events resource). Get that wrong and you create a zero-length event, which several clients hide entirely.

Step 3: the reconcile, and the two rails that keep it safe

Now the diff. Five buckets, and the one that earns the whole design is delete.

COMPARED = ("summary", "description", "transparency")
CANCELLED = "cancelled"


def _remote_date(event):
    """All-day dates come back as 'YYYY-MM-DD' or a full timestamp. Normalize."""
    return str((event.get("start") or {}).get("date") or "")[:10]


def _reminder_key(event):
    """None means UNKNOWN. An absent key is not the same as 'no reminders'."""
    reminders = event.get("reminders")
    if reminders is None:
        return None
    overrides = frozenset(
        (o.get("method"), o.get("minutes")) for o in (reminders.get("overrides") or [])
    )
    return bool(reminders.get("useDefault")), overrides


def differs(existing, desired):
    if any(existing.get(f) != desired.get(f) for f in COMPARED):
        return True
    if _reminder_key(existing) != _reminder_key(desired):
        return True
    return any(
        str((existing.get(side) or {}).get("date") or "")[:10] != desired[side]["date"]
        for side in ("start", "end")
    )


def reconcile(desired, existing, start):
    """Diff desired state against remote state. Returns five disjoint buckets."""
    by_id = {e["id"]: e for e in desired}
    create, update, unchanged, delete, skipped = [], [], [], [], []
    seen = set()

    for row in existing:
        eid = row.get("id")
        if eid is None or _remote_date(row) < start:
            continue                      # rail 1: the past is out of scope
        seen.add(eid)
        want = by_id.get(eid)
        if row.get("status") == CANCELLED:
            if want is not None:          # rail 2: never resurrect a tombstone
                skipped.append(eid)
            continue
        if want is None:
            delete.append(row)            # the branch an upsert cannot express
        elif differs(row, want):
            update.append(want)
        else:
            unchanged.append(eid)

    create = [e for e in desired if e["id"] not in seen]
    return {"create": create, "update": update, "delete": delete,
            "unchanged": unchanged, "skipped": skipped}

Rail one filters existing objects to the window before diffing, so history is never rewritten. Yesterday’s object records what was true yesterday; the plan may have changed since, and forcing the past to match today’s answer is not syncing.

Rail two is the one I feel strongest about. When someone deletes an object your sync created, that is a person telling you something. A sync that puts it back an hour later gets uninstalled. So a cancelled object is neither recreated nor deleted again, and the count is reported so a missing item has an explanation instead of being a mystery.

Step 4: scope the listing, because delete is only as safe as its input

reconcile deletes anything in existing it does not want. That makes the listing query a safety boundary, not a convenience.

Tag every object you create, then filter on the tag. Google supports repeated privateExtendedProperty constraints combined with AND logic, and showDeleted to include objects with status cancelled, which defaults to false (Events: list).

session below is any HTTP client that already carries your auth header and API base URL; a requests.Session with a bearer token set on it is fine.

def list_ours(session, calendar_id, source_tag):
    """Only objects this sync created, tombstones included."""
    params = {
        "privateExtendedProperty": [f"source={source_tag}"],
        "showDeleted": "true",     # rail 2 needs to SEE tombstones
        "maxResults": 250,
        "singleEvents": "true",
    }
    out, page_token = [], None
    for _ in range(10):            # bounded: never page forever
        if page_token:
            params["pageToken"] = page_token
        r = session.get(f"/calendars/{calendar_id}/events", params=params)
        r.raise_for_status()
        body = r.json()
        out.extend(body.get("items") or [])
        page_token = body.get("nextPageToken")
        if not page_token:
            return out
    raise RuntimeError("listing did not terminate")


def apply_actions(session, calendar_id, actions):
    base = f"/calendars/{calendar_id}/events"
    for event in actions["create"]:
        session.post(base, json=event).raise_for_status()
    for event in actions["update"]:
        session.put(f"{base}/{event['id']}", json=event).raise_for_status()
    for row in actions["delete"]:
        resp = session.delete(f"{base}/{row['id']}")
        if resp.status_code not in (200, 204, 404, 410):
            resp.raise_for_status()      # 404/410 mean it is already gone

showDeleted is doing real work. Leave it at its default and a hand-deleted object simply looks absent, so the reconcile recreates it and rail two never fires.

Verify it against a fake remote before you point it at a real account

The reconcile is pure, so you can prove its behaviour without credentials. Write a fake that holds state and leaves a tombstone on delete, then walk the transitions.

from sync import build_desired, reconcile


class FakeCalendar:
    """Enough of the remote to test convergence: it holds state, and a delete
    leaves a tombstone the way Google's does."""

    def __init__(self):
        self.events = {}
        self.writes = 0

    def list_ours(self):                       # showDeleted=True equivalent
        return list(self.events.values())

    def apply(self, actions):
        for event in actions["create"] + actions["update"]:
            self.events[event["id"]] = {**event, "status": "confirmed"}
            self.writes += 1
        for row in actions["delete"]:
            self.events[row["id"]]["status"] = "cancelled"
            self.writes += 1

    def delete_by_hand(self, event_id):
        self.events[event_id]["status"] = "cancelled"

    def live(self):
        return [e for e in self.events.values() if e["status"] != "cancelled"]


def sync_once(cal, records, start):
    desired = build_desired(records, start)
    actions = reconcile(desired, cal.list_ours(), start)
    cal.apply(actions)
    return {k: len(v) for k, v in actions.items()}


START = "2026-03-02"
RECORDS = [
    {"date": "2026-03-01", "seq": 1, "title": "Before the window", "body": "history"},
    {"date": "2026-03-02", "seq": 1, "title": "Task A", "body": "first"},
    {"date": "2026-03-03", "seq": 1, "title": "Task B", "body": "second"},
    {"date": "2026-03-04", "seq": 1, "title": "Task C", "body": "third"},
]

cal = FakeCalendar()
print("1. first run      ", sync_once(cal, RECORDS, START))
print("2. nothing changed", sync_once(cal, RECORDS, START))

edited = [dict(r) for r in RECORDS]
edited[2]["title"] = "Task B (moved earlier)"
print("3. one record edit", sync_once(cal, edited, START))

dropped = [r for r in edited if r["date"] != "2026-03-04"]
print("4. one record gone", sync_once(cal, dropped, START))

victim = next(e["id"] for e in cal.live() if e["summary"].startswith("Task A"))
cal.delete_by_hand(victim)
print("5. user deleted one", sync_once(cal, dropped, START))

print("6. still deleted?  ", not any(e["summary"].startswith("Task A") for e in cal.live()))
print("7. total writes    ", cal.writes)
print("8. live titles     ", sorted(e["summary"] for e in cal.live()))

Save the first four blocks as sync.py and this one as check_sync.py, then run python3 check_sync.py. That produces:

1. first run       {'create': 3, 'update': 0, 'delete': 0, 'unchanged': 0, 'skipped': 0}
2. nothing changed {'create': 0, 'update': 0, 'delete': 0, 'unchanged': 3, 'skipped': 0}
3. one record edit {'create': 0, 'update': 1, 'delete': 0, 'unchanged': 2, 'skipped': 0}
4. one record gone {'create': 0, 'update': 0, 'delete': 1, 'unchanged': 2, 'skipped': 0}
5. user deleted one {'create': 0, 'update': 0, 'delete': 0, 'unchanged': 1, 'skipped': 1}
6. still deleted?   True
7. total writes     5
8. live titles      ['Task B (moved earlier)']

Line 1 creates three of four records, because the March 1 row is outside the window. Line 2 is the one to care about: a second run with unchanged inputs performs zero writes. Line 4 is the delete branch firing on a record that no longer exists locally, and line 5 is rail two, where a hand-deleted object costs one skipped count and no requests at all.

If line 2 shows anything other than all-unchanged, your comparison is reporting a difference that is not real, and the sync will rewrite everything on every run forever.

Gotchas

A field you send is not always the field you get back. Sending {"useDefault": false, "overrides": []} returns {"useDefault": false}, with the empty list dropped. A plain dict comparison therefore reports a difference on every single run and the sync never converges. I caught this before it shipped only because I ran the second sync and checked for zero writes rather than checking that the first one looked right. That is why _reminder_key normalizes to a tuple of a bool and a frozenset instead of comparing the raw structures, and why order-insensitive comparison matters: the ordering of a returned list is the server’s choice, not yours.

An out-of-range value can be accepted and quietly changed. Reminder offsets are documented as valid “between 0 and 40320 (4 weeks in minutes)” (Events resource). I wanted a notification on the morning of an all-day event, which is after its midnight start, so I tried a negative offset. The API answered HTTP 200 and stored 0. Nothing errored, and the reminder was simply at a different time than I asked for. Read the object back after writing it and compare against what you sent; a 200 is not confirmation that the server agreed with you.

A conflict on a deleted object is not “already there.” When you supply your own IDs, a create against an ID that used to exist returns a conflict rather than a fresh object, because the tombstone still owns the ID. If you treat every conflict as “fine, update it instead,” you resurrect things people deliberately removed. Fetch the object on conflict and branch on its status.

Absent is not the same as empty. In _reminder_key, a missing reminders key returns None rather than “no reminders.” If you read absence as emptiness, an object that is actually inheriting a default compares equal to your explicit setting and never gets repaired. Reading it as unknown costs at most one redundant write, and that is the cheaper mistake.

A credential loaded at import time will leak into your test suite. My CLI module calls load_dotenv() at module scope, so the moment any test file imported it, real credentials entered os.environ for the whole test process and unrelated tests began attempting live calls. What surfaced it was a test that passed on its own and failed in the full suite, which is always worth chasing rather than re-running: order-dependent failures mean shared process state, and here the shared state was somebody’s real API credentials. It ran green in CI, which has no .env file, so it broke only on machines where the feature was actually configured. That is the worst direction for a test bug to fail in, because nothing forces anyone to notice. The fix was an autouse fixture that strips those variables for every test, so “not configured” is the default state and a test that wants the integration opts in explicitly.

Sources

Changelog

  • release: 0.55.0 — Google Calendar plan sync (dev → main) (#209) (052d58d)