🪄 Revelio Schema! — Fresh Installs, Migrations, and Debian 13 Checks (May 2026)

in Mediabot · started by TeuK · 16h ago

TeuK · 16h ago

Overview

After the recent Protego Maxima work on the Claude/AI integration, the next step was less flashy but just as important: making sure Mediabot can be installed, upgraded, and validated without guessing what the database is supposed to look like.

The previous round focused on hardening !ai: cleaner output routing, per-nick rate limiting, prompt/history handling, and dedicated regression tests. This round focuses on the ground underneath it: schema consistency, migrations, live-test reliability, and Debian 13 compatibility.

“Revelio Schema! The hidden drift is revealed before it can bite.” ✨

Why this matters

Mediabot has grown a lot: channel moderation, public commands, user management, Partyline, metrics, URL title handling, radio helpers, Claude/AI, reminders, aliases, karma, and more.

That also means the database schema is no longer a small detail.

A fresh install can work perfectly while an older upgraded database silently misses one table, one column, or one reference row. That kind of drift is painful because the bot may start fine, then fail only when a specific command path is used.

This update is about making that failure mode visible before runtime.

🧱 R1 — One reference schema

The live test setup used to carry its own schema file:

t/live/schema_test.sql

That was becoming dangerous because it could drift away from the real install schema.

The new approach is simpler and safer:

install/mediabot.sql        # single reference schema
t/live/fixtures.sql         # live-test data only
t/test_live.pl              # loads schema, then fixtures

install/mediabot.sql is now the source of truth for both fresh installs and live tests.

fixtures.sql intentionally contains only data. It does not duplicate table definitions.

Result: new tables such as REMINDERS, BOT_ALIAS, KARMA, USER_SEEN, CHANNEL_BAN, and the Claude chanset are picked up automatically by live tests.

🧪 R2 — Live tests now follow the real installer schema

t/test_live.pl now loads:

install/mediabot.sql
t/live/fixtures.sql

instead of maintaining a separate test schema.

That matters because live tests are supposed to simulate a real installation as closely as possible. If they use a separate schema, they can accidentally validate a world that no longer exists.

The fixtures remain intentionally small:

-- Test network
-- Test server
-- Test channel
-- Test owner account
-- Test SQL command

No CREATE TABLE statements belong there.

🧭 R3 — Schema drift checker hardened

The official validation tool is:

perl tools/check_schema_drift.pl --conf=mediabot.conf --strict

It compares the live database against:

install/mediabot.sql

It reports:

missing tables;
extra tables;
missing columns;
extra columns;
optional normalized type differences with --types.

It is intentionally safe:

it does not modify the database;
it does not print the database password;
it does not generate DROP TABLE or DROP COLUMN;
--generate-migration only produces reviewable SQL for missing objects.

Example:

cd /home/mediabot/mediabot_v3 || exit 1
perl tools/check_schema_drift.pl --conf=mediabot.conf --strict

For previewing possible SQL:

perl tools/check_schema_drift.pl --conf=mediabot.conf --generate-migration

🐬 R4 — Debian 13 reality check: DBD::MariaDB support

The Debian 13 fresh install exposed a useful compatibility issue.

On some Debian 13 systems, DBI may provide:

DBD::MariaDB

but not:

DBD::mysql

The checker now supports this correctly.

Driver resolution is automatic:

DBD::mysql    if available
DBD::MariaDB  otherwise

The DSN is also adapted for MariaDB: when using host=localhost, the checker avoids passing an explicit port because DBD::MariaDB treats localhost as a socket-style connection.

Charset handling was also made driver-neutral. Instead of relying on driver-specific connect attributes, the checker uses:

SET NAMES utf8mb4;

after connecting.

Result: the schema checker works on a clean Debian 13 install using the driver that Debian actually provides.

🗃️ R5 — Migrations documented properly

Fresh installs use:

install/mediabot.sql

Existing installations must apply migrations from:

install/migrations/

Important current migrations include:

20260502_channel_ban.sql
20260502_user_seen.sql
mediabot_fun_commands_migration_20260512.sql
20260515_claude_chanset.sql

The newer fun/user command migration adds schema support for:

REMINDERS
BOT_ALIAS
KARMA

The Claude chanset migration adds the reference gate used by the ai command.

This is important because tools/check_schema_drift.pl checks schema structure. Reference data migrations, such as adding a chanset row, still need to be applied explicitly when upgrading an existing database.

Recommended SQL client flow:

mysql -u root -p --default-character-set=utf8mb4

Then inside the client:

SET NAMES utf8mb4;
USE mediabot;
SOURCE /home/mediabot/mediabot_v3/install/migrations/mediabot_fun_commands_migration_20260512.sql;
SOURCE /home/mediabot/mediabot_v3/install/migrations/20260515_claude_chanset.sql;

Then validate:

perl tools/check_schema_drift.pl --conf=mediabot.conf --strict

🤖 R6 — Claude/AI configuration made explicit

The sample configuration now includes an [anthropic] section.

That means the ai command is no longer an invisible feature that fails mysteriously if the API settings are missing.

Example:

[anthropic]
API_KEY=
API_URL=https://api.anthropic.com/v1/messages
API_VERSION=2023-06-01
MODEL=claude-haiku-4-5-20251001
MAX_TOKENS=400
MAX_PRIVMSG=4
WRAP_BYTES=400
SLEEP_US=750000
SYSTEM_PROMPT=You are a helpful IRC assistant. Be concise.

The Claude chanset is now present in the reference schema and has its own migration for existing installations.

✅ Fresh install result

The Debian 13 fresh install path now validates the important runtime path:

configure works
database loads
bot starts
IRC connection works
owner registration works
login works
Partyline works
schema drift checker works

That is the important difference between “the code looks fine” and “a real user can install this on a clean machine”.

📦 Files changed

Area	Files
Schema checker	`tools/check_schema_drift.pl`
Reference schema	`install/mediabot.sql`
Migrations	`install/migrations/*.sql`
Migration docs	`docs/DB_MIGRATIONS.md`, `install/migrations/README.md`, `README.md`
Live tests	`t/test_live.pl`, `t/live/fixtures.sql`
Sample config	`mediabot.sample.conf`

Recommended validation commands

After a fresh install or upgrade:

cd /home/mediabot/mediabot_v3 || exit 1

perl -c mediabot.pl
find Mediabot -name '*.pm' -print -exec perl -I. -c {} \;

perl tools/check_schema_drift.pl --conf=mediabot.conf
perl tools/check_schema_drift.pl --conf=mediabot.conf --strict

For live testing:

perl t/test_live.pl --server localhost --channel '#testchan' --verbose

Final thoughts

This update is not just about adding another tool.

It makes the installation and upgrade story more honest:

the schema has one source of truth;
live tests use the real schema;
migrations are documented;
drift is visible before runtime;
Debian 13 driver differences are handled cleanly.

That is the kind of boring magic that keeps an IRC bot alive for years.

“Mischief managed — but this time, the database signed the map too.” 🪄

You must be logged in to reply.