audit trail functionality in database model

[enrico baranski]

Hi all Django users,

I'm quite new to Django and currently experimenting with the database model. Defining fields appears to be quite intuitive and is well described in the documentation. However, I am looking into audit trail functionalities. What that means to me. I have two tables, one is my master data table (rooms) and one is my audit trail table for the rooms table.

So I aim on two major things, first I would like to increment a field "version" in my room-table to track any change on the table record. Inserting the record means version=1 and as soon as the record is changed the field version should increment to 2 and so on.

Second thing would be to automatically track the changes from one version to another in my audit trail table. So I am looking for a way to automatically make table entries when I update the main table ...

My final goal is to define the audit trail functionalities in the database models so it is forced on any record manipulation.

I hope I could describe my issues comprehensible and would be very happy to get some feedback from you guys.


Thanks a lot and best regards,
enrico

[Mike Dewhirst]

You could have a look at Marty Alchin's Pro Django (not really for
beginners but ...) on page 263 where he shows how to do almost exactly
what you describe. If you got that book it would accelerate your
progress in Django anyway. The only downside is it was published in 2008
and Django has moved on since then - but in a good way. Largely backward
compatible. It is probably still all relevant and very valuable.

Mike

> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com
> <mailto:django-users...@googlegroups.com>.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/8f9d2c8e-3fd9-4e75-b628-4e6ff99ee83c%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/8f9d2c8e-3fd9-4e75-b628-4e6ff99ee83c%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Fred Stluka]

Enrico,

I've done this in the past.  I didn't use Django directly for the audit
tables.  Instead I defined DB triggers on each primary table that
inserted a row of audit values into the audit table.  I can send you
sample trigger code that works in Oracle and MySQL.

The nice things about this approach are that Django can entirely
ignore the audit tables, manipulating only the primary tables, and
that audit table entries are ALWAYS created, even of you bypass
Django and do a direct INSERT, UPDATE, or DELETE to a primary
table.

You should still be able to define the audit tables as Django models,
to get the benefit of schema migrations, etc., and to make it easier
to write an audit trail viewer.

In my case, I didn't have an explicit "version" field in the primary
tables, but you could have the triggers handle that also.  Or you
could do it in the Django save() method of each model, or in a
custom manager.

If you go the custom save() or custom manager route, you could
do all of the work there, and avoid the need for triggers, but then
it would be easy to bypass the audit table by doing a direct INSERT,
UPDATE, or DELETE to a primary table.

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

--
You received this message because you are subscribed to the Google Groups "Django users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8f9d2c8e-3fd9-4e75-b628-4e6ff99ee83c%40googlegroups.com.

[enrico baranski]

Hi Mike,

thanks for that reference, I will take a look.


Enrico

[enrico baranski]

Hi Fed,

the DB trigger approach sounds very exciting to me because I really need to be sure that there is no way to manipulate records without audit trail. I also would be very interested in the trigger code for MySQL you mentioned.

You also mentioned that you did something similar in the past, what was your approach to store information in the audit trail table? At the moment, I have two ways in front of me and I am not quite sure which way to go. Assuming my primary table (rooms) stores the fields id, room, number, size and version.

So I could go for one record for each change with audit trail table fields like id, rooms_id, room_old, room_new, number_old, number_new, size_old, size_new, version_old, version_new.
Or with a record for each field, id, rooms_id, field, old, new. There for every field a record is listed.

Do you know other suitable approaches to store audit trail information or did you go one of the listed ways? My main aim here again is to be able to build reasonable audit trail views where a user can see the history of a record with easy query's behind it.

Thanks for reply,
Enrico

[enrico baranski]

Thinking about this topic more detailed made me realize that I also need to track the user who performed the insert/change (delete is globally not permitted) actions. However, that are user names managed via Django ... so when i use DB triggers I only can track the MySQL user who is used by the Django application. Probably that leads to the situation that I need to take care of this in Django or does anyone have another idea how to deal with that?

[Fred Stluka]

Enrico,

the DB trigger approach sounds very exciting to me because I really need to be sure that there is no way to manipulate records without audit trail. I also would be very interested in the trigger code for MySQL you mentioned.

OK.  I'll append some sample code below.

You also mentioned that you did something similar in the past, what was your approach to store information in the audit trail table? At the moment, I have two ways in front of me and I am not quite sure which way to go. Assuming my primary table (rooms) stores the fields id, room, number, size and version.

So I could go for one record for each change with audit trail table fields like id, rooms_id, room_old, room_new, number_old, number_new, size_old, size_new, version_old, version_new.
Or with a record for each field, id, rooms_id, field, old, new. There for every field a record is listed.

Do you know other suitable approaches to store audit trail information or did you go one of the listed ways? My main aim here again is to be able to build reasonable audit trail views where a user can see the history of a record with easy query's behind it.

My approach was one audit record per changed primary record,
not one audit record per changed primary field.  More like your
1st example above. 

But there's no need to store old and new values in the audit table.
Just store new values in the audit table.  You can find the old value
in a previous row of the audit table (sorted by creation time of the
audit table records), from when it was a new value being inserted
or updated.

Make sure you store all new values in the audit table, including the
first INSERT.  Then you can see a complete audit trail in the audit
tables, even if the value has never changed.  In a previous project,
I made the mistake of only putting old values into the audit tables,
after they changed in the primary tables.  So, I had to look at the
audit tables for all old values and at the primary tables for the
current value, which was more complicated.

Be sure to use triggers for DELETE, not just INSERT and UPDATE.
Otherwise, the audit table won't have a record of whether the
row was deleted.

Here are a set of tables (primary and audit) and triggers (INSERT,
UPDATE, and DELETE) for a typical table in my DB.  This is valid
MySQL code.  In this code, you'll see some other conventions I
always follow:

- Naming conventions of:
   -- Tables (prefixed with app name ("ITF" in this case))
   -- PKs (table name, plus suffix "_id")
   -- Constraints (prefixes like "pk_", fk1_", "fk2_", etc for PK and
        potentially multiple FKs)
   -- Audit tables (prefixed with app name plus "A")
   -- Triggers ("ai_", "au_", "ad_" prefixes for audit triggers for
        INSERT, UPDATE, and DELETE)

- All primary tables contain these extra fields which dramatically
   reduce the number of times you have to go to the audit tables:
   -- create_user (string name of user who created row)
   -- create_dt (date/time of row creation)
   -- update_user (string name of user who last updated row)
   -- update_dt (date/time of last row update)

- All primary tables contain this extra field, which I use to track
   the status of the row (active, inactive, archived, etc.).  I never
   actually delete a row.  Just mark it inactive, which makes
   undelete possible w/o having to go to the audit table.
   -- status_id

- Each audit table has exactly the same fields as its primary table,
   plus 3 additional fields:
   -- audited_change_user (string name of user who made the change)
   -- audited_change_dt (date/time time of the change.'
   -- audited_change_type ('INSERT', 'UPDATE', or 'DELETE')

- Audit tables have no need for unique, FK, or PK constraints. 
   Just values.

Here's the code:

--
-- ITF_PRODUCT
--

DROP TABLE IF EXISTS itf_product;
CREATE TABLE itf_product
 (itf_product_id                 INTEGER      NOT NULL AUTO_INCREMENT
                                                       COMMENT 'Primary key'
 ,name                           VARCHAR(255) NOT NULL COMMENT 'Unique user-assigned and user-visible name'
 ,descrip                        VARCHAR(255) NOT NULL COMMENT 'Description'
 ,notes                          VARCHAR(255) NOT NULL COMMENT 'User notes'
 ,create_user                    VARCHAR(255) NOT NULL COMMENT 'User who created this database row.  String, not foreign key, to preserve history when users are deleted.'
 ,create_dt                      DATETIME     NOT NULL COMMENT 'Date and time of creation of this database row.'
 ,update_user                    VARCHAR(255) NOT NULL COMMENT 'User who last updated this database row.  String, not foreign key, to preserve history when users are deleted.'
 ,update_dt                      DATETIME     NOT NULL COMMENT 'Date and time of last update of this database row.'
 ,status_id                      INTEGER      NOT NULL COMMENT 'The status of the data in this database row (active, inactive, archived, etc.).  FK to itf_dict value of category STATUS_LOOKUP.'
 ,CONSTRAINT pk_product PRIMARY KEY (itf_product_id)
 )
  COMMENT='Products'
  ENGINE=InnoDB
;

ALTER TABLE     itf_product
 ADD CONSTRAINT fk1_product
    FOREIGN KEY fk1_product (status_id) REFERENCES itf_dict (itf_dict_id)
;

-- Disallow multiple products with same name.
-- No, we really want to disallow only for records with STATUS=ACTIVE,
-- which can't be done here with a UNIQUE constraint, so this is enforced
-- in the Java BO code instead of here.
-- CREATE UNIQUE INDEX ak1_product ON itf_product (name);

GRANT SELECT, INSERT, UPDATE, DELETE ON itf_product to itfweb;

DROP TABLE IF EXISTS itfa_product;
CREATE TABLE itfa_product
 (audited_change_user            VARCHAR(255) NOT NULL COMMENT 'User who made this change.  String, not foreign key, to preserve history when users are deleted.'
 ,audited_change_dt              DATETIME     NOT NULL COMMENT 'Date and time of this change.'
 ,audited_change_type            VARCHAR(255) NOT NULL COMMENT 'INSERT, UPDATE, or DELETE.  No need for lookup in ITF_DICT since there are only these 3 types of triggers.'
 ,itf_product_id                 INTEGER          NULL COMMENT 'Copied from audited table.'
 ,name                           VARCHAR(255)     NULL COMMENT 'Copied from audited table.'
 ,descrip                        VARCHAR(255)     NULL COMMENT 'Copied from audited table.'
 ,notes                          VARCHAR(255)     NULL COMMENT 'Copied from audited table.'
 ,create_user                    VARCHAR(255)     NULL COMMENT 'Copied from audited table.'
 ,create_dt                      DATETIME         NULL COMMENT 'Copied from audited table.'
 ,update_user                    VARCHAR(255)     NULL COMMENT 'Copied from audited table.'
 ,update_dt                      DATETIME         NULL COMMENT 'Copied from audited table.'
 ,status_id                      INTEGER          NULL COMMENT 'Copied from audited table.'
 )
  COMMENT='Audit table.  No need for constraints.'
  ENGINE=InnoDB
;

GRANT SELECT, INSERT, UPDATE, DELETE ON itfa_product to itfweb;

-- DROP TRIGGER itft_ai_product;
DELIMITER ;;
CREATE TRIGGER itft_ai_product
BEFORE INSERT
ON itf_product
FOR EACH ROW
BEGIN
    -- Purpose: Inserts an audit record into audit table.
    --
    -- MODIFICATION HISTORY
    -- Person        Date           Comments
    -- ---------     ----------     -------------------------------------------
    -- Fred Stluka   2/12/2007      Original version.
    --
    INSERT INTO itfa_product
        (audited_change_user   
        ,audited_change_dt      
        ,audited_change_type
        ,itf_product_id
        ,name
        ,descrip
        ,notes
        ,create_user
        ,create_dt
        ,update_user
        ,update_dt
        ,status_id
        )
    VALUES
        (NEW.update_user
        ,SYSDATE()
        ,'INSERT'
        ,1 + (SELECT IFNULL(MAX(itf_product_id),0) from itf_product)
                -- Can't just use NEW.itf_product_id.  AUTO_INCREMENT
                -- hasn't yet generated a non-zero value.  This works OK
                -- as long as the highest generated value hasn't been
                -- deleted from the table.
                --
                -- Can't use:  1 + (SELECT MAX(itf_product_id) from itf_product)
                -- because it causes the first INSERT to fail.
                -- MAX comes up NULL and gets added to 1 which produces NULL.
                -- IFNULL fixes that.
                --
                -- Could perhaps use an AFTER trigger instead of a BEFORE
                -- trigger, and NEW.itf_product_id would work??
        ,NEW.name
        ,NEW.descrip
        ,NEW.notes
        ,NEW.create_user
        ,NEW.create_dt
        ,NEW.update_user
        ,NEW.update_dt
        ,NEW.status_id
        );
END;
;;
DELIMITER ;

-- DROP TRIGGER itft_au_product;
DELIMITER ;;
CREATE TRIGGER itft_au_product
BEFORE UPDATE
ON itf_product
FOR EACH ROW
BEGIN
    -- Purpose: Inserts an audit record into audit table.
    --
    -- MODIFICATION HISTORY
    -- Person        Date           Comments
    -- ---------     ----------     -------------------------------------------
    -- Fred Stluka   2/12/2007      Original version.
    --
    INSERT INTO itfa_product
        (audited_change_user   
        ,audited_change_dt      
        ,audited_change_type
        ,itf_product_id
        ,name
        ,descrip
        ,notes
        ,create_user
        ,create_dt
        ,update_user
        ,update_dt
        ,status_id
        )
    VALUES
        (NEW.update_user
        ,SYSDATE()
        ,'UPDATE'
        ,NEW.itf_product_id
        ,NEW.name
        ,NEW.descrip
        ,NEW.notes
        ,NEW.create_user
        ,NEW.create_dt
        ,NEW.update_user
        ,NEW.update_dt
        ,NEW.status_id
        );
END;
;;
DELIMITER ;

-- DROP TRIGGER itft_ad_product;
DELIMITER ;;
CREATE TRIGGER itft_ad_product
BEFORE DELETE
ON itf_product
FOR EACH ROW
BEGIN
    -- Purpose: Inserts an audit record into audit table.
    --
    -- MODIFICATION HISTORY
    -- Person        Date           Comments
    -- ---------     ----------     -------------------------------------------
    -- Fred Stluka   2/12/2007      Original version.
    --
    INSERT INTO itfa_product
        (audited_change_user   
        ,audited_change_dt      
        ,audited_change_type
        ,itf_product_id
        ,name
        ,descrip
        ,notes
        ,create_user
        ,create_dt
        ,update_user
        ,update_dt
        ,status_id
        )
    VALUES
        (USER()                         -- Any better solution for DELETE??
                                        -- Could have convention of doing an
                                        -- UPDATE immediately before the
                                        -- DELETE and use OLD.update_user
        ,SYSDATE()
        ,'DELETE'
        ,OLD.itf_product_id
        ,OLD.name
        ,OLD.descrip
        ,OLD.notes
        ,OLD.create_user
        ,OLD.create_dt
        ,OLD.update_user
        ,OLD.update_dt
        ,OLD.status_id
        );
END;
;;
DELIMITER ;


Hope this helps!

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7458b627-f70d-4613-b4c2-3fe53d67452d%40googlegroups.com.

[Fred Stluka]

Enrico,

In the sample MySQL trigger code of my previous message, you'll
see that I always store, in the primary table, the string username
of the most recent user to update the table.  Therefore, that value
is available to the DB trigger as NEW.update_user.

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

On 1/22/17 7:37 AM, enrico baranski wrote:

Thinking about this topic more detailed made me realize that I also need to track the user who performed the insert/change (delete is globally not permitted) actions. However, that are user names managed via Django ... so when i use DB triggers I only can track the MySQL user who is used by the Django application. Probably that leads to the situation that I need to take care of this in Django or does anyone have another idea how to deal with that?

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/63c129b8-6279-4410-8242-ccffef134b6f%40googlegroups.com.

[Melvyn Sopacua]

On Saturday 21 January 2017 04:15:34 enrico baranski wrote:

> I'm quite new to Django and currently experimenting with the database

> model. Defining fields appears to be quite intuitive and is well

> described in the documentation. However, I am looking into audit

> trail functionalities.

 

Don't reinvent the wheel. There's more then enough to choose from:

https://djangopackages.org/grids/g/model-audit/

--

Melvyn Sopacua

[Ryan Castner]

Django Field History is a great project to audit trail a model field

[enrico baranski]

@Fred: Thanks a lot, that really helped me!

enrico