DB: add a migration lint/verify step and keep schema.sql canonical automatically

[Depo-dev]

Summary

Automate keeping schema.sql canonical and lint migrations, so drift like the one fixed in #191 can never silently return.

Background & current behaviour

database/schema.sql was re-synced to the migration chain in #191 and the CI integration job now builds the test DB from migrations. But nothing prevents schema.sql from drifting again, and migrations aren't linted for idempotency/ordering/destructive patterns.

Why this matters for MVP

Schema drift caused real, silent breakage (audit_log/parse_errors missing from schema.sql). A CI guard makes the class of bug impossible going forward.

Proposed implementation

1. In CI, spin up Postgres, apply the migration chain, pg_dump --schema-only, normalise, and diff against a committed canonical dump; fail on divergence (or auto-regenerate schema.sql and fail if uncommitted).
2. Add a migration linter: enforce IF NOT EXISTS, forbid CONCURRENTLY inside a transaction, flag destructive statements (DROP, ALTER ... TYPE) for explicit review, enforce ordered numeric filenames.
3. Document the "adding a migration" workflow.

Acceptance criteria

• CI regenerates the canonical schema from migrations and fails on drift.
• Migration linter enforces idempotency/ordering and flags destructive ops.
• Documented migration-authoring workflow.

Affected files / components

• .github/workflows/ci.yml
• database/ (canonical dump + linter script)

Testing & verification

Introduce a deliberate drift in a test branch and confirm CI fails; a clean chain passes.

Related / dependencies

Follows up #191.

Out of scope

A migration framework change (stay with sqlx migrations).

---

Target branch

All work for this issue MUST target the dev branch. Cut your feature branch from dev and open the pull request against dev, never against main. Any PR opened against main for this issue will be closed and asked to re-target dev.