status: accepted date: 2026-03-12

ADR-0008: UUIDv7 Primary Keys with URL Slugs and BGG Cross-References

Context and Problem Statement

The OpenTabletop project needs a primary key strategy that works across distributed systems (multiple implementations, offline-capable clients, data imports) while also providing human-friendly URLs. Additionally, since much of the initial data will be migrated from BoardGameGeek, we need a way to maintain cross-references to BGG identifiers for backward compatibility and data reconciliation.

Decision Drivers

  • Primary keys must be globally unique without central coordination for distributed implementations
  • URLs should be human-readable and shareable (e.g., /games/catan rather than /games/01912f4c-…)
  • Time-ordered keys improve database index performance (B-tree locality)
  • Cross-references to existing BGG IDs are essential for migration and interoperability

Considered Options

  • Auto-incrementing integers
  • UUIDv4 (random) primary keys
  • UUIDv7 (time-ordered) primary keys with URL slugs and BGG cross-references

Decision Outcome

Chosen option: “UUIDv7 with slugs and BGG cross-references”, because UUIDv7 provides globally unique, time-ordered identifiers that can be generated by any implementation without coordination while maintaining excellent B-tree index performance. URL slugs are stored as a separate unique column, enabling human-friendly URLs like /games/catan while keeping the UUID as the stable internal identifier. BGG IDs are stored in a dedicated cross-reference table (external_references) rather than as a column on the games table, allowing multiple external ID systems. Auto-increment was rejected because it requires central coordination. UUIDv4 was rejected because its random ordering causes index fragmentation.

Consequences

  • Good, because UUIDv7’s time-ordering provides natural chronological sorting and excellent index locality
  • Good, because slugs enable memorable, shareable URLs without exposing internal IDs
  • Good, because the cross-reference table supports BGG IDs and any future external system references
  • Bad, because UUIDs are larger than integers (16 bytes vs 4-8 bytes), increasing storage and join costs
  • Bad, because slug uniqueness must be enforced and slug generation requires collision handling (e.g., catan, catan-2, catan-3)