implementation_plan_admin.md

Implementation Plan

[Overview]

Implement Feature 6 (Admin Panel for Guild Management) and Feature 7 (Audit Log) for the DrusaBoT music Discord bot. The Admin page is an owner-only interface listing every guild the bot is in, letting the owner set a per-guild command prefix and manage per-guild blacklist/whitelist user restrictions. The Audit Log page surfaces every admin/mod action from the existing audit_logs table. This fills a confirmed gap: today access control (prefix, blacklist, whitelist) is global-only and only reachable via Discord text commands; this adds a web UI plus the data layer to support it on a per-guild basis.

Scope is Option A (per-guild), confirmed by the user. The bot is currently single-guild-configured (config.guild_id) with a hardcoded command_prefix="!", global blacklisted_users/approved_users tables, and owner-only Discord admin cogs that already write to audit_logs. The plan adds: three new per-guild tables, a prefix resolver callable in the bot core, per-guild enforcement in is_authorized, owner-only admin API routes, an audit manager module, and a React admin + audit-log SPA surface. No existing user-facing behavior is removed; global lists keep working; default prefix stays ! and is backward compatible.

[Types]

Backend Python (no new public types; route payloads use Pydantic models):

• PrefixUpdate (Pydantic BaseModel, routes.py) — prefix: str = Field(..., min_length=1, max_length=5, pattern=r"^[^\s]{1,5}$"). Validates a custom prefix is a single non-whitespace token, max 5 chars.
• BulkUserRef (Pydantic BaseModel, routes.py) — user_id: str (resolved Discord id), username: str, display_name: str. For add-blacklist/whitelist.
• Reuses existing SettingsUpdate, GuildSettings from routes.py/guild_settings.
• Reuses DB row dicts (sqlite3.Row → dict) returned by managers, matching the favorites_manager/guild_settings pattern (functions return list[dict] / dict).

Frontend TypeScript (web/src/lib/types.ts additions):

• AdminGuild { id: string; name: string; member_count?: number | null; icon_url?: string | null; prefix: string; blacklist_count: number; whitelist_count: number }
• PrefixUpdate { prefix: string }
• AccessEntry { user_id: string; username: string; display_name: string; added_by: string; added_at: string }
• AuditLog { id: number; action: string; target_user_id: string; target_username: string; moderator_id: string; moderator_name?: string; timestamp: string }
• AuditLogsResponse { logs: AuditLog[]; total: number; limit: number; offset: number; actions: string[] }
• AdminAction union: "set_prefix" | "add_blacklist" | "remove_blacklist" | "add_whitelist" | "remove_whitelist"

[Files]

New files:

• bot/database/admin_manager.py — per-guild prefix, blacklist, whitelist CRUD (mirrors favorites_manager.py: plain functions taking db_path, using get_connection). Includes an in-memory _PREFIX_CACHE dict for prefix lookups.
• bot/database/audit_manager.py — log_action(...), get_logs(...), get_actions(...), count_logs(...). Canonical writer for audit_logs; cogs call through here.
• web/src/pages/AdminPage.tsx — owner-only guild management page (guild selector + prefix editor + blacklist/whitelist panels).
• web/src/features/admin/AuditLog.tsx — paginated, filterable audit-log viewer.
• web/src/hooks/use-admin.ts — React Query hooks for admin API (/api/admin/...).
• web/src/hooks/use-audit.ts — React Query hook for /api/admin/audit.
• tests/test_admin_manager.py — unit tests for admin_manager (temp DB).
• tests/test_audit_manager.py — unit tests for audit_manager (temp DB).
• tests/test_dashboard_admin_routes.py — TestClient tests for admin + audit routes (owner auth, 403 for admin role, prefix validation, blacklist/whitelist CRUD).

Modified files: Backend:

• bot/database/database.py — add guild_prefixes, guild_blacklist, guild_whitelist CREATE TABLE + indexes in _create_tables.
• bot/database/repository.py — add the same three tables + indexes in _create_tables_postgres (PostgreSQL parity).
• bot/core/bot.py — command_prefix="!" → command_prefix=get_prefix_resolver callable. Add _PREFIX_CACHE import-free lazy resolver in admin_manager, consumed here.
• bot/dashboard/routes.py — add owner-only admin routes + audit route. Add _require_owner helper (shared secret OR OAuth role=="owner" session; 403 for admin-role, 401 for missing/invalid). Signature register_routes(app, bot, security, check_write_auth) unchanged.
• bot/cogs/admin/base.py — log_audit delegates to audit_manager.log_action (single code path, DRY).
• bot/cogs/music/base.py — is_authorized additionally checks per-guild blacklist and per-guild whitelist mode before global checks (owner always passes; adds no-op when tables empty).

Frontend:

• web/src/lib/types.ts — add AdminGuild, PrefixUpdate, AccessEntry, AuditLog, AuditLogsResponse, AdminAction.
• web/src/router.tsx — add /admin child route (owner-guarded) and /admin/audit route.
• web/src/components/layout/Sidebar.tsx — add conditional Admin nav item (shields icon) shown when sessionUser?.role === "owner".
• web/src/components/layout/ProtectedRoute.tsx — unchanged (login guard); admin gating lives in AdminPage.

Files deleted/moved: none.

Configuration: no .env / settings changes. New behavior is DB-driven and backward-compatible.

[Functions]

New functions: Backend (bot/database/admin_manager.py):

• get_prefix(guild_id: str, db_path: str) -> str — returns stored prefix or "!". Reads _PREFIX_CACHE first.
• set_prefix(guild_id: str, prefix: str, db_path: str) -> str — upserts guild_prefixes row, invalidates _PREFIX_CACHE, returns prefix.
• list_blacklist(guild_id: str, db_path: str) -> list[dict]
• add_blacklist(guild_id, user_id, username, display_name, added_by, db_path) -> bool (True if newly added; False already present)
• remove_blacklist(guild_id, user_id, db_path) -> bool
• list_whitelist(guild_id, db_path) -> list[dict]
• add_whitelist(...) -> bool; remove_whitelist(...) -> bool
• guild_admin_summary(guild_id, db_path) -> dict — {prefix, blacklist_count, whitelist_count}
• is_guild_blacklisted(guild_id, user_id, db_path) -> bool and is_guild_whitelisted(guild_id, user_id, db_path) -> bool — used by enforcement.
• get_prefix_resolver(bot) / resolve_prefix(bot, message) helper used by command_prefix callable — lazy imports admin_manager to avoid import cycles.

Backend (bot/database/audit_manager.py):

• log_action(action, target_user_id, target_username, moderator_id, db_path) -> int — insert, returns row id.
• get_logs(db_path, limit=50, offset=0, action=None) -> list[dict]
• get_actions(db_path) -> list[str] — distinct actions for filter dropdown.
• count_logs(db_path) -> int

Backend (bot/dashboard/routes.py, inside register_routes):

• _require_owner(credentials) -> None — raises HTTPException(403/401). Accepts shared secret OR OAuth session with role=="owner".
• GET /api/admin/guilds — all guilds enriched with prefix + counts (owner-only).
• GET /api/admin/guilds/{guild_id} — single guild summary.
• POST /api/admin/guilds/{guild_id}/prefix — set prefix (PrefixUpdate).
• GET /api/admin/guilds/{guild_id}/blacklist — list.
• POST /api/admin/guilds/{guild_id}/blacklist — add (Body user_id, username, display_name; resolves/normalizes).
• DELETE /api/admin/guilds/{guild_id}/blacklist/{user_id} — remove.
• GET /api/admin/guilds/{guild_id}/whitelist — list.
• POST /api/admin/guilds/{guild_id}/whitelist — add.
• DELETE /api/admin/guilds/{guild_id}/whitelist/{user_id} — remove.
• GET /api/admin/audit — owner-only paginated audit list (limit, offset, action). Resolves moderator_name from bot.get_user cache.

Modified functions:

• bot/cogs/admin/base.py::log_audit — body becomes return await audit_manager.log_action(...) (sync; called via await already works since callers await log_audit). Keep async wrapper for backward compatibility.
• bot/cogs/music/base.py::is_authorized(ctx, bot) — after owner check and before global blacklist/approved checks, add: if ctx.guild and is_guild_blacklisted(guild_id, user_id, db_path) → deny with "blacklisted in this server"; if guild has any whitelist entries and user not whitelisted → deny with "not in server allowlist". Falls through to existing global checks. No-op when tables empty (fresh DB), so existing tests unaffected.
• bot/core/bot.py Bot.__init__ — replace command_prefix="!" with command_prefix=resolve_prefix callable.

New classes: none significant. AuditResult not needed (manager returns plain dicts).

[Classes]

Backend: no new classes. (Bot subclass modified only to swap command_prefix value to a callable.) Frontend: AdminPage and AuditLog are functional components (project convention: pages/features use function components, no classes).

[Dependencies]

Backend: no new packages. Reuses sqlite3 (stdlib), existing get_connection. PostgreSQL path updated in repository.py for parity only. Frontend: no new packages. Reuses lucide-react (add Shield icon import), @tanstack/react-query, zustand, react-router-dom. No changes to package.json. Deploy: cd web && npm run build after frontend changes; bot restart to load new prefix callable + cogs (cogs already auto-load).

[Testing]

New test files:

• tests/test_admin_manager.py: temp DB, init tables; test set/get prefix (cache invalidation), blacklist add/remove/list (dedup), whitelist same, guild_admin_summary counts.
• tests/test_audit_manager.py: temp DB; log_action then get_logs (pagination, action filter, ordering), get_actions, count_logs.
• tests/test_dashboard_admin_routes.py: mirrors test_dashboard_routes.py pattern — simple bot SimpleNamespace with guilds, get_user, config.database_path, config.dashboard_secret_key. Cases:
  • GET /api/admin/guilds returns 401 without token, 200 with shared-secret, 403 with role=admin OAuth token, 200 with role=owner token.
  • Prefix: POST .../prefix rejects prefix="", accepts prefix="?", get_prefix returns ?.
  • Blacklist add/remove/list round-trip. Whitelist add/remove/list round-trip.
  • GET /api/admin/audit returns paginated logs.

Existing tests kept green: run pytest tests/ -x. The is_authorized change is a no-op on empty tables. The command_prefix change is config-level; existing music/cog tests that build a Bot with a fake prefix won't break (they don't invoke real command dispatch). Verify with tests/test_dashboard_routes.py and tests/test_database.py.

Lint/typecheck: ruff check bot/ && black bot/; cd web && npm run lint && npx tsc --noEmit.

Validation strategy:

1. Local: pytest tests/ -x green. ruff + black clean. Frontend tsc + oxlint clean.
2. Frontend dev: cd web && npm run dev, open /admin with owner OAuth session → guild list loads, prefix editable, blacklist/whitelist panels work, /admin/audit shows logs with moderator names.
3. Backend: hit /api/admin/guilds with owner OAuth token → 200; with admin-role token → 403.
4. Deploy: rebuild frontend, restart bot, login as owner, exercise admin page, trigger actions, confirm audit log entries appear; issue !help/commands to confirm custom per-guild prefix works and blacklisted/whitelisted enforcement behaves.

[Implementation Order]

1. DB schema — add the three tables + indexes to database.py::_create_tables AND repository.py::_create_tables_postgres (non-destructive CREATE TABLE IF NOT EXISTS; no migration script needed).
2. bot/database/admin_manager.py — prefix/blacklist/whitelist CRUD + cache + enforcement helpers + prefix resolver.
3. bot/database/audit_manager.py — log getter + writer; refactor bot/cogs/admin/base.py::log_audit to delegate.
4. bot/core/bot.py — swap command_prefix to the resolver callable.
5. bot/cogs/music/base.py::is_authorized — add per-guild blacklist/whitelist enforcement.
6. bot/dashboard/routes.py — add _require_owner + all admin + audit API routes.
7. Frontend types + hooks (types.ts, use-admin.ts, use-audit.ts).
8. web/src/pages/AdminPage.tsx + web/src/features/admin/AuditLog.tsx.
9. web/src/router.tsx + web/src/components/layout/Sidebar.tsx + TopBar/AdminPage gating.
10. Tests (3 new test files) + run pytest -x, ruff, black, frontend tsc/oxlint.
11. Frontend production build (cd web && npm run build) → web/dist.
12. Git commit + push; restart bot; smoke test (owner auth 403-for-admin, prefix works, audit visible).