Як зберігати історію змін записів для аудиту?

Задача: знати, хто, коли й що змінив - для розслідувань, вимог регуляторів, відновлення помилково змінених даних.

1. Таблиця аудиту з тригером у базі:

CREATE TABLE audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text NOT NULL,
    record_id bigint NOT NULL,
    action text NOT NULL,                    -- INSERT / UPDATE / DELETE
    old_data jsonb,
    new_data jsonb,
    changed_by text DEFAULT current_setting('app.user_id', true),
    changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION audit_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, coalesce(NEW.id, OLD.id), TG_OP,
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;
END $$;

CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

• Плюс: ловить усі зміни - з застосунку, міграцій, ручних SQL-запитів.
• Мінус: база не знає користувача застосунку - його передають змінною сесії (SET LOCAL app.user_id), що потребує дисципліни.

2. Аудит у застосунку - події моделей (у Laravel: spatie/laravel-activitylog, owen-it/laravel-auditing):

• Плюс: природно знає користувача, запит, IP, контекст бізнес-дії («скасування замовлення», а не просто «змінено status»).
• Мінус: пропускає масові оновлення в обхід моделей (Model::where(...)->update()) і зміни поза застосунком.

3. Історичні таблиці / темпоральні дані - кожна версія запису з періодом дії (valid_from, valid_to). Дає відповідь на «яким був запис на 1 березня» простим запитом.

Що врахувати:

• Обсяг: таблиця аудиту росте швидше за основні. Партиціонування за часом і політика зберігання.
• Чутливі дані: у знімках змін опиняться паролі, токени, персональні дані - їх треба виключати чи маскувати.
• Незмінність: права лише на INSERT в таблицю аудиту, без UPDATE/DELETE для ролі застосунку.
• Зберігати лише змінені поля (diff), якщо повні знімки занадто великі.

Докладніше в документації: Тригери в PL/pgSQL