Documentation

adrs/034-guid-primary-keys.md

ADR 034: Standardize on GUID Primary Keys for Domain Entities

Status: Accepted
Date: 2025-10-26
Deciders: Architecture Team
Related: ADR 035 (Prism Journal Tables)

Context

The Acsis platform originally used heterogeneous primary key types across components:

  • long (64-bit integers) for most entities
  • int (32-bit integers) for some reference data
  • Guid for some newer entities
  • Composite keys for junction tables

This inconsistency caused several issues:

  1. Type Confusion: Different entities used different ID types, making relationships harder to reason about
  2. Migration Complexity: Changing an entity's ID type required cascading foreign key changes
  3. Distributed System Challenges: Auto-incrementing integers don't work well across distributed databases
  4. Passport Pattern Mismatch: The Passport system (global registry) uses Guids, creating an impedance mismatch
  5. Junction Table Overhead: Explicit junction entities (UserRole, RolePermission) added complexity for simple many-to-many relationships

Specific Pain Points

  • Tenant ID inconsistency: Some components had long TenantId, others Guid TenantId
  • Passport redundancy: Entities had both a primary key and a separate passport_id column
  • Junction entity audit data: Audit fields on junction tables couldn't be easily maintained with implicit many-to-many
  • Cross-component references: Different ID types made cross-component queries more complex

Decision

We will standardize all domain entity primary keys to use Guid (UUID v7), with the following rules:

Primary Keys

GUID Entities (Domain):

  • All domain entities use Guid as primary key
  • [DatabaseGenerated(DatabaseGeneratedOption.None)] - app generates the ID
  • Exception: Reference data entities (Quantity, UnitOfMeasure, etc.) keep int/short IDs for efficiency
  • Exception: PlatformType and PlatformTypeCollection keep long IDs (system architecture tables)

Tenant ID:

  • All entities use Guid TenantId (non-nullable where applicable)
  • Provides tenant isolation in multi-tenant scenarios
  • Tenant.Id itself remains Guid for consistency

Passport Integration

Entities that participate in the Passport system (global registry):

  • Use their primary Id as the Passport foreign key (no separate passport_id column)
  • Include short PlatformTypeId property (e.g., 30 for USER, 31 for GROUP)
  • Configure with HasPassport(PTID) extension in DbContext
  • The Passport table creates a composite FK on (Id, PlatformTypeId)

Example:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column("id", Order = 1)]
public Guid Id { get; set; }

[Column("platform_type_id", Order = 2)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public short PlatformTypeId { get; set; } = 30; // PTIDS.USER

Many-to-Many Relationships

Use implicit many-to-many with shadow properties:

  • Remove explicit junction entities (UserRole, RolePermission)
  • Use EF Core's HasMany().WithMany().UsingEntity() configuration
  • Removes need for junction entity classes, reduces boilerplate
  • Join tables still exist in database with same names for backwards compatibility

Example:

modelBuilder.Entity<User>()
    .HasMany(u => u.Roles)
    .WithMany(r => r.Users)
    .UsingEntity(j => j.ToTable("user_roles", "identity"));

Trade-off: Lose ability to track assignment metadata (AssignedAt, AssignedBy). This is acceptable because:

  • Journal tables provide comprehensive audit trail
  • Assignment metadata was rarely used
  • Can be re-added later if needed

Migration Philosophy: Modern First, Never Backward

CRITICAL: This section documents a fundamental architectural principle that must never be violated.

The Golden Rule

When you encounter a mismatch between:

  • A DTO/API model using Guid (modern pattern)
  • A database entity using long/int (legacy pattern)

The database entity is WRONG and needs migration. You must NEVER change the DTO to accommodate the legacy type.

Why This Matters

The Dynaplex architecture represents a clean break from legacy systems. We are migrating FROM the old AssetTrak SQL Server system TO a modern, distributed architecture. This is a one-way migration:

Legacy (long/int IDs) ──────────> Modern (Guid IDs)
                        NEVER <──

What You MUST Do

  1. STOP if you see a type mismatch between API and database
  2. NEVER write new code that accommodates long/int where Guid should be
  3. ALWAYS create a database migration to change the schema toward Guid
  4. ASK if uncertain whether something is intentionally non-Guid (reference data exceptions exist)

What You Must NEVER Do

// WRONG: Changing a modern DTO to match legacy schema
public class LocationResponse
{
    public long Id { get; set; }  // NO! This accommodates legacy
}

// RIGHT: Keep the DTO modern, fix the database
public class LocationResponse
{
    public Guid Id { get; set; }  // Correct - now migrate the database
}

Exceptions (Intentionally Non-Guid)

Some entities use numeric IDs by design, not legacy debt:

  • Reference Data: Quantity, UnitOfMeasure, Language - use int/short for query efficiency
  • Platform Types: PlatformType, PlatformTypeCollection - system architecture tables with long IDs
  • Lookup Tables: Small, stable, frequently-joined tables may use numeric IDs

If you're unsure whether an entity falls into these categories, ask before proceeding.

Rationale

  1. Distributed Systems: Guids work across service boundaries without coordination
  2. Passport Alignment: The Prism/Passport system uses Guids; mismatched IDs create impedance
  3. No Backward Steps: Every accommodation of legacy types increases technical debt
  4. Clean Architecture: Modern components shouldn't know about legacy constraints

Consequences

Positive

✅ Consistency: All domain entities use same ID type
✅ Distributed-Friendly: Guids work across multiple databases/services
✅ Passport Alignment: Single ID serves both entity and Passport needs
✅ Reduced Coupling: No separate passport_id to keep in sync
✅ Cleaner Code: Implicit many-to-many reduces boilerplate
✅ Future-Proof: Easier to split into microservices later
✅ Better Performance: UUID v7 has better indexing characteristics than v4

Negative

⚠️ Migration Complexity: Existing databases require complex data migrations
⚠️ Larger Indexes: Guids are 16 bytes vs 8 bytes for long
⚠️ Manual ID Generation: Application must generate IDs (can't rely on database)
⚠️ Lost Assignment Metadata: No built-in tracking of when/who assigned roles/permissions
⚠️ Reference Data Exception: Still need to remember int/short for lookups

Migration Strategy

Components are being refactored incrementally:

  1. Phase 1: Identity component (completed)
  2. Phase 2: Spatial, Workflow, Catalog components
  3. Phase 3: Remaining components
  4. Phase 4: Remove old migrations, create consolidated baseline

Each component migration:

  1. Update entity property types
  2. Update navigation properties
  3. Remove obsolete junction entities
  4. Update DbContext configurations
  5. Build and verify
  6. Generate new migrations

References

Notes

  • UUID v7 is preferred over UUID v4 for better database indexing (time-ordered)
  • Reference data (Quantity, UnitOfMeasure, Language) keeps numeric IDs for query efficiency
  • The decision to remove junction entity audit data was made after confirming journal tables provide adequate audit trail
  • This ADR supersedes any previous conventions about ID types