4 ms·

this approach is also available as an extension here: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit it uses a slightly more ge

this approach is also available as an extension here: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit 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

refset 3y ago
Limited to JSONB types though, by the looks of it.
- kiwicopple 3y ago
  the data is stored as JSONB so that it can be used generically across all tables and adapt to schema changes
  - refset 3y ago
    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)?
    - kiwicopple 3y ago
      > 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
      - refset 3y ago
        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.
- cpursley 3y ago
  I imagine you could modify to use array type. But jsonb makes it easy to normalize back and forth to a row.
- Rapzid 3y ago
  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.
random_kris 3y ago
I implemented this using drizzle and PG not long ago Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table https://github.com/grmkris/drizzle-pg-notify-audit-table
cpursley 3y ago
Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#audit-logs 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.
- kiwicopple 3y ago
  > 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
  - cpursley 3y ago
    Sure. It's somewhat out of date but gets the general idea across: https://gist.github.com/cpursley/a2ae79423bee9274e3639839967e17ff#file-activity_feed_logic-sql-L80 https://gist.github.com/cpursley/a2ae79423bee9274e3639839967...

refset 3y ago

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

kiwicopple 3y ago
the data is stored as JSONB so that it can be used generically across all tables and adapt to schema changes
- refset 3y ago
  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)?
  - kiwicopple 3y ago
    > 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
    - refset 3y ago
      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.
cpursley 3y ago
I imagine you could modify to use array type. But jsonb makes it easy to normalize back and forth to a row.
Rapzid 3y ago
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.

random_kris 3y ago

I implemented this using drizzle and PG not long ago Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table https://github.com/grmkris/drizzle-pg-notify-audit-table

cpursley 3y ago

Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#audit-logs 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.

kiwicopple 3y ago
> 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
- cpursley 3y ago
  Sure. It's somewhat out of date but gets the general idea across: https://gist.github.com/cpursley/a2ae79423bee9274e3639839967e17ff#file-activity_feed_logic-sql-L80 https://gist.github.com/cpursley/a2ae79423bee9274e3639839967...