# Bidlo production coverage framework

Prepared October 5, 2026. Proposal and read-only baseline; no production mutations or scheduled jobs created.

## What this should tell us

A reusable coverage map should answer three questions: **How much data can customers actually use? Where is it missing or uncertain? Which gaps share a source, owner, document format, or pipeline cause?** Start with Projects and everything needed to use them: owners, source postings, files, items, bids/prices, contractors and subcontractors, dates, locations, scope, and provenance.

Do not reduce this to one completeness score. A project can be usable as a bid opportunity while unusable for quantity-based pricing or subcontractor attribution.

### Read-only production baseline

The actual census below was executed October 5 against all active, shared base documents in the Project collection (`0950f3b8-5ff7-4867-9162-a9a30d034539`), excluding `deleted_at` rows and tenant-owned documents. Values excluded user/owner overlays; relations and their endpoints had to be active and shared and have the expected collection. These are **presence counts, not missing-data rates or evidence of accuracy**. Independent counts overlap.

| Observed presence | Count |
|---|---:|
| Active shared projects | 81,639 |
| Nonempty document `sources` array | 74,715 |
| At least one live File relation | 28,036 |
| At least one live Project Items relation | 25,836 |
| At least one live Subcontractors relation | 3,167 |
| Nonblank Bid Date value | 81,338 |
| Active shared Project Items documents | 1,233,712 |

The census does not establish how many projects ought to have items, files, or subcontractors. Break those denominators down by posted/awarded/cancelled/unknown lifecycle and source expectations before reporting gaps. It does not apply an archived-state exclusion: the inspected document schema has `deleted_at`, but no general `archived_at`; any archive/status semantics need a separate validated adapter.

Two bounded examples verified the exact fields/relations: first 1,000 active shared item IDs had 1,000 numeric-text Bid Quantities and no missing/blank quantities; first 100 project IDs with live known subs all had items, and one had no live item Contractor relation. Neither sample is random or representative. The latter is a review candidate, not a confirmed awarded-job defect. Numeric text alone proves neither positive/usable quantity nor source accuracy.

The earlier Texas striping audit reported 194 unsupported item/project joins within its reviewed sample (sum of its six unsupported/unrelated join classes). That motivates a provenance check; it is not a production-wide prevalence estimate. File availability in that audit did not prove item-to-source-page lineage.

## Architecture-wide foundation: collections → documents → fields

The foundation is a generic coverage audit across collections, documents and fields. Project-specific checks are additional checks layered on this foundation, not the starting point.

For every active collection and field, report:

- Population: eligible documents with a value, missing value, explicit blank, or conflicting values. Distinguish absent scalar values from absent relationship edges; preserve tenant/shared scope.
- Usability: malformed dates or numbers, invalid types, broken relationship targets, cardinality conflicts and incompatible units. A populated field is not automatically usable or accurate.
- Patterns: gaps grouped by source, owning agency, first-seen/ingestion date, parser version and ingestion run, retaining unknown attribution buckets.
- Expectations: required, optional, conditional, or not yet classified. Version those rules. Do not label every missing optional field a defect.

The primary coverage matrix uses collection and field as rows, with eligible document count, populated count/rate, usable count/rate, missing/blank count, invalid/conflicting count, unknown eligibility and check freshness. Drill into documents and source/owner/pipeline cohorts from each cell. Include rarely populated and newly introduced fields so coverage is not limited to a hand-picked project checklist.

Example: Projects → Bid Date → missing on X of Y eligible projects, followed by the sources and owners accounting for the gaps. Date presence, parseability and correctness are separate checks. Cancelled projects, sources without a deadline, legacy cohorts and fields introduced after ingestion need explicit eligibility decisions.

Collection/document-level checks also cover unclassified documents, duplicate identities, missing expected documents, stale records, and orphaned links. Field-level coverage must use stable collection/field IDs; field names alone are not unique. Source inventories remain necessary to measure documents never captured.

Implement the generic layer first using a collection/field registry and reusable type-aware check templates. Add customer-use-case rules and relationship checks—such as known subs with missing item contractor assignments—afterward. The existing production census and starter SQL below are project-scoped demonstrations, not a claim that every collection and field has already been audited.

## Coverage map and denominators

Make rows selectable by **source × owner × bid month × lifecycle**, with drilldowns for ingestion batch, parser version, document type, geography, and item scope. Civcast is a source; NTTA is an owning agency. Do not confuse either with database `owner_id`, which separates shared and tenant data.

Each cell displays eligible records, usable records, confirmed defects, review candidates, unknown eligibility, not applicable, and not evaluated. Include count and rate together. Define `usable_rate = usable / eligible` only when eligible > 0; never turn zero eligible into 100%. An unknown predicate must remain unknown, not silently disappear from the denominator. Publish unweighted project counts and item counts separately; a 2,000-item project must not dominate project coverage.

Keep four separate concepts:

- **Presence:** a value, relation, file, or row exists.
- **Usable completeness:** necessary fields and relationships satisfy a defined customer use case.
- **Accuracy:** data agrees with primary evidence. Structural tests alone cannot establish this.
- **External capture:** expected projects/documents from an independently enumerated source manifest were ingested. Missing external projects cannot be measured from internal projects alone.

| Customer use case / check | Eligible denominator | Pass or gap rule | Initial severity |
|---|---|---|---|
| Project identity and owner | In-scope public postings; owner applicability from source contract | Resolvable canonical posting ID, title, source, agency; unresolved/contradictory agency is review | Medium; high if wrong project |
| Bid opportunity dates | Posted projects whose source promises a bid deadline | Parseable date and appropriate timezone/date-only semantics; distinguish cancelled, no deadline, and unknown | High for incorrect active deadline |
| Document capture | Enumerated expected files for each posting/version | Discovered → fetched → stored → linked; valid file relation alone is only presence | Medium/high by document role |
| Item extraction | Projects with an available item schedule and extraction due under source SLA | Expected schedule processed, plausible item rows, completeness reconciled to source row count/totals | High for failed known schedule; unknown without manifest |
| Usable quantity | Item rows whose source schedule supplies a measurable quantity for this use case | Numeric, finite, context-appropriate sign, known quantity basis and unit; retain zero/negative adjustment semantics | High if used in pricing; medium otherwise |
| Unit and scope | Items requiring unit-based comparison | Valid unit relationship, compatible quantity basis, scope/specifications adequate; LS can be valid for total pricing while unsuitable for LF/SF comparison | High for incompatible units; unknown when unverified |
| Price coverage | Items and lifecycle with a published bid/award/estimate price expected | Amount/unit price, currency, price basis, contractor/bid/version, date; distinguish observed price from estimate | High if misleading; missing estimate often medium |
| Project-level known subs with no item assignment | Awarded projects with documented subs, applicable item schedule, source expected to provide attributable scope, and grace period passed | No assignments is candidate gap; missing source item-to-sub mapping is an evidence gap, not grounds to invent links | Medium candidate; high when source explicitly maps items |
| Partial contractor assignment | Items explicitly covered by supported subcontract scope on eligible projects | Correct contractor link plus scope evidence; count coverage within supported scope, not all project items | High for wrong attribution |
| Structural links | Active shared relations/values in mapped collections | Endpoints exist and are active, expected field/collection, correct direction, unique according to field cardinality | High for broken/wrong graph |
| Semantic project links | Item/project links used in reporting | Source posting/file/page supports attachment; shared subject keywords or file presence are insufficient | High confirmed; review if evidence absent |
| Duplicates | Canonical source posting/contract/version groups | Separate mirrors/addenda from distinct contracts and legitimate repeated line items; exact source keys versus fuzzy candidates | High if double-counted; medium candidate |
| Provenance | Facts/links used in quantitative or attribution reports | Posting, file checksum/version, page/table/row, extraction run, normalization/estimate method, verification state | Medium absent; high when unverifiable fact drives report |
| Freshness and reconciliation | Enabled source schedules with agreed refresh cadence | Due → successful fetch → discoveries → accepted writes → usable outputs reconciled, with lag allowance | High outage; review zero changes |
| Location | Projects expected to have physical work location | Usable geometry/address, confidence, consistent agency/source geography; solicitation office is not worksite | Medium/high by customer use |

Lifecycle and source capability are versioned rules, not guesses from a date. Known subs do not automatically prove award. A successful scraper with zero rows can mean no change; in the inspected seven-day window 569 of 1,078 runs reported zero rows, across 94 task names. This is an observation to reconcile with discovery manifests, not 569 failures.

## Finding shared causes

Use canonical source and agency IDs plus aliases. Resolve Project Source/Owner UUID-valued tags through their referenced collections. Keep explicit unknown/multiple/conflicting buckets. Preserve primary source and all contributing sources to avoid double counting mirrored postings. Use bid date, posting date, first-seen date, and ingestion date as separate dimensions; do not substitute ingestion month for bid month.

For every issue record store: check/version, project/item/relation IDs, eligibility reason, outcome, observed evidence IDs, source and agency, lifecycle, dates, stage, run, parser/schema version, severity, confidence, first/last seen, and review disposition. Do not persist full sensitive source documents in the dashboard.

Compare failure rate **within eligible cohorts** against peers with the same lifecycle/document type/age. Rank clusters by affected eligible projects, affected items, rate difference, sample size, and customer impact. Start with sources, then source × agency, then bid/ingestion month and parser version; label small cohorts inconclusive. Show uncertainty for sampled checks. Aggregate stable check signatures so one failed parser becomes one actionable cluster rather than thousands of unrelated tickets. Correlation proposes a cause; a source-page review or pipeline reproduction confirms it.

Suggested cause labels: discovery missing; download blocked; PDF/OCR failure; wrong table classification; field normalization; graph attachment; entity resolution; mirror deduplication; assignment not published; public source incomplete; provenance missing; source/lifecycle capability unknown. Public-source absence is distinct from pipeline loss.

## Implementation using the existing stack

Production uses EAV tables `app_documents`, `app_fields`, `app_fields_values`, and `app_fields_relations`; Units/Scope/Contractor are relations, not scalar text fields. Shared fields must be selected by validated IDs and `owner_id IS NULL`; names are not unique because tenant custom fields coexist. Exclude deleted fields/documents/values/relations and user overlays. Scope tenant-specific audits separately with their access rules.

Existing evidence surfaces are already useful: `app_fields_values_changelog` has `source_handle`, `scraper_handle`, `source_url`, `source_document_id`, `run_id`, and `item_id`; `scraper_runs` has task/run/status/timing/rows; schedules have cadence, enablement, last success, and aliases; source mappings resolve scraper labels; ingestion cursors track progress. Schema existence does not prove historical population. Profile each column before joining. Match run IDs using a verified mapping; do not assume the UUID run PK equals the changelog text run ID. Changelog records changed values, so no new changelog row does not itself imply a stale source. `app_documents.project_id` is null across this census; that alone is not a provenance defect because source identity may be held elsewhere.

Build versioned adapters that normalize these into project, item, relation, source-manifest, and run facts. Keep raw values and explicit observed/estimated/verified states. Add missing source manifest and page/table/row lineage at the ingestion boundary; do not fabricate historical lineage. Date normalization already has a writer-side precedent in `supabase/fixes/2026-08-26_normalize_ingestion_date_values.sql`; parser regressions should be caught before accepted writes.

Run SQL-first checks in a read-only worker using the existing ingestion scheduler where practical. Keep audit snapshots/results in a separate output store; this proposal creates no production tables. Use compact project snapshots with preaggregated relation/value facts, rather than repeatedly scanning 1.23 million items for every dashboard cell. Capture rule/adapter versions and snapshot cutoff. Schedule design is proposed, not enabled.

Store runs, per-entity results, cohort aggregates, and reviewed cases separately. Recompute changed project graphs after ingestion and perform a periodic full reconciliation to catch missing/deleted edges and unlogged changes. Handle late data and retain prior snapshots. Dashboard first view: usable coverage by use case; source/agency heatmap; biggest shared causes; new regressions; evidence drilldown. A reviewed case has confirmed/candidate/not-applicable/unknown status and evidence, not merely a red badge.

### Practical rollout

1. **Baseline and rule validation:** confirm collection/field/status mappings and source capabilities; run presence census, inspect provenance population and indices; agree eligibility with ingestion/product owners. Establish explicit capture scope, archived-state policy, SLAs, canonical identities, and sampling rules.
2. **First coverage release:** files/items, quantities/units, known-sub assignment candidates, duplicate mirrors, graph integrity, and provenance. Audit stratified samples across Civcast, NTTA, TxDOT and other high-volume sources; validate source pages and retain labelled false positives.
3. **Pipeline instrumentation:** expected posting/file/item manifests, stage counts, parser versions, and run lineage; reconcile source → discovered → fetched → extracted → linked → usable. Monitor scheduled sources that have no internal records too.
4. **Ongoing review:** baseline cohort history, flag meaningful absolute/rate regressions, route one cluster to the responsible stage owner, verify repaired records and new runs. Never auto-repair/delete or assign contractors based only on a check failure. Track unresolved eligible projects, time to recovery, and confirmed false-positive rate.

Read-only still consumes production resources: the baseline presence census took about 18 seconds. Use replica/off-peak reads, read-only transactions, statement timeouts, bounded batches, execution plans and existing indices; scale full checks only after measuring cost. Never call writer ingestion functions from the audit. Avoid unsanitized logs, credentials, signed URLs and personal contacts in exports; output IDs/counts and access-controlled evidence links. No raw extraction LLM/API calls are needed for baseline checks. Manual evidence verification/OCR, new telemetry storage, and external source enumeration have separate budgets.

## What to borrow from existing systems

Adopt useful concepts now; do not introduce a heavy platform solely for this audit.

- [Great Expectations: Expectations and suites](https://docs.greatexpectations.io/docs/core/define_expectations/) supplies reusable, versionable assertions. Bidlo eligibility must still be expressed explicitly.
- [Soda: metrics and checks](https://docs.soda.io/soda-documentation/soda-v3/sodacl-reference/metrics-and-checks) separates metrics/thresholds and allows custom SQL/failed-row checks. This fits cohort coverage and drilldowns.
- [dbt: data tests](https://docs.getdbt.com/docs/build/data-tests) provides uniqueness, accepted-value and relationship patterns. [Source freshness](https://docs.getdbt.com/docs/deploy/source-freshness) supplies SLA-based lag thinking. dbt adoption is optional; these concepts work with current SQL.
- [OpenMetadata: column lineage](https://docs.open-metadata.org/v1.12.x/how-to-guides/data-lineage/column) illustrates transformation and dependency tracing. Bidlo needs finer file/page/row evidence in addition to table lineage. Consider a catalog when wider governance needs justify it.

## Starter files and limits

`starter-presence.sql`, `bounded-quantity.sql`, and `bounded-assignment.sql` are single SELECT statements verified against production. Their observed outputs are in `validated-results.json`. They demonstrate schema-grounded checks, not a finished production-wide audit. The assignment query deliberately does not label projects awarded or require item-to-sub source evidence; final eligibility must add those before turning candidates into defects. No global completeness or accuracy percentage has been asserted.