Database Schema

The Resonance Discord bot uses Supabase for persistence. This reference documents the key tables.

Core Tables

`discord_server_configs`

Stores server-to-brand linkages and configuration.

server_id       TEXT PRIMARY KEY
brand_id        TEXT NOT NULL
server_name     TEXT
is_active       BOOLEAN DEFAULT TRUE
config_metadata JSONB  -- Event configs, role settings, etc.

`discord_role_configs`

Stores role → effect mappings for loyalty rewards and milestones.

id                    UUID PRIMARY KEY
server_id             TEXT NOT NULL
brand_id              TEXT NOT NULL
discord_role_id       TEXT NOT NULL
discord_role_name     TEXT
discord_role_color    TEXT            -- Hex color (e.g., "#FF5733")
discord_role_position INTEGER

-- Effect Configuration
effect_type           TEXT NOT NULL   -- multiplier, bonus_flat, cooldown_reduction,
                                     -- event_access, one_time_bonus, auto_drip
effect_config         JSONB DEFAULT '{}'
-- Examples:
--   multiplier:         { "value": 2.0 }
--   bonus_flat:         { "value": 50 }
--   cooldown_reduction: { "percent": 50 }
--   one_time_bonus:     { "amount": 100 }   ← used by milestones

-- Assignment Configuration
assignment_type       TEXT DEFAULT 'external'
                      -- manual, balance_threshold, activity_threshold, purchase,
                      -- external, milestone
assignment_config     JSONB DEFAULT '{}'
-- Milestone example:
--   { "name": "Whale", "description": "...",
--     "criteria": [{ "type": "rsnc_earned", "threshold": 5000 }] }

priority              INTEGER DEFAULT 0
is_active             BOOLEAN DEFAULT TRUE
created_at            TIMESTAMPTZ DEFAULT NOW()
updated_at            TIMESTAMPTZ DEFAULT NOW()

CONSTRAINT unique_server_role UNIQUE(server_id, discord_role_id)

`discord_role_assignments`

Tracks role assignments, purchases, and one-time bonus claims.

id                      UUID PRIMARY KEY
server_id               TEXT NOT NULL
brand_id                TEXT NOT NULL
discord_user_id         TEXT NOT NULL
discord_role_id         TEXT NOT NULL
assigned_at             TIMESTAMPTZ DEFAULT NOW()
assigned_by             TEXT NOT NULL   -- manual, auto:balance, auto:activity, purchase, external
ltz_spent               INTEGER DEFAULT 0
one_time_bonus_claimed  BOOLEAN DEFAULT FALSE
one_time_bonus_amount   INTEGER DEFAULT 0
one_time_bonus_claimed_at TIMESTAMPTZ
removed_at              TIMESTAMPTZ     -- NULL if still active
removed_by              TEXT
created_at              TIMESTAMPTZ DEFAULT NOW()
updated_at              TIMESTAMPTZ DEFAULT NOW()

CONSTRAINT unique_user_role UNIQUE(server_id, discord_user_id, discord_role_id)

`discord_user_stats`

Tracks per-user statistics.

server_id       TEXT
discord_user_id TEXT
messages_count  INTEGER
voice_minutes   INTEGER
reactions_count INTEGER
rsnc_earned      BIGINT

`discord_user_streaks`

Tracks user streak data.

server_id        TEXT
discord_user_id  TEXT
current_streak   INTEGER
longest_streak   INTEGER
last_checkin     TIMESTAMPTZ

Admin & Analytics Tables

`discord_audit_log`

Comprehensive audit trail for all admin actions.

server_id       TEXT NOT NULL
brand_id        TEXT
actor_type      TEXT  -- 'admin', 'bot', 'system', 'user'
actor_id        TEXT  -- Discord user ID or 'system'
action          TEXT NOT NULL
target_user_id  TEXT  -- Affected user, if applicable
details         JSONB -- Structured action details
before_state    JSONB -- State before change
after_state     JSONB -- State after change
is_test         BOOLEAN DEFAULT FALSE
created_at      TIMESTAMPTZ DEFAULT NOW()

`discord_kpi_configs`

KPI target configuration per server.

server_id       TEXT NOT NULL
metric          TEXT NOT NULL
target_value    NUMERIC NOT NULL
period          TEXT DEFAULT 'week'
alert_threshold NUMERIC
is_active       BOOLEAN DEFAULT TRUE
created_at      TIMESTAMPTZ DEFAULT NOW()
updated_at      TIMESTAMPTZ DEFAULT NOW()

Available Metrics:

daily_active_users
weekly_active_users
streak_retention
new_user_conversion
voice_participation
reward_efficiency
churn_rate
top_10_concentration

`discord_kpi_snapshots`

Historical KPI data for trend analysis.

server_id     TEXT NOT NULL
snapshot_date DATE NOT NULL
snapshot_type TEXT DEFAULT 'daily'
metrics       JSONB NOT NULL
created_at    TIMESTAMPTZ DEFAULT NOW()

Snapshots are automatically generated daily at midnight UTC.

Event Configuration

`monitoring_rules`

Central event configuration table (shared with Partner Portal).

id                  UUID PRIMARY KEY
brand_id            TEXT NOT NULL
event_type          TEXT NOT NULL
reward_amount       NUMERIC
cooldown_hours      INTEGER
max_claims_per_user INTEGER
is_enabled          BOOLEAN DEFAULT TRUE
detection_method    TEXT            -- JSON array of detection methods
detection_config    JSONB           -- Trigger configuration (see below)
channel_constraints JSONB
rule_metadata       JSONB
server_id           TEXT            -- For Discord-specific events

This table is the source of truth for all events. Changes made in Discord (/config events) sync with Partner Portal and vice versa.

`detection_config` Schema

The detection_config JSONB field stores trigger configuration for automatic event detection:

// Keyword trigger — fires on channel_message matching keywords
{ "trigger": "keyword", "keywords": ["gm", "gn", "gz"], "channels": ["gm-channel"] }

// Min length trigger — fires on message_quality with sufficient length
{ "trigger": "min_length", "min_length": 50 }

// Reaction count trigger — fires on reaction_threshold meeting minimum
{ "trigger": "reaction_count", "min_reactions": 5 }

// Channel-only (default) — manual /reward or channel-matched events
{ "channels": ["announcements"] }

`detection_method` Values

Value	Trigger Type	Description
`gateway_keyword`	`keyword`	Matches message content against keyword list
`gateway_message_quality`	`min_length`	Matches message length against minimum
`gateway_reaction`	`reaction_count`	Matches reaction count against minimum
`discord_interaction`	—	Standard Discord bot interaction

Drop & Engagement Tables

`discord_drops`

Active drops configuration.

drop_id         UUID PRIMARY KEY
server_id       TEXT NOT NULL
event_id        TEXT NOT NULL
drop_type       TEXT  -- 'button', 'reaction'
message_id      TEXT
channel_id      TEXT
emoji           TEXT
expires_at      TIMESTAMPTZ
max_claims      INTEGER
claims_count    INTEGER DEFAULT 0
created_by      TEXT
created_at      TIMESTAMPTZ DEFAULT NOW()

`discord_drop_claims`

Records of who claimed which drops.

claim_id        UUID PRIMARY KEY
drop_id         UUID NOT NULL
discord_user_id TEXT NOT NULL
claimed_at      TIMESTAMPTZ DEFAULT NOW()
reward_amount   NUMERIC

Achievement Tables

`discord_achievements`

Achievement definitions per server.

achievement_id  UUID PRIMARY KEY
server_id       TEXT NOT NULL
name            TEXT NOT NULL
description     TEXT
achievement_type TEXT  -- message_count, voice_hours, streak_days, etc.
threshold       INTEGER NOT NULL
reward_amount   NUMERIC
is_active       BOOLEAN DEFAULT TRUE

`discord_user_achievements`

User progress and completion tracking.

server_id       TEXT NOT NULL
discord_user_id TEXT NOT NULL
achievement_id  UUID NOT NULL
progress        INTEGER DEFAULT 0
completed_at    TIMESTAMPTZ
claimed_at      TIMESTAMPTZ

Goal Tracking

`discord_community_goals`

Server-wide goals.

goal_id         UUID PRIMARY KEY
server_id       TEXT NOT NULL
name            TEXT NOT NULL
goal_type       TEXT  -- users, dau, rsnc_distributed, custom
target_value    NUMERIC NOT NULL
current_value   NUMERIC DEFAULT 0
deadline        DATE
completed_at    TIMESTAMPTZ
created_at      TIMESTAMPTZ DEFAULT NOW()

Report Configuration

`discord_report_schedules`

Scheduled report configuration.

server_id       TEXT PRIMARY KEY
frequency       TEXT  -- daily, weekly, monthly
channel_id      TEXT NOT NULL
day_of_week     INTEGER  -- 0-6 for weekly
is_active       BOOLEAN DEFAULT TRUE
last_sent_at    TIMESTAMPTZ

Notes

Row-Level Security (RLS)

All tables use Supabase RLS policies to ensure:

Brands can only access their own data
Server data is isolated
Admin actions are authenticated

discord_role_configs RLS policies:

Policy	Role	Operations
`service_role_full_access_role_configs`	`service_role`	ALL
`anon_read_active_role_configs`	`anon`	SELECT (`is_active = TRUE` only)
`anon_write_role_configs`	`anon`	INSERT
`anon_update_role_configs`	`anon`	UPDATE
`anon_delete_role_configs`	`anon`	DELETE

RLS Visibility vs Unique Constraints

The anon SELECT policy only returns rows where is_active = TRUE. However, the unique_server_role constraint enforces across all rows regardless of RLS visibility. The bot uses DELETE + INSERT (not upsert) when creating milestones to avoid conflicts with invisible inactive rows.

Indexes

Key indexes for performance:

server_id on all tables
brand_id on brand-scoped tables
discord_user_id for user queries
created_at for time-based queries

Data Retention

Audit logs: Retained indefinitely
KPI snapshots: Retained indefinitely
Drop claims: Retained indefinitely
User stats: Retained while server is connected

Core Tables​

discord_server_configs​

discord_role_configs​

discord_role_assignments​

discord_user_stats​

discord_user_streaks​

Admin & Analytics Tables​

discord_audit_log​

discord_kpi_configs​

discord_kpi_snapshots​

Event Configuration​

monitoring_rules​

detection_config Schema​

detection_method Values​

Drop & Engagement Tables​

discord_drops​

discord_drop_claims​

Achievement Tables​

discord_achievements​

discord_user_achievements​

Goal Tracking​

discord_community_goals​

Report Configuration​

discord_report_schedules​

Notes​

Row-Level Security (RLS)​

Indexes​

Data Retention​

Related Documentation​