Hacker News Reader

Comment by kiwicopple | Hacker News Reader

this approach is also available as an extension here:

it uses a slightly more generic implementation so that you can easily turn on/off auditing. i.e.

    select audit.enable_tracking('public.account'::regclass);
    select audit.disable_tracking('public.account'::regclass);

the reason for the 'public.account'::regclass is to store the tables OID, rather than the name - this is cheaper than text and allows you to rename the table without losing the history

View on HN

Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list:

https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

I do a similar thing but add a foreign key columns for faster lookups (traversing using is slow on large sets when querying). The fk’s get set by the function itself during insert. This allows me to create per resource audit logs in the UI.

I also wrote some deep diff functions to get the changes between the old and new jsonb if anyone is interest. This makes it easy to display what exactly changed.

> deep diff functions to get the changes between the old and new jsonb if anyone is interest

I'd love to see this if it's public

Sure. It's somewhat out of date but gets the general idea across:

https://gist.github.com/cpursley/a2ae79423bee9274e3639839967...

I implemented this using drizzle and PG not long ago

Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table

Limited to JSONB types though, by the looks of it.

Yeah not really the same then. The approach in the post allows you to easily reuse existing queries by just adding global filters and pointing to the history table.

the data is stored as JSONB so that it can be used generically across all tables and adapt to schema changes

It's definitely a reasonable trade-off given the circumstances. Do you know whether Supabase teams who use this extension simply avoid using any non-JSON Postgres types? Or do they lean on workarounds (e.g. JSON-LD encoding)?

> avoid using any non-JSON Postgres types

i'm not sure I follow - why would they avoid using non-JSON types?

more generally however: I don't have a lot of insight into the usage/feedback for this extension, but I can ask the team

The issue is the complexity of figuring out appropriate JSON-compatible serializations and getting the implementation correct for every single column in use. A simple example would be round-tripping the Postgres money type using salary::numeric and later (user_data->>'salary')::money

A much more complex example would be representing a numrange where you have to preserve two bounds and their respective inclusivity/exclusivity, in JSON.

I imagine you could modify to use array type. But jsonb makes it easy to normalize back and forth to a row.

Reply on news.ycombinator.com