59 lines
2.5 KiB
Markdown
59 lines
2.5 KiB
Markdown
# SPEC — F-002 Database foundation with module-owned schemas
|
|
|
|
## Problem
|
|
Modules need PostgreSQL with clear ownership and safe migrations. No schema tooling exists yet.
|
|
|
|
## Goal
|
|
Migration tooling, table naming convention per module, and a local dev database (PostgreSQL + Redis) that starts with one command.
|
|
|
|
## Non-goals
|
|
- No business tables yet (they arrive with their modules).
|
|
- No Redis usage beyond making the service available for future tickets.
|
|
- No production deployment concerns.
|
|
|
|
## User story
|
|
As a developer, I can run one command to get PostgreSQL + Redis locally, apply migrations forward and backward deterministically, and every future module knows exactly how to name and own its tables.
|
|
|
|
## Functional requirements
|
|
1. SQL migrations run through a deterministic tool, tracked in the database, ordered, idempotent per version.
|
|
2. Migrations support up and down.
|
|
3. Naming convention `<module>_<table>` is documented and visible in the baseline migration.
|
|
4. `docker-compose.yml` provides PostgreSQL 16 and Redis 7 with one command.
|
|
5. A typed DB access point lives in `src/infrastructure/db/` (pool creation from env, fail fast on missing config).
|
|
6. Baseline migration: enable extensions + `app_meta` key/value table (foundation-only, not business).
|
|
|
|
## Business rules
|
|
- No schema change without migration.
|
|
- Modules own tables by prefix; cross-module table access is forbidden (enforced later at module API level, documented now).
|
|
|
|
## Inputs
|
|
- `DATABASE_URL` (runtime), `TEST_DATABASE_URL` (integration tests).
|
|
|
|
## Outputs
|
|
- Migration CLI exit codes 0/1, log lines per applied/reverted migration.
|
|
|
|
## Edge cases
|
|
- Re-running `migrate up` on an up-to-date DB is a no-op.
|
|
- `migrate down` reverts exactly the last applied migration.
|
|
- Missing `DATABASE_URL` → clear error, non-zero exit, no partial state.
|
|
|
|
## Acceptance criteria
|
|
1. Given a fresh database, When migrations run, Then schema is created and repeating the run is a no-op.
|
|
2. Given applied migrations, When down runs, Then schema rolls back cleanly.
|
|
3. Table naming convention documented and enforced (documented rule + exemplar).
|
|
4. Dev PostgreSQL and Redis start with one command.
|
|
5. `verify.sh` green.
|
|
|
|
## Dependencies
|
|
- F-001 (project skeleton).
|
|
|
|
## Security implications
|
|
- Dev credentials live only in docker-compose dev file and `.env.example`; never real secrets in repo.
|
|
- DB user for tests should be dedicated (documented).
|
|
|
|
## SEO implications
|
|
- None.
|
|
|
|
## Performance implications
|
|
- Pool defaults conservative (max 10); no caching layer yet.
|