Contacts

Org-private pool of people, with optional email/phone methods, linkable to entities in the org (customers, vendors, locations).

ER diagram

org.contacts

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
display_name	VARCHAR(255)	NOT NULL. UI-facing name on the contact card.
first_name	VARCHAR(100)
last_name	VARCHAR(100)
title	VARCHAR(150)
notes	TEXT
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

org.contact_methods

Emails and phones attached to a contact.

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
contact_id	UUID FK	→ org.contacts(id) ON DELETE CASCADE.
method_type	contact_method_kind	NOT NULL. ENUM (email, phone).
value	VARCHAR(320)	NOT NULL. Email address or phone number.
label	VARCHAR(50)	e.g. work, mobile, billing.
is_primary	BOOL	NOT NULL. Default false.
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

Constraints:

• UNIQUE (contact_id, method_type, value).
• Partial UNIQUE (contact_id, method_type) WHERE is_primary = true — at most one primary email and one primary phone per contact.

org.entity_contacts

Polymorphic link between a contact and an entity. entity_id has no DB-level FK; the application is responsible for cleanup when the parent row is deleted.

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
contact_id	UUID FK	→ org.contacts(id) ON DELETE CASCADE.
entity_type	entity_contact_kind	NOT NULL. ENUM (customer, vendor, location). Values added later via ALTER TYPE ADD VALUE.
entity_id	UUID	NOT NULL. Id of the row in the corresponding entity table. No DB-level FK.
role	VARCHAR(50)	e.g. billing, shipping, sales, ops. Nullable.
is_primary	BOOL	NOT NULL. Default false.
notes	TEXT	Per-link notes — distinct from the contact's own notes.
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

Constraints:

• UNIQUE (contact_id, entity_type, entity_id, role) — same contact can have multiple roles for the same entity, but not the same role twice.
• Partial UNIQUE (entity_type, entity_id) WHERE is_primary = true — at most one primary contact per entity.