# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Status

**Fase 0–5 complete. Fase 6 complete (deploy readiness: PWA injectManifest SW + manifest + iOS meta tags + placeholder icons + Kong rate-limit on invitations + dev JWT rotation + prod env template + rls-audit.sh + Justfile lighthouse/rls-audit/test-rate-limit). Production stack deployed to ambrosio (OVH VPS) at https://erosi.limonia.net (app + Supabase, external Keycloak); edge proxy now off-stack — internal Caddy fans kong/app on host port 3000, whatever external proxy fronts the host terminates TLS and forwards plain HTTP to ambrosio:3000 (originally NetBird's self-hosted Traefik, now proxy-agnostic); PWA strategy switched to generateSW to unblock the prod build (see the "injectManifest hardcoded filename" gotcha below). Fase 7 complete: 12 new Playwright tests fill the collective-flow UI gap (O-01..O-03 onboarding, I-01..I-05 invitation acceptance, MC-02..MC-05 admin-manage) — writing them surfaced + fixed three latent bugs in the product code (atomic `create_collective()` RPC, pendingInvitationToken restore after login, manage page not reloading on late $currentCollective hydration). Plus migration 013 (auth.users role/aud default trigger) — found after the first real self-registration on prod via Keycloak→GoTrue left the user with empty `role`/`aud` columns, breaking every REST call with `role "" does not exist`. Post-fase-7 prod auth hardening (2026-04-20..21, see `docs/history/fase-8-auth-logout.md`): migration 014 extends the role/aud guard to UPDATE (GoTrue's pop-ORM resends role='' right after the INSERT and clobbered the INSERT trigger's backfill); `logout()` now does RP-initiated Keycloak end-session + lands on a new public `/logged-out` route, which both actually ends the Keycloak SSO cookie (so the next login re-prompts) and closes a PKCE-verifier race that produced "PKCE code verifier not found in storage" on prod. 259 tests green: 45 pgTAP + 140 Vitest integration + 15 Vitest unit + 59 Playwright + 1 Vitest rate-limit (gated, run via `just test-rate-limit`). 3 skipped in `just test-all` (2 Realtime presence — upstream `handle_out/3` bug; 1 rate-limit — gated). Push notifications + CI Docker + final production icons deliberately out of scope. ✅**

**MVP2 — in progress (4/6 ✅).** Branch `mvp2`. **Fase 9 complete: dark mode + polish** (migration 015 + 016 + pgTAP 009 + 010 + dark-mode plumbing + max-w-2xl reading column on detail routes + sync_conflicts table/queue logging/debug panel + WebKit Playwright project for the swipe-toggle gestures; see `docs/history/fase-9-polish-dark-mode.md`). **Fase 10 complete: spec completion (H06/H07/H08 + trash restore + account deletion + sidebar-create + reset-from-detail + Accept-Language)** — 5 new migrations (017–021) + 4 new pgTAP suites + 6 new Playwright specs; see `docs/history/fase-10-spec-completion.md`. **Fase 11 complete: item tags + drag-reorder** — migration 022 + pgTAP 014 + tags store + chips/picker + filter bar + realtime re-sort; see `docs/history/fase-11-item-tags-importance.md`. **Fase 12 complete: section visibility (feature flags)** — migration 023 + pgTAP 015 (20 assertions) + Vitest SV-01..SV-06 + Playwright SV-01..SV-03; JSONB `feature_flags` on `users`+`collectives`, `public.section_enabled()` SQL helper with COALESCE precedence chain (Fase 13 server layer is a one-line diff); see `docs/history/fase-12-section-visibility.md`. Test totals: 124 pgTAP + 163 Vitest integration + 45 Vitest unit + 82 Playwright + 1 Vitest rate-limit (gated) = **414 tests green**; 3 skipped in `just test-all` (2 Realtime presence — upstream `handle_out/3` bug `supabase/realtime#1617` still open; 1 rate-limit — gated). WebKit specs (2 in Fase 9.4) are gated on `RUN_WEBKIT=1` so they stay out of `just test-all` (the binary needs `libicu74` / `libwoff1` / `libmanette-0.2-0` system deps via `sudo pnpm exec playwright install-deps`). Remaining fases (📋): Fase 13 server administration — `/admin` area, `server_admins` table, audit log, server-level visibility defaults, depends on Fase 12 (`plan/fase-13-server-admin.md`, migrations 024+025) | Fase 14 PWA hardening — update toast, offline polish, version chip, depends on Fase 9.3 (`plan/fase-14-pwa-hardening.md`, no migration). Fase 7+8 already shipped as MVP cleanup, not MVP2.

## Documentation Map

- `README.md` — full development plan, confirmed tech stack, Justfile reference, technical warnings
- `docs/development.md` — local dev stack, repo layout, Justfile + test commands, endpoints, credentials, secrets
- `docs/deployment.md` — production deploy to ambrosio (erosi.limonia.net) + prod-specific fixes
- `docs/history/fase-{1,2a,2b,3,4,5,6,7}.md` — per-phase record of what was built
- `docs/history/fase-8-auth-logout.md` — post-fase-7 prod auth hardening (migration 014 + RP-initiated logout)
- `docs/history/fase-9-polish-dark-mode.md` — MVP2 dark mode + reading width + sync-conflicts debug panel
- `docs/history/fase-10-spec-completion.md` — MVP2 CU-H06/H07/H08 + trash RPCs + account delete + sidebar create + reset from detail + auto language
- `docs/history/fase-11-item-tags-importance.md` — MVP2 item tags + drag-reorder
- `docs/history/fase-12-section-visibility.md` — MVP2 section visibility / feature flags (this fase)
- `analysis/analisis-funcional.md` — complete functional specification (domain model, use cases, data models, business rules)
- `plan/fase-*.md` — phase-by-phase implementation plans (Fase 0 through Fase 7)
- `design/slate_collective/DESIGN.md` + `design/*/screen.png` — "Monolith Editorial" direction + per-screen Stitch mockups. Consult before changing UI.

## Code Language

**All code must be written in English.** This applies to variable names, function names, type names, database column names, SQL identifiers, comments, and any other code artifact. The UI may display text in any language, but the code layer is always English.

## Local Dev: Required /etc/hosts Entry

GoTrue (Supabase Auth) and the browser both must resolve `keycloak` to reach the Keycloak container. Add this line to `/etc/hosts` on the development machine:

```
127.0.0.1 keycloak
```

Without this, the OAuth redirect URL (`http://keycloak:8080/...`) built by Keycloak's discovery document is unreachable from the browser.

## Auth Architecture (Keycloak → GoTrue → Supabase)

**Strategy: Option B — GoTrue as OIDC proxy.**
The app authenticates with Keycloak (PKCE), then exchanges the Keycloak token with GoTrue for a Supabase JWT. Supabase RLS uses `auth.uid()` which resolves to the GoTrue user UUID (mapped from Keycloak `sub`). GoTrue maintains `auth.users`.

Key decisions:
- **Self-registration enabled** in Keycloak — users can create accounts on the Keycloak login screen.
- **Invitations are link-only** — no email sent. Admin generates a link and shares it manually.
- **Multiple collectives per user** — a user can belong to several collectives and switch between them in the sidebar.

Logout is RP-initiated: `logout()` in `$lib/auth` calls `supabase.auth.signOut()` to clear the GoTrue session, then redirects to Keycloak's `/protocol/openid-connect/logout` endpoint with `client_id` + `post_logout_redirect_uri=/logged-out`. Keycloak clears its SSO cookie and bounces the browser back to the public `/logged-out` page (outside the `(app)` group so the auth-gate `$effect` never runs). Keycloak shows a one-click "Do you want to log out?" confirmation because GoTrue's proxy flow does not surface the Keycloak `id_token` to the client — without `id_token_hint`, Keycloak always asks; this is the accepted UX. An `isLoggingOut` module flag suppresses the `(app)` layout `$effect` between `signOut()` and the end-session redirect, which otherwise races the navigation and can produce "PKCE code verifier not found in storage" errors.

## Sync Strategy

| Content | Mechanism | Target latency |
|---|---|---|
| Items in active shopping session | WebSocket (Supabase Realtime) | < 1 second |
| Lists, tasks (outside session) | SSE / polling | < 5 seconds |
| Notes | Polling | < 30 seconds |

**Offline-first:** all operations execute locally first (IndexedDB), then sync in background. Conflict resolution is last-write-wins. Conflicts are logged to a `sync_conflicts` table for debugging — no CRDT in MVP.

## Domain Model

The central organizing unit is the **Collective** (household group), not the individual user. All content belongs to the Collective.

**Roles:** `admin` (full CRUD + member management) | `member` (full CRUD on content) | `guest` (read-only)

**Core entities:** `Collective` → `CollectiveMember`, `ShoppingList` → `ShoppingItem`, `TaskList` → `Task`, `Note`, `CollectiveInvitation`

**Key field names (English):** `collective_id`, `list_id`, `is_checked`, `checked_by`, `checked_at`, `created_by`, `created_at`, `completed_at`, `sort_order`, `display_name`, `avatar_type`, `avatar_emoji`, `avatar_url`, `language`

**Key business rules:**
- Only one active shopping session per list at a time.
- Completing a list keeps items as history; it can be "reset" (uncheck all → back to active).
- Trash retains deleted items/notes for 7 days before permanent deletion.
- If the sole admin deletes their account, the system auto-promotes the oldest member before allowing deletion.

**Section visibility (feature flags) — required steps for any new top-level section.** A "section" here is one of the four current navigable areas (lists, tasks, notes, search) — adding a fifth means EVERY one of:
1. SQL migration that updates `public.known_sections()` to include the new key + (if it adds a new RPC) maps onto the planned Fase 13 server-layer extension of `public.section_enabled()`.
2. `SectionKey` union + `SECTION_KEYS` array entry in `packages/types/src/domain.ts`.
3. Default-checked toggle row in `/settings` (per-user) and `/collective/manage` (admin) — both already iterate `SECTION_KEYS`, so this is automatic as long as you add the `section_label_<key>` Paraglide message + the route prefix in `(app)/+layout.svelte`'s `SECTION_BY_PREFIX` array (drives the redirect guard).
4. pgTAP assertion in `supabase/tests/015_section_visibility.sql` that `section_enabled('<newkey>', user, collective)` defaults to true.

Without all four, the section either shows up but can't be hidden, or can be hidden but isn't toggled from the UI — both are confusing failure modes.

## Internationalisation

Library: **Paraglide JS** (`@inlang/paraglide-sveltekit`). Compile-time, zero runtime overhead, tree-shaken per language.

- Message files: `messages/en.json` (default) and `messages/es.json`
- Language detection order: `users.language` (if logged in) → `Accept-Language` header → `en`
- Language switching: update `users.language` in Supabase + call `setLanguageTag()` — no page reload
- **Never hardcode visible user-facing strings in components.** All UI text goes through Paraglide messages.

Supported languages: `en`, `es`.

## TypeScript Types

`packages/types/src/database.ts` is **generated** by `just db-types` (`supabase gen types typescript`). Never edit it manually. Domain-level types go in `packages/types/src/domain.ts`.

## Dev Test Users

Defined in `keycloak/realm-export.json` and `supabase/seed.sql`. All use password `test1234`.

| User | Email | Role in test collective |
|---|---|---|
| `ana` | ana@dev.local | Admin |
| `borja` | borja@dev.local | Member |
| `carmen` | carmen@dev.local | Member |
| `david` | david@dev.local | Guest |
| `eva` | eva@dev.local | (no collective — for onboarding testing) |

## Gotchas

### Auth / OIDC integration

- **`colectivo-web` is a confidential client** — GoTrue needs a client secret to exchange codes with Keycloak. `GOTRUE_EXTERNAL_KEYCLOAK_SECRET` must match Keycloak's client secret (`gotrue-dev-secret` in dev).

- **Custom `openid` client scope in Keycloak** — GoTrue v2.158.1 sends `scope=profile email` (no `openid`) to Keycloak, ignoring `GOTRUE_EXTERNAL_KEYCLOAK_SCOPES`. Without `openid` in the token scope, Keycloak's userinfo endpoint returns 401. Fix: a custom client scope named `openid` with `include.in.token.scope=true` is set as a default scope on `colectivo-web`, so Keycloak injects `openid` even when not requested.

- **`GOTRUE_DISABLE_SIGNUP: "false"`** — GoTrue's signup flag blocks OIDC-based user creation too. Must be `false`; Keycloak is the gatekeeper for who can register.

- **`auth.users` pre-seeded with Keycloak UUIDs** — GoTrue generates its own UUIDs on first login. `seed.sql` pre-inserts both `auth.users` (with `instance_id='00000000-0000-0000-0000-000000000000'` and empty-string token fields) and `auth.identities` (provider=keycloak, provider_id=Keycloak sub). This ensures `auth.uid()` = seed UUID = FK in all public tables.

- **Kong env var substitution via `kong-start.sh`** — Kong 2.8 does not interpolate `${VAR}` in declarative config files. `infra/kong-start.sh` runs `perl -pe 's/\$\{(\w+)\}/$ENV{$1}/ge'` on `kong.yml` before starting Kong. Without this, Kong loads the literal string `${SUPABASE_ANON_KEY}` as the API key, rejecting all requests.

- **GoTrue v2.158.1 inserts OIDC-provisioned users with empty-string `role` and `aud`, AND re-clears role via a follow-up UPDATE.** For users created via the external provider path (Keycloak → GoTrue), `auth.users.role` and `auth.users.aud` are left as `''` instead of the expected `'authenticated'`. The JWT minted from that row carries the empty role, and PostgREST's per-request `SET LOCAL role = $role_claim` then fails with `role "" does not exist` — every REST call errors out before policies or RPCs even run. Dev never surfaced this because `seed.sql` hardcodes both columns. Two migrations are required, not one: **013** backfills existing rows and installs a BEFORE INSERT trigger on `auth.users`; **014** installs a matching BEFORE UPDATE trigger because GoTrue immediately UPDATEs the same row (pop ORM resends the whole struct including the zero-value `role=''`), clobbering the insert-time backfill. Both triggers call the same `auth.ensure_user_role_default()` function. Symptom on prod (ambrosio) was two rows with `created_at`/`updated_at` ~150–250ms apart, empty `role`, `aud='authenticated'` (the UPDATE kept aud because GoTrue sends `'authenticated'` for it). Users in existing browser sessions must log out + back in once for a fresh JWT after the migrations are applied. Covered by `supabase/tests/008_auth_user_role_default.sql` (11 pgTAP assertions including UPDATE-clobber behaviour).

### SvelteKit / frontend

- **Do not call `getSession()` in a SvelteKit `load` function to gate auth.** Supabase JS v2 initialises its session from localStorage asynchronously (`_recoverAndRefresh`). In a `load` function that runs immediately on mount, `getSession()` can return `null` for a valid session before initialisation completes — causing `login()` to fire and redirect to Keycloak unnecessarily. The correct pattern: `(app)/+layout.ts` only sets `ssr: false`, and `(app)/+layout.svelte` uses a `$effect` that redirects when `!$authLoading && !$isAuthenticated`. This waits for `onAuthStateChange` to fire (which is authoritative).

- **`onAuthStateChange` fires `INITIAL_SESSION` on page load, not `SIGNED_IN`.** `SIGNED_IN` only fires immediately after a login flow. Load collective data whenever the session is present, regardless of event type — otherwise collectives are never loaded on page refresh. Pattern in root `+layout.svelte`: `if (session) { await loadUserCollectives(...) }` covering `INITIAL_SESSION`, `SIGNED_IN`, and `TOKEN_REFRESHED`.

- **CSS custom properties use RGB triplets — always use `rgb()`, never `hsl()`.** All design tokens in `app.css` store values as space-separated RGB triplets (e.g. `51 65 85`). Using `hsl(var(--token))` interprets the first value as a hue degree — `hsl(51 65% 85%)` renders as light yellow, not slate-700. The Tailwind config and any `background-color`/`color` declarations in `app.css` must use `rgb(var(--token) / <alpha>)`.

- **Supabase JS v2 + SvelteKit: `loadUserCollectives` (or any PostgREST query) inside `onAuthStateChange` must be deferred with `setTimeout(..., 0)`.** GoTrue's default `navigator.locks`-based auth lock is held while the `onAuthStateChange` callback runs. Any `supabase.from(...).select(...)` called inside that callback calls `getAccessToken() → getSession() → initializePromise → _acquireLock`, which is the same lock that's already held by the emit logic — the promise never resolves, the query hangs forever. Fix: schedule the query off the callback's microtask chain with `setTimeout(async () => { await loadUserCollectives(session.user.id); … }, 0)`. As a defensive extra, `$lib/supabase` passes a pass-through `auth.lock` option so the whole lock path is a no-op. Both are needed: without the defer, some browsers still deadlock; without the pass-through, others do. Removing either one breaks the Playwright E2E suite.

- **Logout must be RP-initiated AND guarded against the auth-gate `$effect`.** A naïve logout (`supabase.auth.signOut()` only) has two problems at once: (a) Keycloak's SSO cookie is untouched, so the `(app)` layout's `$effect` immediately re-fires `login()` and Keycloak silently re-authenticates from the SSO cookie — logout looks broken; (b) the `signOut` → `onAuthStateChange` → `$effect` → `signInWithOAuth` chain races `signOut`'s async storage clear, and can clobber the PKCE verifier just written by `signInWithOAuth`, producing "PKCE code verifier not found in storage" on prod (dev doesn't hit it because the loopback round-trip is sub-ms). Fix, in `$lib/auth`: set a module-level `isLoggingOut = true`, call `signOut()`, then `window.location.assign(keycloakEndSessionUrl)` with `client_id` + `post_logout_redirect_uri=${origin}/logged-out`. The `(app)` layout `$effect` reads `isLoggingOut` and skips its auto-login branch. `/logged-out` is a public route outside the `(app)` group so the effect never runs there at all. Keycloak shows a "Do you want to log out?" confirmation because we cannot pass `id_token_hint` (GoTrue's proxy flow doesn't expose the Keycloak id_token in the Supabase session — only `provider_token` and `provider_refresh_token`); clicking once is accepted UX. Covered by A-05 + A-07 in `apps/web/tests/e2e/auth.test.ts`.

- **Never call `crypto.randomUUID()` directly — use `generateId()` from `$lib/utils/id`.** `crypto.randomUUID` is gated behind secure contexts (HTTPS / localhost / 127.0.0.1 / file://). When the app is served over a bare LAN IP for on-device phone previews (`http://192.168.1.167:5173`), `window.crypto.randomUUID` is `undefined` and every optimistic-id site crashes `handleAdd` with `TypeError: crypto.randomUUID is not a function`. `generateId()` uses `crypto.randomUUID()` when available and falls back to an RFC 4122 v4 assembly via `crypto.getRandomValues()` (which IS exposed on insecure contexts). Covered by `src/lib/utils/id.test.ts` (U-01..U-04) + `tests/e2e/insecure-context.test.ts` (INS-01, stubs `randomUUID = undefined` via `addInitScript` and drives the add-item flow).

### PWA / build

- **PWA service worker: do not use `injectManifest` with a file named `service-worker.ts`.** SvelteKit intercepts any file at `src/service-worker.[jt]s` and enforces a hard restriction: only `$service-worker` and `$env/static/public` may be imported — Workbox modules are blocked. With `injectManifest`, `@vite-pwa/sveltekit` tries to find the compiled SW output at `.svelte-kit/output/client/service-worker.js`, but SvelteKit never produces it (compilation fails on the blocked imports). Fix for Fase 1–2a: use `generateSW` strategy (plugin auto-generates the SW, no custom source needed). Fix for Fase 2b when a custom SW is needed: name the file `src/sw.ts` — SvelteKit does not recognise it as a service worker — and set `strategies: 'injectManifest'`, `filename: 'sw.ts'` in `vite.config.ts`.

- **`@vite-pwa/sveltekit` does not auto-register the service worker.** The plugin provides a virtual module (`virtual:pwa-register`, `virtual:pwa-register/svelte`) but you have to call `registerSW()` (or `useRegisterSW()` for Svelte bindings) yourself. Without that call, the SW file is generated + served but never registered, and `navigator.serviceWorker.getRegistration()` returns `undefined`. The canonical place is `apps/web/src/routes/+layout.svelte`'s `onMount()`.

- **`@vite-pwa/sveltekit` 0.6.8 `injectManifest` hardcodes the source SW filename.** The plugin looks for the compiled SW at `.svelte-kit/output/client/service-worker.js` regardless of `filename: 'sw.ts'`. SvelteKit blocks Workbox imports from `src/service-worker.[jt]s` (see the SW-filename gotcha above), so there's no way to actually feed the plugin. **Workaround:** use `strategies: 'generateSW'` instead — the plugin auto-generates a precache-only SW equivalent to our minimal `src/sw.ts`. `vite.config.ts` currently uses this.

### Testing

- **Playwright auth: do not cache Supabase sessions via `storageState`. Do a live Keycloak login per test.** `browser.newContext({ storageState })` restores localStorage + cookies, but Supabase's `_recoverAndRefresh` does not reliably re-fire `INITIAL_SESSION` from a restored context — the page hydrates without triggering `onAuthStateChange`, so `currentUser` / `currentCollective` stay empty. The working pattern is `await loginAs(page, USERS.ana)` at the top of each test (see `tests/fixtures/login.ts`), which exercises the real OAuth flow and waits for the session token to land in localStorage before returning. Adds ~1s per test but is deterministic.

- **Playwright `baseURL` is hardcoded to `http://localhost:5173`, ignoring `PUBLIC_APP_URL`.** The LAN-IP variant in root `.env` is for on-device phone previews and introduces timing races in the OAuth round-trip that produce flaky failures in the suite. Keep the loopback for deterministic E2E; phone previews are separate.

- **Realtime `waitFor` timeout is 10s, not 5s.** Under full `just test-all` load the Phoenix socket occasionally takes several seconds to propagate a fresh subscription's filter before the test mutation fires. Set in `packages/test-utils/src/realtime-helpers.ts`. Don't drop below 10s — passing runs resolve on event arrival, only the flake window widened.

- **WebKit (Mobile Safari) Playwright project is gated on `RUN_WEBKIT=1`.** Fase 9.4 added a `webkit` project (`devices['iPhone 13']`) for touch-gesture specs (the swipe-to-check/uncheck handlers use unified pointer events, but Chromium's touch emulation does not fire them in the right order). The project is conditionally included in `playwright.config.ts` based on `process.env.RUN_WEBKIT` so `just test-all` stays green on hosts that lack the GTK/WebKit system deps (libicu74, libxml2, libmanette-0.2-0, libwoff1 — install via `sudo pnpm exec playwright install-deps`). `just test-webkit` runs the gated suite. Specs named `*.webkit.test.ts` are also excluded from the chromium project via `testIgnore`, so a file never runs in both browsers.

### Backend / RLS / Kong

- **`strip_path: true` on a full-table Kong route strips too much.** With `paths: [/rest/v1/collective_invitations]` and `strip_path: true`, Kong forwards `/` upstream — PostgREST returns `PGRST117: Unsupported HTTP method: POST` because it tries to route root, not the table. Fix: add a `request-transformer` plugin that `replace.uri: /collective_invitations`. Applies identically to any route where the match path equals the full upstream resource path (as opposed to a prefix match like `/rest/v1/`).

- **Kong `KONG_PLUGINS` must explicitly list every plugin used in `kong.yml`.** Without `rate-limiting` (or any other non-default plugin) in the comma list, Kong boots with `plugin 'xxx' not enabled; add it to the 'plugins' configuration property`. `infra/docker-compose.dev.yml` currently lists `request-transformer,cors,key-auth,acl,basic-auth,rate-limiting`.

- **`.select()` after `.insert()` evaluates the SELECT RLS policy on the returned row, not just the INSERT policy.** PostgREST's chained `.insert(...).select().single()` adds `RETURNING *` to the INSERT, and Postgres then evaluates the table's SELECT policy against the fresh row. For policies that gate on sibling-table state (e.g., `is_member(id)` requiring a row in `collective_members`), this causes INSERTs to spuriously fail with "new row violates row-level security policy" even though the INSERT policy itself passed. Fix: drop the `.select()` if you don't need the row back, or (better) wrap the insert + any sibling-table sets in a `SECURITY DEFINER` RPC that atomically builds the membership state.

### Deploy

- **supabase/postgres v15.1.1.78 does NOT create the Supabase internal roles on a fresh volume.** The image ships with custom extensions + configs but no role-bootstrap script. Dev "works" because its volume was initialised long ago, grandfathering the roles (`authenticator`, `anon`, `authenticated`, `service_role`, `supabase_admin`, `supabase_auth_admin`, `supabase_storage_admin`, `supabase_replication_admin`, `supabase_realtime_admin`, `pgbouncer`, `dashboard_user`). On a fresh volume only `postgres` exists. `infra/db-init/00-role-passwords.sh` now bootstraps them idempotently (create-if-missing) + enables `pg_cron` + creates the empty `supabase_realtime` publication + sets `supabase_auth_admin` search_path to `auth` (without this GoTrue migrations create `factor_type` in `public` and later migrations explode).

- **`docker compose exec -T` without explicit stdin drains the surrounding heredoc.** When a script is delivered over SSH as `ssh host bash -s << 'REMOTE' ... REMOTE`, the outer bash reads its script from stdin. Any `docker compose exec -T <service> <cmd>` inside that script — especially inside a command substitution like `x=$(docker compose exec -T db psql -c '...')` — inherits and consumes that same stdin, swallowing the rest of the heredoc. Symptom: the first iteration of a loop runs, every following iteration is silently skipped. Caused `infra/scripts/deploy-erosi.sh` to silently skip migrations 012 and 013 on prod until fixed (had to apply manually after every deploy). **Fix:** redirect stdin per call with `</dev/null` (or the file being piped in). Applies to any heredoc-delivered remote script using `docker compose exec -T` or `kubectl exec`.

### Critical Platform

**iOS Safari / PWA:**
- The app uses Supabase OAuth PKCE flow (`signInWithOAuth({ provider: 'keycloak' })`). No keycloak-js, no iframe-based silent refresh — Safari-compatible by design.
- GoTrue session is persisted in `localStorage` and auto-refreshed by the Supabase client. No manual `updateToken()` needed.
- Background Sync API is not supported in Safari. Implement a manual fallback using the `online` event.
- Push notifications require iOS 16.4+ and the PWA must be installed to the home screen.
- Test the shopping session mode on a real iPhone during development, not only in DevTools.

**Edge proxy (prod):**
- Two layers. Internal: a Caddy container on the `colectivo` Docker network listens on `:3000` (host port `3000:3000`) and does the path split — `/rest/v1`, `/auth/v1`, `/realtime/v1`, `/storage/v1`, `/graphql/v1`, `/pg` → `kong:8000`; everything else → `app:3000`. Config in `infra/Caddyfile.erosi`, mounted into `caddy:2-alpine`. Plain HTTP, no TLS, no Host matching. Global block sets `auto_https off` + `admin off` so the only socket is `:3000`.
- External: TLS termination + outermost routing live outside this repo (originally NetBird's self-hosted Traefik on ambrosio, but the stack is proxy-agnostic now). Whatever fronts the host terminates `https://erosi.limonia.net` and forwards plain HTTP to `ambrosio:3000`. One upstream is enough — Caddy fans out internally.
- External proxy must allow long-lived idle WebSockets for Realtime (≥3600s, or unlimited). NetBird's default is `0` (unlimited). Symptom of too-short: client reconnects continuously mid-shopping-session.
- Bind-mounted files (Caddyfile, etc.) require `docker compose restart caddy` after editing. The deploy script's `up -d` only recreates services whose compose definition changed — a Caddyfile-only edit will silently keep the old config loaded.
- CSP `connect-src` must include both `https://erosi.limonia.net` and `wss://erosi.limonia.net`.

**External Keycloak:**
- Keycloak runs outside this stack; `PUBLIC_KEYCLOAK_URL` in `.env` points at it. Whatever proxy sits in front of it must forward `X-Forwarded-{Host,Port,Proto}` or Keycloak builds redirect URIs with internal ports and the OIDC flow breaks — that's the external operator's concern, not ours.
- The realm + client configuration required on the external Keycloak is documented in `docs/deployment.md` and mirrored in `keycloak/realm-export.erosi.json` (kept as reference; not imported by this stack). Realm name, client id, and client secret are operator-chosen and wired in via `.env` — the file's literal `colectivo-web` / `colectivo` values are illustrative, not required.
- **Legacy Keycloak `/auth/` base path**: Keycloak ≤ 16 (and some later distributions) serve realms under `/auth/realms/...` instead of `/realms/...`. In that case `PUBLIC_KEYCLOAK_URL` must include the suffix, e.g. `https://auth.example.com/auth`. Symptom of a missing `/auth` suffix: `GOTRUE_EXTERNAL_KEYCLOAK_URL/...well-known/openid-configuration` returns 404; the Supabase login redirect lands on a Keycloak error page. Verify before deploying: `curl -I ${PUBLIC_KEYCLOAK_URL}/realms/${PUBLIC_KEYCLOAK_REALM}/.well-known/openid-configuration` must return 200.

**Supabase Realtime self-hosted:**
- Check `MAX_REPLICATION_SLOTS` in PostgreSQL and `REALTIME_MAX_CONNECTIONS` before going to production.
- Presence subscriptions are more resource-intensive than Postgres Changes — limit them to lists with an active session.
