One Document Type, a Million Files: Structured Extraction into the SQL Table RAG Queries

Twelve years of renewal notices for one motor product. Every purchase order a procurement team has issued since the ERP went in. The monthly statements of one fund. Each of those is thousands of files, and each file is the same document with different values in it.

The count is not what matters. A hundred purchase orders behave like a hundred thousand of them: the questions people ask are about which ones, not about where inside one of them.

Two things follow. Documents that came out of one process share a page-one layout, so the same value sits in roughly the same place every time. And the values themselves repeat: eleven products, forty-odd clients, four statuses. A column here has a small, stable set of values, which is what makes it worth filtering on.

A field like effective_date does not come from the text. It comes from whoever designed the form. A template has slots, a form has boxes, a back-office screen has required fields, and the PDF is that screen printed on paper.

Nobody wrote that decision down. There is no data dictionary, and the person who designed the form left in 2014. What survives is the practice: the fields people fill in, and the ones they check before answering a customer. That knowledge sits with the handlers, so getting it out is a conversation rather than an extraction job.

This is where the shape parts company with the folder in Article 14B (a collection whose documents share no field at all). There, no such decision was ever made, so there is nothing upstream to recover.

One hour, two people, and a list of six to ten fields at the end of it.

Ask the person who handles the documents, not the manager who owns the process. The manager describes the process as designed. The handler knows which field is blank on the older files and which one gets typed in wrong.

Ask two handlers, separately. The second hour is there to catch one thing: a field they both name the same way and mean differently.

Four questions, in this order. Each one is easier to answer once the previous one has been.

What comes out is a list of names in the handler’s own words. Types and definitions come later.

Two signals, and both are about the answer rather than about the field.

It comes back without a pause. Ask what someone filters on and a real column arrives at conversational speed, with an example attached: “client, obviously, and the effective date, because that is how we know which version applies.” Take a field that needs thinking about. Nobody filters on it today, so nobody will keep it up to date either.

Two handlers name it the same way. Same word, same meaning, same box on the page. The first signal cannot do this on its own, because a field can be named instantly by both handlers and still mean two different things to them.

Neither signal needs a model or a sample. Both are settled in the room.

Two meanings in one word. One handler says date and means the day the contract was signed. The other says date and means the day the cover starts. Both answered fast, both used the same word, and the column that comes out of it holds two quantities mixed together. A query for policies in force in March then drops the rows carrying a signature date, and what comes back still looks like an answer. A missing field is safer, because a filter trusts what is there.

Empty on half the documents. A field filled on 1,900 rows out of 5,000 is a subset with no name. Sometimes the subset is real, and then say so: the field belongs to a subtype, and the subtype is the column you were missing. Sometimes it is just a field that arrived in 2021, which is fine, as long as the filter reads a null as not recorded yet rather than as no.

A judgement rather than a fact. “Is this client difficult?” is real knowledge and it is not printed anywhere. Ask a model to infer it and you get an opinion with a citation attached. Park those fields. Some come back later as something the business writes into the index by hand, which is not the same thing as extraction.

There is an alternative to that hour, and it is popular for good reasons. Point a model at the corpus, let it pull out whatever entities and relations it finds, and a vocabulary comes back that nobody had to specify. On a collection where no business user can name a field, that is the only move available.

Here the trade is different. You pay a full extraction pass over five thousand documents to rediscover a list two people produce in an hour, and the vocabulary that comes back is one the business did not choose and cannot correct in place. Article 16 (where the corpus vocabulary comes from, curated against extracted) puts the two side by side on cost, audit trail and freshness. The ordering is what matters here. Ask first, and if the answer arrives in ten seconds, there is nothing left to discover.

The index has one job, and the quickest way to see it is to put it beside something every reader already uses.

A table of contents is not part of a document’s content, it is a map of it. Six lines stand in for thirty-four pages, and a question about exclusions lands on section 4 before a page is read.

The index does the same job one level up. It is not the content of the corpus either. Five columns stand in for five thousand files, and a question about one client’s fleet cover lands on one row before a file is opened.

Follow that comparison all the way, because it tells you what belongs in the index. A table of contents does not hold the text of section 4, it holds enough to decide whether section 4 is where to look. An index row holds enough to decide whether a document is a candidate. Once it starts holding answers it is a second copy of the corpus, and the second copy drifts.

Six fields, in the handler’s words, with types on them.

class PolicyIndexRow(BaseModel):
    """One row of the corpus index: one policy, six business fields."""
    doc_id: str              # the file, so an answer can cite where it came from
    policy_number: str       # what the business types into its own systems
    client_name: str         # the party the policy is written for
    product: str             # one of eleven, and the list never changes
    effective_date: date     # the day the cover starts
    renewal_date: date       # the day it lapses if nobody acts
    premium_eur: float       # the number the finance team filters on

Nothing in that declaration is a modelling decision. Every name came out of the interview. doc_id is the one field the business did not name, and it is there so an answer can point back at the file it was read from.

Whether the table stays this flat once amendments and versions arrive is the subject of Article 15A (the schema a corpus gets written into), which replaces the single sheet with three tables and two date columns. How each field gets filled comes with the articles after it.

Whether the preparation is worth its price is arithmetic. Here it is.

The single-document pipeline does not change. It now sits inside two phases that cost very different things.

Ingestion runs once per document, when the file arrives and before anyone has asked anything. It parses, fills the agreed columns, keeps a page reference for each value, and appends a row. It runs again only when the file changes.

Querying runs once per question. It reads the scope fields out of the question, filters the index down to the candidates, and runs the pipeline on what survives. Article 17 (querying a corpus, filter first and retrieve second) builds that half.

Ingestion is bounded by the number of documents. Querying is bounded by the number of questions, and that is the number that keeps going up.

A claims handler uses a system like this about fifty times a day. That is the order of magnitude we keep seeing, and the exact number does not change what follows. Over a working year it is roughly twelve thousand questions from one person, and a team of six puts it past seventy thousand.

Without preparation, every one of those questions is priced against the whole shelf. The system works out from scratch which documents are candidates, and it does that by comparing the question against passages from files that had no business being consulted.

With preparation, the question opens with a filter over a table and the pipeline runs on what comes through. The work per question stops tracking the size of the corpus.

Ingestion is paid once per document and querying once per question. So as soon as the number of questions over the system’s life reaches the same order as the number of documents, the prepared version is ahead on compute. It was ahead earlier than that on two other counts: the answer comes from a document somebody chose, and the filter is something a business user can read and argue with.

A document that gets read once, or never, does not justify a deep pass. Filling twelve columns on a one-off appendix costs more than reading the appendix when the question finally arrives, if it ever does.

So tier the work. A standard policy template consulted thousands of times over a portfolio’s life justifies a deep ingestion, with every field extracted and every cross-reference resolved. The long tail gets the cheap version, which here is the filename, the file metadata and the document type. A document can be promoted later, when its query count says it earned the deeper pass.

On a collection of one document type there is no schema to discover. There is a schema to write down, and it already exists in the practice of the people who handle the documents.

The preparation starts with an hour of conversation and two signals: a field named without a pause, and two people naming it the same way. What passes both goes into the index. What fails is a subtype, a judgement, or a word that means two things, and each of those is cheaper to catch in the room than after a pass over five thousand files.

Try the two signals on a folder of your own before writing any ingestion code. Ask the person who handles those files what they filter on, then ask a second one, and see how much of the schema comes back in the first ten seconds.

Article 14B (the folder whose documents share no field) covers the collection with nothing to put in the columns. Article 14D (case files, one bundle about a single entity) covers the one where a row of the table is a case rather than a document.

Earlier in the series:

What works, what breaks