mc_cars_gmbh_infraestructure/ARQUITECTURE.md

# ARQUITECTURE.md – MC Cars stack

A thorough walkthrough of how the MC Cars system is wired. Complements [AGENT.md](AGENT.md) (operational notes) and [README.md](README.md) (how to run).

---

## 1. High-level diagram

```
                         Browser (<host>)
                   │                │
       :55580 (nginx)        :55521 (Kong)          :55530 (Studio)
             │                      │                         │
      ┌──────┴──────┐                │                         │
      │   web       │                │                         │
      │  (static)   │                │                         │
      └─────────────┘                │                         │
                                     │                         │
           ┌─────────────────────────┼─────────────────────────┤
           │            │            │             │           │
        /auth/v1    /rest/v1    /realtime/v1    /storage/v1   /pg/
           │            │            │             │           │
      ┌────┴───┐  ┌─────┴────┐  ┌────┴────┐  ┌─────┴────┐ ┌────┴───┐
      │ gotrue │  │ postgrest│  │ realtime│  │ storage  │ │  meta  │
      └────┬───┘  └─────┬────┘  └────┬────┘  └─────┬────┘ └────┬───┘
           │            │            │             │           │
           └──────────┬─┴────────────┴─────────────┴───────────┘
                      │
                 ┌────┴─────┐       ┌───────────┐
                 │ postgres │       │ imgproxy  │
                 │ (wal:    │       │  (resize) │
                 │ logical) │       └───────────┘
                 └──────────┘              ▲
                      │                    │
          /mnt/.../data/db      /mnt/.../data/storage
```

Dashed lifetime services (exit 0 once done): `post-init`.

Network: single user-defined bridge `mccars`. No host networking, no ingress controller — everything is container-to-container DNS (`db`, `auth`, `kong`, …).

---

## 2. Request flows

### 2.1 Public visitor reads cars

```
browser → :55580/index.html            (nginx, static)
browser → :55521/rest/v1/vehicles?select=*&is_active=eq.true
        → Kong → rest (PostgREST) → postgres
        → RLS: "vehicles_select_active" (for anon, is_active=true only)
        ← JSON
```

No auth header except the anon `apikey`. Kong strips the key and forwards with the service's internal Auth context.

### 2.2 Public visitor submits booking

```
browser → supabase.rpc('create_lead', {p_name, p_email, p_phone, p_vehicle_id,
             p_vehicle_label, p_date_from, p_date_to, p_message, p_source,
             p_ip_address, p_ip_country})
        → :55521/rest/v1/rpc/create_lead  (POST, apikey=anon)
        → PostgREST → postgres.public.create_lead(...)
        → internally calls calculate_price() → computes pricing
        → INSERT INTO leads (with pricing columns)
        ← lead uuid
```

No price parameters are accepted from the client — all pricing is computed server-side. This prevents price-tampering. The frontend never reads leads back. If the RPC fails (validation, RLS), the UI shows a localized failure string.

### 2.3 Admin login

```
browser(admin.html) → supabase.auth.signInWithPassword
        → :55521/auth/v1/token?grant_type=password
        → Kong → gotrue → postgres(auth.users)
        ← access_token (JWT, aud=authenticated), refresh_token
        ← user.raw_user_meta_data.must_change_password (true on bootstrap)

if must_change_password:
    user blocked in "Passwort setzen" screen
    supabase.auth.updateUser({password, data:{must_change_password:false}})
        → :55521/auth/v1/user  (PUT with Bearer access_token)
        → gotrue updates auth.users
```

### 2.4 Admin qualifies a lead

```
admin.js → supabase.rpc('qualify_lead', {p_lead_id, p_notes})
        → PostgREST → postgres.public.qualify_lead(uuid,text)
        (SECURITY INVOKER, role=authenticated, auth.uid()=admin)
        BEGIN
          SELECT ... FOR UPDATE on leads
          UPDATE leads SET status='qualified', is_active=false, ...
          INSERT INTO customers (lead_id, name, email, phone, ...)
        COMMIT
        ← customers row

Realtime publication fires:
  postgres_changes(table=leads,     event=UPDATE, ...) → admin.js channel
  postgres_changes(table=customers, event=INSERT, ...) → admin.js channel
        → admin UI reloads tabs, badges update
```

### 2.5 Photo upload

```
admin.js → supabase.storage.from('vehicle-photos').upload(path, file)
        → :55521/storage/v1/object/vehicle-photos/<path>
        → Kong → storage-api → /mnt/.../data/storage
        ← public URL served via imgproxy for on-the-fly transforms
admin.js → UPDATE vehicles SET photo_url = <public url>
```

Bucket is public-read, write-authenticated, MIME-restricted to `image/*`, 50 MB cap.

### 2.6 Server-side price calculation

```
app.js → supabase.rpc('calculate_price', {p_vehicle_id, p_date_from, p_date_to})
       → :55521/rest/v1/rpc/calculate_price  (POST, apikey=anon)
       → PostgREST → postgres.public.calculate_price(uuid, date, date)
       ← jsonb {daily_subtotal, weekend_subtotal, subtotal_eur, vat_eur, total_eur,
                deposit_eur, total_days, weekday_count, weekend_day_count}
```

Pricing logic lives **only** in the database — the frontend never computes prices. `create_lead` also calls `calculate_price` internally to store an immutable pricing snapshot on the lead row.

### 2.7 Lead submission (server-computed prices)

```
app.js → supabase.rpc('create_lead', {p_name, p_email, p_phone, p_vehicle_id,
             p_vehicle_label, p_date_from, p_date_to, p_message, p_source,
             p_ip_address, p_ip_country})
       → PostgREST → postgres.public.create_lead(...)
       → internally calls calculate_price → stores pricing on lead row
       ← lead uuid
```

No price parameters are accepted from the client. This prevents price-tampering attacks.

### 2.8 Site settings & configurable hero image

```
app.js → supabase.from('site_settings').select('value').eq('key','hero_image_url').single()
       → :55521/rest/v1/site_settings?key=eq.hero_image_url&select=value
       → RLS: anon SELECT allowed
       ← { value: '/images/ferrari-main-car.png' }
       → app.js sets CSS variable --hero-bg on .hero element
```

Admin upload flow:
```
admin.js → supabase.storage.from('vehicle-photos').upload('site/hero.ext', file, {upsert:true})
         → storage API writes to bucket
         ← public URL
admin.js → supabase.from('site_settings').upsert({key:'hero_image_url', value: publicUrl})
         → PostgREST → UPDATE site_settings
         ← 200
```

The CSS uses `var(--hero-bg, url('images/ferrari-main-car.png'))` so the default hero image is shown until JavaScript loads and overrides it.

---

## 3. Database layout

```
postgres (database = "postgres")
├── auth schema           (GoTrue)
├── storage schema        (Storage API)
├── _realtime schema      (Realtime bookkeeping)
└── public schema
    ├── vehicles              (fleet)
    ├── leads                 (form submissions, server-computed pricing)
    ├── lead_attachments      (id_document, income_proof per lead)
    ├── customers             (spawned from qualified leads)
    ├── customer_attachments  (admin-uploaded docs for customers)
    ├── sales_orders          (rental orders linked to customer+lead)
    ├── sales_order_attachments
    ├── site_settings         (key-value config, e.g. hero_image_url)
    ├── tg_touch_updated_at()       trigger fn
    ├── calculate_price()           RPC (public, read-only pricing)
    ├── create_lead()               RPC (server-side price computation)
    ├── qualify_lead()              RPC
    ├── disqualify_lead()           RPC
    ├── reopen_lead()               RPC
    ├── sales_order_toggle_kaution()   RPC
    ├── sales_order_toggle_rental()    RPC
    ├── sales_order_toggle_complete()  RPC
    ├── customer_update_private_notes() RPC
    ├── sales_order_update_private_notes() RPC
    └── sales_order_upload_attachment()    RPC
```

### 3.1 `public.vehicles`
- `id uuid pk`
- `brand`, `model`, `power_hp`, `top_speed_kmh`, `acceleration`, `seats`, `daily_price_eur`
- `sort_order`, `location`
- `description_de`, `description_en`
- `photo_url`, `photo_path` (storage object key for deletion)
- `is_active bool` — public visibility flag
- `created_at`, `updated_at`

### 3.2 `public.leads`
- `id uuid pk`
- `name`, `email`, `phone`
- `vehicle_id → vehicles(id) ON DELETE SET NULL`
- `vehicle_label text` — denormalized at submit; survives vehicle deletion
- `date_from`, `date_to`, `message`
- Pricing (server-computed, immutable): `daily_subtotal`, `weekend_subtotal`, `subtotal_eur`, `vat_eur`, `total_eur`, `deposit_eur`, `total_days`, `weekday_count`, `weekend_day_count`
- `status check in ('new','qualified','disqualified')` default `new`
- `is_active bool` default `true` — filter for Active/Inactive tabs
- `admin_notes text`
- `source text` default `'website'`
- `ip_address inet`, `ip_country text` — geolocation info from submission
- `qualified_at`, `qualified_by → auth.users(id)`

### 3.3 `public.customers`
- `id uuid pk`
- `lead_id → leads(id) ON DELETE RESTRICT` + `unique index` (exactly one customer per lead)
- `name`, `email`, `phone`
- `first_contacted_at`
- `notes`, `private_notes`
- `status check in ('active','inactive')`
- `created_by → auth.users(id)`

### 3.4 `public.lead_attachments`
- `id uuid pk`
- `lead_id → leads(id) ON DELETE CASCADE`
- `bucket`, `file_path`, `file_name`, `mime_type`
- `kind check in ('id_document', 'income_proof', 'other')`
- Unique partial index: max 1 `id_document` + 1 `income_proof` per lead

### 3.5 `public.sales_orders`
- `id uuid pk`
- `customer_id → customers(id)`, `lead_id → leads(id)`
- `order_number`, `private_notes`
- `kaution_paid`, `rental_paid`, `rental_complete` (booleans + timestamps)
- Pricing snapshot: `daily_subtotal`, `weekend_subtotal`, `subtotal_eur`, `vat_eur`, `total_eur`, `deposit_eur`
- `total_days`, `weekday_count`, `weekend_day_count`, `date_from`, `date_to`, `vehicle_label`

### 3.6 `public.site_settings`
- `key text pk` — setting identifier (e.g. `hero_image_url`)
- `value text` — setting value (e.g. a public URL or path)
- `updated_at timestamptz`
- RLS: public SELECT for all, authenticated-only INSERT/UPDATE/DELETE

### 3.7 RLS matrix

| Table              | anon               | authenticated        |
| ------------------ | ------------------ | -------------------- |
| vehicles           | SELECT where `is_active=true` | full CRUD |
| leads              | INSERT only        | full CRUD            |
| lead_attachments   | INSERT only        | SELECT + INSERT      |
| customers          | denied             | full CRUD            |
| sales_orders       | denied             | full CRUD            |
| site_settings      | SELECT only        | full CRUD            |

### 3.8 Storage buckets

| Bucket              | Public read | anon writes | authenticated writes | Notes |
| ------------------- | ----------- | ----------- | -------------------- | ----- |
| `vehicle-photos`    | yes         | no          | INSERT (MIME `image/*`, 50 MB) | Fleet photos + site hero image |
| `customer-documents`| no          | INSERT only | SELECT + INSERT      | ID docs, income proofs |

Anon users may only `INSERT` into `customer-documents` (no SELECT/UPDATE/DELETE). Admin can SELECT + INSERT but not DELETE (prevents accidental evidence destruction).

### 3.9 RPCs

| Function                          | Role required   | Semantics                                                                 |
| --------------------------------- | --------------- | ------------------------------------------------------------------------- |
| `calculate_price(uuid, date, date)` | anon          | Read-only pricing calculator. Returns jsonb: `{daily_subtotal, weekend_subtotal, subtotal_eur, vat_eur, total_eur, deposit_eur, total_days, weekday_count, weekend_day_count}`. Uses vehicle's `daily_price_eur`, applies 20% weekend surcharge, 19% VAT, 3× daily deposit. |
| `create_lead(...)`                | anon            | Creates a lead with server-computed pricing (calls `calculate_price` internally). Accepts: name, email, phone, vehicle_id, vehicle_label, date_from, date_to, message, source, ip_address, ip_country. No price params from client. |
| `qualify_lead(uuid, text)`        | authenticated   | Locks lead row, flips to `qualified`+inactive, inserts matching customer + sales_order with pricing snapshot. Idempotent: returns existing customer on second call. |
| `disqualify_lead(uuid, text)`     | authenticated   | Marks lead `disqualified`+inactive, stores notes.                         |
| `reopen_lead(uuid)`               | authenticated   | Resets lead to `new`+active, deletes the spawned customer if any.         |
| `sales_order_toggle_kaution(uuid)` | authenticated  | Toggles kaution_paid boolean + timestamp on a sales order.                |
| `sales_order_toggle_rental(uuid)`  | authenticated  | Toggles rental_paid boolean + timestamp.                                  |
| `sales_order_toggle_complete(uuid)` | authenticated | Toggles rental_complete boolean + timestamp.                              |
| `customer_update_private_notes(uuid, text)` | authenticated | Updates private_notes field on a customer.                       |
| `sales_order_update_private_notes(uuid, text)` | authenticated | Updates private_notes field on a sales order.                 |

All three are `SECURITY INVOKER`, so `auth.uid()` is the live admin and RLS applies.

---

## 4. Realtime

- Postgres runs with `wal_level=logical`, `max_wal_senders=10`, `max_replication_slots=10`.
- `supabase/realtime:v2.30.23` on internal `:4000`, DB_USER=`postgres` (no `supabase_admin` on plain postgres).
- Publication `supabase_realtime` dynamically adds `leads`, `customers`, `vehicles`.
- `replica identity full` on all three — delivers the full row on updates (needed so the admin UI can show changed fields without re-fetching).
- Frontend joins a single channel (`mccars-admin`) and attaches three `postgres_changes` handlers.

---

## 5. API gateway (Kong)

Declarative config in `supabase/kong.yml`. One consumer per role:

| Consumer      | `apikey`           | ACL groups | Routes accessible                                          |
| ------------- | ------------------ | ---------- | ---------------------------------------------------------- |
| anon          | `ANON_KEY`         | `anon`     | `/auth/v1/*`, `/rest/v1/*` (rls-gated), `/realtime/v1/*`, `/storage/v1/object/public/*` |
| service_role  | `SERVICE_ROLE_KEY` | `admin`    | all of the above + `/pg/*` (postgres-meta), write paths    |

Plugins pipeline: `cors` → `key-auth` → `acl` → proxy. Public routes (storage public objects) are whitelisted without `key-auth`.

Host port mapping: `55521:8000` (8000 blocked by Docker Desktop's wslrelay on Windows; `555xx` range avoids collisions on busy Docker hosts).

---

## 6. Frontend architecture

```
frontend/
├── nginx.conf              (serves /, gzip, cache headers)
├── index.html  app.js      (public site, anon key, persistSession:false)
├── admin.html  admin.js    (admin CRM + settings, persistSession:true)
├── i18n.js                 (DE/EN)
├── styles.css              (copper/charcoal palette, CSS-variable hero image)
├── impressum.html
└── datenschutz.html
```

- `config.js` is generated at container start by an **inline `entrypoint:` command** in `docker-compose.yml`. It writes `window.MCCARS_CONFIG={SUPABASE_URL,SUPABASE_ANON_KEY}` from the container's environment variables. No separate script file is used — Unraid's filesystem does not preserve Unix execute bits on bind mounts, so a mounted `.sh` file would be silently ignored.
- `config.js` is **git-ignored**. The repo never ships a file with hardcoded URLs.
- Both `index.html` and `admin.html` load `config.js` with `?v=<timestamp>` appended via an inline `document.write` snippet, ensuring proxies and browsers never cache a stale URL.
- The `web` service uses `nginx:1.27-alpine` directly with bind-mounted files (no `build:` step). This is Portainer-compatible: updating the frontend is `git pull` + `docker compose up -d --force-recreate web`.
- `admin.js` is ES modules, imports `@supabase/supabase-js` from `esm.sh` (CDN, pinned version). One Supabase client per page.
- State lives in a single `state` object. Admin re-renders the active tab on realtime events.
- Admin tabs: Aktive Leads, Inaktive Leads, Kunden, Fahrzeuge, **Einstellungen** (site settings: hero image upload).
- Force-password-change modal is the only state that can preempt the rest of the admin UI after login.
- Public site hero image is loaded dynamically from `site_settings` table at boot and applied via CSS variable `--hero-bg`. Fallback default is baked into `styles.css`.

---

## 7. Deployment

**Host root:** `/mnt/user/appdata/mc-cars`

All bind mounts in `docker-compose.yml` use absolute paths under that root. The compose has no `build:` steps — every image is pulled from a registry, making it Portainer-compatible.

What's state (under `/mnt/user/appdata/mc-cars/data/`):
- `data/db/` — Postgres cluster
- `data/storage/` — object bucket files

What's code/config (committed):
- `docker-compose.yml`, `.env`, `supabase/`, `frontend/`, `AGENT.md`, `ARQUITECTURE.md`, `README.md`, `.gitattributes`

**Portainer deployment:**
1. Clone repo to `/mnt/user/appdata/mc-cars`
2. `mkdir -p data/{db,storage}`
3. Edit `.env`: set `SITE_URL` and `SUPABASE_PUBLIC_URL` to your domain (see below)
4. Portainer → Stacks → Add stack → paste compose + env vars → Deploy

> No `chmod` needed. The entrypoint that writes `config.js` is an inline shell command in `docker-compose.yml`, not a bind-mounted script, so file permissions are irrelevant.

**Environment: two variables to change per environment**

| Variable | Local dev | Production (NAS) |
|---|---|---|
| `SITE_URL` | `http://localhost:55580` | `https://your.domain.com` |
| `SUPABASE_PUBLIC_URL` | `http://localhost:55521` | `https://your.domain.com` |

All other GoTrue vars (`API_EXTERNAL_URL`, `GOTRUE_SITE_URL`, `GOTRUE_URI_ALLOW_LIST`) are derived from these two in `docker-compose.yml`.

**Nginx Proxy Manager (NPM) — required settings for single-domain HTTPS:**

Create one proxy host for your domain (e.g. `demo.lago.dev`):

*Details tab:*
- Scheme: `http`
- Forward Hostname / IP: `<NAS IP>` (e.g. `192.168.178.3`)
- Forward Port: `55580`
- Cache Assets: **OFF** (if left ON, NPM caches `config.js` and serves stale URLs)
- Websockets Support: **ON** (required for Realtime)
- Block Common Exploits: ON

*SSL tab:*
- SSL Certificate: your domain cert
- Force SSL: **ON**
- HTTP/2 Support: **ON**

*Advanced tab (⚙️) — paste this exactly:*

```nginx
location /auth/v1/ {
    proxy_pass http://192.168.178.3:55521;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

location /rest/v1/ {
    proxy_pass http://192.168.178.3:55521;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

location /realtime/v1/ {
    proxy_pass http://192.168.178.3:55521;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
}

location /storage/v1/ {
    proxy_pass http://192.168.178.3:55521;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}
```

This routes `https://your.domain.com/` → nginx (static site) and all API paths → Kong. Do **not** expose `/pg/` or Studio publicly.

For real deployments:
1. Generate a new `JWT_SECRET` and matching `ANON_KEY` / `SERVICE_ROLE_KEY` (see Supabase self-hosting docs).
2. Set a strong `POSTGRES_PASSWORD`, `ADMIN_PASSWORD`.
3. Wire real SMTP for password-reset mail (`SMTP_*`).
4. Back up `/mnt/user/appdata/mc-cars/data/` on a schedule.

---

## 8. Security model summary

- **No admin keys in the browser, ever.** Only the anon key reaches the client.
- **Signup is disabled** (`GOTRUE_DISABLE_SIGNUP=true`). New admins are provisioned by inserting into `auth.users` via `post-boot.sql` or Studio.
- **Bootstrap password is forced out** on first login (`must_change_password` metadata). Frontend refuses to reuse the bootstrap value.
- **RLS is the single source of authorization.** The admin UI calling `.from('leads').select('*')` works only because the authenticated policy allows it; without a valid Bearer JWT the same query returns zero rows.
- **RPCs are `SECURITY INVOKER`** — no role-escalation surface.
- **Server-side pricing** — `create_lead` computes prices internally via `calculate_price`. No price parameters are accepted from the client, preventing price-tampering.
- **Document security** — `customer-documents` bucket allows anon INSERT only (upload), no SELECT/DELETE. Admin can SELECT + INSERT but not DELETE. Unique partial indexes enforce max 1 `id_document` + 1 `income_proof` per lead.
- **Storage bucket** (`vehicle-photos`) is public-read (photos must render on the public site) but writes need authenticated, MIME-restricted, size-limited.