Symptom observed twice (migrations 012 and 013): deploy-erosi.sh ran the
migration loop's first iteration (the `CREATE TABLE IF NOT EXISTS
_applied_migrations` step) then silently skipped every file in
supabase/migrations/*.sql without printing a single skip/apply line. Had
to `psql -f <new-migration>` by hand on ambrosio after every deploy.
Root cause: the deploy uses `ssh host bash -s << 'REMOTE' ... REMOTE` so
the outer bash on the remote reads its own script from stdin. Any
`docker compose exec -T db psql ...` inside that script — particularly
inside a command substitution like `already=$(docker compose exec -T ...)`
— inherits that stdin and consumes it, eating the rest of the heredoc.
Fix: pass `</dev/null` (or the migration file for the actual `apply`
step) to every `docker compose exec -T` call so docker gets an empty /
file-scoped stdin instead of the parent's heredoc.
Verified on ambrosio: deploy now prints all 13 `skip ... (applied)` lines
as expected, and will apply new migrations going forward without manual
intervention.
- CLAUDE.md: gotcha #20 documenting the pattern; applies to any
heredoc-delivered remote script using `docker compose exec -T` or
`kubectl exec`.
Self-contained compose for a single-VPS deploy behind the host's Caddy.
App + Supabase API share erosi.oier.ovh; Keycloak on auth.oier.ovh.
- infra/docker-compose.erosi.yml — full stack bound to 127.0.0.1 only
(db, auth, rest, realtime, storage, imgproxy, kong, meta, keycloak, app)
- infra/caddy/erosi.Caddyfile — host-Caddy snippet with TLS + path routing
(/auth /rest /storage /realtime /graphql → kong, rest → app)
- infra/scripts/deploy-erosi.sh — rsync + first-run secret generation +
build + migrations + Caddy snippet install
- keycloak/realm-export.erosi.json — prod redirect URIs, registration on,
client secret as __KEYCLOAK_CLIENT_SECRET__ placeholder (deploy sub'd)
- .env.erosi.example — env template with placeholders
Supporting fixes to make the deploy work on a clean Supabase-postgres volume:
- infra/db-init/00-role-passwords.sh — rewrote as idempotent bootstrap:
CREATE the Supabase service roles if missing, set passwords on pre-existing
ones, enable pg_cron/pgcrypto/uuid-ossp, create auth/storage/graphql_public/
_realtime/realtime schemas, create empty supabase_realtime publication,
set per-role search_path (auth→auth, storage→storage). Old version only
ALTERed passwords and relied on the roles already existing — worked for a
grandfathered dev volume, failed on a fresh prod init.
- infra/db-init/00-role-passwords.sql — removed (folded into .sh).
- apps/web/vite.config.ts — PWA strategy generateSW (was injectManifest).
The plugin's injectManifest hardcodes the SW source filename to
service-worker.js and ignores the filename: 'sw.ts' override, making the
production build fail. generateSW's auto-generated precache-only SW is
functionally equivalent to our 5-line src/sw.ts.
Tested end-to-end on ambrosio: all 9 containers up, 11 migrations applied,
https://erosi.oier.ovh returns 200, Keycloak OIDC discovery serves the
correct issuer, /auth/v1/settings lists Keycloak as the sole external
provider.
Closes the deferrals from Fase 4 that gated a public deploy of the MVP.
PWA install
- Custom SW at apps/web/src/sw.ts (named sw.ts, not service-worker.ts, so
SvelteKit doesn't intercept it and block Workbox imports).
- vite.config.ts switched to injectManifest strategy with precache of the
built shell; no runtime caching of Supabase (offline writes stay on the
existing $lib/sync pending_ops queue).
- Root +layout.svelte onMount() calls registerSW({ immediate: true }) from
virtual:pwa-register — the plugin does not auto-register.
- Web manifest with 192/512/512-maskable icons + iOS meta tags
(apple-mobile-web-app-capable, apple-touch-icon, etc.) in app.html.
- Placeholder icons generated via ImageMagick; replace before public launch
(noted in apps/web/static/icons/README.md).
Kong rate-limit
- /rest/v1/collective_invitations POST: 20/hour per Authorization header.
Anti-spam on invitation creation is the only rate-limit that survived —
auth rate-limits were dropped during execution (see plan §6.3): Keycloak
already provides bruteForceProtected=true on the realm, and stacking a
Kong limit on /auth/v1/token throttled legitimate PKCE code exchanges
during E2E.
- Added 'rate-limiting' to KONG_PLUGINS in docker-compose.dev.yml.
- Discovered: strip_path=true on a full-table path sends "/" upstream and
PostgREST rejects POST to root with PGRST117. Route carries a
request-transformer plugin that rewrites the URI back.
JWT rotation + prod template
- infra/scripts/rotate-jwt.sh signs anon + service_role JWTs with a fresh
48-byte secret via openssl. Prints three values for the operator to
paste; does not touch files.
- Dev secret rotated as a dry-run. Updated both .env (local, gitignored)
and apps/web/.env.development. Existing sessions are invalidated — all
users must re-login once.
- .env.production.example committed with placeholders + rotation notes.
Lighthouse + RLS audit tooling
- `just lighthouse` boots the prod build + Supabase stack and runs
lighthouse CLI against PWA/A11y/Best-Practices.
- `just rls-audit` runs infra/scripts/rls-audit.sh which signs an Eva JWT
(non-member) and GETs 8 REST endpoints — all must return []. Complements
the Vitest rls-audit.test.ts suite.
Tests
- 236 green: 34 pgTAP + 140 Vitest integration + 15 unit + 46 Playwright.
- 1 rate-limit test gated behind RUN_RATE_LIMIT_TESTS=1 (Kong counters are
shared state); run via `just test-rate-limit` which force-recreates Kong
first to reset counters.
- 3 skipped in `just test-all` (2 Realtime presence upstream bug, 1 gated
rate-limit).
Deliberately out of scope: push notifications, CI ephemeral Docker, final
production icons, runtime caching of Supabase (SyncBanner + pending_ops
already covers offline).
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
