04 · Stand up the store¶
Source
plan/09-mobile-datastore.md,
schema/knowledge_graph.sql,
schema/deep/*.sql,
agents/src/onehealth_agents/kg_writer.py,
agents/src/onehealth_agents/mongo_writer.py,
and ansible/roles/ducklake/files/bootstrap_ducklake.py.
What we wanted¶
A single source of truth for the knowledge graph, queryable both from inside the agents (server-side) and from the browser (client-side, via DuckDB-WASM) — and, once the mobile build became real on 2026-05-20, a write path that could absorb a flexible mobile payload without sacrificing the analytical power of the lakehouse. The non-negotiable was that the privacy contract — coarsen location, strip EXIF GPS, never-diagnose, SHA-256 audit digests — had to be enforced in one place, regardless of which sink ended up holding the row.
What we built¶
A dual-sink datastore with one ingress and one privacy enforcement point:
- DuckLake-on-Postgres for web + analytics: Postgres holds the
lakehouse catalog (time-travel, snapshots, ACID); DuckDB is the
query engine; data lives as Parquet in a local data path (or
S3). Bootstrapped by
ansible/roles/ducklake/files/bootstrap_ducklake.py. - MongoDB Community for mobile writes, self-hosted on the same
VM, bound to
127.0.0.1, auth on. Wired by themongodbansible role added in plan/09 Phase B. - An
X-Client-Channel: mobile | webrequest header onPOST /v1/reportsselects the sink — after the shared privacy enforcement runs, so the contract lives inagents/.../api/routes/reports.pyin exactly one place. - A
mongo_to_ducklakewatermarked, idempotent ETL runs every five minutes as a systemd timer, replaying new Mongo documents into DuckLake viaKgWriter.persist_synced_observation()so the agents, MCP servers, and cluster detection see one unified dataset. - A DuckDB-WASM in-browser query console at
query/(also reachable from the app atapp/src/app/duckdb/), pulling DuckDB-WASM from a pinned unpkg URL — same rule as the MapLibre / Cytoscape pinning inmap/andgraph/.
The graph itself is property-graph–shaped, defined in
schema/knowledge_graph.sql
as three tables:
| Table | Holds | Primary key |
|---|---|---|
kg.node |
every concept — pathogen, county, tribe, parameter, milestone, MCP server, observation, … | node_id (stable dot-namespaced slug) |
kg.edge |
typed relationships — belongsTo, precedes, wraps, informs, hasMilestone, … |
edge_id (BIGINT in a contiguous per-seed range) |
kg.property |
the free-form attribute bag, with value_text for strings/enums and value_num for ordinals/counts |
(node_id, key) |
At archive freeze (2026-05-23), the graph contains 572 nodes /
791 edges / 1,027 properties — counts surfaced by
mcp/README.md
and reachable from any client through knowledge-graph-mcp.
What it looks like¶
The same kg.* tables knowledge-graph-mcp queries server-side are
also queryable client-side through DuckDB-WASM at /query/ — a
browser-only console with sixteen pre-written queries plus a
SELECT-only editor and CSV export:
And the live map at /map/ is the geographic projection of the same
graph: every pin links back to its kg_node_id, so the map and the
graph viewer share a single source of truth:
Decisions & trade-offs¶
DuckLake-on-Postgres over Iceberg, over plain DuckDB¶
The
README.md "Why DuckLake + DuckDB + Postgres"
table is the short version: Postgres is the lakehouse catalog
(snapshots, schema evolution, ACID); DuckLake is the open
lakehouse format on Parquet with the catalog in Postgres;
DuckDB is the embedded query engine that reads/writes DuckLake
natively and can join Parquet + Postgres + CSV in one statement.
Iceberg would have meant a metastore (Hive, Glue, or Nessie), a
separate write engine, and a third process to babysit on a
Jetstream2 VM under one ansible playbook — the wrong shape for
this codebase. Plain DuckDB on its own was ruled out because the
agents, the ansible deploy, and the mongo→ducklake sync all need
a shared catalog that survives restarts and tolerates concurrent
writers; a single epihack.duckdb file ticks none of those
boxes. The win of DuckLake-on-Postgres is that the same engine
runs in the agents
(KgWriter),
in knowledge-graph-mcp, in the analyst dashboard, and in the
browser via DuckDB-WASM — without a translation layer between
them.
Three tables, not fifty¶
A "real" surveillance schema would have a pathogen table, a
vector table, a county table, a tribe table, an
mcp_server table, an observation table, and twenty join
tables. The kg schema collapses all of that into
kg.node + kg.edge + kg.property
(schema/knowledge_graph.sql lines 32-65).
The trade-off is real:
- Loses: column-level types per entity, schema-enforced per-table constraints, narrow indexes.
- Wins: every new concept lands as a
INSERT INTO kg.nodeand anINSERT INTO kg.edge— no migration, no new SQL file, no new MCP tool. The 16 pathogens, 15 counties, 22 federally recognized tribes, 11 MCP servers, eight Figure-2 parameter classes, eleven Figure-3 milestones, and twelve Figure-4 lifecycle steps live in the same three tables. That's whyknowledge-graph-mcpcan ship 12 generic tools instead of a per-entity tool family.
The cost-tracking lives in kg.property as (node_id, key,
value_text | value_num) triples, which is enough for the
ordinal / freshness / jurisdiction / URL properties the seeds
populate.
Dot-namespaced slugs are the join key everywhere¶
Every node carries a stable slug like pathogen.west_nile,
county.maricopa, mcp.vectorsurv, milestone.detect.
CLAUDE.md
is explicit: "Every knowledge-graph node has a stable
dot-namespaced slug. Renames cascade through agents/,
dashboards, and map/graph viewers. Coordinate in the PR." This
matters because:
- Every MapLibre pin in
map/and every Cytoscape node ingraph/carries the slug askg_node_id— click any feature and it round-trips to aSELECTonkg.node. - The agents'
FakeMCPClientreturns slugs as IDs in canned responses (mcp_client.pylines 78-101): Patagonia →county.santa_cruz, downtown Phoenix →county.maricopa. - Slug collisions across seeds are the bug class the
plan/EXECUTION-STATUS-*follow-up list tracks; the namespacing (pathogen.,county.,mcp.,param.) is the cheapest way to prevent them.
Mongo for mobile, DuckLake for web — decided 2026-05-20¶
The split is recorded in
plan/09-mobile-datastore.md:
"the mobile app persists reports to MongoDB; the hosted website
and the analytics/knowledge-graph backend stay on DuckLake. Both
write paths go through FastAPI, so the privacy contract stays
enforced in one place." The reasoning is operational separation
(a flexible document store for an evolving mobile payload,
decoupled from the analytical lakehouse) plus a single
enforcement point: validation, coarsening, EXIF check,
triage-output guard, and audit digest all run before the sink
is chosen by the X-Client-Channel header. The mobile sink
(MongoWriter.persist_observation())
and the web sink
(KgWriter.persist_observation())
receive an already-validated payload; SHA-256 digests of notes
and claim_token are computed identically in both writers; and
the mongo→ducklake sync replays mobile documents so the analyst
dashboard, cluster detector, and knowledge-graph-mcp see one
dataset. The
X-Client-Channel
header default is web; the Capacitor build sets mobile; the
header is documented in api/openapi.yaml (spec-first, per
CLAUDE.md).
Five-minute sync, not change streams (yet)¶
plan/09 Phase C
ships mongo_to_ducklake.py as a watermarked, idempotent ETL
driven by a systemd timer
(onehealth-mongo-sync.timer, every five minutes), not a
MongoDB change-stream consumer. The trade-off is explicit:
- Wins: simpler ops, deterministic batches, easy to back-fill by clearing the watermark.
- Loses: sub-minute freshness in DuckLake for mobile reports.
persist_synced_observation()
is idempotent on observation_id: it probes kg.node for the
ID first and short-circuits if it exists, so re-running the
sync is safe. The audit row carries agent_name = 'mongo-sync'
so the analytics layer can attribute synced rows back to the
mobile channel. A change-stream upgrade is the documented
"lower-latency upgrade path later"
(plan/09 line 67-68),
not a blocker.
DuckDB-WASM in the browser as a third surface¶
Beyond the agents (server-side DuckDB) and the dashboards
(server-side DuckDB through knowledge-graph-mcp), the same
parquet files are queryable in the browser via DuckDB-WASM at
query/.
It's a static page (no bundler, ES modules from a pinned unpkg
URL) with a SQL window and a set of pre-written queries — same
engine, same SQL, same data, no server round-trip. The
conscious trade-off: anything queried from the browser is
public-facing, so the WASM surface gets only aggregated views
(ZCTA-bucketed counts via
aggregate_by_zcta(),
with small-cell suppression and withdrawn-row filtering) and
never raw observation nodes. Privacy rule 6 in
CLAUDE.md
— "Cluster output uses ZCTA-week / ZCTA-2h aggregations, never
individual observations" — is the gate.
Seed load order is standards.sql and pathogens.sql first¶
The bootstrap recipe in
CLAUDE.md
and
README.md
both load
schema/deep/standards.sql
and
schema/deep/pathogens.sql
before every other schema/deep/*.sql file. The reason is
referential:
standards.sqlships SNOMED CT / ICD-10 / Darwin Core nodes that downstream symptom + lab nodes reference.pathogens.sqlships the pathogen / vector / reservoir nodes that the outbreak, county, tribe, and follow-up seeds attach edges to.
The
bootstrap_ducklake.py loader
honours this by delegating to knowledge_graph_mcp.loader.
discover_schema_files() first, with an inline fallback that
explicitly promotes standards.sql and pathogens.sql to the
front of the deep list. Get the order wrong and downstream seeds
fail with foreign-key errors on the in-memory replay step.
DuckLake doesn't enforce PK / UNIQUE / FK
bootstrap_ducklake.py
replays the full schema into an in-memory DuckDB (where
constraints work), then CREATE TABLE … AS SELECT *–copies
every base table into DuckLake (constraints drop on CTAS).
The data is identical; the constraint-checking happens once,
in memory, before anything lands in the lakehouse.
Edge-ID ranges are allocated per seed file¶
Each schema/deep/*.sql seed owns a contiguous edge-ID range
documented in the file header. The actual allocations:
| Seed | Range |
|---|---|
pathogens.sql |
10000-10999 |
tribes.sql |
11000-11999 |
counties.sql |
12000-12999 |
datasets_apis.sql |
13000-13999 |
outbreaks.sql |
14000-14999 |
standards.sql |
15000-15999 |
mcp_servers.sql |
16000-16999 |
application.sql |
20000-20999 |
The convention is in
CLAUDE.md:
"New seeds: pick the next free range, document in the PR."
The motive is two-fold — ON CONFLICT DO NOTHING makes
re-running a seed idempotent, and the contiguous ranges let
a reader open kg.edge sorted by edge_id and see exactly
which seed produced which relationship.

