Scheduled discovery capture: worker validation

Status: v1 and v2 failed before source writes; both baseline recoveries verified. The warehouse view is repaired and independently verified. V3's scheduled two-event canary and all 21 report-publication checks passed; its final platform check failed after an independent application release changed configuration. The earlier manual ClickPipe test remains separate from scheduled worker adoption. V3 remains a failed trial. A separate independent read-only reconciliation passed; it does not rewrite that outcome.

Scope

The bounded trial stages the integrated candidate worker with discovery ingestion off, waits for the previous instance to stop, and checks report publication from a new baseline. It then enables worker ingestion for two owned synthetic events. The intended checks cover separate scheduled capture passes, exact content and versions, idempotence, retention deletion, permanent erasure-marker propagation, and exclusion from the filtered view. The public application's Discover, measurement and personalization flags remain off.

Success requires source cleanup, ingestion disabled again, previous instances stopped, and all 21 report periods advancing from the final deployment's baseline. Report publication is checked in both ClickHouse and the actual Redis cache. Expiry is assessed against Redis server time and reported separately from publication. The initial read-only baseline had all 21 publications but 13 expired snapshots; that pre-existing condition is not evidence of a new regression.

Historical v1/v2 source was pinned to f35163950971baba92bc23416e84d213f0fed7bb. Its worker-only context has 1,170 files, 11,239,394 bytes and no credentials or testing preload. Manifest SHA-256: de71d40a392a95252643d15b731f74d3eb290185fda625bb8c1a270b968a1b1d.

V1 outcome and recovery

The candidate deployed, the old worker stopped, and new overview/detail reports reached both ClickHouse and Redis. Enabling discovery ingestion also deployed successfully. The canary then failed while connecting to PostgreSQL, before any fixture insert or ownership confirmation. Its event and operation lists are empty; no synthetic source event or erasure marker was written.

A bounded read-only comparison identified the cause: the database pool rejects the helper's startup options parameter with SQLSTATE 08P01. Connecting with the existing URL succeeds. This is a test-helper compatibility failure, not a measured failure of scheduled event capture.

The owning runner requested one native rollback. Railway restored the exact previous image but retained the newly introduced ANALYTICS_DISCOVERY_READY=false setting. The frozen runner had expected the original absence of that key, so it correctly left its own outcome failed and reconciliation incomplete. That assumption was not patched during execution.

A separate read-only reconciliation subsequently verified the exact baseline image and configuration, all other rendered and unrendered settings, stopped predecessor instances, unchanged application deployment, and fresh overview/detail publication after the restored worker's baseline. Discovery remains explicitly off. The original failed run and its journal remain intact.

• Failed run: 3a628bf819a9205e029ae78b426709d0.
• Frozen v1 plan SHA-256: 2cb60add2600039bcdb1be31df22fe637ea20d01b446edcf05d130543ec44e56.
• Restored deployment: a124991f-e1d3-42b7-9d3d-873d69e4b610.
• Restored image SHA-256: 5da872be6274d9ff6878da0ded419b30231da5f4d9a13052afef324968389577.
• Separate recovery receipt: worker-adoption-recovery-result-v1.json (pass).

V2 outcome and view defect

The new revision uses a normal pool connection and transaction-local query, lock, timezone and date-format settings. Every logical operation has one ten-second deadline including connection, setup and commit; acknowledgement is required before treating a write as successful. Uncertain commits retain owned, idempotent cleanup. A live read-only preflight exercised this protocol successfully with the configured limits and verified zero source events and one pre-existing marker.

Rollback verification accepts an absent or explicitly false discovery flag after checking the exact retained image and all other settings. It records the actual value. Acknowledged deployment operations whose metadata is not visible yet can be reconciled through bounded reads without repeating the mutation.

The PostgreSQL protocol succeeded in v2. Its first combined warehouse snapshot failed with ClickHouse code 47 before ownership confirmation or fixture writes. The owning runner restored the original image at deployment 7ebd83b7-a9f7-4cc8-b76d-fd115ac83276, confirmed ingestion off, stopped predecessors, and observed overview/detail publication in ClickHouse and Redis. The v2 run remains failed; its frozen helpers and results are unchanged.

Read-only replay isolated the failing snapshot branch to source_discovery_events. Live column metadata showed that SELECT e.* after the erasure joins exposed the actor column as e.actor_id. The exporter and verifier request actor_id. This is a consumer schema defect, not missing replication data. version is present and its exact checks must remain in place.

The correction explicitly aliases all 17 consumer columns. The table definitions, join predicates, retention window and erasure conditions are unchanged. Migration 008 is applied by an explicit repeatable CLI rather than an append-only migration ledger, so its canonical view definition is corrected in place. Deployment alone does not apply it to an existing warehouse.

A regression test now executes the full consumer projection, checks all column names and types, and repeats the read after migration reapplication. On real ClickHouse 26.2, the old definition fails with code 47 while seven other subtests pass; the owned test database is removed. Evidence: CI run 35541318268, warehouse job 106159401365. The unrelated remaining verify job was canceled after this expected failure was captured. Two attempted cloud test database creations were denied before ownership or writes; CI supplied the isolated database without changing production permissions.

Production subsequently advanced independently to release c3f0c312e8e689f9fe5fb5b694fe253ce493505f. The candidate includes that release. Any further worker trial must use a fresh artifact and rollback baseline, not overwrite the newer deployment with the earlier test baseline.

Verified view repair and v3 preparation

The corrected source passed all nine real ClickHouse regression checks in CI 35541591155 on commit 91e27378b3aeddc869ee49f2b8cd61a01fb33bc5. The complete verification job also passed. Local build/typecheck and 38 focused import, export, migration and lease tests passed.

The production replacement command returned code 497 under existing narrow view grants. Read-only reconciliation found that it had nevertheless applied the intended definition. A prepared maintenance procedure refused its stale precondition before issuing any DDL; no DROP/CREATE was performed. Both failed attempt receipts are preserved. The independent recovery receipt verifies:

• All 17 canonical columns and original types, including actor_id and version.
• An executable full consumer query, unchanged joins and erasure predicates.
• Unchanged archive table UUIDs and counts: one archived event, one marker, zero visible events.
• New view UUID 243da89e-cf5a-4f2f-8a06-3879f3b15fd3; definition SHA-256 81b45569195f6a532796f3f719764d2b9a6e7b01a4aaafd80a4c918609ee1b36.

V3 uses a new immutable worker context with 1,242 files and 11,887,932 bytes, manifest SHA-256 1a8bdd82d2f4c502e56ca7485f6077d9b4d11dd8217cf9c26ec2aecd7ffcd912. Its rollback baseline is production deployment 10a9d436-b9b6-4536-9fc8-7f255830667b, retaining the newer release. Before deployment, the complete warehouse query set and schema plus both PostgreSQL preflight reads passed against the live databases. The later source cleanup count remains part of the trial. No test events were written by preflight. The canary's SQL and validation logic are unchanged from v2; only its test identity changes. The 88 combined controller/observer/orchestration checks and 26 canary scenarios passed. Frozen plan SHA-256: 31c5a4fb0031e9474ab7189a71f5e3fa63e064da400ba852425991fcdb179f60.

The v3 canary passed in 273,965 ms. Two owned synthetic source events appeared through separate scheduled capture cycles with matching content and exact versions, one physical archive copy each. Source deletion retained the archived events; a permanent actor-erasure marker then excluded both from the consumer view. Cleanup verified zero owned source events and one permanent owned marker. No manual import or direct archive insert supplied the captured events.

The owning runner subsequently disabled ingestion, deployed 9bbb8b73-1f5d-4535-8dea-5a6ede2f0cd9, and verified that the prior instance had stopped. All 21 report periods then advanced in ClickHouse and Redis from that final deployment's baseline, over 1,266,700 ms. Twelve snapshots were expired at the final observation; successful publication does not establish freshness.

The final platform check failed with CONFIG_DRIFT. An independent application release, PR 190 at commit 04608342c95abcb35c4903467e36f1c9d30797d1, had replaced the public application and changed GAME_CONTROL_RELEASES. The guard refused rollback before creating any rollback operation or issuing a rollback mutation. The failed run receipt remains unchanged. The success-only audit was not run.

A separate read-only diagnostic confirmed the candidate worker still serves from the expected image, with one running instance, unchanged worker variables, and discovery ingestion false. All three predecessor worker deployments are removed. The newer application has two running instances, its discovery flags remain off, and dedicated Discover Redis remains unwired. The only application snapshot-variable change is GAME_CONTROL_RELEASES; no keys were added or removed.

The independent interruption audit passed at 2026-09-20T23:28:49.705Z in 10,445 ms, using 24 bounded platform reads, one read-only PostgreSQL transaction, one owned-fixture ClickHouse query and one report-store observation. It verified:

• The exact candidate worker image and configuration, ingestion off, and stopped predecessor instances, before and after the data checks.
• The exact newer application image and two running instances. Replacing only the current GAME_CONTROL_RELEASES value with its prior snapshot value reproduces the original rendered-variable hash; the current full hash stays unchanged across the audit. No other configuration drift is accepted.
• Zero owned source events and one permanent marker; two archived events with their original canonical content and exact versions, one physical copy each; two raw tombstones, propagated markers and zero visible owned events.
• All 21 report snapshots published in ClickHouse and Redis. Six were fresh at that later observation; the audit does not certify freshness or complete source coverage. Historical publication receipts do not identify their producing worker.

The audit explicitly records trialPassed: false and reconciliationPassed: true, with no failures or production writes. Its 28 mocked reconciliation scenarios, 12 PostgreSQL lifecycle checks and strict TypeScript check passed. The original failed trial, all frozen helpers, and the success-only audit remain unchanged. No new scheduled trial or rollback was needed to reconcile the known external application release.

• Run ID: e800d6f932b40e66756214683ebce99a.
• Candidate worker image: sha256:af133a8eecb6d0397be7cb68b19bfcd97d2fe76b1b8169fe7da8621e38e8fba8.
• Unmodified failed run SHA-256: c64f9ec6d683c6fb3e0d74738b47670811450174a53d9d36cc5166b121385c7c.
• Passed scheduled canary SHA-256: eda3231e52abc1542fb2eb020e198a6669fa79da2ed1be41834310e2e163b596.
• Read-only interruption diagnostic SHA-256: 883fd58ea39adb71e47368de5939b745fa0e0753dc3a5efaa61a1d52120cd17d.
• Independent interruption audit SHA-256: fedc9563a48a94b12cfaf79393eabbd3c0eaf351226889eaafa3138adff3d697.

Limits

Two synthetic events cannot establish sustained throughput, latency guarantees, source completeness, ranking improvement or user satisfaction. Discovery's current sixty-second import interval is a throttle in the same sequential worker as reports, so long report jobs can delay capture. The proposed native ClickHouse capture path removes that scheduling dependency; it still needs implementation and measured recovery/capacity evidence. It does not itself fix report freshness or remove shared database contention.