Defect Scenarios Best Practices
Post-mortem schema changes into lint rules. Every modeling defect that became an incident should leave a CI check or review gate behind.
How to Use This List
- After each data incident, map root cause to a checklist item and automate where possible.
- Run orphan detection and fan-out fixture tests nightly on production snapshots (sanitized).
- Include defect scenario links in schema review PR template.
- Revisit rules when PostgreSQL major version upgrades change DDL behavior.
A - Prevention at Design Time
- Require FK on every
*_idreferencing another table. Waivers need ADR comment in migration. - Model M:N with junction tables before writing queries. Block PRs that add
tag1,tag2columns. - Use
timestamptzfor absolute instants. Ban newtimestamp without time zonefor user events in linter. - Prefer
text+CHECKover ENUM for evolving states. Enum only with closed set ADR. - Document fact grain for reporting tables. "One row per order line" in table comment.
B - Detection in CI and Jobs
- Orphan query suite: zero rows for every FK relationship. Run against staging refresh nightly.
- Fan-out revenue fixture test with known totals. Assert SUM matches hand-calculated value.
- Cross-tenant negative tests per tenant table. Tenant A never sees tenant B rows.
- Parse migrations for
ADD VALUEon enums. Flag for DBA review and load test requirement. -
EXPLAINregression for top 10 reporting queries. Row count explosion fails CI.
C - Operational Response
- Post-mortem template includes schema root cause category. Fan-out, orphan, enum, timezone, missing tenant filter.
- Convert post-mortem action items into lint rules within one sprint. No repeat incidents without new gate.
- Data quality dashboard for orphan counts and duplicate determinants. Trend up = page on-call.
- Freeze destructive DDL during integrity incident recovery. Fix data before adding constraints.
- Record remediation SQL in incident ticket. Becomes runbook and test fixture.
D - Culture and Review
- Schema review includes "how does this fail?" section. Reference defect scenario pages by name.
- DBA signs off on hot-path column type changes. Especially
timestampvstimestamptzand enum. - BI semantic layer grain reviewed by data engineering. No auto-join without grain doc.
- New engineers read Defect Scenarios Basics in onboarding week one.
- Quarterly game day: inject orphan rows in staging, measure detection time.
FAQs
What is the highest ROI automated check?
Orphan detection via LEFT JOIN ... WHERE parent IS NULL for every FK pair - catches missing FK and app bugs early.
How do I lint SQL in migrations?
Use sqlfluff, squawk, or custom scripts parsing CREATE TABLE for _id without REFERENCES.
Should production have scheduled integrity jobs?
Yes - nightly read-only queries with alerts. Cheaper than finance reconciliation incidents.
How do I prioritize which defects to automate first?
Order by incident frequency and severity: cross-tenant leak, fan-out revenue, orphans, timezone, enum lock.
Can defect rules slow down development?
Waivers with expiry for prototypes. Production paths have no waiver without VP sign-off.
What goes in a post-mortem lint ticket?
Rule description, sample failing SQL, sample passing SQL, owner, link to incident ID, CI job name.
How do I test timezone rules?
CI sets TimeZone to America/New_York and UTC; assert same timestamptz row orders consistently.
Do ORM schemas need the same lint?
Yes - generate migrations from ORM still subject to SQL lint on output files.
How do fan-out tests differ from unit tests?
They use multi-table fixtures with known cardinalities - integration level, not mocked repositories.
When can I skip a defect check?
Never on production revenue and auth paths. Internal admin tools still need tenant isolation checks if multi-tenant.
Related
- Defect Scenarios Basics - incident patterns
- Fan-Out Join Explosion - aggregate grain
- Missing Foreign Keys - FK enforcement
- Enum Migration Pain - enum DDL risk
- Timestamp Without Timezone Bugs - timestamptz standard
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.