Database as source of truth: refactor the data, not the code

You inherit a 14-year-old WordPress site from a developer who left two contracts ago. You pull the repo, set up the local stack, and grep for the broken hero behaviour. Nothing. Two hours in, after dumping a row out of wp_options, you find a 4 KB serialized blob of Elementor settings overriding the template you have been editing all afternoon. On this site, as on most legacy PHP work, the database is the source of truth and the code is just a viewer.

This is the part of legacy site work that does not show up in onboarding documents. A meaningful share of behaviour in WordPress, Drupal, Joomla, Magento, and most custom CMS work lives in MySQL rows, not in versioned files. If you refactor the repo without reading the database first, you are refactoring the wrong artefact.

Where the logic actually lives

A short tour of the usual suspects:

• WordPress. wp_options holds site config, but also the entire serialised state of every plugin that ever set autoload = 'yes'. wp_postmeta holds ACF fields, Yoast metadata, and (most painfully) full page-builder layouts as one giant string per post.
• Drupal 7. The variable table is a key-value dump of half the site. Views, blocks, and field configurations are read from cache_* tables at runtime.
• Drupal 8+. The config table holds active configuration as blobs. Code-in-files is only the import source.
• Magento 1 and 2. core_config_data drives payment methods, shipping, tax, currency, and most module toggles. EAV tables drive product structure. The PHP module is a renderer of these rows.
• Custom PHP. A settings table. A features table. A templates table where someone stored HTML rows in 2014 and forgot.

If you treat the repo as the source of truth, you will spend a week tracing why a hook fires twice, when the answer is a row in a settings table you have not opened.

The read-only sweep

Before touching any PHP, do a data archaeology pass. The goal is to understand what the database is currently telling the code to do. On WordPress, the first query I run on a new site is the autoload audit:

SELECT option_name, LENGTH(option_value) AS bytes
FROM   wp_options
WHERE  autoload = 'yes'
ORDER  BY bytes DESC
LIMIT  20;

Every WordPress request loads every autoloaded option into memory. A 2 MB row from a plugin that was deactivated three years ago is still being unserialised on every page view. I have found single rows over 8 MB in the wild. The wp_load_alloptions reference is worth re-reading before you assume the cache layer is the bottleneck.

On Magento, the equivalent is:

SELECT scope, scope_id, path, value
FROM   core_config_data
WHERE  path LIKE 'payment/%'
   OR  path LIKE 'carriers/%';

You will usually find three payment methods enabled that the front-end has not exposed since 2019, plus a shipping rate hardcoded for a warehouse that closed.

On Drupal 8+, dump the active config and diff it against whatever is in config/sync in the repo:

drush config:export --destination=/tmp/config-snapshot
diff -ruN config/sync /tmp/config-snapshot

The delta is the gap between what the code thinks the site is and what the site actually is. That gap is the size of your real refactor.

Refactoring with the database in the frame

Once you accept the database as the source of truth, the order of operations on a destructive refactor changes. Removing a deprecated Magento payment module is no longer "delete the files, deploy, hope". It is:

1. Snapshot. A mysqldump --single-transaction --quick of production, stored somewhere you trust.
2. Map the rows. Which rows in which tables actually control the thing you want to change.
3. Mutate the data with the same care you would mutate the code. Write the UPDATE, review the WHERE clause, run it inside a transaction, eyeball the affected row count before commit.
4. Then, and only then, touch PHP.

For the Magento case, that means a row update before the file deletion:

START TRANSACTION;

UPDATE core_config_data
SET    value = '0'
WHERE  path  = 'payment/checkmo/active'
  AND  scope = 'default';

-- inspect: should be exactly 1 row affected
ROLLBACK;  -- or COMMIT once you are sure

If you skip the data step, you will deploy a release where the admin panel still advertises a payment method whose backing class no longer exists. The customer will hit checkout. The autoloader will fatal. The order will not place. You will see this at 23:41 on a Friday.

Treating SQL writes like code commits

The hardest shift is treating SQL writes with the same review weight as PHP commits. A pull request that changes functions.php goes through review. A WP-CLI command that runs UPDATE wp_options SET option_value = ... on production usually does not. Both change site behaviour. Only one leaves a paper trail.

The fix is operational, not technical. Write the SQL into a migration file, even a hand-rolled one. Commit it. Tag it. Run it through the same pipeline as the code change. If your CMS does not have a migrations system you trust (Drupal does, Magento sort of does, WordPress does not), keep a folder of dated .sql files and a small runner that records what ran where. Crude beats untracked, every time.

The same logic applies to fixtures. A staging database that is six months behind production is not a staging database, it is a fiction. Snapshot production weekly, scrub the personal data, restore into staging. Otherwise your refactor is being validated against a site that does not exist any more.

Less time in the viewer

The practical consequence of all of this is that a real legacy refactor spends more hours in MySQL than in PHP. That feels wrong the first time, because the file editor is where the muscle memory is. It stops feeling wrong once the third Friday-evening fatal has been caused by a row nobody reviewed.

When we built Pier we ran into this on every customer site, and we ended up putting the MySQL editor next to the file editor with the same version history applied to both. A row edit and a template edit now show up on the same timeline, which removes the worst class of "where did this come from" question from the work.

The smallest thing to do today: open your production database, run the autoload-size query above, and look at the first three rows. They will tell you more about what your site is doing than the last three commits did.