Advanced

open DbUp
open DbUp.Engine
open DbUp.Engine.Transactions
open System.Reflection
 
// A script provider that generates scripts from an in-memory list
// => Useful for tests, dynamic migration generation, or remote script sources
type InMemoryScriptProvider(scripts: (string * string) list) =
    // => scripts is a list of (scriptName, sqlContent) tuples
    // => Tuple ordering matches DbUp's execution order when sorted by name
 
    interface IScriptProvider with
        member _.GetScripts(connectionManager) =
            // => connectionManager: IConnectionManager — provided by DbUp if the provider needs DB access
            // => Returning IEnumerable<SqlScript>; DbUp iterates this lazily during PerformUpgrade
            scripts
            // => Map each (name, content) pair to a SqlScript value object
            |> List.map (fun (name, content) ->
                // => SqlScript(name, content) — name is the unique identifier stored in the journal
                // => content is the raw SQL that DbUp executes verbatim
                SqlScript(name, content))
            // => Convert F# list to .NET IEnumerable<SqlScript>
            |> List.toSeq
 
// Instantiate with inline scripts — handy for integration tests
let provider =
    InMemoryScriptProvider([
        // => Script names must be unique and stable; changing a name causes a re-run
        "0001-create-products.sql", "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        "0002-seed-products.sql",   "INSERT INTO products (name) VALUES ('Widget'), ('Gadget');"
    ])
// => provider.GetScripts returns 2 SqlScript objects in list order
 
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        // => WithScripts accepts any IScriptProvider implementation
        .WithScripts(provider)
        .LogToConsole()
        .Build()
// => engine will execute both inline scripts on first run; skip on subsequent runs

open DbUp
open DbUp.Engine
open DbUp.Engine.Output
open DbUp.Engine.Transactions
open System
open System.Reflection
 
// Custom executor that logs timing around the default execution
// => Wraps DbUp's built-in PostgresqlScriptExecutor to add telemetry
type TimingScriptExecutor(inner: IScriptExecutor, logger: IUpgradeLog) =
    // => inner: the real executor supplied by the PostgreSQL provider
    // => logger: DbUp's structured logger; outputs to console, NLog, Serilog, etc.
 
    interface IScriptExecutor with
        member _.Execute(script, variables) =
            // => script: SqlScript — name + content to execute
            // => variables: IDictionary<string,string> — substitution variables from WithVariables
            let sw = System.Diagnostics.Stopwatch.StartNew()
            // => sw starts immediately; elapsed is available as sw.Elapsed after Stop
            logger.WriteInformation("Starting script {0}", [| script.Name |])
            // => WriteInformation writes to the configured log sink; {0} is positional placeholder
 
            try
                // => Delegate actual execution to the real PostgreSQL executor
                inner.Execute(script, variables)
                // => Returns unit; throws SqlException on script failure
            finally
                sw.Stop()
                // => sw.Elapsed is the wall-clock duration of the script
                logger.WriteInformation(
                    "Finished script {0} in {1}ms",
                    [| script.Name; sw.ElapsedMilliseconds |])
                // => Log line appears in console output alongside DbUp's normal messages
 
// Note: Wiring a custom IScriptExecutor requires subclassing the provider or using
// the lower-level UpgradeEngineBuilder API — the builder's .WithScriptExecutor() extension
// point accepts any IScriptExecutor implementation
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .LogToConsole()
        // => Build() returns UpgradeEngine; the builder exposes WithScriptExecutor internally
        .Build()
// => In practice: subclass UpgradeEngineBuilder and override CreateScriptExecutor
// => to return TimingScriptExecutor(base.CreateScriptExecutor(), logger)

open DbUp.Engine.Output
open DbUp.Engine.Transactions
open DbUp
 
// In-memory journal — records nothing to disk; every run re-executes all scripts
// => Primary use case: idempotent scripts in local dev where a clean DB is cheap
type AlwaysRunJournal() =
    // => Implements IJournal with no persistent storage
    // => Useful when scripts contain only CREATE OR REPLACE / IF NOT EXISTS DDL
 
    interface DbUp.Engine.IJournal with
        member _.GetExecutedScripts() =
            // => Returns the set of script names DbUp considers "already done"
            // => Returning empty array means every script is always "pending"
            [||]
            // => [||] : string[] — .NET empty array; zero allocation
 
        member _.StoreExecutedScript(script, dbCommandFactory) =
            // => Called after each successful script execution
            // => script: SqlScript — the just-executed script
            // => dbCommandFactory: Func<IDbCommand> — creates a DB command on the active connection
            // => No-op: we intentionally discard the record
            ()
            // => () is F#'s unit value; equivalent to void return
 
// Wire the custom journal into the engine
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        .WithScriptsEmbeddedInAssembly(System.Reflection.Assembly.GetExecutingAssembly())
        // => JournalTo accepts any IJournal implementation
        .JournalTo(AlwaysRunJournal())
        // => AlwaysRunJournal() is the custom journal instance
        .LogToConsole()
        .Build()
// => Every PerformUpgrade call executes ALL scripts regardless of prior runs
// => Appropriate only for idempotent scripts (CREATE TABLE IF NOT EXISTS, etc.)

-- File: 0064-add-status-column-phase1.sql
-- => Phase 1: add nullable column with no server default
-- => This is instantaneous on all PostgreSQL versions — no table rewrite
ALTER TABLE orders ADD COLUMN IF NOT EXISTS status TEXT;
-- => status column exists; all existing rows have status = NULL
-- => Application code must tolerate NULL status during the migration window
 
-- Add a default at the column level for new inserts
-- => DEFAULT 'pending' applies only to INSERT statements going forward
-- => Existing rows remain NULL until the backfill migration runs
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
-- => After this statement: new rows get status='pending'; old rows still NULL
 
-- Add the NOT NULL constraint as NOT VALID (skips existing rows)
-- => NOT VALID means PostgreSQL validates only new/updated rows, not the full table
-- => This is an instant metadata operation; no table scan
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
    CHECK (status IS NOT NULL) NOT VALID;
-- => Constraint exists but is marked invalid; existing NULLs are tolerated temporarily

-- File: 0065-remove-legacy-notes-phase1.sql
-- => Phase 1 (Release N): Deploy BEFORE any application change
-- => No SQL needed — this file documents the phase for audit trail
 
-- Phase 1 goal: ensure all application queries use SELECT <explicit columns>
-- instead of SELECT * so they do not depend on "notes" being present.
-- Deploy this empty migration to record that phase 1 is complete in the journal.
SELECT 1;
-- => SELECT 1 is a no-op DML statement; DbUp executes and journals it
-- => The journal entry marks phase 1 complete in every environment

-- File: 0065-remove-legacy-notes-phase2.sql
-- => Phase 2 (Release N+1): Drop the column after application code is clean
-- => All app instances must be on Release N before this migration runs
 
ALTER TABLE users DROP COLUMN IF EXISTS notes;
-- => DROP COLUMN IF EXISTS: safe if the column was already removed in a prior run
-- => IF EXISTS prevents errors in environments where the column was never added
-- => PostgreSQL marks the column as dropped in pg_attribute immediately
-- => The space is reclaimed during the next VACUUM FULL (not an immediate rewrite)

-- File: 0066-backfill-currency-batched.sql
-- => Backfill NULL currency values to 'USD' in batches of 10,000 rows
-- => Each DO block is a single transaction; psql auto-commits after the block
 
DO $$
DECLARE
    -- => DECLARE block defines PL/pgSQL variables
    v_batch_size INT := 10000;
    -- => v_batch_size: rows per batch; tune between 1,000 and 50,000 based on row width
    v_rows_updated INT := 1;
    -- => v_rows_updated: tracks rows affected in last batch; loop continues while > 0
    v_total INT := 0;
    -- => v_total: cumulative counter for logging purposes
BEGIN
    -- => LOOP ... END LOOP: PL/pgSQL infinite loop; EXIT WHEN breaks the loop
    LOOP
        UPDATE orders
        SET    currency = 'USD'
        WHERE  currency IS NULL
        -- => LIMIT in UPDATE requires a subquery in PostgreSQL
        AND    id IN (
                   SELECT id FROM orders
                   WHERE  currency IS NULL
                   -- => ORDER BY id ensures deterministic batch selection
                   ORDER  BY id
                   LIMIT  v_batch_size
               );
        -- => GET DIAGNOSTICS captures metadata about the last DML statement
        GET DIAGNOSTICS v_rows_updated = ROW_COUNT;
        -- => v_rows_updated is now the number of rows modified in this iteration
        v_total := v_total + v_rows_updated;
        RAISE NOTICE 'Backfilled % rows (total: %)', v_rows_updated, v_total;
        -- => RAISE NOTICE writes to the PostgreSQL log and psql console
        EXIT WHEN v_rows_updated = 0;
        -- => EXIT WHEN: break the loop when no more NULL rows remain
    END LOOP;
    RAISE NOTICE 'Backfill complete. Total rows updated: %', v_total;
END $$;
-- => After completion: all orders.currency values are 'USD' or a non-NULL value
-- => Each 10,000-row batch committed independently; WAL lag stays bounded

-- File: 0067-create-index-concurrently.sql
-- => CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction block
-- => DbUp's WithTransactionPerScript wraps each script; use WithoutTransaction for this file
 
-- Disable the transaction for this script using DbUp's script annotation
-- => DbUp recognizes the comment prefix "-- DbUp:NoTransaction" and skips the transaction wrapper
-- DbUp:NoTransaction
 
-- Create a non-blocking index on orders.customer_id
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
    ON orders (customer_id);
-- => CONCURRENTLY: two-phase build; reads existing rows twice; allows concurrent writes
-- => IF NOT EXISTS: idempotent; safe to re-run if deployment fails mid-way
-- => Estimated time: ~1s per million rows on typical hardware
-- => Lock acquired: ShareUpdateExclusiveLock — blocks only DDL, not DML
 
-- Create a partial index for active orders only (reduces index size)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_active_status
    ON orders (created_at DESC)
    WHERE status = 'active';
-- => Partial index: only rows where status='active' are indexed
-- => Smaller index = faster maintenance; fits common query pattern WHERE status='active'
-- => If the query planner ignores this index, check that the WHERE predicate matches exactly

-- File: 0068-backfill-full-name.sql
-- => Derives full_name from first_name + last_name for all existing users
-- => Assumes 0064-style column was added as nullable in a prior migration
 
-- Step 1: Verify the transformation before mutating data
-- => Run this SELECT in a transaction you will ROLLBACK to preview results
SELECT
    id,
    first_name,
    last_name,
    -- => TRIM removes leading/trailing whitespace from concatenation
    -- => NULLIF converts empty string to NULL to avoid trailing spaces
    TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')) AS computed_full_name
FROM   users
WHERE  full_name IS NULL
LIMIT  10;
-- => Review the first 10 rows before committing the full backfill
-- => COALESCE: replaces NULL with empty string to prevent "NULL Smith" results
 
-- Step 2: Execute the backfill
UPDATE users
SET    full_name = TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))
WHERE  full_name IS NULL;
-- => WHERE full_name IS NULL: idempotent; safe to re-run
-- => Only rows with no full_name are affected; already-backfilled rows are untouched
 
-- Step 3: Validate the result before adding NOT NULL
DO $$
BEGIN
    -- => Raise an error if any NULL remains, aborting the migration
    IF EXISTS (SELECT 1 FROM users WHERE full_name IS NULL OR full_name = '') THEN
        RAISE EXCEPTION 'Backfill incomplete: NULL or empty full_name found';
        -- => RAISE EXCEPTION: rolls back the transaction and marks DbUp script as failed
    END IF;
    RAISE NOTICE 'Backfill validated: all full_name values populated';
END $$;
-- => If the EXCEPTION fires, DbUp records a failure and stops further scripts
 
-- Step 4: Make the column non-nullable now that all rows are populated
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- => Safe because the validation block above confirmed no NULLs remain

open DbUp
open System
open System.Reflection
 
// Entry point for the migration console application
// => argv: string[] from command-line arguments; "--dry-run" flag controls behaviour
[<EntryPoint>]
let main argv =
    let connString =
        // => Fail fast if DATABASE_URL is missing; null dereference later is harder to debug
        match Environment.GetEnvironmentVariable("DATABASE_URL") with
        | null | "" -> failwith "DATABASE_URL environment variable is required"
        // => failwith raises System.Exception and terminates with exit code 1 via unhandled exception
        | s -> s
        // => s: the connection string value from the environment
 
    let isDryRun =
        // => Array.exists: returns true if any element satisfies the predicate
        argv |> Array.exists (fun a -> a = "--dry-run")
        // => isDryRun is true when the pipeline passes "--dry-run" as an argument
 
    let engine =
        DeployChanges.To
            .PostgresqlDatabase(connString)
            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
            .LogToConsole()
            .Build()
    // => engine.PerformUpgrade() executes pending scripts and returns DatabaseUpgradeResult
 
    if isDryRun then
        // => Dry run: log which scripts would execute without applying them
        printfn "=== DRY RUN: scripts that would be applied ==="
        for script in engine.GetScriptsToExecute() do
            // => GetScriptsToExecute() returns IEnumerable<SqlScript> of pending scripts
            printfn "  - %s" script.Name
            // => Outputs each pending script name; useful for PR review in CI output
        0
        // => Exit code 0: dry run succeeded; pipeline continues
    else
        let result = engine.PerformUpgrade()
        // => result.Successful: bool — true if all pending scripts executed without error
        // => result.Error: Exception option — non-null if a script failed
        if result.Successful then
            printfn "Migration completed successfully."
            0
            // => Exit code 0: CI/CD job passes; deployment pipeline continues
        else
            eprintfn "Migration failed: %s" result.Error.Message
            // => eprintfn writes to stderr; CI systems capture stderr separately for alerts
            1
            // => Exit code 1: CI/CD job fails; pipeline stops; deployment is blocked

open DbUp
open DbUp.Engine.Output
open DbUp.Helpers
open System.Reflection
 
// Capture all output into a StringBuilder for inspection or assertion
// => Useful in unit tests where you want to assert which scripts would run
let sb = System.Text.StringBuilder()
// => sb accumulates all log messages written during the dry run
 
// Create a logger that writes to the StringBuilder
let logger =
    // => UpgradeLog.PrintCallbackLog accepts a formatting Action<string, obj[]>
    // => Every IUpgradeLog.WriteInformation / WriteWarning / WriteError call routes here
    { new IUpgradeLog with
        member _.WriteVerbose(format, args) = sb.Append(String.Format(format, args)) |> ignore
        // => WriteVerbose: low-level detail; DbUp uses this for SQL statement echoing
        member _.WriteInformation(format, args) = sb.AppendLine(String.Format(format, args)) |> ignore
        // => WriteInformation: normal progress messages; script names, completion notices
        member _.WriteWarning(format, args) = sb.AppendLine("[WARN] " + String.Format(format, args)) |> ignore
        // => WriteWarning: non-fatal issues; used sparingly by DbUp internals
        member _.WriteError(format, args) = sb.AppendLine("[ERROR] " + String.Format(format, args)) |> ignore
        // => WriteError: script execution failures and connection errors
    }
 
// NullJournal: treats every script as "not yet run" — all scripts are always pending
// => Combined with LogScriptOutput this produces a full preview without mutating the database
let engine =
    DeployChanges.To
        .PostgresqlDatabase("Host=localhost;Database=preview;Username=preview;Password=preview")
        // => Connection string is unused in this pattern; NullJournal prevents DB queries
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        // => NullJournal: returns empty executed list; records nothing; all scripts appear pending
        .JournalTo(NullJournal())
        .LogTo(logger)
        // => LogTo(logger): routes all IUpgradeLog calls to our StringBuilder logger
        .LogScriptOutput()
        // => LogScriptOutput: echoes each SQL statement to the logger before execution
        .Build()
// => engine is configured for preview; PerformUpgrade would attempt real DB calls
// => In a true no-DB preview, combine with a fake IConnectionManager that throws on connect
 
let scripts = engine.GetScriptsToExecute()
// => GetScriptsToExecute() queries the journal (NullJournal returns empty) and returns all scripts
for s in scripts do
    printfn "Would execute: %s" s.Name
    // => Outputs each pending script name to stdout for CI review
 
printfn "%s" (sb.ToString())
// => sb.ToString() contains all log output accumulated during GetScriptsToExecute

open DbUp
open System.Reflection
open System.Threading.Tasks
 
// List of connection strings — one per database shard or environment
// => In production, load these from a secret manager or environment variable list
let connectionStrings =
    [|
        System.Environment.GetEnvironmentVariable("DB_SHARD_1")
        // => Shard 1 connection string; must be non-null
        System.Environment.GetEnvironmentVariable("DB_SHARD_2")
        // => Shard 2 connection string; must be non-null
        System.Environment.GetEnvironmentVariable("DB_SHARD_3")
        // => Shard 3 connection string; must be non-null
    |]
    // => Array of connection strings; null elements will cause NullReferenceException — validate first
 
// Migrate each database; collect results for aggregate error reporting
let results =
    connectionStrings
    // => Array.Parallel.map runs the function on all elements concurrently via the .NET thread pool
    |> Array.Parallel.map (fun connStr ->
        // => Each invocation creates an independent DbUp engine with its own journal
        let engine =
            DeployChanges.To
                .PostgresqlDatabase(connStr)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                // => Each shard has its own schemaversions table; journals are independent
                .LogToConsole()
                .Build()
        // => Returns DatabaseUpgradeResult containing Successful flag and optional Error
        let result = engine.PerformUpgrade()
        connStr, result
        // => Tuple: (connection string, result) — used for error reporting below
    )
// => results: (string * DatabaseUpgradeResult)[] — one entry per shard
 
// Report failures across all shards
let failures =
    results
    |> Array.filter (fun (_, r) -> not r.Successful)
    // => filter keeps only (connStr, result) pairs where Successful = false
 
if failures.Length > 0 then
    for connStr, result in failures do
        eprintfn "Migration failed for %s: %s" connStr result.Error.Message
        // => eprintfn: writes to stderr; visible in CI failure logs
    failwithf "%d shard(s) failed migration" failures.Length
    // => failwithf: raises exception with formatted message; exits with code 1 in Main
else
    printfn "All %d shards migrated successfully." connectionStrings.Length
    // => All shards healthy; deployment may continue

open Npgsql
open System
 
// Query the actual schema state from PostgreSQL information_schema
// => Returns a set of (table_name, column_name, data_type) tuples
let getActualSchema (connStr: string) =
    use conn = new NpgsqlConnection(connStr)
    // => use: F# resource disposal; conn.Dispose() called when binding goes out of scope
    conn.Open()
    // => Opens TCP connection to PostgreSQL; throws on unreachable host or bad credentials
 
    use cmd = conn.CreateCommand()
    // => cmd: NpgsqlCommand bound to the open connection
    cmd.CommandText <- """
        SELECT table_name, column_name, data_type
        FROM   information_schema.columns
        WHERE  table_schema = 'public'
        -- => Filter to the public schema; adjust if your app uses a custom schema
        ORDER  BY table_name, ordinal_position
        -- => ordinal_position: column order as defined in CREATE TABLE
        """
    // => Parameterised query would use cmd.Parameters; no parameters needed here
 
    use reader = cmd.ExecuteReader()
    // => reader: forward-only, read-only cursor over the result set
    [
        while reader.Read() do
            // => reader.Read() advances the cursor; returns false when no more rows
            yield reader.GetString(0), reader.GetString(1), reader.GetString(2)
            // => yield: F# list comprehension; collects each (table, column, type) tuple
    ]
    // => Returns string * string * string list — all columns in public schema
 
// Canonical expected schema defined in code — single source of truth
// => This list is maintained alongside migrations; update when adding columns
let expectedSchema =
    [
        "orders",   "id",          "integer"
        "orders",   "customer_id", "integer"
        "orders",   "total",       "numeric"
        "orders",   "status",      "text"
        "products", "id",          "integer"
        "products", "name",        "text"
        "products", "price",       "numeric"
    ]
    // => List of (table_name, column_name, data_type) tuples matching the migration output
 
let detectDrift (connStr: string) =
    let actual   = getActualSchema connStr |> Set.ofList
    // => Set.ofList: converts list to Set for O(log n) membership tests
    let expected = expectedSchema |> Set.ofList
    // => expected: same structure; Set operations give symmetric difference
 
    let missing = Set.difference expected actual
    // => missing: columns in expected but not in actual DB — schema is behind
    let extra   = Set.difference actual expected
    // => extra: columns in actual DB but not expected — manual hotfix or forgotten migration
 
    if missing.IsEmpty && extra.IsEmpty then
        printfn "Schema matches expected state — no drift detected."
        // => All good; deployment can proceed with confidence
    else
        if not missing.IsEmpty then
            printfn "MISSING columns (migrations needed):"
            for t, c, dt in missing do
                printfn "  %s.%s (%s)" t c dt
        if not extra.IsEmpty then
            printfn "EXTRA columns (drift detected):"
            for t, c, dt in extra do
                printfn "  %s.%s (%s)" t c dt
        // => Drift detected; raise exception to block CI pipeline
        failwith "Schema drift detected — review before deploying"

open DbUp
open System.Reflection
open Testcontainers.PostgreSql
 
// Test: verify that a compensating migration reverts the schema change
// => Uses Testcontainers to spin up a real PostgreSQL instance per test
let testRollbackCompensation () =
    task {
        // => task {} : F# computation expression for System.Threading.Tasks.Task
        use postgres =
            PostgreSqlBuilder()
                // => PostgreSqlBuilder: Testcontainers builder for PostgreSQL containers
                .WithImage("postgres:16-alpine")
                // => postgres:16-alpine: minimal production-grade PostgreSQL image
                .WithDatabase("rollback_test")
                .WithUsername("test")
                .WithPassword("test")
                .Build()
        // => postgres: IContainer instance; not yet running
 
        do! postgres.StartAsync()
        // => do! : awaits the Task; container is fully started and accepting connections after this
 
        let connStr = postgres.GetConnectionString()
        // => connStr: "Host=localhost;Port=<mapped>;Database=rollback_test;Username=test;Password=test"
 
        // Step 1: Apply the forward migration
        let forwardEngine =
            DeployChanges.To
                .PostgresqlDatabase(connStr)
                .WithScriptsFromFileSystem("./migrations/forward")
                // => Loads scripts from the forward migrations directory
                .LogToConsole()
                .Build()
        let forwardResult = forwardEngine.PerformUpgrade()
        // => forwardResult.Successful: true if all forward scripts ran without error
        assert forwardResult.Successful
 
        // Step 2: Apply the compensating (rollback) migration
        let rollbackEngine =
            DeployChanges.To
                .PostgresqlDatabase(connStr)
                .WithScriptsFromFileSystem("./migrations/rollback")
                // => Compensating scripts live in a separate directory; applied manually on rollback
                .JournalToPostgresqlTable("public", "rollback_schemaversions")
                // => Separate journal prevents compensating scripts from colliding with forward journal
                .LogToConsole()
                .Build()
        let rollbackResult = rollbackEngine.PerformUpgrade()
        // => rollbackResult.Successful: true if the compensating migration succeeded
 
        assert rollbackResult.Successful
        printfn "Rollback test passed: compensating migration applied cleanly."
        // => Test passes when both assertions succeed
 
        do! postgres.StopAsync()
        // => do! : awaits container shutdown; releases Docker resources
    }
    |> Async.AwaitTask
    |> Async.RunSynchronously
// => Async.RunSynchronously: blocks the calling thread until the task completes

-- File: 0074-blue-green-compatible-rename.sql
-- => Strategy: add new column, keep old column, migrate data, deprecate old
-- => Both blue (old app) and green (new app) can operate during the cutover window
 
-- Phase A: Add the new column alongside the old (blue sees old_price; green sees new_price)
ALTER TABLE products ADD COLUMN IF NOT EXISTS unit_price NUMERIC(12,4);
-- => unit_price: new canonical column used by the green application
-- => IF NOT EXISTS: idempotent; safe to re-run if deployment is retried
 
-- Phase B: Populate unit_price from price for existing rows
UPDATE products
SET    unit_price = price
WHERE  unit_price IS NULL;
-- => WHERE unit_price IS NULL: idempotent; does not overwrite rows already set by green app
 
-- Phase C: Create a trigger to keep both columns in sync during the cutover window
-- => Trigger ensures blue app writes to "price" are propagated to "unit_price" automatically
CREATE OR REPLACE FUNCTION sync_price_columns() RETURNS TRIGGER AS $$
BEGIN
    -- => NEW: the row being inserted or updated (PL/pgSQL special variable)
    IF NEW.price IS DISTINCT FROM OLD.price THEN
        -- => IS DISTINCT FROM: NULL-safe comparison; true when values differ including NULL
        NEW.unit_price := NEW.price;
        -- => Keep unit_price in sync when blue app updates price
    END IF;
    IF NEW.unit_price IS DISTINCT FROM OLD.unit_price THEN
        NEW.price := NEW.unit_price;
        -- => Keep price in sync when green app updates unit_price
    END IF;
    RETURN NEW;
    -- => RETURN NEW: required for BEFORE triggers; returns the (possibly modified) row
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_price_columns
    BEFORE UPDATE ON products
    -- => BEFORE: trigger fires before the row is written; allows modification of NEW
    FOR EACH ROW EXECUTE FUNCTION sync_price_columns();
-- => Trigger is active during the blue-green overlap window only
-- => Remove trigger and old "price" column in the next migration after green is fully live

open DbUp
open System
open System.Reflection
 
// Determine which feature migrations to include based on active feature flags
// => Feature flags are read from environment variables; in production use LaunchDarkly/Unleash
let activeFeatures =
    (Environment.GetEnvironmentVariable("ACTIVE_FEATURES") ?? "")
    // => ?? is F#'s null coalescing operator via the Operators module; returns "" if null
    .Split(',', StringSplitOptions.RemoveEmptyEntries)
    // => Split: converts "feature-a,feature-b" to [| "feature-a"; "feature-b" |]
    |> Set.ofArray
    // => Set.ofArray: O(n log n) conversion; Set.contains is O(log n)
 
// Custom script filter that includes core migrations plus feature-flagged ones
// => Scripts named "feature-<flag>-*.sql" are only included when the flag is active
let featureFilter (scripts: DbUp.Engine.SqlScript seq) =
    scripts
    |> Seq.filter (fun s ->
        let name = s.Name
        // => name: e.g. "MyApp.migrations.feature-recommendations-001-create-table.sql"
        if name.Contains("/feature-") then
            // => Extract the feature flag name from the script file name
            let featureName =
                name.Split('/') |> Array.last
                // => Array.last: the filename segment, e.g. "feature-recommendations-001-..."
                |> fun n -> n.Split('-').[1]
                // => Split on '-', take index 1: the flag name after "feature-"
            Set.contains featureName activeFeatures
            // => Include script only if the flag is in the active set
        else
            true
            // => Core migrations (no "/feature-" in name) always included
    )
 
let engine =
    DeployChanges.To
        .PostgresqlDatabase(Environment.GetEnvironmentVariable("DATABASE_URL"))
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        // => Apply the feature flag filter before the journal filter
        .WithFilter({ new DbUp.Engine.IScriptFilter with
            member _.Filter(scripts, journal, logger) = featureFilter scripts })
        .LogToConsole()
        .Build()
// => Only core migrations and migrations for active features will be executed

open DbUp
open Microsoft.EntityFrameworkCore
open System
open System.Reflection
 
// Step 1: Run DbUp structural migrations before EF Core starts
// => DbUp handles CREATE TABLE, ALTER TABLE, CREATE INDEX
// => EF Core never calls Database.Migrate(); it assumes schema is already current
let runStructuralMigrations (connStr: string) =
    let engine =
        DeployChanges.To
            .PostgresqlDatabase(connStr)
            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
            // => Embedded scripts contain all DDL; EF Core's __EFMigrationsHistory table is unused
            .LogToConsole()
            .Build()
    let result = engine.PerformUpgrade()
    // => result.Successful: bool — true when all pending DDL scripts succeed
    if not result.Successful then
        failwithf "Structural migration failed: %s" result.Error.Message
        // => Terminate immediately; EF Core must not start against a partially migrated schema
 
// Step 2: Configure EF Core to use the DbUp-managed schema
type AppDbContext(options: DbContextOptions<AppDbContext>) =
    inherit DbContext(options)
    // => inherit: F# class inheritance; AppDbContext extends DbContext
 
    // => DbSet<'T> maps to a PostgreSQL table managed by DbUp scripts
    member val Orders = base.Set<Order>() with get
    // => base.Set<Order>(): EF Core registers the Order entity without calling Migrate()
 
    override _.OnModelCreating(builder) =
        // => OnModelCreating: called once at startup to configure the EF Core model
        builder.Entity<Order>()
            // => Entity<Order>(): fluent API entry point for Order configuration
            .ToTable("orders")
            // => ToTable: maps Order type to the "orders" table created by DbUp script 001
            |> ignore
        // => |> ignore: discard the EntityTypeBuilder return value; F# requires unit in overrides
 
// Step 3: Bootstrap sequence in Program.fs
let bootstrap () =
    let connStr = Environment.GetEnvironmentVariable("DATABASE_URL")
    runStructuralMigrations connStr
    // => DbUp migrations run first; schema is guaranteed current before EF Core initialises
    let options =
        DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql(connStr)
            // => UseNpgsql: configures EF Core to use the Npgsql PostgreSQL driver
            .Options
    use ctx = new AppDbContext(options)
    // => ctx: EF Core DbContext; never calls ctx.Database.Migrate()
    // => Schema is already current thanks to DbUp running above
    ctx

open DbUp
open System
open System.Reflection
 
// List of tenant schema names — in production, load from a database or config service
let tenantSchemas =
    [| "tenant_acme"; "tenant_globex"; "tenant_initech" |]
    // => Each element is a PostgreSQL schema name; must exist before migration runs
    // => Convention: "tenant_<slug>" where slug is the company identifier
 
// Migrate each tenant schema independently
let migrateAllTenants (connStr: string) =
    let failures = System.Collections.Generic.List<string>()
    // => List<string>: mutable .NET list; accumulates failed schema names
 
    for schema in tenantSchemas do
        // => for..in: F# imperative loop; processes each tenant sequentially
        printfn "Migrating schema: %s" schema
 
        let engine =
            DeployChanges.To
                .PostgresqlDatabase(connStr)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                // => WithVariables: substitutes $schema$ placeholder in SQL scripts
                // => Scripts use SET search_path TO $schema$; or reference $schema$.tablename
                .WithVariables(dict [ "schema", schema ])
                // => dict [ key, value ]: F# shorthand for Dictionary<string,string>
                // => "schema" variable maps to the current tenant's schema name
                .JournalToPostgresqlTable(schema, "schemaversions")
                // => Each tenant schema has its own journal table; journals are independent
                .LogToConsole()
                .Build()
 
        let result = engine.PerformUpgrade()
        if not result.Successful then
            // => Collect failures; continue migrating other tenants
            failures.Add($"{schema}: {result.Error.Message}")
            // => $"...": F# interpolated string; includes schema name and error message
 
    if failures.Count > 0 then
        eprintfn "Migration failures:\n%s" (String.Join("\n", failures))
        // => String.Join: concatenates failure messages with newline separator
        failwith "One or more tenant migrations failed"
        // => Raise exception; application startup fails; no tenant served with broken schema
    else
        printfn "All %d tenant schemas migrated successfully." tenantSchemas.Length

-- File: 0078-enable-pgcrypto.sql
-- => Run this script with superuser credentials (separate from application credentials)
-- => pgcrypto provides gen_random_bytes, crypt, gen_random_uuid, pgp_sym_encrypt, etc.
 
-- Enable the pgcrypto extension if not already present
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- => IF NOT EXISTS: idempotent; no error if extension is already installed
-- => Extension is installed into the public schema by default
-- => After this statement: pgcrypto functions are available database-wide
 
-- Create a table that stores hashed passwords using pgcrypto
CREATE TABLE IF NOT EXISTS credentials (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => gen_random_uuid(): generates a v4 UUID using cryptographically secure random bytes
    -- => Provided by pgcrypto; faster than uuid-ossp's uuid_generate_v4()
    user_id       UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- => ON DELETE CASCADE: deleting a user automatically removes their credentials row
    password_hash TEXT        NOT NULL,
    -- => Stores the bcrypt hash; never the plaintext password
    -- => bcrypt hash format: $2b$<cost>$<22-char-salt><31-char-hash>
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Example: insert a hashed password (for migration seed data or tests)
-- => crypt(password, gen_salt('bf', 12)): bcrypt with cost factor 12
-- => Cost 12: ~300ms per hash on modern hardware; adjust per OWASP guidance
INSERT INTO credentials (user_id, password_hash)
SELECT id, crypt('initial-admin-password', gen_salt('bf', 12))
FROM   users
WHERE  email = 'admin@example.com'
ON CONFLICT DO NOTHING;
-- => ON CONFLICT DO NOTHING: idempotent; does not re-hash if the row already exists
-- => In production: replace 'initial-admin-password' with a vault-fetched secret

-- File: 0079-create-audit-trail.sql
-- => Audit trail captures all INSERT, UPDATE, DELETE on the "orders" table
-- => Schema-level auditing requires no application code changes
 
CREATE TABLE IF NOT EXISTS audit_log (
    id          BIGSERIAL   PRIMARY KEY,
    -- => BIGSERIAL: auto-incrementing 8-byte integer; supports ~9.2 quintillion events
    table_name  TEXT        NOT NULL,
    -- => Stores the name of the table being audited; allows one audit_log for all tables
    operation   TEXT        NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    -- => CHECK constraint: enforces only valid DML operation names
    row_id      TEXT        NOT NULL,
    -- => TEXT: stores the primary key as text; handles UUID, integer, and composite keys
    old_data    JSONB,
    -- => old_data: row state before the mutation; NULL for INSERT operations
    new_data    JSONB,
    -- => new_data: row state after the mutation; NULL for DELETE operations
    changed_by  TEXT        NOT NULL DEFAULT current_user,
    -- => current_user: PostgreSQL session user; captures the DB role, not the app user
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
    -- => changed_at: server-side timestamp; immune to client clock skew
);
 
-- Index to support queries like "show me all changes to order 42"
CREATE INDEX IF NOT EXISTS idx_audit_log_row
    ON audit_log (table_name, row_id, changed_at DESC);
-- => (table_name, row_id, changed_at DESC): supports the common "history for entity X" query
-- => DESC on changed_at: most recent entries retrieved first without a sort step
 
-- Trigger function: captures OLD and NEW row data as JSONB
CREATE OR REPLACE FUNCTION audit_orders() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        -- => TG_TABLE_NAME: PL/pgSQL special variable; the name of the triggering table
        TG_OP,
        -- => TG_OP: 'INSERT', 'UPDATE', or 'DELETE' — set by PostgreSQL trigger engine
        COALESCE(NEW.id::TEXT, OLD.id::TEXT),
        -- => COALESCE: uses NEW.id for INSERT/UPDATE, OLD.id for DELETE (NEW is NULL on DELETE)
        CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
        -- => row_to_json(OLD)::JSONB: serialises the pre-mutation row to JSONB
        CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
        -- => row_to_json(NEW)::JSONB: serialises the post-mutation row to JSONB
    );
    RETURN COALESCE(NEW, OLD);
    -- => AFTER triggers must return NEW (or OLD for DELETE) to allow the DML to proceed
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_orders();
-- => AFTER: trigger fires after the DML succeeds; audit entry is within the same transaction
-- => FOR EACH ROW: one trigger invocation per modified row

-- File: 0080-add-soft-delete-pattern.sql
-- => Implements soft delete on the "products" table
-- => All existing queries see only non-deleted rows via the view
 
-- Step 1: Add the deleted_at column
ALTER TABLE products ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
-- => NULL: product is active; non-NULL: product was deleted at the stored timestamp
-- => IF NOT EXISTS: idempotent; safe to re-run on already-migrated databases
 
-- Step 2: Create a partial index for active-records-only queries
CREATE INDEX IF NOT EXISTS idx_products_active
    ON products (id)
    WHERE deleted_at IS NULL;
-- => Partial index covers only rows where deleted_at IS NULL
-- => Queries with WHERE deleted_at IS NULL use this smaller, faster index
-- => Rows with non-NULL deleted_at are excluded from the index, reducing its size
 
-- Step 3: Create a view exposing only active products
CREATE OR REPLACE VIEW active_products AS
    SELECT * FROM products
    WHERE  deleted_at IS NULL;
-- => Application queries use active_products instead of products directly
-- => The view is automatically updated when rows are soft-deleted
 
-- Step 4: Create a function for soft-deleting a product
CREATE OR REPLACE FUNCTION soft_delete_product(p_id UUID) RETURNS VOID AS $$
BEGIN
    UPDATE products
    SET    deleted_at = NOW()
    -- => NOW(): returns current transaction timestamp; consistent within a transaction
    WHERE  id = p_id
    AND    deleted_at IS NULL;
    -- => AND deleted_at IS NULL: idempotent; re-deleting an already-deleted product is a no-op
    IF NOT FOUND THEN
        RAISE WARNING 'soft_delete_product: product % not found or already deleted', p_id;
        -- => RAISE WARNING: non-fatal; logs to PostgreSQL error log; does not rollback
    END IF;
END;
$$ LANGUAGE plpgsql;
-- => Call: SELECT soft_delete_product('some-uuid') — application code never sets deleted_at directly

open DbUp
open DbUp.Engine
open System.Text.RegularExpressions
open System.Reflection
 
// Preprocessor that strips SQL line comments (-- comment) before execution
// => Useful when migrating scripts from a system where comments cause parsing issues
type StripCommentsPreprocessor() =
    // => Implements IScriptPreprocessor; single method: Process(sql: string) -> string
 
    interface IScriptPreprocessor with
        member _.Process(contents) =
            // => contents: the raw SQL file content as a string
            // => Returns the transformed SQL that DbUp will execute
            Regex.Replace(
                contents,
                // => Pattern: -- followed by any characters to end of line
                @"--[^\r\n]*",
                // => [^\r\n]*: matches any character except carriage return and newline
                "",
                // => Replace with empty string: removes the comment and its text
                RegexOptions.Multiline
                // => Multiline: ^ and $ match start/end of each line (not relevant here, but good practice)
            )
            // => Returns the SQL with all -- style comments stripped
            // => Note: does not strip /* block comments */ — extend the regex if needed
 
// Preprocessor that prepends SET search_path to every script
// => Ensures all scripts operate in the correct PostgreSQL schema
type SearchPathPreprocessor(schema: string) =
    interface IScriptPreprocessor with
        member _.Process(contents) =
            // => Prepend SET search_path to the script content
            $"SET search_path TO {schema}, public;\n{contents}"
            // => SET search_path TO {schema}, public: makes unqualified table names resolve to {schema} first
            // => Followed by newline and original script content
            // => Each script now executes in the correct schema without modifying the SQL files
 
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        // => WithPreprocessor: chain multiple preprocessors; applied in order
        .WithPreprocessor(SearchPathPreprocessor("app_schema"))
        // => SearchPathPreprocessor runs first: prepends SET search_path
        .WithPreprocessor(StripCommentsPreprocessor())
        // => StripCommentsPreprocessor runs second: removes comments from the (already-modified) SQL
        .LogToConsole()
        .Build()
// => engine applies both preprocessors to every script before execution

open DbUp
open DbUp.Engine.Output
open System
open System.Collections.Generic
open System.Reflection
 
// Timing logger that records per-script durations
// => Wraps a real IUpgradeLog to intercept WriteInformation calls
type TimingLogger() =
    // => Mutable dictionary accumulates script timing data
    let timings = Dictionary<string, TimeSpan>()
    // => Dictionary<string, TimeSpan>: maps script name to elapsed duration
    let mutable currentScript = ""
    // => currentScript: tracks the script currently being measured
    let mutable scriptStart = DateTime.UtcNow
    // => scriptStart: wall-clock time when the current script began
 
    member _.StartScript(name: string) =
        currentScript <- name
        // => <- : F# mutable assignment operator
        scriptStart   <- DateTime.UtcNow
        // => DateTime.UtcNow: UTC timestamp; immune to DST transitions
        printfn "[benchmark] Starting: %s" name
 
    member _.EndScript() =
        let elapsed = DateTime.UtcNow - scriptStart
        // => TimeSpan subtraction: elapsed wall-clock time for this script
        timings.[currentScript] <- elapsed
        // => Store elapsed time keyed by script name
        printfn "[benchmark] Completed: %s in %dms" currentScript (int elapsed.TotalMilliseconds)
 
    member _.Timings = timings :> IReadOnlyDictionary<string, TimeSpan>
    // => :> : F# upcast operator; exposes timings as read-only to callers
 
    member _.PrintSummary() =
        printfn "\n=== Migration Performance Summary ==="
        for kvp in timings |> Seq.sortByDescending (fun kv -> kv.Value) do
            // => sortByDescending: slowest scripts first; draws attention to outliers
            printfn "  %-50s %6dms" kvp.Key (int kvp.Value.TotalMilliseconds)
        let total = timings.Values |> Seq.sumBy (fun t -> t.TotalMilliseconds)
        printfn "  %-50s %6dms" "TOTAL" (int total)
 
    // Enforce a per-script SLA — fail if any script exceeds the threshold
    member _.EnforceSla(maxMs: int) =
        let violations =
            timings
            |> Seq.filter (fun kv -> kv.Value.TotalMilliseconds > float maxMs)
            // => Filter to scripts that exceeded the SLA threshold
            |> Seq.toList
        if violations.Length > 0 then
            for kv in violations do
                eprintfn "SLA VIOLATION: %s took %dms (limit: %dms)"
                    kv.Key (int kv.Value.TotalMilliseconds) maxMs
            failwithf "%d script(s) exceeded %dms SLA" violations.Length maxMs
            // => Fail deployment if any script is too slow for the deployment window

open Npgsql
open System
 
// Detect drift by comparing pg_class statistics to expected table list
// => A lightweight check that does not require storing full schema snapshots
let detectMissingTables (connStr: string) (expectedTables: string list) =
    use conn = new NpgsqlConnection(connStr)
    conn.Open()
    // => Opens connection; throws NpgsqlException if unreachable
 
    use cmd = conn.CreateCommand()
    cmd.CommandText <- """
        SELECT table_name
        FROM   information_schema.tables
        WHERE  table_schema = 'public'
        AND    table_type   = 'BASE TABLE'
        -- => BASE TABLE: excludes views, foreign tables, and partitioned table parents
        ORDER  BY table_name
        """
    // => Queries the information_schema; available without superuser privileges
 
    use reader = cmd.ExecuteReader()
    let actualTables =
        [ while reader.Read() do yield reader.GetString(0) ]
        |> Set.ofList
    // => actualTables: Set of table names currently in the public schema
 
    let expected = expectedTables |> Set.ofList
    // => expected: Set of table names that should exist after all migrations
 
    let missing = Set.difference expected actualTables
    // => missing: tables expected but not present — migration may not have run
    let extra   = Set.difference actualTables expected
    // => extra: tables present but not expected — manual creation or leftover from rollback
 
    {|
        // => Anonymous record: F# shorthand for a one-off data structure
        Missing = missing
        Extra   = extra
        HasDrift = not missing.IsEmpty || not extra.IsEmpty
    |}
 
// Run drift detection before migrations
let preflightCheck (connStr: string) =
    let knownTables = [ "users"; "orders"; "products"; "audit_log"; "credentials" ]
    // => Maintain this list alongside migrations; update when adding or dropping tables
 
    let drift = detectMissingTables connStr knownTables
    if drift.HasDrift then
        if not drift.Missing.IsEmpty then
            eprintfn "DRIFT: Missing tables: %s" (String.Join(", ", drift.Missing))
        if not drift.Extra.IsEmpty then
            eprintfn "DRIFT: Extra tables: %s" (String.Join(", ", drift.Extra))
        // => Option A: fail hard — block migration; require manual investigation
        failwith "Schema drift detected — resolve before running migrations"
        // => Option B: warn and continue — suitable when extra tables are expected (e.g., legacy tables)
    else
        printfn "Preflight check passed: schema matches expected state."

open System
 
// Checklist item: a single pre/during/post migration concern
// => Encodes the check as a named function returning (passed: bool, message: string)
type ChecklistItem =
    {
        Name    : string
        // => Human-readable name for the checklist item; appears in deployment output
        Category: string
        // => "pre", "during", or "post" — controls when the check is run
        Check   : unit -> bool * string
        // => Returns (passed, message); message explains why the check passed or failed
    }
 
// Build the production checklist
let productionChecklist (connStr: string) =
    [
        {
            Name     = "DATABASE_URL is set"
            Category = "pre"
            Check    = fun () ->
                let v = Environment.GetEnvironmentVariable("DATABASE_URL")
                not (String.IsNullOrEmpty v), "DATABASE_URL must be a non-empty connection string"
                // => Returns (true, message) when set; (false, message) when missing
        }
        {
            Name     = "Database is reachable"
            Category = "pre"
            Check    = fun () ->
                try
                    use conn = new Npgsql.NpgsqlConnection(connStr)
                    conn.Open()
                    // => Opens and immediately closes to verify connectivity
                    true, "Database connection successful"
                with ex ->
                    false, $"Cannot connect to database: {ex.Message}"
                    // => Catches NpgsqlException or SocketException; returns descriptive message
        }
        {
            Name     = "Backup timestamp is recent (< 1 hour)"
            Category = "pre"
            Check    = fun () ->
                // => In practice: query your backup system's API or a backup_status table
                // => This stub always passes; replace with real backup verification
                true, "Backup check: MANUAL VERIFICATION REQUIRED — check backup timestamp"
                // => Return false to block migration if last backup is older than 1 hour
        }
        {
            Name     = "No active long-running queries (> 5 min)"
            Category = "pre"
            Check    = fun () ->
                use conn = new Npgsql.NpgsqlConnection(connStr)
                conn.Open()
                use cmd = conn.CreateCommand()
                cmd.CommandText <- """
                    SELECT COUNT(*) FROM pg_stat_activity
                    WHERE  state = 'active'
                    AND    query_start < NOW() - INTERVAL '5 minutes'
                    AND    query NOT ILIKE '%pg_stat_activity%'
                    -- => Exclude the check query itself from the count
                    """
                let count = cmd.ExecuteScalar() :?> int64
                // => :?> : F# dynamic downcast; throws InvalidCastException if type mismatch
                count = 0L, $"Long-running queries: {count} found (must be 0 before DDL migrations)"
        }
    ]
 
// Execute the checklist and report results
let runChecklist (items: ChecklistItem list) (category: string) =
    printfn "\n=== %s Migration Checklist ===" (category.ToUpperInvariant())
    let mutable allPassed = true
    for item in items |> List.filter (fun i -> i.Category = category) do
        let passed, message = item.Check()
        let status = if passed then "[PASS]" else "[FAIL]"
        printfn "  %s %s: %s" status item.Name message
        if not passed then allPassed <- false
    if not allPassed then
        failwith $"{category} checklist failed — resolve issues before proceeding"

open DbUp
open DbUp.Engine.Output
open System
open System.Collections.Generic
open System.Reflection
 
// Structured migration event for observability systems
// => Serialise to JSON for forwarding to ELK, Datadog, or CloudWatch Logs
type MigrationEvent =
    {
        Timestamp     : DateTimeOffset
        // => DateTimeOffset: includes UTC offset; unambiguous across timezones
        ScriptName    : string
        // => Script name from the journal; used as a metric label
        DurationMs    : int
        // => Elapsed milliseconds; used as a gauge metric value
        Success       : bool
        // => true = script applied; false = script failed
        ErrorMessage  : string option
        // => Some(message) on failure; None on success
        Environment   : string
        // => "production", "staging", "development" — from APP_ENV environment variable
    }
 
// Observability-aware logger that emits structured migration events
type ObservableLogger(inner: IUpgradeLog, emit: MigrationEvent -> unit) =
    // => inner: the underlying console/file logger for human-readable output
    // => emit: a callback that forwards events to the observability backend
    let mutable scriptStart = DateTimeOffset.UtcNow
    // => scriptStart: wall-clock start time for the current script
 
    let env = Environment.GetEnvironmentVariable("APP_ENV") ?? "unknown"
    // => env: "production", "staging", or "development" — injected as metric label
 
    member _.OnScriptStarting(scriptName: string) =
        scriptStart <- DateTimeOffset.UtcNow
        // => Reset timer when a new script begins
        inner.WriteInformation("Starting migration: {0}", [| scriptName |])
        // => Delegate to the underlying logger for human-readable console output
 
    member _.OnScriptCompleted(scriptName: string) =
        let elapsed = int (DateTimeOffset.UtcNow - scriptStart).TotalMilliseconds
        // => Calculate wall-clock duration in milliseconds
        let event = {
            Timestamp    = DateTimeOffset.UtcNow
            ScriptName   = scriptName
            DurationMs   = elapsed
            Success      = true
            ErrorMessage = None
            Environment  = env
        }
        emit event
        // => Forward the event to Prometheus, Datadog, or a custom HTTP sink
        inner.WriteInformation("Completed: {0} in {1}ms", [| scriptName; elapsed |])
 
    member _.OnScriptFailed(scriptName: string, ex: exn) =
        let elapsed = int (DateTimeOffset.UtcNow - scriptStart).TotalMilliseconds
        let event = {
            Timestamp    = DateTimeOffset.UtcNow
            ScriptName   = scriptName
            DurationMs   = elapsed
            Success      = false
            ErrorMessage = Some ex.Message
            // => Some: wraps the error message in an option type
            Environment  = env
        }
        emit event
        // => Alert: observability backend triggers PagerDuty/OpsGenie on Success = false events
        inner.WriteError("FAILED: {0} — {1}", [| scriptName; ex.Message |])
 
    interface IUpgradeLog with
        member _.WriteInformation(format, args) = inner.WriteInformation(format, args)
        member _.WriteWarning(format, args)     = inner.WriteWarning(format, args)
        member _.WriteError(format, args)       = inner.WriteError(format, args)
        member _.WriteVerbose(format, args)     = inner.WriteVerbose(format, args)
 
// Wire everything together for production deployment
let runWithObservability () =
    let events = ResizeArray<MigrationEvent>()
    // => ResizeArray<T>: F# alias for System.Collections.Generic.List<T>
    // => Collects all events for post-migration reporting or assertion in tests
 
    let observableLogger = ObservableLogger(
        ConsoleUpgradeLog(),
        // => ConsoleUpgradeLog: DbUp's built-in console logger; used as the inner logger
        fun event ->
            events.Add(event)
            // => Accumulate locally; in production, forward to HTTP metrics endpoint
            if not event.Success then
                eprintfn "ALERT: migration failure in %s — script: %s — error: %s"
                    event.Environment event.ScriptName (event.ErrorMessage |> Option.defaultValue "unknown")
                // => eprintfn to stderr: captured by Docker/Kubernetes log aggregators
                // => In production: call PagerDuty API or send to alerting webhook here
    )
 
    let engine =
        DeployChanges.To
            .PostgresqlDatabase(Environment.GetEnvironmentVariable("DATABASE_URL"))
            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
            .LogTo(observableLogger)
            // => LogTo(observableLogger): all DbUp log calls route through our observable logger
            .Build()
 
    let result = engine.PerformUpgrade()
    // => result.Successful: aggregate success across all scripts
 
    printfn "\n=== Migration Observability Summary ==="
    for e in events do
        let status = if e.Success then "OK" else "FAIL"
        printfn "  [%s] %-50s %6dms" status e.ScriptName e.DurationMs
    // => Summary table: status, script name, duration — visible in deployment pipeline output
 
    result