Tech Series: The database project

Part 1 of the inveazy tech series. This post is about who owns the schema. Not in the abstract — in the daily work of adding a column, publishing Azure SQL, seeding a first workspace, indexing a public feed, and querying that catalog from ASP.NET Core without letting Entity Framework become a second database team.

Series: ← Tech Series: How we built inveazy · Next → Dacpac publish and data lockdown

---

Why this matters before the object model

Business software fails in a quiet way when the database is treated as a side effect of the object model. A developer changes a C# class, generates a migration, and ships. The laptop database looks fine. A shared catalog already has customer rows. Azure SQL rejects a type the local engine accepted. A unique index does not understand soft delete, so a slug that used to belong to a retired post can never be reused — or worse, two live posts share it because uniqueness was left to the application. None of that looks like a crash on day one. It looks like drift, timeouts, and arguments about whose version of the table is real.

inveazy is CRM, inventory, warehouse, purchasing, sales, accounting, storefront, blog, files, and billing in one product. Those modules share a catalog. Workspaces share that catalog on a tenant id. The same logical model has to run on a developer SQL Server, a LAN database people actually use, Azure SQL for a paid customer site, and a Docker volume you are allowed to throw away. If each of those environments invents schema from the last app start, you do not have a platform. You have four slightly different products and a lot of confidence until the first republish.

The inveazy database project is the source of truth for tables, keys, indexes, procedures, and the seed that belongs next to schema. The inveazy web application consumes the published database — it does not migrate schema at startup or scaffold DDL from entities. When a feature needs a column, the column lands in the project first. Pages and services follow.

The database project is the contract. A dacpac is how that contract is compiled and compared. The web application honors the published shape. It does not invent one at startup.

Workflow: edit .sql → build dacpac → publish to Azure SQL or SQL Server → app connects

Why schema lives in the database project

Entity Framework Core is excellent at mapping rows to objects, opening connections, retrying transient Azure SQL faults, and tracking a small graph you intend to update. It is a weak owner of DDL once the catalog is a product. Migrations are a history of how the code used to think, applied in order, on whatever database the connection string points at. That is convenient when the app and the database are the same person’s disposable sandbox. It is a weak contract when republish must keep extra objects, must stop a plan that could lose data, and must leave a customer’s invoices alone.

The generated SQL is the other gap. EF will give you a table. Database-engine work typically stays outside that migration story:

• A filtered unique index that says a slug is unique only among live rows.
• ROWVERSION last in the table (a binary stamp the engine updates for optimistic concurrency).
• Dropping a row-level security policy before ALTER TABLE and putting it back after.
• A MERGE of lookup codes that does not duplicate them on the third publish.

Those belong in a database project that compiles against the engine you actually ship to.

There is a cultural cost as well. When the ORM owns schema, “the model” means the C# types in the web repo. Indexes become a later ticket. Seed becomes whatever was in the last developer’s local database. Audit columns appear on the tables someone remembered. When they do, compliance reviews and backfill queries become firefighting instead of planning. Tenant isolation is a WHERE clause in this service and a hope in that one. The database administrator — even when that person is the same developer after lunch — is always catching up. We wanted the opposite: the catalog is designed, compiled, published, and then coded against, the way you would treat a public API you are not allowed to break from a controller.

What the database project actually is

The inveazy database project is a SQL Server Data Tools (SSDT) project: a compiled model of the catalog, not a folder of CREATE TABLE scripts someone ran once. Visual Studio and MSBuild can turn that model into a coherent Azure SQL database. Foreign keys have to point at columns that exist. Indexes have to name columns that exist. A procedure cannot reference a table you have not declared. If the project will not build, it will not produce a dacpac (a Data-tier Application Package — the compiled schema plus the scripts that ride with a publish), and the web application never gets a chance to paper over a broken catalog. The compile is the first administration gate, and it runs on every CI build, not only when someone remembers to open SQL Server Management Studio.

One object per script, grouped like the product

Schema is not a single dump. Each table, index set, view, and procedure lives in its own script, in a folder that matches the module prefix the rest of the product already uses.

Security and users
Tenants, site, branding
CRM, inventory, warehouse
Purchasing, sales, accounting
Blog, billing
Reporting + tenant isolation

That layout is not housekeeping. It is how two people can change purchasing and blog in the same week without merging a megabyte script. It is how a review can see that a new table followed the same audit columns and tenant foreign key as its neighbors, instead of inventing a slightly different timestamp type. Prefixes in the names — the workspace row, the user row, the post row — are the same prefixes the application services already speak. When the hub says “this is a CRM account,” the table name and the folder agree. Drift between product language and catalog language is how you get two sources of truth before you have even published.

Azure SQL is the compile target

The project targets Azure SQL Database. Local SQL Server still accepts the same dacpac, which is the point: development is not allowed to use engine features the cloud will reject. Three settings make that target real instead of a label on a slide.

Collation is fixed so string comparison is the same on a laptop Docker volume and a customer Azure SQL database. A unique slug that collides in the cloud but not locally is a bug you only find after provision.

Partial containment is on so the application login can be a contained user — an identity that lives inside the database, not a server-wide login. That matters when you ship to customer Azure SQL instances where you do not want to recreate a server login by hand in every environment, and where everyday app traffic should never ALTER tables.

Column order is ignored in compare because ROWVERSION must sit last in the model. We will not let a casual column insert in the middle of a table become a noisy publish diff or a broken concurrency column.

Teams that “support Azure SQL later” write against on-prem SQL Server, then discover contained users, certain index options, or deprecated types at the first cloud publish. We compile as Azure SQL from the start. Local SQL Server — Docker on a developer machine, or a LAN instance — is where you confirm the same dacpac still applies. Features we did not take — temporal tables, ledger, memory-optimized tables, graph — are absent on purpose. The standout is not a list of checkboxes. It is that every feature we did take is on the publish and query path you use this week.

Standard columns are the law of a business row

Almost every operational table carries the same spine. Lookups that are truly global — tenant statuses, subscription tiers, document entity types that mean the same thing in every workspace — omit TenantId on purpose. Status lists that a company might rename or extend keep the workspace. That distinction is a design decision, not an accident of whoever created the table.

Unique natural keys include the workspace. A slug is unique per workspace among live rows, not unique on earth, and not unique including trash. A unique index with no filter would pin a retired slug forever; no unique index at all would let two live posts share a URL. The filtered index excludes IsDeleted = 1 from the uniqueness check, so trash can give the slug back. Those rules live in the project because if they only live in a prompt document, the next table will miss them.

Dacpac is the unit of schema

A dacpac is the compiled project: the schema you intend, in a form sqlpackage can compare to a live database. Publish is that comparison applied as a differential. The engine is not replaying a year of migrations in order and hoping the history still matches reality. It is asking: given this compiled model, and given that catalog, what must change? Adding a nullable column is a small plan. Widening a type might be acceptable. Dropping a column that holds data is the kind of plan we want the tool to stop unless a human has explicitly accepted data loss — and our default is that they have not.

Why a package instead of migrating at startup

When the web host starts, it should assume the database already looks like the contract. If the catalog is missing a column, that is a failed release of the database project, not a reason for the first HTTP request to run DDL. For live catalogs, migrate-on-startup is how a scaled-out app, a second instance, or a crash mid-migration becomes a schema argument. It is also how a developer points a new build at a shared database and upgrades it while someone else is taking an order.

CI builds the dacpac so we know the project compiles. CI does not auto-publish it onto Azure SQL or the LAN catalog. Compiling and applying are different jobs. Applying is an administration event: you chose the target, you accepted the compare, you live with the result. On the managed customer path, the provisioning listener is that administrator — it publishes the dacpac into the new Azure SQL database as part of creating https://{slug}.inveazy.com. On Docker, a LAN instance, or Visual Studio publish, a person runs sqlpackage or the publish script and chooses the target. In none of those cases does the web application apply schema when a page loads. A Razor hub is not the database administrator.

Schema lives in the database project and ships as a dacpac.
The web application does not run EF migrations at startup.
Publish is a chosen target, not a side effect of boot.

What rides along with publish

A dacpac can carry pre-deploy and post-deploy scripts. We use them as companions to schema, not as a second schema owner. Row-level security has to be dropped before some ALTER TABLE work and re-applied after, or publish fails in a way that looks like a mystery engine error. Lookups MERGE so codes exist. A switch decides whether sample story data is allowed to land. Those details — flags, what lockdown still does, what a republish must never MERGE — are Part 2. The idea for this post is that schema release is a database operation with a compiled package, a compare, and a known after-step. It is not an ORM surprise on first request, and it is not a handwritten CREATE TABLE IF NOT EXISTS that becomes the real source of truth in a manual folder.

Publish also leaves objects that exist only on the target. A support view someone added in an emergency should not vanish because it is not in the project yet. The honest fix is to bring it into the project or to drop it on purpose. Silent drop-not-in-source is how production grows a shadow schema and then loses it. Block on possible data loss stays on for the same reason: publish should stop and show the plan rather than truncate a column behind a successful exit code.

Seed that knows the difference between a code and a story

Seed is where a lot of database projects mix codes with stories. One script inserts everything: statuses, a fake customer, a blog post, a warehouse, and the developer’s favorite test user. Run it twice and you get duplicates or a primary key collision. Run it against a customer catalog and you have overwritten their CRM with last year’s demo narrative. inveazy splits seed by audience because those are different jobs that only look the same in a text editor.

Lookups the product cannot boot without

Reference data has to exist on every catalog, including a locked-down Azure database that will never see sample orders. Tenant statuses, subscription tiers, document entity types, invoice and journal statuses, the coded lists that screens bind to — those are part of the product’s vocabulary. Post-deploy MERGEs them by natural key. Republish does not duplicate them. Republish does not fail because they are already there. If a display name or sort order in the canonical list changed, the MERGE can update that metadata without inventing a second “active” status.

Idempotency is not a nicety. Publish runs more than once. Developers republish locally. The fleet republishes schema without touching customer facts. First-time install and day-two upgrade have to share the same lookup script. IF NOT EXISTS on a single column is how you skip a needed update. MERGE on the real business key is how a code stays a code.

Story data that must not land on a customer

Sample data is the other pile: demo users and roles, a CRM narrative you can click, inventory and warehouse rows, blog posts on the public library, a POS menu you can ring up. That data is for evaluation, training, and a disposable local database. It is also useful on a first customer install so SandBox is not an empty shell while someone completes first-organization setup. It is destructive if a day-two republish MERGEs it back over real accounts.

The gate is a publish variable, not a comment in the script. Azure and shared catalogs default it off. Local disposable databases default it on. A lockdown switch turns it off when a LAN catalog has become too real to reshuffle. The SandBox workspace itself is handled carefully: lockdown may insert it if it is missing so the product still has a training room, and it does not rewrite SandBox metadata or retire a marketing demo tenant because a publish profile felt tidy. Part 2 is the operational rules. The design point here is that story rows are not schema, and treating them as schema is how you lose a customer’s week of work.

The chart of accounts is infrastructure

A new workspace that will post inventory and payables needs books, not a scavenger hunt. The default general ledger is a stored procedure that MERGEs a QuickBooks-style chart for that workspace: cash and checking, AR, inventory asset, AP, goods received not invoiced, equity, product and services revenue, COGS, freight, and the rest of a thirty-four-account starter set. It runs at publish when seed is appropriate, and the application can call the same procedure when a live workspace is created later. That is product infrastructure. It is not a developer’s leftover INSERT of account 1000 on their laptop that never made it to Azure.

Account numbers are unique per workspace among live rows, same filtered-uniqueness habit as slugs. Types (asset, liability, equity, revenue, expense) are tenant-scoped codes so a company can still speak its own language later without forking the procedure. The MERGE updates names and sort order for the canonical set without duplicating 1000 Cash because someone published twice. If we had left the chart in application HasData, the first workspace created before the next migration would have a different chart than the second. A procedure in the database project is the same chart everywhere the dacpac has been published.

Why it matters: Lookups are schema’s companions. The starter chart is a product feature. Demo stories are neither. Mixing the three is how a republish overwrites a customer.

Indexes written for the queries we actually run

Indexing in this project is not a later performance ticket. When you author a table in SSDT, you author the uniqueness the business means and the access path the hub will use. Entity Framework can suggest indexes from usage, after production has already scanned the table. We would rather the public blog feed and the user email lookup be cheap on the first day a customer publishes a post or invites a clerk.

Filtered uniqueness is the uniqueness people mean

Soft delete breaks naive unique constraints. If a blog slug must be unique on (TenantId, Slug) with no filter, a retired post occupies that slug forever. The author cannot republish a corrected URL. If you have no unique index at all, two live posts can share a slug and the public route becomes a coin flip. The business rule is simpler than either extreme: among rows that still count, this slug is taken. The WHERE IsDeleted = 0 clause is what makes that true — the uniqueness check ignores trash, so a deleted post gives the slug back.

CREATE UNIQUE INDEX UQ_Blog_Post_TenantId_Slug
    ON Blog_Post (TenantId, Slug)
    WHERE IsDeleted = 0;

User email in a workspace follows the same pattern. General ledger account numbers do too. Categories have their own tenant-scoped slug. The workspace slug on the tenant row is globally unique because it is a hostname story, not a per-workspace document. Those are different uniqueness problems. Putting the same unfiltered unique index on all of them is how you copy a pattern that was right once and wrong the next three times. The filter is IsDeleted = 0 because that is the only uniqueness an operator means when the form says “this slug is taken.”

Covering, filtered indexes for list paths

The public landing feed is a good example of query efficiency as design. The page does not load every post and throw away drafts in memory. The query asks for posts that are not deleted, are published, are public audience, and have been landing-approved, newest first. The index is that predicate, ordered by publish time, and it includes title, slug, excerpt, and image so the card does not have to jump back to the clustered key for the columns the card actually shows. Hub lists lead with workspace id, then status or date, and often include the two or three columns the grid renders. A CRM account list that always shows name, owner, and organization should not become a lookup dance per row if we already know that shape.

Foreign keys used in joins and deletes get indexes. Composite indexes put the selective filter first — almost always workspace, then the date or status the screen filters on. We avoid near-duplicate indexes that lead with the same columns and waste writes on every insert. Clustered keys stay on the big integer identity. Random GUIDs as cluster keys are a fragmentation tax we did not sign up for. None of this is exotic Azure SQL. It is the unglamorous half of modern SQL Server that ORMs skip because the table “already works” at fifty rows.

IX_Blog_Post_PublicLandingFeed
  (PublishedAtUtc DESC)
  INCLUDE (TenantId, Title, Slug, Excerpt, FeaturedImageUrl)
  WHERE IsDeleted = 0
    AND IsPublished = 1
    AND AudienceKind = 1
    AND LandingApprovedAtUtc IS NOT NULL

Auditing the row, not a mystery log

When something goes wrong in business software, the first questions are boring and necessary. When was this created? Who last changed it? Is it deleted or just hidden by a filter? Did two people save it at once? If those answers live in an optional audit table that writers forget to call, you do not have auditing. You have a table that looks responsible in a diagram.

The columns on the row are the live discipline

Transactional tables carry created-at and modified-at in UTC, who created the row, who last changed it, soft-delete flag, deleted-at, deleted-by, and ROWVERSION. Created-at is set once. Modified-at moves on every update. Queries default to live rows. A delete in the product is an update that sets the flag and the deleted metadata. Trash views and undo become queries, not archaeology. The landing page can still omit a deleted post without reconstructing history from change tracking we never enabled.

UTC is not a style choice. A customer site, a LAN server, and a laptop in another time zone will lie to each other if “now” is local. The engine default SYSUTCDATETIME() means a row inserted from post-deploy seed and a row inserted from the app are on the same clock. Display layers convert. Storage does not. That sounds small until you debug a “published in the future” post that was saved at 5 p.m. Pacific and stored as 5 p.m. with no offset.

Concurrency is an engine column

ROWVERSION is a binary stamp the engine updates. The application reads it with the row and sends it back on save. If another session won, the update affects zero rows and the hub says the document changed, instead of last-write-wins on a purchase order. We put that column last in the table because that is how the type behaves in this model, and we told SSDT to ignore column order in compare so adding a middle column does not become a pointless rebuild of the version stamp.

There is a site audit-log table for field-level history when a screen truly needs old-value and new-value by field. That is the exception. The habit is still the columns on the row. We did not turn on system-versioned temporal tables, change tracking, or ledger. Those are real Azure SQL features with real operational weight: history tables, retention, and a different backup story. We did not need them to know who last touched a vendor bill and whether two editors collided. Using them as decoration is how a catalog gets expensive before it gets correct.

Query efficiency as a habit, not a rescue

Efficiency here is not a bag of hints. It is the decision to write the SQL the index was built for, return only the columns the screen needs, and keep writes explicit. A multi-tenant catalog that always filters on workspace and live rows will scan itself to death if those two predicates are an afterthought in LINQ that the planner cannot trust. Filtered indexes only help if the query is sargable and actually contains the filter. That is why the application’s public-feed SQL repeats the same IsDeleted, IsPublished, audience, and landing-approved predicates the index uses, instead of loading a BlogPost entity and filtering in C#.

Say the query, size the index, return a row

List screens and public cards declare a row type: id, workspace, title, slug, excerpt, image, publish time, category, author. The SELECT names those columns. EF Core materializes that shape with parameterized SQL. The DTO is the contract between the query and the Razor page. It is not a shadow of a table entity the ORM wished it owned. Hydrating a full post — body, SEO, footer media, carousel order, landing workflow — to draw a card is how you turn a covering index into a clustered lookup and then into a timeout when the body is a long article.

Joins in those lists follow the same tenant and live-row rules as the parent. A category join is on category id and workspace, and the category must not be deleted. An author join is the same. A tenant join for the public slug in the URL is on live tenants. That is slightly more SQL than Include() on a navigation property. It is also the SQL you can put next to the index definition and agree that they match. When they do not match, you fix one of them on purpose instead of discovering it in a profiler after launch.

Numbers, JSON, and what stays relational

Document numbers — invoices, POs, receipts — are per workspace, per document kind, per fiscal year. A SQL SEQUENCE object is awkward to scope that way. We keep a number-sequence table with a unique key on workspace, entity code, and year, and we allocate with a single atomic UPDATE … SET CurrentValue = CurrentValue + 1 OUTPUT INSERTED.*. Two clerks posting invoices do not get the same number because the increment happens in the engine under a row lock, not in application memory. Prefix and pad length live on that row so the format is data, not a hard-coded string in a service that drifted between modules.

JSON is stored as NVARCHAR(MAX) where the payload is genuinely unstructured: import staging rows, provider options, some address blobs, landing layout blocks. We query it with OPENJSON when a board or import needs to probe inside. We did not migrate the whole product to JSON documents, and we did not wait for a native JSON index to make a blob look like a table. Relational columns still own quantity, price, tenant, and status. JSON is a pressure valve, not an architecture. That is a modern Azure SQL pattern in the boring sense: use the type that matches the integrity you need.

How the application talks to the database

This is the part that surprises people who assume “.NET app” means “EF owns everything.” Entity Framework Core is in the stack. It is a client. There is no migrations folder racing the dacpac. There is no Database.Migrate() or EnsureCreated() at startup. A modest set of mapped entities covers the identity and settings spine — workspaces, users, site configuration, a few inventory and billing shapes that benefit from change tracking. The rest of the catalog is hundreds of tables. The application reaches them with SQL it can read in review, not with a LINQ graph that might emit a surprise join.

EF Core as a connection, a retry, and a small entity set

What we keep EF for is the unglamorous half of data access. It pools connections. It retries transient Azure SQL failures. An interceptor runs when a connection opens. Command timeout and the SQL Server provider are configured in one place. For the tables that are genuine documents you load, edit, and save as a unit, a mapped entity is fine. For a list of fifty cards, it is the wrong abstraction. Treating every table as an entity is how you spend a year generating a complete object model of a catalog you already designed in SQL, then fight the ORM to get the query you could have written in twenty lines.

Database-first, in our case, does not mean “scaffold nightly and check in 400 generated files.” It means the published database is canonical, and the C# types we do maintain are hand-kept to match it. If a column lands in the project, the few entities that include that table get the property. The many queries that never used that table do not get a migration. That is less magic. It is also fewer ways for a generated entity to drag a column into a save that the service did not intend to touch.

DTOs are the query contract

A DTO in this codebase is often a row: a sealed class whose properties match the SELECT list. Public blog cards, hub post grids, carousel slots, tag counts, trash rows — each has a shape. EF’s SqlQueryRaw materializes it. Parameters are SqlParameter values, not concatenated strings, so a slug in the URL cannot become SQL. Writes in many modules use explicit INSERT and UPDATE with the columns the service means to change, via ExecuteSqlAsync, instead of attaching a graph and hoping the change tracker does not cascade into a neighbor table.

That pattern is older than EF Core, and it is still the one that maps cleanly onto covering indexes and audit columns. You set ModifiedAtUtc and ModifiedByUserId in the same UPDATE that changes the title. You set IsDeleted and DeletedAtUtc in the same UPDATE that retires the row. You check RowVersion in the WHERE clause. A change tracker can do some of that with interceptors and shadow properties. We preferred the SQL to be visible next to the business rule, especially on money, inventory, and publish-to-landing paths where a silent extra column update is a bug, not a convenience.

SELECT TOP (@take)
    p.Id, p.TenantId, t.Slug AS TenantSlug,
    p.Title, p.Slug, p.Excerpt, …
FROM Blog_Post AS p
JOIN Core_Tenant AS t ON t.Id = p.TenantId AND t.IsDeleted = 0
WHERE p.IsDeleted = 0
  AND p.IsPublished = 1
  AND p.AudienceKind = 1
  AND p.LandingApprovedAtUtc IS NOT NULL
ORDER BY p.PublishedAtUtc DESC

That fragment is not a tutorial. It is the same predicate as the covering index above. The DTO has those columns and no body. The page cannot accidentally request the article text. If we had used a full entity, the easy code would have been the expensive code. Aligning DTO, SQL, and index is the optimization. Clever caching on top of a SELECT * is not.

Session context and row-level security

When a request has an authenticated workspace, opening a connection sets SQL SESSION_CONTEXT for that tenant id. Row-level security policies on tenant-owned tables use a predicate that allows the row if the session tenant matches, or if the caller is the contained app user / db_owner path that publish and certain system jobs need. Identity tables — the workspace itself, users, roles — are excluded from that blanket policy so you can still authenticate. Everything else that carries TenantId is a candidate for the filter.

Application code still passes workspace in SQL. RLS is the backstop so a forgotten WHERE is not the only wall between two companies on the same catalog. The interceptor clears session context when a connection returns to the pool, so the next renter does not inherit a tenant. The application login is a contained database user. Everyday traffic does not need a server-level login. Administration still uses an elevated path for dacpac publish, which must be able to ALTER tables the app user should never ALTER. That split is Azure SQL containment used as a product boundary, not as a checkbox on a security review slide.

EXEC sys.sp_set_session_context @key = N'TenantId', @value = @tid;

Administration is part of the design

If the database project is the source of truth, then changing a live catalog is a ritual with rules, not a developer convenience. Rebuild so the dacpac is not stale. Point sqlpackage at the target. Read the compare. Stop a plan that might lose data. Do not drop extra objects. Re-apply RLS. MERGE lookups. Leave sample seed off unless the catalog is disposable or a first install that still needs SandBox. If that sounds slower than pressing F5 on a migration, it is. It is also the speed you want when the target already holds someone’s books.

CI is allowed to fail the build when the project will not compile. It is not allowed to decide that Azure SQL should change because a branch merged. Drift should show up as a dacpac diff you can review, not as a hotfix script that becomes canonical in a Manual folder. The ops kit exists for containment diagnosis, RLS drop-and-reapply when a human is publishing from Visual Studio, and documented emergencies. New modules are not born there. If the table is product schema, it belongs in the project, in the module folder, with the same audit columns as its siblings.

That is also how local Docker, a shared LAN catalog, and a customer Azure SQL stay in the same family. The same dacpac applies. The difference is seed and lockdown, not a different mental model of the tables. Developers do not use EnsureCreated locally and “do it properly” in Azure. The local path is the proper path with sample data turned on. The day you treat local as a different product is the day Azure is surprised by an index you never compiled.

Questions we get about this path

Can EF Core migrations sit alongside the database project?

Not as a second schema owner. The project is the contract. EF maps rows to objects, retries Azure SQL, and materializes DTOs. It does not run Database.Migrate() at startup, and there is no migrations folder racing the dacpac.

Does this work with SQL Server on a box, not only Azure SQL?

Yes. The project compiles as Azure SQL so local SQL Server cannot invent features the cloud will reject. The same dacpac publishes to Docker SQL, a LAN instance, and customer Azure SQL. Containment and collation are part of that compile, not a later cloud surprise.

Why not a unique index on slug with no filter?

Soft-deleted rows would keep the slug forever. The filtered unique index excludes IsDeleted = 1, so trash can give the URL back, and two live posts still cannot share it.

Who applies the dacpac?

CI compiles it. A person, or the provisioning listener on a paid site, applies it. The web host does not. That split is Part 2.

How this sets the process apart

A typical application process treats SQL as an implementation detail of the object model. You can ship fast until the first production-like database, the first multi-tenant report, the first “we must not lose this column,” and the first argument about whether the ORM’s SQL is the query you meant. Indexing becomes reactive: wait for a timeout, add an index, hope it matches the generated SQL next month. Seed becomes a copy of whoever’s laptop was working on Friday. Audit columns appear on some tables and not others, so trash and concurrency only work in the modules that had a careful day. Tenant isolation is a filter in this controller and a join that forgot it in that export.

Administration in that world is often after-the-fact. Someone gets db_owner. Someone runs a script from chat. Someone turns off a migration that failed halfway and edits the history table. Azure SQL is “the production engine,” but the project was authored against a different engine, so the first cloud publish is a science fair. EF Core is a fine tool in that process. It is the wrong executive. It will optimize for developer flow, not for a catalog that has to be republished for years without mixing demo stories into customer rows.

inveazy treats the catalog as a product of its own. Azure SQL is the compile target, so the daily build already knows the cloud’s limits. The dacpac is the release unit, so local SQL Server, a LAN catalog, and Azure SQL in a customer resource group are the same contract. Seed is split into codes the product needs, a chart of accounts a workspace needs, and stories a demo needs. Indexes and audit columns are written next to CREATE TABLE, not after an outage. EF Core is a client: connections, interceptors, retries, a small entity set. Parameterized SQL plus DTOs keep hot paths aligned with those indexes. RLS and session context make tenancy an engine policy as well as an application habit. Publish is allowed to fail. Data-loss block stays on. That is how you keep a multi-module catalog honest for years: one compiled contract, many environments, no second quieter database inside the ORM.

That is slower on day one than “add a DbSet and migrate.” It is faster on day one hundred, when the question is whether a republish will respect a customer catalog, whether a public feed can stay cheap as the blog grows, whether two modules agree what a row looks like after someone presses delete, and whether a new workspace gets the same books as the last one. Schema-first is not nostalgia for stored-procedure shops. It is how a multi-tenant, Azure-hosted product stays one product.

Other stacks generate a database from the app. This stack generates an app from a database that was already designed to be published.

What this contract keeps in place

The catalog has one schema owner: the database project. Entity Framework does not ship a parallel migration history. New modules land as scripts in the project, not Create-if-missing one-offs. The web host does not apply DDL when it starts, even when the database already holds someone’s books. Uniqueness includes soft delete. Tenant-owned list indexes lead with workspace. List screens select a card DTO, not every column on the table. Seed can tell a lookup from a demo customer. The chart of accounts is a procedure in the project, not a leftover local insert. Row columns and ROWVERSION already answer who changed a document and whether two editors collided, so temporal tables and ledger are not how we prove the catalog is serious. Extra objects on the server stay until someone brings them into the project or drops them on purpose. A publish plan that could lose data does not apply by default.

The rest of that discipline — the publish scripts, pre-deploy and post-deploy, when sample seed is allowed, and what a republish leaves untouched on a shared or customer catalog — is the next post. This post is the contract those scripts are allowed to apply.

The database project is the contract. The web application honors the published shape. It does not invent one at startup.

---

What’s next

Part 2 is dacpac publish and data lockdown: the publish scripts, pre-deploy and post-deploy behavior, when sample seed is allowed, and what must never happen to a shared or customer catalog. Operator walkthroughs live in the separate how-to series.