# Queue cost and Integration dependency resilience ## 2026-09-05 Test2 Integration outbound dependency backlog - Run/commits/environment: read-only Test2 L0 observer `33956035415`, window `2026-09-05T08:13:14Z..08:43:14Z`; deployed Connectors `v0.2.22-exp.owner-lifecycle.3` (`30e98db`), Core `612bdc3b`, and L2 observer `9b200539`. The bounded queue sample contained the complete 1,251 command rows, so no sampled group was omitted. - Profile result: 933 commands are `association_link_pending` retries with 44,544 persisted retry turns. Current Catalog revision 27 owns 189 of those commands and 11,936 turns; another 69 pending commands are behind active lane heads. In the 30-minute window the owner performed 1,187 retry updates, matching approximately six capped five-minute retries per waiting command. `ClaimNext` executed 1,796 times at 112.269 read RU and 1,733.2 keys on average. `NextWorkAt` executed 604 times at 122.325 read RU and 2,921.2 keys on average. The claim plan uses a due-state index range, an anti join over active same-lane predecessors, and a TopN for the computed wake ordering. - Root cause: an absent association Remote Link is a durable prerequisite, but the current worker stores it as an ordinary safe retry. Per-command exponential backoff is functioning as implemented; 189 staggered commands at the five-minute cap still produce nearly continuous claims. SQL tuning alone would retain work that cannot make progress. - Correctness constraint: association resolution accepts the current target action slot and every declared previous action slot. A durable wait keyed only by the current Remote Link can miss a later previous-slot finalization and strand the command. Remote Link remains readiness authority; any stored dependency identity is a wake hint and must close ready-before-register, register-before-ready, restart, and concurrent finalization windows without allowing a same-lane follower to bypass the waiter. - Next hypothesis/gate: persist one stable association dependency wake key derived from the trusted scope, connector owner, source object/record, and destination slot, while rechecking the exact current/previous Remote Link candidates before entering a non-runnable wait. Finalization computes the same wake key and atomically returns matching waiters to the runnable queue. Validate current/previous-slot wake, harmless unrelated-slot wake and re-wait, no lost wake under real TiDB races, crash recovery, FIFO, and a 1,000-waiter quiet-window query-count/plan bound before changing claim SQL. Re-profile the same workload after the state fix; retain a separate claim rewrite only if valid runnable work remains a high-RU owner. ## 2026-09-05 Dependency wake implementation loop 1 - Candidate: current Connector main `9b0104c` plus the dependency-wake behavior, using current Core main `a85206627` plus the catalog and warm-schema candidate. - Verification: focused unit behavior and the real-TiDB dependency lifecycle, concurrent registration/finalization, interrupted-registration recovery, and schema-drift cases all passed. The Core warm upgrade separately preserved a retained command, resumed after one previously added column, remained idempotent on a second pass, and rejected a wrong binary-key collation before issuing later DDL. - Learned: the two-phase `dependency_registration` state closes the missing-row race on TiDB, and the stable association key lets a declared previous action slot wake the same logical waiter. Before freezing the candidate, explicitly prove that an obsolete action-slot event can only cause a harmless recheck, race both current and previous-slot finalization, and measure a 1,000-waiter quiet backlog against the production claim/schedule queries. ## 2026-09-05 Dependency wake implementation loop 2 - Candidate: same current-main Core and Connector pair as loop 1. - Verification: the real-TiDB lifecycle now includes an obsolete action-slot finalization that schedules one readiness recheck and returns to `waiting_dependency` with zero provider attempts. The concurrent test races registration against current-slot finalization on even iterations and previous-slot finalization on odd iterations; all twelve iterations passed. - Learned: the wake key may conservatively over-notify within one logical association without becoming readiness authority. Exact Remote Link keys are re-read before provider I/O, so metadata lineage stays authoritative and a stale slot produces bounded work rather than a false dispatch. Next measure the steady-state query behavior with 1,000 durable waiters and no runnable command. ## 2026-09-05 Dependency wake implementation loop 3 - Candidate: same current-main Core and Connector pair as loop 2. - Verification: a real TiDB table with 1,000 `waiting_dependency` commands returned no work hint, no schedule, and no claim after a one-hour clock advance; every row remained waiting and total RetryCount remained zero. The first observed local calls were 158.5 ms for the optional work hint, 21.6 ms for NextWorkAt, and 37.2 ms for ClaimNext. - Learned: the backlog no longer generates repeated claims or writes, but wall-clock time alone cannot distinguish cold statement compilation from row scanning. Explain the exact production lease-recovery, claim-candidate, and schedule statements on the same 1,000-row fixture and require bounded index scans before calling the design performance-safe. ## 2026-09-05 Dependency wake implementation loop 4 - Candidate: same current-main Core and Connector pair as loop 3. - Verification: EXPLAIN ANALYZE executed the exact production lease-recovery, claim-candidate, and NextWorkAt statements against 1,000 waiting commands. Every plan used the existing claim/lane indexes, scanned zero rows, and contained no full scan. A second end-to-end observation measured about 71 ms, 19 ms, and 29 ms respectively on the local TiDB while all 1,000 rows and zero retries remained unchanged. - Learned: waiting backlog cardinality is removed from the runnable read path; no claim-query rewrite or new lane cache is justified by this profile. Freeze the implementation and run race, complete outbound package, and repository verification. Test2 remains the authority for post-deployment RU reduction. ## 2026-09-05 Dependency wake verification - Candidate: frozen current-main Core and Connector pair from loop 4. - Verification: the focused real-TiDB dependency suite passed twice with the Go race detector; the complete outboundintent package passed; and the complete Connector repository passed sequentially with TiDB required. The live writer audit found one production Remote Link insert owner, and the same transaction now performs the indexed wake. Other Remote Link writes only maintain an already-finalized snapshot and do not create readiness. - Result: current-slot, previous-slot, ready-before-register, register-before-ready, obsolete-slot recheck, interrupted registration, same-lane FIFO, schema drift, and 1,000-waiter quiet-backlog evidence are all green. No public Command shape or established worker-store method changed. - Conclusion: release Core schema first, then Connector behavior through the quiescent cutover below, and measure the same Test2 window. ## Dependency-state rollout contract The dependency states introduced above are not rolling-compatible with an Integration worker built before this change. An older worker does not include `dependency_registration` or `waiting_dependency` in its same-lane predecessor set and does not wake dependency rows when it finalizes a Remote Link. Release this change only through a quiescent Integration cutover: 1. install and verify the additive Core schema. If the paired Core release includes Scheduled/Rollup digest reads, first stop and drain all old Runtime API/Worker producers and keep them stopped through the final schema reapply; follow Core's `docs/design/worker-queue-digest-cutover.md`. An earlier online schema pass is not the final backfill. The old Integration process may run only until its separate stop below; 2. stop every older Integration process that can claim the same outbound scope and prove that those processes are no longer running; 3. start the candidate Integration process only after that proof succeeds. Do not deploy this release through a blue/green or rolling path that overlaps old and new Integration workers. Test2's dedicated Integration controller and the D3 same-run Integration image replacement already use stop-then-create process replacement. A full Prod D3 promotion that retains its predecessor is not eligible for this Connector release until that deployment owner removes the Integration overlap. After a new worker has persisted either dependency state, an older Integration binary must not be restarted against that database. A rollback must first prove that no command remains in either state or restore the pre-activation database state. This is a release fence, not a second queue state representation. The steady-state worker adds no version probe; its two-minute safety pass reads only the indexed waiter page and the exact persisted Remote Link keys. ## Outcome and frozen baseline Remove broad per-lane maintenance scans and prevent transient dependencies from terminating the Integration role, without changing lane ordering, durable effect ownership, or fail-closed security. Base: Connectors `0e7d079`, Core `d95d5af81`. Test3 evidence used Core `015c75c2` and Connectors `fb343725`; its experimental V2 composition is not silently treated as main. No remote deployment or data repair is part of this implementation. 2026-09-02 test3 SQL history: the dominant next-head and demotion plans executed 468 and 362 times in one minute, averaging 7,322 processed keys each. The plans contained full scans. Security renewal SQL in the incident window recorded 3,043.88 ms maximum latency and 3,037.74 ms maximum RU queue wait; it had taken milliseconds before. This proves resource wait on the renewal path, but the historical first UNAVAILABLE has no individual RPC trace. Do not attribute every resource consumer or the exact first request from aggregate statistics. ## Current-tree ownership - Workqueue owns normalized target-first/source-fallback lane identity and readiness reconciliation: creation, projection changes, replay, completion, retry and blocked-head recovery all reach the same select/demote/promote path. - Core `nexuscrm-kernel/pkg/schemacatalog` already supplies source_lane_gen and target_lane indexes. Controlplane consumes schema authority; no tenant/L1 payload or new lane cache belongs here. - Global claim admission has a separate active-state-index bounded blocker query and CAS. Preserve its ordering/lease/projection guards and existing terminal-history regression; a point-lane optimization is not permission to redesign claim concurrency. - Outbound Service owns transient recovery around idempotent acceptance and fresh durable worker claims. Never retry a captured provider Dispatch. Durable dispatch checkpoints decide uncertain/recovery/writeback outcomes. - Readiness must verify configuration, schema and Runtime authority, not finish a real backlog/provider operation. Core composes ready callbacks from both lanes; permanent lane exits still cancel the peer and fail the process. ## Verification contract before implementation 1. Point-lane identity: target override, null/empty fallback, cross-kind key collisions, other pack/generation, pending/retry/blocked/processing and inactive projection successors; preserve due-time and Event ordering. 2. Real TiDB plans for all three maintenance operations with 100 and 10,000 unrelated live rows plus retained terminal history; record scans and latency, not only mocks or optimizer estimates. Reuse production indexes. 3. Existing enqueue, claim CAS, concurrent completion/materialization, retry, replay and blocked-head recovery walking slices against disposable local DBs. 4. Runtime unavailable/deadline/resource pressure and transient DB failures at schema, generation, list, acknowledgement, durable claim/checkpoint boundaries: recover without process exit or duplicate effects. Unknown/permanent errors, joined permanent+transient errors, and parent cancellation remain fail-closed. 5. Startup callbacks precede business drain; first work still runs immediately. Receipt readiness probes the actual ingress receipt SELECT with no rows. Core composition starts with busy lanes and cancels both on genuine failure. Run focused batches after each coherent boundary, then owner/race suites and full module after candidate freeze. Require local TiDB (skips are not passes). Only synthetic disposable schemas; never production or test3 records. ## Initial design hypotheses (not yet performance-proven) Expose target/source equality to existing indexes while preserving the exact effective-lane relation, including target K matching source K with absent target. No new persisted lane fact or cache. Validate actual TiDB OR/index-merge plans before choosing a larger rewrite. Retry at lifecycle ownership with bounded backoff and explicit transient error types; do not hide schema/identity errors. Performance loop results and counterexamples must be appended before the next optimization change. ## Local baseline / first measurement Run `lane-maintenance-baseline.log`, Connectors baseline production queue code, local TiDB 4001, synthetic isolated databases. With 100/10,000 unrelated active plus equal terminal rows, next-head scanned 202/20,002 rows (18.45/27.99 ms), demotion scanned 201/20,001 rows (7.36/18.06 ms). The baseline test failed as expected. Promotion initially measured zero rows because EXPLAIN ran after the first promotion had changed the row; that is not valid proof. Restore the mutation input and seed unrelated processing rows before the next comparison. Hypothesis remains exposing lane equality to existing indexes; no queue production edit has been made at this measurement boundary. The corrected baseline (`lane-maintenance-baseline-complete.log`) confirms 202/20,002 next-head and 201/20,001 demotion scans. Promotion's UPDATE plan does not expose its subquery scans in the current extractor; zero is not yet performance proof. Next change: rewrite the three point-lane predicates with direct target/source equality, then inspect the processing-blocker query itself as well as the mutation plan. ## OR predicate falsification `lane-maintenance-or.log`: direct target/source OR preserved the model but did not change TiDB's chosen access path. Next-head still scanned 20,002 rows and demotion 20,001 at the large size. Do not claim the predicate rewrite is a performance fix. Next bounded probe: request the existing source/target index merge explicitly; if it cannot produce lane-bounded plans for mutations too, use disjoint indexed branches. No extra stored lane identity is justified. ## Named index-merge falsification / selected design `lane-maintenance-index-merge.log`: naming both indexes selected an intersection of broad pack prefixes, not a union of lane ranges. Head scans grew from 496 to 49,006 rows (demotion 49,005). Remove this failed path. Use disjoint indexed UNION ALL for head selection, two direct indexed demotion UPDATEs, and two indexed NOT EXISTS processing checks. Reconciliation already has separate select/demote/promote statements; inside WithTx they share the caller's transaction, outside it the claim CAS remains execution authority. Stop on either demotion error before promotion. This avoids derived UPDATE snapshots and preserves live state/due guards without new storage or optimizer switches. The guarantee is bounded by existing source/target indexed key ranges, not strictly by effective-lane size: same-source rows with an overriding target and same-target rows from other generations can still be residual-filtered. Cover these distributions and state the bound honestly. ## Disjoint branch proof `lane-maintenance-disjoint.log`, same local TiDB/database fixture: head reads 4 rows at both 100 and 10,000 live + equal terminal history (large case 16.30ms). Demotion reads 2/0 rows in target/source branches (large case 8.50ms combined). Both exact processing-blocker SELECTs use lane index ranges with zero matches, not the 1,000 unrelated processing rows. No full scan. The UNION/two-UPDATE design is retained; extend correctness/failure coverage before freezing. ## Lifecycle batch counterexample `resilience-owner-first.jsonl`: startup/recovery classification tests passed, but the real outbound `NextWorkAt` test failed. TiDB exposes the computed MIN/CASE/UNION wake time as bytes, so `sql.NullTime` rejects it and production falls back to polling. This path is inside the recovery scheduler being proven, not an unrelated failure. Preserve the SQL time type with an explicit final DATETIME(6) cast; reuse the existing exact pending/expired/writeback test. ## Frozen local verification and release boundary Candidate: the working-tree diff on `codex/sync-queue-resilience`, paired with Core `codex/integration-startup-resilience`. Full logs are under local artifact directory `/private/tmp/test3-hubspot100.EaUVjz` (synthetic local TiDB only). - Workqueue: all 163 top-level tests passed, including real concurrency/CAS, projection lifecycle and point-lane performance/identity tests. - Events: all 257 top-level tests passed across two complementary batches. The first batch hit its aggregate 180-second limit with the active test only one second old. That test passed alone in 0.686s; the 181 not-yet-completed tests passed in 28.004s. No product assertion failure was hidden by the split. - Outbound: all 105 top-level tests passed under the race detector, including committed-result response loss (writeback succeeds) and uncommitted-result loss (uncertain), each with exactly one provider dispatch. The prior mock expectation was updated for the SQL time cast; its focused rerun passed. - Ingress receipts 31, webhook/router 191, dependency retry 3: all passed. Key queue CAS/maintenance and receipt readiness tests also passed with race. - Core Integration: all 41 top-level tests passed normally and with race using the paired source candidate in an external temporary Go workspace. - Total 791 distinct top-level owner tests; no functional skips. Whole Connectors module compile succeeded; packages with no tests are not counted as functional proof. No unrelated full-module functional suite or live HubSpot/test3 acceptance was claimed. Independent review confirmed indexed branch disjointness, preserved claim CAS, fresh-claim retry safety, permanent-error precedence and immediate initial work. Its standalone `Worker.Run` compatibility concern was resolved by retaining that entrypoint's first-turn startup contract with safe retry and dedicated tests; production `Service.Run` owns schema/authority-only readiness. Release blocker is explicit: Core still pins Connectors `v0.2.20-exp.4695.5`, which has no `RunReady`. The Core source patch is a paired follow-up, **not independently buildable/shippable against its current pin**. Publish the reviewed Connectors candidate, update Core's real version pin, and repeat clean-dependency build before release. No local replace was added to either tracked module. Port/rebase onto the actual test3 experimental branch and freeze its BOM before any environment validation; these main-based local tests do not certify that different deployed V2 composition. No deployment, production-data access, remote test dispatch, automation resume, or external provider request was performed by this implementation. ## Review reopening: tool schema and partial maintenance failure The pressure tool's supported normal-enqueue path was missing the canonical target-lane index. With two same-lane events, the base succeeded and this candidate failed with error 1176. Adding only the existing canonical index to the fixture restores admission; no production migration or fallback is needed. The permanent tool regression now executes its real createSchema and seedNormalEnqueue for both ordinary and workflow-action inputs and reads back durable Event/WorkItem counts plus one ready head per lane. Replace the SQL-mock demotion-stop test with a real TiDB producer-to-consumer test. Inject failure after the first demotion commits or before promotion; observe durable blocked rows, then require a fresh normal consumer to claim and complete every row in lane order, with and without unrelated ready lanes. This does not promise bounded recovery latency under perpetually saturated traffic: conditional blocked-head repair is also present in the base. 2026-09-02 local proof, synthetic TiDB 127.0.0.1:4001, on `8faeed9` plus this follow-up: both tool scenarios and all four recovery cases passed with race detection. The initial recovery test omitted a required claim input FencingToken and was rejected before claiming; supplying the normal distributed claim input fixed the test, with no production change. The existing maintenance identity and performance cases passed: head scans 4, demotion scans 2/0 at both 100 and 10,000 unrelated live plus equal terminal rows, no full scans. Logs: `/private/tmp/pr331-review-evidence.pva24S/fix-owner-tests.log` and `fix-final-race.log`. Required TiDB mode was enabled; no functional skips. ## 2026-09-05 - Dependency wake fallback and worker audit - Candidate/environment: PR #317 commit `b1489e0`, Core `3230b03c7`, local TiDB `v8.5.0`; Test2 baseline remains run `33956035415`. - Correctness gap: Remote Link finalization and its durable waiter wake share one transaction, and the registration race is closed, but a future bypass writer or missed wake leaves `waiting_dependency` outside Claim, HasWorkHint, and NextWorkAt forever. The normal 30-second recovery pass cannot see it. - Performance constraint: restoring ordinary retries is invalid. At a fixed two-minute retry, the 189 observed waiters would execute about 2,835 complete preparation retries in 30 minutes, more than the 1,187 already observed. The fallback must recheck only persisted exact Remote Link keys and must not reopen metadata, records, profiles, mappings, provider requests, or the provider retry budget. - KISS model: producer finalization remains the fast path. A two-minute safety pass pages the exact scope's wait rows through a state/id index, point-reads only their persisted candidate LinkKeys, and uses one idempotent state CAS to wake a ready row. Missing dependencies cause no row update and no retry-count change. Event wake and safety wake converge on the same existing runnable state and claim path. - Separate inbound finding: the normal due dependency probe uses the correct `(State, DependencyProbeAfter, WorkId)` range and scanned zero rows (0.475 read RU), but the five-second loop first performs a structural invalid-row LEFT JOIN/REGEXP audit. With 1,000 valid waits it full-scanned all 1,000 WorkItems and Events and consumed 9.334 read RU per call. Structural validation belongs at the only writer and bounded startup audit; the hot fallback should select only indexed due rows. - Next change and proof: persist the exact LinkKey list, add one scope/state/id reconciliation index, remove the inbound structural audit from the five-second loop, and preserve it at startup. Prove immediate wake, wake-loss recovery within one safety interval, concurrent event/safety wake idempotence, and no full scan with 1,000 waiting or foreign-scope rows. - Local result: the production path remains producer-transaction wake first; the safety pass runs every two minutes, selects at most 1,000 waiters through `idx_integration_outbound_dependency_reconcile`, and point-checks only the exact LinkKeys saved when preparation blocked. A missing dependency performs no command write and changes neither provider attempts nor retry count. Twelve registration/finalization races, a deliberately missed producer wake, repeated safety passes, previous-slot lineage wake, and the released custom store retry fallback all passed. Both generation-scheduled production and legacy-source service entrypoints run the same safety pass when their store supports durable dependency waits. With 1,000 foreign-scope wait rows, the reconcile page, normal claim, next-work, and lease-recovery plans each examined zero rows outside the target range and reported no full scan. - Verification: the complete outbound-intent owner package passed against real TiDB in 21.236s and the inbound supervisor package passed in 0.398s. The five-second inbound loop now executes only the indexed due/expired read; structural invalid-row discovery remains in the bounded startup pass and individual due rows are still validated before reconciliation. Post-rollout Test2 RU remains the release-level comparison; local plans prove the query shape rather than forecasting cloud RU from elapsed time. - Final liveness review found four gaps and all were fixed at their existing owner boundaries. The bounded inbound due/expired query now left-joins its Event point read, so an Event deleted after startup returns an invalid waiter that the existing terminalization CAS can release instead of hiding it forever. The outbound dependency safety pass now uses the shared transient dependency retry boundary; an intermittent TiDB lock or connection failure backs off inside the long-lived service instead of stopping Integration. Successful CAS wakes from a partially failed pass are retained for the next worker drain. Dependency registration now likewise returns only its durable wait-write error: the already handled `association_link_pending` outcome is not joined back into the recovery classifier, so a transient write failure keeps the worker alive while an invalid dependency is still terminalized. The exported direct `Worker.Run` entrypoint invokes that same reconciliation method on the same two-minute safety interval as `Service.Run`, so direct Worker consumers cannot strand a waiter after a missed producer wake. Real TiDB proved an orphaned due waiter remains visible and is classified invalid; a service test proved one bad connection retries after 250 ms and then drains the woken command. Full workqueue, outbound-intent, and inbound-supervisor owner packages passed again in 47.167s, 36.010s, and 0.346s; a direct-Worker missed-wake test proves it reclaims the command. ## 2026-09-05 review correction: dependency probe cost and scheduling budget - Candidate reviewed: Connector `59159ea`, Core `9bbb56f85`; local TiDB v8.5.0. - With 1,000 existing Remote Links, `BINARY LinkKey = BINARY ?` selects an `IndexFullScan`. The waiter-page bound therefore did not bound dependency lookup cost. An indexable equality plus the binary residual selects Point_Get. - Explaining an uncorrelated EXISTS can return only Projection/TableDual after evaluating the subquery; absence of Scan nodes is not zero-read evidence. - Next change: share the exact indexable LinkKey query between registration and safety checks, test the inner read on populated canonical schema, and give each safety turn a deadline with resumable progress. Keep normal event wakes and provider retry accounting unchanged. The safety turn has a one-second database/retry budget and at most 1,000 waiter probe attempts, shared across transient retries. On exhaustion it preserves the last completed cursor and returns to normal scheduling; an unfinished page also triggers one normal drain because a timed-out wake may have committed. All three entrypoints use this same worker boundary. Parent cancellation still stops the owner. A two-minute tick is not a two-minute per-waiter recovery SLA: larger or slow pages span ticks, and a process restart begins the sweep again. - Final correction evidence (local TiDB v8.5.0, canonical Core schema): 1,000 existing Remote Links retain Point_Get for present, absent and wrong-case keys, including the registration lock read. A real lock timeout after the first CAS retains its wake/cursor; two later bounded pages wake the remaining commands without increasing attempts or retries. The normal, legacy and direct Worker entrypoints all continue normal delivery after a safety deadline. A transient retry consumes the remaining waiter budget and resumes its completed cursor. - `NEXUSCRM_REPO_ROOT= go test -count=1 -timeout=180s ./features/connector/outboundintent ./features/connector/workqueue ./application/connectors` passes with no skips (30.339s / 17.545s / 0.574s). `go build ./...` and `git diff --check` pass. Production Test2 SQL/RU comparison remains a deployment verification, not a claim inferred from these timings.