ProofedMesh/db/README.md

ProofedMesh Database Layer

Alle Postgres-Schemas von ProofedMesh werden mit golang-migrate versioniert. Entscheidungsgrundlage: siehe ADR-0004.

Layout

db/
├── README.md              — diese Datei
├── trust/
│   ├── migrations/
│   │   ├── 0001_per_diem_rates.up.sql
│   │   ├── 0001_per_diem_rates.down.sql
│   │   ├── 0002_agents.up.sql
│   │   ├── 0002_agents.down.sql
│   │   ├── 0003_agent_keys.up.sql
│   │   └── 0003_agent_keys.down.sql
│   └── seeds/
│       └── 0001_phase1_agents.sql
└── expense/
    └── migrations/        — kommt im Phase-4-Rollout der DB-App

Seeds werden nicht von migrate versioniert — sie sind idempotentes Bootstrap-SQL für Phase-1-Fixturen (z.B. die drei Agent-Identitäten) und werden per psql -f oder äquivalentem Kubernetes-Job eingespielt. Jeder Seed MUSS ON CONFLICT … DO NOTHING / IF NOT EXISTS verwenden, damit mehrfaches Einspielen nach einem migrate down/up-Zyklus keine Duplikate produziert.

Jede logische Datenbank bekommt ein eigenes migrations/-Verzeichnis. Die Migrationstabelle (schema_migrations) wird je Datenbank separat gepflegt. Pro Postgres-Instanz können mehrere logische Schemas liegen — die Migrationen schreiben SQL mit CREATE SCHEMA IF NOT EXISTS und SET search_path.

Konventionen

• Dateinamen: NNNN_description.up.sql + .down.sql (vierstellige Nummer, Snake-Case).
• Nummerierung streng monoton. Fehlerhafte Migrationen werden nie umnummeriert — immer durch eine neue Migration korrigiert.
• Immutable Business-Felder werden per BEFORE UPDATE-Trigger gesichert (siehe per_diem_rates).
• Postgres-Extensions werden am Anfang der Migration eingezogen (CREATE EXTENSION IF NOT EXISTS …).
• Jede up.sql MUSS ein down.sql haben; die down.sql DARF im Business-Fall unvollständig oder destruktiv sein, muss das dann aber im Kommentar sagen.

Lokal ausführen

Voraussetzung: eine lokale Postgres (z.B. docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:16) und die migrate-CLI (brew install golang-migrate / apt install migrate / go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest).

# Up (alle noch nicht angewandten Migrationen)
migrate -path db/trust/migrations \
  -database "postgres://postgres:postgres@localhost:5432/trust?sslmode=disable" up

# Down um eine Version
migrate -path db/trust/migrations \
  -database "postgres://postgres:postgres@localhost:5432/trust?sslmode=disable" down 1

# Aktuelle Version anzeigen
migrate -path db/trust/migrations \
  -database "postgres://postgres:postgres@localhost:5432/trust?sslmode=disable" version

In Prod

• Rollout als dedizierter Kubernetes-Job (job-migrate-trust) VOR dem Rolling-Update der trust-api-Pods. Siehe ADR-0004 → Ausführungsmodell.
• Trust-API startet nur, wenn schema_migrations.dirty = false und die höchste Version die vom Code erwartete erreicht.
• Rollback ist ein manueller Prozess mit CTO-Freigabe.

CI

Der Job db in .forgejo/workflows/validate.yml macht pro PR und main-Push:

1. Startet postgres:16-alpine als Container.
2. migrate up → prüft, dass per_diem_rates, agents und agent_keys angelegt sind.
3. migrate down 2 → prüft, dass agents und agent_keys wieder entfernt sind, per_diem_rates aber bleibt (Trenn-Test).
4. migrate up erneut und spielt db/trust/seeds/0001_phase1_agents.sql zweimal ein (Idempotenz-Test, Erwartung: genau 3 Seed-Zeilen).

Lint (sqlfluff / pg_format) ist noch nicht aktiviert — kommt in einem separaten Heartbeat.