crm — Phase 5 (module #5, first product module)

13 tables, 193 columns (was 189 at 2026-07-06 lock; +2 from the 2026-07-08 Remediation Phase 3 pass — see customer.credit_limit_cents/customer.credit_terms below; +2 more from the same-day Remediation Phase 4 pass — see customer.payment_terms_id/customer.pii_vault_ref below; column count unchanged 2026-07-18 — customer gained UNIQUE(id, tenant_id), constraint-only, the prerequisite for billing.gift_card/store_credit_account's composite FKs, Phase 3 stored-value build, PROJECT_DECISIONS #71) — schema-locked, migrated, and seeded 2026-07-06. crm is the first of the 13 nursery-vertical product modules to actually build under the v2 pipeline (modules #1–4 — platform, identity, shared, multi_loc — were foundation/service-layer modules). Covers customers (individual/business), their contacts and addresses, staff/agent notes and follow-up tasks, duplicate-customer merge workflow, marketing/consent tracking, tax-exemption certificates, and customer segmentation (both agent-computed segments and human free-form tags). Tenant-scoped throughout (one mixed-scope exception: customer_segment_definition). Depends on platform (tenant ownership), shared (global address/country/region reference data, plus the new payment_terms_catalog), multi_loc (site attribution on interaction records), and identity (actor-attribution FKs on every table, from day 1 — crm is the first module built entirely after the autonomy-first backfill, so it never had a "pre-autonomy" shape to retrofit).

PROJECT_DECISIONS entry: #23 (build), #28 (2026-07-07 reopen — 1 live-bug fix + 2 erosion fixes, see below), #39 (2026-07-08 Remediation Phase 3, Item 8 — customer credit columns, see below), #40 (2026-07-08 Remediation Phase 4, Items 17b/18 — customer.payment_terms_id/customer.pii_vault_ref, see below). Design rationale entries DR-33 through DR-49 (cited inline below).

Global rules for this schema:

• Tenant-scoped — every table carries tenant_id NOT NULL FK → platform.tenant, with RLS enabled and a permissive tenant-isolation policy (USING/WITH CHECK on current_setting('app.current_tenant_id')::uuid), except customer_segment_definition, which is mixed-scope (see its own section).
• Soft delete on master-data and mutable-state tables (deleted_at timestamptz, nullable); append-only, no soft delete on event/audit tables (customer_note, customer_consent, customer_merge — no updated_at, no deleted_at).
• updated_at is trigger-maintained via platform.set_updated_at() on every table that has an updated_at column (verified live: set_updated_at fires on address, contact, customer, customer_group, customer_merge_candidate, customer_segment_definition, customer_segment_membership, customer_task, customer_tax_certificate).
• Agent-as-actor attribution from day 1 — created_by_actor_id / updated_by_actor_id / reviewed_by_actor_id / role-specific actor FKs (e.g. proposed_by_actor_id, verified_by_actor_id, computed_by_actor_id, merged_by_actor_id, captured_by_actor_id, assigned_to_actor_id) all target identity.actor (the polymorphic root), never identity.identity_user directly — same pattern multi_loc.site established in the 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19), applied here natively rather than retrofitted.
• Autonomy treatment varies deliberately by table — full (automation_source + review_status + decision_provenance + reviewer FKs), light (created_by_actor_id only), or none (human/Vrida-curated catalogs). See each table's section and Design Notes for the specific reasoning per table (DR-38, DR-41, DR-42, DR-44, DR-46).
• Global address shape (DR-37) — address and customer_tax_certificate's issuing-jurisdiction fields use flat address lines + natural-key FKs into shared.country (iso_alpha2, char(2)) and shared.administrative_region (iso_3166_2, text), mirroring multi_loc.site's precedent exactly, including the region/country consistency CHECK pattern.
• Three DEFERRED forward-refs — customer.consumer_id, customer.search_vector, customer_tax_certificate.document_ref are all plain columns with no FK / not yet a generated column, because the modules they'd reference (consumer, search, files) do not exist in this v2 database. Verified live via psql \dn — as of this build the only schemas present are identity, multi_loc, platform, shared, crm. Also verified via filesystem that no consumer.md/rewards.md/offers.md/search.md/files.md exist under docs/database/schema_docs/. Any doc describing these as "locked" or "FK — READY" (e.g. older MODULE_INDEX.md/CROSS_MODULE_CONTRACTS.md entries dated 2026-06-11/12) is stale v1/pre-pivot documentation — see DR-34.

Cross-Phase Foreign Keys (crm)

Column	Target	Notes
*.tenant_id (all 13 tables)	platform.tenant	Intra-tenancy, enforced.
customer.customer_group_id	crm.customer_group	Nullable. Intra-schema. customer_group defined before customer in migration order so this FK resolves directly.
customer.consumer_id	(none — deferred)	Plain nullable UUID, no FK. DR-34: consumer schema does not exist in v2.
contact.customer_id, address.customer_id, customer_note.customer_id, customer_task.customer_id, customer_merge_candidate.customer_a_id/customer_b_id, customer_merge.source_customer_id/target_customer_id, customer_consent.customer_id, customer_tax_certificate.customer_id, customer_segment_membership.customer_id, customer_tag_assignment.customer_id	crm.customer	Intra-schema, enforced.
customer_merge.candidate_id	crm.customer_merge_candidate	Nullable — a merge need not originate from a logged candidate.
customer_segment_membership.segment_definition_id	crm.customer_segment_definition	Real FK, not bare text — per resolved decision to add the catalog table.
address.region_code, customer_tax_certificate.issuing_region_code	shared.administrative_region.iso_3166_2	Nullable, ON DELETE SET NULL. Cross-schema, enforced.
address.country_code	shared.country.iso_alpha2	NOT NULL (was nullable — DR-48, 2026-07-07), ON DELETE RESTRICT (was SET NULL — a NOT NULL column can't absorb a deleted-country cascade). Cross-schema, enforced.
customer_tax_certificate.issuing_country_code	shared.country.iso_alpha2	NOT NULL, no default (DR-49, 2026-07-07 — the silent DEFAULT 'US' was dropped; callers must now state the country explicitly). No ON DELETE action set — verified live.
customer_note.site_id, customer_task.site_id	multi_loc.site	Nullable. Cross-schema, enforced. Deliberate narrow exception to "master data doesn't carry site_id" (DR-39) — these are event/interaction records.
*_actor_id FKs (all roles: created_by, updated_by, reviewed_by, proposed_by, verified_by, computed_by, merged_by, captured_by, assigned_to)	identity.actor	Nullable throughout. Cross-schema, enforced.
customer_tax_certificate.document_ref	(none — deferred)	Plain nullable text, no FK. DR-34: files schema does not exist in v2.
customer.search_vector	(not built at all this pass)	DR-34: search schema does not exist in v2; no generated tsvector column exists anywhere in v2 yet either (no Drizzle precedent to follow).
customer.payment_terms_id	shared.payment_terms_catalog	Added 2026-07-08 (Remediation Phase 4, Item 17b). Nullable, cross-schema, enforced. Additive-interim alongside the unchanged credit_terms CHECK-enum — NOT a clean vocabulary subset (see customer table notes).
customer.pii_vault_ref	(none — vault service not yet built)	Added 2026-07-08 (Remediation Phase 4, Item 18). Plain nullable text, no FK — mirrors platform.tenant_profile.ein_ref.

---

crm.customer_group

Tenant-defined customer segments used by both CRM and Pricing (pricing.price_list_assignment.customer_group_id, forward-looking — Pricing not yet built). Defined before customer in the same migration file specifically so customer.customer_group_id can FK it directly. A simple, human-curated catalog — deliberately carries no autonomy columns at all (no created_by_actor_id, no automation_source, nothing), on the same reasoning as customer_segment_definition's no-autonomy design (catalog definitions are human/Vrida-curated, never agent-written).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_group_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
name	text	NOT NULL	—
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()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

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

---

crm.customer

The core entity of the module. Individual/business customers are modeled as one table, not two, distinguished by the customer_type discriminator — preserving v1's rationale rather than splitting into separate tables. Full autonomy treatment (actor attribution, automation_source, is_verified, review seam, decision_provenance) — the richest autonomy shape of any table in this module, since customer is the primary target of CRM's AI-enrichment and dedup capabilities.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_number	text	nullable	—	UNIQUE per tenant when set (partial-unique, see indexes)
customer_type	text	NOT NULL	—	CHECK IN (individual,business). Discriminator — one table, not two.
display_name	text	NOT NULL	—
first_name	text	nullable	—	Required (with last_name) when customer_type = 'individual' — see table CHECK below
last_name	text	nullable	—	See table CHECK below
company_name	text	nullable	—	Required when customer_type = 'business' — see table CHECK below
email	text	nullable	—	Deliberately NOT unique (DR-33)
email_normalized	text	nullable	—	Lowercased/trimmed. Deliberately NOT unique (DR-33) — dedup is the explicit merge workflow, never a DB constraint
phone	text	nullable	—
customer_group_id	UUID	nullable	—	FK → crm.customer_group
consumer_id	UUID	nullable	—	DEFERRED forward-ref, plain UUID, NO FK (DR-34) — the consumer schema doesn't exist in this v2 database (verified live)
status	text	NOT NULL	'active'	CHECK IN (active,inactive)
tax_exempt	boolean	NOT NULL	false
source	text	nullable	—	CHECK IN (walk_in,pos,import,online,app,manual) OR NULL
marketing_opt_in	boolean	NOT NULL	false	Current-state cache; event history lives in customer_consent
last_activity_at	timestamptz	nullable	—
notes	text	nullable	—	Freeform. (Structured, timestamped observations belong in customer_note instead.)
attributes	jsonb	nullable	'{}'	Open extensibility bag
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
is_verified	boolean	NOT NULL	true	Defaults true, not shared.plant's false (DR-36) — staff/POS/import-entered data is presumptively trustworthy; per-field trust for AI-written fields is tracked in decision_provenance instead
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—	Field-level provenance — supports mixed per-field trust within one row (DR-36)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete
credit_limit_cents	bigint	nullable	—	Added 2026-07-08 (Remediation Phase 3, Item 8). NULL = no credit extended (fail-closed). CHECK chk_customer_credit_limit_nonnegative
credit_terms	text	nullable	—	Added 2026-07-08 (Remediation Phase 3, Item 8). CHECK chk_customer_credit_terms IN (due_on_receipt,net_15,net_30,net_45,net_60,net_90)
payment_terms_id	UUID	nullable	—	Added 2026-07-08 (Remediation Phase 4, Item 17b). FK → shared.payment_terms_catalog. Additive-interim alongside the unchanged credit_terms CHECK-enum — NOT yet kept in sync (see disclosure below)
pii_vault_ref	text	nullable	—	Added 2026-07-08 (Remediation Phase 4, Item 18). Mirrors platform.tenant_profile.ein_ref's vault-reference pattern, enabling future crypto-shred GDPR/CCPA erasure

Removed vs. v1, then partially reinstated (DR-35, corrected 2026-07-08 by Remediation Phase 3 Item 8): credit_terms/credit_limit_cents were dropped entirely at 2026-07-06 lock, on the stated rationale that credit-worthiness is Billing's domain. That rationale was itself found to be a bug, not a real decision: billing.ar_account's own column comment already claimed it "reads from crm.customer, never duplicated" — but crm.customer never actually had these columns to read from. Remediation Phase 3 (PROJECT_DECISIONS #39) restores both columns here, nullable, with the corrected ownership: crm.customer OWNS credit-worthiness (a relationship judgment about the customer, squarely crm's domain) and billing.ar_account READS credit_limit_cents/credit_terms from here, never duplicating them. tax_exempt_id remains dropped (already deprecated in v1, unaffected by this correction).

Remediation Phase 4, Item 17b (2026-07-08) — payment_terms_id, additive-interim, NOT a clean vocabulary subset: shared.payment_terms_catalog (10 seeded rows, e.g. cod, prepaid, net_7, net_15, net_30, net_45, net_60, net_90, 2_10_net_30, and one more — each row carries net_days/discount_percent/discount_days, letting 2_10_net_30 genuinely represent "2/10 net 30", which a bare CHECK-enum cannot) is added as a global (no tenant_id) reference table, with customer.payment_terms_id as a new, independently-nullable FK alongside the existing credit_terms CHECK-enum — the enum itself is completely unchanged. Disclosed explicitly: the two are NOT a clean subset of each other. The catalog's 4 codes cod/prepaid/net_7/2_10_net_30 have no credit_terms equivalent at all, so a future cutover to the catalog as sole source of truth requires a real mapping decision for those 4 codes, not a trivial 1:1 rename. This interim gap (the new FK column is not yet constrained to stay in sync with the legacy CHECK-enum column) is disclosed in the Drizzle TypeScript source directly, not just a migration-file comment.

Remediation Phase 4, Item 18 (2026-07-08) — pii_vault_ref, shares (does not duplicate) an existing OPEN_ITEMS dependency: mirrors platform.tenant_profile.ein_ref's vault-reference pattern — a nullable pointer to an out-of-band vault service, enabling future crypto-shred erasure (GDPR/CCPA "right to be forgotten") without storing the sensitive value itself in this table. This column shares the same vault-service-not-yet-built dependency already logged in OPEN_ITEMS for ein_ref — no new vault-service OPEN_ITEMS row was created for this column; the Drizzle comment explicitly cross-references the existing one instead of duplicating it.

Deferred, not built this pass: search_vector (Search-module FTS touch) — the search schema doesn't exist in v2 (verified live), and no v2-built table anywhere uses a generated tsvector column yet, so there's no Drizzle precedent to follow either (DR-34).

CHECK constraints (verified live):

Name	Condition
chk_customer_customer_type	customer_type IN ('individual','business')
chk_customer_status	status IN ('active','inactive')
chk_customer_source	source IS NULL OR source IN ('walk_in','pos','import','online','app','manual')
chk_customer_automation_source	automation_source IN ('human','agent','system','seed')
chk_customer_review_status	review_status IN ('not_required','pending','approved','rejected')
chk_customer_type_identity_fields	(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)
chk_customer_credit_limit_nonnegative	credit_limit_cents IS NULL OR credit_limit_cents >= 0 — added 2026-07-08 (Remediation Phase 3, Item 8)
chk_customer_credit_terms	credit_terms IS NULL OR credit_terms IN ('due_on_receipt','net_15','net_30','net_45','net_60','net_90') — added 2026-07-08 (Remediation Phase 3, Item 8)

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on (tenant_id, display_name) — name search
• Plain index on (tenant_id, email_normalized) — email lookup
• Plain index on (tenant_id, phone) — phone lookup
• Plain index on customer_group_id
• Partial index on consumer_id WHERE NOT NULL
• Partial index on review_status WHERE = 'pending' — review-queue query
• Partial UNIQUE on (tenant_id, customer_number) WHERE customer_number IS NOT NULL AND deleted_at IS NULL
• Partial index on payment_terms_id WHERE NOT NULL — added 2026-07-08 (Remediation Phase 4, Item 17b)

---

crm.contact

Additional named contacts per customer — e.g. a purchasing agent or site manager at a business-type customer. A subordinate table with a light autonomy touch only (created_by_actor_id, nothing else) — no distinct AI capability targets it this build pass.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy contact_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
display_name	text	NOT NULL	—
first_name	text	nullable	—
last_name	text	nullable	—
email	text	nullable	—
email_normalized	text	nullable	—
phone	text	nullable	—
role_title	text	nullable	—	e.g. "Purchasing Manager"
is_primary	boolean	NOT NULL	false	At most one true per customer (partial-unique, see indexes)
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_id
• Partial UNIQUE on customer_id WHERE is_primary = true AND deleted_at IS NULL

---

crm.address

Billing/shipping addresses for a customer. Uses the GLOBAL address shape (DR-37): address_line1/address_line2 + city + region_code (FK → shared.administrative_region.iso_3166_2) + country_code (FK → shared.country.iso_alpha2, char(2)) — mirroring multi_loc.site's exact precedent, including the region/country consistency CHECK (chk_address_region_country_match). region_code is nullable (not every address has a state/province); country_code is NOT NULL (DR-48, added 2026-07-07 — restores v1's NOT NULL country_id guarantee, which this table had originally shipped without; the crm/inventory/pricing erosion audit found an address with neither country_code nor region_code set passed chk_address_region_country_match trivially). Light autonomy touch only (created_by_actor_id).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy address_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
address_type	text	NOT NULL	—	CHECK IN (billing,shipping,both)
address_line1	text	NOT NULL	—
address_line2	text	nullable	—
city	text	nullable	—
region_code	text	nullable	—	FK → shared.administrative_region.iso_3166_2, ON DELETE SET NULL
postal_code	text	nullable	—	Free text — not zip-shaped
country_code	char(2)	NOT NULL (DR-48)	—	FK → shared.country.iso_alpha2, ON DELETE RESTRICT (was SET NULL)
is_default	boolean	NOT NULL	false	At most one true per (customer_id, address_type) — partial-unique, see indexes
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_address_address_type	address_type IN ('billing','shipping','both')
chk_address_region_country_match	region_code IS NULL OR (country_code IS NOT NULL AND left(region_code, 2) = country_code)

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_id
• Plain index on (customer_id, address_type)
• Plain index on country_code
• Partial index on region_code WHERE NOT NULL
• Partial UNIQUE on (customer_id, address_type) WHERE is_default = true AND deleted_at IS NULL

---

crm.customer_note

Append-only staff/agent observation log against a customer — no updated_at, no deleted_at. Has automation_source + decision_provenance but explicitly NO review_status (DR-38) — logging a note is may-act-alone, additive, zero mutation risk; a formal review gate would be bureaucratic overhead. Has an optional site_id (FK → multi_loc.site) — a deliberate, narrow exception to "master data doesn't carry site_id" (DR-39), because this is an event/interaction record, not master data, and multi-site tenants need to know where an interaction happened.

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy customer_note_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
site_id	UUID	nullable	—	FK → multi_loc.site. Deliberate exception to "master data doesn't carry site_id" (DR-39)
note_type	text	nullable	—	CHECK IN (call,email,visit,general) OR NULL
body	text	NOT NULL	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()

No review_status — DR-38: a note is append-only and additive; dismissing/completing isn't applicable since there's nothing to act on. Deliberate exception, not an oversight.

CHECK constraints (verified live):

Name	Condition
chk_customer_note_note_type	note_type IS NULL OR note_type IN ('call','email','visit','general')
chk_customer_note_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on (customer_id, created_at)
• Partial index on site_id WHERE NOT NULL

---

crm.customer_task

Follow-up tasks against a customer — human- or agent-suggested. Has a status lifecycle (open/completed/dismissed) instead of a review_status seam (DR-38, folded per DR-40) — dismissing or completing the task is the review mechanism. Has an optional site_id too, same DR-39 exception as customer_note.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_task_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

No soft delete — task lifecycle is fully captured by status; verified live, no deleted_at column exists.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
site_id	UUID	nullable	—	FK → multi_loc.site. Same DR-39 exception as customer_note.site_id
title	text	NOT NULL	—
description	text	nullable	—
assigned_to_actor_id	UUID	nullable	—	FK → identity.actor
due_at	timestamptz	nullable	—
status	text	NOT NULL	'open'	CHECK IN (open,completed,dismissed). IS the review mechanism (DR-38/DR-40)
completed_at	timestamptz	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No review_status — DR-38/DR-40: the task's own state machine (open/completed/dismissed) is its review mechanism; a parallel review_status column would be redundant.

CHECK constraints (verified live):

Name	Condition
chk_customer_task_status	status IN ('open','completed','dismissed')
chk_customer_task_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_id
• Partial index on site_id WHERE NOT NULL
• Partial index on (assigned_to_actor_id, status) WHERE status = 'open' — "my open tasks" query
• Partial index on due_at WHERE status = 'open' — due-soon sweep

---

crm.customer_merge_candidate

A pending dedup proposal awaiting human review — the review gateway for merges. review_status DEFAULTs 'pending' (not 'not_required' like every other table in this module), since every row here exists because it needs review. Full autonomy treatment. Kept deliberately SEPARATE from customer_merge (DR-41) rather than unifying with a review_status column bolted onto the append-only audit table, because most candidates are REJECTED and never become a customer_merge row at all — genuinely different lifecycle (mutable pending→approved/rejected vs. permanent audit fact) and cardinality.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_merge_candidate_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: none — verified live, no deleted_at column. Lifecycle is fully captured by review_status.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_a_id	UUID	NOT NULL	—	FK → crm.customer. CHECK <> customer_b_id (no self-match)
customer_b_id	UUID	NOT NULL	—	FK → crm.customer. CHECK <> customer_a_id (no self-match)
match_basis	text	nullable	—	CHECK IN (email,phone,name_fuzzy,address,manual) OR NULL
confidence_score	numeric(3,2)	nullable	—
proposed_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected). Defaults pending, not not_required — every row here needs review by construction
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_customer_merge_candidate_self_match	customer_a_id <> customer_b_id
chk_customer_merge_candidate_match_basis	match_basis IS NULL OR match_basis IN ('email','phone','name_fuzzy','address','manual')
chk_customer_merge_candidate_review_status	review_status IN ('pending','approved','rejected') — note: no not_required value here, unlike other review-seam tables, since a candidate always needs review
chk_customer_merge_candidate_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_a_id
• Plain index on customer_b_id
• Partial index on review_status WHERE = 'pending' — review-queue query

---

crm.customer_merge

Append-only POST-EXECUTION merge audit record — no updated_at, no deleted_at. By the time a row exists here, review already happened on customer_merge_candidate (FK'd via candidate_id, nullable — a merge need not originate from a logged candidate). Per the D7 rule, merge execution is always needs-approval, never may-act-alone.

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy customer_merge_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
source_customer_id	UUID	NOT NULL	—	FK → crm.customer. The record merged away. CHECK <> target_customer_id
target_customer_id	UUID	NOT NULL	—	FK → crm.customer. The surviving record. CHECK <> source_customer_id
merged_by_actor_id	UUID	nullable	—	FK → identity.actor
candidate_id	UUID	nullable	—	FK → crm.customer_merge_candidate. Nullable — merge need not originate from a logged candidate
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
reason	text	nullable	—
metadata	jsonb	nullable	—	Snapshot supporting reversal in principle (see OPEN_ITEMS — no enforcement built this pass)
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()

CHECK constraints (verified live):

Name	Condition
chk_customer_merge_self_merge	source_customer_id <> target_customer_id
chk_customer_merge_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on source_customer_id
• Plain index on target_customer_id
• Partial index on candidate_id WHERE NOT NULL

---

crm.customer_consent

Append-only consent event log — no updated_at, no deleted_at. Current opt-in state is cached on customer.marketing_opt_in; this table is the historical record of each consent action. Gets NO review seam at all (DR-42, an explicit exception) — consent is a captured FACT (the customer's own opted-in/opted-out action), not an inference, so there is structurally nothing to review-gate. Per the D7 rule, consent must NEVER be AI-initiated.

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy customer_consent_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
consent_type	text	NOT NULL	—	CHECK IN (marketing_email,sms,phone,privacy_terms,loyalty)
channel	text	nullable	—	CHECK IN (email,sms,phone,in_app,pos) OR NULL
status	text	NOT NULL	—	CHECK IN (opted_in,opted_out). No default — must be explicitly stated (verified live: no column default)
source	text	nullable	—	CHECK IN (pos,import,online,manual,unsubscribe_link,app) OR NULL
captured_at	timestamptz	NOT NULL	—	No default — the actual moment consent was captured, must be supplied explicitly (verified live: no column default)
captured_by_actor_id	UUID	nullable	—	FK → identity.actor. NEVER an agent by the D7 rule — consent must reflect the customer's own action, though this is app-enforced, not a DB CHECK
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
metadata	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()

No review seam of any kind — DR-42: consent is a captured fact, never an inference; nothing to review-gate structurally.

CHECK constraints (verified live):

Name	Condition
chk_customer_consent_consent_type	consent_type IN ('marketing_email','sms','phone','privacy_terms','loyalty')
chk_customer_consent_channel	channel IS NULL OR channel IN ('email','sms','phone','in_app','pos')
chk_customer_consent_status	status IN ('opted_in','opted_out')
chk_customer_consent_source	source IS NULL OR source IN ('pos','import','online','manual','unsubscribe_link','app')
chk_customer_consent_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on (customer_id, consent_type, captured_at)

---

crm.customer_tax_certificate

Tax-exemption certificates held by a customer. Global jurisdiction fields (issuing_country_code + issuing_region_code) use the same DR-37 pattern as address. status DEFAULTs 'pending_review', not v1's 'active' (DR-43) — an unverified certificate (especially one a future OCR-extraction agent might draft from a photo) must never default to trusted. REUSES the existing status/verified_by_actor_id/verified_at columns as the review seam rather than adding a parallel review_status column set (DR-44) — v1 already built an equivalent gate, so a duplicate structure would be redundant.

LIVE BUG fixed 2026-07-07 (DR-49): the crm/inventory/pricing erosion audit found the DR-43 default above only suggested the review workflow — nothing enforced it. A row could be inserted with status='active' (meaning "this customer is currently tax-exempt") while verified_by_actor_id/verified_at/document_ref were all NULL, recording an unverified, nobody-signed-off exemption. chk_customer_tax_certificate_active_requires_verification now closes this: status='active' requires both verified_by_actor_id and verified_at non-null. Separately, issuing_country_code's DEFAULT 'US' (below) was dropped for the same reason — a silently-assumed country is the same class of bug as a silently-assumed verification.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_tax_certificate_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
certificate_number	text	nullable	—
certificate_type	text	NOT NULL	—	CHECK IN (resale,agricultural,non_profit,government,other)
issuing_country_code	char(2)	NOT NULL	(none — dropped 2026-07-07, DR-49)	FK → shared.country.iso_alpha2. Was DEFAULT 'US'; the silent default was dropped so an omitted country is rejected (NOT NULL violation) rather than silently recorded as domestic
issuing_region_code	text	nullable	—	FK → shared.administrative_region.iso_3166_2, ON DELETE SET NULL
valid_from	date	NOT NULL	—
expires_at	date	nullable	—
document_ref	text	nullable	—	DEFERRED forward-ref, plain text, NO FK (DR-34) — the files schema doesn't exist in v2
status	text	NOT NULL	'pending_review'	CHECK IN (active,expired,revoked,pending_review). Defaults pending_review, changed from v1's active (DR-43)
verified_by_actor_id	UUID	nullable	—	FK → identity.actor. Reused as the review seam (DR-44) — no separate reviewed_by_actor_id
verified_at	timestamptz	nullable	—	Reused as the review seam (DR-44) — no separate reviewed_at
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
decision_provenance	jsonb	nullable	—
note	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Review seam (DR-44): this table reuses its existing status(pending_review)/verified_by_actor_id/verified_at columns as the review gate rather than adding a parallel review_status/reviewed_by_actor_id/reviewed_at set — v1 already built an equivalent gate.

CHECK constraints (verified live):

Name	Condition
chk_customer_tax_certificate_certificate_type	certificate_type IN ('resale','agricultural','non_profit','government','other')
chk_customer_tax_certificate_status	status IN ('active','expired','revoked','pending_review')
chk_customer_tax_certificate_region_country_match	issuing_region_code IS NULL OR (issuing_country_code IS NOT NULL AND left(issuing_region_code, 2) = issuing_country_code)
chk_customer_tax_certificate_automation_source	automation_source IN ('human','agent','system','seed')
chk_customer_tax_certificate_active_requires_verification	status <> 'active' OR (verified_by_actor_id IS NOT NULL AND verified_at IS NOT NULL) — added 2026-07-07 (DR-49), closes the live bug described above

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_id
• Plain index on (certificate_type, status)
• Partial index on expires_at WHERE deleted_at IS NULL
• TWO partial UNIQUE indexes — customer_tax_certificate_no_region_unique on (customer_id, certificate_type, issuing_country_code) WHERE issuing_region_code IS NULL AND deleted_at IS NULL AND status = 'active', and customer_tax_certificate_with_region_unique on (customer_id, certificate_type, issuing_country_code, issuing_region_code) WHERE issuing_region_code IS NOT NULL AND deleted_at IS NULL AND status = 'active'. Deliberately split this way to avoid the NULL-in-multi-column-unique trap (same bug class caught in identity.agent_duty_grant's original design, PROJECT_DECISIONS #22) — a single unique index including the nullable issuing_region_code column directly would fail to catch duplicates whenever the column is NULL, since Postgres treats NULL as distinct from NULL in uniqueness checks.

---

crm.customer_segment_definition

NEW table (not carried from v1) — catalog of segments. Mirrors identity.role's mixed-scope pattern exactly (DR-45): tenant_id is nullable (NULL = Vrida-shipped built-in segment such as vip/at_risk/seasonal/wholesale_like; populated = tenant-custom segment). Uses the same two-partial-unique-index shape identity.role uses to avoid the NULL-uniqueness trap. No autonomy columns at all — catalog definitions are human/Vrida-curated, never agent-written (same reasoning as customer_group).

Mixed-scope — tenant-scoped OR global, depending on tenant_id. tenant_id nullable, FK → platform.tenant.

RLS enabled — permissive policy customer_segment_definition_tenant_isolation on authenticated: USING/WITH CHECK ((tenant_id IS NULL) OR (tenant_id = current_setting('app.current_tenant_id')::uuid)) — the tenant_id IS NULL branch is load-bearing, same as identity.role; omitting it would hide all built-in segments from every tenant.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	NULL = Vrida-shipped built-in segment; populated = tenant-custom. FK → platform.tenant
code	text	NOT NULL	—	e.g. vip, at_risk, seasonal, wholesale_like
name	text	NOT NULL	—
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()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• Plain index on tenant_id — covers the (tenant_id IS NULL OR tenant_id = $1) RLS disjunction query pattern
• Partial UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL — customer_segment_definition_tenant_id_code_unique, constrains custom tenant segments only
• Partial UNIQUE on code WHERE tenant_id IS NULL AND deleted_at IS NULL — customer_segment_definition_builtin_code_unique, prevents duplicate built-in codes (NULL tenant_id rows don't collide under the standard partial unique — same NULL-uniqueness trap identity.role already solved)

Not seeded this pass — no Vrida-default segment definitions (vip/at_risk/seasonal/wholesale_like) exist as rows yet; this is schema-only (see OPEN_ITEMS).

---

crm.customer_segment_membership

Customer × segment membership. segment_definition_id is a real FK into customer_segment_definition (not bare CHECK-less text), per the resolved decision to add the catalog table. Full autonomy treatment (confidence_score, computed_by_actor_id, automation_source, full review seam, decision_provenance) — this is the primary home for the D6/D7 agent-computed-segment capability (VIP/at-risk/seasonal/wholesale-like classification).

Uses a stored status column (active/expired), NOT a WHERE expires_at > now() partial-index predicate (DR-47) — Postgres requires index predicates to be IMMUTABLE, and now() is only STABLE, so such an index fails to create at all (ERROR: functions in index predicate must be marked IMMUTABLE, confirmed empirically before this migration was written). This matches the existing convention identity.role_assignment.ends_at/identity.agent_duty_grant.ends_at already use — expiry is checked on-read or by a service-layer sweep, never computed live inside a partial index.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_segment_membership_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

No soft delete — verified live, no deleted_at column; lifecycle fully captured by status.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
segment_definition_id	UUID	NOT NULL	—	FK → crm.customer_segment_definition. Real FK, not bare text
confidence_score	numeric(3,2)	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,expired). Stored, not derived from expires_at at query time (DR-47)
computed_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
starts_at	timestamptz	NOT NULL	now()
expires_at	timestamptz	nullable	—	NULL = no expiry. Never referenced inside an index predicate (DR-47) — checked on-read/by sweep only
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_customer_segment_membership_status	status IN ('active','expired')
chk_customer_segment_membership_automation_source	automation_source IN ('human','agent','system','seed')
chk_customer_segment_membership_review_status	review_status IN ('not_required','pending','approved','rejected')

Indexes:

• PK on id
• Plain index on tenant_id
• Partial index on customer_id WHERE status = 'active'
• Partial index on expires_at WHERE expires_at IS NOT NULL AND status = 'active' — expiry sweep candidate list (predicate references only stored columns, not now() — DR-47 compliant)
• Partial index on review_status WHERE = 'pending' — review-queue query
• Partial UNIQUE on (customer_id, segment_definition_id) WHERE status = 'active' — at most one active membership per customer per segment

---

crm.customer_tag_assignment

NEW table (not carried from v1) — human free-form labels (e.g. "Master Gardener", "Garden Club"). Deliberately SEPARATE from customer_segment_membership (DR-46, per the resolved decision to split tags and segments) — no catalog, no confidence, no review seam; the lightest-weight table in the module. tag_name (as-entered) + tag_name_normalized (lowercased/trimmed, dedup key) follows the same pattern as customer.email/email_normalized. Soft-delete (deleted_at), not append-only — removing a tag is a light, reversible staff action, unlike the append-only event tables in this module.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy customer_tag_assignment_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

No updated_at — verified live, no updated_at column; a tag assignment is either present or soft-deleted, never edited in place.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
tag_name	text	NOT NULL	—	As-entered display value
tag_name_normalized	text	NOT NULL	—	Lowercased/trimmed — dedup key, same pattern as customer.email/email_normalized
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete — reversible staff action, not an audit event

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on customer_id
• Partial UNIQUE on (customer_id, tag_name_normalized) WHERE deleted_at IS NULL — one active instance of a given tag per customer

---

crm — Design Patterns Summary

Column-count reconciliation (verified live via information_schema.columns, schema crm)

Table	Cols
customer_group	10
customer	36
contact	15
address	15
customer_note	10
customer_task	15
customer_merge_candidate	15
customer_merge	11
customer_consent	12
customer_tax_certificate	19
customer_segment_definition	10
customer_segment_membership	17
customer_tag_assignment	8
Total	193

Remediation Phase 2 (2026-07-08)

Cross-cutting Remediation Plan Phase 2 (Item 6) changed the id column DEFAULT on crm's three append-only event/audit logs — customer_note, customer_consent, customer_merge — from gen_random_uuid() to platform.uuid_generate_v7(). This is a PK-generation-strategy change only: no column added, no column dropped, no type change. Reasoning: UUIDv7 is time-ordered, which keeps future time-range partitioning available on these append-only ledgers without a PK rewrite — a option that becomes impossible once rows have already landed on a random (time-unordered) UUIDv4 PK. crm.customer_tag_assignment was considered for the same change and explicitly excluded — it is soft-delete (carries deleted_at), not append-only, so it doesn't fit the rationale. No other crm table was touched. Column count is unaffected — still 189 (DEFAULT-only change). See PROJECT_DECISIONS #38 for the full cross-module record.

Remediation Phase 3 (2026-07-08)

Remediation Plan Phase 3, Item 8 added two nullable columns to crm.customer: credit_limit_cents (bigint, CHECK chk_customer_credit_limit_nonnegative — NULL or >= 0) and credit_terms (text, CHECK chk_customer_credit_terms IN due_on_receipt/net_15/net_30/net_45/net_60/net_90). This is a corrective fix, not a new feature: billing.ar_account's own column comment already stated it "reads from crm.customer, never duplicated," but crm.customer never actually carried these columns — DR-35's claim (2026-07-06 lock) that they were "REMOVED and reassigned to billing entirely" was itself the bug, not a real ownership decision. The corrected, now-enforced ownership: crm.customer owns credit-worthiness (a relationship judgment about the customer — squarely crm's domain), and billing.ar_account reads credit_limit_cents/credit_terms from here rather than duplicating them locally. Additive change against 60 live customer rows at build time; both columns nullable (NULL = no credit extended, fail-closed default). Service-layer enforcement — blocking a charge that would exceed the limit — is deferred to OPEN_ITEMS, not built this pass. crm is now 13 tables / 191 columns (up from 189). Migration: packages/db/migrations/20260708210000_phase3_item8_customer_credit_terms.sql. See PROJECT_DECISIONS #39.

Remediation Phase 4 (2026-07-08)

Remediation Plan Phase 4 (the final phase of the 4-phase plan, items 14–20 plus closing the Phase 3 anonymous-return decision) touched crm via 2 sub-items, both additive nullable columns on crm.customer — no new tables, no CHECKs, no destructive DDL:

• Item 17b — Enum→Catalog, additive interim: customer.payment_terms_id (UUID, nullable) FKs to the new shared.payment_terms_catalog (10 seeded rows — cod, prepaid, net_7, net_15, net_30, net_45, net_60, net_90, 2_10_net_30, plus one more — each carrying net_days/discount_percent/discount_days, so 2_10_net_30 can genuinely represent "2/10 net 30", something a bare CHECK-enum cannot). This sits alongside, not in place of, the existing customer.credit_terms CHECK-enum from Phase 3 — that enum is completely unchanged. Disclosed explicitly, not glossed over: the two are NOT a clean vocabulary subset of each other. The catalog's 4 codes cod/prepaid/net_7/2_10_net_30 have no credit_terms equivalent at all, so a future cutover to the catalog as sole source of truth needs a real mapping decision for those 4 codes — not a trivial 1:1 rename. This interim sync gap is disclosed durably in the Drizzle TypeScript source itself (crm/customer.ts), not just a migration-file SQL comment that could rot if migrations are ever squashed.
• Item 18 — GDPR/Erasure: customer.pii_vault_ref (text, nullable) mirrors platform.tenant_profile.ein_ref's vault-reference pattern — a pointer to an out-of-band vault service, enabling future crypto-shred erasure (the GDPR/CCPA "right to be forgotten") without this table ever storing the sensitive value directly. Shares, does not duplicate, the existing ein_ref vault-service-not-yet-built OPEN_ITEMS dependency — no new vault-service OPEN_ITEMS row was created for this column; the Drizzle comment explicitly cross-references the existing one.

Both columns nullable and additive against live data — zero backfill risk, zero DROP TABLE/DROP COLUMN anywhere in this phase (independently confirmed by adversarial verification). crm is now 13 tables / 193 columns (up from 191). Migrations: packages/db/migrations/20260709030000_phase4_item17_enum_to_catalog.sql (part b, customer.payment_terms_id) and packages/db/migrations/20260709040000_phase4_item18_gdpr_erasure.sql (customer.pii_vault_ref). Independently verified by 2 adversarial lenses, both CLEAN — 5 new regression tests added to crm-schema.spec.ts (section P). See PROJECT_DECISIONS #40.

First product module — foundation vs. product

crm is the first of the 13 nursery-vertical product modules to build under the v2 pipeline. Modules #1–4 (platform, identity, shared, multi_loc) were foundation/service-layer modules — tenancy, auth, global reference data, and physical locations, none of them customer-facing business domains. crm is the first module whose tables model an actual retail business concept (customers) rather than platform infrastructure.

Autonomy treatment varies deliberately by table, not uniformly

Unlike a blanket "every table gets the full autonomy shape" approach, crm applies three distinct tiers deliberately:

• Full (actor attribution + automation_source + review seam + decision_provenance): customer, customer_merge_candidate, customer_segment_membership. These are the tables where AI enrichment/dedup/segmentation actually write.
• Light (created_by_actor_id only, no automation_source/review seam): contact, address. Subordinate tables with no distinct AI capability targeting them this pass.
• None (no autonomy columns whatsoever): customer_group, customer_segment_definition. Human/Vrida-curated catalogs — never agent-written.
• Append-only with automation_source + decision_provenance but explicitly no review seam: customer_note (DR-38 — additive, zero mutation risk), customer_task (DR-38/DR-40 — task status IS the review mechanism), customer_merge (post-execution audit; review already happened upstream on the candidate).
• No review seam by design, not by omission: customer_consent (DR-42 — a captured fact, never an inference).
• Reuses existing domain columns as the review seam rather than adding a parallel set: customer_tax_certificate (DR-44 — status/verified_by_actor_id/verified_at already do the job).

Global address model, reused from multi_loc.site (DR-37)

address and customer_tax_certificate's issuing-jurisdiction fields both use flat address lines + natural-key FKs into shared.country/shared.administrative_region, replacing v1's US-only us_state/country_id shape — the same pattern multi_loc.site established at module #4, including the region_code/country_code consistency CHECK (left(region_code, 2) = country_code).

Three deferred forward-refs, all for the same reason (DR-34)

customer.consumer_id, customer.search_vector, and customer_tax_certificate.document_ref all target modules (consumer, search, files) that do not exist in this v2 database — verified live via psql \dn (only identity, multi_loc, platform, shared, crm exist) and via filesystem (no corresponding schema_docs/*.md files). Older docs (MODULE_INDEX.md, CROSS_MODULE_CONTRACTS.md) describing these as "locked" or "FK — READY" as of 2026-06-11/12 are stale v1/pre-pivot documentation predating the actual v2 rebuild sequence (platform → identity → shared → multi_loc → crm). consumer_id and document_ref are plain nullable columns with no FK; search_vector isn't built as a column at all this pass, since no v2 table anywhere yet uses a generated tsvector column to follow as precedent.

Segments vs. tags — two tables, not one (DR-46)

customer_segment_membership (computed/analytical: agent-assigned, confidence-scored, provenance-tracked, drawn from a defined catalog) and customer_tag_assignment (human free-form: no catalog, no confidence, no review, ad-hoc staff-typed text) are deliberately kept as separate tables. An earlier design draft proposed unifying them; this was reconsidered and rejected before build, per explicit resolution — different lifecycle, different authority, different provenance.

customer_segment_definition mirrors identity.role's mixed-scope pattern exactly (DR-45)

Nullable tenant_id (NULL = Vrida-shipped built-in, populated = tenant-custom), the same two-partial-unique-index shape, and the same mixed-scope RLS disjunction identity.role uses — a proven, already-audited pattern reused rather than invented fresh.

Two partial unique indexes beat one nullable-column unique index

customer_tax_certificate uses two partial unique indexes (split on issuing_region_code IS NULL vs. IS NOT NULL) instead of one unique index directly including the nullable column — avoiding the same NULL-uniqueness trap first caught in identity.agent_duty_grant's original design (PROJECT_DECISIONS #22). customer_segment_definition and identity.role use the analogous two-index split on tenant_id IS NULL vs. populated.

now() is not IMMUTABLE — stored status, not a live partial-index predicate (DR-47)

customer_segment_membership.status is a stored column, not derived via WHERE expires_at > now() in a partial index — Postgres requires index predicates to be IMMUTABLE, and now() is STABLE, so such an index fails to create (ERROR: functions in index predicate must be marked IMMUTABLE, confirmed empirically before writing this migration). Matches the existing convention identity.role_assignment.ends_at / identity.agent_duty_grant.ends_at already use — expiry is checked on-read or by a service-layer sweep, never live inside an index.

Agent-authority mapping — pure consumer of identity.agent_duty_grant

crm introduces no new authority mechanism. Every autonomous crm action would be gated by an active identity.agent_duty_grant row scoped to a crm-module identity.permission (module_code has no CHECK constraint, so crm-scoped permission codes require zero identity-module changes). crm is the first module where agent_duty_grant.spend_limit_cents is essentially always NULL — its autonomous actions are volume-bounded (quantity_limit: records/run, batch size), not money-bounded. Illustrative mapping (no permission rows seeded this pass):

crm action	Illustrative permission code	authority_level	Limit dimension
Enrich a customer field	crm:customer:enrich	draft_only (or may_act_alone above confidence threshold for non-contact fields)	quantity_limit
Compute a segment membership	crm:customer_segment:compute	may_act_alone above threshold	quantity_limit
Propose a merge candidate	crm:customer_merge_candidate:propose	draft_only	quantity_limit
Execute a merge	crm:customer_merge:execute	needs_approval (always)	N/A — gated by authority_level alone
Create a follow-up task	crm:customer_task:create	draft_only	quantity_limit

D7 autonomy-boundary table (the load-bearing artifact of this design)

Action	Authority	Notes
Create a customer record from deterministic capture (walk-in form, POS attach)	may-act-alone	Standard data entry, not an AI action
AI-enrich an EXISTING customer record	draft-only (needs-approval if touching email/phone)	Never auto-execute a change to how the customer is contacted
Compute/update a customer_segment_membership row	may-act-alone above confidence threshold; draft-only below	Segments are informational/targeting labels, lower blast radius than enrichment
Propose a duplicate-customer merge candidate	draft-only, always	Proposing costs nothing; a human reviews before any data moves
Execute a customer merge	needs-approval, always, never may-act-alone	Hard-to-fully-reverse; moves real transactional links
Create a customer_task	draft-only	Inert until a human acts
Record a customer_note	may-act-alone	Append-only, additive, zero mutation risk
Record a customer_consent event	never AI-initiated	Must reflect the customer's own action
Verify/reject a customer_tax_certificate	needs-approval, always	Compliance-bearing, already human-only in v1
Anything touching credit_terms/credit_limit	N/A to crm	Moved to Billing, out of this module's D7 table entirely

JSONB columns

customer.attributes, customer_merge.metadata, customer_consent.metadata, and decision_provenance on every table that carries the autonomy review seam (customer, customer_note, customer_task, customer_merge_candidate, customer_merge, customer_tax_certificate, customer_segment_membership).

Triggers

set_updated_at (via platform.set_updated_at()) fires BEFORE UPDATE on every table with an updated_at column: address, contact, customer, customer_group, customer_merge_candidate, customer_segment_definition, customer_segment_membership, customer_task, customer_tax_certificate. No crm-schema-specific triggers exist — same shared trigger function used across platform, identity, shared, and multi_loc.

Service layer

No CrmService exists yet — this pass is Drizzle schema + hand-written migration + tests against raw SQL only, matching the precedent set by shared and multi_loc (schema-first, service-layer-later).

Open items carried forward (see OPEN_ITEMS.md for full text)

1. Merge-reversal 24h window is a service-layer requirement, not schema — customer_merge.metadata supports it in principle, but no enforcement exists yet.
2. customer.consumer_id forward-ref deferred until the consumer module is designed/built.
3. customer.search_vector (Search FTS touch) deferred until the Search module is designed/built.
4. customer_tax_certificate.document_ref forward-ref deferred until the Files module is built.
5. CrmService (all service-layer methods) does not exist yet — schema-only this pass.
6. Vrida-default segment definitions (vip/at_risk/seasonal/wholesale_like) are not seeded yet — customer_segment_definition supports them structurally but no rows exist.
7. customer_merge_candidate has no dedup protection — an exact-duplicate proposal or a mirrored (customer_a_id, customer_b_id)/(customer_b_id, customer_a_id) pair can both be inserted with no conflict (found in the post-build Section 4 audit).
8. customer.tax_exempt/customer.marketing_opt_in maintained-cache columns have no reconciliation trigger or documented sync formula (found in the post-build Section 4 audit).
9. customer.tax_exempt_id's removal (DR-35) had never been cross-referenced from OPEN_ITEMS until the 2026-07-07 erosion audit — closed as recorded-not-an-action-item, not a real gap.
10. customer.payment_terms_id (Remediation Phase 4, Item 17b) is not yet kept in sync with customer.credit_terms — an additive-interim gap, and the 2 vocabularies are not a clean subset of each other (4 catalog codes have no credit_terms equivalent); a real mapping decision is needed before any eventual cutover.
11. customer.pii_vault_ref (Remediation Phase 4, Item 18) has no vault service to point to yet — shares the existing ein_ref vault-service-not-yet-built OPEN_ITEMS dependency, not a new one.

Fixed 2026-07-07 (PROJECT_DECISIONS #28 — see DR-48/DR-49 above)

• LIVE BUG closed: customer_tax_certificate can no longer be 'active' without a real verifier + timestamp (chk_customer_tax_certificate_active_requires_verification).
• Erosion closed: address.country_code restored to NOT NULL (was silently nullable).
• Erosion closed: customer_tax_certificate.issuing_country_code's silent DEFAULT 'US' dropped — callers must now state the issuing country explicitly.