# Data Integrity Patterns

> Constraints, triggers, audit trails, referential integrity, and data validation.

- Skill: `sethdford/data-integrity-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add sethdford/data-integrity-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/sethdford/data-integrity-patterns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: sethdford (https://skillmd.com/u/sethdford)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/sethdford/data-integrity-patterns

---


# Data Integrity Patterns

Maintaining data correctness through database constraints and patterns.

## Context

You are ensuring data stays correct. Use database constraints, not just application validation.

## Domain Context

- **Constraints**: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
- **Triggers**: Automatic actions on data changes; use sparingly
- **Audit Trail**: Record of what changed and when
- **Referential Integrity**: Foreign keys prevent orphaned records
- **Validation**: Business rules enforced at database level

## Instructions

1. **NOT NULL**: Columns that must have values
2. **UNIQUE**: Columns/combinations that must be unique
3. **FOREIGN KEYS**: References to other tables; prevent orphans
4. **CHECK**: Business rules (price > 0, status IN (values))
5. **Triggers**: Rarely; keep business logic in application
6. **Audit Trail**: Timestamp, user columns; or trigger for history table
7. **Enforcement**: Database enforces; application can't bypass

## Anti-Patterns

- No constraints; trust application to validate (it won't)
- Over-reliance on triggers; hard to test, understand
- Audit table with triggers; complex and slow
- CHECK constraints that are wrong; don't trust them until tested
- Not testing constraint enforcement

## Further Reading

- PostgreSQL constraints documentation
- Data Vault 2.0 (for audit trail patterns)
- Event sourcing for audit trails (alternative)