Milestone 1.1.1: Postgres Migration System

Status: Complete (merged PR #11, 3fcf75f)
Goal: 1.1 Persistence and auth data plane

Summary

Introduce infra/migrations/postgres/, a migration runner wired into Makefile / dev-tool.sh, and the minimal ibex_core schema required for auth (organizations + tokens) with RLS policies.

Branch

chore/m1-1-1-postgres-migrations

PR title (example)

chore(db): postgres migrations (m1.1.1)

Estimated effort

2–3 days

Prerequisites

• Foundation-004 complete on main
• make compose-dev-up healthy Postgres

Tasks

• Write ADR-0005: migration tool choice, directory layout, CI policy
• Add github.com/golang-migrate/migrate/v4 to root go.mod (or shell-only CLI via documented install — prefer embed for CI reproducibility)
• Create infra/migrations/postgres/ with numbered SQL files:
  • 000001_init_schemas.up.sql — ibex_core schema
  • 000002_organizations.up.sql — subset of DATABASE_SCHEMA.md organizations table
  • 000003_tokens.up.sql — ibex_core.tokens subset (PAT fields, Argon2id hash column, permission bitmap, org FK)
  • 000004_rls.up.sql — RLS policies + helper role grants for dev
  • Matching .down.sql for each
• Add infra/scripts/db-migrate.sh (up, down one step, version)
• Add Make targets via dev-tool.sh: db-migrate, db-migrate-down, db-version
• Document env vars in ENVIRONMENT_VARIABLES.md (POSTGRES_DSN for migrate)
• CI advisory job: docker compose up + migrate against ephemeral Postgres (or reuse compose-validate pattern)
• Integration test: migrate twice (second run no-op); verify tables exist; basic RLS smoke with two org_ids

Files affected (anticipated)

Testing requirements

• Unit: migration file naming/version monotonicity check (script or Go test)
• Integration (real Postgres): apply up → verify schema → apply up again → no error
• Cross-tenant RLS: session A cannot SELECT org B row when app.current_org_id set (minimal SQL test or Go integration)

Per TESTING_STRATEGY.md: do not mock RLS behavior.

Documentation updates

• ADR-0005
• DATABASE_SCHEMA.md reference section
• DEVELOPMENT_GUIDE.md local DB section
• docs/app/content/roadmap/CURRENT_STATE after merge

Definition of done

• make compose-dev-up && make db-migrate succeeds on clean volume
• Re-running make db-migrate is idempotent
• Schema for organizations + tokens matches documented subset (columns, constraints, indexes)
• RLS enabled on tenant tables; fail closed without app.current_org_id
• Required CI green; advisory migrate job green if added
• No application token validation logic in this PR (migrations only)

Risks