# Batched ingress materialization performance

## 2026-09-03: re-anchor on current owners

Original incident: canceled test3 run33718996120 left2,534 receipts expanding
to20,000 immutable Events. Connectors v0.2.20-exp.4740.19 batches of50 receipts
contained2,903/2,628/3,083 Events across960/815/1,023 source IDs; the interval
after admission/pack-lock acquisition to receipt ACK timestamp was284/262/334s.
Current source-settle EXPLAIN chose a full scan (estimated36,059 rows); this
was not a historical execution profile and does not establish SQL time shares.

Current implementation base: main16e48a8, which incorporates #330's immutable
projection ranges. The experimental quadratic delivery classifier is already
gone: do not reintroduce or separately optimize it. Current remaining costs are
per-typed-source settle reads, per-projection validation/state operations, and
per-lane readiness maintenance, serial within the existing admitted transaction.

Canonical schema: Core f38904eea78c6d096cae2210899e415a3c89fddb at
NexusCRM-pr329-root-fix. Current Core main lacks this protocol schema; this
dependency must remain explicit in the release BOM. An initial fixture run
failed without NEXUSCRM_REPO_ROOT, and a second with older Core main failed
because _System_ConnectorProjectionProtocol was missing. Neither is a pass or
application-performance measurement. No substitute test schema is introduced.

Local baseline: existing TestSourceProjectionRangeSC400WalkingSlice,4,400 Events,
400 sources,400 source projections plus400 ordinary commands. Local TiDB8.5.0
127.0.0.1:4001, disposable synthetic database; no remote or production data.
Logs: /private/tmp/connectors-ingress-batch-evidence/main-baseline.log.

Baseline completed: admission7.995s, payload loading1.965s, combined9.960s;
all4,400 Event identities and4,000 source inputs were checked. Added low-cardinality
stage timing to the existing enqueue histogram before optimizing. The stage
baseline log is main-stages.log; these timers record successful stage boundaries,
not per-record payloads or a replacement tracing framework.

Stage baseline (same workload, timings vary on shared local TiDB): admission4.564s,
prepare/settle0.508s, bulk facts0.430s, projections1.376s, lanes2.205s;
payload reads1.604s. This makes lane maintenance the first measured set-based
optimization target, followed by projection admission. Settle batching removes
the broad per-source query class but is not claimed to own most baseline time.
Next change reuses the existing lane head ordering, two disjoint index branches,
live state/due predicates and processing guards for a bounded set of lanes.

Settle+lane candidate SC400: admission2.509s, prepare/settle0.136s, bulk0.776s,
projection1.411s, lanes0.133s. Full input-coverage assertions passed. Do not
attribute shared-TiDB bulk timing variation to these edits. The remaining
largest measured admission stage is now projection creation. Next: batch its
validation/current reads/writes under the same owner; retain one state decision
for singleton and batch and never mutate an attempted active generation.

Intended invariant: same immutable Events, complete nonoverlapping projection
ranges, frozen claimed generations, same per-lane execution order, and atomic
rollback/pack-incarnation fencing. Optimize set-based I/O within existing
owners; no new queue, cache authority, table, lease or scheduler mechanism.

Verification matrix: mixed/ordinary/fresh/duplicate input; multiple typed source
keys with source-lane overrides; route/companion separation; collation semantics;
unclaimed/claimed/retry/terminal projection transitions; concurrent enqueue and
completion; rollback; actual claim→load→complete consumer; representative fresh
and repeated batches plus history scaling. Use exact same workloads for baseline
and candidate; timing is reported, deterministic work/SQL shape is asserted.

Projection-batch candidate: admission 1.425s; prepare/settle 0.135s, bulk facts
0.913s, projections 0.199s, lanes 0.089s. Payload loading varied to 3.994s
without a read-path change, so admission gains are not an end-to-end guarantee.
Log: projection-batch.log. Walking/frozen-generation and concurrent-completion
tests passed in owner-batch.jsonl. The typed-source oracle exposed a real
collation difference: derived requested-key string columns made CASE-ID fail
to match a case-insensitive stored column. Replace the derived join with
bounded conditional aggregates using column-to-parameter comparisons, keeping
the indexed future-deadline scan. Recheck the oracle before further tuning.
Four SQL mocks also detected whitespace-only differences in shared lane SQL;
restore the existing formatting rather than weaken their query contracts.

Correction batch passed: typed-source oracle (including 265 keys across chunks,
route/companion separation and case-insensitive storage) and existing enqueue
SQL contracts. lane-batch.jsonl also passed: 130 lanes plus 20,000 unrelated
live/history rows required exactly eight captured production SQL statements,
with no full scan and bounded actual scanned rows. Both processing-guard
branches, target override identity, future-due exclusion and retry-state
ordering were checked. Shared SQL templates replace string rewriting, and the
old unused current-read fallback helper was removed. Next boundary: complete
owner suites, forced successor-promotion/rollback race, then frozen comparison.

Frozen owner boundary passed: 506 tests, zero skips/failures (owners-full.jsonl).
Two additional real-TiDB regressions passed: a deliberately invalid second
projection chunk rolls back first-chunk updates and all newly inserted Events;
and a DATA_LOCK_WAITS-observed producer current read races completion of an
active generation with an existing successor. The latter observed the inherited
unique-active-key conflict, complete producer rollback, then successful retry
of identical idempotency keys, preserving the successor's lower bound and
exactly-once input coverage through actual claim/load/complete. No production
test hook or new recovery protocol was added. Logs: chunk-rollback.jsonl and
successor-race.jsonl.

Frozen same-workload comparison (three sequential runs each, no other tests
launched by this task during measurement; local TiDB remains a shared host):

| SC400 timing | main 16e48a8 | candidate | median ratio |
| --- | --- | --- | --- |
| admission, seconds | 7.377 / 4.768 / 6.988 | 1.053 / 0.940 / 0.907 | 7.43x faster |
| admission + input reads, seconds | 9.435 / 6.314 / 8.549 | 3.564 / 2.880 / 2.543 | 2.97x faster |

Logs: frozen-baseline.log and frozen-candidate.log. Every repetition verified
4,400 immutable Events, 800 WorkItems and all 4,000 projection inputs without
overlap or omissions. Input reading itself was not optimized; these are local
walking-slice measurements, not provider-to-business completion or test3
acceptance. Runtime claim search and same-parent record locking remain separate
measured downstream work, intentionally not changed in this Connectors patch.
Next: frozen full-module build/test and targeted race verification. No deployment
or production access occurred in this coding loop.

Frozen full-module verification: `go build ./...` passed; `go test -p 2 ./...`
passed 3,299 tests/subtests across 34 tested packages, with zero skipped tests
and zero failures. Four packages have no tests (not skipped integration proof).
Canonical Core fixture and TiDB-required settings are unchanged.

Independent final falsification found no reachable new semantic blocker. Two
performance limits remain explicit: settle aggregation scans the indexed
future-pending horizon across packs (not all terminal/expired history), and
selected-lane window sorting still costs work proportional to that lane's
actual backlog. Other-pack future-event skew and very deep single-hot-lane
resource limits were not measured by the 130-lane/two-row-per-lane test. These
are not asserted regressions or reasons to add another queue/cache mechanism.

Final targeted `-race` run passed (race.jsonl), including ingress batching,
cross-chunk rollback, real successor-promotion contention, frozen generations,
typed-source equivalence and batched lane maintenance. Final diff whitespace
check passed. The implementation was frozen on codex/ingress-batch-performance
before PR publication. No tag, consumer pin or deployment was changed.

## 2026-09-03: precision review follow-up

Review #333 comment 5523024393 was checked against base 16e48a8 and published
head 269e77d on the same local TiDB 8.5.0 with synthetic canonical fixtures.
Canonical Event.NextAttemptAt and WorkItem.RunAfter are whole-second DATETIME;
both versions pass that shape. With those two test columns widened to
DATETIME(6), actual enqueue then claim exposed a conditional regression:
269e77d changed a stored 05.250 deadline to 05.000 and admitted a claim at
05.150; base preserved the deadline and refused that claim. Review logs:
/private/tmp/pr333-review.ITmPLK/{base,head}.jsonl. This does not establish
test3 exposure or a published higher-precision schema contract. It establishes
that the new aggregate cast unnecessarily narrows a stored value. Next change:
preserve temporal result precision, retaining the same bounded query, index,
transaction and state-transition paths; add real enqueue/claim coverage for
single and multi-event admission at both precisions.

The separate missing-ReplayOfWorkId probe bypassed native projection
activation. Base and head both reject that schema at activation. Ordinary
WorkItem rolling-schema compatibility is unchanged; native projection requires
the complete canonical schema and protocol checkpoint described in
[source-projection-input-ranges.md](source-projection-input-ranges.md).
Keep that existing boundary and test both activation entrypoints rather than
add optional-column detection or an alternate projection insert path.

Pre-fix same-host SC400 repetitions on 269e77d: admission
1.444 / 1.320 / 1.121 seconds; combined admission/input reads
4.184 / 4.213 / 5.031 seconds. All immutable-input coverage assertions passed.
The permanent regression matrix distinguishes singleton admission (which still
uses an inline, precision-preserving MIN) from fresh multi-event admission and
duplicate-triggered batch fallback (which share the narrowing loader).
Logs for this correction: /private/tmp/pr333-precision-fix.81RTRo/.
No query count, index predicate, batch size, schema or concurrency policy changes
are required: the correction only preserves fractional precision in the SQL
result type. Re-run the correctness matrix and same workload after that change.

The new regression failed on 269e77d for both microsecond fresh-batch and
duplicate-batch admission: sibling/WorkItem deadlines became 05.000 rather than
05.250123 and a claim one microsecond before the original deadline succeeded.
After changing only the cast to DATETIME(6), the focused batch passed all ten
tests/subtests with zero skips: both precisions across singleton, fresh batch
and duplicate fallback; exact-deadline claim and input coverage; typed-source
oracle/index plan; both native activation gates; ordinary WorkItem rolling
compatibility. Logs: red-matrix.jsonl and focused.jsonl.

Post-fix SC400 admission: 2.674 / 1.594 / 1.597 seconds; combined:
11.804 / 5.414 / 4.444 seconds. These wall times are noisier/slower than the
immediately preceding run, including the unchanged payload-reader stage; they
do not prove end-to-end performance equivalence. To isolate the changed SQL,
a temporary overlay probe admitted 400 sources, then alternated old/new casts
on the same 256 requested keys and stored rows (two warmups, 20 samples each).
Median query+scan time: DATETIME 217.972 ms, DATETIME(6) 216.067 ms. Both plans
use idx_connector_event_retry, with no full scan, and all returned deadlines
match. The controlled comparison shows no observed added cast cost; do not
attribute the shared-host SC400 variation to this change or claim a new speedup.
Logs: perf-before.log, perf-after.log and cast-cost.log. No benchmark framework
or alternate SQL path was added to production.

Frozen correction verification passed: 515 tests/subtests across the complete
events and workqueue owner packages, zero failures/skips (140.741s / 34.072s),
and full-module build. Independent read-only review found no concrete blocker
in the precision change or its regression matrix. No consumer pin, schema,
L1/L2 metadata or deployment changed. Logs: owners.jsonl and build.log.
With the same Core fixture and TiDB-required environment as below:

```sh
go test -p 1 ./features/connector/events ./features/connector/workqueue -count=1 -json -timeout=180s
go build ./...
```

## 2026-09-03: bounded admission reads

Reopened head `7738511` against base `16e48a8` for the original bulk-ingress
lock-hold problem. Two regressions belong to this delivery: settle aggregation
lost the physical pack predicate, and successor selection lost its bounded
ambiguity witness. On canonical local TiDB 8.5, 20,000 unrelated future Events
changed the settle index scan from 1 to 20,001 rows. With 33 ambiguous successors,
the prior owner locked 2 rows while head locked 33 before refusing admission.
These supersede the earlier unmeasured-skew boundary note; they do not require
a new queue, index, scheduler, or identity cache.

Before implementation, temporary candidates compared scoped CASE aggregation,
full requested-key OR predicates, and scalar per-key MIN subqueries. The full
OR and scalar alternatives added small-batch planning/read cost. Keep CASE's
existing column-to-parameter identity comparisons and DATETIME(6), add only the
requested-pack WHERE predicate, and remove the forced global retry index.
Allow the optimizer to use either the existing pack/status index or the future
deadline index: forcing the former could instead scan expired pending history.
Singleton semantics, duplicate fallback caching, transaction scope, and lane
and projection writes remain unchanged.

Successor admission reads at most K+1 rows for K frozen projection keys (K <= 128).
Existing exact duplicate rejection plus exact requested-key membership makes
truncated results fail closed, including SQL-equal but byte-distinct corrupt
keys. Missing successors are valid. This bounds the result/observed lock set,
not the underlying scan or sort; it is not a per-key two-row limit for K > 1.
Completion still uses its own singleton successor reader.

Temporary combined-candidate evidence: 27 distinct tests/subtests succeeded,
including precision, typed-source matching, worker walking, promotion races,
rollback, and cap defenses. One native fallback measurement first refused an
expired five-second setup window, then passed unchanged in isolation. With
256 requested keys, both 20,000 other-pack future rows and 20,000 same-pack
expired rows required only 256 index/row reads. Native other-pack workload
retained all 20,000 inputs; selected fresh/fallback admission samples averaged
32/57 ms. These are local samples, not production latency promises.

The original SC400 workload passed three runs each: head admission
1.510/2.317/1.718 s; candidate 1.634/1.898/1.580 s. All 4,400 Events and 4,000
projection inputs were checked. Unchanged payload-reader timing varied/slowed,
so this proves retention of admission performance, not end-to-end speedup.
Detailed pre-implementation logs: /private/tmp/pr333-full-picture.HKu7uA/.

Permanent verification matrix: other-pack live and same-pack expired scan
scope; K1/K3/K128 ambiguous successor bounds; duplicate/SQL-alias refusal and
whole-service rollback; healthy partial/full successors across 128-key chunks;
existing precision/collation/fallback/claim/complete tests. Tests use canonical
TiDB fixtures and behavioral/physical evidence, not SQL-text or index-name
expectations.

Shipping verification, run `pr333-final-fix.sD8B8h`: production changes in this
commit on parent `7738511`, canonical local TiDB 8.5 at `127.0.0.1:4001`, Core
fixture checkout `NexusCRM-pr329-root-fix` (`08118fc`, canonical schema unchanged
from `f38904ee`). All databases were disposable synthetic fixtures; no remote
or production data was read and no deployment was performed.

- Red control: the permanent scope, successor-bound and ambiguity tests with
  only the two production files overlaid from `7738511` failed as expected.
  The old query scanned 20,256 rows for 256 targets; the old successor reader
  locked 33/160 rows for K=1/128; both SQL-alias cases were wrongly accepted.
- Frozen candidate, no overlay: `go test ./features/connector/events -run
  '^TestIngressBatch' -count=1 -json -timeout=180s` passed 25 tests/subtests,
  zero failures or skips. Both 20,000-row backlog shapes read 256 index rows
  and 256 table rows. Successor lock counts were 2, 2, 4 and 129 for
  (K, ambiguous rows)=(1,33), (3,2), (3,4), (128,160). Healthy chunk coverage
  retained all 455 inputs exactly once across 260 generations.
- Complete changed-owner suites: `go test -p 1 ./features/connector/events
  ./features/connector/workqueue -count=1 -json -timeout=240s` passed 528
  tests/subtests, zero failures or skips; Events 129.959 s, Workqueue 21.982 s.
  The SC400 walking slice retained all 4,400 Events, 800 WorkItems and 4,000
  projection inputs. Admission was 1.972 s and unchanged payload reads were
  6.718 s in this full-suite run; this is not a controlled timing comparison.
- `GOWORK=off go build ./...` and `git diff --check` passed.

Test commands used `GOWORK=off`, `NEXUSCRM_TIDB_REQUIRED=1`,
`NEXUSCRM_REPO_ROOT=/Users/ian/Work/NexusCRM-pr329-root-fix`,
`TIDB_HOST=127.0.0.1`, `TIDB_PORT=4001`, and `TIDB_USER=root`. Logs are
`/private/tmp/pr333-final-fix.sD8B8h/{red,focused,owners}.jsonl` and `build.log`.
The old code failed the intended assertions, not fixture setup or compilation.
No additional mechanism or performance tuning is needed for these two scoped
regressions; test3 rollout and end-to-end HubSpot throughput remain separate
environment verification.

## Reproduction

Use a checkout of Core f38904eea78c6d096cae2210899e415a3c89fddb as
`NEXUSCRM_REPO_ROOT` and a disposable local TiDB 8.5.0 instance. The existing
fixture creates and cleans up its own synthetic databases. The original run
used `TIDB_HOST=127.0.0.1`, `TIDB_PORT=4001`, and `TIDB_USER=root`.

With `GOWORK=off` and `NEXUSCRM_TIDB_REQUIRED=1` set:

```sh
go build ./...
go test -p 2 ./... -count=1 -json -timeout=240s
go test -race ./features/connector/events ./features/connector/workqueue \
  -run 'Test(IngressBatch|LaneMaintenanceBatch|Service_SourceProjectionRetry|Service_WorkItemOwnedConcurrentReplay)' \
  -count=1 -json -timeout=120s
go test ./features/connector/events \
  -run '^TestSourceProjectionRangeSC400WalkingSlice$' -count=3 -v -timeout=120s
```

For the performance comparison, run the final command separately on main
16e48a85a0da6c70b2cc32d735281cfcd888c9b0 and this candidate against the same
TiDB instance and Core fixture, without overlapping test runs.