Knowledge > Products > WiseAI Realtor > Data Model

WiseAI Realtor — Data Model (v0 spec)

Closes P0 #1 of the Backend Completeness Audit: "No backend data-model/schema spec. Only a 14-line ASCII diagram exists." This is a paper design. Nothing here is applied — every table is a proposal for a founder-gated migration, authored to be IF NOT EXISTS / additive in the house style of migrations/20260520_local_business_platform.sql.

Naming convention: all NEW real-estate-native tables use the re_ prefix so they are unmistakably scoped on the shared single Supabase instance (wrwkszmobuhvcfjipasi) that also holds 222K churches, ITW, SermonWise, and the cross-vertical local-business platform. Every re_ table is tenant-scoped by business_id uuid → local_businesses.id.

---

0. Ground truth — what already exists (verified 2026-06-27)

A correction to the brief's premise, important enough to lead with:

The live RE runtime does NOT write to the church voice_* tables. The real-estate vertical (voice verticals/real_estate/tools.py + chat + the web APIs api/real-estate/{contact,home-value,newsletter}) captures every inbound lead into the cross-vertical local_business_leads table via src/lib/local-business/server/mutations.ts → createLead() with source='voice' | 'chatbot' | 'form'. The church voice_prayer_requests / voice_callback_requests / voice_visitor_contacts tables are written by the church vertical only.

lead_events + campaign_members are a different world again: they belong to the outbound cold-outreach funnel (selling WiseAI TO realtors) and reference the prospecting leads table — NOT the realtor's own customers. Do not conflate the realtor-as-prospect funnel with the realtor's CRM. (See Open Questions — the brief's "RE leads overload voice_* (+ lead_events/campaign_members)" line is stale and should be corrected in do-not-reinvent.md.)

Concern	Existing table(s)	Verdict for WiseAI Realtor
Tenant (agent/team/brokerage account)	local_businesses (vertical='real_estate', slug, admin_token, metadata jsonb)	KEEP as the tenant root
Per-tenant config / AI training	local_business_setup_profiles (1:1)	KEEP
Brokerage roster	local_business_agents (id, business_id, agent_name/email/phone, languages[], areas[], price_band_min/max, active, is_default)	KEEP + EXTEND (add auth + RBAC)
Listings	local_business_listings (REListing shape; source manual|ddf, ddf_id, is_published, sort_order, banner, photos jsonb, maps)	KEEP; move the overlay out (see §1.6)
Phone lines	local_business_voice_lines (telnyx_phone_number, voice_id, calls_limit, status)	KEEP + EXTEND (language, line_type)
Inbound leads (flat)	local_business_leads (source/status/priority enums, contact fields, summary/intent, follow-up dates, metadata)	EVOLVE → re_contacts + re_interactions (§1.1/§1.2). Keep table as the capture buffer short-term
Follow-up tasks	local_business_followup_tasks	EVOLVE → re_tasks (§1.8)
Review requests / message log	local_business_review_requests, local_business_message_logs	KEEP (Reviews Engine shipped); link to re_contacts
Owner website content / blog	local_businesses.metadata.website jsonb {draft,published} (incl. blog[])	EVOLVE → re_content_items (+ translations) once multi-type/multilingual (§1.10)
Church pastoral leads	voice_prayer_requests / voice_callback_requests / voice_visitor_contacts	DO NOT TOUCH — church-only; not part of RE

Net: the listings half is solid and reused as-is. The missing half is the CRM spine — a durable person record, a unified interaction timeline, a consent ledger, deals/transactions, saved searches, real tasks, and per-agent identity. That is what the re_ tables below add.

---

1. Core entities

All columns below are proposals. Conventions: uuid PKs DEFAULT gen_random_uuid(); every table carries business_id uuid NOT NULL REFERENCES local_businesses(id) ON DELETE CASCADE, created_at/updated_at timestamptz NOT NULL DEFAULT now() with the shared local_business_set_updated_at() trigger; metadata jsonb NOT NULL DEFAULT '{}' as the per-vertical escape hatch. Enumerations are named CHECK constraints (house style), called out as enum: for readability.

1.0 Entity-relationship diagram

re_phone_lines in the ERD is the extended local_business_voice_lines (§1.9) — shown by its conceptual name. re_users is the auth-identity layer over local_business_agents (§1.7).

---

1.1 re_contacts — the long-lived person (lead → client → past client)

The spine. One row per person, surviving across multiple deals and years. Replaces the per-inquiry flatness of local_business_leads.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK	tenant scope
assigned_agent_id	uuid NULL FK → local_business_agents(id) ON DELETE SET NULL	per-agent ownership
lifecycle_stage	text NOT NULL DEFAULT 'lead'	enum: lead | active_client | under_contract | past_client | sphere | dead
contact_type	text NOT NULL DEFAULT 'buyer'	enum: buyer | seller | both | investor | renter | referral_partner | vendor | other
pipeline_status	text NULL	enum: new | attempted | contacted | qualified | nurture | appointment_set | showing | offer | closed | lost (kanban)
lead_score	integer NULL	0–100, computed (see re_lead_score_factors in metadata or a future table)
first_name / last_name	text NULL
display_name	text NULL	denormalized for lists
primary_email / primary_phone	text NULL	normalized (E.164 phone, lower-cased email) for dedup — see §3
emails / phones	jsonb NOT NULL DEFAULT '[]'	secondary contacts [{value,label,verified}]
preferred_channel	text NULL	enum: email | sms | voice | whatsapp
preferred_language	text NULL	BCP-47 (en, fr, pa, ur, hi, zh) — drives multilingual routing
address / city / region / postal_code / country	text NULL	mailing address
timezone	text NULL
Relationship memory		the IXACT/FUB table-stakes gap
birthday	date NULL
home_anniversary	date NULL	closing anniversary (keep-in-touch trigger)
relationship_anniversary	date NULL	wedding etc. (optional)
family	jsonb NOT NULL DEFAULT '[]'	[{relation,name,birthday}] — spouse, kids, pets
mortgage	jsonb NOT NULL DEFAULT '{}'	{lender, rate, term_years, renewal_date, amount, type} → renewal = a reach-out trigger
important_dates	jsonb NOT NULL DEFAULT '[]'	free-form [{label,date,recurring}]
interests_notes	text NULL	freeform "remember this" notes
Attribution		see §4
source_category	text NOT NULL DEFAULT 'unknown'	first-touch bucket (taxonomy §4)
source_detail	text NULL	first-touch sub-source
first_touch_at	timestamptz NULL
last_touch_source	text NULL	last-touch bucket
last_touch_at	timestamptz NULL
referral_source_contact_id	uuid NULL FK → re_contacts(id)	who referred them (self-FK)
referral_source_text	text NULL	when the referrer isn't a contact
Consent rollup (denormalized from re_consent_events, fast gate)
email_consent_status / sms_consent_status / voice_consent_status	text NOT NULL DEFAULT 'none'	enum: none | implied | express | withdrawn
email_consent_expires_at / sms_consent_expires_at	timestamptz NULL	implied-consent expiry (CASL: 6mo inquiry / 2yr transaction)
do_not_contact	boolean NOT NULL DEFAULT false	global suppression — gates EVERY outbound channel incl. the AI
merged_into_contact_id	uuid NULL FK → re_contacts(id)	dedup: losing row points to survivor (§3)
ai_summary	text NULL	latest rolling AI summary of the relationship
tags	text[] NOT NULL DEFAULT '{}'	Smart-List fuel
metadata	jsonb

Indexes: (business_id, lifecycle_stage), (business_id, pipeline_status), (business_id, assigned_agent_id), (business_id, primary_phone), (business_id, primary_email), (business_id, last_touch_at DESC), GIN on tags. Partial unique helpers for dedup: UNIQUE (business_id, primary_phone) WHERE primary_phone IS NOT NULL AND merged_into_contact_id IS NULL (and same for email) — soft-enforced (see §3 risks).

1.2 re_interactions — the unified timeline

One append-only row per touch, of any kind, from any channel. The "unified contact timeline" the audit calls PARTIAL today.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
contact_id	uuid NULL FK → re_contacts(id) ON DELETE CASCADE	nullable until identity-resolved
deal_id	uuid NULL FK → re_deals(id) ON DELETE SET NULL	when tied to a transaction
agent_id	uuid NULL FK → local_business_agents(id)	acting agent (NULL = AI/system)
channel	text NOT NULL	enum: voice | chat | sms | email | web | qr | form | in_person | social | system
direction	text NOT NULL DEFAULT 'inbound'	enum: inbound | outbound | internal
actor	text NOT NULL DEFAULT 'human'	enum: human | ai_agent | system (who performed it — AI-bridge transparency)
interaction_type	text NOT NULL	enum: call | text | email | chat_session | website_visit | listing_view | saved_search_alert | qr_scan | form_submit | showing | note | status_change | review_request | appointment
occurred_at	timestamptz NOT NULL DEFAULT now()
subject	text NULL
body	text NULL	message text / note
summary	text NULL	AI summary
transcript	text NULL	call/chat transcript
recording_url	text NULL
duration_seconds	integer NULL	calls
sentiment	text NULL	enum: positive | neutral | negative
intent	text NULL	RE intent tag (§4 of tools.py taxonomy)
language	text NULL	BCP-47 detected language
source_table / source_record_id	text NULL	provenance back-pointer (voice_call_logs.id, local_business_leads.id, etc.) — migration bridge
metadata	jsonb	channel-specific payload (web event props, QR campaign id, listing id viewed)

Indexes: (contact_id, occurred_at DESC), (business_id, occurred_at DESC), (deal_id), (business_id, interaction_type), (business_id, channel). This is the table the dashboard reads for "what happened, when" and the speed-to-lead KPI (MIN(occurred_at) WHERE direction='outbound' minus the inbound).

1.3 re_consent_events — the CASL/PIPEDA ledger (append-only)

Closes audit P0 #3. Immutable log of every consent grant/withdrawal; the re_contacts.*_consent_status columns are a denormalized rollup of this. Required before ANY outbound AI email/SMS/voice ships.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
contact_id	uuid NOT NULL FK → re_contacts(id) ON DELETE CASCADE
channel	text NOT NULL	enum: email | sms | voice | all
consent_action	text NOT NULL	enum: granted | withdrawn | confirmed | expired
consent_basis	text NOT NULL	enum: express | implied_inquiry | implied_transaction | implied_existing_relationship | imported
scope	text NULL	enum: marketing | transactional | all
source	text NOT NULL	enum: web_form | chat | voice | sms_reply | import | manual | double_optin
evidence	jsonb NOT NULL DEFAULT '{}'	verbatim wording shown, form id, message body, etc.
captured_at	timestamptz NOT NULL DEFAULT now()
expires_at	timestamptz NULL	computed for implied bases (inquiry +6mo, transaction +24mo)
recorded_by	text NULL	agent id / 'system'
ip_inet	inet NULL
user_agent	text NULL

Index: (contact_id, channel, captured_at DESC). A DB trigger (or app service) recomputes the re_contacts rollup on insert. STOP/HELP keyword handling on inbound SMS writes a withdrawn row here.

1.4 re_deals — transactions / active business hub

Closes the "Active Business Hub" NEED. One row per transaction (a buyer deal, a listing/sell deal, a lease).

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
primary_contact_id	uuid NULL FK → re_contacts(id) ON DELETE SET NULL	the client (other parties via re_deal_parties)
owner_agent_id	uuid NULL FK → local_business_agents(id)	deal owner (commission attribution)
co_agent_id	uuid NULL FK → local_business_agents(id)
listing_id	uuid NULL FK → local_business_listings(id) ON DELETE SET NULL	subject property when ours
side	text NOT NULL	enum: buy | sell | lease_tenant | lease_landlord | referral
status	text NOT NULL DEFAULT 'prospect'	enum: prospect | active | offer_out | conditional | firm | closed | fell_through | cancelled
property_address	text NULL	snapshot (may not be a listing of ours)
mls_number	text NULL
list_price / offer_price / accepted_price / sale_price	numeric NULL
Key dates		the transaction-checklist backbone
offer_date	date NULL
acceptance_date	date NULL
condition_dates	jsonb NOT NULL DEFAULT '[]'	[{label,due_date,status}] financing/inspection/sale-of-buyer
conditions_waived_date (firm/firm-up)	date NULL
closing_date	date NULL
possession_date	date NULL
Commission		gated to FINANCIAL_ROLES — never leaks (see §3)
commission_basis	text NULL	enum: percent | flat
commission_rate	numeric NULL	e.g. 2.5
commission_gross	numeric NULL
commission_split_pct	numeric NULL	agent's share of gross
brokerage_split_pct	numeric NULL	brokerage's cut
referral_fee_pct	numeric NULL
commission_net	numeric NULL	computed/override
Attribution
source_category / source_detail	text NULL	deal-level source
probability	integer NULL	0–100 forecast
expected_close_value	numeric NULL	for the pipeline forecast
lost_reason	text NULL
notes	text NULL
metadata	jsonb

Indexes: (business_id, status), (business_id, owner_agent_id), (primary_contact_id), (business_id, closing_date).

re_deal_parties — the multi-party join (co-buyers, lawyers, lenders, the other agent, inspectors):

Column	Type	Notes
id	uuid PK
deal_id	uuid NOT NULL FK → re_deals(id) ON DELETE CASCADE
contact_id	uuid NULL FK → re_contacts(id)	when the party is a known contact
role	text NOT NULL	enum: buyer | co_buyer | seller | co_seller | buyer_lawyer | seller_lawyer | lender | mortgage_broker | inspector | other_agent | other_brokerage | appraiser | other
name / email / phone / company	text NULL	snapshot for non-contact parties
metadata	jsonb

1.5 local_business_listings — KEEP (reused as-is)

Already the canonical listing store and the DDF sync target (source manual|ddf, ddf_id, maps, photos). Full shape in src/lib/real-estate/types.ts (REListingAdminRow) and migrations/20260623_real_estate_vertical.sql + 20260627_re_listings_admin_fields.sql. No change beyond moving the display-overlay out (next).

1.6 re_listing_overlay — featured + sort, decoupled from the feed

Recommendation (changes current behavior): today is_published, sort_order, and banner live ON local_business_listings, but DDF sync.ts upserts feed rows on (business_id, ddf_id) — which can clobber an agent's featuring/ordering on every 6h sync. The do-not-reinvent "BEAT" requires feature + reorder of feed listings without editing MLS data and without duplicating. Store the overlay in its own table keyed by the listing so sync never touches it.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
listing_id	uuid NULL FK → local_business_listings(id) ON DELETE CASCADE	when the listing is ours
mls_number	text NULL	stable key for feed rows that get re-inserted by sync
ddf_id	text NULL	alt stable key
featured	boolean NOT NULL DEFAULT false
sort_order	integer NOT NULL DEFAULT 0	drives the homepage scroll/carousel
pinned_banner	text NULL	owner badge override
metadata	jsonb

Unique: (business_id, listing_id) WHERE listing_id IS NOT NULL, (business_id, mls_number) WHERE mls_number IS NOT NULL. The renderer LEFT JOINs overlay onto listings; featured DESC, sort_order ASC wins. (Interim: keep the on-row columns for the MANUAL editor and only consult the overlay for feed rows — but the clean target is the separate table.)

1.7 re_users + local_business_agents (extend) — identity & team

local_business_agents already models the roster (name/email/phone, languages[], areas[], price_band_*, is_default). It has no login. Add an auth-identity layer:

local_business_agents — ADD columns:

Column	Type	Notes
auth_user_id	uuid NULL FK → auth.users(id) ON DELETE SET NULL	Supabase Auth identity
role	text NOT NULL DEFAULT 'agent'	enum (rbac.ts — implemented): agent | team_admin | transaction_coordinator | broker_admin | brokerage_owner | marketing_assistant | isa | external_partner
scope	text NOT NULL DEFAULT 'own'	enum: own | team | brokerage — the role's reach
can_see_commissions	(superseded)	Superseded by rbac.ts capability gating — commission is a capability, not a boolean: commissions:amount:edit (=brokerage_owner only) + commissions:amount:read:{own|team|brokerage}
can_see_all_pipelines	(superseded)	Superseded by rbac.ts — pipeline reach is the role's scope (own/team/brokerage), not a boolean
status	text NOT NULL DEFAULT 'active'	active | invited | disabled

⚠️ IMPLEMENTED SOURCE OF TRUTH = rbac.ts (PR #1070, live + tested), now also baked into the applied RLS migration's realtor_can() helper. This v0 table proposed boolean flags; the shipped RBAC instead gates by capability across the 8 roles above at scopes own | team | brokerage. Commission example: only brokerage_owner holds commissions:amount:edit; broker_admin/team_admin/ brokerage_owner hold commissions:amount:read:{brokerage|team}; agent holds commissions:amount:read:own; isa/transaction_coordinator/marketing_assistant/ external_partner hold none. When this doc and rbac.ts disagree, rbac.ts wins. The acceptance specs (knowledge/acceptance/realtor-*.md) are reconciled to this enum.

re_brokerage (OPTIONAL, only when a brokerage spans multiple team tenants): for the Beckett MVP the team = one local_businesses tenant with two local_business_agents rows, so this is deferred. When needed: re_brokerage(id, name, legal_name, region, ...) + local_businesses.brokerage_id nullable FK. Solo agent = one tenant, one agent row, is_default=true.

Why not a separate re_users? Keeping auth on local_business_agents avoids a four-way join (auth.users ↔ user ↔ agent ↔ tenant). One agent = one roster row = one login. A future multi-tenant user (an assistant working two brokerages) would need a re_user_business_roles join, but that is out of MVP scope.

1.8 re_tasks — tasks, follow-ups, action-plan steps

Evolves local_business_followup_tasks (which is lead-only and free-text-assigned) into a contact/deal-aware task with real ownership.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
contact_id	uuid NULL FK → re_contacts(id) ON DELETE CASCADE
deal_id	uuid NULL FK → re_deals(id) ON DELETE SET NULL	transaction-checklist tasks
assigned_agent_id	uuid NULL FK → local_business_agents(id)
type	text NOT NULL DEFAULT 'todo'	enum: call | text | email | showing | follow_up | document | deadline | review_request | todo
title	text NOT NULL
body	text NULL
status	text NOT NULL DEFAULT 'open'	enum: open | scheduled | in_progress | completed | dismissed | overdue
priority	text NOT NULL DEFAULT 'normal'	enum: low | normal | high | urgent
channel	text NULL	enum: phone | sms | email | in_person | manual
due_at	timestamptz NULL
completed_at	timestamptz NULL
created_by	text NULL	'system' | 'ai_coach' | agent id
action_plan_step_id	uuid NULL FK → re_action_plan_steps(id)	when spawned by a drip/action plan
metadata	jsonb

Indexes: (business_id, status, due_at), (assigned_agent_id, status), (contact_id), (deal_id).

re_action_plans + re_action_plan_steps (the drip/action-plan library — buyer/seller/FSBO/expired/past-client/investor/newcomer): re_action_plans(id, business_id NULL→global template, name, audience, active); re_action_plan_steps(id, plan_id, step_order, offset_days, channel, template_subject, template_body, task_type). Enrolling a contact spawns re_tasks rows. Library templates can be global (business_id NULL) and cloned per tenant.

1.9 local_business_voice_lines (extend) = re_phone_lines + re_sms_messages

Phone lines already exist (telnyx_phone_number, voice_id, calls_limit, status). Extend for the multilingual line strategy (Premium EN/ES/FR + Community PA/UR/HI/Mandarin):

local_business_voice_lines — ADD columns:

Column	Type	Notes
line_type	text NOT NULL DEFAULT 'premium'	enum: premium | community
language	text NULL	BCP-47 primary language of the line
label	text NULL	"Main", "Punjabi line"
sms_enabled	boolean NOT NULL DEFAULT false
provider	text NOT NULL DEFAULT 'telnyx'	telnyx | twilio

re_sms_messages — NEW (the SMS/text ISA lane the audit flags PARTIAL; threaded, consent-gated):

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
contact_id	uuid NULL FK → re_contacts(id)
phone_line_id	uuid NULL FK → local_business_voice_lines(id)	which DID
agent_id	uuid NULL FK → local_business_agents(id)	NULL = AI ISA
direction	text NOT NULL	enum: inbound | outbound
from_number / to_number	text NOT NULL	E.164
body	text NULL
media_urls	jsonb NOT NULL DEFAULT '[]'	MMS
status	text NOT NULL DEFAULT 'queued'	enum: queued | sent | delivered | failed | received
provider / provider_message_id	text NULL
segments	integer NULL	billing
is_automated	boolean NOT NULL DEFAULT false	AI vs human
consent_checked	boolean NOT NULL DEFAULT false	outbound gate audit
error	text NULL
created_at	timestamptz

Each SMS also writes a thin re_interactions row (channel='sms') so the timeline stays unified; re_sms_messages holds the channel detail. STOP/HELP on inbound → re_consent_events withdrawal. (The existing local_business_message_logs remains the email/notification send-audit table; SMS conversation threading is what re_sms_messages adds.)

1.10 re_content_items + re_content_translations — content & multilingual

Promotes blog/newsletter/etc. out of local_businesses.metadata.website JSONB into real rows once content is multi-type + multilingual (genuinely greenfield per do-not-reinvent). The website {draft,published} JSONB stays the source for hero/about/sections; content_items owns long-form, schedulable, translatable pieces.

re_content_items:

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
author_agent_id	uuid NULL FK → local_business_agents(id)
type	text NOT NULL	enum: blog | newsletter | social_post | buyer_guide | seller_guide | neighbourhood_page | market_update | open_house_followup | email_campaign
status	text NOT NULL DEFAULT 'draft'	enum: draft | scheduled | published | sent | archived
base_language	text NOT NULL DEFAULT 'en'	BCP-47
title	text NOT NULL
slug	text NULL
excerpt	text NULL
body_html	text NULL	sanitized allowlist (same as REBlogPost)
hero_image	text NULL
seo	jsonb NOT NULL DEFAULT '{}'	{metaTitle, metaDescription, schema}
channels	text[] NOT NULL DEFAULT '{}'	where it publishes: website | email | facebook | instagram | linkedin
scheduled_at / published_at	timestamptz NULL
tags	text[]
metadata	jsonb

re_content_translations (one row per language variant; keeps variants linked + independently publishable):

Column	Type	Notes
id	uuid PK
content_item_id	uuid NOT NULL FK → re_content_items(id) ON DELETE CASCADE
language	text NOT NULL	BCP-47
title / excerpt / body_html	text
seo	jsonb
status	text NOT NULL DEFAULT 'draft'	per-language publish state
translated_by	text NULL	'ai' | agent id
UNIQUE	(content_item_id, language)

1.11 re_saved_searches — buyer alerts (VOW capture gate)

The "saved searches + price-drop / just-sold alerts" table-stake; also the lead-capture mechanism behind a registration gate.

Column	Type	Notes
id	uuid PK
business_id	uuid NOT NULL FK
contact_id	uuid NULL FK → re_contacts(id) ON DELETE CASCADE	the buyer (NULL = anonymous until registered)
name	text NULL	"Byron 3-bed under 700k"
criteria	jsonb NOT NULL DEFAULT '{}'	{minPrice,maxPrice,beds,baths,propertyTypes[],areas[],polygon,keywords[]}
alert_frequency	text NOT NULL DEFAULT 'instant'	enum: instant | daily | weekly | off
alert_channels	text[] NOT NULL DEFAULT '{email}'	email | sms | push
alert_types	text[] NOT NULL DEFAULT '{new_listing}'	new_listing | price_drop | just_sold | open_house | back_on_market
last_run_at / last_alert_at	timestamptz NULL
active	boolean NOT NULL DEFAULT true
metadata	jsonb

Index: (business_id, active), (contact_id). (Price-drop / just-sold alerts depend on the VOW/sold feed which is still greenfield — see risks.)

---

2. Coexistence & migration plan

2.1 The three lead worlds — keep them separate

1. Church pastoral (voice_prayer_requests / voice_callback_requests / voice_visitor_contacts, scoped by church_id, with prayer_text, is_confidential, RBAC for PASTORAL_ROLES): untouched. Wrong semantics for RE (no prayer/confessional fields; RE has deals/commissions/showings). RE never reads or writes these. The brief's claim that RE overloads them is stale — correct do-not-reinvent.md line 28.
2. Cross-vertical inbound (local_business_leads, scoped by business_id): the table RE actually writes today via createLead(). The migration source.
3. Outbound cold-outreach (leads + outreach_campaigns + campaign_members + lead_events, the realtor-as-prospect funnel): untouched — different lifecycle, keep as the marketing engine that SELLS WiseAI Realtor.

2.2 local_business_leads → re_contacts + re_interactions

local_business_leads is a flat per-inquiry row. It is fine as a capture buffer but not as a CRM spine. Plan:

• Phase A (additive, no behavior change): create the re_ tables. Keep createLead() writing local_business_leads exactly as-is. Add a thin forward-fill service/trigger: on each local_business_leads insert/update, upsert a re_contacts row (identity-resolved on normalized phone/email, §3) and append a re_interactions row (channel from source, source_table='local_business_leads', source_record_id=leads.id). re_contacts becomes the read model; the lead row stays the write path. Zero risk.
• Phase B (cutover): point createLead() (and the dashboard inbox) at re_contacts/re_interactions directly; local_business_leads becomes append-only legacy. Backfill all historical local_business_leads rows (one-time) into contacts+interactions via the same resolver.
• Phase C (retire): stop writing local_business_leads; keep it read-only for audit. Migrate local_business_followup_tasks → re_tasks (FK remap lead_id → contact_id via the provenance pointer); local_business_review_requests gains a nullable contact_id and is linked by the resolver.

Column mapping (local_business_leads → target):

local_business_leads	→ target
contact_name / contact_email / contact_phone	re_contacts.display_name / primary_email / primary_phone (normalized)
source (voice|chatbot|form|sms|email|manual|appointment|review_feedback)	re_interactions.channel + re_contacts.source_category/detail (mapped via §4)
source_record_id	re_interactions.source_record_id
status (new|needs_response|contacted|booked|won|lost|archived)	re_contacts.pipeline_status (mapped) + on won → spawn/attach re_deals
priority	re_tasks.priority (when a follow-up exists)
subject / message	re_interactions.subject / body
summary / intent / urgency_reason	re_interactions.summary / intent + contact ai_summary
assigned_to (free text)	resolve to re_contacts.assigned_agent_id (match name → local_business_agents)
last_contacted_at / next_follow_up_at	re_contacts.last_touch_at + a re_tasks row
won_at / lost_at / lost_reason	re_deals.status/closed/lost_reason
metadata (incl. RE intent tags, is_demo)	merge into re_interactions.metadata / re_contacts.metadata

2.3 What to keep vs replace (summary)

Keep as-is	Keep + extend	Evolve (new re_)	Do not touch
local_businesses, local_business_setup_profiles, local_business_listings, local_business_review_requests, local_business_message_logs	local_business_agents (auth/RBAC), local_business_voice_lines (line_type/language/SMS)	local_business_leads→contacts+interactions; local_business_followup_tasks→re_tasks; website metadata.blog→re_content_items	voice_* (church), leads/campaign_members/lead_events (outreach), 222K churches, unified_rag_content

2.4 The source column + tenant scoping on the shared DB

Every re_ table carries business_id (the tenant). There is no source='voice' ambiguity across verticals because RE data lives in RE-native tables — but re_interactions.channel + re_contacts.source_category preserve "where it came from." Cross-vertical safety is by business_id + vertical='real_estate' on the parent, never by a magic prefix. (Contrast the church voice_* tables, which disambiguate church-vs-chat with a source column on a shared table — a pattern we deliberately do NOT repeat.)

---

3. Identity, scoping & RLS

3.1 Two scoping axes

• Tenant scope (business_id): hard isolation between accounts. Every query filters business_id. Every re_ table FKs the tenant.
• Agent ownership (assigned_agent_id / owner_agent_id): WITHIN a tenant, who owns the contact/deal. Solo agent: one agent, sees all. Team (Beckett = 2 agents): each sees own + shared per their scope (own/team/brokerage). Commissions gated by the commission capability (rbac.ts — see §1.7). (The v0 can_see_* booleans are superseded by rbac.ts.)

3.2 Identity resolution / dedup (audit P0 #2)

One person who scans a QR + calls + submits a form = 3 rows today. Resolver, run at capture time and on backfill:

1. Normalize phone → E.164, email → lowercased/trimmed, on write.
2. Match within (business_id) on primary_phone OR any phones[], then primary_email OR any emails[]. Exact normalized match → attach to existing contact.
3. No match → new contact.
4. Merge UI for fuzzy/manual: losing row sets merged_into_contact_id = survivor; interactions/deals/tasks/consent repoint to survivor; survivor unions emails/phones/relationship-memory. Never hard-delete (audit trail).
5. Soft uniqueness only (partial unique indexes excluding merged rows) — a hard unique constraint would break legitimate shared numbers (couples, office lines). Dedup is a service, not a constraint.

3.3 RLS on the shared instance

Today: the local-business platform is service-role-only (FA-110 — no anon/authenticated grants; all access via SUPABASE_SERVICE_ROLE_KEY in src/app/api/**), with token-based admin auth (local_businesses.admin_token, magic link). RLS is effectively "deny all; server mediates."

MVP recommendation: keep the service-role + app-layer scoping model. Grant re_ tables to service_role only; enforce business_id + agent ownership in the API/query layer (as voice-queries.ts already does for churches). This is consistent, ships fastest, and matches the existing dashboard. Per-user accountability comes from local_business_agents.auth_user_id recorded on writes — not from RLS yet.

Forward path (when agents get direct Supabase Auth sessions): layer RLS keyed on a JWT business claim. Illustrative policy (NOT applied):

-- conceptual, post-Supabase-Auth
alter table public.re_contacts enable row level security;
create policy re_contacts_tenant_isolation on public.re_contacts
  for all to authenticated
  using ( business_id = (auth.jwt() ->> 'business_id')::uuid )
  with check ( business_id = (auth.jwt() ->> 'business_id')::uuid );
-- agent-ownership + commission gates enforced in a second policy / by column-level views.

A business_id custom claim must be minted into the JWT (Supabase Auth hook / app_metadata) when an agent logs in. Commission columns on re_deals should additionally sit behind the commission capability gate (rbac.ts — a restricted view or a policy that checks the commissions:amount:read:{own|team|brokerage} / :edit capability) — mirroring churchwiseai-web's existing financial-data RBAC. Do not expose any re_ table to anon; the public website reads listings through the existing server APIs, never directly.

---

4. Lead-source taxonomy (first-touch + last-touch)

Stored as two columns on re_contacts (source_category + source_detail) for first touch, and last_touch_source + last_touch_at for last touch; every re_interactions row also records its own channel, so full multi-touch attribution is reconstructable from the timeline. Deals carry their own source_category/source_detail (the touch that produced the transaction).

source_category (enum)	source_detail examples (sub-source)
ai_voice	inbound_call, missed_call_text_back, sign_call, after_hours
ai_chat	website_widget, listing_page, facebook_messenger
ai_sms	inbound_text, keyword_optin
web_form	contact_form, home_valuation, book_showing, newsletter_signup
qr_scan	yard_sign, open_house, feature_sheet, mailer, business_card
saved_search	registration_gate, vow_signup
referral	past_client, sphere, agent_referral, partner (lender/lawyer)
portal	realtor_ca, housesigma, zoocasa
paid	google_ads, meta_ads, youtube
organic	google_search, direct, social_organic
import	csv, prior_crm, follow_up_boss
manual	agent_entered, walk_in, event
unknown	(default)

Rules: first_touch_* is set once (immutable after first interaction); last_touch_* updates on each new inbound interaction. The local_business_leads.source enum maps in: voice→ai_voice, chatbot→ai_chat, sms→ai_sms, form→web_form, appointment→web_form/book_showing, review_feedback→referral/past_client, manual→manual, email→organic|manual (resolve by context).

---

5. Open questions, migration risks & phased rollout

5.1 Open questions (need founder/decision input)

1. Auth model timing. Stay service-role + magic-link for the Beckett MVP (fastest), or stand up Supabase Auth per-agent now so RBAC/RLS is real from day one? (Audit P0 #4 wants ≥2 real users — Beckett is two agents.) Recommendation: magic-link MVP, Auth in Phase 2.
2. Brokerage vs team modelling. Confirm team = one local_businesses tenant + N agents (recommended) vs a tenant-per-agent with a re_brokerage parent. Affects lead routing + commission rollups.
3. Listing overlay cutover. Move featured/sort_order off local_business_listings into re_listing_overlay now (clean, sync-safe) or keep on-row and special-case the DDF upsert to preserve them? Recommendation: separate overlay table.
4. Content storage. When to promote blog out of metadata.website JSONB into re_content_items — only when newsletters/multilingual ship, or now? Recommendation: defer until multi-type/multilingual is built (don't disrupt the shipped blog).
5. Consent on imported contacts. What basis do we record for a CSV/FUB import (likely imported, no express consent) — and does that block AI outbound until re-permissioned? (CASL exposure — needs the compliance stance from the audit's P0 #3/#6.)
6. Sold/VOW data home. Sold comps + price history + AVM need a board VOW feed (PropTx for Beckett), still greenfield. Where do sold records live — extend local_business_listings with a sold_* set behind the registration gate, or a separate re_sold_comparables table? (Gates the whole HouseSigma-class feature set.)
7. Lead scoring storage. Inline lead_score + factors in metadata, or a re_lead_score_events table for explainability ("why is this hot")?

5.2 Migration risks

• Double-write drift (Phase A). The forward-fill from local_business_leads → contacts/interactions must be idempotent (the resolver keys on normalized phone/email + source_record_id) or re-runs duplicate contacts. Mirror the existing migrations' ON CONFLICT DO NOTHING/UPDATE discipline.
• Bad dedup merges. Over-eager matching (shared family phone) collapses two people; under-eager leaves the 3-rows-per-person problem. Ship merge as reversible (keep merged_into_contact_id, never hard-delete) and human-confirmed for fuzzy cases.
• Phone/email normalization gaps. Un-normalized legacy rows won't match. Backfill must normalize first, then resolve. E.164 needs a default region (CA) — Beckett is Ontario, but design for per-tenant country/timezone.
• Commission leakage. re_deals financial columns are the highest-sensitivity data in the product. If RLS isn't on yet, the API layer MUST gate them (a commission-capability check per rbac.ts on every read) — a single un-gated select leaks a colleague's income. Add a contract test like tier-config.contract.test.ts.
• Consent rollup divergence. The denormalized re_contacts.*_consent_status can drift from the re_consent_events ledger. The ledger is source of truth; the rollup is recomputed by trigger and must be treated as a cache, never written directly by feature code.
• Shared-DB blast radius. All of this lands in the same instance as 222K churches + unified_rag_content. Every migration is founder-gated, additive, IF NOT EXISTS, service-role-grant-only, with a grep-audit of readers first (Rule #18 / FA-110). No ALTER to church or outreach tables.
• DDF sync vs overlay. If the overlay stays on-row, the 6h DDF sync silently resets agent featuring — a quiet, hard-to-debug regression. The separate re_listing_overlay removes the risk.

5.3 Phased rollout — what to build first (Beckett MVP)

Phase 0 — spine (this doc → one founder-gated migration):

• re_contacts, re_interactions, re_consent_events. Extend local_business_agents (auth_user_id, role, RBAC flags).
• Phase-A forward-fill: keep createLead() writing local_business_leads; add the idempotent upsert into re_contacts + append re_interactions. Dashboard inbox reads the contact + timeline.
• Outcome: a real person record with a unified timeline + a consent ledger — unblocks everything, zero behavior change to capture.

Phase 1 — workable CRM for two agents:

• re_deals + re_deal_parties (Active Business Hub, key dates, commission — gated). re_tasks (migrate local_business_followup_tasks). Pipeline (kanban on re_contacts.pipeline_status) + speed-to-lead from re_interactions.
• Per-agent ownership + Smart Lists (saved filters over re_contacts tags/stage/language). Link local_business_review_requests to re_contacts.

Phase 2 — AI front-office depth + alerts:

• re_sms_messages + voice-line extensions (SMS/text ISA lane, STOP/HELP → consent). re_saved_searches + listing alerts (new-listing on the DDF active feed; price-drop/just-sold await VOW). re_action_plans/re_action_plan_steps (drip + AI Follow-Up Coach). re_listing_overlay cutover.

Phase 3 — content + compliance + scale:

• re_content_items + re_content_translations (multilingual generation). Supabase Auth + RLS cutover. Brokerage hierarchy (re_brokerage) if a multi-team account lands. VOW/sold-data track (re_sold_comparables or extension) — the HouseSigma-class gate.

---

6. See also

• Information Architecture — the nav/screen model these tables back.
• Backend Completeness Audit — the P0/P1 gaps; this doc closes P0 #1 and frames #2/#3/#4.
• Do Not Reinvent — existing RE code map (correct line 28's lead-table claim per §0/§2.1 here).
• Competitive Analysis — the CRM-spine benchmark (FUB/IXACT) this model targets.
• Existing migrations grounding this spec: churchwiseai-web/migrations/20260520_local_business_platform.sql, 20260623_real_estate_vertical.sql, 20260627_re_listings_admin_fields.sql, 2026-06-06-{lead-events,campaign-members}-table.sql. Listing shape: src/lib/real-estate/types.ts. Lead write path: src/lib/local-business/server/mutations.ts. Church lead tables (do-not-touch): src/lib/voice-queries.ts.