Distributed Data — Database per Service Pattern

Rule: each microservice owns its data exclusively.
  PatientService:      patients DB (SQL Server)
  PrescriptionService: prescriptions DB (SQL Server)
  LabService:          lab_results DB (PostgreSQL)
  NotificationService: notifications DB (MongoDB)

Why:
  → Services can scale independently (different DB engines, different configs)
  → Schema changes don't break other services
  → Each team deploys without coordinating with other teams

What this means for data:
  → No shared tables or views across services
  → No direct DB calls from one service to another's DB
  → Data duplication is expected and acceptable
  → Cross-service queries require API calls or event-driven projections

ACID transactions span one database.
Across services, you get eventual consistency — not immediate consistency.

Scenario: CreatePrescription
  1. PrescriptionService creates the prescription (ACID within one service)
  2. PrescriptionService publishes PrescriptionCreated event
  3. InventoryService receives event, decrements medication stock
  4. BillingService receives event, creates a billing entry

Between steps 2 and 4, the data is temporarily inconsistent:
  Prescription exists. Stock not yet decremented. Billing not yet created.

In most cases: eventual consistency is acceptable.
  "The prescription appears before the stock adjustment" — clinically fine.

When it is NOT acceptable:
  → Dispensing medication (physical action) — stock must be accurate before dispensing
  → That's where pessimistic locking or a Saga with compensation is needed.

Saga: a sequence of local transactions, each with a compensating transaction
     that can undo it if a later step fails.

Choreography Saga (event-driven):
  Each service publishes events and reacts to other services' events.
  No central coordinator.

Orchestration Saga (command-driven):
  A Saga Orchestrator sends commands and tracks state.
  Easier to reason about, but a central point of failure.

// Orchestration Saga: patient admission flow
public sealed class PatientAdmissionSaga : ISaga
{
    // Step 1: check bed availability
    // Step 2: admit patient
    // Step 3: create billing record
    // Step 4: send notification

    // If Step 3 fails:
    //   Compensate Step 2: discharge patient
    //   Compensate Step 1: release reserved bed

    public async Task HandleAsync(StartPatientAdmission command, CancellationToken ct)
    {
        var admissionId = Guid.NewGuid();

        try
        {
            await _wardService.ReserveBedAsync(command.WardId, admissionId, ct);
            await _patientService.AdmitPatientAsync(command.PatientId, command.WardId, ct);
            await _billingService.CreateAdmissionBillingAsync(admissionId, ct);
            await _notificationService.SendAdmissionConfirmationAsync(command.PatientId, ct);
        }
        catch (BillingException)
        {
            // Compensate in reverse order
            await _patientService.UndoAdmissionAsync(command.PatientId, ct);
            await _wardService.ReleaseReservationAsync(command.WardId, admissionId, ct);
            throw;
        }
    }
}

// PrescriptionService needs to display patient name alongside prescriptions
// But PatientService owns patient names

// Option A: call PatientService API at query time (synchronous coupling)
var patient      = await _patientClient.GetAsync(prescriptionDto.PatientId, ct);
prescriptionDto.PatientName = patient?.FullName;

// Option B: denormalized snapshot in PrescriptionService DB (recommended)
// When PatientNameChanged event received, update the snapshot

public sealed class PatientNameUpdatedHandler
    : IIntegrationEventHandler<PatientNameChangedEvent>
{
    public async Task Handle(PatientNameChangedEvent @event, CancellationToken ct)
    {
        await _db.PrescriptionPatientSnapshots
            .Where(s => s.PatientId == @event.PatientId)
            .ExecuteUpdateAsync(s =>
                s.SetProperty(x => x.PatientName, @event.NewFullName), ct);
    }
}

// Trade-off: data may be stale by seconds/milliseconds
// Acceptable for most reporting and display use cases