Data Quality Monitoring, Audit Logging, and Compliance Documentation

Why Data Quality Monitoring and Audit Logging Matter

A database that stores accurate, trustworthy data is only as useful as the controls that keep it that way over time. Data quality degrades silently: NULL values accumulate, out-of-range entries slip through application bugs, and unauthorized changes go unnoticed. Two complementary mechanisms address this: audit logging records who changed what and when, while data quality monitoring checks whether the data still meets defined standards. Together they form the foundation of any compliance posture, whether the concern is an internal data governance policy or an external privacy framework.

Audit Logging with PostgreSQL Triggers

A PostgreSQL trigger is a function that the database engine calls automatically when a specified event occurs on a table. For audit purposes you attach an `AFTER` trigger to `INSERT`, `UPDATE`, and `DELETE` operations. Inside the trigger function you capture the operation type, the acting database user (`SESSION_USER` or `CURRENT_USER`), the wall-clock timestamp (`NOW()`), and the old and new row values exposed through the `OLD` and `NEW` pseudo-records.

The audit records go into a dedicated `audit_log` table. That table should be append-only: grant `INSERT` to the trigger-owning role but revoke `UPDATE` and `DELETE` so that no one can tamper with historical records. Storing changed values as `JSONB` columns (`old_data`, `new_data`) keeps the schema generic and allows you to reuse the same trigger function across multiple target tables.

A minimal `audit_log` schema looks like this:

```sql

CREATE TABLE audit_log (

log_id BIGSERIAL PRIMARY KEY,

table_name TEXT NOT NULL,

operation TEXT NOT NULL, -- INSERT, UPDATE, DELETE

db_user TEXT NOT NULL,

changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

old_data JSONB,

new_data JSONB

);

```

The trigger function returns `NULL` for `AFTER` triggers (the return value is ignored) and uses `TG_TABLE_NAME`, `TG_OP`, `ROW_TO_JSON(OLD)`, and `ROW_TO_JSON(NEW)` to populate each column.

Scheduled Quality Checks with pg_cron

pg_cron is a background worker extension for PostgreSQL that lets you schedule SQL statements using standard cron syntax directly inside the database. Enabling it requires adding `pg_cron` to `shared_preload_libraries` in `postgresql.conf` and restarting the server. Once loaded, the `cron.schedule()` function registers a job.

A daily quality-check script should test at minimum:

• NULL counts — count rows where a required column is NULL.
• Range checks — count rows where a numeric or date column falls outside an acceptable range.

Results write to a `data_quality_log` table that includes a `pass_fail` flag (a simple `BOOLEAN` or `TEXT` column), the check name, the table checked, the count of violations found, and the run timestamp. Setting `pass_fail = FALSE` when the violation count exceeds zero gives operators a quick filter.

If the `pg_cron` extension is unavailable in your environment, an OS-level cron job on the Ubuntu VM can call `psql` with a `-f` flag pointing to your SQL script. Both approaches are acceptable; the key is that the job runs unattended on a schedule.

Privacy Principles and Compliance Documentation

Data privacy frameworks worldwide share a set of core principles even when their legal texts differ. For this lesson you document how your technical controls support three of the most widely recognized:

• Data minimization — collect and store only the personal data necessary for the stated purpose. In database terms, this means not logging sensitive column values (passwords, payment card numbers) in the audit trail, and dropping columns from the target table that serve no operational need.
• Purpose limitation — data collected for one purpose must not be repurposed without authorization. Your `audit_log` exists solely to support security review and compliance; restrict read access to authorized roles only.
• Access control — only authorized users should be able to read or modify personal data. PostgreSQL role-based privileges, row-level security, and schema separation all enforce this principle. The audit log itself supports accountability by recording which user performed each change.

Your one-page compliance summary must explain each principle in plain language, describe the specific technical control in your implementation that supports it, and note any gaps or limitations. This document is not a legal opinion; it is a technical governance artifact.

Safety and Professional Practice

In a database environment, "safety" means protecting data integrity and system availability rather than physical hazards. Apply these professional practices:

• Test triggers in a development schema first. An incorrectly written trigger function can block all DML on a production table. Use a separate test table before attaching triggers to any table containing real data.
• Control permissions on audit tables. Grant only `INSERT` to the trigger role on `audit_log`. Never grant `DELETE` or `UPDATE` to ordinary application roles.
• Back up before schema changes. Run `pg_dump` before adding triggers or extensions to any database that holds data you cannot recreate.
• Review scheduled job output. A cron job that fails silently is worse than no monitoring. Log the exit status and email or alert on failure.
• Handle sensitive data with care. If the target table contains personal data, review what the trigger stores in `old_data` and `new_data` before deploying. Redact or exclude columns that are not needed for audit purposes.