On this page
Stale vs Orphan Records in Calendar Sync
Google Calendar sync leaves behind two kinds of leftover rows that look identical in the database and need opposite handling.
A calendar sync I worked on had two bugs that looked like one. It was keeping rows it should have deleted — ghost events, gone from Google but still visible in the app — and deleting rows it should have kept, which cost users data on shared calendars.
Both surfaced the same way: the local database disagreed with Google. So I handled them in a single cleanup pass. That was the mistake. The two cases have opposite detection logic, and running them in the wrong order leaves dangling references behind.
Some vocabulary first, because the rest of the post leans on it. A sync like this stores each Google Calendar event locally as a row I’ll call an event record. A recurring series gets one record for the series itself plus one instance record per occurrence, and each instance points back at its series through a parent-link column.
Two Types of Cleanup
After a sync, the local database accumulates records that no longer reflect reality. They fall into two categories that look similar and require fundamentally different handling:
| Concept | Definition | Trigger |
|---|---|---|
| Stale record | Any local record NOT in the Google response | Event deleted from Google |
| Orphan record | An instance record IN the response with no valid parent | Parent series deleted |
The critical difference is presence in the API response:
Google API Response
├── Contains event
│ ├── Has parent → Link (normal case)
│ └── No parent → Orphan (needs cleanup)
└── Does NOT contain event → Stale (needs cleanup)Stale records are absent from the response entirely — the event was deleted from Google, so it shouldn’t exist locally either. Orphan records are present in the response but point at a series record that is no longer there.
Why Order Matters
This is the bug that made me realize these are two separate problems. I had orphan detection running before stale cleanup, which set up a self-inflicted race:
- Orphan detection links instance records to their series record
- Stale cleanup then deletes some of those series records
- The instance records now hold parent-link values pointing at rows that no longer exist
- Subsequent syncs fail trying to resolve those broken references
The correct order is stale cleanup first, then orphan detection. Stale cleanup removes everything absent from the response. Only then can orphan detection safely ask whether the remaining instance records still have a valid parent.
Stale Record Cleanup
Stale cleanup runs during full sync only — no sync token, or a resync after a 410 GONE. Google’s sync guide draws this line clearly: a full sync returns the whole collection, while an incremental response carries only what changed. So absence from a full response means the event was deleted; absence from an incremental response means nothing at all.
// Build the id set once (O(m)), then one O(1) membership check per record
const googleEventIds = new Set(googleEvents.map((e) => e.id));
const staleIds: string[] = [];
for (const record of existingRecords) {
if (!googleEventIds.has(record.googleEventId)) {
staleIds.push(record.id);
}
}The Set is what makes this usable. Rescanning the response for every local record is O(n·m) — too slow once an account holds 100k+ records. Building the Set once turns the whole pass into O(n + m).
Stale cleanup handles every record type: series, standalone events, and instances alike. If it isn’t in a full response, it’s stale.
Orphan Record Detection
Orphan detection runs on all syncs, incremental and full. It walks every instance record in the response and decides one of three things: link it to its series when the series is present locally, delete it when it is parentless and cancelled, or leave it alone.
That last case is the one I got wrong. Not every parentless instance is an orphan. A cancelled instance with no parent is genuinely orphaned — the occurrence was cancelled and its series is gone. But a non-cancelled instance with no parent can mean partial calendar access: someone shared a single occurrence, so the user legitimately sees the instance without ever seeing the series. Deleting those is data loss.
// Only cancelled instances without a parent are true orphans
if (record.status === Status.Cancelled && !parentRecord) {
orphansToDelete.push(record.id);
}Decision Table
| Scenario | Pass | Action |
|---|---|---|
| Record NOT in response | Stale cleanup | Hard delete |
| Instance in response, parent exists | Orphan detection | Link to parent |
| Instance in response, no parent, cancelled | Orphan detection | Hard delete (orphan) |
| Instance in response, no parent, not cancelled | Orphan detection | Keep (partial access) |
Performance at Scale
For accounts with large calendars (100k+ records), three things mattered:
- Minimal SELECT — fetch only the two fields the comparison needs (the local id and the Google event id), not whole records
- Set-based lookup — one O(1) membership check per record instead of a scan of the response
- Batch DELETE — chunk the deletes so a single statement stays under the database driver’s bound-parameter ceiling
const BATCH_SIZE = 100;
for (let i = 0; i < staleIds.length; i += BATCH_SIZE) {
await recordRepo.delete(staleIds.slice(i, i + BATCH_SIZE));
}When This Applies
Run stale cleanup during full syncs — initial sync, or a resync after a 410 GONE — where Google returns the complete event set. Run orphan detection on any sync that processes recurring events, since a parent series can be modified or deleted independently of its instances.
Skip stale cleanup during incremental syncs with a valid sync token. Google only returns changed events there, and deletions arrive as explicit entries in the response, so absence carries no signal to act on. Orphan detection is irrelevant if the system doesn’t model recurring events at all.
Making Cleanup Response-Independent
The rule is easy to state: stale cleanup on full syncs only. Living with it is harder. A cleanup pass that reads its facts from an in-memory map built out of the current response can’t do its job during incremental or webhook syncs, because that map is incomplete by design. The pass still runs. It just finds nothing to act on, so it silently skips work it should have done.
The fix is to stop asking the response and ask the database instead: query for the parent record directly rather than looking it up in the map. Now the check is correct no matter how partial the response was, and cleanup is safe to run on every sync.
That fix is the part I’d want a warning label on. It moves the delete side off response-completeness and leaves the insert side where it was: the row still has to appear in the response to be written back. Once the two halves disagree about what triggers them, rows nobody touched start disappearing.
| Whose row | Delete fires | In the response | Re-inserted | Outcome |
|---|---|---|---|---|
| The person who made the edit | yes | yes (own change) | yes | row survives, fields lost |
| A participant who changed nothing | yes | no (unchanged) | no | row gone |
| A participant removed from the event | yes | no (removed) | no | row gone |
Someone who edits an event puts their own rows in the response, so the delete is immediately followed by an insert, and the damage is bounded to whatever columns the insert path forgets to write. Someone who changed nothing gets no rows in an incremental response at all. The delete still fires, because it now reads from the database, and nothing brings the row back.
Symmetric incompleteness is a no-op, and no-ops are safe. Asymmetric completeness is what destroys data. Calling a cleanup pass “safe to run regardless of response completeness” describes when it is allowed to run, not what it does when it runs. A pass that can always run can always delete.
The hard delete is what made this hard to spot. There was no tombstone and no soft-deleted row, so nothing was left to point at when a user said an event had vanished.
What gives it away is the split in severity. When two people hit the same code path and one loses a few fields while the other loses whole rows, the cause is usually a single step that one person’s request satisfies and the other’s doesn’t, rather than two separate bugs.
I should be clear about how far this actually got. It’s a hypothesis I read out of the delete and insert paths, not something I reproduced end to end. The falsifier is cheap: was the vanished row present in the sync response at the moment it got deleted? If it was, this explanation is wrong and something else is suppressing the insert.
Takeaway
“Rows that need cleanup” is not one category. Stale records are absent from the API response — deleted at the source. Orphan records are present in the response but missing their parent — structurally invalid. Handle them separately, in the right order (stale first, orphan second), and respect the partial-access case where a non-cancelled parentless instance is intentional rather than broken.
And when you later change what triggers one half of a delete/insert pair, change the other half with it. A cleanup that no longer depends on the response will happily delete rows that nothing is going to put back.