Script to create initial audit-history log for invoices and organizations

This script inserts missing audit log records for organizations, invoices, and
invoice lines into the audit tables.

PURPOSE

The script creates audit log entries for existing acquisition records
(organizations, invoices, and invoice lines) that don't have corresponding
audit logs. This is useful for:

Populating audit logs after enabling the audit feature
Recovering missing audit records
Ensuring compliance and audit trail completeness

USAGE

Prerequisites

PostgreSQL database access with appropriate permissions
Connection to the database where the following schemas exist:
- <tenant_name>_mod_organizations_storage
- <tenant_name>_mod_invoice_storage
- <tenant_name>_mod_audit
Records in organizations, invoices, and invoice lines tables must have
metadata.updatedByUserId and metadata.updatedDate fields.

Running the Script

Option 1: Using psql (Command Line)

Connect to your PostgreSQL database:
psql -h <host> -U <username> -d <database>
Set the schema paths (replace <tenant_name> with your tenant, e.g., diku):
SET search_path TO <tenant_name>_mod_audit, <tenant_name>_mod_organizations_storage, <tenant_name>_mod_invoice_storage;
Replace <tenant_name> with your tenant in the insert_audit_logs.sql
Execute the script:
\i insert_audit_logs.sql
Or run it directly:
psql -h <host> -U <username> -d <database> -f insert_audit_logs.sql

Option 2: Using a DB Editor (DBeaver, pgAdmin, DataGrip, etc.)

Connect to the database (default port: 5432).
Determine your schema names:
- Audit schema: <tenant_name>_mod_audit (e.g., diku_mod_audit)
- Organizations schema: <tenant_name>_mod_organizations_storage
  (e.g., diku_mod_organizations_storage)
- Invoice schema: <tenant_name>_mod_invoice_storage
  (e.g., diku_mod_invoice_storage)
Open a new SQL editor and set the search path:
SET search_path TO <tenant_name>_mod_audit, <tenant_name>_mod_organizations_storage, <tenant_name>_mod_invoice_storage;
Open and execute the insert_audit_logs.sql file (or copy/paste its
contents).
Review the output notices showing the number of rows inserted.

What the Script Does

The script performs the following operations:

Organizations: Inserts CREATE action audit logs for all organizations that:
- Have metadata.updatedByUserId and metadata.updatedDate fields.
- Don't already have an audit log entry.
Invoice Lines: Inserts CREATE action audit logs for all invoice lines that:
- Have metadata.updatedByUserId and metadata.updatedDate fields.
- Don't already have an audit log entry.
Invoices: Inserts CREATE action audit logs for all invoices that:
- Have metadata.updatedByUserId and metadata.updatedDate fields.
- Don't already have an audit log entry.
Reports Progress: Displays the following information:
- Start time.
- Number of organization audit logs inserted.
- Number of invoice audit logs inserted.
- Number of invoice line audit logs inserted.
- End time and elapsed time.

Example Output

  NOTICE:  Starting inserting organization, invoice and invoice line audit logs at 2024-11-15 10:30:45.123456
  NOTICE:  Completed inserting 150 organization, 2340 invoice and 5678 invoice line audit logs
  NOTICE:  Finished at 2024-11-15 10:31:12.654321. Elapsed time: 00:00:27.530865

NOTES

The script generates new UUIDs for audit log entries using gen_random_uuid().
The action field is always set to 'CREATE' for these backfilled audit logs.
The event_date is set to the current timestamp (now()).
The action_date is taken from the original record's metadata.updatedDate.
The modified_content_snapshot excludes the metadata field from the
original record.

do
$$
declare
  v_start_time timestamp;
  v_end_time timestamp;
  v_elapsed_time interval;
  v_rows_affected_invoices integer;
  v_rows_affected_invoice_lines integer;
  v_rows_affected_organizations integer;
begin
  v_start_time := clock_timestamp();
  raise notice 'Starting inserting organization, invoice and invoice line audit logs at %', v_start_time;

  -- Organizations
  insert into <tenant_name>_mod_audit.acquisition_organization_log (id, "action", organization_id, user_id, event_date, action_date, modified_content_snapshot)
    select gen_random_uuid() id,
           'CREATE' action,
           t.id organization_id,
           (t.jsonb->'metadata'->>'updatedByUserId')::uuid user_id,
           now() event_date,
           (t.jsonb->'metadata'->>'updatedDate')::timestamp action_date,
           t.jsonb - 'metadata' modified_content_snapshot
      from <tenant_name>_mod_organizations_storage.organizations t
      where (t.jsonb->'metadata') ? 'updatedByUserId'
        and (t.jsonb->'metadata') ? 'updatedDate'
        and (t.jsonb->'metadata'->>'updatedByUserId') is not null
        and (t.jsonb->'metadata'->>'updatedDate') is not null
        and not exists(
          select 1
            from <tenant_name>_mod_audit.acquisition_organization_log c
            where c.organization_id = t.id
        );

  get diagnostics v_rows_affected_organizations = row_count;

  -- Invoice Lines
  insert into <tenant_name>_mod_audit.acquisition_invoice_line_log (id, "action", invoice_id, invoice_line_id, user_id, event_date, action_date, modified_content_snapshot)
    select gen_random_uuid() id,
           'CREATE' action,
           t.invoiceid invoice_id,
           t.id invoice_line_id,
           (t.jsonb->'metadata'->>'updatedByUserId')::uuid user_id,
           now() event_date,
           (t.jsonb->'metadata'->>'updatedDate')::timestamp action_date,
           t.jsonb - 'metadata' modified_content_snapshot
      from <tenant_name>_mod_invoice_storage.invoice_lines t
      where (t.jsonb->'metadata') ? 'updatedByUserId'
        and (t.jsonb->'metadata') ? 'updatedDate'
        and (t.jsonb->'metadata'->>'updatedByUserId') is not null
        and (t.jsonb->'metadata'->>'updatedDate') is not null
        and not exists(
          select 1
            from <tenant_name>_mod_audit.acquisition_invoice_line_log c
            where c.invoice_line_id = t.id
        );

  get diagnostics v_rows_affected_invoice_lines = row_count;

  -- Invoices
  insert into <tenant_name>_mod_audit.acquisition_invoice_log (id, "action", invoice_id, user_id, event_date, action_date, modified_content_snapshot)
    select gen_random_uuid() id,
           'CREATE' action,
           t.id invoice_id,
           (t.jsonb->'metadata'->>'updatedByUserId')::uuid user_id,
           now() event_date,
           (t.jsonb->'metadata'->>'updatedDate')::timestamp action_date,
           t.jsonb - 'metadata' modified_content_snapshot
      from <tenant_name>_mod_invoice_storage.invoices t
      where (t.jsonb->'metadata') ? 'updatedByUserId'
        and (t.jsonb->'metadata') ? 'updatedDate'
        and (t.jsonb->'metadata'->>'updatedByUserId') is not null
        and (t.jsonb->'metadata'->>'updatedDate') is not null
        and not exists(
          select 1
            from <tenant_name>_mod_audit.acquisition_invoice_log c
            where c.invoice_id = t.id
        );

  get diagnostics v_rows_affected_invoices = row_count;

  v_end_time := clock_timestamp();
  v_elapsed_time := v_end_time - v_start_time;

  raise notice 'Completed inserting % organization, % invoice and % invoice line audit logs',
    v_rows_affected_organizations, v_rows_affected_invoices, v_rows_affected_invoice_lines;
  raise notice 'Finished at %. Elapsed time: %', v_end_time, v_elapsed_time;
end;
$$;