Migration 025 adds:
- collectives.deleted_at column for soft delete (app-level visibility;
RLS does NOT enforce soft-delete invisibility — admin area surfaces
everything by design).
- server_settings(key text pk, value jsonb) — generic admin-write bag
seeded with an empty 'default_sections' row.
- section_enabled() extended with the server layer as the topmost
coalesce branch. Final precedence: server > collective > user > true.
- Privileged RPCs: grant_/revoke_server_admin (last-admin guard fires
P0003), admin_list_collectives, admin_soft_delete_collective,
admin_restore_collective, admin_hard_delete_collective (30-day or
p_force=true), admin_remove_member, admin_set_default_section
(rejects unknown sections via known_sections() lookup).
- _log_admin_action(): private helper centralising the
audit INSERT, called by every privileged RPC BEFORE the mutation.
Migration 024 RLS policy on server_admins rewritten to delegate the
admin branch to is_server_admin() (SECURITY DEFINER, bypasses RLS) —
the original inline EXISTS hit infinite recursion (caught empirically
with `SET ROLE authenticated`).
22 new pgTAP assertions cover schema shape, RPC signatures, the
SECURITY DEFINER posture of every admin function, the new deleted_at
column, and the server-layer precedence semantics for section_enabled.
10 new Vitest integration tests (SA-01..SA-10) cover:
- admin_list_collectives gated for non-admins (P0001 'forbidden').
- soft / restore / hard delete audit + state transitions.
- hard-delete recency guard (not_soft_deleted, too_recent, force).
- hard-delete cascade is non-destructive to public.users.
- remove_member writes role_was + reason to audit payload.
- set_default_section rejects unknown sections; patches JSONB
without clobbering siblings.
- grant/revoke with last-admin guard (revoking the sole admin
raises P0003 'last_admin'; failed call does NOT write audit).
- section_enabled precedence walked layer by layer in one client.
- RLS: non-admin sees zero rows in admin_actions + server_admins.
packages/types/src/database.ts hand-extended with the new tables and
RPC signatures; collectives Row/Insert/Update get deleted_at.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Migration 024 introduces the orthogonal global role and the append-only
audit log. server_admins is a separate table (not a column on users) so
that promote/revoke don't require a JWT re-issue; is_server_admin() is a
STABLE SECURITY DEFINER helper matching the shape of is_admin/is_member.
admin_actions has only a SELECT policy (admin-only) — no INSERT/UPDATE/
DELETE policies, so only the SECURITY DEFINER RPCs in migration 025 can
write to it. actor_id FK uses RESTRICT so the audit trail outlives
admins.
Bootstrap via infra/db-init/10-server-admin-seed.sh on first volume
init (reads SERVER_ADMIN_EMAIL); supabase/seed.sql also pre-seeds Ana
for dev/test because docker-entrypoint-initdb.d does not re-fire on
long-lived dev volumes. Documented in .env.erosi.example.
20 new pgTAP assertions cover schema shape, RLS posture, FK behaviour,
and the SECURITY DEFINER + STABLE attributes on is_server_admin().
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
