What Monday's deploy does with Friday's hotfix

Friday, eleven at night. The order-history screen is timing out, support can’t reach a customer, and a bulk load is crawling. A DBA opens a connection straight to prod and fixes all three. An index here. A column there. One index dropped to get the load through. Nobody touches the package. Everybody goes home.

Monday, nine in the morning, the next release goes out. The database says one thing. The package says another. The deploy is about to settle the argument, and it’s not going to ask anybody first.

That gap has a name: drift. Every team that’s ever had production access has it. This module is about what a declarative deploy does with it, and how you stay ahead of that instead of finding out from the log.

A declarative deploy converges. Every time.

A migration tool runs the scripts it hasn’t run yet and never looks at the rest. A hand-made change is invisible to it, so drift piles up quietly until a script collides with it. SchemaSmith works the other way. Every quench compares the live database to the package and closes the gap. So drift doesn’t pile up. It gets settled on the next deploy, one way or the other.

That’s the good news and the warning in one sentence. Whatever happened on Friday, Monday puts prod back on the record. If some of Friday’s work was worth keeping, Monday undoes that too.

So you read Monday before it runs. That’s what WhatIf is for.

Three hotfixes, three fates

In the lab you play the DBA. You deploy the OrdersService release to prod, seed it, and make Friday’s three changes by hand. Then you run Monday’s deploy as a WhatIf. On SQL Server the lines that matter come back like this:

RAISERROR('  Dropping columns from [dbo].[Customer] ([Phone])', 10, 100) WITH NOWAIT;
ALTER TABLE [dbo].[Customer] DROP COLUMN [Phone];
RAISERROR('  Creating index [dbo].[Customer].[IX_Customer_Email]', 10, 100) WITH NOWAIT;
CREATE NONCLUSTERED INDEX [IX_Customer_Email] ON [dbo].[Customer] ([Email]) WITH (DATA_COMPRESSION=NONE);

PostgreSQL, MySQL and MariaDB say the same thing in their own dialects. Three changes went in. Each one gets a different answer:

Friday’s change	Monday’s deploy
Dropped the declared index `IX_Customer_Email`	Puts it back. The package declares it, so it converges, whoever changed it.
Added a `Phone` column and filled it in	Drops it, and the phone number with it.
Added an index on `OrderHeader.OrderDate`	Leaves it alone. It doesn’t even appear in the output.

Read the middle row twice. The recyclebin from Module 3 catches dropped tables. It never catches columns. Without the preview, Monday’s deploy takes the callback number support wrote down on Friday, says Successfully Quenched, and moves on.

Why a column and an index get different answers

Two settings, two defaults, and they disagree on purpose.

DropColumnsRemovedFromProduct defaults to true. A column on a managed table that the package doesn’t declare is treated as removed. That’s the right call most of the time. The column is either something you forgot to declare or something you deliberately took out, and the package is the record.

DropUnknownIndexes defaults to false. An index SchemaSmith never created is kept until you say otherwise. Teams adopting SchemaSmith inherit years of indexes somebody tuned by hand, and dropping one a query leans on is a slow, painful way to find out it mattered. Keep first, align later.

Turn index enforcement on in the preview and you see what it would do:

SmithySettings_WhatIfONLY=true \
SmithySettings_DropUnknownIndexes=true \
  schemaquench --ConfigFile:./base.settings.json

The same two statements come back, plus a third that drops the OrderDate index. That’s the switch you flip once the package really does describe the whole schema. The end-user guide’s adoption approach walks that sequence: extract, align in stages, then enforce.

Keep it by declaring it

A hotfix is a change somebody needed. So the real question on Monday morning isn’t “how do I stop the deploy undoing it.” It’s “does this belong in the product?”

In the lab the answer splits. The OrderDate index fixed a real problem, so it stays. Support is using Phone, so it stays. The bulk load is over, so IX_Customer_Email goes back where it was. Keeping something means writing it into the package:

// Customer: append to "Columns"
{ "Name": "Phone", "DataType": "NVARCHAR(30)", "Nullable": true }

// OrderHeader: append to "Indexes"
{ "Name": "IX_OrderHeader_OrderDate", "IndexColumns": "OrderDate" }

Run the WhatIf again. The column drop is gone. The only schema change left is the one you chose, the email index going back. On SQL Server you’ll also see SchemaSmith claim the index you just adopted:

EXEC sp_addextendedproperty @name = N'ProductName', @value = 'OrdersService', @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table', @level1name = 'OrderHeader', @level2type = N'Index', @level2name = 'IX_OrderHeader_OrderDate';

That’s SQL Server’s way of recording ownership; the other engines track it without printing anything. On all four, a declared index is now the product’s. Take the OrderDate index out of the package later and the next deploy drops it as removed, enforcement or not. Deploy for real, check Customer, and the phone number is still there.

Two objects, you edit by hand. A whole schema’s worth of drift, you don’t. Cast the live database back into the package with SchemaTongs, the move from Course 1 · Module 4, and git diff reads the drift back to you one line at a time. Keep the lines you want, revert the rest, commit.

The drift report is a WhatIf on a schedule

Prod and the package agree again. The way to keep it that way is to ask Monday’s question every night, not just on deploy day. Pin the summary with --report: and turn index enforcement on so a stray index counts too:

SmithySettings_WhatIfONLY=true \
SmithySettings_DropUnknownIndexes=true \
  schemaquench --ConfigFile:./base.settings.json --report:./drift-report

A clean prod reads all zeroes in drift-report.md:

## Object Changes
- Created: tables=0, columns=0, indexes=0, constraints=0, foreignKeys=0, procedures=0, views=0, functions=0
- Modified: tables=0, columns=0
- Dropped: tables=0, indexes=0, constraints=0, foreignKeys=0

In the lab you then add one more index by hand and run it again. Dropped reads indexes=1, and drift-report.json names the index with the action wouldDrop. A job that fails on any non-zero count catches the next Friday before the next Monday.

One gap to know about. The summary doesn’t yet count a column the deploy would drop (#607). PostgreSQL, MySQL and MariaDB list it in drift-report.json as a wouldDrop; SQL Server doesn’t list it there yet. Until that’s fixed, have the job also search the run’s log for the drop line, because that’s the drift that loses data.

Check yourself: A DBA added a column to a production table by hand and filled it with data. What happens to it on the next deploy if nobody touches the package, and how do you keep it?

DropColumnsRemovedFromProduct defaults to true, so the next deploy drops the column and its data, and the recyclebin doesn’t catch columns. A WhatIf shows the drop before it happens. To keep it, declare the column in the table’s file; the next deploy then leaves it, and its data, alone.

Think of it this way. The package is the pattern on the wall, and every quench presses the metal back to it. Somebody can always reach into the fire on a Friday. What they can’t do is make that change stick, unless it goes into the pattern. So read the press before it comes down. Keep what earned its place by writing it into the mold. Let the rest get hammered flat. Preview, declare, quench.

Want the longer version of the story, from extraction through enforcement? Read the end-user guide’s Extracting changes from a live database and The adoption approach.

Got a prod that’s been drifting for years and you’re not sure where to start? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 4 · Driving the tools, where custom properties and script tokens start telling the deploy what to do.

Until then, may every hotfix find its way into the pattern, and every Monday’s deploy find prod just as you meant it.

— Forge

Mark this lesson complete