TASK: Add SQLAlchemy async engine and session dependency wiring #11

New Issue

darkhelm · 2026-05-15T17:32:22-04:00

darkhelm commented

2026-05-15 17:32:22 -04:00

Parent

#2

What to build

Introduce centralized async engine/session factory and inject DB sessions through FastAPI dependencies.

Acceptance criteria

Async engine/session factory is implemented
FastAPI dependency provides scoped async sessions
Health endpoint validates DB connectivity

Blocked by

TASK: Upgrade backend runtime and dependency constraints (#10)

Notes

Python backend code must be fully typed and async-safe.
Any blocking work must be wrapped through thread/process execution paths as appropriate.
Must pass lint, format, typing, docstring, and test quality gates.

## Parent #2 ## What to build Introduce centralized async engine/session factory and inject DB sessions through FastAPI dependencies. ## Acceptance criteria - [ ] Async engine/session factory is implemented - [ ] FastAPI dependency provides scoped async sessions - [ ] Health endpoint validates DB connectivity ## Blocked by - #10 ## Notes - Python backend code must be fully typed and async-safe. - Any blocking work must be wrapped through thread/process execution paths as appropriate. - Must pass lint, format, typing, docstring, and test quality gates.

darkhelm added this to the v1 Dynamic Realtime Playlist milestone 2026-05-15 17:32:22 -04:00

darkhelm added the database task afk backend labels 2026-05-15 17:32:22 -04:00

darkhelm referenced this issue

2026-05-15 17:32:30 -04:00

TASK: Add migration framework and baseline migration #12

darkhelm added this to the Main Project Board project 2026-05-15 17:51:06 -04:00

darkhelm removed this from the Main Project Board project 2026-05-15 17:53:47 -04:00

darkhelm modified the milestone from v1 Dynamic Realtime Playlist to E1 - Platform Baseline and Standards Upgrade

2026-05-15 17:57:58 -04:00

darkhelm added this to the Main Project Board project 2026-06-18 11:25:30 -04:00

copilotcoder was assigned by darkhelm

2026-06-18 11:28:14 -04:00

darkhelm moved this to To Do in Main Project Board on 2026-06-18 11:33:53 -04:00

darkhelm moved this to In Progress in Main Project Board on 2026-06-18 11:38:02 -04:00

darkhelm commented

2026-06-18 12:13:27 -04:00

Plan: Async DB Session Wiring for PP-11

Implement a centralized SQLAlchemy async engine/session layer for the backend, inject sessions through FastAPI dependency wiring, and change /health to perform a real DB connectivity check with the new response contract. This keeps ADR001 runtime-policy behavior intact, minimizes surface-area changes, and uses mocked integration coverage for this ticket.

Steps

Phase 1: Dependency and runtime baseline
1.1 Add exact backend runtime pins for SQLAlchemy async support and PostgreSQL async driver.
1.2 Regenerate lockfile under Python 3.14 and verify deterministic resolution.
Phase 2: Database layer primitives
2.1 Add a new backend database module containing:

async engine factory sourced from DATABASE_URL
async sessionmaker factory
FastAPI dependency yielding one scoped AsyncSession per request with guaranteed cleanup
small helper for health probe query execution (SELECT 1)
2.2 Ensure full typing and async safety for all new functions.

Phase 3: App wiring and endpoint behavior
3.1 Update FastAPI lifespan to initialize DB resources at startup and dispose engine at shutdown while preserving runtime policy validation ordering.
3.2 Replace /health behavior to check DB connectivity through injected async session dependency and return the new DB-centric schema.
3.3 Keep root and compatibility endpoint behavior unchanged aside from required imports/refactors.
Phase 4: Tests aligned with selected scope
4.1 Update integration tests to validate:

healthy DB path returns success payload
DB failure path returns unhealthy/error payload with correct status code
4.2 Implement mocked dependency override for DB session in tests (no real Postgres integration for PP-11).
4.3 Extend shared test fixtures only if needed, keeping scope minimal to avoid cross-test state.

Phase 5: Quality gate and regression checks
5.1 Run backend format, lint, type, integration test, and docstring checks.
5.2 Run full project quality gate if feasible to catch cross-stack regressions.
5.3 Confirm coverage remains at configured threshold and runtime startup policy tests still pass.

Relevant files

backend dependency manifest and lockfile
backend main app module for lifespan and endpoint wiring
new backend database module for engine/session dependency
backend integration API tests
backend pytest config/fixtures if shared DB mocks are needed

Verification

Confirm SQLAlchemy and async driver exact pins are present in dependency manifest and lockfile.
Confirm /health returns expected DB-centric success schema when probe succeeds.
Confirm /health returns expected unhealthy/error contract when DB probe/session acquisition fails.
Confirm integration suite passes with dependency overrides.
Confirm existing runtime policy and compatibility checks still pass.
Confirm formatting, linting, typing, docstring checks, and coverage target pass.

Decisions

/health response will use a new DB-focused schema.
Integration testing for PP-11 will mock DB session dependency (no real Postgres integration).
Included scope: async engine/session wiring, DI integration, health DB check, tests, dependency/lock update.
Excluded scope: ORM models, migrations/Alembic, repository layer, transactional business logic, and advanced DB observability beyond health.

Further considerations

Finalize failure status code for DB-unavailable health checks. Recommendation: 503.
Confirm whether DB connectivity should also be checked during startup. Recommendation: defer connectivity checks to /health for PP-11 unless fail-fast startup is required.
If real DB integration tests are needed later, track as a follow-up ticket to avoid over-scoping PP-11.

## Plan: Async DB Session Wiring for PP-11 Implement a centralized SQLAlchemy async engine/session layer for the backend, inject sessions through FastAPI dependency wiring, and change /health to perform a real DB connectivity check with the new response contract. This keeps ADR001 runtime-policy behavior intact, minimizes surface-area changes, and uses mocked integration coverage for this ticket. **Steps** 1. Phase 1: Dependency and runtime baseline 1.1 Add exact backend runtime pins for SQLAlchemy async support and PostgreSQL async driver. 1.2 Regenerate lockfile under Python 3.14 and verify deterministic resolution. 2. Phase 2: Database layer primitives 2.1 Add a new backend database module containing: - async engine factory sourced from DATABASE_URL - async sessionmaker factory - FastAPI dependency yielding one scoped AsyncSession per request with guaranteed cleanup - small helper for health probe query execution (SELECT 1) 2.2 Ensure full typing and async safety for all new functions. 3. Phase 3: App wiring and endpoint behavior 3.1 Update FastAPI lifespan to initialize DB resources at startup and dispose engine at shutdown while preserving runtime policy validation ordering. 3.2 Replace /health behavior to check DB connectivity through injected async session dependency and return the new DB-centric schema. 3.3 Keep root and compatibility endpoint behavior unchanged aside from required imports/refactors. 4. Phase 4: Tests aligned with selected scope 4.1 Update integration tests to validate: - healthy DB path returns success payload - DB failure path returns unhealthy/error payload with correct status code 4.2 Implement mocked dependency override for DB session in tests (no real Postgres integration for PP-11). 4.3 Extend shared test fixtures only if needed, keeping scope minimal to avoid cross-test state. 5. Phase 5: Quality gate and regression checks 5.1 Run backend format, lint, type, integration test, and docstring checks. 5.2 Run full project quality gate if feasible to catch cross-stack regressions. 5.3 Confirm coverage remains at configured threshold and runtime startup policy tests still pass. **Relevant files** - backend dependency manifest and lockfile - backend main app module for lifespan and endpoint wiring - new backend database module for engine/session dependency - backend integration API tests - backend pytest config/fixtures if shared DB mocks are needed **Verification** 1. Confirm SQLAlchemy and async driver exact pins are present in dependency manifest and lockfile. 2. Confirm /health returns expected DB-centric success schema when probe succeeds. 3. Confirm /health returns expected unhealthy/error contract when DB probe/session acquisition fails. 4. Confirm integration suite passes with dependency overrides. 5. Confirm existing runtime policy and compatibility checks still pass. 6. Confirm formatting, linting, typing, docstring checks, and coverage target pass. **Decisions** - /health response will use a new DB-focused schema. - Integration testing for PP-11 will mock DB session dependency (no real Postgres integration). - Included scope: async engine/session wiring, DI integration, health DB check, tests, dependency/lock update. - Excluded scope: ORM models, migrations/Alembic, repository layer, transactional business logic, and advanced DB observability beyond health. **Further considerations** 1. Finalize failure status code for DB-unavailable health checks. Recommendation: 503. 2. Confirm whether DB connectivity should also be checked during startup. Recommendation: defer connectivity checks to /health for PP-11 unless fail-fast startup is required. 3. If real DB integration tests are needed later, track as a follow-up ticket to avoid over-scoping PP-11.

darkhelm moved this to Done in Main Project Board on 2026-06-19 07:42:21 -04:00

darkhelm closed this issue

2026-06-19 07:43:01 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: DarkHelm.org/plex-playlist#11