-- Foundation: schemas, shared triggers, append-only audit log. -- Conventions: docs/development/BACKEND_ARCHITECTURE.md §3, §7; business architecture §29. -- Forward-only. Never edit after it has been applied: write a new migration. -- --------------------------------------------------------------------------- -- Schemas -- --------------------------------------------------------------------------- -- private: security-definer helpers and internal functions. Not exposed to -- PostgREST (config.toml api.schemas lists public only). create schema if not exists private; -- audit: append-only event log, read by platform admins only. create schema if not exists audit; revoke all on schema private from public; revoke all on schema audit from public; -- Policies evaluated for API roles call private.* helpers by qualified name, -- which needs USAGE on the schema. EXECUTE is granted per function below. -- anon gets nothing: no policy for anon evaluates a helper yet. grant usage on schema private to authenticated, service_role; grant usage on schema audit to authenticated, service_role; -- PostgreSQL grants EXECUTE on every new function to PUBLIC. Flip the default -- for both schemas so a helper is callable only where a migration says so. alter default privileges in schema private revoke execute on functions from public; alter default privileges in schema audit revoke execute on functions from public; -- --------------------------------------------------------------------------- -- Shared trigger: updated_at -- --------------------------------------------------------------------------- create or replace function private.set_updated_at() returns trigger language plpgsql set search_path = '' as $$ begin new.updated_at := now(); return new; end; $$; comment on function private.set_updated_at() is 'BEFORE UPDATE trigger: stamps updated_at. Attach to every table that has the column.'; -- --------------------------------------------------------------------------- -- Audit log (business architecture §29) -- --------------------------------------------------------------------------- create table audit.audit_logs ( id uuid primary key default gen_random_uuid(), occurred_at timestamptz not null default now(), -- null actor = system action (migration, worker, bootstrap). actor_user_id uuid, -- "." e.g. platform_role.granted. Kept as text + CHECK: the -- set grows with every step. action text not null constraint audit_logs_action_format check (action ~ '^[a-z_]+\.[a-z_]+$'), resource_type text not null constraint audit_logs_resource_type_format check (resource_type ~ '^[a-z_]+$'), resource_id uuid, organization_id uuid, target_user_id uuid, -- Sanitized by the caller: never report contents, passwords, tokens. metadata jsonb not null default '{}'::jsonb constraint audit_logs_metadata_object check (jsonb_typeof(metadata) = 'object'), correlation_id text ); comment on table audit.audit_logs is 'Append-only trace of sensitive operations. Insert only through private.log_event(). No FK on user ids: rows outlive accounts.'; create index audit_logs_occurred_at_idx on audit.audit_logs (occurred_at desc); create index audit_logs_actor_idx on audit.audit_logs (actor_user_id, occurred_at desc); create index audit_logs_target_idx on audit.audit_logs (target_user_id, occurred_at desc); create index audit_logs_resource_idx on audit.audit_logs (resource_type, resource_id); -- Append-only, enforced for every role including the table owner. create or replace function audit.reject_mutation() returns trigger language plpgsql set search_path = '' as $$ begin raise exception 'audit.audit_logs is append-only (% refused)', tg_op using errcode = '42501'; end; $$; create trigger audit_logs_append_only before update or delete on audit.audit_logs for each row execute function audit.reject_mutation(); -- Privileges: nobody writes through the API; admins read (policy added with -- the role helpers in the platform-roles migration). revoke all on audit.audit_logs from public, anon, authenticated, service_role; grant select on audit.audit_logs to authenticated, service_role; alter table audit.audit_logs enable row level security; create or replace function private.log_event( p_actor_user_id uuid, p_action text, p_resource_type text, p_resource_id uuid default null, p_target_user_id uuid default null, p_organization_id uuid default null, p_metadata jsonb default '{}'::jsonb ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_id uuid; v_correlation text; begin -- PostgREST exposes request headers as a JSON setting; absent elsewhere. v_correlation := nullif(current_setting('request.headers', true), '')::jsonb ->> 'x-correlation-id'; insert into audit.audit_logs ( actor_user_id, action, resource_type, resource_id, target_user_id, organization_id, metadata, correlation_id ) values ( p_actor_user_id, p_action, p_resource_type, p_resource_id, p_target_user_id, p_organization_id, coalesce(p_metadata, '{}'::jsonb), v_correlation ) returning id into v_id; return v_id; end; $$; comment on function private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb) is 'Single write path into audit.audit_logs. Called from server-side functions only; never granted to API roles.'; revoke all on function private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb) from public; grant execute on function private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb) to service_role;