Migration-generated identifiers are not valid UUIDs; route all id creation through one factory #360

Background

Migrations that must invent a primary key build one in SQL, because migration text is handed straight
to SQLite and cannot call Guid.NewGuid(). Every such site hand-writes the same expression:

lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-' ||
lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(6)))

That expression does not produce a UUID. It produces 16 random bytes sliced 4-2-2-2-6 and
hyphenated, so it has a GUID's shape and none of its structure. A v4 UUID must carry 4 in the version
nibble (position 15) and one of 8/9/a/b in the variant nibble (position 20); both are left
random here. Measured against SQLite, six consecutive values:

Guid.Parse ignores version and variant bits, so nothing fails today. What the project has instead is
three id-generation strategies where its documentation implies two — deterministic content hashes for
domain entities, Guid.NewGuid() for infrastructure rows, and this — while
schemas/source-flat.schema.json tells a reader every id is a UUID v4. Anything that inspects a
version nibble finds otherwise.

Five files carry the hand-written expression: QuotinatorMigrations, AuditMigrations,
ImportConflictMigrations, NotificationLegacyMetadataMigrations, NotificationTranslationMigrations.
It spreads by copying, which is how it reached the fifth — no one checked what it emitted.

This has to be fixed in this milestone, before a release. The application is generating these
identifiers now, on every upgrade that runs one of those migrations, so every day this stays open adds
rows that a later decision cannot reach. Shipping the milestone first would make the affected set
permanent and larger.

A SQL-callable function is already an established mechanism here. #222 registered
UNICODE_CONTAINS via SqliteConnection.CreateFunction, so migration SQL calling a project-supplied
function is a pattern with precedent rather than a new idea.

What needs to be done

1. A single factory produces every new identifier the application creates, exposed as an interface so a
   test can substitute a deterministic sequence instead of a random one.
2. A SQLite user-defined function — registered on every connection the application opens, before any
   migration runs — returns a new identifier from that same factory, so migration SQL calls it rather
   than constructing one.
3. Identifiers the factory returns are valid v4 UUIDs: version nibble 4, variant nibble in
   8/9/a/b, canonical lowercase per
   ADR 012.
4. The factory supplies case-testing identifiers directly, so a casing test never has to hand-type a
   literal or invent a construct of its own. testing-policy.md already requires that any
   case-insensitivity fixture contain an alphabetic hex character and prefers one that starts with a
   letter — an all-digit id is unchanged by UPPER() and silently proves nothing, which is exactly what
   ImportBatch.ImportedBy doesn't follow the *Id naming convention every id-casing guard relies on #213 found live. The factory guarantees that property instead of leaving each test to remember it,
   and returns the same identifier in both casings so a test asserts a round-trip rather than comparing
   two separately-invented values.
5. Every migration added from this issue onward uses the function.
6. The hand-written randomblob expression is not used again, anywhere. This is the rule, not a
   preference: it is not a UUID, it reached five files by being copied, and the sixth copy is prevented
   here rather than found later. A guard test scans SQL constants and migration text and fails on a
   hand-written randomblob id construction, mirroring SqlIdCaseGuard's strip-then-scan technique
   rather than a maintained list of known sites. The five existing occurrences are exempted by name,
   so the exemption list is finite, visible, and cannot grow silently.
7. Rows already carrying a non-v4 identifier are left as they are — and this is a statement about
   stored rows only, never a licence to keep generating them. Rewriting a primary key means rewriting
   every foreign key pointing at it (System_Notification.AppVersionId references
   System_AppVersion.Id today), and the existing values are unique, parseable and canonically cased,
   so the migration risk outweighs correcting values nothing reads for their version bits.
8. An ADR records the resulting rule, since it constrains every future migration and is not obvious from
   the code: identifiers come from the factory, migrations call the function, the hand-written
   expression is forbidden, and existing rows are not rewritten.

Expected tests

Definition of done

• All expected tests listed above start red before implementation
• All requirements implemented
• All expected tests pass (green)
• No regression in related tests
• Findings summarised in a closing comment