Simulator - Add transactions audit table

[cristiam86]

New table called "rollup"/"audit" with all the transaction states.

[sevenearths]

@cristiam86 any more information you can give me on this ticket?

[sevenearths]

Schema:

• transaction_id
• status
• JSON of the transactions row
• timestamp

[sevenearths]

Use the new database files for all of this => https://github.com/yeagerai/genlayer-simulator/blob/main/database/functions.py

[sevenearths]

Inserts will happen to the audit table when:

• call_contract_function
• send_transaction
• deploy_intelligent_contract

[sevenearths]

After looking into the following two options:

• pgAudit
• transactions_audit table I have decided to go for the transactions_audit table for the following options:
  1. pgAudit is a blanket logging approach
  2. pgAudit only writes to file
  3. pgAudit can only filter (in the postgresql.conf file, triggers are a different matter) by command (pgaudit.log = 'write')
  4. pgAudit triggers are an extra setup layer (*)
  5. A transactions_audit table offers a more fine grained approach
  6. An endpoint can be built with it if required
  7. The audit log can be returned with the transaction data if required
  8. A transaction audit table requires less setup
  9. The recording of a transaction can also be recorded at the same time as the INSERT/UPDATE and hidden in the database functionality so a separate call does not need to be issued
  10. A more fine grained approach can be taken to auditing by building a transaction_audit table

(*)

-- Create a function to log changes
CREATE OR REPLACE FUNCTION log_transaction_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM pgaudit.audit_function('WRITE');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger on the transactions table
CREATE TRIGGER audit_transaction_changes
AFTER INSERT OR UPDATE ON transactions
FOR EACH ROW EXECUTE FUNCTION log_transaction_changes();