Problem
src/climatevision/db.py (551 lines) contains all database access with raw SQL strings and standard sqlite3. This has several issues:
- No type safety in queries
- No reusable ORM models
- No schema migrations
- SQLite in production (no PostgreSQL support)
- Boilerplate CRUD code
- Hard to test with in-memory database
Proposed solution
Implement SQLAlchemy 2.0 async with Repository Pattern:
src/climatevision/
└── database/
├── __init__.py
├── models.py # SQLAlchemy ORM models (Run, Organization, Alert, etc.)
├── connection.py # Async engine + session factory
├── repositories/
│ ├── __init__.py
│ ├── base.py # Abstract BaseRepository[T]
│ ├── run_repository.py
│ ├── org_repository.py
│ └── alert_repository.py
└── migrations/ # Alembic migrations
Tasks
- [ ] Add SQLAlchemy and alembic to dependencies
- [ ] Create ORM models for all tables
- [ ] Implement generic BaseRepository
- [ ] Implement specific repositories
- [ ] Configure Alembic for migrations
- [ ] Replace db.py references in API layer
- [ ] Update tests with in-memory database fixtures
- [ ] Support PostgreSQL via
DATABASE_URL env var
Problem
src/climatevision/db.py(551 lines) contains all database access with raw SQL strings and standardsqlite3. This has several issues:Proposed solution
Implement SQLAlchemy 2.0 async with Repository Pattern:
Tasks
DATABASE_URLenv var