collective-lists/docs/deployment.md

# Deployment

## Production stack — ambrosio (OVH VPS)

Live URL: **https://erosi.limonia.net** (app + Supabase API, single-domain). Keycloak is external — its URL is set via `PUBLIC_KEYCLOAK_URL` in `/opt/colectivo/.env`. OVH VPS, Ubuntu 24, Docker 29.

- `infra/docker-compose.erosi.yml` — full stack (db, auth, rest, realtime, storage, imgproxy, kong, app, caddy). `studio`, `meta`, and the bundled `keycloak` service are intentionally absent. All services live on a single internal Docker network (`colectivo`); nothing on the stack is bound to a public network or labeled for an external reverse proxy.
- **Internal edge — Caddy on `:3000`**: `infra/Caddyfile.erosi` does the path split — `/rest/v1`, `/auth/v1`, `/realtime/v1`, `/storage/v1`, `/graphql/v1`, `/pg` → `kong:8000`; everything else → `app:3000`. WebSockets pass through (Caddy's `reverse_proxy` handles upgrade headers). Plain HTTP, no TLS. The container publishes host port 3000 (`ports: ["3000:3000"]`). Global block sets `auto_https off` + `admin off`, so the only listening socket is `:3000` — no ACME, no admin API.
- **External reverse proxy + TLS** are owned outside this repo (originally NetBird's self-hosted Traefik on ambrosio). Whatever proxy fronts the host terminates TLS for `erosi.limonia.net` and forwards plain HTTP to `ambrosio:3000`. Repo has no labels, no Traefik network attachment, no cert config — only the bind on `:3000`.
- `infra/scripts/deploy-erosi.sh` — idempotent redeploy: rsync → on first run generate secrets + write `/opt/colectivo/.env` with placeholders the operator fills in (`PUBLIC_KEYCLOAK_URL`, `KEYCLOAK_CLIENT_SECRET`) → subsequent runs fail fast if any placeholder is unfilled → docker compose build + up → apply pending migrations. Never touches `/etc/caddy/*`, never runs `sudo`. Note: bind-mounted files (e.g. `Caddyfile.erosi`) require `docker compose restart caddy` after editing — the script's `up -d` only recreates services whose compose definition changed.
- `keycloak/realm-export.erosi.json` — **not imported** by this stack (no bundled Keycloak). Kept as reference for what the external Keycloak client must have — realm name, client id, and client secret are operator-chosen and wired in via `.env` (`PUBLIC_KEYCLOAK_REALM`, `PUBLIC_KEYCLOAK_CLIENT_ID`, `KEYCLOAK_CLIENT_SECRET`), so the file's literal `colectivo-web` / `colectivo` values are illustrative, not required. What the client must actually have: `redirectUris: ["https://erosi.limonia.net/*", "https://erosi.limonia.net/auth/v1/callback"]`, `webOrigins: ["https://erosi.limonia.net"]`, confidential, `openid` as a default client scope with `include.in.token.scope=true`.
- `.env.erosi.example` — committed template. The real `/opt/colectivo/.env` on ambrosio is 600 and stays on the server.

## Pre-deploy prerequisites

1. **External Keycloak** reachable from ambrosio's Docker network (outbound HTTPS). Realm + confidential client configured as described above; `.env` gets `PUBLIC_KEYCLOAK_URL`, `PUBLIC_KEYCLOAK_REALM`, `PUBLIC_KEYCLOAK_CLIENT_ID`, `KEYCLOAK_CLIENT_SECRET`. **If the Keycloak deployment uses the legacy `/auth/` base path** (Keycloak ≤ 16 default; some distributions still carry it — `auth.fosil.eu` is one) then `PUBLIC_KEYCLOAK_URL` must include it, e.g. `https://auth.example.com/auth`. Verify by opening `${PUBLIC_KEYCLOAK_URL}/realms/${PUBLIC_KEYCLOAK_REALM}/.well-known/openid-configuration` — it should return 200 JSON; 404 means the base path is wrong.
2. **Users in the external Keycloak must match existing `auth.users` UUIDs** (`auth.users.id = keycloak sub`) or the stack starts fresh with no prior users. Re-registering produces new sub UUIDs; either re-seed `auth.users` + `auth.identities` or wipe and start over.
3. **External proxy idle timeout** — Realtime WebSockets stay open for the entire shopping session, so whatever fronts the host must allow long-lived idle connections. NetBird's installer sets `--entryPoints.websecure.transport.respondingTimeouts.idleTimeout=0` (unlimited) by default; if the external proxy is something else, configure ≥3600s on the relevant entrypoint. Symptom of a too-short timeout: client reconnects continuously mid-session.
4. **External proxy upstream** — forward `erosi.limonia.net` (HTTPS, terminate TLS there) to `ambrosio:3000` (plain HTTP). One upstream is enough; Caddy inside the stack does the kong-vs-app path split.
5. **DNS** for `erosi.limonia.net` → ambrosio (A/AAAA).
6. **Host Caddy disabled**: `sudo systemctl stop caddy && sudo systemctl disable caddy`. Without `disable`, the unit comes back on reboot and races the external proxy for 80/443. Remove any `# BEGIN colectivo-erosi … # END` block from `/etc/caddy/Caddyfile` for tidiness. (The internal Caddy inside the stack runs in a container on `:3000` and is unrelated to this host unit.)

## Prod-specific fixes

Had to be made for the stack to boot cleanly on a fresh volume:

- `infra/db-init/00-role-passwords.sh` — rewrote as idempotent bootstrap (see the "supabase/postgres doesn't bootstrap Supabase roles" gotcha in `CLAUDE.md`): creates all Supabase service roles if absent, enables `pg_cron` + `pgcrypto` + `uuid-ossp`, creates `auth` / `storage` / `graphql_public` / `_realtime` / `realtime` schemas + empty `supabase_realtime` publication, sets `supabase_auth_admin` search_path to `auth`. The old `00-role-passwords.sql` (ALTER-only) has been removed.
- `apps/web/vite.config.ts` — PWA strategy switched `injectManifest` → `generateSW` (see the "injectManifest hardcoded filename" gotcha in `CLAUDE.md`).

## Runbook

### App build hang / PostHog timeout
The `app` image's final build stage (`pnpm --filter @colectivo/web build` → vite SSR) occasionally stalls during `transforming...` and ~9 minutes later dies with `UnhandledPromiseRejection: "Timeout while shutting down PostHog"`. Intermittent; reproducible across rebuilds with the same layer cache.

Fix: rebuild with `--no-cache` once:
```sh
ssh ambrosio 'cd /opt/colectivo && docker compose --env-file .env -f infra/docker-compose.erosi.yml build --no-cache app'
```
Subsequent normal builds succeed. Run inside `tmux` if you're on a flaky SSH link — the no-cache build takes 5–15 minutes and losing the connection kills BuildKit.

### Changing `PUBLIC_*` env values (URL, realm, client id)
Any change to `PUBLIC_APP_URL`, `PUBLIC_SUPABASE_URL`, `PUBLIC_KEYCLOAK_URL`, `PUBLIC_KEYCLOAK_REALM`, `PUBLIC_KEYCLOAK_CLIENT_ID` requires an **app rebuild** (those values are baked into the client bundle at build time via `docker-compose.erosi.yml` build args). After editing `.env`:
```sh
ssh ambrosio 'cd /opt/colectivo && docker compose --env-file .env -f infra/docker-compose.erosi.yml build app && docker compose --env-file .env -f infra/docker-compose.erosi.yml up -d'
```
`KEYCLOAK_CLIENT_SECRET` is runtime-only (GoTrue reads it) — for that one a `docker compose restart auth` is enough.

### Rollback (code + DB)

Every `just deploy` pairs a code-rsync with a `pg_dumpall` taken just before the build, and logs the pair to `/opt/colectivo/.deploys.log` on ambrosio. Rollback uses that log to restore both atomically.

```sh
just rollback-list          # show recent deploys (timestamp / sha / backup)
just rollback               # roll back code + DB to N-1
just rollback-to <git-sha>  # roll back to a specific deploy
just rollback-code          # roll back code only, leave DB at current state
```

Safety:
- 5-second abort window before anything destructive runs.
- Verifies the target SHA exists in the local git repo (fetch first if you're rolling back to an old commit).
- Verifies the matching backup is still on ambrosio (older than 10 deploys = pruned).
- Stops `app` / `auth` / `rest` / `realtime` / `storage` before the `gunzip | psql` restore so they don't observe inconsistent state.
- Materialises the target commit into a temp `git worktree`; your working tree is untouched.
- Does **not** write to `.deploys.log` — rollback is intentionally not a deploy event. The next `just deploy` will be from your current HEAD.
- The storage volume (user uploads at `/var/lib/storage`) is **not** rolled back. Treated as append-only.

When to use `--code-only`: regression is UI/JS only and the DB schema is forward-compatible. Skips the destructive restore but still rebuilds + redeploys the older app image.

### Wipe all app + auth data (keep schema, keep migrations)
When switching Keycloak realms or starting fresh:
```sql
BEGIN;
TRUNCATE TABLE public.notes, public.tasks, public.task_lists,
  public.shopping_items, public.shopping_lists, public.item_frequency,
  public.collective_invitations, public.collective_members, public.collectives,
  public.users
  RESTART IDENTITY CASCADE;
TRUNCATE TABLE auth.refresh_tokens, auth.sessions, auth.identities, auth.users
  RESTART IDENTITY CASCADE;
COMMIT;
```
CASCADE also empties `auth.mfa_factors`, `auth.mfa_amr_claims`, `auth.mfa_challenges`, `auth.one_time_tokens` — expected. Never touch `auth.schema_migrations` or `public._applied_migrations` (they track what DDL has been applied).

## Server administration (Fase 13)

The instance has a global `server_admin` role separate from the per-collective `admin` role. Server admins can list/soft-delete/restore/hard-delete any collective, remove members, set server-level section visibility defaults, and read the append-only audit log at `/admin/audit`.

### Promoting the first admin

Set `SERVER_ADMIN_EMAIL` in `.env` to the operator's email BEFORE first deploy. `infra/db-init/10-server-admin-seed.sh` runs once on first volume init and inserts the matching user into `public.server_admins`.

**Gotcha**: `docker-entrypoint-initdb.d` fires before any user has signed in via Keycloak, so `public.users` is empty and the script will log `WARNING: no public.users row matches email <...>, skipping`. To actually bootstrap:

1. Bring up the stack normally (`docker compose --env-file .env -f infra/docker-compose.erosi.yml up -d`).
2. Sign in once via Keycloak using `SERVER_ADMIN_EMAIL` to populate `public.users`.
3. Re-run the bootstrap script manually:
   ```bash
   docker compose -f infra/docker-compose.erosi.yml exec -T db \
       bash /docker-entrypoint-initdb.d/10-server-admin-seed.sh </dev/null
   ```
   The `</dev/null` redirect is mandatory per the heredoc-stdin gotcha (the surrounding bash heredoc on the SSH side would otherwise be drained).

After this `server_admins` is non-empty and the script is a no-op forever.

### Promoting subsequent admins

Use `/admin/admins` → "Promote user" with the target's email. Or directly in SQL:
```sql
INSERT INTO public.server_admins (user_id, granted_by)
SELECT id, '<your-user-id>'::uuid FROM public.users WHERE email = '<target>'
ON CONFLICT (user_id) DO NOTHING;
```

### Last-admin guard

`revoke_server_admin()` refuses to remove the only remaining admin (`P0003 'last_admin'`). If you really need to demote the last admin (e.g. handover), promote the replacement FIRST.

### Audit log

Every privileged RPC writes to `public.admin_actions`. Read it from `/admin/audit` or with:
```sql
SELECT created_at, actor_id, action, target_type, target_id, payload
FROM public.admin_actions ORDER BY created_at DESC LIMIT 100;
```
The log is append-only via RLS — there are no INSERT/UPDATE/DELETE policies, only the SECURITY DEFINER RPCs can write. A `postgres` superuser shell can still delete rows; the threat model is "malicious admin via the product UI", not "operator with DB access".

## Not yet configured on ambrosio

- SMTP (Resend)
- Automated backups — script exists at `infra/scripts/backup.sh`, not scheduled
- Lighthouse run against prod URL
- Final production icons