Format Coverage Ledger

format-coverage-ledger

Template skill (doc 09, doc 10, doc 02, complements source-extraction-coverage). Worked example: docs/case-studies/format-coverage-audit.md — a 53-document mixed-office archive scored a mean 63.8% text-only completeness, with diagram decks and formula spreadsheets the most under-captured. Extended worked example: docs/case-studies/structured-data-geospatial-coverage-sweep.md — the same coverage idea applied to CSV / solver / geospatial sources, where a text-readable file is shown to be not an answer-bearing one. Each office/email format hides its most valuable content in a layer that a naive text dump silently discards. A faithful extract is not a complete one. This skill pairs a deterministic per-format lane with an explicit coverage ledger that names what was not captured, so the gap is recorded against the page instead of being discovered later as a surprise.

Trigger

/format-coverage-ledger <path> [--format auto|docx|xlsx|msg|pdf|pptx]

Preconditions

1. The corpus root is referenced via an env var (e.g. $CORPUS_ROOT), so no host mount path is ever committed; the extractor reads $ROOT/<relpath>.
2. Format is detected by content/structure, not filename (names lie).
3. Each batch carries a manifest; one reusable extractor, per-batch manifests.

Steps

1. Route to the deterministic lane (one extractor, format-dispatched):

   • docx → txt (python-docx): paragraphs + tables.
   • xlsx → csv per sheet (openpyxl), data_only=True so cells hold the computed values — note this means formulas are not captured.
   • csv/delimited → probed table with resources/csv_dialect_probe.py: delimiter, row widths, ragged rows, numeric-column hints, content digest, and convention-sidecar validation.
   • msg → txt thread (MSG reader): the message thread text.
   • pdf → txt: deterministic text layer.
   • pptx → txt: slide text + tables.

2. Record the coverage ledger (mandatory). In the page (a completeness field or a coverage/status section — the campaign used a body status section), state per this format what was captured AND what was not, from the text/CSV-lane known-loss table:

   Format	Captured (text/CSV lane)	KNOWN loss of this lane (record it)
   xlsx	computed cell values (data_only)	formulas, named ranges, charts/plots
   csv/delimited	parsed rows, delimiter, field counts, content digest	units, sign conventions, coordinate frames, producer quirks unless sidecar is present
   pptx	slide-shape text + tables	diagrams, plots, drawn figures, speaker notes (often the engineering content)
   msg	message thread text	attachments (dropped)
   docx	text + tables	embedded images/figures
   pdf	text layer	images/figures/scanned regions
   solver output listing (ASCII)	result tables in the text block	nothing if it is a listing — but a same-extension file may be a run-script (report-definition that generates results elsewhere); classify by first lines, not suffix
   solver export (.dat/.txt)	parsed values if plain ASCII	UTF-16 / binary exports read as binary (NUL bytes) — needs an encoding probe + transcode before any text lane touches it
   bare-geometry point file (x y z)	the point coordinates	units / datum / sign convention (not in-band) → resolve to a sidecar before promoting past partial
   KML	geospatial text (placemarks, tracks)	a .kmz is zipped — needs unzip first; and the name (e.g. "course") may misstate what the geometry is

   (These are losses of this lane, not of the libraries — openpyxl can read formulas, extract-msg can read attachments, etc.; a richer lane recovers them.)

   Two richer lanes are now validated (pilot campaign, independently verified): xlsx formulas — a second data_only=False load emits a per-sheet formulas CSV only where formulas exist; and msg attachments — inventory-then-recurse per GP-40: every attachment recorded (name + sha256 + size) in the parent extract, format-lane attachments recursed through the same extractors as <parent>__att-<slug>.*, images inventory-only. Treat a container format (email) as a distinct ledger row: its lane is not complete until nested content is accounted for.

3. Provenance on every value. Each extracted value carries corpus-relative-path + sha256 (the source pointer). Raw binary never committed — derived text/CSV + pointer only.

4. Flag the gap as backlog, not done. A page whose lost layer is where the value lives (e.g. an xlsx that is a calculation tool → formulas lost; a pptx that is engineering plots → diagrams lost) is marked completeness: partial and queued for a richer lane (formula-graph extraction, image/diagram capture, attachment harvesting), not silently shipped as complete.

5. Carry epic-level wave fields for ACE batches. For ACE ingestion waves, the ledger row also records method_issue, skill_group, expected_useful_ingestion_range, % ingested success, difficulty_rank, review_status, and implementation_ready. % ingested success uses successful_routed_items / eligible_candidate_items * 100; hard exclusions are reported separately as % excluded.

6. For ACE wave 2 / issue #53, keep classifier rows non-durable. The spreadsheet/CSV validator may classify csv_table, delimited_table, ragged_delimited, data_workbook, calculation_workbook, report_workbook, and excluded_workbook, but target paths, retrieval metadata, lifecycle state, persistent metrics, and private sidecars belong to the #61 durable-output workflow and must not be smuggled into #53 rows.

Verification

• Every extracted page declares a coverage ledger naming the lane's known loss; a reviewer rejects a page that omits it (a frontmatter-field validator is the L2 hardening to add — the pilot campaign enforced this by review, not yet a check).
• No host mount path appears in any committed file (the corpus root is an env var); grep the diff to confirm.
• Every value has a corpus-relative-path + sha256 pointer; raw binary absent.
• ACE wave ledgers include method issue, skill group, expected useful ingestion range, % ingested success, difficulty rank, review status, and implementation readiness.
• CSV/delimited ACE wave-2 fixtures pass uv run python scripts/validate_ace_wave2_spreadsheet_csv.py and carry a convention sidecar before numeric engineering data is considered usable.

Cleanup

• Raw binary stays in the temp/off-repo location; only derived parts + pointers persist. Manifests are not committed as corpus content.

Incident appendix