So if we were doing a view instead of triggers, we'd still have to add the manual work of creating a transaction (but attaching the txn id to the app data so the view can pull it)

is there something wrong with the trigger approach?

Just that triggers are the root of all evil. 😄

More specifically I can write a view in ~20-30 lines of SQL, but the triggers take about 500 LoC of PL/PGSQL keep all of the relations in sync, and that's just for our initial security model.

what about solving it in a different way? what if we directly supported 2 phase commit primitives in our APIs?

I could see 2 phase commit working.

if you have any ideas about how you would like to see that API designed to work with your app

i would love to hear them

So the reason we're wanting to do sync at the db level is we have two applications that hit the DB directly, a legacy PHP app and an Elixir microservice ecosystem with React front ends that get embedded in the legacy PHP to write new functionality/port over legacy functionality. So we don't duplicate/fragment the work across PHP and Elixir, I proposed doing sync at the db level using triggers or views (I was noncommittal to the exact nature of db level sync at the time).

and they write to the same tables? and the data that they write in those tables affects the way that permissions are computed?

Yep

😰 yeah that's a tough one

Yeah I don't reach for triggers under normal circumstances 😅

we're working on a postgres connector: https://github.com/authzed/connector-postgres

Well I'm happy enough with the trigger solution for the moment. It's job security for me at any rate and it allows us to use zedtokens.

I'm just making sure I'm not overlooking something obviously and demonstrably better.

When the connector becomes prod ready I will push for a refactor to use that instead.

Thanks Jake, that makes sense

So I think I was missing a piece of critical info about zedtokens. The idea is you generate and stick them on the resource in the application database, and then use them kind of like an etag to guarantee the freshness of a spicedb check. Is that close to correct?

yep!

they're like a temporal etag, they guarantee that the data used will have some relationship to the zedtoken provided, usually "at least as fresh, but often newer"

temporal in the sense that it's tied to time and not content

we debated calling them ztags at one point 😛

So... a thought experiment to see if I've grokked this. Say I, a resource owner, make an update to the resource. The app puts a zedtoken on the resource while sending a spicedb update to some out-of-band process via SQS or Rabbit or similar. Then the queue gets congested and spicedb relations lag the application DB by 30 minutes while the infrastructure team scrambles to fix the congestion. In the meantime, a user tries to access the resource. Presumably (?) they grab the zedtoken off the resource and send it with their spicedb permissions check. The data in SpiceDB lags the app data so the zedtoken is invalid (somehow). SpiceDB responds with... what?

you won't be able to get the zedtoken without actually having written the update to spicedb

Oh ok. So you have to write the update to spicedb to get a zedtoken to attach to the resource.

the flow usually goes: 1) for new resources, write permissions to spicedb, get the zedtoken 2) store the zedtoken with the content 3) send the zedtoken from the content on access attempts for that content 3a) ensuring that the permissions used are from a time after which the permissions were changed

you can get a zedtoken back from a check request as well, but that will only ensure that you evaluate the permissions from a point in time after the check

not necessarily after you wrote your permissions changes

in your postgres cluster example though, it's possible you would send a zedtoken that refers to a time which has not been written to all read replicas yet, in which case I think all datastore reads would return "no data", which would lead to all check requests failing