Database Integrity: Excellent
Despite zero foreign key constraints, application-level integrity is working well. Only 9 orphaned rows in a single low-risk relationship.
Orphan Found
customer_opp_summaries.customer_location_id → customer_locations
9 active rows reference customer_location_id values (39, 40, 41)
that do not exist in customer_locations. Max ID is 32 — locations were likely hard-deleted.
| Opp Summary ID | Customer | Location ID |
|---|---|---|
| 60 | BWC Weststeyn Dairy, LP | 39 |
| 183 | Faria Brothers Dairy LLC | 40 |
| 310 | Maid Rite Feeds | 41 |
| 311 | Maid Rite Feeds | 41 |
| 312 | Maid Rite Feeds | 41 |
| 313 | Maid Rite Feeds | 41 |
| 314 | Maid Rite Feeds | 41 |
| 315 | Maid Rite Feeds | 41 |
| 316 | Maid Rite Feeds | 41 |
Risk Level
Low — The parent customer_relationship_id is
intact for all 9 rows. Only the specific location link is broken.
Recommendation
Set customer_location_id to NULL on these 9 rows before adding FK constraints.
All Relationship Checks
| Child Table | Column | Parent Table | Active Orphans | Deleted Orphans | NULL Refs | Total Rows |
|---|---|---|---|---|---|---|
| contract_numbers | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 253 |
| contract_summaries | contract_number_id | contract_numbers | 0 | 0 | 0 | 567 |
| contract_summaries | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 567 |
| contract_tracker | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 1,077 |
| contract_tracker | product_id | products | 0 | 0 | 0 | 1,077 |
| customer_accounts | customer_id | customers | 0 | — | 0 | 170 |
| customer_billing_profiles | customer_id | customers | 0 | — | 0 | 170 |
| customer_dispositions | customer_location_id | customer_locations | 0 | 0 | 39 | 41 |
| customer_dispositions | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 41 |
| customer_jobs | customer_id | customers | 0 | — | 0 | 0 |
| customer_locations | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 32 |
| customer_opp_summaries | customer_disposition_id | customer_dispositions | 0 | 0 | 500 | 540 |
| customer_opp_summaries | customer_location_id | customer_locations | 9 | 0 | 473 | 540 |
| customer_opp_summaries | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 540 |
| customer_opp_summaries | product_id | products | 0 | 0 | 14 | 540 |
| customer_opp_summaries | state_id | states | 0 | 0 | 3 | 540 |
| customer_relationships | rep_id | reps | 0 | 0 | 0 | 302 |
| customers | rep_id | reps | 0 | 0 | 3 | 439 |
| customer_shipping_profiles | customer_id | customers | 0 | — | 0 | 170 |
| form_price_quotes | customer_location_id | customer_locations | 0 | 0 | 0 | 0 |
| form_price_quotes | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 0 |
| form_price_quotes | product_id | products | 0 | 0 | 0 | 0 |
| form_site_visit_action_items | site_visit_id | form_site_visits | 0 | 0 | 0 | 5 |
| form_site_visits | customer_location_id | customer_locations | 0 | 0 | 3 | 3 |
| form_site_visits | customer_poc_id | customer_contacts | 0 | 0 | 0 | 3 |
| form_site_visits | customer_relationship_id | customer_relationships | 0 | 0 | 0 | 3 |
| product_pricing_tables | product_id | products | 0 | 0 | 0 | 161 |
| product_pricing_tables | packaging_id | structural_fields | 0 | 0 | 0 | 161 |
| product_pricing_tables | warehouse_id | structural_fields | 0 | 0 | 0 | 161 |
| sheets | application_id | applications | 0 | — | 0 | 65 |
| sheets | product_id | products | 0 | — | 0 | 65 |
| shipment_payment_schedule | product_id | products | 0 | 0 | 0 | 239 |
Notable Observations
High NULL Rates
customer_opp_summaries has 93% NULL for customer_disposition_id and
88% NULL for customer_location_id. Fields are optional — filled progressively.
Polymorphic References
product_pricing_tables references structural_fields for both
packaging and warehouse IDs. Differentiated by field_type. All valid.
Empty Tables
customer_jobs, form_price_quotes, and password_resets have
0 rows. May be legacy or planned features.
JSONB Columns — Clean
JSONB fields in shipment_payment_schedule and contract_summaries store
display values only — no embedded foreign keys. No orphan risk.
The database is ready for FK constraints. With only 9 orphaned rows to clean up, you can safely add foreign key constraints across the entire schema. This is the foundation for Phase 1 of the architecture roadmap.