Database model

Database model

Status: generated from the current install/mediabot.sql reference schema.

This page describes the fresh-install database model for Mediabot v3. Existing production databases may contain older local data, legacy rows, or optional historical tables. Use tools/check_schema_drift.pl to compare a live database with the reference schema before assuming they are identical.

Source of truth

• Fresh install schema: install/mediabot.sql
• Existing database upgrades: install/migrations/
• Schema validation: tools/check_schema_drift.pl --conf=mediabot.conf --strict --types --indexes

Use the MySQL/MariaDB client with an explicit charset and SOURCE; avoid shell redirection when documenting imports:

SET NAMES utf8mb4;
USE mediabot;
SOURCE /home/mediabot/mediabot_v3/install/mediabot.sql;

Big picture

Mediabot's schema is organized around five main ideas:

1. Users and access levels — global users, hostmasks, global levels, and per-channel access.
2. Channels — IRC channel registry, auto-join data, channel keys/modes, feature flags, and moderation settings.
3. Command/content features — quotes, responders, reminders, karma, trivia, notes, aliases, MP3 metadata.
4. Runtime/audit data — logs, actions, web logins, timers, seen cache.
5. Reference data — user levels, chansets, networks, timezones.

Important design notes

• Most primary keys are BIGINT UNSIGNED.
• Newer text fields use utf8mb4 where the schema explicitly needs IRC nick/content safety.
• USER.hostmasks is no longer the model; hostmasks live in USER_HOSTMASK.
• WEBLOG.password is intentionally absent.
• CHANNEL_LOG.publictext is TEXT, not a small varchar.
• CHANNEL_FLOOD remains the DB-backed configuration for the legacy outgoing antiflood guard. Newer in-memory antiflood thresholds live in mediabot.conf under [antiflood].
• KARMA_LOG is part of the current reference schema. KARMA stores the current score; KARMA_LOG stores the persistent vote history used by !karmahist.
• FACTOID stores shared per-channel facts introduced in 3.3.
• QUOTES.hits and idx_quotes_channel_hits support quote hall-of-fame rankings.
• idx_channel_log_channel_ts supports channel-history and analytics queries.

Reference values seeded by the schema

User levels

id	level	description	Meaning
1	0	Owner	Highest global level.
2	1	Master	High-privilege global operator.
3	2	Administrator	Global admin-level user.
4	3	User	Normal authenticated user.

Lower numeric values mean higher privilege.

Channel feature flags

CHANSET_LIST seeds the known per-channel feature flags:

Youtube, UrlTitle, Weather, YoutubeSearch, NoColors, AntiFlood, Hailo,
HailoChatter, RadioPub, Twitter, chatGPT, AppleMusic, RandomQuote, Claude,
AchievementAnnounce, Games, ChannelReport, DidYouMean, Factoids,
OnThisDay, OnThisDayDigest

These are enabled per channel through CHANNEL_SET.

Table groups

Core identity and access

Table	Primary key	Purpose	Main columns
USER	id_user	User accounts, levels, auth flags, timezone and identity metadata.	id_user, creation_date, nickname, password, username, id_user_level, info1, info2, … (13 cols)
USER_LEVEL	id_user_level	Reference levels: Owner, Master, Administrator, User.	id_user_level, level, description
USER_HOSTMASK	id_user_hostmask	User hostmask list replacing old USER.hostmasks.	id_user_hostmask, id_user, hostmask, created_at
USER_CHANNEL	id_user_channel	Per-user, per-channel access/greet/automode settings.	id_user_channel, id_user, id_channel, level, greet, automode
CHANNEL	id_channel	Canonical channel registry: name, key, modes, auto-join, owner user.	id_channel, name, creation_date, description, key, chanmode, auto_join, notice, … (11 cols)

Channel feature flags and moderation

Table	Primary key	Purpose	Main columns
CHANSET_LIST	id_chanset_list	Reference list of per-channel feature flags such as UrlTitle, AntiFlood, Claude.	id_chanset_list, chanset
CHANNEL_SET	id_channel_set	Join table linking CHANNEL to enabled CHANSET_LIST flags.	id_channel_set, id_channel, id_chanset_list
CHANNEL_FLOOD	id_channel_flood	Legacy DB-backed outgoing antiflood settings per channel.	id_channel_flood, id_channel, nbmsg_max, nbmsg, duration, first, latest, timetowait, … (9 cols)
CHANNEL_BAN	id_channel_ban	Persistent channel ban records with creator/remover metadata.	id_channel_ban, id_channel, mask, ban_level, reason, created_by, created_by_nick, created_at, … (15 cols)
BADWORDS	id_badwords	Per-channel badword patterns.	id_badwords, id_channel, badword
IGNORES	id_ignores	Per-channel hostmask ignore list.	id_ignores, id_channel, hostmask
CHANNEL_LOG	id_channel_log	Channel event/message log used by seen/activity/stat commands.	id_channel_log, id_channel, ts, event_type, nick, userhost, publictext
CHANNEL_PURGED	id_channel_purged	Archive of channels removed/purged from active CHANNEL.	id_channel_purged, id_channel, name, purge_date, description, key, chanmode, auto_join, … (10 cols)

Reference data: CHANSET_LIST

CHANSET_LIST is small reference data, but it is part of the runtime contract.

Mediabot 3.3 relies on these additional social/community chansets:

AchievementAnnounce
Games
ChannelReport
DidYouMean
Factoids
OnThisDay
OnThisDayDigest

AchievementAnnounce controls public achievement unlock announcements. Games controls playful commands such as duel, horoscope, compat, and quotegame. The remaining flags gate channel reports, typo suggestions, factoids, historical lookup and the opt-in daily historical digest.

The schema drift checker now compares expected CHANSET_LIST seed rows from install/mediabot.sql with the live database. Missing reference rows may be reported even when the table structure itself is correct.

User-facing features

Table	Primary key	Purpose	Main columns
QUOTES	id_quotes	Per-channel quote storage and recall ranking.	id_quotes, id_channel, id_user, ts, quotetext, hits
FACTOID	id_factoid	Shared per-channel keyword/value facts with authorship and recall counts.	id_factoid, id_channel, keyword, value, created_by, created_by_nick, created_at, updated_at, hits
RESPONDERS	id_responders	Per-channel responder triggers and answers.	id_responders, id_channel, hits, chance, responder, answer
PUBLIC_COMMANDS	id_public_commands	Database-backed public custom commands.	id_public_commands, id_user, id_public_commands_category, creation_date, command, description, action, hits, … (9 cols)
PUBLIC_COMMANDS_CATEGORY	id_public_commands_category	Categories for PUBLIC_COMMANDS.	id_public_commands_category, description
REMINDERS	id_reminder	Reminder storage for remind/remindlist/remindcancel/remindsnooze.	id_reminder, id_channel, from_nick, to_nick, message, created_at, delivered
BOT_ALIAS	id_alias	Public command aliases.	id_alias, alias, command, created_by, created_at
KARMA	id_karma	Per-channel karma scores by nick.	id_karma, id_channel, nick, score, updated_at
KARMA_LOG	id_karma_log	Persistent per-channel karma vote history.	id_karma_log, id_channel, nick, delta, from_nick, score, ts
TRIVIA_SCORES	id_channel, nick	Per-channel trivia score table keyed by channel and nick.	id_channel, nick, score, last_correct
NOTE	id_note	Persistent notes table.	id_note, nick, text, created_at
MP3	id_mp3	MP3/YouTube download metadata.	id_mp3, id_user, id_youtube, folder, filename, artist, title
YOMOMMA	id_yomomma	Joke/content table.	id_yomomma, yomomma

Hailo, network and infrastructure

Table	Primary key	Purpose	Main columns
HAILO_CHANNEL	id_hailo_channel	Per-channel Hailo learning/speaking ratio.	id_hailo_channel, id_channel, ratio
HAILO_EXCLUSION_NICK	id_hailo_exclusion_nick	Nicks excluded from Hailo learning/response logic.	id_hailo_exclusion_nick, nick
NETWORK	id_network	IRC network reference table.	id_network, network_name
SERVERS	id_server	IRC servers linked to NETWORK.	id_server, id_network, server_hostname
TIMEZONE	id_timezone	Reference list of timezone names.	id_timezone, tz
CONSOLE	id_console	Web/admin console menu/tree metadata.	id_console, id_parent, position, level, description, url
WEBLOG	id_weblog	Web login history/audit table without password storage.	id_weblog, login_date, nickname, ip, hostname, logresult
ACTIONS_LOG	id_actions_log	Audit log for command/action events tied optionally to users and channels.	id_actions_log, ts, id_user, id_channel, hostmask, action, args
ACTIONS_QUEUE	id_actions_queue	Queue/history table for action command results.	id_actions_queue, ts, command, params, result1, result2, result3, result4, … (11 cols)
TIMERS	id_timers	Named periodic timers/commands.	id_timers, name, duration, command

Core relationship map

From table	Column	References	On delete	Notes
ACTIONS_LOG	id_user	USER.id_user	SET NULL	fk_actions_log_user
ACTIONS_LOG	id_channel	CHANNEL.id_channel	SET NULL	fk_actions_log_channel
BADWORDS	id_channel	CHANNEL.id_channel	CASCADE	fk_badwords_channel
CHANNEL	id_user	USER.id_user	SET NULL	fk_channel_user
CHANNEL_BAN	id_channel	CHANNEL.id_channel	CASCADE	fk_channel_ban_channel
CHANNEL_BAN	created_by	USER.id_user	SET NULL	fk_channel_ban_created_by
CHANNEL_BAN	removed_by	USER.id_user	SET NULL	fk_channel_ban_removed_by
CHANNEL_FLOOD	id_channel	CHANNEL.id_channel	CASCADE	fk_channel_flood_channel
CHANNEL_LOG	id_channel	CHANNEL.id_channel	SET NULL	fk_channel_log_channel
CHANNEL_SET	id_channel	CHANNEL.id_channel	CASCADE	fk_channel_set_channel
CHANNEL_SET	id_chanset_list	CHANSET_LIST.id_chanset_list	CASCADE	fk_channel_set_chanset_list
HAILO_CHANNEL	id_channel	CHANNEL.id_channel	CASCADE	fk_hailo_channel_channel
REMINDERS	id_channel	CHANNEL.id_channel	CASCADE	fk_reminders_channel
KARMA	id_channel	CHANNEL.id_channel	CASCADE	fk_karma_channel
KARMA_LOG	id_channel	CHANNEL.id_channel	CASCADE	fk_karma_log_channel
TRIVIA_SCORES	id_channel	CHANNEL.id_channel	CASCADE	fk_trivia_scores_channel
MP3	id_user	USER.id_user	CASCADE	fk_mp3_user
PUBLIC_COMMANDS	id_user	USER.id_user	SET NULL	fk_pc_user
PUBLIC_COMMANDS	id_public_commands_category	PUBLIC_COMMANDS_CATEGORY.id_public_commands_category	RESTRICT	fk_pc_category
QUOTES	id_channel	CHANNEL.id_channel	CASCADE	fk_quotes_channel
QUOTES	id_user	USER.id_user	CASCADE	fk_quotes_user
SERVERS	id_network	NETWORK.id_network	CASCADE	fk_servers_network
TIMERS	id_channel	CHANNEL.id_channel	SET NULL	fk_timers_channel
USER	id_user_level	USER_LEVEL.id_user_level	RESTRICT	fk_user_level
USER_CHANNEL	id_user	USER.id_user	CASCADE	fk_user_channel_user
USER_CHANNEL	id_channel	CHANNEL.id_channel	CASCADE	fk_user_channel_channel
USER_HOSTMASK	id_user	USER.id_user	CASCADE	fk_user_hostmask_user

Key table details

USER, USER_LEVEL, USER_HOSTMASK

USER stores the canonical account. USER_LEVEL stores the global privilege level. USER_HOSTMASK stores one or more hostmasks per user and replaces the old single-field hostmask model.

Operational consequences:

• authentication/autologin should resolve a user through hostmask data;
• user level checks use the numeric level from USER_LEVEL; lower is stronger;
• deleting a user cascades hostmasks and per-channel rows where foreign keys are enabled.

CHANNEL, USER_CHANNEL, CHANNEL_SET

CHANNEL is the channel registry. USER_CHANNEL stores per-channel user privileges, greetings, and automode. CHANNEL_SET enables flags listed in CHANSET_LIST.

Important operational detail: IRC channel names may contain mixed case, such as #WatchDog.Test. Code must not assume that a channel cache is lowercase-only.

CHANNEL_FLOOD and newer antiflood code

CHANNEL_FLOOD stores the older per-channel outgoing antiflood settings:

• nbmsg_max
• duration
• timetowait

The newer in-memory guards, such as per-nick and global channel input throttling, are configured in mediabot.conf under [antiflood] and do not require schema changes.

CHANNEL_LOG and USER_SEEN

CHANNEL_LOG is the historical event/message log. USER_SEEN is a compact last-seen cache keyed by nick. Retention is controlled by configuration keys, notably:

• CHANNEL_LOG_RETENTION_DAYS
• USER_SEEN_RETENTION_DAYS

Production instances that preserve history may intentionally set these to 0.

REMINDERS

REMINDERS stores pending and delivered reminders. Recent commands such as remindcancel all and remindsnooze operate on this table without schema changes.

KARMA, KARMA_LOG and TRIVIA_SCORES

KARMA stores the current per-channel score for a nick. KARMA_LOG stores the persistent history of karma votes (nick, delta, from_nick, resulting score, and timestamp) used by !karmahist. TRIVIA_SCORES stores trivia scoreboard data.

KARMA_LOG was introduced by install/migrations/20260603_karma_log.sql and is also included in the fresh-install schema. Older databases must apply the migration if they want persistent karma history across restarts.

NOTE

NOTE is the persistent notes table in the reference schema. In-memory note helpers may also use runtime structures; keep docs and code clear about which behavior is persistent.

2026-06 karma history migration

Existing databases created before the KARMA_LOG table was added should apply:

SET NAMES utf8mb4;
USE mediabot;
SOURCE /home/mediabot/mediabot_v3/install/migrations/20260603_karma_log.sql;

A fresh install using the current install/mediabot.sql already includes KARMA_LOG; do not apply the migration again blindly on a new database.

Fresh install versus migration

Fresh install

For a new database, import install/mediabot.sql only, then run the schema checker:

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

Existing database

For an existing database, apply migrations in the documented order, then run the schema checker. Do not blindly re-import install/mediabot.sql over a production database.

Recommended workflow:

SET NAMES utf8mb4;
USE mediabot;
SOURCE /home/mediabot/mediabot_v3/install/migrations/20260502_channel_ban.sql;
SOURCE /home/mediabot/mediabot_v3/install/migrations/20260502_user_seen.sql;
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:

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

Note: the drift checker validates schema structure. Reference-data migrations, such as adding a chanset row, may still be needed when upgrading old databases.

Table appendix

This section is intentionally compact. Use install/mediabot.sql for exact SQL types and defaults.

ACTIONS_LOG

Purpose: Audit log for command/action events tied optionally to users and channels.

Primary key: id_actions_log

Column	Type / attributes
id_actions_log	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
ts	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
id_user	BIGINT UNSIGNED DEFAULT NULL
id_channel	BIGINT UNSIGNED DEFAULT NULL
hostmask	VARCHAR(255) NOT NULL
action	VARCHAR(255) NOT NULL
args	VARCHAR(255) DEFAULT NULL

ACTIONS_QUEUE

Purpose: Queue/history table for action command results.

Primary key: id_actions_queue

Column	Type / attributes
id_actions_queue	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
ts	DATETIME NOT NULL
command	VARCHAR(255) NOT NULL
params	VARCHAR(255) NOT NULL
result1	VARCHAR(255) DEFAULT NULL
result2	VARCHAR(255) DEFAULT NULL
result3	VARCHAR(255) DEFAULT NULL
result4	VARCHAR(255) DEFAULT NULL
result5	VARCHAR(255) DEFAULT NULL
result6	VARCHAR(255) DEFAULT NULL
status	INT(11) NOT NULL DEFAULT 0

BADWORDS

Purpose: Per-channel badword patterns.

Primary key: id_badwords

Column	Type / attributes
id_badwords	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL DEFAULT 0
badword	VARCHAR(255) NOT NULL

CHANNEL

Purpose: Canonical channel registry: name, key, modes, auto-join, owner user.

Primary key: id_channel

Column	Type / attributes
id_channel	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
name	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
creation_date	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
description	VARCHAR(255) DEFAULT NULL
key	VARCHAR(255) DEFAULT NULL
chanmode	VARCHAR(255) DEFAULT NULL
auto_join	TINYINT(1) NOT NULL DEFAULT 0
notice	VARCHAR(255) DEFAULT NULL
tmdb_lang	VARCHAR(255) NOT NULL DEFAULT 'en-US'
topic	VARCHAR(400) DEFAULT NULL
id_user	BIGINT UNSIGNED DEFAULT NULL

Unique keys:

• UNIQUE KEY name (name)

CHANNEL_BAN

Purpose: Persistent channel ban records with creator/remover metadata.

Primary key: id_channel_ban

Column	Type / attributes
id_channel_ban	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
mask	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
ban_level	INT UNSIGNED NOT NULL DEFAULT 75
reason	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
created_by	BIGINT UNSIGNED DEFAULT NULL
created_by_nick	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
expires_at	DATETIME DEFAULT NULL
active	TINYINT(1) NOT NULL DEFAULT 1
removed_by	BIGINT UNSIGNED DEFAULT NULL
removed_by_nick	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
removed_at	DATETIME DEFAULT NULL
remove_reason	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
source	VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'irc'

CHANNEL_FLOOD

Purpose: Legacy DB-backed outgoing antiflood settings per channel.

Primary key: id_channel_flood

Column	Type / attributes
id_channel_flood	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
nbmsg_max	INT(11) NOT NULL DEFAULT 5
nbmsg	INT(11) NOT NULL DEFAULT 0
duration	INT(11) NOT NULL DEFAULT 30
first	INT(11) DEFAULT 0
latest	BIGINT(20) NOT NULL DEFAULT 0
timetowait	INT(11) NOT NULL DEFAULT 300
notification	INT(11) NOT NULL DEFAULT 0

CHANNEL_LOG

Purpose: Channel event/message log used by seen/activity/stat commands.

Primary key: id_channel_log

Column	Type / attributes
id_channel_log	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED DEFAULT NULL
ts	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
event_type	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
nick	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
userhost	VARCHAR(255) NOT NULL
publictext	TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL

Important indexes:

idx_channel_log_id_channel (id_channel)
idx_channel_log_channel_ts (id_channel, ts)

CHANNEL_PURGED

Purpose: Archive of channels removed/purged from active CHANNEL.

Primary key: id_channel_purged

Column	Type / attributes
id_channel_purged	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
name	VARCHAR(255) NOT NULL
purge_date	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
description	VARCHAR(255) DEFAULT NULL
key	VARCHAR(255) DEFAULT NULL
chanmode	VARCHAR(255) DEFAULT NULL
auto_join	TINYINT(1) NOT NULL DEFAULT 0
purged_by	VARCHAR(255) DEFAULT NULL
purged_at	DATETIME DEFAULT CURRENT_TIMESTAMP

CHANNEL_SET

Purpose: Join table linking CHANNEL to enabled CHANSET_LIST flags.

Primary key: id_channel_set

Column	Type / attributes
id_channel_set	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
id_chanset_list	BIGINT UNSIGNED NOT NULL

CHANSET_LIST

Purpose: Reference list of per-channel feature flags such as UrlTitle, AntiFlood, Claude.

Primary key: id_chanset_list

Column	Type / attributes
id_chanset_list	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
chanset	VARCHAR(255) NOT NULL

Unique keys:

• UNIQUE KEY chanset (chanset)

CONSOLE

Purpose: Web/admin console menu/tree metadata.

Primary key: id_console

Column	Type / attributes
id_console	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_parent	BIGINT UNSIGNED DEFAULT NULL
position	INT(11) NOT NULL DEFAULT 1
level	INT(11) NOT NULL DEFAULT 0
description	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
url	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

HAILO_CHANNEL

Purpose: Per-channel Hailo learning/speaking ratio.

Primary key: id_hailo_channel

Column	Type / attributes
id_hailo_channel	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
ratio	INT(11) NOT NULL

Unique keys:

• UNIQUE KEY id_channel (id_channel)

HAILO_EXCLUSION_NICK

Purpose: Nicks excluded from Hailo learning/response logic.

Primary key: id_hailo_exclusion_nick

Column	Type / attributes
id_hailo_exclusion_nick	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
nick	VARCHAR(255) NOT NULL

IGNORES

Purpose: Per-channel hostmask ignore list.

Primary key: id_ignores

Column	Type / attributes
id_ignores	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL DEFAULT 0
hostmask	VARCHAR(255) NOT NULL

REMINDERS

Purpose: Reminder storage for remind/remindlist/remindcancel/remindsnooze.

Primary key: id_reminder

Column	Type / attributes
id_reminder	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
from_nick	VARCHAR(64) NOT NULL
to_nick	VARCHAR(64) NOT NULL
message	VARCHAR(512) NOT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
delivered	TINYINT UNSIGNED NOT NULL DEFAULT 0

BOT_ALIAS

Purpose: Public command aliases.

Primary key: id_alias

Column	Type / attributes
id_alias	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
alias	VARCHAR(32) NOT NULL
command	VARCHAR(64) NOT NULL
created_by	VARCHAR(64) DEFAULT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Unique keys:

• UNIQUE KEY uniq_bot_alias_alias (alias)

KARMA

Purpose: Per-channel karma scores by nick.

Primary key: id_karma

Column	Type / attributes
id_karma	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
nick	VARCHAR(64) NOT NULL
score	INT NOT NULL DEFAULT 0
updated_at	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Unique keys:

• UNIQUE KEY uniq_karma_channel_nick (id_channel, nick)

KARMA_LOG

Persistent karma vote history used by !karmahist.

Important columns:

Column	Meaning
id_karma_log	Primary key
id_channel	Channel id
nick	Nick whose karma changed
delta	Vote delta, normally +1 or -1
from_nick	Nick who cast the vote
score	Score after the vote
ts	Vote timestamp

MP3

Purpose: MP3/YouTube download metadata.

Primary key: id_mp3

Column	Type / attributes
id_mp3	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_user	BIGINT UNSIGNED NOT NULL
id_youtube	VARCHAR(255) NOT NULL
folder	VARCHAR(255) NOT NULL
filename	VARCHAR(255) NOT NULL
artist	VARCHAR(255) NOT NULL
title	VARCHAR(255) NOT NULL

NETWORK

Purpose: IRC network reference table.

Primary key: id_network

Column	Type / attributes
id_network	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
network_name	VARCHAR(255) NOT NULL

Unique keys:

• UNIQUE KEY network_name (network_name(191))

PUBLIC_COMMANDS

Purpose: Database-backed public custom commands.

Primary key: id_public_commands

Column	Type / attributes
id_public_commands	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_user	BIGINT UNSIGNED DEFAULT NULL
id_public_commands_category	BIGINT UNSIGNED NOT NULL
creation_date	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
command	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
description	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
action	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
hits	BIGINT UNSIGNED NOT NULL DEFAULT 0
active	TINYINT(1) NOT NULL DEFAULT 1

Unique keys:

• UNIQUE KEY command (command(191))

PUBLIC_COMMANDS_CATEGORY

Purpose: Categories for PUBLIC_COMMANDS.

Primary key: id_public_commands_category

Column	Type / attributes
id_public_commands_category	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
description	VARCHAR(255) NOT NULL

QUOTES

Purpose: Per-channel quote storage.

Primary key: id_quotes

Column	Type / attributes
id_quotes	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
id_user	BIGINT UNSIGNED NOT NULL
ts	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
quotetext	VARCHAR(255) NOT NULL
hits	BIGINT UNSIGNED NOT NULL DEFAULT 0

Important index:

idx_quotes_channel_hits (id_channel, hits)

FACTOID

Purpose: Shared per-channel facts used by !learn, !whatis, !factoid, !factoids, !forget and ?keyword.

Primary key: id_factoid

Column	Type / attributes
id_factoid	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL
keyword	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
value	VARCHAR(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
created_by	BIGINT UNSIGNED DEFAULT NULL
created_by_nick	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
hits	BIGINT UNSIGNED NOT NULL DEFAULT 0

Important keys:

UNIQUE KEY uniq_factoid_channel_keyword (id_channel, keyword)
KEY idx_factoid_created_by (created_by)

RESPONDERS

Purpose: Per-channel responder triggers and answers.

Primary key: id_responders

Column	Type / attributes
id_responders	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_channel	BIGINT UNSIGNED NOT NULL DEFAULT 0
hits	BIGINT UNSIGNED NOT NULL DEFAULT 0
chance	BIGINT UNSIGNED NOT NULL DEFAULT 95
responder	VARCHAR(255) DEFAULT NULL
answer	TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL

SERVERS

Purpose: IRC servers linked to NETWORK.

Primary key: id_server

Column	Type / attributes
id_server	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_network	BIGINT UNSIGNED NOT NULL
server_hostname	VARCHAR(255) NOT NULL

TIMERS

Purpose: Named periodic timers/commands.

Primary key: id_timers

Column	Type / attributes
id_timers	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
name	VARCHAR(255) NOT NULL
duration	INT(11) NOT NULL
command	VARCHAR(255) NOT NULL

Unique keys:

• UNIQUE KEY name (name(191))

TIMEZONE

Purpose: Reference list of timezone names.

Primary key: id_timezone

Column	Type / attributes
id_timezone	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
tz	VARCHAR(255) NOT NULL

USER

Purpose: User accounts, levels, auth flags, timezone and identity metadata.

Primary key: id_user

Column	Type / attributes
id_user	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
creation_date	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
nickname	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
password	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
username	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
id_user_level	BIGINT UNSIGNED NOT NULL
info1	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
info2	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
last_login	DATETIME NULL DEFAULT NULL
auth	TINYINT(1) NOT NULL DEFAULT 0
tz	VARCHAR(255) DEFAULT NULL
birthday	VARCHAR(255) DEFAULT NULL
fortniteid	VARCHAR(255) DEFAULT NULL

Unique keys:

• UNIQUE KEY nickname (nickname)

USER_SEEN

Purpose: Last-seen cache by nick/channel.

Primary key: nick

Column	Type / attributes
nick	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
channel	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
userhost	VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
event_type	ENUM('message','join','part','quit','nick') NOT NULL DEFAULT 'message'
last_msg	VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
new_nick	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
seen_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

USER_CHANNEL

Purpose: Per-user, per-channel access/greet/automode settings.

Primary key: id_user_channel

Column	Type / attributes
id_user_channel	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_user	BIGINT UNSIGNED NOT NULL
id_channel	BIGINT UNSIGNED NOT NULL
level	BIGINT UNSIGNED NOT NULL DEFAULT 0
greet	VARCHAR(255) DEFAULT NULL
automode	VARCHAR(255) NOT NULL DEFAULT 'NONE'

Unique keys:

• UNIQUE KEY uc_user_channel (id_user, id_channel)

USER_HOSTMASK

Purpose: User hostmask list replacing old USER.hostmasks.

Primary key: id_user_hostmask

Column	Type / attributes
id_user_hostmask	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
id_user	BIGINT UNSIGNED NOT NULL
hostmask	VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

USER_LEVEL

Purpose: Reference levels: Owner, Master, Administrator, User.

Primary key: id_user_level

Column	Type / attributes
id_user_level	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
level	INT(11) NOT NULL
description	VARCHAR(255) NOT NULL

WEBLOG

Purpose: Web login history/audit table without password storage.

Primary key: id_weblog

Column	Type / attributes
id_weblog	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
login_date	DATETIME NOT NULL
nickname	VARCHAR(255) NOT NULL
ip	VARCHAR(255) NOT NULL
hostname	VARCHAR(255) DEFAULT NULL
logresult	TINYINT(1) NOT NULL

YOMOMMA

Purpose: Joke/content table.

Primary key: id_yomomma

Column	Type / attributes
id_yomomma	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
yomomma	VARCHAR(255) NOT NULL

TRIVIA_SCORES

Purpose: Per-channel trivia score table keyed by channel and nick.

Primary key: id_channel, nick

Column	Type / attributes
id_channel	BIGINT UNSIGNED NOT NULL
nick	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
score	BIGINT UNSIGNED NOT NULL DEFAULT 0
last_correct	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

NOTE

Purpose: Persistent notes table.

Primary key: id_note

Column	Type / attributes
id_note	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
nick	VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
text	VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
created_at	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

---

Reference data used by runtime features

CHANSET_LIST is runtime reference data, not just user content.

Important current entries include:

AchievementAnnounce
Games
ChannelReport
DidYouMean
Factoids
OnThisDay
OnThisDayDigest
UrlTitle
Youtube
YoutubeSearch
RandomQuote
Claude
NoColors
AntiFlood

The schema drift checker now compares these seeds against the live database and can report missing reference rows.

This matters because code may depend on CHANSET_LIST entries even when the table structure is correct.