The data model

The whole database splits into two kinds of thing: a published local mirror of the CRM’s property inventory (read-mostly, admin-enriched) and a small set of transactional rows that record what a user did (a sale, an add-on, an EOI, a payment). The CRM owns the business truth; the portal keeps a projection plus its own workflow state. Hold that split and the schema makes sense. Verified against the current backend.

The inventory hierarchy

flowchart TB
C[Country]:::mirror --> D[Destination]:::mirror --> P[Project]:::mirror --> U[Unit]:::mirror
P --> PP[ProjectPhase]:::local --> U
UT[UnitType]:::mirror --> U
U --> US[UnitSale]:::txn
U --> UAS[UnitAddonSale]:::txn
L[Launch]:::mirror --> EOI[EOIRequest]:::txn
US --> TX[(Transaction ledger)]:::txn
UAS --> TX
EOI --> TX
classDef mirror fill:#dff0f0,stroke:#0C7C84,color:#0b1b2b;
classDef local fill:#eceff1,stroke:#5B6B7A,color:#0b1b2b;
classDef txn fill:#f6e7dc,stroke:#C4622D,color:#0b1b2b;

Data model — inventory hierarchy + transactional rows (verified)

The spine is Country → Destination → Project → Unit, with ProjectPhase as an optional grouping under a project and UnitType classifying a unit:

Model	Table	Belongs to	Identity
Country	countries	—	slug, is_published — no source_id (fully local)
Destination	destinations	Country (country_id)	source_id, is_published, leads_limit, requires_payment_currency_choice
Project	projects	Destination (destination_id)	source_id, is_published, payment_api_secret_key
ProjectPhase	project_phases	Project (project_id)	local grouping — no source_id
UnitType	unit_types	Country (country_id)	source_id, SoftDeletes
Unit	units	Project, UnitType, ProjectPhase (nullable)	source_id, is_published (+ broker / sales-man variants), is_available, auto_synced

A unit resolves its country through the whole chain — $unit->project->destination->country — canonicalised in Util::getUnitCountrySlug() (app/Actions/Util.php:77-80). That’s the same lookup the payment/CRM code uses to pick a gateway and a Dynamics org.

Publishing cascades up — a unit is public only if its whole chain is

Each level’s scopePublished requires its parent published: Unit → project.published (Unit.php:309-313) → Project → destination.published (Project.php:169-173) → Destination → country.published (Destination.php:339-343). So a unit is publicly visible only when unit + its project + its destination + its country are all is_published = true. Broker and sales-man surfaces use parallel flags (scopePublishedForBroker / …ForSalesMan). A “why isn’t this unit showing” question is almost always an unpublished parent, not the unit itself.

The inventory is a published local mirror of the CRM

Units, unit-types, and the destination/project skeleton are a local projection of Dynamics data, keyed by source_id (the Dynamics GUID). The lifecycle: fetch → store hidden → admin enriches → publish.

1. Extract — ExtractUnitsCrm sends the country header to the middleware, whose v9.1 OData inventory readers (GetUnits / GetUnitsWithParams / GetResaleUnits — see the CRM integration model) pull from Dynamics.
2. Transform — TransformUnitsCrm resolves/creates the parent Destination + Project, maps the unit_type_id, decodes the option-set codes (finishing, construction, direction, bedrooms…), converts price to USD, and stamps auto_synced (app/Actions/Unit/TransformUnitsCrm.php:62-141).
3. Load — LoadUnitsCrm.php:25-28 does Unit::updateOrCreate(['source_id' => …], $unitJson) — an upsert keyed on source_id. A newly-created unit fires NotifyRecipientsOfNewUnits. There’s also an inbound push (POST units/{sourceId} from the middleware) as an alternative to the pull.

Because Load only writes the keys Transform produced, admin enrichment survives every re-sync:

CRM-owned (overwritten each sync)	Local-owned (survives re-sync)
price, currency, dollar_price, areas, bedrooms/bathrooms, view, direction, construction_status, finishing_status, delivery_date, is_available, is_resale, name, auto_synced, source_created_at/updated_at	is_published (+ broker / sales-man), brochure, account_statement, masterplan_description, meta_title/description, reservation_fees, sales_offer_base_pdf_id, and the image/video/facility/masterplan relations

New units land hidden

units.is_published defaults false (2022_10_24_…create_units_table.php:41), so a freshly-synced unit is invisible on public APIs until an admin publishes it. destinations/projects carry a source_id but are mostly local CMS content (the sync only guarantees the row exists); project_phases and countries have no source_id and are fully local.

The transactional rows

Only a few tables record what a user did. They all hang off a single polymorphic payment ledger, transactions:

Model	Table	What it is
UnitSale	unit_sale (singular)	A reservation/purchase of a unit — the local mirror of a CRM sale. status (UnitSaleStatus) is the local workflow. crm_status mirrors the CRM’s status but is inconsistently typed — the sale webhook stores the raw CRM integer (UpdateSaleRequest.php:13 validates it as integer), while other paths write literal strings ('contracted', CreateLeadSalesInvoice.php:47) and some queries compare it as 'draft'/'canceled'. Lean on status; treat crm_status as untyped — see the KT tracker.
UnitAddonSale	unit_addon_sale	An add-on purchase against a unit.
EOIRequest	eoi_requests	A paid Expression of Interest an agent submits — see Submitting an EOI.
Transaction	transactions	The polymorphic ledger (morphTo model → one of the three above). Holds payment_reference_id, status, type, amount, callback (raw gateway JSON), and status_after_success_transaction.
UserRequest	user_requests	The lead/interest audit row (SaveUserRequest) — a broker lead writes one on a successful submit; it holds no lead PII.

UnitSale / UnitAddonSale / EOIRequest each expose transactions() as a morphMany and cache the latest on transaction_id. The payment lifecycle on that ledger is the online payment pipeline.

State-machine catalog

The workflows are all enum-backed. This is the map; the flow pages have the full machines.

Enum	Backing	Values	Set where
TransactionStatus	string	pending · success · failure · canceled	the gateway callback path
UnitSaleStatus	string	draft · pending_info · download_form · pending_finish · done · canceled	the shopper-buy flow; CRM webhook derives draft/canceled/done
ReservedSaleStatusEnums	string	pending_odh_review · partially_reserved · reserved · closed · lost	derived, not stored — computed for the broker “my sales” listing (UnitSaleMapper.php:40-42)
Egypt/Oman/MontenegroCRMSaleStatusEnums	int	Dynamics option-set codes → the two enums above	per-country mappers; UpdateSale.php on the sale webhook
EOIPaymentStatusEnums	int	pending_payment · paid · draft · expired · collected · refunded · requires_action	CRM-owned — sent to the CRM (PaymentService.php:283), not persisted on the local eoi_requests row
OmanBrokerApplicationStatusEnums	string	the 12-state pre-registration machine	Oman broker pre-registration

Confirmed defect: Montenegro RESERVED = 7 resolves to null

MonetengroCRMSaleStatusEnums declares case RESERVED = 7 (:15), but neither mapper handles 7: getStatus() (:19-25) and getReservedStatus() (:27-35) both fall through to default => null. So a Montenegro sale the CRM marks RESERVED (7) shows a blank status in the broker listing — unlike Egypt/Oman, where 100000003 → RESERVED. (Same enum also has an orphan PENDING_ON_HEAD_OF_SALES = 100000002 and the misspelled class name Monetengro.) Filed on the KT tracker.

Local-mirror vs CRM-owned — and where the PII is

This is the table to know for support and compliance: which rows are disposable projections, and which actually hold customer identity.

Table	Kind	Holds PII?
countries, destinations, projects, project_phases, unit_types, units	Pure projections / CMS content	No
unit_sale, unit_addon_sale	Transactional (CRM references only — *_source_id FKs)	Indirect (refs only)
user_requests	Lead/interest audit (references the user + unit-type/destination)	No inline PII
eoi_requests	Transactional	Yes — name, phone, email stored inline (EOIRequest.php:16-18)
transactions	Payment ledger	Possibly — callback holds the raw gateway payload; treat as sensitive

Two things this tells you

1. Most of the DB has no PII — the inventory is CRM/CMS content you can freely inspect, log, and re-sync. 2. The lead-write flow deliberately keeps PII out of the portal (a broker lead’s name/ phone/email are not stored locally — see Broker submits a lead); the two places customer identity does land locally are eoi_requests (inline) and transactions.callback (gateway payload). Guard those two.

Where to go next

The CRM integration modelWhere the mirror's data comes from — the v9.1 OData readers that feed the ETL.

Shopper buys a unitThe UnitSaleStatus machine in motion, and the local-mirror-vs-CRM split at work.

ArchitectureHow these tables sit inside the wider system.

Verified, not guessed

Every claim on this page is traced to a model, migration, or action in the current backend (see the evidence in docs-site/_evidence/data-model.md) — the publish cascade, the ETL upsert on source_id, the polymorphic Transaction ledger, the state-machine set-sites, and the PII table. No customer PII is reproduced; only the PII-bearing columns are named. There are no open questions here; the Montenegro mapping gap is filed on the KT tracker.