Can we stream changes in data across all tracked tables for visualising purposes

[ziaulrehman40]

I know this might not sound like a good use case, but we do have a valid use case and requirement where we stream change-logs from our paper_trail's table in an HTML view/table (of-course we serialise the records things).

While I convinced my team to switch to logidze and avoid some over heads and 1 giant table with all changes, this 1 thing has us wondering if we can actually make this transition or not.
So wanted to seek help regarding this, is there a way to stream change-logs from across the DB(all tracked tables) so we can visualize them as they happen. Or so we can just see what things changed in specified time window for some audit purposes.

Thanks for the nice gem 👍

[palkan]

is there a way to stream change-logs from across the DB(all tracked tables) so we can visualize them as they happen

That's an interesting idea.

Currently, it's not possible but I think we can add NOTIFY calls into a trigger.

You can try it on your own: run logidze:install and manually update the logidze_logger function SQL.

[ziaulrehman40]

Thanks for the response @palkan , this NOTIFY thing in postgres is interesting, its kind of webhooks(you know how rails devs usually don't use much of the goodies the DBMS engines provide, this is one of them, i just got to know this now).

I suppose where it will end up will be maintaining a kind of a duplicate table, or at-least a table which contains serialized string of changes in our desired format with timestamps, and than we will use that table to stream changelogs etc.
But that means we will end up with a giant table, something like what paper_trail does.

I was thinking of something with the mixup of paper_trail and logidze to solve our problem as well as maintain distributed per-record changelogs.

[ziaulrehman40]

What about single table changelogs streaming?
Can we add some special indexes on ts and at-least be able to do that easily?

[palkan]

If you want to stream changes in real-time, you don't need to write them into a separate table; you can create a subscriber process/thread and, for example, send them right to Action Cable. See Action Cable PostgreSQL adapter, for example.

[ziaulrehman40]

Yeah, but we also need to be able to query these streams of logs for specific time periods, so we will have to store them somewhere.

[fidalgo]

@ziaulrehman40 I guess in your case you want to store them on another table (or set of tables) as it seems you may want to query them later. With that in mind, I guess you can make use of https://github.com/teoljungberg/fx (already a dependency) and create another function that will insert the required files on another table.
Are you trying to build an audit log on top of logidze?