Single Record — Insert, View, Update

Insert one record

INSERT INTO records (data) VALUES ('{"name": "Widget A", "qty": 10}');

The insert trigger fires and puts two events into the pg_relay queue: one json_record event and one audit_action event (action NEW_RECORD). Within about one second the Processor runs them. When the JSON snapshot is inserted, its own trigger fires and queues a third event (NEW_JSON_RECORD).

Wait a couple of seconds, then check the results:

-- The JSON snapshot of the record:
SELECT record_id, data, updated_at FROM records_json;

 record_id |                       data                        |         updated_at
-----------+---------------------------------------------------+----------------------------
         1 | {"id": 1, "data": {"name": "Widget A", "qty": 10},| 2026-04-22 12:00:01.234+00
           |  "created_by": "app_user", "created_at": "..."}   |

-- The audit trail so far:
SELECT record_id, audit_action, actioned_by, actioned_at FROM records_audit ORDER BY id;

 record_id | audit_action   | actioned_by |         actioned_at
-----------+----------------+-------------+----------------------------
         1 | NEW_RECORD     | app_user    | 2026-04-22 12:00:01.235+00
         1 | NEW_JSON_RECORD| app_user    | 2026-04-22 12:00:01.890+00

Two audit entries came from one insert — NEW_RECORD from the records trigger, and NEW_JSON_RECORD from the records_json trigger. The whole chain runs entirely through pg_relay: the two triggers never call each other directly.

Update the record

UPDATE records SET data = '{"name": "Widget A", "qty": 25}' WHERE id = 1;

The update trigger queues another json_record and audit_action (RECORD_UPDATED). The Processor runs them; the records_json UPSERT fires its own update trigger and queues UPDATED_JSON_RECORD.

Wait 1 second, then check:

-- Snapshot is updated in place (same row, new data):
SELECT record_id, data->>'data' AS record_data, updated_at FROM records_json;

-- Audit trail now has four entries:
SELECT record_id, audit_action, actioned_by, actioned_at FROM records_audit ORDER BY id;

 record_id | audit_action        | actioned_by |         actioned_at
-----------+---------------------+-------------+----------------------------
         1 | NEW_RECORD          | app_user    | 2026-04-22 12:00:01.235+00
         1 | NEW_JSON_RECORD     | app_user    | 2026-04-22 12:00:01.890+00
         1 | RECORD_UPDATED      | app_user    | 2026-04-22 12:00:03.112+00
         1 | UPDATED_JSON_RECORD | app_user    | 2026-04-22 12:00:03.456+00

---

Continue to Bulk Insert and Update to see the same pattern at a larger scale.