Database
Zander uses MySQL with Prisma as the ORM. This page documents the connection setup, how to apply migrations, and a full breakdown of every model in the schema so you know exactly where your data lives. The schema is defined in prisma/schema.prisma.
Connections
Up to four separate MySQL connections can be configured. zander-web owns and migrates only the main database via Prisma; the other three are external schemas it reads/writes into without owning their migrations:
| Variable | Purpose | Owned by zander-web? |
|---|---|---|
DATABASE_URL | Main Zander database (required) | Yes — migrated via prisma/migrations/ |
LUCKPERMS_URL | LuckPerms database — used for rank/permission lookups (required if using ranks) | No — external schema, read for permission checks and rank management |
QUICKSHOP_URL | QuickShop database — used for the shop directory (required if using shop directory) | No — external schema |
PUNISHMENTS_URL | LiteBans database — read/written for the web punishments dashboard and profile pages | No — external schema |
Connection strings use the format:
mysql://username:password@host:3306/database_name
Most controllers query the main database with raw SQL via a mysql2 pool rather than through Prisma Client models — Prisma is used here primarily as a migration tool, not the everyday query layer. Schema changes should still always go through a new Prisma migration.
Initial Setup
Run the SQL initialisation script to create the base schema, then apply all numbered migrations from the migration/ directory in order:
mysql -u root -p zander < dbinit.sql
# then for each migration file in order:
mysql -u root -p zander < migration/001_name.sql
For new deployments using the Prisma build script:
npm run build
This runs prisma migrate deploy and prisma generate automatically.
Schema Overview
The following models are defined in prisma/schema.prisma:
Users & Authentication
| Model | Description |
|---|---|
users | Core user accounts — UUID, username, email, password hash, profile data, social links |
userEmailVerifications | Email verification tokens |
userPasswordResets | Password reset tokens |
userVerifyLink | Short-lived codes for linking Discord accounts to Minecraft UUIDs |
Sessions
| Model | Description |
|---|---|
session | Express session store (Prisma-backed) |
Forums
| Model | Description |
|---|---|
forumCategories | Forum categories with view/post permission nodes |
forumDiscussions | Discussion threads |
forumPosts | Individual posts/replies |
forumPostRevisions | Edit history for posts |
forumPolls | Polls embedded in discussions |
forumPollOptions | Poll answer options |
forumPollVotes | User vote records |
Announcements & Servers
| Model | Description |
|---|---|
announcements | Multi-type announcements (web, popup, tip, motd) |
servers | Server registry with connection details and type |
serverStatus | Cached live server status (player count, online state) |
gameSessions | In-game session records reported by the plugins |
Applications
| Model | Description |
|---|---|
applications | Staff/player application listings and submissions |
Discord
| Model | Description |
|---|---|
discord_punishments | Punishment records (warn, kick, ban, mute) with expiry and status |
discord_punishment_appeals | Appeal submissions and review outcomes |
scheduledDiscordMessages | Scheduled message queue for the Discord scheduler |
Support
| Model | Description |
|---|---|
supportTickets | Support ticket records with category and status |
supportTicketMessages | Messages and internal notes within tickets |
supportTicketCategories | Configured ticket categories |
supportTicketCategoryPermissions | Per-category access permissions for staff |
Reports (Web)
| Model | Description |
|---|---|
reports | Player reports submitted via web or Discord |
There is no local punishments Prisma model for website punishments — the web punishments dashboard and profile pages read and write to the external LiteBans schema via PUNISHMENTS_URL instead (see the Connections table above), the same way LUCKPERMS_URL is used for rank data.
Badges
| Model | Description |
|---|---|
badges | Badge definitions (name, icon, description) |
user_badges | Badge assignments to users |
Webstore
| Model | Description |
|---|---|
webstorePurchases | Recorded Tebex/Stripe purchases |
webstoreSubscriptions | Recurring subscription records |
webstoreWebhookEvents | Raw webhook events received from Stripe |
webstoreStripeCommands | Command mappings run for Stripe products |
webstoreCommandRuns | Execution log for webstore-triggered commands |
webstoreTransactions | Transaction ledger for the webstore/goal tracking |
Finance
| Model | Description |
|---|---|
financeAccounts | Finance accounts (e.g. bank, PayPal) |
financeCategories | Income/expense categories |
financeTags | Tags applied to finance transactions |
financeVendors | Vendor/payee records |
financeTransactions | Individual income/expense transactions |
financeTransactionTags | Join table linking transactions to tags |
financeInvoices | Invoice records |
financePayments | Payments applied against invoices |
financeAttachments | File attachments on finance records |
financeOperationsBudget | Operating budget tracking |
financeMonthlyReports | Generated monthly finance reports |
Voting
| Model | Description |
|---|---|
votes | Individual vote records |
vote_sites | Configured voting sites |
vote_reward_templates | Command templates for vote and monthly rewards |
vote_monthly_totals | Aggregated monthly vote counts per player |
vote_monthly_results | Processed monthly reward results |
player_command_queue | Queue of reward commands to execute in-game |
Events
| Model | Description |
|---|---|
events | Community events with full lifecycle state |
event_templates | Recurring event templates |
event_template_announcements | Announcement config attached to event templates |
event_template_hosts | Default host assignments for event templates |
event_hosts | Host assignments for events |
event_actions | In-game actions triggered by events |
event_announcements | Discord/web announcements tied to events |
event_audit_logs | Change history for events |
Creator Content
| Model | Description |
|---|---|
creator_content_items | Cached Twitch/YouTube content items |
creator_content_notifications | Notification records for new creator content |
user_platform_connections | Twitch/YouTube account links for users |
Bridge & Automation
| Model | Description |
|---|---|
bridge | Legacy single command-bridge queue (command, target server, processed flag) |
executorTasks | Individual command tasks in the newer bridge executor queue |
executorRoutines | Named multi-step routines for the bridge |
executorRoutineSteps | Individual steps within an executor routine |
Logs
| Model | Description |
|---|---|
logs | System activity/audit log entries shown in the dashboard (Dashboard → Logs) |
Notifications
| Model | Description |
|---|---|
userNotifications | In-app notification records |
pushSubscriptions | Web push subscription endpoints per user |
Migrations
Migration files are located in migration/. Run them in numeric order. After applying all migrations, the Prisma client must be regenerated:
npx prisma generate