Implementing system-versioned tables in Postgres

• supa_audit

  4 610 5.0 PLpgSQL

  Generic Table Auditing

  

this approach is also available as an extension here:

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);

• drizzle-pg-notify-audit-table

  1 1 7.5 TypeScript

  example project of using pg-notify and custom trigger for audit table

• SurveyJS

  surveyjs.io sponsored

  Open-Source JSON Form Builder to Create Dynamic Forms Right in Your App. With SurveyJS form UI libraries, you can build and style forms in a fully-integrated drag & drop form builder, render them in your JS app, and store form submission data in any backend, inc. PHP, ASP.NET Core, and Node.js.

  

• nfcompose

  6 32 8.7 Python

  Build REST APIs/Integrations in minutes instead of hours - NF Compose is a (data) integration platform that allows developers to define REST APIs in seconds instead of hours. Generated REST APIs are backed by postgres and support automatic consumer webhook notifications on data changes out of the box.

  

I have implemented this for our tool NF Compose that allows us to build REST APIs without writing a single line of code [0]. I didn't go the route of triggers because we generate database tables automatically and we used to have a crazy versioning scheme that was inspired by data vault and anchor modelling where we stored every change on every attribute as a new record.

Sounded cool, but in practice it was really slow. The techniques that are usually employed by Data Vault to fix this issue seemed too complex. Over time we moved to an implementation that handles the historization dynamically at runtime by generating SQL queries ourselves [1]. On a sidenote: Generating SQL in python sounds dangerous, but we spent a lot of time on making it secure. We even have a linter that checks that everything is escaped properly whenever we are in dev mode [2]

[0] https://github.com/neuroforgede/nfcompose/

• bemi

  4 135 8.2 TypeScript

  Automatic data change tracking for PostgreSQL

  

This is actually what we're @ Bemi https://bemi.io/

We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases.

We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi, check it out if interested :)

• bemi-prisma

  2 64 7.7 TypeScript

  Automatic data change tracking for Prisma

  

This is actually what we're @ Bemi https://bemi.io/

We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases.

We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi, check it out if interested :)

• WorkOS

  workos.com sponsored

  The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  

Suggest a related project