Adopt a conflict-resistant strategy for session DB migrations from parallel branches

[aheritier]

Problem

The session-store migration ledger (pkg/session/migrations.go) assigns each migration a sequential integer ID and a unique name, and is protected by a content-pinned digest test (pkg/session/migrations_pinned_test.go) that fails the build if an existing entry's fields change. That guards against accidental edits within a single branch, but it does nothing to prevent two independently developed branches from each appending a different migration under the same next ID (or the same name). Whichever branch merges last silently collides with a database that already recorded the other branch's migration under that ID.

Today, sqlitestore.New reacts to most migration failures — including this kind of ledger conflict — by moving the existing database aside to <path>.bak and starting fresh (pkg/session/sqlitestore/sqlitestore.go). For a session database this is a destructive, silent data-loss path: the user's history is preserved only by accident, in a .bak file most users don't know to look for or how to recover.

Desired invariants

• Migration identity is globally conflict-resistant, not just sequential-and-pinned per branch.
• Migrations apply in a well-defined, dependency-safe order even when authored on parallel branches.
• The catalogue and the on-disk ledger are validated against each other exactly (no silent partial application, no implicit normalization of drifted descriptions).
• Migration failures caused by an identity conflict fail closed — never an automatic reset/backup-and-recreate — since that discards session history.
• Recovery from a genuine conflict has a defined, documented procedure instead of being left to ad hoc manual repair.

Options to evaluate (not selecting one yet)

1. Merge-time reservation/registry — a checked-in table or CI check that reserves the next migration ID/name at PR-merge time, so collisions are caught before merge rather than at runtime.
2. Timestamp/UUID/content-addressed identities — replace or augment the sequential integer with an identity that two branches can't independently collide on.
3. Dependency graph — express migrations as a DAG instead of a flat sequence, so ordering doesn't depend on both branches agreeing on the same next integer.

Acceptance criteria (for whichever option is chosen)

• CI catches an identity conflict between two branches before merge, or the runtime detects and fails closed on one without resetting the database.
• Tests cover: two branches introducing conflicting IDs/names, a dependency-graph cycle (if applicable), and the existing append-only content-pin behavior continuing to work.
• A documented upgrade path for existing on-disk databases that already used the old sequential scheme.
• Backward compatibility: older binaries encountering a newer scheme still fail closed with a clear error (as ErrNewerDatabase does today), never silently truncate or reset data.
• Documentation (troubleshooting guide, session docs) describes the new conflict-detection/recovery behavior.

See also #3021 for related prior discussion.