D1 / Cockroach Work Todo

Updated: 2026-05-12 Status: implementation work plan.

Decided Boundary

D1/Y.Doc     private workspace rung
Cockroach    shared ad/role knowledge database
R2/B2        large immutable blobs

D1/Y.Doc owns live CVL, CL, workspace jobs, workspace facts, evaluations, custom columns, local summaries, settings, notes, and collaboration.

Cockroach owns roles, ads, jobcache, shared work cache, embeddings, matching, and shared evidence.

Private workspace data does not leave D1 by default.

Privacy is mostly solved by this physical boundary. Do not spend another broad design pass debating it. The current work is the split itself: field ownership, storage placement, read resolution, outbox synchronization, jobcache integration, and migration cleanup.

Parallel Work Lanes

Current Hot Spots

These are the known implementation pressure points in the current checkout:

  • lib/mutations/src/opsCatalog.ts, workspace-client, web routes, MCP, and dashboard code must agree on job.* operations and the jobs workspace sub-doc.
  • lib/domain/src/read-chain.ts and lib/domain/src/jobcache.ts already point toward a split reader/writer model, but there is no real Cockroach adapter yet.
  • migrations/schema.sql and the new jobcache migration still model shared jobcache tables in D1. Those shared tables belong in Cockroach.
  • ui/src/lib/server/workspace-subdoc.ts is the main boundary risk: workspace commits currently combine D1 sub-doc writes and D1 jobcache writes in one D1 batch. That must become D1 workspace commit plus outbox plus idempotent Cockroach application.
  • ui/src/lib/pipeline.svelte.ts and the process-request fallback route need routing rules: public scrape/extract/enrich facts go to Cockroach; workspace status, evaluations, tailoring, and overrides stay in D1.
  • ui/src/lib/server/job-records.ts, workspace job routes, and MCP read tools need to return resolved workspace projections, not direct job/ad nodes.

Lane A: Field Registry And Data Ownership

Write scope:

  • lib/domain/src/
  • lib/domain/tests/
  • docs generated from the registry, if any

Tasks:

  • Create a field definition registry.
  • Give each field an owner:
    • d1_workspace;
    • cockroach_ad;
    • cockroach_role;
    • blob_ref.
  • Give each field a boundary class:
    • D1-private;
    • Cockroach-shared;
    • operational metadata.
  • Declare read behavior for every field:
    • workspace overrides inherited value;
    • ad overrides role;
    • role base value;
    • no inheritance.
  • Declare write behavior for every field:
    • D1-only;
    • Cockroach-only;
    • D1 with outbox;
  • Mark fields outside the D1/Cockroach split as out of scope instead of designing them now.
  • Add tests proving unknown/originless workspace fields default to D1.

Dependency: should start before schema and migration lanes finalize.

Lane B: Cockroach Schema

Write scope:

  • jobcache/
  • migrations/ only if shared app migrations are required
  • lib/domain/src/jobcache*
  • tests under relevant packages

Tasks:

  • Choose migration tool for Cockroach.
  • Add Cockroach connection module with retry handling for transaction restarts.
  • Create roles table.
  • Create ads table with stable ad_id, normalized URL, role_id, source, scrape status, timestamps, and sparse facts.
  • Create orgs table.
  • Create work_cache table keyed by stable input hash.
  • Create evidence table for source references and blob pointers.
  • Create embeddings storage and vector indexes.
  • Create ad_role_links or equivalent history if ad grouping needs audit.
  • Add indexes for URL lookup, role/ad reads, org lookup, place/language, source, scrape status, and vector search.
  • Add RU-conscious query budget notes for hot paths.

Dependency: can run after Lane A draft exists; can proceed before app reads.

Lane C: D1 Workspace Rung

Write scope:

  • migrations/
  • ui/src/lib/server/workspace-subdoc.ts
  • lib/mutations/
  • lib/workspace-client/
  • focused tests

Tasks:

  • Confirm jobs is the only workspace sub-doc for job tracking.
  • Align workspace client and ui on job.* ops and the jobs sub-doc.
  • Keep CVL/CL as D1/Y.Doc private workspace documents.
  • Store workspace job records and workspace facts in D1/Y.Doc.
  • Add inherited projection hash/version fields to workspace job records.
  • Add local summary structures for workspace-rung compression.
  • Ensure originless imported cells stay in D1 workspace rung.
  • Ensure D1 never stores shared ad/role facts as a second long-term source.
  • Keep private workspace facts queryable for workspace UI without exposing role/ad nodes directly.

Dependency: partly independent, but read resolver needs Lane E.

Lane D: Outbox And Cross-Database Protocol

Write scope:

  • ui/src/lib/server/
  • ui/src/routes/api/workspaces/[id]/...
  • jobcache/
  • tests under ui and jobcache

Tasks:

  • Add D1 outbox table for commands crossing to Cockroach.
  • Define command ids and input hashes.
  • Define command types:
    • ensure ad;
    • store public scrape;
    • store extraction;
    • refresh shared projection;
  • Implement idempotent command enqueue.
  • Replace direct shared jobcache D1 writes inside workspace sub-doc commit with outbox commands.
  • Implement jobcache worker command consumer.
  • Implement retry and failure states.
  • Implement D1 projection update after Cockroach success.
  • Ensure workspace remains usable when Cockroach is unavailable.
  • Add no-silent-split tests.

Dependency: needs early Cockroach schema and D1 command schema.

Lane E: Read Chain And Projection Resolver

Write scope:

  • lib/domain/src/read-chain.ts
  • ui/src/lib/server/job-records.ts
  • dashboard resolved-record helpers
  • MCP read helpers
  • tests

Tasks:

  • Define resolved workspace projection type.
  • Implement D1 workspace value -> Cockroach ad -> Cockroach role read chain.
  • Ensure absence means inherit, not unknown.
  • Support explicit empty/unknown values separately from absence.
  • Add projection hash calculation.
  • Add projection cache invalidation rules.
  • Ensure workspace APIs expose only resolved projection.
  • Update job-records.ts, workspace job routes, workspace GET, and MCP read helpers to use the resolver.
  • Ensure role/ad nodes are hidden from workspace-facing API/MCP/UI.
  • Add tests for all inheritance combinations.
  • Add tests for parent ad/role projection changes.

Dependency: central. UI/MCP lanes should depend on this.

Lane F: Jobcache Integration

Write scope:

  • jobcache/
  • lib/domain/src/jobcache.ts
  • ui only at outbox/read boundaries

Tasks:

  • Align jobcache service with Cockroach as primary shared database.
  • Add the missing Cockroach store/adapter implied by the current domain reader/writer interfaces.
  • Remove SQLite-only assumptions or keep only for local fixture mode.
  • Make scraper output write ads and work-cache entries to Cockroach.
  • Make extraction output produce sparse ad facts.
  • Add ad grouping into role nodes.
  • Add evidence/blob pointers for raw scrape snapshots.
  • Add embedding generation and vector upsert.
  • Add lookup by normalized URL.
  • Add lookup by ad_id.
  • Add "known ad" endpoint or internal function for workspace import.
  • Add RU-conscious batching for crawler writes.

Dependency: Cockroach schema.

Lane G: Workspace API, MCP, And Browser Worker

Write scope:

  • ui/src/routes/api/workspaces/[id]/...
  • mcp/src/tools/
  • lib/workspace-client/
  • Workspace Intelligence code
  • tests/e2e

Tasks:

  • Update add-job route to write D1 first and enqueue outbox.
  • Audit MCP CVL tools to ensure live quarry, tailored CV/CL, notes, and files remain D1/R2-private unless an explicit exporter exists.
  • Return resolved projection from workspace APIs.
  • Keep direct role/ad queries out of MCP tools.
  • Keep MCP tool names and parameter docs on public job language.
  • Remove stale internal terms once callers are migrated.
  • Update Workspace Intelligence to show D1 outbox/process state.
  • Ensure browser workers claim D1 work only, not direct Cockroach writes.
  • Add tests for headless workspace import with no browser open.
  • Add tests for multi-tab import dedupe.

Dependency: Lanes D, E, F.

Lane H: CVL Boundary

Write scope:

  • lib/mutations/
  • ui/src/lib/components/cvl/
  • ui/src/routes/api/...
  • lib/domain/src/
  • tests

Tasks:

  • Keep live CVL and CL in D1/Y.Doc.
  • Ensure every CVL edit path stays D1/Y.Doc and does not touch Cockroach.
  • Add tests proving live CVL edits do not enqueue Cockroach writes.
  • Add tests proving tailored CV/CL remain scoped to workspace job.

Dependency: immediate. Candidate discovery artifacts are out of scope for the split and must not block it.

Lane I: Migration And Cleanup

Write scope:

  • ui/scripts/
  • lib/importer/
  • migrations/
  • tests

Tasks:

  • Build migration from current D1 shape to D1 workspace jobs plus Cockroach shared ad/role knowledge.
  • Migrate workspace records to workspace.jobs.
  • Migrate shared public ad/role facts to Cockroach.
  • Keep raw private workspace fields in D1.
  • Recompute projection hashes.
  • Add dry-run reports.
  • Add rollback or stop-the-world failure behavior.
  • Delete old paths after migration. No dual current paths.
  • Remove stale route names and docs after code migration.

Dependency: schema and resolver.

Lane J: Test Dataset And Verification

Write scope:

  • lib/domain/tests/fixtures/
  • ui/e2e/fixtures/
  • ui/e2e/stories/
  • jobcache/test/

Dataset:

  • Role R1 has ad A1 and A2.
  • A1 and A2 share most role facts but differ on salary wording and language.
  • Workspaces W1-W12 track A1/A2 with mixed local values:
    • no local value, inherits shared;
    • explicit override;
    • explicit empty;
    • unknown value;
    • local evaluation;
    • custom column.
  • Color toy field for projection/compression tests:
    • shared ad value yellow;
    • D1 groups: inherit, green, blue, red.
  • Commute field with shared workplace and private home location.
  • CVL quarry with many edits and variants.
  • Tailored CV/CL snapshots for A1 and A2.

Test suites:

  • Unit tests for read chain and absence/empty/unknown semantics.
  • Unit tests for projection hash stability.
  • Unit tests for ad reparenting preserving workspace projections.
  • Unit tests for D1 horizontal summaries.
  • Unit tests for outbox idempotency.
  • Unit tests for Cockroach transaction retry helper.
  • Integration test for add URL -> D1 ref -> outbox -> Cockroach ad -> D1 projection update.
  • Integration test for known URL import with no scrape/extract repeat.
  • Integration test for Cockroach unavailable path.
  • E2E test for human import and reload.
  • E2E test for MCP/headless import.
  • E2E test for multi-tab dedupe.
  • E2E test proving CVL edits do not hammer Cockroach.

Dependency: can start now with fixture design.

Lane K: Observability, Quotas, And Operations

Write scope:

  • jobcache/
  • ui/src/lib/server/
  • infra docs
  • tests/smokes

Tasks:

  • Add metrics for Cockroach RU-relevant operations.
  • Add D1 outbox queue depth and failure metrics.
  • Add work-cache hit rate metrics.
  • Add crawler batch size controls.
  • Add smoke check for Cockroach connectivity.
  • Add smoke check for D1-only workspace availability when Cockroach is down.
  • Document backup story:
    • D1 exports;
    • Cockroach backups;
    • R2/B2 blobs;
    • restore drills.
  • Track 10 GiB Cockroach limit and 50M RU/month usage.
  • Track D1 database size and whether per-workspace or per-shard D1 split is needed later.

Dependency: schema and outbox.

Round 1 can run immediately:

  • Agent 1: Lane A, field registry and data ownership.
  • Agent 2: Lane B, Cockroach schema draft.
  • Agent 3: Lane C, D1 workspace rung cleanup.
  • Agent 4: Lane J, test dataset and fixtures.
  • Agent 5: Lane H, live CVL boundary audit.

Round 2 depends on Round 1:

  • Agent 6: Lane D, outbox.
  • Agent 7: Lane E, read chain and projection resolver.
  • Agent 8: Lane F, jobcache integration.

Round 3 depends on Round 2:

  • Agent 9: Lane G, workspace API/MCP/browser worker.
  • Agent 10: Lane I, migration and cleanup.
  • Agent 11: Lane K, operations and quota checks.

Deferred until the split is stable:

  • Reducers and aggregates.
  • Recommendations.
  • Candidate discovery artifacts.

Do not let two agents write the same module family at the same time. Each lane must declare its write scope before implementation starts.

Critical Invariants

  • Workspace APIs expose only resolved workspace projections.
  • Role/ad nodes are hidden from workspace-facing humans, machines, MCP tools, browser workers, and UI.
  • Private workspace data does not leave D1 by default.
  • Privacy is enforced first by placement: D1-private data stays in D1, and exceptions require a named exporter/reducer.
  • Live CVL edits do not write to Cockroach.
  • Cockroach contains shared ad/role knowledge and shared work cache.
  • D1 and Cockroach are coordinated by outbox/idempotency, not fake atomicity.
  • No permanent dual path for the same fact.
  • Originless workspace cells stay in D1 unless a field definition permits upward movement.
  • Compaction must not change workspace projections.
  • Ad grouping must not silently merge workspace tracking records.
Source: wiki/content/working-memory/d1-cockroach-todo.md