Data Isolation Between Modules — Schema-per-Module Strategies

Shared database, no isolation:
  Any module can query any table.
  JOIN prescriptions to patients to lab_results to billing in one query.
  A schema change in patients breaks prescriptions, lab, and billing simultaneously.
  All modules must deploy together when any schema changes.

Schema-per-module isolation:
  Each module owns its schema (or database).
  Other modules cannot see or query its tables.
  Schema changes in patients only affect the patients module.
  Modules can deploy independently.

This is how a modular monolith avoids the "big ball of mud" trap.

-- SQL Server: each module gets its own schema
CREATE SCHEMA patients;
CREATE SCHEMA prescriptions;
CREATE SCHEMA lab_results;
CREATE SCHEMA billing;

-- Patients module tables (only patients module reads/writes these)
CREATE TABLE patients.patients (
    id          UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    mrn         NVARCHAR(20)     NOT NULL,
    first_name  NVARCHAR(100)    NOT NULL,
    last_name   NVARCHAR(100)    NOT NULL,
    ward_id     UNIQUEIDENTIFIER NULL
);

-- Prescriptions module tables (different schema, same database or different database)
CREATE TABLE prescriptions.prescriptions (
    id            UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    patient_id    UNIQUEIDENTIFIER NOT NULL,  -- stores PatientId by reference only
    medication    NVARCHAR(200)    NOT NULL,
    dose_amount   DECIMAL(10,4)    NOT NULL
);

-- Cross-schema references stored as IDs only — no FK constraints across schemas
-- (Or use CHECK constraints, but not FOREIGN KEY across schemas/databases)

// Patients module DbContext — only maps patients schema
public sealed class PatientsDbContext : DbContext
{
    public PatientsDbContext(DbContextOptions<PatientsDbContext> options)
        : base(options) { }

    public DbSet<Patient>   Patients   => Set<Patient>();
    public DbSet<Admission> Admissions => Set<Admission>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("patients");
        modelBuilder.ApplyConfigurationsFromAssembly(
            typeof(PatientsDbContext).Assembly);
    }
}

// Prescriptions module DbContext — only maps prescriptions schema
public sealed class PrescriptionsDbContext : DbContext
{
    public DbSet<Prescription> Prescriptions => Set<Prescription>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("prescriptions");
        modelBuilder.ApplyConfigurationsFromAssembly(
            typeof(PrescriptionsDbContext).Assembly);
    }
}

// Both can point to the same connection string (same SQL Server database, different schemas)
// Or different connection strings (different databases) for stronger isolation

// Each module manages its own migrations independently
// Patients module:
dotnet ef migrations add AddPatientWardColumn \
  --project Modules/Patients/Patients.Infrastructure \
  --startup-project Host/SystemForge.Api \
  --context PatientsDbContext \
  --output-dir Persistence/Migrations

// Prescriptions module:
dotnet ef migrations add AddExpiryDate \
  --project Modules/Prescriptions/Prescriptions.Infrastructure \
  --startup-project Host/SystemForge.Api \
  --context PrescriptionsDbContext \
  --output-dir Persistence/Migrations

// Apply at startup — each DbContext applies its own migrations
using (var scope = app.Services.CreateScope())
{
    await scope.ServiceProvider
        .GetRequiredService<PatientsDbContext>()
        .Database.MigrateAsync();

    await scope.ServiceProvider
        .GetRequiredService<PrescriptionsDbContext>()
        .Database.MigrateAsync();
}

// Prescriptions module needs to display patient name and ward alongside prescriptions.
// Instead of calling PatientsService (synchronous coupling), store a snapshot.

// Prescriptions module's own "patient snapshot" table
CREATE TABLE prescriptions.patient_snapshots (
    patient_id   UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    full_name    NVARCHAR(200)    NOT NULL,
    mrn          NVARCHAR(20)     NOT NULL,
    ward_id      UNIQUEIDENTIFIER NULL,
    updated_at   DATETIME2        NOT NULL
);

// Updated via module event subscription
public sealed class UpdatePatientSnapshotOnPatientChanged
    : INotificationHandler<PatientUpdatedModuleEvent>
{
    private readonly PrescriptionsDbContext _db;

    public async Task Handle(PatientUpdatedModuleEvent @event, CancellationToken ct)
    {
        await _db.PatientSnapshots
            .Upsert(new PatientSnapshot(
                @event.PatientId, @event.FullName, @event.Mrn, @event.WardId, DateTime.UtcNow))
            .On(s => s.PatientId)
            .RunAsync(ct);
    }
}

// Queries: JOIN prescriptions.prescriptions with prescriptions.patient_snapshots
// All within one schema — no cross-module data access needed at query time