db-referential-integrity
Audit foreign-key integrity — missing FKs that allow orphan rows (financial/auth = severity 5), absent or unsafe ON DELETE/ON UPDATE actions, reference cycles, and composite-FK column/order mismatches. Module M3. Feeds both the Design & Integrity (Integridad referencial) and Performance & Scale (Query) scores.
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-referential-integrityIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill audits database referential integrity by parsing DDL and executing SQL queries. It is generally safe but carries a minor risk of indirect prompt injection due to its processing of untrusted database schema and query results without explicit boundary markers or sanitization.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
db-referential-integrity (M3)
Foreign keys are the database enforcing that a reference points at something real — the cheapest, most durable guarantee against orphan data. Missing them pushes integrity into application code, where it silently rots. This module is both-axis: integrity on design, and join/planning behaviour on performance. It applies to FK-supporting relational engines only; the document/KV profiles drop it entirely (no false penalty).
What it checks
- Missing FK: a column named/typed as a reference (
*_idmatching another table's PK) with noFOREIGN KEYconstraint. On financial or auth tables (orders, payments, sessions, memberships) an orphan-enabling missing FK is the severity-5 cap case. - ON DELETE / ON UPDATE action: FK with no explicit referential action where the default (
NO ACTION/RESTRICT) is wrong for the relationship, or a dangerousCASCADEthat can mass-delete (e.g. deleting a user cascades to invoices). Each action must be intentional. - Reference cycles: FK cycles (A→B→C→A) that block ordered insert/delete and complicate migrations — severity 4.
- Composite FK mismatch: multi-column FK whose column set/order does not match the referenced unique key, or partial composite references.
- Untrusted/NOT VALID FK left unvalidated after a backfill (Postgres
NOT VALID).
Axis & severity
- Missing FK enabling orphan financial/auth rows: severity 5,
fail, axisboth, confidenceestablished(caps). - FK cycle: severity 4,
warn, axisboth. - Missing/over-broad
ON DELETE(silentRESTRICTblocking, or unintendedCASCADE): severity 3–4,warn, axisdesign. - Composite-FK order mismatch: severity 3,
warn. - Note: FK columns lacking a supporting index are owned by M11 (indexing), not here — cross-reference, do not double-count.
Tier-0 static check
Parse DDL via scripts/parse-schema.mjs: for each *_id-style column, check whether a matching FOREIGN KEY/REFERENCES clause exists; enumerate declared FKs and flag those without an explicit ON DELETE/ON UPDATE action; build the FK graph and detect cycles. Directional ORM-source parses never raise the sev-5 orphan cap.
Tier-1 verification query
List FK constraints and their actions (confirms missing/dangerous actions):
-- $DATABASE_URL, read-only
SELECT conrelid::regclass AS child, confrelid::regclass AS parent,
confdeltype AS on_delete, confupdtype AS on_update, convalidated AS validated
FROM pg_constraint WHERE contype = 'f';
Confirm real orphans exist for a suspected missing FK:
SELECT count(*) AS orphans FROM child c
LEFT JOIN parent p ON c.parent_id = p.id WHERE p.id IS NULL AND c.parent_id IS NOT NULL;
No live DB → needs_api for the orphan count, never a silent pass.
Findings
Emit per schema/finding.schema.json. Examples:
M3.payments.user_id_no_fk—user_idreferences no table; orphan payments possible (severity 5,fail, axisboth, confidenceestablished).M3.invoices.fk_cascade_deletes_financial—ON DELETE CASCADEfrom users mass-deletes invoices (severity 4,warn, axisdesign,fixable: proposed).M3.graph.fk_cycle_a_b_c— reference cycle blocks ordered load (severity 4,warn, axisboth). Each finding:evidence.observedquotes the FK/column DDL or catalog row verbatim;verification.reproduceis a runnable query above (method: ddl_parse/constraint_check/query_stat);expected_impactbanded + confidence-tagged.
Honesty
- A missing FK only caps when the orphan risk is real (financial/auth integrity); a missing FK on a low-stakes lookup is at most a
warn. - App-level integrity (validation, soft FKs) is a mitigation, not a substitute — note it, but the DB-level guarantee is absent. Do not claim app code "fixes" it.
- Never assert orphan rows exist without a Tier-1 count; static, it is
directional.
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-referential-integrity">View db-referential-integrity on skillZs</a>