Database Schema

Database Schema

Schema is managed via drizzle-kit push — no SQL migration files are committed to git. It lives in src/db/schema/*.ts and is applied automatically on every container start (the Docker entrypoint runs drizzle-kit push --force).

Postgres runs in a Docker container with no host port mapping (security). To reach it from the VPS host: docker compose exec db psql -U squishybot squishybot (or squishybot db:shell). botpanel reaches it over the shared botpanel-net network at db-squishy:5432.

There are 19 tables. They group into voice, profiles/staff, games, social/roles/archive, reaction roles, reporting, and runtime config.

---

Voice

auto_channels

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	Attached text channel ID (unique)
owner_user_id	text	Channel owner
acting_owner_user_id	text	Temporary driver during an owner-grace window
owner_grace_expires_at	timestamp	When the owner-grace window ends
host_user_ids	text[]	Host user IDs
allowed_user_ids / allowed_role_ids	text[]	Explicit allow lists
source_hub_id	text	Hub that spawned this channel
is_locked / is_hidden	boolean	@everyone Connect / View state
user_limit	integer	Max members (0 = unlimited)
auto_name_enabled	boolean	Whether the name tracks rich presence
name_template	text	Active template key (auto / counter / squad / …)
manual_name / fallback_name	text	Custom name / fallback when nobody is playing
control_panel_msg_id	text	Control-panel message ID
scheduled_cleanup_at	timestamp	When the cleanup timer fires
created_at / last_active_at	timestamp

auto_channel_members

Backs the control panel's "In channel" list. Written on join (upsert) / leave (delete); the reconciler backfills current members at now() on boot.

Column	Type	Description
voice_channel_id + user_id	text	Composite primary key
joined_at	timestamp	Join time (relative <t:N:R> in the panel)

hub_channels

Registry of managed hub voice channels — DB-authoritative; HUB_CHANNEL_IDS env is a one-time seed. Loaded into a hot-path cache so isHubChannel() is sync.

Column	Type	Description
id	uuid	Primary key
guild_id	text
channel_id	text	Discord channel ID (unique). Updated in place when the reconciler creates a replacement hub.
category_id	text	Parent category
position	integer	Position in category
label	text	Display name
default_template_key / default_manual_name / default_user_limit	text/text/int	Per-hub defaults for spawned channels
lockdown_until	timestamp	Per-hub lockdown expiry
created_at	timestamp

---

Profiles & staff

user_profiles

Bot-side member profile. Members edit a subset via /settings → Profile & Birthday; sudo edits all fields via /sudo → Settings → User Profiles or right-click → Manage → Edit Profile. Upserted lazily on first edit.

Column	Type	Description
id	uuid	Primary key
guild_id + user_id	text	Unique per user per guild
display_name	text	How the bot refers to the user (self-editable)
real_name	text	Sudo only
birthday_month / birthday_day	integer	Month/day only
birthday_pings_enabled	boolean	Default true; the scheduler skips opted-out members
birthday_year_visible	boolean	Default false; reserved for future age display
staff_category / department / tier / leadership_title	text	Sudo only
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	New flow: { role_key, role_label, real_name, reason }; legacy rows: { category, department, tier, … }
approval_msg_id	text	Card message ID in the approvals thread
status	text	pending / approved / denied
reviewed_by	text	Reviewer
review_note	text	Optional
created_at / reviewed_at	timestamp

---

Games

games

Catalog backing /games and /play. Managed via /sudo → Settings → Games; loaded into an in-memory cache at boot.

Column	Type	Description
id	uuid	Primary key
guild_id	text
name	text	Display name
role_id	text	View role (toggled by /games View)
ping_role_id	text	LFG ping role (toggled by /games Pings; pinged by /play)
channel_id	text	Where /play posts
category_id	text	Optional parent category for auto-provisioned channels
is_archived	boolean	Hidden from /games, rejected by /play
is_visible	boolean	When false: hidden from /games but resolvable by /play
sort_order	integer	Lower = first
aliases	text[]	Autocomplete + findGameByNameOrAlias()
created_at	timestamp

user_game_prefs

Per-user opt-in state. Toggling writes here AND syncs the Discord role on the target. Cascade-deletes with the parent game.

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 / wants_ping	boolean	Desired role state

---

Roles, social & archive

auto_join_roles

Roles applied to every new member on guildMemberAdd. Gated by feature.auto_role_on_join (default off). Managed via /sudo → Settings → Auto Roles.

Column	Type	Description
role_id	text	Primary key
guild_id	text
added_at / added_by_user_id	timestamp/text

color_roles

Curated color-only roles for /color. Gated by feature.color_roles (default off).

Column	Type	Description
role_id	text	Primary key
guild_id	text
label	text	Shown in the /color picker
sort_order	integer	Default 0
added_at	timestamp

social_feeds

RSS-backed feeds the poller fetches and reposts. Loaded into a cache at boot; deduped by last_seen_id.

Column	Type	Description
id	uuid	Primary key
guild_id	text
label	text	e.g. "ITSupportRI Instagram"
source_url	text	rss.app (or any RSS) feed URL
channel_id	text	Where new items post
enabled	boolean	Default true
last_seen_id	text	Dedupe cursor (seeded on add so the backlog isn't replayed)
last_polled_at / last_error	timestamp/text	Diagnostics
max_items_per_poll	integer	0 = latest only; sudo cap 3; bot owner unlimited
created_by_discord_id / created_at	text/timestamp

archive_eligible_categories

Categories sudo has opted into the channel-archive workflow.

Column	Type	Description
id	uuid	Primary key
guild_id	text
category_id	text	Unique
added_by_user_id / added_at	text/timestamp

archived_channels

Channels currently archived; holds the data needed to restore them.

Column	Type	Description
channel_id	text	Primary key
guild_id	text
original_category_id / original_name	text	Restore target
archived_at / archived_by_user_id	timestamp/text

---

Reaction roles

reaction_role_messages

One row per watched message. expires_at ⇒ temporary (game-night) mode; a daily check deletes the message + rows at expiry.

Column	Type	Description
id	uuid	Primary key
guild_id / channel_id	text
message_id	text	Unique
anchor_role_id	text	Optional anchor; temp roles get bumped one position above it
expires_at	timestamp	Set ⇒ temporary
created_by_user_id / created_at	text/timestamp

reaction_role_mappings

(emoji, role) pairs per message.

Column	Type	Description
id	uuid	Primary key
message_pk	uuid	FK → reaction_role_messages.id
emoji	text	Unicode emoji or custom-emoji ID
role_id	text	Role to toggle

---

Reporting & config

report_log

Append-only log of every /report submission. The bot-owner triage view reads recent rows; Approve/Reject updates status and github_issue_url.

Column	Type	Description
id	uuid	Primary key
guild_id / user_id	text
title / report_type / description / steps	text	Modal fields
status	text	Default pending
github_issue_url	text	Set when filed
decided_by_user_id / decided_at	text/timestamp
created_at	timestamp

bot_settings

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

Column	Type	Description
key	text	Primary key
value	text	Channel ID, integer-as-string, or 0/1 for booleans
updated_by_discord_id	text	Sudo who set the override
updated_at	timestamp

Representative keys in use today: channel.log, channel.admin, channel.birthday, channel.staff_approval_thread, channel.auto_voice_category, channel.archive_destination, channel.games_category, channel.games_prefix; voice.cleanup_delay_ms, voice.owner_grace_ms, voice.no_voice_chat_messages, voice.guild_lockdown_until; birthday.target_hour, birthday.last_run_date; welcome.enabled / welcome.channel_id / welcome.template, goodbye.enabled / goodbye.channel_id / goodbye.template; archive.stale_days; the feature flags feature.auto_voice, feature.auto_threads, feature.social_poller, feature.presence_renames, feature.birthday_pings, feature.auto_role_on_join, feature.color_roles; and the seven staff.role.* links.

sudo_users

Members granted sudo at runtime, beyond the immutable SUDO_USER_IDS env list. Cached at startup.

Column	Type	Description
user_id	text	Primary key
added_by_discord_id / added_at	text/timestamp
note	text	Optional

auto_thread_channels

Channels where every non-bot, non-system message gets a public thread. Edited via /sudo → Settings → Auto Threads; cached at startup.

Column	Type	Description
channel_id	text	Primary key
guild_id	text
name_template	text	Optional {author} / {content} template (null → {author} — {content})
archive_duration	integer	Optional auto-archive minutes (60 / 1440 / 4320 / 10080; null → 1440)
added_by_discord_id / added_at	text/timestamp

setting_changes

Audit trail for bot_settings edits — one row per successful set/clear. Rendered in /sudo → Settings → Debug → Audit log. Manual retention.

Column	Type	Description
id	uuid	Primary key
key	text	Which setting
old_value / new_value	text
changed_by_user_id / changed_at	text/timestamp