CrateDB Migration (jobcache)

Status as of 2026-06-07. jobcache is moving its hot DB off CockroachDB Cloud (RU-disabled) onto CrateDB Cloud (CRFREE). CareerVector is unaffected — it runs on Cloudflare D1; only jobcache used Cockroach.

Cutover landed (2026-06-07): pulumi up --target jobcache-interface flipped DATABASE_URL to the CrateDB DSN (Pulumi secret jobcache:cratedbDatabaseUrl); a Render redeploy from main shipped the ported code + DSN together. After fixing a build-time crash (the config.ts DATABASE_URL fail-fast was firing during SvelteKit's import-time analyse pass — moved it to server boot + the connection layer), the service is live: /healthz, /api/stats, /api/adapters, /metrics, and jobcache.corbet.ch all return 200 (were 500 on the dead Cockroach). Corpus is greenfield (total:0) — the fleet backfills it by scraping on the orchestrator's schedule. Cockroach cluster stays dormant/protect:true for the optional historical backfill after its RU window resets (~July 1).

Targeted cutover note: the full-stack pulumi up showed pre-existing drift (3 Cloudflare DNS updates + 3 Render custom-domain replacements) unrelated to the DB. The cutover was scoped with --target to ONLY jobcache-interface to avoid disrupting live custom domains. That drift is still pending — resolve separately (decision: leave vs. reconcile).

Cluster: jobcache.aks1.westeurope.azure.cratedb.net — pg-wire 5432, HTTP/Admin-UI 4200. CrateDB 6.2.7, West Europe (Azure), CRFREE (2 vCPU / 2 GiB / 8 GiB, single node, no request meter — structurally cannot repeat Cockroach's RU hard-disable). Credentials: ~/.agent/secrets/cratedb.yml.

Gate-Zero applied (2026-06-07)

The live schema is the Gate-Zero redesign (jobcache/migrations/cratedb/schema.sql, apply via apply.ts), in the canonical doc schema. ads is now 42 columns: searchable facts (title/organization/location/description/employment_type/industry/salary_min/salary_max/remote…) promoted to typed columns alongside the residual ad_facts EAV (which stays the source of truth for value_hash/churn/pair-trust); multilingual jobcache_multilang FULLTEXT analyzer; GEO_POINT + has_geo; job-level FLOAT_VECTOR(1024) (job_vec) for person↔jobs KNN; person_vectors table; scrape_events monthly-partitioned. MATCH(...), knn_match(job_vec, …), and geo all verified live.

Trust/learning preserved (not duplicated). The live trust system is jobcache's DB-side pairwise increment model in fact-tree.ts (updatePairTrust/updateDeviceTrust, keyed off ad_field_state.last_observer_device_id), not sporewright (whose Beta-reputation trust is designed-only, uncoded). Method learning runs in-memory on the real sporewright Tensor (reduceMedian). The redesign preserves every dependent column (devices.trust_score/trust_state/agreement counts; ad_facts/role_facts writer/value_hash/reducer/basis_hash/observed_at; ad_field_state churn + observer). The increment trust model is idempotent-safe (fires only on cross-device value_hash transitions); the port must not double-fire a transition on retry.

CrateDB gotchas learned the hard way (baked into apply.ts): the pg-wire database param sets the schema — connect to doc, not crate; information_schema.{tables,columns} is unreliable for the doc schema (returns 0/partial) — use SHOW TABLES and live SELECTs to verify; CrateDB forbids DEFAULT {}/DEFAULT [] on OBJECT/ARRAY and standalone CREATE INDEX; OBJECT columns can't hold scalars/arrays (fact value → JSON TEXT); input and the *_stem filter names are reserved.

Why we moved

CockroachDB Cloud BASIC pinned its spend limit exactly at the 50M-RU/month free ceiling, so it hard-disabled when the cap was hit (code 53300, connections forced to 0) — the corpus went offline mid-month with storage far under cap. CrateDB CRFREE's only binding limit is disk (8 GiB), and disk-full degrades gracefully (reads stay live, writes go read-only, auto-recovers) rather than killing the cluster.

The deeper rationale: jobcache is a search/analytics corpus (world-scale postings + embeddings + full-text + freshness), which is exactly CrateDB's shape — and CrateDB unlocks native vector KNN, the one capability that turns the corpus into a person↔jobs matching engine (see Improvements). The price is that CrateDB only speaks the Postgres wire protocol; it is not Postgres-semantics-compatible.

CrateDB 6.2 compatibility — empirical findings

Verified directly against the live cluster (not docs):

Keepalive — CRFREE survival (do not lose the DB)

CRFREE suspends after 4 days idle and deletes after ~14. Two independent keepalives run a real SELECT 1 (genuine activity); both verified end-to-end:

• Primary — Cloudflare Worker cron 0 */6 * * * (ops/keepalive/), POSTs to the /_sql endpoint. Confirmed against CrateDB's own sys.jobs_log.
• Secondary — GitHub Actions 23 */12 * * * (.github/workflows/cratedb-keepalive.yml), independent platform.

To lose the cluster, both Cloudflare and GitHub must stop firing for two weeks. (Phase-4 follow-up: add a native B2 snapshot repository as a true restore tier — see Improvements/Wave-1.)

DDL translation ruleset

The rules applied to produce jobcache/migrations/cratedb/schema.sql:

1. STRING → keep. UUID → STRING. type[] → ARRAY(type).
2. JSONB → OBJECT(DYNAMIC) (or OBJECT(IGNORED) for opaque blobs).
3. FOREIGN KEY/REFERENCES/ON DELETE … → drop the clause, keep the column.
4. standalone CREATE INDEX → delete; fulltext → table-level INDEX … USING FULLTEXT.
5. secondary UNIQUE → drop; promote to PRIMARY KEY if it's the natural key.
6. no DEFAULT {} / DEFAULT []; no CLUSTERED/number_of_replicas (single-node defaults); drop COMMENT ON.
7. gen_random_uuid() → gen_random_text_uuid(); quote reserved-word columns.

The schema applies greenfield with jobcache/migrations/cratedb/apply.ts (CRATE_PW=… bun apply.ts schema.sql --reset — statement-by-statement, reports each, --reset drops first).

Schema foundation — 28 Core tables

jobcache/migrations/cratedb/schema.sql creates the 28 Core tables cleanly on the live cluster: orgs, roles, ads, ad_role_links, ad_facts, role_facts, evidence, object_refs, evidence_objects, text_chunks, ad_annotations, embeddings, graph_edges, work_cache, applied_inputs, ad_observation_state, ad_field_state, families, sources, adapters, bindings, devices, device_sessions, tasks, leases, runs, scrape_events, operator_actions. The obsolete Crate-backed jobcache_sessions App table and runtime adapter were retired when the Cloudflare App D1 became authoritative; an old live table may remain as empty historical residue until an explicit database cleanup.

App-enforce punch list (the dropped DB guarantees — Phase-3 spec)

CrateDB no longer enforces these; the app must. The single most important ones are the partial-unique invariants the freshness loop depends on:

• leases(task_id) WHERE state='active' — at-most-one active lease per task (lease exclusivity). The broker MUST guarantee this via conditional/optimistic writes; the DB no longer rejects concurrent grants.
• ad_role_links(ad_id) WHERE active — one active link per ad (resolveCurrentRole).
• device_sessions(device_id) WHERE is_leader AND ended_at IS NULL — one leader per device.
• FK cascades (app must cascade-delete): ad → {ad_facts, ad_role_links, ad_observation_state, ad_field_state, text_chunks, ad_annotations}; role → role_facts; evidence → evidence_objects; chunk → embeddings; source → bindings; device → device_sessions; task → leases. object_refs must keep being tombstoned (missing_at), never hard-deleted while referenced.
• Surrogate-key upserts (CrateDB ON CONFLICT only targets the PK): evidence (6-tuple), embeddings (5-tuple), object_refs (provider,bucket,key), orgs (normalized_name), graph_edges — rework to deterministic PK upsert or SELECT-then-write. ads identity is preserved (deterministic ad_id from normalized_url×language).
• runs.id BIGSERIAL → CrateDB has no sequences; startRun() must supply a client-generated monotonic/snowflake BIGINT (or verify RETURNING).
• CHECK value-domains dropped where translation removed them (operator_actions) → writer is sole guard.

Query-layer port (Phase 3)

Driver stays postgres.js. The work:

• JSONB→OBJECT at every site: ::JSONB casts, ->/->> → subscript, jsonb_typeof, and especially the config = config || $N::JSONB merge (no merge operator → read-modify-write the object, or upsert the whole value). Includes session-store.ts update().
• Transactions → idempotent: fact-tree.ts transaction() (client.begin) and callers → ON CONFLICT upserts keyed on deterministic ids, safe under partial failure (no rollback).
• REFRESH: insert REFRESH TABLE where an operation writes then immediately reads (session create→load; lease grant→read; compute-lock double-check).
• Connection/cutover: config.ts/db.ts DSN (prepare:false, sslmode=require, connection budget ≈ shared cluster limit); Pulumi infra/jobcache repoint DATABASE_URL to a CrateDB DSN secret (Cockroach cluster kept dormant for later backfill); fleet/orchestrator connection; Render env; run jobcache tests.

Greenfield: old corpus data is unreadable (Cockroach disabled) → fresh schema, devices repopulate, backfill later if the Cockroach RU window reopens.

Improvements unlocked by CrateDB

CrateDB is not just a like-for-like replacement; treat the migration as an upgrade.

⭐ Top prize — native vector KNN → person↔jobs matching

FLOAT_VECTOR(n) + knn_match (HNSW) over job-posting embeddings is the one capability categorically impossible on Cockroach (no vector type; pgvector unavailable on Serverless; brute-force cosine over JSONB is O(corpus)/query — fatal at "all jobs in the world"). The entire embeddings/text_chunks/embed-task substructure was built for this and has never been usable — dead infrastructure today.

Because knn_match is an ordinary WHERE predicate, "semantically near this CV AND remote AND posted this week AND title MATCHes ML" is one ranked query mixing vector + structured + full-text in a single store. Embedding a CareerVector workspace's CV-quarry summary and knn_match-ing it against the corpus is the concrete mechanism that auto-populates CV dashboard rows via RADAR-style discovery — turning two products into one funnel (the Product North Star). Suggested model: multilingual (corpus is DE/EN/FR/IT) — bge-m3 (1024d) or multilingual-e5-base (768d), emitted on-device. Bonus: dedup / similar-jobs / clustering for free off the same index.

Wave 1 quick wins (after the port)

• B2 snapshot repository (CREATE REPOSITORY TYPE s3 against the existing jobcache-snapshots bucket + daily CREATE SNAPSHOT) — do first; it's the real restore tier behind CRFREE's auto-delete.
• Full-text search — MATCH + _score ranking with per-language analyzers (de/en/fr stemming + accent folding), replacing value::STRING ILIKE '%q%' newest-first search and the brittle 4-pattern "remote" string-sniff.
• Partition-drop retention — PARTITIONED BY (month) + DROP PARTITION on high-churn tables (scrape_events, expired tasks/leases), replacing hand-rolled eviction.

Big bets

Person→jobs matching (above) · near-duplicate/repost clustering (feeds graph_edges, makes #66 dedup-aware) · time-series market analytics (postings/day, salary percentiles, demand-by-location — turns jobcache from a job index into market intelligence, powering the #72 dashboard and feeding CV's salary estimator) · GEO (GEO_POINT + distance/within for radius/map search and a free commute pre-filter before CV's paid Maps calls).

Open strategic decisions — "Gate Zero" (decide in #75, not after)

The schema foundation above is a faithful 1:1 EAV port — it proves the model applies, but it does not yet let CrateDB shine. Three schema decisions belong in this migration because bolting them on later is impossible or wastes the query-port:

• (a) Denormalize searchable facts (title/org/location/description) onto stored columns on ads — FULLTEXT and GEO indexes cannot sit on EAV ad_facts.value OBJECT rows.
• (b) Widen salary/industry/location/lat/lng out of ad_facts into typed + GENERATED columns — otherwise the columnar GROUP-BY advantage (the analytics big-bet) is largely lost.
• (c) Partition + shard high-churn tables (PARTITIONED BY (month), CLUSTERED INTO 2–3 SHARDS).

This collides with our "no-deferred-schema / no-MVP" principle: if we want the CrateDB capabilities (which are why we chose it over staying on Postgres), the denormalized schema should land in #75, before the query-layer port is written against it.

Separately — the OLTP-vs-OLAP fork. CrateDB is eventually-consistent and cannot enforce the partial-unique invariants the freshness loop (#66) leans on (single active lease/link/leader). Decide: one cluster for both the hot task-queue and the corpus (accept app-enforced exclusivity + REFRESH), or split — task-queue stays on a small always-on Postgres (e.g. Aiven, free) while only the corpus + analytics move to CrateDB. This is the highest-order decision; it shapes everything downstream.

Post-cutover: operator cockpit ported (2026-06-08)

The ops cockpit (jobcache/ops/, api.ops.jobcache.corbet.ch + ops.jobcache.corbet.ch) has its own direct-to-DB layer, separate from the main service's jobcache/shared. It was NOT covered by the Wave-1 query port and stayed Cockroach-shaped, so every direct-DB panel read "down" after cutover (the worker's JOBCACHE_DATABASE_URL secret — a manual wrangler secret put, NOT Pulumi-managed — still pointed at the RU-disabled Cockroach). Ported in commits c9f8d3c4 + 38cb09f4:

• withCrdb → fetch_types:false + sslmode-aware ssl (mirrors pg-options.ts); overview tables read information_schema … WHERE table_schema='doc' (info_schema does work on CrateDB — the earlier "unreliable" finding was the crate-vs-doc namespace bug, now resolved).
• PERCENTILE_CONT … WITHIN GROUP → percentile(x, q); DISTINCT ON → ROW_NUMBER() OVER; jsonb_typeof/jsonb_array_length → array_length(col,1); current_schema() catalog filter → 'doc'; audit result_details OBJECT(DYNAMIC) bound raw (not sql.json).
• The Cockroach RU panel (/cockroach, monthlyRequestUnitLimit) was meaningless on CrateDB → repurposed to /cratedb showing CrateDB's real ceiling: disk (sys.nodes['fs']['total'], CRFREE ≈ 8.35 GB node), data size + shards (sys.shards WHERE primary), cluster health (worst-of sys.health). /cockroach kept as a deprecated alias. Each sys.* read is best-effort → null on failure (panel shows absence, never a fabricated number).
• Secret swapped to the CrateDB pg-wire DSN; both jobcache-ops-api + jobcache-ops-ui redeployed. Live: cockpit operational — 12/12 tables, health GREEN, 176 ms.

New CrateDB gotcha — user is a reserved word. current_user::TEXT AS user parses on Cockroach but throws no viable alternative at input on CrateDB; the alias must be quoted AS "user" (same class as the reserved input). Unit tests with mocked sql will NOT catch this — only a live query does. Lesson: probe the verbatim query string against the cluster, not a paraphrase.

✅ Follow-up done (#77, commit e88b8e90): the shared cockpit-snapshot schema was lifted cockroach → cratedb (disk/health shape), /cockpit/snapshot now carries cratedb as a first-class panel, and /cockroach remains a deprecated alias. Verified live.

Post-cutover: source catalog re-synced (2026-06-08)

The cutover to a fresh CrateDB was a DB wipe — the control-plane catalog (families/adapters/sources/bindings) came up empty, so the planner had no active bindings and the corpus could never fill. syncSourceCatalog (jobcache/interface/src/api/catalog/sync.ts, exposed at POST /api/catalog/sync, control-token gated) exists for exactly this — an idempotent, additive UPSERT of the declarative SOURCE_CATALOG. Re-ran it post-cutover:

POST https://api.jobcache.corbet.ch/api/catalog/sync   (Bearer JOBCACHE_CONTROL_TOKEN)
→ {"ok":true,"upserted":{"families":3,"adapters":5,"sources":5,"bindings":6}}

Ran clean against CrateDB (ON CONFLICT-on-PK + raw OBJECT binds all fine). State after: 5 sources (jobup.ch, ostjob.ch, zentraljob.ch, jobs.nzz.ch, jobs.ch — Swiss boards), 5 adapters, 6 active bindings, and the fleet is alive — 3 devices, all with heartbeats. The control-plane is configured + ready.

The corpus is still empty (0 ads, 0 tasks) by design: nothing enqueues scrape tasks until the planner runs (POST /api/planner/run), which is orchestrator-driven and not auto-triggered (the CI planner lane was removed in f750f8fa). Triggering it starts live scraping of the Swiss commercial boards — a prod operation left to the operator/orchestrator (still dev, not prod).

Operational learning: a future CrateDB wipe (or CRFREE auto-delete + restore) requires re-running the catalog sync. It is NOT auto-run on deploy/boot (deliberately — the sync never prunes; an operator removes stale rows by hand). Worth an explicit post-restore runbook step.

Cockpit observability gap (found here): /source-matrix keys off scrape activity (DISTINCT source FROM ads UNION adapter_id FROM tasks), so the 5 configured sources stay invisible in the cockpit until they've scraped at least once — an operator sees "0 sources" despite a populated catalog. A "configured vs active" view (or a dormancy callout: "N bindings active, planner last run: never") would make the dormant state diagnosable.

See also: ops/keepalive/ · .github/workflows/cratedb-keepalive.yml · jobcache/migrations/cratedb/schema.sql · jobcache/ops/api/src/lib/crdb.ts · jobcache/interface/src/api/catalog/sync.ts · ~/.agent/secrets/cratedb.yml · task #75.