Database Schema

Overview

Faculty Bot uses PostgreSQL as its primary data store. The database schema is defined in migrations/faculty_manager.sql and mapped to Rust structs using SQLx.

Database Tables

verified_users

Stores user verification data linking Discord accounts to student emails.

CREATE TABLE IF NOT EXISTS verified_users (
    user_id BIGINT PRIMARY KEY NOT NULL,
    user_email TEXT NOT NULL
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct VerifiedUsers {
    pub user_id: i64,        // Discord user ID
    pub user_email: String,  // Student email address
}

Purpose:

Track which users have verified their student status
Prevent duplicate verifications
Allow staff to look up user emails

Usage Example:

// Check if user is verified
let user = sqlx::query_as::<_, structs::VerifiedUsers>(
    "SELECT * FROM verified_users WHERE user_id = $1"
)
.bind(user_id)
.fetch_optional(pool)
.await?;

user_xp

Tracks user experience points and levels for the gamification system.

CREATE TABLE IF NOT EXISTS user_xp (
    user_id BIGINT PRIMARY KEY NOT NULL,
    user_xp FLOAT8 NOT NULL DEFAULT 0.0,
    user_level INTEGER NOT NULL DEFAULT 0
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct UserXP {
    pub user_id: i64,
    pub user_xp: f64,
    pub user_level: i32,
}

impl Default for UserXP {
    fn default() -> Self {
        Self {
            user_id: 0,
            user_xp: 0.0,
            user_level: 0,
        }
    }
}

XP Calculation: XP is awarded based on message length with logarithmic scaling:

src/eventhandler.rs

// Scaling formula: xp = base_xp / (1 + scale_factor * ln(level))
let scaling_factor = data.config.general.xp_scaling_factor;
let base_xp = content_len as f64 / data.config.general.chars_for_level as f64;
let xp_to_add = base_xp / (1.0 + scaling_factor * (user_data.user_level as f64).ln());

// Level up at every 100 XP
let new_level = (new_xp / 100.0).floor() as i32;

See eventhandler.rs:86-112

voice_channels

Tracks dynamically created temporary voice channels.

CREATE TABLE IF NOT EXISTS voice_channels (
    channel_id BIGINT PRIMARY KEY NOT NULL,
    owner_id BIGINT NOT NULL,
    deletion_marker BOOL NOT NULL DEFAULT FALSE
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct VoiceChannels {
    pub channel_id: i64,
    pub owner_id: i64,
    pub deletion_marker: bool,
}

Lifecycle:

User joins the “Create Channel” voice channel
Bot creates a new voice channel owned by the user
Entry added to voice_channels table
When channel becomes empty, bot deletes it and removes entry

See eventhandler.rs:145-250

mensaplan

Tracks meal plan posting history to prevent duplicates.

CREATE TABLE IF NOT EXISTS mensaplan (
    date TEXT PRIMARY KEY NOT NULL,
    posted BOOL NOT NULL DEFAULT FALSE,
    plan_hash TEXT NOT NULL DEFAULT ''
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct Mensaplan {
    pub date: String,      // Format: YYYY-MM-DD
    pub posted: bool,      // Has been posted today?
    pub plan_hash: String, // Hash of plan to detect updates
}

Usage:

src/tasks.rs

// Check if already posted today
let mensaplan_posted = sqlx::query_as::<sqlx::Postgres, structs::Mensaplan>(
    "SELECT * FROM mensaplan WHERE date = $1"
)
.bind(&today)
.fetch_optional(&data.db)
.await?
.map(|row| row.posted)
.unwrap_or(false);

See tasks.rs:54-63

Tracks advertisement messages for automatic deletion.

CREATE TABLE IF NOT EXISTS ads (
    message_id BIGINT PRIMARY KEY NOT NULL,
    posted_at TIME DEFAULT CURRENT_TIME
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct Ads {
    pub message_id: i64,
    pub posted_at: chrono::NaiveTime,
}

Purpose:

Track when ads are posted
Enable automatic deletion after configured timeout
Configured via config.json → general.adstimeout

rules

Stores server rules that can be managed via commands.

CREATE TABLE IF NOT EXISTS rules (
    rule_number INTEGER PRIMARY KEY NOT NULL UNIQUE,
    rule_text TEXT NOT NULL
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct Rules {
    pub rule_number: i64,
    pub rule_text: String,
}

Management Commands:

/rule add <text>         // Add new rule
/rule remove <number>    // Delete rule
/rule edit <number> <text> // Update rule
/rule get <number>       // View specific rule
/rule list               // View all rules
/rule post [channel]     // Post rules embed

See administration.rs:223-487

semestermods

Tracks users with semester moderator privileges.

CREATE TABLE IF NOT EXISTS semestermods (
    user_id BIGINT PRIMARY KEY NOT NULL
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct Semestermods {
    pub user_id: i64,
}

Purpose:

Track semester moderators beyond Discord roles
Enable additional permission checks in commands
Used alongside staffrole for authorization

posted_rss

Tracks RSS feed items that have been posted to prevent duplicates.

CREATE TABLE IF NOT EXISTS posted_rss (
    message_id BIGINT PRIMARY KEY NOT NULL,
    rss_title TEXT NOT NULL,
    channel_id BIGINT NOT NULL
);

Rust Struct:

src/structs.rs

#[derive(Debug, FromRow)]
pub struct Rss {
    pub rss_title: String,
    pub message_id: i64,
    pub channel_id: i64,
}

RSS Task Flow:

Fetch RSS feed items
Check if rss_title exists in posted_rss for this channel
If new: post and insert into database
If exists: check timestamp and update if newer

See tasks.rs:119-340

Database Migrations

To initialize the database:

psql -U faculty_manager -d faculty_manager -f migrations/faculty_manager.sql

The migration file contains all table definitions with IF NOT EXISTS checks, making it safe to run multiple times.

Connection Pooling

The bot uses SQLx connection pooling for efficient database access:

src/main.rs

let pool = PgPoolOptions::new()
    .max_connections(15)  // Maximum concurrent connections
    .connect(&db_url)
    .await?;

See main.rs:171-175

Query Patterns

Insert or Update (Upsert)

sqlx::query(
    "INSERT INTO user_xp (user_id, user_xp) VALUES ($1, $2)
    ON CONFLICT (user_id) DO UPDATE SET user_xp = $2"
)
.bind(user_id)
.bind(new_xp)
.execute(pool)
.await?;

Fetch Optional

let user = sqlx::query_as::<_, structs::UserXP>(
    "SELECT * FROM user_xp WHERE user_id = $1"
)
.bind(user_id)
.fetch_optional(pool)
.await?
.unwrap_or_default();  // Use default if not found

Fetch Multiple

let users = sqlx::query_as::<_, structs::UserXP>(
    "SELECT * FROM user_xp ORDER BY user_xp DESC LIMIT 10"
)
.fetch_all(pool)
.await?;

Type Safety

SQLx provides compile-time verification of SQL queries when used with the query! macro:

// Compile-time checked (requires DATABASE_URL at build time)
let user = sqlx::query!("SELECT * FROM user_xp WHERE user_id = $1", user_id)
    .fetch_optional(pool)
    .await?;

// Runtime checked (used in Faculty Bot)
let user = sqlx::query_as::<_, structs::UserXP>(
    "SELECT * FROM user_xp WHERE user_id = $1"
)
.bind(user_id)
.fetch_optional(pool)
.await?;

Faculty Bot uses query_as for flexibility during development.

Database Access in Commands

Access the database pool through the context:

#[poise::command(slash_command)]
pub async fn my_command(ctx: Context<'_>) -> Result<(), Error> {
    let pool = &ctx.data().db;
    
    let user = sqlx::query_as::<_, structs::UserXP>(
        "SELECT * FROM user_xp WHERE user_id = $1"
    )
    .bind(ctx.author().id.0 as i64)
    .fetch_optional(pool)
    .await
    .map_err(Error::Database)?;
    
    Ok(())
}

Indexing Considerations

All tables use primary keys for efficient lookups:

user_id: Primary key in verified_users, user_xp, semestermods
channel_id: Primary key in voice_channels
message_id: Primary key in ads, posted_rss
date: Primary key in mensaplan
rule_number: Primary key with UNIQUE constraint in rules

No additional indexes are currently needed given the query patterns and data volumes.

Contributing

Guides

Overview

Database Tables

verified_users

user_xp

voice_channels

mensaplan

ads

rules

semestermods

posted_rss

Database Migrations

Connection Pooling

Query Patterns

Insert or Update (Upsert)

Fetch Optional

Fetch Multiple

Type Safety

Database Access in Commands

Indexing Considerations

Build docs developers (and LLMs) love

Contributing

Guides

​Overview

​Database Tables

​verified_users

​user_xp

​voice_channels

​mensaplan

​ads

​rules

​semestermods

​posted_rss

​Database Migrations

​Connection Pooling

​Query Patterns

​Insert or Update (Upsert)

​Fetch Optional

​Fetch Multiple

​Type Safety

​Database Access in Commands

​Indexing Considerations

Build docs developers (and LLMs) love

Overview

Database Tables

verified_users

user_xp

voice_channels

mensaplan

ads

rules

semestermods

posted_rss

Database Migrations

Connection Pooling

Query Patterns

Insert or Update (Upsert)

Fetch Optional

Fetch Multiple

Type Safety

Database Access in Commands

Indexing Considerations