backend/docs/SQLITE_FALLBACK.md

SQLite Fallback Database

This document describes the SQLite fallback database implementation for the AegisLM SaaS Backend.

Overview

The SQLite fallback database provides high availability by automatically switching to a local SQLite database when the primary PostgreSQL database becomes unavailable. This ensures the application remains functional even during PostgreSQL outages.

Architecture

Primary Database (PostgreSQL)

• Production Database: Neon PostgreSQL
• Connection: Async SQLAlchemy with connection pooling
• Features: Full feature support, high performance

Fallback Database (SQLite)

• Fallback Database: Local SQLite file
• Connection: Async SQLite with aiosqlite driver
• Features: Basic functionality, local storage, automatic failover

Configuration

Add these environment variables to your .env file:

# SQLite Fallback Database Configuration
ENABLE_SQLITE_FALLBACK=true
SQLITE_DATABASE_PATH="aegislm_fallback.db"
SQLITE_FALLBACK_TIMEOUT=5

Configuration Options

• ENABLE_SQLITE_FALLBACK: Enable/disable the fallback system
• SQLITE_DATABASE_PATH: Path to the SQLite database file
• SQLITE_FALLBACK_TIMEOUT: Timeout in seconds for fallback connections

How It Works

1. Automatic Failover

• Application tries to connect to PostgreSQL first
• If PostgreSQL fails, automatically switches to SQLite
• Seamless switching without application downtime

2. Data Synchronization

• Initial Setup: Migrates data from PostgreSQL to SQLite
• Ongoing Sync: Can sync SQLite data back to PostgreSQL
• Bidirectional: Supports both migration directions

3. Health Monitoring

• Continuous health checks for both databases
• Automatic recovery when PostgreSQL becomes available
• Detailed status reporting

API Endpoints

The system provides management endpoints for superusers:

Status Monitoring

• GET /api/v1/sqlite-fallback/status - Get fallback system status
• POST /api/v1/sqlite-fallback/test - Test fallback mechanism

Database Control

• POST /api/v1/sqlite-fallback/switch-to-primary - Force switch to PostgreSQL
• POST /api/v1/sqlite-fallback/switch-to-fallback - Force switch to SQLite

Data Management

• POST /api/v1/sqlite-fallback/migrate-from-primary - Migrate PostgreSQL → SQLite
• POST /api/v1/sqlite-fallback/sync-to-primary - Sync SQLite → PostgreSQL
• POST /api/v1/sqlite-fallback/backup - Backup SQLite database
• DELETE /api/v1/sqlite-fallback/cleanup - Remove SQLite database

Setup and Installation

1. Install Dependencies

pip install aiosqlite

2. Configure Environment

Add the SQLite fallback configuration to your .env file (see above).

3. Run Setup Script

cd backend
python setup_sqlite_fallback.py

4. Verify Installation

The setup script will:

• Initialize the SQLite fallback database
• Test the fallback mechanism
• Report system status

Usage Examples

Checking Fallback Status

from core.fallback_database import check_database_health, get_current_database_type

is_healthy, db_type = await check_database_health()
print(f"Database healthy: {is_healthy}, Active: {db_type}")

Manual Database Switching

from core.fallback_database import switch_to_fallback, switch_to_primary

# Switch to SQLite fallback
success = await switch_to_fallback()

# Switch back to PostgreSQL
success = await switch_to_primary()

Data Migration

from core.sqlite_fallback_manager import SQLiteFallbackManager

manager = SQLiteFallbackManager()

# Migrate from PostgreSQL to SQLite
success = await manager.migrate_from_postgresql()

# Sync from SQLite back to PostgreSQL
success = await manager.sync_to_postgresql()

Database Compatibility

SQLite Compatibility Features

• JSON Fields: Automatically converted to/from text
• DateTime Fields: Proper timezone handling
• Boolean Fields: Standard SQLite boolean support
• Foreign Keys: Enabled with proper constraints

Limitations

• Some PostgreSQL-specific features may not work in SQLite
• Performance is lower than PostgreSQL for large datasets
• Concurrent writes are limited by SQLite's file-based nature

Monitoring and Logging

Health Checks

The system provides comprehensive health monitoring:

• Primary database connectivity
• Fallback database connectivity
• Automatic failover status
• Data synchronization status

Logging

All fallback operations are logged with appropriate levels:

• INFO: Normal operations, status changes
• WARNING: Failover events, connection issues
• ERROR: Critical failures, setup problems

Best Practices

1. Regular Backups

• Create regular backups of the SQLite database
• Use the provided backup endpoint for automated backups

2. Monitor Performance

• Monitor fallback usage patterns
• Check database file size and performance
• Set up alerts for frequent fallback usage

3. Data Synchronization

• Regularly sync data back to PostgreSQL
• Verify data integrity after synchronization
• Test migration procedures periodically

4. Security Considerations

• Restrict fallback management endpoints to superusers
• Secure the SQLite database file permissions
• Monitor fallback system access logs

Troubleshooting

Common Issues

SQLite Database Not Found

• Check SQLITE_DATABASE_PATH configuration
• Verify directory permissions
• Run the setup script again

Migration Failures

• Ensure both databases are accessible
• Check for data type compatibility issues
• Review error logs for specific problems

Performance Issues

• Monitor SQLite database size
• Consider regular cleanup and optimization
• Check for long-running transactions

Debug Mode

Enable debug logging for detailed troubleshooting:

import logging
logging.getLogger('core.fallback_database').setLevel(logging.DEBUG)
logging.getLogger('core.sqlite_fallback_manager').setLevel(logging.DEBUG)

Performance Considerations

SQLite Optimizations

The system automatically applies SQLite optimizations:

• WAL journal mode for better concurrency
• Normal synchronous mode for balance
• Memory temp store for performance
• Optimized cache size

Connection Pooling

• Primary database uses connection pooling
• SQLite uses static pooling for consistency
• Automatic connection cleanup on errors

Security

Access Control

• Fallback management requires superuser privileges
• Database file permissions are properly set
• API endpoints are protected with authentication

Data Protection

• SQLite database file is stored securely
• Backup files include timestamps
• Sensitive data is not logged

Testing

Automated Tests

Run the comprehensive test suite:

cd backend
python -m core.sqlite_fallback_manager test

Manual Testing

Use the provided test endpoints to verify:

• Connection failover
• Data migration
• API functionality
• Performance characteristics

Future Enhancements

Planned Features

• Multi-database Support: Support for additional database types
• Advanced Sync: Real-time synchronization capabilities
• Performance Monitoring: Built-in performance metrics
• Automated Recovery: Enhanced automatic recovery procedures

Extensibility

The system is designed for easy extension:

• Plugin architecture for new database types
• Configurable failover strategies
• Custom synchronization rules
• Enhanced monitoring capabilities