# Acquisto Real Estate — Unified Platform Migration Plan
**Legacy (Next.js · PHP API · WordPress · MySQL) → PURE (Next.js SSR on Netlify + Supabase/Postgres)**

> **This document supersedes and merges the two parallel migration plans** — Lane A (`MIGRATION-PLAN.md`, grounded in live DB introspection + the RLS/PII fix) and Lane B2 (`docs/MIGRATION-PLAN.md` + `docs/ARCHITECTURE.md` + `docs/merger-plan-feedback.md`, grounded in the mature PURE platform). Reconciled 2026-06-24. It is the single source of truth; the two source docs are now historical.

---

## 0. TL;DR
- **Stack (both lanes agree):** **Next.js 14+ App Router (SSR/ISR) on Netlify** for the public site **and** `/admin` **and** the authed CRM/ERP app; **Supabase** (Postgres · Auth · Storage · Realtime · Edge Functions · pg_cron) as the decoupled backend. One Postgres model, fully dynamic, no hardcoded content. SEO-friendly URLs + JSON-LD + a 301 redirect map.
- **We extend, we don't start cold.** The PURE platform already exists and is substantial (see §2).
- **🔑 Reconciliation — there are TWO Supabase projects:**
  - **`mbnakfrkfwxinhxlisyq` — CANONICAL PURE platform.** 160 tables (100% RLS-on), 114 db functions (~70 `mcp_*`), 30 edge functions (incl. `mls-intake`), 21 cron jobs, Auth live (email/magic/Google/passkeys). **This is the migration target.**
  - **`oquynmzyypwrqpxtcrjb` — raw Zoho data dump.** 52 tables (leads 19k · contacts 10k · deals 2.8k · emails 37k), **RLS was disabled (real PII exposed) — now secured** (see §8). Role: a **data source** to fold into the canonical model, then retire. *(Needs a team decision — §11 D-A.)*
- **🚨 Security already actioned:** the Zoho-dump project's anon-key PII exposure is closed (RLS enabled + policies; verified). Full launch-gate register in §9 / §13.
- **What we still need from you** (esp. old-repo access — both lanes are blocked on it) is in §11.

---

## 1. Current → Target
| Concern | Legacy | Target (PURE) |
|---|---|---|
| Public rendering | Next.js (client-heavy) | **Next.js App Router — SSR/ISR** on Netlify (crawlable) |
| Backend / API | PHP API server | **Supabase**: PostgREST auto-API + `mcp_*`/`pure_*` RPC + Edge Functions (no PHP server) |
| CMS + admin | WordPress admin | **Native `/admin`** (Next.js) over Supabase |
| Users | WP `wp_users` (phpass) | **Supabase Auth** (`auth.users`) + `profiles` |
| Database | MySQL | **Supabase Postgres** (RLS on every table) |
| Listings source | NTREIS via PHP cron | **NTREIS RESO Web API** via Edge Function (`mls-intake`/`ntreis-sync`) + pg_cron |
| CRM / back-office | Zoho + spreadsheets | Canonical Postgres CRM/ERP (`contacts`/`leads`/`deals`/`cdas`) |
| Media | WP uploads | **Supabase Storage** (or CDN) |
| Hosting | mixed | **Netlify** (FE/SSR) + **Supabase** (backend) |

---

## 2. What already exists (the head start)
**Canonical platform `mbnakfrkfwxinhxlisyq`** (Postgres 17.6, us-east-2) — live snapshot 2026-06-24:
- **160 tables, 100% RLS-on**, 39 FKs, 0 views.
- **114 db functions** (~70 are the `mcp_*` PostgREST-RPC API the front-end + Claude call).
- **30 Deno edge functions** incl. `pure-mcp` (tool brain), `mls-intake`, `chat`, `billing`, `comms`/`pure-comms`/`resend-email`, `puresign`, `pure-passkey`/`webauthn-verify`/`access-guard`/`auth-email-hook`, `roster-*`, `offers-analyze`, `call-intel`, `pure-scan`/`perf-audit`.
- **21 pg_cron jobs** (all green): improvement-engine dispatch/digest, watchdogs, cron-health, comms-drain, cache, lead/MLS/memory sweeps.
- **Auth live**: email/pw, magic link, Google; passkeys/WebAuthn wired.
- `pure-brand-kit` ships a **Next.js theme**; the improvement-engine fleet + watchdogs monitor SEO/perf/uptime.

**Zoho-dump project `oquynmzyypwrqpxtcrjb`** (Postgres 17, ap-northeast-1): 52 tables of raw Zoho CRM (leads/contacts/deals/accounts/emails/SMS/cdas), the brokerage's real operational data — secured but un-normalized.

> **Implication:** Migration = (a) build the Next.js SSR front-end + `/admin` over the canonical platform, (b) stand up the NTREIS sync, (c) fold the legacy MySQL/WordPress data **and** the Zoho dump into the canonical model, (d) cut over SEO. Most backend primitives already exist.

---

## 3. Stack & rationale
**Frontend + SSR — Next.js 14+ App Router on Netlify.** RSC + SSR/SSG/**ISR** → property/content pages render server-side (crawlable + fast). Netlify Next Runtime supports SSR/ISR/Edge natively. SEO URLs via file-based dynamic routes (`/homes-for-sale/[city-state]/[address-slug]-[mls]`, `/agents/[slug]`, `/blog/[slug]`). Per-page metadata + JSON-LD from Postgres; on-demand revalidation so edits go live without a full rebuild. **One codebase, three route groups:** public (SSR/SEO) · app (authed CRM/ERP) · admin.

**Backend — Supabase (the "separate backend server" you asked for).** Postgres (RLS) · Auth/GoTrue · Storage · Realtime · Edge Functions (Deno: NTREIS sync, webhooks, integrations — 30 live) · pg_cron. API surface = PostgREST + `mcp_*`/`pure_*` RPC + Next.js route handlers. Fully decoupled; no PHP to run.
- **Heavy/long workloads:** start on Edge Functions + pg_cron (already the pattern). Add **one small Deno/Node worker** (Railway/Render/Fly) only if a job genuinely needs always-on/websockets/huge ETL.

**Supporting:** Postgres FTS + `pg_trgm` for listing search (Typesense/Algolia only at scale) · PostGIS for geo · Google Maps/Mapbox · Resend (email; auth hook wired) + Twilio (SMS) · Stripe (billing wired) · GitHub→Netlify CI + Supabase migrations · the improvement-engine fleet + watchdogs for SEO/perf/uptime.

---

## 4. Architecture schema
```
 Search bots + Browsers ── clean SEO URLs, server-rendered HTML
            │
            ▼
 ┌──────────────── Netlify · Next.js App Router ─────────────────┐
 │ PUBLIC (SSR/ISR, SEO)     APP (authed)        ADMIN (role)     │
 │ / /homes-for-sale/...     /app/crm /app/deals /admin/pages     │
 │ /agents /blog             /app/dashboard      /admin/listings  │
 │ sitemap.xml robots.txt    route handlers      /admin/redirects │
 └─────────┬───────────────────────┬───────────────────┬─────────┘
   server Supabase client    RPC / PostgREST       Auth (JWT)
           ▼                        ▼                   ▼
 ┌──────────────────────────── Supabase (mbnakfrkfwxinhxlisyq) ──────────────────────────┐
 │ Postgres 17 (160 tbl, RLS) · PostgREST · GoTrue · Storage · Realtime                   │
 │ 30 Edge Fns: mls-intake · forms · webhooks · chat · billing · comms · pure-mcp …       │
 │ 21 pg_cron: NTREIS sync · sitemap warm · improvement engines · watchdogs · digests     │
 │ mcp_guard (scope/kill/rate gate) · pg_net (async HTTP out) · pure_audit_chain          │
 └─────────┬──────────────────────────────────────────────────────────────┬──────────────┘
           ▼                                                                ▼
   External: NTREIS RESO Web API · Resend · Twilio · Stripe · Maps · GA · Follow Up Boss
           ▲
   one-time ETL ── legacy MySQL/WordPress  +  Zoho-dump project (oquynmzyypwrqpxtcrjb)
```

---

## 5. Data model (fully dynamic, Postgres)
**Two schemas already in the canonical platform:**
1. **Canonical relational core** (lowercase, FK-linked): `organizations` is the hub; `deals` is the transaction hub (`cdas`, `documents`, `financing_terms`, `transaction_parties`, `seller_disclosures`, `representation_agreements`, `open_houses` FK→`deals`; `deals`→`listings`→`organizations`); `comm_log` ties campaigns/contacts/leads together. The 39 FKs live here.
2. **Operational `pure_*` layer (~140 tables):** coordination (`pure_board`, `prompt_queue`, `pure_relay`), money (`pure_wallets`/`_txns`, `pure_books_ledger`, `pure_cda`), comms (`pure_comms_log`, `pure_notifications`), auth/security (`pure_org_members`, `pure_member_roles`, `pure_webauthn_creds`, `pure_audit_chain`, `audit_log`), MCP/automation (`pure_mcp_*`, `pure_rate_limits`, `pure_cron_*`, `pure_watch_alerts`), engines (`pure_improve_*`, `pure_icon_*`), RE app (`pure_leads`, `pure_offers`, `pure_showings`, `pure_rooms`, `pure_envelopes`, `pure_cma`).

**🔧 Open canon work (`t-schema-canon`) — the "twins":** the two schemas overlap (lowercase `leads`/`listings`/`contacts` vs `pure_leads`/`pure_listing_check`). **Pick the authoritative table per domain before migration writes begin** — otherwise ETL guesses. This is the #1 data-model task.

**New for the public site (CMS, replaces WP):** `cms_pages`, `cms_blocks`, `blog_posts`, `categories`, `media_assets`, `navigation`, `redirects` (301 map), `forms`, `form_submissions`, `seo_meta`, `site_settings`.

**Listings/MLS:** `listings`, `listing_media`, `listing_features`, `neighborhoods`, `agents`, `offices`, `saved_searches`, `favorites`, `listing_views`, `ntreis_sync_state`, `sync_log`. Address stored **structured** (street/city/state/postal) for RESO syndication; geo via PostGIS.

**RLS:** public `listings`/`cms_pages`/`blog_posts` readable by anon (for SSR + SEO); everything else scoped by JWT identity/role. *Fully dynamic = pages, nav, settings, listings all render from Postgres.*

---

## 6. NTREIS property pipeline (replaces the PHP cron)
Edge Function `ntreis-sync` (extends existing `mls-intake`) against the **RESO Web API (OData)**: incremental pulls (`$filter=ModificationTimestamp gt <last>`), idempotent upsert of `listings` + `listing_media` + `listing_features`, mark Sold/Withdrawn/Pending, honor RESO replication rules + rate limits, checkpoint `ntreis_sync_state`, log `sync_log`. **pg_cron** every 15–30 min + a nightly full reconcile. Media → Supabase Storage. Open tickets `t-ntreis-feed` / `t-ntreis-mls-api` ("creds from Shattique"). Maps to the existing `feat/mls-connector` PR (#51).

---

## 7. SSR + SEO
SSR/ISR on all public pages · clean URLs · dynamic `sitemap.xml` (+ image sitemap for listing photos) + `robots.txt` · **JSON-LD** (`RealEstateListing`, `Organization`, `BreadcrumbList`, `FAQPage`) · per-page title/description/OG/canonical from `seo_meta` · a **301 redirect map** (every old URL → new, served via Next middleware / Netlify `_redirects`) — **non-negotiable for preserving rankings** · Core Web Vitals budget · Search Console + Bing verification + sitemap submit · **parallel-run on a staging subdomain → monitor → DNS cutover.** The `seo-meta`/`seo-schema`/`broken-link`/`page-speed` improvement engines monitor this continuously.

---

## 8. Admin panel (retires WordPress)
`/admin/*` (Next.js, Supabase Auth, admin role): page + blog editor (blocks) · media library · **forms inbox** · user/agent management · listings overrides · navigation/menus · **redirects + SEO manager** · site settings. All DB-backed.

---

## 9. Auth, security & multi-tenancy
**Auth:** Supabase GoTrue — email/pw, magic link, Google live; passkeys/WebAuthn wired. JWT carries identity into RLS. WP `wp_users` (phpass) **not** GoTrue-compatible → import emails + profiles and **force reset / magic-link on first login** (or a one-time phpass-verify hook that rehashes to bcrypt). Map WP roles → org roles.

**Security model (canonical platform):** RLS on all 160 tables; `mcp_guard` gates every MCP tool (kill switch + per-principal scope + rate/daily-spend caps); hash-chained `pure_audit_chain` + `audit_log`.

**✅ Already actioned (Zoho-dump project):** that project had RLS **disabled** on all 52 tables exposing real PII via the anon key (leads 19k, contacts 10k, emails 37k, a `logins.password`). **Fixed 2026-06-24** — RLS enabled on all tables + temporary `authenticated` policy; `anon` now sees 0 rows; verified. See `SECURITY-FIX-2026-06-24.md`. Next: org/role-scoped policies (move off `using(true)`), key rotation if service-role was ever client-side, drop plaintext password.

**Launch-gate register (Lane B2 security/ops review) — see §13.**

---

## 10. Migration phases
| Phase | Goal |
|---|---|
| **0 — Discovery** *(blocked on access, §11)* | Read old repos + MySQL schema; inventory WP content/users/forms; map URLs; get NTREIS creds; decide `t-schema-canon` + Zoho-dump fate |
| **1 — Foundation** | Next.js app on Netlify (route groups, `pure-brand-kit`) + confirm canonical schema/RLS + Auth |
| **2 — Listings + SEO** | `ntreis-sync` + SSR listing/search pages + sitemap + JSON-LD + 301 redirect map |
| **3 — CMS + Admin** | content model + migrate WP content/media + build `/admin` + contact forms |
| **4 — Users + Leads** | migrate WP users (reset-on-first-login) + MySQL/Zoho leads → canonical CRM |
| **5 — CRM/ERP** | authed app over `deals`/`cdas`/transactions/`leads` |
| **6 — SEO cutover** | verify crawl, submit sitemap, parallel-run, 301s live, DNS cutover, monitor |
| **7 — Decommission** | retire WP/PHP/MySQL **and** the Zoho-dump project after parallel-run confidence |

---

## 11. ✅ What we need from you
**A — Access (both lanes are blocked here)**
1. **Old repo access** — GitHub names for the **Next.js FE**, **PHP API**, **WP theme/plugins**, granted to the session (they're not in `puremlstech` scope), or zipped exports. *(Currently 404/out-of-scope.)*
2. **Confirm canonical Supabase project** = `mbnakfrkfwxinhxlisyq`, and **decide the Zoho-dump project's fate** (fold into canonical then retire — recommended). Grant access to both.

**B — Data**
3. **MySQL** dump (schema + data, or read replica): properties, users, leads, CMS, forms.
4. **WordPress** WXR export (pages/posts/media) + `wp_users` + form submissions + `uploads/`; plugin list (forms, SEO/Yoast redirects).
5. **Declare systems of record** + resolve `t-schema-canon` (canonical table per domain).

**C — Property feed**
6. **NTREIS / RESO Web API creds** — base URL, OAuth client_id/secret (or token), dataset/OUID, **and the existing PHP sync script** to mirror field maps + rules. *(`t-ntreis-mls-api`.)*

**D — SEO / domains / infra**
7. **URL inventory** — Search Console export or current sitemap → 301 map.
8. **DNS/domain control** for `acquisto.biz` + a **staging subdomain** (`new.acquisto.biz`) for parallel-run.
9. **Integrations to preserve** — GA/GTM IDs, Follow Up Boss, pixels, Stripe + Twilio keys.

**E — Sign-off**
10. Stack sign-off (Next.js App Router on Netlify + Supabase + `pure-brand-kit`) and **cutover preference** (phased parallel-run recommended).

---

## 12. Decisions to confirm (recommendation in **bold**)
| # | Decision | Recommendation |
|---|---|---|
| D1 | Frontend | **Next.js App Router on Netlify** (both lanes agree) |
| D2 | Backend | **Supabase** (Edge Functions + pg_cron); add a worker only if a job demands it |
| D-A | Two Supabase projects | **Canonical = `mbnakfrkfwxinhxlisyq`**; fold Zoho-dump data in, then retire `oquynmzyypwrqpxtcrjb` |
| D-B | Schema "twins" (`t-schema-canon`) | **Pick one authoritative table per domain** before ETL writes |
| D3 | Property source of truth | **Re-sync from NTREIS**; migrate only non-MLS data |
| D4 | Property search | **Postgres-native (PostGIS + FTS/pg_trgm)** first |
| D5 | Admin/CMS | **Native Next.js `/admin`** over Supabase |

---

## 13. Launch-gate register (security / ops — Lane B2 review)
Enforced by the **cron watchdog fleet** ("nothing ships until all watchdog jobs green for N consecutive days"):
1. **Tenant isolation as a tested invariant** — RLS regression suite (tenant A can't read B with a real 2nd JWT) in CI **and** nightly cron; standardize join keys on `auth.uid()` + orgs/memberships (not email strings); wire `get_advisors` into the daily review. *(Highest risk.)*
2. **Secret rotation + key mgmt** — templated anon-key injection (rotate in one build, not per-page edits); per-principal MCP tokens with expiry; rotation cadence for service-role/SMTP/Stripe/Twilio; CI secret-scan as a hard gate; leaked-secret runbook.
3. **Backups, PITR + a *tested* restore drill** — confirm PITR tier, document RPO/RTO, periodic restore drills, export critical tables (board/deals/audit_log) to object storage, finish localStorage→tables before launch.
4. **Server-side money math** — move ledger/balance to triggers / SECURITY DEFINER RPCs + check constraints; idempotent wallet debits; reconciliation (ledger vs CDA vs Stripe).
5. **Error budgets + synthetic transactions** — SLOs per surface (auth, deal room, comms, MCP); queue-depth/lag on `prompt_queue` + relay bus; Edge Function latency/error dashboards; synthetic flows.
6. **Incident response + paging + public status page** — severity levels, escalation owner, public status page, real paging (SMS/Opsgenie), **dead-man's-switch** (who watches the watchdog).
7. **Deliverability + A2P/TCPA** — SPF/DKIM/DMARC; bounce/complaint handling + suppression; **10DLC/A2P brand+campaign** registration (the 30034 SMS block); STOP/HELP → real suppression; quiet-hours + consent records.
8. **Brute-force + abuse defense** — login rate-limit/lockout/anomaly detection; passkey/MFA policy; CAPTCHA/Turnstile on public lead + ticket-intake forms; edge/WAF rate-limiting.
9. **Compliance tied to MLS reality** — SOC 2 path/scaffolding; data-retention/deletion (CCPA); **RESO Web API / Data Dictionary certification** as an explicit MLS deliverable; TREC records-retention; append-only tamper-evident `audit_log`.
10. **Least-privilege for MCP/admin** — enforce rate/spend caps + approval gates in the guard; access reviews; short-lived tokens; audited break-glass.
11. **Cost controls + scalability** — budget alerts on Supabase/Netlify/LLM/Twilio/Resend; PgBouncer/pooling; indexing/partitioning for hot tables (pageviews, comms_log, audit_log, listings); media/storage lifecycle; LLM per-tier budget ceilings.
12. **Supply-chain hygiene** — pin/self-host third-party scripts (fonts, Babel/JSX) with SRI; dependency/CVE scan; reproducible CI build (retire drag-drop deploys).

---

*Reconciles `MIGRATION-PLAN.md` (Lane A) + `docs/MIGRATION-PLAN.md` / `docs/ARCHITECTURE.md` / `docs/merger-plan-feedback.md` (Lane B2). Live figures from introspection of both Supabase projects on 2026-06-24.*