80 lines
3.9 KiB
Markdown
80 lines
3.9 KiB
Markdown
# 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.
|