How should I store the users groups relationships? Say I have the following table in Postgres

sql
            Table "public.users_groups"
  Column  |  Type  | Collation | Nullable | Default 
----------+--------+-----------+----------+---------
 user_id  | bigint |           | not null | 
 group_id | bigint |           | not null |

And of course, the same data need to be passed to spicedb as well, specifically to the

relation_tuple

table. Do I 1. remove

users_groups

rely on

relation_tuple

entirely for users-groups relationship. 2. keep both tables and sync changes between them, i.e. when a change(removing/adding an user from a group) happens, I will use a database transaction to wrap the actions and deal with rollback upon failures.

SQL
BEGIN TRANSACTION;

UPDATE users_groups SET ..  WHERE user_id = 1;

-- call spciedb api to update the same change
-- if the call failed, we roll back this transaction
ROLLBACK;

COMMIT TRANSACTION;