Commit: a191682
Version: 3.4dev-20260823_113810
Branch: master
Status: pushed and synchronized with origin/master

---

Why MB695 mattered

MB695 was not a feature round. It was a release-hardening round focused on one difficult problem: making a long-lived Mediabot database converge safely with the current canonical schema without destroying historical data or hiding genuine drift.

The initial audit identified 27 historical schema differences. Before applying any reconciliation, the data was checked for blockers such as negative values before unsigned conversions, duplicate prefixes before unique indexes, invalid integer ranges, zero dates, nullability problems and orphan references.

The result was reassuring:

27 historical drift issues
26 candidates for reconciliation
1 compatibility column deliberately preserved
0 data blockers

That preserved column is:

USER.hostmasks_legacy

It still contains compatibility information on the long-lived database, so MB695 explicitly refuses to discard it.

---

A fail-closed reconciliation migration

MB695 adds:

install/migrations/20260823_legacy_schema_reconciliation.sql

The migration is deliberately defensive. It normalizes the audited historical schema only after proving that existing data can safely tolerate the change.

It covers the relevant historical differences in:

• charset and collation metadata;
• column definitions and defaults;
• the ACTIONS_QUEUE primary key;
• required prefix indexes;
• canonical foreign-key definitions and rules.

Unsafe data stops the migration instead of being silently rewritten.

Unrelated extra indexes are preserved because they may be legitimate local performance tuning, and USER.hostmasks_legacy is intentionally retained.

No Vanishing Spell was allowed anywhere near useful historical data. 🪄

---

Rehearsed before touching the live DEV database

The reconciliation path was exercised on disposable databases before being applied to the real long-lived DEV database.

Validation covered:

• fresh current schema;
• real stable 3.3 → current upgrade;
• migration replay/idempotence;
• deep table, column, index and foreign-key equivalence;
• row preservation;
• hostmasks_legacy preservation;
• cleanup of temporary migration routines.

Only after those proofs was the live DEV database reconciled.

A logical backup was created first:

/home/mediabot/backups/mb695_r5_mediabotv3_before_20260823_104451.sql.gz

SHA-256:

12a3207323c2f77ab39d9167ecdc0527c73e668caadd2efe33d7d24240e438e9

The controlled live migration passed with rows preserved, the legacy hostmask fingerprint unchanged, temporary routines cleaned, required schema/index contracts restored and the DEV bot returning normally to IRC.

The Undernet instance was untouched.

---

One exception, not a blanket blind spot

The final MB695 improvement tightened schema validation itself.

Previously, Mediabot Doctor delegated to the drift checker with:

--ignore-extra

That was too broad: tolerating one known compatibility column also meant ignoring every unrelated extra table or column.

The checker now supports a precise repeatable option:

--allow-extra-column TABLE.COLUMN

Doctor now uses exactly:

--allow-extra-column USER.hostmasks_legacy

The checker remains generic; it does not hard-code this Mediabot-specific exception.

The behavior was explicitly proved:

USER.hostmasks_legacy
    accepted

CHANNEL.auto_join made artificially extra
    detected as drift
    strict exit code = 1

So one reviewed compatibility exception can no longer hide future accidental schema drift.

---

Doctor result

After MB695:

DATABASE
  [ ok ] connected read-only
  [ ok ] session charset utf8mb4 / utf8mb4_unicode_ci
  [ ok ] MB646 achievement persistence tables present
  [ ok ] required live schema/reference data match install/mediabot.sql

MIGRATIONS
  [ ok ] 20 migration files discovered
  [ ok ] all observable migration effects present

Result: READY

The final R6 validation performed no database writes and no service restart.

---

Final regression proof

Targeted database/release contracts

9/9 files
293/293 tests
PASS

Fast lane

332/332 files
6127/6127 tests
PASS

Full suite

791/791 files
16014/16014 tests
PASS

Runtime identities stayed unchanged throughout the final read-only validation:

DEV
  MainPID   3819132 -> 3819132
  NRestarts 0       -> 0

Undernet
  MainPID   3815619 -> 3815619
  NRestarts 2       -> 2

---

What landed

The commit contains the migration, documentation, checker/Doctor changes and regression contracts:

CHANGELOG.md
VERSION
docs/DB_MIGRATIONS.md
install/migrations/20260823_legacy_schema_reconciliation.sql
install/migrations/README.md
t/cases/830_mb649_doctor_database_migrations.t
t/cases/907_mb695_legacy_schema_reconciliation.t
t/cases/908_mb695_precise_legacy_schema_exception.t
tools/check_schema_drift.pl
tools/mediabot_doctor.pl

Commit:

a191682 🧙 Seal the Long-Lived Schema Rift Before the Next Chapter

Final repository state:

HEAD -> master
origin/master -> a191682
working tree -> clean

---

Where this leaves Mediabot

MB695 closes the database-compatibility part of the pre-release hardening work.

The important result is not simply that the schema checker is green. Mediabot can now explain why it is green:

• fresh installations have a canonical schema;
• stable upgrades follow a validated migration path;
• long-lived installations have a reviewed reconciliation path;
• unsafe legacy data fails closed;
• historical compatibility data is preserved deliberately;
• local performance indexes are left alone;
• one accepted legacy column no longer masks unrelated future drift;
• Doctor remains read-only;
• the complete test suite remains green.

The castle is a little less haunted now. 🏰

Next stop: the security and secrets review before moving any closer to 3.5.