All notes
Architecture7 min read

Two databases, one source of truth

A schema that lives only in the production database is a schema nobody can review. Getting ours into Git meant solving a loop first.

The platform I work on has two sides. Auditors and administrators plan audits, review answers and make decisions. Customers answer requirements, upload documents and manage who on their team can see what. Both sides work on the same certifications, but they own different data, and they grew up as two separate SQL Server databases.

This post covers how the system is put together, how the two databases talk to each other, and how we moved both schemas out of "whatever is on the server" and into version control with a pipeline that deploys them.

The shape of the system

There is one ASP.NET Core API and three Next.js portals. The API is split into two versioned surfaces, one for each side, and each surface talks mainly to its own database.

LayerAdmin and auditor sideCustomer side
Front endAdmin portal (Next.js)Customer portal (Next.js)
API surfaceapi/v1/*api/v2/*
DatabaseAudit databaseCustomer database
OwnsCertifications, requirements, auditors, notificationsCustomers, audits, answers, documents, decisions, background jobs

Splitting the API by version group keeps the boundary visible in the code. When I add a controller, its route and its Swagger group say which side it belongs to, and the front end picks the matching base URL. Nobody has to guess.

The database is the business logic

This was the biggest adjustment when I joined the project, and it shapes everything else. Almost no domain logic lives in C#. The API checks who you are, passes parameters into a stored procedure, and shapes what comes back. The rules live in T-SQL.

That only stays manageable with strict conventions, so every procedure follows the same pattern:

-- One procedure per entity, one @Action switch, validated first.
CREATE PROCEDURE [app].[sp_Assignment_CRUD]
    @Action      NVARCHAR(20),   -- 'CREATE' | 'READ' | 'UPDATE' | 'DELETE' | 'LIST'
    @PageNumber  INT = 1,
    @PageSize    INT = 20,
    @SearchValue NVARCHAR(200) = NULL
    -- ...
AS
IF @Action NOT IN ('CREATE', 'READ', 'UPDATE', 'DELETE', 'LIST')
    RAISERROR('Unknown action.', 16, 1);

Three more conventions do most of the work:

  • Procedures return JSON. Nested shapes, like a chapter with its requirements and their answers, are built in T-SQL with FOR JSON and read into DTOs. The C# side never stitches rows together.
  • Business errors are a contract. A RAISERROR with a message number of 50000 or above is a message meant for the user, and the API passes it through. Anything else is logged and returned as a generic error, so a SQL internal never leaks to the browser.
  • One helper runs every procedure. It opens its own connection rather than borrowing Entity Framework's, which avoids "a reader is already open" conflicts, and it is the only place that reads columns. A missing column throws with its name, instead of quietly returning null.

Entity Framework Core is still there, but only for a handful of login-related tables. It has no migrations at all. The schema is owned somewhere else, which is the rest of this post.

Two databases, joined by synonyms

The two databases are not independent. An auditor's screen needs the customer's name. A customer's list needs the certification's title. Rather than call across databases by full name everywhere, each database has synonyms for the tables it borrows from the other:

-- In the audit database. Reads like a local table, lives in the customer database.
CREATE SYNONYM [app].[Customers] FOR [$(CustomerDb)].[app].[Customers];

$(CustomerDb) is a deployment variable, so the same project deploys to development, staging and production, where the databases have different names. There are more than forty of these synonyms between the two databases.

Synonyms make the procedures easy to read. They also hide where the data actually lives. A query that looks local may be crossing databases, so the first thing I check before reasoning about a procedure is the synonyms folder. That habit has saved me more than once.

LessonA synonym is a convenience for the reader and a trap for the next reader. Keep them in one folder per database and look there first.

Getting the schema into Git, and the loop

For a long time the real schema was whatever was on the server, and changes moved between environments through Visual Studio's Schema Compare. It worked until it didn't. A missed procedure here, a column on staging that never reached production there, and the occasional "the target schema is in an unknown state" with no clear reason.

We moved both databases into SQL Server Data Tools projects (.sqlproj). Every table, view, function, procedure and synonym is a file in Git, the build compiles the whole model, and the output is a .dacpac that a deploy tool can compare with a live database.

The catch was the circular reference. To build the audit database, the compiler needs to know the customer database's columns, because of the synonyms and cross-database views. To build the customer database, it needs the audit database's columns. Each project needs the other one built first.

The way out is to commit a reference copy of each dacpac, and build in three passes whenever a column that crosses the boundary changes:

  1. Build the customer database against the committed audit dacpac, then refresh the customer reference.
  2. Build the audit database against that fresh customer dacpac, then refresh the audit reference.
  3. Build the customer database again, so it sees any new audit columns.

It looks like ceremony. It is not optional. Skip a pass after changing a shared column and one side builds against a stale picture of the other, and the error shows up at deploy time instead of on your machine. For everyday changes that don't cross the boundary, a single pass is enough.

Deploying two databases that depend on each other

The same loop shows up again at deploy time, because SQL Server checks a view's references when the view is created. If a view in one database uses a column being added in the other, deploying either database first fails. So the pipeline deploys in stages:

StageWhat runsWhy
0A data-shape scriptChanges the deploy tool can't plan alone: add a column, backfill it, then make it required
1Tables, columns, keys and synonyms in both databasesSo every column exists before anything references it
2Everything, in both databasesViews, functions and procedures now resolve on both sides
3Numbered migrationsOne-off data fixes, run once and recorded

Each deploy call took between 15 seconds and two and a half minutes on our build runner, so the pipeline avoids the ones it doesn't need. Stage 1 only runs when a table, synonym or type file changed since the last successful deploy of that branch. A procedure-only change goes straight to Stage 2. The deploy is also set to never drop objects it doesn't know about, so something created by hand on a server is reported, not silently deleted.

Migrations for what a schema can't say

A dacpac describes what the database should look like. It can't describe how to get the data there. For that there is a MIGRATIONS folder per database, with numbered scripts like V002__assignmentparticipant_customercertificationdetailsid.sql, and a small table in each database that records which ones have run:

-- The runner wraps every script like this, then records it.
BEGIN TRANSACTION;
BEGIN TRY
    -- ... the migration ...
    INSERT INTO [dbo].[__SchemaVersions] (Version, Description, ScriptName, AppliedBy)
    VALUES ('V002', '...', 'V002__....sql', 'GitHub Actions');
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

A migration either fully applies and is recorded, or it rolls back and the deploy stops. It never half-runs.

That V002 script is a good example of why these exist. A customer can hold the same certificate more than once, and the old way of linking a participant to a certificate couldn't tell those instances apart, so participants could appear on the wrong one. The fix was a new column pointing at the exact certificate instance. Adding the column is the dacpac's job. Filling it in for existing rows is the migration's job, and it is careful about it:

  • Every step checks first, so it is safe to run on a database where the column already exists.
  • It fills the new column only where exactly one instance matches.
  • Rows that match more than one instance are exactly the ambiguity the column fixes, so it leaves them empty and lists them for a person to resolve. Guessing would have recreated the bug.
LessonA data migration that guesses is a bug with a version number. When the data is ambiguous, stop and list it.

The habits that keep it working

  • Database before API. When a procedure gains a parameter the API sends, the database deploys first. The other order breaks every call to it until the second deploy lands.
  • Review the script, not just the project. A helper script generates the exact T-SQL a deploy would run, without running it. Applying changes for real needs an explicit switch.
  • Check scripts next to the change. The project has no unit test suite, so each risky procedure change comes with a Check-*.sql script that seeds test data in a local copy, runs the real procedure, checks the result and rolls everything back. The access-control rules have a full test run of their own.
  • Commit the reference dacpacs. They are build inputs, not build output. If they are stale, the next person's build is wrong.

None of this is glamorous. But the schema is now something you can read in a pull request, deploy the same way to every environment, and roll forward with a record of what ran. After living without that, I would not go back.