Tech Series: Dacpac publish and data lockdown

Part 2 of the inveazy tech series. Part 1 was the contract: the database project owns schema, and the web application consumes a published catalog. This post is what happens when that contract is applied to a live database — Azure SQL for a customer, a shared LAN catalog people already work in, or a disposable Docker volume you are allowed to throw away.

Series: ← The database project as source of truth · Next → Web app and API architecture

Why this matters before the flags

A schema update that is safe on an empty local volume is not automatically safe on a catalog that already holds invoices, users, and a public blog. Without an explicit lockdown, publish can MERGE last year’s demo customers over this week’s work, drop a support view that was not in the project yet, or widen a column in a way that truncates data and still exits zero. That is silent data loss: the release looks green, the shape looks newer, and the story already in the catalog is not the story you meant to keep.

inveazy treats that as a product problem, not a folklore problem. Schema ships as a compiled package. The tool that applies it is a compare, not a reset. Sample story data is a switch you set for the target, not a side effect of having a post-deploy script. The rest of this post is how that compare is built, who is allowed to run it, and how lockdown tells post-deploy that this catalog is not empty.

Publish updates the shape of the catalog. It does not get to rewrite the story already in it, unless you explicitly said this database is disposable.

What a dacpac is, and how the compare works

A dacpac is a Data-tier Application Package: the inveazy database project compiled into a single file that describes the catalog we intend — tables, keys, indexes, procedures, and the scripts that ride with a publish. It is not a backup of customer rows. It is not a folder of CREATE TABLE statements someone ran once. Visual Studio and MSBuild build it the way you would compile an application.

The tool that applies it is sqlpackage. It reads the dacpac and scans the target database. The compare is the plan it produces: add this column, create that index, leave these rows alone. We use that pair because the product has to stay honest on more than one engine and more than one catalog. The same package is what a developer publishes to Docker, what a shared LAN database receives under lockdown, and what the provisioning listener publishes when a paid site is born at https://{slug}.inveazy.com.

Publish vs migrate: why the dacpac is the release

For live catalogs, treating the first HTTP request as a database upgrade is risky. Entity Framework migrations replay, in order, how the code used to think, on whatever connection string the process has. That is a reasonable fit for a disposable sandbox that belongs to one developer. It is a weak fit when the same logical model must land on Azure SQL and SQL Server, when two app instances must not race DDL, and when a developer must not upgrade a shared catalog while someone else is taking an order. The dacpac compare is already the question we want: given this compiled model, and given that catalog, what must change? Republish should not turn into a second, quieter migration story inside the web host.

Who is allowed to apply schema

CI builds the dacpac so we know the project compiles. It does not push schema into Azure SQL or the LAN catalog, because compiling and applying are different permissions. Applying is an administration event: you named the server and the database, you passed the admin login, you accepted that this compare will run. The three paths look like this:

• Managed customer site — the provisioning listener creates the database, publishes the dacpac, deploys the Container App, and attaches DNS for https://{slug}.inveazy.com.
• Docker, LAN, or Visual Studio — a person runs the publish script or the IDE publish profile and chooses the target.
• Hub pages — never. A Razor screen that is missing a column is a failed database release, not a reason for the first HTTP request to run DDL.

The app uses a contained user — a database-level identity, not a server-wide login you have to recreate in every environment — so the same credential shape works across instances. Passwords for that user are SqlCmd variables: placeholders in the deploy scripts that sqlpackage fills at publish time so post-deploy can ensure the login exists inside the catalog. They are not committed. Azure publish talks TLS to the logical server. Schema release uses an elevated path. Everyday app traffic uses the contained user, which should never ALTER tables.

The dacpac publish workflow

inveazy treats publish as a schema release. Compiling the project and applying the package are different jobs. CI proves the dacpac builds. A person — or the provisioning listener acting as one — chooses the target, reads the compare, and lives with the result. Sample story data is a switch, not a side effect of having a post-deploy script — SQL that runs after the schema changes land, for lookups, optional SandBox seed, and putting row-level security back on.

How the compare runs

Rebuild the package, then ask sqlpackage what the target would need in order to match it. Pre-deploy makes ALTER TABLE possible. The schema diff is the plan. Post-deploy MERGEs lookups, optionally seeds a disposable catalog, and puts row-level security back on. That is one release, not three folklore scripts in chat.

rebuild dacpac
    → sqlpackage compare against the target
        → pre-deploy (make ALTER TABLE possible)
        → schema diff
        → post-deploy (lookups, gated seed, RLS)

The decisions on every publish

Three questions sit on every apply. Block a plan that could lose data. Do not drop objects that exist only on the target. Seed sample story data only when this catalog is disposable or a first customer install. The scripts wrap those answers so they cannot drift between local Docker and an Azure customer catalog.

Block on possible data loss?     yes
Drop objects not in the package? no
Seed sample story data?          only if this catalog is disposable
                                 or a first customer install

The differential is the point

Adding a nullable column is a small plan. Dropping a column that still holds rows is the kind of plan we want the tool to stop, not apply behind a green exit code. Visual Studio Publish and sqlpackage /Action:Publish are the same idea. The scripts in the database project wrap that call so the flags cannot drift.

/p:BlockOnPossibleDataLoss=true
/p:DropObjectsNotInSource=false

Extra objects stay until someone chooses

Objects that exist only on the target stay until someone brings them into the project or drops them on purpose. Silent drop-not-in-source is how production grows a shadow schema and then loses it on the next tidy publish. That is why inveazy treats publish as an explicit admin action — a chosen compare with an audit trail, not a cleanup job that deletes whatever the package did not mention.

Rebuild so the package is not stale

Rebuild Release first, unless you are applying a dacpac you already built as a fleet artifact. A stale package is how a new table exists in git and not on the server, and the app looks broken when the schema was never there.

The lockdown switch

Post-deploy is where a lot of database projects mix codes with stories. One script inserts statuses, a fake company, a warehouse, and the developer’s favorite user. Run it against a customer catalog and you have overwritten their CRM with a training narrative. inveazy splits that work with a SqlCmd variable the publish scripts set on purpose.

SeedSandboxSampleData = 0   // Azure, shared LAN, day-two fleet republish
SeedSandboxSampleData = 1   // disposable local, or first customer install only

Azure publish defaults the variable to off. You opt in with an explicit flag, and that flag is for disposable or training Azure databases, not a paid customer catalog. Local Docker defaults it on so a developer gets SandBox and story data. Pass lockdown on a LAN database that has become too real to reshuffle, and sample MERGEs stay off. Block on possible data loss and keep extra objects stay on in every case.

The lockdown rule: Lockdown is not “skip post-deploy.” Lockdown is “post-deploy may not treat this catalog as empty.”

Reference data that always runs

Reference data has to exist or the product cannot boot: tenant statuses, subscription tiers, document entity types, invoice and journal statuses — codes rather than stories. Those MERGEs are idempotent: run them twice and you still have one “active” status. Republish does not duplicate them and does not fail because they are already there. If a display name in the canonical list changed, the MERGE can update that metadata without inventing a second code.

SandBox is handled carefully. Lockdown may insert the sandbox workspace if it is missing, so first-organization setup still has a training room. It does not UPDATE sandbox metadata when seed is off, and it does not create, rename, or soft-delete the marketing-only demo slug. That slug is a manual fact on a marketing host. Publish does not invent it or retire it as a tidy-up.

Row-level security is re-applied after the differential. A few schema-drift helpers exist for older catalogs that predate a column the project now expects. Those helpers are not a second schema owner. New modules still land in the database project. The Manual folder is an ops kit for containment, RLS emergencies, and documented restore — not where a feature is born, and not a place to bulk soft-delete users because a screen listed the wrong people.

First-install versus day-two seed audience is the rest of the story, and it belongs with provisioning in Part 7. This post only needs the split: lookups always MERGE; stories do not, unless you said the catalog is disposable.

Sample story data stays off under lockdown

Sample users and roles, CRM narrative, inventory and warehouse story rows, clickable blog posts, a POS menu you can ring — that entire block is skipped when the variable is off. Existing tenant rows are left untouched. Integration seed that would stamp placeholder secret references does not overwrite a customer’s own key pointers on MATCHED. The default general ledger procedure can still exist in the project; calling it to MERGE a starter chart is a product action for a new workspace, not a license to re-story an old one.

Why it matters: Lookups are schema’s companions. SandBox-if-missing is a bootstrapping courtesy. Demo stories are neither. Mixing them is how a fleet republish overwrites a customer.

Pre-deploy exists so ALTER TABLE can run

Row-level security binds filter predicates to tenant-owned tables. That is the backstop so a forgotten WHERE clause is not the only wall between workspaces. It is also why a naive publish fails with a policy blocking ALTER on a purchasing or cycle-count table. Pre-deploy turns the policy off and drops it so the dacpac can change tables. Post-deploy puts the policy back. The pair is the publish workflow, not an optional extra.

If you publish from Visual Studio instead of the scripted path, the same drop still has to happen before the compare, and the same apply still has to happen after. Re-applying RLS between drop and publish puts you back in the blocked state. The symptom looks like a mystery engine error. The cause is a security policy doing its job at the wrong moment in the release.

On box SQL Server, pre-deploy also stages contained database authentication so the app login can be a contained user. Azure SQL skips that server configuration — the cloud engine already works that way — and prints that it skipped. One pre-deploy script, two engines, no “we’ll fix containment later.”

drop tenant isolation policy → apply dacpac diff → re-apply RLS

First install is not day two

A brand-new customer database has no invoices to protect. A first install may run with sample seed on so SandBox is not an empty shell while someone completes first-organization setup. Creating the live workspace after that is a tenant step inside an already published database. It is not a second Azure provision, and it is not a second excuse to MERGE sample companies into the live books.

Day-two fleet republish — schema for a new module, an index, a column — turns sample seed off and keeps lockdown on. The listener or the operator is applying the same dacpac they would apply locally, with the variable the target deserves. That distinction is Part 7 of this series in full. The rule for this post is smaller: the same package, different seed audience. Disposable local defaults to stories. Shared LAN and Azure customer catalogs default to schema plus lookups.

Register-as-data-tier-application stays off. We are not trying to make every target a registered DAC instance for its own sake. We are trying to make the compare honest and the post-deploy gated.

When a list looks wrong, fix the query

When a hub list looks wrong, the instinct is to make the rows match the screen: soft-delete extra users, retire a tenant, run a restore script because the UI was confusing. Lockdown says the opposite. Trust the catalog counts first. Fix the query. Do not bulk-update IsDeleted across a customer database to paper over a filter. Do not run retire or restore kits on Azure or a shared LAN catalog unless someone named that database in an explicit request.

The marketing demo slug is the other tripwire. It is not SandBox. It is not created by dacpac. A publish that “cleans up” demo because the slug looks obsolete will take down a signup path that only exists on the marketing host. Paid customer databases should simply not have that slug. The project does not MERGE it into existence to be helpful.

Allow-sample-seed on Azure is the third tripwire. It exists so a disposable cloud database can look like local Docker. It does not exist so a republish can refresh training data on a catalog that already has real work. If the database is a customer’s books, the flag stays off.

Questions we get about this path

Can EF migrations own schema in production?

They can own a disposable database that belongs to one developer. For a live Azure SQL or shared LAN catalog, inveazy does not apply migrations on first request. Two app instances must not race DDL, and a hub page must not become the upgrade tool. Schema is compiled into a dacpac and applied as an administration event. Entity Framework remains the client of the published catalog.

What happens if sample seed runs on a live catalog?

Post-deploy MERGEs the training narrative — sample customers, warehouses, users, blog posts — over rows that already have a week of work in them. Lockdown sets SeedSandboxSampleData = 0 so that block does not run. Lookups still MERGE. Stories do not.

Does lockdown skip post-deploy?

No. Post-deploy still MERGEs reference codes, may insert SandBox if it is missing, and re-applies row-level security. What it may not do is treat the catalog as empty and write a demo company over a customer.

Why does ALTER TABLE fail with a security policy?

Row-level security is doing its job. Pre-deploy drops the tenant isolation policy, the dacpac apply changes tables, post-deploy puts the policy back. If you re-apply RLS between drop and publish, you are back in the blocked state. The pair is the workflow, not an optional extra.

How this sets the process apart

A typical app-development process lets the first request migrate the database, lets seed be whatever was in the last developer’s local insert, and lets a hotfix script in chat become the real source of truth. Indexes and RLS then show up as afterthoughts that break ALTER TABLE in surprising ways. Production is the first time anyone used BlockOnPossibleDataLoss.

inveazy makes those decisions in the publish scripts so they cannot be forgotten. Rebuild so the package is not stale. Compare with data-loss blocked. Keep extra objects. Drop RLS only long enough to ALTER, then put it back. MERGE codes. Leave stories off unless the catalog is disposable or a first install that still needs SandBox. CI does not get to decide that Azure SQL should change because a branch merged. That is how you ship schema with confidence and an audit trail: a chosen compare, a named target, and a seed audience that matches the catalog you pointed at.

It is slower than pressing F5 on a migration. It is the speed you want when the target already holds someone’s purchase orders. Local Docker with seed on is still the same dacpac. The difference is the variable, not a different mental model of the tables. The day you treat local as a different product is the day Azure is surprised by an index you never compiled — or a MERGE you never meant to run.

Schema release is a chosen compare. Data lockdown is how that compare is allowed to treat a catalog that already has a week of work in it.

What this contract keeps in place

Publish is a database operation with a compiled package, not an ORM surprise on first request. The dacpac is rebuilt unless you are applying a known artifact. Possible data loss stops the plan. Objects that exist only on the target stay. Pre-deploy drops the tenant isolation policy so ALTER TABLE can succeed; post-deploy puts RLS back and MERGEs lookups. Sample seed is off for Azure and shared catalogs and on for disposable local or a first install that still needs SandBox. SandBox may be inserted if missing; its metadata is not rewritten under lockdown. The marketing demo slug is not created or retired by publish. Customer story rows are not MERGEd. Users and workspaces are not soft-deleted to make a screen look tidy. New modules land in the database project, not in a Manual one-shot. The web application still does not own DDL.

Part 7 returns to how the provisioning listener uses this path on a paid site. This post is the discipline that listener is required to keep: same package, seed audience chosen for the target, catalogs with real work left as they were except for schema.

Publish updates the shape. It does not get to rewrite the story already in it.

What’s next

Part 3 is the web application and API: Razor for people, JSON under /api for hub scripts, services, cookies and roles, and keeping API failures in JSON so a save error does not replace the composer with a generic error page. Operator walkthroughs live in the separate how-to series.