# 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
```python
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URL')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
```

## Database Schema

### Core Tables

#### Users Table
```sql
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
```sql
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
```sql
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7) DEFAULT '#00f5ff'
);
```

#### Posts Table
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```bash
# 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
```python
# Creates admin user
# Creates default categories
# Initializes database
# Run after first migration
```

### Running Initialization
```bash
python init_db.py
```

## Database Operations

### Query Examples

**Get User by Username**
```python
user = User.query.filter_by(username='username').first()
```

**Get Posts with Category**
```python
posts = Post.query.filter_by(category_id=category_id).all()
```

**Get Unread Notifications**
```python
notifications = Notification.query.filter_by(user_id=user.id, is_read=False).all()
```

**Search Posts**
```python
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
```bash
# 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
```bash
# 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