Skip to main content

Drift detection rollout

Turning on drift detection against a database that has existed for years is the moment this feature is usually lost.

The first run does not meet a clean schema. It meets every decision anybody made outside a migration since the project started — the index somebody added during an incident, the column a data fix left behind, the trigger a DBA wrote in 2019. On a mature database that is not five findings; it can be a hundred. If that first run is a red build, the fix that ships is switching the check off, and it never comes back on.

So the rollout is three steps, in this order, and step 1 is not optional.

Step 1 — Look, block nothing​

sqlens:drift reports by default. That is deliberate and it is the whole reason the feature survives contact with a real database.

php artisan sqlens:drift --format=json > drift-first-run.json

Read it. Do not act on it yet. What you are looking at is a list of every place your database and your migration history disagree, sorted into three classes:

  • unexpected in database — it exists live and no migration describes it,
  • missing in database — a migration describes it and it is not there,
  • divergent — both sides have it, under different definitions.

Expect the first class to dominate, and expect most of it to be legitimate. That is not a problem with your database; it is the accumulated history of a system people kept running.

The exit code stays 0 in this mode however much it found. Nothing is blocked, so there is nothing to switch off.

Step 2 — Write down what you accept, and why​

Every difference you look at is one of two things: something you meant, or something you did not. This step is for the first kind.

php artisan sqlens:drift --update-excludes

That writes sqlens-drift-excludes.json in your repository root, one entry per current difference, and then ends the run. It does not also report — a command that gated on the very differences it had just written could never fail on the run that changed anything.

The file it writes is not finished, and it says so in every entry. Each reason carries a placeholder that spells out what is missing:

{
  "schema_version": 1,
  "entries": [
    {
      "type": "index",
      "name": "public.orders_created_at_idx",
      "attribute": null,
      "reason": "unexplained: replace this with why the difference is deliberate, or delete the entry"
    }
  ]
}

Replace it with the actual reason:

{
  "schema_version": 1,
  "entries": [
    {
      "type": "index",
      "name": "public.orders_created_at_idx",
      "attribute": null,
      "reason": "added during the 2024-11 incident; keeping it, migration deliberately not written"
    }
  ]
}

Until you do, the next run refuses to start. Missing, empty and still-placeholder are treated as one fact — an exclusion whose reason is a placeholder is not a decision, and the reader will not pretend otherwise.

That refusal is the point of the step. An exclusion without a reason cannot be reviewed, and six months from now nobody can tell a deliberate decision from a silenced finding. Filling them in is the work; the generator only saves you the typing.

Re-running --update-excludes later is safe: existing reasons win. A regeneration adds entries for new differences and never overwrites a sentence somebody wrote.

Commit the file. It is a decision log, and it belongs in review like any other.

A stale entry ends the run. Once an exclusion stops covering anything — the object was finally dropped, or the migration was finally written — the next sqlens:drift refuses with Misconfiguration and names it. An entry that protects nothing while still looking like a decision is how an exclude file rots, and forty of them are indistinguishable from four that are still true.

Step 3 — Fix the rest, then turn on the gate​

What is left after step 2 is drift you did not mean. Each one becomes a migration — either writing the object your database already has, or dropping the object your history says should not exist.

Re-run step 1 as you go. When the report is empty except for your accepted exclusions, and not before:

php artisan sqlens:drift --fail-on-drift

Now a difference exits 1, and your build goes red the day somebody changes production by hand.

Put it on a schedule or in CI, not in the deploy script. The expectation side replays your whole migration history into a throwaway database; how long that takes is a fact about your history rather than about SQLens, which is why this command deliberately carries no time budget. If you want a comparison inside a deploy, that is sqlens:postdeploy --expect-shadow.

Why the order cannot be rearranged​

Every shortcut here has been taken by somebody, and each fails the same way:

  • Straight to --fail-on-drift. The first run is red, on a database nobody has done anything wrong to today. The check gets removed, not investigated.
  • --update-excludes before reading. You accept a hundred differences you never looked at, with no reasons, and the file becomes a permanent blindfold that looks like governance.
  • Fixing before excluding. You write migrations for objects you were always going to keep, because with a hundred findings in front of you there is no way to tell which is which.

The order exists to make each step small enough to finish.

Privileges for this command​

The live side reads catalogs only — same as post-deploy verification, no write privilege of any kind.

The expectation side creates and drops a database, so on the shadow host it needs CREATEDB (PostgreSQL) or CREATE/DROP on a name prefix it owns (MySQL). Point it at a scratch server. The production guard will refuse a production connection anyway, and that guard is a backstop rather than the reason.

What this never becomes​

Drift detection tells you two readings disagree. It does not tell you which side is right — that a table exists in the database and in no migration is a fact; whether to write the migration or drop the table is a decision, and it stays yours.