A meal planner stores one third-party API grant row per user and provider — why is the granted scope set part of that row's identity?

## What this row actually is A service that writes to somebody else's API on a user's behalf ends up with a table nobody sat down and designed. It starts life as *somewhere to keep the tokens* and becomes the most valuable table in the schema: every live row is standing write access to another person's account at another company, usable while that person is asleep. That is why the row's **identity** deserves more thought than its columns. The row does not record *that a user connected a provider*. It records **one authorization that one user gave**, and an authorization is meaningless apart from the thing it permits. ## Why the granted scope set is part of the identity - **A grant that reads a list is not a grant that writes to it.** Two rows with the same user and the same provider can permit completely different operations, so user plus provider identifies nothing you can safely act on. - **You cannot upgrade the row by refreshing it.** RFC 6749 §6 is explicit: a refresh request may carry a `scope` parameter, but the requested scope must not include any scope that was not originally granted, and omitting it means the same scope as before. Widening is a new authorization, not an `UPDATE`. - **Record what was granted, not what you asked for.** The token response reports the scope the user actually approved, which may be narrower than your authorization request. A row that stores the wish rather than the grant makes every downstream permission check lie. - **Narrower is not a damaged version of wider.** A read-only grant is a complete, valid authorization over a smaller set of operations, and feature gating should read it that way rather than as a broken write grant. ## What else the row has to carry to be operable The two tokens alone will not run anything: 1. **An absolute expiry.** `expires_in` arrives as a number of seconds relative to the response; resolve it once against your clock and store the instant. Nothing later can reconstruct it. 2. **The last successful use.** The only evidence you hold that the grant still works, and the input to every dormancy and retention rule. 3. **A status with a reason and a timestamp.** Active, dead, needs re-authorization — with *why* and *when*, because the job that skips a row and the screen that explains the skip both read this column. 4. **The identifier of the key the token columns are encrypted under**, so that rotating that key is a row-by-row migration rather than one enormous transaction. And deliberately **not** stored: anything derivable. *Is it expired* is a comparison against the clock, not a boolean somebody must remember to flip. ## Which columns are secrets and which must stay searchable The access token and the refresh token are the only secrets in the row. Encrypt them in the application, under a key the database dump does not contain — the whole point of the exercise is that a copy of the table is not a copy of the access. | column | secret? | treatment | why | |---|---|---|---| | access token, refresh token | yes | ciphertext, key held outside the database | a dump must not be usable access | | absolute expiry | no | plaintext, indexed | every refresh sweep filters on it | | granted scope set | no | plaintext, part of the key | permission checks read it on every call | | status, last successful use | no | plaintext, indexed | selects the rows worth touching at all | | key identifier | no | plaintext | lets one key rotate row by row | Encrypting a column removes it from every index and every range predicate. Encrypt the expiry and the *which grants are due* query becomes a full-table decrypt; encrypt the scope set and every permission check turns into a key operation. Neither value is a secret, so neither is worth that price. ## What breaks when the row is keyed on user and provider alone - A feature needing a wider permission quietly uses a narrower grant and fails at the provider — `403` with `insufficient_scope`, halfway through a job, on the provider's schedule rather than yours. - A second authorization covering a different permission set overwrites the first, and background work that depended on the old one stops with nothing recording why. - A migration that introduces a new permission cannot ask *which users have already approved it*, because the answer was never stored as data. - "Why did my list stop updating" becomes a log-diving exercise instead of a query against one table. The corrective is unglamorous: make the granted scope set part of what identifies the row, populate it from what the provider granted, and have every write path consult it before it calls.