Change audit table to comply with other RMB and CQL2PG tables:
- Move all fields into the JSONB field.
- Make jsonb->>'id' a unique primary key.
RMB-277 and RMB-346 we decided that the primary is always in both id and jsonb->>'id'. CQL2PG relys on this for optimizations and foreign key joins.
It was forgotten to change the audit table used for history accordingly: https://github.com/folio-org/raml-module-builder#postgresql-integration
CQL2PG fails when trying to query an audit table because id equals jsonb->>'origId' but not jsonb->>'id'.
Our existing CQL2PG code should also work on audit tables so that it can handle complicated queries and make use of indexes. Therefore we should move all these fields into the jsonb field. Only id will remain a field outside of jsonb (but with duplicate jsonb->>'id').
The triggers need to be adjusted accordingly.
These modules will be affected:
mod-configuration (Configurations history)
mod-circulation-storage (LoansApi history)
Insert origId, operation, creationDate into the record to audit. origId is the id of the original record. These audit fields overwrite any data if these fields exists in the original record (name clash).
Store the original record into the "item" field. Advantages: No name clash. The original id field doesn't need renaming. The RAML .json file for the audit record can use a $ref reference to the .json file of the original record. Disadvantage: Need to prepend "item." to any field name, for example "item.name" or "item.id".
Note description change: Use origId and creationDate instead of orig_id and creation_date to comply with camelCase convention for field names.