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.

Column	Type	Description
id	uuid	Primary key
guild_id	text	Discord guild ID
channel_id	text	Discord channel ID (unique)
category_id	text	Parent category ID
position	integer	Position in category
label	text	Display name (e.g. "➕ Create Voice")
created_at	timestamp

user_profiles

Future: staff info, birthdays, display names. Currently defined but not used by active features.

Column	Type	Description
id	uuid
guild_id + user_id	text	Unique per user per guild
real_name	text
display_name	text
birthday_month / birthday_day	integer	Month/day only, no year
staff_category	text	ITSRI Staff, Friend of ITSRI, etc.
department	text	Help Desk, Sales, Leadership, etc.
tier	text	Tier 1/2/3/N/A
leadership_title	text	CEO, COO, CFO, Service Manager

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 (future)

Column	Description
name, role_id, channel_id, category_id	Game info
ping_role_id	Separate role for LTP pings
is_archived, is_visible	Visibility controls
sort_order, aliases	Display ordering and search

user_game_prefs (future)

Per-user opt-in for game view and ping roles.

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