Rustelo/migrations

Database Migrations

This directory contains SQL migration files for the Rustelo application database setup.

Overview

The migration system sets up a complete database with authentication, content management, and auditing capabilities. Rustelo now supports both PostgreSQL and SQLite through a database-agnostic architecture.

Migration Files

Rustelo provides database-specific migration files to support both PostgreSQL and SQLite:

Database-Specific Files

• 001_initial_setup_postgres.sql - PostgreSQL-optimized schema
• 001_initial_setup_sqlite.sql - SQLite-optimized schema
• 002_add_2fa_support_postgres.sql - PostgreSQL 2FA tables
• 002_add_2fa_support_sqlite.sql - SQLite 2FA tables
• 003_rbac_system_postgres.sql - PostgreSQL RBAC system

Legacy Files

• 001_initial_setup.sql - Legacy unified file (deprecated)
• 002_add_2fa_support.sql - Legacy 2FA file (deprecated)

The migration system automatically detects your database type from the connection URL and runs the appropriate migration files.

Schema Features

Authentication Tables

• users - Core user accounts and profile information
• user_roles - User role assignments for RBAC (Role-Based Access Control)
• oauth_accounts - External OAuth provider account links
• sessions - User session management
• tokens - Security tokens for password reset, email verification, etc.
• permissions - System permissions for fine-grained access control
• role_permissions - Role to permission mappings
• user_audit_log - Audit trail for user actions

Content Management Tables

• page_contents - Main content management table for pages, posts, and other content

Key Features

• UUID Primary Keys - All tables use UUID primary keys for better security
• Comprehensive Indexing - Optimized indexes for performance
• Full-Text Search - PostgreSQL full-text search capabilities
• Audit Logging - Complete audit trail for user actions
• Role-Based Access Control - Flexible permission system
• Automatic Timestamps - Automatic created_at/updated_at handling
• Data Validation - Comprehensive constraints and validation rules

Database Schema

User Management

users (id, email, username, password_hash, display_name, ...)
user_roles (user_id, role)
oauth_accounts (id, user_id, provider, provider_id, ...)
sessions (id, user_id, expires_at, ...)
tokens (id, user_id, token_type, token_hash, ...)

Authorization

permissions (id, name, description, resource, action)
role_permissions (role, permission_id)
user_audit_log (id, user_id, action, resource, ...)

Content Management

page_contents (id, slug, title, content, author_id, ...)

Default Data

The migration includes default data:

User Roles

• admin - Full system access
• moderator - Content management and user oversight
• user - Basic content creation
• guest - Read-only access

Default Admin User

• Username: admin
• Email: admin@example.com
• Password: admin123 (⚠️ CHANGE THIS IMMEDIATELY IN PRODUCTION!)

Sample Content

• Welcome page
• About page
• Sample blog post

Functions and Triggers

Automatic Triggers

• update_updated_at_column() - Updates timestamps automatically
• assign_default_role() - Assigns default role to new users

Utility Functions

• log_user_action() - Logs user actions for auditing
• cleanup_expired_auth_data() - Cleans up expired sessions and tokens

Running Migrations

Using the Built-in Migration Runner (Recommended)

# The application automatically runs migrations on startup
cargo run

# Or use the database tool
cargo run --bin db_tool -- migrate

Database Type Detection

The migration system automatically detects your database type from the DATABASE_URL:

# PostgreSQL - runs *_postgres.sql files
DATABASE_URL=postgresql://user:pass@localhost/database_name

# SQLite - runs *_sqlite.sql files  
DATABASE_URL=sqlite:data/app.db

Using SQLx CLI

# Install sqlx-cli
cargo install sqlx-cli

# PostgreSQL migrations
sqlx migrate run --database-url "postgresql://username:password@localhost/database_name"

# SQLite migrations
sqlx migrate run --database-url "sqlite:data/app.db"

Manual Migration

# PostgreSQL
psql -U username -d database_name -f 001_initial_setup_postgres.sql

# SQLite
sqlite3 data/app.db < 001_initial_setup_sqlite.sql

Environment Setup

Configure your database connection using the DATABASE_URL environment variable:

PostgreSQL

DATABASE_URL=postgresql://username:password@localhost:5432/database_name

SQLite

DATABASE_URL=sqlite:data/app.db

Additional Database Configuration

# Connection pool settings (optional)
DATABASE_MAX_CONNECTIONS=10
DATABASE_MIN_CONNECTIONS=1
DATABASE_CONNECT_TIMEOUT=30
DATABASE_IDLE_TIMEOUT=600
DATABASE_MAX_LIFETIME=3600

Security Considerations

1. Change Default Admin Password - The default admin password is admin123
2. Review Permissions - Customize role permissions based on your needs
3. Configure OAuth - Set up OAuth providers if using external authentication
4. Enable SSL - Use SSL connections in production (PostgreSQL)
5. Backup Strategy - Implement regular database backups
6. File Permissions - Secure SQLite database files (chmod 600)
7. Database Location - Store SQLite files outside web root

Schema Evolution

For future schema changes:

1. Create database-specific migration files:
   • 003_new_feature_postgres.sql
   • 003_new_feature_sqlite.sql
2. Always use CREATE TABLE IF NOT EXISTS for safety
3. Add proper indexes for performance
4. Handle database-specific differences (UUID vs TEXT, etc.)
5. Test migrations on both database types
6. Include rollback scripts when possible
7. Test migrations on a copy of production data

Database Differences to Consider

• UUIDs: PostgreSQL native vs SQLite TEXT
• Timestamps: PostgreSQL native vs SQLite TEXT (ISO 8601)
• JSON: PostgreSQL JSONB vs SQLite TEXT
• Arrays: PostgreSQL arrays vs SQLite JSON arrays
• Booleans: PostgreSQL BOOLEAN vs SQLite INTEGER

Performance Notes

PostgreSQL

• All tables have appropriate indexes for common queries
• Full-text search is enabled for content
• Partial indexes are used for filtered queries
• GIN indexes are used for JSONB and array columns
• Connection pooling for high concurrency

SQLite

• Optimized indexes for single-user scenarios
• WAL mode enabled for better concurrency
• Foreign key constraints enabled
• Query planner optimizations
• Smaller memory footprint

Troubleshooting

Common Issues

PostgreSQL Issues

1. Extension Error: If you get an error about uuid-ossp extension, ensure your PostgreSQL user has superuser privileges or the extension is already installed.
2. Permission Denied: Ensure your database user has CREATE privileges.
3. Connection Failed: Check PostgreSQL service is running and connection details are correct.

SQLite Issues

1. File Permission Error: Ensure the SQLite file and directory have proper write permissions.
2. Database Locked: Close other connections to the SQLite file.
3. Directory Not Found: Ensure the directory for the SQLite file exists.

General Issues

1. Constraint Violations: Check that your data meets the constraint requirements (email format, username format, etc.).
2. Migration Version Mismatch: Ensure all migration files are present and properly numbered.
3. Database Type Detection Failed: Verify your DATABASE_URL format is correct.

Verification

After running the migration, verify the setup:

PostgreSQL Verification

-- Check tables were created
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- Check default admin user
SELECT username, email, is_active FROM users WHERE username = 'admin';

-- Check permissions setup
SELECT COUNT(*) FROM permissions;
SELECT COUNT(*) FROM role_permissions;

-- Check sample content
SELECT slug, title, state FROM page_contents;

SQLite Verification

-- Check tables were created
SELECT name FROM sqlite_master WHERE type='table';

-- Check default admin user
SELECT username, email, is_active FROM users WHERE username = 'admin';

-- Check permissions setup
SELECT COUNT(*) FROM permissions;
SELECT COUNT(*) FROM role_permissions;

-- Check sample content
SELECT slug, title, state FROM page_contents;

Backup and Restore

PostgreSQL

# Create backup
pg_dump -U username -d database_name > backup.sql

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

# Compressed backup
pg_dump -U username -d database_name | gzip > backup.sql.gz

SQLite

# Create backup (simple copy)
cp data/app.db data/app_backup.db

# Create SQL dump
sqlite3 data/app.db .dump > backup.sql

# Restore from SQL dump
sqlite3 data/app_new.db < backup.sql

# Online backup (while app is running)
sqlite3 data/app.db ".backup data/app_backup.db"

Cross-Database Migration

# SQLite to PostgreSQL
sqlite3 data/app.db .dump | sed 's/INSERT INTO/INSERT INTO public./g' > backup.sql
psql -U username -d database_name < backup.sql

# Note: May require manual adjustments for data types

Support

For issues or questions:

1. Check the application logs
2. Verify database connectivity
3. Review the migration file for any custom modifications
4. Consult the PostgreSQL documentation for specific errors