feat(db): versioned schema migrations with idempotent backfill

[Vasanthdev2004]

What

Add a versioned migration system for the Postgres schema. Migrations are
now recorded in a schema_migrations table and applied at most once per
node, inside a single transaction.

Closes the gap called out in docs/OSS-READINESS-AUDIT.md:78
("no versioned database migration system; schema is created/altered
from code at startup, which is convenient early on but risky for live
upgrades").

Why

• Today every node restart re-executes ~140 inline &str SQL statements.
  That's safe only because every statement is IF NOT EXISTS. The
  moment we need to add a non-IF NOT EXISTS change (rename, add a
  NOT NULL column to a large existing table, add an index without
  IF NOT EXISTS, etc.) we have no record of what's been applied where.
• A live network with 2+ public nodes needs a way to confirm every node
  is at the same schema version.
• This is the prerequisite for v0.4.x work that will require real
  schema changes (UCAN revocation table, per-repo authorization,
  metrics counters, etc.).

Behavior change

None observable to operators. The schema content is byte-for-byte
identical. What changes internally:

1. A new schema_migrations table is created.
2. On a fresh install: all 25 tables / ~140 statements run inside
   one transaction and are recorded as v1 applied.
3. On an existing install: v1 is recorded as already applied without
   re-running statements (detected via the canonical repos table).

Operator action required: none.

Safety properties

• Atomic per migration: each migration runs in a single transaction.
  A failure mid-way rolls the whole version back; the node refuses to
  start rather than booting on a partially-applied schema.
• Idempotent: if a node is killed mid-migration, on restart the
  version is still unrecorded, so the runner picks it up again and
  completes it.
• Statement-level error context: with_context includes the
  failing SQL string and migration version in any error chain, so
  operators don't have to dig through logs.
• Backwards-compatible for existing installs: the legacy-install
  path detects the repos table and short-circuits the v1
  application. No existing operator will see their 140 statements
  re-run.

Testing

• cargo fmt --all -- --check — clean
• cargo clippy --workspace --all-targets -- -D warnings — clean
• cargo test --workspace — all tests pass, including 6 new
  db::migration_tests::* tests that validate the static migration
  catalogue:
  • migrations_are_non_empty
  • migration_versions_are_strictly_increasing
  • migration_versions_start_at_one
  • migration_names_are_non_empty_and_distinct
  • migration_bodies_are_non_empty
  • v1_name_is_the_initial_schema (catches an accidental rename
    of v1, which would silently break the legacy-install backfill)

A new Db::migration_status() returns Vec<(version, name, applied_at)>
for ops/observability. It is intentionally not wired into any
HTTP route in this PR — that's a one-line follow-up. The catalogue
itself is the API.

Risk

Low. The change is purely additive. Every existing IF NOT EXISTS
guard is preserved inside the v1 migration body, so the schema content
is identical. Failures roll back the transaction and prevent the node
from starting (the correct behavior on a partially-applied schema).

The only failure mode worth flagging: if a node's Postgres is reachable
but the repos table is missing for some reason (manual DROP TABLE,
a fresh empty DB, etc.), the legacy-install backfill will run v1 from
scratch. That is the correct behavior.

Follow-ups (not in this PR)

• Surface Db::migration_status() via gl status and/or
  /api/v1/stats so operators can confirm a node's schema version
  at a glance.
• Begin adding real schema changes as v2, v3, … as they are needed
  (this PR lays the groundwork; v2 will be a no-op Migration { version: 2, name: "noop_placeholder", stmts: &[] } if we want a clean version
  bump point).

🤖 Generated with Claude Code

[kevincodex1]

LGTM!