finance-app/prisma/migrations/0037_transaction_audit_log/migration.sql

-- Append-only change log for the ledger and its curation children.
--
-- WHY THIS EXISTS: on 2026-08-23 the CC Statement Processor's `Run Reconciliation`
-- node stamped `statement_id` onto seven statementless wallet/pantry rows without
-- setting `reconciled_with_id` or superseding the statement's own extracted line.
-- The purchase counted twice, and because finance-app's reconcile queue is
-- `WHERE statement_id IS NULL`, the stamp also hid those rows from the only thing
-- that could repair them. Answering "was anything lost?" required restoring the
-- 02:30 pg_dump into a scratch database and diffing it column by column, because
-- NOTHING in this estate records what changed. That is the gap this closes.
--
-- WHY AT THE DATABASE AND NOT IN THE APP: the ledger has at least five independent
-- writers -- finance-app (Prisma), n8n (raw SQL in a Postgres node), pantry-app,
-- the ingestion-engine runner (psycopg), and ad-hoc `docker exec psql`. The 4a bug
-- was an n8n raw-SQL UPDATE; an application-level audit would not have seen it at
-- all. A trigger cannot be bypassed by choice of client.
--
-- WHY NOT POSTGRES' OWN LOGS: checked live on this instance -- wal_level=replica
-- (so WAL cannot be logically decoded into row changes), wal_keep_size=0 and
-- archive_mode=off (so WAL is recycled almost immediately), log_statement=none,
-- logging_collector=off, track_commit_timestamp=off, and pgaudit is not installed.
-- Postgres was recording nothing usable. It has no built-in audit or temporal
-- tables; this always has to be built.

CREATE TABLE IF NOT EXISTS transaction_audit (
  id             bigserial   PRIMARY KEY,
  changed_at     timestamptz NOT NULL DEFAULT now(),
  -- Groups every row touched by one logical edit. `applyReconcileMatch` moves
  -- overrides, tags, splits and metadata in a single transaction; without this
  -- they read as four unrelated events.
  txid           bigint      NOT NULL DEFAULT txid_current(),
  table_name     text        NOT NULL,
  op             text        NOT NULL CHECK (op IN ('INSERT','UPDATE','DELETE')),
  -- Full primary key as an object, because transaction_tags is composite
  -- (transaction_id, tag_id) and the others are a bare `id`.
  row_key        jsonb       NOT NULL,
  -- Denormalised so "everything that ever happened to transaction N" is one
  -- index scan rather than a five-way union.
  transaction_id bigint,
  -- On UPDATE these hold ONLY the columns that actually changed. Storing whole
  -- rows would bury a one-column edit in forty unchanged fields, and
  -- expense_metadata.line_items alone can be kilobytes.
  old_data       jsonb,
  new_data       jsonb,
  -- Attribution is partial and that is understood: every client connects as the
  -- same `personal` role. app_name is only as good as what each client sets;
  -- client_addr distinguishes containers and is NULL for `docker exec` over the
  -- unix socket, which is itself a useful signal.
  db_user        text        NOT NULL DEFAULT current_user,
  app_name       text,
  client_addr    inet
);

COMMENT ON TABLE transaction_audit IS
  'Append-only row-level change log for transactions + curation children. Never UPDATE or DELETE from this table.';

CREATE INDEX IF NOT EXISTS transaction_audit_txn_time
  ON transaction_audit (transaction_id, changed_at DESC);
CREATE INDEX IF NOT EXISTS transaction_audit_time
  ON transaction_audit (changed_at DESC);
CREATE INDEX IF NOT EXISTS transaction_audit_txid
  ON transaction_audit (txid);
CREATE INDEX IF NOT EXISTS transaction_audit_table_time
  ON transaction_audit (table_name, changed_at DESC);

-- TG_ARGV[0]: comma-separated candidate columns for the owning transaction id;
--             the first non-null wins (expense_metadata carries the id in
--             transaction_id OR matched_transaction_id -- a card-settled order
--             only ever has the latter).
-- TG_ARGV[1]: comma-separated primary key columns.
CREATE OR REPLACE FUNCTION log_transaction_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public, pg_temp
AS $fn$
DECLARE
  v_full jsonb;
  v_old  jsonb;
  v_new  jsonb;
  v_key  jsonb := '{}'::jsonb;
  v_txn  bigint;
  k      text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_new  := to_jsonb(NEW);
    v_full := v_new;
  ELSIF TG_OP = 'DELETE' THEN
    v_old  := to_jsonb(OLD);
    v_full := v_old;
  ELSE
    v_old  := to_jsonb(OLD);
    v_new  := to_jsonb(NEW);
    v_full := v_new;
    -- Reduce to the changed columns only. IS DISTINCT FROM so a NULL->NULL
    -- comparison counts as unchanged rather than evaluating to NULL.
    FOR k IN SELECT jsonb_object_keys(v_old) LOOP
      IF (v_old -> k) IS NOT DISTINCT FROM (v_new -> k) THEN
        v_old := v_old - k;
        v_new := v_new - k;
      END IF;
    END LOOP;
    -- A no-op UPDATE is noise, and finance-app's upserts issue plenty of them.
    IF v_old = '{}'::jsonb THEN
      RETURN NULL;
    END IF;
  END IF;

  FOREACH k IN ARRAY string_to_array(TG_ARGV[1], ',') LOOP
    v_key := v_key || jsonb_build_object(k, v_full -> k);
  END LOOP;

  FOREACH k IN ARRAY string_to_array(TG_ARGV[0], ',') LOOP
    IF v_txn IS NULL AND (v_full ->> k) IS NOT NULL THEN
      v_txn := (v_full ->> k)::bigint;
    END IF;
  END LOOP;

  INSERT INTO transaction_audit
    (table_name, op, row_key, transaction_id, old_data, new_data, app_name, client_addr)
  VALUES
    (TG_TABLE_NAME, TG_OP, v_key, v_txn, v_old, v_new,
     NULLIF(current_setting('application_name', true), ''), inet_client_addr());

  RETURN NULL;  -- AFTER trigger; return value is ignored
END
$fn$;

-- AFTER, so a statement that raises never leaves a phantom audit row; the audit
-- write is in the same transaction as the change and rolls back with it.
DROP TRIGGER IF EXISTS trg_audit_transactions ON transactions;
CREATE TRIGGER trg_audit_transactions
  AFTER INSERT OR UPDATE OR DELETE ON transactions
  FOR EACH ROW EXECUTE FUNCTION log_transaction_change('id', 'id');

DROP TRIGGER IF EXISTS trg_audit_transaction_splits ON transaction_splits;
CREATE TRIGGER trg_audit_transaction_splits
  AFTER INSERT OR UPDATE OR DELETE ON transaction_splits
  FOR EACH ROW EXECUTE FUNCTION log_transaction_change('transaction_id', 'id');

DROP TRIGGER IF EXISTS trg_audit_transaction_tags ON transaction_tags;
CREATE TRIGGER trg_audit_transaction_tags
  AFTER INSERT OR UPDATE OR DELETE ON transaction_tags
  FOR EACH ROW EXECUTE FUNCTION log_transaction_change('transaction_id', 'transaction_id,tag_id');

DROP TRIGGER IF EXISTS trg_audit_transaction_overrides ON transaction_overrides;
CREATE TRIGGER trg_audit_transaction_overrides
  AFTER INSERT OR UPDATE OR DELETE ON transaction_overrides
  FOR EACH ROW EXECUTE FUNCTION log_transaction_change('transaction_id', 'id');

DROP TRIGGER IF EXISTS trg_audit_expense_metadata ON expense_metadata;
CREATE TRIGGER trg_audit_expense_metadata
  AFTER INSERT OR UPDATE OR DELETE ON expense_metadata
  FOR EACH ROW EXECUTE FUNCTION log_transaction_change('transaction_id,matched_transaction_id', 'id');