# Database Setup & Deploy

How to build the petav3 database from scratch, keep it up to date, and load the
data that is **not** carried in git. Applies to local dev and live deploy alike —
the only difference is which database `.env` points at.

> **PHP 8.4+ required.** Commands below use `php artisan`; on Windows/Laragon use the
> full 8.4 binary path if `php` resolves to the wrong version.

---

## Going live (prod) — quick steps

The short version; the annotated **Production runbook** (with gotchas) is at the bottom.

```bash
# 1. .env → petav3 prod DB (+ APP_KEY, APP_ENV=production), AND set the `reference`
#    connection to petav2:  REFERENCE_DB_HOST=159.65.139.125  REFERENCE_DB_DATABASE=peta_wk_dev
#    REFERENCE_DB_USERNAME=readuser  REFERENCE_DB_PASSWORD=…
php artisan migrate                              # 2. table structure
php artisan db:seed --class=RolesSeeder          # 3. roles only (NOT full db:seed — it has demo data) + make a real admin
php artisan db:seed --class=PositionsSeeder      # 3b. admin positions lookup (Caller/Appointment/Closer/Marketing/Normal, ids 1–5)
php artisan market:import                        # 4. market data, direct from petav2 (no dump, no 2006 error)
php artisan db:seed --class='\LmsImportSeeder'   # 5. Learn content
npm run build && php artisan storage:link        # 6. assets + storage; then start the redis-ai queue worker
# 7. Repoint the external scraper at the petav3 prod DB.
```

> ⚠️ Two pre-reqs for step 4: petav3's prod server must be able to **reach** petav2's DB
> (`159.65.139.125:3306`), and `readuser` must be **allowed from petav3's IP** with SELECT on
> `peta_wk_dev`. The command fails fast with a clear message if either is missing.

---

## The three kinds of "data" and where they live

| Thing | In git? | How it gets into the DB |
|---|---|---|
| **Table structure** (all tables) | ✅ migrations | `php artisan migrate` |
| **Baseline data** (roles, demo users, memberships, …) | ✅ seeders | `php artisan db:seed` |
| **LMS / Learn content** (~200 rows, small) | ✅ committed SQL | `db:seed --class='\LmsContentSeeder'` |
| **Market data** (`catalog_*`, `market_airbnbs`, `market_property_agents`) | Real datasets are not in git | **Prod/staging:** `php artisan market:import`. **Local:** `db:seed --class='\MarketDataSeeder'` creates a small committed-code fixture with no external connection. |

The full scraped datasets remain outside git because of their size. Production
loads them through the governed, batched `market:import` path. Local development
does not need production or petaV2 credentials: the explicit `MarketDataSeeder`
creates three catalogue projects plus representative floor-plan analytics,
Airbnb rows and agents directly in the redesigned schema.

`MarketDataSeeder` and `LmsContentSeeder` are intentionally **NOT** called by
`DatabaseSeeder`. Invoke them explicitly; this prevents local fixture market
data from ever being seeded accidentally during a production baseline setup.

---

## Command cheat-sheet

| Command | Destructive? | When |
|---|---|---|
| `php artisan migrate:fresh --seed` | **YES — drops all tables** | First-time setup, or a deliberate full reset |
| `php artisan migrate` | No — runs only new migrations | After pulling a branch that added tables |
| `php artisan db:seed` | No — adds baseline data | After a fresh migrate, or to (re)seed baseline |
| `php artisan market:import` | No — stable-identity upsert | **Prod/staging**: load/refresh the three redesigned target datasets from a configured read-only source |
| `php artisan db:seed --class='\MarketDataSeeder'` | No — stable-identity upsert | **Local dev**: create a deterministic sample directly in the redesigned schema; no petaV2/prod access |
| `php artisan db:seed --class='\LmsContentSeeder'` | No | To load Learn content (Learn branch) |

> The `\` prefix is required — these seeders live in the global namespace
> (`database/seeds/`), not `Database\Seeders\`.

---

## First-time local setup

```bash
composer install
php artisan key:generate
php artisan migrate:fresh --seed          # structure + baseline (roles, demo users, …)
php artisan db:seed --class='\MarketDataSeeder' # optional local Analyze fixture
php artisan storage:link
```

Then load the data that isn't in git (see the two sections below). Finally:

```bash
npm install && npm run dev                # Vite dev server
php artisan serve --port=8001             # app on :8001
```

## Day-to-day (after pulling changes)

```bash
git pull
composer install
npm install
php artisan migrate                       # NOT migrate:fresh — keeps your data
php artisan db:seed                        # only if baseline seeders changed
```

Re-run a specific seeder only if that data changed. **Never** `migrate:fresh` on a
DB you want to keep — it drops everything.

---

## Loading market data

### Local development (no external database)

```bash
php artisan migrate
php artisan db:seed --class='\MarketDataSeeder'
```

This creates a small MY fixture in `catalog_projects`,
`catalog_project_sources`, `catalog_floor_plans`,
`catalog_floor_plan_analytics`, `developers`,
`catalog_project_developers`, `market_airbnbs` and
`market_property_agents`. Re-running it is idempotent by stable source
identity and does not write any retiring `properties` / `property_*` /
`airbnbs` / `property_agents` table.

### Production or staging (real source)

```bash
php artisan market:import --source=reference
```

The source connection must be read-only. The command currently imports only:

- `edgeprop_projects` → catalogue source/canonical rows;
- `airbnbs` → `market_airbnbs`;
- `propsense_agents` → `market_property_agents`.

Options: `--chunk=`, `--tables=` and `--fresh`. No dump file is accepted by
the redesigned workflow.

## Loading Learn content (`LmsContentSeeder`)

> Lives on the `john-learn-petav3-migration` branch (the LMS-to-petav3 migration).
> Its dump (`database/data/lms_content.sql`, ~200 rows) **is** committed, so no
> mysqldump needed:

```bash
php artisan db:seed --class='\LmsContentSeeder'
```

---

## Live deploy (petav3 hosting)

The code is **connection-agnostic** — going live is configuration, not code. **Run by a
human with prod access, one step at a time, verifying each.** Do not point an unattended
agent at prod: `migrate:fresh` DROPS every table and these steps are irreversible.

### Auto-deploy from GitHub (2026-09-08)

A push to **`master`** or any **`claude/**`** branch deploys itself:
[`.github/workflows/deploy.yml`](/.github/workflows/deploy.yml) SSHes into the box and runs
`scripts/deploy-update.sh --branch <that branch> --yes` — the same script, backup, maintenance
window and `--rollback` as a hand deploy. It exists because a cloud Claude Code session
(claude.ai/code) works in its own container and pushes to GitHub, unlike Claude Code run ON
the server, whose edits are live on refresh; without this, every cloud push needed someone to
SSH in and run the script by hand. Watch a run under the repo's **Actions** tab.

**One-time setup — four commands.** A key pair the runner uses to log in as `ubuntu`:

```bash
# On the server, as ubuntu:
ssh-keygen -t ed25519 -N "" -f ~/.ssh/github-deploy -C github-actions-deploy
cat ~/.ssh/github-deploy.pub >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys
base64 -w0 ~/.ssh/github-deploy; echo   # ← ONE long line. Copy all of it.
```

Then GitHub → the repo → **Settings → Secrets and variables → Actions → New repository
secret**, name **`DEPLOY_SSH_KEY`**, paste that line, save. Delete the private half from the
server afterwards (`rm ~/.ssh/github-deploy`); GitHub is the only place it needs to live. Until
the secret exists the workflow exits green with a notice and deploys nothing.

> **Why base64 and not the key itself.** A multi-line private key copied out of a terminal
> window — PowerShell especially — rarely arrives intact: line breaks lost, `\r\n` endings, a
> trailing space. OpenSSH then reports only `error in libcrypto`, and the first attempt at this
> setup failed exactly that way. One base64 line has nothing to mangle. The workflow still
> accepts the raw key (and repairs its line breaks) and validates whichever form it gets with
> `ssh-keygen -y` before use, so a bad paste fails at that step with a message saying what to do.

Optional secrets override the defaults baked into the workflow: `DEPLOY_HOST`
(`35.213.165.89`), `DEPLOY_USER` (`ubuntu`), `DEPLOY_DIR` (`/var/www/html/peta`).

**Three things that stop it, all by design:**
- **Uncommitted edits on the server.** `deploy-update.sh` refuses to run over them
  ("Working tree has uncommitted changes") — that is the script protecting an on-box edit,
  not the workflow failing. Commit or `git stash` them on the server and the next push goes.
- **`sudo` asking for a password.** The workflow uses `sudo -n`, which fails rather than hangs.
  `sudo -n true && echo OK` on the server confirms it is passwordless for `ubuntu`.
- **The firewall.** GitHub's runners must reach port 22 on the box. If the GCP firewall only
  allows your own IP, the SSH step times out at 20s.

**Two listed branches share one checkout.** Pushing `claude/a` then `claude/b` deploys `b`;
pushing `a` again deploys `a`. Last push wins, exactly as two people editing on the server
would. Merge to `master` when something is meant to stay.

### Production runbook (in order)

1. **`.env`** — point the **default** connection (`DB_HOST`, `DB_DATABASE`, `DB_USERNAME`,
   `DB_PASSWORD`) at the live petav3 DB; set `APP_KEY`, `APP_ENV=production`, `APP_URL`.
2. **`php artisan migrate`** — builds all table structure. (NOT `migrate:fresh` on a populated DB.)
3. **Baseline — selective, NOT the full `db:seed`.** ⚠️ `DatabaseSeeder` also seeds **demo**
   records (`PeopleSeeder`, `LeadsSeeder`, `EventSeriesSeeder`, `MembershipsSeeder`, …) meant
   for dev. In prod, seed only what's real:
   ```bash
   php artisan db:seed --class=RolesSeeder        # roles + permissions
   # + create a real admin user (console/tinker) — do NOT seed demo leads/users into prod
   ```
   Run the other baseline seeders only if the team explicitly wants that sample data live.
4. **Market data (three redesigned targets) — use the direct importer on prod.**
   Put petav2's **read-only** creds in `.env` as the `reference` connection
   (`REFERENCE_DB_HOST=159.65.139.125`, `REFERENCE_DB_DATABASE=peta_wk_dev`,
   `REFERENCE_DB_USERNAME=readuser`, `REFERENCE_DB_PASSWORD=…`), then:
   ```bash
   php artisan market:import        # reads petav2 → upserts petav3 in 500-row batches
   ```
   No `mysqldump`, no `.sql` file. **Use this on prod**; batched stable-identity
   upserts target only the catalogue plus governed Airbnb/agent tables. The
   local `MarketDataSeeder` is sample data and must never be run in production.
5. **Learn content.** `php artisan db:seed --class='\LmsImportSeeder'` (loads the committed
   `lms_content.sql`). ⚠️ Confirm the Learn/Course content state with the team first — the
   module was reworked on `master`, so what's "live content" vs demo is a coordination item.
6. **App runtime.** `npm run build` (production assets, **not** `dev`), `php artisan storage:link`,
   and start a **queue worker on `redis-ai`** (`php artisan queue:work redis-ai --queue=ai`) —
   the Amenities-tab AI jobs never run without it.
7. **Keep ingestion scheduled.** Provider output must enter through its
   adapter/import path; external writers must not write target tables directly.

No PHP/Vue code changes — only `.env`. The local dev DB is **never** uploaded;
the live DB is rebuilt from migrations, approved seeders and governed imports.

### Server packages (one-time, via `scripts/server-setup.sh`)

The AI Video pipeline needs OS packages beyond PHP: **`ffmpeg`** (renders/stitches the
clips) and **`fonts-noto-cjk`** (Chinese captions — GD falls back to it for CJK text, so
without it Chinese captions render as boxes/tofu on Linux even though they look fine on a
macOS dev box). `scripts/server-setup.sh` installs both as part of provisioning. On a
machine that was set up before this was added — or any fresh box that skipped the script —
install them manually, otherwise Chinese videos won't render:

```bash
sudo apt-get install -y ffmpeg fonts-noto-cjk
```

### Inertia SSR (public-page server rendering)

The public marketing pages (`/{country}/home`, `/{country}/new-projects`, funnel
`/{slug}` and slot `/{funnel}/{slot}` landings) are server-rendered for SEO — crawlers
and WhatsApp/Facebook share-scrapers get full HTML instead of an empty Inertia shell.
Only those pages render on the server (`config/inertia.php` → `ssr.only`, enforced by
`App\Ssr\PublicPagesGateway`); the Manage/portal pages always client-render.

Moving parts:

- **Bundle** — `npm run build` builds the client assets **and** the SSR bundle
  (`bootstrap/ssr/ssr.js`, gitignored). The deploy script runs this automatically.
- **Render server** — `petav3-inertia-ssr.service` (systemd, installed by
  `scripts/server-setup.sh`) runs `php artisan inertia:start-ssr` on `:13714`.
  The deploy script restarts it on every code deploy (it holds the old bundle in
  memory until bounced).
- **Flag** — `INERTIA_SSR_ENABLED=true` in the prod `.env` (then `php artisan
  config:cache`). Rollout order on a new box: deploy code → build → start the unit →
  flip the flag.

**Failure mode is graceful:** if the daemon is down or the bundle is missing, pages
fall back to client-side rendering — degraded SEO, never an outage. Health check:
`php artisan inertia:check-ssr`; logs: `journalctl -u petav3-inertia-ssr -n 50`.

Related SEO surface (no daemon needed — rendered by Blade regardless of SSR):
per-page OpenGraph/description/canonical/JSON-LD tags come from `App\Support\Seo`
(see `docs/modules_handbook/shared/seo/readMe.md`), `/sitemap.xml` is generated from
active countries + funnels + slots (cached 1 h), and `public/robots.txt` must have its
`Sitemap:` line pointed at the real production domain before go-live.

### The `catalogue` connection — decide this BEFORE step 2

⚠️ **The project catalogue is not on the default connection.** Every catalogue model pins
`protected $connection = 'catalogue'`, and `config/database.php` resolves that from
`MASTER_DB_*` **unless** `CATALOGUE_USE_DEFAULT_CONNECTION` is set, in which case it collapses
onto `DB_*`. Pick the mode before migrating or importing anything:

| | Copy (flag SET) | Live (flag ABSENT) |
|---|---|---|
| Catalogue lives in | this deployment's own database | the shared `master_projects` |
| Refreshed by | `catalogue:mirror-from-master` | nothing — always current |
| Catalogue writes | work | fail on a SELECT-only account |

**Production runs COPY.** Full explanation:
[project-catalogue/databases-and-distribution.md](/docs/modules_handbook/shared/project-catalogue/databases-and-distribution.md) §2.

⚠️ **Turn the flag off by DELETING the line, never `=false`** — `env()` here is CakePHP's and
returns the raw string, so `"false"` is truthy.

⚠️ **`market:import` writes to whatever the `catalogue` connection points at** (it hardcodes
`$targetConn = 'catalogue'`), NOT to "petav3". On a deployment reading the master live with a
write-capable account, that step rewrites the SHARED `master_projects` every platform reads.
Confirm the mode before running step 4.

### Catalogue schema migrations

Catalogue-family schema changes live in `database/migrations/catalogue/` and are applied to
**two** databases. The site-local half is automatic — `AppServiceProvider::boot()` calls
`loadMigrationsFrom()`, so a bare `php artisan migrate` picks them up. The master half is a
separate line that **only the Hub's deploy pipeline may run against production**:

```bash
php artisan migrate --path=database/migrations/catalogue --database=catalogue
```

### The `reference` connection (catalogue ingestion — permanent)

`REFERENCE_DB_*` points at the EXTERNAL, read-only scraped-data database. **Nothing in this
codebase ever writes to it**, and it is not transitional: it is the backbone of catalogue
ingestion. Five providers name it as `providers.*.connection` (house730, centanet,
centanet-estates, propertysifu MY, propertyfinder AE) and read it live; two commands
(`market:import`, `market:import-hk`) copy wholesale from it into the catalogue connection.
In local dev point `REFERENCE_DB_*` at a database that has those tables, or the ingestion
commands fail.

## Project catalogue deployment sequence (PetaV3 → PetaHK)

The catalogue is canonical and multi-source: `catalog_projects` (one row per real-world
development), `catalog_project_sources` (one row per provider record) and
`catalog_floor_plans` (catalogue-owned unit types). PetaV3 and PetaHK are separate
deployments/databases; catalogue data moves between them with the versioned JSON
export/import commands — **catalogue data only, never workflow data** (`projects`,
`focus_projects`, owner rows, campaigns, leads all stay local to each deployment).

Production order for a catalogue refresh + HK sync:

```bash
php artisan migrate --force
php artisan edgeprop:import /absolute/path/to/source.json
php artisan catalogue:export /absolute/path/to/catalogue-hk.json --country=HK
# copy the file to the PetaHK deployment, then there:
php artisan catalogue:import /absolute/path/to/catalogue-hk.json --dry-run
php artisan catalogue:import /absolute/path/to/catalogue-hk.json
```

Notes:

- Import is idempotent: canonical projects upsert by **UUID**, sources by provider code +
  external id, floor plans by UUID; missing country/provider lookup rows are created from
  the package metadata. Re-running the same package changes nothing.
- Active admin manual overrides (`manual_overrides` on `catalog_projects` /
  `catalog_floor_plans`) survive every scraper import and travel inside the package.
- An unsupported `schema_version` aborts before any write (supported: 1–4). ⚠️ **The whole
  package is written in ONE transaction by default** — a failure part-way keeps nothing. Pass
  `--resume` to commit each aggregate separately and keep what landed.
- ⚠️ **`catalogue:export` aborts and writes NO file** — not even a partial one — 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. The shipped MY package needed it.
- ⚠️ **Loading a package is memory-hungry and a large one will REFUSE to run.** `json_decode`
  costs roughly **8× the file size**, so a 271 MB package peaks at ~2.2 GB. CLI `memory_limit`
  is usually `-1`, so PHP never stops and the kernel OOM-killer picks a victim instead — on a
  web box that is php-fpm or MySQL, not the import. `GuardsPackageMemory` is what refuses.
- **This is Generation 1 of catalogue distribution** — point-to-point packages, no shared
  infrastructure. Generation 2 is the shared `master_projects` database with its own lifecycle
  (`catalogue:cutover`, `catalogue:offset-local-ids`, `catalogue:mirror-from-master`). See
  [project-catalogue/databases-and-distribution.md](/docs/modules_handbook/shared/project-catalogue/databases-and-distribution.md) §5.
- Media binaries do not travel in the package — only stable URLs already stored as
  catalogue values.

## Zoom recording media archiving — enablement + backfill runbook

Zoom cloud recordings (video/audio) are re-hosted into the shared `MediaService` (private
GCS) so playback survives Zoom-side deletion; `zoom_recordings.media_id` points at the
durable copy, and `play_url`/`download_url` are kept untouched as the secondary Zoom
reference. Both the auto-archive-on-sync hook and the manual/backfill command already
ship in code; go-live is a config flip plus a batched backfill, no code changes.

**Enable auto-archiving on future syncs** (`ZoomRecordingSyncService::queueArchiving()` —
gated OFF by default so a deploy never auto-starts large uploads):

```bash
# .env
ZOOM_ARCHIVE_RECORDINGS=true
php artisan config:cache
```

**Backfill the existing rows** (`php artisan zoom:archive-recordings` — this command runs
regardless of `ZOOM_ARCHIVE_RECORDINGS`, since that flag only gates the sync hook):

```bash
# 1. See the volume before committing to anything.
php artisan zoom:archive-recordings --dry-run

# 2. Run in --limit BATCHES on the default redis-video lane — it shares workers
#    with video renders, so a full-backlog burst of Zoom downloads returns 429s.
php artisan zoom:archive-recordings --limit=50
#    (let it drain, then repeat)

# 3. Between batches, watch Horizon for 429s / failed jobs on the video queue.
#    A transient 429 self-heals via the job's $tries/$backoff (3 tries, 120s
#    backoff). If 429s persist or video renders lag, switch to a dedicated
#    redis-media lane (maxProcesses 2-3 — services.zoom.archive.queue_connection
#    is config-driven, so this is a config-only swap, no code change) and
#    continue batching.

# 4. Verify progress at any point:
#    SELECT COUNT(*) FROM zoom_recordings WHERE media_id IS NOT NULL;
```

`--force` re-archives rows that already have `media_id` — this is a **safe swap**, not a
delete-then-replace: the job stores and verifies the new `Media`, attaches it, and only
**then** deletes the previous `Media`, so a failed re-archive never destroys a working
copy. Full option reference: `php artisan help zoom:archive-recordings`.

Config (`config('services.zoom.archive')`, all env-backed except `collection` /
`backfill_max_processes`): `enabled` (`ZOOM_ARCHIVE_RECORDINGS`, default `false`),
`queue_connection` (`ZOOM_ARCHIVE_QUEUE_CONNECTION`, default `redis-video`),
`download_timeout` (`ZOOM_ARCHIVE_DOWNLOAD_TIMEOUT`, default `900`),
`retry_failed_on_sync` (`ZOOM_ARCHIVE_RETRY_FAILED_ON_SYNC`, default `false`),
`stream_via_signed_url` (`ZOOM_ARCHIVE_STREAM_VIA_SIGNED_URL`, default `false`).