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 (*_id matching another table's PK) with no FOREIGN KEY constraint. 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 dangerous CASCADE that 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, axis both, confidence established (caps).
• FK cycle: severity 4, warn, axis both.
• Missing/over-broad ON DELETE (silent RESTRICT blocking, or unintended CASCADE): severity 3–4, warn, axis design.
• 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.