Course 4 · Recipe 1 — The environment-aware schema

You’ve got an index that only earns its keep in production — a covering index for a report that never runs in dev. And a DebugPayload column your test harness loves but you’d never ship to prod. So now you’re maintaining two versions of the same table. Two files, or two branches, or a pile of IF @env = ... you have to remember to update on both sides. Miss one, and prod gets a debug column or dev loses an index. There’s a better way, and you already own the lever.

The lever: the object carries its own intent

In Course 2 you gated a whole object on one product token — '{{Environment}}' = 'Production'. That works, but the decision lives in the expression, not on the thing being decided. This recipe puts the intent on the object, as a custom property, and reads it back in the gate.

Every component — table, column, index — carries an Extensions bag. Drop a tag in it, and that tag becomes a bare {{token}} inside that component’s own ShouldApplyExpression. Pair it with the deploy token and each object decides its own fate:

{
  "Name": "IX_Customer_Email",
  "IndexColumns": "Email",
  "Extensions": { "Purpose": "PerfOnly" },
  "ShouldApplyExpression": "'{{Purpose}}' = 'PerfOnly' AND '{{DeployEnv}}' = 'Production'"
}

{{Purpose}} is the index’s own metadata — PerfOnly. {{DeployEnv}} is the product token, Production by default and overridden per environment. The index applies only when it’s a perf index and we’re in prod. The diagnostic column does the mirror image:

{
  "Name": "DebugPayload",
  "DataType": "NVARCHAR(MAX)",
  "Extensions": { "Audience": "NonProdOnly" },
  "ShouldApplyExpression": "'{{Audience}}' = 'NonProdOnly' AND '{{DeployEnv}}' <> 'Production'"
}

One table file. The metadata that decides each object’s fate lives right on that object.

The recipe: one package, two shapes

{{DeployEnv}} defaults to Production in Product.json. The non-prod settings file overrides it — and points {{TargetDb}} at the non-prod database — without touching the package. Deploy to prod:

schemaquench --ConfigFile:prod.settings.json

Then deploy the same package to non-prod:

schemaquench --ConfigFile:nonprod.settings.json

Let’s prove it. Read both Customer tables back:

cookbook_r1_prod      → columns: CustomerId, Email          indexes: PK_Customer, IX_Customer_Email
cookbook_r1_nonprod   → columns: CustomerId, Email, DebugPayload   indexes: PK_Customer

The perf index lands only in prod. The diagnostic column lands only outside it. Same source package, switched by one token, with every object’s intent declared on the object itself.

The aha: the decision lives on the thing being decided

That’s the shift. Conditional deployment told the package what to do per environment; custom-property-driven deployment lets each object carry what it is, and the gate just reads it. Add a new prod-only index tomorrow — tag it PerfOnly, give it the same gate, done. No central switch statement to update, no second file to keep in sync. The index that doesn’t belong in dev says so itself, in the metadata right next to its definition.

The mechanism is identical on SQL Server, PostgreSQL, and MySQL — a component’s own Extensions becomes a bare token in its ShouldApplyExpression; only the dialect’s names and types differ. Want the exact scope rules — bare names vs. Table.-prefixed, how nested values flatten? The Custom Properties reference has the full table. And if conditional deployment itself is new to you, Course 2 · Module 3 is the ground floor this recipe builds on.

Check yourself: How does one package deploy a different physical schema to production vs. non-production without separate files?

ShouldApplyExpression is evaluated at deploy time against custom-property tokens (an object’s own Extensions) and a deploy-environment token — each object decides whether to apply itself, so the same package produces the prod shape or the non-prod shape based on the values, not on which files you shipped.

Think of it as one mold that knows which fire it’s headed into. The standard dimensions are the same every time, but you’ve stamped each part with a note — this edge only gets hardened for the heavy work, this one’s just for the test bench. The mold reads its own marks and presses the right shape for the fire in front of it. You forge once; production and the test bench each get exactly what they were meant to.

Got a per-environment difference you’re keeping in two files right now — an index, a column, a constraint that only belongs in some targets? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 4 · Recipe 2 — Policy that enforces itself, where a retention rule you declare once becomes the column default and the check constraint that hold it.

Until then, may your one package wear the right face in every environment, and never need a twin.

— Forge

Mark this lesson complete