Database Setup

Faculty Bot uses PostgreSQL to store user data, XP levels, verification records, and more.

Prerequisites

PostgreSQL 13 or later
Database user with CREATE privileges
Network access to PostgreSQL server

PostgreSQL Installation

Ubuntu/Debian
CentOS/RHEL
macOS
Docker

# Install PostgreSQL 13
sudo apt-get update
sudo apt-get install -y postgresql-13 postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
sudo systemctl status postgresql

# Install PostgreSQL repository
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL 13
sudo yum install -y postgresql13-server postgresql13-contrib

# Initialize database
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

# Start PostgreSQL service
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

# Install via Homebrew
brew install postgresql@13

# Start PostgreSQL service
brew services start postgresql@13

# Verify installation
psql --version

# Run PostgreSQL in a container
docker run -d \
  --name faculty_postgres \
  -e POSTGRES_USER=faculty_manager \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=faculty_manager \
  -p 5432:5432 \
  -v faculty_data:/var/lib/postgresql/data \
  postgres:13

# Verify container is running
docker ps | grep faculty_postgres

Database Configuration

Create database user

sudo -u postgres psql

In the PostgreSQL prompt:

CREATE USER faculty_manager WITH PASSWORD 'your_secure_password';
ALTER USER faculty_manager WITH SUPERUSER;

Use a strong password and store it securely. Never commit passwords to version control.

Create database

CREATE DATABASE faculty_manager OWNER faculty_manager;
GRANT ALL PRIVILEGES ON DATABASE faculty_manager TO faculty_manager;

Configure PostgreSQL authentication

Edit pg_hba.conf to allow password authentication:

# Location varies by system
# Ubuntu/Debian: /etc/postgresql/13/main/pg_hba.conf
# CentOS/RHEL: /var/lib/pgsql/13/data/pg_hba.conf
sudo nano /etc/postgresql/13/main/pg_hba.conf

Add or modify this line:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    faculty_manager faculty_manager 127.0.0.1/32           md5

Reload PostgreSQL:

sudo systemctl reload postgresql

Test connection

psql -U faculty_manager -d faculty_manager -h localhost

Enter the password when prompted. If successful, you’ll see the PostgreSQL prompt.

Database Schema

Faculty Bot uses the following database schema:

Tables Overview

verified_users - User Verification Records

Stores verified user information and their associated email addresses.

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

Columns:

user_id: Discord user ID (Primary Key)
user_email: Verified student email address

user_xp - Experience and Leveling System

Tracks user experience points and levels.

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
);

Columns:

user_id: Discord user ID (Primary Key)
user_xp: Total experience points (floating point)
user_level: Current user level (integer)

voice_channels - Temporary Voice Channel Management

Manages dynamically created 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
);

Columns:

channel_id: Discord channel ID (Primary Key)
owner_id: Discord user ID of channel creator
deletion_marker: Flag for channels pending deletion

mensaplan - Meal Plan Tracking

Tracks posted meal plans 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 ''
);

Columns:

date: Date of meal plan (Primary Key)
posted: Whether the plan has been posted
plan_hash: Hash of the meal plan PDF for change detection

ads - Advertisement Management

Tracks advertisements for automatic deletion.

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

Columns:

message_id: Discord message ID (Primary Key)
posted_at: Timestamp when ad was posted

rules - Server Rules Storage

Stores server rules with versioning.

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

Columns:

rule_number: Rule identifier (Primary Key)
rule_text: Content of the rule

semestermods - Semester Moderators

Tracks users with semester moderator permissions.

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

Columns:

user_id: Discord user ID (Primary Key)

posted_rss - RSS Feed Tracking

Prevents duplicate RSS feed posts.

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

Columns:

message_id: Discord message ID (Primary Key)
rss_title: Title of the RSS item
channel_id: Channel where item was posted

Running Migrations

Locate migration file

The migration script is located at migrations/faculty_manager.sql in the repository.

Apply migrations

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

You should see output like:

CREATE TABLE
CREATE TABLE
CREATE TABLE
...

Verify tables were created

psql -U faculty_manager -d faculty_manager -h localhost -c "\dt"

Expected output:

              List of relations
 Schema |      Name       | Type  |      Owner
--------+-----------------+-------+-----------------
 public | ads             | table | faculty_manager
 public | mensaplan       | table | faculty_manager
 public | posted_rss      | table | faculty_manager
 public | rules           | table | faculty_manager
 public | semestermods    | table | faculty_manager
 public | user_xp         | table | faculty_manager
 public | verified_users  | table | faculty_manager
 public | voice_channels  | table | faculty_manager

Connection Configuration

Configure the database connection in your .env file:

# Standard PostgreSQL connection format
DATABASE_URL=postgres://username:password@host:port/database

# Example for local PostgreSQL
DATABASE_URL=postgres://faculty_manager:averysecurepasswordyes@localhost:5432/faculty_manager

# Example for remote PostgreSQL
DATABASE_URL=postgres://faculty_manager:password@db.example.com:5432/faculty_manager

# Example with SSL (recommended for production)
DATABASE_URL=postgres://faculty_manager:password@db.example.com:5432/faculty_manager?sslmode=require

The connection string format is: postgres://USER:PASSWORD@HOST:PORT/DATABASE

Docker Compose Database Setup

For automated database initialization with Docker:

version: "3"
services:
  database:
    image: postgres:13
    ports: [5432:5432]
    environment:
      POSTGRES_USER: faculty_manager
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: faculty_manager
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./migrations/faculty_manager.sql:/docker-entrypoint-initdb.d/init.sql
    networks:
      - bot

volumes:
  postgres_data:

networks:
  bot: {}

The migration script in /docker-entrypoint-initdb.d/ runs automatically on first container startup.

Database Maintenance

Backup Database

# Full database backup
pg_dump -U faculty_manager -d faculty_manager -h localhost > faculty_manager_backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U faculty_manager -d faculty_manager -h localhost | gzip > faculty_manager_backup_$(date +%Y%m%d).sql.gz

Restore Database

# From uncompressed backup
psql -U faculty_manager -d faculty_manager -h localhost < faculty_manager_backup.sql

# From compressed backup
gunzip -c faculty_manager_backup.sql.gz | psql -U faculty_manager -d faculty_manager -h localhost

Vacuum and Analyze

Optimize database performance:

psql -U faculty_manager -d faculty_manager -h localhost -c "VACUUM ANALYZE;"

Monitoring and Performance

Check Database Size

SELECT pg_size_pretty(pg_database_size('faculty_manager'));

Check Table Sizes

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Monitor Active Connections

SELECT
    count(*) as connection_count,
    state
FROM pg_stat_activity
WHERE datname = 'faculty_manager'
GROUP BY state;

View Slow Queries

Enable query logging in postgresql.conf:

log_min_duration_statement = 1000  # Log queries taking > 1 second
log_statement = 'all'

Troubleshooting

Connection refused errors

Symptoms: could not connect to server: Connection refusedSolutions:

Check if PostgreSQL is running: sudo systemctl status postgresql
Verify PostgreSQL is listening on port 5432: sudo netstat -plnt | grep 5432

Check postgresql.conf for listen_addresses:

listen_addresses = 'localhost'  # or '*' for all interfaces

Restart PostgreSQL: sudo systemctl restart postgresql

Authentication failed errors

Symptoms: FATAL: password authentication failed for userSolutions:

Verify username and password in DATABASE_URL
Check pg_hba.conf has correct authentication method (md5 or scram-sha-256)
Test connection manually: psql -U faculty_manager -d faculty_manager -h localhost

Reset password if needed:

ALTER USER faculty_manager WITH PASSWORD 'new_password';

Permission denied on table operations

Symptoms: ERROR: permission denied for tableSolutions:

Grant necessary permissions:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO faculty_manager;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO faculty_manager;

Verify table ownership:

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';

Database disk space issues

Symptoms: ERROR: could not extend file ... No space left on deviceSolutions:

Check disk usage: df -h

Clean up old data:

-- Remove old ad records
DELETE FROM ads WHERE posted_at < NOW() - INTERVAL '30 days';

-- Remove old meal plans
DELETE FROM mensaplan WHERE date < NOW() - INTERVAL '90 days';

Run VACUUM to reclaim space:
```
VACUUM FULL;
```

Migration script fails

Symptoms: Errors when running faculty_manager.sqlSolutions:

Check if tables already exist:

psql -U faculty_manager -d faculty_manager -c "\dt"

Drop and recreate database if needed:

DROP DATABASE faculty_manager;
CREATE DATABASE faculty_manager OWNER faculty_manager;

Verify SQL syntax in migration file
Check PostgreSQL logs for detailed error messages

Too many open connections

Symptoms: FATAL: sorry, too many clients alreadySolutions:

Check current connections:
```
SELECT count(*) FROM pg_stat_activity;
```
Increase max_connections in postgresql.conf:
```
max_connections = 100
```
Restart PostgreSQL: sudo systemctl restart postgresql
Implement connection pooling (PgBouncer)

Security Best Practices

Follow these security guidelines for production databases:

Use strong, unique passwords for database users
Restrict network access using pg_hba.conf
Enable SSL/TLS for remote connections
Regularly update PostgreSQL to latest patch version
Implement automated backups with off-site storage
Use separate database users for different applications
Enable audit logging for sensitive operations
Set up monitoring and alerting for suspicious activity

Enable SSL Connections

In postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

In pg_hba.conf:

hostssl  faculty_manager  faculty_manager  0.0.0.0/0  md5

Update DATABASE_URL:

DATABASE_URL=postgres://faculty_manager:password@host:5432/faculty_manager?sslmode=require

Production Recommendations

Automated Backups

Set up daily automated backups with cron:

0 2 * * * pg_dump -U faculty_manager faculty_manager | gzip > /backups/faculty_$(date +\%Y\%m\%d).sql.gz

Monitoring

Use tools like:

pgAdmin for GUI management
pg_stat_statements for query analysis
Prometheus + postgres_exporter for metrics

Connection Pooling

Use PgBouncer to manage connection pooling:

apt-get install pgbouncer

Update DATABASE_URL to point to PgBouncer port

Replication

Set up streaming replication for high availability:

Configure primary and standby servers
Enable WAL archiving
Set up automatic failover

Next Steps

Docker Deployment

Deploy with Docker for simplified setup

Manual Deployment

Deploy manually with process managers

Get Started

Configuration

Features

Deployment

Prerequisites

PostgreSQL Installation

Database Configuration

Database Schema

Tables Overview

Running Migrations

Connection Configuration

Docker Compose Database Setup

Database Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Monitoring and Performance

Check Database Size

Check Table Sizes

Monitor Active Connections

View Slow Queries

Troubleshooting

Security Best Practices

Enable SSL Connections

Production Recommendations

Automated Backups

Monitoring

Connection Pooling

Replication

Next Steps

Docker Deployment

Manual Deployment

Build docs developers (and LLMs) love

Get Started

Configuration

Features

Deployment

​Prerequisites

​PostgreSQL Installation

​Database Configuration

​Database Schema

​Tables Overview

​Running Migrations

​Connection Configuration

​Docker Compose Database Setup

​Database Maintenance

​Backup Database

​Restore Database

​Vacuum and Analyze

​Monitoring and Performance

​Check Database Size

​Check Table Sizes

​Monitor Active Connections

​View Slow Queries

​Troubleshooting

​Security Best Practices

​Enable SSL Connections

​Production Recommendations

Automated Backups

Monitoring

Connection Pooling

Replication

​Next Steps

Docker Deployment

Manual Deployment

Build docs developers (and LLMs) love

Prerequisites

PostgreSQL Installation

Database Configuration

Database Schema

Tables Overview

Running Migrations

Connection Configuration

Docker Compose Database Setup

Database Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Monitoring and Performance

Check Database Size

Check Table Sizes

Monitor Active Connections

View Slow Queries

Troubleshooting

Security Best Practices

Enable SSL Connections

Production Recommendations

Next Steps