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:

```sql
-- 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:

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

Each `patient_encounters` document already contains:
```json
{
  "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.