Database Schema Documentation

Complete reference for the DBK Gaming Platform database schema.

Overview

The platform uses a shared Prisma schema that supports both:

• SQLite (Development)
• PostgreSQL (Production)

Schema Location: prisma/schema.prisma

---

Tables

Guild

Represents Discord servers that have the bot installed.

Relations:

• games: One-to-many with Game
• reviews: One-to-many with ServerReview
• members: One-to-many with ServerMember
• gameServers: One-to-many with GameServer

Indexes:

• isOfficial: Fast filtering of official servers
• isApproved: Fast filtering of approved servers
• isFeatured: Fast filtering of featured servers

---

Game

Represents games managed by the bot within a Discord server.

Relations:

• guild: Many-to-one with Guild

Unique Constraints:

• (guildId, name): One game per name per server

Indexes:

• guildId
• archived

---

User

Represents users who have logged into the web panel.

Relations:

• sessions: One-to-many with Session
• announcements: One-to-many with Announcement
• reviews: One-to-many with ServerReview
• notifications: One-to-many with Notification
• rank: One-to-one with UserRank
• permissions: One-to-many with Permission
• serverMemberships: One-to-many with ServerMember
• forumThreads: One-to-many with ForumThread
• forumPosts: One-to-many with ForumPost
• forumReactions: One-to-many with ForumPostReaction
• gameServers: One-to-many with GameServer
• liveChatMessages: One-to-many with LiveChatMessage

Indexes:

• username: Unique index for fast lookup
• role: Fast filtering by role (deprecated in favor of UserRank)
• createdAt: Fast sorting by join date

Social Links Format:

{
  "twitter": "@username",
  "twitch": "username",
  "youtube": "channelname",
  "steam": "steamid"
}

---

Session

Represents active user sessions for the web panel.

Relations:

• user: Many-to-one with User

Indexes:

• userId
• expiresAt

Session Expiry: 7 days from creation

---

Announcement

Represents announcements displayed on the web panel homepage.

Relations:

• author: Many-to-one with User

Indexes:

• (published, createdAt): Composite index for efficient queries

---

ServerReview

Represents user reviews/ratings for Discord servers.

Relations:

• server: Many-to-one with Guild
• user: Many-to-one with User

Unique Constraints:

• (serverId, userId): One review per user per server

Indexes:

• serverId

---

Notification

Represents user notifications in the web panel.

Relations:

• user: Many-to-one with User

Indexes:

• (userId, read): Composite index for unread queries
• createdAt

Notification Types:

• SERVER_APPROVED: Server listing approved
• SERVER_FEATURED: Server featured on homepage
• ANNOUNCEMENT_POSTED: New announcement published
• GAME_ADDED: Game successfully added to server
• GAME_ARCHIVED: Game archived by admin

---

AuditLog

Represents bot action audit trail (game management operations).

Indexes:

• (guildId, createdAt): Composite index

Action Types:

• GAME_CREATED: Game setup completed
• GAME_ARCHIVED: Game archived
• GAME_DELETED: Game permanently deleted
• DASHBOARD_SETUP: Dashboard created

---

AdminAuditLog

Represents admin action audit trail (web panel operations).

Indexes:

• (adminId, createdAt): Composite index
• createdAt

Action Types:

• UPDATE_USER_ROLE: User role changed
• UPDATE_USER_RANK: User rank changed
• GRANT_PERMISSION: Permission granted to user
• REVOKE_PERMISSION: Permission revoked from user
• DELETE_USER: User account deleted
• FEATURE_SERVER: Server featured/unfeatured
• APPROVE_SERVER: Server approved/rejected
• CREATE_ANNOUNCEMENT: Announcement created
• UPDATE_ANNOUNCEMENT: Announcement updated
• DELETE_ANNOUNCEMENT: Announcement deleted
• DELETE_FORUM_POST: Forum post deleted
• DELETE_FORUM_THREAD: Forum thread deleted
• APPROVE_GAME_SERVER: Game server approved
• REJECT_GAME_SERVER: Game server rejected

---

CustomActivity

Represents custom bot activity status.

Indexes:

• isActive
• expiresAt

Activity Types:

• 0: Playing
• 1: Streaming (requires URL)
• 2: Listening to
• 3: Watching
• 5: Competing in

---

UserRank

Represents user rank/hierarchy level in the platform.

Relations:

• user: One-to-one with User

Unique Constraints:

• userId: One rank per user

Indexes:

• rank: Fast filtering by rank type
• level: Fast sorting by level

Rank Hierarchy:

• MEMBER (Level 1): Default rank for all users
• MODERATOR (Level 50): Can moderate content and manage users
• ADMIN (Level 100): Full platform access

---

Permission

Represents granular user permissions (overrides rank defaults).

Relations:

• user: Many-to-one with User

Unique Constraints:

• (userId, permission, scope): One permission per user per scope

Indexes:

• userId: Fast lookup of user permissions
• permission: Fast filtering by permission type

Permission Types:

• MANAGE_SERVERS: Manage server listings
• MANAGE_USERS: Manage user accounts
• MANAGE_ANNOUNCEMENTS: Create/edit announcements
• MODERATE_FORUM: Moderate forum content
• APPROVE_GAME_SERVERS: Approve game server submissions
• DELETE_CONTENT: Delete user content

---

ServerMember

Represents per-server membership and roles.

Relations:

• server: Many-to-one with Guild
• user: Many-to-one with User

Unique Constraints:

• (serverId, userId): One membership per user per server

Indexes:

• serverId: Fast lookup of server members
• userId: Fast lookup of user memberships

Server Ranks:

• MEMBER: Basic server access
• MODERATOR: Can manage server settings
• ADMIN: Full server control

---

ForumCategory

Represents forum categories/sections.

Relations:

• threads: One-to-many with ForumThread
• permissions: One-to-many with ForumCategoryPermission

Unique Constraints:

• slug: Unique category URL slug

Indexes:

• position: Fast sorting by display order
• slug: Fast lookup by slug

---

ForumThread

Represents forum discussion threads.

Relations:

• category: Many-to-one with ForumCategory
• author: Many-to-one with User
• posts: One-to-many with ForumPost

Unique Constraints:

• (categoryId, slug): Unique slug per category

Indexes:

• (categoryId, isPinned, createdAt): Fast thread listing with pinned first
• (categoryId, isPinned, updatedAt): Fast thread listing sorted by activity
• authorId: Fast lookup of user's threads
• createdAt: Fast sorting by creation date

---

ForumPost

Represents forum thread replies/posts.

Relations:

• thread: Many-to-one with ForumThread
• author: Many-to-one with User
• reactions: One-to-many with ForumPostReaction

Indexes:

• (threadId, createdAt): Fast post listing in chronological order
• authorId: Fast lookup of user's posts

---

ForumPostReaction

Represents emoji reactions to forum posts.

Relations:

• post: Many-to-one with ForumPost
• user: Many-to-one with User

Unique Constraints:

• (postId, userId, emoji): One reaction per emoji per user per post

Indexes:

• postId: Fast lookup of post reactions
• userId: Fast lookup of user reactions

---

ForumCategoryPermission

Represents per-category permission overrides.

Relations:

• category: Many-to-one with ForumCategory

Unique Constraints:

• (categoryId, rank): One permission set per rank per category

Indexes:

• categoryId: Fast lookup of category permissions

---

GameServer

Represents submitted game servers for community listing.

Relations:

• submitter: Many-to-one with User
• linkedGuild: Many-to-one with Guild (optional)
• status: One-to-many with GameServerStatus

Indexes:

• gameType: Fast filtering by game type
• (isApproved, isActive): Fast filtering of approved/active servers
• submittedBy: Fast lookup of user submissions
• linkedGuildId: Fast lookup of guild servers

Game Types:

• minecraft: Minecraft servers
• csgo: Counter-Strike: Global Offensive
• rust: Rust
• ark: ARK: Survival Evolved
• valheim: Valheim
• terraria: Terraria

---

GameServerStatus

Represents historical status snapshots of game servers.

Relations:

• server: Many-to-one with GameServer

Indexes:

• (serverId, timestamp): Fast lookup of server history
• timestamp: Fast cleanup of old records

Retention: Records older than 48 hours are automatically deleted

---

LiveChatMessage

Represents real-time chat messages on the homepage.

Relations:

• user: Many-to-one with User

Indexes:

• createdAt: Fast retrieval of recent messages
• userId: Fast lookup of user messages

Retention: Recent 100 messages are kept in memory for real-time display

---

LiveChatSettings

Stores configuration for Discord webhook integration with the live chat system. Uses singleton pattern (single row with id = "singleton").

Relations:

• None (singleton configuration model)

Purpose:

• Enables syncing web chat messages to Discord via webhook
• Configured through admin panel by users with MANAGE_LIVECHAT_SETTINGS or MANAGE_SYSTEM permission
• Messages sent to live chat widget are forwarded to configured Discord channel with user's username and avatar

Singleton Pattern:

• Always accessed with id = "singleton"
• Only one configuration allowed per system
• Created on first access if not exists

---

AMPServer

Represents official game servers managed through CubeCoders AMP (Application Management Panel).

Relations:

• None (independent model for official servers)

Unique Constraints:

• ampInstanceId: Unique AMP instance identifier

Indexes:

• gameType: Fast filtering by game type
• isRunning: Fast filtering of running servers
• lastSync: Fast identification of stale data

Game Types:

• minecraft: Minecraft servers
• rust: Rust servers
• ark: ARK: Survival Evolved servers
• valheim: Valheim servers
• csgo: Counter-Strike servers
• terraria: Terraria servers

Sync Behavior:

• Polled periodically via AMP API (configurable interval)
• Status updated automatically by background service
• Admin users can trigger manual sync via web panel

---

Relationships

Entity Relationship Diagram

Guild (1) ──┬─→ (N) Game
            ├─→ (N) ServerReview
            ├─→ (N) ServerMember
            └─→ (N) GameServer

User (1) ──┬─→ (N) Session
           ├─→ (N) Announcement
           ├─→ (N) ServerReview
           ├─→ (N) Notification
           ├─→ (1) UserRank
           ├─→ (N) Permission
           ├─→ (N) ServerMember
           ├─→ (N) ForumThread
           ├─→ (N) ForumPost
           ├─→ (N) ForumPostReaction
           ├─→ (N) GameServer
           └─→ (N) LiveChatMessage

ForumCategory (1) ──┬─→ (N) ForumThread
                    └─→ (N) ForumCategoryPermission

ForumThread (1) ──→ (N) ForumPost

ForumPost (1) ──→ (N) ForumPostReaction

GameServer (1) ──→ (N) GameServerStatus

Guild (N) ←─── (N) User (via ServerReview, ServerMember)

Cascade Behaviors

ON DELETE CASCADE:

• When Guild is deleted:

  • All related Game records deleted
  • All related ServerReview records deleted
  • All related ServerMember records deleted
  • All related GameServer records deleted

• When User is deleted:

  • All related Session records deleted
  • All related ServerReview records deleted
  • All related Notification records deleted
  • All related UserRank records deleted
  • All related Permission records deleted
  • All related ServerMember records deleted
  • All related ForumThread records deleted
  • All related ForumPost records deleted
  • All related ForumPostReaction records deleted
  • Related GameServer records preserved (submitter reference)
  • All related LiveChatMessage records deleted

• When ForumCategory is deleted:

  • All related ForumThread records deleted (and their posts)
  • All related ForumCategoryPermission records deleted

• When ForumThread is deleted:

  • All related ForumPost records deleted (and their reactions)

• When ForumPost is deleted:

  • All related ForumPostReaction records deleted

• When GameServer is deleted:

  • All related GameServerStatus records deleted

Preserved Relations:

• Announcement: Not cascaded (preserved even if author is deleted)
• GameServer.submittedBy: Not cascaded (submitter reference preserved)

---

Indexes and Performance

Critical Indexes

1. Guild:

   • isOfficial: Fast filtering of official servers
   • isApproved: Fast filtering of approved servers
   • isFeatured: Fast filtering of featured servers

2. Game:

   • guildId: Fast lookup of games by server
   • archived: Fast filtering of active games

3. User:

   • role: Fast filtering of admins
   • createdAt: Fast sorting by join date

4. Session:

   • userId: Fast lookup of user sessions
   • expiresAt: Fast cleanup of expired sessions

5. Announcement:

   • (published, createdAt): Fast queries for published announcements

6. Notification:

   • (userId, read): Fast queries for unread notifications
   • createdAt: Fast sorting by date

Query Optimization Tips

Efficient Queries:

// ✅ Good: Uses index on published + createdAt
const announcements = await prisma.announcement.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  take: 10
});

// ✅ Good: Uses index on userId + read
const unreadCount = await prisma.notification.count({
  where: { userId: userId, read: false }
});

// ❌ Bad: No index on username + name combination
const result = await prisma.user.findMany({
  where: { username: { contains: 'test' } }
});

---

Migrations

Migration History

All migrations are stored in prisma/migrations/.

View Migration Status:

npx prisma migrate status

Create New Migration:

# Development
npm run db:migrate

# Production
npm run db:migrate:prod

Common Migration Tasks

Add New Field:

1. Update prisma/schema.prisma:

model User {
  id       String   @id
  email    String?  // New field
  ...
}

2. Create and apply migration:

npm run db:migrate

Add Index:

1. Update schema:

model User {
  ...
  @@index([email])
}

2. Apply migration:

npm run db:migrate

---

Database Maintenance

Cleanup Tasks

Expired Sessions:

DELETE FROM "Session" WHERE "expiresAt" < NOW();

Run this periodically (e.g., daily cron job).

Archived Games:

-- View archived games older than 6 months
SELECT * FROM "Game" 
WHERE archived = true 
AND "archivedAt" < NOW() - INTERVAL '6 months';

-- Permanently delete old archived games (use with caution!)
DELETE FROM "Game" 
WHERE archived = true 
AND "archivedAt" < NOW() - INTERVAL '6 months';

Old Notifications:

-- Delete read notifications older than 30 days
DELETE FROM "Notification" 
WHERE read = true 
AND "createdAt" < NOW() - INTERVAL '30 days';

Database Statistics

Table Sizes (PostgreSQL):

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Row Counts:

SELECT 
  'User' as table_name, COUNT(*) as rows FROM "User"
UNION ALL
SELECT 'Guild', COUNT(*) FROM "Guild"
UNION ALL
SELECT 'Game', COUNT(*) FROM "Game"
UNION ALL
SELECT 'Session', COUNT(*) FROM "Session"
UNION ALL
SELECT 'Announcement', COUNT(*) FROM "Announcement"
UNION ALL
SELECT 'Notification', COUNT(*) FROM "Notification";

---

Schema Evolution

Adding New Features

When adding new features, consider:

1. Backward Compatibility: Can existing data be migrated?
2. Default Values: Use sensible defaults for new fields
3. Indexes: Add indexes for frequently queried fields
4. Relations: Use appropriate cascade behaviors

Example: Adding Server Tags

model ServerTag {
  id        String   @id @default(cuid())
  name      String   @unique
  createdAt DateTime @default(now())
  
  servers   ServerTagRelation[]
}

model ServerTagRelation {
  id       String    @id @default(cuid())
  guildId  String
  guild    Guild     @relation(fields: [guildId], references: [id], onDelete: Cascade)
  tagId    String
  tag      ServerTag @relation(fields: [tagId], references: [id], onDelete: Cascade)
  
  @@unique([guildId, tagId])
  @@index([guildId])
  @@index([tagId])
}

---

Troubleshooting

Common Issues

Issue: Prisma Client out of sync

Solution:

npm run db:generate

---

Issue: Migration failed

Solution:

# Mark migration as rolled back
npx prisma migrate resolve --rolled-back <migration_name>

# Fix the issue and re-run migration
npm run db:migrate

---

Issue: Database locked (SQLite)

Solution:

• Stop all processes accessing the database
• Restart the application

---

Issue: Foreign key constraint violation

Solution:

• Ensure related records exist before creating relationships
• Use onDelete: Cascade or onDelete: SetNull appropriately

---

Best Practices

1. Always backup before migrations in production
2. Test migrations in development first
3. Use transactions for related operations
4. Add indexes for frequently queried fields
5. Use CUID for primary keys (secure, sortable, URL-safe)
6. Soft delete important records (use archived flags)
7. Cascade deletes for dependent records
8. Validate input before database operations
9. Use Prisma Client instead of raw SQL when possible
10. Monitor database size and clean up old data

---

Resources

• Prisma Documentation
• Prisma Schema Reference
• PostgreSQL Documentation
• SQLite Documentation

---

New Models Summary

The following models were added in the major platform update:

Permission System:

• UserRank: Hierarchical rank system (MEMBER, MODERATOR, ADMIN)
• Permission: Granular permission overrides
• ServerMember: Per-server membership and roles

Forum System:

• ForumCategory: Forum categories/sections
• ForumThread: Discussion threads
• ForumPost: Thread replies
• ForumPostReaction: Emoji reactions
• ForumCategoryPermission: Per-category permission overrides

Game Server System:

• GameServer: Community game server submissions
• GameServerStatus: Historical status monitoring

Real-time Chat:

• LiveChatMessage: Homepage live chat messages
• LiveChatSettings: Discord webhook integration configuration (singleton)

---

Last Updated: February 21, 2026