Company search queries curated tables behind one product operation

Status: accepted Date: 2026-08-28 Deciders: Chris (solo founder) Depends on: ADR-0011 and ADR-0012 Tracking: spec #549, implementation #550, and read-contract gaps #428

Context

The sales application needs one operation for the Virksomheder grid. The hub already has curated registry, financial, and web tables, but no operation that keeps companies with missing optional data, searches names and domains, and pages one stable result.

A second daily company table could put every grid field in one row. It would also create another representation to build, index, version, and operate after each daily ingestion. The relevant question is whether that work saves enough request processing to justify it.

Decision

1. search_companies is the product boundary. It reads the existing curated tables and returns at most 100 rows, a next cursor, and one data version. company_detail(bigint) remains the profile operation.
2. Search uses separate indexed candidate paths for exact CVR, exact name or normalized domain, prefix, and pg_trgm fuzzy match. It selects the page of CVRs before it joins the current domain and latest financial values.
3. Optional domain and financial values are null when missing. A missing value never becomes zero and never removes the company.
4. The cursor carries the normalized query, sort, last sort value, CVR, and data version. CVR is the final stable tie-breaker. Null sort values are last in both directions.
5. The data version combines the latest successful company-registry run, latest successful financial-publication run, and latest terminal web-enrichment run. A changed version rejects the old cursor. The consumer restarts at page one.
6. There is no prepared company-search table and no Cloud Run publisher job.
7. Exact disjunctive Facet counts use one narrow company_facet_cube. It has only the four canonical Facet keys, their source labels, and an exact company count. The existing CVR ingestion refreshes it after a delta and inside the atomic staging swap for a backfill or reconciliation. There is no separate publisher job.
8. company_facet_counts reads the cube when search text is empty. With search text, it first selects indexed company candidates and counts that small set from the curated company table. Grid rows and counts bind to the same data version, query, and filter object.

Evidence

A disposable PostgreSQL 15 A/B test used 2,260,000 companies, 1,000,000 financial reports and metrics, and 50,000 website records.

Operation	Curated tables	Prepared table	Saving
Filtered page with financial and domain joins	1.77 ms	0.13 ms	1.64 ms
Fuzzy company-name search	285 ms	273 ms	12 ms
Facet count with a financial filter	606 ms	84 ms	522 ms
Broad industry count	137 ms	no consistent improvement	none

The prepared table took 82.76 seconds to build and index and used 479 MB. Two retained versions would use about 958 MB. Ordinary pages and search did not recover that daily work at the expected request volume. Financial Facet counts did show a material saving, which is why they stay a separate measured slice.

The first search_companies query shape materialized the full active population before candidate selection and took 35.686 seconds. It was rejected. The indexed candidate-first shape measured 204 ms for a one-letter fuzzy search and 41 ms for the default first page on 2,260,000 active synthetic companies.

These figures compare query shapes on local synthetic data. They are not a production capacity forecast.

The Request 4 performance test used the same 2,260,000-company synthetic population. This population is only for the opt-in performance test. Routine functional tests use small fixtures.

Exact Facet count shape	Result
Direct operation baseline	one sample exceeded 70 seconds; stopped
Optimized direct four-aggregate query	exceeded the 10-second limit
Narrow per-company Facet projection	3.77 seconds
Pre-aggregated four-key Facet cube	209 ms broad count

The final operation took 52.4 seconds to refresh the cube once. Its request p95 across 20 warmed samples per context was 286.2 ms with no filters, 136.7 ms with all four Facets, and 14.2 ms with fuzzy search and Facets. All three contexts met the 500 ms gate. The test is tests/test_company_facet_performance.py with RUN_PERFORMANCE_TESTS=1.

Options considered

Option	Decision
Replace curated tables with one daily read table	Rejected. The curated tables remain the authoritative reusable data model.
Add a daily prepared company-search table beside the curated tables	Rejected for company pages and search because the measured saving does not pay for its build, storage, and operation.
Let the sales application join published views	Rejected because paging, search order, versioning, and later storage changes would become consumer behavior.
Query curated tables behind one candidate-first operation	Chosen. It meets the measured request target without another stored representation.
Count exact Facets from curated rows on every request	Rejected. Both the first and optimized direct shapes failed the 500 ms gate.
Add one narrow, pre-aggregated Facet cube	Chosen. It meets the gate and the existing ingestion refreshes it without a new job.

Consequences

• The sales application depends on one operation instead of table layout.
• A later internal storage optimization can keep the same operation and remove the old query. It does not add a compatibility path.
• Daily ingestion can invalidate an in-progress cursor. The explicit restart is safer than mixing two data versions in one grid.
• Output domain and financial joins run for only the selected page in normal search and browsing paths.
• Facet counts are exact and are not inferred from a 100-row page.
• The cube adds one daily rebuild to CVR ingestion. A failed rebuild fails the ingestion run and does not publish a new data version.