I suppose this can be done with row-based security if you are willing to mirror/maintain this in Postgres and/or fork the query engine to issue them under the r

I suppose this can be done with row-based security if you are willing to mirror/maintain this in Postgres and/or fork the query engine to issue them under the right roles. There was a talk at PG Day UK 2018 about this but there are no performance benchmarking results available AFAIK. As to triggers, you can totally do this with event sourcing in triggers, so when something is modified you write a row to the audit table, and you use another process to read from it and do the right thing, etc.