SchemaSmith v2.3.0

Added

• Sticky per-table drop protection — Table.PreventDrop. Mark a table "PreventDrop": true and SchemaSmith will never drop it by absence — even after you remove it from the product package. The protection is sticky: it's persisted in SchemaSmith's ownership tracking (a SQL Server extended property; a ProductOwnership.PreventDrop column on PostgreSQL and MySQL), so it survives the very removal it guards against, where a flag living in the package file could not (the flag would leave with the table). A protected table that falls out of the package is logged and skipped, never silently dropped — and its inbound foreign keys are preserved too, so a kept table isn't left with broken references. To retire a protected table deliberately, either clear the guard first (set PreventDrop: false and re-deploy while the table is still in the package, then remove it) or drop it with a migration script (migrations run outside drop-by-absence). Ownership is now reconciled against the live catalog each run, so a table dropped out-of-band (by a migration or a DBA) has its ownership pruned and doesn't leave a stale marker. Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• Environment-level no-drop protection tier — PreventDrop. Set PreventDrop: true in the environment configuration (SchemaQuench.settings.json or the SmithySettings_PreventDrop environment variable) and the target environment will never drop an object for being absent from the product — every drop-by-absence pass (tables, columns, foreign keys, check/exclude constraints, statistics, product-owned indexes, and unknown out-of-band indexes) is suppressed for the whole run. This is the blanket guardrail for a protected environment (production, a shared staging fleet) where "the deploy tool must not remove anything by omission" is a hard rule, without marking every table individually. Suppressed drops are logged and itemized in the deployment summary's new preventDrop manifest so you can see exactly what was withheld; the run then completes normally (exit 0) — it doesn't drop, rather than exploding. Transient drops are unaffected: an object that is still declared but must be dropped and recreated to apply a change (dropping an index to alter its column, modifying a constraint, recreating a computed column) reconciles as usual — only removal-by-absence is held back. Composes with the per-table sticky PreventDrop and the four-tier drop-control cascade. Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• Canonical variant-aware table filenames + a --Validate naming lean. SchemaTongs now writes each table file under a canonical <schema>.<table>[.<VariantName>].json name (the schema segment is omitted for MySQL and schema-template packages), so a table's conditional variants sort together in source control and in a file listing, and a file's name reflects the table it holds. Because a table's identity lives in its content, not its filename, a non-canonical name never breaks a deploy — --Validate emits an SS-FILE-NAME-003 warning naming the canonical form so the convention stays honest without gating CI. Cross-platform (SQL Server, PostgreSQL, MySQL).
• Deployment summary report — a machine- and human-readable run summary. At the end of every SchemaQuench run, a SchemaQuench - Summary.json (a versioned, stable contract) and SchemaQuench - Summary.md (human-readable) are written to the log directory. The summary captures run metadata (product, platform, mode, outcome, exit code, duration, whether the run resumed from a checkpoint), per-target outcomes (Success / Failed / Skipped) with durations, the migration scripts that actually ran this run, per-slot and per-database timing with a configurable bottleneck highlight, the failure roll-up, and — in WhatIf mode — the would-apply / skip / deliver listing. Always on; redirect both files with --report <path> and tune the highlight cutoff with BottleneckThresholdMs (default 30000). The summary is archived alongside the run's logs, and is emitted on every exit path (success, partial failure, and abort) as a best-effort step that never disrupts the run. A per-object change section (objectChanges) reports what the run actually changed on each engine: verified created / modified / dropped counts for tables, columns, indexes, constraints, and foreign keys (captured in-proc at the moment each DDL statement runs), a scriptsRan count of the object scripts (procedures / views / functions) re-applied, and a per-object details list; instrumented is true once the audit is populated for the run's engine. Cross-platform (SQL Server, PostgreSQL, MySQL). — #243
• Fleet enumeration against a nominated control database — Template.IdentificationDatabase. A new optional template property that re-targets which database a template's DatabaseIdentificationScript connects to when discovering its roster of databases. Left unset (the default) enumeration runs against the platform init database (master / postgres / information_schema) exactly as before. Point it at a control-plane registry database to read a tenant roster from a registry table at enumeration time — for example "DatabaseIdentificationScript": "SELECT db_name FROM dbo.tenants WHERE active = 1" with "IdentificationDatabase": "FleetRegistry". This is the only way to reach such a table on PostgreSQL, where a connection is bound to a single database and cannot cross-database-query. The value is token-resolvable ({{ControlDb}}) for per-environment control. The re-target is scoped to the enumeration connection alone: database provisioning and existence checks still run against the init database, and SchemaIdentificationScript (schema discovery) is unaffected. Cross-platform (SQL Server, PostgreSQL, MySQL).
• Deployment failure triage — a consolidated, phase-grouped failure roll-up. When a run finishes with failures, SchemaQuench now writes a SchemaQuench - Failures.log that names every failed scope — a tenant work unit ([server].[db] [Schema: x]), a per-server Before/After product script, or a product-level Validate phase — grouped by phase, each with the engine error, the Resolved SQL written to: artifact path, and a captured tail of the log lines leading up to the failure. A loud *** FAILED banner marks each failure live in the progress stream (greppable on *** FAILED), and the roll-up echoes to the console at end of run — so when one of N parallel targets fails, you get a consolidated list of what broke instead of reconstructing it from one interleaved log. For a failed user script the roll-up's Error: line names the specific script and its engine error (Unable to quench '<path>': <error>) and Debug SQL: points at the resolved-SQL artifact — parity with mechanical failures, rather than a generic wrapper message. Always on; a clean run adds nothing. The captured-context depth is set by FailureContextLines (default 25; 0 disables context capture). Cross-platform (SQL Server, PostgreSQL, MySQL). — #338
• Command-line configuration overrides — set any option with --Key=value. Any configuration setting can now be supplied or overridden from the command line, not just the handful exposed as named switches. Nest into the hierarchy with a double underscore, exactly like the SmithySettings_ environment variables (--Target__Server=prod-db, --Target__ConnectionProperties__Encrypt=true). A command-line override sits at the top of the configuration hierarchy — it wins over the settings file, user secrets, and environment variables. Consistent across SchemaQuench, SchemaTongs, and DataTongs; cross-platform (SQL Server, PostgreSQL, MySQL). — #307
• Resolved command-line switches are logged at startup. Every tool now echoes the switches it was invoked with to the progress log, right before the active-configuration dump, so a run's effective command line is visible in the log and CI output. Sensitive switches (--ConnectionString, --Target__Password, and any name matching the LogHygiene sensitive-name rules) are scrubbed to ***, and embedded connection-string passwords are stripped — the same masking already applied to the configuration echo. Consistent across all CLI tools; cross-platform. — #306
• --Validate: no-database static schema-package linter. A new SchemaQuench switch that loads a schema package through the domain model for the platform declared in Product.json and reports coherence problems before the package reaches any target — the target-less member of the read-only pre-flight gate family (--TestConnection → --PreviewTargets → --Validate). No connection, no target, no side effects. It catches: malformed/unloadable package files (reported cleanly instead of crashing mid-deploy); accidental duplicate columns, indexes, foreign keys, check constraints, tables, and templates (same-name entries all gated by ShouldApplyExpression are recognized as legitimate conditional variants, not flagged); dangling foreign-key and index column references, unresolvable related tables, and FK column-count mismatches; undefined, malformed, and unused {{token}} references across scripts and JSON expression fields; and JSON files that violate their committed .json-schemas/*.schema (misnamed / misplaced / missing-required properties and custom-property governance), plus a staleness check that flags committed schemas out of date with the current model (regenerate with --WriteSchemasOnly). Exit code 0 when clean or warnings-only, 2 on any error — designed for CI gating. Cross-platform (SQL Server, PostgreSQL, MySQL). — #324
• DataDelivery gating and variants — ShouldApplyExpression + VariantName, object-or-array. A table's DataDelivery now accepts either a single object (unchanged) or an array of independently-gated deliveries, each with an optional ShouldApplyExpression (evaluated per target at deploy time) and VariantName. This gates seed/test data to specific environments, selects per-environment variants, or applies additive patch slices — every delivery whose gate passes applies, in declared order; a blank/absent gate always applies (today's behavior). A gated-off delivery is logged as skipped, distinct from delivered and failed. Cross-platform (SQL Server, PostgreSQL, MySQL). — #278
• Resolved-SQL artifact coverage extended to product-level and validation scripts. Product-level Before/After scripts and validation scripts (BaselineValidationScript, VersionStampScript) now write a re-runnable resolved-SQL artifact on failure and surface it via the same Resolved SQL written to: progress-log line as every other script surface, completing coverage across the entire deployment. Cross-platform (SQL Server, PostgreSQL, MySQL). — #327

Changed

• Re-extraction now reconciles an extracted table or component to its active variant instead of discarding the extracted shape. When a table — or a table component (column, index, foreign key, check constraint, statistic, full-text index) — has an authored variant set (same name, gated by ShouldApplyExpression), SchemaTongs evaluates each variant's gate against the source database and folds the freshly-extracted shape into the variant that is active there, keeping that variant's gate and VariantName and leaving the inactive variants untouched. When no single variant is active, the extracted shape is written as an ungated entry that --Validate flags (SS-DUP-001) for reconciliation. Previously extraction preserved the authored variant set wholesale and discarded the extracted shape, silently losing real database drift on a variant table. A malformed or erroring gate fails the extraction rather than mis-attributing (fail-closed). Cross-platform (SQL Server, PostgreSQL, MySQL).

Fixed

• SchemaShears and SchemaTongs rejected a relative --Source / Product:Path on Windows. schemashears --Source:Package (a relative path, the form the training labs document) failed with Source folder is not a product (no Product.json): 'Package' even when Package/Product.json existed in the current directory — while an absolute path worked — and schematongs --WriteSchemasOnly with a relative Product:Path failed the same way. The long-path helper was prepending the \\?\ prefix (which requires a fully-qualified path) to relative paths, producing an invalid \\?\Package\… path that the Windows file APIs silently report as non-existent. Relative paths now resolve against the current directory as expected, and SchemaShears additionally canonicalizes its --Source/--Manifest/--Output/--AlwaysInclude paths to absolute so logs and errors show the resolved location. Windows only (POSIX was unaffected).

• SchemaTongs extraction produced table filenames that disagreed with the table's own content — and PostgreSQL extraction dropped a table's schema. Regular (non-schema-template) PostgreSQL extraction deserialized each table into a shape that couldn't hold a Schema, so the schema was silently dropped from the written content while the filename kept the catalog prefix (public.<table>.json). Two consequences: a table in a named, non-default schema (e.g. sales) lost that schema and would re-deploy into public; and every extracted file failed SchemaSmith's own --Validate SS-FILE-NAME-003 naming check (which derives the canonical name from content). MySQL extraction similarly emitted a leading-dot .<table>.json for its (schema-less) tables. Extraction now derives the filename from the table's content schema — schema-less when the schema is the platform default (PostgreSQL public, omitted by convention and re-resolved on load) or absent (MySQL), and schema-qualified for a named schema — and preserves named non-default schemas in content. Extraction output now passes its own --Validate check by construction. Cross-platform (SQL Server was already correct).

• PostgreSQL: a table kept via DropTablesRemovedFromProduct: false lost its SchemaSmith ownership when removed from the package. Ownership was pruned by package absence rather than catalog absence, so once the drop-suppression flag (shipped in v2.2.0) let a removed-from-package table survive, that table's ownership record was still deleted — silently un-managing it, so a later re-enable of drop-by-absence would no longer recognize or reconcile it. Ownership is now reconciled against the live catalog: a row is pruned only when the object no longer physically exists, so a suppressed or protected table stays owned across runs. PostgreSQL only (SQL Server tracks ownership as extended properties that drop with the table; MySQL already pruned only tables it actually dropped). — #270

• SQL Server: DropCheckConstraintsRemovedFromProduct: false did not protect a single-column named check constraint. SQL Server stores a check that references only one column as column-associated (parent_column_id), so a single-column named check removed from a table's CheckConstraints was reconciled through the column-check path — which the DropCheckConstraintsRemovedFromProduct flag never gated. The flag (and, now, the environment-level PreventDrop protection) therefore held table-level checks but silently dropped single-column ones. Removal of any named check is now governed by DropCheckConstraintsRemovedFromProduct across every cascade tier, regardless of how many columns it spans; a genuine check modification (expression changed) still drops and recreates as before. The flag's default is unchanged (true), so default behavior is unchanged. SQL Server only (PostgreSQL and MySQL identify column-level checks by the CK_<table>_<column> name convention, so a differently-named single-column check was already treated as table-level and protected). — #270

• SchemaTongs re-extraction could misattribute or duplicate variant table files. With a table split into structurally-different, same-named variants across separate files, re-extraction matched the write target by computed filename — so it could refresh the wrong variant's file or, when no bare-named file existed, write a spurious ungated <schema>.<table>.json duplicate that then always applied, defeating the variants' gating. Table write targets are now resolved by content identity (Schema, Name), and the extracted shape is attributed to the active variant (see the re-extraction change above). Cross-platform (SQL Server, PostgreSQL, MySQL).

• PostgreSQL: a computed (GENERATED ALWAYS AS) column on a newly-created table was created as a plain column, then converted via a drop-and-re-add. SchemaSmith's CREATE TABLE emitted the column with no generation clause (only identity generation was inlined), so the modified-tables phase then had to drop and re-add it as generated to converge — needless churn on the first deploy, and an intermediate plain column whose value the expression could depend on. Computed columns are now excluded from CREATE TABLE and added in the deferred "add computed columns" step (which can reference the rest of the table), matching SQL Server and MySQL. PostgreSQL only.

• PostgreSQL: tables with an identity column (GENERATED … AS IDENTITY) failed to deploy and never converged. The modified-tables phase could fail with 42601: syntax error at end of input, repeating on every run. The existing-column read captured the identity sequence's START WITH / INCREMENT BY options — which the declarative package cannot express — so the column was perpetually seen as "modified" and produced a malformed ALTER TABLE. SchemaSmith now compares identity columns by kind only, so an unchanged identity column is no longer flagged. This is a round-trip bug (the extractor emits this shape), so it also blocked re-deploying any extracted PostgreSQL table with an identity column. PostgreSQL only (SQL Server / MySQL were unaffected).

• MySQL: an index removed from a product was reconciled only when DropUnknownIndexes was enabled. MySQL coupled removed-from-product index cleanup to DropUnknownIndexes (default off), so an index deleted from a table's JSON silently survived on the next quench unless that flag was explicitly turned on — while SQL Server and PostgreSQL already dropped it by default (gated by DropIndexesRemovedFromProduct). MySQL now matches: a product-owned index no longer in the definition is dropped by default, gated by DropIndexesRemovedFromProduct (env / product / table level, default on) and independent of DropUnknownIndexes. Behavior change: teams that relied on the old MySQL default keeping removed indexes should set DropIndexesRemovedFromProduct: false to preserve them. MySQL only (SQL Server / PostgreSQL already behaved this way). — #270

• MySQL: genuinely out-of-band indexes were never dropped. DropUnknownIndexes on MySQL only ever affected product-owned indexes; an index created out-of-band (e.g. by hand via CREATE INDEX, never recorded in SchemaSmith's ownership) was never removed, even with the flag on — unlike SQL Server and PostgreSQL, which drop unowned, not-in-definition indexes under DropUnknownIndexes. MySQL now detects and drops out-of-band indexes on managed tables when DropUnknownIndexes is enabled (default off), completing index-drop parity across all three engines. MySQL only. — #270

• MySQL: enum(...) / set(...) values were upper-cased on deploy. A column declared enum('web','ios','android') deployed as enum('WEB','IOS','ANDROID') — the type-normalization path upper-cased the whole type string, including the quoted literals, which are case-sensitive data. The deployed definition therefore diverged from the declared one, and because the enum/set comparison was case-insensitive the wrong-case form was sticky: correcting the declaration back to lowercase did not re-apply. SchemaSmith now upper-cases only the type keyword and preserves the quoted enum/set literals verbatim, and compares those values case-sensitively so a corrected declaration converges. MySQL only.

• MySQL: a foreign key or generated column dropped alongside its dependencies could fail with a duplicate-drop error (1091). Several drop paths could build the same DROP FOREIGN KEY / DROP COLUMN twice and fail on the second: a self-referencing foreign key (or one whose source and referenced columns were both removed in one run), or a generated column referencing two or more columns dropped in the same run (ModifiedTableQuench); and a composite foreign key backing a unique index that is being removed — one DROP FOREIGN KEY per FK column (MissingIndexesAndConstraintsQuench STEP 8). All these drop sets are now de-duplicated so each object is dropped exactly once. MySQL only.

• MySQL: unknown-index cleanup no longer reads INFORMATION_SCHEMA inside set-based DML on every quench. The DropUnknownIndexes reconciliation in MissingIndexesAndConstraintsQuench joined INFORMATION_SCHEMA.STATISTICS inside a set-based statement that runs each quench, and MySQL 8.0's optimizer can produce incorrect results when the same correlated INFORMATION_SCHEMA read repeats at high frequency. The catalog rows the step needs are now materialized into a temporary table once and the reconciliation reads that snapshot, keeping INFORMATION_SCHEMA out of the hot set-based path. Behavior and generated DDL are unchanged. MySQL only.

• Fixed: the PostgreSQL materialized-view quench no longer errors XX000: could not open relation with OID under concurrent multi-tenant (schema-template) fan-out. Its drop-detection queries read pg_matviews database-wide and evaluated pg_get_viewdef on sibling tenants' materialized views, racing with a sibling's concurrent DROP MATERIALIZED VIEW; they are now scoped to the iteration's own schema so a tenant never inspects another tenant's views.

• Fixed: PostgreSQL could not create a DEFERRABLE primary key or unique constraint from scratch — the generated DDL emitted the DEFERRABLE clause before the index WITH (fillfactor) clause, which PostgreSQL rejects (42601). The clauses are now emitted in the correct order.

• Fixed: a DataDelivery that permanently fails at execution now fails the deploy (exit 2) on all engines (SQL Server, PostgreSQL, MySQL). Previously the error was logged and an artifact written but the deploy still reported success (exit 0), risking a silent data gap in CI/CD where the exit code is the gate. #334

• Fixed: a PostgreSQL PRIMARY KEY … DEFERRABLE no longer phantom drop/recreates on every quench — the existing-index snapshot now reads a primary key's deferred status accurately. (Surfaced while consolidating the shared index-snapshot helper for #332.)

• Fixed: on PostgreSQL, re-running a quench after a failed unique-index deploy (dirty data fixed) no longer crashes with relation "temp_existing_indexes" does not exist; the index/constraint phase rebuilds its session snapshot when a resumed run skipped the step that built it. --ResumeQuench is now a real opt-in — without it, a re-run discards any leftover checkpoint and starts fresh rather than silently resuming. #332

• Fixed: a relative --LogPath no longer splits output — the active logs and the numbered backup subdirectory now resolve to the same absolute directory (the invocation directory). #331

• PostgreSQL full-sync data delivery (Insert/Update/Delete) generated v17-only syntax on older servers. The MERGE WHEN NOT MATCHED INSERT clause emitted BY TARGET whenever a delete was requested, but BY TARGET is valid only on PostgreSQL 17+ — so a full-sync delivery to PostgreSQL 16 or earlier failed with 42601: syntax error at or near "BY". The BY TARGET keyword is now emitted only when the server is 17+ (matching the existing version gate on the accompanying WHEN NOT MATCHED BY SOURCE ... DELETE clause, which already falls back to a standalone DELETE below 17). PostgreSQL only. — #329

• Fixed: DataDelivery MergeFilter is now portable across engines — the MySQL full-sync delete aliases the target Target (matching SQL Server/PostgreSQL), so a filter authored as Target.<col> no longer fails on MySQL with "Unknown column". #333

• PostgreSQL and MySQL connection factories leaked a connection pool on every connection. PostgreSqlConnectionFactory and MySqlConnectionFactory created a new NpgsqlDataSource / MySqlDataSource — each of which owns its own connection pool — on every GetDbConnection call and never disposed it, so pooled connections accumulated for the lifetime of the process. A one-shot CLI run masked it (the process exits), but a long-running or fleet deployment that touches many databases in a single process could exhaust the server's connection slots (too many clients already). Both factories now cache one data source per connection string (the intended long-lived, shared usage), so pooled connections are reused rather than accumulated. SQL Server was unaffected (it uses connection-string pooling directly). — #278

• install.sh and the .deb / .rpm packages did not install SchemaShears. SchemaShears shipped in the v2.2.0 release archives, but two install channels never delivered it: install.sh copied only the three original tool binaries out of the verified bundle, and the .deb / .rpm packages omitted it entirely. Both now install schemashears (with a /usr/bin/schemashears symlink from the Linux packages), matching the other tools; the installation guide, Chocolatey package description, and bug-report tool list were updated to the four shipped tools. The v2.2.0 .deb / .rpm release assets were also re-cut so existing apt / dnf installs receive it. — #325

• SchemaShears wrote its logs under the wrong name. SchemaShears's log4net configuration named its progress and error logs DataTongs - Progress.log / DataTongs - Errors.log (a copy/paste from DataTongs), so the logs were misnamed and SchemaShears's on-exit log backup — which looks for SchemaShears - *.log — never captured them. The logs are now correctly named SchemaShears - *.log.

• Fixed: stale checkpoint no longer skips forge kindling after a target database is reset out-of-band; KindleForge is always evaluated and self-skips via its kindle stamp — #322

• Generated quench DDL failures used a separate, unscrubbed debug line instead of the unified resolved-SQL artifact. A failure inside generated quench DDL (modified-table, index/constraint, foreign-key, materialized/indexed-view, or table-JSON-parse procedures) logged a Debug Script: line pointing at a plain debug .sql file that was never run through ScrubArtifacts redaction, unlike every other script surface. It now logs the same Resolved SQL written to: line as user scripts, product scripts, validation scripts, and data-delivery merges, and the file is scrubbed when ScrubArtifacts is enabled. Cross-platform (SQL Server, PostgreSQL, MySQL). — #327

• Hand-authored Extensions schema-fragment governance was only preserved at the table root on regeneration. A custom JSON-Schema fragment added to the open Extensions bag in a generated .json-schemas/*.schema file — the documented way to enforce governance (required keys, value enums) on custom properties at PR time — survived regeneration only at the table's top level. A fragment authored at any deeper level — column, index, foreign key, check constraint, statistic, XML index, full-text index, exclude constraint, or the indexes of an indexed/materialized view — was silently discarded and rebuilt as an empty bag on every --WriteSchemasOnly run and every SchemaTongs extraction, contradicting the reference docs that instruct authoring column-level governance under properties.Columns.items.properties.Extensions and promise it survives the round-trip. The merge now carries over an authored Extensions fragment wherever it was defined — at every component level and across the table, materialized-view, and indexed-view schema variants — via a location-exact recursive merge, so nothing leaks across levels. Affected all three engines. — #320