search

kvesteri / postgresql-audit

Audit trigger for PostgreSQL
BSD 2-Clause "Simplified" License
126 stars 28 forks source link

Would it be possible/reasonable to stuff a serialized copy of the object with nested fields into the activity table? #59

Closed jsormaz closed 2 years ago

jsormaz commented 2 years ago

I am using Marshmallow to generate nested serializations of objects to send to the frontend UI.

Since PostgresSQL-Audit is already storing the changed_data as a json, would it be reasonable to store a Nested serialized object in the activity table instead or along with?

Admittedly I am a beginner with Postgres, I have only really interacted with the db at the ORM level. As I understand pg-audit is creating the activity rows at the db level, so maybe this is not reasonable to implement.

As a hack, I was considering adding a json field to my models with an 'onupdate' function to serialize the relationships, then this could be accessed via Activity.data.serialized_repr or something. This feel like anti-pattern however as a lot of info is going to be duplicated in the database.

My end goal is to have really easy access to a snapshot of a deeply nested object state at the point in time that it was modified, rather than having to hop through the activity table to piece it together manually. Is this already possible with pg-audit and I am just missing it?

jsormaz commented 2 years ago

FWIW I went down this path for a little while, determined that the amount of bloat is excessive. I decided to revisit my business logic case and come up with a more targeted solution for what I actually need.