An updated_after query can never return a deleted record

An incremental sync asks a source one question: which documents have changed since a given moment? The answer drives everything downstream — a fetch, a chunking pass, an embedding call, a write into a vector index — and for edits it works. A page rewritten at noon appears in the next answer, is refetched, and its new text replaces its old. The mechanism is cheap, it scales with the rate of change rather than with the size of a corpus, and that is why almost every connector is built this way.

Deletion sits outside that mechanism, and outside it by construction rather than by omission. A source asked what has changed answers with documents it still holds, and a deleted document is no longer among them. There is nothing to fetch, no timestamp to compare and no payload to process, so a pipeline that only ever reacts to that answer keeps serving a deleted page until something else entirely tells it not to. In practice that gap is measured in weeks, and it tends to arrive as an email from a lawyer rather than as an alert.

The set an incremental query can see#

An updated_after query is a filter over rows that currently exist. Written as sets, the source holds a current set S, the index holds whatever the pipeline has written, and each poll returns the subset of S whose modification time exceeds the cursor. Documents that were deleted are not members of S at all, so they cannot be members of any subset of it. Every deletion is therefore invisible to the incremental path by construction, and the invisibility is a property of the query shape rather than a bug in the connector, a gap in coverage, or a consequence of polling too slowly.

That conclusion is stronger than it first sounds, so it is worth taking one step at a time. Call the set the source currently holds S, and call the moment the connector last read the cursor. Each poll returns those members of S whose modification time is later than the cursor, which is a filter applied to S, and a filter can only ever hand back things that were in S to begin with. Now call the set of documents that once existed and have since been deleted D. Deletion is the operation that removes a document from S, so no member of D is a member of S. A filter over S cannot return something absent from S. The poll's answer and D therefore share no member, and that holds for every value of the cursor, at every interval, on every schedule.

The poll interval touches one term in that argument and one only, and it appears inside the filter rather than outside it. Shrinking the interval changes which members of S come back and how soon they come back. It cannot introduce a member of D, because the interval appears nowhere in the statement that D and S are disjoint. The term cancels, and it cancels exactly: deletion latency is independent of polling frequency, not merely insensitive to it.

As an analogy, reading a source's change list to find deletions is like reading a list of what is on a shelf to find out what was taken off it. The analogy breaks in the place that makes this hard. A shelf leaves a gap where the item was, and a change list leaves none — an absent document and a document that simply did not change produce identical answers. That is why the failure is silent, and why it stays silent for as long as nobody checks the other direction.

Why a 404 check on refetch fires on nothing#

This is why the fix that suggests itself does nothing. Adding a check that removes any indexed document whose fetch returns 404 sounds like it addresses deletions directly, and it does address one narrow case: a page that was edited and then deleted between the connector reading the change list and fetching the body. It only runs on documents the pipeline fetches, and the pipeline only fetches documents the source reported as changed. The check has the shape of deletion handling, it appears in the code review as deletion handling, and it leaves the gap exactly where it was. Teams ship it, close the ticket, and find out the next time legal writes.

The belief underneath that check is worth stating plainly, because it has the shape most defences that do not work have. The check's coverage is complete: it runs on every single document that gets fetched, and that figure stays pinned at complete however many documents are deleted at the source. What varies is the size of D, and D is not in the fetched set. So a dashboard reports full coverage while the gap grows without limit, and the quantity being watched holds still precisely because it is measuring the wrong set. A number that cannot move is not passing a test. It is not running one.

The one honest thing to say about polling frequency is that it changes the staleness of everything the incremental path can see, and deletion is not in that category. Halving the poll interval halves the median lag on edits and has no effect at all on deletions.

Where deletions actually come from#

Sources differ enormously in whether they will tell you. Google Drive's changes.list endpoint, driven by a startPageToken, returns entries with removed: true and a file ID; Microsoft Graph's /delta queries return objects annotated @removed with a reason of deleted or changed; a Postgres source read through logical replication emits a DELETE record with the old primary key. A Confluence space read through the REST search API offers none of these, and neither do most internal HTTP APIs that grew an updated_after parameter because somebody needed incremental sync one afternoon.

The bottom row is the fallback, and the row above it is the one that gets mistaken for the bottom row.

Read the Coverage column as a set of gaps rather than a set of levels, because that is where the choice actually lives. Two mechanisms are complete, one is complete for a restricted question, and three are not complete at all — and those three fail in different directions. A trash listing sees soft deletes and misses purges, so its blind spot is the deletions that matter most legally. A refetch check sees only documents that also changed, so its blind spot is every pure deletion. A permission feed sees no deletions whatsoever and covers a risk of the same urgency. Nothing in the Detection latency column alters any of that, because a mechanism with a hole in its coverage does not close the hole by running sooner.

The Cost per day column has one gap in it worth pricing before anything else is decided. Every mechanism above the last row costs roughly what the incremental path already costs, or costs nothing at all. The last row costs a five-figure number of API calls per run against a four-million-document source. That is the price of the only complete answer available when the source will not tell you, and it is the reason the row sits at the bottom rather than the top.

Permission changes deserve their line in the table even though they are not deletions, because they produce the same category of incident. A page that stops being readable by the group your service account impersonates has effectively been deleted from the point of view of every user who should no longer see it, and a connector that ingested it while access was broad will keep serving it. If the source exposes an ACL feed — a stream of access-control-list changes, which is to say a record of who is now allowed to read what — the deletion path and the permission path are usually the same code with a different predicate.

Reconciliation over four million pages, with the cost attached#

Reconciliation means enumerating the current source IDs, comparing that set against the IDs in the index, and removing the difference. On the 4M-page wiki, the enumeration runs against a paginated listing endpoint at 200 IDs per request, which is 20,000 requests. The API's published limit is 10 requests per second and it responds in about 250ms, so the run takes between 33 and 70 minutes depending on whether you pipeline requests. Four million IDs at 24 bytes each is 96 MB held as a Python set, which fits comfortably in a worker with 2 GB, and a sorted-file merge or a SQLite temp table costs a few minutes more if you would rather not hold it in memory.

Every figure in that paragraph comes out of two inputs, and watching them combine is what makes the shape transferable to a source of a different size. Four million IDs divided by two hundred per request is twenty thousand requests exactly, with no remainder to round. Twenty thousand requests at ten a second is two thousand seconds, which is the lower end of the stated range and the floor the rate limit imposes; no amount of concurrency gets underneath it. The upper end is set by the round trip instead, when each request waits for the one before it and the quarter-second latency rather than the rate limit is what paces the run — the stated upper bound assumes a little overlap, since a strictly serial run at that latency would take longer still. Four million IDs at twenty-four bytes each is ninety-six megabytes, and that is what settles the memory question: a worker sized in gigabytes is nowhere near the boundary, which makes the on-disk alternatives an option rather than a necessity.

The dangerous part is the second half. An enumeration that takes an hour is not a snapshot: pages are created and deleted throughout it, and a page created after your pagination passed its shard will be absent from the enumerated set while very much existing at the source. Deleting it would be a bug that erases live content, so the diff has to be treated as a set of candidates rather than a verdict.

That gives the difference a precise composition, and naming it is what makes the rest of the design readable. Subtract the enumerated set from the indexed set and what comes back is a union of two things: documents genuinely deleted at the source, and documents that exist but were missed by an enumeration that was never atomic. The two are indistinguishable inside the difference itself — same shape, same type, no marker separating them. Everything after the diff exists to split that union. The individual refetch splits it one document at a time. The cap catches the case where the second part has grown large enough to swamp the first, which is what an enumeration failure looks like from outside.

The individual re-fetch in step D is the check that was useless on its own in the previous section, doing real work here because reconciliation has finally supplied it with the right candidate list. Step G is a soft delete, meaning the document's row is marked with a deleted_at timestamp and excluded from retrieval while its bytes stay in place, so a candidate that turns out to have been live is recoverable by clearing one column. At a plausible deletion rate of 0.1% of the corpus per day, that is around 4,000 fetches per run, which is small next to the 20,000 enumeration requests.

Nothing about that refetch is different from the earlier one. It is the same HTTP call, against the same endpoint, expecting the same status codes, written by somebody with the same intention. What changed is the set it is applied to, and that is the entire difference between a check that fires on approximately nothing and a check that resolves a deletion.

def reconcile(source, store, index, *, max_delete_fraction=0.005):
    live = set()
    for page in source.enumerate_ids(page_size=200):
        live.update(page)

    indexed = store.all_doc_ids()                  # 4M rows, one column
    candidates = indexed - live

    if len(candidates) > max_delete_fraction * len(indexed):
        raise ReconcileHalted(
            f"{len(candidates)} candidates ({len(candidates)/len(indexed):.2%}) "
            f"exceeds cap; enumeration returned {len(live)} ids"
        )

    for doc_id in candidates:
        if source.head(doc_id).status in (404, 410):
            store.mark_deleted(doc_id)             # deleted_at = now()
            index.delete(filter={"doc_id": doc_id})
        else:
            metrics.increment("reconcile.enumeration_miss")

The cap is the single most important line. A reconciliation whose enumeration was truncated by an expired token, a permission change on the service account, or an API returning an empty final page instead of an error will produce a candidate set of hundreds of thousands of documents, and a job that faithfully deletes them destroys the index in twenty minutes. Halting at 0.5% means a legitimate bulk deletion — an archived space with 40,000 pages in it — also halts, which is the correct outcome, since somebody should look at a 40,000-page deletion before it lands.

A cap has to sit above the normal reading and below the failure reading, and on these numbers it does, with room on both sides. An ordinary day's candidates are a tenth of a percent of the corpus and the cap is half a percent, so the cap sits at five times the ordinary reading and a day with several times the usual deletion volume still passes. An archived space of forty thousand pages is twice the cap and halts. The threshold has to live in the gap between five times normal and half of one bulk deletion, and that is a wide gap on a corpus this size and a narrow one on a small corpus — which is the thing to check before copying the number into a smaller pipeline.

Cost, at daily cadence: roughly 24,000 API calls per run against the wiki, negligible compute, and about an hour of a worker. Compared with the incremental path's 1,200-odd calls per day it is a twentyfold increase in API traffic, which is why teams run reconciliation nightly rather than continuously, and why the deletion SLA — the service-level agreement, the maximum delay the team commits to in public — is what it is.

That total closes exactly, and the fact that it closes is what makes the multiplier worth quoting. Twenty thousand enumeration requests plus four thousand candidate refetches is twenty-four thousand calls, with no third term, because the diff is computed locally and the deletes go to your own stores. Divide that by the incremental path's daily figure and the answer is twenty on the nose. The two quantities being divided came from unrelated places: one is a corpus size divided by a page size, the other is an observed daily change volume at the source. Nothing in the derivation of either touches the other, and they still land on a round multiplier.

What the multiplier licenses is the cadence decision and nothing beyond it. Running reconciliation continuously would mean spending twenty times the incremental path's API budget permanently, which is a different conversation with whoever owns the source's rate limits. The residual the sum hides is the enumeration misses, which arrive in the candidate set alongside genuine deletions and get refetched with them. On a healthy run they are a small fraction of the four thousand and the total does not move. On a bad run they are the reason the cap exists rather than a rounding error.

Reconciling one bucket at a time#

Where an hourly deletion guarantee is needed and a full enumeration every hour is out of the question, the usual compromise is a rolling reconciliation: hash each document ID into 24 buckets and reconcile one bucket per hour, so every document is checked once a day while each run touches 167,000 IDs and takes a few minutes. That only works if the enumeration endpoint can be filtered or paginated in a way that maps onto your buckets, which for a wiki organised by space usually means reconciling a rotation of spaces rather than a hash rotation. The tail risk changes shape with it, since a bucket that consistently fails leaves one twenty-fourth of the corpus unreconciled indefinitely, so the run needs to record per-bucket completion rather than a single global timestamp.

The bucket count is doing two jobs at once, which is what fixes it at twenty-four rather than any other number. It is the number of runs in a full cycle, so it divides the work — four million IDs over twenty-four runs is the hundred and sixty-seven thousand each run touches. It is also the number of hours in the cycle, so it sets the guarantee at once a day. Move it and both consequences move together: more buckets means shorter runs, and the same daily guarantee only if the runs also start more often. What does not move is the total work, since the same four million IDs are enumerated per cycle either way. A rolling reconciliation buys a smoother load and a per-bucket failure surface, and it buys no reduction in cost at all.

Change feeds, delta tokens and expiry#

A change feed does not retire reconciliation, which is the part teams get wrong in the other direction. Delta tokens expire — Microsoft Graph invalidates a token after a period of inactivity and answers with a 410 Gone that requires a full resync — and a feed consumer that crashes past its retention window silently misses whatever happened in the gap, which is the same problem chapter 1 described with a different name on it. A feed gives you a deletion latency measured in seconds and reconciliation gives you a proof that the two sets agree, so pipelines that hold themselves to a deletion SLA usually run both, with the reconciliation cadence relaxed to weekly because it is a backstop rather than the mechanism.

The two are not two implementations of one thing, and the difference lies in what each one's output means. A feed reports events, so its guarantee is a statement about what it saw. Reconciliation compares states, so its guarantee is a statement about what is true now — and a comparison of states is the only instrument of the two that can detect its own missed events. That is why the pair is not redundant, and why the relaxed cadence goes on the stronger instrument rather than the faster one.

The deletion window a team can publish#

A defensible public statement for this architecture is that content deleted at the source stops being retrievable within 24 hours, and that content subject to an erasure request — a legal demand, under a privacy regime such as GDPR, that every copy of a person's data be deleted — is removed from every store within 72 hours. Both numbers follow from the design: 24 hours is the reconciliation cadence plus the run duration plus a margin, and 72 hours is the time needed to fan the deletion out to the places a copy of the text lives.

The first of those two has to be read in the right direction, because read the other way it does not close. A daily cadence is 24 hours on its own, and a run of about an hour sits on top of that, so a cadence plus a duration plus a margin is already past the number a team is committing to. The commitment is a ceiling the components have to fit under, which means the cadence has to be set below it by at least the run duration and the margin. A run started every 24 hours, with a worst case of a deletion landing moments after one begins, does not meet a 24-hour promise; a run started more often does. The published number constrains the schedule rather than summing it.

That fan-out is larger than it looks, and topic 8.17 established the general shape of it — a delete that only touches the primary chunk table leaves reachable copies elsewhere. For an ingestion pipeline specifically, the copies are the raw blob in object storage that the fetcher cached, the parsed intermediate the chunker consumed, the embedding cache keyed by content hash, the broker's retained messages, the dead-letter records that chapter 5 is about to start writing, and any evaluation snapshot taken from the corpus. A dead-letter queue is the one that surprises people, because it stores whole document bodies by design and nobody thinks of it as a datastore.

The thing most likely to break the 24-hour number is the cap. A halt is a correct decision and it is also a silent one unless somebody is paged, and a reconciliation that halts on Friday evening, sits unreviewed over a weekend, and gets acknowledged on Monday has quietly extended the deletion window to 72 hours without anybody deciding to. The halt has to page rather than log, and the SLA you publish should be the one your on-call rotation actually supports.

There is a second-order limitation worth being honest about. Reconciliation proves that a document ID is absent from the source; it says nothing about a document that still exists but has had a paragraph removed, which is the shortened-revision case chapter 3 handled with generations, and it says nothing about content that was never supposed to be ingested. Neither soft deletion nor reconciliation is erasure in the regulatory sense until the hard delete runs and the derived stores are covered, so the deleted_at marker is an availability control rather than a compliance one.

Read that limitation against the set algebra and it is the same boundary approached from the other side. Reconciliation computes a difference of ID sets, so every question it can answer is a question about membership. Whether a document's contents still ought to be there is not a membership question, and no cadence of reconciliation converts it into one.

A deletion is not a small edit, and the difference is structural rather than one of degree. An edit changes a document's contents and leaves it inside the set the source will report on; a deletion removes it from that set, and the incremental path has no operator that reads outside the set it is given. Every mechanism that closes the gap does so by obtaining the deleted IDs from somewhere the updated_after query cannot reach — a feed that reports removals as events, a replication stream carrying the old primary key, or an enumeration that hands over the whole current set so the difference can be taken locally.

Everything so far assumes documents make it through the pipeline. Between three and five percent of them do not.