DuckLake schema

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.

Why DuckLake + DuckDB + Postgres

LayerRole
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.

Bootstrap

# 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

Core graph — schema/

Frameworks from the reference figures plus the two focus-group seeds. Load these first.

knowledge_graph.sql

Core kg.node / kg.edge / kg.property tables plus the seed nodes from Figures 1–4 (purpose, minimum dataset, timeliness milestones, design lifecycle).

315 lines · 11 INSERT blocks

system_designs.sql

Worksheet template (Figure 5), focus-area catalog, and a few completed example designs.

148 lines · 8 INSERT blocks

world_cafe.sql

World Café Q4 engagement cards (heat, unhoused, information flow) and the tactics each table produced.

140 lines · 8 INSERT blocks

wildlife_vectors.sql

Wildlife & Vector-Borne Diseases focus group: four guiding questions plus 30+ anchor resources across state, county, tribal, federal, academic, and citizen-science jurisdictions.

360 lines · 14 INSERT blocks

heat.sql

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).

328 lines · 13 INSERT blocks

Deep seeds — 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.

deep/counties.sql

All 15 Arizona counties: FIPS, seat, area, population, vector control program, and the agency-to-county edges.

364 lines · 11 INSERT blocks

deep/tribes.sql

All 22 federally recognized AZ tribes: enrollment, land base, epidemiology contacts, ITCA-TEC affiliation, and tribal-data sovereignty annotations.

519 lines · 16 INSERT blocks

deep/pathogens.sql

Pathogens with their vectors, reservoirs, AZ presence, ICD-10 codes, and surveilling agencies. Drives the Cytoscape pathogen graph.

590 lines · 13 INSERT blocks

deep/outbreaks.sql

Historical AZ outbreaks (hantavirus, plague, WNV, etc.) with the Figure-3 timeliness-milestone dates (Detect → Notify → Verify → Lab → Respond).

528 lines · 7 INSERT blocks

deep/datasets_apis.sql

Upstream datasets and APIs (NEON DPs, WHISPers, NWS, GBIF, iNat, VectorSurv, AZGFD, ArcGIS Feature Services, etc.) modeled as resource and api nodes.

578 lines · 14 INSERT blocks

deep/standards.sql

Interop standards used by the agentic pipeline: FHIR, OMOP CDM, ICD-10, SNOMED CT, LOINC, Darwin Core, GeoSPARQL.

550 lines · 14 INSERT blocks

deep/mcp_servers.sql

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.

133 lines · 8 INSERT blocks

deep/outbreaks_near_me.sql

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.

58 lines · 3 INSERT blocks

deep/application.sql

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.*).

540 lines · 15 INSERT blocks

deep/audit.sql

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.

158 lines · views only

deep/followups.sql

Idempotent patch file: small gaps the Phase 0 sub-agents flagged (missing SNOMED codes, county aliases, etc.). Safe to re-run.

165 lines · 7 INSERT blocks

deep/cluster_followups.sql

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.

146 lines · 4 INSERT blocks

Example queries

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;

Conventions for new seeds

Cross-links