CockroachDB — Hot Store + CF Worker Access

SUPERSEDED (2026-06-08). The canonical hot store is now CrateDB Cloud CRFREE (West Europe, Azure; pg-wire only — no JSONB/txns/FK; dialect contract in jobcache/shared/src/pg-options.ts). Cockroach disabled the free RU tier; the cluster described below is dormant and kept for rollback only. Cutover runbook: jobcache/migrations/cratedb/CRATEDB-CUTOVER.md. This page is retained as the historical record of the Cockroach-era design.

JobCache's canonical hot store is CockroachDB Serverless on AWS Frankfurt. Shared ad/role data, scrape events, submitted Observation indexes, and the operator-actions audit log all live there. The hosted interface/control plane and the ops API both read and write through the same SQL schema. Devices do not receive database credentials; they submit Observations through submitObservation.

Why CockroachDB

Free tier covers the shared ad set today. ≈6 GB rent-free is enough for the current postings + role tables with room for years of growth.
Horizontal scaling is provisioned. When the shared ad set grows past the free tier we move to a paid plan without re-architecting.
Postgres-compatible. postgres.js (postgres@^3.4.9) works directly; no proprietary driver, no vendor lock-in beyond the connection string.
The hosted interface already uses it. Re-using the same database from the Cloudflare Worker ops API avoids a second store and a sync seam.

SQLite was rejected because Render containers do not have a persistent disk on Hobby plans. A self-hosted Postgres would require operating a server explicitly, and the shared ad set doesn't justify that yet.

Observation Hot Indexes

submitObservation writes the ad payload cells and the hot indexes for records already emitted inside the Observation envelope:

object_refs for retained cold evidence objects
evidence_objects for evidence-to-object joins
text_chunks for chunk_refs and search_chunks
ad_annotations for emitted annotations
embeddings for emitted vectors attached to chunks

These rows are indexes over submitted device/import output. Cockroach-side code validates ids, hashes, schema, lease or command provenance, and policy before writing them. It does not fetch source URLs, read object-store blobs, split text into chunks, enrich fields, create annotations, produce embeddings, or call LLMs.

RU Pattern

Hot Cockroach writes should be idempotent and boring:

Duplicate task enqueue uses insert-or-readback. If the task already exists and mutable fields did not change, return task_id, input_hash, and state from the existing row without rewriting it.
Identity and hot-index upserts use WHERE ... IS DISTINCT FROM ... guards on conflict updates. Replaying the same role, ad, object ref, chunk, annotation, or embedding should not consume write RUs beyond the required read/compare.
Role membership is URL-family identity from roleIdFromUrl(url). The hot Observation path records the ad and role link and performs only cheap ad_propagation for safe changed fields. Cross-URL clustering, cross-language aggregation, and semantic role reduction are deferred async DB work. Direct or adapter-written role rows are ignored by read paths.
Lease expiry cleanup is bounded. Before leasing, the broker expires only the oldest overdue active leases in a small batch, updates their tasks, then selects retryable work.

Reaching CockroachDB from a Cloudflare Worker

jobcache-ops-api is on Cloudflare. CockroachDB is on AWS Frankfurt. Three options were evaluated; one was kept:

Option	Verdict	Why
Per-request `postgres.js` over `nodejs_compat`	Kept	Short-lived TCP per request. Predictable cold-start cost. No extra service to operate. Compatible with the worker's request-lifetime model.
Hyperdrive	Rejected	Hyperdrive does not target external (non-CF) Postgres endpoints in the configuration we'd need, and the region mismatch with CRDB-Frankfurt removes the pooling benefit.
Interface proxy hop	Rejected	Adds a hop, doubles the failure surface, and re-introduces the interface service into the read path that the ops API was meant to bypass.

The connection is built per request from JOBCACHE_DATABASE_URL (a wrangler secret). The connection is closed when the request ends — no long-lived pools, no idle TCP. The full rationale is mirrored as a top-of-file comment in jobcache/ops/api/src/lib/crdb.ts.

Tradeoffs