Customers

Sales-side parties. Carries the rich model: classification (type + segment), default broker, hierarchy (parent customer), country, QBO sync. Auxiliary tables in this cluster (customer_types, customer_segments, brokers) only exist for customers — they're not shared with vendors, which carry a deliberately flatter shape.

ER diagram

org.customers

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
name	VARCHAR
customer_type_id	UUID FK	→ org.customer_types(id) ON DELETE RESTRICT. NOT NULL.
customer_segment_id	UUID FK	→ org.customer_segments(id) ON DELETE SET NULL.
broker_id	UUID FK	→ org.brokers(id) ON DELETE SET NULL. Default broker — overridable per SO.
broker_fee_percent	NUMERIC	CHECK 0..100. Default broker fee for this customer.
address	TEXT
city	VARCHAR(150)
state	VARCHAR(100)
country	CHAR(2)	ISO 3166-1 alpha-2.
zip_code	VARCHAR(20)
parent_customer_id	UUID FK	→ org.customers(id) ON DELETE SET NULL. Hierarchy — see Hierarchy rules.
qbo_id	VARCHAR(100)	UNIQUE per (org_id, qbo_id). QBO sync identifier.
notes	TEXT	Free-form notes (nullable).
is_active	BOOL	Default true. Soft-delete.
created_at	TIMESTAMPTZ	Default NOW().
updated_at	TIMESTAMPTZ	Default NOW(). Auto-updated by trigger.

org.customer_types

Required taxonomic classification (e.g., Purchaser, Location, Customer). NOT NULL FK from customers.customer_type_id.

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
name	VARCHAR(100)	UNIQUE per (org_id, name).
sort_order	SMALLINT	CHECK >= 0. UNIQUE per (org_id, sort_order) — DEFERRABLE INITIALLY IMMEDIATE for reorders.
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

org.customer_segments

Optional softer classification (e.g., Club, Distributor, Grocery). Nullable FK from customers.customer_segment_id.

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
name	VARCHAR(100)	UNIQUE per (org_id, name).
sort_order	SMALLINT	CHECK >= 0. UNIQUE per (org_id, sort_order) — DEFERRABLE INITIALLY IMMEDIATE for reorders.
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

org.brokers

Third-party intermediaries who get a percentage commission on sales. Nullable FK from customers.broker_id. Also referenced from sales_orders.broker_id (per-SO override).

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
name	VARCHAR(100)	UNIQUE per (org_id, name).
sort_order	SMALLINT	CHECK >= 0. UNIQUE per (org_id, sort_order) — DEFERRABLE INITIALLY IMMEDIATE for reorders.
is_active	BOOL	NOT NULL. Default true.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Hierarchy rules

Application-enforced — not in the schema:

• Purchaser customers cannot have a parent_customer_id.
• Location and Customer types must have a parent_customer_id pointing at a Purchaser.

Cross-references

• Used by sales orders — every SO has a customer_id (RESTRICT) and a broker_id + broker_fee_percent snapshot (overridable from the customer default).
• Linked to contacts via entity_contacts.entity_type = 'customer'.