headquarter/docs/superpowers/plans/2026-05-17-database-models.md

Database Models Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Build the backend database foundation for Headquarter with SQLAlchemy 2.0 async models, Alembic migrations, and development seed data.

Architecture: Add a minimal FastAPI backend package under apps/api/src with one shared declarative base, one async database/session module, and focused model modules for each core entity. Drive the work from tests that assert schema metadata and relationship wiring first, then add Alembic and seeding on top.

Tech Stack: Python 3.11, SQLAlchemy 2.x, asyncpg, Alembic, pytest, pytest-asyncio, Pydantic Settings, PostgreSQL JSONB/UUID types

---

Task 1: Backend package skeleton and settings

Files:

• Create: apps/api/src/__init__.py

• Create: apps/api/src/config.py

• Create: apps/api/src/database.py

• Test: apps/api/tests/test_config.py

• Step 1: Write a failing test for configuration defaults and async engine URL expectations.

• Step 2: Run the focused config test and confirm it fails because the module does not exist.

• Step 3: Add minimal settings and async session factory implementation.

• Step 4: Run the focused config test and confirm it passes.

Task 2: Declarative base and shared timestamp/UUID columns

Files:

• Create: apps/api/src/models/__init__.py

• Create: apps/api/src/models/base.py

• Test: apps/api/tests/test_models.py

• Step 1: Write a failing metadata test that imports the base and asserts mapped tables can inherit UUID/timestamp columns.

• Step 2: Run the focused model test and confirm it fails.

• Step 3: Implement the declarative base plus reusable UUID/timestamp mixins.

• Step 4: Re-run the focused model test and confirm it passes.

Task 3: Core entity models and relationships

Files:

• Create: apps/api/src/models/user.py

• Create: apps/api/src/models/project.py

• Create: apps/api/src/models/git_repository.py

• Create: apps/api/src/models/ssh_key.py

• Create: apps/api/src/models/user_config.py

• Modify: apps/api/src/models/__init__.py

• Test: apps/api/tests/test_models.py

• Step 1: Write failing tests that assert the five tables exist, required columns are present, and the expected relationships are wired.

• Step 2: Run the focused model tests and confirm they fail because the models are missing.

• Step 3: Implement the minimal models to satisfy the spec, including PostgreSQL UUID/JSONB fields and foreign keys.

• Step 4: Re-run the focused model tests and confirm they pass.

Task 4: Alembic integration and initial migration

Files:

• Create: apps/api/alembic.ini

• Create: apps/api/alembic/env.py

• Create: apps/api/alembic/script.py.mako

• Create: apps/api/alembic/versions/0001_initial_schema.py

• Test: apps/api/tests/test_migration_metadata.py

• Step 1: Write a failing test that imports model metadata and asserts the initial migration covers all expected tables.

• Step 2: Run the focused migration test and confirm it fails.

• Step 3: Add minimal Alembic configuration plus an initial migration that creates all core tables.

• Step 4: Re-run the focused migration test and confirm it passes.

Task 5: Seed data and verification

Files:

• Create: apps/api/scripts/seed.py

• Modify: openspec/changes/database-models/tasks.md

• Test: apps/api/tests/test_seed.py

• Step 1: Write a failing test that verifies the seed module builds a deterministic development user payload.

• Step 2: Run the focused seed test and confirm it fails.

• Step 3: Implement the minimal seed helpers and script entrypoint.

• Step 4: Re-run the focused seed test and confirm it passes.

• Step 5: Mark completed OpenSpec checklist items and run the targeted verification commands.