consumer — Consumer Layer (post-v1.0)

Schema locked 2026-06-11. 4 tables, 47 cols: consumer (16), consumer_tenant_link (10), consumer_address (13), consumer_interest (8).

consumer is the THIRD non-tenant-scoped schema in Vrida (after platform and shared). It represents a real-world PERSON — the platform-level Vrida consumer account (Model B: one account per shopper, usable across all Vrida tenant nurseries). It carries NO tenant_id as a scope column. This is a deliberate architectural departure from every operational module (all tenant-scoped with RLS); it is not a gap or an oversight.

Consumer is a PERSON, not a commercial relationship. consumer.consumer stores who a person is — their platform identity, preferences, and the nurseries they're linked to. Whether they are wholesale, trade, or retail at any given nursery is a per-nursery crm.customer concern (customer_type + customer_group_id + pricing.price_list_assignment — all tenant-scoped, all locked). There is NO consumer_type, is_business, or is_wholesale on consumer.consumer. If any of these are ever proposed here, reject them — the field belongs on crm.customer, not on the platform-level person identity.

---

Design principles (load-bearing — document at every session touching this schema)

1. Consumer-scoped RLS — new guard pattern

Consumer tables are secured by a consumer-scoped RLS predicate, NOT a tenant-scoped one:

• consumer.consumer: WHERE id = current_setting('app.current_consumer_id')::UUID
• All other consumer.* tables: WHERE consumer_id = current_setting('app.current_consumer_id')::UUID

This requires a new ConsumerGuard / ConsumerInterceptor analogous to TenantGuard / TenantInterceptor, but keyed on consumer_id from the social-login JWT. ConsumerService sets SET LOCAL app.current_consumer_id = ? at the start of every consumer-session transaction, exactly as TenantInterceptor sets app.current_tenant_id. All consumer.* queries run inside a ConsumerService-managed transaction with that local variable set.

Consumer auth: social login (Google / Apple) → Supabase Social Auth → JWT with a consumer_id claim. A consumer is NOT an identity.identity_user — that is staff. Auth provider subject IDs are stored on consumer.consumer.auth_provider_sub; there is no enforced FK to Supabase Auth (external auth, same text-seam pattern as identity_user.supabase_auth_user_id).

2. Cross-store privacy — highest-stakes property of this schema

A tenant NEVER queries consumer.* directly. Tenants access consumer data ONLY through ConsumerService, which operates via a service-role / SECURITY DEFINER boundary and exposes a narrow interface:

• "Is this crm.customer linked to a consumer account?" → yes/no + consumer_id
• "What is this consumer's platform display name and email?" → staff UI display only
• "Create or link a consumer at checkout" → POSService calls ConsumerService.findOrCreate(email|phone), never a direct query

A tenant CANNOT see: which other nurseries a consumer shops at; the consumer's activity or points balance at other businesses; any consumer.* data except through their own crm.customer.consumer_id link.

Cross-store visibility belongs to the CONSUMER ALONE — visible in the consumer app only, via ConsumerService reading across the consumer's linked crm.customer rows using service_role. This is a commercial and legal requirement. Do not add any read path that exposes cross-tenant consumer data to a tenant session.

3. Identity-canonical rule — reference-don't-copy for identity fields

When crm.customer.consumer_id is set (consumer linked), consumer.consumer is the CANONICAL source of identity: email, phone, display_name. The corresponding crm.customer fields become tenant-specific overrides — a tenant may record a different billing email, trade name, or company alias. ConsumerService resolves canonical identity; service-layer code reads from consumer.consumer for platform-level identity display.

Do NOT copy consumer.consumer.email / phone / display_name down into crm.customer on link — that creates drift risk. For anonymous buyers (consumer_id IS NULL), crm.customer carries the only identity copy; this is unchanged behavior.

4. consumer_tenant_link ≠ crm.customer.consumer_id — complementary, not redundant

• crm.customer.consumer_id = the NURSERY'S view: "this customer record belongs to this platform consumer" (tenant-scoped, nursery-owned)
• consumer.consumer_tenant_link = the CONSUMER'S view: "I am linked to this nursery" (consumer-scoped, consumer-owned)

Both are needed. The consumer-side link supports app-following before any purchase exists (link_source = 'app_follow', crm_customer_id = NULL). consumer_tenant_link.crm_customer_id is the loose back-ref from the consumer side to the nursery's crm.customer row — filled in by ConsumerService when the nursery-side record is created or linked.

---

Cross-Phase FK seams

Column	References	Status
consumer.consumer — no tenant_id FK	—	By design — non-tenant schema; platform and shared have the same property
consumer_tenant_link.consumer_id	consumer.consumer	Intra-schema — enforced
consumer_tenant_link.tenant_id	platform.tenant	Cross-schema — enforced (platform locked 2026-06-09)
consumer_tenant_link.crm_customer_id	crm.customer	Loose ref — NOT enforced FK (cross-schema, different RLS domain; ConsumerService maintains coherence)
consumer_address.consumer_id	consumer.consumer	Intra-schema — enforced
consumer_address.country_code	shared.country	Cross-schema — enforced (shared locked 2026-06-09)
consumer_address.state_code	shared.us_state	Loose ref — NOT enforced FK (international consumers may have non-US province values)
consumer_interest.consumer_id	consumer.consumer	Intra-schema — enforced
consumer_interest.interest_ref	shared.plant.slug (loose text ref)	Loose ref — NOT enforced FK (category codes are not a locked table; plant slugs may not always resolve in shared.plant; validated by ConsumerService at write time)
consumer.auth_provider_sub	Supabase Social Auth (external)	Text seam — no FK (external auth provider, same pattern as identity_user.supabase_auth_user_id)
crm.customer.consumer_id	consumer.consumer	FORWARD-FK CLOSURE — currently OPEN in FORWARD_FK_REGISTRY. Moves OPEN → READY when consumer.consumer locks. FK addable at consumer-phase migration. Direction: CRM → consumer (tenant side points at platform side, NEVER reverse). ALTER TABLE SQL pre-written in PROJECT_DECISIONS CRM Module § Forward-ref: consumer link.

---

consumer.consumer — 16 cols

Platform-level Vrida consumer account — one row per real-world person. Covers all three creation paths: (1) self-register via app (social login → consumer row, status = 'active' immediately); (2) cashier-create at POS checkout (status = 'unclaimed' stub, claimed later via app); (3) cashier-link (existing consumer linked to a new or existing crm.customer).

Status lifecycle: unclaimed (cashier-created stub; no auth provider claimed yet) → active (claimed via social login or direct app self-registration). suspended and closed are terminal platform-action states. Active-requires-auth invariant (DB-enforced): any non-unclaimed account MUST have auth_provider_sub set — a claimed account necessarily went through social login. Only unclaimed stubs may have a NULL auth_provider_sub. auth_provider and auth_provider_sub are always co-null or co-present; never set one without the other.

Stub / self-signup email-collision — flagged design detail: when a cashier creates an unclaimed stub with email X (path 2), and the same person later self-registers via app with the same email X (path 1), the system must MERGE the stub into the self-registration rather than collide. The email_normalized partial unique enforces uniqueness only among status = 'active' rows, so an unclaimed stub can share an email address with a future active account during the claim window. ConsumerService MUST detect the unclaimed stub at claim time and execute the merge — transferring consumer_tenant_link rows, crm.customer.consumer_id references, and any other dangling refs to the new active consumer_id — before deleting the stub. Mechanics are a service-layer design detail resolved at consumer-phase build; the schema is structured to allow it.

RLS: WHERE id = current_setting('app.current_consumer_id')::UUID. service_role bypasses RLS for ConsumerService cross-tenant operations.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
auth_provider	text	nullable	—	CHECK (auth_provider IS NULL OR auth_provider IN ('google','apple','email')) — NULL for unclaimed stubs not yet claimed via social login
auth_provider_sub	text	nullable	—	Social-login subject ID from the auth provider (Google sub, Apple sub). NULL for unclaimed stubs. Text seam to Supabase Social Auth — not an enforced FK.
email	text	nullable	—	Canonical platform email as provided by auth provider or cashier. Stored as-provided for display.
email_normalized	text	nullable	—	Lowercase-trimmed canonical email for matching and dedup. Set by service layer on write.
phone	text	nullable	—	Canonical platform phone (E.164 format). NULL if consumer has no phone on record.
phone_normalized	text	nullable	—	Normalized E.164 phone for dedup matching. Set by service layer.
display_name	text	nullable	—	Consumer's chosen name on their Vrida account. May differ from any nursery's crm.customer name record. NULL for unclaimed stubs.
status	text	NOT NULL	'unclaimed'	CHECK (status IN ('unclaimed','active','suspended','closed'))
claimed_at	timestamptz	nullable	—	When an unclaimed stub was claimed (social login completed). NULL for direct self-registrations (status goes directly to active; claimed_at records the unclaimed→active transition only).
usda_zone	text	nullable	—	Platform-level USDA hardiness zone preference — for plant recommendations and nurseries-near-me defaults in the consumer app. The consumer's own zone; NOT a nursery's record about the consumer.
notification_opt_in	JSONB	nullable	—	Consumer app channel preferences (e.g. {"email":true,"push":true}). Governs Vrida platform-level consumer notifications (app announcements, cross-store offers). Distinct from crm.customer_consent / crm.customer.marketing_opt_in, which govern a tenant's marketing consent for that business relationship. Both must be honoured; neither overrides the other.
locale	text	nullable	—	BCP-47 locale code (e.g. 'en-US'). Consumer app UI locale preference.
(active-requires-auth CHECK)	—	—	—	CHECK (auth_provider_sub IS NOT NULL OR status = 'unclaimed') — any non-unclaimed account (i.e. active, suspended, closed) MUST have auth_provider_sub set. Only unclaimed stubs may have a NULL auth sub.
(auth-provider-pair CHECK)	—	—	—	CHECK ((auth_provider IS NULL) = (auth_provider_sub IS NULL)) — auth_provider and auth_provider_sub are always co-null (unclaimed stub) or co-present (claimed account). Never set one without the other.

Indexes:

• PK on id
• PARTIAL UNIQUE on (auth_provider, auth_provider_sub) WHERE auth_provider_sub IS NOT NULL AND deleted_at IS NULL — one platform account per social identity
• PARTIAL UNIQUE on (email_normalized) WHERE email_normalized IS NOT NULL AND status = 'active' AND deleted_at IS NULL — one active account per email (unclaimed stubs may share an email during the claim-window; see stub/self-signup merge note above)
• on (phone_normalized) WHERE phone_normalized IS NOT NULL AND deleted_at IS NULL
• on (status) WHERE status = 'unclaimed' — fast lookup of stubs awaiting claim

---

consumer.consumer_tenant_link — 10 cols

The consumer's own record of which Vrida nurseries they are linked to. This is the CONSUMER'S view of their nursery relationships — the complement to crm.customer.consumer_id (the nursery's view of the same link).

crm.customer.consumer_id is authoritative for the nursery side ("this customer record belongs to this platform consumer"). consumer_tenant_link is authoritative for the consumer side ("I am connected to this nursery"). The two sides complement each other; both are needed. The consumer-side record supports app-following before any purchase or crm.customer row exists at that nursery (link_source = 'app_follow', crm_customer_id = NULL).

tenant_id is an FK dimension, NOT the RLS scope. This is the only consumer.* table that carries a tenant_id column, and it carries it as a dimension (which nursery) — not as the RLS security boundary. RLS is still scoped on consumer_id. Do not confuse this tenant_id with the standard tenant-scoped pattern; it is a foreign-key pointer to platform.tenant, not a partition key.

RLS: WHERE consumer_id = current_setting('app.current_consumer_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — the RLS-scoping column
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — which nursery (FK dimension, NOT the RLS scope — see note above)
link_source	text	NOT NULL	—	CHECK (link_source IN ('app_follow','cashier_create','cashier_link','purchase')) — how the link was first established
crm_customer_id	UUID	nullable	—	Loose back-ref to the nursery's crm.customer row for this consumer at this tenant. NULL when link_source = 'app_follow' and no crm.customer exists yet. Filled in by ConsumerService when the nursery-side record is created or linked. NOT an enforced FK (cross-schema, different RLS domains).
linked_at	timestamptz	NOT NULL	—	When the link was established
consumer_opt_in	boolean	NOT NULL	false	The consumer's own opt-in to this nursery's consumer-facing communications via the Vrida app. Distinct from crm.customer.marketing_opt_in (the tenant's record of marketing consent for their CRM). Both must be true before consumer-app notifications from this nursery are sent.

Indexes:

• PK on id
• on (consumer_id)
• on (tenant_id)
• PARTIAL UNIQUE on (consumer_id, tenant_id) WHERE deleted_at IS NULL — one active link per consumer per nursery
• on (crm_customer_id) WHERE crm_customer_id IS NOT NULL

---

consumer.consumer_address — 13 cols

Platform-level consumer addresses — the consumer's personal home, shipping, and billing addresses on their Vrida account.

Distinct from crm.address (a nursery's tenant-scoped record of where to deliver to a customer). crm.address is owned by the nursery for its own delivery and billing purposes; consumer.consumer_address is owned by the consumer for use in the consumer app (nurseries-near-me distance calculation, future consumer-side delivery). They represent different things: the nursery's view of a buyer's delivery address vs the person's own home address.

state_code is a loose text ref (not an enforced FK to shared.us_state) because international consumers may have non-US province or state values not present in the us_state table. ConsumerService validates US state_code values against shared.us_state at write time when country_code = 'US'. country_code is an enforced FK to shared.country.

RLS: WHERE consumer_id = current_setting('app.current_consumer_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — RLS scope
address_type	text	NOT NULL	'home'	CHECK (address_type IN ('home','shipping','billing','other'))
is_default	boolean	NOT NULL	false	Whether this is the consumer's default address for this type
line1	text	NOT NULL	—	Street address line 1
line2	text	nullable	—	Apartment, suite, unit, etc.
city	text	NOT NULL	—
state_code	text	nullable	—	US state / province code (e.g. 'CA'). Loose text ref — not an enforced FK to shared.us_state; international consumers may have non-US province values. ConsumerService validates against shared.us_state when country_code = 'US'.
postal_code	text	NOT NULL	—	ZIP / postal code
country_code	text	NOT NULL	—	FK → shared.country (code column). ISO 3166-1 alpha-2 (e.g. 'US', 'CA', 'MX').

Indexes:

• PK on id
• on (consumer_id)
• PARTIAL UNIQUE on (consumer_id, address_type) WHERE is_default = true AND deleted_at IS NULL — one default address per type per consumer

---

consumer.consumer_interest — 8 cols

Multi-valued consumer plant interests — drives personalized plant recommendations and nursery matching in the consumer app.

Why a table (not JSONB on consumer.consumer): interests are multi-valued (a consumer may be interested in succulents, Japanese maples, AND vegetables) and drive queryable server-side logic ("find all consumers interested in acer-palmatum near this nursery"). JSONB on the parent row serves display but cannot serve recommendation-index queries efficiently. A separate table is correct when interests drive server-side recommendation lookup, not just client display.

interest_ref is a loose text ref to shared.plant.slug (for interest_type = 'plant_specific') or a category/topic code. Not an enforced FK because category codes are not a locked table, and future plant slugs may not always resolve in shared.plant. ConsumerService validates interest_ref values at write time.

RLS: WHERE consumer_id = current_setting('app.current_consumer_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — RLS scope
interest_type	text	NOT NULL	—	CHECK (interest_type IN ('plant_category','plant_specific','care_topic'))
interest_ref	text	nullable	—	Loose ref to the interest target: shared.plant.slug for 'plant_specific'; a category code for 'plant_category'; a topic code for 'care_topic'. NULL for interests without a specific ref. Not an enforced FK. Validated by ConsumerService at write time.
interest_label	text	NOT NULL	—	Human-readable display label (e.g. 'Japanese Maple', 'Succulents', 'Container Gardening').

Indexes:

• PK on id
• on (consumer_id)
• PARTIAL UNIQUE on (consumer_id, interest_type, interest_ref) WHERE deleted_at IS NULL — prevents duplicate interest entries per consumer/type/ref
• on (interest_type, interest_ref) WHERE interest_ref IS NOT NULL — recommendation queries: "find consumers interested in plant X or category Y"

---

Column counts: consumer(16) + consumer_tenant_link(10) + consumer_address(13) + consumer_interest(8) = 47

---

Deferred items

Item	Deferred to
Loyalty / points balance	rewards module — per (consumer, tenant), per-business. NOT here.
Promotions / offers	offers module (or consolidated into rewards — open question for offers-phase design). NOT here.
Plant browser, care chat, garden plans, app content	consumer_app module. NOT here.
ai_response_cache (consumer AI query cache)	ai schema — forward-decision locked at AI module lock (2026-06-11). NOT here or in consumer_app.
Consumer-side orders / in-app commerce	consumer_app deferred sub-phase.
Consumer-facing geo features (nurseries near me, PostGIS)	v1.5 — multi_loc.site lat/lon already present; PostGIS extension + GiST index build with consumer geo features.
Rich plant care content (care guides, images, pests, companions)	v1.5+ — generated at query time by AIService; cache in ai schema when built.

---