# Genesis Coworking Space — Backend Technical Plan

Planning document derived from [docs/BRIEF.md](docs/BRIEF.md) and [docs/API-CONTRACT.md](docs/API-CONTRACT.md). No code yet — this defines structure, route surface, module breakdown, and data model so implementation is mechanical.

**Scope boundary:** this repo is the **API service** at `https://api.genesiscoworkingspace.com.my`. It serves two consumers:

| Consumer | Repo | Status |
|---|---|---|
| Marketing landing page | `genesiscoworkingspace-landing-page` | **Built and shipping.** Its `src/api/` already calls these endpoints and zod-parses the responses. The contract is not negotiable from this side. |
| Admin portal (CMS) | *not yet created* | Assumed to be a separate SPA. See §4c and Q1. |

The landing page ships with baked JSON snapshots and a `mock` API mode, so it launches without this service. That means **this backend is not on the launch critical path** — but everything it exposes to the landing page is already frozen by code that exists.

---

## 1. Hard constraints inherited from the frontend

These are not proposals. They come from `docs/API-CONTRACT.md` and from the frontend code that is already written against it (`src/api/client.ts`, `src/api/schemas.ts`).

| Constraint | Source | Consequence here |
|---|---|---|
| Paths are `GET /spaces/:slug/gallery` and `POST /enquiries`, **no version prefix** | `src/api/gallery.ts`, `src/api/enquiries.ts` — base URL is the bare origin | Public routes mount at the root. Versioning, if ever needed, goes on a header or a new path — not retrofitted onto these two. |
| `src`, and every `variants[].url`, must be **absolute URLs** | zod `z.string().url()` | Responses build full URLs from `ASSET_BASE_URL`. A relative `/media/...` path fails parse and silently drops the page to its stale snapshot. |
| `updatedAt` must be **ISO-8601 UTC with `Z`** | zod `z.string().datetime()` | Never hand MySQL's `2026-08-15 09:32:00` straight to JSON. Serialize via `.toISOString()`. |
| `width`/`height` per image are **required positive integers** | zod, and the frontend computes ScrollTrigger pin distance from them before any image loads | Dimensions must be probed at upload and persisted. An image row without them is not publishable. |
| Images arrive **pre-sorted by `order`** | contract req. 3 | `ORDER BY position` server-side. |
| No cookies on public endpoints — `credentials: 'omit'` | `src/api/client.ts` | Public CORS policy must **not** set `Allow-Credentials`. Admin CORS is a separate policy (§10). |
| 10s client timeout | `src/api/client.ts` | Gallery reads must be comfortably sub-second. No image processing in a public request path. |

### 1a. Two contract mismatches to settle before coding

Both are cross-repo decisions. Neither blocks starting, but both cause silent, hard-to-debug failures if discovered at cutover.

**M1 — validation errors: HTTP 422 vs `{ ok: false }`.** The contract says validation errors return **HTTP 422** with the `ok: false` body. But the frontend's `apiFetch` throws `ApiError` on any non-2xx *before* the body reaches `enquiryResponseSchema` — so `submitEnquiry()` can never return `{ ok: false }` against a real 422. Meanwhile the mock adapter returns `{ ok: false }` as a **resolved** value, i.e. it models a 200. The two paths the frontend was tested against disagree.

> **Recommendation: keep HTTP 422.** It is what the contract committed to, it is correct HTTP, and 200-ing validation failures blinds backend logging and monitoring. The fix is one line in the frontend's `submitEnquiry` — catch `ApiError`, re-parse `err.body` through `enquiryResponseSchema`, return it. Cheaper than distorting the API. Flag it to the frontend before their step 9 cutover.

**M2 — the empty gallery.** Contract req. 8 says a space with no images returns `200` with an empty array. The frontend schema is `z.array(galleryImageSchema).min(1)` — an empty array **fails parse**, and the page falls back to its baked snapshot.

> This is arguably the better user outcome (never an empty hero scroller), but it is accidental, not designed. Backend behaviour stays as specified — `200` + `[]`, since `404` would be wrong — and the frontend decides whether `.min(1)` is intentional. Worth a line in their planning doc either way.

---

## 2. Stack decisions

Node/Express/MySQL are inherited from the brief. Every other choice below is made to match **`messagepal-backend`**, a service the same team already runs on equivalent cPanel/Passenger hosting — see §13's note on why that lets us skip a formal host probe. The two dependencies messagepal does *not* exercise (`sharp`, native `argon2`) are the only real unknowns, and are handled conservatively rather than probed.

| Concern | Choice | Reasoning |
|---|---|---|
| Runtime | Node LTS (pin in `.nvmrc` + `engines`) | Match the major version messagepal runs on this host. |
| Language | **TypeScript**, compiled with `tsc` to `dist/` | The contract types are already written as TS/zod in the frontend. Sharing shapes verbatim removes the single most likely source of drift. Passenger runs the compiled output; no `ts-node` in production. messagepal is plain JS, but its cPanel constraints are runtime, not language — `tsc` runs locally, only `dist/` ships. |
| Framework | **Express 4** | Per brief. v4, not v5 — the middleware ecosystem this needs (rate limit, multer, helmet) is stable there. |
| Database | **MySQL 8** via `mysql2/promise` | Per brief; cPanel provides it. Same driver messagepal uses. |
| Query layer | **Knex** (query builder + migrations) | Migrations are the real requirement. Deliberately *not* Prisma or TypeORM: Prisma ships a native query-engine binary, which is the exact thing most likely to fail on shared cPanel hosting. Knex is pure JS. (messagepal uses Sequelize; Knex is the thinner tool and keeps the raw SQL legible — a local preference, not a hosting constraint.) |
| Validation | **zod** | Same library, same shapes as the frontend. `EnquiryInput` is copy-pasteable between repos. |
| Auth | JWT access token + rotating refresh token | §8. |
| Password hash | **`bcryptjs`** | messagepal uses it on this host, so it is the known-good default and we skip the argon2 question entirely. Pure JS, no native build. A handful of admin users — its slowness is irrelevant at this volume. |
| Image processing | **`sharp`** — assumed to install; `@jsquash/*` (WASM) is the fallback if it does not | §7. The one dependency messagepal cannot vouch for. Rather than a formal probe, the first foundation deploy (§13 step 1) installs `sharp` and settles it; the module is written so the fallback is a one-file swap. |
| Email | **Nodemailer** → cPanel SMTP (`localhost:465`, domain mailbox) | messagepal sends transactional mail this way on the same hosting. Needs SPF/DKIM on the domain regardless. |
| File uploads | `multer` → disk, size-capped | Never memory storage; a 25MB upload buffered in a 512MB Passenger process is a self-inflicted outage. |
| Logging | `pino` → rotating file under `logs/` | cPanel gives no log aggregation. Files + `stdout` for Passenger. |
| Process model | **Single process.** No cluster, no PM2, no Redis | Passenger owns the process. Design accordingly — in-memory rate-limit state is correct here precisely *because* there is only one process (§10). |

---

## 3. Repository structure

Single Express app at the repo root. No workspaces.

```
genesiscoworkingspace-backend/
├─ package.json
├─ package-lock.json
├─ .nvmrc
├─ tsconfig.json
├─ knexfile.ts
├─ .env.example                      # see §3a
├─ app.js                            # Passenger entry — requires ./dist/server.js
├─ PLANNING.md
├─ docs/
│  ├─ BRIEF.md
│  ├─ API-CONTRACT.md                # what the landing page requires (frozen, §1)
│  ├─ ADMIN-API.md                   # generated OpenAPI for the admin portal team
│  └─ DEPLOY.md                      # cPanel runbook
│
├─ src/
│  ├─ server.ts                      # listen(process.env.PORT) + graceful shutdown
│  ├─ app.ts                         # express() assembly: middleware order, routers, error handler
│  ├─ env.ts                         # zod-validated process.env — throws at boot, not at first request
│  │
│  ├─ routes/
│  │  ├─ index.ts                    # mounts public + admin routers
│  │  ├─ public/
│  │  │  ├─ gallery.routes.ts        # GET /spaces/:slug/gallery
│  │  │  └─ enquiries.routes.ts      # POST /enquiries
│  │  └─ admin/
│  │     ├─ auth.routes.ts
│  │     ├─ enquiries.routes.ts
│  │     ├─ users.routes.ts
│  │     └─ media.routes.ts
│  │
│  ├─ modules/                       # one folder per brief module
│  │  ├─ auth/
│  │  │  ├─ auth.controller.ts
│  │  │  ├─ auth.service.ts          # login, refresh, logout, forgot, reset
│  │  │  ├─ auth.schema.ts
│  │  │  ├─ token.service.ts         # sign/verify access, issue/rotate/revoke refresh
│  │  │  └─ password.service.ts      # hash/verify — bcryptjs, isolated for a future algo swap
│  │  ├─ enquiry/
│  │  │  ├─ enquiry.controller.ts
│  │  │  ├─ enquiry.service.ts       # create (public), list/detail/reply (admin)
│  │  │  ├─ enquiry.repository.ts
│  │  │  ├─ enquiry.schema.ts        # EnquiryInput — mirrors frontend src/api/schemas.ts
│  │  │  └─ spam.service.ts          # honeypot, timing, heuristics, optional Turnstile
│  │  ├─ admin/
│  │  │  ├─ user.controller.ts
│  │  │  ├─ user.service.ts
│  │  │  ├─ user.repository.ts
│  │  │  └─ user.schema.ts
│  │  └─ media/
│  │     ├─ media.controller.ts      # admin CRUD + reorder + visibility
│  │     ├─ media.service.ts
│  │     ├─ media.repository.ts
│  │     ├─ media.schema.ts
│  │     ├─ gallery.service.ts       # public read — builds the SpaceGallery response
│  │     ├─ derivative.service.ts    # AVIF/WebP/JPEG generation — §7
│  │     └─ storage.service.ts       # content-hashed paths, atomic write, delete
│  │
│  ├─ middleware/
│  │  ├─ cors.ts                     # two policies: publicCors, adminCors (§10)
│  │  ├─ authenticate.ts             # verifies access token → req.admin
│  │  ├─ authorize.ts                # role guard
│  │  ├─ validate.ts                 # zod body/query/params → 422 in contract shape
│  │  ├─ rateLimit.ts                # per-route-group limiters
│  │  ├─ requestId.ts
│  │  ├─ notFound.ts
│  │  └─ errorHandler.ts             # the ONLY place that formats an error response
│  │
│  ├─ lib/
│  │  ├─ db.ts                       # knex instance, pool sizing for shared hosting
│  │  ├─ logger.ts
│  │  ├─ mailer.ts                   # nodemailer transport + template render
│  │  ├─ ulid.ts
│  │  ├─ phone.ts                    # E.164 normalisation (libphonenumber-js)
│  │  ├─ hash.ts                     # sha256 for tokens + IP pseudonymisation
│  │  ├─ AppError.ts                 # typed errors carrying status + field map
│  │  └─ asyncHandler.ts
│  │
│  ├─ emails/
│  │  ├─ enquiry-notification.hbs
│  │  ├─ enquiry-reply.hbs
│  │  └─ password-reset.hbs
│  │
│  └─ types/
│     ├─ contract.ts                 # SpaceGallery, GalleryImage, EnquiryResponse — frontend-mirrored
│     └─ express.d.ts                # Request.admin augmentation
│
├─ migrations/                       # knex migrations, timestamped
├─ seeds/
│  ├─ 01_media_categories.ts
│  └─ 02_bootstrap_admin.ts          # first admin user, from env — never a hardcoded password
│
├─ scripts/
│  ├─ create-admin.ts                # CLI, for when the bootstrap user is lost
│  ├─ reprocess-media.ts             # regenerate all derivatives (codec/quality changes)
│  └─ check-sharp.ts                 # step 1 smoke test — does `sharp` install & load on the host? (§13 step 0)
│
└─ storage/                          # NOT web-served; originals + temp uploads
   ├─ originals/
   └─ tmp/
```

**Where the served files live.** Derivatives are written to a directory Apache serves **directly**, outside this tree — `~/public_html/media/` on the API subdomain, or a dedicated docroot. Node writes bytes; Apache serves them. Passenger streaming image bytes through Express would burn the single process's event loop on work Apache does better, and it is the difference between the immutable-cache header being a one-line `.htaccess` rule and a hand-rolled middleware.

### 3a. Environment variables

| Var | Example | Notes |
|---|---|---|
| `NODE_ENV` | `production` | |
| `PORT` | *(set by Passenger)* | `app.listen(process.env.PORT)` — never hardcode. |
| `DB_HOST` / `DB_PORT` / `DB_USER` / `DB_PASSWORD` / `DB_NAME` | | cPanel prefixes DB and user names with the account name. |
| `DB_POOL_MAX` | `5` | Shared hosting caps concurrent connections; the default of 10 is often too high. |
| `PUBLIC_ORIGINS` | `https://genesiscoworkingspace.com.my,https://www.genesiscoworkingspace.com.my,http://localhost:5173` | Public CORS allowlist. |
| `ADMIN_ORIGINS` | `https://admin.genesiscoworkingspace.com.my,http://localhost:5174` | Credentialed CORS allowlist. |
| `ASSET_BASE_URL` | `https://api.genesiscoworkingspace.com.my/media` | Prefix for every absolute URL in the gallery response (§1). |
| `MEDIA_ROOT` | `/home/<acct>/public_html/media` | Where derivatives are written. |
| `STORAGE_ROOT` | `/home/<acct>/backend-storage` | Originals + tmp. Outside the web root. |
| `JWT_ACCESS_SECRET` / `JWT_REFRESH_SECRET` | | Distinct 32-byte random values. |
| `ACCESS_TOKEN_TTL` / `REFRESH_TOKEN_TTL` | `15m` / `30d` | |
| `SMTP_HOST` / `SMTP_PORT` / `SMTP_USER` / `SMTP_PASSWORD` | `localhost` / `465` | |
| `ENQUIRY_NOTIFY_TO` | `enquiries@genesiscoworkingspace.com.my` | Comma-separated. |
| `MAIL_FROM` | `Genesis <no-reply@genesiscoworkingspace.com.my>` | Must be a real mailbox on the domain or SPF fails. |
| `TURNSTILE_SECRET` | *(optional)* | Absent → token verification is skipped, not failed. |
| `ADMIN_PORTAL_URL` | `https://admin.genesiscoworkingspace.com.my` | For building password-reset links. |
| `IP_HASH_SALT` | | Pseudonymises stored IPs (§9). |

All parsed through `src/env.ts` with zod. A missing secret must **fail the boot**, not surface as `undefined` inside a JWT signature.

---

## 4. Route surface

### 4a. Public — consumed by the landing page. Frozen by §1.

| Method | Path | Auth | Notes |
|---|---|---|---|
| `GET` | `/spaces/:slug/gallery` | none | `slug` ∈ `genesis \| hive`. Optional `?category=<slug>` — additive, the frontend never sends it. Unknown slug → `404`. `Cache-Control: public, max-age=300, stale-while-revalidate=86400`. |
| `POST` | `/enquiries` | none | Rate-limited, honeypot-screened. `422` + field map on validation failure (M1). |
| `OPTIONS` | *(both)* | none | Preflight, `Access-Control-Max-Age: 86400`. |
| `GET` | `/health` | none | DB ping + build SHA. For uptime monitoring, not the frontend. |

### 4b. Admin — consumed by the admin portal. Ours to design.

All under `/admin`, all requiring a valid access token except the four auth entry points.

**Auth**

| Method | Path | Notes |
|---|---|---|
| `POST` | `/admin/auth/login` | `{ phone, password }` → access token + refresh cookie. Rate-limited per phone *and* per IP. |
| `POST` | `/admin/auth/refresh` | Rotates the refresh token. Reuse of a rotated token revokes the whole family (§8). |
| `POST` | `/admin/auth/logout` | Revokes the presented refresh token. |
| `POST` | `/admin/auth/forgot-password` | **Always `200`**, regardless of whether the account exists — otherwise this is a phone-number enumeration oracle. |
| `POST` | `/admin/auth/reset-password` | `{ token, password }`. Consumes the token, revokes all sessions for that user. |
| `GET` | `/admin/auth/me` | Current admin. |

**Enquiries**

| Method | Path | Notes |
|---|---|---|
| `GET` | `/admin/enquiries` | Filters: `status`, `source`, `q` (name/email/phone/message), `from`, `to`, `page`, `perPage`, `sort`. Returns `{ data, meta: { page, perPage, total } }`. |
| `GET` | `/admin/enquiries/:id` | Detail + reply thread. Marks `new` → `read`. |
| `POST` | `/admin/enquiries/:id/replies` | `{ body }` → persists, sends email, records delivery outcome. |
| `PATCH` | `/admin/enquiries/:id` | `{ status }` — `read \| replied \| spam \| archived`. |

**Admin users**

| Method | Path | Notes |
|---|---|---|
| `GET` | `/admin/users` | Filters: `q`, `status`, `role`, pagination. |
| `POST` | `/admin/users` | Creates; no password in the payload — issues a set-password token by the same mechanism as reset. |
| `PATCH` | `/admin/users/:id` | Name, email, role, status. Phone change re-normalises and re-checks uniqueness. |
| `POST` | `/admin/users/:id/password` | Self-service change (requires current password) or superadmin force-reset. |

The brief lists list/create/edit only — no delete. Deactivation via `status` is the right primitive anyway: enquiry replies reference their author, and hard-deleting a user would orphan that history.

**Media**

| Method | Path | Notes |
|---|---|---|
| `GET` | `/admin/media/categories` | Category list for filter UI. |
| `GET` | `/admin/media` | `?page=genesis\|hive` (**required**), `?category=`, `?visible=`. Returns in `position` order, hidden images included. |
| `POST` | `/admin/media` | `multipart/form-data`: one file + `page`, `category`, `alt`, `caption`. Synchronous derivative generation (§7); responds with the complete image record including real dimensions. |
| `PATCH` | `/admin/media/:id` | `alt`, `caption`, `category`. |
| `PATCH` | `/admin/media/:id/visibility` | `{ isVisible }`. |
| `PATCH` | `/admin/media/order` | `{ page, ids: string[] }` — the **complete** ordered list for that page. Rewrites positions in one transaction; rejects if the id set doesn't match exactly (§6c). |
| `DELETE` | `/admin/media/:id` | Soft delete (§6c). |

### 4c. Admin portal screens *(separate repo — listed so the API surface above is checkable against real UI)*

`/login` · `/forgot-password` · `/reset-password` · `/` (dashboard: new enquiry count, recent activity) · `/enquiries` (filterable table) · `/enquiries/:id` (detail + reply composer) · `/media` (page tab → category filter → drag-reorder grid, visibility toggle, upload) · `/users` (table + create/edit drawer) · `/account`.

The reorder grid is the screen that dictates `PATCH /admin/media/order` taking a whole list rather than per-item positions — drag-and-drop produces a new sequence, not a set of deltas, and per-item updates would leave the list inconsistent if one request failed mid-drag.

---

## 5. Module breakdown

Four layers, one direction of dependency. Routers never touch the database; repositories never format a response.

```
route  →  middleware (cors → rateLimit → authenticate → authorize → validate)
       →  controller   (HTTP in/out only: read req, call service, shape response)
       →  service      (business rules, transactions, orchestration — the testable part)
       →  repository   (knex queries; the only place SQL lives)
```

**Cross-cutting rules**

- **One error formatter.** Controllers throw `AppError`; `errorHandler` is the only code that writes an error body. That is what guarantees `POST /enquiries` produces exactly `{ ok: false, error, fields }` and never Express's default HTML error page — which would reach the frontend as an HTML-parse crash rather than a caught `ApiError`.
- **The public gallery response is built in one function** (`gallery.service.ts`), typed as the frontend-mirrored `SpaceGallery` from `types/contract.ts`. Every URL is absolutised there, timestamps are `.toISOString()`d there, and — in dev and test — the response is parsed back through a copy of the frontend's zod schema before it is sent. Self-checking a response against the consumer's own parser is cheap and catches drift on the exact boundary that fails silently in production.
- **Repositories return rows; services return domain objects.** `snake_case` dies at the repository boundary.
- **Every multi-write operation is a transaction.** Reorder, media delete + file cleanup, reply + status change, reset + session revocation.

### Module responsibilities

| Module | Owns | Notes |
|---|---|---|
| **auth** | Login, token issue/rotate/revoke, forgot/reset, password hashing | `password.service.ts` (bcryptjs) is isolated so a future algorithm change touches one file. |
| **enquiry** | Public create; admin list/detail/reply/status | Public create and admin read share a repository but nothing else — different validation, different rate limits, different auth. |
| **admin** | Admin user CRUD, roles, activation | |
| **media** | Upload → derivatives → storage; ordering; visibility; the public gallery read | The largest module and the only one with meaningful I/O risk (§7). |

---

## 6. Data model

MySQL 8, `utf8mb4_0900_ai_ci`, InnoDB.

**Key strategy:** `CHAR(26)` ULIDs as primary keys for everything the API exposes by id. ULIDs are lexicographically sortable, so unlike random UUIDv4 they don't fragment InnoDB's clustered index on insert, and unlike auto-increments they don't leak enquiry volume to anyone who submits the form twice. Join tables and lookup tables keep auto-increment integers.

### 6a. Auth & admin

```sql
CREATE TABLE admin_users (
  id              CHAR(26)     NOT NULL PRIMARY KEY,
  phone           VARCHAR(20)  NOT NULL,           -- E.164, normalised on write
  phone_display   VARCHAR(32)  NOT NULL,           -- as typed, for the UI
  email           VARCHAR(254) NULL,               -- reset delivery channel (Q2)
  name            VARCHAR(120) NOT NULL,
  password_hash   VARCHAR(255) NULL,               -- NULL until the invite is accepted
  role            ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',
  status          ENUM('active','inactive')  NOT NULL DEFAULT 'active',
  failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME NULL,
  last_login_at   DATETIME NULL,
  created_by      CHAR(26) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_admin_users_phone (phone),
  UNIQUE KEY uq_admin_users_email (email),
  KEY idx_admin_users_status (status),
  CONSTRAINT fk_admin_users_creator FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE admin_refresh_tokens (
  id              CHAR(26)    NOT NULL PRIMARY KEY,
  admin_user_id   CHAR(26)    NOT NULL,
  family_id       CHAR(26)    NOT NULL,            -- rotation lineage (§8)
  token_hash      CHAR(64)    NOT NULL,            -- sha256; the raw token is never stored
  expires_at      DATETIME    NOT NULL,
  revoked_at      DATETIME    NULL,
  replaced_by     CHAR(26)    NULL,
  user_agent      VARCHAR(255) NULL,
  ip_hash         CHAR(64)    NULL,
  created_at      DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_refresh_token_hash (token_hash),
  KEY idx_refresh_user (admin_user_id),
  KEY idx_refresh_family (family_id),
  CONSTRAINT fk_refresh_user FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

CREATE TABLE password_reset_tokens (
  id            CHAR(26) NOT NULL PRIMARY KEY,
  admin_user_id CHAR(26) NOT NULL,
  token_hash    CHAR(64) NOT NULL,
  purpose       ENUM('reset','invite') NOT NULL DEFAULT 'reset',
  expires_at    DATETIME NOT NULL,                 -- +60 min
  used_at       DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reset_token_hash (token_hash),
  KEY idx_reset_user (admin_user_id),
  CONSTRAINT fk_reset_user FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
);
```

### 6b. Enquiries

```sql
CREATE TABLE enquiries (
  id            CHAR(26)     NOT NULL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  phone         VARCHAR(20)  NOT NULL,
  email         VARCHAR(254) NOT NULL,
  message       TEXT         NOT NULL,
  source        ENUM('contact','genesis','hive','home') NOT NULL DEFAULT 'contact',
  status        ENUM('new','read','replied','spam','archived') NOT NULL DEFAULT 'new',
  spam_score    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  spam_reason   VARCHAR(64) NULL,                  -- 'honeypot' | 'rate' | 'turnstile' | ...
  ip_hash       CHAR(64)  NULL,                    -- sha256(ip + IP_HASH_SALT) — §9
  user_agent    VARCHAR(255) NULL,
  referer       VARCHAR(255) NULL,
  notified_at   DATETIME NULL,                     -- when the notification email left
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_enq_status_created (status, created_at),
  KEY idx_enq_source (source),
  KEY idx_enq_email (email),
  FULLTEXT KEY ft_enq_search (name, email, message)
);

CREATE TABLE enquiry_replies (
  id              CHAR(26) NOT NULL PRIMARY KEY,
  enquiry_id      CHAR(26) NOT NULL,
  admin_user_id   CHAR(26) NOT NULL,
  body            TEXT     NOT NULL,
  delivery_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  delivery_error  VARCHAR(255) NULL,
  sent_at         DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_reply_enquiry (enquiry_id, created_at),
  CONSTRAINT fk_reply_enquiry FOREIGN KEY (enquiry_id) REFERENCES enquiries(id) ON DELETE CASCADE,
  CONSTRAINT fk_reply_author  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id)
);
```

`enquiry_replies` is a table rather than a column because "reply enquiry" will become "reply again" the first week it's used, and a thread costs nothing to model now.

**Spam is stored, not dropped.** A honeypot hit writes a row with `status='spam'`, `spam_reason='honeypot'`. It costs a few KB and it is the only way to answer "did we lose a real enquiry to the filter?" — which is the question that gets asked after a lead goes missing.

### 6c. Media

```sql
CREATE TABLE media_categories (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug       VARCHAR(64)  NOT NULL,
  label      VARCHAR(120) NOT NULL,
  position   INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_category_slug (slug)
);
-- seeded: office, function-hall, sharing-corner, event-space, meeting-room

CREATE TABLE media_images (
  id            CHAR(26) NOT NULL PRIMARY KEY,
  page          ENUM('genesis','hive') NOT NULL,   -- matches the frontend's slug union
  category_id   INT UNSIGNED NULL,
  alt           VARCHAR(255) NOT NULL,             -- required, per contract req. 4
  caption       VARCHAR(500) NULL,
  position      INT UNSIGNED NOT NULL,             -- page-scoped, NOT category-scoped
  is_visible    TINYINT(1) NOT NULL DEFAULT 1,
  width         INT UNSIGNED NOT NULL,             -- intrinsic, probed at upload
  height        INT UNSIGNED NOT NULL,
  content_hash  CHAR(64) NOT NULL,                 -- sha256 of the original
  base_path     VARCHAR(255) NOT NULL,             -- e.g. genesis/9f3a…c1/
  original_name VARCHAR(255) NOT NULL,
  original_bytes INT UNSIGNED NOT NULL,
  status        ENUM('ready','processing','failed') NOT NULL DEFAULT 'processing',
  created_by    CHAR(26) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at    DATETIME NULL,
  KEY idx_media_public (page, is_visible, deleted_at, position),
  KEY idx_media_category (page, category_id, position),
  CONSTRAINT fk_media_category FOREIGN KEY (category_id) REFERENCES media_categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_media_creator  FOREIGN KEY (created_by)  REFERENCES admin_users(id)
);

CREATE TABLE media_variants (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  image_id   CHAR(26) NOT NULL,
  format     ENUM('avif','webp','jpeg') NOT NULL,
  width      INT UNSIGNED NOT NULL,
  height     INT UNSIGNED NOT NULL,
  bytes      INT UNSIGNED NOT NULL,
  path       VARCHAR(255) NOT NULL,                -- relative to ASSET_BASE_URL
  is_primary TINYINT(1) NOT NULL DEFAULT 0,        -- the single JPEG that becomes `src`
  UNIQUE KEY uq_variant (image_id, format, width),
  KEY idx_variant_image (image_id),
  CONSTRAINT fk_variant_image FOREIGN KEY (image_id) REFERENCES media_images(id) ON DELETE CASCADE
);
```

**Three decisions worth defending:**

**`position` is page-scoped, not category-scoped.** The brief describes category-filtered lists, which reads like per-category ordering — but `GET /spaces/:slug/gallery` returns one flat sequence with one `order` integer, and the horizontal scroller renders exactly that sequence. If order were per-category, the page-level sequence would be undefined. So: one editorial sequence per page; category is a label and a filter, not a grouping. The admin UI can filter by category while still dragging within the page's single order.

**Soft delete, with file retention.** `deleted_at` hides the image immediately, but the derivative files stay for 30 days before `reprocess-media.ts --gc` removes them. The frontend bakes CMS responses into committed JSON snapshots and serves them with year-long immutable cache headers — a hard delete would leave live 404s in a shipped bundle. 30 days is enough for a snapshot refresh and a redeploy.

**`status='processing'` exists even though upload is synchronous (§7).** It costs one column, and it means a derivative run that dies halfway leaves a visibly broken row instead of a `ready` image with missing variants. Public reads filter on `status='ready'`.

### 6d. Public response assembly

```
GET /spaces/:slug/gallery
  → media_images WHERE page = :slug
      AND is_visible = 1 AND deleted_at IS NULL AND status = 'ready'
    ORDER BY position ASC
  → media_variants for that id set (one query, not N)
  → {
      slug,
      updatedAt: MAX(updated_at).toISOString(),   -- ISO-8601 UTC (§1)
      images: [{ id, alt, caption?, order: position, width, height,
                 src: ASSET_BASE_URL + primary jpeg path,
                 variants: { avif: [{url,width}], webp: [{url,width}] } }]
    }
```

Two queries, no joins fanning out rows, no N+1. At the expected volume (tens of images per page) this is sub-10ms and the 5-minute `Cache-Control` absorbs the rest.

---

## 7. Media pipeline — the highest-risk component

Contract req. 2 is the most demanding thing in this repo: on upload, derive **AVIF and WebP at 640 / 1024 / 1600 / 2400**, plus one **JPEG at 1600w** used as `src`, cap the long edge at 2400px, quality ~72 (AVIF) / ~80 (WebP), strip EXIF, and persist intrinsic dimensions.

**Upload flow (synchronous, one file per request):**

```
multer → storage/tmp/  (cap 25MB, sniff magic bytes — never trust Content-Type)
  → probe intrinsic width/height + orientation
  → normalise: apply EXIF rotation, strip all metadata, cap long edge at 2400
  → content_hash = sha256(normalised original)
  → generate 9 derivatives into MEDIA_ROOT/{page}/{hash}/  (skip widths > original — never upscale)
  → write media_images (status='processing') + media_variants in one transaction
  → flip to status='ready'
  → move the normalised original into storage/originals/
  → respond with the complete record
```

**Why synchronous rather than a job queue.** Passenger idles the app process out between requests and offers no supervised worker; a queue would need a cPanel cron running a second Node process, a lock table, and a job state machine — a large amount of machinery for a site whose admins upload a handful of photos at a time. Synchronous generation costs ~3–6s per image with `sharp`, gives the admin immediate feedback with real dimensions, and removes the entire "why is this image stuck in processing" support surface. The client uploads files one request at a time, with a progress list.

> **Escape hatch, decided by measurement:** if p95 upload exceeds ~15s on the real host, move derivative generation to a `media_jobs` table drained by a cPanel cron (`* * * * * node scripts/process-media.js`), keep `status='processing'` as the admin-visible state, and have the upload endpoint return `202`. The schema above already supports this — it is a service-layer change only.

**Codec risk.** `sharp` ships prebuilt libvips binaries for common platforms, but cPanel hosts run old glibc and sometimes block native module installation entirely. This is the one dependency `messagepal-backend` can't vouch for (it does no image work), so it isn't covered by inheriting that baseline (§13 step 0). If `sharp` won't install, the fallback is `@jsquash/*` (WASM codecs) — pure JS, no native build, but roughly 3–5× slower and memory-hungry, which likely forces the queue variant above. **It's the single question that most changes the shape of this module**, so it's settled first thing in step 1 by `scripts/check-sharp.ts` on the real host, not left to be discovered when the media module is built. A third option, shelling out to ImageMagick if the host provides it, is worth checking in the same test.

**Paths and caching.** `MEDIA_ROOT/{page}/{content_hash}/{width}.{ext}` — content-addressed, so the same bytes never regenerate and a changed image is a new path rather than a cache-busting problem. Apache serves `/media/*` with `Cache-Control: public, max-age=31536000, immutable` via `.htaccess`, satisfying contract req. 5 without Node touching the response.

---

## 8. Auth design

**Login identifier is the phone number**, normalised to E.164 (`+60…`) on both write and lookup, with `phone_display` kept for the UI. Without normalisation, `012-345 6789` and `+60123456789` become two accounts.

**Tokens.** A 15-minute JWT access token returned in the JSON body and held in the admin SPA's memory, plus a 30-day opaque refresh token in an `httpOnly; Secure; SameSite=None` cookie scoped to `/admin/auth`. `SameSite=None` is mandatory because the admin portal is a different origin from the API — and it is precisely why the admin CORS policy must be separate from the public one (§10).

**Refresh rotation with reuse detection.** Every refresh issues a new token and marks the old one `replaced_by`. If an already-rotated token is presented, the entire `family_id` is revoked — the standard containment for a stolen refresh token, and it costs one extra column.

**Brute force.** Per-IP and per-phone rate limits on login, plus `failed_attempts` / `locked_until` on the user row (lock 15 minutes after 10 failures). Login failure always returns the same generic message and roughly the same latency whether the phone exists or not.

**Forgot/reset.** `POST /admin/auth/forgot-password` **always returns 200** — a distinguishable response is a phone-number enumeration oracle. Tokens are 32 random bytes, stored as sha256, single-use, 60-minute expiry. Successful reset revokes every refresh-token family for that user.

> **Delivery channel is unresolved (Q2).** The brief says login is by phone number but doesn't say how a reset link reaches the user. SMS needs a paid gateway and a Malaysian sender-ID registration; email needs an `email` column (already in the schema) and costs nothing. **Recommendation: email delivery, phone login** — admin accounts are created by other admins, so an email address is always available at creation time.

---

## 9. Enquiry flow

**Public submit** — `POST /enquiries`:

1. zod-validate against the schema mirrored from the frontend. Failure → `422` + `{ ok: false, error, fields }` keyed to the frontend's own field names (`name`, `phone`, `email`, `message`).
2. Honeypot: `website` non-empty → persist with `status='spam'`, respond **`200 { ok: true, id }`** with a real id. Telling a bot it was caught only teaches it to stop filling the field.
3. Turnstile token verified only if `TURNSTILE_SECRET` is set. Absent config skips the check rather than failing it, so the frontend can ship the field before the backend has an account.
4. Rate limit: 5/hour and 20/day per IP hash. Over the limit → `429`, row persisted with `spam_reason='rate'`.
5. Persist, then send the notification email to `ENQUIRY_NOTIFY_TO`.

**Email failure must not fail the request.** The enquiry is already durably stored; a bounced SMTP connection turning into a `500` would tell the user their message was lost when it wasn't. Send after the row commits, record `notified_at` on success, log and continue on failure. An unnotified-enquiry count on the dashboard covers the gap.

**IP handling.** Behind Apache/Passenger, `req.ip` is the proxy unless `app.set('trust proxy', 1)` is configured — get this right or every rate limit buckets the entire internet together. Store only `sha256(ip + IP_HASH_SALT)`: enough for rate limiting and abuse forensics, not personal data at rest under PDPA.

---

## 10. CORS & security

**Two policies, mounted per router — not one global `cors()` call.**

| | Public router | Admin router |
|---|---|---|
| Origins | `PUBLIC_ORIGINS` | `ADMIN_ORIGINS` |
| `Allow-Credentials` | **false** | **true** |
| Methods | `GET, POST, OPTIONS` | `GET, POST, PATCH, DELETE, OPTIONS` |
| `Max-Age` | 86400 | 86400 |

They cannot be merged: the admin refresh cookie requires `Allow-Credentials: true`, and a policy that sets it while echoing the public allowlist widens the credentialed surface for no reason. The frontend sends `credentials: 'omit'`, so the public side must never need it. Neither policy may ever use `*` with credentials — that combination is rejected by browsers anyway, and reaching for it is the usual sign someone has merged the two.

**Other baseline:**

- `helmet()`; `x-powered-by` off; JSON body cap `100kb` on public routes.
- Uploads: extension **and** magic-byte check, 25MB cap, filenames never derived from user input (content hash only) — the path traversal and double-extension classics.
- Knex parameterises everything; no string-concatenated SQL anywhere, including the enquiry search filter.
- Reply bodies are escaped when rendered into email HTML.
- Secrets live in `.env` **outside** the web root, `chmod 600`. A `.env` inside `public_html` is downloadable.
- `.htaccess` on the API subdomain denies direct access to `storage/`, `logs/`, and dotfiles.
- Structured request logging with a request id; never log passwords, tokens, or raw IPs.

---

## 11. Deployment — cPanel Node.js App

Unlike the frontend's static drop, this needs a real Node process under Passenger.

- **Setup Node.js App** in cPanel: application root `genesiscoworkingspace-backend`, application URL `api.genesiscoworkingspace.com.my`, startup file `app.js`.
- `app.js` is a two-line CommonJS shim requiring `./dist/server.js` — Passenger's entry point stays stable while the TypeScript build output moves underneath it.
- Build locally (`npm run build` → `tsc`), deploy `dist/`, `package.json`, `package-lock.json`, `migrations/`. Install with cPanel's **Run NPM Install** (it uses the venv Node, which is what Passenger runs — a locally-built `node_modules` uploaded wholesale will have the wrong native binaries).
- Migrations run over SSH: `npx knex migrate:latest`. Never on boot — a failed migration in a Passenger start loop is very hard to see.
- Restart by touching `tmp/restart.txt`.
- **DNS + SSL for `api.genesiscoworkingspace.com.my`** — subdomain record plus an AutoSSL certificate covering it. The frontend plan flags this as the classic launch-day blocker; it is also the thing that must exist before any CORS testing is meaningful. Confirm early.
- MySQL database + user created through cPanel (both get the account-name prefix), privileges granted, credentials into `.env`.
- Backups: cPanel scheduled dump of the database plus `storage/originals/`. Derivatives are regenerable from originals; originals are not regenerable from anything.

---

## 12. Quality bar

- **Testing, aimed where bugs will be:** Vitest on the enquiry and gallery schemas (including malformed input), the gallery response assembler (asserted against a copy of the frontend's own zod schema — the single highest-value test in the repo), token rotation and reuse detection, ordering transactions under a concurrent add, and derivative generation against a real fixture image. Supertest for the two public routes end-to-end.
- **Contract regression test:** a test that fetches `GET /spaces/genesis/gallery` from the running app and parses it through the frontend's `spaceGallerySchema`, copied verbatim into `src/types/contract.ts`. If someone changes a field name, this fails in CI rather than silently in production five months later.
- **Performance:** gallery read < 50ms server time; the whole public surface is two endpoints, so there is no excuse for a slow one. Pool sized for shared hosting (`DB_POOL_MAX=5`).
- **Tooling:** ESLint + Prettier, `tsc --noEmit` in CI, strict mode, Husky + lint-staged — matching the frontend's setup so the two repos feel like one project.
- **Observability:** `/health` with a DB ping, pino to rotating files, an unhandled-rejection handler that logs before exiting.

---

## 13. Build order

**Step 0 — no separate host probe. Inherit messagepal's proven baseline.** `messagepal-backend` already runs on the same team's cPanel/Passenger hosting, so the things a probe would have checked are taken as answered by it: a working Node LTS, MySQL 8, `mysql2/promise`, `bcryptjs`, `jsonwebtoken`, `nodemailer` over cPanel SMTP (`localhost:465`), `multer`, `helmet`, and the Passenger deploy/restart cycle. Those choices in §2 are settled; do not re-litigate them.

The probe is skipped, not free — messagepal does **not** exercise two things this repo needs:

- **`sharp` (native libvips).** Assumed to install. The first foundation deploy (step 1) runs `npm install sharp` on the host and imports it once; if that fails, switch `derivative.service.ts` to the `@jsquash/*` WASM path (§7) — a one-file change the module is already shaped for. This is the only "probe" left, folded into step 1.
- **`MEDIA_ROOT` / Apache-served derivative directory.** Confirm the real path when the API subdomain is set up (§11) — a config value, not a code risk.

Everything else about §7 and §8 can be built on messagepal's baseline without waiting.

1. **Foundation** — TS + Express skeleton, `env.ts`, knex + `db.ts`, error handler, logger, health check, CORS middleware, deployed to cPanel and reachable over HTTPS. Install and smoke-test `sharp` here (see step 0). Deploy on day one, not at the end; Passenger surprises are cheaper to find with an empty app.
2. **Schema** — all migrations from §6, seeds for categories and the bootstrap admin.
3. **Public endpoints** — `GET /spaces/:slug/gallery` (against seeded data) and `POST /enquiries` with validation, honeypot, rate limiting, and notification email. **Ship these first**: they are the only endpoints another repo is already waiting on, and they unblock the frontend's step 9 cutover.
4. **Auth** — login, refresh rotation, logout, forgot/reset, lockout.
5. **Media (admin)** — upload + derivatives + storage, CRUD, visibility, reorder. The largest step; its one real risk (`sharp`) was settled back in step 1.
6. **Enquiry admin** — list with filters, detail, reply + email.
7. **Admin users** — CRUD, invite flow reusing step 4's token mechanism.
8. **Hardening + handover** — helmet, security headers, OpenAPI spec for `docs/ADMIN-API.md`, backup schedule, `docs/DEPLOY.md`, contract regression test in CI.

Steps 1–2 gate everything (step 0 is just the inherited-baseline assumptions, no work of its own). Step 3 is the only one the frontend cares about and should not wait behind 4–7.

---

## 14. Open questions

None block starting. Ordered by when they're needed.

**Q1 — Where does the admin portal live?** This plan assumes a separate SPA repo consuming `/admin/*`, which is why the CORS design has two policies and why auth uses cross-origin cookies. If instead the portal is served *by this app* (same origin, server-rendered or static-mounted), auth simplifies to a plain `SameSite=Lax` session cookie and the admin CORS policy disappears entirely. Materially different work. *Needed before step 4.*

**Q2 — Password reset delivery: SMS or email?** Login is by phone, but the brief doesn't say how a reset link is delivered. Recommendation in §8 is email, with phone remaining the login identifier — SMS means a paid gateway and Malaysian sender-ID registration. *Needed before step 4.*

**Q3 — Who resolves the two contract mismatches (§1a)?** The 422-vs-`{ok:false}` handling and the empty-gallery `.min(1)` both need a decision in the frontend repo. Neither blocks backend work — the backend behaviour is already specified — but both surface as silent failures at cutover if nobody owns them. *Raise now; needed before the frontend's step 9.*

**Q4 — Roles.** The schema has `superadmin | admin`. Is that the real distinction (only superadmins manage users), or is everyone equal? Simplest correct answer today is two roles with user management restricted; anything richer wants a permissions table. *Needed before step 7.*

**Q5 — Are the five categories fixed?** They are seeded as data rather than an enum precisely so they can change without a migration, but if admins should *manage* categories, that's a small CRUD screen and four more endpoints. *Needed before step 5.*

**Q6 — Enquiry retention.** Under PDPA, keeping names, phone numbers and emails indefinitely needs a stated purpose and period. A retention window (e.g. purge non-replied enquiries after 24 months) is a scheduled job and one migration — but it needs a business decision, and it pairs with the privacy policy the frontend plan raises as its own Q13. *Needed before handover.*

**Q7 — Backend timeline vs. frontend launch.** The landing page ships without this service. If the gallery endpoint lands before the frontend's step 8, their cutover folds into launch. If it's months out, the baked snapshots become the de-facto production content path and the CMS is a post-launch project — which would argue for reordering steps 3–5 to put the enquiry endpoint first and media later.