285f027ee9
ISSUE: Admin modules (Players, Grace, Audit, Servers, Roles) were failing silently HTMX endpoints returned 500 errors because required tables didn't exist ROOT CAUSE (per Gemini): Modules expect users table (Identity) separate from subscriptions (Billing) - users: discord_id, minecraft_username, minecraft_uuid, is_staff - admin_audit_log: audit trail for Trinity actions - server_sync_log: Pterodactyl sync tracking SOLUTION: Created schema migration with 3 new tables - Preserves Identity/Billing separation (critical for whitelist sync) - Auto-syncs existing subscriptions to users table - Pre-populates Trinity members as staff DEPLOYMENT: Run on Command Center: cp services/arbiter-3.0/migrations/arbiter_schema_migration.sql /tmp/ bash services/arbiter-3.0/migrations/run-migration.sh After migration, all 5 admin modules will work correctly. Credit: Gemini architectural guidance - Option A (create tables) vs Option B (rewrite queries) Signed-off-by: Claude (Chronicler #57) <claude@firefrostgaming.com>
43 lines
1.4 KiB
SQL
43 lines
1.4 KiB
SQL
-- 1. Users Table (Identity mapping for Whitelist Sync)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
discord_id VARCHAR(255) PRIMARY KEY,
|
|
minecraft_username VARCHAR(255),
|
|
minecraft_uuid VARCHAR(255),
|
|
is_staff BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 2. Admin Audit Log (For the Audit Module)
|
|
CREATE TABLE IF NOT EXISTS admin_audit_log (
|
|
id SERIAL PRIMARY KEY,
|
|
admin_discord_id VARCHAR(255) NOT NULL,
|
|
action_type VARCHAR(100) NOT NULL,
|
|
target_discord_id VARCHAR(255),
|
|
details JSONB,
|
|
performed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 3. Server Sync Log (For the Servers Matrix Module)
|
|
CREATE TABLE IF NOT EXISTS server_sync_log (
|
|
id SERIAL PRIMARY KEY,
|
|
server_identifier VARCHAR(100) NOT NULL,
|
|
sync_status VARCHAR(50) NOT NULL,
|
|
synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
details JSONB
|
|
);
|
|
|
|
-- Insert Trinity members with staff status
|
|
INSERT INTO users (discord_id, minecraft_username, is_staff)
|
|
VALUES
|
|
('269225344572863754', 'Frostystyle', true),
|
|
('219389716821444609', 'Gingerfury66', true)
|
|
ON CONFLICT (discord_id) DO NOTHING;
|
|
|
|
-- Sync existing subscriptions to users table
|
|
INSERT INTO users (discord_id, is_staff)
|
|
SELECT DISTINCT discord_id, false
|
|
FROM subscriptions
|
|
WHERE discord_id IS NOT NULL
|
|
ON CONFLICT (discord_id) DO NOTHING;
|