crm — Phase 7

8 tables, 108 cols. (+1 search_vector on crm.customer added 2026-06-11 — Search FTS touch.) Owns the tenant's record of buyers — customers, contacts, addresses, groups, notes, consent, dedup audit, and tax exemption certificates. charge_account lives in Billing, not here. Consumer-app identity lives in the future consumer schema; this schema carries only the nullable link.

Design notes

• Email is NOT hard-unique (retail reality: shared household email, null email, multiple walk-in accounts). Dedup is service-layer via customer_merge. email_normalized is indexed for lookup but not unique.
• Merge pattern: merge moves activity (orders, notes, consent history) to the surviving target_customer_id; the source record is soft-deleted (not physically removed) so customer_merge FKs and historical references stay valid.
• Consumer link: customer.consumer_id is a nullable forward-ref to consumer.consumer (future platform-level Vrida consumer account). NULL = anonymous or not-yet-linked. FK constraint added when the consumer schema is built.
• customer_group_id migration ordering: customer.customer_group_id is a self-referencing intra-schema FK (references crm.customer_group). The migration must create both tables first, then add the FK with ALTER TABLE. The documentation order (customer listed first) does not dictate migration order.

Cross-Phase Foreign Keys (crm)

Column	Target	Status
*.tenant_id	platform.tenant	Phase 1 exists — enforced.
address.state_id	shared.us_state	Phase 2 exists — enforced (nullable).
address.country_id	shared.country	Phase 2 exists — enforced.
customer_note.created_by	identity.identity_user	Phase 3 exists — enforced (nullable).
customer_merge.merged_by	identity.identity_user	Phase 3 exists — enforced (nullable).
customer_consent.captured_by	identity.identity_user	Phase 3 exists — enforced (nullable).
customer.customer_group_id	crm.customer_group	Intra-schema — enforced after both tables created (migration ordering note above).
contact.customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
address.customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer_note.customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer_merge.source_customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer_merge.target_customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer_consent.customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer.consumer_id	consumer.consumer	FORWARD-REF — consumer schema not yet built (Consumer Layer is a post-merchant-core phase). Column is plain UUID; FK added when consumer schema locks.
customer_tax_certificate.customer_id	crm.customer	Intra-schema — enforced at Phase 7 migration.
customer_tax_certificate.issuing_state_id	shared.us_state	Phase 2 exists — enforced (nullable).
customer_tax_certificate.verified_by_user_id	identity.identity_user	Phase 3 exists — enforced (nullable).

---

crm.customer

Tenant's record of a buyer — individual or business. The core entity for all CRM, POS, and order activity. Email is not unique by design (see design notes). consumer_id links to the future Vrida consumer platform account.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_number	text	nullable	—	Tenant-assigned account number for lookup; not required
customer_type	text	NOT NULL	—	CHECK (customer_type IN ('individual','business'))
display_name	text	NOT NULL	—	Primary display name — derived at service layer from first/last or company
first_name	text	nullable	—	Individual's first name
last_name	text	nullable	—	Individual's last name
company_name	text	nullable	—	Business name; required when customer_type = 'business'
email	text	nullable	—	As entered; NOT unique (see design notes)
email_normalized	text	nullable	—	Lowercased / trimmed; used for dedup lookup; NOT unique
phone	text	nullable	—
customer_group_id	UUID	nullable	—	FK → crm.customer_group (intra-schema deferred FK — see migration ordering note)
consumer_id	UUID	nullable	—	Forward-ref consumer.consumer — Vrida consumer account link; NULL for anonymous / not-yet-linked
status	text	NOT NULL	'active'	CHECK (status IN ('active','inactive'))
tax_exempt	boolean	NOT NULL	false	Maintained cache — true iff the customer has ≥1 active, non-expired crm.customer_tax_certificate. Source of truth = customer_tax_certificate rows; this is a fast-read cache for checkout (cf. marketing_opt_in / customer_consent pattern).
tax_exempt_id	text	nullable	—	DEPRECATED (2026-06-10) — cert number now lives on customer_tax_certificate.certificate_number. Retained for backward-compat; do not write new values. Cert evidence is the customer_tax_certificate table.
credit_terms	text	nullable	—	CHECK (credit_terms IS NULL OR credit_terms IN ('prepaid','net15','net30','net60'))
credit_limit_cents	bigint	nullable	—	Credit limit in cents; NULL = no limit set
source	text	nullable	—	CHECK (source IS NULL OR source IN ('walk_in','pos','import','online','app','manual')) — 'app' covers consumer-app self-service CRM record creation
marketing_opt_in	boolean	NOT NULL	false	Current-state cache; customer_consent is the authoritative audit trail
last_activity_at	timestamptz	nullable	—	Maintained by service events (last sale, note, etc.)
notes	text	nullable	—	Freeform internal notes
attributes	JSONB	nullable	'{}'	Extensible type-specific or tenant-custom fields
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(display_name,'') || ' ' || coalesce(customer_number,'') || ' ' || coalesce(company_name,''))) STORED. (FTS touch 2026-06-11 — Search module.) Maintained by Postgres; never written directly.
(type CHECK)	—	—	—	CHECK ((customer_type = 'individual' AND (first_name IS NOT NULL OR last_name IS NOT NULL)) OR (customer_type = 'business' AND company_name IS NOT NULL))

Indexes:

• PK on id
• on (tenant_id)
• on (customer_group_id)
• on (consumer_id)
• on (tenant_id, email_normalized) — dedup lookup
• on (tenant_id, phone) — POS phone lookup
• on (tenant_id, display_name) — search
• UNIQUE on (tenant_id, customer_number) WHERE customer_number IS NOT NULL AND deleted_at IS NULL — account number unique when present
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (display_name gin_trgm_ops) — fuzzy / partial / prefix name search (pg_trgm)

---

crm.contact

Additional contacts for a customer — e.g. accounts-payable department, on-site manager, secondary buyer. Each customer can have multiple contacts; at most one is is_primary.

Tenant-scoped. Subordinate to customer — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
display_name	text	NOT NULL	—	Allows role names: "AP Department", "Front Office"
first_name	text	nullable	—
last_name	text	nullable	—
email	text	nullable	—
email_normalized	text	nullable	—	Lowercased / trimmed
phone	text	nullable	—
role_title	text	nullable	—	Job title or role description
is_primary	boolean	NOT NULL	false	At most one primary contact per customer
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• UNIQUE on (customer_id) WHERE is_primary = true AND deleted_at IS NULL — at most one primary contact per customer

---

crm.address

Billing and / or shipping addresses for a customer. Multiple addresses per customer; at most one default per address_type.

Tenant-scoped. Subordinate to customer — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
address_type	text	NOT NULL	—	CHECK (address_type IN ('billing','shipping','both'))
line1	text	NOT NULL	—
line2	text	nullable	—
city	text	NOT NULL	—
state_id	UUID	nullable	—	FK → shared.us_state — nullable for non-US addresses
postal_code	text	NOT NULL	—
country_id	UUID	NOT NULL	—	FK → shared.country
is_default	boolean	NOT NULL	false	At most one default per customer per address_type
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• on (customer_id, address_type) — fetch all billing/shipping addresses for a customer by type (POS, PricingService)
• UNIQUE on (customer_id, address_type) WHERE is_default = true AND deleted_at IS NULL — at most one default address per type per customer

---

crm.customer_group

Tenant-defined customer segments (e.g. "Landscapers", "Wholesale Accounts", "VIP Members"). Used for both CRM segmentation (customer.customer_group_id) and pricing (pricing.price_list_assignment.customer_group_id).

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	Short code, e.g. 'wholesale', 'landscaper'
name	text	NOT NULL	—	Display name
description	text	nullable	—
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL

---

crm.customer_note

Freeform staff notes on a customer — call logs, visit notes, general observations. Append-only: never edited or deleted once written.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
note_type	text	nullable	—	CHECK (note_type IS NULL OR note_type IN ('call','email','visit','general'))
body	text	NOT NULL	—	Note content
created_by	UUID	nullable	—	FK → identity.identity_user — NULL for system-generated notes
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id, created_at) — customer note timeline

---

crm.customer_merge

Audit trail for customer dedup / merge operations. Records which source record was merged into which target. Source customer is soft-deleted after merge; this table preserves the full merge history. Append-only.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
source_customer_id	UUID	NOT NULL	—	FK → crm.customer — the record merged away (will be soft-deleted)
target_customer_id	UUID	NOT NULL	—	FK → crm.customer — the surviving record
merged_by	UUID	nullable	—	FK → identity.identity_user — NULL for automated merges
reason	text	nullable	—	Human-readable reason for the merge
metadata	JSONB	nullable	—	Snapshot of what was moved (activity counts, linked IDs, etc.)
created_at	timestamptz	NOT NULL	now()
(self-merge CHECK)	—	—	—	CHECK (source_customer_id != target_customer_id)

Indexes:

• PK on id
• on (tenant_id)
• on (source_customer_id)
• on (target_customer_id)

---

crm.customer_consent

Immutable consent event log — records every opt-in and opt-out event per customer per consent type. Current state is cached on customer.marketing_opt_in for fast reads; this table is the authoritative audit trail for compliance.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
consent_type	text	NOT NULL	—	CHECK (consent_type IN ('marketing_email','sms','phone','privacy_terms','loyalty'))
channel	text	nullable	—	CHECK (channel IS NULL OR channel IN ('email','sms','phone','in_app','pos'))
status	text	NOT NULL	—	CHECK (status IN ('opted_in','opted_out'))
source	text	nullable	—	CHECK (source IS NULL OR source IN ('pos','import','online','manual','unsubscribe_link','app'))
captured_at	timestamptz	NOT NULL	—	When the consent event occurred
captured_by	UUID	nullable	—	FK → identity.identity_user — NULL for self-service / system events
metadata	JSONB	nullable	—	Additional context (IP, form version, etc.)
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id, consent_type, captured_at) — consent history per type; latest row = current state

---

crm.customer_tax_certificate — 17 cols

Customer-provided tax exemption certificates (resale certs, agricultural exemptions, non-profit exemptions, etc.). Referenced by Admin tax-exemption rules and surfaced in POS / Orders at checkout. Managed by tenant staff. Source of truth for customer tax-exempt status — crm.customer.tax_exempt is a maintained cache of this table (true iff ≥1 active, non-expired row; cf. marketing_opt_in / customer_consent pattern). crm.customer.tax_exempt_id is deprecated; cert number now lives on certificate_number.

Tenant-scoped. Soft-delete — one certificate per customer per type (active only).

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
customer_id	UUID	NOT NULL	—	FK → crm.customer
certificate_number	text	nullable	—	State-issued certificate number; nullable for certificates without a formal number
certificate_type	text	NOT NULL	—	CHECK IN ('resale','agricultural','non_profit','government','other')
issuing_state_id	UUID	nullable	—	FK → shared.us_state; NULL for non-US or non-state-specific certs. CHECK: issuing_state_id IS NULL OR issuing_country_code = 'US'
issuing_country_code	text	NOT NULL	'US'	ISO 3166-1 alpha-2 country code of the issuing jurisdiction. Default 'US'. Generic-first — supports non-US exemption certs.
valid_from	date	NOT NULL	—	Effective date
expires_at	date	nullable	—	Expiry date; NULL = no expiry (some states issue perpetual certs)
document_ref	text	nullable	—	FORWARD-REF — R2 object key for the scanned PDF; Files module owns storage
status	text	NOT NULL	'active'	CHECK IN ('active','expired','revoked','pending_review')
verified_by_user_id	UUID	nullable	—	FK → identity.identity_user — staff member who verified the cert
verified_at	timestamptz	nullable	—	When the cert was verified; NULL = unverified
note	text	nullable	—

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• on (certificate_type, status)
• on (expires_at) WHERE deleted_at IS NULL — expiry sweep
• UNIQUE on (customer_id, certificate_type, issuing_country_code) WHERE issuing_state_id IS NULL AND deleted_at IS NULL AND status = 'active' — one active non-state-specific cert per type per country per customer (NULL-safe: prevents duplicate non-US certs of same type)
• UNIQUE on (customer_id, certificate_type, issuing_country_code, issuing_state_id) WHERE issuing_state_id IS NOT NULL AND deleted_at IS NULL AND status = 'active' — one active cert per type per state per customer

---

crm — Design Notes

Column counts

Table	Cols
customer	26
contact	14
address	14
customer_group	10
customer_note	7
customer_merge	8
customer_consent	11
customer_tax_certificate	17
Total	107

Deferred notes (low-priority GAPs with written triggers)

Deferred item	Trigger for resolution
customer.attributes / customer_merge.metadata — add example JSONB shapes	When CRM service layer is built
customer_consent current-state query optimization	Evaluate at scale; acceptable for v1.0 row counts

Migration ordering

1. customer_group — must exist before customer.customer_group_id FK is added
2. customer — create table (with customer_group_id as plain UUID); add FK to customer_group after
3. contact → address → customer_note → customer_merge → customer_consent

Patterns recap

• Email not unique — email_normalized indexed for lookup only; dedup via customer_merge.
• Three append-only tables (customer_note, customer_merge, customer_consent) — no updated_at, no deleted_at.
• Soft-delete tables (customer, contact, address, customer_group) — all uniques are partial WHERE deleted_at IS NULL.
• Consumer link — customer.consumer_id is a forward-ref (plain UUID now; FK added when consumer schema locks). NULL = anonymous or not-yet-linked.
• JSONB — customer.attributes for tenant-custom fields; customer_merge.metadata for merge snapshot. No formal example shape required (freeform by design).

---