vigilcare-clinical/docs/guides/11-cqrs-command-query-separation.md

Guide 11: CQRS (Command Query Responsibility Segregation)

What is CQRS?

CQRS stands for Command Query Responsibility Segregation. It's an architectural pattern where you use different models (and often different databases) for writing data versus reading data.

In a traditional application, you have one database that handles everything:

┌─────────────┐     read + write     ┌────────────┐
│  Application│ ◄──────────────────► │ PostgreSQL │
└─────────────┘                      └────────────┘

With CQRS, you split the read and write paths:

┌─────────────┐      write           ┌────────────┐
│  API writes │ ───────────────────► │ PostgreSQL │  (normalized, transactional)
└─────────────┘                      └──────┬─────┘
                                            │ events (via Kafka)
                                            ▼
┌─────────────┐      read            ┌───────────────┐
│  Dashboard  │ ◄──────────────────  │ Elasticsearch │  (denormalized, searchable)
│  queries    │                      └───────────────┘
└─────────────┘

Why Separate Reads and Writes?

The ideal data structure for writing is different from the ideal structure for reading:

Writing needs:

• Normalized tables (no duplicated data) to prevent inconsistencies
• Foreign keys and constraints to enforce business rules
• ACID transactions to guarantee atomicity
• Example: A patient's name is stored once in the patients table, referenced by ID everywhere else

Reading needs:

• Denormalized documents (all related data in one place) to avoid expensive JOINs
• Full-text search and fuzzy matching
• Fast aggregations (counts, averages by department)
• Example: A single document contains the patient name, encounter status, department, alert count, NEWS2 score — no JOINs needed

You can't optimize for both in one database. A normalized PostgreSQL schema is great for writes but requires multiple JOINs for complex reads. An Elasticsearch index is great for reads but has no foreign keys, no transactions, and no constraint enforcement.

The Event Bridge

The write side and read side are connected by events. When the write side changes data, it publishes an event. A consumer on the read side processes that event and updates its own data store. This makes the read side eventually consistent — there's a short delay (usually under a second) between a write and the data appearing on the read side.

---

How CQRS Works in This Project

                 WRITE PATH                                READ PATH
                 ──────────                                ─────────
  HTTP Request                                          Dashboard / API
       │                                                     │
       ▼                                                     ▼
┌──────────────┐                                    ┌────────────────┐
│ Controller   │                                    │ Analytics      │
│              │                                    │ Controller     │
│ POST /obs    │                                    │ GET /search    │
│ POST /alerts │                                    │ GET /analytics │
└──────┬───────┘                                    └────────┬───────┘
       │                                                     │
       ▼                                                     ▼
┌──────────────┐                                    ┌────────────────┐
│ Service      │                                    │ Analytics      │
│ Layer        │                                    │ Service        │
│              │                                    │                │
│ Validates,   │                                    │ Queries ES     │
│ writes to DB │                                    │ (no JOINs)    │
└──────┬───────┘                                    └────────┬───────┘
       │                                                     │
       ▼                                                     ▼
┌──────────────┐    outbox     ┌─────┐  es-indexer  ┌────────────────┐
│  PostgreSQL  │──────────────►│Kafka│─────────────►│ Elasticsearch  │
│              │    relay      │     │              │                │
│  (source of  │               └─────┘              │ patient_enc.   │
│   truth)     │                                    │ observations   │
└──────────────┘                                    │ clinical_alerts│
                                                    └────────────────┘

The Write Path (Commands)

"Commands" are operations that change state — creating patients, recording observations, acknowledging alerts. They go through the service layer and write to PostgreSQL:

1. Validate the request (FluentValidation)
2. Apply business logic (threshold checks, scoring)
3. Write to PostgreSQL (the source of truth) in a transaction
4. Write outbox events (in the same transaction) for downstream consumers
5. Return the response — the caller doesn't wait for Elasticsearch

The Read Path (Queries)

"Queries" are operations that read state without changing it — searching patients, viewing analytics, getting alert summaries. They read from Elasticsearch:

1. Build an Elasticsearch query (filters, full-text search, aggregations)
2. Execute against Elasticsearch — one network call returns a denormalized document with all the data
3. Return the results — no JOINs, no multiple database calls

The Bridge: EsIndexerService

The EsIndexerService Kafka consumer reads events from the write side and updates Elasticsearch:

Kafka Topic	ES Operation	What Happens
encounter.status.changed	Upsert patient_encounters	Creates or updates the encounter document with patient name, department, status
observation.recorded	Index observations + update patient_encounters	Appends the observation and updates lastObservationAt on the encounter
alert.generated	Index clinical_alerts + update patient_encounters	Appends the alert and increments openAlertCount on the encounter
sepsis.bundle.created	Update patient_encounters	Stamps bundle status on the encounter document
sepsis.bundle.updated	Update patient_encounters	Updates bundle compliance status

Each handler is idempotent — processing the same event twice produces the same result, so retries after failures don't corrupt data.

---

Denormalization in Practice

In PostgreSQL (normalized), getting a ward overview requires joining 4+ tables:

-- Normalized: multiple JOINs
SELECT e.id, p.first_name, p.last_name, p.mrn,
       e.department, e.status,
       COUNT(a.id) as open_alerts,
       MAX(o.recorded_at) as last_observation
FROM encounters e
JOIN patients p ON e.patient_id = p.id
LEFT JOIN clinical_alerts a ON a.encounter_id = e.id AND a.status = 'OPEN'
LEFT JOIN observations o ON o.encounter_id = e.id
WHERE e.status = 'Active'
GROUP BY e.id, p.first_name, p.last_name, p.mrn, e.department, e.status;

In Elasticsearch (denormalized), it's one query with no JOINs:

{
  "query": { "term": { "status": "Active" } },
  "sort": [{ "openAlertCount": "desc" }]
}

Each patient_encounters document already contains:

{
  "encounterId": "...",
  "patientName": "John Smith",
  "mrn": "MRN-001",
  "department": "ICU",
  "status": "Active",
  "openAlertCount": 3,
  "lastObservationAt": "2026-06-24T14:23:00Z",
  "news2Score": 7,
  "news2RiskLevel": "HIGH",
  "sepsisBundleStatus": "IN_PROGRESS"
}

The tradeoff: the ES indexer must keep these denormalized fields in sync whenever the source data changes. Every alert generated increments openAlertCount. Every observation updates lastObservationAt. Every NEWS2 score updates news2Score and news2RiskLevel. This is more work on the write side, but it makes the read side fast and simple.

---

When Data Lives in Which Store

Data Need	Store	Why
Record a new observation	PostgreSQL	Needs transactions, constraints, outbox
Acknowledge an alert	PostgreSQL	Needs atomic status update, audit log
Search patients by name/MRN	Elasticsearch	Full-text search, relevance scoring
Filter encounters by department	Elasticsearch	Fast keyword filtering on denormalized docs
Alert volume by department	Elasticsearch	Aggregations across millions of documents
Observation trend over time	Elasticsearch	Date histogram aggregations
Compute NEWS2/SOFA score	PostgreSQL (via Redis)	Needs transactional alert creation
Generate discharge summary	PostgreSQL	Needs authoritative patient/encounter data

---

Eventual Consistency

What does "eventually consistent" mean in practice? When a nurse records a vital sign:

1. t=0ms: PostgreSQL has the observation (write committed)
2. t=0ms: HTTP response returned to the nurse (she sees "saved")
3. t=1000ms: Outbox relay publishes to Kafka
4. t=1100ms: ES indexer consumer processes the event
5. t=1100ms: Elasticsearch has the observation (read store updated)

During the ~1 second between steps 2 and 5, the dashboard (reading from Elasticsearch) doesn't yet show the new observation. In practice, the dashboard polls every 5-10 seconds, so the delay is imperceptible.

What if the read store is wrong? Elasticsearch is never the source of truth. If it gets corrupted or out of sync, you can rebuild it entirely by resetting the es-indexer consumer group's Kafka offset to zero and replaying all events from the beginning.

---

Where the Pattern Components Live

Component	File	Role
Write side (domain logic)	Services/ObservationService.cs	Validates, writes to PostgreSQL + outbox
Outbox entity	Data/Configurations/OutboxEventConfiguration.cs	Defines the outbox_events table
Event relay	BackgroundServices/OutboxRelayService.cs	Polls outbox, publishes to Kafka
Event bridge	BackgroundServices/ElasticsSearch/EsIndexerService.cs	Kafka → Elasticsearch projections
Read side (queries)	Services/AnalyticsService.cs	Queries Elasticsearch for search/analytics
Index setup	BackgroundServices/ElasticsSearch/ElasticIndexProvisioner.cs	Creates ES indexes on startup

---

Key Takeaways

• CQRS is about using the right tool for each job — PostgreSQL for writes (transactions, constraints), Elasticsearch for reads (search, aggregations).
• Events connect the two sides — the outbox pattern guarantees events are published, and Kafka consumers update the read store.
• Denormalization trades write complexity for read performance — updating openAlertCount on every alert is extra work, but it eliminates JOINs on every dashboard refresh.
• Eventual consistency is the tradeoff — there's a short delay between a write and the data appearing in the read store. For dashboards that poll every few seconds, this is invisible.
• The read store is rebuildable — if Elasticsearch data gets corrupted, reset the consumer offset and replay all events. PostgreSQL is always the source of truth.
• You don't need CQRS everywhere — simple CRUD endpoints that don't need search or aggregations can read directly from PostgreSQL. CQRS adds complexity, so only use it where the read and write requirements genuinely differ.