Nine percent of the 3.2 million files were close to empty, and every one of them was in the index. Nearly all were scanned PDFs averaging under 50 characters a page, embedded like everything else. The firm behind that figure is invented: a research group at a place I’m calling Averill & Stone, loading files from a document management system, two shared drives and a contracts repository.
Their complaint was about something else. The assistant kept quoting superseded contracts. The retriever was finding the best match for each question, and there were four best matches, three of them old.
People respond to that kind of complaint by tuning retrieval. They add BM25 next to the vectors, add a reranker, change the chunk size. Hybrid search is worth having, and I’d still do it second. At a few million documents the answer quality is mostly settled before a query runs, by three things nobody has a number for: how many files parsed into garbage, how many are near-copies of each other, and how long an edited document takes to reach the index.
So I’ll go through it in the order I’d build it, using that invented team. The other numbers are made up too. If every figure changed, I’d still keep the order.
A row per document before anything else
Their first version was a script. Walk the sources, extract text, chunk, embed, insert. It worked, and it had no memory. Nothing recorded whether a file had been seen, which parser handled it, or whether the chunks in the index still matched the file.
The first change is a manifest: one row per document, written when a connector first sees the file and updated by every stage after it.
The doc_id deserves some care. It can’t be the path, because people move files. It can’t be the content hash, because then an edit creates a new document and the old one lingers. I’d hash the connector name together with the source system’s own identifier for the file. Content gets its own column.
CREATE TABLE doc_manifest (
doc_id text PRIMARY KEY, -- hash of source + the source's own file id
source text NOT NULL, -- connector name
source_uri text NOT NULL,
source_modified_at timestamptz NOT NULL,
first_seen_at timestamptz NOT NULL DEFAULT now(),
content_hash text, -- sha256 of the normalized extracted text
parser text,
parse_score numeric(4,3), -- 0 to 1, set by the quality gate
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'quarantined', 'duplicate', 'indexed')),
status_at timestamptz NOT NULL DEFAULT now(),
quarantine_reason text, -- which signal failed the gate
duplicate_of text REFERENCES doc_manifest (doc_id),
first_indexed_at timestamptz,
indexed_at timestamptz,
indexed_hash text -- content_hash the index was built from
);
CREATE INDEX doc_manifest_content_hash_idx ON doc_manifest (content_hash);
Chunks and vectors carry doc_id. That one foreign key is what lets you delete or replace a document’s chunks later without guessing.
The gate, and what it caught
A PDF parser almost never fails. It returns something. For a scanned page with no text layer, that something is a page number and a stray header, and the pipeline happily embeds it.
The gate sits right after the parser and scores each document on cheap signals: characters per page, the share of characters that are letters, how much of the text is one line repeated. Below the threshold, the document goes to quarantined with the failing signal recorded, and stops there, kept but not indexed.
That’s where the 9% at the top came from. The first full run through the gate put about 288,000 documents in quarantine, nearly all of them scans. Before that, they sat in the index as tiny chunks that matched nothing useful, or matched by accident.
Quarantine is a queue with a reason attached. The scanned PDFs got routed to an OCR parser and scored again, and a third of them came back clean. The rest stayed out, visible in a count.
The same contract, four times
Exact duplicates are easy. Two rows with the same content_hash are the same text, and one of them gets duplicate_of pointing at the other.
Near-duplicates are where the wrong-version answers come from: the same contract saved again with a new signature page. The hashes differ and the text is 98% identical. The team added a similarity pass over word shingles with MinHash and grouped anything above a high overlap threshold.
Then someone has to pick which member of a group gets indexed. They kept the most recently modified one and marked the rest duplicate, 22% of the corpus. That rule is wrong for some corpora. If a draft and an executed copy are near-identical, you want the executed one regardless of dates, and that takes a rule per source. I’d start with newest-wins and write the exceptions down as they show up.
With duplicates collapsed and the unrecovered scans held back, the index was 28% smaller. Four chances to quote the old contract became one.
Edits that took four days
The original refresh was a full rebuild, twice a week, taking most of a day. An edit made just after a run started waited for the next one to finish: about four days.
With the manifest, the refresh becomes the comparison I described for embeddings, one level up: per document, against the text the index was built from. A newer source_modified_at triggers a reparse, and if content_hash no longer equals indexed_hash, that one document is re-chunked, re-embedded and swapped in. Unchanged text costs nothing.
Embedding runs on two lanes. The backfill and any model change go through OpenAI’s Batch API: half price, up to 24 hours, fine for three million documents with nowhere to be. Daily changes use the synchronous endpoint, because a batch window would put a day of lag right back. On pgvector with an HNSW index, replacing a document is a delete and an insert keyed on doc_id inside one transaction.
The report that replaced the arguments
Every stage writes its outcome to the manifest, so the daily report is one query.
SELECT
d.day AS report_date,
count(*) FILTER (WHERE m.first_seen_at::date = d.day) AS ingested,
count(*) FILTER (WHERE m.status = 'quarantined'
AND m.status_at::date = d.day) AS quarantined,
count(*) FILTER (WHERE m.status = 'duplicate'
AND m.status_at::date = d.day) AS deduplicated,
count(*) FILTER (WHERE m.indexed_at::date = d.day
AND m.first_indexed_at < m.indexed_at) AS reindexed,
count(*) FILTER (WHERE m.status = 'indexed'
AND m.indexed_hash IS DISTINCT FROM m.content_hash) AS waiting,
round((percentile_cont(0.95) WITHIN GROUP (
ORDER BY extract(epoch FROM m.indexed_at
- greatest(m.source_modified_at, m.first_seen_at)) / 3600.0
) FILTER (WHERE m.indexed_at::date = d.day))::numeric, 1) AS lag_p95_hours
FROM doc_manifest m
CROSS JOIN (SELECT current_date - 1 AS day) d
GROUP BY d.day;
Lag runs from the later of the edit and the connector’s first sighting, so a ten-year-old PDF loaded yesterday isn’t ten years late.
waiting is the one I’d put an alert on. It counts documents whose text has changed since the index last saw them. If it climbs, the lag number is about to.
The team also kept a 200-question check with known correct sources. Before the gate and the grouping, 31 answers cited a superseded version of a document. After, 6 did. Nobody touched the retriever between those two runs.
Where this still leaks
Those 6 are worth reading one by one. In this story they were amendments: short documents that change one clause of a long one, similar to nothing and superseding part of something. Duplicate grouping can’t see that relationship. It needs a link between documents that the source system may or may not record, and I don’t have a general answer for it.
I’d also expect the quarantine threshold to be wrong on the first try. A table-heavy spreadsheet export scores badly on “share of letters” and is perfectly good content. Sample fifty quarantined documents a week and read them. It’s the only way to find out what your gate is rejecting.