db-keys (M2)

The primary key is the identity contract of a row: it dictates how the row is referenced, how it clusters on disk, and how it scales. A missing or exhausting PK is one of the few defects that can cap a score. This module is both-axis: identity/modeling on design, index locality and exhaustion on performance.

What it checks

• No primary key (or no unique row identifier on a collection): the cap case. A table without a PK cannot be safely updated, replicated, or de-duplicated.
• PK type strategy: random UUIDv4 as a clustered/leading B-tree key fragments inserts and bloats indexes; prefer time-ordered UUIDv7/ULID or bigint identity. Flag uuid_generate_v4() defaults on hot insert tables.
• Postgres ≥18 (GA Sept 2025): native uuidv7() (RFC 9562) is the recommended time-ordered UUID PK default (timestamp recoverable via uuid_extract_timestamp()). When the detected engine is PG ≥18, treat a gen_random_uuid()/v4 default as a downgrade and recommend uuidv7(). Pre-18 or non-Postgres engines: gen_random_uuid() and app-side UUIDv7 remain fine.
• Natural vs surrogate: a mutable natural key (email, slug) used as PK propagates churn through every FK; recommend a stable surrogate.
• Integer width exhaustion: int4/serial/SERIAL PK on a table whose volume can exceed ~2.1B rows — int4 exhaustion is a production-halting event. bigint/bigserial is the safe default.
• Composite PK ordering: when composite, the leading column should match the dominant access/partition pattern (ties to M9 tenant_id, M11 ESR).

Axis & severity

• No PK on a relational/wide-column table: severity 5, fail, axis both, confidence established (caps both scores).
• int4/serial PK near exhaustion: severity 5 only with Tier-1 row-count evidence; otherwise severity 4 warn, confidence directional (never caps without live data).
• UUIDv4 clustered PK on a hot-insert table: severity 3, warn, axis performance, confidence directional.
• Mutable natural-key PK: severity 3, warn, axis design.