File Catalog
The unstructured half. Crawl a share, catalogue what is there, extract what is useful.
File Catalog — the unstructured data component
Component documentation · Data Wrangler v4 Written against the code as of 8 August 2026 and the measured runs of 7–8 August. Timings and counts are from real runs over 1,055 files, not estimates.
1 · What the File Catalog is for
A company’s subsurface data is mostly not in a database. It is in folders — decades of scout tickets, end-of-well reports, casing records, LAS files, SEG-Y volumes, shapefiles, spreadsheets — on a shared drive nobody has fully read. The File Catalog is the component that goes and looks.
It does four things, and they are separable on purpose:
Crawls a directory tree and records what is there
Reads each file far enough to say what it is and which well or survey it concerns
Extracts the data inside it
Files the documents themselves into a curated vault, organised by well and survey
Loads the extracted data into DataView, but only what passes a stated test
The last two are optional and independent. A customer can crawl and read without a single row entering the database — often the more urgent question is simply what do we have — and can vault the documents without loading them, or load without vaulting.
Boundaries with the other three components. The Bulk Tabular Loader owns CSV and Excel; those extensions are deliberately excluded from the crawl (see 3.1). The Document Assistant owns shape recognition — the File Catalog calls the recogniser but does not define shapes or vocabulary. DataView is the destination; the File Catalog never writes dv_* directly, only through promote.
2 · The inventory
Everything hangs off one table: file_catalog.GLOBAL_FILE_CATALOG, one row per file. It carries identity (path, name, extension, size, modified date), content fingerprints, what the file was found to be, which well it matched, and every state the pipeline records as it works.
2.1 INVENTORY_ID
INVENTORY_ID is a SHA-1 of the file’s path. It is the identity that ties the whole system together: capture stamps it on every row extracted from that file, promote carries it into dv_*, and a single join answers which document did this number come from — the question the whole product exists to make answerable.
Two consequences follow from keying on the path rather than the content:
The same file in two folders is two entries. Detectable via the content fingerprint (FILE_HASH / DUPLICATE_GROUP), which is recorded precisely so duplicates can be found after the fact.
A path key still resolves when the file changes. Correct and re-save a document and the entry survives, reporting a changed hash — which is more useful than a content key that silently dangles. Content identity is recorded alongside; it just isn’t the key.
The choice is also practical: a path hash is computable in T-SQL (HASHBYTES('SHA1', UPPER(path))), a content hash is not, and naming a file by its content means reading every byte just to identify it.
2.2 The path must be canonical
Windows collapses repeated separators, so a root pasted as C:\\Users\\perry\\docs opens exactly the same folder as C:\Users\perry\docs. The scan runs perfectly and finds every file — but str(Path(...)) keeps the spelling it was given, and the id is a hash of that string.
This happened: 1,050 catalog rows for 525 PDFs, one set from a run whose root had doubled separators and one from a later run that doubled them again. Nothing errored, because as far as the operating system was concerned nothing was wrong. Every count in every report was doubled, and rows stamped under one spelling could never resolve against an entry keyed by the other.
Fixed in the File Manager scan by normalising at the point the id is minted, and defended at the UI by a _canon_root on the scan-root box that strips quotes, a leading &, and expands variables while preserving a UNC prefix.
It is not fixed everywhere. The pipeline has its own scan, and that one still hashes the raw string: paths come from os.scandir on whatever root it was handed, and the id function uppercases without normalising. A run started from the page is protected by _canon_root; a run started from the command line is not. The correct place for this is the minting site, not the UI — which is what the File Manager scan already does and the pipeline scan does not.
3 · The pipeline, in order
One run, orchestrated by run_pipeline. Each stage is independently switchable, and each records its own timing.
scan → extract → enrich → triage → capture → [deep] → [vault] → promote → report
3.1 Scan
Walks the tree, hashes, and MERGEs into GLOBAL_FILE_CATALOG. New files are inserted, changed files updated, unchanged files left alone.
What gets scanned is decided by extension group: Well Logs (.las .dlis .dis .lis), Seismic (.segy .sgy .seg .p190 .p90 .p1), Spatial (.shp .geojson .gdb .kml .kmz), Documents (.pdf .docx .doc).
Tabular formats are excluded deliberately. .csv .tsv .xlsx .xls .xlsm .xlsb belong to the Bulk Tabular Loader, which loads them with mapping and FK resolution. The File Catalog has no extractor for them, so inventorying one produces a row that can never drain — it sits in “pending” forever and makes a finished run look unfinished. Typing one into the Formats-to-scan box now gets a redirect to the right tool rather than a silent scan.
A consequence worth stating plainly: the file count on disk and the inventoried count will differ, and that is correct. On one test tree, 347 files on disk against 291 inventoried — the 56 being shapefile sidecars (.dbf/.shx/.prj/.cpg; only .shp is scanned), excluded CSVs, and unsupported types.
Measured: 1,055 files across 4 folders in 1.6–3.7 seconds.
3.2 Extract
A process pool — six workers by default — opens each file and pulls out its header-level facts: well name, UWI, operator, dates, depths, and for seismic the survey geometry and CRS. Results land in FILE_WELL_HEADER and FILE_SEIS_HEADER.
Measured: 1,055 files in 144–165s. Parse cost by type: PDF 0.23s each (525 files), LAS 0.10s (438), DOCX 0.05s (92).
Extract is write-bound, not parse-bound, and that took instrumentation to establish rather than reasoning. Of 144s, header_write is 103s (72%), parse_wait 33s (23%), claim_query 8s (5%). The pool parallelises fine — 179 worker-seconds became 33s of wall time on six workers.
The write is a per-row MERGE into FILE_WELL_HEADER, one statement per file, and it is deliberate and it is also the fast form. Batching it with a multi-row VALUES source was tried and made it worse — 136s to 178s — because a multi-row source changes the plan the optimiser picks, while the single-row form seeks. The batched version is still in the code, unused, with the measurement in its comment, so the next person can read why it loses before spending an afternoon on it. The original per-row form also avoids a fast_executemany truncation trap where string buffers are sized from the first row.
3.3 Enrich
Resolves what the documents couldn’t. Three passes against the master well header reference — 3.9 million wells from state and federal agencies, shipped with the product:
Curate UWI14, the persisted 14-character key
Resolve missing UWIs by joining what the document did state (well name, operator, field, county, state) against the master, corroborated by total depth and spud date. A shared well name alone never auto-fills.
Fill blank attributes on headers that identify a well but say little else
Measured: 14s. UWI14 valid on 921 files, 974 blank-attribute fills, and 74 files given coordinates they did not carry.
Those coordinates matter more than they look. Promote holds any well without a surface location, so a backfilled coordinate is the difference between a document’s contents landing and waiting indefinitely.
3.4 Triage
Normalises keys, cross-fills UWIs between files that describe the same well, attempts a reference fill, and scores each file into a tier.
Measured: 9–12s. 994 HIGH, 22 LOW, 39 REVIEW.
The tier is a routing decision, not a quality judgement — REVIEW means a person should look, not that the file is bad.
3.5 Capture — the stage with two lanes
This is where the data inside the document is read, and it is the most architecturally interesting stage.
Lane A — bulk BCP, for LAS and SEG-Y. These are high-volume and uniform. bcp_capture peels them off, parses in its own pool, aggregates into per-table buckets, and bulk-loads. Roughly 40 files/sec — 6.6× the per-file path, about three minutes for 7,326 LAS files.
Lane B — the process pool plus the recogniser, for documents. PDF, DOCX and the rest go to a general pool. Each worker builds one pack and recogniser per process, reads the tables, identifies each by shape, and maps its columns to a target.
The two lanes exist because they are execution modes, not duplicate implementations. Funnelling bulk LAS through the per-file path would throw away the throughput; funnelling a scout ticket through BCP would gain nothing.
Lane A also gets something Lane B structurally cannot: because it aggregates before loading, it can reconcile keys across a batch. A well with two LAS files gets LOG_<uwi> and LOG_<uwi>_2. A strictly per-file worker cannot see which files share a well, so it cannot assign the suffix and the second log would be silently skipped.
Measured: 617 documents, 10,006 rows captured from 592 of them, 37s. Writes batched at 100 documents — 7 flushes for 617 files.
Batching that write was worth doing: insert time went 124.5s to 8.1s, 1,907 calls to 149. The reason is that write cost is per call, not per row — measured at 1 row 59.6ms, 500 rows 221.4ms — and it scales with column count. The fix for a slow write is always fewer, bigger statements.
A batch failure does not cost the batch: a failing table retries one call per document, which is exactly the granularity the unbatched path had.
3.6 Vault — optional, and it is the answer to “the drive keeps moving”
Cataloguing tells you what you have. The vault protects it. Documents on a shared drive get moved, renamed and deleted by people who have no idea anything depends on them, and a catalog entry keyed on a path is only as durable as the path.
The vault copies qualifying files into a curated tree, filed by what they are about rather than by whoever happened to save them:
Wells <vault>\<COUNTRY>\<STATE>\<UWI14>\<WELL_NAME>\<file> Seismic <vault>\<COUNTRY>\<STATE>\<2D|3D>\<SURVEY_NAME>\<file>
Copies, never moves — the shared drive is untouched, and nobody’s workflow breaks because Data Wrangler ran.
Several details in it are worth knowing, because each encodes a decision:
It runs after enrichment, and must. It keys off the curated FILE_WELL_HEADER.UWI14, not GLOBAL_FILE_CATALOG.MATCHED_UWI — enrichment writes the first and never updates the second. Run it earlier and the well-keyed files simply don’t qualify.
A valid well key is the only test for wells. The catalog score is ignored. A document that identifies its well belongs in that well’s folder regardless of how much else was extracted from it — filing and extraction are different jobs.
Seismic needs a survey name and a seismic extension. The extension gate exists to keep mis-catalogued GIS files out of the seismic bucket.
It never guesses. No country becomes the configured default; no state becomes _NoState; a volume with no clear 2D/3D marker goes to _UnknownDim. A folder named for a guess is worse than one named for the absence.
Sidecars travel with their parent. A .shp without its .dbf and .prj is not a shapefile, it is a file. The vault carries the whole set.
Copies are idempotent. A destination that already exists at the same size is skipped; a name clash at a different size gets a numbered suffix rather than overwriting. A source that has moved or vanished since the scan is reported, not fatal.
The result is a tree a geologist can navigate without the application — which matters more than it sounds. It is the part of the output that survives the software.
3.7 Promote
Moves rows from cat_* into dv_*. Covered in the DataView document; the parts that matter here are in §5.
Measured: 8,749 eligible, 8,484 moved, 12s on a warm metadata cache.
3.8 Report
A markdown run report plus rollups. One design rule, learned the hard way: a run report describes the run. A rollup that describes the database — every file ever catalogued, not the ones this run touched — was costing 75 seconds of every run and feeding one section. It is now opt-in, and when off the section still prints, saying it was skipped and why. Dropping it silently would read as “nothing landed”, the opposite of the truth.
Measured: 75s → 0.2s.
4 · What the whole run costs
Full corpus, after a catalog clear, 8 August:
218s total · 1,055 files · 617 documents · 10,006 rows captured · 8,484 promoted extract 144s · capture 37s · enrich 13s · promote 13s · triage 9s · scan 2s
For context, the same corpus took 486s a week earlier. The path down was 486 → 330 → 261 → 235 → 218, and every step came from measurement rather than reasoning — seven consecutive theories about where the time went were wrong, including one that was shipped and reverted.
Run-to-run variance is ±20%. header_write has measured 103, 111, 136 and 178 seconds, two of those on identical code. No change worth less than about 25 seconds can be judged from a single run.
5 · The staging mirror, and what it means when it’s full
Documents are catalogued before the well exists. A scout ticket arrives with tops, casing and completion details for a well that may not be in dv_well yet. file_catalog.cat_* holds that: every dv_* column, same name and type, but always nullable and with no foreign keys — capture is tolerant and parentless — plus provenance columns and a UWI helper.
The mirrors are generated from the model rather than written by hand, so the two cannot drift in shape. Promote then moves rows by column-name intersection, with no hand-maintained column map.
cat_ is a drain, not a record. Promote deletes rows as it lifts them, so:
An empty cat_ table with a full dv_ table is what success looks like
A full cat_ table means rows are held, not that capture is working
“Captured” must be tested as cat_ OR dv_, because after a successful promote the cat_ side is empty
Rows are held, never discarded, by three gates: a well with no surface coordinate, a detail row whose well isn’t in dv_well yet, and a coded value not present in its reference table. Each is reported by name and count. Improving the reference vocabulary or loading the missing header promotes the held rows on the next run with no re-extraction.
6 · Knowing what actually happened
This turned out to be harder than doing the work, and it produced the component’s sharpest lesson.
Three reports had three different definitions of “landed.” One counted dv_prod_entity and dv_well_dir_srvy_hdr but not dv_well; another counted the opposite pair; a third had its own logic. Same file, two reports, two answers.
Worse, the per-file stamps lie by omission. Documents get PROMOTED_AT stamped; logs and seismic never did, because they travel a different route. So a report reading the stamp alone said logs and seismic were never promoted when their data was in dv_* and queryable — the files most likely to have worked were the ones most likely to look like they had failed.
promotion_lineage.py fixes this by defining the test once: every dv_ detail table carries the INVENTORY_ID of the file its rows came from, so a file is promoted when its id appears in any of them. Twelve entries, each naming its cat_ and dv_ table, and every table probed for existence and for the column before it enters a query — so the module runs unchanged against a partially built database and a missing table narrows the answer rather than raising.
Adding a dv_ table used to mean remembering two reports. Now it means editing one tuple.
7 · The traps
Each is paired with the failure that taught it.
A path is not its spelling. Doubled separators produced 1,050 catalog rows for 525 files, silently. Canonicalise where the id is minted, not only at the UI.
One identity, one function. Three live functions currently mint INVENTORY_ID — the File Manager scan hashes the path in its original case, the pipeline scan hashes it uppercased, and the Data Assistant hashes it uppercased as UTF-16-LE. All produce 40 hex characters, all look identical in the table, none of them join. This database has only ever been scanned by one path, so the divergence is latent — but it is the same failure class as the doubled backslashes, in code rather than in a pasted path. It should collapse to one shared function.
“0 cataloged” usually means “already cataloged.” CAPTURED_HASH makes re-capture idempotent, so a re-run of unchanged files reports zeros. This has been mistaken for a regression more than once. A clear resets the stamps precisely so a cleared catalog genuinely re-processes.
A temp table is a session-scoped assumption. #doc_ids worked from the CLI, where one cursor runs the whole job, and failed from the page, where statements run on different pooled connections. Anything a Streamlit page may call must carry its working set in Python or in a real table.
A delete must not inherit a definition a later feature invalidated. The catalog clear scoped dv_* deletions to “has an INVENTORY_ID”, which meant “came from the catalog” until the Bulk Tabular Loader started stamping ids on its own rows. After that the predicate matched everything — one click from deleting 6,737 bulk-loaded production rows while labelling them “catalog rows”. Now scoped to ids whose file is a document.
Ordering matters when a tool empties its own evidence. The clear removes GLOBAL_FILE_CATALOG in the same transaction as the dv_* deletes, so the document ids must be captured first — by the time those deletes run, the catalog can no longer say which ids were documents.
A feature that defaults off is a feature nobody runs. The recogniser path was complete and wired since July and defaulted to recognise=False. Turning it on took capture from 3 files to 8 and from a few hundred rows to 10,006. Scout tickets, casing records and end-of-well reports had been producing nothing at all, silently, because the older per-format extractors have no handler for them.
The test data must be one generation. Documents generated against one well set, loaded against a database holding a different set, produce a total promote stall with no error anywhere — every detail row held on the “does this well exist” gate. The gate behaved perfectly; the data didn’t match.
SUM(CASE WHEN EXISTS (...)) is illegal in SQL Server. Rewrite as a CTE plus a LEFT JOIN. This has bitten twice.
8 · What earns a place in the database
The honest version of the product promise is not “it reads everything.” The first real folder disproves that. It is: it reads most things, tells you exactly what it could not, and makes fixing that cheap and permanent.
The design that supports the last clause rests on a distinction worth stating: wrong and missing are not the same failure.
Missing is recoverable and visible — the value sits in the extras, the census names it, nothing in the database is false. Wrong is invisible and permanent: permeability filed as measured depth, net pay overwriting gross thickness, a combined figure in the oil column. Nobody queries those and sees an error; they see a number. All three were real, all found in a single day, all silently true for weeks.
So the admission test is strict about wrongness and tolerant of incompleteness. Refusing a table because one column went unclaimed withholds good data to guard against the lesser failure, and makes the gate unreachable.
The test is not “is this perfect” — the system cannot certify that a number is correct and should never claim to. It is “are the conditions present under which we have historically been wrong”: the table identified cleanly, no rival shape explains more of it, no two fields landed in one target column, the header looks like a header, types fit, row counts are conserved.
Each failure names itself, and the work queue groups by reason rather than by document — one vocabulary change clears thirty tables. Held tables are captured, stored and re-testable, so improving the vocabulary promotes them with no re-extraction.
Status: designed and specified, not built. The store already carries the review_status machinery this would set; nothing reads it yet.
9 · Open items
Collapse the three INVENTORY_ID functions to one. Latent today, and the identity everything joins on.
The catalog clear behaves differently depending on how it’s invoked. Its import of the table allowlist resolves from the repo root; run as a script it silently falls back to a stale hardcoded list that omits casing and perforation. So the CLI and the button clear different sets.
No shape emits perforation or field rows. Both mirrors exist and both stay empty. Either the shapes get written — if the source documents carry those sections — or the mirrors go.
Two documents lose a row each to a date conversion, every run. The batch falls back to per-document and drops two rather than the whole batch, which is the guard working, but those headers don’t land.
LAS is parsed in three places and a fix for mislabelled version headers landed in only one of them.
The pipeline reorder is parked. Extract and the recogniser both open the same 617 documents; scoping extract to the formats only it can read would remove roughly 100 seconds. Perry’s call: “I feel it may be too risky.” Correct — three quiet dependencies for ~100s on a run already 55% faster than it started.