Two earlier posts in this series each landed on the same defensive pattern independently. The original index-hint rewrite checked whether its target index existed at runtime, on every request, before deciding whether to rewrite a query — because a schema migration and a code deploy aren’t guaranteed to land atomically, and referencing a nonexistent index isn’t a slow query, it’s a hard error. The later index-migration rollout used the same shape of check — status before and after ensure, per site — to confirm a batch of five indexes had actually landed before trusting the network was in the expected state.
Both are the same pattern, applied by hand, once each. This is that pattern turned into something reusable: a pre-deploy CI gate that checks index existence across an entire network automatically, so the next migration doesn’t need its own bespoke runtime guard written from scratch.
TL;DR: The runtime existence-check that kept one index rewrite safe to deploy in either order was written by hand, once. This post turns that same pattern into a reusable pre-deploy CI gate that checks index existence across an entire network automatically — kept alongside the runtime guard, not instead of it, since the two cover different failure windows.
What Does the Runtime Guard Look Like?
The original pattern lives inside application code, checked live, with a request-scoped cache so it only costs one query per request rather than one per invocation:
function target_index_exists( string $table, string $index_name ): bool {
static $cache = [];
$key = "{$table}:{$index_name}";
if ( isset( $cache[ $key ] ) ) {
return $cache[ $key ];
}
global $wpdb;
$exists = (bool) $wpdb->get_var(
$wpdb->prepare(
"SELECT 1 FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = %s
AND INDEX_NAME = %s
LIMIT 1",
$table,
$index_name
)
);
return $cache[ $key ] = $exists;
}
This is the belt: if the index isn’t there yet, the code degrades gracefully to its slower-but-correct fallback instead of hard-failing. It’s exactly right for code that has to keep working across a deploy window where the two halves — schema and code — might land in either order. It is not, by itself, a way to know before a deploy whether the schema side is actually ready across an entire network. For that, checking at runtime, request by request, is the wrong tool — you want an answer before the deploy proceeds, not a graceful degradation discovered after it already has.
How Do You Move the Same Check Earlier and Widen It?
The CI-gate version asks the identical question — does this index exist — but runs it once, deliberately, against every site in the network, before a deploy that depends on the answer is allowed to proceed:
#!/usr/bin/env php
<?php
/**
* Pre-deploy gate: exits non-zero if any required index is missing
* on any site in the network. Meant to run as a CI step before a
* deploy that assumes these indexes already exist.
*/
require_once __DIR__ . '/wp-load.php';
$required_indexes = [
'posts' => [ 'idx_sitemap_modified_gmt' ],
'postmeta' => [ 'idx_focus_keyword' ],
];
$missing = [];
foreach ( get_sites( [ 'number' => 0 ] ) as $site ) {
switch_to_blog( $site->blog_id );
global $wpdb;
foreach ( $required_indexes as $table_suffix => $indexes ) {
$table = $wpdb->{$table_suffix};
foreach ( $indexes as $index_name ) {
if ( ! target_index_exists( $table, $index_name ) ) {
$missing[] = "{$site->domain}: {$table}.{$index_name}";
}
}
}
restore_current_blog();
}
if ( ! empty( $missing ) ) {
fwrite( STDERR, "Missing required indexes:\n" . implode( "\n", $missing ) . "\n" );
exit( 1 );
}
echo "All required indexes present across " . count( get_sites( [ 'number' => 0 ] ) ) . " sites.\n";
exit( 0 );
Wired into a deploy pipeline as a gate ahead of any release that assumes a given index exists, this converts "the index probably shipped already, right?" from an assumption into a checked precondition — the deploy simply doesn’t proceed if any site in the network is missing something the incoming code depends on. Which is precisely the situation the version-skew incident turned into a near-miss: a branch further behind than assumed, discovered only because someone happened to check state directly rather than trusting what should have been true.
Why Keep Both the CI Gate and the Runtime Guard?
Keeping the runtime guard and adding the CI gate isn’t redundant — they cover different failure windows. The CI gate catches "we’re about to deploy and the schema isn’t ready" before it happens. The runtime guard catches everything the CI gate can’t: a new site added to the network after the gate last ran, a manual schema change reverted out of band, a migration that partially failed on one site without anyone noticing before the next deploy. Removing the runtime check because "CI already verified this" reintroduces exactly the hard-error deploy trap the original post was about — the CI gate is a snapshot at one point in time, not a standing guarantee.
What’s the Generalizable Lesson Here?
- A defensive check written once, inline, for one migration is worth generalizing into a reusable gate the moment a second migration needs the same protection — the pattern doesn’t change, only the specific table and index names do.
- A pre-deploy CI gate and a runtime existence guard solve different problems: one prevents deploying into a known-bad state, the other keeps the system correct when the state turns out to be bad anyway. Keep both; they’re not substitutes for each other.
- Any check that’s worth doing manually once, by hand, during an incident is worth automating the moment you can articulate exactly what it checked — "does this index exist, on every site" is a five-line SQL query away from being a script instead of a memory.
- A CI gate should fail loud and specific — which site, which table, which index — not just "precondition not met." The whole point is turning a silent assumption into an actionable failure.