Use client-generated UUIDv7 primary keys for entity tables

[dgee2]

Goal

Move entity table primary keys from sequential int identity columns to client-generated UUIDv7 Guid values.

Why

Three reasons that stand independently of any future datastore change:

1. Idempotent creates. A client-generated id means the editor mints the recipe id before submitting, so retrying a POST after a timeout is safe instead of producing a duplicate recipe. This matters directly for the recipe editor, where a failed save is an expensive loss of work.
2. No enumeration surface. Recipe ids are currently sequential identity integers, so any authenticated caller can walk /api/recipe/1..n and map the size and shape of the corpus. GET /api/recipe/{id} returns 404 rather than 403 for inaccessible recipes partly to mitigate this; with GUIDs the exposure disappears structurally.
3. No cross-environment id collisions. Recipes can be moved or merged between environments without renumbering.

Portability to a non-relational store (e.g. DynamoDB, which has no auto-increment) is a secondary benefit, but should not be recorded as the primary justification — the reasons above hold regardless of whether that migration ever happens.

Scope

Convert to Guid — entity tables:

• Recipe
• RecipeIngredient
• RecipeStep
• MenuUser
• The Epic 6 tables when they land (RecipeShare, RecipePublication, RecipeSearchIndex, RecipeFavorite, RecipeDiaryEntry)

Leave as small integer ids — lookup/reference tables:

• UnitType, Unit
• The AccessScope lookup table

Open question: Ingredient (the canonical ingredient table, referenced by RecipeIngredient.CanonicalIngredientId). It is a reference table, but may grow to thousands of rows. Decide before implementing.

Why UUIDv7 rather than v4

Random v4 GUIDs as a clustered primary key cause page splits and index fragmentation on insert. UUIDv7 embeds a millisecond timestamp, so inserts stay append-mostly and the fragmentation problem does not arise, without needing a separate clustered key per table.

Guid.CreateVersion7() is in the BCL on net10.0 — no new dependency. Unlike NEWSEQUENTIALID(), it can be generated client-side, which is what enables the idempotent-create benefit above.

Known tradeoff: a v7 GUID leaks its creation time to anyone holding it. This is not a concern for the tables listed above. Any future table where it would be should use v4.

Timing

This is dramatically cheaper now than later:

• InitialCreate seeds only UnitType and Unit. There are no Recipe, RecipeIngredient, RecipeStep or MenuUser rows anywhere, so there is no data to convert.
• The frontend already models recipe ids as strings — RecipeDetail.vue takes recipeId: string, the TanStack query key does String(recipeId), putRecipe takes a string. The only place integer-ness leaks is data.id.toString() against the generated type.

The cost grows permanently once real user data exists.

Implementation notes

• Change the Vogen value objects from [ValueObject<int>] to [ValueObject<Guid>] (RecipeId, MenuUserId, and the step/ingredient ids).
• EF configuration: uniqueidentifier columns, ValueGeneratedNever(), remove UseIdentityColumn().
• Generate ids in the service layer (or accept a client-supplied id on POST) using Guid.CreateVersion7().
• Migration approach depends on whether any environment has already applied InitialCreate. If nothing is deployed, regenerating InitialCreate is cleaner than a conversion migration. See docs/database-migrations.md.
• Regenerate the frontend OpenAPI types (pnpm generate-openapi) and drop the .toString() on data.id.
• Unique index UX_Recipe_OwnerUserId_Title and all FK/cascade behaviour are unaffected in shape.

Follow-up (separate, once ids are client-generated)

Make POST /api/recipe idempotent: accept the client-supplied id and return the existing recipe on a repeat submit rather than a 409.

Acceptance criteria

• Listed entity tables use uniqueidentifier primary keys generated as UUIDv7
• Lookup tables retain their small integer ids
• Ids are generated by the application, not the database
• Frontend OpenAPI types regenerated; no integer assumptions remain in the client
• Existing integration tests pass unchanged in intent

Testing

• Integration: create/read/update/delete round-trip with GUID ids
• Integration: (OwnerUserId, Title) uniqueness still returns 409
• Unit: generated ids are UUIDv7 and monotonically increasing within a batch

[dgee2]

Soft delete (#1216) should land with or before this issue, so the entity tables are reshaped once rather than twice. It adds DeletedAtUtc to Recipe and changes UX_Recipe_OwnerUserId_Title to a filtered index.