core-staff/docs/04_Schema.md

Schema

Two MySQL tables, created automatically on start() via CREATE TABLE IF NOT EXISTS. Both prefixed staff_ so they don't collide with other plugins.

staff_punishments

CREATE TABLE IF NOT EXISTS staff_punishments (
    id BIGINT NOT NULL AUTO_INCREMENT,
    target_uuid CHAR(36) NOT NULL,
    target_name VARCHAR(64) NOT NULL,
    type VARCHAR(16) NOT NULL,           -- BAN, TEMPBAN, MUTE, TEMPMUTE, KICK, WARN
    reason VARCHAR(512) NOT NULL,
    issued_by_uuid CHAR(36) NOT NULL,
    issued_by_name VARCHAR(64) NOT NULL,
    issued_at BIGINT NOT NULL,           -- epoch millis
    expires_at BIGINT NULL,              -- epoch millis; NULL = permanent
    issued_on_server VARCHAR(64) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    revoked_at BIGINT NULL,
    revoked_by_uuid CHAR(36) NULL,
    revoked_by_name VARCHAR(64) NULL,
    revoke_reason VARCHAR(512) NULL,
    PRIMARY KEY (id),
    KEY idx_target_uuid (target_uuid),
    KEY idx_target_active (target_uuid, type, active),
    KEY idx_issued_at (issued_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Notes

• Rows are never deleted. Revokes flip active=0 and fill the revoked_* columns. Wipe via SQL only if you really mean to lose history.
• target_name is captured at issue time and never updated. If a player renames, history shows the old name; lookup by UUID is canonical.
• issued_on_server is the server id that issued the action. Useful for "who issued this from where" forensics; not used for enforcement.

Common queries

-- Active ban for a player
SELECT * FROM staff_punishments
WHERE target_uuid = ? AND type IN ('BAN', 'TEMPBAN')
  AND active = 1 AND revoked_at IS NULL
  AND (expires_at IS NULL OR expires_at > UNIX_TIMESTAMP() * 1000)
ORDER BY issued_at DESC LIMIT 1;

-- All actions a moderator has taken in the last 7 days
SELECT * FROM staff_punishments
WHERE issued_by_uuid = ?
  AND issued_at > (UNIX_TIMESTAMP() * 1000) - 7 * 86400000
ORDER BY issued_at DESC;

-- Most-punished players in the last month
SELECT target_name, COUNT(*) c
FROM staff_punishments
WHERE issued_at > (UNIX_TIMESTAMP() * 1000) - 30 * 86400000
GROUP BY target_uuid, target_name
ORDER BY c DESC
LIMIT 20;

staff_players_seen

CREATE TABLE IF NOT EXISTS staff_players_seen (
    uuid CHAR(36) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    first_seen BIGINT NOT NULL,
    last_seen BIGINT NOT NULL,
    last_server_id VARCHAR(64) NULL,
    online TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (uuid),
    KEY idx_last_name (last_name),
    KEY idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Notes

• One row per player UUID. Upserted on join.
• online is best-effort: a hard server crash leaves the row stuck at online=1 until that server boots again and runs markServerOffline(serverId) (which Staff does in start()).
• last_server_id is the last server the player joined. If online=1 it's the server they're on now.

Migrations

There aren't any yet. When a schema change is needed:

1. Add a migrations/00X_what.sql file
2. Add a tiny runner in SchemaBootstrap that:
   • Reads applied migrations from a staff_schema_version table (create on first run)
   • Applies the next pending one, commits, records it
3. Ship together. Don't backport schema changes to old plugin versions without a migration.

Don't drop into Flyway/Liquibase until you have at least 3 real migrations - they earn their weight on real projects, not on the second migration.