ADR-002: UUID v7 for Primary Keys

Time-ordered: UUID v7 embeds a Unix timestamp in the most significant bits, providing natural chronological ordering without requiring ORDER BY created_at. This improves B-tree index locality and reduces page splits in PostgreSQL.

Globally unique: No cross-service coordination needed. Services can generate IDs independently without collision risk.

Standard format: UUID v7 is a recognized RFC standard, widely supported in libraries and tools. The uuid Rust crate supports v7 natively.

Cross-service references: Placement and exchange services reference case/person IDs from the cases service as plain UUIDs — no foreign key constraints across databases, but IDs are still meaningful and time-traceable.

Why not UUID v4: Random UUIDs cause B-tree index fragmentation. With the volumes expected in child welfare (thousands of records), v7’s time-ordering provides measurable insert performance benefits.

Why not ULID: ULIDs offer similar benefits but use a non-standard encoding. UUID v7 uses the standard UUID format, compatible with PostgreSQL’s native UUID type and all existing tooling.

All id columns are UUID PRIMARY KEY DEFAULT uuidv7() (PostgreSQL). CRAIG ships a uuidv7() SQL function in migrations (services/*/migrations/*_uuid_v7_defaults.sql) because stock PostgreSQL 16 has no built-in UUID v7 generator. Application code can still generate UUIDs client-side via craig_common::id::new_id(), which is preferred for cross-service consistency and for deterministic seed data.

IDs leak creation time (to millisecond precision). This is acceptable for a government case management system where creation timestamps are already stored explicitly.

Seed data and tests use pre-generated UUID v7 values to maintain referential integrity.