Design the database for oauth permissions
PremiumDatabase designMedium
- Oauth 2.0
- Scopes
- Many to many
Quick answer
This database design interview question asks you to design the database for oauth permissions. Aim for a design that stores data correctly and serves its main queries well, then explain the schema, keys, indexes and trade-offs. Interviewers want to see how you model entities, choose keys and design for the real access patterns.
The problem
Design the database for OAuth permissions: the tables an OAuth 2.0 platform uses to store customers, client apps, access tokens and the permissions (scopes) each token carries. Every API call looks up a token, so reads must be fast, while issuing and revoking must be strictly correct. The hard part is the many-to-many link between tokens and permissions at billions of rows.
Customers are accounts whose data the platform's APIs expose. A client app asks a customer for permissions such as orders:read; the customer's approval is a grant. Tokens are opaque random strings that resource servers look up on every request.
What must the database for oauth permissions do?
- Token lookup: Given a token from an API call, return whether it is valid, its customer, app and permissions, in one fast read.
- Token issue: Issue an access token (1 hour) and a refresh token (30 days) carrying a subset of the permissions in the customer's grant to that app.
How well must it do it?
- Fast checks: Token lookup under 5 ms at p99 at peak, permissions included.
- Strict writes: A token and its permissions are stored together or not at all, and a revocation is never lost.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Oauth 2.0
- Scopes
- Many to many
- Token revocation
- PostgreSQL
- Table partitioning
How you’ll practise
- Access patterns
- Entities
- Schema
- Indexes and queries
- Scale and consistency
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any database design round.
- Lists the entities and how they relate before writing tables
- Designs the schema around the main access patterns
- Chooses primary keys, foreign keys and indexes with reasons
- Explains where to normalise and where to denormalise
- Considers integrity, transactions and concurrent writes
- Plans for growth: partitioning, archiving and migrations
Questions people ask about this interview question
- Which design type is this question?
- It is a database design question. You design the database for oauth permissions and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the entities and the main queries the database must answer. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team