Script to update POL metadata from the latest POL audit logs

This script corrects the drift between a purchase order line's
metadata.updatedDate and the most recent event_date recorded in the audit log
for that line.

PURPOSE

When the audit module records an EDIT event for a purchase order line, it
captures an event_date that can be ahead of what
po_line.jsonb->metadata->updatedDate stored at that moment. Over time this
drift causes the "last updated" timestamp shown in the UI to be stale compared
to the actual audit history.

The script updates metadata.updatedDate in po_line.jsonb to match
the latest event_date from acquisition_order_line_log — but only for lines
where the values actually differ, making the operation idempotent and safe to
re-run.

USAGE

Prerequisites

PostgreSQL database access with appropriate permissions.
Connection to the database where the following schemas exist:
- <tenant_name>_mod_orders_storage
- <tenant_name>_mod_audit
Purchase order lines must have at least one entry in
acquisition_order_line_log.

Running the Script

Option 1: Using psql (Command Line)

Connect to your PostgreSQL database:
psql -h <host> -U <username> -d <database>
Execute the script directly (the script references diku_ schemas;
substitute your tenant prefix in the SQL if needed):
psql -h <host> -U <username> -d <database> -f update_poline_metadata.sql
Or interactively:
\i update_poline_metadata.sql

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

Connect to the database (default port: 5432).
Confirm your schema names:
- Orders schema: <tenant_name>_mod_orders_storage
  (e.g., diku_mod_orders_storage)
- Audit schema: <tenant_name>_mod_audit
  (e.g., diku_mod_audit)
Open update_poline_metadata.sql in a new SQL editor (or copy/paste its
contents).
Execute and review the output notices showing the number of rows updated.

What the Script Does

Identifies drifted rows: Joins po_line against a subquery that returns
the max(event_date) per order_line_id from acquisition_order_line_log.
Only rows where the cast of metadata.updatedDate does not equal
max(event_date) are selected.
Applies a jsonb update: Uses jsonb_set to overwrite only the
{metadata,updatedDate} path, leaving the rest of the document untouched.
The timestamp is formatted with microsecond precision:
YYYY-MM-DD"T"HH24:MI:SS.US"Z".
Reports progress: Displays start time, the number of rows updated, and
elapsed time.

Example Output

NOTICE: Starting updating POL metadata objects, at 2026-03-18 14:49:18.151556
NOTICE: Completed updating 1000000 POL metadata objects
NOTICE: Finished at 2026-03-18 15:09:20.903166. Elapsed time: 00:20:02.75161

SAFETY NOTES

NO DOWNTIME: The script can be run on a live production system without
causing downtime.
IDEMPOTENT: Only rows with a mismatch between metadata.updatedDate and the
latest audit event_date are updated. Running the script a second time will
update 0 rows.
UPDATE: jsonb_set modifies only the metadata.updatedDate key; no
other fields in the document are touched.
RESIDUAL MICROSECOND DRIFT IS EXPECTED: The update uses HH24:MI:SS.US to
carry microseconds through the format string, but PostgreSQL's native jsonb
utility methods (to_jsonb / to_char) perform internal microsecond truncation
within the timestamp data type. The audit log event_date was originally
transferred by JDBC in binary form at full sub-millisecond precision, so a
small gap of up to ~500-950 µs between the written metadata.updatedDate and the
source event_date is expected and harmless. Example:
Source audit log event_date (epoch): 1768706651291063
Written POL updatedDate (epoch): 1768706651291000
Residual drift : 63 µs
AUDIT LOG REQUIRED: Only po lines that have at least one row in
acquisition_order_line_log are candidates. Lines with no audit history are
left untouched.
It is recommended to back up your database before running the script.
Test on a non-production environment first to verify the expected behaviour.

NOTES

The script uses max(event_date) per order_line_id as the authoritative
"last updated" timestamp, consistent with how mod-audit records the most
recent edit event.
The Z suffix in the formatted timestamp is a hardcoded literal, not a
timezone conversion — consistent with FOLIO ISO 8601 jsonb conventions
throughout the platform.
The comparison date_trunc('milliseconds', (t.jsonb->'metadata'->>'updatedDate')::timestamp) != date_trunc('milliseconds', c.event_date);
strips microseconds and casts both sides to PostgreSQL timestamp (no timezone) for the equality
check.
Because both sides of the comparison are cast to timestamp before the !=
test, the sub-millisecond portion is discarded symmetrically on both sides.
This means the comparison correctly identifies rows that need updating
without being tripped up by the residual µs drift introduced by jsonb
serialisation.
check.

do
$$
declare
  v_start_time timestamp;
  v_end_time timestamp;
  v_elapsed_time interval;
  v_rows_affected_rows integer;
begin
  v_start_time := clock_timestamp();
  raise notice 'Starting updating POL metadata objects, at %', v_start_time;

  update <tenant_name>_mod_orders_storage.po_line t
    set jsonb = jsonb_set(
          t.jsonb,
          '{metadata,updatedDate}',
          to_jsonb(to_char(c.event_date, 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')
        )
    )
    from (
      select order_line_id, max(event_date) event_date
        from <tenant_name>_mod_audit.acquisition_order_line_log
        group by order_line_id
    ) c
    where t.id = c.order_line_id
      and date_trunc('milliseconds', (t.jsonb->'metadata'->>'updatedDate')::timestamp) != date_trunc('milliseconds', c.event_date);

  get diagnostics v_rows_affected_rows = row_count;

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

  raise notice 'Completed updating % POL metadata objects', v_rows_affected_rows;
  raise notice 'Finished at %. Elapsed time: %', v_end_time, v_elapsed_time;
end;
$$;