Add SQLAlchemy models, Alembic migrations, and DB-backed health check #2

New issue

Closed

opened 2026-03-16 00:58:36 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-03-16 00:58:36 +00:00

Contributor

Lineage

plan-mcd-tracker → Phase 3: Data Model + Postgres

Repo

forgejo_admin/mcd-tracker-api

User Story

As a developer
I want the domain model and database layer in place
So that API endpoints (Phase 5) can persist and query coupon usage data.

Architecture

arch-domain-mcd-tracker — this issue implements the entity model exactly as designed. Two entities: Location and CouponUsage. No separate User table — identity from Keycloak JWT sub claim stored as keycloak_sub string.
Rolling window query: WHERE expires_at > NOW() counts active codes. expires_at = used_at + 30 days.

Context

Phase 2 completed: FastAPI scaffold is live at https://mcd-tracker.tail5b443a.ts.net/healthz. Postgres 16-alpine pod is already running in the mcd-tracker namespace (deployed in Phase 1). The database password is in k8s secret mcd-tracker-secrets key postgres-password. The env var MCD_TRACKER_DATABASE_URL is already wired in the kustomize deployment patch.

Connection string pattern: postgresql://mcd_tracker:$(POSTGRES_PASSWORD)@postgres:5432/mcd_tracker

Follow the basketball-api pattern at ~/basketball-api/src/basketball_api/ for database.py, models.py, and alembic setup.

File Targets

Files to create:

src/mcd_tracker_api/database.py — SQLAlchemy engine from settings.database_url, SessionLocal factory, Base declarative base, get_db() FastAPI dependency
src/mcd_tracker_api/models.py — two models:
- Location: id (PK), keycloak_sub (String, indexed), name (String), address (String), city (String), state (String, 2-char), latitude (Float, nullable), longitude (Float, nullable), created_at (DateTime, server_default=now)
- CouponUsage: id (PK), location_id (FK → location.id), keycloak_sub (String, indexed), code (String), used_at (DateTime, server_default=now), redeemed (Boolean, default=False), redeemed_at (DateTime, nullable), expires_at (DateTime — computed as used_at + 30 days, set in application logic or column default)
alembic/ — standard alembic init with env.py configured to use settings.database_url and import models for autogenerate
alembic.ini — standard config pointing to alembic/
alembic/versions/001_initial_schema.py — autogenerated migration creating both tables
tests/test_models.py — test that models create tables in a test DB

Files to modify:

src/mcd_tracker_api/main.py — add alembic.command.upgrade(config, "head") in lifespan (run migrations on startup)
src/mcd_tracker_api/routes/health.py — add DB connectivity check (SELECT 1 via get_db)
pyproject.toml — add dependencies: sqlalchemy, psycopg2-binary, alembic
src/mcd_tracker_api/config.py — ensure database_url field exists (it should from Phase 2)

Files NOT to touch:

.woodpecker.yaml — CI pipeline unchanged
Dockerfile — no changes needed
Any k8s/deployment files — postgres is already deployed

Acceptance Criteria

Location and CouponUsage SQLAlchemy models match arch-domain-mcd-tracker entity diagram
CouponUsage.expires_at computed as used_at + timedelta(days=30)
keycloak_sub indexed on both tables (query performance for per-user lookups)
location_id FK on CouponUsage with cascade delete
Alembic migration creates both tables idempotently
Migration runs on app startup (lifespan)
/healthz returns DB connection status (e.g., {"status": "ok", "db": "connected"})
/healthz returns degraded status if DB is unreachable
Tests pass with real Postgres (not mocks)

Test Expectations

test_models.py — create Location, create CouponUsage linked to it, query back, verify fields
test_health.py — health endpoint includes DB status
test_models.py — verify expires_at is 30 days after used_at
Run command: pytest tests/ -v

Constraints

Use postgresql.ENUM if needed (not sa.Enum) — avoids non-idempotent CREATE TYPE issues (lesson from basketball-api migration 008)
expires_at should be set in application logic (default in model or pre-insert hook), NOT a Postgres generated column — keeps the logic portable and testable
Do NOT add API routes for these models yet — that's Phase 5
Follow basketball-api database.py pattern exactly for engine/session setup

Checklist

PR opened with Closes #2
Tests pass
Ruff clean
No unrelated changes
Migration file checked in (not auto-generated at runtime)

project-mcd-tracker — project page
phase-mcd-tracker-3-data-model — phase note
arch-domain-mcd-tracker — entity model being implemented

### Lineage `plan-mcd-tracker` → Phase 3: Data Model + Postgres ### Repo `forgejo_admin/mcd-tracker-api` ### User Story As a developer I want the domain model and database layer in place So that API endpoints (Phase 5) can persist and query coupon usage data. ### Architecture - `arch-domain-mcd-tracker` — this issue implements the entity model exactly as designed. Two entities: Location and CouponUsage. No separate User table — identity from Keycloak JWT `sub` claim stored as `keycloak_sub` string. - Rolling window query: `WHERE expires_at > NOW()` counts active codes. `expires_at = used_at + 30 days`. ### Context Phase 2 completed: FastAPI scaffold is live at `https://mcd-tracker.tail5b443a.ts.net/healthz`. Postgres 16-alpine pod is already running in the `mcd-tracker` namespace (deployed in Phase 1). The database password is in k8s secret `mcd-tracker-secrets` key `postgres-password`. The env var `MCD_TRACKER_DATABASE_URL` is already wired in the kustomize deployment patch. Connection string pattern: `postgresql://mcd_tracker:$(POSTGRES_PASSWORD)@postgres:5432/mcd_tracker` Follow the basketball-api pattern at `~/basketball-api/src/basketball_api/` for database.py, models.py, and alembic setup. ### File Targets Files to create: - `src/mcd_tracker_api/database.py` — SQLAlchemy engine from `settings.database_url`, SessionLocal factory, Base declarative base, `get_db()` FastAPI dependency - `src/mcd_tracker_api/models.py` — two models: - `Location`: id (PK), keycloak_sub (String, indexed), name (String), address (String), city (String), state (String, 2-char), latitude (Float, nullable), longitude (Float, nullable), created_at (DateTime, server_default=now) - `CouponUsage`: id (PK), location_id (FK → location.id), keycloak_sub (String, indexed), code (String), used_at (DateTime, server_default=now), redeemed (Boolean, default=False), redeemed_at (DateTime, nullable), expires_at (DateTime — computed as used_at + 30 days, set in application logic or column default) - `alembic/` — standard alembic init with env.py configured to use `settings.database_url` and import models for autogenerate - `alembic.ini` — standard config pointing to `alembic/` - `alembic/versions/001_initial_schema.py` — autogenerated migration creating both tables - `tests/test_models.py` — test that models create tables in a test DB Files to modify: - `src/mcd_tracker_api/main.py` — add `alembic.command.upgrade(config, "head")` in lifespan (run migrations on startup) - `src/mcd_tracker_api/routes/health.py` — add DB connectivity check (`SELECT 1` via get_db) - `pyproject.toml` — add dependencies: sqlalchemy, psycopg2-binary, alembic - `src/mcd_tracker_api/config.py` — ensure `database_url` field exists (it should from Phase 2) Files NOT to touch: - `.woodpecker.yaml` — CI pipeline unchanged - `Dockerfile` — no changes needed - Any k8s/deployment files — postgres is already deployed ### Acceptance Criteria - [ ] `Location` and `CouponUsage` SQLAlchemy models match `arch-domain-mcd-tracker` entity diagram - [ ] `CouponUsage.expires_at` computed as `used_at + timedelta(days=30)` - [ ] `keycloak_sub` indexed on both tables (query performance for per-user lookups) - [ ] `location_id` FK on CouponUsage with cascade delete - [ ] Alembic migration creates both tables idempotently - [ ] Migration runs on app startup (lifespan) - [ ] `/healthz` returns DB connection status (e.g., `{"status": "ok", "db": "connected"}`) - [ ] `/healthz` returns degraded status if DB is unreachable - [ ] Tests pass with real Postgres (not mocks) ### Test Expectations - [ ] `test_models.py` — create Location, create CouponUsage linked to it, query back, verify fields - [ ] `test_health.py` — health endpoint includes DB status - [ ] `test_models.py` — verify expires_at is 30 days after used_at - Run command: `pytest tests/ -v` ### Constraints - Use `postgresql.ENUM` if needed (not `sa.Enum`) — avoids non-idempotent `CREATE TYPE` issues (lesson from basketball-api migration 008) - `expires_at` should be set in application logic (default in model or pre-insert hook), NOT a Postgres generated column — keeps the logic portable and testable - Do NOT add API routes for these models yet — that's Phase 5 - Follow basketball-api `database.py` pattern exactly for engine/session setup ### Checklist - [ ] PR opened with `Closes #2` - [ ] Tests pass - [ ] Ruff clean - [ ] No unrelated changes - [ ] Migration file checked in (not auto-generated at runtime) ### Related - `project-mcd-tracker` — project page - `phase-mcd-tracker-3-data-model` — phase note - `arch-domain-mcd-tracker` — entity model being implemented