Discord Entity Normalization

Date: 2026-05-19 Module: integration-discord Status: Draft

Problem

The discord_event_logs table stores raw gateway events as JSONB blobs. Discord entities like guilds, channels, roles, and members exist only as embedded strings (guild_id, channel_id) or nested JSON fields across the codebase. This makes it impossible to efficiently query community health metrics like "messages per channel", "role growth trends", or "member retention funnels" without parsing JSON at query time.

Goal

Normalize Discord entities into first-class relational tables within the integration-discord module. Populate them via Discord REST API sync and real-time gateway event processing. Enable efficient SQL queries for the 10 community health features identified in the analysis phase.

Decision Summary

Schema

discord_guilds

Stores Discord guild metadata. Linked to the existing tenants table via optional FK.

Indexes: unique on discord_guild_id.

discord_channels

All channels in a guild (text, voice, category, stage, forum, announcement).

Indexes: unique on discord_channel_id, index on discord_guild_id.

discord_roles

All roles in a guild.

Indexes: unique on discord_role_id, index on discord_guild_id.

discord_members

Guild members. One row per user per guild (same Discord user in two guilds = two rows).

Indexes: unique composite on (discord_guild_id, discord_user_id), index on discord_user_id, index on external_identity_id.

discord_member_roles

Current role assignments (pivot table). Represents the current state snapshot.

Indexes: composite PK on (discord_member_id, discord_role_id).

discord_member_role_history

Log of role assignment and removal events. Append-only for trend analysis.

Indexes: index on discord_member_id, index on discord_role_id, index on occurred_at.

Entity Relationship Diagram

discord_guilds (1) ──────┬──── (N) discord_channels
                         │              │
                         │              └── parent_id → discord_channels (self-FK)
                         │
                         ├──── (N) discord_roles
                         │              │
                         │              └──── (N) discord_member_roles (N) ────┐
                         │                                                     │
                         └──── (N) discord_members ◄───────────────────────────┘
                                        │
                                        ├──── (N) discord_member_role_history
                                        │              │
                                        │              └── discord_role_id → discord_roles
                                        │
                                        └──── external_identity_id → external_identities (optional)

tenants (1) ──── (0..1) discord_guilds

Enums

DiscordChannelType

enum DiscordChannelType: int
{
    case GuildText = 0;
    case Dm = 1;
    case GuildVoice = 2;
    case GroupDm = 3;
    case GuildCategory = 4;
    case GuildAnnouncement = 5;
    case AnnouncementThread = 10;
    case PublicThread = 11;
    case PrivateThread = 12;
    case GuildStageVoice = 13;
    case GuildForum = 15;
    case GuildMedia = 16;
}

RoleHistoryAction

enum RoleHistoryAction: string
{
    case Assigned = 'assigned';
    case Removed = 'removed';
}

Sync Strategy

Full API Sync (Command)

php artisan discord:sync {guild_id?} --fresh

• Fetches guild, channels, roles, and members from Discord REST API
• Upserts all entities (idempotent, safe to re-run)
• --fresh flag truncates guild data before re-importing
• Paginates member list (1000 per request, uses after cursor)
• Schedulable daily via Laravel scheduler

New Transport Requests:

Event-Driven Updates (Observer)

A DiscordEventLogObserver triggers on DiscordEventLog::created and routes to event-specific handlers:

Each handler extracts structured data from the JSONB payload and upserts into the normalized tables.

File Structure

app-modules/integration-discord/
├── database/
│   ├── migrations/
│   │   ├── create_discord_guilds_table.php
│   │   ├── create_discord_channels_table.php
│   │   ├── create_discord_roles_table.php
│   │   ├── create_discord_members_table.php
│   │   ├── create_discord_member_roles_table.php
│   │   └── create_discord_member_role_history_table.php
│   └── factories/
│       ├── DiscordGuildFactory.php
│       ├── DiscordChannelFactory.php
│       ├── DiscordRoleFactory.php
│       └── DiscordMemberFactory.php
├── src/
│   ├── Models/
│   │   ├── DiscordGuild.php
│   │   ├── DiscordChannel.php
│   │   ├── DiscordRole.php
│   │   ├── DiscordMember.php
│   │   └── DiscordMemberRoleHistory.php
│   ├── Enums/
│   │   ├── DiscordChannelType.php
│   │   └── RoleHistoryAction.php
│   ├── Sync/
│   │   ├── Actions/
│   │   │   └── SyncDiscordGuildAction.php
│   │   ├── Console/
│   │   │   └── SyncDiscordGuildCommand.php
│   │   ├── Observers/
│   │   │   └── DiscordEventLogObserver.php
│   │   └── Handlers/
│   │       ├── HandleMemberAdd.php
│   │       ├── HandleMemberRemove.php
│   │       ├── HandleMemberUpdate.php
│   │       ├── HandleAuditLogEntry.php
│   │       └── HandleVoiceStateUpdate.php
│   └── Transport/
│       └── Requests/
│           ├── Guilds/
│           │   └── GetGuild.php
│           ├── Channels/
│           │   └── ListGuildChannels.php
│           ├── Members/
│           │   └── ListGuildMembers.php
│           └── Roles/
│               └── ListGuildRoles.php
└── tests/
    ├── Feature/
    │   ├── Sync/
    │   │   ├── SyncDiscordGuildTest.php
    │   │   ├── HandleMemberAddTest.php
    │   │   ├── HandleMemberRemoveTest.php
    │   │   ├── HandleMemberUpdateTest.php
    │   │   └── HandleAuditLogEntryTest.php
    │   └── Models/
    │       ├── DiscordGuildTest.php
    │       ├── DiscordChannelTest.php
    │       ├── DiscordRoleTest.php
    │       └── DiscordMemberTest.php
    └── Unit/
        └── Transport/
            └── Requests/
                ├── GetGuildTest.php
                ├── ListGuildChannelsTest.php
                ├── ListGuildMembersTest.php
                └── ListGuildRolesTest.php

Module Boundaries

integration-discord owns (new):

• Discord entity models (Guild, Channel, Role, Member)
• Sync action and command (API → database)
• Event log observer and handlers (events → normalized tables)
• Transport requests for new Discord API endpoints

integration-discord does NOT own:

• The bot runtime or event handlers (bot-discord)
• Community health dashboard queries or widgets (future panel-admin feature)
• The RawGatewayEvent handler that creates DiscordEventLog entries (bot-discord)

bot-discord changes: None

• RawGatewayEvent continues to create DiscordEventLog entries unchanged
• The observer on DiscordEventLog handles normalization transparently

Testing Strategy

• Unit tests for each Transport request (verify URL, method, headers)
• Feature tests for each handler with real payloads from discord_event_logs
• Feature tests for the sync action (mock API responses, verify upserts)
• Model tests for relationships and scopes
• All tests use factories; no direct DB manipulation