36 lines
1.2 KiB
PL/PgSQL
36 lines
1.2 KiB
PL/PgSQL
CREATE TABLE public.audit_log (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE RESTRICT,
|
|
profile_id uuid REFERENCES public.profiles(id) ON DELETE SET NULL,
|
|
action text NOT NULL,
|
|
target_type text NOT NULL,
|
|
target_id uuid,
|
|
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT audit_log_action_not_blank_check CHECK (btrim(action) <> ''),
|
|
CONSTRAINT audit_log_target_type_not_blank_check CHECK (btrim(target_type) <> '')
|
|
);
|
|
|
|
CREATE INDEX audit_log_tenant_id_created_at_idx ON public.audit_log (tenant_id, created_at DESC);
|
|
CREATE INDEX audit_log_profile_id_idx ON public.audit_log (profile_id);
|
|
CREATE INDEX audit_log_target_idx ON public.audit_log (target_type, target_id);
|
|
|
|
CREATE OR REPLACE FUNCTION public.prevent_audit_log_mutation()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
RAISE EXCEPTION 'audit_log is append-only';
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER audit_log_no_update
|
|
BEFORE UPDATE ON public.audit_log
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.prevent_audit_log_mutation();
|
|
|
|
CREATE TRIGGER audit_log_no_delete
|
|
BEFORE DELETE ON public.audit_log
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.prevent_audit_log_mutation();
|