PostgreSQL CRUD Backend¶

Overview¶

All portfolio content — projects, work experience, and skill categories — is currently stored as Python literals inside api/main.py. The API only exposes read-only GET endpoints. To support content management without code deployments, and to lay the groundwork for admin-facing features, this work replaces the static in-process data with an Azure Database for PostgreSQL Flexible Server and adds proper CRUD endpoints backed by SQLAlchemy + asyncpg.

Current State¶

The following data is hardcoded in api/main.py as module-level Python lists/dicts:

Variable	Type	Records	Model
`PROJECT_CARDS`	`list[ProjectCard]`	6 entries	Summary cards for the portfolio grid
`PROJECT_DETAILS`	`dict[str, ProjectDetail]`	6 entries	Full case study detail, keyed by slug
`WORK_HISTORY`	`list[WorkEntry]`	~10 entries	Employment/research timeline
`SKILL_CATEGORIES`	`list[SkillCategory]`	5 entries (each with nested `Skill` list)	Grouped skills with icon metadata

The API exposes only GET endpoints for all resources. There is no write path and no persistence layer. Adding, editing, or reordering content requires a code change and a full deployment.

Proposed Data Model¶

The schema maps directly from the existing Pydantic models. Arrays of strings (technologies, responsibilities, results) are stored as TEXT[] or normalized to a join table depending on whether cross-entity querying is needed (prefer TEXT[] for simplicity unless noted).

`projects`¶

Column	Type	Notes
`id`	`UUID` PK	Generated, used as stable row identity
`slug`	`TEXT` UNIQUE	URL-safe identifier (e.g. `genai-framework`)
`title`	`TEXT NOT NULL`
`description`	`TEXT NOT NULL`	Short card description
`long_description`	`TEXT NOT NULL`
`technologies`	`TEXT[]`
`category`	`TEXT NOT NULL`
`status`	`TEXT NOT NULL`
`impact`	`TEXT NOT NULL`
`demo`	`TEXT`	Internal route or external URL
`image`	`TEXT`	Image URL
`client`	`TEXT`
`duration`	`TEXT`	Detail only
`team`	`TEXT`	Detail only
`overview`	`TEXT`	Detail only
`challenge`	`TEXT`	Detail only
`solution`	`TEXT`	Detail only
`results`	`TEXT[]`	Detail only
`responsibilities`	`TEXT[]`	Detail only
`additional_images`	`TEXT[]`	Nullable
`embedded_app`	`TEXT`	Nullable
`publication_url`	`TEXT`	Nullable
`thesis_url`	`TEXT`	Nullable
`sort_order`	`INT`	Controls display order
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

Note: ProjectCard and ProjectDetail are merged into a single projects table. The API returns the appropriate subset of columns based on the endpoint.

`work_entries`¶

Column	Type	Notes
`id`	`UUID` PK
`company`	`TEXT NOT NULL`
`role`	`TEXT NOT NULL`
`period`	`TEXT NOT NULL`	Human-readable range string
`location`	`TEXT NOT NULL`
`type`	`TEXT NOT NULL`	e.g. `Current`, `Foundation`, `Research`
`description`	`TEXT NOT NULL`
`responsibilities`	`TEXT[]`
`technologies`	`TEXT[]`
`note`	`TEXT`	Nullable
`additional_projects`	`TEXT[]`	Nullable
`achievement`	`TEXT`	Nullable
`sort_order`	`INT`	Controls display order
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

`skill_categories`¶

Column	Type	Notes
`id`	`UUID` PK
`title`	`TEXT NOT NULL`	e.g. `Cloud Platforms`
`icon_name`	`TEXT NOT NULL`	Lucide icon name
`icon_color`	`TEXT NOT NULL`	Tailwind class string
`sort_order`	`INT`
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

`skills`¶

Column	Type	Notes
`id`	`UUID` PK
`category_id`	`UUID` FK → `skill_categories.id`
`name`	`TEXT NOT NULL`
`projects`	`TEXT[]`	Associated project names
`sort_order`	`INT`	Within-category ordering

skills is normalized to its own table because it has structured sub-fields (name, projects) and needs per-row editing.

Tech Stack Choices¶

Concern	Choice	Rationale
Database	Azure Database for PostgreSQL Flexible Server	Managed, scales to zero on Burstable B1ms tier for dev/low traffic; production-proven on Azure; supports `pg_vector` if needed later
ORM	SQLAlchemy 2.x (async mode)	Mature, aligns with FastAPI async model; type-safe with mapped classes
Driver	`asyncpg`	Native async PostgreSQL driver; required for SQLAlchemy async
Migrations	Alembic	Standard migration tool for SQLAlchemy; supports auto-generation and rollback
Seed data	Alembic data migration script	Moves the existing hardcoded content into the DB at first startup
Pydantic models	Extend existing Pydantic v2 models	Add `id`, `created_at`, `updated_at` fields for response models; separate `Create`/`Update` schemas for writes
`pydantic-settings`	Already in `requirements.txt` (unused)	Activated in the typing plan (Phase 1) — `Settings` class gains `database_url` field in this phase; no additional install needed

Integration with the Typing Plan¶

This work depends on and builds on the End-to-End Typing plan (docs/planning/typing-validation.md). Typing must ship first.

`Settings` class dependency¶

The typing plan (Phase 1a) introduces a pydantic-settings Settings class that replaces the scattered os.getenv() calls in main.py. The Postgres phase adds one field to that class:

python

class Settings(BaseSettings):
    # ... existing fields from typing Phase 1 ...
    database_url: str  # added in Postgres Phase 1

The typing plan should be complete before Postgres Phase 1 starts, so this field slot is already waiting.

TypeScript types — free via `openapi-typescript`¶

Once the typing plan's Phase 2 pipeline is in place (npm run types:generate → shared/api.types.ts), the Postgres migration requires no manual TypeScript work:

Postgres phase updates the Python Pydantic response models (adds id, created_at, updated_at; renames to snake_case consistently; introduces new ProjectCardResponse, WorkEntryResponse, etc.)
npm run types:generate is run once after the backend is deployed.
shared/api.types.ts is regenerated automatically with the new schema.
The CI type-drift check (from typing Phase 2) catches any frontend consumer that wasn't updated.

Without the typing pipeline, this phase would require parallel manual updates to TypeScript interfaces — a fragile step that the pipeline eliminates.

Model fate after this migration¶

The following Pydantic models change role. Ephemeral models (seeded from hardcoded literals) are replaced; permanent models are extended:

Model	Fate	Notes
`ProjectCard`	Replaced → `ProjectCardResponse`	DB-backed, gains `id`, `slug`, `sort_order`, `created_at`, `updated_at`
`ProjectDetail`	Replaced → merged into `ProjectCardResponse`	Single `projects` table serves both list and detail responses
`WorkEntry`	Replaced → `WorkEntryResponse`	DB-backed
`Skill`	Replaced → DB row in `skills` table
`SkillCategory`	Replaced → `SkillCategoryResponse`	DB-backed
`ContactRequest`	Kept — hardened in typing Phase 1	Unchanged by Postgres work
`ContactResponse`	Kept
`HealthResponse`	Kept
`Settings`	Extended — gains `database_url` field	Already created in typing Phase 1

`shared/schema.ts` (Drizzle/Zod)¶

shared/schema.ts currently holds Drizzle/Zod table definitions (users, contactSubmissions) that don't appear to be actively used by either the current frontend or backend. This is potentially leftover scaffold. Clarify before the Postgres migration lands whether it belongs or can be removed — it is unrelated to the shared/api.types.ts file that the typing plan generates, but may cause confusion if left in place.

API Design¶

All new write endpoints sit under the existing /api/ prefix and follow REST conventions. Rate limiting is already wired in via slowapi and will apply to new routes.

Projects¶

Method	Path	Description
`GET`	`/api/projects`	List all project cards (existing, now DB-backed)
`GET`	`/api/projects/{slug}`	Get project detail by slug (existing, now DB-backed)
`POST`	`/api/projects`	Create a project
`PUT`	`/api/projects/{slug}`	Replace a project in full
`PATCH`	`/api/projects/{slug}`	Partial update (e.g. update status, add technology)
`DELETE`	`/api/projects/{slug}`	Delete a project
`PATCH`	`/api/projects/reorder`	Update `sort_order` for multiple projects in one call

Work Experience¶

Method	Path	Description
`GET`	`/api/experience`	List all work entries (existing, now DB-backed)
`GET`	`/api/experience/{id}`	Get single entry by UUID (replaces index-based path)
`POST`	`/api/experience`	Create a work entry
`PUT`	`/api/experience/{id}`	Replace entry in full
`PATCH`	`/api/experience/{id}`	Partial update
`DELETE`	`/api/experience/{id}`	Delete a work entry
`PATCH`	`/api/experience/reorder`	Bulk sort-order update

Note: the existing GET /api/experience/{index} uses a 0-based integer index. This will be replaced with UUID-keyed paths. A compatibility shim or deprecation notice should be considered.

Skills¶

Method	Path	Description
`GET`	`/api/skills`	List all skill categories with nested skills (existing, now DB-backed)
`GET`	`/api/skills/{category_id}`	Get a single skill category
`POST`	`/api/skills`	Create a skill category
`PUT`	`/api/skills/{category_id}`	Replace a skill category
`DELETE`	`/api/skills/{category_id}`	Delete a skill category
`POST`	`/api/skills/{category_id}/skills`	Add a skill to a category
`PUT`	`/api/skills/{category_id}/skills/{skill_id}`	Replace a skill
`DELETE`	`/api/skills/{category_id}/skills/{skill_id}`	Delete a skill

Write Endpoint Authorization¶

Write endpoints (POST, PUT, PATCH, DELETE) must be protected. Options:

Simplest: Static bearer token stored in Key Vault, passed as Authorization: Bearer <token> header. FastAPI dependency validates the token.
Better: Entra ID client credentials, reusing the existing OIDC app registration. A dedicated scope on the API app registration; admin tooling acquires a token via client_credentials.

The authorization approach is an open question (see below). Phase 1 can gate writes behind a simple token. Phase 3 can upgrade to Entra ID.

Local Development¶

Add a postgres service to docker-compose.yml:

yaml

postgres:
  image: postgres:16-alpine
  container_name: portfolio-postgres
  ports:
    - "5432:5432"
  environment:
    POSTGRES_USER: portfolio
    POSTGRES_PASSWORD: localdev
    POSTGRES_DB: portfolio
  volumes:
    - postgres_data:/var/lib/postgresql/data
  networks:
    - portfolio-network
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U portfolio"]
    interval: 10s
    timeout: 3s
    retries: 5

volumes:
  postgres_data:

The backend service gains a DATABASE_URL environment variable:

yaml

backend:
  environment:
    - DATABASE_URL=postgresql+asyncpg://portfolio:localdev@postgres:5432/portfolio
    # ... existing vars
  depends_on:
    postgres:
      condition: service_healthy

Run migrations locally with alembic upgrade head inside the backend container or via docker-compose exec backend alembic upgrade head.

Infrastructure Changes¶

New Bicep Module: `infra/modules/postgres.bicep`¶

Following the existing module pattern (see infra/modules/key_vault.bicep and container_registry.bicep for structure):

Resources to create:

Microsoft.DBforPostgreSQL/flexibleServers — Flexible Server, Burstable B1ms for dev, General Purpose D2s_v3 for prod
Microsoft.DBforPostgreSQL/flexibleServers/databases — portfolio database
Microsoft.DBforPostgreSQL/flexibleServers/firewallRules — allow Azure services (0.0.0.0 to 0.0.0.0 rule) for Container Apps egress

Naming: ${org}-${env_short}-${project}-${location_short}-pg (e.g. dna-prd-portfolio-we-pg)

Key parameters:

Parameter	Dev	Prod
`sku_name`	`Standard_B1ms`	`Standard_D2s_v3`
`tier`	`Burstable`	`GeneralPurpose`
`storage_size_gb`	`32`	`64`
`backup_retention_days`	`7`	`14`
`high_availability`	Disabled	`ZoneRedundant`
`admin_user`	`portfolioadmin`	`portfolioadmin`

Admin password: Stored in Key Vault as postgres-admin-password. Generated once and referenced as a Key Vault secret param (same pattern as email-address).

Connection string: Assembled in container_apps.bicep and injected as a Container App secret from Key Vault (or as a direct secretRef if the value is constructed in Bicep). Pattern:

postgresql+asyncpg://{admin_user}:{password}@{server_fqdn}/portfolio?sslmode=require

Changes to Existing Modules¶

infra/modules/key_vault.bicep:

Add postgres_admin_password secret (same pattern as email-password)

infra/modules/container_apps.bicep:

Add postgres_fqdn and postgres_admin_password_kv_url params
Add database-url secret in the backend Container App secrets block (Key Vault ref)
Inject DATABASE_URL env var pointing at the secret

infra/main.bicep:

Add postgres module invocation (after key_vault, before container_apps)
Pass postgres.outputs.server_fqdn and postgres.outputs.admin_password_kv_url to container_apps
Add postgres_admin_password param (marked @secure())

infra/parameters.dev.bicepparam / parameters.prod.bicepparam:

Add postgres_admin_password binding (value injected from GitHub Secrets at deploy time)

CI/CD¶

Add POSTGRES_ADMIN_PASSWORD to GitHub Secrets
Pass it to the infra deploy step (same approach as EMAIL_ADDRESS)

Migration Strategy¶

Alembic manages all schema changes. Migrations live in api/alembic/versions/.

Initial migration (0001_initial_schema):

Creates all four tables (projects, work_entries, skill_categories, skills)
Seeds data from the current hardcoded Python literals (data migration in the same revision or a dedicated 0002_seed_initial_data)

Run strategy — startup migration:

The backend container runs alembic upgrade head as part of its startup command (before uvicorn). This is the simplest approach for a single-replica deployment and avoids the complexity of an init container at this scale.

dockerfile

CMD ["sh", "-c", "alembic upgrade head && uvicorn main:app --host 0.0.0.0 --port 8000"]

Startup migration trade-off

Running migrations at startup is acceptable for a low-traffic single-replica app. If the deployment ever moves to multiple simultaneous replicas (blue/green, rolling), a pre-deployment migration job using Azure Container Apps Jobs should replace this approach to avoid concurrent migration runs.

Rollback: alembic downgrade -1 can be run manually from a local environment or a Container Apps job.

Implementation Phases¶

Phase 1 — Database layer, read path (no breaking changes)¶

Goal: introduce SQLAlchemy + asyncpg, Alembic, and the Postgres module. All existing GET endpoints continue to work; data is now sourced from the DB.

[ ] Add asyncpg, sqlalchemy[asyncio], alembic to api/requirements.txt
[ ] Create api/db/ package: engine.py (async engine + session factory), models.py (SQLAlchemy mapped classes)
[ ] Write Alembic initial migration (0001_initial_schema.py)
[ ] Write seed data migration (0002_seed_initial_data.py) — port hardcoded Python literals to SQL INSERT statements
[ ] Update GET endpoints in main.py to query the DB via async session dependency
[ ] Update Dockerfile.backend entrypoint to run alembic upgrade head before uvicorn
[ ] Add postgres service to docker-compose.yml
[ ] Write infra/modules/postgres.bicep
[ ] Update key_vault.bicep, container_apps.bicep, main.bicep, and param files
[ ] Add POSTGRES_ADMIN_PASSWORD secret to GitHub and CI deploy step
[ ] Update API tests to use a test DB (pytest fixture with anyio + sqlalchemy test transactions)

Phase 2 — Write endpoints, admin token auth¶

Goal: add full CRUD for all resources, protected by a static bearer token.

[ ] Add api/schemas/ package: CreateProject, UpdateProject, CreateWorkEntry, etc. (Pydantic v2 request models separate from response models)
[ ] Implement POST, PUT, PATCH, DELETE for /api/projects, /api/experience, /api/skills
[ ] Implement /reorder endpoints for projects and experience
[ ] Add bearer token auth dependency (token stored in Key Vault as api-admin-token)
[ ] Add token to Key Vault module and inject into backend Container App
[ ] Write API tests for all new endpoints
[ ] Verify Swagger UI documents the new endpoints correctly (OpenAPI spec already auto-generated)

Phase 3 — Entra ID protection for write endpoints (optional upgrade)¶

Goal: replace static token with proper Entra ID-based authorization on write paths.

[ ] Create a new Entra ID API app registration (or add a scope to the existing one)
[ ] FastAPI dependency validates JWT issued by Entra, checks scope portfolio.write
[ ] Update dev Entra SSO config accordingly
[ ] Document token acquisition flow for any admin tooling

Phase 4 — Admin UI or CLI tooling (out of scope for this plan)¶

Goal: provide a usable interface for content management beyond raw API calls.

This phase is explicitly out of scope here. Options include a minimal React admin page (gated by EasyAuth), a CLI script using httpx, or managing content via SQL migrations. The decision should be made after Phase 2 ships and the content editing frequency is better understood.

Open Questions¶

Authorization in Phase 1: Should write endpoints ship in Phase 1 behind a static token, or should Phase 1 be purely read-only with writes deferred to Phase 2? (Recommendation: defer writes to Phase 2 to keep Phase 1 focused and low-risk.)
Experience endpoint path change: The existing GET /api/experience/{index} uses a 0-based integer index, which is unusual and fragile. Migrating to UUID-keyed paths is the right move but is a breaking change for any client depending on that path. Is a deprecation period needed, or can it be cut over cleanly?
Postgres sizing for prod: Standard_D2s_v3 General Purpose may be over-specified for a low-traffic portfolio site. Standard_B1ms Burstable could be used for prod as well, accepting the trade-off of no HA and lower consistent compute. Evaluate cost vs. requirement.
Network security: The current plan uses the allow Azure services firewall rule. A more secure approach is VNet integration (Container Apps Environment + Postgres in the same VNet with a private endpoint). This adds significant infra complexity. Acceptable to defer; track in roadmap.
CV download endpoint: GET /api/download-cv serves a static PDF file from the container image. This is unrelated to the DB work but should be reviewed — consider moving the file to Azure Blob Storage as part of this work or as a follow-on.
Seed data ownership: Once the DB is the source of truth, the hardcoded Python literals in main.py become dead code and should be removed. When is that safe to do? (After Phase 1 is deployed and verified in prod.)
shared/schema.ts cleanup: shared/schema.ts holds Drizzle/Zod table definitions (users, contactSubmissions) that do not appear to be used by the current frontend or backend. The typing plan flags this as potentially removable leftover scaffold (open question 4 in typing-validation.md). Resolve before this Postgres migration lands to avoid confusion with shared/api.types.ts (generated by openapi-typescript in typing Phase 2).
Typing plan prerequisite: This plan assumes the End-to-End Typing plan (docs/planning/typing-validation.md) is complete, specifically Phase 1 (pydantic-settings Settings class) and Phase 2 (openapi-typescript pipeline). Do not start Postgres Phase 1 until typing Phase 1 is merged; do not start Postgres Phase 2 until typing Phase 2 is merged (so TS types regenerate automatically after new Pydantic response models are added).

PostgreSQL CRUD Backend¶

Overview¶

Current State¶

Proposed Data Model¶

projects¶

work_entries¶

skill_categories¶

skills¶

Tech Stack Choices¶

Integration with the Typing Plan¶

Settings class dependency¶

TypeScript types — free via openapi-typescript¶

Model fate after this migration¶

shared/schema.ts (Drizzle/Zod)¶

API Design¶

Projects¶

Work Experience¶

Skills¶

Write Endpoint Authorization¶

Local Development¶

Infrastructure Changes¶

New Bicep Module: infra/modules/postgres.bicep¶

Changes to Existing Modules¶

CI/CD¶

Migration Strategy¶

Implementation Phases¶

Phase 1 — Database layer, read path (no breaking changes)¶

Phase 2 — Write endpoints, admin token auth¶

Phase 3 — Entra ID protection for write endpoints (optional upgrade)¶

Phase 4 — Admin UI or CLI tooling (out of scope for this plan)¶

Open Questions¶

`projects`¶

`work_entries`¶

`skill_categories`¶

`skills`¶

`Settings` class dependency¶

TypeScript types — free via `openapi-typescript`¶

`shared/schema.ts` (Drizzle/Zod)¶

New Bicep Module: `infra/modules/postgres.bicep`¶