Skip to main content

Database System

RedHarmony uses SQLite for persistent storage and activity tracking. This lightweight yet powerful database system helps maintain interaction history and enforce rate limits across the platform.

Schema Overview​

RedHarmony's database consists of three main tables:

Posts Table​

Tracks all posts created by the system:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id TEXT UNIQUE,        -- Reddit's post identifier
    username TEXT,              -- Bot personality username
    subreddit TEXT,            -- Target subreddit
    post_title TEXT,           -- Post title
    timestamp DATETIME         -- Creation timestamp
)

Comments Table​

Tracks all comments and responses:

CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT,              -- Bot personality username
    comment_id TEXT UNIQUE,     -- Reddit's comment identifier
    post_id TEXT,              -- Parent post identifier
    timestamp DATETIME         -- Creation timestamp
)

Account Activity Table​

Manages rate limiting and activity tracking:

CREATE TABLE account_activity (
    account TEXT PRIMARY KEY,   -- Bot personality username
    last_post_time DATETIME,   -- Last post timestamp
    last_comment_time DATETIME -- Last comment timestamp
)

Tracking Capabilities​

RedHarmony's database system provides:

  1. Post Tracking

    • Unique post identification
    • Creation timestamps
    • Subreddit distribution
    • Author attribution

  2. Comment Tracking

    • Parent-child relationship tracking
    • Comment chains
    • Response timestamps
    • Author attribution

  3. Activity Monitoring

    • Per-account activity tracking
    • Rate limit enforcement
    • Usage patterns and statistics

Database Maintenance​

Regular Maintenance​

  1. Backup Schedule

    # Create a backup of the database
    sqlite3 reddit_bot.db ".backup 'backup-$(date +%Y%m%d).db'"

  2. Database Optimization

    # Optimize the database
    sqlite3 reddit_bot.db "VACUUM;"

  3. Data Cleanup

    # Remove old records (older than 30 days)
    sqlite3 reddit_bot.db "DELETE FROM posts WHERE timestamp < date('now', '-30 days');"
    sqlite3 reddit_bot.db "DELETE FROM comments WHERE timestamp < date('now', '-30 days');"

Best Practices​

  1. Regular Backups

    • Maintain daily backups
    • Store backups in a separate location
    • Keep at least 7 days of backup history

  2. Performance Optimization

    • Run VACUUM monthly
    • Index heavily queried columns
    • Clean up old records periodically

  3. Monitoring

    • Check database size regularly
    • Monitor query performance
    • Track error rates and types

Troubleshooting​

Common issues and solutions:

  1. Database Locked

    • Ensure all connections are properly closed
    • Check for long-running transactions
    • Verify file permissions

  2. Performance Issues

    • Run VACUUM to reclaim space
    • Check and optimize indexes
    • Review and optimize queries

  3. Corruption Recovery

    # Export and reimport database
    sqlite3 reddit_bot.db ".dump" > dump.sql
    sqlite3 new_reddit_bot.db < dump.sql

Security Considerations​

  1. File Permissions

    • Set appropriate file permissions (600)
    • Restrict directory access
    • Regular permission audits

  2. Backup Security

    • Encrypt backup files
    • Secure transfer protocols
    • Access control for backups

  3. Access Control

    • Limit direct database access
    • Use connection pooling
    • Implement query timeouts