Skip to main content

DB Table Auditing Utilities

Project description

There comes a time when you require auditing to be present on a database table. It usually looks like a painful process to implement it due to the vast changes required to your codebase. Wouldn’t it be wonderful if you could implement it, while changing the minimal amount of lines of code, and even obtain an easy access to its logged audit data?

Note: this library is specifically designed to work with SQLAlchemy_ and Alembic_. It also supports integration with Marshmallow_. If using a different ORM layer, feel free to use the concepts herein, adapted to your needs.

A big thanks to the Alembic cookbook, for the majority of the code used in audit.utils.alembic.

Example usage

Install audit-utils as one of your project dependencies:

pip3 install audit-utils

You will likely want to adjust the model you wish to audit, so that you have a base model, from which to inherit in both the audited table and the audit table.

Add the appropriate mixins to your DB models and create an audit table model:

from audit.models import AuditTableMixin, AuditedMixin
from application import db

class UserBase:
    username = db.Column(db.String, nullable=False)
    first_name = db.Column(db.String)
    last_name = db.Column(db.String)

class User(UserBase, AuditedMixin, db.Model):
    id = db.Column(db.String, nullable=False, primary_key=True)

class UserAudit(UserBase, AuditTableMixin, db.Model):
    # This is equivalent to User.id, but without being part of the
    # primary key. You should create an index for it, for query
    # performance on the audit data.
    audited_id = db.Column(db.String, nullable=False, primary_key=False)

And if you use Marshmallow schemas, you can import the provided schemas for simplicity:

from audit.schemas import AuditLogSchema
from application import marshamallow as ma

class UserSchema(ma.Schema):
    username = ma.String(required=True)
    first_name = ma.String()
    last_name = ma.String()

    auditlog = ma.Nested(AuditLogSchema, dump_only=True)

Finally, generate a new Alembic migration that includes the audit table definition, and add the following bits:

from application.models import User

user_audit_sp = User().stored_procedure()
user_trigger = User().trigger()

def upgrade():
    op.create_procedure(user_audit_sp)
    op.create_trigger(user_trigger)

def downgrade():
    op.drop_trigger(user_trigger)
    op.drop_procedure(user_audit_sp)

Note: Before running the database upgrade, ensure that the generated audit model columns are in the _same_ order as the audited table. The stored procedure relies on the order being the same to not need updates every time a column is added to the base table definition.

Upgrade your database and you should now have auditing on the user table, handled at the database level, so you don’t need to implement all sorts of things in your code.

Implementation Notes

It is entirely possible that you have a compound primary key for the table that you wish to be audited. This can be handled by overriding a couple of extra methods from the model mixins.

The id and audited_id columns are not required to be string columns. They can be numeric, provided that the types match in both models.

The default stored procedure does not include column names for your table, and as such does not need to be updated when columns are added/removed/changed.

Occasionally, you will also have columns that you do not wish to include in the audit changeset. While these columns must exist on both the audited table and the audit table, you can add them to the list of ignored columns by overriding audit_ignorable_columns in your audited model.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

audit-utils-0.1.1.tar.gz (10.1 kB view details)

Uploaded Source

File details

Details for the file audit-utils-0.1.1.tar.gz.

File metadata

  • Download URL: audit-utils-0.1.1.tar.gz
  • Upload date:
  • Size: 10.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/47.1.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.8.5

File hashes

Hashes for audit-utils-0.1.1.tar.gz
Algorithm Hash digest
SHA256 880bffc49a16a14f84c7b157a67d9c84982ffd2262f564c4cb9b75bd1926e700
MD5 279680804efbcee704f47440953c99ef
BLAKE2b-256 8d13355b976fe5942b026218ad4bb072b48d880fa08d8e51c9764d22ac07a401

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page