db-keys
Audit primary-key strategy — missing PK (severity 5), surrogate vs natural keys, UUIDv4 index fragmentation vs time-ordered UUIDv7/ULID/bigint, and int4/serial exhaustion on high-volume tables. Module M2. Feeds both the Design & Integrity (Llaves) and Performance & Scale (Escala) scores.
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-keysIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is a database auditing utility designed to analyze primary key strategies for performance and integrity risks. It uses local scripts and standard SQL queries to inspect schema definitions without any detected malicious intent or data exfiltration.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
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
UUIDv4as a clustered/leading B-tree key fragments inserts and bloats indexes; prefer time-orderedUUIDv7/ULIDorbigintidentity. Flaguuid_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 viauuid_extract_timestamp()). When the detected engine is PG ≥18, treat agen_random_uuid()/v4 default as a downgrade and recommenduuidv7(). 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/SERIALPK on a table whose volume can exceed ~2.1B rows —int4exhaustion is a production-halting event.bigint/bigserialis 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, axisboth, confidenceestablished(caps both scores). int4/serial PK near exhaustion: severity 5 only with Tier-1 row-count evidence; otherwise severity 4warn, confidencedirectional(never caps without live data).UUIDv4clustered PK on a hot-insert table: severity 3,warn, axisperformance, confidencedirectional.- Mutable natural-key PK: severity 3,
warn, axisdesign.
Tier-0 static check
Parse DDL/snapshot via scripts/parse-schema.mjs: assert every table declares a PRIMARY KEY (or document-store _id/shard identity); inspect PK column type for uuid + gen_random_uuid()/uuid_generate_v4() default, and for int4/serial. Directional program-source parses never raise the sev-5 no-PK cap.
Tier-1 verification query
Find tables with no primary key:
-- $DATABASE_URL, read-only
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
ON c.table_name = t.table_name AND c.table_schema = t.table_schema
AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN ('pg_catalog','information_schema')
AND c.constraint_name IS NULL;
Check int4 PK headroom (confirms exhaustion sev-5):
SELECT max(id) AS cur, 2147483647 AS int4_max, round(100.0*max(id)/2147483647,1) AS pct_used FROM <table>;
Findings
Emit per schema/finding.schema.json. Examples:
M2.events.no_primary_key— base table has no PK (severity 5,fail, axisboth, confidenceestablished).M2.audit_log.int4_pk_exhaustion—id integerPK at high pct of int4 max (severity 5 Tier-1 / 4 static, axisboth).M2.orders.uuidv4_clustered_pk— randomuuidPK withgen_random_uuid()default fragments inserts (severity 3,warn, axisperformance, confidencedirectional). Each finding:evidence.observedquotes the PK DDL or the catalog result verbatim;verification.reproduceis the runnable query above (method: ddl_parse/schema_introspect/query_stat);expected_impactbanded + confidence-tagged.
Honesty
- Do not claim int4 exhaustion is imminent without a Tier-1
max(id)and growth signal — static, it isdirectionaland never caps. UUIDv4is not "wrong"; the cost is index locality on hot inserts. Band the impact, never fabricate an insert-throughput percentage.- A surrogate vs natural choice is contextual; present the trade, do not assert one as universally correct.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/hainrixz/claude-db/db-keys">View db-keys on skillZs</a>