Database deployment

Docsentry deploys its database schema as numbered, immutable, forward-only SQL scripts. Entity Framework migrations are used for one thing only: the ASP.NET Identity tables. Everything else is deployed by script.

Why it works this way

• The schema can be reviewed and applied by a database administrator independently of an application release.
• The identity that deploys schema and the identity the application runs as can be kept strictly separate. The application is never permitted to alter schema.
• Every applied script is recorded with a SHA-256 checksum, so replaying a script that has since been edited is refused rather than silently applied.

Deployment order

1. Provision the database

Connect to master as the privileged deployment principal, set the @DatabaseName value inside the script, and run:

Database/deploy/000_provision_database.sql

This script is not idempotent schema deployment. It must never run through the web application’s account or connection.

2. Configure the connection

Supply ConnectionStrings:DefaultConnection through environment variables, user secrets, or your secret store. Source control holds only a placeholder:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=<sql-server>;Database=<dms-database>;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;"
  }
}

The SQL scripts contain no database name, no USE, and no GO. The target database comes entirely from the connection.

3. Apply the Identity migration first

dotnet ef database update --project src/Docsentry.Infrastructure --startup-project src/Docsentry.Web

Entity Framework owns the AspNet* tables exclusively. Database/deploy/010_identity_contract.sql is a fail-fast preflight that asserts this contract - it never creates, alters, or conditionally references Identity tables.

4. Apply the DMS scripts in numeric order

001_schema_versions.sql creates the ledger before any later script runs.

There are 55 numbered scripts, from 000_provision_database.sql through 530_signature_revocation_retains_signed_at.sql. Broadly:

Range	Covers
000-010	Provisioning, the schema-version ledger, and the Identity contract preflight.
020-050	Organisation, metadata and taxonomy, documents and storage, collaboration.
060-080	Authorisation, workflows, governance.
090	Search and reporting, including the full-text catalog.
100	Seed data.
110 onward	Forward-only corrections, feature schema, and least-privilege grants added over time.

5. Verify

Run both with the privileged verification principal, not the application account:

Database/verification/900_schema_contract.sql
Database/verification/910_integrity_negative_cases.sql

900 asserts the catalog contract, trusted foreign keys, seed rows, Identity-only migration history, and typed live-reference rules. 910 creates scoped fixtures inside one transaction, prints the database’s rejection for each negative case, and always rolls the fixtures back.

Database/verification/920_query_plan_baseline.sql records the access paths the hot queries are expected to use. It is not part of routine deployment, but it is the right starting point when investigating a performance regression.

The runner contract

The deployment runner is defined by Database/orchestration/Invoke-DmsSchemaDeployment.cs. For each ordinary script it must:

1. Acquire an application lock, so two deployments cannot interleave.
2. Validate that this is the expected next version, and that the script’s SHA-256 matches the recorded ScriptChecksum if it has been applied before.
3. Execute exactly one script inside one SQL transaction.
4. Insert that script’s ledger row in the same transaction.

A failed script therefore leaves neither its DDL nor its ledger row committed.

dbo.DmsSchemaVersions is independent of Entity Framework’s __EFMigrationsHistory. The two ledgers track different things and must not be conflated.

Checksums are over raw file bytes

The recorded checksum is computed over the file as it exists on disk, so a change in line endings changes the checksum and causes the runner to refuse the script. The repository’s .gitattributes normalises SQL files to LF for exactly this reason. Keep it.

The one non-transactional script

090_search_reporting.sql is an explicit exception. SQL Server full-text catalog and index operations cannot run inside a transaction, so the runner surfaces the capability result rather than treating it as an ordinary transactional schema change.

If the full-text feature is not installed on the instance, this script’s full-text portion cannot take effect. That is not a deployment failure - the application detects the absence at runtime and falls back. See Search.

There is no rollback

Applied versions are never rolled back and applied scripts are never edited, renumbered, or deleted. A schema correction ships as a new, higher-numbered script. Restoring a database is an operational recovery procedure, not a schema operation.

Principal separation

Two distinct database identities are required.

The deployment principal needs only the server and database permissions required to create the database and deploy DDL. It applies the Identity migration, the DMS scripts, and the schema version rows.

The application principal is what ConnectionStrings:DefaultConnection authenticates as at runtime. It must be a member of DocsentryAppRole and must be distinct from the schema owner. It performs data reads and writes only, under the grants deployed in the schema. It cannot create databases, run 000_provision_database.sql, alter DMS schema, or apply schema versions.

Governance surfaces such as dbo.AuditLogs and dbo.WorkflowActionHistory are intentionally append-only for this role: the running application can add to the audit trail but cannot rewrite it.

RuntimePrincipalProbe checks this at startup, so a connection string pointing at an over-privileged account is reported immediately rather than at the first privileged operation.