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:

```env
# 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
```bash
pip install aiosqlite
```

### 2. Configure Environment
Add the SQLite fallback configuration to your `.env` file (see above).

### 3. Run Setup Script
```bash
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
```python
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
```python
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
```python
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:
```python
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:
```bash
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