RDBMS

The audit database should persist a diff of the entity. The queries are made mostly by the entity's unique identifier. Thus partitioning by UUID and subpartitioning by date ranges can be applied to audit tables

Pros:

• allows flexible access to versioning data

Cons:

• limited scaling options

• negative impact on Postgres that is used by all others modules

Object Storage

AWS S3-like storage can be used to persist diffs because audit events can be stored as plain-text (JSON) documents

Pros:

• allows scaling almost indefinitely

Cons:

• requires an additional solution for complex queries

• might cause high costs for storage bc of a large amount of operations on small files

EventID

UUID

y

y

unique event identifier

EventDate

timestamp

y

n

date when the event appeared in the event log

Origin

varchar

y

n

Origin of the event: data-import, batch-update, user, etc.

Action

varchar

y

n

what action was performed

ActionDate

timestamp

y

n

when action was performed

EntityID

UUID

y

n

entity identifier

UserId

UUID

y

n

user who did the action, fixed UUID for anonymized user

Diff

jsonb

y

n

Difference between “old” and “new” body of the entity

[Instance (FOLIO) Audit] Extend domain event with source FOLIO

Create/Update/Delete operations in the module should generate related events with the new/old body for the entity
Acceptance Criteria:

1. The domain event has new/old fields

2. The domain has a unique identifier for the message

mod-invenotry-storage

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Item Audit] Extend domain event with source FOLIO

Create/Update/Delete operations in the module should generate related events with the new/old body for the entity
Acceptance Criteria:

1. The domain event has new/old fields

2. The domain has a unique identifier for the message

mod-invenotry-storage

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Holding Audit] Extend domain event with source FOLIO

Create/Update/Delete operations in the module should generate related events with the new/old body for the entity
Acceptance Criteria:

1. The domain event has new/old fields

2. The domain has a unique identifier for the message

mod-invenotry-storage

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance (MARC) Audit] Extend domain event with source MARC

Create/Update/Delete operations in the module should generate related events with the new/old body for the entity
Acceptance Criteria:

1. The domain event has new/old fields

2. The domain has a unique identifier for the message

mod-source-record-storage

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Authority Audit] Extend domain event for Authority

Create/Update/Delete operations in the module should generate related events with the new/old body for the entity
Acceptance Criteria:

1. The domain event has new/old fields

2. The domain has a unique identifier for the message

mod-entities-links

UXPROD-4126: MARC authority app and Inventory app | Audit log/Change tracker v1 (Spitfire dev)Open

[Instance(FOLIO) Audit] Consume domain event

To allow efficient access to the data in the audit table it is required to:

1. Create a table with partitioning by UUID, date ranges (a quarter of a year) (Inventory Audit log | ERD )

2. Create a scheduled nightly job that creates additional partitions when necessary

3. Create Kafka consumer for domain event

4. Persist entity diff

Acceptance Criteria:

1. Entity diff object should contain "added", "removed", and "modified" lists with old-new values for any affected field

2. Changes in metadata should be ignored

3. Audit records related to the "create" event should contain only "added" sub-object without previous values

4. Audit records related to "delete" events should contain no body

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance(MARC) Audit] Consume domain event

To allow efficient access to the data in the audit table it is required to:

1. Create a table with partitioning by UUID, date ranges (a quarter of a year) (Inventory Audit log | ERD )

2. Create a scheduled nightly job that creates additional partitions when necessary

3. Create Kafka consumer for domain event

4. Persist entity diff

Acceptance Criteria:

1. Entity diff object should contain "added", "removed", and "modified" lists with old-new values for any affected field

2. Changes in metadata should be ignored

3. Audit records related to the "create" event should contain only "added" sub-objects without previous values

4. Audit records related to "delete" events should contain no body

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Holding Audit] Consume domain event

To allow efficient access to the data in the audit table it is required to:

1. Create a table with partitioning by UUID, date ranges (a quarter of a year) (Inventory Audit log | ERD )

2. Create a scheduled nightly job that creates additional partitions when necessary

3. Create Kafka consumer for domain event

4. Persist entity diff

Acceptance Criteria:

1. Entity diff object should contain "added", "removed", and "modified" lists with old-new values for any affected field

2. Changes in metadata should be ignored

3. Audit records related to the "create" event should contain only "added" sub-object without previous values

4. Audit records related to "delete" events should contain no body

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Item Audit] Consume domain event

To allow efficient access to the data in the audit table it is required to:

1. Create a table with partitioning by UUID, date ranges (a quarter of a year) (Inventory Audit log | ERD )

2. Create a scheduled nightly job that creates additional partitions when necessary

3. Create Kafka consumer for domain event

4. Persist entity diff

Acceptance Criteria:

1. Entity diff object should contain "added", "removed", and "modified" lists with old-new values for any affected field

2. Changes in metadata should be ignored

3. Audit records related to the "create" event should contain only "added" sub-objects without previous values

4. Audit records related to "delete" events should contain no body

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Authority Audit] Consume domain event

To allow efficient access to the data in the audit table it is required to:

1. Create a table with partitioning by UUID, date ranges (a quarter of a year) (Inventory Audit log | ERD )

2. Create a scheduled nightly job that creates additional partitions when necessary

3. Create Kafka consumer for domain event

4. Persist entity diff

Acceptance Criteria:

1. Entity diff object should contain "added", "removed", and "modified" lists with old-new values for any affected field

2. Changes in metadata should be ignored

3. Audit records related to the "create" event should contain only "added" sub-objects without previous values

4. Audit records related to "delete" events should contain no body

mod-audit

UXPROD-4126: MARC authority app and Inventory app | Audit log/Change tracker v1 (Spitfire dev)Open

[Instance/Item/Holding/Bib Audit] Configuration

Provide configuration parameter to enable/disable audit log on tenant level
Acceptance Criteria:

1. The local configuration table is present

2. API for enabling/disabling the feature is available with the correct permissions/capabilities

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance/Item/Holding/Bib Audit] Configuration

Anonymize events
Acceptance Criteria:

1. The local configuration table is present

2. API for enabling/disabling the feature is available with the correct permissions/capabilities

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance/Item/Holding/Bib Audit] Configuration

Provide configuration parameter to set the retention period for audit records on tenant level
Acceptance Criteria:

1. Retention period in years (with default value - 0 for indefinite retention)

2. The local configuration table is present

3. API for enabling/disabling the feature is available with the correct permissions/capabilities

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance/Item/Holding/Bib Audit] Configuration

Create a scheduled nightly job to clean up the records that exceed the retention period
Acceptance Criteria:

1. The job execution should be skipped if the configuration for the retention period is 0 (indefinite period)

2. If the related subpartition of the table is empty, then the subpartition should also be dropped

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance(FOLIO) Audit] Rest Endpoint for history

Query list of diffs per entity from the database with pagination by quarter of the year by entity identifier. Return list of diff records

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Instance(MARC) Audit] Rest Endpoint for history

Query list of diffs per entity from the database with pagination by quarter of the year by entity identifier. Return list of diff records

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Holding Audit] Rest Endpoint for history

Query list of diffs per entity from the database with pagination by quarter of the year by entity identifier. Return list of diff records

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Item Audit] Rest Endpoint for history

Query list of diffs per entity from the database with pagination by quarter of the year by entity identifier. Return list of diff records

mod-audit

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

[Authority Audit] Rest Endpoint for history

Query list of diffs per entity from the database with pagination by quarter of the year by entity identifier. Return list of diff records

mod-audit

UXPROD-4126: MARC authority app and Inventory app | Audit log/Change tracker v1 (Spitfire dev)Open

[Instance/Item/Holding/Bib Audit] Show history Pane in inventory

Call related API to provide the most recent updates. The older records should be fetched only if a user clicks “Show more” button

ui-inventory

UXPROD-4125: Inventory app | Audit log/Change tracker v1 (Folijet dev)Open

Long period for audit records retention

The number of records could overwhelm the capability of the Postgres database both from a computational point of view and cost

High

High

Introduce separate storage for audit-events

Cascade Updates will create redundant copies in the audit log

The update to holdings causes updates to all related items. Some holdings may contain ~15000 records

High

Medium

Collapse or filter out events that only change the parent entity

Some flows could update inventory entities without using the Domain-events mechanism

With different capabilities of the system including UI, data import, bulk edit, etc some of the flows might skip sending Domain events and/or edit entities directly

Low

Medium

List those cases and add domain events to flows that has no this capability

Linked data

The flow and integration with inventory are not clear for the BIBFRAME format

Low

Low

Adjust BIBFRAME flow to follow the proposed solution for other inventory entities

Should failure in sending audit message block the create/update/delete operation?

Hey @Kalibek Turgumbayev - what happens today when an update is made and the create/update date and time stamp is not updated?

The question is related to transactional outbox pattern implementation

What would be the period of retention for Audit records?

@Dennis Bridges has this requirement come up for you with respect to Acq’s change tracker?

The storage options depend on this question:

• Postgres for ~ 1-3year

• Postrgres with Partitioning by UUID 5-15 years

• NoSQL options for 20+ years

In what form should we show the changes to non-marc fields (e.g. staffSuppress, administrative Notes, etc.) in MARC instances?

@Kalibek Turgumbayev - I am unsure I understand this question. Can you review this mockup of how to display updates made to a FOLIO instance record?

Instance with source FOLIO or MARC from inventory is a separate object than SRS record and should be tracked separately

If only the order of fields in a MARC record is changed, should it be logged?

@Kalibek Turgumbayev - Good question - I need to ask users but unless it is a significant to implement, answer is Yes.

@Dennis Bridges has this requirement come up for you with respect to Acq’s change tracker?

Do we have scenarios where the audit log is exported in batches for some period of dates?

It is possible that a library may want to do so but I do not think it is a requirement for Sunflower. @Dennis Bridges has this requirement come up for you with respect to Acq’s change tracker?

If the solution uses Postgres with partitioning by entity key, such exports would cause significant performance issues.

Do we need to have remapping or other technical updates as auditable events?

Not for Sunflower

We might need a list of actions leading to an event in the audit log.

Can we show only the last 20 records (last 3 months)?

Yes. Older records can be fetched with “see more” button and alert that it can take time.

Display of the whole history of the entity will impact performance negatively.

What should be tracked with BIBFRAME? Should we track the original BIBFRAME record or related MARC records is enough.

For Sunflower only track related MARC records.