World-Building Platform

Appearance

ADR-005: ID Strategy ​

Status: Accepted Date: 2026-02-07

Context ​

Internal database IDs should not be exposed to users for security and usability reasons. The platform needs a user-friendly public identifier system, similar to Jira/YouTrack project-based IDs (e.g., PROJ-123).

Decision ​

  • Internal IDs: UUIDv7 across all entities
  • Public IDs: Project key + shared sequence number (e.g., VNO-42)
  • Sequence counter: DB-side atomic increment on the Project entity

Rationale ​

UUIDv7 for Internal IDs ​

  • Globally unique like standard UUIDs
  • Time-ordered prefix for chronological sorting and efficient B-tree indexing in PostgreSQL
  • Native support in .NET 9+ via Guid.CreateVersion7()
  • No collision risk across distributed systems

Project-Scoped Sequence Numbers ​

  • User-friendly and memorable (VNO-42 vs a UUID)
  • Follows established patterns from project management tools (Jira, YouTrack, Linear)
  • Each project has a Key (e.g., "VNO") set at creation, unique across the platform
  • Each aggregate root gets a sequence number scoped to its project

Shared Sequence (not per-type) ​

  • One counter per project for all entity types (characters, timelines, factions)
  • Simplest to implement - no type prefixes, no multiple counters
  • Adding new entity types doesn't require new counters

DB-Side Atomic Counter ​

  • NextSequenceNumber column on the Project table
  • Incremented via UPDATE ... SET "NextSequenceNumber" = "NextSequenceNumber" + 1 ... RETURNING
  • PostgreSQL row-level locking prevents race conditions under concurrent requests
  • Application-side incrementing would require distributed locking or retry logic

Alternatives Considered ​

  • Expose UUIDs directly - Technically simpler but not user-friendly. Long, unmemorable strings in URLs
  • Sqids/Hashids - Short encoded strings (e.g., k5nZ3d). Compact but not meaningful to users
  • Slugs - Human-readable (e.g., vampire-new-orleans) but need uniqueness handling and rename logic
  • Per-type sequences - Separate counters with type prefixes (VNO-C1, VNO-T1). More complex, no real benefit
  • Application-side counter - Race condition risk under concurrent requests without additional locking

Consequences ​

  • Project Key must be validated (alphanumeric, unique, immutable after creation)
  • Only aggregate roots get sequence numbers (Section, Item, FactionMembership etc. are accessed through their parent)
  • The public ID VNO-42 is composed at runtime, not stored as a single field
  • API routes use the key and sequence: /api/projects/VNO/characters/42