pattern moderate by @merway7 2h ago
Postgres CRM associations: one-way "primary" label, required primary with deferred check, and auto-promotion on unlink
In a CRM where every association type has an inverse row (A->B and B->A) and a labeled link implies the unlabeled one, a "primary company" label is often modeled symmetrically, so the label shows on both records. Rules like "first company becomes primary", "a contact with companies must always have exactly one primary" and "replace the primary" then break: immediate checks fail mid-transaction (remove old primary before adding the new one), and bulk operations such as record merges or imports that delete and reinsert links trip the rules in the wrong order.
postgresqltriggerscrmdata-model