Leverage database triggers to generate object change records

[jeremystretch]

NetBox version

v3.6.4

Feature type

Change to existing functionality

Proposed functionality

NetBox currently records the creation, modification, and deletion of objects using application-level logic; namely the change_logging() context manager and receiver functions for Django's post_save and post_delete signals. This FR proposes replacing this logic with PostgreSQL triggers using django-pgtrigger. (This is similar in nature to how django-pghistory works, however we'll keep our general-purpose ObjectChange model in place.)

At a high level, this will entail the following:

1. Remove the ObjectChange creation logic from the handle_changed_object() and handle_deleted_object() signal receivers. (The receivers themselves must remain to handle webhooks and metrics.)
2. Enable or replicate django-pghistory's context manager to expose request details to the database.
3. Define the appropriate triggers (TBD) under ChangeLoggingMixin to effect the automatic creation of a new changelog record whenever an object is created, modified, or deleted.

While much more exploration is needed, I believe all the necessary components have been identified.

Use case

Moving the change logging logic from the Python application layer to the database layer has several major benefits:

1. Provide a much more robust change logging solution
2. Eliminate the need to manually invoke the change_logging() context manager when working in the Python shell
3. Reduce request processing overhead
4. Simplify the core middleware

Database changes

While this implementation would obviously effect the creation of PostgreSQL triggers, I don't predict that any substantial schema changes to existing models will be necessary.

External dependencies

• django-pgtrigger
• django-pghistory (potentially)

[ITJamie]

one thing to consider. It's currently possible to have models that don't have changelog entries.

I recently ended up making an internal plugin for documenting "last seen" timestamps for inventory items. we did not want changelogs created every time we wanted to update the "last seen" time so a custom field was not an option, instead we made a plugin with a relationship model to inventory items insert /update last seen timestamps there.

I would hope by going down this path we can have some kind of flag on models where changelogs should or should not be created

[DanSheps]

one thing to consider. It's currently possible to have models that don't have changelog entries.

I recently ended up making an internal plugin for documenting "last seen" timestamps for inventory items. we did not want changelogs created every time we wanted to update the "last seen" time so a custom field was not an option, instead we made a plugin with a relationship model to inventory items insert /update last seen timestamps there.

I would hope by going down this path we can have some kind of flag on models where changelogs should or should not be created

It sounds like currently you are ommitting the ObjectChange mixin.

If I am understanding the proposal, the ObjectChange mixin will still exist but now it will instead generate the triggers for each table that needs to be change logged instead of having the middleware intercept the request and process it.

[ITJamie]

It sounds like currently you are ommitting the ObjectChange mixin.

correct

If I am understanding the proposal, the ObjectChange mixin will still exist but now it will instead generate the triggers for each table that needs to be change logged instead of having the middleware intercept the request and process it.

Hopefully, otherwise ill have ~15k changelogs every night when airflow inventorys our ssd's and update the last_seen objects

[jeremystretch]

After digging further into this, I'm not sure it's feasible. All the logic for serializing object representations exists in Python; migrating this to a native database function would be challenging if not impossible. It would also preclude models from overriding their own serialization logic.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

[jeremystretch]

We might revisit this idea in the future, but currently it does not seem feasible without undue effort.