# ADR-068: UUID Storage as VarChar(36)

**Status:** Accepted
**Date:** 2026-04-10
**Context:** Enterprise permission system schema design (Phase 1)

## Decision

All UUID columns use `VarChar(36)` storage, not `BINARY(16)`.

## Context

The permission system adds 18 new models with UUID primary keys and foreign keys. The question arose during greenfield review whether to use `BINARY(16)` (compact, faster comparisons) or `VarChar(36)` (human-readable, debuggable).

### Option A: BINARY(16)

- 16 bytes per UUID vs 37 bytes (VarChar + length byte) — 2.3x more compact
- Binary comparison is faster than collation-aware string comparison
- Smaller indexes = more keys per B-tree page = fewer disk reads
- At 100M audit log rows/year with 5 UUID columns, the difference is ~10GB/year of index storage

### Option B: VarChar(36) (chosen)

- Human-readable in query results, logs, debug tools, Sequel Ace
- Consistent with every existing model in the codebase (User, Chat, Message, Model, DataSource, etc.)
- No type conversion needed at cross-table join boundaries (permission models join existing models via `userId`)
- No application-level `UUID_TO_BIN()`/`BIN_TO_UUID()` wrapper functions needed
- Prisma's `@default(uuid())` generates VarChar natively

## Rationale

1. **Consistency outweighs compactness.** Mixing VarChar(36) and BINARY(16) across tables that join together (e.g., `UserRole.userId` → `User.id`) would require type conversion at every join, which is worse for performance than consistent VarChar(36).

2. **The performance difference is a constant factor, not algorithmic.** At the projected scale (1,000 tenants, 10,000 users/tenant), the bottleneck will be query patterns and index coverage, not byte-level storage efficiency. The 3 targeted indexes added during review (RoleInheritance childRoleId, UserRole userId, UserPermission tenant+user+type) provide orders-of-magnitude more impact than a storage format change.

3. **Debuggability matters for a security-critical system.** When investigating permission denials, audit log entries, or role assignments, being able to read UUIDs directly in database queries without conversion functions reduces investigation time.

4. **Migration cost is prohibitive for existing tables.** Converting the existing 12 production models to BINARY(16) would require downtime, data conversion for every row and FK, and application code changes at every UUID read/write point. Not justified for a constant-factor improvement.

## Consequences

- Index sizes are ~2.3x larger than BINARY(16) for UUID columns
- AuditLog table at 100M rows/year will accumulate ~10GB more index storage annually than BINARY(16)
- If AuditLog query performance becomes a measured bottleneck, consider BINARY(16) for that table specifically (isolated migration, no cross-table joins)

## Revisit Conditions

- AuditLog table exceeds 500M rows and query latency degrades past the <2s p99 SLO
- A project-wide migration to BINARY(16) becomes feasible (all 30+ models at once)
- MySQL introduces transparent UUID-to-binary optimization that eliminates the tradeoff