[Future] Consider infrastructure change in DB from SQLite to PostreSQL for web hosted version of the application

The original design of the application was a locally-run or self-hosted version of CodeFRAME with a single user. SQLite was chosen because of the simple architecture and single user.

But, in a multi-user hosted environment, such as a multi-tenant SaaS environment, SQLite may not be a good choice. PostreSQL may be a better choice.

Migrating the DB connection is a significant refactor but could be the right choice as a SaaS application.

Plan

Implementation Plan: SQLite to PostgreSQL Migration for Multi-Tenant SaaS

Observations

The CodeFRAME application currently uses SQLite with a well-structured repository pattern architecture. The codebase already supports multi-user functionality with authentication (users, sessions, accounts tables) and authorization (project_users table). The repository pattern with 17 domain-specific repositories provides excellent isolation for database operations. The application uses both synchronous (sqlite3) and asynchronous (aiosqlite) database operations, with threading locks for thread-safe access. Standard SQL is used throughout with minimal SQLite-specific features, making migration feasible.

Approach

The migration will leverage the existing repository pattern to minimize code changes. We'll introduce a database abstraction layer that supports both SQLite and PostgreSQL, allowing gradual migration and testing. The strategy focuses on replacing connection management and adapting SQL syntax differences while maintaining the repository interfaces. PostgreSQL will provide better concurrency, connection pooling, and multi-tenant scalability required for SaaS deployment. The implementation will be phased to allow testing at each stage and maintain backward compatibility during transition.

Implementation Steps

Phase 1: Database Abstraction Layer

1.1 Create Database Adapter Interface

Create file:codeframe/persistence/adapters/base.py to define the database adapter interface:

Define abstract base class DatabaseAdapter with methods: connect(), execute(), fetchone(), fetchall(), commit(), close(), get_last_insert_id()
Define async variants: connect_async(), execute_async(), fetchone_async(), fetchall_async(), commit_async(), close_async()
Include connection health check and reconnection logic
Support both sync and async operations

1.2 Implement SQLite Adapter

Create file:codeframe/persistence/adapters/sqlite_adapter.py:

Implement SQLiteAdapter class inheriting from DatabaseAdapter
Wrap existing sqlite3 and aiosqlite connection logic
Maintain current threading lock behavior for thread-safety
Preserve check_same_thread=False and PRAGMA foreign_keys = ON
Keep row factory configuration (sqlite3.Row)

1.3 Implement PostgreSQL Adapter

Create file:codeframe/persistence/adapters/postgres_adapter.py:

Implement PostgresAdapter class inheriting from DatabaseAdapter
Use psycopg2 for synchronous operations (or psycopg v3)
Use asyncpg for asynchronous operations
Implement connection pooling (e.g., asyncpg.create_pool() with configurable min/max connections)
Handle PostgreSQL-specific connection parameters (host, port, database, user, password)
Implement row-to-dict conversion to match SQLite's Row behavior
Add connection health checks and automatic reconnection

Phase 2: Schema Translation

2.1 Create PostgreSQL Schema Manager

Create file:codeframe/persistence/schema_manager_postgres.py:

Extend or duplicate SchemaManager for PostgreSQL-specific schema
Translate SQLite schema to PostgreSQL equivalents:
- INTEGER PRIMARY KEY AUTOINCREMENT → SERIAL PRIMARY KEY or BIGSERIAL
- INTEGER (for booleans) → BOOLEAN
- TEXT → TEXT or VARCHAR (depending on use case)
- REAL → REAL or DOUBLE PRECISION
- JSON → JSONB (for better performance and indexing)
- TIMESTAMP DEFAULT CURRENT_TIMESTAMP → TIMESTAMP DEFAULT NOW()
- CHECK constraints remain similar but verify syntax
Update index creation syntax (PostgreSQL uses slightly different syntax)
Remove SQLite-specific PRAGMA statements
Add PostgreSQL-specific optimizations (e.g., CREATE INDEX CONCURRENTLY)

2.2 Handle SQL Dialect Differences

Update file:codeframe/persistence/adapters/base.py to include SQL dialect translation:

Create method translate_query(query: str, dialect: str) to handle differences:
- datetime() function → NOW() or CURRENT_TIMESTAMP
- CAST(... AS REAL) → CAST(... AS DOUBLE PRECISION)
- || string concatenation (same in both)
- LIMIT and OFFSET (same in both)
Handle parameter placeholders: ? (SQLite) → %s or $1, $2, ... (PostgreSQL)
Consider using a query builder library like SQLAlchemy Core for abstraction (optional)

Phase 3: Repository Layer Updates

3.1 Update BaseRepository

Modify file:codeframe/persistence/repositories/base.py:

Replace direct sqlite3.Connection references with DatabaseAdapter
Update _execute(), _fetchone(), _fetchall(), _commit() to use adapter methods
Update async methods to use adapter's async interface
Maintain backward compatibility by detecting adapter type
Update _get_last_insert_id() to use adapter method (handles lastrowid vs RETURNING id)

3.2 Update Database Facade

Modify file:codeframe/persistence/database.py:

Replace sqlite3.connect() with adapter factory pattern
Add adapter_type parameter to __init__() (default: "sqlite")
Create adapter instance based on configuration
Pass adapter to all repository constructors instead of raw connections
Update initialize() to use adapter's schema manager
Maintain backward compatibility for existing code

3.3 Handle PostgreSQL-Specific Queries

Review and update queries in all repository files that may need PostgreSQL-specific handling:

file:codeframe/persistence/repositories/project_repository.py
file:codeframe/persistence/repositories/issue_repository.py
file:codeframe/persistence/repositories/task_repository.py
All other repository files in file:codeframe/persistence/repositories/

Focus on:

INSERT ... RETURNING id instead of lastrowid
Boolean comparisons (= TRUE vs = 1)
JSON operations (-> and ->> operators in PostgreSQL)
Date/time functions

Phase 4: Configuration Management

4.1 Update Configuration Schema

Modify file:codeframe/core/config.py:

Add new configuration fields to GlobalConfig:
- database_type: str = Field("sqlite", alias="DATABASE_TYPE") (values: "sqlite", "postgresql")
- database_url: Optional[str] = Field(None, alias="DATABASE_URL") (PostgreSQL connection string)
- postgres_host: Optional[str] = Field(None, alias="POSTGRES_HOST")
- postgres_port: int = Field(5432, alias="POSTGRES_PORT")
- postgres_database: Optional[str] = Field(None, alias="POSTGRES_DB")
- postgres_user: Optional[str] = Field(None, alias="POSTGRES_USER")
- postgres_password: Optional[str] = Field(None, alias="POSTGRES_PASSWORD")
- postgres_pool_min: int = Field(10, alias="POSTGRES_POOL_MIN")
- postgres_pool_max: int = Field(20, alias="POSTGRES_POOL_MAX")
Add validation method to ensure required PostgreSQL config is present when database_type="postgresql"
Support both DATABASE_URL (single connection string) and individual parameters

4.2 Update Environment Files

Update file:.env.example and file:.env.staging.example:

Add PostgreSQL configuration examples:

# Database Configuration
DATABASE_TYPE=postgresql  # or sqlite
DATABASE_URL=postgresql://user:password@localhost:5432/codeframe
# Or use individual parameters:
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=codeframe
POSTGRES_USER=codeframe_user
POSTGRES_PASSWORD=secure_password
POSTGRES_POOL_MIN=10
POSTGRES_POOL_MAX=20

Phase 5: Migration Scripts

5.1 Create Data Migration Tool

Create file:codeframe/persistence/migrations/sqlite_to_postgres.py:

Implement migration script to transfer data from SQLite to PostgreSQL
Read from SQLite database using existing Database class
Write to PostgreSQL using new adapter
Handle table-by-table migration with progress reporting
Preserve foreign key relationships and constraints
Handle large datasets with batching (e.g., 1000 rows at a time)
Include rollback capability
Validate data integrity after migration (row counts, checksums)

5.2 Create Schema Migration Tool

Create file:codeframe/persistence/migrations/apply_postgres_schema.py:

Script to create PostgreSQL schema from scratch
Use SchemaManager (PostgreSQL variant)
Include idempotency checks (skip if tables exist)
Support both fresh installation and migration scenarios

Phase 6: Connection Pooling & Performance

6.1 Implement Connection Pool Manager

Create file:codeframe/persistence/pool_manager.py:

Implement connection pool for PostgreSQL using asyncpg.create_pool()
Configure pool size based on environment (min/max connections)
Implement connection health checks and automatic recovery
Add metrics: active connections, idle connections, wait time
Implement graceful shutdown (drain pool on application exit)

6.2 Update Server Initialization

Modify file:codeframe/ui/server.py:

Update lifespan() function to initialize database with correct adapter type
Read DATABASE_TYPE from environment
Initialize connection pool for PostgreSQL
Maintain SQLite support for backward compatibility and local development
Add health check endpoint to report database type and connection pool status

Phase 7: Testing Strategy

7.1 Create Adapter Tests

Create file:tests/persistence/test_adapters.py:

Test SQLite adapter maintains existing behavior
Test PostgreSQL adapter with test database
Test connection pooling and reconnection logic
Test query translation for both dialects
Use Docker container for PostgreSQL test database

7.2 Update Existing Tests

Modify existing test files to support both database types:

file:tests/persistence/test_database.py
file:tests/persistence/test_database_issues.py
file:tests/persistence/test_database_git_branches.py
All other persistence tests

Add parameterized tests to run against both SQLite and PostgreSQL:

@pytest.mark.parametrize("db_type", ["sqlite", "postgresql"])
def test_create_project(db_type):
    # Test implementation

7.3 Integration Testing

Update file:tests/integration/test_database_operations.py:

Test full workflow with PostgreSQL
Test concurrent operations (multiple users, multiple projects)
Test connection pool under load
Test failover and reconnection scenarios

Phase 8: Deployment & Documentation

8.1 Update Deployment Configuration

Update deployment files:

file:ecosystem.staging.config.js - Add PostgreSQL environment variables
file:.staging-server.conf.example - Add PostgreSQL configuration
Create Docker Compose file for PostgreSQL service (if not exists)

8.2 Create Migration Documentation

Create file:docs/migration/sqlite-to-postgresql.md:

Document migration process step-by-step
Include prerequisites (PostgreSQL installation, backup procedures)
Provide rollback instructions
Include troubleshooting guide
Document performance tuning recommendations
Provide example configurations for different deployment scenarios

8.3 Update Architecture Documentation

Update file:docs/architecture/database-repository-pattern.md:

Document adapter pattern implementation
Explain database type selection logic
Document connection pooling strategy
Update diagrams to show adapter layer

Phase 9: Gradual Rollout

9.1 Feature Flag Implementation

Add feature flag support in file:codeframe/core/config.py:

Allow runtime switching between SQLite and PostgreSQL
Support A/B testing in production
Enable gradual migration of users/projects

9.2 Monitoring & Observability

Add monitoring for database operations:

Log database type on startup
Track query performance metrics (execution time, connection pool stats)
Alert on connection pool exhaustion
Monitor migration progress and errors

Architecture Diagram

sequenceDiagram
    participant App as Application Layer
    participant DB as Database Facade
    participant Adapter as Database Adapter
    participant SQLite as SQLite DB
    participant Postgres as PostgreSQL DB
    
    App->>DB: Initialize(config)
    DB->>DB: Read DATABASE_TYPE from config
    
    alt SQLite Mode
        DB->>Adapter: Create SQLiteAdapter()
        Adapter->>SQLite: Connect
        SQLite-->>Adapter: Connection
    else PostgreSQL Mode
        DB->>Adapter: Create PostgresAdapter()
        Adapter->>Postgres: Create Connection Pool
        Postgres-->>Adapter: Pool
    end
    
    Adapter-->>DB: Adapter Instance
    DB->>DB: Initialize Repositories with Adapter
    DB-->>App: Database Ready
    
    App->>DB: create_project(...)
    DB->>Adapter: execute(INSERT query)
    
    alt SQLite
        Adapter->>SQLite: Execute with ?
        SQLite-->>Adapter: lastrowid
    else PostgreSQL
        Adapter->>Postgres: Execute with $1 RETURNING id
        Postgres-->>Adapter: id from RETURNING
    end
    
    Adapter-->>DB: Result
    DB-->>App: Project ID

Migration Checklist

Dependencies to Add

Update file:pyproject.toml dependencies:

dependencies = [
    # Existing dependencies...
    "psycopg2-binary>=2.9.9",  # or psycopg>=3.1.0 for v3
    "asyncpg>=0.29.0",
    # Optional: SQLAlchemy for query abstraction
    # "sqlalchemy>=2.0.0",
]

Rollback Strategy

If issues arise during migration:

Switch DATABASE_TYPE back to sqlite in environment configuration
Restore SQLite database from backup
Restart application - adapter pattern ensures immediate fallback
Investigate PostgreSQL issues offline
Re-attempt migration after fixes

Performance Considerations

Connection Pooling: PostgreSQL pool size should be tuned based on expected concurrent users (start with 10-20, monitor and adjust)
Indexing: Ensure all indexes from SQLite are recreated in PostgreSQL with appropriate types
Query Optimization: Use EXPLAIN ANALYZE to optimize slow queries
JSONB: Use JSONB instead of JSON for better performance and indexing capabilities
Prepared Statements: Consider using prepared statements for frequently executed queries
Monitoring: Track query execution times and connection pool metrics

Import In IDE

🤖 Prompt for AI Agents

# Implementation Plan: SQLite to PostgreSQL Migration for Multi-Tenant SaaS

## Observations

The CodeFRAME application currently uses SQLite with a well-structured repository pattern architecture. The codebase already supports multi-user functionality with authentication (users, sessions, accounts tables) and authorization (project_users table). The repository pattern with 17 domain-specific repositories provides excellent isolation for database operations. The application uses both synchronous (`sqlite3`) and asynchronous (`aiosqlite`) database operations, with threading locks for thread-safe access. Standard SQL is used throughout with minimal SQLite-specific features, making migration feasible.

## Approach

The migration will leverage the existing repository pattern to minimize code changes. We'll introduce a database abstraction layer that supports both SQLite and PostgreSQL, allowing gradual migration and testing. The strategy focuses on replacing connection management and adapting SQL syntax differences while maintaining the repository interfaces. PostgreSQL will provide better concurrency, connection pooling, and multi-tenant scalability required for SaaS deployment. The implementation will be phased to allow testing at each stage and maintain backward compatibility during transition.

## Implementation Steps

### Phase 1: Database Abstraction Layer

#### 1.1 Create Database Adapter Interface
Create `file:codeframe/persistence/adapters/base.py` to define the database adapter interface:
- Define abstract base class `DatabaseAdapter` with methods: `connect()`, `execute()`, `fetchone()`, `fetchall()`, `commit()`, `close()`, `get_last_insert_id()`
- Define async variants: `connect_async()`, `execute_async()`, `fetchone_async()`, `fetchall_async()`, `commit_async()`, `close_async()`
- Include connection health check and reconnection logic
- Support both sync and async operations

#### 1.2 Implement SQLite Adapter
Create `file:codeframe/persistence/adapters/sqlite_adapter.py`:
- Implement `SQLiteAdapter` class inheriting from `DatabaseAdapter`
- Wrap existing `sqlite3` and `aiosqlite` connection logic
- Maintain current threading lock behavior for thread-safety
- Preserve `check_same_thread=False` and `PRAGMA foreign_keys = ON`
- Keep row factory configuration (`sqlite3.Row`)

#### 1.3 Implement PostgreSQL Adapter
Create `file:codeframe/persistence/adapters/postgres_adapter.py`:
- Implement `PostgresAdapter` class inheriting from `DatabaseAdapter`
- Use `psycopg2` for synchronous operations (or `psycopg` v3)
- Use `asyncpg` for asynchronous operations
- Implement connection pooling (e.g., `asyncpg.create_pool()` with configurable min/max connections)
- Handle PostgreSQL-specific connection parameters (host, port, database, user, password)
- Implement row-to-dict conversion to match SQLite's `Row` behavior
- Add connection health checks and automatic reconnection

### Phase 2: Schema Translation

#### 2.1 Create PostgreSQL Schema Manager
Create `file:codeframe/persistence/schema_manager_postgres.py`:
- Extend or duplicate `SchemaManager` for PostgreSQL-specific schema
- Translate SQLite schema to PostgreSQL equivalents:
  - `INTEGER PRIMARY KEY AUTOINCREMENT` → `SERIAL PRIMARY KEY` or `BIGSERIAL`
  - `INTEGER` (for booleans) → `BOOLEAN`
  - `TEXT` → `TEXT` or `VARCHAR` (depending on use case)
  - `REAL` → `REAL` or `DOUBLE PRECISION`
  - `JSON` → `JSONB` (for better performance and indexing)
  - `TIMESTAMP DEFAULT CURRENT_TIMESTAMP` → `TIMESTAMP DEFAULT NOW()`
  - `CHECK` constraints remain similar but verify syntax
- Update index creation syntax (PostgreSQL uses slightly different syntax)
- Remove SQLite-specific `PRAGMA` statements
- Add PostgreSQL-specific optimizations (e.g., `CREATE INDEX CONCURRENTLY`)

#### 2.2 Handle SQL Dialect Differences
Update `file:codeframe/persistence/adapters/base.py` to include SQL dialect translation:
- Create method `translate_query(query: str, dialect: str)` to handle differences:
  - `datetime()` function → `NOW()` or `CURRENT_TIMESTAMP`
  - `CAST(... AS REAL)` → `CAST(... AS DOUBLE PRECISION)`
  - `||` string concatenation (same in both)
  - `LIMIT` and `OFFSET` (same in both)
- Handle parameter placeholders: `?` (SQLite) → `%s` or `$1, $2, ...` (PostgreSQL)
- Consider using a query builder library like SQLAlchemy Core for abstraction (optional)

### Phase 3: Repository Layer Updates

#### 3.1 Update BaseRepository
Modify `file:codeframe/persistence/repositories/base.py`:
- Replace direct `sqlite3.Connection` references with `DatabaseAdapter`
- Update `_execute()`, `_fetchone()`, `_fetchall()`, `_commit()` to use adapter methods
- Update async methods to use adapter's async interface
- Maintain backward compatibility by detecting adapter type
- Update `_get_last_insert_id()` to use adapter method (handles `lastrowid` vs `RETURNING id`)

#### 3.2 Update Database Facade
Modify `file:codeframe/persistence/database.py`:
- Replace `sqlite3.connect()` with adapter factory pattern
- Add `adapter_type` parameter to `__init__()` (default: "sqlite")
- Create adapter instance based on configuration
- Pass adapter to all repository constructors instead of raw connections
- Update `initialize()` to use adapter's schema manager
- Maintain backward compatibility for existing code

#### 3.3 Handle PostgreSQL-Specific Queries
Review and update queries in all repository files that may need PostgreSQL-specific handling:
- `file:codeframe/persistence/repositories/project_repository.py`
- `file:codeframe/persistence/repositories/issue_repository.py`
- `file:codeframe/persistence/repositories/task_repository.py`
- All other repository files in `file:codeframe/persistence/repositories/`

Focus on:
- `INSERT ... RETURNING id` instead of `lastrowid`
- Boolean comparisons (`= TRUE` vs `= 1`)
- JSON operations (`->` and `->>` operators in PostgreSQL)
- Date/time functions

### Phase 4: Configuration Management

#### 4.1 Update Configuration Schema
Modify `file:codeframe/core/config.py`:
- Add new configuration fields to `GlobalConfig`:
  - `database_type: str = Field("sqlite", alias="DATABASE_TYPE")` (values: "sqlite", "postgresql")
  - `database_url: Optional[str] = Field(None, alias="DATABASE_URL")` (PostgreSQL connection string)
  - `postgres_host: Optional[str] = Field(None, alias="POSTGRES_HOST")`
  - `postgres_port: int = Field(5432, alias="POSTGRES_PORT")`
  - `postgres_database: Optional[str] = Field(None, alias="POSTGRES_DB")`
  - `postgres_user: Optional[str] = Field(None, alias="POSTGRES_USER")`
  - `postgres_password: Optional[str] = Field(None, alias="POSTGRES_PASSWORD")`
  - `postgres_pool_min: int = Field(10, alias="POSTGRES_POOL_MIN")`
  - `postgres_pool_max: int = Field(20, alias="POSTGRES_POOL_MAX")`
- Add validation method to ensure required PostgreSQL config is present when `database_type="postgresql"`
- Support both `DATABASE_URL` (single connection string) and individual parameters

#### 4.2 Update Environment Files
Update `file:.env.example` and `file:.env.staging.example`:
- Add PostgreSQL configuration examples:
  ```
  # Database Configuration
  DATABASE_TYPE=postgresql  # or sqlite
  DATABASE_URL=postgresql://user:password@localhost:5432/codeframe
  # Or use individual parameters:
  POSTGRES_HOST=localhost
  POSTGRES_PORT=5432
  POSTGRES_DB=codeframe
  POSTGRES_USER=codeframe_user
  POSTGRES_PASSWORD=secure_password
  POSTGRES_POOL_MIN=10
  POSTGRES_POOL_MAX=20
  ```

### Phase 5: Migration Scripts

#### 5.1 Create Data Migration Tool
Create `file:codeframe/persistence/migrations/sqlite_to_postgres.py`:
- Implement migration script to transfer data from SQLite to PostgreSQL
- Read from SQLite database using existing `Database` class
- Write to PostgreSQL using new adapter
- Handle table-by-table migration with progress reporting
- Preserve foreign key relationships and constraints
- Handle large datasets with batching (e.g., 1000 rows at a time)
- Include rollback capability
- Validate data integrity after migration (row counts, checksums)

#### 5.2 Create Schema Migration Tool
Create `file:codeframe/persistence/migrations/apply_postgres_schema.py`:
- Script to create PostgreSQL schema from scratch
- Use `SchemaManager` (PostgreSQL variant)
- Include idempotency checks (skip if tables exist)
- Support both fresh installation and migration scenarios

### Phase 6: Connection Pooling & Performance

#### 6.1 Implement Connection Pool Manager
Create `file:codeframe/persistence/pool_manager.py`:
- Implement connection pool for PostgreSQL using `asyncpg.create_pool()`
- Configure pool size based on environment (min/max connections)
- Implement connection health checks and automatic recovery
- Add metrics: active connections, idle connections, wait time
- Implement graceful shutdown (drain pool on application exit)

#### 6.2 Update Server Initialization
Modify `file:codeframe/ui/server.py`:
- Update `lifespan()` function to initialize database with correct adapter type
- Read `DATABASE_TYPE` from environment
- Initialize connection pool for PostgreSQL
- Maintain SQLite support for backward compatibility and local development
- Add health check endpoint to report database type and connection pool status

### Phase 7: Testing Strategy

#### 7.1 Create Adapter Tests
Create `file:tests/persistence/test_adapters.py`:
- Test SQLite adapter maintains existing behavior
- Test PostgreSQL adapter with test database
- Test connection pooling and reconnection logic
- Test query translation for both dialects
- Use Docker container for PostgreSQL test database

#### 7.2 Update Existing Tests
Modify existing test files to support both database types:
- `file:tests/persistence/test_database.py`
- `file:tests/persistence/test_database_issues.py`
- `file:tests/persistence/test_database_git_branches.py`
- All other persistence tests

Add parameterized tests to run against both SQLite and PostgreSQL:
```python
@pytest.mark.parametrize("db_type", ["sqlite", "postgresql"])
def test_create_project(db_type):
    # Test implementation
```

#### 7.3 Integration Testing
Update `file:tests/integration/test_database_operations.py`:
- Test full workflow with PostgreSQL
- Test concurrent operations (multiple users, multiple projects)
- Test connection pool under load
- Test failover and reconnection scenarios

### Phase 8: Deployment & Documentation

#### 8.1 Update Deployment Configuration
Update deployment files:
- `file:ecosystem.staging.config.js` - Add PostgreSQL environment variables
- `file:.staging-server.conf.example` - Add PostgreSQL configuration
- Create Docker Compose file for PostgreSQL service (if not exists)

#### 8.2 Create Migration Documentation
Create `file:docs/migration/sqlite-to-postgresql.md`:
- Document migration process step-by-step
- Include prerequisites (PostgreSQL installation, backup procedures)
- Provide rollback instructions
- Include troubleshooting guide
- Document performance tuning recommendations
- Provide example configurations for different deployment scenarios

#### 8.3 Update Architecture Documentation
Update `file:docs/architecture/database-repository-pattern.md`:
- Document adapter pattern implementation
- Explain database type selection logic
- Document connection pooling strategy
- Update diagrams to show adapter layer

### Phase 9: Gradual Rollout

#### 9.1 Feature Flag Implementation
Add feature flag support in `file:codeframe/core/config.py`:
- Allow runtime switching between SQLite and PostgreSQL
- Support A/B testing in production
- Enable gradual migration of users/projects

#### 9.2 Monitoring & Observability
Add monitoring for database operations:
- Log database type on startup
- Track query performance metrics (execution time, connection pool stats)
- Alert on connection pool exhaustion
- Monitor migration progress and errors

## Architecture Diagram

```mermaid
sequenceDiagram
    participant App as Application Layer
    participant DB as Database Facade
    participant Adapter as Database Adapter
    participant SQLite as SQLite DB
    participant Postgres as PostgreSQL DB
    
    App->>DB: Initialize(config)
    DB->>DB: Read DATABASE_TYPE from config
    
    alt SQLite Mode
        DB->>Adapter: Create SQLiteAdapter()
        Adapter->>SQLite: Connect
        SQLite-->>Adapter: Connection
    else PostgreSQL Mode
        DB->>Adapter: Create PostgresAdapter()
        Adapter->>Postgres: Create Connection Pool
        Postgres-->>Adapter: Pool
    end
    
    Adapter-->>DB: Adapter Instance
    DB->>DB: Initialize Repositories with Adapter
    DB-->>App: Database Ready
    
    App->>DB: create_project(...)
    DB->>Adapter: execute(INSERT query)
    
    alt SQLite
        Adapter->>SQLite: Execute with ?
        SQLite-->>Adapter: lastrowid
    else PostgreSQL
        Adapter->>Postgres: Execute with $1 RETURNING id
        Postgres-->>Adapter: id from RETURNING
    end
    
    Adapter-->>DB: Result
    DB-->>App: Project ID
```

## Migration Checklist

- [ ] Phase 1: Database abstraction layer implemented
- [ ] Phase 2: PostgreSQL schema created and tested
- [ ] Phase 3: All repositories updated to use adapters
- [ ] Phase 4: Configuration management updated
- [ ] Phase 5: Migration scripts created and tested
- [ ] Phase 6: Connection pooling implemented
- [ ] Phase 7: All tests passing for both SQLite and PostgreSQL
- [ ] Phase 8: Documentation completed
- [ ] Phase 9: Monitoring and rollout strategy in place
- [ ] Backup and rollback procedures documented
- [ ] Performance benchmarks completed
- [ ] Security review completed (connection strings, credentials)

## Dependencies to Add

Update `file:pyproject.toml` dependencies:
```toml
dependencies = [
    # Existing dependencies...
    "psycopg2-binary>=2.9.9",  # or psycopg>=3.1.0 for v3
    "asyncpg>=0.29.0",
    # Optional: SQLAlchemy for query abstraction
    # "sqlalchemy>=2.0.0",
]
```

## Rollback Strategy

If issues arise during migration:
1. Switch `DATABASE_TYPE` back to `sqlite` in environment configuration
2. Restore SQLite database from backup
3. Restart application - adapter pattern ensures immediate fallback
4. Investigate PostgreSQL issues offline
5. Re-attempt migration after fixes

## Performance Considerations

- **Connection Pooling**: PostgreSQL pool size should be tuned based on expected concurrent users (start with 10-20, monitor and adjust)
- **Indexing**: Ensure all indexes from SQLite are recreated in PostgreSQL with appropriate types
- **Query Optimization**: Use `EXPLAIN ANALYZE` to optimize slow queries
- **JSONB**: Use JSONB instead of JSON for better performance and indexing capabilities
- **Prepared Statements**: Consider using prepared statements for frequently executed queries
- **Monitoring**: Track query execution times and connection pool metrics

Execution Information

Branch: main
Commit: e1539b8

💡 Tips

Supported Commands (Inside Comments)

Use @traycerai generate to iterate on the previous version of the implementation plan.

Supported Commands (Inside Description)

Add @traycerai ignore anywhere in the ticket description to prevent this ticket from being processed.
Add @traycerai branch:<branch-name> anywhere in the ticket description to specify the target branch for the implementation plan.

Community

Join our Discord Community to get help, request features, and share feedback.
Follow us on X/Twitter for updates and announcements.

Re-labeled from P3 to Future. SQLite is sufficient for v1/v2. PostgreSQL migration is only needed for multi-tenant hosted version which is beyond current scope.

[Future] Consider infrastructure change in DB from SQLite to PostreSQL for web hosted version of the application #232

Description

Activity

traycerai commented on Jan 9, 2026