How to Track Database Changes in a Django App Using PostgreSQL Triggers

Alasco stores construction-finance records that people rely on for payments and reporting. We needed a history of those changes: enough to debug a customer issue, see who did what inside the product, and show during a compliance review that values did not disappear without a trace.

That history is an audit trail. Initially, django-auditlog gave us one. It tracked changes to our models, from creating new records to updating and deleting existing ones.

As the application grew, we started to see the limitations of doing this at the application layer.

We need a new solution

django-auditlog calculates changes in the Python layer. That works well for individual model operations, but it means the application has to determine what changed before the data is persisted to PostgreSQL.

If we update 100 objects, the application needs to process the changes for each object individually.

Some of our APIs perform large numbers of database operations, and this extra work started affecting their performance.

Worse, we always missed some changes at the application layer: bulk_create, bulk_update, and QuerySet.update() never call save(), so they never reached the audit log table.

So we started looking at other ways to capture database changes.

What are the common ways to do this?

There are three common approaches, depending on where the change is calculated.

1. Application layer: The application calculates the diff on the write path. This is usually the easiest to integrate because everything happens inside the application.
2. Database triggers: An AFTER INSERT/UPDATE/DELETE trigger calculates the diff using OLD and NEW. This catches all changes, including bulk operations, but it still adds cost to the write path.
3. Log-based change data capture (CDC): A separate process consumes database change events, typically from PostgreSQL’s WAL. This keeps overhead off the application and the write path.

We already had an audit-trail format in production, so whatever we picked had to keep producing the same kind of information. Triggers were the middle path: closer to the database than Python, without requiring a CDC pipeline.

Same format, new engine

The first challenge was backward compatibility.

We introduced a json_delta function on the database that calculates the difference between OLD and NEW, similar to how django-auditlog calculated model changes.

The result is a list of changes containing the old and new values:

[
  {
    "field": "name",
    "old": "Riverfront Offices",
    "new": "Riverfront Offices Phase 2"
  },
  {
    "field": "status",
    "old": "draft",
    "new": "active"
  }
]

Each audit row also gets a request id, created once per request-response. Every insert, update, and delete in that request shares the same value, so the whole action can be read back as one unit.

Keeping the developer experience

Although we were moving the logic into PostgreSQL, we still wanted engineers working at the Django level. Nobody should have to write PostgreSQL triggers by hand every time they want to audit a model.

So we attached the audit trigger through Django metadata using django-pgtrigger:

class Project(models.Model):
    ...
    class Meta:
        triggers = _audit_triggers.Triggers

Adding a model to the audit trail is now a metadata choice. The SQL that actually writes the history lives in one place. That got us most of the way. Then we hit another problem.

The migration explosion

With django-pgtrigger, the SQL required for the trigger was being included in the generated Django migrations.

As we added auditing to more models, the same audit SQL was duplicated across many migrations. We needed to separate the audit implementation from the model-level trigger definition.

Instead of putting all the audit logic directly into every trigger, we moved the implementation into shared PostgreSQL functions. We introduced two functions in SQL, audit_insert_update_delete and audit_truncate. The Django-side trigger definitions just invoke them:

def get_triggers(product: _enums.Product) -> list[_pgtrigger.Trigger]:
    insert_update_delete = _pgtrigger.Trigger(
        name="audit_insert_update_delete_trigger",
        operation=_pgtrigger.Insert | _pgtrigger.Delete | _pgtrigger.Update,
        func=(
            "PERFORM audit_insert_update_delete("
            "to_jsonb(NEW), to_jsonb(OLD)"
            "); RETURN COALESCE(NEW, OLD);"
        ),
        when=_pgtrigger.After,
    )

    truncate = _pgtrigger.Trigger(
        name="audit_truncate_trigger",
        operation=_pgtrigger.Truncate,
        func=(
            "PERFORM audit_truncate(TG_OP, TG_TABLE_NAME); "
            "RETURN COALESCE(NEW, OLD);"
        ),
        level=_pgtrigger.Statement,
        when=_pgtrigger.After,
    )
    return [insert_update_delete, truncate]

All you need is context

We still had another question:

Who made the change, and which tenant did it belong to?

A database trigger knows that a row changed. It does not automatically know about the application context that caused the change.

Alasco is a multi-tenant application, so our audit trail also needs the tenant information.

We introduced Django middleware that builds the context from the incoming request and applies it to the database connection.

The audit trail can then capture the tenant and other request metadata without every database operation having to pass that information around explicitly.

We apply that context with SET LOCAL, so PostgreSQL discards it at the end of the transaction. The middleware also clears it in process_response and process_exception, in case the connection is reused.

Takeaways

The biggest lesson for us was that there is not one perfect solution. We started with django-auditlog because it was simple and solved the problem we had at the time.

As the system grew, the requirements changed. We moved the responsibility closer to the source of truth by moving change detection into PostgreSQL. Now, every change to an audited table is captured by the database, including changes made through bulk operations that bypass Django’s model layer.

Future work

Today, the audit trail is centralised within the boundaries of a single database. At Alasco we have two databases inside one monolith, and more than one web framework. The next challenge is centralising those audit events across databases.

Acknowledgements

A huge thank you to all the members of the Platform team at Alasco for supporting us throughout this journey!