Files

57 lines
2.1 KiB
PL/PgSQL

-- sys_audit is the field-level audit trail: one row per changed field of one
-- record, whatever the table. Settings resources are the first writer.
-- +goose Up
CREATE TABLE sys_audit (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
at timestamptz NOT NULL,
table_name text NOT NULL,
record_key text NOT NULL,
field text NOT NULL,
old_value jsonb,
new_value jsonb,
record_version bigint NOT NULL,
actor text NOT NULL,
source text NOT NULL,
request_id text NOT NULL
);
CREATE INDEX idx_sys_audit_record ON sys_audit (table_name, record_key, id DESC);
-- audit_json_diff lists the top-level fields that differ between two JSON
-- documents. A document that is not an object is audited whole, as field ''.
-- +goose StatementBegin
CREATE FUNCTION audit_json_diff(prev jsonb, doc jsonb)
RETURNS TABLE (field text, old_value jsonb, new_value jsonb)
LANGUAGE sql IMMUTABLE AS $$
SELECT k, prev -> k, doc -> k
FROM (
SELECT jsonb_object_keys(doc) AS k WHERE jsonb_typeof(doc) = 'object'
UNION
SELECT jsonb_object_keys(prev) WHERE jsonb_typeof(prev) = 'object'
) keys
WHERE jsonb_typeof(COALESCE(doc, '{}')) = 'object'
AND jsonb_typeof(COALESCE(prev, '{}')) = 'object'
AND (prev -> k) IS DISTINCT FROM (doc -> k)
UNION ALL
SELECT '', prev, doc
WHERE NOT (jsonb_typeof(COALESCE(doc, '{}')) = 'object'
AND jsonb_typeof(COALESCE(prev, '{}')) = 'object')
AND prev IS DISTINCT FROM doc
$$;
-- +goose StatementEnd
INSERT INTO sys_audit (at, table_name, record_key, field, old_value, new_value, record_version, actor, source, request_id)
SELECT h.at, 'resources', h.kind, d.field, d.old_value, d.new_value, h.version, h.actor, h.source, h.request_id
FROM (
SELECT kind, version, actor, source, request_id, at,
convert_from(value, 'UTF8')::jsonb AS doc,
lag(convert_from(value, 'UTF8')::jsonb) OVER (PARTITION BY kind ORDER BY version) AS prev
FROM resource_history
) h
CROSS JOIN LATERAL audit_json_diff(h.prev, h.doc) d
ORDER BY h.kind, h.version, d.field;
-- +goose Down
DROP TABLE sys_audit;
DROP FUNCTION audit_json_diff(jsonb, jsonb);