Comprehensive documentation of the generic audit trail system for tracking all entity changes, user actions, and maintaining compliance records across the ProKure platform.
ProKure implements a comprehensive audit logging system that captures all changes to business entities. This system is essential for compliance (SOX, GDPR), security investigations, and operational debugging. Every CREATE, UPDATE, and DELETE operation is logged with full before/after state preservation.
New record inserted
Record modified
Record removed
Sensitive data accessed
Audit logs are immutable once written. They cannot be modified or deleted by regular users. Only database administrators with elevated privileges can manage audit retention policies. All access to audit logs is itself audited.
Audit logs are captured at both the application layer (for user context, IP address, session info) and the database layer (via triggers for data integrity). This ensures no changes escape logging, even direct database access.
| Field | Type | Description |
|---|---|---|
id |
UUID | Primary key, auto-generated |
entity_type |
VARCHAR(50) | Entity/table name (VENDOR, PO, PR, ITEM, etc.) |
entity_id |
UUID | Primary key of the affected record |
action |
VARCHAR(20) | CREATE UPDATE DELETE VIEW |
old_values |
JSONB | Record state before change (null for CREATE) |
new_values |
JSONB | Record state after change (null for DELETE) |
changed_fields |
TEXT[] | Array of field names that changed (for UPDATE) |
user_id |
UUID | Reference to admin.users |
ip_address |
INET | Client IP address |
user_agent |
TEXT | Browser/client user agent string |
session_id |
UUID | Reference to user session |
request_id |
UUID | Correlation ID for request tracing |
created_at |
TIMESTAMPTZ | Timestamp of the action (immutable) |
Before any database operation, the application layer captures user context including user ID, session ID, IP address, and request correlation ID.
// Application sets session variables for audit context SET LOCAL app.current_user_id = 'user-uuid-here'; SET LOCAL app.current_ip = '192.168.1.100'; SET LOCAL app.request_id = 'correlation-uuid'; SET LOCAL app.session_id = 'session-uuid';
PostgreSQL AFTER triggers capture the old and new row values for INSERT, UPDATE, and DELETE operations.
CREATE OR REPLACE FUNCTION audit_trigger_function() RETURNS TRIGGER AS $$ DECLARE v_old_data JSONB; v_new_data JSONB; v_changed_fields TEXT[]; BEGIN IF (TG_OP = 'UPDATE') THEN v_old_data := to_jsonb(OLD); v_new_data := to_jsonb(NEW); -- Calculate changed fields SELECT array_agg(key) INTO v_changed_fields FROM jsonb_each(v_old_data) old_kv FULL JOIN jsonb_each(v_new_data) new_kv USING (key) WHERE old_kv.value IS DISTINCT FROM new_kv.value; ELSIF (TG_OP = 'DELETE') THEN v_old_data := to_jsonb(OLD); ELSIF (TG_OP = 'INSERT') THEN v_new_data := to_jsonb(NEW); END IF; INSERT INTO config.audit_trails (...) VALUES (...); RETURN NEW; END; $$ LANGUAGE plpgsql;
The audit record is inserted with full context. For updates, only changed fields are highlighted while preserving the complete before/after state.
Certain fields are automatically redacted before storing in audit logs to comply with security policies.
password_hash, bank_account_number, gstin, pan, api_keys, tokens - these fields are stored as '[REDACTED]' in audit logs. Original values are never persisted.
SELECT action, old_values, new_values, changed_fields, u.email AS changed_by, created_at FROM config.audit_trails at LEFT JOIN admin.users u ON at.user_id = u.id WHERE entity_type = 'VENDOR' AND entity_id = 'vendor-uuid-123' ORDER BY created_at DESC;
SELECT entity_type, entity_id, action, changed_fields, created_at FROM config.audit_trails WHERE user_id = 'user-uuid-456' AND created_at >= NOW() - INTERVAL '7 days' ORDER BY created_at DESC;
-- Find all status changes for Purchase Orders SELECT entity_id, old_values->>'status' AS from_status, new_values->>'status' AS to_status, u.email AS changed_by, created_at FROM config.audit_trails at LEFT JOIN admin.users u ON at.user_id = u.id WHERE entity_type = 'PURCHASE_ORDER' AND action = 'UPDATE' AND 'status' = ANY(changed_fields) ORDER BY created_at DESC;
-- High-frequency changes from single IP SELECT ip_address, COUNT(*) AS change_count, array_agg(DISTINCT entity_type) AS affected_entities FROM config.audit_trails WHERE created_at >= NOW() - INTERVAL '1 hour' GROUP BY ip_address HAVING COUNT(*) > 100 ORDER BY change_count DESC;
Financial transaction audit trails are retained for 7 years. All changes to vendors, purchase orders, invoices, and payments are immutably logged with user attribution.
Personal data in audit logs can be anonymized upon request. VIEW actions for personal data are logged. Data subject access requests can be fulfilled via audit queries.
Audit logs older than 1 year are automatically archived to cold storage (partitioned tables). Queries can span both hot and cold partitions transparently. Full-text search is available for archived logs via read replicas.
Before any database operation, ensure the application sets the user context via session variables. Anonymous changes indicate a bug in the application layer.
Audit triggers add minimal overhead (~2-5ms per operation). For bulk imports, consider temporarily disabling audit triggers and logging the batch operation as a single entry with reference to the import file.
Access to audit logs should be restricted to compliance officers and security administrators. Use row-level security to limit visibility based on department or role. Never expose raw audit logs via public APIs.
Always include request_id in audit logs to correlate with application logs. This enables end-to-end tracing from user click to database change.