Skip to content

Migrations

Applies to: All subscriptions

Purpose

Apply the database schema and prove it landed completely. This page exists mainly because of one property: a data migration written without tenant scope updates nothing and reports success. Nothing downstream catches it, so verification here is not optional diligence — it is the only thing standing between you and a half-applied schema.

Audience

Database administrators and implementation engineers applying schema changes, during installation or upgrade.

Prerequisites

  • Database Setup complete
  • A verified backup, on an upgrade. On a fresh installation the database is empty and there is nothing to lose
  • Rights to create and alter objects in the Complifly database
  • A maintenance window, on an upgrade

Steps

1. Understand what you are applying

Migrations are ordered SQL scripts. Two kinds behave very differently:

Kind What it does Risk
Structural Creates or alters tables, indexes, security policies Fails loudly if it fails at all
Data Backfills or corrects existing rows Can silently affect zero rows and report success

The second kind is where care is required.

2. Take a backup, on an upgrade

Take it, and verify it. An unverified backup is an assumption. On a fresh installation this step is unnecessary.

3. Apply in order

Migrations are ordered and must be applied in that order. Skipping one because it "looks unrelated" produces a schema that no version of the application expects.

There is no ledger recording which migrations have been applied. That has two consequences:

  • You must track what you have applied, per environment, yourself. A simple recorded log per environment is sufficient and is the difference between a controlled upgrade and archaeology.
  • The application's startup check is your safety net, not your inventory. It verifies a manifest of objects the code depends on unconditionally. It will catch a missing object that matters and name the migration that creates it. It cannot tell you that everything has been applied.

4. Respect the row-level-security rules for data migrations

Any statement that inserts, updates or deletes rows in a tenant-scoped table must handle scope explicitly. Three rules, each of which has caused a real incident:

Elevate in the same batch as the statement. Scope elevation applies to a session. Migration runners commonly take a fresh request per batch and may receive a different pooled connection, so elevation set in one batch and used in the next silently does not apply — and the statement affects zero rows while reporting success.

Reset elevation before the batch ends, inside error handling. The connection returns to the pool afterwards. A connection returned with elevation still set grants the next unrelated request full cross-tenant visibility. Reset it on the failure path too, not only the success path.

Cross-check counts against storage metadata. Read row counts from sys.partitions (index_id IN (0,1)), which reports from storage metadata and is not filtered by security policies. It is the only check that catches a half-applied backfill. A verification query written the same way as the migration will agree with the migration, including when both are wrong.

5. Restart the application

Restart after applying migrations so the startup check runs against the new schema. Read the startup log to its end.

Outcome Meaning
Starts cleanly The manifest is satisfied
Refuses to start, naming an object and a migration That migration has not been applied. Apply it and restart

6. Verify security policies on new tables

A new tenant-scoped table without a security policy is unprotected, and nothing will tell you. After any migration that adds a table, confirm a policy exists for it. This is a five-minute check that prevents a genuine data-exposure defect.

Validation

A green run is not evidence. Every check below is designed to catch a failure that reports success.

Check Method Pass condition
Objects exist Inspect the object catalogue for the tables, columns and indexes the migration creates All present
Security policies exist List the database's security policies One for every tenant-scoped table, including any table added by this migration
Data migration affected the intended rows Compare the migration's reported count with the sys.partitions count for the same table Equal. Any divergence means stop — the change is half-applied
Row counts are non-zero where they should be Confirm the affected count is not zero when rows were expected Non-zero. A zero count with a success message is the signature of a scope failure
No elevation leaked After the migration, run an ordinary scoped query on the same connection pool Returns only in-scope rows
Application starts Restart and read the startup log to the end Clean start, no missing-object message
Function still works Process one document end to end Reaches its expected status
Record kept Update your per-environment migration log Applied migrations recorded with date and operator

If the storage-metadata cross-check diverges from the migration's own count, do not continue. Restore from backup and investigate. Proceeding on a half-applied data migration produces inconsistency that is far harder to unpick later than a restore is now.

Troubleshooting

Symptom Cause Action
Migration reports success; nothing changed Scope not elevated, or elevated in a different batch from the statement Elevate in the same batch. Re-run and verify against storage metadata
Application refuses to start, naming an object That migration has not been applied Apply it and restart. This is the safety net working
Migration fails on a permissions error The application login lacks rights to create or alter objects Grant rights within this database only, not at server level
Migration fails partway An error mid-script, with earlier statements committed Restore from backup rather than attempting a manual repair, unless the script is transactional and rolled back cleanly
After migration, a user sees no data Elevation left set, or a policy predicate wrong Verify policies and scope. Never disable a policy to restore visibility
After migration, a user sees another tenant's data A policy is missing on a new table, or a predicate is wrong Treat as a security incident. Escalate immediately
Unsure which migrations have been applied No ledger exists Compare the object catalogue against what each migration creates. Then start keeping a log
Migration takes far longer than rehearsed Production data volume differs from the rehearsal environment Rehearse against representative volume. A rehearsal on an empty database predicts nothing about duration