ADR-005: Automated Database Migration Execution and Ticket-Driven Schema Governance

Status

ACCEPTED  2026-09-12

Context

In enterprise SaaS systems with PostgreSQL Row-Level Security (RLS) and strict regulatory standards (ANVISA RDC 67/2007, Portaria SVS/MS 344/98, CFM Res. 1.821), database schema evolution requires deterministic orchestration.

The Pitfalls of Naive Database Migrations

1. The In-App Startup Anti-Pattern (Database.Migrate() in Program.cs):

   • Security Leak (DML vs DDL): Application server roles (gercia_server, gercia_api) must operate strictly under the principle of least privilege, with Data Manipulation Language (DML) rights only (SELECT, INSERT, UPDATE, DELETE). Granting Data Definition Language (DDL) permissions (ALTER TABLE, CREATE POLICY) to application server hosts creates severe blast-radius vulnerabilities in the event of SQL injection or container compromise.
   • Race Conditions & Concurrency Locks: When multiple horizontal replicas or rolling-deployment pods start concurrently, they race to apply pending migrations simultaneously, causing deadlock collisions on the migration history table or crashed startup sequences.
   • Healthcheck Failures & Boot Loops: If a schema migration takes 20 to 30 seconds to alter an index or migrate data, container healthcheck probes (/health) fail, causing container orchestrators to kill and restart containers in an infinite boot loop.

2. The Frictions of EF Core Migration Bundles (efbundle):

   • While dotnet ef migrations bundle avoids SDK dependencies, it incurs compilation overhead in Cloud Build (compiling standalone executables for every DbContext).
   • It struggles to seamlessly integrate with dynamic Google Secret Manager resolution and transport security credentials configured in our C# infrastructure helpers (PostgresSecurityHelper).

3. Rejection of Out-of-the-Box EF Core History Tables (public.__EFMigrationsHistory):

   • By default, out-of-the-box EF Core creates a global __EFMigrationsHistory table in the public schema. In our hardened PostgreSQL architecture, the public schema has revoked privileges and is excluded from the search_path. Furthermore, sharing a single default table between multiple DbContexts (GerciaDbContext and GerciaClinicalDbContext) causes severe metadata collisions and snapshot inconsistencies.
   • Arbitrary human-written migration names (AddFields, UpdateDatabase) lead to naming drift and ambiguity, while random 32-character GUIDs conceal what each migration actually did during production incident triage and regulatory compliance audits.

Decision

1. Ephemeral Pre-Deployment Migration Runner (--migrate)

The platform implements a dedicated, non-interactive migration CLI mode within the Gercia.Server host, executed as an ephemeral Cloud Run Job:

flowchart TD
    subgraph CI ["1. Google Cloud Build CI/CD"]
        BUILD["Builds & Pushes gercia-server:staging (or :main)<br>to Google Artifact Registry"]
    end

    subgraph DEPLOY ["2. Ephemeral Cloud Run Job Execution"]
        direction TB
        RUN["2.1 Execute Cloud Run Job:<br><code>gcloud run jobs execute gercia-migrator --wait</code>"]
        MIG_CORE["2.2 coreContext.Database.MigrateAsync()"]
        MIG_CLIN["2.3 clinicalContext.Database.MigrateAsync()"]
        CHECK{"All migrations<br>succeeded?"}

        RUN --> MIG_CORE --> MIG_CLIN --> CHECK
    end

    subgraph PROMOTION ["3. Service Promotion (Google Cloud Run)"]
        SUCCESS["Exit 0: Cloud Run deploys and promotes new revisions<br>(gercia-server)"]
        FAILURE["Exit 1: Pipeline aborts immediately.<br>Existing revisions continue serving 100% traffic untouched."]
    end

    BUILD --> RUN
    CHECK -- "YES" --> SUCCESS
    CHECK -- "NO" --> FAILURE

• Execution Lifecycle: Before Cloud Run deploys and promotes new service revisions, an ephemeral Cloud Run Job executes gcloud run jobs execute gercia-migrator-$_ENV --wait.
• Exit Codes: If all registered DbContexts migrate successfully, the process logs confirmation and exits with code 0. If any migration throws an exception, it logs the error via Serilog and terminates with exit code 1, aborting deployment before user-facing containers receive traffic.
• Separation of Privileges: The migration run is granted elevated DDL credentials from Google Secret Manager (CONNECTION_STRING_MIGRATOR_*), while Web and API containers run with least-privilege DML credentials.

2. Multi-Schema Migration History Segregation (core.migrations, clinical.migrations)

To uphold domain boundary isolation and adhere strictly to PostgreSQL snake_case conventions, each DbContext explicitly configures its history table as migrations inside its own schema, completely eliminating the default __EFMigrationsHistory:

• GerciaDbContext (Core / IAM / Tenancy):

  options.UseNpgsql(dataSource, x => x.MigrationsHistoryTable("migrations", "core"))
         .ReplaceService<IMigrationsSqlGenerator, GerciaMigrationsSqlGenerator>()
         .AddInterceptors(rlsInterceptor)
         .UseSnakeCaseNamingConvention();

• GerciaClinicalDbContext (Healthcare / Clinical Vertical):

  options.UseNpgsql(dataSource, x => x.MigrationsHistoryTable("migrations", "clinical"))
         .ReplaceService<IMigrationsSqlGenerator, GerciaMigrationsSqlGenerator>()
         .AddInterceptors(rlsInterceptor)
         .UseSnakeCaseNamingConvention();

This prevents cross-context table pollution, ensures schema-level isolation, and guarantees that migrations in clinical never collide with core.

3. Linear Ticket-Driven Governance (add-migration.ps1)

The developer tooling script dotnet/scripts/add-migration.ps1 mandates a Linear ticket identifier for every migration:

.\dotnet\scripts\add-migration.ps1 -Ticket OPS-1 -Context Core
.\dotnet\scripts\add-migration.ps1 -Ticket CLI-1 -Context Clinical

• Traceability by Design: No developer or AI agent can generate an arbitrary database schema change without an authorized, documented issue in Linear.
• C# Identifier Normalization: Identifiers are sanitized automatically by removing hyphens and whitespace (e.g., OPS-1 → OPS1, ENG-10 → ENG10), producing clean, valid C# classes (public partial class OPS1 : Migration). Descriptive prose suffixes (e.g., _InitialCoreSchema) are strictly forbidden to guarantee direct 1:1 mapping between Linear tickets and the database history catalog.
• Subdirectory & Idempotent SQL Generation: Each migration is stored in its own dedicated timestamped directory (YYYYMMDDHHMMSS_OPS1/), housing the C# migration file, the designer file, and an idempotent SQL script (YYYYMMDDHHMMSS_OPS1.sql) formatted automatically via CSharpier.
• Local Developer Execution: To apply migrations locally, developers execute the exact same runner CLI used in production:

  dotnet run --project dotnet/src/Hosts/Gercia.Server/Gercia.Server.csproj -- --migrate

  The Server’s local profile (Properties/launchSettings.json) activates DOTNET_ENVIRONMENT=Development, targeting the local PostgreSQL container (localhost:5432) and sequentially applying all pending migrations across core.migrations and clinical.migrations.

Consequences

Positive

• Zero Startup Deadlocks: Server containers boot instantly without competing for database locks or failing healthchecks.
• Privilege Segregation: Long-running web applications are strictly constrained to DML operations.
• Deterministic Rollback Safety: Failed migrations halt the deployment pipeline before user-facing traffic is impacted.
• Audit Compliance: Regulators (ANVISA, CFM) can correlate every schema change directly from core.migrations or clinical.migrations to Linear tickets and Git commits.

Negative / Mitigations

• Pre-Deploy Execution Step: Deployment pipelines must orchestrate the ephemeral Cloud Run Job run before promoting service revisions, automated natively via Google Cloud Build.