# Menu Platform — Technical Specification

| | |
|---|---|
| Status | Draft v1.0, for review before Phase 1 build |
| Owner | Herbert |
| Date | 2026-11-11 |
| Reference | <https://menu.templegroup.asia/amaradining/> ("Amara") |
| Companion files | [`docs/schema.sql`](docs/schema.sql) (DDL, validated on PostgreSQL 18), [`docs/seed/amara-sample.csv`](docs/seed/amara-sample.csv), [`docs/seed/amara-modifier-groups.csv`](docs/seed/amara-modifier-groups.csv) |

Contents:
1. [Goals and non-goals](#1-goals-and-non-goals)
2. [Reference analysis (Amara)](#2-reference-analysis-amara)
3. [Phases and scope](#3-phases-and-scope)
4. [Architecture and stack](#4-architecture-and-stack)
5. [Multi-tenancy](#5-multi-tenancy)
6. [Data model](#6-data-model)
7. [Roles, auth and permissions](#7-roles-auth-and-permissions)
8. [REST API](#8-rest-api)
9. [Visitor app](#9-visitor-app)
10. [Admin app](#10-admin-app)
11. [Media and file storage](#11-media-and-file-storage)
12. [Caching and CDN](#12-caching-and-cdn)
13. [Internationalisation and money](#13-internationalisation-and-money)
14. [CSV import/export](#14-csv-importexport)
15. [Security](#15-security)
16. [Testing strategy](#16-testing-strategy)
17. [Deployment and operations](#17-deployment-and-operations)
18. [Seed data](#18-seed-data)
19. [Phase 2/3 design hooks](#19-phase-23-design-hooks)
20. [Acceptance criteria (Phase 1)](#20-acceptance-criteria-phase-1)
21. [Open questions](#21-open-questions)
- [Appendix A: Full DDL](#appendix-a-full-ddl)

---

## 1. Goals and non-goals

**Goals**
- A multi-tenant SaaS for restaurant digital menus. Each tenant gets a fast, mobile-first public menu at `https://<platform-host>/<slug>/` or on its own domain, and an admin app to manage it.
- Match everything Amara shows, and fix its structural problems (§2.2) with a proper data model.
- Build Phase 1 so that Phase 2 (QR table ordering) and Phase 3 (POS, analytics) add tables and endpoints without reshaping Phase 1 data.

**Non-goals (Phase 1)**
- Ordering, carts, in-app payment, reservations, delivery. Phase 2 is ordering only, pay at the counter, kitchen/staff screen, no printers (see §19 and [DECISIONS.md](DECISIONS.md)).
- Charging a card when `PAYMENT_PROVIDER=none` (the default). Stripe Checkout is implemented; Mollie and Redsys are named stubs. Pro is otherwise granted by a platform admin.
- Native mobile apps. The visitor app is a PWA-capable website.

**Decisions.** Herbert closed §21 on 2026-11-11. [DECISIONS.md](DECISIONS.md) wins where this text still shows the old placeholder (`menu.example.com`, no signup, CDN, USD as the product default). The product host is `listado.eu`. New tenants default to Spanish (`es`), euro, and `Europe/Madrid`. Free plan: 30 dishes and 30 drinks, path URL only. Pro: EUR 10 / year, `<slug>.listado.eu`, caps lifted.

---

## 2. Reference analysis (Amara)

### 2.1 What Amara is
- One server-rendered, read-only HTML page per tenant at path `/amaradining/`, topped by a banner image (`images/nav-01.png`).
- About 25 flat sections in fixed order: Set Menu, Starter, Soup, Maincourse, Steak Lover, Coolcut, Pasta, Burger Sandwich, Side Dish, Pizza, **Vegetarian**, **Gluten Free**, Dessert, Coffee, Soft Drink (beers mixed in), Shakes, Smoothies, Fresh Juice, Chilled Juice, Mocktail, Cocktails, Signature Cocktails, Aperitif, Gin/Vodka/Tequila/Rum/Bourbon/Whisky/Premium Scotch/Single Malt/Cognac/Liqueur, White/Red Wine by Glass, White Wine by Bottle (reds mixed in), Sparkling, Rosé.
- Item = `code` (POS-style: `083`, `094N`, `009T`, `M079`, `C080`, `022ns`), price (USD), English name, optional Khmer name (only seen on `055 តុមយ៉ាំគ្រឿងសមុទ្រ`), free-text description.
- No search, filters, item detail, language switch, images per item, ordering, or structured data.

### 2.2 Observed flaws and the fix for each

| # | Flaw in Amara | Example | Fix in this design |
|---|---|---|---|
| F1 | Dietary sections are copied items with suffixed codes | `083` → `083v` (Vegetarian) and `083g` (Gluten Free); the whole Vegetarian block appears **twice** | One item. Dietary **tags** at item or variant level (`item_tags`, `variant_tags`). Visitor **filter chips**. An item can sit in several categories through `category_items` without being copied. |
| F2 | Price variants written into the description | "Choices of prawn 6.00$, Choices of pork 4.00$ or vegetables Only 3.50$" | `item_variants` rows, each with its own price, optional POS code (`083v`) and its own tags. The list shows "from $3.50". |
| F3 | Options written into the description | "Choice of sauce: Red wine sauce, Black pepper sauce, Mushroom sauce" on 12 steaks | Reusable `modifier_groups` (min/max select) linked to items. |
| F4 | Inconsistent price formats | `$7.00`, `7.00$`, `6,00$`, `5.75`, `$ 11.50` | `price_minor bigint` (cents) plus tenant `currency`. Formatting is done only at render time with `Intl.NumberFormat`. |
| F5 | Missing prices | Most soft drinks, beers, all spirits, all wines | Explicit `price_on_request` flag with a localised label ("Ask staff"). A missing price is never rendered as blank. |
| F6 | Bilingual names pasted together | `055 $7.00 តុមយ៉ាំគ្រឿងសមុទ្រ SEAFOOD TOM YAM SOUP` | Localised JSONB fields `{"en": "...", "km": "..."}` with a language switch and fallback to the default locale. |
| F7 | Glass/bottle wine split into separate lists, reds under "White wine by bottle" | Woolshed Chardonnay in "by glass" (573) and "by bottle" | One wine item with `Glass` / `Bottle` variants. Categories are nested (`Wine > White`). |
| F8 | Conflicting data | `077T Hawaiian Pizza $9.50` and "$9.00" in its description; "Choices of Vegetarian Option" with no price | Descriptions are plain text. The admin flags price-like patterns in descriptions (`/\d+[.,]\d{2}\s*\$|\$\s*\d/`) with an "Embedded price detected, create variants?" hint. |
| F9 | Typos, inconsistent casing | "Pranw", "Rip Eye", "Grames", mixed ALL CAPS | Out of scope to auto-fix. The visitor applies `text-transform` only through theme settings. The admin shows a casing lint hint. |
| F10 | Items without codes | Beers, wines, some cocktails | `code` is optional in the DB. CSV export emits `@<uuid>` as the key for code-less items (§14). The seed uses `X-…` synthetic codes. |

---

## 3. Phases and scope

### Phase 1: view-only menu, admin, API (this spec, fully specified)
- Visitor app: tenant menu by slug or custom domain. Multiple menus (Food, Drinks, Happy hour) with time windows, sticky category nav with scrollspy, search, dietary filters, language switch, optional secondary currency display, sold-out badges, item modal with variants and modifiers (price preview only), lazy WebP images, schema.org JSON-LD, accessibility, optional PWA, table QR deep links.
- Admin app: login with optional TOTP 2FA, dashboard, branding and theme, branches, menus with schedules, category and item CRUD, drag-drop ordering, bulk price edit, availability toggles (global and per branch), image upload with server-side resize, CSV import (dry-run, then commit) and export, live preview, tables with QR generator and printable sheet, members and invites, audit log.
- Platform admin: tenant CRUD, suspend/restore, domain oversight, platform audit view.
- API: versioned REST `/api/v1`, OpenAPI 3.1 generated from Zod schemas.

### Phase 2: QR table ordering (data hooks specified in §19, not built)
- A guest scans a table QR, which starts a table session. The guest builds an order from variants and modifiers and submits it to the kitchen/staff screen (live via SSE/WebSocket). A staff screen manages order status. Waiter call and bill request.

### Phase 3: POS integration and analytics (hooks only)
- Two-way POS sync of items, prices, availability and orders, keyed on `code`/`external_refs`. Analytics on menu views, item opens, searches, filter usage and orders. Multi-branch price overrides.

---

## 4. Architecture and stack

### 4.1 Overview

```
                    ┌──────────────── Caddy (TLS, on-demand certs for custom domains) ─────────────┐
 guest browser ───► │ menu.example.com/* , <custom-domain>/*  ──► visitor  (Fastify SSR, :3001)    │
                    │ admin.menu.example.com/*                ──► admin    (static SPA files)      │
 admin browser ───► │ api.menu.example.com/*                  ──► api      (Fastify REST, :3000)   │
                    │ cdn.menu.example.com/*                  ──► S3/R2 public bucket (via CDN)    │
                    └──────────────────────────────────────────────────────────────────────────────┘
 visitor ──HTTP──► api (/api/v1/public/*, internal network, ETag-aware LRU)
 api ──► PostgreSQL 18 (menu_app / menu_system pools)  ──► Valkey (rate limits)  ──► S3-compatible storage
 api ──► SMTP (invites, password reset)                 pg-boss queues in Postgres (email, media GC, imports)
```

### 4.2 Stack decisions

| Concern | Choice | Why |
|---|---|---|
| Runtime | Node.js 24 LTS, TypeScript 5.x `strict` | Required. Node 24 is the active LTS in Nov 2026. |
| Monorepo | npm workspaces plus Turborepo (task caching only) | Shared types and validation between API, admin and visitor. The server has npm only. |
| API framework | **Fastify 5** | Fast, schema-first, mature plugins (`@fastify/cookie`, `@fastify/multipart`, `@fastify/rate-limit`, `@fastify/helmet`, `@fastify/cors`, `@fastify/swagger`). |
| Validation / contracts | **Zod 4** via `fastify-type-provider-zod`; OpenAPI 3.1 generated | One schema gives runtime validation, TS types (`packages/shared`) and docs. |
| ORM / SQL | **Drizzle ORM** plus `drizzle-kit` for migrations; RLS/triggers in raw SQL migrations | SQL-first and transparent. `SET LOCAL` inside a transaction works naturally. Prisma's connection and engine model makes per-transaction RLS settings awkward. |
| Database | **PostgreSQL 18** | Native `uuidv7()`, `UNIQUE NULLS NOT DISTINCT`, RLS, JSONB, `pg_trgm`. |
| Queue / cron | **pg-boss** | Postgres-backed, so no extra infrastructure. Used for email, media GC, large import commits and domain re-verification. |
| Rate limiting | `@fastify/rate-limit` on **Valkey** (Redis-compatible) | Shared counters across API instances. |
| Visitor app | **Fastify + Eta templates (SSR)**, Bootstrap 5.3 (Sass, purged), vanilla TS bundled by esbuild | Full HTML on first byte for SEO and LCP. No SPA framework, which keeps JS ≤ 30 KB gz. Same stack as the API. |
| Admin app | **Vite + React 19 + TypeScript**, Bootstrap 5.3 + `react-bootstrap`, TanStack Query, React Router, `dnd-kit`, React Hook Form + Zod resolvers | Lots of forms, drag-drop and optimistic updates. React has the best ecosystem for this. Bootstrap styling is kept as required. |
| Icons | **Font Awesome 6 Free**, SVG + JS-free sprite subset (visitor), `@fortawesome/react-fontawesome` (admin) | The visitor ships only the icons it uses (about 5 KB), not the webfont (over 100 KB). |
| Images | **sharp** (libvips) | Fast resize and WebP encode. Strips EXIF. |
| Storage | S3 API: MinIO (dev), Cloudflare R2 or AWS S3 (prod) via `@aws-sdk/client-s3` | Provider-agnostic. |
| Email | nodemailer over SMTP: Mailpit (dev), Postmark/SES (prod) | |
| Auth | Server-side sessions (opaque cookie, Postgres-backed), argon2id, TOTP (`otplib`) | Revocation is instant. No JWT refresh complexity for a first-party admin. |
| QR | `qrcode` (SVG/PNG), `pdfkit` (printable sheets) | |
| Logging / tracing | pino (JSON), OpenTelemetry SDK (OTLP exporter optional), Sentry (optional) | |
| Testing | Vitest, Testcontainers (Postgres 18), Playwright, axe-core, Lighthouse CI, k6 | §16 |

### 4.3 Repository layout

```
menu/
├─ apps/
│  ├─ api/                 Fastify REST API
│  │  └─ src/
│  │     ├─ server.ts      build app, register plugins
│  │     ├─ plugins/       db, auth, tenant, errors, rate-limit, openapi
│  │     ├─ modules/       one folder per resource: routes.ts, service.ts, repo.ts, schema.ts
│  │     │   ├─ public/    tenant profile, menu tree, item, table resolve
│  │     │   ├─ auth/      login, sessions, mfa, invites, password reset   (menu_system pool)
│  │     │   ├─ platform/  superadmin endpoints                           (menu_system pool)
│  │     │   ├─ tenants/ branches/ menus/ categories/ items/ modifiers/ tags/
│  │     │   ├─ media/ imports/ tables/ members/ audit/ dashboard/ preview/
│  │     ├─ jobs/          pg-boss workers                                 (menu_system pool)
│  │     └─ lib/           money, i18n, csv, qr, storage, image
│  ├─ visitor/             Fastify SSR app
│  │  ├─ src/server.ts, routes/, views/*.eta, client/*.ts (esbuild), styles/*.scss
│  └─ admin/               Vite React SPA
│     └─ src/ routes/, features/<resource>/, components/, api/ (generated client)
├─ packages/
│  ├─ shared/              Zod schemas + inferred types, permissions matrix, money/i18n utils, error codes
│  ├─ db/                  Drizzle schema, migrations (SQL), seed scripts, withTenant()
│  └─ theme/               Bootstrap Sass overrides shared by visitor + admin preview
├─ infra/                  docker-compose.yml, Caddyfile, Dockerfiles, postgres init (roles)
├─ docs/                   schema.sql, seed/, ADRs
└─ SPEC.md
```

Lint rule (`eslint no-restricted-imports`): `@menu/db/system` (the BYPASSRLS pool) may only be imported from `apps/api/src/modules/{auth,platform}/**`, `apps/api/src/jobs/**` and `apps/api/src/plugins/tenant.ts`.

---

## 5. Multi-tenancy

### 5.1 Model
- Shared database and schema. Every tenant-owned row has `tenant_id uuid NOT NULL`.
- **Isolation is enforced at three layers:**
  1. **RLS.** All tenant tables have `ENABLE` + `FORCE ROW LEVEL SECURITY` with the policy `tenant_id = app_tenant_id()`, where `app_tenant_id()` reads `current_setting('app.tenant_id')`. The runtime role `menu_app` is `NOBYPASSRLS`.
  2. **Composite foreign keys.** Every cross-table reference is `(tenant_id, x_id) → parent(tenant_id, id)`. FK checks bypass RLS, so without this a tenant could link another tenant's item into its own category. The smoke test in §16.3 proves this is rejected.
  3. **Application.** Each admin route resolves `:tenantId`, checks the caller's membership and role, then runs all queries in `withTenant(tenantId, fn)`.
- `users`, `sessions` and tokens are global and readable only by `menu_system`. `menu_app` lists members through the `SECURITY DEFINER` function `tenant_users()`, which only returns users with a membership or audit entry in the current tenant.

```ts
// packages/db/src/with-tenant.ts
export async function withTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>): Promise<T> {
  return appDb.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.tenant_id', ${tenantId}, true)`); // SET LOCAL semantics
    return fn(tx);
  });
}
```
Pool hygiene: `set_config(..., true)` is transaction-local, so a pooled connection never leaks a tenant. Any query on `menu_app` outside `withTenant` sees zero rows, so a missing wrapper fails closed.

### 5.2 Tenant resolution

| Surface | Rule |
|---|---|
| Visitor, platform host (`PLATFORM_HOST` = `listado.eu`) | First path segment = tenant slug: `/{slug}/…`. Unknown slug → 404 page. `slug` is case-insensitive (citext). Requests to a non-canonical case are 301-redirected. |
| Visitor, pro subdomain | `<slug>.listado.eu` resolves only when `tenants.plan = 'pro'` and the subscription is active. Paths have no slug prefix. A free tenant's subdomain is 404. `www`, `api`, and `admin` are not tenant labels. |
| Visitor, custom domain | `Host` header looked up in `domains` where `status='verified'` → tenant. Paths have no slug prefix (`/`, `/es/`, `/i/...`). Requests to `PLATFORM_HOST/{slug}/` for a tenant with a primary custom domain → 301 to the custom domain (setting `settings.redirect_to_primary_domain`, default true). |
| Visitor → API | Visitor calls `GET /api/v1/public/resolve?host=<host>&path=<path>` → `{tenant_slug, base_path, canonical_origin}` (cached in-process for 60 s; invalidated on `content_changed`). |
| API, admin | Explicit in path: `/api/v1/admin/tenants/{tenantId}/…`. Never inferred from Host. |
| API, public | `/api/v1/public/t/{slug}/…` |

**Reserved slugs** (also rejected as tenant slugs): `api admin www app cdn static assets img images media login logout signup register help docs status health healthz readyz t i b q km en th zh vi fr de es robots.txt sitemap.xml favicon.ico manifest.webmanifest sw.js _`.

**Custom domain flow:** the owner adds `menu.amara.com` → API creates `domains(status='pending', verification_token=<random 32 chars>)` → the owner adds `TXT _menu-verify.menu.amara.com = <token>` and `CNAME menu.amara.com → edge.menu.example.com` → `POST …/domains/{id}/verify` (and a pg-boss job every 15 min for pending domains, up to 72 h) resolves TXT, then sets `verified`. Caddy on-demand TLS calls `GET /internal/tls-ask?domain=` (internal network only), which returns 200 only for verified hostnames, so certificates can't be minted for arbitrary hosts.

---

## 6. Data model

### 6.1 Conventions
- PK `uuid DEFAULT uuidv7()` (time-ordered, index-friendly). `audit_log` uses `bigint identity`.
- `created_at`, `updated_at` (trigger), `deleted_at` (soft delete) on entities. Join tables are hard-deleted.
- Unique constraints on soft-deletable tables are **partial** (`WHERE deleted_at IS NULL`), so a deleted code can be reused.
- `version integer` on editable aggregates (tenants, branches, menus, categories, items, modifier_groups), incremented by trigger. It is used for optimistic concurrency (`If-Match`, §8.1).
- `sort_order integer` with ascending order. Reorder endpoints rewrite the whole sibling set as `0..n-1` in one transaction. Sibling sets are small (≤ 300), so no fractional indexing is needed.
- Localised text = JSONB object `{locale: string}`, validated by `is_localized_text()`. We chose this over a `translations` EAV table because it means one-row reads with no N-way joins, straightforward CSV mapping, atomic updates, and GIN/trigram searchability. It is the "translations" mechanism of this system. Allowed keys per tenant = `tenants.locales` (validated in the API).
- Money = `bigint` minor units of `tenants.currency`. Exponent comes from ISO 4217 (USD 2, KHR 0, JPY 0) via `Intl.NumberFormat().resolvedOptions().maximumFractionDigits`. Floats are never used.
- **Variant rule:** a choice is a **variant** if it changes price, dietary tags or POS code (pork/prawn/vegetable, glass/bottle). Otherwise it is a **modifier** (sauce, doneness, tea type). Every item has at least one variant. Single-price items have one default variant with `name = NULL`. This is enforced by a deferred constraint trigger.
- Tag inheritance: an item matches a dietary filter if the **item** has the tag (whole dish), or if **any variant** has it ("Vegetarian option"). `variant_tags` fixes F1.

### 6.2 Entity overview

```
tenants 1─* domains
tenants 1─* branches 1─* tables
tenants 1─* memberships *─1 users           (users 1─* sessions)
tenants 1─* invitations
tenants 1─* menus 1─* menu_schedules
              menus *─* branches (branch_menus)
              menus 1─* categories (self-parent, depth ≤ 2)
categories *─* items (category_items, sort_order)
items 1─* item_variants *─* tags (variant_tags)
items *─* tags (item_tags)                  tags: tenant_id NULL = system tag
items *─* modifier_groups (item_modifier_groups) 1─* modifiers
items/variants 1─* branch_item_overrides *─1 branches
media ← tenants.logo/banner, categories.banner, items.media
imports, audit_log
```

### 6.3 JSONB shapes (validated by Zod in `packages/shared`)

```jsonc
// tenants.theme
{
  "primary": "#8b1e3f", "secondary": "#c9a227", "background": "#fffaf3", "surface": "#ffffff",
  "text": "#1f1a17",                      // auto-derived if absent; contrast >= 4.5:1 enforced (warn)
  "font_heading": "Playfair Display",     // whitelist: system, Inter, Playfair Display, Lora, Kantumruy Pro, Noto Sans Khmer
  "font_body": "Inter",
  "radius": "0.75rem",
  "item_layout": "list" | "cards" | "compact",
  "show_item_images": true,
  "uppercase_category_titles": true,
  "banner_mode": "image" | "image_with_logo" | "none"
}
// tenants.settings
{
  "show_item_codes": true,                 // Amara shows codes; staff use them to order
  "hide_sold_out": false,                  // false = show greyed with badge
  "price_on_request_label": {"en": "Ask staff", "km": "សូមសួរបុគ្គលិក"},
  "price_display": "narrowSymbol",          // Intl currencyDisplay
  "show_secondary_currency": false,
  "footer_note": {"en": "Prices in USD, incl. 10% VAT"},
  "redirect_to_primary_domain": true,
  "filter_tags": ["vegetarian", "gluten_free", "spicy_2"], // visitor chips; if absent: tags with is_filterable
  "show_secondary_name": true,             // show other-locale item name under the primary one
  "require_mfa": false,
  "pwa_enabled": false,
  "seo": {"title": {"en": "Amara Dining — Menu"}, "description": {"en": "…"}, "noindex": false},
  "restaurant": {"cuisine": ["Cambodian","Western"], "price_range": "$$", "geo": {"lat": 11.5564, "lng": 104.9282}}
}
// tenants.contact
{ "phone": "+855 …", "email": "…", "address": {"en": "…", "km": "…"}, "map_url": "https://maps…",
  "socials": {"facebook": "https://…", "instagram": "https://…", "telegram": "https://t.me/…"},
  "opening_hours": [{"days": 127, "open": "07:00", "close": "23:00"}] }
// tenants.features (platform-controlled)
{ "ordering": false, "waiter_call": false, "max_branches": 3, "max_items": 2000, "custom_domains": true }
// media.variants
[ {"w": 160, "h": 120, "format": "webp", "key": "t/<tid>/m/<mid>/160.webp", "bytes": 6123}, … ]
```

### 6.3b Market amendments (2026-11-11)

- `tenants.plan` is `free` or `pro`. New tenants default to `es` / `EUR` / `Europe/Madrid`.
- `items.kind` and `categories.kind` are `dish` or `drink`. The free plan refuses a create or import that would leave more than 30 live dishes or 30 live drinks.
- `item_tags.presence` and `variant_tags.presence` are `contains` (default) or `may_contain`. No row means none.
- `subscriptions` records the plan period and the billing provider id. `PAYMENT_PROVIDER=stripe` writes it from Checkout and the webhook. `none` writes it when a platform admin grants Pro.
- `translations` caches provider output on `(source_hash, source_locale, target_locale)`. Not tenant-scoped.
- `exchange_rates` stores ECB euro reference rates (`1 EUR = rate × currency`) by date.
- `analytics_events` is cookieless: kind, optional subject, locale. No IP and no visitor id.
- A selection's POS identity is not a stored column in Phase 1. It is `variant.code ?? item.code` plus each selected modifier code, joined with ` / `.

### 6.4 Table reference (summary; full DDL in Appendix A)

| Table | Purpose / notable columns |
|---|---|
| `tenants` | slug (citext, unique), locales[], default_locale, currency, secondary_currency/rate/round, timezone, theme, settings, contact, features, logo/banner media, `content_version` (ETag source) |
| `domains` | custom hostnames, TXT verification, `is_primary` (≤1 per tenant) |
| `branches` | locations. `is_default` (exactly 1). Own timezone optional |
| `users` | global identity. argon2id hash, TOTP secret (AES-GCM encrypted), lockout counters, `is_platform_admin` |
| `sessions` | sha256(token) PK, csrf_token, mfa_verified, idle/absolute expiry |
| `memberships` | user↔tenant, role, optional `branch_ids` restriction for staff |
| `invitations` | email, role, sha256 token, 7-day expiry |
| `media` | original (private) + WebP variants (public), focal point, dominant colour, localised alt |
| `menus` | Food / Drinks / Happy hour. `menu_schedules` = availability windows (days bitmask, local time, crosses-midnight support, date range) |
| `branch_menus` | which menus a branch shows (no rows = all branches) |
| `categories` | per menu, depth ≤ 2 (trigger), FA icon, optional banner |
| `items` | code (unique per tenant among live items), localised name/description, image, `is_visible`, `is_available`, Phase 2/3 hooks, generated `search_text` (trigram index) |
| `category_items` | placement + order. One item can be in many categories |
| `item_variants` | price_minor or price_on_request, optional POS code, default flag, own availability |
| `branch_item_overrides` | per-branch sold-out (Phase 1) / price (reserved) |
| `modifier_groups`, `modifiers`, `item_modifier_groups` | reusable option sets, min/max select, price delta, per-item min/max override |
| `tags`, `item_tags`, `variant_tags` | dietary / allergen / badge / spice. System tags have `tenant_id NULL`. `is_filterable` → visitor chip |
| `tables` | per branch, label, 128-bit `qr_token` (rotatable) |
| `imports` | CSV dry-run results, commit state, `base_content_version` guard |
| `audit_log` | append-only (no UPDATE/DELETE grant), before/after JSON |

### 6.5 Availability resolution (visitor, at request time `now` in branch timezone)
1. Menus: `is_active AND deleted_at IS NULL AND (no branch_menus rows OR linked to branch) AND (no schedules OR any schedule matches now)`. A schedule matches if the day bit is set for the local weekday (for a crosses-midnight window, the post-midnight part belongs to the previous weekday's bit), local time is in `[start, end)`, and the date is within `[valid_from, valid_to]`.
2. If several menus are active, show them all as top-level tabs, ordered by `sort_order`. If none is active, show all active menus with a notice "Served from 11:00" computed from the next window. Guests should still see the menu, so it is never empty.
3. Categories: `is_visible`, at least one visible item after filters, otherwise hidden.
4. Items: `is_visible AND deleted_at IS NULL`. Sold out if `NOT items.is_available` or a branch override `is_available=false` (item-level) or all variants are unavailable.
5. Variants: hidden if unavailable and `hide_sold_out`, otherwise shown disabled.

### 6.6 DDL
The full DDL is in [Appendix A](#appendix-a-full-ddl) and `docs/schema.sql`. It was validated on PostgreSQL 18.

---
## 7. Roles, auth and permissions

### 7.1 Roles

| Role | Scope | Summary |
|---|---|---|
| `platform_admin` | global (`users.is_platform_admin`) | Creates, suspends and archives tenants. Sets slug, features and limits. Can act in any tenant with "support mode", which is audited and shows a banner. **Must** have TOTP enabled. |
| `owner` | tenant | Everything in the tenant, including billing settings (future), domains, members, deleting the tenant's content. At least 1 per tenant, and the last owner can't be removed or demoted. |
| `manager` | tenant | All menu content, media, imports/exports, tables/QR, branding. Can invite and remove `staff` only. No domains, no owner/manager management. |
| `staff` | tenant (optionally limited to `branch_ids`) | Toggle availability (sold out) and view the menu, tables and QR. Read-only for everything else. |

### 7.2 Permission matrix (`packages/shared/src/permissions.ts` is the single source)

| Permission | owner | manager | staff |
|---|:-:|:-:|:-:|
| `tenant.read` | ✓ | ✓ | ✓ |
| `tenant.branding.update` (theme, logo, banner, contact, settings) | ✓ | ✓ | |
| `tenant.locale_currency.update` (locales, currency, timezone) | ✓ | | |
| `domain.manage` | ✓ | | |
| `branch.manage` | ✓ | ✓ | |
| `menu.manage` (menus, schedules, categories, items, variants, modifiers, tags) | ✓ | ✓ | |
| `availability.update` | ✓ | ✓ | ✓ (own branches) |
| `price.bulk_update` | ✓ | ✓ | |
| `media.manage` | ✓ | ✓ | |
| `import.run` / `export.run` | ✓ | ✓ | export only |
| `table.manage` (CRUD, rotate QR) | ✓ | ✓ | |
| `table.read` / QR download | ✓ | ✓ | ✓ |
| `member.manage` | ✓ (all roles) | ✓ (staff only) | |
| `audit.read` | ✓ | ✓ | |

### 7.3 Authentication
- **Mechanism:** an opaque session cookie. Token = 32 random bytes (base64url). The DB stores `sha256(token)` only.
  - Cookie `__Host-menu_sid`: `HttpOnly; Secure; SameSite=Lax; Path=/`. It is set by the API on `api.menu.example.com`. The admin calls the API with `credentials: 'include'`. CORS allows exactly `ADMIN_ORIGIN` with credentials.
  - Idle timeout 24 h (sliding; `last_seen_at` written at most once per 5 min), absolute 14 days. The session is rotated on login, MFA verification, password change and role change. Logout deletes the row. "Log out other sessions" is available in Profile.
- **CSRF:** a synchronizer token. `GET /auth/me` returns `csrf_token`, and every non-GET admin request must send `X-CSRF-Token` matching `sessions.csrf_token`. Together with SameSite=Lax and the strict CORS origin this gives defence in depth.
- **Passwords:** argon2id (`@node-rs/argon2`) with m=19456 KiB, t=2, p=1 (OWASP baseline), re-hashed on login if the parameters changed. Policy: 10 to 128 chars, checked against the top-100k breached list (bundled bloom filter, no external call). No composition rules.
- **Lockout:** 5 failures → 1 min lock, doubling up to 1 h (`failed_login_count`, `locked_until`). The response is always the generic `invalid_credentials`. Rate limits are in §15.
- **2FA (optional per user, mandatory for platform admins, and owners can require it tenant-wide via `settings.require_mfa`):** TOTP RFC 6238 (SHA-1, 6 digits, 30 s, ±1 step), with the secret encrypted using AES-256-GCM under `APP_ENCRYPTION_KEY`. 10 single-use recovery codes (sha256-stored). Login with TOTP creates the session with `mfa_verified=false`, and only `/auth/mfa/verify` and `/auth/logout` are allowed until it is verified.
- **Invites:** owner/manager → `POST /members/invitations` → email with a link `https://admin…/invite/<token>` (token 32 bytes, sha256 stored, 7-day expiry, single use). If the email already has a user, accepting adds a membership after login. Otherwise the user sets a display name and password. Re-inviting revokes the previous open invite.
- **Password reset:** `POST /auth/password/forgot` always returns 202. Token valid for 30 min, single use. All sessions are revoked on reset.
- **Platform bootstrap:** `npm exec -w @menu/api -- tsx src/cli.ts create-platform-admin --email=owner@example.com` prints the one-time password.

---

## 8. REST API

### 8.1 Conventions
- Base `https://api.menu.example.com/api/v1`. Breaking changes → `/api/v2`. Additive changes are made in place. OpenAPI at `/api/v1/openapi.json`, Swagger UI at `/api/docs` (non-prod or authenticated).
- JSON in and out, `snake_case`, UTF-8. Timestamps are RFC 3339 UTC (`2026-11-11T20:09:00Z`). IDs are UUID strings.
- **Money in admin payloads:** `price_minor` integer in the tenant currency. The response also includes `currency` at the top level of the tenant resource. **Public payloads** include `price_minor` plus a preformatted `price_display` for the requested locale.
- **Localised fields** in admin payloads are full objects (`{"en":"…","km":"…"}`). In public payloads they are resolved to a string for `?locale=` (fallback: requested → tenant default → first non-empty).
- **Pagination:** cursor-based. `?limit=` (default 50, max 200) and `&cursor=` (opaque base64url of the last sort key). Response:
  ```json
  { "data": [ … ], "page": { "next_cursor": "eyJ1IjoiMjAyNi0xMS0xMVQyMDowOTowMFoiLCJpZCI6Ii4uLiJ9", "limit": 50 } }
  ```
  `next_cursor: null` on the last page. Small bounded collections (menus, categories, tags, modifier groups, branches, tables, members) return all rows with no pagination.
- **Filtering and sorting:** `?q=` (trigram search), resource-specific filters, `?sort=field` or `-field` from a per-endpoint whitelist.
- **Optimistic concurrency:** versioned resources return `ETag: "v<version>"`. `PATCH`/`DELETE` **require** `If-Match: "v<version>"`. A mismatch → `412 precondition_failed` with the current resource in `current`. If the header is missing → `428 precondition_required`.
- **Idempotency:** `POST` creating resources accepts an `Idempotency-Key` header (UUID). The response is stored for 24 h per (user, key) in Valkey.
- **Request ID:** `X-Request-Id` (generated if absent), echoed in the response and logs.
- **Errors:** RFC 9457 `application/problem+json`:
  ```json
  {
    "type": "https://menu.example.com/problems/validation_failed",
    "title": "Validation failed",
    "status": 422,
    "code": "validation_failed",
    "detail": "2 fields are invalid",
    "errors": [
      { "path": "/variants/0/price_minor", "code": "too_small", "message": "Must be ≥ 0" },
      { "path": "/name/en", "code": "required", "message": "English name is required (default locale)" }
    ],
    "request_id": "01JCX4…"
  }
  ```

| HTTP | `code` | When |
|---|---|---|
| 400 | `bad_request` | Malformed JSON, bad query param |
| 401 | `unauthenticated` | No or expired session |
| 401 | `mfa_required` | Session not MFA-verified |
| 403 | `forbidden` | Authenticated, lacks permission or membership |
| 403 | `csrf_failed` | Missing or invalid `X-CSRF-Token` |
| 403 | `tenant_suspended` | Tenant suspended (public: 410 page) |
| 404 | `not_found` | Also returned for other tenants' IDs (no existence leak) |
| 409 | `conflict` | Unique violation (e.g. duplicate item code); `errors[].path` points to the field |
| 409 | `import_stale` | Content changed since the import dry-run |
| 412 | `precondition_failed` | `If-Match` mismatch |
| 413 | `payload_too_large` | Upload > limit |
| 415 | `unsupported_media_type` | Upload not jpeg/png/webp/avif |
| 422 | `validation_failed` | Schema or business-rule failure |
| 428 | `precondition_required` | Missing `If-Match` |
| 429 | `rate_limited` | Includes `Retry-After` |
| 500 | `internal` | Never leaks internals; `request_id` for support |

### 8.2 Public endpoints (no auth; cacheable)

All public reads send `ETag` and `Cache-Control`, and honour `If-None-Match` → `304`.

| Method | Path | Description |
|---|---|---|
| GET | `/public/resolve?host=&path=` | Host/path → tenant slug (used by the visitor server). |
| GET | `/public/t/{slug}` | Tenant profile: name, locales, currency, theme, logo/banner, contact, branches, filterable tags. |
| GET | `/public/t/{slug}/menu?locale=km&branch=riverside&at=2026-11-11T13:00:00Z` | Full denormalised menu tree for the active menus. `at` is honoured only with a valid `preview` token. |
| GET | `/public/t/{slug}/items/{itemIdOrSlug}?locale=` | Single item (deep link / share page). |
| GET | `/public/t/{slug}/tables/{qrToken}` | `{ branch_slug, table_label }`, or 404 if the token was rotated or the table is inactive. |
| GET | `/public/t/{slug}/menu?…&preview=<token>` | As above, but includes hidden items and ignores schedules. `Cache-Control: private, no-store`. |

Caching headers (menu, profile, item):
```
ETag: W/"t:0190a000…:cv:1842:l:km:b:riverside:m:3f2a"   # tenant, content_version, locale, branch, active-menu-set hash
Cache-Control: public, max-age=60, s-maxage=300, stale-while-revalidate=86400, stale-if-error=86400
Vary: Accept-Encoding
```
The active-menu-set hash is included because a schedule can change what is served without any write.

**Example `GET /public/t/amaradining/menu?locale=en`** (trimmed):
```json
{
  "tenant": {
    "slug": "amaradining", "name": "Amara Dining", "locale": "en", "locales": ["en", "km"],
    "currency": "USD", "secondary_currency": "KHR", "secondary_rate": "4100", "secondary_round_minor": 100,
    "content_version": 1842
  },
  "branch": { "slug": "main", "name": "Amara Dining", "timezone": "Asia/Phnom_Penh" },
  "generated_at": "2026-11-11T20:09:00Z",
  "filters": [
    { "code": "vegetarian", "name": "Vegetarian", "icon": "fa-leaf", "color": "#2e7d32" },
    { "code": "gluten_free", "name": "Gluten free", "icon": "fa-wheat-awn-circle-exclamation", "color": "#8d6e63" },
    { "code": "spicy_2", "name": "Spicy", "icon": "fa-pepper-hot", "color": "#e65100" }
  ],
  "menus": [
    {
      "id": "0190a0f1-…", "slug": "food", "name": "Food", "available_now": true,
      "categories": [
        {
          "id": "0190a0f2-…", "slug": "starter", "name": "Starter", "icon": "fa-bowl-food",
          "children": [],
          "items": [
            {
              "id": "0190a0f3-…", "slug": "fresh-spring-rolls", "code": "083",
              "name": "Fresh Spring Rolls",
              "description": "Avocado, fresh organic cucumber, carrot, basil leaf, local lettuce, peanut with sweet-sour dip.",
              "image": {
                "src": "https://cdn.menu.example.com/t/0190a000…/m/0190a1aa…/480.webp",
                "srcset": "…/160.webp 160w, …/480.webp 480w, …/960.webp 960w",
                "width": 480, "height": 360, "alt": "Fresh spring rolls with peanut dip",
                "dominant_color": "#d8c9a3", "focal": [0.5, 0.45]
              },
              "is_available": true,
              "tags": ["gluten_free"],
              "allergens": ["peanuts"],
              "badges": [],
              "variant_tags": ["vegetarian"],
              "price_from_minor": 350, "price_from_display": "from $3.50",
              "variants": [
                { "id": "…", "code": null,   "name": "Pork",      "price_minor": 400, "price_display": "$4.00", "price_on_request": false, "is_default": true,  "is_available": true, "tags": [] },
                { "id": "…", "code": null,   "name": "Prawn",     "price_minor": 600, "price_display": "$6.00", "price_on_request": false, "is_default": false, "is_available": true, "tags": [] },
                { "id": "…", "code": "083v", "name": "Vegetable", "price_minor": 350, "price_display": "$3.50", "price_on_request": false, "is_default": false, "is_available": true, "tags": ["vegetarian"] }
              ],
              "modifier_groups": []
            },
            {
              "id": "…", "slug": "filet-steak-250g", "code": "886", "name": "Filet Steak 250g",
              "description": "Tender, lean and juicy filet.",
              "image": null, "is_available": true, "tags": [], "allergens": [], "badges": [], "variant_tags": [],
              "price_from_minor": 2600, "price_from_display": "$26.00",
              "variants": [ { "id": "…", "code": null, "name": null, "price_minor": 2600, "price_display": "$26.00", "price_on_request": false, "is_default": true, "is_available": true, "tags": [] } ],
              "modifier_groups": [
                { "id": "…", "code": "steak-sauce", "name": "Choice of sauce", "min_select": 1, "max_select": 1,
                  "modifiers": [
                    { "id": "…", "name": "Red wine sauce",     "price_delta_minor": 0, "price_delta_display": null, "is_default": false, "is_available": true },
                    { "id": "…", "name": "Black pepper sauce", "price_delta_minor": 0, "price_delta_display": null, "is_default": true,  "is_available": true },
                    { "id": "…", "name": "Mushroom sauce",     "price_delta_minor": 0, "price_delta_display": null, "is_default": false, "is_available": true }
                  ] }
              ]
            }
          ]
        }
      ]
    },
    { "id": "…", "slug": "drinks", "name": "Drinks", "available_now": true, "categories": [ "…" ] }
  ]
}
```
Server-side: one query per relation (menus, categories, category_items+items, variants, tags, modifier groups, modifiers) inside one `withTenant` transaction, assembled in memory. That is 7 queries for ~300 items. The result is cached in an in-process LRU keyed by the ETag (max 200 MB, 10 min TTL) and invalidated by `LISTEN content_changed`. Target p95 < 30 ms cache hit, < 150 ms miss.

### 8.3 Auth endpoints (`/auth`, `menu_system` pool)

| Method | Path | Auth | Body → Response |
|---|---|---|---|
| POST | `/auth/login` | none | `{email, password}` → `200 {user, mfa_required, memberships[]}` + Set-Cookie |
| POST | `/auth/mfa/verify` | session (pre-MFA) | `{code}` or `{recovery_code}` → `200 {user, memberships}` (session rotated) |
| POST | `/auth/logout` | session | → `204` |
| POST | `/auth/logout-others` | session | → `204` |
| GET | `/auth/me` | session | → `200 {user, memberships[], csrf_token}` |
| PATCH | `/auth/me` | session | `{display_name?, ui_locale?}` → `200 {user}` |
| POST | `/auth/password/change` | session | `{current_password, new_password}` → `204` |
| POST | `/auth/password/forgot` | none | `{email}` → `202` (always) |
| POST | `/auth/password/reset` | none | `{token, new_password}` → `204` |
| POST | `/auth/mfa/totp/setup` | session | → `200 {otpauth_url, qr_svg}` (pending secret held in session) |
| POST | `/auth/mfa/totp/enable` | session | `{code}` → `200 {recovery_codes: ["xxxx-xxxx", …10]}` |
| POST | `/auth/mfa/totp/disable` | session | `{password, code}` → `204` (forbidden for platform admins / when tenant requires MFA) |
| GET | `/auth/invitations/{token}` | none | → `200 {tenant_name, email, role, user_exists}` |
| POST | `/auth/invitations/accept` | none or session | `{token, display_name?, password?}` → `200 {user, memberships}` + Set-Cookie |

Example login:
```http
POST /api/v1/auth/login
Content-Type: application/json

{"email":"herbert@example.com","password":"correct horse battery staple"}
```
```json
{
  "user": { "id": "0190…", "email": "herbert@example.com", "display_name": "Herbert", "is_platform_admin": true, "totp_enabled": true },
  "mfa_required": true,
  "memberships": []
}
```
```json
// GET /auth/me (after MFA)
{
  "user": { "id": "0190…", "email": "herbert@example.com", "display_name": "Herbert", "is_platform_admin": true, "totp_enabled": true, "ui_locale": "en" },
  "memberships": [ { "tenant_id": "0190a000…", "tenant_slug": "amaradining", "tenant_name": "Amara Dining", "role": "owner", "branch_ids": null } ],
  "csrf_token": "b3JhbmdlLXRyZWUtZmx5"
}
```

### 8.4 Admin endpoints

Prefix `/admin/tenants/{tid}`. Auth = session (MFA-verified) + membership + permission (§7.2). All mutating calls write `audit_log` in the same transaction.

**Tenant, domains, branches**

| Method | Path | Perm | Notes |
|---|---|---|---|
| GET | `/` | tenant.read | Tenant resource incl. theme/settings/contact/locales, `version` |
| PATCH | `/` | branding / locale_currency | Partial update; field-level permission check |
| GET | `/dashboard` | tenant.read | Counts, sold-out list, recent audit, items missing translations/images/prices |
| GET POST | `/domains` | domain.manage | POST `{hostname}` → `201 {id, hostname, status:"pending", dns:{txt_name, txt_value, cname_target}}` |
| POST | `/domains/{id}/verify` | domain.manage | Re-checks DNS → `200 {status}` |
| PATCH | `/domains/{id}` | domain.manage | `{is_primary}` |
| DELETE | `/domains/{id}` | domain.manage | `204` |
| GET POST | `/branches` | branch.manage (GET: tenant.read) | |
| PATCH DELETE | `/branches/{id}` | branch.manage | Can't delete the default branch |

**Menus, schedules, categories**

| Method | Path | Notes |
|---|---|---|
| GET | `/menus` | All menus with schedules and branch links |
| POST | `/menus` | `{slug, name, description?, is_active?, branch_ids?: []}` |
| PATCH / DELETE | `/menus/{id}` | DELETE soft-deletes the menu and its categories (items remain, unplaced) |
| PUT | `/menus/order` | `{ids: [uuid, …]}` full ordered list |
| PUT | `/menus/{id}/schedules` | Replace set: `{schedules: [{days, start_time:"16:00", end_time:"19:00", valid_from?, valid_to?}]}` |
| GET | `/menus/{id}/categories` | Tree: `[{…, children: […]}]` |
| POST | `/menus/{id}/categories` | `{name, slug?, parent_id?, icon?, description?, is_visible?}`. Slug auto-generated from the default-locale name if omitted |
| PATCH / DELETE | `/categories/{id}` | DELETE: `409` if it has children unless `?cascade=true` |
| PUT | `/menus/{id}/categories/order` | `{parent_id: null | uuid, ids: [...]}` (reorder within one sibling set; moving between parents is a PATCH of `parent_id`) |
| GET | `/categories/{id}/items` | Items in category order (compact item shape) |
| PUT | `/categories/{id}/items` | `{item_ids: [...]}` sets membership **and** order atomically (drag-drop save) |
| POST | `/categories/{id}/items` | `{item_id, position?}` add placement |
| DELETE | `/categories/{id}/items/{itemId}` | Remove placement (item kept) |

**Items**

| Method | Path | Notes |
|---|---|---|
| GET | `/items` | Filters: `q, category_id, menu_id, tag, available, visible, has_image, missing_locale=km, price_on_request, deleted=true`. Sort: `name, code, -updated_at, price`. Paginated |
| POST | `/items` | Full aggregate create (below) |
| GET | `/items/{id}` | Full aggregate: item + variants + tags + modifier_group_ids + category placements |
| PATCH | `/items/{id}` | Partial. If `variants` is present it is the **complete** desired list (items with `id` update, without `id` create, missing are soft-deleted). Same for `tag_ids`, `modifier_group_ids`, `category_ids` |
| DELETE | `/items/{id}` | Soft delete |
| POST | `/items/{id}/restore` | |
| POST | `/items/{id}/duplicate` | `{code?}` → new item, `code` cleared unless given, name suffixed " (copy)" |
| PATCH | `/variants/{id}` | Quick inline edit `{price_minor?, price_on_request?, is_available?}`. Item `If-Match` required |
| POST | `/availability` | Bulk toggle (staff allowed) |
| POST | `/prices/preview` | Bulk price change preview |
| POST | `/prices/apply` | Apply previewed change |

Create item request:
```json
{
  "code": "083",
  "name": { "en": "Fresh Spring Rolls", "km": "" },
  "description": { "en": "Avocado, fresh organic cucumber, carrot, basil leaf, local lettuce, peanut with sweet-sour dip." },
  "media_id": null,
  "is_visible": true,
  "is_available": true,
  "category_ids": ["0190a0f2-…"],
  "tag_ids": ["<gluten_free>", "<peanuts>"],
  "modifier_group_ids": [],
  "variants": [
    { "name": { "en": "Pork" },      "price_minor": 400, "is_default": true },
    { "name": { "en": "Prawn" },     "price_minor": 600 },
    { "name": { "en": "Vegetable" }, "price_minor": 350, "code": "083v", "tag_ids": ["<vegetarian>"] }
  ]
}
```
Response `201`, `ETag: "v1"`, `Location: /api/v1/admin/tenants/{tid}/items/{id}`:
```json
{
  "id": "0190a0f3-…", "code": "083", "slug": "fresh-spring-rolls", "version": 1,
  "name": { "en": "Fresh Spring Rolls" }, "description": { "en": "…" },
  "media": null, "is_visible": true, "is_available": true,
  "categories": [ { "id": "0190a0f2-…", "name": { "en": "Starter" }, "menu_id": "…", "sort_order": 0 } ],
  "tags": [ { "id": "…", "code": "gluten_free", "kind": "dietary" }, { "id": "…", "code": "peanuts", "kind": "allergen" } ],
  "modifier_groups": [],
  "variants": [
    { "id": "…", "code": null,   "name": { "en": "Pork" },      "price_minor": 400, "price_on_request": false, "is_default": true,  "is_available": true, "sort_order": 0, "tags": [] },
    { "id": "…", "code": null,   "name": { "en": "Prawn" },     "price_minor": 600, "price_on_request": false, "is_default": false, "is_available": true, "sort_order": 1, "tags": [] },
    { "id": "…", "code": "083v", "name": { "en": "Vegetable" }, "price_minor": 350, "price_on_request": false, "is_default": false, "is_available": true, "sort_order": 2, "tags": [ { "id": "…", "code": "vegetarian" } ] }
  ],
  "created_at": "2026-11-11T20:09:00Z", "updated_at": "2026-11-11T20:09:00Z"
}
```
Business validation (422): name present for the default locale; ≥1 variant; exactly one `is_default` (if none is flagged, the first becomes default); `price_minor` xor `price_on_request`; variant names required when >1 variant; codes unique (409); `tag_ids` visible to the tenant; `category_ids` / `modifier_group_ids` belong to the tenant (404 otherwise).

Bulk availability:
```json
POST /availability
{ "changes": [
  { "item_id": "…", "is_available": false },
  { "item_id": "…", "variant_id": "…", "is_available": false },
  { "item_id": "…", "branch_id": "…", "is_available": false }
] }
→ 200 { "updated": 3, "content_version": 1843 }
```
Branch-scoped staff may only send changes with their `branch_id` (a global toggle needs manager+).

Bulk price edit:
```json
POST /prices/preview
{
  "selection": { "item_ids": ["…"], "category_id": null, "tag": null },
  "operation": { "type": "percent", "value": 10 },          // percent | add_minor | set_minor
  "rounding": { "mode": "nearest", "step_minor": 25 },      // nearest | up | down; step 1 = none
  "scope": "all_variants"                                   // all_variants | default_variant
}
→ 200 {
  "preview_token": "pv_7f3c…",             // HMAC over (selection, op, current variant versions), valid 10 min
  "changes": [ { "item_id": "…", "code": "083", "variant_id": "…", "variant": "Pork", "from_minor": 400, "to_minor": 450 } ],
  "skipped": [ { "variant_id": "…", "reason": "price_on_request" } ]
}
POST /prices/apply { "preview_token": "pv_7f3c…" }
→ 200 { "updated": 42, "content_version": 1850 }   // 409 conflict if any variant changed since preview
```

**Modifier groups, tags**

| Method | Path | Notes |
|---|---|---|
| GET / POST | `/modifier-groups` | POST `{code, name, min_select, max_select, modifiers: [{name, code?, price_delta_minor, is_default?}]}` |
| GET / PATCH / DELETE | `/modifier-groups/{id}` | PATCH `modifiers` = complete list (same semantics as variants). GET includes `used_by_count` |
| GET | `/tags` | System + tenant tags, `?kind=` |
| POST / PATCH / DELETE | `/tags` / `/tags/{id}` | Tenant tags only. System tags → 403 |

**Media**

| Method | Path | Notes |
|---|---|---|
| POST | `/media` | `multipart/form-data`: `file` (≤ 10 MB, jpeg/png/webp/avif, ≤ 40 MP), `alt_en?`, `purpose` = `item|banner|logo|category`. Processed synchronously (p95 < 2 s) → `201 {id, status:"ready", width, height, variants[], dominant_color}` |
| GET | `/media` | Library, paginated, `?unused=true` |
| PATCH | `/media/{id}` | `{alt, focal_x, focal_y}` |
| DELETE | `/media/{id}` | 409 if referenced unless `?detach=true` |

**Imports / exports**

| Method | Path | Notes |
|---|---|---|
| POST | `/imports` | multipart: `file`, `kind=items_csv|modifier_groups_csv`, `mode=upsert|replace`, `apply_order=bool` → dry-run result |
| GET | `/imports/{id}` | Status + summary + errors |
| POST | `/imports/{id}/commit` | → `200 {status:"committed", summary}`. `409 import_stale` if `content_version` moved. >2,000 rows → `202` + job, poll GET |
| GET | `/exports/items.csv?menu_id=` | `text/csv; charset=utf-8` with BOM, streamed |
| GET | `/exports/modifier-groups.csv` | |

Dry-run response:
```json
{
  "id": "0190b…", "status": "validated", "kind": "items_csv", "mode": "upsert",
  "summary": { "rows": 86, "items": { "create": 61, "update": 3, "unchanged": 1, "delete": 0 },
               "variants": { "create": 80, "update": 4, "delete": 0 }, "categories_created": ["Wine > Rosé"],
               "warnings": 2, "errors": 1 },
  "errors": [ { "row": 14, "column": "price", "code": "invalid_price", "message": "\"6,00$\" is not a valid USD amount (use 6.00)" } ],
  "warnings": [ { "row": 52, "column": "item_tags", "code": "unknown_tag", "message": "Unknown tag \"spicey\"; ignored" } ],
  "expires_at": "2026-11-11T21:09:00Z"
}
```
A commit is refused while `errors > 0`.

**Tables / QR**

| Method | Path | Notes |
|---|---|---|
| GET / POST | `/tables` | `?branch_id=`. POST `{branch_id, label, area?, seats?}` or bulk `{branch_id, labels: ["T1".."T20"]}` |
| PATCH / DELETE | `/tables/{id}` | |
| POST | `/tables/{id}/rotate-qr` | New token; old QR → 404 "This code is no longer valid, ask staff" |
| GET | `/tables/{id}/qr.svg?size=512&logo=true` | `image/svg+xml`, error correction level Q (H with logo) |
| GET | `/tables/{id}/qr.png?size=1024` | |
| GET | `/tables/qr-sheet.pdf?branch_id=&layout=a4-6` | Printable sheet: logo, table label, QR, "Scan for menu" in tenant locales. Layouts `a4-1`, `a4-6`, `a6-tent` |

QR target URL: `https://<primary-domain-or-platform-host>/<slug-if-platform>/q/<qr_token>`.

**Members, invitations, audit, preview**

| Method | Path | Notes |
|---|---|---|
| GET | `/members` | `[{membership_id, user:{id,email,display_name,totp_enabled,last_login_at}, role, branch_ids}]` |
| PATCH | `/members/{membershipId}` | `{role?, branch_ids?}`. Last owner protected (422) |
| DELETE | `/members/{membershipId}` | Revokes that user's sessions' access to the tenant |
| GET / POST | `/invitations` | POST `{email, role, branch_ids?}` → `201` (email sent via job) |
| POST | `/invitations/{id}/resend` | |
| DELETE | `/invitations/{id}` | Revoke |
| GET | `/audit` | `?entity_type=&entity_id=&actor_user_id=&action=&from=&to=` paginated (newest first) |
| POST | `/preview-tokens` | `{branch_id?, at?}` → `{url, token, expires_at}`. HMAC-signed, 30 min |

Audit entry shape:
```json
{ "id": 98123, "action": "item.update", "entity_type": "item", "entity_id": "0190a0f3-…",
  "actor": { "id": "…", "display_name": "Herbert" }, "actor_ip": "203.0.113.7",
  "diff": { "variants[2].price_minor": [350, 375] }, "created_at": "2026-11-11T20:10:12Z", "request_id": "01JCX…" }
```
(`diff` is computed from `before`/`after` at read time.)

### 8.5 Platform endpoints (`/platform`, platform admin + MFA)

| Method | Path | Notes |
|---|---|---|
| GET / POST | `/platform/tenants` | POST `{slug, name, owner_email, locales, default_locale, currency, timezone}` → creates tenant, default branch "Main", menus "Food"/"Drinks", owner invitation |
| GET / PATCH | `/platform/tenants/{id}` | `{status, slug, features}`. Slug change keeps a 301 from the old slug for 90 days (`tenant_slug_redirects`, Phase 1.1) |
| POST | `/platform/tenants/{id}/support-session` | `{reason}` → time-boxed (1 h) owner-equivalent access. Audited, banner shown |
| GET | `/platform/users?q=` | |
| GET | `/platform/audit` | Platform-level events |
| GET | `/platform/domains?status=` | |

### 8.6 Internal / ops

| Method | Path | Notes |
|---|---|---|
| GET | `/healthz` | Liveness, no dependencies |
| GET | `/readyz` | DB + Valkey + storage reachability |
| GET | `/internal/tls-ask?domain=` | Caddy on-demand TLS. Bound to the internal network only |
| GET | `/metrics` | Prometheus (internal network) |

---
## 9. Visitor app

### 9.1 Routes (platform host shown; on a custom domain drop the `/{slug}` prefix)

| Path | Behaviour |
|---|---|
| `/{slug}/` | Menu, default locale, default branch |
| `/{slug}/{locale}/` | Menu in a non-default locale (e.g. `/amaradining/km/`). Default locale has no prefix. `hreflang` alternates on every page |
| `/{slug}/b/{branch}/` and `/{slug}/{locale}/b/{branch}/` | Branch-specific menu (availability, menus, overrides) |
| `/{slug}/i/{item-slug}` | Item share page: SSR full page with the modal open, OG tags (title, description, image), canonical |
| `/{slug}/q/{qrToken}` | Resolve table → set cookie `menu_ctx={branch, table}` (Lax, 12 h) → 302 to the branch menu. Phase 2: starts a table session |
| `/{slug}/manifest.webmanifest`, `/{slug}/sw.js` | PWA (if `settings.pwa_enabled`) |
| `/sitemap.xml`, `/robots.txt` | Platform: index of tenants not `noindex`. Custom domain: that tenant only |
| `/` (platform host) | Simple platform landing page (static) |

Query parameters: `?preview=<token>` (admin preview, `noindex`, `no-store`).

### 9.2 Page structure (top to bottom)
1. **Header / banner:** tenant banner image (`<picture>` with WebP `srcset`, `fetchpriority="high"`, explicit width/height) or logo plus name, in the style of Amara's `nav-01.png`. Under it: compact info row (opening status "Open until 23:00", phone `fa-phone`, map `fa-location-dot`, socials).
2. **Toolbar** (sticky top, `position: sticky`): language switch (`fa-globe`, a link list, not a form, so it works without JS), search button (`fa-magnifying-glass`) that expands to an input, filter button (`fa-filter`) with an active-count badge.
3. **Menu tabs** (if >1 active menu): Bootstrap `nav-pills` (Food | Drinks | Happy hour).
4. **Category nav** (sticky below the toolbar): horizontally scrollable `nav` of anchor links, one per top-level category, each with an FA icon. Scrollspy highlights the current section and auto-scrolls the active pill into view (`scrollIntoView({inline:'center', block:'nearest'})`). Implemented with `IntersectionObserver` (rootMargin offset = sticky header height), not Bootstrap ScrollSpy, for precise offset control.
5. **Filter chips** row (collapsible): tenant's `is_filterable` tags as toggle buttons (`btn btn-sm btn-outline-*`, `aria-pressed`). Multi-select = AND.
6. **Sections:** `<section id="c-{slug}" aria-labelledby>` with `<h2>`. Subcategories are `<h3>` blocks (Spirits → Gin, Vodka …).
7. **Item row** (layout from theme: `list` default, `cards`, `compact` for drinks):
   - Optional thumbnail (lazy, 80×80 list / 4:3 card) using `dominant_color` as the placeholder background.
   - Name (`<h4 class="h6">`), secondary-locale name under it in a muted style if the tenant shows both (setting `show_secondary_name`). Khmer text is wrapped in `lang="km"`.
   - Code (if `show_item_codes`) as a muted monospace badge, for example `083`.
   - Description, clamped to 2 lines (`-webkit-line-clamp`), full text in the modal.
   - Price right-aligned: single price `$9.50`, variants `from $3.50`, price on request shown as the label "Ask staff". Secondary currency below: `≈ 39,000៛`.
   - Badges: FA icons for dietary tags (leaf etc., with `title` and visually-hidden text), "Vegetarian option" pill when matched via a variant only, spice peppers, "Sold out" badge (`text-bg-secondary`) with the row at 55% opacity and `aria-disabled`.
   - The whole row is a `<button>` that opens the modal (keyboard accessible).
8. **Footer:** footer note (VAT/service), allergen disclaimer, "Powered by" (removable via feature flag).

### 9.3 Item detail modal
Bootstrap modal (`modal-fullscreen-sm-down`):
- Image (960w), name (all tenant locales shown if `show_secondary_name`), code, full description, tag and allergen list ("Contains: peanuts").
- **Variants:** radio list (`form-check`), price per variant, unavailable ones disabled with "sold out". Default preselected. If opened while a dietary filter is active, the first variant matching the filter is preselected (e.g. the Vegetarian filter preselects "Vegetable $3.50").
- **Modifier groups:** title + rule hint ("Choose 1", "Choose up to 3", "Optional"). `max_select = 1` → radios, otherwise checkboxes, with max enforced by disabling the rest. Price deltas shown as "+$0.50".
- **Computed total** (variant + modifiers), live. Phase 1 has no add-to-cart. Optional "Show to staff" button enlarges the selection summary (item code, variant code, modifiers) in high contrast to show a waiter. Phase 2 replaces it with "Add to order".
- Deep linking: opening pushes `history.pushState` to `…/i/{item-slug}`. Back closes the modal. A direct load of `/i/…` renders the menu with the modal open.

### 9.4 Search and filters (client-side)
- The full menu is in the DOM (~300 items). Each item element carries `data-search` (normalised: lowercase, NFKD, diacritics stripped, all locales, code) and `data-tags` (item + variant tags).
- Search: debounce 120 ms, min 1 char (codes like `83`). Matches substrings across all locales, including Khmer (no word boundaries assumed). Non-matching items and empty sections are hidden. Result count is announced via an `aria-live="polite"` region. An empty state offers "Clear search". `Esc` clears.
- Filters and search combine (AND). The active state is reflected in the URL (`?tags=vegetarian,gluten_free&q=amok`, `replaceState`) so it can be shared.
- Without JS the full menu is still readable and the language links work. Search and filters require JS.

### 9.5 Language and currency
- Locale switch = links to the locale-prefixed URL. Preference stored in cookie `menu_locale` (1 year). On the first visit only, `Accept-Language` picks the locale, with a 302 to the prefixed path when it differs from the default and the tenant supports it, then `Vary: Accept-Language` on that redirect only.
- Fonts: Khmer pages load `Kantumruy Pro` (or `Noto Sans Khmer`) subset, `font-display: swap`, preloaded only on `km` pages.
- Currency: primary always shown. Secondary (e.g. KHR) shown if `show_secondary_currency`: `round(price_minor × rate / 10^(exp_primary − exp_secondary), step=secondary_round_minor)`, formatted `Intl.NumberFormat(locale, {style:'currency', currency:'KHR', currencyDisplay:'narrowSymbol'})`. Labelled "≈" because it is display only.

### 9.6 SEO
- SSR HTML. `<title>`, meta description, canonical (custom primary domain if any), `hreflang` per locale + `x-default`, Open Graph/Twitter cards (banner image 1200×630 variant), `robots` from `settings.seo.noindex`.
- **schema.org JSON-LD** (one `<script type="application/ld+json">`):
```json
{
  "@context": "https://schema.org",
  "@type": "Restaurant",
  "name": "Amara Dining",
  "url": "https://menu.example.com/amaradining/",
  "servesCuisine": ["Cambodian", "Western"],
  "priceRange": "$$",
  "hasMenu": {
    "@type": "Menu",
    "name": "Food",
    "inLanguage": "en",
    "hasMenuSection": [{
      "@type": "MenuSection",
      "name": "Starter",
      "hasMenuItem": [{
        "@type": "MenuItem",
        "name": "Fresh Spring Rolls",
        "description": "Avocado, fresh organic cucumber, carrot, basil leaf, local lettuce, peanut with sweet-sour dip.",
        "suitableForDiet": ["https://schema.org/GlutenFreeDiet"],
        "offers": [
          { "@type": "Offer", "name": "Pork", "price": "4.00", "priceCurrency": "USD" },
          { "@type": "Offer", "name": "Prawn", "price": "6.00", "priceCurrency": "USD" },
          { "@type": "Offer", "name": "Vegetable", "price": "3.50", "priceCurrency": "USD" }
        ],
        "menuAddOn": []
      }]
    }]
  }
}
```
  Multiple menus → `hasMenu` array. Price-on-request items omit `offers`. Sold-out items get `offers.availability = https://schema.org/OutOfStock`. Modifier groups map to `menuAddOn` (`MenuSection` of `MenuItem`s).

### 9.7 Accessibility (WCAG 2.2 AA)
- Landmarks (`header`, `nav[aria-label="Categories"]`, `main`, `footer`), a "Skip to menu" link, one `h1` (restaurant name), logical heading levels.
- All interactive elements are reachable by keyboard with a visible focus ring (`:focus-visible`, 2px outline in a contrast colour). Modal focus trap and return focus come from Bootstrap. Chips use `aria-pressed`. The current category link has `aria-current="true"`.
- Theme contrast is validated in the admin (warning below 4.5:1 for text on primary/background, hard block below 3:1). Text colour on primary is auto-derived (black/white by WCAG relative luminance).
- Icons are decorative (`aria-hidden="true"`) with visually-hidden text equivalents. Prices are read as "4 dollars" via `aria-label` on the price element.
- `prefers-reduced-motion` disables smooth scroll and animations. Touch targets ≥ 44×44 px. Zoom isn't blocked (no `maximum-scale`).
- `lang` on `<html>` plus `lang` on mixed-language fragments.

### 9.8 Performance budget (enforced in CI by Lighthouse CI on the seeded Amara tenant, Moto G Power profile, slow 4G)

| Metric | Budget |
|---|---|
| LCP | ≤ 2.0 s |
| CLS | ≤ 0.05 |
| INP (field) / TBT (lab) | ≤ 200 ms / ≤ 150 ms |
| HTML (300 items) | ≤ 60 KB gzip |
| CSS (Bootstrap purged + theme) | ≤ 25 KB gzip, critical CSS inlined ≤ 10 KB |
| JS | ≤ 30 KB gzip total, `defer`, no third-party |
| Icons | inline SVG sprite ≤ 8 KB |
| Images | lazy (`loading="lazy" decoding="async"`), except banner. WebP, `srcset`/`sizes`, explicit dimensions |
| Fonts | ≤ 2 families, ≤ 60 KB WOFF2 total, `font-display: swap` |
| Lighthouse | Performance ≥ 95, Accessibility 100, Best Practices ≥ 95, SEO 100 |
| TTFB (CDN hit / miss) | ≤ 100 ms / ≤ 400 ms |

### 9.9 PWA (optional, `settings.pwa_enabled`)
- Per-tenant manifest (name, theme colour, icons generated from the logo at 192/512 plus maskable).
- Service worker (Workbox): HTML stale-while-revalidate (last menu available offline with an "Offline, showing last saved menu" banner), images cache-first (max 200 entries / 30 days), API not cached. Scope `/{slug}/`.
- No install prompt nagging. The browser's default prompt is used.

### 9.10 Theming
Theme JSON → CSS custom properties in a nonce'd `<style>` (`--bs-primary`, `--bs-primary-rgb`, `--menu-bg`, `--menu-surface`, `--menu-radius`, fonts). Bootstrap 5.3 is compiled once with CSS-variable-driven colours, so there is no per-tenant Sass build.

---

## 10. Admin app

SPA at `https://admin.menu.example.com`. Layout: left sidebar (collapsible offcanvas on mobile), top bar with a tenant switcher (for users with more than one membership), a "View menu" link, and a user menu. Bootstrap 5.3 with dark mode toggle (`data-bs-theme`). FA icons. All lists are responsive tables that become cards below the `md` breakpoint. UI locale is en first, with km as an option (UI strings in `packages/shared/i18n`).

General behaviours: TanStack Query caches. Optimistic updates for toggles and reorder, with rollback on error. Toasts for success/failure. Unsaved-changes guard on forms. `If-Match` on every save. On 412 a dialog shows "Someone else changed this item" with Reload/Overwrite. Keyboard shortcuts: `/` search, `n` new item, `Ctrl/⌘+S` save.

| # | Screen | Contents and behaviour |
|---|---|---|
| A1 | **Login / MFA / invite accept / reset** | Email + password, then TOTP or recovery code step. Invite page shows tenant + role. Password strength meter (zxcvbn-ts). |
| A2 | **Dashboard** | Cards: items (visible/hidden/sold out), menus active now, items missing `km` translation, missing images, price-on-request count. "Sold out today" list with one-click restore. Recent changes (audit). QR quick links. Staff land on the Availability screen instead. |
| A3 | **Branding & settings** | Name, logo, banner (upload + crop with focal point), theme colours (picker + contrast checker), fonts (whitelist), layout (list/cards/compact), toggles (show codes, hide sold out, secondary currency + rate, PWA), contact & socials, opening hours, SEO fields, footer note. **Live preview** pane (iframe of the visitor with a preview token and an unsaved theme passed via `postMessage`). |
| A4 | **Locales & currency** (owner) | Enabled locales (en, km, plus any BCP-47), default locale, currency (ISO 4217 select), timezone. Changing currency warns "prices are not converted". |
| A5 | **Domains** (owner) | List with status chips, DNS instructions (copy buttons), verify button, set primary. |
| A6 | **Branches** | CRUD, default branch, timezone, which menus are linked. |
| A7 | **Menus** | List with drag-drop order, active toggle, schedule editor (weekday chips + time range rows, "add window", date range), branch links. A "Now showing" indicator computed in the tenant timezone. |
| A8 | **Menu editor** (core screen) | Two panes. **Left:** category tree (drag-drop reorder, drag a category onto another to nest at depth ≤ 2, inline rename, icon picker with FA search, visibility toggle). **Right:** items of the selected category in order. Drag handle to reorder (saves via `PUT /categories/{id}/items`, debounced 400 ms). Inline toggles for available/visible. Inline price edit for single-variant items. "Add existing item" (typeahead), "New item". Multi-select checkboxes for bulk actions: move/add to category, tag, hide, sold out, delete, bulk price. |
| A9 | **Item editor** (drawer or full page) | Tabs per locale for name/description (default locale required; missing-translation badge on the tab). Code (uniqueness checked on blur). Image (upload/drag-drop, library picker, focal point). **Variants table:** name per locale, code, price (masked input in tenant currency, stored as minor), price-on-request toggle, default radio, available toggle, variant tags, drag order. "Single price" mode hides the table. **Modifier groups:** attach existing reusable groups (with a "used by N items" hint), per-item min/max override, "create group" inline. **Tags:** dietary/spice/badge chips, allergen checklist. **Placements:** categories (multi). Embedded-price lint (F8) with a "Convert to variants" helper that parses `name price` pairs from the description into variant rows for review. Sidebar: preview card (exactly as the visitor renders), audit history for this item. |
| A10 | **Modifier groups** | List (code, name, rule, used-by count). Editor: name per locale, min/max, modifiers table (name, code, price delta, default, available, drag order). |
| A11 | **Tags** | System tags (read-only, can be marked filterable per tenant via `settings.filter_tags`) + custom tags CRUD (kind, icon, colour, filterable). |
| A12 | **All items** | Searchable, filterable, paginated table across menus: code, name, categories, price(s), tags, flags, updated. Saved filter "needs attention". Bulk actions as A8. Trash view (deleted items, restore). |
| A13 | **Bulk price edit** | Select via filter or checkbox → operation (+/− %, + amount, set) → rounding (step: none/0.05/0.10/0.25/0.50/1.00) → scope → **preview diff table** (old → new, highlighted) → Apply. Undo = audit entry offers "Revert this bulk change" (applies the inverse if prices are unchanged since). |
| A14 | **Availability** (staff home) | Large-touch list grouped by category with a search box. Each item/variant has a big toggle (Available / Sold out), a branch selector (limited to the staff member's branches), "Reset all sold-out" button (end of day). Optimistic with immediate effect on the visitor (cache invalidation ≤ 5 s). |
| A15 | **Media library** | Grid, upload (multi, drag-drop, progress), alt text per locale, focal point, usage, unused filter, delete. |
| A16 | **Import / Export** | Download template + current export. Upload CSV → dry-run report (summary counts, error table with row/column/message, warnings, created categories) → Commit button (disabled with errors) → result. Mode explanation (upsert vs replace). History of imports. |
| A17 | **Preview** | Opens the visitor in a new tab or embedded device frame (phone/tablet widths) with a preview token. Options: branch, "simulate time" (schedule testing), show hidden items. |
| A18 | **Tables & QR** | Per branch: table list (label, area, seats, active), bulk create ("T1–T20"), QR preview, download SVG/PNG per table, printable PDF sheet (layout picker), rotate QR (with confirmation). Branch-level "menu QR" (no table) too. |
| A19 | **Members** | List with role, branches, 2FA status, last login. Invite (email + role + branches). Change role. Remove. Pending invites (resend/revoke). Owner can set "Require 2FA for all members". |
| A20 | **Audit log** | Filterable timeline (actor, action, entity, date range). Entry detail shows a before/after diff (JSON diff viewer). Links to the entity. |
| A21 | **Profile** | Name, UI language, password change, 2FA setup (QR + recovery codes download), active sessions with "log out others". |
| P1–P3 | **Platform** (superadmin) | Tenants list (status, slug, created, item count, last activity), create tenant wizard, tenant detail (features/limits JSON form, suspend/restore, slug change, start support session), platform audit. |

---

## 11. Media and file storage

- **Buckets / prefixes** (`S3_BUCKET_PUBLIC`, `S3_BUCKET_PRIVATE`, which may be the same bucket with prefix policies):
  - Private: `originals/t/{tenant_id}/m/{media_id}/original.{ext}`, plus import files `imports/t/{tenant_id}/{import_id}.csv` (lifecycle: delete after 30 days).
  - Public (served via CDN `CDN_BASE_URL`): `t/{tenant_id}/m/{media_id}/{w}.webp`. Keys are immutable (a new upload means a new media id), so `Cache-Control: public, max-age=31536000, immutable`.
- **Pipeline** (`apps/api/src/lib/image.ts`, sharp):
  1. Stream the upload with `@fastify/multipart` (limit 10 MB). Sniff magic bytes (`file-type`) and allow only jpeg/png/webp/avif. Reject when the extension and content disagree.
  2. `sharp(buf, { limitInputPixels: 40e6, failOn: 'error' }).rotate()` (auto-orient), strip metadata (the default; never `withMetadata`), convert to sRGB.
  3. Variants (`fit: 'inside'`, no upscaling) at WebP quality 78, effort 4:
     - item / category: 160, 480, 960, 1600 w
     - banner: 768, 1280, 1920 w, plus `og` 1200×630 cover crop at the focal point
     - logo: 128, 256, 512 (PNG kept for transparency + WebP). PWA icons 192/512 and maskable are generated on demand.
  4. Dominant colour (`stats().dominant`) is stored for the placeholder background.
  5. Upload original + variants (parallel), insert `media` row `status='ready'`. On failure, `status='failed'` with the error logged; partial objects are deleted.
- The client never receives storage credentials or presigned PUTs in Phase 1, so all uploads go through the API (simpler validation). If uploads get large, presigned upload + async processing via pg-boss is a drop-in (`status='pending'`).
- **GC job** (daily): media `deleted_at` older than 30 days and unreferenced → delete objects + row. Orphan sweep compares bucket listing vs DB monthly.
- Remote image import (CSV `image_url`): job fetches with a 10 s timeout and 10 MB cap, over http(s) only. Hostnames are DNS-resolved and private, loopback and link-local ranges are rejected (SSRF guard via `ssrf-req-filter`), with max 3 redirects each re-checked.

---

## 12. Caching and CDN

| Layer | What | Key / TTL | Invalidation |
|---|---|---|---|
| Postgres | `tenants.content_version` bumped by statement triggers on every content write | n/a | n/a |
| API in-process LRU | Assembled public menu JSON | ETag (tenant, content_version, locale, branch, active-menu hash). 10 min | `LISTEN content_changed` → drop tenant entries |
| API HTTP | Public endpoints | `ETag`, `max-age=60, s-maxage=300, swr=86400, sie=86400` | Conditional requests |
| Visitor in-process | Rendered HTML per (tenant, locale, branch, path), with API ETag stored | 60 s, revalidated with `If-None-Match` | `LISTEN content_changed` (visitor holds its own PG listener connection via `menu_system`, or polls the API ETag every 5 s if DB access is undesired) |
| CDN (Cloudflare or any) | Visitor HTML | `Cache-Control: public, max-age=0, s-maxage=60, stale-while-revalidate=600`; `Cache-Tag: tenant-{id}` | On `content_changed`, debounced 2 s, purge by tag/URL prefix via provider API (`CDN_PURGE_*` env). Without purge support: ≤ 60 s staleness |
| CDN | Images | immutable 1 year | Never (new keys) |
| Browser | Static assets (hashed filenames) | immutable 1 year | Deploy |
| Admin API | Never cached | `Cache-Control: no-store` | n/a |

Target: a sold-out toggle is visible to guests within 5 s (no CDN, or a CDN with purge). Worst case is 60 s when the CDN can't purge.

---

## 13. Internationalisation and money

- **Content locales:** per tenant (`tenants.locales`, ≥1, default required). Phase 1 ships en and km UI support. Other locales work for content (any BCP-47 tag), but the visitor UI strings fall back to English until a translation file exists.
- **UI strings:** `packages/shared/i18n/{en,km}.json`, ICU MessageFormat via `@formatjs/intl` (plurals, e.g. "{count, plural, one {# item} other {# items}}"). The visitor UI uses the content locale and the admin UI uses `users.ui_locale`.
- **Fallback for content fields:** requested locale → tenant default → first non-empty value. The visitor marks fallback text with the original `lang` attribute.
- **Formatting:** all prices, numbers and dates go through `Intl` in `packages/shared/money.ts`:
  ```ts
  export function formatMoney(minor: bigint | number, currency: string, locale: string, display: 'symbol'|'narrowSymbol'|'code' = 'narrowSymbol') {
    const nf = new Intl.NumberFormat(locale, { style: 'currency', currency, currencyDisplay: display });
    const exp = nf.resolvedOptions().maximumFractionDigits ?? 2;
    return nf.format(Number(minor) / 10 ** exp);
  }
  export function parseMoney(input: string, currency: string): bigint  // strict: /^\d+(\.\d{1,exp})?$/, no symbols or thousand separators
  ```
- Money arithmetic happens in integers (`bigint` in the API, `number` safe-integer in the browser since values are < 2^53). Percentage changes: `round_half_even(minor × (100 + pct) / 100)`, then step rounding.
- Timezones: schedules and opening hours are stored as local `time` and evaluated with `Temporal` (polyfill `temporal-polyfill`) in the branch/tenant IANA zone.

---

## 14. CSV import/export

### 14.1 Items CSV (`items_csv`)
UTF-8 (BOM optional on import, written on export for Excel), RFC 4180, comma separator, header row required, header names exact (order free, unknown columns → warning). **One row per variant.** Rows for the same item must be contiguous. The first row of an item group carries the item-level fields. Following rows leave them empty (or identical, otherwise error).

| Column | Level | Required | Format / rule |
|---|---|---|---|
| `item_code` | item | yes | Key. `^[A-Za-z0-9][A-Za-z0-9._-]{0,31}$`, case-insensitive match. `@<uuid>` = code-less existing item (export emits this) |
| `menu` | item | first row | Menu slug (`food`). The menu must already exist, otherwise the row is an error |
| `categories` | item | first row | Category path(s) by default-locale name: `Starter`, `Spirits > Gin`. Multiple: `Starter|Specials`. Missing categories are created (listed in dry-run) |
| `item_name_<locale>` | item | default locale on first row | One column per tenant locale (`item_name_en`, `item_name_km`) |
| `description_<locale>` | item | no | Plain text. `\n` allowed inside quotes |
| `variant_code` | variant | no | POS code of the variant (`083v`). Unique per tenant |
| `variant_name_<locale>` | variant | if >1 variant | Empty on a single-variant item |
| `price` | variant | unless on request | Decimal in major units, `.` separator, max currency exponent decimals: `3.50`, `12`. Rejects `6,00$`, `$7` |
| `price_on_request` | variant | no | `true/false/1/0/yes/no`, default false. Must be true iff price empty |
| `is_default_variant` | variant | no | Default: first row of group |
| `variant_tags` | variant | no | Tag codes, `|`-separated (`vegetarian`) |
| `item_tags` | item | no | Tag codes `|`-separated: dietary, allergen, spice, badge (`gluten_free|peanuts|spicy_2`) |
| `modifier_groups` | item | no | Modifier group codes `|`-separated, in display order. Must exist (import modifier groups CSV first) |
| `is_available` | item | no | Default true |
| `is_visible` | item | no | Default true |
| `image_url` | item | no | http(s) URL fetched by a job (§11). Ignored if unchanged since the last import (URL stored in `external_refs.import_image_url`) |

**Matching and modes**
- Items are matched by `item_code` (or `@uuid`). Variants within an item are matched by `variant_code` if present, else by default-locale `variant_name`, else by position.
- `upsert` (default): create new items, update matched ones (only columns present in the file are touched; an empty cell on an item-level column in the first row **clears** optional fields), never delete.
- `replace`: as upsert, plus soft-delete live items of the referenced menus that are absent from the file. The dry-run lists them under `delete`.
- `apply_order=true`: row order defines `sort_order` within each category. Otherwise only new placements are appended.
- Commit runs in one transaction (`withTenant`) and is refused (`409 import_stale`) if `tenants.content_version` ≠ `imports.base_content_version`. It writes one `import.commit` audit entry with the summary plus per-item audit entries.
- Limits: 5 MB, 10,000 rows.
- **Export** writes the same format (round-trip stable: export → import with `upsert` = 0 changes). Cells starting with `= + - @ \t \r` are prefixed with `'` (CSV injection guard), except `item_code` cells of the form `@<uuid>`, which import recognises.

Example (see `docs/seed/amara-sample.csv` for 86 rows):
```csv
item_code,menu,categories,item_name_en,item_name_km,description_en,description_km,variant_code,variant_name_en,variant_name_km,price,price_on_request,is_default_variant,variant_tags,item_tags,modifier_groups,is_available,is_visible,image_url
083,food,Starter,Fresh Spring Rolls,,"Avocado, fresh organic cucumber, carrot, basil leaf, local lettuce, peanut with sweet-sour dip.",,,Pork,,4.00,false,true,,gluten_free|peanuts,,true,true,
083,,,,,,,,Prawn,,6.00,false,false,,,,,,
083,,,,,,,083v,Vegetable,,3.50,false,false,vegetarian,,,,,
886,food,Steak Lover,Filet Steak 250g,,"Tender, lean and juicy filet.",,,,,26.00,false,true,,,steak-sauce,true,true,
X-WINE-WOOLSHED-CHARD,drinks,Wine > White,"Woolshed Chardonnay, Australia",,,,573,Glass,,,true,true,,contains_alcohol|sulphites,,true,true,
X-WINE-WOOLSHED-CHARD,,,,,,,,Bottle,,,true,false,,,,,,
```

### 14.2 Modifier groups CSV (`modifier_groups_csv`)
One row per modifier. Group fields on the first row of each group.

| Column | Level | Rule |
|---|---|---|
| `group_code` | group | Key, `^[a-z0-9][a-z0-9-]{0,39}$` |
| `group_name_<locale>` | group | Default locale required |
| `min_select`, `max_select` | group | Integers, `max ≥ 1`, `max ≥ min` |
| `modifier_code` | modifier | Key within group (optional; else matched by name) |
| `modifier_name_<locale>` | modifier | Default locale required |
| `price_delta` | modifier | Decimal ≥ 0, default 0 |
| `is_default`, `is_available` | modifier | Booleans |

```csv
group_code,group_name_en,group_name_km,min_select,max_select,modifier_code,modifier_name_en,modifier_name_km,price_delta,is_default,is_available
steak-sauce,Choice of sauce,,1,1,red-wine,Red wine sauce,,0.00,false,true
steak-sauce,,,,,black-pepper,Black pepper sauce,,0.00,true,true
steak-sauce,,,,,mushroom,Mushroom sauce,,0.00,false,true
```

---
## 15. Security

Baseline: OWASP ASVS 4.0 Level 2 for the admin/API, OWASP Top 10 (2025) mapping below.

| Risk | Controls |
|---|---|
| A01 Broken access control / tenant isolation | RLS (FORCE) on every tenant table. Composite tenant FKs. `menu_app` has no BYPASSRLS. Membership + permission check on every admin route (one Fastify `preHandler` driven by route config `permission: 'menu.manage'`). Other tenants' IDs → 404. Lint-restricted system pool. Isolation test suite (§16.3). Platform support sessions time-boxed and audited. |
| A02 Security misconfiguration | Helmet headers. Strict CORS (admin origin only, credentials). Swagger UI off in prod unless authenticated. Containers run as non-root with a read-only FS. Secrets only via env/secret store. |
| A03 Supply chain | npm lockfile, `npm audit --omit=dev` in CI, Renovate, Trivy image scan, pinned base images (`node:24-alpine@sha256:…`). Native modules (`sharp`, `@node-rs/argon2`) run their install scripts. |
| A04 Cryptographic failures | TLS everywhere (HSTS `max-age=63072000; includeSubDomains; preload`). argon2id passwords. sha256 for tokens at rest. AES-256-GCM for TOTP secrets (`APP_ENCRYPTION_KEY`, 32 bytes, rotation via key id prefix). HMAC-SHA256 for preview/price tokens (`APP_HMAC_KEY`). |
| A05 Injection | Parameterised SQL only (Drizzle / `sql` tag). No string-built SQL except identifiers in migrations. Zod validation on all inputs. Plain-text content escaped by Eta (`<%= %>` auto-escape) and React. No HTML/Markdown in descriptions in Phase 1. CSV injection guard on export. |
| A06 Insecure design | Optimistic concurrency, import dry-run, bulk-price preview tokens, last-owner protection, soft deletes + audit. |
| A07 Authentication failures | Lockout with backoff, rate limits, generic errors, session rotation, 2FA, breached-password check, session idle/absolute timeouts. |
| A08 Integrity | Signed preview tokens. Audit log append-only by grant. |
| A09 Logging / alerting | pino JSON with request_id, tenant_id, user_id. Auth events (login ok/fail, MFA, lockout, password reset) audited. Alerts on 5xx rate, login failure spikes, RLS violations (SQLSTATE 42501 logged as `security` level). No secrets/PII in logs (pino `redact`: `req.headers.cookie`, `password`, `token`, `code`). |
| A10 SSRF / exceptional conditions | Remote image fetch guard (§11). Central error handler maps to problem+json without stack traces. |

**Rate limits** (Valkey, sliding window):

| Scope | Limit |
|---|---|
| `POST /auth/login` | 5/min per IP+email, 30/hour per IP |
| `POST /auth/mfa/verify` | 5/min per session |
| `POST /auth/password/forgot` | 3/hour per email, 20/hour per IP |
| Public API | 300/min per IP (the CDN absorbs most traffic) |
| Visitor pages | 600/min per IP at the edge |
| Admin API | 600/min per user, uploads 30/min, imports 10/hour per tenant |

**HTTP headers**
- Visitor CSP: `default-src 'none'; script-src 'self' 'nonce-{n}'; style-src 'self' 'nonce-{n}'; img-src 'self' {CDN_BASE_URL} data:; font-src 'self'; connect-src 'self'; manifest-src 'self'; worker-src 'self'; base-uri 'none'; form-action 'self'; frame-ancestors 'none'` (with a valid preview token: `frame-ancestors {ADMIN_ORIGIN}`).
- Admin CSP: `default-src 'self'; script-src 'self'; style-src 'self'; img-src 'self' {CDN_BASE_URL} data: blob:; connect-src {API_ORIGIN}; frame-src {VISITOR_ORIGINS}; frame-ancestors 'none'; base-uri 'none'; form-action 'self'`.
- `X-Content-Type-Options: nosniff`, `Referrer-Policy: strict-origin-when-cross-origin`, `Permissions-Policy: camera=(), microphone=(), geolocation=()`, `Cross-Origin-Opener-Policy: same-origin`.

**Data protection:** minimal PII (staff emails/names only, no guest data in Phase 1). Visitor sets only functional cookies (`menu_locale`, `menu_ctx`), so no consent banner is needed. Tenant data export (owner) = CSV export + media zip (Phase 1.1). Tenant deletion = archive, then hard delete after 30 days by a platform job.

---

## 16. Testing strategy

### 16.1 Layers

| Layer | Tooling | Scope | Gate |
|---|---|---|---|
| Unit | Vitest | money/format/parse, schedule matching (incl. midnight crossing, DST-free zones and DST zones), CSV parser/validator, permission matrix, slug generation, price rounding, availability resolution | 90% line coverage on `packages/shared`, `apps/api/src/lib` |
| DB | Vitest + Testcontainers `postgres:18` | Migrations up from empty, constraints and triggers (variant invariant, depth ≤ 2, content_version bump), **RLS isolation suite** | All pass |
| API integration | Vitest + `fastify.inject` against Testcontainers DB + MinIO | Every endpoint: happy path, validation 422, auth 401/403, cross-tenant 404, If-Match 412/428, pagination, ETag 304 | All pass. OpenAPI snapshot diff reviewed |
| Contract | OpenAPI generated from Zod; admin client generated (`openapi-typescript`) | Admin build fails on drift | CI |
| E2E | Playwright (Chromium, WebKit, mobile viewports) | Visitor: load, scrollspy, search, filters, modal variants/modifiers, locale switch, QR deep link, no-JS readability. Admin: login + MFA, create item with variants, drag-drop reorder, import dry-run → commit, bulk price, sold-out toggle visible on visitor ≤ 5 s, QR PDF download | Critical paths pass |
| Accessibility | `@axe-core/playwright` on visitor + every admin screen | 0 serious/critical violations | CI |
| Performance | Lighthouse CI (budgets §9.8) on seeded Amara; k6: public menu 500 rps for 5 min, p95 < 100 ms (cache hit), error rate < 0.1% | CI (Lighthouse), nightly (k6) | |
| Security | ZAP baseline scan against staging, `npm audit --omit=dev`, Trivy, secret scan (gitleaks) | Weekly + CI | |

### 16.2 Fixtures
- `packages/db/seed/`: platform admin, tenant `amaradining` (from §18), tenant `othertenant` (minimal, for isolation tests).
- Factories (`@faker-js/faker`, seeded) for bulk data (300 items, 25 categories) for perf tests.

### 16.3 Tenant isolation suite (must stay green; runs on every PR)
For **every** table with `tenant_id` (enumerated from `information_schema.columns`, so new tables are covered automatically), as `menu_app` with `app.tenant_id = B`:
1. `SELECT` returns 0 rows of tenant A.
2. `INSERT` with `tenant_id = A` → `42501` (RLS WITH CHECK).
3. `INSERT` with `tenant_id = B` referencing A's parent ID → `23503` (composite FK).
4. `UPDATE`/`DELETE` affecting A's rows → 0 rows.
5. With `app.tenant_id` unset → 0 rows / insert denied.
6. API level: for each admin route, call as a member of B with A's resource IDs → 404, and with A's `tenantId` in the path → 403.
7. `users`, `sessions` not readable by `menu_app` (42501).

A smoke version of 1–5 and 7 was executed against `docs/schema.sql` on PostgreSQL 18 while this spec was written. All checks behaved as specified.

---

## 17. Deployment and operations

### 17.1 Containers (`infra/docker-compose.yml`, dev = all services, prod = same images)

| Service | Image | Notes |
|---|---|---|
| `caddy` | `caddy:2` | TLS (ACME), on-demand TLS with `ask` → `api:3000/internal/tls-ask`, reverse proxy, serves admin static files, compression (zstd/gzip) |
| `api` | `ghcr.io/cursor-vliet-net/menu-api` | Node 24 alpine, `node dist/server.js`, port 3000, ≥ 2 replicas in prod |
| `worker` | same image as api | `node dist/worker.js` (pg-boss jobs) |
| `visitor` | `ghcr.io/cursor-vliet-net/menu-visitor` | port 3001, ≥ 2 replicas |
| `admin` | built into the caddy image or served from object storage | static |
| `migrate` | api image | `node dist/migrate.js` one-shot, runs as `menu_owner` before rollout |
| `postgres` | `postgres:18` | dev only. Prod: managed PG 18 (PITR enabled) |
| `valkey` | `valkey/valkey:8` | rate limits, idempotency keys |
| `minio` | `minio/minio` | dev only. Prod: R2/S3 |
| `mailpit` | `axllent/mailpit` | dev only |

`infra/postgres/init/00-roles.sql` creates `menu_owner`, `menu_app` (NOBYPASSRLS), `menu_system` (BYPASSRLS) with passwords from env.

### 17.2 Environment variables

| Var | Service | Example / notes |
|---|---|---|
| `NODE_ENV` | all | `production` |
| `PLATFORM_HOST` | api, visitor | `menu.example.com` |
| `ADMIN_ORIGIN` | api | `https://admin.menu.example.com` |
| `API_ORIGIN` | admin build, visitor | `https://api.menu.example.com` (visitor uses `API_INTERNAL_URL`) |
| `API_INTERNAL_URL` | visitor | `http://api:3000` |
| `CDN_BASE_URL` | api, visitor | `https://cdn.menu.example.com` |
| `DATABASE_URL_APP` | api, visitor(listen) | `postgres://menu_app:…@postgres:5432/menu` |
| `DATABASE_URL_SYSTEM` | api, worker | `postgres://menu_system:…@postgres:5432/menu` |
| `DATABASE_URL_OWNER` | migrate | `postgres://menu_owner:…@postgres:5432/menu` |
| `DB_POOL_MAX` | api | `20` |
| `REDIS_URL` | api | `redis://valkey:6379/0` |
| `S3_ENDPOINT`, `S3_REGION`, `S3_ACCESS_KEY_ID`, `S3_SECRET_ACCESS_KEY` | api, worker | MinIO/R2 |
| `S3_BUCKET_PUBLIC`, `S3_BUCKET_PRIVATE`, `S3_FORCE_PATH_STYLE` | api, worker | |
| `APP_ENCRYPTION_KEY` | api | 32-byte base64 (TOTP secrets), `kid:key` list for rotation |
| `APP_HMAC_KEY` | api | 32-byte base64 (preview/price tokens, cursors) |
| `SESSION_IDLE_HOURS`, `SESSION_ABSOLUTE_DAYS` | api | `24`, `14` |
| `SMTP_URL`, `MAIL_FROM` | worker | `smtp://…`, `Menu <no-reply@menu.example.com>` |
| `CDN_PURGE_PROVIDER`, `CDN_PURGE_TOKEN`, `CDN_ZONE_ID` | worker | `cloudflare` / `none` |
| `TLS_ASK_ALLOWED_CIDR` | api | `172.16.0.0/12` |
| `LOG_LEVEL` | all | `info` |
| `OTEL_EXPORTER_OTLP_ENDPOINT`, `SENTRY_DSN` | all | optional |
| `UPLOAD_MAX_BYTES` | api | `10485760` |

### 17.3 CI/CD (GitHub Actions)
`npm ci` → lint (eslint, prettier, `tsc -b`) → unit → DB/integration (service containers) → build images → E2E + a11y + Lighthouse against compose → push images (main) → deploy staging (migrate, then rolling update) → manual promotion to prod. Migrations must be backward-compatible with the previous app version (expand/contract).

### 17.4 Operations
- Backups: managed PITR (7 days) + nightly `pg_dump` to separate object storage (30 days). Restore drill quarterly.
- Object storage versioning on the private bucket.
- Observability: `/metrics` (HTTP latency histograms by route, DB pool, cache hit ratio, image processing time), dashboards, alerts (5xx > 1% 5 min, p95 public > 300 ms, login failures > 50/min, job failures).
- Scheduled jobs (pg-boss cron): expire sessions/imports (hourly), domain verification (15 min), media GC (daily), CDN purge debounce (event), audit retention (keep 2 years, platform configurable).

---

## 18. Seed data

`npm run db:seed` (idempotent, runs through the same service layer as the API, so audit + content_version are exercised):

1. Platform admin `admin@menu.local` / random password printed once (dev only).
2. Tenant:
```json
{
  "slug": "amaradining", "name": "Amara Dining",
  "locales": ["en", "km"], "default_locale": "en",
  "currency": "USD", "secondary_currency": "KHR", "secondary_rate": "4100", "secondary_round_minor": 100,
  "timezone": "Asia/Phnom_Penh",
  "theme": { "primary": "#7a1f2b", "secondary": "#c9a227", "background": "#fbf7f0", "font_heading": "Playfair Display",
             "font_body": "Inter", "item_layout": "list", "uppercase_category_titles": true, "banner_mode": "image" },
  "settings": { "show_item_codes": true, "hide_sold_out": false, "show_secondary_currency": false,
                "price_on_request_label": { "en": "Ask staff", "km": "សូមសួរបុគ្គលិក" },
                "filter_tags": ["vegetarian", "vegan", "gluten_free", "spicy_2"] }
}
```
3. Branch `main` (default). Tables `T1`–`T12` (area "Dining room"), `B1`–`B4` (area "Bar").
4. Menus: `food` (no schedule, always), `drinks` (always), `happy-hour` (inactive example: Mon–Sun 16:00–19:00, with cocktails placed in it via `category_items` to demonstrate multi-placement).
5. Modifier groups from `docs/seed/amara-modifier-groups.csv`: `steak-sauce` (from Amara), `caesar-protein` (from Amara), `tea-choice` (from Amara), `coffee-extras` (**illustrative**, not on Amara, shows paid multi-select modifiers).
6. Items from `docs/seed/amara-sample.csv`: **65 items / 86 variant rows across 28 category paths** (30 categories including the `Spirits` and `Wine` parents), sampled from every Amara section. The sample demonstrates:
   - **Variants with different prices:** `083` Fresh Spring Rolls (Pork 4.00 / Prawn 6.00 / Vegetable 3.50 `083v`), `045`, `055`, `050`, `094N`, `145`.
   - **Vegetarian via variant tag** (replaces Amara's duplicated `…v` items): `083v`, `045v`, `055v`, `050v`, `094nv`, `145v`, plus `021` Khmer Discovery Set "Vegetarian" variant at the same price.
   - **Whole-item tags** (replaces the "Gluten Free" section): `gluten_free` on `083`, `012N`, `037`, `055`, `050`, `810`, `034`, `038T`, `106N`, `765`, `009`.
   - **Modifiers:** steaks `886`, `883`, `810` → `steak-sauce`. `804` → `caesar-protein`. `M079` → `tea-choice`. Coffees → `coffee-extras`.
   - **Price on request:** soft drinks `206`, `200`, beers, spirits, all wines.
   - **Glass/Bottle variants:** Woolshed Chardonnay (`573` glass), Woolshed Sauvignon Blanc (`516G`), Pigmentum Malbec (`511`).
   - **Khmer names:** `055` "តុមយ៉ាំគ្រឿងសមុទ្រ" (verbatim from Amara), `094N` "អាម៉ុក", `100N` "ឡុកឡាក់សាច់គោ" (**need native review**, see §21).
   - **Synthetic codes** `X-…` for Amara items that have no code (beers, wines, White Russian).
   - **Nested categories:** `Spirits > Gin`, `Spirits > Cognac`, `Wine > White|Red|Sparkling|Rosé`.
   - Allergen tags are inferred from descriptions (peanut, egg, milk, gluten, fish, soy, sulphites) and are **for demo only**. A real restaurant must confirm them.
7. Category icons (FA Free): Set Menu `fa-utensils`, Starter `fa-bowl-food`, Soup `fa-mug-hot`, Main Course `fa-plate-wheat`, Steak Lover `fa-fire-burner`, Cold Cuts `fa-bacon`, Pasta `fa-bowl-rice`, Burgers & Sandwiches `fa-burger`, Side Dishes `fa-seedling`, Pizza `fa-pizza-slice`, Dessert `fa-ice-cream`, Coffee & Tea `fa-mug-saucer`, Soft Drinks `fa-bottle-water`, Beer `fa-beer-mug-empty`, Shakes/Smoothies `fa-blender`, Juices `fa-lemon`, Mocktails `fa-martini-glass-empty`, Cocktails / Signature `fa-martini-glass-citrus`, Aperitif `fa-wine-glass`, Spirits `fa-whiskey-glass`, Wine `fa-wine-bottle`.

---

## 19. Phase 2/3 design hooks

Already present in Phase 1 (no migration of existing data needed later):
- `tables` with a stable, rotatable `qr_token`. The `/q/{token}` route already resolves table context (cookie `menu_ctx`).
- Variants, modifiers and modifier groups with min/max rules, which is exactly the selection model an order line needs. The modal already validates selections client-side (`packages/shared/selection.ts` validates `{variant_id, modifier_ids[]}` against the item and is reused server-side in Phase 2).
- `items.is_orderable`, `prep_minutes`, `kitchen_station`. `tenants.features.ordering/waiter_call`.
- POS codes on items, variants and modifiers. `external_refs` JSONB on items, variants and modifiers.
- `branch_item_overrides.price_minor` (branch pricing).
- `content_version` + `pg_notify` infrastructure, reused for live order updates.

Phase 2 tables (planned; shown for review, **not** in `schema.sql`):
```sql
CREATE TYPE order_status AS ENUM ('submitted','accepted','preparing','ready','served','cancelled');
CREATE TABLE table_sessions (id uuid PK, tenant_id, branch_id, table_id, opened_at, closed_at,
  guest_token_hash bytea, status text);                         -- one open session per table
CREATE TABLE orders (id uuid PK, tenant_id, branch_id, table_id, table_session_id, number int,  -- daily sequence per branch
  status order_status, note text, subtotal_minor bigint, currency char(3), submitted_at, updated_at);
CREATE TABLE order_items (id uuid PK, tenant_id, order_id, item_id, variant_id,
  item_code text, variant_code text, name_snapshot jsonb, unit_price_minor bigint, quantity int, note text,
  station text, status order_status);                           -- snapshots: menu edits never change past orders
CREATE TABLE order_item_modifiers (tenant_id, order_item_id, modifier_id, modifier_code text,
  name_snapshot jsonb, price_delta_minor bigint);
CREATE TABLE waiter_calls (id uuid PK, tenant_id, branch_id, table_id, kind text CHECK (kind IN ('waiter','bill','water')),
  created_at, acknowledged_by uuid, acknowledged_at);
CREATE TABLE devices (id uuid PK, tenant_id, branch_id, name, role text /* kitchen|bar|floor */, token_hash bytea, last_seen_at);
```
Phase 2 (ordering only, decided 2026-11-11): the guest builds a selection and submits it. They pay at the counter. There is no in-app payment, no card capture, and no ESC/POS printer. A kitchen/staff screen is the only fulfillment UI. Orders are not held for approval; they appear on that screen as soon as they are submitted. The composed POS code (`ho4 / ketchup`) is snapshotted onto the order line.

Phase 2 API sketch: `POST /public/t/{slug}/sessions` (from QR), `POST /public/t/{slug}/orders`, `GET /public/t/{slug}/orders/{id}` (SSE status), `POST /public/t/{slug}/waiter-calls`. Staff screen: `GET /admin/tenants/{tid}/orders/stream` (SSE, filtered by station), `PATCH /orders/{id}` status. Guest abuse controls: session token bound to a QR scan, rate limits.

Phase 3: `pos_connections(tenant_id, provider, credentials_enc, mapping jsonb, last_sync_at)`, sync jobs keyed on `code` / `external_refs`, webhook endpoints. `analytics_events` (partitioned by month: `menu_view`, `item_open`, `search`, `filter`, `qr_scan`) collected via `navigator.sendBeacon` to a cookieless endpoint (no PII, daily-rotating salted visitor hash).

---

## 20. Acceptance criteria (Phase 1)

Each criterion is verified by an automated test unless marked (M) for manual.

**Tenancy & resolution**
- AC-T1: `GET /amaradining/` on the platform host renders the Amara seed menu. `/AmaraDining/` → 301 to `/amaradining/`. An unknown slug → 404 page with status 404.
- AC-T2: After verifying `menu.amara.test`, `GET https://menu.amara.test/` renders the same menu, and `PLATFORM_HOST/amaradining/` → 301 to the custom domain. An unverified domain gets no TLS certificate (tls-ask 404).
- AC-T3: The isolation suite (§16.3) passes for all tenant tables and all admin routes.
- AC-T4: A suspended tenant's public menu returns 410 with a neutral page. Its members get `403 tenant_suspended`.

**Visitor**
- AC-V1: All seed categories render in admin-defined order with sticky category nav. Scrolling updates the active pill (scrollspy) and clicking a pill scrolls to the section below the sticky header (no overlap).
- AC-V2: Fresh Spring Rolls shows one row "from $3.50" with code `083`. The modal lists Pork $4.00 / Prawn $6.00 / Vegetable $3.50. **There is no separate "Vegetarian" or "Gluten Free" section** and no duplicated item.
- AC-V3: The "Vegetarian" chip shows only items that are vegetarian or have a vegetarian variant (exactly 12 in the seed). `083` shows a "Vegetarian option" pill. Opening it preselects "Vegetable". Combining "Vegetarian"+"Gluten free" shows the intersection. The URL reflects the filters and reloading restores them.
- AC-V4: Searching `amok` finds `094N` and `009`, `083` finds Fresh Spring Rolls, and Khmer `តុមយ៉ាំ` finds `055`. Empty sections are hidden and the result count is announced (aria-live).
- AC-V5: `/amaradining/km/` shows Khmer names where present and English fallback elsewhere, `<html lang="km">`, Khmer font loaded, `hreflang` links present.
- AC-V6: Price-on-request items show "Ask staff" (km: localised label) and never a blank or `$0.00`. All prices are formatted identically (`$4.00`), whatever the seed's original format.
- AC-V7: Filet Steak modal requires exactly one sauce (default Black pepper preselected). Coffee extras allow 0–3 with the total updating (+$0.50 each).
- AC-V8: Marking `886` sold out in the admin shows the "Sold out" badge on the visitor within 5 s (without CDN), with the item greyed and the modal showing it unavailable. With `hide_sold_out=true` it disappears.
- AC-V9: A menu with schedule 16:00–19:00 is shown only within that window (tested with preview `at`). A 22:00–02:00 window works across midnight.
- AC-V10: JSON-LD validates against schema.org (Restaurant → Menu → MenuSection → MenuItem → Offer), and the Rich Results test shows no errors (M).
- AC-V11: Lighthouse budgets (§9.8) pass on the seeded tenant. axe: 0 serious/critical. The whole menu is navigable by keyboard only. With JS disabled, the full menu and language links work.
- AC-V12: Images are WebP with `srcset`, lazy below the fold, with no layout shift (CLS ≤ 0.05).
- AC-V13: `/amaradining/q/{token}` → 302 to the branch menu with table context. A rotated token → friendly 404.
- AC-V14: With `pwa_enabled`, the menu loads offline after one visit with an offline banner (M + Playwright offline mode).

**Admin**
- AC-A1: Login with wrong credentials returns a generic error. 6 rapid failures lock the account (backoff). With TOTP enabled, the session is unusable until the code is verified. Recovery codes work once.
- AC-A2: Owner invites a manager by email (Mailpit). The invitee sets a password and gets manager permissions. The manager can't access Domains or Members-manager actions (403, hidden in UI). Staff see only Availability, tables/QR and read-only views.
- AC-A3: Create an item with 3 variants, 2 tags, 1 modifier group, 2 categories. It appears in both categories on the visitor. Saving with two default variants or neither price nor on-request → inline 422 errors.
- AC-A4: Two browsers edit the same item. The second save receives 412 and the reload/overwrite dialog.
- AC-A5: Drag-drop reorder of items and categories persists and is reflected on the visitor. Nesting a third level is rejected.
- AC-A6: Bulk price +10% rounded to 0.25 on the Steak Lover category shows a preview diff and applies atomically, with one audit entry plus per-item entries. Revert restores the previous prices.
- AC-A7: Uploading a 6000×4000 JPEG with EXIF GPS produces 160/480/960/1600 WebP variants, with no EXIF in the outputs and the original kept privately. A PNG renamed `.jpg` that is actually a PDF → 415.
- AC-A8: Importing `docs/seed/amara-sample.csv` into an empty tenant (after the modifier groups CSV) gives a dry-run of 65 creates, 0 errors. Commit creates the menu, which is identical to the seeded one. Exporting then re-importing gives 0 changes. A row with `6,00$` → error with row/column. Committing after another admin edit → `409 import_stale`.
- AC-A9: The QR SVG for table T5 decodes to `…/amaradining/q/<token>`. The A4 sheet PDF contains 6 labelled codes per page. Rotating invalidates the old code.
- AC-A10: The audit log shows who changed what (before/after) for item, price, availability, member and import actions. Entries can't be updated or deleted through any API or the `menu_app` role.
- AC-A11: Live preview reflects unsaved theme colours. The contrast checker warns below 4.5:1.
- AC-A12: Branch-scoped staff can toggle availability only for their branch.

**API**
- AC-P1: OpenAPI 3.1 document is served and matches the implementation (contract test). All errors are problem+json with `code` and `request_id`.
- AC-P2: Public menu responds with an ETag. A repeat request with `If-None-Match` → 304. Any content write changes the ETag.
- AC-P3: Rate limits return 429 with `Retry-After`.
- AC-P4: p95 latency for the public menu < 30 ms (cache hit) and < 150 ms (miss) for 300 items on the reference hardware (2 vCPU).

---

## 21. Resolved decisions

Herbert answered every open question on 2026-11-11. The normative write-up is [DECISIONS.md](DECISIONS.md). Short form:

1. Admin is React + Bootstrap 5 + Font Awesome 6 Free.
2. Hosting is a FastPanel VPS with systemd and nginx. No production Docker. No CDN in v1. Local disk storage, S3 pluggable.
3. Host is `listado.eu`. Free tenants use `listado.eu/<slug>/` only. Pro unlocks `<slug>.listado.eu`.
4. Edits go live immediately. No draft/publish.
5. Self-service signup is in Phase 1. Free = 30 dishes + 30 drinks. Pro = EUR 10/year, subdomain, caps lifted. `PAYMENT_PROVIDER` is `stripe` (Checkout + webhook), `mollie`, `redsys`, or `none` (platform admin grants Pro). `TRANSLATION_PROVIDER` is `deepl`, `google`, or `none`.
6. Owners write in their language (default `es`). Other visitor languages are machine-translated through a cached provider. The Amara seed's Khmer strings stay as authored demo content.
7. Display currency is chosen by the visitor (ECB daily euro rates), not a fixed tenant KHR rate. Converted prices are indicative.
8. Prices include VAT. Footer copy only. No tax tables.
9. Item codes are POS codes. A selection is `base / modifier / modifier` (separator ` / `). Item and variant codes keep separate unique indexes.
10. EU 14 allergens, each `none | contains | may_contain`. Visitor allergies control only if the owner enables it.
11. Audit retention 2 years. Soft-deleted items purged after 30 days. Both are scheduled jobs.
12. Phase 2 is ordering only: pay at the counter, no printers, kitchen/staff screen, no approval gate.
13. Cookieless analytics, no PII, stored in `analytics_events`.
14. HEIC is converted in the browser with `heic2any`; otherwise the admin asks for JPEG/PNG.
15. One repository.

Schema additions (plans, `items.kind`, allergen presence, `translations`, `exchange_rates`, `subscriptions`, `analytics_events`) live in `docs/schema.sql` and `packages/db/migrations/0002_market.sql`. Appendix A below is the original base DDL.

---

## Appendix A: Base DDL

The base below was applied to PostgreSQL 18 with `menu_app` (NOBYPASSRLS) and `menu_system` (BYPASSRLS). The live DDL is [`docs/schema.sql`](docs/schema.sql): this appendix plus the market amendments in `packages/db/migrations/0002_market.sql` (plan, dish/drink kind, allergen presence, subscriptions, translation cache, ECB rates, cookieless analytics).

```sql
-- =====================================================================
-- menu platform — Phase 1 schema (PostgreSQL 18)
-- Source of truth for SPEC.md §6. Drizzle schema in packages/db mirrors this;
-- RLS, grants, triggers live in raw SQL migrations.
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- ---------------------------------------------------------------------
-- Roles (created by infra/bootstrap, shown for reference)
--   menu_owner  : owns all objects, runs migrations. Never used by the app at runtime.
--   menu_app    : LOGIN NOBYPASSRLS. All tenant-scoped request work. Must run inside
--                 a transaction with SET LOCAL app.tenant_id = '<uuid>'.
--   menu_system : LOGIN BYPASSRLS. Auth/sessions, tenant resolution, platform admin,
--                 background jobs. Import of this pool is lint-restricted to
--                 apps/api/src/{auth,platform,resolve,jobs}.
-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------
-- Helpers
-- ---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION app_tenant_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.tenant_id', true), '')::uuid
$$;

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  IF TG_OP = 'UPDATE' AND to_jsonb(NEW) ? 'version' THEN
    NEW.version := OLD.version + 1;
  END IF;
  RETURN NEW;
END $$;

-- Localized text: {"en": "...", "km": "..."}; keys must be BCP-47-ish codes, values strings.
CREATE OR REPLACE FUNCTION is_localized_text(j jsonb) RETURNS boolean
LANGUAGE sql IMMUTABLE AS $$
  SELECT j IS NULL OR (
    jsonb_typeof(j) = 'object'
    AND NOT EXISTS (
      SELECT 1 FROM jsonb_each(j) e
      WHERE e.key !~ '^[a-z]{2,3}(-[A-Za-z0-9]{2,8})?$'
         OR jsonb_typeof(e.value) <> 'string'
         OR length(e.value #>> '{}') > 4000
    )
  )
$$;

-- Flattened search text across all locales (admin search, trigram index).
CREATE OR REPLACE FUNCTION localized_search_text(j jsonb) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce(string_agg(lower(e.value #>> '{}'), ' '), '')
  FROM jsonb_each(coalesce(j, '{}'::jsonb)) e
$$;

-- ---------------------------------------------------------------------
-- Enums
-- ---------------------------------------------------------------------
CREATE TYPE tenant_status AS ENUM ('active', 'suspended', 'archived');
CREATE TYPE domain_status AS ENUM ('pending', 'verified', 'failed');
CREATE TYPE member_role   AS ENUM ('owner', 'manager', 'staff');
CREATE TYPE tag_kind      AS ENUM ('dietary', 'allergen', 'badge', 'spice');
CREATE TYPE media_status  AS ENUM ('pending', 'ready', 'failed');
CREATE TYPE import_status AS ENUM ('validated', 'committing', 'committed', 'failed', 'expired');
CREATE TYPE import_kind   AS ENUM ('items_csv', 'modifier_groups_csv');

-- ---------------------------------------------------------------------
-- Tenancy
-- ---------------------------------------------------------------------
CREATE TABLE tenants (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  slug                citext NOT NULL UNIQUE
                      CHECK (slug ~ '^[a-z0-9](?:[a-z0-9-]{1,38}[a-z0-9])$'),
  name                text NOT NULL CHECK (length(name) BETWEEN 1 AND 120),
  status              tenant_status NOT NULL DEFAULT 'active',
  default_locale      text NOT NULL DEFAULT 'en',
  locales             text[] NOT NULL DEFAULT '{en}',
  currency            char(3) NOT NULL DEFAULT 'USD' CHECK (currency ~ '^[A-Z]{3}$'),
  secondary_currency  char(3) CHECK (secondary_currency ~ '^[A-Z]{3}$'),
  secondary_rate      numeric(18,6) CHECK (secondary_rate > 0),   -- 1 primary = rate secondary
  secondary_round_minor integer CHECK (secondary_round_minor > 0), -- e.g. 100 for KHR
  timezone            text NOT NULL DEFAULT 'Asia/Phnom_Penh',
  theme               jsonb NOT NULL DEFAULT '{}'::jsonb,          -- see SPEC §6.3
  contact             jsonb NOT NULL DEFAULT '{}'::jsonb,          -- phone, address, map_url, socials
  settings            jsonb NOT NULL DEFAULT '{}'::jsonb,          -- see SPEC §6.3
  features            jsonb NOT NULL DEFAULT '{}'::jsonb,          -- {"ordering":false,"waiter_call":false}
  logo_media_id       uuid,
  banner_media_id     uuid,
  content_version     bigint NOT NULL DEFAULT 1,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  CHECK (default_locale = ANY (locales)),
  CHECK ((secondary_currency IS NULL) = (secondary_rate IS NULL))
);

CREATE TABLE domains (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  hostname            citext NOT NULL UNIQUE
                      CHECK (hostname ~ '^([a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z]{2,63}$'),
  status              domain_status NOT NULL DEFAULT 'pending',
  verification_token  text NOT NULL,      -- expected TXT at _menu-verify.<hostname>
  verified_at         timestamptz,
  last_checked_at     timestamptz,
  is_primary          boolean NOT NULL DEFAULT false,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id)
);
CREATE UNIQUE INDEX domains_one_primary ON domains (tenant_id) WHERE is_primary;

CREATE TABLE branches (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  slug                citext NOT NULL CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,39}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  address             jsonb CHECK (is_localized_text(address)),
  phone               text,
  timezone            text,               -- NULL = tenant timezone
  is_default          boolean NOT NULL DEFAULT false,
  sort_order          integer NOT NULL DEFAULT 0,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id)
);
CREATE UNIQUE INDEX branches_slug ON branches (tenant_id, slug) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX branches_one_default ON branches (tenant_id) WHERE is_default AND deleted_at IS NULL;

-- ---------------------------------------------------------------------
-- Identity (global, not tenant-scoped; menu_system only)
-- ---------------------------------------------------------------------
CREATE TABLE users (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  email               citext NOT NULL UNIQUE,
  password_hash       text,               -- argon2id PHC string; NULL until invite accepted
  display_name        text NOT NULL DEFAULT '',
  is_platform_admin   boolean NOT NULL DEFAULT false,
  totp_secret_enc     bytea,              -- AES-256-GCM(secret) with APP_ENCRYPTION_KEY
  totp_enabled_at     timestamptz,
  email_verified_at   timestamptz,
  failed_login_count  integer NOT NULL DEFAULT 0,
  locked_until        timestamptz,
  last_login_at       timestamptz,
  ui_locale           text NOT NULL DEFAULT 'en',
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz
);

CREATE TABLE user_recovery_codes (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id             uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash           bytea NOT NULL,     -- sha256
  used_at             timestamptz
);

CREATE TABLE sessions (
  id                  bytea PRIMARY KEY,  -- sha256(raw 32-byte token); raw token only in cookie
  user_id             uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  csrf_token          text NOT NULL,
  mfa_verified        boolean NOT NULL DEFAULT false,
  ip                  inet,
  user_agent          text,
  created_at          timestamptz NOT NULL DEFAULT now(),
  last_seen_at        timestamptz NOT NULL DEFAULT now(),
  expires_at          timestamptz NOT NULL      -- absolute expiry
);
CREATE INDEX sessions_user ON sessions (user_id);
CREATE INDEX sessions_expires ON sessions (expires_at);

CREATE TABLE password_reset_tokens (
  token_hash          bytea PRIMARY KEY,
  user_id             uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at          timestamptz NOT NULL,
  used_at             timestamptz
);

-- ---------------------------------------------------------------------
-- Membership & invitations (tenant-scoped)
-- ---------------------------------------------------------------------
CREATE TABLE memberships (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  user_id             uuid NOT NULL REFERENCES users(id),
  role                member_role NOT NULL,
  branch_ids          uuid[],             -- NULL = all branches; staff may be limited
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz
);
CREATE UNIQUE INDEX memberships_unique ON memberships (tenant_id, user_id) WHERE deleted_at IS NULL;
CREATE INDEX memberships_user ON memberships (user_id) WHERE deleted_at IS NULL;

CREATE TABLE invitations (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  email               citext NOT NULL,
  role                member_role NOT NULL,
  token_hash          bytea NOT NULL UNIQUE,
  invited_by          uuid NOT NULL REFERENCES users(id),
  expires_at          timestamptz NOT NULL,
  accepted_at         timestamptz,
  revoked_at          timestamptz,
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX invitations_open ON invitations (tenant_id, email)
  WHERE accepted_at IS NULL AND revoked_at IS NULL;

-- ---------------------------------------------------------------------
-- Media
-- ---------------------------------------------------------------------
CREATE TABLE media (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  status              media_status NOT NULL DEFAULT 'pending',
  original_key        text NOT NULL,      -- private bucket/prefix
  original_mime       text NOT NULL,
  original_bytes      integer NOT NULL,
  width               integer,
  height              integer,
  variants            jsonb NOT NULL DEFAULT '[]'::jsonb,  -- [{"w":480,"h":360,"key":"...","bytes":21000,"format":"webp"}]
  dominant_color      text CHECK (dominant_color ~ '^#[0-9a-f]{6}$'),
  focal_x             real NOT NULL DEFAULT 0.5 CHECK (focal_x BETWEEN 0 AND 1),
  focal_y             real NOT NULL DEFAULT 0.5 CHECK (focal_y BETWEEN 0 AND 1),
  alt                 jsonb CHECK (is_localized_text(alt)),
  created_by          uuid REFERENCES users(id),
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id)
);
CREATE INDEX media_tenant_created ON media (tenant_id, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE tenants
  ADD CONSTRAINT tenants_logo_fk   FOREIGN KEY (id, logo_media_id)   REFERENCES media (tenant_id, id),
  ADD CONSTRAINT tenants_banner_fk FOREIGN KEY (id, banner_media_id) REFERENCES media (tenant_id, id);

-- ---------------------------------------------------------------------
-- Menus & schedules
-- ---------------------------------------------------------------------
CREATE TABLE menus (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  slug                citext NOT NULL CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,39}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  description         jsonb CHECK (is_localized_text(description)),
  is_active           boolean NOT NULL DEFAULT true,
  sort_order          integer NOT NULL DEFAULT 0,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id)
);
CREATE UNIQUE INDEX menus_slug ON menus (tenant_id, slug) WHERE deleted_at IS NULL;

-- A menu is shown at a branch only if linked. No links at all => shown at every branch.
CREATE TABLE branch_menus (
  tenant_id           uuid NOT NULL,
  branch_id           uuid NOT NULL,
  menu_id             uuid NOT NULL,
  PRIMARY KEY (branch_id, menu_id),
  FOREIGN KEY (tenant_id, branch_id) REFERENCES branches (tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, menu_id)   REFERENCES menus (tenant_id, id)    ON DELETE CASCADE
);

-- Availability windows in branch (or tenant) local time. No rows => always available.
-- days: bitmask Mon=1, Tue=2, Wed=4, Thu=8, Fri=16, Sat=32, Sun=64.
-- end_time <= start_time means the window crosses midnight (e.g. 22:00–02:00).
CREATE TABLE menu_schedules (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  menu_id             uuid NOT NULL,
  days                smallint NOT NULL CHECK (days BETWEEN 1 AND 127),
  start_time          time NOT NULL,
  end_time            time NOT NULL,
  valid_from          date,
  valid_to            date,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (tenant_id, menu_id) REFERENCES menus (tenant_id, id) ON DELETE CASCADE,
  CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from)
);
CREATE INDEX menu_schedules_menu ON menu_schedules (menu_id);

-- ---------------------------------------------------------------------
-- Categories (max depth 2: section > subsection, e.g. "Spirits > Gin")
-- ---------------------------------------------------------------------
CREATE TABLE categories (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  menu_id             uuid NOT NULL,
  parent_id           uuid,
  slug                citext NOT NULL CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,59}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  description         jsonb CHECK (is_localized_text(description)),
  icon                text CHECK (icon ~ '^fa-[a-z0-9-]{1,40}$'),   -- Font Awesome icon name
  banner_media_id     uuid,
  is_visible          boolean NOT NULL DEFAULT true,
  sort_order          integer NOT NULL DEFAULT 0,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, menu_id)         REFERENCES menus (tenant_id, id),
  FOREIGN KEY (tenant_id, parent_id)       REFERENCES categories (tenant_id, id),
  FOREIGN KEY (tenant_id, banner_media_id) REFERENCES media (tenant_id, id)
);
CREATE UNIQUE INDEX categories_slug ON categories (menu_id, slug) WHERE deleted_at IS NULL;
CREATE INDEX categories_menu_sort ON categories (menu_id, parent_id, sort_order) WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION categories_check_depth() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE p record;
BEGIN
  IF NEW.parent_id IS NULL THEN RETURN NEW; END IF;
  SELECT parent_id, menu_id INTO p FROM categories WHERE id = NEW.parent_id;
  IF p.parent_id IS NOT NULL THEN
    RAISE EXCEPTION 'category depth > 2' USING ERRCODE = 'check_violation';
  END IF;
  IF p.menu_id <> NEW.menu_id THEN
    RAISE EXCEPTION 'parent category in different menu' USING ERRCODE = 'check_violation';
  END IF;
  IF EXISTS (SELECT 1 FROM categories WHERE parent_id = NEW.id AND deleted_at IS NULL) THEN
    RAISE EXCEPTION 'category with children cannot become a child' USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER categories_depth BEFORE INSERT OR UPDATE OF parent_id, menu_id ON categories
  FOR EACH ROW EXECUTE FUNCTION categories_check_depth();

-- ---------------------------------------------------------------------
-- Items, placements, variants
-- ---------------------------------------------------------------------
CREATE TABLE items (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  code                text CHECK (code ~ '^[A-Za-z0-9][A-Za-z0-9._-]{0,31}$'),  -- POS/menu code e.g. 094N
  slug                citext NOT NULL CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,79}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name) AND name <> '{}'::jsonb),
  description         jsonb CHECK (is_localized_text(description)),
  media_id            uuid,
  is_visible          boolean NOT NULL DEFAULT true,   -- false: hidden from visitors
  is_available        boolean NOT NULL DEFAULT true,   -- false: "Sold out" badge
  -- Phase 2 hooks (unused by Phase 1 UI, present so ordering needs no data migration)
  is_orderable        boolean NOT NULL DEFAULT true,
  prep_minutes        smallint CHECK (prep_minutes BETWEEN 0 AND 240),
  kitchen_station     text CHECK (length(kitchen_station) <= 40),
  -- Phase 3 hook
  external_refs       jsonb NOT NULL DEFAULT '{}'::jsonb,  -- {"pos:<provider>": "<id>"}
  search_text         text GENERATED ALWAYS AS
                      (lower(coalesce(code,'')) || ' ' || localized_search_text(name) || ' ' || localized_search_text(description)) STORED,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, media_id) REFERENCES media (tenant_id, id)
);
CREATE UNIQUE INDEX items_code ON items (tenant_id, lower(code)) WHERE deleted_at IS NULL AND code IS NOT NULL;
CREATE UNIQUE INDEX items_slug ON items (tenant_id, slug) WHERE deleted_at IS NULL;
CREATE INDEX items_search_trgm ON items USING gin (search_text gin_trgm_ops);
CREATE INDEX items_tenant_updated ON items (tenant_id, updated_at DESC);

-- An item may be placed in several categories (fixes copy-paste duplication).
CREATE TABLE category_items (
  tenant_id           uuid NOT NULL,
  category_id         uuid NOT NULL,
  item_id             uuid NOT NULL,
  sort_order          integer NOT NULL DEFAULT 0,
  PRIMARY KEY (category_id, item_id),
  FOREIGN KEY (tenant_id, category_id) REFERENCES categories (tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, item_id)     REFERENCES items (tenant_id, id)      ON DELETE CASCADE
);
CREATE INDEX category_items_item ON category_items (item_id);
CREATE INDEX category_items_sort ON category_items (category_id, sort_order);

-- Every item has >= 1 variant; a single-price item has one default variant with name NULL.
CREATE TABLE item_variants (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  item_id             uuid NOT NULL,
  code                text CHECK (code ~ '^[A-Za-z0-9][A-Za-z0-9._-]{0,31}$'),   -- e.g. 083v
  name                jsonb CHECK (is_localized_text(name)),
  price_minor         bigint CHECK (price_minor >= 0 AND price_minor < 100000000000),
  price_on_request    boolean NOT NULL DEFAULT false,
  is_default          boolean NOT NULL DEFAULT false,
  is_available        boolean NOT NULL DEFAULT true,
  sort_order          integer NOT NULL DEFAULT 0,
  external_refs       jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, item_id) REFERENCES items (tenant_id, id),
  CHECK (price_on_request = (price_minor IS NULL))
);
CREATE UNIQUE INDEX item_variants_one_default ON item_variants (item_id) WHERE is_default AND deleted_at IS NULL;
CREATE UNIQUE INDEX item_variants_code ON item_variants (tenant_id, lower(code)) WHERE deleted_at IS NULL AND code IS NOT NULL;
CREATE INDEX item_variants_item ON item_variants (item_id, sort_order) WHERE deleted_at IS NULL;

-- Enforce ">= 1 live variant and exactly one default" at commit time.
CREATE OR REPLACE FUNCTION items_check_variants() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE iid uuid; n_live int; n_default int;
BEGIN
  IF TG_TABLE_NAME = 'items' THEN iid := NEW.id;
  ELSIF TG_OP = 'DELETE' THEN iid := OLD.item_id;
  ELSE iid := NEW.item_id;
  END IF;
  IF EXISTS (SELECT 1 FROM items WHERE id = iid AND deleted_at IS NOT NULL) THEN RETURN NULL; END IF;
  SELECT count(*), count(*) FILTER (WHERE is_default) INTO n_live, n_default
    FROM item_variants WHERE item_id = iid AND deleted_at IS NULL;
  IF n_live < 1 OR n_default <> 1 THEN
    RAISE EXCEPTION 'item % must have >=1 variant and exactly one default (has %/%)', iid, n_live, n_default
      USING ERRCODE = 'check_violation';
  END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER items_variants_ck AFTER INSERT OR UPDATE ON items
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION items_check_variants();
CREATE CONSTRAINT TRIGGER item_variants_ck AFTER INSERT OR UPDATE OR DELETE ON item_variants
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION items_check_variants();

-- Per-branch overrides (Phase 1: availability; price override reserved for Phase 2/3).
CREATE TABLE branch_item_overrides (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  branch_id           uuid NOT NULL,
  item_id             uuid NOT NULL,
  variant_id          uuid,
  is_available        boolean,
  price_minor         bigint CHECK (price_minor >= 0),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE NULLS NOT DISTINCT (branch_id, item_id, variant_id),
  FOREIGN KEY (tenant_id, branch_id)  REFERENCES branches (tenant_id, id)      ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, item_id)    REFERENCES items (tenant_id, id)         ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, variant_id) REFERENCES item_variants (tenant_id, id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------
-- Modifiers (reusable groups, e.g. "Steak sauce" shared by 10 steaks)
-- ---------------------------------------------------------------------
CREATE TABLE modifier_groups (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  code                citext NOT NULL CHECK (code ~ '^[a-z0-9][a-z0-9-]{0,39}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  min_select          smallint NOT NULL DEFAULT 0 CHECK (min_select >= 0),
  max_select          smallint NOT NULL DEFAULT 1 CHECK (max_select >= 1),
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  CHECK (max_select >= min_select)
);
CREATE UNIQUE INDEX modifier_groups_code ON modifier_groups (tenant_id, code) WHERE deleted_at IS NULL;

CREATE TABLE modifiers (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  group_id            uuid NOT NULL,
  code                text CHECK (code ~ '^[A-Za-z0-9][A-Za-z0-9._-]{0,31}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  price_delta_minor   bigint NOT NULL DEFAULT 0 CHECK (price_delta_minor >= 0),
  is_default          boolean NOT NULL DEFAULT false,
  is_available        boolean NOT NULL DEFAULT true,
  sort_order          integer NOT NULL DEFAULT 0,
  external_refs       jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, group_id) REFERENCES modifier_groups (tenant_id, id)
);
CREATE INDEX modifiers_group ON modifiers (group_id, sort_order) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX modifiers_code ON modifiers (group_id, lower(code)) WHERE deleted_at IS NULL AND code IS NOT NULL;

CREATE TABLE item_modifier_groups (
  tenant_id           uuid NOT NULL,
  item_id             uuid NOT NULL,
  group_id            uuid NOT NULL,
  sort_order          integer NOT NULL DEFAULT 0,
  min_select_override smallint CHECK (min_select_override >= 0),
  max_select_override smallint CHECK (max_select_override >= 1),
  PRIMARY KEY (item_id, group_id),
  FOREIGN KEY (tenant_id, item_id)  REFERENCES items (tenant_id, id)           ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, group_id) REFERENCES modifier_groups (tenant_id, id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------
-- Tags: dietary, allergens, badges, spice. tenant_id NULL = platform system tag.
-- ---------------------------------------------------------------------
CREATE TABLE tags (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid REFERENCES tenants(id),
  kind                tag_kind NOT NULL,
  code                citext NOT NULL CHECK (code ~ '^[a-z0-9][a-z0-9_]{0,39}$'),
  name                jsonb NOT NULL CHECK (is_localized_text(name)),
  icon                text CHECK (icon ~ '^fa-[a-z0-9-]{1,40}$'),
  color               text CHECK (color ~ '^#[0-9a-fA-F]{6}$'),
  is_filterable       boolean NOT NULL DEFAULT false,   -- shows as a visitor filter chip
  schema_org_diet     text,                             -- e.g. https://schema.org/VegetarianDiet
  sort_order          integer NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz
);
CREATE UNIQUE INDEX tags_code ON tags (tenant_id, code) NULLS NOT DISTINCT WHERE deleted_at IS NULL;

CREATE TABLE item_tags (
  tenant_id           uuid NOT NULL,
  item_id             uuid NOT NULL,
  tag_id              uuid NOT NULL REFERENCES tags(id),
  PRIMARY KEY (item_id, tag_id),
  FOREIGN KEY (tenant_id, item_id) REFERENCES items (tenant_id, id) ON DELETE CASCADE
);
CREATE INDEX item_tags_tag ON item_tags (tag_id);

-- Variant-level tags: "Fresh Spring Rolls" (083) is vegetarian only in its Vegetable (083v) variant.
CREATE TABLE variant_tags (
  tenant_id           uuid NOT NULL,
  variant_id          uuid NOT NULL,
  tag_id              uuid NOT NULL REFERENCES tags(id),
  PRIMARY KEY (variant_id, tag_id),
  FOREIGN KEY (tenant_id, variant_id) REFERENCES item_variants (tenant_id, id) ON DELETE CASCADE
);

-- tags.tenant_id may be NULL so it is not covered by composite FKs; enforce tag visibility.
CREATE OR REPLACE FUNCTION tag_link_check() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM tags t WHERE t.id = NEW.tag_id
                 AND (t.tenant_id IS NULL OR t.tenant_id = NEW.tenant_id)) THEN
    RAISE EXCEPTION 'tag % not visible to tenant', NEW.tag_id USING ERRCODE = 'foreign_key_violation';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER item_tags_ck    BEFORE INSERT OR UPDATE ON item_tags    FOR EACH ROW EXECUTE FUNCTION tag_link_check();
CREATE TRIGGER variant_tags_ck BEFORE INSERT OR UPDATE ON variant_tags FOR EACH ROW EXECUTE FUNCTION tag_link_check();

-- ---------------------------------------------------------------------
-- Tables + QR (Phase 1: deep link to branch menu; Phase 2: ordering session)
-- ---------------------------------------------------------------------
CREATE TABLE tables (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL,
  branch_id           uuid NOT NULL,
  label               text NOT NULL CHECK (length(label) BETWEEN 1 AND 20),   -- "T12", "Bar 3"
  area                text CHECK (length(area) <= 40),                         -- "Terrace"
  seats               smallint CHECK (seats BETWEEN 1 AND 100),
  qr_token            text NOT NULL UNIQUE CHECK (qr_token ~ '^[A-Za-z0-9]{22}$'),  -- 128-bit base62
  qr_rotated_at       timestamptz,
  is_active           boolean NOT NULL DEFAULT true,
  sort_order          integer NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  deleted_at          timestamptz,
  UNIQUE (tenant_id, id),
  FOREIGN KEY (tenant_id, branch_id) REFERENCES branches (tenant_id, id)
);
CREATE UNIQUE INDEX tables_label ON tables (branch_id, lower(label)) WHERE deleted_at IS NULL;

-- ---------------------------------------------------------------------
-- Imports (CSV dry-run -> commit)
-- ---------------------------------------------------------------------
CREATE TABLE imports (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id           uuid NOT NULL REFERENCES tenants(id),
  kind                import_kind NOT NULL,
  mode                text NOT NULL CHECK (mode IN ('upsert', 'replace')),
  apply_order         boolean NOT NULL DEFAULT false,
  status              import_status NOT NULL DEFAULT 'validated',
  file_key            text NOT NULL,
  file_sha256         text NOT NULL,
  base_content_version bigint NOT NULL,   -- commit refused if content changed since dry-run
  summary             jsonb NOT NULL DEFAULT '{}'::jsonb,
  errors              jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_by          uuid NOT NULL REFERENCES users(id),
  created_at          timestamptz NOT NULL DEFAULT now(),
  committed_at        timestamptz,
  expires_at          timestamptz NOT NULL DEFAULT now() + interval '1 hour'
);

-- ---------------------------------------------------------------------
-- Audit log (append-only)
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id           uuid REFERENCES tenants(id),    -- NULL = platform-level event
  actor_user_id       uuid REFERENCES users(id),
  actor_ip            inet,
  action              text NOT NULL,                  -- e.g. item.update, member.invite, import.commit
  entity_type         text NOT NULL,
  entity_id           uuid,
  before              jsonb,
  after               jsonb,
  request_id          text,
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_tenant_time ON audit_log (tenant_id, created_at DESC);
CREATE INDEX audit_entity ON audit_log (tenant_id, entity_type, entity_id, created_at DESC);

-- ---------------------------------------------------------------------
-- updated_at / version triggers
-- ---------------------------------------------------------------------
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['tenants','domains','branches','users','memberships','media','menus',
    'menu_schedules','categories','items','item_variants','modifier_groups','modifiers','tags','tables']
  LOOP
    EXECUTE format('CREATE TRIGGER %I_updated BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION set_updated_at()', t, t);
  END LOOP;
END $$;

-- ---------------------------------------------------------------------
-- Content version: any write to public-facing content bumps tenants.content_version
-- (drives ETags) and NOTIFYs caches. Requires app.tenant_id to be set (all content
-- writes go through withTenant(), including jobs).
-- ---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION bump_content_version() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE tid uuid := app_tenant_id();
BEGIN
  IF tid IS NULL THEN
    RAISE EXCEPTION 'content write without app.tenant_id on %', TG_TABLE_NAME;
  END IF;
  UPDATE tenants SET content_version = content_version + 1 WHERE id = tid;
  PERFORM pg_notify('content_changed', tid::text);
  RETURN NULL;
END $$;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['branches','media','menus','branch_menus','menu_schedules','categories',
    'items','category_items','item_variants','branch_item_overrides','modifier_groups','modifiers',
    'item_modifier_groups','item_tags','variant_tags','tables','domains']
  LOOP
    EXECUTE format('CREATE TRIGGER %I_content_version AFTER INSERT OR UPDATE OR DELETE ON %I
                    FOR EACH STATEMENT EXECUTE FUNCTION bump_content_version()', t, t);
  END LOOP;
END $$;
-- tags: system tags (tenant_id NULL) are written by menu_system without app.tenant_id;
-- tenant tag writes bump via the service layer calling bump explicitly. Branding changes
-- on tenants bump in this BEFORE trigger (avoids recursion):
CREATE OR REPLACE FUNCTION tenants_bump_on_branding() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.content_version = OLD.content_version AND
     (NEW.name, NEW.theme, NEW.contact, NEW.settings, NEW.locales, NEW.default_locale, NEW.currency,
      NEW.secondary_currency, NEW.secondary_rate, NEW.logo_media_id, NEW.banner_media_id, NEW.status, NEW.slug)
     IS DISTINCT FROM
     (OLD.name, OLD.theme, OLD.contact, OLD.settings, OLD.locales, OLD.default_locale, OLD.currency,
      OLD.secondary_currency, OLD.secondary_rate, OLD.logo_media_id, OLD.banner_media_id, OLD.status, OLD.slug)
  THEN
    NEW.content_version := OLD.content_version + 1;
    PERFORM pg_notify('content_changed', NEW.id::text);
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER tenants_branding_bump BEFORE UPDATE ON tenants FOR EACH ROW EXECUTE FUNCTION tenants_bump_on_branding();

-- ---------------------------------------------------------------------
-- Row-level security
-- ---------------------------------------------------------------------
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['domains','branches','memberships','invitations','media','menus',
    'branch_menus','menu_schedules','categories','items','category_items','item_variants',
    'branch_item_overrides','modifier_groups','modifiers','item_modifier_groups','item_tags',
    'variant_tags','tables','imports']
  LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format('CREATE POLICY tenant_isolation ON %I
                    USING (tenant_id = app_tenant_id())
                    WITH CHECK (tenant_id = app_tenant_id())', t);
  END LOOP;
END $$;

ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_self ON tenants USING (id = app_tenant_id()) WITH CHECK (id = app_tenant_id());

ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE tags FORCE ROW LEVEL SECURITY;
CREATE POLICY tags_read  ON tags FOR SELECT USING (tenant_id IS NULL OR tenant_id = app_tenant_id());
CREATE POLICY tags_write ON tags FOR INSERT WITH CHECK (tenant_id = app_tenant_id());
CREATE POLICY tags_update ON tags FOR UPDATE USING (tenant_id = app_tenant_id()) WITH CHECK (tenant_id = app_tenant_id());

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;
CREATE POLICY audit_read   ON audit_log FOR SELECT USING (tenant_id = app_tenant_id());
CREATE POLICY audit_insert ON audit_log FOR INSERT WITH CHECK (tenant_id = app_tenant_id());

-- ---------------------------------------------------------------------
-- Grants
-- ---------------------------------------------------------------------
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

GRANT SELECT ON tenants TO menu_app;
-- slug, status, features are platform-controlled (menu_system only)
GRANT UPDATE (name, default_locale, locales, currency, secondary_currency, secondary_rate,
  secondary_round_minor, timezone, theme, contact, settings, logo_media_id, banner_media_id)
  ON tenants TO menu_app;
GRANT SELECT, INSERT, UPDATE ON
  domains, branches, memberships, invitations, media, menus, menu_schedules, categories, items,
  item_variants, branch_item_overrides, modifier_groups, modifiers, tags, tables, imports
  TO menu_app;
-- hard delete only on join/child tables; entities are soft-deleted
GRANT DELETE ON branch_menus, menu_schedules, category_items, item_modifier_groups, item_tags,
  variant_tags, branch_item_overrides, domains TO menu_app;
GRANT SELECT, INSERT ON branch_menus, category_items, item_modifier_groups, item_tags, variant_tags TO menu_app;
GRANT SELECT, INSERT ON audit_log TO menu_app;          -- no UPDATE/DELETE: append-only
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO menu_app;
-- users/sessions/password_reset_tokens/user_recovery_codes: menu_system only

-- Member lists / audit actor names without exposing the global users table to menu_app.
CREATE OR REPLACE FUNCTION tenant_users()
RETURNS TABLE (id uuid, email citext, display_name text, totp_enabled boolean, last_login_at timestamptz)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT u.id, u.email, u.display_name, u.totp_enabled_at IS NOT NULL, u.last_login_at
  FROM users u
  WHERE u.id IN (SELECT m.user_id FROM memberships m WHERE m.tenant_id = app_tenant_id()
                 UNION SELECT a.actor_user_id FROM audit_log a WHERE a.tenant_id = app_tenant_id())
$$;
REVOKE ALL ON FUNCTION tenant_users() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION tenant_users() TO menu_app;

GRANT ALL ON ALL TABLES IN SCHEMA public TO menu_system;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO menu_system;
REVOKE UPDATE, DELETE ON audit_log FROM menu_system;

-- ---------------------------------------------------------------------
-- System tags (seed; tenant_id NULL)
-- ---------------------------------------------------------------------
INSERT INTO tags (tenant_id, kind, code, name, icon, color, is_filterable, schema_org_diet, sort_order) VALUES
 (NULL,'dietary','vegetarian', '{"en":"Vegetarian","km":"បួស"}',          'fa-leaf',          '#2e7d32', true,  'https://schema.org/VegetarianDiet', 10),
 (NULL,'dietary','vegan',      '{"en":"Vegan"}',                          'fa-seedling',      '#1b5e20', true,  'https://schema.org/VeganDiet',      20),
 (NULL,'dietary','gluten_free','{"en":"Gluten free"}',                    'fa-wheat-awn-circle-exclamation','#8d6e63', true, 'https://schema.org/GlutenFreeDiet', 30),
 (NULL,'dietary','dairy_free', '{"en":"Dairy free"}',                     'fa-cow',           '#5d4037', false, null, 40),
 (NULL,'dietary','halal',      '{"en":"Halal"}',                          'fa-star-and-crescent','#00695c', true, 'https://schema.org/HalalDiet', 50),
 (NULL,'dietary','contains_pork','{"en":"Contains pork"}',                'fa-bacon',         '#ad1457', false, null, 60),
 (NULL,'dietary','contains_alcohol','{"en":"Contains alcohol"}',          'fa-wine-glass',    '#6a1b9a', false, null, 70),
 (NULL,'spice','spicy_1',      '{"en":"Mild"}',                           'fa-pepper-hot',    '#ef6c00', false, null, 10),
 (NULL,'spice','spicy_2',      '{"en":"Spicy"}',                          'fa-pepper-hot',    '#e65100', true,  null, 20),
 (NULL,'spice','spicy_3',      '{"en":"Very spicy"}',                     'fa-pepper-hot',    '#bf360c', false, null, 30),
 (NULL,'badge','signature',    '{"en":"Signature"}',                      'fa-award',         '#c9a227', false, null, 10),
 (NULL,'badge','chef_pick',    '{"en":"Chef''s pick"}',                   'fa-utensils',      '#c9a227', false, null, 20),
 (NULL,'badge','new',          '{"en":"New"}',                            'fa-certificate',   '#1565c0', false, null, 30),
 (NULL,'badge','popular',      '{"en":"Popular"}',                        'fa-fire',          '#d84315', false, null, 40),
 -- EU/UK 14 allergens ("contains")
 (NULL,'allergen','gluten',      '{"en":"Gluten"}',      'fa-wheat-awn', null,false,null,1),
 (NULL,'allergen','crustaceans', '{"en":"Crustaceans"}', 'fa-shrimp',    null,false,null,2),
 (NULL,'allergen','eggs',        '{"en":"Eggs"}',        'fa-egg',       null,false,null,3),
 (NULL,'allergen','fish',        '{"en":"Fish"}',        'fa-fish',      null,false,null,4),
 (NULL,'allergen','peanuts',     '{"en":"Peanuts"}',     null,           null,false,null,5),
 (NULL,'allergen','soybeans',    '{"en":"Soy"}',         null,           null,false,null,6),
 (NULL,'allergen','milk',        '{"en":"Milk"}',        'fa-cheese',    null,false,null,7),
 (NULL,'allergen','nuts',        '{"en":"Tree nuts"}',   null,           null,false,null,8),
 (NULL,'allergen','celery',      '{"en":"Celery"}',      null,           null,false,null,9),
 (NULL,'allergen','mustard',     '{"en":"Mustard"}',     null,           null,false,null,10),
 (NULL,'allergen','sesame',      '{"en":"Sesame"}',      null,           null,false,null,11),
 (NULL,'allergen','sulphites',   '{"en":"Sulphites"}',   null,           null,false,null,12),
 (NULL,'allergen','lupin',       '{"en":"Lupin"}',       null,           null,false,null,13),
 (NULL,'allergen','molluscs',    '{"en":"Molluscs"}',    null,           null,false,null,14);
```