At federated login, what must your server record so that a later logout notice naming a provider session identifier maps onto local sessions?

## What arrives, and why it names nothing you hold An academic reference manager that signs researchers in through their institution issues **its own session** when the federated login completes: a row in a session store keyed by an opaque identifier that only your service understands, and a cookie carrying that identifier back on every request. The institution's identity provider knows nothing about that row. When the researcher signs out at the institution — at a shared library terminal, before walking away from it — the provider can notify each application that person signed into, and the notification necessarily speaks **the provider's identifiers, not yours**: - `sub` — the subject: a stable identifier for the person, unique *within that issuer*. - `sid` — an identifier for one provider-side session: that particular sign-in, not that person. A notification may carry both, or only the subject. Neither is your session key. If your row recorded only "this session belongs to user 18244", no query turns an arriving `sid` into rows, and the endpoint you registered with the provider is decorative — it answers and ends nothing. **The index must already exist before any notice is useful, and the only place it can be built is the login path.** ## What the login path must write At the moment the callback hands you a verified subject and you create the local session, persist three values and index two pairs: 1. `iss` — the issuer that authenticated this login. It belongs in both lookup keys and is never optional. 2. `sub` — the subject exactly as that issuer spells it, copied verbatim, never normalised, trimmed or lower-cased. 3. `sid` — the provider session identifier, when the provider issues one. Absent is a legitimate state, not an error. Then index `(iss, sid)` and `(iss, sub)`. Three properties of that write are worth stating outright: - **It happens on every path that mints a session**, including a silent re-authentication that replaces one. A session created without the index is invisible to every later notice, and nothing will ever tell you it is. - **It is copied, not derived.** The local user identifier is yours; these three are the provider's, and the moment you map them onto your own primary key at login you have thrown away the ability to look up by what the notice actually carries. - **It is per session, not per user.** A researcher signed in on a library terminal and on their own laptop has two rows, and the whole point of storing `sid` is that those two rows can be told apart by something the provider can name. ## Two identifiers, two fan-outs | The notice names | Resolves through | Ends | Getting it wrong costs | |---|---|---|---| | `sid` — one provider session | `(iss, sid)` | at most one local session | widening it signs the researcher out of every device, mid-import | | `sub` only — the person | `(iss, sub)` | every local session that person holds here | narrowing it to one row leaves the library terminal signed in | This is the decision the index exists to support, and it is the part interviewers probe. A `sid` is the narrow instrument: exactly one row, or zero if that sign-in never produced a session here. A subject-only notice is the blunt one: it can only mean *this person is no longer signed in at the provider*, so it resolves to the whole set. Providers differ on which they send, and some never issue a session identifier at all — so the handler must resolve both shapes, and the design must be honest that against such a provider single-session logout is simply unavailable. ## Where the index lives, and what it costs - **On the session record itself** when sessions live in one store you control: two extra columns and two secondary indexes on a table already written once per login. - **In a separate mapping table** when a single provider sign-in can back more than one local session record — the join then carries the fan-out and the session rows stay narrow. - **The read side is cheap and rare:** a point lookup when a notice arrives, which happens once per sign-out, against a write on every login. The real cost is discipline, not throughput — one login path that forgets the write is a silent hole. - **The issuer belongs in the key even in a single-provider deployment.** Subjects are unique per issuer only, so a service that later accepts a second institution and indexed on the subject alone can resolve a notice from one provider onto sessions established through another. Adding the column later means backfilling rows whose issuer nobody recorded. ## What resolution deliberately is not Resolving a notice produces a **set of local session identifiers** and stops there. Destroying a session record, what dies with it and how the browser finds out on its next request are the session layer's concern; whether a standing grant your service holds for that researcher survives the sign-out is a separate, explicit decision. Keeping resolution as a pure lookup is what lets the same mapping serve a notice that names one sign-in and a notice that names a person, without either path growing its own special case.