Hi, I am learning doing CRUD with authentication and authorization. I will be descriptive because my phase of experience and to check if I understand it correctly.

I will share my specific use-case as example; I have nextAuth set up with a social provider (azure b2c) and I have prisma with postgres as database. Added to this, I plan to use a PHP/C# API in future.

Now let's say there is account A and account B and a posts table in the database. Account A should be able to get all posts but only be able to delete/edit posts from itself, account A.

To do that, please correct if I am wrong, you use an access token to access the API route. After that, you extract the sub claim from the access token and use that to identify the account, so only account A can access account A's posts for edit/delete.

If this is correct so far, my question is, do you use the sub claim as accountId field in posts and as primary key in a accounts table?

Or do you use a database generated cuid() as primary key in the accounts table and have a field with sub (or another name, like idp_id, google_id e.g.)? Whereafter you use the sub to query the accounts table to find the id (generated by cuid()) and use that for accountId field in posts?

tl:dr;

if (account.id (cuid()) === posts.accounId)

or

if( account.id (sub claim) === posts.accountId)

​