Auditing

Auditing can be enabled to track all data changes in the consumer database. When enabled, an additional audit table is created for each main table.

Enabling Auditing

Configure auditing in your application.yml:

solifi:
  audit:
    enabled: true           # Enable audit tables
    include-all: true       # Create audit tables for all topics
    audit-suffix: _audit    # Suffix for audit table names

Configuration Options

Property	Default	Description
enabled	false	Enable/disable auditing
include-all	true	Create audit tables for all configured topics
audit-suffix	_audit	Suffix appended to table name for audit table

How Auditing Works

Scenario 1: Insert or Update Event

When a record is inserted or updated, a corresponding record is inserted into the audit table with additional metadata columns.

Main Table: ls_master

id	contract_number
1	ABC

Audit Table: ls_master_audit

id	contract_number	lp_kafka_partition	lp_kafka_offset	lp_db_action	lp_kafka_date	lp_db_insert_user	lp_db_insert_date
1	ABC	0	13	UPSERT	2023-11-13 03:35:02.275	solifi-consumer	2024-09-11 04:15:04.175

Scenario 2: Delete Event

When a record is deleted:

1. A record with null values is inserted into the audit table with DELETE action
2. The record is deleted from the main table

Main Table: ls_master

id	contract_number
(empty)

Audit Table: ls_master_audit

id	contract_number	lp_kafka_partition	lp_kafka_offset	lp_db_action	lp_kafka_date	lp_db_insert_user	lp_db_insert_date
1	ABC	0	13	UPSERT	2023-11-13 03:35:02.275	solifi-consumer	2024-09-11 04:15:04.175
1		0	14	DELETE	2023-11-13 03:36:15.000	solifi-consumer	2024-09-11 04:16:22.500

Audit Table Columns

Each audit table includes the following metadata columns:

Column	Description
lp_kafka_partition	Kafka partition the message was received from
lp_kafka_offset	Kafka offset of the message
lp_db_action	Action performed: UPSERT, DELETE, or INITIAL
lp_kafka_date	Timestamp from Kafka message
lp_db_insert_user	Consumer group ID that processed the message
lp_db_insert_date	Timestamp when the record was inserted into the audit table

lp_db_insert_user

The value of lp_db_insert_user defaults to the group-id specified in the application.yml. This enables customers to keep the history of audit logs during data refreshes.

Important Considerations

Storage Requirements

• Audit tables store a copy of every change, so storage requirements grow significantly
• If auditing is enabled for all tables, plan for approximately more than twice the storage

Data Retention

• Data in audit tables is not automatically cleaned up
• You must handle cleanup manually according to your organization's data retention policies

Configuration Changes

• Any configuration changes to auditing do not come into effect without a restart of the consumer

Audit Table Primary Key

The audit table primary key includes the following columns:

• lp_kafka_partition
• lp_kafka_offset
• lp_db_insert_user
• lp_db_insert_date

Upgrade Note (Version 2.2.4)

If upgrading to version 2.2.4 and your existing audit tables don't include all these columns in the primary key, you may need to perform a full data resynchronization. Contact LimePoint Support for guidance.

Querying Audit Data

View Change History for a Record

SELECT * 
FROM ls_master_audit 
WHERE id = 1 
ORDER BY lp_kafka_date;

View Recent Changes

SELECT TOP 100 * 
FROM ls_master_audit 
ORDER BY lp_db_insert_date DESC;

View Changes by Action Type

SELECT lp_db_action, COUNT(*) as count
FROM ls_master_audit
GROUP BY lp_db_action;

Selective Auditing

While include-all: true creates audit tables for all topics, you can also configure auditing for specific topics by specifying the audit table name in the topics configuration:

solifi:
  topics:
    - topicA:tableA:tableA_audit    # Creates audit table
    - topicB:tableB                  # No audit table
    - topicC                         # Uses default (depends on include-all)

Best Practices

1. Plan storage capacity - Audit data grows quickly
2. Implement retention policies - Define how long to keep audit data
3. Archive old audit data - Move to cheaper storage if needed
4. Index strategically - Add indexes for common query patterns
5. Monitor table sizes - Track audit table growth

Next Steps

• Configure health monitoring
• Understand data refresh
• Learn about error handling