PostgreSQL Schema, Migrations, and EF Core Configuration

ClinicFlow uses PostgreSQL 17 as its primary data store, accessed through Entity Framework Core with the Npgsql provider. All schema changes are version-controlled as EF Core migrations stored in ClinicFlow.Infrastructure/Migrations/. This page explains how the context is configured, how soft-delete filtering works, how to manage migrations from the CLI, and how the seeding layer generates realistic development data.

ApplicationDbContext

ApplicationDbContext is the single EF Core DbContext for the entire application. It is defined in ClinicFlow.Infrastructure.Persistence and exposes a DbSet<T> property for each of the ten aggregate roots and supporting entities:

public class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : DbContext(options)
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Doctor> Doctors => Set<Doctor>();
    public DbSet<Patient> Patients => Set<Patient>();
    public DbSet<PatientPenalty> PatientPenalties => Set<PatientPenalty>();
    public DbSet<Appointment> Appointments => Set<Appointment>();
    public DbSet<AppointmentTypeDefinition> AppointmentTypes => Set<AppointmentTypeDefinition>();
    public DbSet<ClinicalFormTemplate> ClinicalFormTemplates => Set<ClinicalFormTemplate>();
    public DbSet<MedicalRecord> MedicalRecords => Set<MedicalRecord>();
    public DbSet<MedicalSpecialty> MedicalSpecialties => Set<MedicalSpecialty>();
    public DbSet<Schedule> Schedules => Set<Schedule>();
    // ...
}

OnModelCreating performs two cross-cutting configuration passes before delegating to the per-entity Fluent API configurations:

Domain event exclusion — BaseEntity.DomainEvents is a transient in-memory collection that must not be persisted. The method iterates over all BaseEntity subtypes and calls Ignore(nameof(BaseEntity.DomainEvents)).
Soft-delete global query filters — described in detail below.
Assembly scan — modelBuilder.ApplyConfigurationsFromAssembly(...) picks up all IEntityTypeConfiguration<T> classes in the Infrastructure assembly.

Soft-Delete Global Query Filters

Several entities inherit from SoftDeletableEntity, which adds an IsDeleted boolean column. Rather than scattering .Where(e => !e.IsDeleted) throughout every query, ApplicationDbContext registers a global query filter at model creation time so that soft-deleted rows are automatically excluded from every LINQ query against those entity sets. Because EF Core’s HasQueryFilter requires a strongly-typed lambda and the entity type is only known at runtime, the filter expression is built dynamically via the System.Linq.Expressions API:

foreach (var clrType in modelBuilder.Model.GetEntityTypes()
    .Where(e => typeof(SoftDeletableEntity).IsAssignableFrom(e.ClrType))
    .Select(e => e.ClrType))
{
    var parameter = Expression.Parameter(clrType, "e");
    var body = Expression.Equal(
        Expression.Property(parameter, nameof(SoftDeletableEntity.IsDeleted)),
        Expression.Constant(false)
    );
    var lambda = Expression.Lambda(body, parameter);
    modelBuilder.Entity(clrType).HasQueryFilter(lambda);
}

This means that any query such as context.Patients.ToListAsync() silently adds WHERE "IsDeleted" = FALSE. To query soft-deleted rows explicitly, use IgnoreQueryFilters():

context.Patients.IgnoreQueryFilters().Where(p => p.IsDeleted).ToListAsync();

Column Name Constants

EF Core Fluent API configurations reference column names through the ColumnNames internal static class in ClinicFlow.Infrastructure.Persistence. This prevents magic strings from spreading across configuration files:

internal static class ColumnNames
{
    internal static class Appointment
    {
        public const string StartTime = "StartTime";
        public const string EndTime   = "EndTime";
    }

    internal static class AppointmentTypeDefinition
    {
        public const string AgePolicyMinimumAge           = "AgePolicyMinimumAge";
        public const string AgePolicyMaximumAge           = "AgePolicyMaximumAge";
        public const string AgePolicyRequiresLegalGuardian = "AgePolicyRequiresLegalGuardian";
    }

    internal static class Doctor
    {
        public const string ConsultationRoomNumber = "ConsultationRoomNumber";
        public const string ConsultationRoomName   = "ConsultationRoomName";
        public const string ConsultationRoomFloor  = "ConsultationRoomFloor";
    }

    internal static class Patient
    {
        public const string EmergencyContactName  = "EmergencyContactName";
        public const string EmergencyContactPhone = "EmergencyContactPhone";
    }

    internal static class Schedule
    {
        public const string StartTime = "StartTime";
        public const string EndTime   = "EndTime";
    }
}

Connection String Configuration

The connection string is loaded from the Database section of appsettings.json using the DatabaseOptions class:

public sealed class DatabaseOptions
{
    public const string SectionName = "Database";

    public string ConnectionString { get; init; } = string.Empty;
    public bool SeedOnStartup { get; init; }

    public static DatabaseOptions FromConfiguration(IConfiguration configuration) =>
        configuration.GetSection(SectionName).Get<DatabaseOptions>()
        ?? throw new InvalidOperationException(
            "Database configuration is missing. Ensure 'Database' section exists in appsettings."
        );
}

In appsettings.json (or environment-specific overrides), add:

{
  "Database": {
    "ConnectionString": "Host=localhost;Port=5432;Database=ClinicFlowDb;Username=postgres;Password=postgres",
    "SeedOnStartup": false
  }
}

ApplicationDbContextFactory (Design-Time)

EF Core CLI tools (dotnet ef migrations add, dotnet ef database update) need to instantiate ApplicationDbContext without a running host application. ApplicationDbContextFactory fulfills this role by implementing IDesignTimeDbContextFactory<ApplicationDbContext>:

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();

        optionsBuilder.UseNpgsql(
            "Host=localhost;Port=5432;Database=ClinicFlowDb;Username=postgres;Password=postgres"
        );

        optionsBuilder.UseSeeding(
            (context, _) =>
                Seeding.DbSeeder.Seed((ApplicationDbContext)context, TimeProvider.System)
        );

        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

The connection string inside ApplicationDbContextFactory is a hardcoded local development credential. It is intentional — this class exists solely for EF Core CLI migrations and is never invoked at runtime. It will be removed once the API layer provides its own DI-based context configuration. Never copy this connection string into a production configuration.

Managing Migrations

Apply all pending migrations

dotnet ef database update --project ClinicFlow.Infrastructure

Create a new migration

After modifying entity configurations or adding new entities, generate a migration snapshot:

dotnet ef migrations add <MigrationName> --project ClinicFlow.Infrastructure

Replace <MigrationName> with a descriptive PascalCase name, for example AddPatientEmergencyContact. The generated files appear in ClinicFlow.Infrastructure/Migrations/.

Revert the last migration (before applying to the database)

dotnet ef migrations remove --project ClinicFlow.Infrastructure

Roll back to a specific migration

dotnet ef database update <TargetMigrationName> --project ClinicFlow.Infrastructure

Seeding Layer

The ClinicFlow.Infrastructure/Persistence/Seeding/ directory contains a self-contained, deterministic data generator for local development. The entry point is DbSeeder, which orchestrates the entire seeding pipeline:

Step	What is created
`SeedMedicalSpecialtiesAsync`	15 medical specialties (General Medicine, Cardiology, Oncology, etc.)
`SeedClinicalFormTemplatesAsync`	Clinical form template definitions
`SeedAppointmentTypeDefinitionsAsync`	Appointment type catalogue with age policies and required templates
`SeedUsersAsync`	3 admins, 8 receptionists, 35 doctors, 120 patients
`SeedDoctorsAsync`	35 doctor profiles with consultation rooms and specialties
`SeedPatientsAsync`	105 self patients + 15 older patients + 80 family-member dependents
`SeedSchedulesAsync`	Weekly schedules for all 35 doctors
`SeedAppointmentsAsync`	500 appointments across all statuses via `AppointmentGenerator`
`SeedMedicalRecordsAsync`	250 medical records linked to completed appointments
`SeedPatientPenaltiesAsync`	Automatic penalties for no-shows and late cancellations

AppointmentGenerator produces deterministic appointments by cycling through a fixed distribution of statuses (250 Completed, 100 Scheduled, 50 Cancelled, and so on) and matching each appointment to eligible doctors, patients, and schedules. The seeder uses Randomizer.Seed = new Random(42) so every fresh run produces exactly the same dataset.

Get Started

Core Concepts

Features

Configuration & Deployment

Development Guide

PostgreSQL Schema, Migrations, and EF Core Configuration

ApplicationDbContext

Soft-Delete Global Query Filters

Column Name Constants

Connection String Configuration

ApplicationDbContextFactory (Design-Time)

Managing Migrations

Apply all pending migrations

Create a new migration

Revert the last migration (before applying to the database)

Roll back to a specific migration

Seeding Layer

Build docs developers (and LLMs) love

Get Started

Core Concepts

Features

Configuration & Deployment

Development Guide

Documentation Index

​ApplicationDbContext

​Soft-Delete Global Query Filters

​Column Name Constants

​Connection String Configuration

​ApplicationDbContextFactory (Design-Time)

​Managing Migrations

​Apply all pending migrations

​Create a new migration

​Revert the last migration (before applying to the database)

​Roll back to a specific migration

​Seeding Layer

Build docs developers (and LLMs) love

ApplicationDbContext

Soft-Delete Global Query Filters

Column Name Constants

Connection String Configuration

ApplicationDbContextFactory (Design-Time)

Managing Migrations

Apply all pending migrations

Create a new migration

Revert the last migration (before applying to the database)

Roll back to a specific migration

Seeding Layer