Skip to main content

Database Schema

The Loyalteez 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-based reward configurations.

server_id       TEXT
discord_role_id TEXT
effect_type     TEXT  -- multiplier, flat_bonus, cooldown_reduction
effect_value    NUMERIC
priority        INTEGER

discord_user_stats​

Tracks per-user statistics.

server_id       TEXT
discord_user_id TEXT
messages_count  INTEGER
voice_minutes   INTEGER
reactions_count INTEGER
ltz_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
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.

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, ltz_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

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
Last updated on