# Project Catalogue — databases, connections and distribution

> 📍 Part of the project catalogue doc set — the map and routing table is
> [start-here.md](/docs/modules_handbook/shared/project-catalogue/start-here.md).

Companion to [the module doc](/docs/modules_handbook/shared/project-catalogue/readMe.md),
which explains what the catalogue *is* (merge, classification, completeness, publication,
federation). This file explains **where its rows physically live, how a second deployment
gets them, and the commands that move them** — the layer underneath everything that doc
describes.

It exists because that layer was designed but never written down. `config/database.php`,
[`CutoverToMasterCatalogue`](/app/Console/Commands/CutoverToMasterCatalogue.php),
[`OffsetLocalCatalogueIds`](/app/Console/Commands/OffsetLocalCatalogueIds.php) and the module
doc all point at `docs/planning/master-catalogue-shared-database-plan.md`, and **that file has
never been committed to this repository** — `git log --all` has no record of it. Four
load-bearing references to a document nobody on the team has ever been able to open. What
follows is that knowledge, reconstructed from the code and verified against the live
databases on 2026-09-11.

---

## 1. The four connections

All declared in [`config/database.php`](/config/database.php). Catalogue-family Eloquent
models pin themselves with `protected $connection = CatalogProject::CONNECTION` (the string
`'catalogue'`), so **which database a `CatalogProject` reads is a deployment setting, not a
code change**.

| Connection | Env prefix | Points at | Who uses it |
|---|---|---|---|
| **`catalogue`** | `MASTER_DB_*`, or `DB_*` when collapsed | the catalogue the APPLICATION reads | every catalogue model, `DB::connection('catalogue')`, validation rules prefixed `catalogue.` |
| **`catalogue_master`** | `MASTER_DB_*` — **always**, never collapsed | the real shared master | one caller: [`catalogue:mirror-from-master`](/app/Console/Commands/MirrorCatalogueFromMaster.php) |
| **`catalogue_staging`** | `MASTER_DEV_DB_*` | `master_projects_dev`, a scrape staging schema | **nothing in this codebase today.** Declared for promotion tooling that has not landed. Treat an empty `MASTER_DEV_DB_*` as normal |
| **`reference`** | `REFERENCE_DB_*` | an EXTERNAL scraped-data database | ingestion only. **Nothing in this codebase ever WRITES to it.** Two different access patterns — see below |

**The `reference` connection is used two ways, and only one of them is "read live".**
The provider ADAPTERS read it live and never copy it — `HkReferenceAdapter` (the base of the
House730 and two Centanet adapters), `PropertySifuMyAdapter` and `PropertyFinderUaeAdapter`,
each naming it as `providers.*.connection`. But two COMMANDS copy wholesale from it into the
catalogue connection: `market:import-hk` upserts the entire HK market layer (transactions alone
are ~2.35M rows) and `market:import` copies `edgeprop_projects`, `airbnbs` and
`propsense_agents`. So "read live, never imported" is true of ingestion and false of those two.

`catalogue_master` exists precisely **because** `catalogue` can be collapsed: the mirror has
to reach the real master even on a box whose application is deliberately reading a local
copy. Give it a **SELECT-only** account — nothing in this codebase writes over it, and a
read-only credential is what makes that a guarantee rather than a promise. It additionally
opens its session `READ ONLY` at the server (`PDO::MYSQL_ATTR_INIT_COMMAND`), so a mistake
fails loudly instead of landing in the catalogue every platform reads.

---

## 2. Two modes: read the master LIVE, or keep a COPY

One env variable decides, and it changes nothing else:

```
CATALOGUE_USE_DEFAULT_CONNECTION
```

| | **Copy** (set) | **Live** (absent) |
|---|---|---|
| `catalogue` resolves to | `DB_*` — this deployment's own database | `MASTER_DB_*` — the shared master |
| `catalog_*` rows live | in `petav3`, beside the CRM | in `master_projects`, on the master host |
| Refreshed by | `catalogue:mirror-from-master` | nothing — reads are always current |
| Catalogue **writes** | work | fail, if the account is SELECT-only |
| Catalogue **migrations** | apply to the tables the app reads | apply to unused local tables; the app reads a master that lacks the new column |
| Cross-database bugs | invisible — there is only one database | surfaced |

**Production runs COPY.** Its own `petav3` holds a full `catalog_projects` (37,692 rows on
2026-09-11, against the master's 38,632), refreshed from the master.

> ⚠️ **OPEN — this sentence and the module doc disagree, and code cannot settle it.**
> [readMe.md](/docs/modules_handbook/shared/project-catalogue/readMe.md) *Master catalogue
> connection* says "dev and tests resolve to the local database and production resolves to the
> shared `master_projects` schema" — the opposite of the line above. `config/database.php`
> is purely env-driven, so only someone holding production's `.env` can say which is true.
> **Do not assume either while debugging a production catalogue question; check the flag on
> the box.** Note also that "dev resolves to the local database" is NOT true of every dev box:
> a checkout with no `CATALOGUE_USE_DEFAULT_CONNECTION` line reads the master LIVE, which is
> exactly the configuration that surfaces the cross-database bugs §6 lists (and the reason two
> of them were found at all). Whoever resolves this should correct BOTH files and the
> `CatalogProject.php:43-45` class comment, which repeats the readMe's wording.

The test suite runs collapsed: [`phpunit.xml`](/phpunit.xml) forces the flag on, and
[`TestCase::shareCatalogueConnection()`](/tests/TestCase.php) then aliases the whole
Connection OBJECT so both handles share one PDO session and one transaction.

**A deployment keeps a copy when catalogue features are built on it** — a migration adding a
column to a catalogue table can only run against a database this deployment may write. The
cost is that the copy goes stale, and the mirror is what pays it.

### ⚠️ The flag is turned off by DELETING the line, never by `=false`

`env()` in this project is **CakePHP's, not Laravel's**.
`vendor/cakephp/core/functions.php` is autoloaded before
`vendor/laravel/framework/src/Illuminate/Support/helpers.php` (positions 31 and 41 in
`vendor/composer/autoload_files.php`) and wins the `function_exists` guard. CakePHP's version
returns the **raw string** and never maps `"true"`/`"false"` onto booleans —
`env('APP_DEBUG')` is the string `"true"`, and `env('CATALOGUE_USE_DEFAULT_CONNECTION')` set
to `false` is the non-empty, **truthy** string `"false"`.

`config/database.php` reads it as `env(…, false) ? collapse : master`, so `=false` still
collapses. Absent, the ternary sees the boolean default and reads the master. The same trap
applies to **every** `env('X', false) ? … : …` in this codebase and to every truthiness check
on a config value fed by `env()`.

---

## 3. What lives where

The master owns **29 tables plus its own `migrations`** (verified 2026-09-11):

```
catalog_ai_contents        catalog_floor_plan_sources   catalog_units          market_airbnbs
catalog_building_sources   catalog_floor_plans          catalog_unit_sources   market_area_benchmarks
catalog_buildings          catalog_identity_locks       catalog_unit_valuations  market_area_profiles
catalog_doc_pages          catalog_media                countries              market_catalysts
catalog_floor_plan_analytics  catalog_project_developers  data_providers        market_price_indices
                           catalog_project_sources      developers             market_property_agents
                           catalog_projects             developer_relationships  market_schools
                           catalog_sync_runs            media                  market_transactions
```

Three of those are read over the **DEFAULT** connection even though the master has them,
because their models set no `$connection`: **`countries`** (`Src\Common\Country`),
**`data_providers`** (`Src\Common\DataProvider`) and — via `Src\Common\Media` — the site's own
half of **`media`**. They therefore need rows in the local database whichever mode a box is
in. `Src\Common\MasterMedia` is the same table read on the `catalogue` connection; that is the
split, and it is why `media` can never simply be emptied on a box that has its own uploads.

`catalog_identity_locks` is the opposite case: it looks like shared reference data, but it is
read through `Developer`, which IS pinned to the catalogue connection, so its single mutex row
(`catalog-developer-identity`) is expected on the MASTER. Two things follow, and both bite
before any write is attempted:

- **A missing row throws, it does not degrade.** `Developer::withIdentityLock()` raises
  *"Developer identity lock row is missing; run migrations."* before the callback runs, so on a
  master without that row every developer create/update fails — including the ones that would
  otherwise have been perfectly legal local writes.
- ⚠️ **The lock is not taken on the connection being written.**
  `createCanonicalIdentityOn()` does not forward its `$connection` to the lock, and none of the
  four callers pass `$onConnection`. So an admin creating a developer takes the mutex on the
  MASTER while writing the row to the LOCAL database. On a SELECT-only master the
  `lockForUpdate()` is a read and succeeds, so this does not announce itself — but the mutex is
  not guarding the database the write lands in.

**The per-unit layer is empty on purpose, not retired.** `catalog_buildings`, `catalog_units`,
`catalog_unit_valuations` and their attribution tables `catalog_building_sources` /
`catalog_unit_sources` hold block → unit → valuation observation, each attributed to the source
that contributed it. They are written ONLY by a second, optional ingestion stream
(`UnitValuationIngestionService`), and **no adapter implements that interface yet** — only the
interface, the orchestrator branch and the service exist. Empty here means "no producer", so do
not drop them and do not read their emptiness as a sign the feature was removed.

**These `catalog_*` / `market_*` tables are petav3-owned and are NOT on the master** — they
stay in the local database in both modes:

| Table | What it is |
|---|---|
| `catalog_analysis_snapshots` | the saved deep-analysis payload per catalogue floor plan, so a project page renders numbers instead of re-running the engine. Written by `catalogue:precompute-analysis` |
| `catalog_legacy_crosswalks` | the migration ledger mapping every retired Analyze row to its catalogue outcome (migrated / excluded / unresolved) |
| `catalog_publish_reviews`, `catalog_review_assignments`, `catalog_review_panels` | the publication review workflow |
| `catalog_floor_plan_key_sizes`, `catalog_project_highlights` | per-deployment derived data |
| `catalog_vr_bakes` | the [VR360](/docs/modules_handbook/shared/project-catalogue/vr360/readMe.md) bake queue |
| `sites`, `site_catalog_projects`, `market_sites`, `market_site_countries` | the site listing layer — WHICH published records a given deployment lists |

The rule behind the split: **the master holds what is true about the world; the deployment
holds what is true about this deployment.** A review decision, a precomputed analysis, a
listing choice and a migration ledger are all the second kind.

---

## 4. Id spaces — why two databases never collide

Both sides allocate `AUTO_INCREMENT` independently, and a column such as
`projects.catalog_project_id` may point at either kind of row. Three mechanisms keep `42`
unambiguous:

- **`catalog_projects.origin`** — `CatalogProject::ORIGIN_OWN` (`'own'`) vs `ORIGIN_MIRROR`
  (`'mirror'`). A mirror is a frozen copy of a master row kept so existing bookings, leads and
  analyses still resolve; it is not a second project. Marking them wrong makes the federation
  count the whole mirrored catalogue as local, duplicating every list and doubling every total
  — and the uuid dedupe hides that on screen, which is what makes it dangerous rather than
  obvious.
- **`catalogue:offset-local-ids`** raises this platform's own counters to
  `OffsetLocalCatalogueIds::LOCAL_ID_FLOOR` = **1,000,000,000**. The master is in the tens of
  thousands and would need a billion rows to reach it.
- **`MirrorCatalogueFromMaster::MEDIA_ID_OFFSET`** = **50,000,000**. `media` is the one table
  the mirror PROTECTS rows in — because it is the one table read over BOTH connections (§3), so
  emptying it would break this site's own uploads. A refresh keeps them (identified by a uuid
  the master has not got, never by id) and moves a colliding master row to `id + 50,000,000`.
  **No other table gets this protection** — see the warning in §5.

**Identity is the uuid, not the id.** Everything that travels between deployments keys on
`uuid`; foreign keys inside one database key on `id`.

---

## 5. How a catalogue reaches another deployment — two generations

### Generation 1: a versioned JSON package

Point-to-point, no shared infrastructure. Used for PetaV3 → PetaHK.

| Command | Purpose |
|---|---|
| `catalogue:export <file> [--country=HK] [--skip-orphans]` | write a versioned package. Aggregates keyed by stable **uuid**, never numeric ids; carries country metadata, provider sources, floor plans and manual-override metadata. **No deployment-local workflow data** (projects, focus projects, owners, campaigns, leads) ever travels. ⚠️ **It aborts and writes NO file** if any media or AI-content row cannot be represented — missing storage record, or a source that moved to another canonical. Every such row is printed either way; `--skip-orphans` omits them and ships anyway. Not hypothetical: the shipped MY package needed it |
| `catalogue:import <file> [--dry-run]` | idempotent upsert — projects by uuid, sources by provider code + external id, floor plans by uuid; missing country/provider lookups created from package metadata. Whole package in ONE transaction; schema version validated before any write. Destination working projects untouched |
| `catalogue:verify-sync` | read-only proof that the destination holds what the package shipped. **The package is the specification** — it walks the package and asks the local database whether it agrees, field by field, rather than re-deriving what an export *would* produce. Safe on production, either side, any time |
| `catalogue:explain-drift` | reads verify-sync's JSON reports and says *why* each field drifted, printing the destination's own row beside the package's claim. Exists because a count cannot distinguish data loss from better normalisation — `developers[<uuid>] MISSING locally` is usually the same company under a different uuid on each deployment |

Loading a package is memory-hungry: `json_decode` costs roughly **8× the file size** (a 271 MB
package peaks at 2.2 GB), so every command that reads one uses
[`GuardsPackageMemory`](/app/Console/Concerns/GuardsPackageMemory.php). Without it PHP's CLI
`memory_limit` of `-1` lets the kernel OOM-killer pick a victim instead — on a web box that is
php-fpm or MySQL, not the command that caused it. That is how a catalogue transfer took a site
down on 2026-08-11.

### Generation 2: one shared master database

Every deployment reads `master_projects`, populated upstream. Platforms never write it **as a
matter of policy**, which is why the credential is SELECT-only.

| Command | Purpose |
|---|---|
| `catalogue:cutover [--apply]` | put a deployment into shared-catalogue mode AFTER its connection is pointed at the master. Marks its own catalogue rows as **mirrors** (identified by uuid against the master, not assumed) and clears its id space. A migration cannot do either: a deployment migrates BEFORE its env is flipped, so the guards correctly no-op and the migration is recorded done forever. Safe to re-run |
| `catalogue:offset-local-ids [--apply]` | raise local counters above the master's (§4). Idempotent; refuses to lower a counter. Same "why a command and not a migration" reason — the local counter was once found at 53,691 against a master max of 53,690, one row from a collision |
| `catalogue:mirror-from-master [--apply] [--table=] [--force]` | refresh a deployment's own COPY from the master. Compares row count + a whole-table `crc32` checksum on the columns the two sides SHARE (so an in-flight local migration does not report every table stale forever), then replaces each stale table. Refuses to run when the deployment reads the master live, unless `--force`. ⚠️ **"Replaces" means wholesale — see the warning below** |

> ⚠️ **A mirror DELETES every local-only row in each stale table, except in `media`.**
> `MirrorCatalogueFromMaster` empties the local table and re-inserts the master's rows, and
> `media` is the ONLY table given row-level protection (`$protect = $table === 'media' ? … : []`,
> then a bare `delete()` for everything else). So in COPY mode, a project this platform created
> itself — written to the DEFAULT connection with `origin = 'own'` by
> `CatalogProject::creationConnection()`, i.e. anything an admin added through the normal Manage
> form — is **destroyed** by the next `--apply`, along with its floor plans, media rows and
> sources. Nothing warns you; the row count simply comes back matching the master.
>
> Before mirroring a box that has its own catalogue rows, check for them:
> `select count(*) from catalog_projects where origin = 'own'`. If that is not zero, either
> export them first (`catalogue:export`) or limit the run with `--table=`.

---

## 6. Rules that are not cosmetic

- **Never JOIN across the `catalogue` connection and the default one in SQL.** It happens to
  work while both resolve to one database and fatals when they do not. Pluck ids/uuids from
  one side, `whereIn` on the other. Working-project search over canonical facts is FEDERATED
  in `Src\Property\Project` (`matchingCanonicalIds` + a bounded id→name CASE), never a
  subquery into `catalog_*`.
  - ⚠️ **`whereHas` on a `FederatedBelongsTo` is one of these joins, and it does not even
    fatal.** The relation federates `getResults()` / `getEager()` / `addEagerConstraints()` /
    `fallbackQuery()` only — it never overrides `getRelationExistenceQuery()`, so an existence
    query is stock `BelongsTo` and emits a same-connection correlated subquery against an
    unqualified `catalog_projects`. It does not error, because every site keeps its own
    `catalog_projects` table (§6.1 runs catalogue migrations against both connections); it
    silently reads the WRONG one and returns nothing for a master-owned project. **Invisible
    on production** (collapsed) and **invisible to the suite** (phpunit forces the collapse) —
    a box reading the live master is the only thing that finds it.
    **Both live callers were fixed on 2026-09-14** and are the worked examples of the
    replacement below: `ProjectDetailController::approvedVrPanorama()` (the public 360° tab)
    now matches `catalog_vr_bakes` on `catalog_project_id` **and**
    `(catalog_project_uuid IS NULL OR = the resolved project's uuid)` against the project the
    page already federated; `App\Http\Requests\Manage\RentalEstimate\SubmissionQueryRequest::filterSearch`
    (⚠️ the full namespace — there is an unrelated `Manage\PropertyMatch\SubmissionQueryRequest`)
    plucks the ids its submissions reference and passes them to
    `CatalogueFederationService::matchingReferenceIds()`.
    ⚠️ **The rule is about SITE models only.** A `whereHas('catalogProject')` on a
    catalogue-family model (`CatalogProjectSource`, `CatalogMedia` — `BuildHkDeveloperRecord`,
    `BuildHkSupplyComparison`, `StoreHouse730CatalogueMedia`, `StoreUaeCatalogueMedia`) is
    correct and must be left alone: those inherit the parent's connection through
    `KeepsRelatedConnections`, so the EXISTS is same-connection.
  - ⚠️ **Never chain a federated relation's builder either.**
    `$model->catalogProject()->firstOrFail()` (or `->get()`, `->exists()`) looks federated and
    is not: `Relation::__call` forwards straight to the PRIMARY builder, bypassing
    `getResults()`, so a project this platform created itself resolves to nothing. Use
    `$model->catalogProject`, or `->catalogProject()->getResults()` when you need the call
    form. `ProjectRepository::syncFromCatalog` did exactly this and 404'd every
    platform-created project until 2026-09-14.

- **Reach a catalogue table only through the catalogue connection.** A bare
  `DB::table('catalog_…')` or `DB::table('market_…')` silently reads the DEFAULT database —
  identical while collapsed, empty in live mode. Found and fixed this way on 2026-09-11:
  [`BuildHkUnitSizeBand`](/app/Actions/BuildHkUnitSizeBand.php) read `market_transactions`
  on the default connection while its three siblings (`BuildHkValuation`,
  `BuildHkAnalytics`, `BuildHkProjectScore`) all used `DB::connection('catalogue')`. The HK
  size band rendered empty and nothing errored. **A box pointed at the live master is the
  only thing that finds these** — production cannot, because it is collapsed.
- **Validation rules on catalogue tables carry the `catalogue.` prefix** —
  `exists:catalogue.catalog_projects,id`.
- **Children follow their parent's connection.**
  [`Concerns\KeepsRelatedConnections`](/src/Analysis/Reference/Concerns/KeepsRelatedConnections.php)
  makes a platform's own project keep its floor plans, media and developers in the site
  database. Repositories create children on the PARENT's connection.
- ⚠️ **`MasterMedia` must always be written with `::on($parent->getConnectionName())`.**
  `Src\Common\MasterMedia` pins `$connection = CatalogProject::CONNECTION`, and
  `KeepsRelatedConnections` only rewrites RELATED instances — it does not touch a direct
  `MasterMedia::create()`. So every writer spells the connection out
  (`CatalogMediaRepository::store()`, `CatalogueMediaStorageService::storeUae()` and
  `::storeHouse730()`). Get it wrong and a media row for a SITE-owned project lands on the
  MASTER, leaving `media_id` pointing at an id the site's `media` table does not have: **the
  image renders broken and nothing fails**. This has already happened in production.
- **Catalogue schema changes live in their own migration directory.** See §6.1 below — this
  is the rule most easily broken, because nothing enforces it.
- **Catalogue writes need a writable catalogue.** With a SELECT-only master every create /
  edit / merge / publish-review fails with `1142 … command denied`. That is correct, not a
  bug: an edit to a master row is seen by every platform at once, which is also why the
  domain gate (`CatalogueFederationService::canEdit`, `config/site.php`) pins editing to the
  admin domains.
- **A commanded write to the master is explicit — where it is guarded at all.** Only
  `catalogue:copy-uae-snapshots` refuses to run while the catalogue connection resolves to the
  local database, so only its target is unambiguous. The UAE **media** commands are NOT
  guarded: `catalogue:upload-uae-media-objects` never looks at the flag (it reads the default
  connection deliberately and is meant to run *with* `CATALOGUE_USE_DEFAULT_CONNECTION=true`),
  and `catalogue:store-uae-media` writes through `CatalogMedia` / `MasterMedia`, i.e. into
  whatever the catalogue connection currently points at. **Check the flag yourself before
  running either.**

### 6.1 Migrations — one file, two databases

A catalogue-family schema change is applied to **two** databases: the shared master, and
every site's own local catalogue tables (a site keeps the same shape for the projects it
owns). So those migrations live in their own directory,
[`database/migrations/catalogue/`](/database/migrations/catalogue/README.md), and **never in
`database/migrations/`**.

```bash
# the master — ONLY the Hub's deploy pipeline may run this against production
php artisan migrate --path=database/migrations/catalogue --database=catalogue

# this site's own catalogue tables — automatic, see below
php artisan migrate --path=database/migrations/catalogue
```

The second line is automatic everywhere:
[`AppServiceProvider::boot()`](/app/Providers/AppServiceProvider.php) calls
`loadMigrationsFrom(database_path('migrations/catalogue'))`, so an ordinary
`php artisan migrate` picks them up — including under `RefreshDatabase`, which would
otherwise never create the columns and would fail the suite.

Three consequences:

- **A site deploy must never ALTER the master.** `scripts/deploy-update.sh` deliberately does
  not contain the `--database=catalogue` line; it is the Hub's to run.
- **The master schema has no create-from-scratch history.** Its baseline is a seeded copy of
  the production catalogue, so this directory holds only `ALTER`-shaped migrations.
- **On a box reading the master LIVE, a catalogue migration cannot reach what the app reads.**
  The ordinary `migrate` applies it to the local tables the app is no longer using, and the
  master is reachable only through a SELECT-only account. The column will not appear until
  the Hub deploys it. **That is the single strongest reason to develop catalogue features on
  a COPY** (§2).

Which tables count as catalogue-family: `catalog_*` on the catalogue connection, `developers`,
`developer_relationships`, the `market_*` reference family, and the master's `media`,
`countries` and `data_providers`. The petav3-owned `catalog_*` tables in §3 are **not**
catalogue-family — they are ordinary migrations in `database/migrations/`.

### 6.2 Crossing the boundary without a subquery

`App\Services\Property\CatalogueFederationService` carries the four helpers that replace a
cross-connection `whereHas` / correlated subquery. All four are cheap in single-database mode
(`catalogueIsSeparate()` is false, so the ownership question short-circuits).

| Helper | Question it answers |
|---|---|
| `siteOnlyReferenceIds(array $ids)` | **"Which of these ids does the MASTER lack?"** — the only safe ownership question, because a site keeps frozen MIRRORS of master rows under the master's own ids, so "the site has this id" proves nothing. Always `[]` in single-database mode |
| `referencedRows(array $ids, ?Closure $constrain, array $columns)` | The rows behind a set of referenced ids, asked of each owning database with the SAME constraint and concatenated. The site side deliberately reads every origin rather than `local()`'s own-only rows: it must match what the relation DISPLAYS, and it is already narrowed to ids the master lacks. It re-asks `siteOnlyReferenceIds` rather than deriving ownership from the constrained result — a master row that FAILS the constraint must not be mistaken for a site-owned id |
| `matchingReferenceIds(array $ids, Closure $constrain)` | The `whereHas` replacement: of these referenced ids, the ones whose resolved row satisfies the constraint. Apply the answer as a plain `whereIn` |
| `isListedOnSite(CatalogProject $row)` | The single-row twin of `scopePubliclyListed`'s listing half, asked on the connection that owns the row — for callers that resolved the record first, like the three detail pages |

Two relation classes sit beside `FederatedBelongsTo` in
[`src/Common/Relations/`](/src/Common/Relations/):

- **[`FederatedHasMany`](/src/Common/Relations/FederatedHasMany.php)** — the hasMany twin, for
  catalogue children (floor plans) keyed by a catalogue id that may belong to either database.
  ⚠️ Only the EAGER path lives in the class. A **single** parent is handled by the declaring
  model, which builds the relation on the owning connection **up front** — that is the only way
  `->floorPlans()->get()` and `->count()` read the right database, because those go straight to
  the builder and never pass through `getResults()`. In `getEager()` it splits the batch per
  key, **rejects** primary-side rows found under a fallback-owned key as orphans, and
  **concats** the fallback's rows — never `merge()`, which keys by primary key, and two
  databases can hold children sharing an id.
- **[`Concerns\QueriesFallbackConnection`](/src/Common/Relations/Concerns/QueriesFallbackConnection.php)**
  — used by both. The fallback **clones the relation's own builder** and re-points model,
  connection, grammar and post-processor, so nested eager loads (`with('catalogProject.developers')`)
  and eager constraint closures (`with(['catalogProject' => fn ($q) => …])`) survive. A fallback
  built from a fresh `newQuery()` silently dropped all of it for the rows it found. Safe because
  Eloquent's `Builder::__clone` deep-clones the base query, so the primary relation's connection
  is never mutated.

**Consumers today:** `Project::floorPlans()` / `floorPlanCounts()` / `matchingCanonicalIds()` /
`scopeOrderByCanonicalName()`, `LeadsController::buildFloorPlanOptions()`,
`ProjectDetailController::approvedVrPanorama()`, the RentalEstimate submission search, the Area
Tutorial stop pickers, and `CatalogueDetailService::viewerMaySee()`.

⚠️ **Two costs to know before you reach for these.** In SPLIT mode only (both are free when
`catalogueIsSeparate()` is false, which is every collapsed deployment and the whole test suite):

- A relation built per parent asks `siteOnlyReferenceIds()` once **per construction**, so a loop
  that lazily touches `$project->floorPlans` pays one extra small master query per project. Every
  caller today either eager-loads or handles a single project — write the eager load before you
  write that loop.
- Turning a name search into `matchingReferenceIds()` produces an `IN` list rather than a
  subquery, bounded by how many rows of that table carry a catalogue id. Fine at current sizes
  (it is the shape `matchingCanonicalIds` always used), but it is a list, not a predicate.

---

## 7. Setting up a developer box

Point `MASTER_DB_*` at the master with a read-only account, then choose a mode.

**Live** (nothing to mirror, no local disk cost, catalogue is read-only):

```dotenv
MASTER_DB_HOST=…
MASTER_DB_PORT=3306
MASTER_DB_DATABASE=master_projects
MASTER_DB_USERNAME=…            # SELECT-only
MASTER_DB_PASSWORD="…"
# CATALOGUE_USE_DEFAULT_CONNECTION intentionally absent — see §2
```

**Copy** (catalogue is writable, migrations apply, costs ~3.7 GB and a refresh):

```dotenv
CATALOGUE_USE_DEFAULT_CONNECTION=true
```
then `php artisan catalogue:mirror-from-master --apply`.

Two things that bite either way:

- **`bootstrap/cache/config.php` must not exist on a dev box.** A cached config outranks every
  `env()` value, so flipping the flag appears to do nothing. `php artisan config:clear`.
  This is the mechanism behind
  [the 2026-08-07 production wipe](/docs/incident-2026-08-07-production-database-wipe.md).
- **Without `MASTER_DB_*` set at all**, the `catalogue` connection falls back to Laravel's
  `forge`/`forge` defaults and every page reading `catalog_projects` 500s with
  *"Access denied for user 'forge'"*.

---

## 8. Related files

- Connections: [`config/database.php`](/config/database.php) (`catalogue`,
  `catalogue_master`, `catalogue_staging`, `reference`),
  [`config/site.php`](/config/site.php) (the edit-domain gate),
  [`config/project_catalogue.php`](/config/project_catalogue.php) (per-provider source
  connection)
- Lifecycle commands: `app/Console/Commands/{CutoverToMasterCatalogue,OffsetLocalCatalogueIds,MirrorCatalogueFromMaster}.php`
- Package transfer: `app/Console/Commands/{ExportProjectCatalogue,ImportProjectCatalogue,VerifyCatalogueSync,ExplainCatalogueDrift}.php`,
  [`app/Console/Concerns/GuardsPackageMemory.php`](/app/Console/Concerns/GuardsPackageMemory.php)
- Federation: [`app/Services/Property/CatalogueFederationService.php`](/app/Services/Property/CatalogueFederationService.php)
- Connection pinning: [`src/Analysis/Reference/CatalogProject.php`](/src/Analysis/Reference/CatalogProject.php)
  (`CONNECTION`, `ORIGIN_OWN`, `ORIGIN_MIRROR`),
  [`src/Analysis/Reference/Concerns/KeepsRelatedConnections.php`](/src/Analysis/Reference/Concerns/KeepsRelatedConnections.php),
  [`src/Common/MasterMedia.php`](/src/Common/MasterMedia.php)
- Schema: [`database/migrations/catalogue/`](/database/migrations/catalogue/README.md) (its
  README is the contract), [`app/Providers/AppServiceProvider.php`](/app/Providers/AppServiceProvider.php)
  (`loadMigrationsFrom`)
- Single-database test setup: [`phpunit.xml`](/phpunit.xml),
  [`tests/TestCase.php`](/tests/TestCase.php) (`shareCatalogueConnection`)
- The module itself: [readMe.md](/docs/modules_handbook/shared/project-catalogue/readMe.md),
  [vr360/readMe.md](/docs/modules_handbook/shared/project-catalogue/vr360/readMe.md)