# Projection materialization correctness and performance Current design: [durable input ranges](source-projection-input-ranges.md). Earlier entries below record PR #329 experiments; their mixed-order inference and missing-work repair are superseded, not current runtime guarantees. ## Baseline / 2026-09-02 Candidate: Connectors main `9b44792258a708b8337db7f229c38f777d5e26b9`. Environment: local TiDB, disposable synthetic databases; no remote data. Workload: duplicate A after A/B coalescing, with unclaimed, processing, queued-successor, terminal and missing-task states. Observed: unclaimed/queued selection regresses; processing/terminal duplicate creates an unnecessary generation; legitimate missing-task repair succeeds. The four-case lifecycle diagnostic took 1.39 s (package 1.723 s), with three expected assertion failures and one pass. This is correctness evidence, not a production latency baseline. Current-tree audit: Core main already owns `idx_connector_work_item_projection_order(ProjectionKey,LaneOrdinal,EventId,WorkId)`. The local Event integration-test fixture omitted it; align the fixture rather than invent another index or a coverage cache. No schema migration is needed. Implementation hypothesis: one indexed coverage decision in ordinary projection materialization can suppress covered duplicates without rewriting Event facts. Positive-ordinal lookup and zero-ordinal detection use the existing index; historical fallback and coverage-inclusion checks are cold paths. Existing fresh-batch partitioning must remain intact. Preserve current transaction/lock ownership and use read-committed ingress reads so a waiter sees immutable Events selected by a materializer that committed after its transaction began. Next evidence: original and lifecycle regressions, missing repair, replay, mixed-order coverage preservation and concurrent snapshot/claim/completion; then history-scaling query plans and owner-level functional batches. Record measurements and counterexamples here before another optimization change. ## Candidate first measurement / 2026-09-02 Run: `projection-history-scaling.log`, same base plus candidate edits, local TiDB at port 4001. Each isolated database contains 100 or 10,000 retained generations of one projection; 32 covered single duplicates and 32 batches of 10 fresh Events. Duplicate p50/p95: 21.70/36.39 ms versus 71.77/99.01 ms. Fresh batch p50/p95: 40.77/49.39 ms versus 141.69/196.32 ms. The new coverage seek uses `projection_order`: 64 scanned index rows for a hit and zero for a miss at both sizes. Thus bounded scanned rows alone do not prove bounded end-to-end latency. Compare unchanged main under the same fresh workload before attributing the slowdown to the candidate. Independent review also found that Event wrappers discard the materializer's nonselection result. A covered duplicate batch therefore performs unnecessary lane readiness reconciliation. Next change: return no touched lane when ordinary projection materialization is a no-op, including the fresh-batch caller; preserve reconciliation for actual mutations and explicit replay. Verify covered batches leave all WorkItem fields unchanged and mixed batches still materialize new input. ## Baseline comparison and frozen candidate Runs: `projection-performance-baseline.log` (unchanged main production code), `projection-owner-final.log` (candidate including no-op propagation). Same TiDB instance, fixture index and 32 batches of 10 fresh Events, separate disposable databases. Main fresh p50/p95: 84.70/103.79 ms at 100 histories, 230.82/328.88 ms at 10,000. Candidate: 41.82/87.83 ms and 134.74/173.03 ms respectively. Candidate covered-duplicate p50/p95: 27.65/69.90 ms and 110.22/279.80 ms. Main does not meet duplicate correctness, so its duplicate timings are not a valid success baseline. Both builds exhibit history-sensitive latency in this shared local standalone TiDB, including the same bounded index query. These wall-clock samples do not establish a production speedup or a flat latency bound. What is established: the candidate preserves fresh batching, uses the existing ordered index without a full-history scan on the positive-ordinal path, and removes covered-duplicate selection writes and batch reconciliation. No extra cache, table, index or provider-specific branch is introduced. Historical zero/mixed ordering remains an explicitly cold, potentially history-dependent correctness path. The owner batch passed on the frozen production candidate: Event enqueue, projection history/input loading, claim/completion handoff, ordinary and explicit replay, historical compatibility, both duplicate batch cases, and the concurrent snapshot regression. Full module and race verification follow this boundary. ## Current-tree ownership and verification map - Inbound receipt producers call Event service enqueue. Single/mixed admission uses `ensureWorkItemForEventWithReplay`; fresh batches partition all immutable Events into ordinary work and one head per source, then call `ensureWorkItemForFreshEvent`. Both ordinary projection paths converge on Workqueue's `EnsureSourceProjectionWorkItem`. - Workqueue owns active/queued selection and terminal history, and already owns `eventAtOrBeforeSourceBoundaryPredicate` and missing-coverage readiness. The materialization guard reuses that membership fact rather than defining a new order or relying on Event executor status. - Event `LoadSettledProjectionPayloads`, inbound WorkItem claim/worker input adapters, and WorkItem claim/finalize consume that durable selection. No execution, lease or completion protocol is replaced. - `ReplayDeadEvent` and `EnsureSourceProjectionReplayWorkItem` remain the explicit replay boundary. A terminal/dead generation is represented work, not permission to execute its inputs again during ordinary receipt retries. - `sqltx.BeginPessimisticReadCommittedTx` supplies the existing isolation mechanism; namespace/pack and active/queued lock ownership stays in the existing owners. - Core's schema catalog already supplies `projection_order`; only the test schema was missing it. L1/L2 configuration, provider OAuth, remote deployment and recovery of previously stranded test data are outside this code change. Verification matrix: single/fresh/mixed/duplicate receipt entrypoints; unclaimed/processing/retry/queued/terminal/missing-task lifecycle; positive/equal/missing provider time and legacy-zero/mixed source order; same/different route scope; transaction waiter and concurrent completion; explicit replay and ordinary route non-regression; 100/10,000-history query plans. ## Final verification / 2026-09-02 - `projection-owner-final.log`: Event/Workqueue projection, enqueue and replay owner batch passed, including all newly added regressions. - `projection-connectors-full.jsonl`: `GOWORK=off go test -json ./... -count=1 -timeout=240s` against the required local TiDB executed 3,247 passing test/subtest entries and two failures; no individual integration test was skipped. One failure was the partial-schema fixture dropping `ProjectionKey` before dropping the newly aligned production index. Its setup now drops that index first and the actual compatibility path passed in the final race batch. - The other full-suite failure, `TestRepositoryWorkHintCoversPendingExpiredAndWritebackAgainstTiDB`, is outside this diff: a `MIN(scheduled.WakeAt)` result cannot scan from bytes into a time. The same isolated test fails identically on unchanged main `9b447922` in `projection-unrelated-main-baseline.log`; outboundintent has no edits here. The full repository suite is therefore not claimed green. This independent existing failure is retained as a disclosed baseline issue, not bypassed. - `projection-race-final.log`: `go test -race` passed the duplicate, mixed-batch, historical admission, snapshot waiter, concurrent completion, retry, partial schema and Workqueue materialization/replay tests. Event package 11.335 s, Workqueue 1.474 s. No data race reported. - `projection-build.log`: `GOWORK=off go build ./...` succeeded; `git diff --check` succeeded. All database evidence uses disposable synthetic local databases. No production or test3 data was read/modified for this implementation, no deployment was run, and existing stranded remote inputs have not been declared repaired. ## Root fix implementation loop / 2026-09-02 Candidate bases: Connectors `0e7d07939c98fb14b5a7bee13c08fafb88331956`, Controlplane `4ebf6c12c87ebe599ebd3c0fbc9087510bf05215`, Core `d95d5af81fb539bbc793483e3826bdcfd27bed33`; coordinated branch `codex/projection-input-ranges`. Local TiDB v8.5.0 at port 4001, isolated synthetic databases, shared developer host. The reopened counterexample H(legacy zero ordinal), P(fresh ordinal 1 with an earlier wall clock), Q(fresh ordinal 2) falsified both admission-only repair and timestamp-based payload loading. Positive ordinal ranges are the new membership authority; no retained terminal history is scanned at execution. Before implementation, the disposable prototype and unchanged merged baseline ran identical workloads. At 100 histories, load 20–60 inputs median was 17.47 ms baseline vs 5.38 ms range; at 10,000 histories, 1223.49 vs 3.89 ms. In a 4,400-Event workload (4,000 projection inputs across 400 interleaved sources plus 400 commands), all 4,000 inputs were loaded exactly once. Three-run median admission+load was 11.571585 s baseline vs 7.535521 s prototype. Admission medians: 8.361746 vs 6.511214 s; load: 3.283735 vs 1.024307 s. Existing indexes bounded the new range to 60 rows and duplicate seek to one row. These are local measurements, not a production latency guarantee. Prototype evidence: `/tmp/pr329-range-cost.log`, `/tmp/pr329-baseline-cost.log`, `/tmp/pr329-range-sc400-repeat.log`, `/tmp/pr329-baseline-sc400-repeat.log`. The actual implementation now includes canonical schema, admission/protocol gates, claim validation and migration. Its first seven real TiDB range tests passed in 6.891 s (`/tmp/pr329-root-ranges.log`). Independent migration review found and fixed coalesced-skipped boundary confusion, replay+fresh membership and competing per-event ownership; a first 39-case migration batch passed without skips. Owner-suite evidence exposed old test-fixture DATETIME(6)/index drift from the canonical schema, as well as assumptions that WorkId equals ProjectionKey. Tests now use canonical tables, whole-second legacy clock fixtures, and the separate identities. Replaced SQL-choreography tests are covered by real range, deadline, replay and frozen-generation tests. The missing-work repair expectation was replaced with a corruption/rollback assertion. Next measurement: run the same history and 400-source workloads on the complete candidate after correctness batches are green; record the observed result here before making any further optimization change. Final structural cost audit found one remaining old-duplicate scan: filtering `InputStartOrdinal IS NOT NULL` after seeking ordinal zero can walk all retained legacy rows. Independent review confirmed the index/filter ordering. The immutable cutoff PK read now precedes that seek; old receipts return immediately. Modern duplicates add one bounded PK read. No new index/cache or historical coverage query is needed. Measure this final shape only after full correctness runs finish, without concurrent test load on the shared TiDB. ## Complete candidate measurements / 2026-09-02 Run `/tmp/pr329-root-performance-final.log`, identical history and SC400 workloads, three repetitions, no concurrent test batch. Each history sample loads 20–60 new payloads. Medians of the three run medians: | Histories | Admit 10 | Load 20–60 | Duplicate | |---|---:|---:|---:| | 100 | 16.051 ms | 3.375 ms | 10.545 ms | | 10,000 | 143.208 ms | 3.774 ms | 20.185 ms | All range/duplicate index-plan assertions passed. Payload lookup stays bounded as history grows. Total admission still shows history sensitivity in this legacy Event-owned retained-history fixture; the change does not claim flat end-to-end ingress latency. The range membership queries remove the historical coverage work, not every legacy scheduling check elsewhere in the runtime. SC400 admission+load samples: 5.638386, 6.145620, 10.341305 seconds; median 6.145620 s. Admission median 4.784550 s, payload-read median 1.361070 s. Every run retained 4,400 Events, 800 WorkItems and exactly 4,000 distinct projection inputs. Earlier unchanged baseline median was 11.571585 s for this workload. The shared-host variance is visible; this is local evidence, not a production SLA. No further optimization is proposed in this correctness delivery. Full Connectors run: 3,231 tests/subtests passed, no skips; one unrelated outbound-intent time decoding failure reproduced on unchanged merged main (`/tmp/pr329-root-outbound-baseline.log`). Full Controlplane with its own core pins: 4,886 passed, 12 unrelated environment-dependent skips; one unchanged frontend source-fixture test references a lock file removed from current Core, also reproduced in isolation (`/tmp/pr329-root-packs-isolated.log`). Neither package contains changes from this fix. A workspace-wide Core override also exposed pre-existing SQL-mock coupling to newer persistence index reads; the release-boundary run uses Controlplane's pinned dependencies instead. The shared canonical walking runner passed: 19 Event/ingress tests/subtests, real worker-to-Flow payload assembly, 800 receipt/800 WorkItem pressure path, 58 migration proofs, and Core process startup before/after the protocol gate; no skips. Logs are under `/tmp/pr329-canonical-walking/`. Canonical schema tests and the Workqueue/Inbound owner suites also passed. The old two Connectors-only trusted ingress workflows move to Core's existing TiDB CI job, which already owns the canonical schema checkout and private module credentials; local and CI execute `tests/integration/connector-projection-ranges.sh` unchanged. The final release-dependency proof reran that same runner with `GOWORK=off`, Connectors `64c0b1e55325308d0da43e46862925178fe716ca` and Controlplane `71f38dee60f92225d4109024fdbadcbf88e6efb0` pinned in Backend, against Core's canonical schema from `83b7cf3fbc346d5764775eae1b3919ea56ef6588` plus its host activation gate. The same 19 Event, one worker, one pressure, 58 migration and one host proofs passed without skips. Logs: `/tmp/pr329-release-walking/`. Connectors and Controlplane also built with their actual published dependency graphs and no temporary workspace overrides. ## Checkpoint-only cutover revision / 2026-09-03 Reopened evidence rejected the receipt-only prototype without a cutoff: it silently acknowledged missing new-protocol tasks. The retained cutoff remains one immutable protocol fact per incarnation; historical range reconstruction has been removed. Old tasks drain under the old runtime. Controlplane audits retained responsibility and writes only the boundary, leaving all historical Event/WorkItem columns unchanged. Earlier conversion measurements above describe the superseded candidate, not the current release procedure. The reopened native-missing counterexample also showed that selected Event status could bypass coverage verification. Source duplicates now resolve their projection identity and verify coverage before that shortcut, including while the real WorkItem is processing and after it finishes. Ordinary terminal receipts without a source lane retain their existing fast return. Current three-repository workspace proof: Core's unchanged shared runner plus its newly required duplicate/cutover cases passed 27 Event tests/subtests, one real worker-to-Flow boundary, one 800-receipt pressure case, 36 checkpoint proofs, and one host composition test; zero skips. An additional two-binary test used merged #329 to enqueue/drain, the actual new Controlplane CLI to checkpoint, then the new producer/worker to execute. Every retained old Event/WorkItem column stayed unchanged. Logs: `/tmp/pr329-checkpoint-rootfix/walking/` and `/tmp/pr329-checkpoint-rootfix/actual-cutover.log`. One parallel owner batch timed out the 30-second enqueue/claim loop while new workspace dependencies were compiling. Its isolated rerun passed in 5.01s, and the final shared runner also passed it. This is disclosed rather than counted as a green first run. No timeout or concurrency assertion was weakened. This revision does not change native payload range/index selection. The prior unmodified-history probe (100/10000 retained rows) read 20–60 current inputs in median 6.8/4.9ms, but admission retained other history-dependent costs. These observations do not establish constant total enqueue cost or production latency.