OPS-008
A migration runner that recognises hand-applied migrations
Operators run SQL by hand. If the runner only trusts its own table, it will later insist fifteen applied migrations are pending and lock them out.
Every runner records what it has applied. The gap is what happens when someone pastes a migration into phpMyAdmin — which operators do, routinely, when a release lands and they are impatient.
The schema now has the change and the migrations table does not. The next deploy either re-runs the file (duplicate key, half-applied mess) or a pending-migrations guard decides the install is fifteen versions behind and refuses to let anyone in.
The fix is to let a migration describe how to recognise itself. Parse the file for the tables, columns and settings it would create and check whether they already exist; if they do, record it as applied with a note. Data-only migrations have no schema fingerprint, so they carry an explicit marker instead.
/**
* Decide whether an unrecorded migration is in fact already applied.
* Returns true when the schema already shows everything the file creates.
*/
function migration_already_applied(PDO $pdo, string $sql): bool
{
// An explicit fingerprint wins: data-only files declare their own.
// -- @detect: SELECT 1 FROM settings WHERE k = 'free_spin_every'
if (preg_match('~--\s*@detect:\s*(.+)~i', $sql, $m)) {
try {
return (bool) $pdo->query(trim($m[1]))->fetchColumn();
} catch (PDOException $e) {
return false;
}
}
$wantTables = $wantColumns = $wantSettings = [];
if (preg_match_all('~CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?(\w+)`?~i', $sql, $m)) {
$wantTables = $m[1];
}
if (preg_match_all('~ALTER\s+TABLE\s+`?(\w+)`?\s+ADD\s+(?:COLUMN\s+)?`?(\w+)`?~i', $sql, $m, PREG_SET_ORDER)) {
foreach ($m as $hit) {
$wantColumns[] = [$hit[1], $hit[2]];
}
}
// Only count settings keys when the file does nothing else — otherwise a
// table+settings migration reads as applied the moment the table exists.
if (!$wantTables && !$wantColumns
&& preg_match_all("~INSERT[^;]*INTO\s+settings[^;]*?\('([^']+)'~i", $sql, $m)) {
$wantSettings = $m[1];
}
if (!$wantTables && !$wantColumns && !$wantSettings) {
return false; // nothing to recognise
}
foreach ($wantTables as $t) {
if (!table_exists($pdo, $t)) {
return false;
}
}
foreach ($wantColumns as [$t, $c]) {
if (!column_exists($pdo, $t, $c)) {
return false;
}
}
foreach ($wantSettings as $k) {
$st = $pdo->prepare('SELECT 1 FROM settings WHERE k = ?');
$st->execute([$k]);
if ($st->fetchColumn() === false) {
return false;
}
}
return true;
}Using it
Record auto-detected files with a note saying so, and show that note in the admin schema screen. An operator should be able to see which ones were adopted rather than run.
Keep a manual "mark as applied" button for the cases detection cannot cover. It beats having them edit the table by hand.
A migration that a later file deletes from (a retired settings key) will never detect. Exclude keys that later migrations remove.
What bites people
Detection must be conservative. Wrongly marking a partly-applied file as done leaves a schema that is missing half a change with nothing to tell you.
Regex over SQL is approximate by nature. It is a recovery mechanism, not the primary path — the migrations table is still the record.
Run detection only for files not already recorded. Never re-check applied ones.
This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.