Auditing table changes in CYPEX
CYPEX tracks every modification made to its tables using generic changelog triggers. These triggers capture inserts, updates, and deletions and write a copy of each change into dedicated log tables. The visual editor in CYPEX then renders those logs in a diff format, making it easy to see what changed, where, and when.
The history tables store records as JSON. That design choice keeps the stored data format generic and adaptable, so structural changes to the underlying tables do not break existing audit history. It also means the logs can be queried and analyzed without custom parsing per table.
How the changelog triggers work
PostgreSQL's support for stored procedures is what makes this approach practical. CYPEX implements a single PL/pgSQL function and applies it as an audit trigger across the relevant tables in the system. Because the trigger observes the row data directly, it handles new rows, deletions, and updates uniformly, and each operation type is recorded explicitly.
This type of logging is important in any environment where sensitive or business-critical data is managed. Being able to answer exactly who changed which row, when, and with what operation is a basic security and accountability requirement for database-backed applications.
For a deeper dive into the implementation, see the Cybertec article on tracking changes in PostgreSQL or the related discussion on Stack Overflow.
