Database Schema

Database Schema

Schema is managed via drizzle-kit push — no SQL migration files are committed to git. The schema lives in src/db/schema/*.ts and is applied to the database automatically on every container start.

Postgres runs in a Docker container. Local access: localhost:5434

---

auto_channels

Tracks every active auto voice channel and its full state.

Column	Type	Description
id	uuid	Primary key
guild_id	text	Discord guild ID
voice_channel_id	text	Discord voice channel ID (unique)
text_channel_id	text	Discord text channel ID (unique)
owner_user_id	text	Discord user ID of the channel owner
host_user_ids	text[]	Array of host user IDs
allowed_user_ids	text[]	Explicitly allowed users
allowed_role_ids	text[]	Explicitly allowed roles
source_hub_id	text	Hub channel that spawned this
is_locked	boolean	Whether new members can join
is_hidden	boolean	Whether channel is hidden from @everyone
user_limit	integer	Max members (0 = unlimited)
auto_name_enabled	boolean	Whether name tracks rich presence
manual_name	text	Custom name if auto-name is disabled
control_panel_msg_id	text	Message ID of the control panel
scheduled_cleanup_at	timestamp	When the cleanup timer fires
created_at	timestamp	When the channel was created
last_active_at	timestamp	Last member join/leave event

hub_channels

Registry of managed hub voice channels. Authoritative as of 0.7.x — the HUB_CHANNEL_IDS env var is a legacy seed list that runs once on boot when set. Add/remove hubs at runtime via /sudo → Settings → Hub Channels. The set of channel_ids is loaded into a hot-path cache (hubsCache) so isHubChannel() is sync.

Column	Type	Description
id	uuid	Primary key
guild_id	text	Discord guild ID
channel_id	text	Discord channel ID (unique). Updated in place when the reconciler creates a replacement hub.
category_id	text	Parent category ID. Defaults to the channel's current parent or the channel.auto_voice_category setting / AUTO_VOICE_CATEGORY_ID env.
position	integer	Position in category
label	text	Display name (e.g. "➕ Create Voice")
created_at	timestamp

user_profiles

Bot-side member profile. Sudo edits any field via /sudo → Settings → User Profiles or right-click → Manage User → Edit Profile. Members edit their own subset (display name, birthday, opt-outs) via /profile. Rows are upserted lazily on first edit. Birthday pings + future per-user behavior (game prefs, staff request pre-fills) read from this table.

Column	Type	Description
id	uuid	Primary key
guild_id + user_id	text	Unique per user per guild
real_name	text	Sudo only
display_name	text	How the bot refers to the user (self-editable)
birthday_month / birthday_day	integer	Month/day only, no year stored
birthday_pings_enabled	boolean	Default true. /profile exposes a toggle; sudo can also flip it. The birthday scheduler skips opted-out members.
birthday_year_visible	boolean	Default false. Reserved for future age-display feature; no year is currently stored.
staff_category	text	Sudo only — ITSRI Staff, Friend of ITSRI, etc.
department	text	Sudo only — Help Desk, Sales, Leadership, etc.
tier	text	Sudo only — Tier 1/2/3/N/A
leadership_title	text	Sudo only — CEO, COO, CFO, Service Manager
created_at / updated_at	timestamp

staff_approvals

Staff role request queue.

Column	Type	Description
id	uuid
guild_id / user_id	text	Who submitted
requested_data	jsonb	Form data (category, dept, tier, etc.)
approval_msg_id	text	Discord message ID in approval thread
status	text	pending / approved / denied
reviewed_by	text	Sudo user who reviewed
review_note	text	Optional reason

games

Game catalog backing /games and /play. Managed via /sudo → Settings → Games. Loaded into an in-memory cache (loadGames() in services/games.ts) at boot and updated on every panel mutation.

Column	Type	Description
id	uuid	Primary key
guild_id	text	Discord guild ID
name	text	Display name (e.g. "Valorant")
role_id	text	Discord role assigned when a member toggles View access
ping_role_id	text	Discord role assigned when a member toggles LFG pings (separate from view role so they can opt out of pings while keeping channel access)
channel_id	text	Game's primary text channel (where /play posts)
category_id	text	Optional category — reserved for future channel grouping
is_archived	boolean	When true: hidden from /games and rejected by /play
is_visible	boolean	When false: hidden from /games but still resolvable by /play
sort_order	integer	Lower = appears first in /games and the catalog editor
aliases	text[]	Searchable aliases for /play autocomplete + findGameByNameOrAlias()
created_at	timestamp

user_game_prefs

Per-user opt-in state. Toggling either flag in /games or via Manage User → Game Prefs writes here AND adds/removes the matching Discord role on the target member. Cascade-deletes when the parent game is removed.

Column	Type	Description
id	uuid	Primary key
guild_id + user_id	text	Owner of the prefs
game_id	uuid	FK → games.id (onDelete: cascade)
wants_view	boolean	Whether the member should have the role_id assigned
wants_ping	boolean	Whether the member should have the ping_role_id assigned

bot_settings

Runtime-overridable key/value config — overrides the values that would normally come from .env. Edited via /sudo → Settings. Reading any setting falls back to the env value when no row exists.

Column	Type	Description
key	text	Primary key — e.g. channel.log, voice.cleanup_delay_ms, feature.clips_auto_thread
value	text	Stored value (channel ID, integer-as-string, or 0/1 for booleans)
updated_by_discord_id	text	Sudo user who set the override
updated_at	timestamp	Last modification time

Current keys: channel.log, channel.admin, channel.birthday, channel.clips, channel.food, channel.staff_approval_thread, voice.cleanup_delay_ms, feature.clips_auto_thread, feature.food_auto_thread.

sudo_users

Members granted sudo access at runtime, beyond the immutable SUDO_USER_IDS env list. Edited via /sudo → Settings → Sudo Users. The membership cache is loaded into memory at startup and refreshed on changes.

Column	Type	Description
user_id	text	Discord user ID — primary key
added_by_discord_id	text	Sudo user who granted access
added_at	timestamp	When access was granted
note	text	Optional reason (currently Added via /sudo Settings panel)

auto_thread_channels

Channels where every non-bot, non-system message gets a public thread. Edited via /sudo → Settings → Auto Threads. The set of configured channels is loaded into memory at startup and updated on every add/remove from the panel.

Column	Type	Description
channel_id	text	Discord channel ID — primary key
guild_id	text	Discord guild ID
name_template	text	Optional template — supports {author} and {content} placeholders. When null, falls back to {author} — {content}. Reserved for future per-channel UI; not editable in the current panel.
archive_duration	integer	Optional Discord auto-archive duration in minutes (60, 1440, 4320, 10080). When null, defaults to 1440 (24 h).
added_by_discord_id	text	Sudo user who added the channel
added_at	timestamp	When the channel was added