# The two CSV importers

**Portal:** Manage · **Two importers, two pages, two permissions:**

| Importer | Where | Route names | Gate |
|---|---|---|---|
| **A — project info** | the Projects catalogue (`?view=projects`) | `manage.sales-projects.import-preview` / `.import` | `view-projects` + **`manage-projects`** |
| **B — legacy bookings** | **a project's Show page** | `manage.sales-projects.bookings.import-preview` / `.bookings.import` | `view-projects` + **`manage-leads`** |

Part of the [Engagements & Bookings handbook](/docs/modules_handbook/manage/engagement/readMe.md).

⚠️ **The booking import is per-project and lives on the Show page**, not on the index. Older
documentation says the index in two places; it is wrong.

Both share one reader ([`MemberCsvImport`](/app/Imports/MemberCsvImport.php)) and one file rule —
`required · file · mimes:csv,txt,xlsx,xls · max:10240` (10 MB) — and both are **upload → preview →
confirm**, with nothing held in the session: each step re-parses the file from scratch.

---

## A — Project info

Creates plain `origin = custom` projects so a whole price list can be entered at once.

### The columns

Five canonical fields, matched by **alias** so the header spelling and order do not matter. The
first alias present in the header wins.

| Field | Aliases (after slugging) |
|---|---|
| `name` | `project_name` · `name` · `project` · `development` · `property` |
| `price_from` | `price_from` · `from` · `min_price` · `price_min` · `starting_price` · `price_start` |
| `price_to` | `price_to` · `to` · `max_price` · `price_max` · `ending_price` · `price_end` |
| `commission_rate` | `commission_rate` · `commission` · `rate` · `comm_rate` · `commission_percent` · `commission_pct` |
| `commission_basis` | `commission_basis` · `basis` · `commission_base` · `comm_basis` · `price_basis` · `commission_on` |

Amounts are stripped of everything but digits, `.` and `-`, so `"RM 650,000.00"` → `650000` and
`"3.5%"` → `3.5`.

**`commission_basis` accepts `SPA` / `Net`** — the word or the raw code, case- and
separator-insensitive. ⚠️ **A blank or unrecognised cell falls back to Net**, the same default the
Add-Project form and the column itself use. The field is never null.

### The three outcomes

| Outcome | When |
|---|---|
| `create` | a name not already present |
| `skip` | **the name already exists — the old project is KEPT and nothing is written** |
| `invalid` | the row has no project name |

"Skip" is the right word: the admin chose *keep*, not *update*. ⚠️ **A file listing the same NEW name
twice creates it once** — the first row marks the name as seen and the rest skip.

⚠️ **Only `origin = custom` projects count as duplicates**, and the set is **`GroupScope`-applied**.
So a catalogue project with the same name does not block a create, and a grouped admin only collides
with their own group's projects. Soft-deleted projects are excluded.

### The write

Through [`SalesProjectRepository::create()`](/src/Engagement/Repositories/SalesProjectRepository.php),
which forces `origin = custom` and `status = active`, and stamps `group_id` from the **actor** — so
there is nothing to authorise on the target and the controller does no group check.

⚠️ **A project created here has no developer, no location and no catalogue link**, so its bookings
derive a **null commission** until an admin sets a rate.

### A valid file

```
Project Name,Price From,Price To,Commission Rate,Commission Basis
Sutera Avenue @ KLCC,650000,1170000,3.5,Net
The Birch,480000,820000,4,SPA
```

Minimum viable — the name is the only field that decides validity:

```
Project Name
Beta Residence
```

The modal offers that sample as a download, and **auto-runs the preview** the moment a file is
picked. Confirm is disabled while nothing would be created.

Pinned by `SalesProjectsIndexTest::test_project_import_creates_new_names_and_skips_existing` and
`..._dedupes_a_repeated_new_name_within_the_file`. ⚠️ There is **no unit test for
`ProjectRowMapper`** (the booking mapper has one).

---

## B — Legacy bookings

Each row becomes a **lead + engagement + booking** on one project. This is how the historical
property-bookings export was brought in.

**The project is FIXED by the URL.** The controller resolves it by uuid, re-checks
`GroupScope::allows()` with a 403, and passes its name into the mapper as `$forceProperty` — so
**the file's own `property` column is ignored entirely**. Because the `legacy_ref` fingerprint folds
`property` in, the SAME booking row can be imported under two different projects without a false
duplicate.

⚠️ It is gated by **`manage-leads`**, not `manage-projects`: the import creates leads and
engagements, which is a lead write.

### The columns

Nineteen canonical fields. Identity: `name` · `email` · `phone` · `property`. The booking record:
`unit` · `block` · `floor` · `built_up` · `price` · `spa_price` · `net_price` · `booking_fee` ·
`booking_no` · `status` · `booking_date` · `spa_signed` · `lo_signed` · `bank` · `loan_margin`.

Normalisers worth knowing:

- **`email`** is lower-cased and run through `FILTER_VALIDATE_EMAIL` — **an invalid address becomes
  null**, not an error.
- **`phone`** is reduced to digits only.
- **`built_up`** is rounded to a whole int (`"850 sqft"` → `850`).
- **`floor`** stays a **string**, not an int.
- **`spa_signed` / `lo_signed`** land on `spa_signed_at` / `lo_signed_at`; both are `date` columns.
- ⚠️ **`has_identity`** = an email, **or** a phone of **at least 7 digits** — the same junk floor
  `LeadLinker` uses.

⚠️ **The detection ORDER is load-bearing.** `spa_price` / `net_price` are listed *after* the legacy
`price`, and `price` no longer claims the `spa_price` alias — so a `SPA Price` header lands on
`spa_price`, not on the legacy column.

**Dates are parsed strictly first.** `d/m/Y H:i:s` / `d/m/Y H:i` / `d/m/Y` are each accepted **only
when the reparse round-trips exactly**, so `13/01/2026` can never be silently read as an American
date; then `Carbon::parse()` as the fallback (so ISO works); any exception yields null.

**No commission and no rate.** Commission is derived, so the CSV carries no such column — and a bare
CSV `price` **IS the net price**: it is written to `net_price`.

### The project-alias merge map

`KLCC TERA (Member)` and `KLCC TERA (Public)` fold into one `KLCC TERA`. It is an **explicit map,
not a bracket-stripping regex**, so a real bracketed name like `Hyde (i-City)` is left alone.

⚠️ **In the per-project import this map is dead code** — `$forceProperty` short-circuits before it is
ever consulted. It only matters for an unscoped call, which the action still supports but nothing
calls today.

### One status column, and the matrix it drives

**A single `status` column**, valued as one of the engagement statuses, matched case- and
separator-insensitively (`appointment set` = `Appointment_Set` = `APPOINTMENTSET`).

⚠️ **The lookup is built from `Engagement::ALL_STATUSES`, not `STATUSES`** — deliberately, and the
code says so: *"an importer ingests legacy exports, so it must still understand a legacy status word
even though the UI can no longer set it."* **This makes the importer the ONE lane that can still mint
a retired status 5 (Negotiating) or 7 (Following Up)**, because it calls `changeStatus()` directly and
bypasses `StatusRequest`'s `Rule::in`. Pinned by
`LegacyBookingRowMapperTest::test_each_of_the_nine_statuses_parses_by_name`, which asserts all nine
words parse — so rebuilding the lookup from `STATUSES` would break that test.

Quiet aliases: `completed` / `won` → Converted; `cancelled` / `canceled` → Lost. ⚠️ **A blank or
unknown cell defaults to Booked** — a booking row is, by default, a booked unit.

The booking's own two columns are then **derived** from that engagement status:

| Engagement status | `bookings.status` | `bookings.spa_status` |
|---|---|---|
| Converted | Completed | Signed |
| Lost | Cancelled | Not Signed |
| everything else | Active | Pending |

⚠️ This is the **only** writer of `bookings.spa_status` — and nothing reads it. See
[booking.md](/docs/modules_handbook/manage/engagement/booking.md).

### `legacy_ref` — the idempotency fingerprint

```php
sha1(email | phone | property | unit | price | status_raw | booking_date | name)
```

Eight components, and the omissions matter: `spa_price`, `net_price`, `booking_fee`, `booking_no`,
`block`, `floor`, `built_up`, `bank`, `loan_margin`, `spa_signed` and `lo_signed` are **not** in it.

⚠️ **The importer is skip-only, never update.** Changing any of those fields and re-importing
produces the *same* ref, so the row is **skipped, not updated**. Changing one of the eight produces a
*different* ref, so it imports as a **new booking**.

⚠️ `price` here is the **legacy `price` field**, kept in the fingerprint on purpose even though the
column was dropped: it folds the CSV's price VALUE, so re-imports keep matching rows imported before
`price` became `net_price`.

⚠️ `status_raw` is the **raw lower-cased cell**, not the resolved constant — so `"Booked"` and
`"booked"` fingerprint the same, but `"Booked"` and `""` (both resolving to Booked) do not.

A repeat buyer's several rows all land on **one engagement** as several bookings, which is one of the
two states that break the status ⇄ booking invariant.

### Backdating, and its two limits

An imported row's `created_at` — on both the newly opened engagement and the booking — is set to the
CSV **booking date**, so the pipeline reads as happening when it happened rather than when the file
was uploaded. A blank booking date keeps today's timestamp. `last_activity_at` stays `now()`, so
imported rows still sort as recent.

⚠️ **Two limits.** Because the import is skip-only, **an already-imported row is never re-dated** on a
re-run. And **a backdated `open()` deliberately gets NO standing team** — a historical import is not
a new deal, and today's staff never worked it — so imported engagements have **zero
`engagement_assignments`**, and therefore no commission split until somebody assigns one.

Pinned by `SalesProjectsIndexTest::test_import_backdates_the_engagement_and_booking_to_the_booking_date`
and `..._without_a_booking_date_keeps_todays_timestamp`.

### Identity runs through `LeadLinker`

No hand-rolled identity code: the action goes through the shared
[`LeadLinker`](/docs/modules_handbook/shared/lead-linking/readMe.md), inheriting tolerant phone
matching, staff exclusion, the `NON_MEMBER` role stamp, the `lead_funnels` attribution row and the
phone-placeholder-name upgrade. **Preview calls `preview()`, apply calls `link()` / `linkConfirmed()`
— one classifier, so a dry run and a real run cannot disagree.**

### The preview answers what an admin has to sign off on

Five outcomes: `import_new` · `import_existing` · `no_identity` · `staff` · `duplicate`.

**`import_new` vs `import_existing` are separate outcomes** because creating a person and linking one
who is already here are very different things to approve. ⚠️ **The bucket asks about the LEAD**, so an
account that exists *without* a lead still counts as new.

**Mismatches** flag where the file disagrees with the CRM — `identity` (the gate's email-wins
conflict), `name`, `email`, `phone`. These rows **still import**; by default the CRM value wins and
the file's is dropped, because backfill only ever fills a *blank* field. ⚠️ The phone mismatch is
**suppressed on an identity conflict**: the phone was already reported as belonging to somebody else,
and repeating it reads as two problems when it is one.

**Name — and only name — is adoptable.** An admin can tick a mismatched name to overwrite the CRM's
with the file's. Safe because a name is not an identity key — nothing signs in, resolves or matches
on it — so the worst case is a wrong label, which is what the admin is fixing.

⚠️ **Email and phone are NOT adoptable here**, and that is enforced *structurally*, not by
convention: `ADOPTABLE_FIELDS = ['name']`, and the ticked list is intersected against it **twice** —
once in the Form Request's `decisions()` and again in the action's `resolveDecisions()`. No payload
can push an identity-key overwrite through this path however it is crafted. They are login
credentials (a live magic-link target and a live OTP target), so a bulk overwrite belongs on the
per-lead edit form, not a 500-row import.

**Same-person name collision.** Two rows renaming the SAME person to DIFFERENT names adopt
**neither** — the tie is refused and flagged. CSV order is an export artefact, not a statement of
which name is right, so last-write-wins would hand the outcome to row position, a choice the admin
never made. The check is **pure over (rows, decisions) with no DB**, so preview and apply compute the
identical set; identical names (case- and space-insensitive) are not a collision and adopt once per
person per run.

### Rows with no email and no phone

Such a row is **unfilable by design**: the gate has no key to converge on, so it cannot promise one
person = one account, and it refuses. The importer does not weaken that refusal — it lets an **admin
override it for one named row**, with two decisions:

- **`link`** — tick a lead from `LeadLinker::suggestByName()`. ⚠️ **A name never resolves identity on
  its own**; the suggestion is a proposal and the tick is the authority. The uuid is re-resolved
  under the caller's own `LeadVisibility` scope, so a hand-written payload cannot link outside it,
  and an unresolvable one is silently dropped rather than trusted.
- **`placeholder`** — mint `LeadLinker::placeholderEmail($name)`, per-person unique so ticking N rows
  never merges N humans into one lead. ⚠️ A row with **no name** drops the decision.

⚠️ Both decisions are keyed by **`legacy_ref`** and applied to a **COPY of the row, never to the
fingerprint** — so a decided row stays idempotent on re-run and the preview's choice still points at
the same row when the file is parsed again on confirm.

Untouched rows are skipped, exactly as before.

Name suggestions are cached per distinct name and **capped at 200 lookups** — a LIKE query per
unresolved name is fine for the handful a real export leaves behind, but a pathological file must not
turn preview into thousands of queries.

⚠️ **Decisions ride as a JSON string**, not nested form fields, so the two callers send them
identically — the preview posts raw `FormData`, the confirm goes through Inertia — instead of relying
on both to bracket-encode a nested map the same way.

### Where preview and apply are NOT byte-identical

Minor but real, and worth knowing before trusting a count:

- duplicate detection is **batched** in preview and **per row** in apply;
- preview classifies each person once against the DB **as it was before the run**, while apply
  mutates as it goes (both compensate in their counters);
- the stats keys differ (`importable` / `unresolved` vs `imported` / `skipped`);
- **nothing pins the file version** — a different file uploaded on confirm is simply re-classified;
- the modal's running total is a **client-side estimate**; the admin must press *"Re-check with your
  decisions"* to get the server's number.

### A valid file

```
buyer name,email,phone,unit,block,floor,built-up,spa price,net price,booking fee,booking no,status,booking date,spa signed,lo signed,bank,loan margin
Tan Wei Ming,tanweiming@example.com,60123456789,A-12-3,B,12,850,850000,820000,20000,BK-001,Booked,15/07/2026,20/07/2026,25/07/2026,Maybank,90
```

Minimum viable row — an email **or** a ≥7-digit phone is all that is strictly required:

```
buyer_name,email,phone,unit,price
Tan Wei,tanwei@example.com,60123456789,A-1-1,500000
```

(no `status` → Booked; no `booking_date` → today's timestamp; `price` lands in `net_price`.)

A row with **neither** email nor phone imports only if the admin ticks a decision.

## Related files

**Importer A — project info**
- [app/Http/Controllers/Manage/Engagements/ProjectsImportController.php](/app/Http/Controllers/Manage/Engagements/ProjectsImportController.php)
- [app/Http/Requests/Manage/SalesProjects/ProjectImportRequest.php](/app/Http/Requests/Manage/SalesProjects/ProjectImportRequest.php)
- [app/Support/ProjectImport/ProjectRowMapper.php](/app/Support/ProjectImport/ProjectRowMapper.php) — pure, no DB
- [app/Actions/ImportProjectsAction.php](/app/Actions/ImportProjectsAction.php) — `OUTCOME_CREATE` / `_SKIP` / `_INVALID`
- [resources/js/Pages/Manage/SalesProjects/Partials/ProjectImportModal.vue](/resources/js/Pages/Manage/SalesProjects/Partials/ProjectImportModal.vue)

**Importer B — legacy bookings**
- [app/Http/Controllers/Manage/Engagements/BookingsImportController.php](/app/Http/Controllers/Manage/Engagements/BookingsImportController.php)
- [app/Http/Requests/Manage/Engagements/BookingImportRequest.php](/app/Http/Requests/Manage/Engagements/BookingImportRequest.php) — extends the leads `ImportRequest` for the file rule, adds `decisions` and whitelists its shape **by rejection**
- [app/Support/BookingImport/LegacyBookingRowMapper.php](/app/Support/BookingImport/LegacyBookingRowMapper.php) — pure column detect + normalize + alias merge + status matrix + fingerprint
- [app/Actions/ImportLegacyBookingsAction.php](/app/Actions/ImportLegacyBookingsAction.php) — `preview()` / `apply()`, `OUTCOME_*`, `DECISION_*`, `ADOPTABLE_FIELDS`, `mismatchesFor()`, `suggestionsFor()`, `nameAdoptCollisions()`
- [src/Engagement/Repositories/BookingRepository.php](/src/Engagement/Repositories/BookingRepository.php) `importLegacy()` · [EngagementRepository.php](/src/Engagement/Repositories/EngagementRepository.php) `open()`
- [resources/js/Pages/Manage/SalesProjects/Partials/BookingImportModal.vue](/resources/js/Pages/Manage/SalesProjects/Partials/BookingImportModal.vue)

**Shared** — [app/Imports/MemberCsvImport.php](/app/Imports/MemberCsvImport.php) (the reader) · [src/Lead/Services/LeadLinker.php](/src/Lead/Services/LeadLinker.php)

**Migrations** — `2026_07_17_100001_add_legacy_fields_to_bookings_table` (adds `legacy_ref`, widens the money columns to `decimal(12,2)` because the export carries cents) · `2026_07_26_100001` (adds `projects.commission_basis`)

**Tests**
- [tests/Unit/BookingImport/LegacyBookingRowMapperTest.php](/tests/Unit/BookingImport/LegacyBookingRowMapperTest.php) — header detection, the SPA/net ordering guarantee, all nine status words, case/separator insensitivity, the blank-defaults-to-Booked rule, the derived booking/SPA matrix, cents preservation, `assertArrayNotHasKey('commission', …)`, the `Hyde (i-City)` non-merge, and fingerprint stability. ⚠️ It does **not** exercise `$forceProperty`, the date fallback, or `property_raw`
- [tests/Feature/Engagement/BookingImportDecisionsTest.php](/tests/Feature/Engagement/BookingImportDecisionsTest.php) — the load-bearing negative: *a name alone never resolves a person, even on an exact match*. If that ever flips, a name has become an identity key and any file naming a real customer can graft bookings onto their account
- [tests/Feature/Engagement/SalesProjectsIndexTest.php](/tests/Feature/Engagement/SalesProjectsIndexTest.php) — both importers' create/skip behaviour and the backdating rules

## Related chapters
[sales-projects.md](/docs/modules_handbook/manage/engagement/sales-projects.md) — the project entity and the two pages ·
[booking.md](/docs/modules_handbook/manage/engagement/booking.md) — the booking fields and `importLegacy()` ·
[lifecycle.md](/docs/modules_handbook/manage/engagement/lifecycle.md) — `open()`'s three parameters ·
[Lead Linking](/docs/modules_handbook/shared/lead-linking/readMe.md) — the identity gate