consumer — new module (2026-07-11), platform-level consumer identity

10 tables, 104 columns — schema built and migration applied/verified live 2026-07-11; not yet locked (regression tests, independent lock-gate verification, and the PROJECT_DECISIONS entry are still in progress — see docs/modules/MODULE_BUILD_STATUS.md for current status). consumer is Vrida's platform-level person-identity layer: one account per real-world shopper, usable across every Vrida-tenant nursery. It is non-tenant-scoped for 8 of its 10 tables — RLS is keyed on app.current_consumer_id, not app.current_tenant_id — because a consumer is a fully separate principal population from tenant staff/operators: a consumer carries no identity.actor row and no identity.* presence of any kind. Auth is Supabase Auth (auth.users), the same credential store identity.identity_user/identity.operator already use, but a consumer session runs as a dedicated Postgres role, consumer_authenticated, never the merchant-shared authenticated role. consumer.consumer_merchant_link and consumer.event are the 2 exceptions — tenant-scoped, merchant-authenticated-accessible, standard RLS — see "The two tenant-scoped exceptions" below.

Depends on platform (tenant — only the 2 tenant-scoped tables), multi_loc (site — event's composite FK), shared (country — consumer_address.country_code). consumer_merchant_link.crm_customer_id is a deliberate loose ref, no FK into crm.customer — cross-schema, different RLS domain (consumer-scoped vs. tenant-scoped), matching this codebase's forward-ref discipline. rewards/offers both hold bare (non-composite) FKs INTO consumer.consumer — see those modules' own docs.

PROJECT_DECISIONS entry: pending (not yet logged — this build has not reached the lock gate).

Global rules for this schema:

• Non-uniform scoping, the defining trait of this module. 8 of 10 tables (consumer, consumer_identifier, consumer_address, consumer_interest, consumer_consent, identity_merge_event, identity_map, consumer_feature) carry no tenant_id column at all and are RLS-scoped to app.current_consumer_id, policy <table>_self_access, TO consumer_authenticated. The other 2 (consumer_merchant_link, event) carry tenant_id NOT NULL and standard <table>_tenant_isolation RLS TO authenticated, exactly like every other tenant-scoped table codebase-wide.
• No identity.actor presence anywhere in the 8 consumer-scoped tables. Zero *_actor_id columns, zero created_by_actor_id/reviewed_by_actor_id, zero autonomy pack of any kind — a structural consequence of consumers being a separate principal population with no identity.actor row to attribute to. identity_merge_event.automation_source is the sole exception (see its own section).
• updated_at trigger-maintained via platform.set_updated_at() on 4 of 10 tables (consumer, consumer_merchant_link, consumer_address, consumer_interest — the 4 with a genuine post-creation update lifecycle). The other 6 (consumer_identifier, identity_merge_event, consumer_consent, event, identity_map, consumer_feature) have no updated_at column at all — append-only, ledger-fact, or wholesale-recomputed-cache shapes.
• Soft delete (deleted_at) on the same 4 tables that get updated_at — consumer, consumer_merchant_link, consumer_address, consumer_interest. None of the other 6.
• Append-only, 3 tables: identity_merge_event, consumer_consent (both consumer-scoped — REVOKE UPDATE, DELETE ... FROM consumer_authenticated) and event (tenant-scoped — REVOKE UPDATE, DELETE ... FROM authenticated). All 3 reuse platform.reject_append_only_mutation() verbatim and use platform.uuid_generate_v7() for their PK default (time-ordered, matching this codebase's append-only-ledger PK convention). See the Triggers section below.
• The consumer_authenticated Postgres role. CREATE ROLE consumer_authenticated NOLOGIN NOINHERIT, GRANT consumer_authenticated TO authenticator. NOT a Supabase Custom Access Token Auth Hook — apps/api (NestJS) owns its Postgres connection directly and never routes through PostgREST/GoTrue's own JWT-role-claim auto-switch. The mechanism is a new consumerDB() helper in packages/db/src/client.ts, mirroring the existing tenantDB() pattern exactly: SET LOCAL ROLE consumer_authenticated + set_config('app.current_consumer_id', ...) per transaction. NOINHERIT is deliberate — this role must never silently pick up privileges from a future, unrelated role membership; every grant it has is explicit. See "The GRANT/REVOKE matrix" below.
• consumer.get_cross_tenant_activity(p_consumer_id uuid) — the one sanctioned cross-tenant read path for a consumer's own view across merchants. Parameter-based SECURITY DEFINER, never reads an ambient session GUC. See its own section below.

Cross-Phase / Cross-Module Foreign Keys (consumer)

Column	Target	Notes
consumer_merchant_link.tenant_id, event.tenant_id	platform.tenant	NOT NULL — the only 2 tenant_id columns anywhere in this schema.
consumer_identifier.consumer_id, consumer_merchant_link.consumer_id, consumer_address.consumer_id, consumer_interest.consumer_id, identity_merge_event.source_consumer_id/.target_consumer_id, consumer_consent.consumer_id, event.consumer_id (nullable), identity_map.consumer_id (nullable), consumer_feature.consumer_id	consumer.consumer.id	Bare FK throughout — consumer.consumer itself has no tenant_id, so no composite (col, tenant_id) FK is possible or needed anywhere in this schema pointing to it. The structural inverse of every other FK convention in this codebase.
event.site_id	multi_loc.site (id, tenant_id)	nullable, composite FK event_site_tenant_fkey (the one composite FK in this module — event is tenant-scoped, so the usual composite convention applies here).
consumer_address.country_code	shared.country.iso_alpha2	char(2), NOT NULL, bare natural-key FK (codebase-wide convention).
consumer_merchant_link.crm_customer_id	— (loose ref, no FK)	Cross-schema, different RLS domain (consumer-scoped source row referencing a tenant-scoped target) — matches v1's own precedent.
(reciprocal, owned by rewards/offers) rewards.loyalty_account.consumer_id, offers.offer.consumer_id, offers.offer_code.consumer_id, offers.offer_assignment.consumer_id, offers.offer_redemption.consumer_id, offers.customer_discount_exposure.consumer_id	consumer.consumer.id	Bare, nullable-or-not per table — see rewards.md/offers.md.

---

consumer.consumer (17 cols) — the account itself

v1's 16 columns preserved verbatim + 1 genuinely new this build: supabase_auth_user_id, the seam the consumerDB()/role mechanism needs — a direct, unambiguous link from a Supabase Auth session (auth.users.id) to a consumer.consumer row. Not the same thing as auth_provider_sub (the upstream OAuth provider's own subject claim, e.g. Google's sub — informational/display only). email/phone on this row are a display cache only, populated once at creation and never synced from later consumer_identifier changes (disclosed, not assumed to reconcile). The full multi-identifier set lives in consumer_identifier, not here.

Non-tenant-scoped, consumer-scoped RLS. No tenant_id. RLS enabled — policy consumer_self_access, FOR ALL TO consumer_authenticated, USING/WITH CHECK both id = current_setting('app.current_consumer_id')::uuid. The merchant authenticated role is REVOKEd entirely (see GRANT matrix).

Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
supabase_auth_user_id	UUID	nullable	—	NEW this build. No FK — auth.users lives in Supabase's own auth schema, outside migration-managed tables (mirrors identity.operator's own unenforced seam). Nullable: unclaimed stubs have no auth account yet
auth_provider	text	nullable	—	CHECK IN (google,apple,email) when set
auth_provider_sub	text	nullable	—	Upstream OAuth subject claim — informational, NOT the auth seam
email	text	nullable	—	Display cache
email_normalized	text	nullable	—
phone	text	nullable	—	Display cache
phone_normalized	text	nullable	—
display_name	text	nullable	—
status	text	NOT NULL	'unclaimed'	CHECK IN (unclaimed,active,suspended,closed)
claimed_at	timestamptz	nullable	—
usda_zone	text	nullable	—
notification_opt_in	jsonb	nullable	—	e.g. {"email": true, "push": true} — lightweight UI preference cache, distinct from consumer_consent (formal consent record)
locale	text	nullable	—

CHECK constraints (4): chk_consumer_status; chk_consumer_active_requires_auth (supabase_auth_user_id IS NOT NULL OR status = 'unclaimed'); chk_consumer_auth_provider_pair ((auth_provider IS NULL) = (auth_provider_sub IS NULL)); chk_consumer_auth_provider_value.

Indexes (6): PK on id; consumer_supabase_auth_user_id_unique (UNIQUE, WHERE supabase_auth_user_id IS NOT NULL AND deleted_at IS NULL); consumer_auth_provider_sub_unique (UNIQUE, auth_provider+auth_provider_sub, WHERE auth_provider_sub IS NOT NULL AND deleted_at IS NULL); consumer_email_normalized_active_unique (UNIQUE, WHERE email_normalized IS NOT NULL AND status = 'active' AND deleted_at IS NULL); consumer_phone_normalized_idx (partial); consumer_status_unclaimed_idx (partial, WHERE status = 'unclaimed').

---

consumer.consumer_identifier (9 cols) — the multi-identifier model

Real table, not JSONB — identifiers must be individually filterable/joinable. UNIQUE (identifier_type, identifier_value) WHERE superseded_at IS NULL is partial, not blanket, by explicit design: a recycled identifier (shared household email, reassigned phone number) is locked to its first active claimant until a documented human-driven release/dispute flow releases it via superseded_at. This constraint guarantees data SHAPE (one active claim per identifier at a time) — it does not by itself solve mis-merge risk; that judgment lives in the not-yet-built service-layer conflict-resolution logic.

Non-tenant-scoped, consumer-scoped RLS. RLS enabled — policy consumer_identifier_self_access. No updated_at, no soft delete (a superseded row remains as history via superseded_at, never hard-deleted).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
identifier_type	text	NOT NULL	—	CHECK IN (email,phone,google_sub,apple_sub,device_id,card_token)
identifier_value	text	NOT NULL	—
is_primary	boolean	NOT NULL	false
is_verified	boolean	NOT NULL	false
verified_at	timestamptz	nullable	—
superseded_at	timestamptz	nullable	—	Release/dispute path — never hard-deleted
created_at	timestamptz	NOT NULL	now()

CHECK constraints (1): chk_consumer_identifier_type.

Indexes (3): PK on id; consumer_identifier_consumer_id_idx; consumer_identifier_type_value_active_unique (UNIQUE, partial WHERE superseded_at IS NULL).

---

consumer.consumer_merchant_link (12 cols) — 1 of 2 tenant-scoped exceptions

Renamed from v1's consumer_tenant_link. The consumer's own record of which Vrida nurseries they're linked to — complementary, not redundant, to crm.customer.consumer_id (the nursery's own view of the same link, v1's own established distinction, preserved). tenant_id IS the RLS scope here, the structural inverse of this module's 8 other tables. A merchant legitimately needs "which consumers are linked to my tenant" (e.g. POS loyalty lookup), so this table keeps standard tenant-scoped RLS to authenticated — it does not also grant consumer_authenticated direct access; a consumer's own "which merchants am I linked to" view goes exclusively through consumer.get_cross_tenant_activity().

linkage_method CHECK is widened: v1's 4 values (app_follow/cashier_create/cashier_link/purchase) PLUS 5 new (login/email_match/phone_match/card_token/explicit) — both sets preserved as real, distinct events, not one replacing the other.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — policy consumer_merchant_link_tenant_isolation, TO authenticated.

Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
linkage_method	text	NOT NULL	—	CHECK IN 9 values (v1's 4 + 5 new)
crm_customer_id	UUID	nullable	—	Loose ref, no FK — different RLS domain
linked_at	timestamptz	NOT NULL	—
consumer_opt_in	boolean	NOT NULL	false
confidence_score	numeric(3,2)	nullable	—
last_seen_at	timestamptz	nullable	—

CHECK constraints (1): chk_consumer_merchant_link_linkage_method.

Indexes (5): PK on id; consumer_merchant_link_consumer_id_idx; consumer_merchant_link_tenant_id_idx; consumer_merchant_link_consumer_tenant_unique (UNIQUE, consumer_id+tenant_id, WHERE deleted_at IS NULL); consumer_merchant_link_crm_customer_id_idx (partial).

---

consumer.consumer_address (13 cols)

v1 unchanged. Platform-level consumer addresses (home/shipping/billing), distinct from crm.address (the nursery's own delivery-address record for a customer).

Non-tenant-scoped, consumer-scoped RLS. RLS enabled — policy consumer_address_self_access.

Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
address_type	text	NOT NULL	'home'	CHECK IN (home,shipping,billing,other)
is_default	boolean	NOT NULL	false
line1	text	NOT NULL	—
line2	text	nullable	—
city	text	NOT NULL	—
state_code	text	nullable	—	Loose text ref — not FK'd to shared.us_state; international consumers may hold non-US province values
postal_code	text	NOT NULL	—
country_code	char(2)	NOT NULL	—	FK → shared.country.iso_alpha2

CHECK constraints (1): chk_consumer_address_address_type.

Indexes (3): PK on id; consumer_address_consumer_id_idx; consumer_address_consumer_type_default_unique (UNIQUE, consumer_id+address_type, WHERE is_default = true AND deleted_at IS NULL).

---

consumer.consumer_interest (8 cols)

v1 unchanged. Multi-valued plant interests for the recommendation engine — real table, not JSONB, since recommendation queries filter on (interest_type, interest_ref) server-side.

Non-tenant-scoped, consumer-scoped RLS. RLS enabled — policy consumer_interest_self_access.

Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
interest_type	text	NOT NULL	—	CHECK IN (category,item,topic) — generalized 2026-07-18, Phase 2 (PROJECT_DECISIONS #70; was plant_category,plant_specific,care_topic)
interest_ref	text	nullable	—	Loose ref — shared.plant.slug or a category code, not FK'd
interest_label	text	NOT NULL	—

Generalized 2026-07-18, Phase 2 (Nursery Vertical Extraction, PROJECT_DECISIONS #70) — the table's own STRUCTURE was already vertical-neutral (a generic type+ref+label shape); only the CHECK's closed vocabulary was nursery-specific. A nursery tenant populates interest_ref with plant category codes/plant slugs/care topics under these same 3 generic buckets; another vertical populates its own values. Zero live rows at the time — pure schema-only CHECK-widen, no data migration needed.

CHECK constraints (1): chk_consumer_interest_type.

Indexes (4): PK on id; consumer_interest_consumer_id_idx; consumer_interest_consumer_type_ref_unique (UNIQUE, consumer_id+interest_type+interest_ref, WHERE deleted_at IS NULL); consumer_interest_type_ref_idx (partial).

---

consumer.identity_merge_event (9 cols) — append-only

Audits a merge of two consumer identities (an unclaimed stub claimed by a real login, or two accounts matched on a shared identifier). Audits, does NOT prevent, mis-merges — the real conflict-resolution judgment lives in the not-yet-built service layer. The one consumer-scoped table in this module that DOES carry automation_source.

Non-tenant-scoped, consumer-scoped RLS, append-only. RLS enabled — policy identity_merge_event_self_access (source_consumer_id = ... OR target_consumer_id = ...). REVOKE UPDATE, DELETE FROM consumer_authenticated + trigger trg_identity_merge_event_append_only.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK — append-only PK convention
source_consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
target_consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
merge_reason	text	NOT NULL	—	CHECK IN (unclaimed_stub_claim,identifier_match,manual_support)
matched_identifier_type	text	nullable	—
matched_identifier_value	text	nullable	—
confidence_score	numeric(3,2)	nullable	—
merged_at	timestamptz	NOT NULL	now()
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)

CHECK constraints (3): chk_identity_merge_event_merge_reason; chk_identity_merge_event_automation_source; chk_identity_merge_event_source_ne_target (source_consumer_id != target_consumer_id).

Indexes (3): PK on id; identity_merge_event_source_consumer_id_idx; identity_merge_event_target_consumer_id_idx.

---

consumer.consumer_consent (9 cols) — append-only

The platform-level half of two-level consent. Merchant-level consent stays entirely in crm.customer_consent, untouched by this build — the two are deliberately separate records, not a shared table. A consent change is a NEW row (opted-in then later opted-out is 2 rows), never an UPDATE.

Non-tenant-scoped, consumer-scoped RLS, append-only. RLS enabled — policy consumer_consent_self_access. REVOKE UPDATE, DELETE FROM consumer_authenticated + trigger trg_consumer_consent_append_only.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK — append-only PK convention
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
purpose	text	NOT NULL	—	CHECK IN (cross_merchant_profiling,platform_marketing,ai_personalization)
status	text	NOT NULL	—	CHECK IN (opted_in,opted_out)
captured_at	timestamptz	NOT NULL	now()
source	text	nullable	—
channel	text	nullable	—
metadata	jsonb	nullable	—	e.g. {"ip_hint": null, "consent_version": "v1"}
created_at	timestamptz	NOT NULL	now()

CHECK constraints (2): chk_consumer_consent_purpose; chk_consumer_consent_status.

Indexes (3): PK on id; consumer_consent_consumer_id_idx; consumer_consent_consumer_id_purpose_idx.

---

consumer.event (10 cols) — 2 of 2 tenant-scoped exceptions, append-only

The engagement spine: every purchase/cart/view/email/loyalty/offer/search touchpoint a merchant's own systems produce, one row each. BINDING READ CONTRACT: consumer_id is nullable — anonymous events (browsing before login) carry only anonymous_id. Once written, an append-only row's consumer_id can never be backfilled after identity_map later resolves that anonymous_id to a real consumer. Every reader that needs a consumer's complete history MUST resolve anonymous_id → consumer_id via identity_map in addition to filtering on the row's own consumer_id, or it silently misses all pre-login activity — a read-time convention, reader-enforced not DB-enforced (mirrors this codebase's own disclosed ai_request.tenant_id/agent_identity_id consistency contract).

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. RLS enabled — policy event_tenant_isolation, TO authenticated (the structural inverse of every other table in this schema — this is the merchant's own engagement data). REVOKE UPDATE, DELETE FROM authenticated + trigger trg_event_append_only. No updated_at column.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK — append-only PK convention
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
consumer_id	UUID	nullable	—	FK → consumer.consumer (bare). See binding read contract above
anonymous_id	text	nullable	—
site_id	UUID	nullable	—	Composite FK → multi_loc.site (id, tenant_id), event_site_tenant_fkey
event_type	text	NOT NULL	—	CHECK IN 10 values (purchase,cart_add,page_view,email_open,email_click,loyalty_earn,loyalty_redeem,offer_redeem,search,product_view)
occurred_at	timestamptz	NOT NULL	now()
source	text	NOT NULL	—	CHECK IN (pos,web,email,sms,app)
properties	jsonb	nullable	—	e.g. {"product_id": "...", "category": "perennials", "campaign_id": null, "value_cents": 2999}
created_at	timestamptz	NOT NULL	now()

CHECK constraints (3): chk_event_event_type; chk_event_source; chk_event_consumer_or_anonymous (consumer_id IS NOT NULL OR anonymous_id IS NOT NULL).

Indexes (5): PK on id; event_tenant_id_idx; event_tenant_id_consumer_id_idx (partial); event_tenant_id_anonymous_id_idx (partial); event_tenant_id_occurred_at_idx.

Consent gate (Phase 1 remediation, 2026-07-18, Item 4): trg_event_validate_consent — BEFORE INSERT → consumer.validate_event_consent(). consumer_id IS NULL (the anonymous_id path) always bypasses the check — there is no known consumer to hold a consent record against. consumer_id IS NOT NULL requires a live (deleted_at IS NULL), opted-in (consumer_opt_in = true) consumer.consumer_merchant_link row for (consumer_id, tenant_id) — the real merchant-local half of this table's two-level consent model (mirroring the platform-level/merchant-level split consumer_consent's own docs already establish — see that table's entry above). The platform/cross-merchant half (consumer.consumer_consent, purpose = 'cross_merchant_profiling') is deliberately NOT consulted at event INSERT time: event rows are tenant-scoped and never automatically shared cross-tenant by this table itself; cross-merchant consent is a concern for whatever future cross-tenant read/aggregation path consumes this data, not the raw per-tenant write. Pre-migration audit: 57 of 57 live rows with a non-NULL consumer_id had no opted-in link at the time this trigger was added (100% — consumer_opt_in was never set true in existing seed/test data); not a blocking violation (a BEFORE INSERT trigger has no effect on already-committed rows), but every future identified-event insert path must establish an opted-in link first. Disclosed, not fixed here: consumer.get_cross_tenant_activity() (the one sanctioned cross-tenant read path, reading rewards/offers data) does not itself check cross_merchant_profiling consent either — a separate, pre-existing gap in the READ path, logged to OPEN_ITEMS rather than folded into this fix.

---

consumer.identity_map (6 cols) — the anonymous→consumer resolution join

Maps an anonymous_id to the consumer_id it was later resolved to — the join table event's own binding read contract (above) requires.

Non-tenant-scoped, consumer-scoped RLS. RLS enabled — policy identity_map_self_access. Pre-resolution rows (consumer_id IS NULL) are invisible to every consumer session — correct, since an unresolved anonymous_id isn't "owned" by anyone yet. No updated_at, no soft delete.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
anonymous_id	text	NOT NULL	—
consumer_id	UUID	nullable	—	FK → consumer.consumer (bare)
resolved_at	timestamptz	nullable	—
resolution_method	text	nullable	—	CHECK IN (login,form_submit,email_click) when set
created_at	timestamptz	NOT NULL	now()

CHECK constraints (2): chk_identity_map_resolution_method; chk_identity_map_resolved_pair ((consumer_id IS NULL AND resolved_at IS NULL) OR (consumer_id IS NOT NULL AND resolved_at IS NOT NULL)).

Indexes (3): PK on id; identity_map_anonymous_id_unique (UNIQUE); identity_map_consumer_id_idx (partial).

---

consumer.consumer_feature (11 cols) — maintained RFM cache

RFM and derived-feature store for the recommendation/targeting engine. A batch-computed cache, not itself a source of truth — recomputed wholesale by a deferred RFM batch job, so it carries no autonomy pack.

Non-tenant-scoped, consumer-scoped RLS. RLS enabled — policy consumer_feature_self_access. No updated_at (uses computed_at instead), no soft delete.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
recency_days	numeric	nullable	—
frequency_count	numeric	nullable	—
monetary_cents	numeric	nullable	—
rfm_segment	text	nullable	—
category_affinity	jsonb	nullable	—	e.g. {"perennials": 0.8, "annuals": 0.3}
channel_preference	jsonb	nullable	—	e.g. {"email": 0.6, "sms": 0.1, "app": 0.3}
price_sensitivity	numeric	nullable	—
computed_at	timestamptz	NOT NULL	now()
computed_by	text	NOT NULL	'batch_job'	CHECK IN (batch_job,realtime)

CHECK constraints (1): chk_consumer_feature_computed_by.

Indexes (2): PK on id; consumer_feature_consumer_id_unique (UNIQUE).

---

The GRANT/REVOKE matrix

The 8 consumer-identity tables (consumer, consumer_identifier, consumer_address, consumer_interest, consumer_consent, identity_merge_event, identity_map, consumer_feature) are REVOKEd entirely from authenticated and granted to consumer_authenticated only. consumer_merchant_link and event stay standard merchant-authenticated-accessible with no consumer_authenticated grant at all. Both directions are enforced with an explicit REVOKE, not just an absent GRANT — belt-and-suspenders, matching this codebase's established practice of never relying on absence-of-grant alone.

Role	consumer/consumer_identifier/consumer_address/consumer_interest/consumer_consent/identity_merge_event/identity_map/consumer_feature (8)	consumer_merchant_link/event (2)	rewards.* / offers.*
authenticated (merchant)	REVOKE ALL — zero access	Full CRUD (append-only narrowing re-applied on event)	Full CRUD (append-only narrowing re-applied on loyalty_point_ledger/offer_redemption)
consumer_authenticated	SELECT/INSERT/UPDATE/DELETE, narrowed to SELECT/INSERT on the 2 append-only tables	REVOKE ALL — zero access	REVOKE ALL on every table in both schemas, plus schema-level REVOKE ALL ON SCHEMA and a default-privileges REVOKE for future tables — zero access

consumer_authenticated also has an explicit, defensive REVOKE ALL (table- and schema-level) across every other merchant schema (platform, identity, shared, multi_loc, crm, inventory, ai, pricing, pos, orders, purchasing, tax, billing, payments, admin, approvals, receiving) — a brand-new role has zero access by default anyway (none of these schemas grant PUBLIC usage), so this is belt-and-suspenders disclosure, not a functional gap being closed.

---

consumer.get_cross_tenant_activity() — the sanctioned cross-tenant read path

The only way a consumer session reads across merchants. Parameter-based SECURITY DEFINER, never reads an ambient session GUC for its cross-tenant SELECT — closes a leak scenario an ambiguous "session-variable elevation" implementation could otherwise create (a ballooned standing RLS access across a whole transaction). Called only from a consumer-authenticated request path — enforced by the service layer (a future ConsumerGuard), not by this function itself, disclosed exactly like ai_request.tenant_id/agent_identity_id consistency being service-layer-enforced elsewhere in this codebase.

CREATE OR REPLACE FUNCTION consumer.get_cross_tenant_activity(p_consumer_id uuid)
RETURNS TABLE (
  tenant_id uuid,
  loyalty_program_id uuid,
  balance_points integer,
  lifetime_points integer,
  offer_id uuid,
  offer_name text
)
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog
STABLE
AS $$
  SELECT la.tenant_id, la.loyalty_program_id, la.balance_points, la.lifetime_points,
         NULL::uuid, NULL::text
  FROM rewards.loyalty_account la
  WHERE la.consumer_id = p_consumer_id AND la.deleted_at IS NULL
  UNION ALL
  SELECT oa.tenant_id, NULL::uuid, NULL::integer, NULL::integer, oa.offer_id, o.name
  FROM offers.offer_assignment oa
  JOIN offers.offer o ON o.id = oa.offer_id
  WHERE oa.consumer_id = p_consumer_id AND oa.deleted_at IS NULL AND oa.status IN ('issued','viewed','claimed');
$$;
REVOKE ALL ON FUNCTION consumer.get_cross_tenant_activity(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION consumer.get_cross_tenant_activity(uuid) TO consumer_authenticated;

Unions a consumer's rewards.loyalty_account rows (balance/lifetime points per tenant/program) with their active offers.offer_assignment rows (issued/viewed/claimed, per tenant/offer) into one result shape — owned by a role with real table access (not superuser), so SECURITY DEFINER genuinely widens privilege for this one narrow purpose rather than granting blanket superuser-equivalent reach.

---

consumer — Design Patterns Summary

Column-count reconciliation

Table	Cols
consumer	17
consumer_identifier	9
consumer_merchant_link	12
consumer_address	13
consumer_interest	8
identity_merge_event	9
consumer_consent	9
event	10
identity_map	6
consumer_feature	11
Total	104

Counted directly from the applied CREATE TABLE statements (packages/db/migrations/20260711000000_consumer_rewards_offers.sql), not from Drizzle source comments — 3 of the per-table comments in the .ts source (consumer_identifier "10 cols", event "11 cols", consumer_feature "13 cols") overstate their table's true column count by 1–2 each; the schema-level total these same comments imply (10 tables, 104 cols) is nonetheless correct, since the per-table errors are a source-comment bookkeeping slip, not a build error. 10 tables, 104 columns, matching the build task's own stated total exactly.

Triggers, full list (3 functions, 7 trigger objects)

Trigger	Table	Fires	Function
set_updated_at	consumer, consumer_merchant_link, consumer_address, consumer_interest	BEFORE UPDATE	platform.set_updated_at() (shared, reused)
trg_event_append_only	event	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation() (shared, reused)
trg_identity_merge_event_append_only	identity_merge_event	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation()
trg_consumer_consent_append_only	consumer_consent	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation()

Plus consumer.get_cross_tenant_activity(uuid) — not a trigger, a SECURITY DEFINER function (see its own section above).

Service layer

No ConsumerService yet — schema-only this pass, same pattern as every other module's deferred service layer at build time. The consumerDB() client helper (mirroring tenantDB()) exists in packages/db/src/client.ts as the connection-level mechanism a future ConsumerService will use.

JSONB columns

consumer.notification_opt_in (UI preference cache), consumer_consent.metadata, event.properties (documented example shapes above), consumer_feature.category_affinity/.channel_preference (documented example shapes above).

Open items carried forward

1. consumer.email/.phone display-cache drift — populated once at creation, never synced from later consumer_identifier changes. Disclosed, not solved.
2. consumer_identifier mis-merge risk — the partial-unique constraint guarantees shape (one active claim per identifier) but not correctness; real conflict-resolution judgment is an unbuilt service-layer concern.
3. consumer_feature's RFM batch job — deferred; this table is a maintained cache with nothing yet computing into it.
4. No ConsumerService/ConsumerGuard yet — the service-layer enforcement get_cross_tenant_activity()'s own doc comment assumes (only ever called from a consumer-authenticated request path) is not yet built.
5. consumer_merchant_link.crm_customer_id/multi_loc.site_id on event — loose refs and one composite FK respectively; crm_customer_id stays a permanently loose ref by cross-RLS-domain design, not a gap.