Implementing an Effective Recording Change Audit Log in Google App Engine Design Patterns

Question

I have a pretty common design problem: I need to implement a history log (audit log) for entries in the Google App Engine. The history journal should be structured, i.e. I can’t join all the changes in some free form text and store in the string field.

I considered the following options for the story model and, noticing performance problems in option # 1, I chose option # 3. But there are some doubts if this solution is efficient and scalable. For example: is there a risk that performance will decrease significantly with an increase in the number of dynamic properties in option No. 3?

Do you have more in-depth knowledge about the pros and cons of each option, or can you offer other audit template templates that apply to the characteristics of the Google App Engine database?

• Use classic SQL-master-detail relation
  • Pros
    • easy to understand SQL database developers
    • clean: direct definition for recording history and its properties
    • search performance: simple search in history (indexes can be used)
    • troubleshooting: easy access with administration tools (_ah / admin)
  • Cons
    • one-to-many relationships are often not recommended for implementation in DBE DB
    • read performance: excessive write read operations to show a long audit log, for example. in the detailed window of a large list of entries.
• Saving history in blob field (pickled python structure)
  • Pros
    • easy to implement and flexible
    • read speed: very effective
  • Cons
    • query performance: cannot search indexes
    • Troubleshooting: cannot verify data using db viewer admin (_ah / admin)
    • unclean: not easy to understand / accept for SQL developers (they find this ugly)
• Save history in Expando dynamic properties. For example. for each fieldName field fieldName create fieldName fields (where n = <0..N> is the number of history records)
  • Pros:
    • simple: easy to implement and understand
    • troubleshooting: you can read all the history properties through the admin interface.
    • read speed: one read operation to get a write
  • Minuses:
    • search performance: can't just search history records (they have a different name)
    • not too clean: the number of properties can be confusing on first viewing
• Save history in some sets of list fields in the master record. For example. for each fieldName create a list field fieldName_history
  • Pros:
    • clean: direct definition of history properties
    • simple: easy to understand for SQL developers
    • read speed: one read operation to get a write
  • Minuses:
    • search performance: index search is possible only for records that have ever had any value, and could not search for records that have a combination of values ​​at a specific time;
    • Troubleshooting: viewing lists is difficult in admin db view mode

Answer 1

If I had to choose, I would go for option 1. Reading is performed as (if not more) for other parameters. And all other options have only speed advantages in certain circumstances (small or very large changes). It will also provide you with greater flexibility (with greater ease), for example, a history of cleaning after x days or a history of queries in different types of models. Make sure you create history objects as children of the changed object in one transaction to ensure consistency. You could get one of them:

 class HistoryEventFieldLevel(db.Model): # parent, you don't have to define this date = db.DateTime() model = db.StringProperty() property = db.StringProperty() # Name of changed property action = db.EnumProperty(['insert', 'update', 'delete']) old = db.PickleProperty() # Old value for field, empty on insert new = db.PickleProperty() # New value for field, empty on delete class HistoryEventModelLevel(db.Model): # parent, you don't have to define this date = db.DateTime() model = db.StringProperty() action = db.EnumProperty(['insert', 'update', 'delete']) change = db.PickleProperty() # Dictionary with changed fields as keys and tuples (old value, new value) as values