Inconsistency in database writes

[mabdh]

Is your feature request related to a problem? Please describe.
There are some places where we add a new organization, group, and project. For those cases, we auto create the creator of those as the member and admin. With this flow, we are writing to 2 places, postgres and spicedb.

However there is a case where we are successfully creating a record in postgres but failed to write to spicedb. This case will make inconsistency in our system. User won't be able to check the authorization even though he is the one who created the org/group/project.

Describe the solution you'd like
The cases above could be solved by considering the writing to 2 places, postgres and spicedb as an atomic transaction. We can wrap both action with a PostgresDB transaction with one of two solutions mentioned in this discussion.

[krtkvrm]

We are still thinking about consistency issue when writing to different DBs. I am bit more inclined towards fire-and-forget, where we use something like outbox pattern to only use 1 call

[mabdh]

@krtkvrm is there any specific reason you prefer that approach? wouldn't that approach be more complex with shield use case?

[ravisuhag]

@kushsharma @anujk14 Is this issue applicable?