Functions and examples on how to easily query logs table [EventSourcing helpers]

Open Forevka opened this issue 10 months ago • 0 comments

What kind of change does this PR introduce?

Feature that adds ability to query over logs with comparing old-to-new records. Please see Readme.md that I included into PR.

What is the current behavior?

Users that needs to query logs should compare by hands all records in order to restore changes that ve been made to particular enity.

What is the new behavior?

So, with this functions user may easily navigate and restore all history in "this changed 'when' to 'value' from 'value'" manner, please see Readme.md

Examples:

Users table DDL:

CREATE TABLE "Users" (
  "id" SERIAL PRIMARY KEY,
  "tenant_id" int NOT NULL,
  "email" varchar(255) NOT NULL,
  "password_hash" varchar(255) NOT NULL,
  "role_id" int,
  "is_email_confirmed" boolean NOT NULL DEFAULT false,
  "created_datetime" TIMESTAMP DEFAULT (now() at time zone 'utc'),
  "modified_datetime" TIMESTAMP DEFAULT (now() at time zone 'utc'),
  "is_blocked" boolean NOT NULL DEFAULT false,
  "first_name" varchar(255) NOT NULL,
  "last_name" varchar(255) NOT NULL,
  "is_password_set" boolean DEFAULT false
);

I've ommited data insertion/updates for brevity

Retrieve Full History (Pivoted JSON):

SELECT * FROM audit.get_record_history(17318::oid, '{"id": 6}'::jsonb);

The result:

{"id": 1, "op": "UPDATE", "ts": "2024-10-04T10:27:09.254176+00:00", "new_id": "6", "old_id": "6", "new_email": "[email protected]", "old_email": "[email protected]", "record_id": "6abba0cb-a814-5ccd-ad88-86cae70986bd", "table_name": "Users", "new_role_id": "1", "old_role_id": "1", "table_schema": "public", "new_last_name": "l", "new_tenant_id": "1", "old_last_name": "l", "old_tenant_id": "1", "new_first_name": "bohdan3", "new_is_blocked": "false", "old_first_name": "bohdan2", "old_is_blocked": "false", "new_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "new_is_password_set": "false", "old_is_password_set": "false", "new_created_datetime": "2024-10-02T15:03:55.154947", "old_created_datetime": "2024-10-02T15:03:55.154947", "new_modified_datetime": null, "old_modified_datetime": null, "new_is_email_confirmed": "true", "old_is_email_confirmed": "true"}
{"id": 43, "op": "UPDATE", "ts": "2024-10-08T07:40:02.088688+00:00", "new_id": "6", "old_id": "6", "new_email": "[email protected]", "old_email": "[email protected]", "record_id": "6abba0cb-a814-5ccd-ad88-86cae70986bd", "table_name": "Users", "new_role_id": "1", "old_role_id": "1", "table_schema": "public", "new_last_name": "l", "new_tenant_id": "1", "old_last_name": "l", "old_tenant_id": "1", "new_first_name": "bohdan3", "new_is_blocked": "false", "old_first_name": "bohdan3", "old_is_blocked": "false", "new_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "new_is_password_set": "false", "old_is_password_set": "false", "new_created_datetime": "2024-10-02T15:03:55.154947", "old_created_datetime": "2024-10-02T15:03:55.154947", "new_modified_datetime": null, "old_modified_datetime": null, "new_is_email_confirmed": "true", "old_is_email_confirmed": "true"}
{"id": 44, "op": "UPDATE", "ts": "2024-10-08T07:40:09.503683+00:00", "new_id": "6", "old_id": "6", "new_email": "[email protected]", "old_email": "[email protected]", "record_id": "6abba0cb-a814-5ccd-ad88-86cae70986bd", "table_name": "Users", "new_role_id": "2", "old_role_id": "1", "table_schema": "public", "new_last_name": "l", "new_tenant_id": "1", "old_last_name": "l", "old_tenant_id": "1", "new_first_name": "bohdan3", "new_is_blocked": "false", "old_first_name": "bohdan3", "old_is_blocked": "false", "new_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "new_is_password_set": "false", "old_is_password_set": "false", "new_created_datetime": "2024-10-02T15:03:55.154947", "old_created_datetime": "2024-10-02T15:03:55.154947", "new_modified_datetime": null, "old_modified_datetime": null, "new_is_email_confirmed": "true", "old_is_email_confirmed": "true"}
{"id": 57, "op": "UPDATE", "ts": "2024-10-08T15:05:55.743479+00:00", "new_id": "6", "old_id": "6", "new_email": "[email protected]", "old_email": "[email protected]", "record_id": "6abba0cb-a814-5ccd-ad88-86cae70986bd", "table_name": "Users", "new_role_id": "2", "old_role_id": "2", "table_schema": "public", "new_last_name": "l", "new_tenant_id": "1", "old_last_name": "l", "old_tenant_id": "1", "new_first_name": "bohdan3", "new_is_blocked": "false", "old_first_name": "bohdan3", "old_is_blocked": "false", "new_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "new_is_password_set": "true", "old_is_password_set": "false", "new_created_datetime": "2024-10-02T15:03:55.154947", "old_created_datetime": "2024-10-02T15:03:55.154947", "new_modified_datetime": null, "old_modified_datetime": null, "new_is_email_confirmed": "true", "old_is_email_confirmed": "true"}
{"id": 189153, "op": "UPDATE", "ts": "2025-02-14T17:18:41.925637+00:00", "new_id": "6", "old_id": "6", "new_email": "[email protected]", "old_email": "[email protected]", "record_id": "6abba0cb-a814-5ccd-ad88-86cae70986bd", "table_name": "Users", "new_role_id": "2", "old_role_id": "2", "table_schema": "public", "new_last_name": "l", "new_tenant_id": "1", "old_last_name": "l", "old_tenant_id": "1", "new_first_name": "bohdan33", "new_is_blocked": "false", "old_first_name": "bohdan3", "old_is_blocked": "false", "new_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old_password_hash": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "new_is_password_set": "true", "old_is_password_set": "true", "new_created_datetime": "2024-10-02T15:03:55.154947", "old_created_datetime": "2024-10-02T15:03:55.154947", "new_modified_datetime": null, "old_modified_datetime": null, "new_is_email_confirmed": "true", "old_is_email_confirmed": "true"}

So, as you can see there is full log with changes with 'new_' and 'old_' properties for easier parsing data.

Retrieve Only Changes (Diffs) with Initial Baseline (this one may be useful in systems that uses event sourcing to restore current state of an entity):

SELECT * FROM audit.get_record_history_diff(17318::oid, '{"id": 6}'::jsonb, true);

Result:

{"id": {"new": "6", "old": null}, "ts": {"new": "2024-10-04T10:27:09.254176+00:00", "old": null}, "email": {"new": "[email protected]", "old": null}, "role_id": {"new": "1", "old": null}, "last_name": {"new": "l", "old": null}, "tenant_id": {"new": "1", "old": null}, "first_name": {"new": "bohdan3", "old": null}, "is_blocked": {"new": "false", "old": null}, "password_hash": {"new": "QrObR/+fEF6v49u/hNTpmZkH9Nc3cyMp0XwESxFgG98=", "old": null}, "is_password_set": {"new": "false", "old": null}, "created_datetime": {"new": "2024-10-02T15:03:55.154947", "old": null}, "modified_datetime": {"new": null, "old": null}, "is_email_confirmed": {"new": "true", "old": null}}
{"ts": {"new": "2024-10-08T07:40:02.088688+00:00", "old": "2024-10-04T10:27:09.254176+00:00"}, "email": {"new": "[email protected]", "old": "[email protected]"}}
{"ts": {"new": "2024-10-08T07:40:09.503683+00:00", "old": "2024-10-08T07:40:02.088688+00:00"}, "role_id": {"new": "2", "old": "1"}}
{"ts": {"new": "2024-10-08T15:05:55.743479+00:00", "old": "2024-10-08T07:40:09.503683+00:00"}, "is_password_set": {"new": "true", "old": "false"}}
{"ts": {"new": "2025-02-14T17:18:41.925637+00:00", "old": "2025-01-13T10:54:02.26464+00:00"}, "email": {"new": "[email protected]", "old": "[email protected]"}, "first_name": {"new": "bohdan33", "old": "bohdan3"}}

Additional context

Screenshot of the example I've provided above:

I would like to thank the authors and those who helped create supa_audit, you all did great job. I also want to contribute to the development of this wonderful repo

Feb 15 '25 10:02 Forevka