Data engineering: separate transactional and analytical paths (Bulk Data, analytics, SQL-on-FHIR)

[cursor]

Summary

HAIStack needs a clear separation between transactional (interactive REST) and analytical (cohort extraction, reporting, AI batch context) data paths. Today the conceptual split exists (pkg/http vs pkg/view + pkg/analytics), but edge mode co-locates OLTP and reporting in the same Postgres database, Bulk Data export is unimplemented (501), analytics uses full-scan view execution, and there is no verification that export features conform to the HL7 Bulk Data profile.

Problem / gap

What we have today

Transactional path (pkg/core + pkg/http):

• CRUD, conditional operations, JSON Patch
• _search (GET/POST), _history
• Transaction/batch bundles
• SDC operations when wired
• Sync hub routes (/sync/push, /sync/pull)
• Write path: validate → persist + history → terminology projection → outbox → search index (single WriteSession transaction)

Analytical path (pkg/view + pkg/analytics):

• Subset of FHIR ViewDefinition (SQL-on-FHIR-style): single resource, flat columns, FHIRPath filters
• No joins, forEach, unionAll, or materialization directives
• pkg/analytics refreshes views into Postgres JSONB reporting tables or CSV export
• Three built-in views: patient_summary_view, appointment_view, observation_view
• Full refresh only; no incremental cursors

Bulk Data:

Component	Status
Server (pkg/http)	Routes exist; return 501 Not Implemented
Client (pkg/client)	Kickoff/poll/cancel/manifest client with tests
NDJSON pipeline	Not implemented

Cloud mode seams:

• ExternalSearch adapter — noop placeholder
• ExternalWarehouse adapter — noop placeholder
• No read replica, columnar store, or CDC/streaming path

What is missing

1. Standard cohort extraction (Bulk Data)

   • GET /fhir/$export and GET /fhir/Group/{id}/$export must implement HL7 Bulk Data profile
   • Async job polling, manifest, NDJSON output, delete-after-download semantics
   • Conformance tests against Bulk Data spec (not just "we emit JSON lines")

2. Analytics path maturity

   • View execution scans ListIDs/Read — not search-driven or warehouse-optimized
   • No incremental refresh (full scan every run)
   • No Parquet, warehouse, or lakehouse sinks (deferred in docs)
   • Reporting tables share Postgres with OLTP in edge mode

3. Physical/logical separation

   • No read replica routing for analytics queries
   • No guidance preventing REST _search pagination abuse for large exports
   • No event-driven CDC from outbox/sync to analytics layer

4. SQL-on-FHIR evaluation

   • Current ViewDefinition subset should be evaluated against maturing SQL-on-FHIR spec
   • Gap analysis: which ViewDefinition features to add vs defer
   • Portability story for views defined in standard IG packages

5. Cloud export verification

   • Before designing downstream jobs against cloud provider "FHIR export" features, verify Bulk Data conformance
   • HAIStack client (pkg/client) exists but server cannot be self-tested

Why this matters for HAIStack

• Researchers and data engineers assume $export + NDJSON manifests for cohort extraction — REST pagination is wrong semantically and expensive on SQLite edge nodes.
• AI pipelines need batch context via views/analytics — full table scans do not scale.
• Edge deployments risk analytics workloads starving interactive clinical use when sharing one SQLite/Postgres instance.
• Interoperability: Downstream Spark/dbt/ML jobs expect standard Bulk Data, not custom export endpoints.

Proposed scope

Phase 1 — Bulk Data server implementation

• Implement GET /fhir/$export (system export) in pkg/http:
  • Async kickoff → 202 Accepted + Content-Location
  • Status polling endpoint
  • Cancel operation
  • Manifest (export.json) with correct structure
  • NDJSON resource files per type
  • _since, _type, _typeFilter parameters
  • Tenant scoping and authorization (backend-service + policy)
• Implement GET /fhir/Group/{id}/$export (group export)
• Wire async execution through pkg/jobs with blob storage for export files (pkg/binary)
• Add integration tests using pkg/client against pkg/http server (round-trip)
• Add conformance test fixtures (manifest schema, NDJSON layout, error cases)

Phase 2 — Analytics pipeline hardening

• Incremental view refresh:
  • Track last-processed resource version/timestamp per view
  • Support _since-style delta refresh
• Search-driven view execution option (use pkg/search index instead of full ListIDs scan where possible)
• Add Parquet export sink (minimum viable)
• Document and enforce: do not use REST search pagination for large cohort export — use Bulk Data

Phase 3 — Transactional vs analytical separation

• Edge mode guidance:
  • Document OLTP vs analytics resource limits (connection pool, query timeout)
  • Optional read-only connection for pkg/analytics against Postgres replica
• Cloud mode:
  • Implement ExternalWarehouse adapter interface (S3 + Parquet or warehouse stub)
  • CDC hook from sync outbox → analytics refresh trigger
• Rate limiting / circuit breaker for analytics jobs impacting OLTP (edge)

Phase 4 — SQL-on-FHIR alignment

• Gap analysis document: HAIStack ViewDefinition subset vs HL7 SQL-on-FHIR spec
• Prioritize features: forEach, joins, unionAll, materialized views
• Support loading ViewDefinitions from conformance IG packages (depends on conformance issue)
• Tests with portable view definitions from public IGs

Phase 5 — Client and cloud export verification

• Self-test harness: HAIStack server + HAIStack client Bulk Data round-trip in CI
• Document how to verify third-party cloud "FHIR export" against Bulk Data profile before building downstream jobs
• Optional: test against HAPI/Firely reference server for client compatibility

Acceptance criteria

• GET /fhir/$export returns valid async Bulk Data response (not 501)
• Export manifest and NDJSON pass Bulk Data structural validation
• pkg/client bulk export integration test passes against pkg/http server
• Authorization enforced: unauthorized principal cannot export
• Analytics refresh supports incremental mode (not only full scan)
• README documents transactional vs analytical path decision tree
• At least one Parquet or warehouse export path demonstrated

Out of scope (for this issue)

• GraphQL API
• Full SQL-on-FHIR engine with arbitrary SQL
• OpenSearch external search backend (separate search scaling issue)
• Real-time streaming (Kafka/Kinesis) — CDC hook only

Affected packages / files

Area	Path
HTTP / Bulk routes	pkg/http/handler.go, pkg/http/doc.go
Bulk client	pkg/client/
Jobs (async export)	pkg/jobs/
Blob storage	pkg/binary/
Views	pkg/view/
Analytics	pkg/analytics/
Runtime modes	pkg/runtime/
Store/reporting	pkg/store/reporting_table.go, pkg/postgres/migrations/0009_reporting_tables.sql
Auth (export permissions)	pkg/auth/

Architecture target

Interactive workloads:
  Client → pkg/http (REST) → pkg/core → pkg/search

Large cohort extraction:
  Client → pkg/http ($export) → pkg/jobs → NDJSON blobs
  OR Client → pkg/client → external conformant server

Analytics / reporting:
  pkg/view (ViewDefinition) → pkg/analytics → reporting tables / Parquet / warehouse

AI batch context:
  pkg/view + pkg/fhirpath (permissioned, row-limited) — not raw Bulk export

References

• HL7 Bulk Data Export
• SQL on FHIR
• HAIStack pkg/http/doc.go — bulk routes documented as 501
• HAIStack pkg/view/README.md — ViewDefinition v1 subset

Related issues

• Conformance artefacts (ViewDefinitions from IG)
• Authorization semantics (export permissions, backend-service clients)
• Benchmark harness (bulk export at scale)