Wayfarer

History derivation and upgrade preparation

Issue #918 package I918-A supplies internal data and upgrade readiness. It does not by itself deliver the history routes or complete #918. I918-B supplies routed reads and dependent-operation gates. I918-C documents backend composition/fidelity and the measured performance limitation below; backend #918 acceptance is not yet supported. #604 owns actual M6/maintainer instance qualification. This procedure grants no production operation authority.

Authorities and upgrade boundary

Follow Versioning, the Release contract and the installation’s supported operator lifecycle. Do not turn a schema migration or ordinary startup/read into a history backfill. The early history-data application CLI exits before web startup, migrations, seeding, logging sinks, Quartz, storage initialization or provider registration. It consumes the normal application configuration, including Database:PasswordFile; it never prints connection strings or source content.

Before an authorized upgrade, preserve the normal database/uploads/key-ring recovery set. Stop/quiesce all old application/import/enrichment writers at the supported upgrade boundary. Apply migration 20261023175322_LocationHistoryPreparation using that release’s normal schema-maintenance procedure and exact target binary. New writers must all be from that binary before preparation/readiness is accepted. An old binary or arbitrary external SQL writer can violate these invariants and must not run concurrently. Whole-database restores require this check again.

The migration adds nullable facts, coherence constraints, a two-row preparation state table and four indexes. It requests trusted extension pg_trgm alongside existing PostGIS/citext. Have the database owner provision the extension if the maintenance role cannot do so; never grant the application superuser rights. Missing privileges or failed index creation leave preparation unavailable. The migration uses ordinary transactional index builds while writers are quiesced; allow its table/index locks and disk headroom. No concurrent-build release protocol or release manifest/operator changes are introduced here.

UTC index status coverage correction

Migration 20261024172605_LocationHistoryUtcStatusCoverage replaces only IX_Location_HistoryUtc. Its B-tree keys remain (UserId, LocalTimestamp, Id); the INCLUDE payload becomes (Coordinates, CaptureLocalTimestamp, ActivityTypeId, HistoryTimeStatus), with no predicate. The EF model matches this definition. Status covers capture membership and uncertainty without becoming a search key. HistoryTimeVersion and singleton UUID remain uncovered. Preparation logic, generation versions, filters and budgets are unchanged; no history rebuild is needed. The physical preparation check accepts either exact UTC payload, retaining the original correctness prerequisite and recognizing the corrected coverage. It still rejects missing/invalid indexes, different keys, payloads and predicates. Migration history and the current EF model own installation of the corrected definition; a preparation-ready result alone does not certify that migration or performance.

Use the existing explicit schema-maintenance workflow, with a verified recovery set and all application/read/import/enrichment traffic quiesced. This ordinary EF transaction drops and rebuilds the index, then records migration history at commit. The DROP INDEX lock blocks table access until the transaction ends. This is an offline replacement, without CONCURRENTLY or transaction suppression. Fresh migration chains first create the original index and then replace it; upgrades replace that original. Down rebuilds the original payload under the same locking/headroom requirements.

If CREATE, lock acquisition, cancellation or commit fails before a confirmed commit, inspect migration history and the physical definition before retrying. Transaction rollback restores the original index and leaves this migration unapplied; there is no committed interval without the index or invalid concurrent-build remnant. An ambiguous commit requires inspection rather than blindly rebuilding. Do not perform an out-of-band replacement and mark history applied manually.

Allow space for the old index retained until commit, the complete replacement, temporary sort files, WAL and the recovery reserve. Final index growth alone is insufficient headroom. The supported 2,838,240-row experiment measured 408.64 → 440.68 MiB (+32.04 MiB), a 3.6-second CREATE, 384.11 MiB database build-temp writes and about 395.63 MiB shared-cluster WAL LSN advance. These are local observations, not peak bounds or production sizing. Maintenance command timeouts must accommodate the real rebuild and lock window through the established operator workflow; no global tuning is introduced. Verify valid/ready physical definition, no pending model/migration changes, and ordinary readiness before resuming traffic. See the bounded delivery evidence. #604 retains actual deployment, restored-population headroom and recovery qualification.

Commands and recovery

Run the target application’s CLI under the same protected configuration/database identity used by its supported maintenance process. For native/source qualification:

dotnet Wayfarer.dll history-data status
dotnet Wayfarer.dll history-data prepare
dotnet Wayfarer.dll history-data status

prepare resumes existing compatible progress. It defaults to both generations; time or text selects one. JSON progress reports target/active versions, all-owner total/pending/unresolved counts, examined/prepared counts, last committed ID, physical index readiness and last actionable failure. It contains no owner IDs, Notes, addresses or provider credentials. Exit codes: 0 selected generations ready, 1 pending/failed, 2 invalid arguments, 130 cancellation. status is read-only and observes readiness/counts in one repeatable-read database transaction.

Each batch locks/reloads at most 500 rows in ascending ID order, derives from their current stored values, writes only derived columns and commits progress in the same transaction. Per-batch lock timeout is five seconds and statement timeout sixty seconds. Ctrl-C cancels the current batch; process termination also leaves committed checkpoints recoverable. Resume prepare; do not reset import checkpoints, replace canonical rows, or retry provider requests. An operational failure records a safe resumable category when the database permits it and never becomes unresolved evidence. If recording the failure also fails, the primary error and earlier committed progress remain authoritative. A completed cursor is followed by a stale-version sweep; readiness never relies on maximum ID alone. New supported writers already produce current facts. Edits/deletions serialize with each short row lock; deleted rows are not recreated. No locks span provider HTTP.

Before activation the command verifies the required valid/ready index definitions, trigram operator class and C text collation, then records the active generation and writer contract. It runs ANALYZE "Locations" after selected preparation. Final status rechecks physical prerequisites and all-owner versions. A durable ready flag alone is insufficient. Repair a missing index through authorized schema maintenance, then resume; do not weaken matching or indexes. Restored mixed/stale rows defeat status even when an old preparation receipt says ready, and prepare sweeps them.

Servicing dependency changes

Time and text have independent, explicit semantic generation IDs. A reviewed compatible dependency/runtime update retains the affected ID, prepared rows, active/target state, resumable checkpoints and cursor binding. An actual behavior change, or compatibility that cannot be justified, changes only the affected ID and requires its controlled rebuild. Package/runtime metadata alone is not a reason to rebuild. There is no automatic startup/background preparation, alias matching, legacy-prefix acceptance, silent restamping or forced readiness.

For the R1 batch (#937/#938), both old generations require rebuilding: NodaTime 3.3.3 / TZDB 2026c → 3.3.6 / 2026e changes historical capture values (Dublin 1925-09-25 12:00Z changes from 13:00/+3600 to 12:00/0). AngleSharp 1.7.1 → 1.7.2 changes visible extraction of historical MathML TEXT/HTML content from SECRET VISIBLE to VISIBLE. Unchanged recent records cannot waive these all-owner rebuilds. Runtime-only 10.0.13 → 10.0.14 fixture equivalence is a separate bounded compatibility decision, not an extra reason to rewrite rows.

The first explicit IDs retain the exact qualified R1 strings to avoid rebuilding already-compatible R1 data merely for a rename:

history-time-1;GeoTimeZone-6.1.0;TZDB: 2026e (mapping: 49)
history-text-1;RichNotes-2;AngleSharp-1.7.2.0;runtime-10.0.14;unicode-31129-0000799900000000000000000000007f

These are opaque historical names. Their embedded versions do not report the currently executing runtime. Future semantic changes may use history-time-2 or history-text-2; do not rename a compatible generation for cosmetic reasons. history-writers-1 retains its unchanged atomic-writer meaning.

Environment qualification versus preparation

history-data status reports actual dependency build identities, full TZDB identity, executing runtime and effective invariant globalization/sort profile separately from IDs. Qualified is independent of row counts/index readiness; QualificationFailure explains missing admission. Status never repairs state and returns 1 for an unready or unqualified selected generation. Exit 0 still requires all-owner readiness.

The code-owned R1 admission checks exact GeoTimeZone 6.1.0, NodaTime 3.3.6 and AngleSharp 1.7.2 upstream build identities, TZDB 2026e (mapping: 49), and actual .NET 10.0.13 or 10.0.14. Text additionally requires effective ICU normalization, verified by a normalization control, and invariant sort version 31129 / identity 00007999-0000-0000-0000-00000000007f. Time does not depend on this text profile. The maintained Noble image uses ICU 74.2 and runtime 10.0.14; native Ubuntu also qualifies on runtime 10.0.13. NuGet 10.0.14 packages do not imply that a probe executes on 10.0.14. No OS name, machine identity, path or architecture is an admission condition. Framework-dependent and self-contained processes are checked by what they actually load. Invariant mode, NLS, unknown artifacts, future runtimes and other globalization fingerprints need a reviewed compatibility decision first. This is history admission, not security-release or native-platform acceptance.

Qualification is lazy and local to history; it does not stop global web startup. Unknown qualification cannot stamp a selected generation or activate data. New Locations require both qualifications; edits require each generation they actually recompute (including opportunistic stale-generation updates). A rejection fails the whole tracked/import save atomically through the caller’s existing operational-error path (generic HTTP failure where applicable), without a success acknowledgement. Both fenced bulk address publication branches use the same text check and transaction: address, derived text and final provider publication commit together or roll back together. Operational failure never becomes an unresolved Location. Other independent writes retain their existing boundaries.

prepare/rebuild preflight every selected kind before changing any state; batches also check before row/checkpoint work. On qualification failure committed progress, active state and row facts remain untouched, while fresh status/readiness refuses to advertise readiness. Use a qualified build/runtime/globalization profile or have the maintainer qualify a supported profile. Rebuilding alone cannot qualify an unsupported environment. There is no operator version override.

Capture/viewer predicates and nonempty normalized searches reject missing required qualification with 503 historyQualificationRequired, separately from 503 historyPreparationRequired on a qualified but unprepared deployment. UTC nontext collections and direct detail remain usable. Unqualified capture/viewer publication says unavailable / historyQualificationRequired, retains the stored capture version, and advertises null for an unqualified serving generation in derivationVersions. It never calls that operational failure pending or unresolved. Detail still publishes safe Notes through the existing sanitizer contract.

Before accepting a new dependency/profile, review relevant source/data changes and run the existing time, parser and Unicode fixtures at shared writer/query seams. Finite controls and sort equality are admission evidence, not universal behavioral proof. Retain IDs only with a justified compatibility decision; otherwise change the affected ID. Record actual profile and rationale in the servicing record. No compatibility registry, extra stored generation or migration framework is used. See the R1 record.

Independent rules rebuilds and readiness

dotnet Wayfarer.dll history-data rebuild time
dotnet Wayfarer.dll history-data rebuild text

A rebuild marks selected generations unready before processing any rows, resets only their progress and scans every row even for a same-version rebuild. Interrupted rebuilds resume with prepare time/prepare text, rather than starting another rebuild. Changed target rules require explicit rebuild; arbitrary operator version labels are rejected. There is one stored generation, so declare maintenance for affected reads while rebuilding. Unselected facts/progress stay intact.

Deploy coherent qualified binaries across writers/readers, with the existing quiesced upgrade procedure. Qualification is not a live-process fence; stop old web/import/enrichment/CLI processes before new writers. No rolling/mixed-release upgrade is supported. Collection cursors bind both semantic IDs: compatible servicing preserves them; either semantic change rejects old continuations.

Pending time has null version/status/facts; a mismatched version is also pending. Resolved time carries the coordinate-derived IANA zone, wall timestamp and historical offset together. Current-version unresolved time carries a safe evidence reason and no invented capture values. Invalid/empty/nonfinite/out-of-range coordinates, unusable event evidence (including PostgreSQL infinity sentinels) and unrepresentable local conversion are exceptional evidence; unexpected resolver/tool/store failures remain unfinished work. Stored UTC LocalTimestamp, Timestamp, coordinates and imported TimeZoneId are preserved; the imported zone does not override coordinates.

Readiness requires matching target/active/writer identities, valid indexes and zero pending rows across every owner. Current-version unresolved time is terminal classified evidence, counted separately from pending. Collection reads check the required generation identities, physical prerequisites and a single all-owner pending-row existence predicate in the response snapshot. They do not compute unused operator totals; history-data status retains its full progress accounting. Capture predicates depend on time readiness, nonempty search on text readiness. Affected operations return historyPreparationRequired; unaffected UTC/viewer/nontext reads and direct detail retain their contracts. I918-A exposes no routes or readiness/capability advertisement to consumers.

Rollback and restored-data readiness

History preparation does not authorize a binary or schema downgrade. Follow the existing release authority and installation recovery procedure. There is one stored generation per kind; rollback has no automatic-readiness or zero-downtime guarantee.

1. Stop current writers before changing binaries or restoring data. Stop all current Web, import, enrichment and CLI writer processes, including interrupted maintenance processes, and keep affected reads in maintenance. Qualification and the shared history-writers-1 value are not process fences. Do not let old and new writers alternate stamps.
2. Restoring the pre-upgrade recovery set: use the supported managed update recovery or managed restore procedure, or the administrator-owned native recovery boundary. Select a verified, compatible, coherent pre-upgrade database, complete active Data Protection ring and durable uploads/imports together, with the protected deployment configuration needed by that installation. This returns canonical data and history state to that recovery point; it does not retain later writes. Preserve the upgraded state’s recovery evidence through the existing emergency backup requirements, retained receipts and recovery holds. Let that authority select the compatible target; merely selecting old binaries is not a restore.
3. Retaining the upgraded database: consider an earlier binary only where the installation’s existing release procedure explicitly supports that target’s schema, credential and storage compatibility. Managed update refuses downgrades; this runbook adds no downgrade route or migration reversal. If that compatibility is not established, use the coherent recovery-set path. A binary change alone preserves the newer row stamps, active/target state and checkpoints; it does not restore the older binary’s prepared generations.
4. Inspect with the actual recovered target. Run dotnet Wayfarer.dll history-data status both using that target binary, its actual runtime/globalization profile and protected configuration/database identity, before maintenance and again before reopening affected reads. Inspect both generations’ target/active/writer agreement, committed progress, all-owner pending and separate unresolved counts, and valid/ready physical indexes, extension and collation prerequisites. Check Qualified and actual diagnostics when the target provides them; previous-main status lacks those fields, so use its release’s profile evidence rather than inventing an admission result. Under R1, an unsupported profile needs qualification, not a rebuild.
5. Prepare only what that target requires. The previous-main binary compares exact dependency/runtime-derived IDs: newer R1 time/text stamps are stale to its older rules, even if a stored Ready flag is true. A different persisted target refuses ordinary prepare; use that binary’s explicit history-data rebuild time and/or history-data rebuild text for the affected kinds. If R1 changed both targets, returning to previous-main rules while retaining that database requires both rebuilds. Conversely, compatible R1 binaries retain the exact qualified IDs: do not rebuild compatible ready data merely because runtime provenance changed. Resume interrupted work for the same target with history-data prepare time, prepare text or prepare both; retain its committed checkpoints and let the stale-row sweep finish. Use the same preparation for missing/stale rows under an unchanged target. Rebuilds recompute derived fields only; preserve canonical UTC times, coordinates, Notes, addresses, identities and provenance. Never copy Ready flags, relabel generations or rows, or reset import checkpoints/provider accounting.

Until target-binary readiness is established, capture-basis pages, overviews and candidate lists remain unavailable while time is unready; any nonempty normalized search remains unavailable while text is unready, including UTC/viewer searches. Combined requests need both. Qualified but unprepared reads return 503 historyPreparationRequired; R1 rejects missing required qualification separately with 503 historyQualificationRequired, including viewer-time predicates. UTC and qualified viewer collections without text, and direct owner detail, retain their independent contracts, with stale capture facts reported as pending and unqualified time facts unavailable. Changed generation IDs invalidate old continuations. Reopen each affected read only after fresh target status confirms its required generation ready; backup receipts or a prior binary’s status are insufficient.

Text equivalences and matching

LocationHistoryDerivation.NormalizeText is the shared stored/query implementation. It applies compatibility decomposition (NFKD), the explicitly selected accent fold, Unicode whitespace collapse/trim, invariant scalar uppercase and NFC composition. It removes U+0300/0301/0302/0303/0304/0306/0307/0308/030A/030B/030C/0313/0314/031B/ 0323/0324/0325/0326/0327/0328/032D/032E/0330/0331/0342 only following Latin or Greek base letters, including cedilla, ogonek, comma/dot below and Vietnamese horn/tone marks. Thus cafe, CAFÉ, and decomposed cafe + acute agree; Greek tonos, dialytika, breathing and circumflex accents fold. Greek final sigma agrees through uppercase. Iota subscript, Arabic marks, Indic virama and Japanese dakuten remain meaningful. Letters without an applicable compatibility decomposition (for example ø, ł, ß) are not transliterated. This is not translation, stemming, fuzzy matching or a wildcard/token language.

Notes first use RichNotes.Normalize and parsed visible text. Inline words stay continuous; block/list/line boundaries add spaces, including discarded legacy block containers. Parsing decodes entities once; attributes, URLs, scripts/styles, images and removed content never contribute text. No media is fetched. Each of the eleven authorized fields normalizes independently, then joins with newline. A normalized query cannot contain that delimiter, so phrases cannot cross fields. Original Notes/address fields are never rewritten by preparation.

Store search text with C collation. Collection queries use ordinal literal substring: parameterized LIKE with escaped backslash/percent/underscore for useful trigrams; exact strpos for short/punctuation-only terms. No minimum phrase length or raw-Notes application scan is needed. The qualification checks LIKE/strpos parity with the accent-folded representation and confirms the GIN path; common terms may legitimately scan broadly. I918-B qualifies exact routed predicates; I918-C records scale performance below.

Writer coverage and canonical isolation

Time is recomputed for event/coordinate changes or stale time rules; text for any of the eleven text inputs (including clears) or stale text rules. Activity remains the ordinary catalog FK. Internal properties have nonpublic setters, BindNever and JsonIgnore; parsers/exports retain explicit canonical mappings. API whole-entity snapshot comparison excludes only the eight new derived properties while retaining all previous canonical checks. Preparation therefore creates no new owner edit conflict or idempotency equality/export field.

Local qualification and measured limits

The retained I918-A receipt uses PostgreSQL 18.6/PostGIS on Linux/WSL, 10,000 synthetic rows in a fresh fixture-owned database, with distinct diary content and all stored address fields. Rules were GeoTimeZone 6.1.0, TZDB 2026c (mapping 48.2), RichNotes-2, AngleSharp 1.7.1.0, .NET 10.0.13 and Unicode 31129 with the invariant sort identity recorded in CLI status. It uses the actual production preparer, 500-row transactions, no provider calls and no raw pre-extracted text COPY. The trial took 13.14 seconds; maximum batch elapsed/lock upper bound was 448 ms. Table+TOAST grew 4,349,952 → 14,991,360 bytes; all indexes grew 6,881,280 → 24,576,000 bytes, with four history indexes growing 3,096,576 → 16,949,248 bytes. Observed preparation WAL was 86,780,568 bytes. These are pre-vacuum physical footprints including MVCC/GIN pending-list growth and fixture allocation, not steady-state per-row or production estimates. Allow at least the observed growth plus index-build temporary space and WAL retention; measure the restored installation’s real headroom before its authorized upgrade. Migration/index-build elapsed time and peak temporary disk were not measured by this preparation trial; instance qualification must budget those separately.

Equivalent 100-row canonical SQL trials, each rolled back, compared baseline versus the same transaction plus production derivation/storage: create/import 35.45/124.15 ms, Notes/address edit 68.80/154.43 ms, enrichment 61.93/126.45 ms. Observed WAL respectively 177,904/724,360; 454,872/2,528,088; 478,328/960,888 bytes. Transaction/lock windows were respectively 27.85/117.27, 61.81/147.34 and 54.93/119.53 ms; totals include connection/rollback overhead. These isolate added derivation at the SQL seam; they do not measure complete routed/import/provider workflows or sustained load. WAL is cluster-observed and can include other work. A forced eligible GIN plan for folded CAFE took 5.8 ms locally; this proves amended-normalizer compatibility, not routed query performance or a production SLO.

Focused tests cover folded scripts, safe visible Notes, historical folds/gaps/non-hour offsets, final partial edits, both fenced publication branches, import original/dedup preservation, real checkpoints, interruption after a derived UPDATE, concurrent edit/delete, all-owner accounting and independent rebuilds. Consumer/native/browser, production population/headroom remain separate. I918-C routed evidence follows below.

Routed qualification and readiness limitation (#918 I918-C)

The earlier timeout correction retains the existing routes, prepared-data architecture, shared representable partition edges, complete filters, uncertainty and five-second cancellation. Overview SQL streams numeric extraction and validation through subquery boundaries before exact edge comparisons; the actual grouping plan no longer expands PostGIS conversion/validation into every comparison. Collection readiness uses one all-owner pending-row existence check for required generations instead of unused operator totals. Physical index, target/active/writer version and scanning/checkpoint guarantees remain in the response snapshot.

Fresh comparable before/after trials use the retained 210,240 and 1,051,200-record owners plus 210,240 unrelated rows, distinct Notes and all eleven searchable fields, five-minute events, PostgreSQL 18.6/PostGIS 3.6.4, Debug and actual bearer MVC routes. Each selection has first use plus two warm observations. This is local WSL2 TestServer evidence with warm OS cache; no disk-cold, network, sustained-load or production-host guarantee. Prepared fixture insertion is not backfill throughput. The under-one-second target remains an engineering target on these workloads.

All 158 corrected requests return 200, versus 25 failures before correction. All five pure four-read rounds complete: zero failures in twenty requests versus nine in twenty before, at 2,091–3,725 ms after correction. Both receipts retain exactly 158 unique intact observations; appends are serialized and no concurrent round contains diagnostic replays. The original qualification’s nine failures in sixteen clean requests and logging collision/recovery remain separate historical evidence.

The sub-second target is not fully met: 80 corrected requests are under one second and 78 miss it, including every concurrent and post-write request. Warm narrow days/months, broad chronology, rare/absent long phrases, detail, small-owner broad overview and small-owner absent short text succeed under one second. Recurring hours with/without common text, combined Tokyo scopes and punctuation overviews miss at both sizes; large broad/short-text overviews and absent-short-text pages also miss. The first small-owner day overview takes 1,050 ms. Completion inside five seconds alone does not establish I918-C performance acceptance.

Natural broad aggregate replays improve from 7,201/30,785 ms to 456/1,559 ms for small/large owners. JIT stays enabled: its totals fall from 5,998/9,120 ms to 34/35 ms, while planning falls from 3.128/6.189 ms to 0.871/0.840 ms. Grouping keys shrink from 35,181 characters to about 3,480 without repeated coordinate expressions. Execution work improves as well as compilation. The large aggregate still examines the 1,471,680-row relation and reads about 247,913 shared blocks. Its external sort writes 15,973 temporary 8 KiB blocks (about 125 MiB), up from 10,061 before; the small aggregate no longer spills. These are the earlier correction’s measurements; the compact-cell follow-up below removes the remaining broad spill without changing work_mem.

Readiness remains a required all-owner scan: the combined pending probe takes about 361 ms in the large recurring/common plan. Its exact uncertainty count still takes about 1,085 ms and the complete aggregate about 931 ms in separate natural replays. The Tokyo candidate plan has lossy bitmap rechecks; its count takes about 1,232 ms. These are remaining concrete query-work costs, not serialization costs. Further target work needs a separately bounded investigation of those repeated required scans and filtered aggregation spill/access paths; no new indexes, fused query architecture or weakened readiness is approved by either correction.

Historical whole-cluster samples retain 169.15 → 110.90 PostgreSQL CPU seconds and 2,664 → 2,730 MiB summed RSS over 25.16 → 16.18 wall seconds. They include background work and double-count shared pages, so they are excluded from attributable query CPU/private-memory claims. Direct application peak RSS is 393 → 436 MiB. Admission, worker and memory settings are unchanged; no memory reduction or sustained capacity is established. The compact key’s separate concurrency result below remains unproven.

After the same 500 tracked address edits without explicit vacuum, all corrected post-write routes succeed: broad overview 1,933–2,013 ms, recurring/common 2,380–2,480 ms. The count’s covering index has 716 heap fetches versus two in the prepared observation. The separate post-write broad aggregate takes 1,567 ms and retains the large sort spill at that stage. These do not qualify write throughput improvements. Complete long detail remains 1,203,344/1,203,347 response bytes; the observed serialization work is about 2–3 ms, with no truncation or invented unavailable facts.

Retained raw requests/responses, first/warm tables, CPU/RSS samples, stage timings, SQL/plans/buffers/rechecks and reproducible commands are in /home/stef/.local/share/wayfarer-investigations/918-performance-20261024/. The original failure report remains at /home/stef/.local/share/wayfarer-investigations/918-20261024/i918-c/. See testing and evidence classification.

Compact cell grouping follow-up

The subsequent change groups the existing exact X/Y decisions by one reversible integer key, then decodes and sorts the completed cells with the original null ordering. Nullable positions become digits 0–16 in radix 17; keys 0–288 are unique and safely fit PostgreSQL integers for every supported grid (1–16). Exact shared edges, every match/count/extremum, sparse geography, singleton facts, uncertainty, all-owner readiness, response snapshots, authority and deadlines remain unchanged. There is no epsilon, sampling, new index, schema or planner-setting change.

The retained query-shape investigation is /home/stef/.local/share/wayfarer-investigations/918-query-shape-20261024/. Its final three-repetition SQL comparison has large broad medians 1,508 → 1,295 ms (about 14%); the small broad median is essentially unchanged, 403 → 402 ms. The large broad plan changes from sorting all 1,051,200 matches to a one-batch 40 KiB hash aggregate and a 25 KiB sort of six completed cells. Temporary writes fall from 15,973 8 KiB blocks (124.79 MiB) to zero; the former sort’s peak disk footprint is 63,816 KiB, a different measure. All 1,471,680 relation rows are still examined and 247,914 shared blocks touched. Buffer reads do not prove disk reads.

The investigation’s warm large broad routed range is 1,846–1,879 → 1,656–1,705 ms. Sequential measurements straddle a background memory release, so this full latency difference cannot be attributed solely to the key. Its paired post-write large broad SQL observation is 1,485 → 1,237 ms with identical shared-block totals and 15,973 → zero temporary writes; routed ranges are 1,873–1,918 → 1,722–1,795 ms. The modest latency gain leaves the query-plus-projection target unmet. Filtered recurring/common aggregation still spills 668 temporary blocks, and readiness and uncertainty scans remain material. No gain is claimed for the selective shapes.

Reuse the investigation’s 36 compact-key aggregate parity receipts (plus 90 for rejected alternatives), 50 complete routed response pairs and unchanged lower-seam time/text/filter evidence. Its five alternating four-read rounds all succeed, but summed makespans are 16.15 → 16.65 s; the compact key demonstrates no concurrency/throughput improvement. No attributable PostgreSQL CPU/private-memory reduction is established. These read-only prototype results motivated the delivery change; they are not independent review or I918-C acceptance. The bounded production repeat, commands, response parity and plans are retained separately in /home/stef/.local/share/wayfarer-investigations/918-compact-cell-20261024/.

That production repeat has 48/48 successful routed reads with no-store and eight exact overview response pairs (only response observation time excluded). Its eight reconstructed baseline commands equal the retained previous production SQL exactly; the candidate commands are unchanged production SQL. The same seed/versions/settings and 1,471,680-row shape are used. Three routed observations per shape and one separate natural plan replay suffice here; the unaffected matrix and concurrency are reused.

The separate fresh large broad SQL replay takes 1,633 → 1,295 ms, with the same 247,918 shared blocks and 15,973 → zero temporary writes. After 500 tracked address edits without vacuum, it takes 1,576 → 1,159 ms, with identical 247,995 shared blocks and 15,973 → zero temporary writes; both uncertainty counts have 680 heap fetches. The candidate retains the 40 KiB one-batch hash and 25 KiB six-row output sort. Large recurring/common aggregation still writes 668 temporary blocks. Companion query-plus-projection remains 2,004 → 1,688 ms for large broad and 1,801 → 1,597 ms post-write; selective results remain variable. Serialization is below 0.4 ms. The roughly 7% warm broad routed gain is modest and does not meet the sub-second target. Direct-probe sampled RSS is about 300 → 301 MiB; no CPU/private database memory or new concurrency improvement is claimed. The owned fixture is cleaned up.

A data-ready receipt proves generation/index/pending prerequisites, not performance. The reviewed finite year-0001 capture limitation remains honestly unresolved while UTC/viewer/detail facts are retained. Fresh exact-head independent review, applicable CI, maintainer acceptance and disposition of the measured target misses remain outstanding. #918 stays open. Reuse I918-A’s preparation/write/storage receipts; #604 still owns restored-population headroom, WAL/index-build/migration/recovery qualification. Mobile/deployed acceptance and #853 C4 remain separate.

Deferred singleton UUID follow-up

Overview now aggregates singleton IDs before retrieving their nullable UUIDs by owner and primary key in the same SQL statement. This removes the noncovered UUID from broad aggregation inputs, allowing the existing IX_Location_HistoryUtc covering path. Every matching row still contributes to exact cell membership, counts and extrema. Response shape, shared edges/drill-down, uncertainty, readiness, cancellation, repeatable-read snapshot and deadlines are unchanged. There is no application query per cell, added index or combined-scan experiment.

The compiled delivery repeat uses the same research seed and two owner scales, plus 210,240 unrelated-owner rows. All 30 routed reads return 200/no-store, and all five complete response pairs agree except root observation time. Three observations per variant cover only broad and recurring/common-text overviews at both scales and one large-owner post-write broad control. Candidate SQL is unchanged production SQL; reconstructed baseline SQL is checked against retained commands. Use qualified/ in the receipt below: the initial comparison attempt failed its strict reconstruction check on a missing final newline, was preserved, and was corrected once with all five SQL shapes checked before seeding again.

Prepared broad aggregate plans examine 210,240 / 1,051,200 owner index entries instead of all 1,471,680 heap rows, with zero prepared index heap fetches and two singleton UUIDs recovered. Inclusive root shared-block references fall from 247,915 → 3,847 small and 247,918 → 19,244 large. After 500 tracked address edits without vacuum, they fall from 247,995 → 19,454, with 680 covering-scan heap fetches. Natural aggregate execution is 483 → 165 ms small, 1,466 → 717 ms large and 1,403 → 1,092 ms post-write. These are separate diagnostics; shared-block reads do not establish physical disk reads. Broad temporary writes remain zero, with a 32/40 KiB one-batch hash and 25 KiB six-cell output sort.

The large filtered routed control shows no material regression in this selection. Filtered improvement is not established: the small candidate has a 1,662 ms observation, and separate query/projection companions are slower at both sizes. Its slow request includes slower unchanged readiness and uncertainty commands as well as aggregation. Filtered heap access remains; large aggregate spill falls from 668 to 494 temporary write blocks, but that separate replay takes 918 → 1,206 ms. Post-write warm routed latency overlaps baseline despite lower broad buffer work. These observations limit any latency claim; no additional run or optimization is authorized by this receipt.

Separate candidate query-plus-projection is 227 ms for small broad (meets the one-second target), 1,028 ms for large broad, 1,611/2,562 ms for small/large recurring/common and 1,854 ms for large post-write broad (misses). Serialization is below 0.4 ms for these compact responses. Five-second completion does not waive these misses. Full first-use/warm observations, commands, plans, typed parameters, parity and both attempts are retained at /home/stef/.local/share/wayfarer-investigations/918-singleton-delivery-20261024/. Both guarded fixtures completed cleanup. The wider named workload, lower-seam semantics, preparation/write/storage and four-read evidence above are reused; there is no newly measured concurrency gain.

This is a bounded broad-query resource improvement. I918-C and #918 remain incomplete. Independent exact-head review and an explicit measured maintainer readiness decision remain required; filtered workloads and universal sub-second performance are unresolved. No further optimization campaign is automatically authorized. Production/mobile/deployed acceptance remains separate.

Final protective ceiling and operating range

The 24 October 2026 maintainer decision in #918 sets history/page and overview evaluation to fifteen seconds, retaining the separate thirty-second selected-detail ceiling. The SQL timeout remains transaction-local; application-wide and database-wide defaults, preparation timeouts, admission and disconnect handling are unchanged. Prior five-second observations above retain their original provenance.

See the final bounded performance/resource qualification for the 25-year synthetic owner, field-distribution comparisons and mixed-use results. Sub-second target misses require an explicit measured maintainer disposition. No automatic acceptance, production sizing, issue closure or further optimization authorization follows from raising the protective ceiling.