SQL seeds for the EpiHack Arizona 2026 knowledge graph — a
property graph encoded as kg.node / kg.edge
/ kg.property tables, stored in DuckLake (Postgres
catalog + Parquet data), queried with DuckDB.
| Layer | Role |
|---|---|
| Postgres | DuckLake catalog: snapshot metadata, schema evolution, ACID transactions over the lakehouse. |
| DuckLake | Open lakehouse format on top of Parquet, catalog in Postgres. Time travel, branches, and multi-writer concurrency without having to stand up Iceberg/Hudi infrastructure. |
| DuckDB | Embedded query engine. Reads/writes DuckLake natively; can join Parquet, Postgres, and CSV in a single SQL statement; works on a laptop and at hack-day scale. |
# 1. Postgres for the DuckLake catalog
createdb epihack
# 2. DuckDB session
duckdb
-- Inside DuckDB:
INSTALL ducklake; INSTALL postgres;
LOAD ducklake; LOAD postgres;
ATTACH 'ducklake:postgres:dbname=epihack host=localhost user=epihack'
AS epihack
(DATA_PATH 's3://epihack/ducklake/'); -- or a local path for laptop dev
USE epihack;
-- Core graph (run first)
.read schema/knowledge_graph.sql
.read schema/system_designs.sql
.read schema/world_cafe.sql
.read schema/wildlife_vectors.sql -- focus group 1
.read schema/heat.sql -- focus group 2
-- Deep seeds (any order, except the explicit dependencies below)
.read schema/deep/counties.sql
.read schema/deep/tribes.sql
.read schema/deep/pathogens.sql
.read schema/deep/outbreaks.sql
.read schema/deep/datasets_apis.sql
.read schema/deep/standards.sql
.read schema/deep/mcp_servers.sql
.read schema/deep/outbreaks_near_me.sql
.read schema/deep/application.sql -- depends on knowledge_graph.sql
.read schema/deep/audit.sql -- depends on application.sql
.read schema/deep/followups.sql -- run last (idempotent patches)
.read schema/deep/cluster_followups.sql
schema/Frameworks from the reference figures plus the two focus-group seeds. Load these first.
Core kg.node / kg.edge / kg.property
tables plus the seed nodes from Figures 1–4 (purpose,
minimum dataset, timeliness milestones, design lifecycle).
Worksheet template (Figure 5), focus-area catalog, and a few completed example designs.
World Café Q4 engagement cards (heat, unhoused, information flow) and the tactics each table produced.
Wildlife & Vector-Borne Diseases focus group: four guiding questions plus 30+ anchor resources across state, county, tribal, federal, academic, and citizen-science jurisdictions.
Heat focus group: four guiding questions, vulnerable populations, and 30+ heat-specific resources (ADHS BRACE, MAG HRN, Phoenix OHRM, NWS HeatRisk, UA Heat Resilience Initiative, CLIMAS, 211 Arizona).
schema/deep/Per-domain seeds produced by parallel deep-research sub-agents. Each
file documents its own dependencies and reserves a numeric range of
edge_id values to avoid collisions.
All 15 Arizona counties: FIPS, seat, area, population, vector control program, and the agency-to-county edges.
All 22 federally recognized AZ tribes: enrollment, land base, epidemiology contacts, ITCA-TEC affiliation, and tribal-data sovereignty annotations.
Pathogens with their vectors, reservoirs, AZ presence, ICD-10 codes, and surveilling agencies. Drives the Cytoscape pathogen graph.
Historical AZ outbreaks (hantavirus, plague, WNV, etc.) with the Figure-3 timeliness-milestone dates (Detect → Notify → Verify → Lab → Respond).
Upstream datasets and APIs (NEON DPs, WHISPers, NWS, GBIF, iNat,
VectorSurv, AZGFD, ArcGIS Feature Services, etc.) modeled as
resource and api nodes.
Interop standards used by the agentic pipeline: FHIR, OMOP CDM, ICD-10, SNOMED CT, LOINC, Darwin Core, GeoSPARQL.
MCP servers and their exposed tools as graph nodes
(mcp_server, mcp_tool, api),
wired up with wraps / exposedBy /
informs edges. Backs the
vectorsurv-mcp family.
Boston Children's / HealthMap Outbreaks Near Me as a
resource_org + dataset, with edges to the
vector-borne and zoonotic focus areas and to the Figure-3
Detect milestone.
Application runtime types: the observation node type
that every report lands in, plus heat / VBD symptom and exposure
enumerations, consent profiles, wearable metrics, and the
triage-class enum (tc.*).
Agent-runtime audit log: one row per agent invocation, written by the orchestrator's audit sink, plus a per-observation timeliness pivot view that anchors the Figure-3 milestones.
Idempotent patch file: small gaps the Phase 0 sub-agents flagged (missing SNOMED codes, county aliases, etc.). Safe to re-run.
Closes the eight historical-outbreak gaps the cluster-detection
calibration sub-agent itemised in
plan/CLUSTER-CALIBRATION.
Adds single_case_alertable flags read by the
Tier-A detector at runtime.
After loading, the graph is queryable in plain SQL. A few starters:
-- All parameters in the Exposure class (Figure 2)
SELECT n.label
FROM kg.edge e
JOIN kg.node n ON n.node_id = e.subject_id
WHERE e.predicate = 'belongsTo'
AND e.object_id = 'category.exposure';
-- Outbreak timeliness intervals (Figure 3)
SELECT * FROM kg.v_outbreak_timeliness;
-- Which MCP tools answer Wildlife Q1?
SELECT s.label AS mcp_server, t.label AS mcp_tool
FROM kg.edge ei
JOIN kg.node t ON t.node_id = ei.subject_id
JOIN kg.edge eb ON eb.subject_id = t.node_id AND eb.predicate = 'exposedBy'
JOIN kg.node s ON s.node_id = eb.object_id
WHERE ei.predicate = 'informs'
AND ei.object_id = 'wv.q1';
-- Convenience: every MCP server with the API it wraps
SELECT * FROM kg.v_mcp_servers;
INSERT uses
ON CONFLICT DO NOTHING, so re-running a seed is safe.16000–16999 for mcp_servers.sql,
40000–40999 for audit.sql,
50000–50999 for cluster_followups.sql).
New seeds should pick the next free block and document it in the
header comment.source_fig: every node and edge carries
a source_fig tag so any row can be traced back to the
figure, focus group, or sub-agent that introduced it.kg.v_*: convenience views are
defined in the seed that owns their data and exposed under the
kg.v_ prefix.deep/pathogens.sql.deep/counties.sql and
deep/tribes.sql.deep/mcp_servers.sql.