SchemaQuench for PostgreSQL

SchemaQuench reads configuration from SchemaQuench.settings.json (or the file specified by --ConfigFile), environment variables with the SmithySettings_ prefix, and command-line switches. Later sources override earlier ones. For the full loading-order and precedence rules, see Configuration.

Target connection settings

Behavior settings

The two drop-control flags above are shown as representative environment-tier entries. All eight drop-control flags are settable here (and via SmithySettings_<FlagName> environment variables) and resolve through a tiered cascade — see DropTablesRemovedFromProduct and Per-type drop protection for the full set and semantics.

Full settings file example

{
  "Target": {
    "Server": "localhost",
    "Port": "5432",
    "User": "deploy",
    "Password": "",
    "ConnectionProperties": {
      "SslMode": "Require"
    },
    "Templates": [],
    "Databases": [],
    "Schemas": []
  },
  "WhatIfONLY": false,
  "SchemaPackagePath": "./MyProduct",
  "KindleTheForge": true,
  "UpdateTables": true,
  "DropTablesRemovedFromProduct": true,
  "DropColumnsRemovedFromProduct": true,
  "DeliverData": true,
  "RunScriptsTwice": false,
  "TrackRunOnceMigrations": true,
  "PruneObsoleteMigrationTracking": true,
  "CheckpointDirectory": "",
  "MaxThreads": 10,
  "VerboseLogging": false,
  "ScriptTokens": {}
}

For environment variable mapping, see Environment variables.

Two template-level flags decide whether one failure stops the run or is isolated so the rest proceeds. Both are set in Template.json, not in SchemaQuench.settings.json, and both default to true (continue).

ContinueOnDatabaseFailure

Failure isolation at the database level applies to all templates — both regular templates and schema templates. When ContinueOnDatabaseFailure is true (the default), one database's failure does not abort the product run; SchemaQuench logs the failure, continues processing remaining databases, and exits with code 2 after all work units have completed or failed.

When false, the first database-level failure aborts subsequent iterations. In-flight work units drain naturally — SchemaQuench does not cancel active database connections because an incomplete transaction is more hazardous than a completed one. The product run exits with code 2.

{
  "Name": "CustomerDB",
  "DatabaseIdentificationScript": "...",
  "ContinueOnDatabaseFailure": false
}

For schema templates, ContinueOnDatabaseFailure governs database-level failures during work unit enumeration (a bad DatabaseIdentificationScript, an unreachable server). Schema iteration failures inside a schema template are governed separately by ContinueOnSchemaFailure.

ContinueOnSchemaFailure

Schema templates fan out across multiple schema iterations inside a database. ContinueOnSchemaFailure controls what happens when one of those iterations fails.

When true (the default), a single schema iteration's failure does not halt the others. The failed iteration logs an error, the remaining iterations continue, and the product run exits with code 2 after all iterations have completed or failed. This is the appropriate default for production multi-tenant deployments where one tenant's problem should not block every other tenant.

When false, the first iteration failure stops the dispatcher: no new iterations start, in-flight iterations drain naturally, and subsequent templates in TemplateOrder do not run.

How failures surface. Each iteration's log lines carry a [Schema: <name>] prefix, so failures are traceable per tenant even in a parallel run. The deployment log will show the per-iteration error line for the failed schema, then continue with remaining iterations (in continue mode) or stop (in abort mode). The exit code is 2 whenever any iteration failed, regardless of mode.

ContinueOnSchemaFailure is ignored on regular templates. If set non-default on a regular template, SchemaQuench logs a warning at load time. For database-level failure isolation on any template, use ContinueOnDatabaseFailure.

For each work unit dispatched by a template — one identified database for regular templates, one (database, schema) pair for schema templates — the database quench runs the following sequence. All steps execute on the identified database. For schema templates, the active schema name is available throughout as {{SchemaName}}.

1. Kindle the Forge. Deploys SchemaSmith helper procedures, functions, and the migration tracking table for PostgreSQL. Skipped if KindleTheForge is false.
2. Validate baseline. Executes Template.BaselineValidationScript if configured. Aborts if falsy.
3. Object scripts (first pass). Executes scripts from all Objects-slot folders using the dependency retry loop. If RunScriptsTwice is enabled, resets all scripts and runs a complete second pass to verify idempotency.
4. Parse table JSON. Serializes all Tables/*.json definitions into temp/staging tables for the modular procedures to consume.
5. MissingTableAndColumnQuench. Creates missing tables and adds missing columns.
6. Object scripts (second opportunity). Re-attempts any Objects-slot scripts that failed in step 3, now that missing tables exist.
7. Before scripts. Executes migration scripts from any folder in the Before slot. Sequential and tracked.
8. ModifiedTableQuench. Alters existing columns (type changes, nullability, defaults, generated columns) and manages indexes and check constraints.
9. Object scripts (third opportunity). Re-attempts any remaining failed Objects-slot scripts now that table modifications are complete.
10. BetweenTablesAndKeys scripts. Executes migration scripts from any folder in the BetweenTablesAndKeys slot. Sequential and tracked.
11. MissingIndexesAndConstraintsQuench. Creates missing indexes, check constraints, default constraints, and statistics.
12. AfterTablesScripts. Executes migration scripts from any folder in the AfterTablesScripts slot. Sequential and tracked.
13. AfterTablesObjects scripts. Executes scripts from AfterTablesObjects-slot folders (triggers, rules, post-table views) using the dependency retry loop. Also retries any still-unresolved Objects-slot scripts.
14. Table data delivery. Merges table data described by per-table DataDelivery blocks, ordered by foreign key dependencies. See Table data delivery. Then executes any hand-written scripts in the TableData slot using the dependency retry loop.
15. ForeignKeyQuench. Creates, drops, and modifies foreign keys to match the schema package.
16. Materialized view quench. If the template defines materialized views, deploys them (including their indexes) via the MaterializedViewQuench procedure.
17. After scripts. Executes migration scripts from any folder in the After slot. Sequential and tracked.
18. Stamp version. Executes Template.VersionStampScript if configured.

When UpdateTables is false, steps 4 through 16 are skipped entirely. When IndexOnlyTableQuenches is enabled on a template, steps 4–8 (parse JSON, missing tables, second Objects pass, Before scripts, modified tables) are replaced by a single call to the IndexOnlyQuench procedure. Steps 9–16 still execute, with MissingIndexesAndConstraintsQuench (step 11) and ForeignKeyQuench (step 15) skipped.

SchemaQuench assigns every script folder to a quench slot that determines when the folder's scripts execute and how they are handled. The slot list is the same on every platform; the default folders vary by platform.

Template quench slots

Product quench slots

Product scripts run against the administrative connection, outside the per-database template loop.

• Sequential, tracked — scripts run in alphabetical order and are recorded in CompletedMigrationScripts so they only run once (unless marked [ALWAYS]).
• Dependency retry loop — scripts are retried in rounds until all succeed or no progress is made. See Dependency retry loop.

SchemaQuench remembers what it has already run, so you never have to worry about a migration script executing twice. Migration scripts (scripts in the Before, BetweenTablesAndKeys, AfterTablesScripts, and After slots) are tracked in the SchemaSmith.CompletedMigrationScripts table:

Execution rules

• On each quench run, SchemaQuench checks which scripts in each slot have already been recorded.
• Scripts that appear in the tracking table are skipped.
• Scripts that don't appear are executed, and on success a tracking entry is inserted.

The [ALWAYS] suffix

Scripts with [ALWAYS] in the filename (before the .sql extension) run on every quench regardless of tracking:

001_SeedReferenceData [ALWAYS].sql
002_RefreshPermissions [ALWAYS].sql

[ALWAYS] scripts are never recorded in the tracking table.

Common [ALWAYS] patterns — refreshing materialized views

SchemaQuench deploys materialized view definitions via the MaterializedViewQuench procedure, but it does not refresh their data on every deployment. If your materialized views need periodic refreshing, use an [ALWAYS] script in the After Scripts folder:

-- After Scripts/001_RefreshMaterializedViews [ALWAYS].sql
REFRESH MATERIALIZED VIEW CONCURRENTLY "reporting"."active_orders";
REFRESH MATERIALIZED VIEW CONCURRENTLY "reporting"."monthly_summary";

The CONCURRENTLY keyword allows the refresh to happen without locking out concurrent reads — but it requires a unique index on the materialized view. If your view has no unique index, drop the CONCURRENTLY keyword (which will block reads during refresh).

This runs on every deployment, keeping your materialized view data current with the underlying tables. For views that are expensive to refresh, consider gating the refresh with a condition or scheduling it outside of deployment.

Ordering

Migration scripts within each slot execute in alphabetical order by filename. Use numeric prefixes to control execution order:

001_CreateStagingTable.sql
002_MigrateData.sql
003_DropStagingTable.sql

Obsolete entry cleanup

When SchemaQuench processes a slot, it compares the tracking table entries against the scripts currently present in the package. Entries for scripts that no longer exist in the package are automatically removed.

Forcing re-execution

To force a tracked script to run again, either delete the corresponding row from SchemaSmith.CompletedMigrationScripts in the target database, or rename the script file (tracking is by path, so a renamed script is treated as new).

When DropTablesRemovedFromProduct is true (the default), ModifiedTableQuench drops tables that:

• Exist in the target database.
• Aren't defined in any table JSON file in the schema package.
• Were previously managed by this product.

This keeps the database clean as tables are removed from the schema package over time.

Three-tier cascade

The setting resolves across three tiers — environment → product → template — evaluated from broadest to narrowest:

• Environment — DropTablesRemovedFromProduct in SchemaQuench.settings.json (or the SmithySettings_DropTablesRemovedFromProduct environment variable). Controls all products deployed in that environment.
• Product — DropTablesRemovedFromProduct in Product.json. Controls a single product regardless of environment.
• Template — DropTablesRemovedFromProduct in Template.json. Controls a single template within a product.

Explicit false is sticky. A false set at any tier locks the effective value to false for all lower tiers — a true below can never override an ancestor's false. Absent means inherit. So an environment (or product) that sets false is a hard guardrail that suppresses the drop pass regardless of what any lower tier declares. Unlike the per-type flags, DropTablesRemovedFromProduct has no table tier — see Per-type drop protection.

Environment guidance

The rollback-friendly removal pattern

1. Remove the table from your product definition.
2. Keep DropTablesRemovedFromProduct: false in the production config.
3. Write a migration script that renames or archives the table (or verifies no dependencies remain).
4. After the retention period, either enable the setting for one deployment or add an explicit DROP in a migration script.

For an alternative that keeps auto-drops on while still protecting data, see Recyclebin — soft-drop and restore hooks (drop-but-recoverable via the "SchemaSmith"."CustomTableDrop" / "SchemaSmith"."CustomTableRestore" hooks).

When TrackRunOnceMigrations is false, SchemaQuench treats all migration scripts as if they had the [ALWAYS] suffix — no script is recorded in CompletedMigrationScripts, no script is skipped based on prior runs. Every migration script in every slot runs on every deployment.

When tracking is off, PruneObsoleteMigrationTracking is forced off regardless of its configured value.

For how this flag fits partial-package deployments, see Data fixes — the datafix profile.

Engine error codes

Exit codes tell a pipeline whether the run passed. When a run fails, the engine's own error code tells you what went wrong. PostgreSQL prints the SQLSTATE literally, so the code below is what you'll see in the progress log.

Deadlocks are retried for you — SchemaSmith detects the deadlock and re-runs the operation with backoff, so a transient lock collision resolves itself rather than failing the deploy. To recognize the same fault across engines, see per-platform error codes.

Long deployments fail. Network blips, transient lock timeouts, a migration script that tripped on bad data at step 14 of 20. Without checkpointing, a failure in the final stretch means the next run starts from zero — re-running every step you've already successfully applied.

SchemaQuench writes checkpoints as it goes. Every completed quench step and every completed migration script is recorded to disk. On the next run, already-completed work is skipped and execution resumes at the first incomplete step.

Enabling resume

SchemaQuench --ResumeQuench

With --ResumeQuench, SchemaQuench reads the existing checkpoint files (if any) and skips anything already recorded as complete. Without the switch, the resume logic is off — every step executes regardless of prior state.

Where checkpoints live

SchemaQuench --CheckpointDirectory:C:\schemasmith\checkpoints

By default, checkpoints live in %TEMP%/schemaquench-checkpoints (or the platform equivalent). Override with --CheckpointDirectory:<path> when you need them on a specific volume — for a CI runner with ephemeral temp storage, a shared build server, or a mounted volume that outlives the container. The directory is created if it doesn't exist.

The same value can be set in SchemaQuench.settings.json via the CheckpointDirectory key. The CLI switch wins if both are present.

Checkpoint scopes

SchemaQuench tracks two kinds of progress:

Product-scoped — Cross-database work shared by all templates:

• Before and After product-level scripts.
• Completed templates (a template with every database finished is itself recorded as complete).

Database-scoped — One checkpoint file per {product, template, server, database} combination:

In addition, each template slot (Before, Objects, BetweenTablesAndKeys, AfterTablesScripts, AfterTablesObjects, TableData, After) records the exact scripts that ran, so resumed runs skip each individual script that already succeeded.

Automatic cleanup after success

Checkpoints exist to protect against failures. When the quench completes without error, SchemaQuench deletes every checkpoint file associated with the product. A clean run leaves no residue to mislead the next deployment. A failed run leaves the checkpoint files in place, ready for the next --ResumeQuench invocation.

Practical resume workflow

A 90-minute deployment to a large production database fails at minute 75 because a migration script hit a transient deadlock. You fix the data, re-run the deployment:

SchemaQuench --ResumeQuench

SchemaQuench reads the checkpoints, sees that KindleTheForge, ValidateBaseline, missing tables, modifications, indexes, constraints, and every Objects-slot script already succeeded, logs what it's skipping, and picks up at the first incomplete step. Minutes of work instead of starting from the top.

When to leave resume off

• Normal, clean deployments — The resume flag is opt-in. Without it, every step executes, and at the end the checkpoint files get cleaned up regardless. There's no cost to leaving it off for fast, green runs.
• CI pipelines that rebuild databases from scratch — Each run is a fresh slate, so resume has nothing to do.

Use --ResumeQuench when you specifically expect that a prior run may have left partial state — typically when re-running after a real failure in a non-trivial deployment.

The JSON is the frozen contract: camelCase keys, enum values as their names, indented for reading. Here it is end to end for a small two-tenant run, annotated — the field tables below define every key.

{
  "schemaVersion": "1.0",           // contract version of this report shape
  "tool": "SchemaQuench",
  "toolVersion": "2.2.0.0",
  "run": {
    "product": "Northwind",
    "platform": "PostgreSQL",      // SqlServer | PostgreSQL | MySQL
    "startedUtc": "2026-07-09T14:03:11.204Z",
    "finishedUtc": "2026-07-09T14:03:47.881Z",
    "durationMs": 36677,            // run wall-clock
    "mode": "Quench",               // Quench | WhatIf | Validate
    "outcome": "Success",           // Success | PartialFailure | Aborted
    "exitCode": 0,
    "resumedFromCheckpoint": false
  },
  "targets": [
    {
      "server": "primary",
      "database": "TenantA",
      "schema": "sales",            // null when the target has no schema
      "template": "Tenant",
      "outcome": "Success",         // Success | Failed | Skipped
      "durationMs": 14820,
      "slots": [
        {
          "slot": "ModifiedTables",
          "durationMs": 9120,
          "scriptsRun": 3
        },
        {
          "slot": "ObjectScripts",
          "durationMs": 4110,
          "scriptsRun": 12
        }
      ]
    }
  ],
  "migrationScripts": [
    {
      "path": "MigrationScripts/0007-backfill-region.sql",
      "slot": "MigrationScripts",
      "template": "Tenant",
      "schema": "sales",
      "server": "primary",
      "database": "TenantA",
      "outcome": "Ran"  // always "Ran" — a listed script is one that ran
    }
  ],
  "timing": {
    "totalMs": 36677,
    "bySlot": [
      { "slot": "ModifiedTables", "totalMs": 18240, "targetCount": 2 }
    ],
    "byDatabase": [
      { "database": "TenantA", "totalMs": 14820 }
    ],
    "bottlenecks": [
      {
        "scope": "[primary].[TenantA] [Schema: sales]",
        "slot": "ModifiedTables",
        "durationMs": 31210
      }
    ]
  },
  "failures": [],  // one entry per failed scope; empty on a clean run
  "whatIf": null,                   // populated only for a WhatIf-mode run
  "objectChanges": {
    "instrumented": true,
    "created": {
      "tables": 1,
      "indexes": 4,
      "constraints": 2,
      "foreignKeys": 1,
      "procedures": 0,
      "views": 0,
      "functions": 0
    },
    "modified": {
      "tables": 1,
      "columns": 3
    },
    "dropped": {
      "tables": 0,
      "indexes": 1,
      "constraints": 0,
      "foreignKeys": 0
    },
    "scriptsRan": 12,
    "details": [
      {
        "objectType": "table",
        "objectName": "sales.Orders",
        "action": "created"
      },
      {
        "objectType": "column",
        "objectName": "sales.Orders.Region",
        "action": "modified"
      },
      {
        "objectType": "index",
        "objectName": "sales.Orders.IX_Region",
        "action": "dropped"
      },
      {
        "objectType": "procedure",
        "objectName": "Procedures/GetOrders.sql",
        "action": "ran"
      }
    ]
  }
}

Top-level fields

run

targets[]

migrationScripts[]

timing

failures[]

Empty on a clean run. Each entry mirrors the failure triage roll-up exactly — same content, same backup directory, no new exposure.

whatIf

null for a real quench. On a WhatIf-mode run it holds the plan, split three ways — and every entry carries a script path, never a SQL body.