Skip to content

Database System

Jump to bottom
AutoBot Solutions edited this page Apr 29, 2026 · 1 revision

Database System

Overview

The database system uses PostgreSQL as the primary database with SQLAlchemy ORM for database operations. It includes migrations via Flask-Migrate and supports all forum data including users, posts, comments, votes, and more.

Database Configuration

Environment Variables

  • DATABASE_URL: PostgreSQL connection string
  • Format: postgresql://user:password@host:port/database
  • Example: postgresql://forumuser:password@localhost:5432/forumdb

SQLAlchemy Configuration

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URL')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

Database Schema

Core Tables

Users Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(64) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash VARCHAR(256) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(256),
    reset_token VARCHAR(256),
    reset_token_expiration TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);

Repositories Table

CREATE TABLE repositories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    github_url VARCHAR(256) UNIQUE NOT NULL,
    stars INTEGER DEFAULT 0,
    language VARCHAR(64),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_repositories_github_url ON repositories(github_url);

Categories Table

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7) DEFAULT '#00f5ff'
);

Posts Table

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(256) NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    repository_id INTEGER REFERENCES repositories(id) ON DELETE SET NULL,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    attachment VARCHAR(256),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    upvotes INTEGER DEFAULT 0,
    downvotes INTEGER DEFAULT 0
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_posts_category_id ON posts(category_id);

Comments Table

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    upvotes INTEGER DEFAULT 0,
    downvotes INTEGER DEFAULT 0
);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_created_at ON comments(created_at DESC);

Votes Table

CREATE TABLE votes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    comment_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    value INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (value IN (1, -1))
);

CREATE UNIQUE INDEX idx_votes_user_post ON votes(user_id, post_id) WHERE post_id IS NOT NULL;
CREATE UNIQUE INDEX idx_votes_user_comment ON votes(user_id, comment_id) WHERE comment_id IS NOT NULL;

Notifications Table

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    link VARCHAR(256),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);

Messages Table

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_messages_sender_id ON messages(sender_id);
CREATE INDEX idx_messages_receiver_id ON messages(receiver_id);
CREATE INDEX idx_messages_is_read ON messages(is_read);

Bookmarks Table

CREATE TABLE bookmarks (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, post_id)
);

CREATE INDEX idx_bookmarks_user_id ON bookmarks(user_id);

Badges Table

CREATE TABLE badges (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64) UNIQUE NOT NULL,
    description TEXT,
    icon VARCHAR(32) DEFAULT '★',
    color VARCHAR(7) DEFAULT '#ff00ff'
);

User_Badges Association Table

CREATE TABLE user_badges (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    badge_id INTEGER NOT NULL REFERENCES badges(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, badge_id)
);

Relationships

One-to-Many Relationships

  • User → Posts
  • User → Comments
  • User → Notifications
  • User → Sent Messages
  • User → Received Messages
  • User → Bookmarks
  • Repository → Posts
  • Category → Posts
  • Post → Comments

Many-to-Many Relationships

  • User ↔ Badges (via user_badges)

One-to-One Relationships

  • None currently

Migrations

Flask-Migrate

  • Database schema versioning
  • Automatic migration generation
  • Upgrade/downgrade support
  • Migration history tracking

Migration Commands

# Generate migration
flask db migrate -m "description"

# Apply migrations
flask db upgrade

# Rollback migration
flask db downgrade

# View migration history
flask db history

Initial Migration

  • Creates all tables
  • Creates indexes
  • Creates constraints
  • Sets up relationships

Database Initialization

init_db.py Script

# Creates admin user
# Creates default categories
# Initializes database
# Run after first migration

Running Initialization

python init_db.py

Database Operations

Query Examples

Get User by Username

user = User.query.filter_by(username='username').first()

Get Posts with Category

posts = Post.query.filter_by(category_id=category_id).all()

Get Unread Notifications

notifications = Notification.query.filter_by(user_id=user.id, is_read=False).all()

Search Posts

posts = Post.query.filter(
    (Post.title.ilike(f'%{query}%')) | 
    (Post.content.ilike(f'%{query}%'))
).all()

Performance Optimization

Indexes

  • Username index
  • Email index
  • User foreign key indexes
  • Created_at indexes for sorting
  • Category index
  • Unique constraints for votes and bookmarks

Query Optimization

  • Lazy loading for relationships
  • Eager loading when needed
  • Query filtering
  • Pagination (future)
  • Query result caching (future)

Connection Pooling

  • SQLAlchemy connection pooling
  • Configurable pool size
  • Automatic connection management
  • Connection reuse

Database Backup

Backup Strategies

  • PostgreSQL pg_dump
  • Scheduled backups
  • Off-site storage
  • Point-in-time recovery

Backup Commands

# Full backup
pg_dump -U username -d forumdb > backup.sql

# Restore
psql -U username -d forumdb < backup.sql

Database Security

Access Control

  • Database user with limited privileges
  • No direct web access to database
  • Connection string in environment variables
  • SSL/TLS for database connections

Data Protection

  • Passwords hashed before storage
  • No plain text passwords
  • Sensitive data in environment variables
  • Regular security audits

Database Maintenance

Regular Tasks

  • Vacuum and analyze tables
  • Reindex indexes
  • Update statistics
  • Monitor disk space
  • Check for bloat

Maintenance Commands

# Vacuum and analyze
VACUUM ANALYZE;

# Reindex
REINDEX DATABASE forumdb;

# Update statistics
ANALYZE;

Database Monitoring

Metrics to Monitor

  • Connection pool usage
  • Query performance
  • Table sizes
  • Index usage
  • Disk space
  • Slow queries

Monitoring Tools

  • PostgreSQL logs
  • Query performance logs
  • Connection pool logs
  • External monitoring (future)

Future Enhancements

Database Features

  • Read replicas for scaling
  • Partitioning for large tables
  • Full-text search (PostgreSQL)
  • JSONB fields for flexible data
  • Materialized views for common queries
  • Database triggers for automation
  • Stored procedures for complex operations

Data Integrity

  • Foreign key constraints
  • Check constraints
  • Unique constraints
  • Not null constraints
  • Data validation at database level

Performance

  • Connection pooling optimization
  • Query result caching (Redis)
  • Database connection monitoring
  • Automatic failover
  • Load balancing

Clone this wiki locally