Ingestion unblocked end-to-end — CrateDB corpus filling — 2026-06-08

Autonomous /loop continuation. The CrateDB cutover had left the device→broker ad-write path completely non-functional (0 ads despite a configured, alive fleet). Root-caused and fixed the full chain; the corpus now fills live. All pushed to main.

Result

  • Corpus: 0 → 246 ads and climbing, 100% titled, source zentraljob-ch (a server-rendered JSON-LD board). The pipeline is self-driving again: orchestrator (alwaysdata) → planner (hourly) → server devices lease/scrape → broker records → CrateDB.
  • Cockpit observability verified live and accurate: /corpus (growing), /freshness (246 postings, 0 stale), /ingestion-health (readiness: active), /source-matrix (per-source health — correctly flags jobup-ch RED).
  • Product API /api/stats reflects the corpus (total, bySource, topCompanies).

The three root causes (all fixed)

The stall was three bugs stacked behind one symptom (0 ads):

  1. schema-invalid crash — device, 1ba40c86 (crates/jobcache/src/schema_org.rs). parse_schema_org_jobposting's no-JSON-LD branch emitted status: opts.status.unwrap_or(Failed), but ~89 adapters pre-set Some(Success) before the parser runs, so an SPA page with no server-rendered JSON-LD produced a success observation with 0 fields → the broker's .strict() ObservationSchema superRefine rejected it → task burned 3 lease attempts → failed. Forced Failed on that branch (matches the function's own doc contract) + regression test.

  2. Broker ad-write 500 — d247f59a (jobcache/shared/src/fact-tree.ts). Once #1 unblocked real observations, they reached upsertObservationInStore — the ad-write path that had never executed against CrateDB — and threw Column reference "ads.source_ref" has too many parts (the #80/#81 pattern: CrateDB forbids table-qualified columns in ON CONFLICT DO UPDATE SET; the existing-row value must be bare, excluded.col is the proposed value). Ported every ON CONFLICT SET in the observation-write path to bare existing-row refs (ads 24 cols, object_refs ×2, text_chunks, ad_annotations, ad_role_links, ad_observation_state increment). Single-table UPDATE…WHERE ads.col IS DISTINCT FROM is a different context CrateDB accepts — verified live (fixture observation upserts clean).

  3. Observability gap — 35fb25c1 (jobcache/interface/src/api/device-control.ts). The broker swallowed the exact Zod issue on schema-invalid. Now it surfaces a bounded reason_detail in both the server log and the rejection response (the device logs the response, so it's readable via kubectl logs).

Key insight: the broker fix (#2) alone makes the EXISTING old server devices record ads for any JSON-LD page — their crash was the broker's 500, not the device. Validated/seeded by running the fixed Rust device locally against the prod broker (it's a portable HTTP client — no server k3s changes needed).

Verification method

  • upsertObservationInStore reproduced directly against live CrateDB with the rich fixture observation → inserted:true, ad + title fact written, 0 errors.
  • Local fixed device (40 cycles) vs prod broker → 84 observations all status: recorded; corpus then kept growing from the server devices too.
  • Render build flake (EEXIST … sporewright submodule symlink in the bun cache) cleared with a clearCache: clear redeploy — not a code issue.

Follow-ups (none blocking; corpus fills without them)

  1. Device image redeploy (server k3s/docker). The server jobcache-device pods run the OLD image, so schema_org fix #1 isn't on them — SPA pages (jobup-ch, jobs.ch) still schema-invalid there instead of failing honestly. This is a server container op (NO-DOCKER hard rule), so left for Julian: docker build -f crates/jobcache-device/Dockerfile -t jobcache-device:latest . → load onto k3s → kubectl -n jobcache rollout restart deployment/jobcache-device. Low value: SPA boards produce no Tier-1 ads regardless (they need the Tier-2 render device, #68); the corpus fills from JSON-LD boards meanwhile.
  2. Cockpit live tape — FIXED (f88f0bdd, deployed). /changes/stream was erroring Unknown function: to_timestamp — its 4 cursor comparisons used the Postgres to_timestamp(cursor/1000.0), which CrateDB lacks. Replaced with EXTRACT(EPOCH FROM col) * 1000 > cursor (the form the same queries' SELECTs already used). The tape derives from live leases/observations/task-failures (NOT the legacy scrape_events log, which is dead — only the old bun ingest.ts ever wrote it). Now streams scrape-run-started/completed/failed frames. Deployed via wrangler deploy --env production with the global API key (the scoped-token secret field is malformed — token mashed with its ✓-permission notes; see follow-up 5).
  3. Secret hygiene: secrets/cloudflare.yml:jobcache_scoped_token is 774 chars — the token concatenated with its permission list (contains a ✓), so it can't be used as CLOUDFLARE_API_TOKEN (ByteString error). Re-store just the bare 40-char token value. Worked around with the (sanctioned) global API key for now.
  4. SPA boards need Tier-2 render (jobup-ch / jobs.ch) — separate feature (#68 render device exists; not yet driving these).
  5. The 32 zentraljob-ch failed tasks are non-detail/listing URLs (no JSON-LD) — now an honest Failed; could be filtered at discovery to cut noise.

Follow-up session — open-TODO review + queue fixes (2026-06-08, later)

A 6-agent review workflow re-graded every open TODO against the live system and swept for more latent CrateDB bugs. Outcome + three more P1 backend fixes shipped:

  • #82 b0b0cc63 — resolveFactChains CTE was named input (a CrateDB reserved word) → unconditional 500 on POST /api/ads/resolve. Renamed → link_inputs; verified live the query returns correct ad+role data. (Cold path — CV fact resolution — so it had never fired.)
  • #83 85c84849 — lease FAIRNESS: selectReadyTasks scanned 50 pending tasks ORDER BY priority DESC, created_at ASC and every priority is 0, so pure created_at FIFO let one board fill all 50 slots. Live: 1360 nzz-jobs tasks stuck at attempts=0. Fix: per-source ROW_NUMBER() OVER (PARTITION BY source_id) cap + random pick among tied top-score candidates in leaseTask. Result: all 4 sources now GREEN (zentraljob/ostjob/nzz/jobup) — the corpus went single-source → genuine multi-board. This was the highest-impact fix.
  • #84 85c84849 — lease REAPER only reaped active leases; a submit that flips the lease active→submitted then throws before the task flips strands the task leased forever. Added a second reaper pass (anti-join for leased tasks lacking any active+unexpired lease, with a grace window). Verified: the 4 live stranded tasks self-healed to done; still-stranded count = 0.

#70 closed as MOOT (no verify task class exists post-wipe).

Remaining backend roadmap (tasks #85, #86 — none corpus-breaking):

  • #85 maintenance pass: evictRawObjects (raw GC / 9 GB B2 cap) has zero call sites; non-atomic ad-write leaks ~0.2% orphan ads (ad+facts+field_state written, ad_observation_state missing — CrateDB has no rollback). Wire a control-gated POST /api/maintenance/run called by the orchestrator tick. Low urgency.
  • #86 close the #66 loop: orchestrator never budgets freshness/reparse (0 such tasks ever fire) AND the live TS device can't execute verify/reparse. Needs an alwaysdata tick change + a verify/reparse-capable fleet (partly server-docker).
  • #66 status: trust (live, 3 devices off the 0.5 default) + re-investigation engine are BUILT and wired; the loop just isn't CLOSED end-to-end yet (≈55%).

Follow-up session 2 — orphan ads + #66 loop closure (2026-06-08, later)

  • Orphan ads eliminated (d0916978, deployed). Ads with ad+facts+field_state but no ad_observation_state row (the final write fails, no CrateDB rollback) were slowly accumulating. Healed all existing to 0, then two prevention layers: (1) fact-tree self-heals at source — on an obs-state write failure it logs the real CrateDB error (root-cause capture) and retries with empty change arrays; (2) device-control expireOverdueLeases runs a throttled (≤1/60s) sweep that re-derives the row from ad_field_state for any ad orphaned past 120s. Verified live: persistent orphans hold at 0.
  • #66 freshness loop CLOSED (42364392, deployed). Root cause: freshness re-fetch took only the discover LEFTOVER budget, and discover never exhausts, so it produced 0 re-investigation tasks ever. Decoupled it to an independent per-tick budget (planner-only, no alwaysdata/device change). Proven live: a 1h-target run yields 25 freshness re-fetch tasks of real ads (vs 0 at the 3h default). Re-fetch is scrape-class → the existing fleet executes it → the re-investigate → fresh observation → agreement → trust loop now runs. DEFAULT_FRESHNESS_TARGET_MS 24h→3h (targets aged-out ads; re-discovery covers active ones). Reparse default-OFF (TS fleet can't execute reparse-class). Remaining #86: reparse/verify execution (needs a server-docker fleet) + M2 orchestrator budget — see task #86.
  • The four queue/integrity fixes plus the freshness decouple all landed via Render deploys with no orchestrator (alwaysdata) or device-image (k3s) change required.

Follow-up session 3 — reparse arm deep-dive (2026-06-08, later)

Drove the #66/#87 self-upgrading loop's REPARSE arm toward 100%. De-risking the never-run path (local HEAD device, fe1449a4 collector, against the live prod broker, with a new target_collector_version planner override) surfaced a CASCADE of latent bugs — same pattern as the original 0-ads stall. Six found:

  1. Infinite-loop target (65c73b87, deployed). loadVersionUpgradeTarget used max(last_collector_version). Git short-shas are NOT chronologically ordered, and a re-deriving device stamps its OWN version, so a target that never equals what live devices produce makes staleness unresolvable → the ad re-parses forever. Fixed → RECENCY (latest real-runtime observation).
  2. Heal-sentinel poison (same commit). The orphan-heal sweep stamps last_collector_version='orphan-heal' (runtime cv-internal), which sorts above any sha and became the max. Fixed by the recency query's last_collector_runtime IS DISTINCT FROM 'cv-internal' filter.
  3. Loader joined the wrong key (df7637be, deployed). The version-stale loader joined object_refs ON r.content_hash = o.last_content_hash, but last_content_hash is the OBSERVATION id (a nanoid), not the raw sha256 — it matched 0 of 4932 eligible ads, so reparse silently minted nothing. Fixed → evidence chain (evidence[subject_kind=ad] → evidence_objects[role=raw] → object_refs) + inner row_number() PARTITION BY ad_id ORDER BY observed_at DESC to dedup to the freshest raw per ad. Verified live: 0 → expected per-source-capped rows with correct B2 locators.
  4. Re-parse fed bare JSON-LD to an HTML extractor (0b3b723e, committed; device not yet redeployed). schema_org stores raw: Some(jp) — the BARE extracted JobPosting object (kind='json'), but run_reparse_grant fed it to a parser that only lifts JSON-LD from an HTML <script> block. Fixed → canned_body_for_kind re-wraps json-kind raws. (The old reparse test fed HTML, so it stayed green while every live json raw failed.)
  5. Grant-delivery red herring + observability fix (4b30962c). First read as "grants never reach the device", but instrumentation proved raw_grants=1 arrives fine — the broker mints + delivers correctly. (Kept the win: every empty-grant path in mintRawGrants now logs a [reparse-grant] reason instead of returning [] silently.)
  6. Identity-encoding raw fetch — THE blocker, FIXED (6ceff20b). The device DID route to handle_reparse, but the fetch of the presigned B2 object failed: curl (61) bad Content/Transfer-Encoding. CurlEnv always passed --compressed, so curl tried to transparently decode the object's stored Content-Encoding — but the body is the RS02-encrypted .zst.enc blob the device decrypts + zstd-decompresses ITSELF. Added Request::header(); the reparse fetch sets Accept-Encoding: identity and CurlEnv skips --compressed on any explicit Accept-Encoding, returning verbatim bytes.
  7. failed_observation wrong schema_id — OPEN follow-up. The device's adapter.rs imports SCHEMA_ID from broker.rs ("jobcache.device-contract") but the OBSERVATION schema requires "cv.jobcache" — so a genuine adapter Err/None failure (incl. a reparse that legitimately can't re-derive) is rejected schema-invalid, not recorded. Not blocking (the happy path now emits valid SUCCESS observations via parsed_ad); fix is device-side, deploy with the fleet.

REPARSE ARM CLOSED — proven END-TO-END live: planner mint → broker grant → identity fetch → RS02 decrypt → re-wrap + re-parse → recorded. Clean run: 3/3 reparse tasks status:recorded, faithful re-derivation (title/org/description unchanged_fields). 138 tests pass (27 device + 111 shared + 122 interface). NOTE: reparse re-derives IDENTICAL facts today (no extraction gap ec08c496→HEAD), so the data value is ~0 — the value was DE-RISKING the never-run path (7 bugs!). The target_collector_version override on POST /api/planner/run is the deterministic forced-re-derivation handle. FLEET STILL ON ec08c496 (no handle_reparse) — ongoing fleet reparse needs the HEAD device-image ctr-import, DEFERRED until a real adapter upgrade makes re-derivation worthwhile.

kind: journal nodes:

  • jobcache-ingestion-unblocked-2026-06-08 summary: "Working record of the JobCache ingestion recovery and the evidence that unblocked it on 2026-06-08." links: event:
    • jobcache-ingestion-recovery-2026-06-08 implementation:
    • sporewright-server
Source: wiki/content/working-memory/ingestion-unblocked-2026-06-08.md