Which columns and index let a refresh-credential table answer "has this one been presented before?" in a single lookup?

middlemust knowfreq 47%

answer

one lookup, never a scan
the digest is the index key
a lineage id across every rotation
never delete the superseded row
state column decides the branch

basics

~20 s

A unique index on the stored digest makes a presented credential its own lookup key. The row also carries a family identifier for the rotation lineage and a state column; superseded rows must stay until expiry, or a replay looks merely unknown.

solid answer

~40 s

Index the stored digest uniquely: hashing the presented value gives you the row directly, so detection is one key lookup rather than a scan of forty thousand rows. Each row then carries a family_id shared by every credential in that rotation lineage, a state of active, superseded or revoked, and a pointer to the successor it was replaced by. One lookup yields three outcomes: active — rotate it; superseded or revoked — this credential has been presented before, so revoke the whole family by its indexed family_id; absent — reject, and note you have no family to kill. The critical rule is that rotation never deletes the old row. Delete it and a replay becomes indistinguishable from noise, which is precisely the case you built the table to catch.

Index the stored digest uniquely: hashing the presented value gives you the row directly, so detection is one key lookup rather than a scan of forty thousand rows. Each row then carries a `family_id` shared by every credential in that rotation lineage, a `state` of active, superseded or revoked, and a pointer to the successor it was replaced by. One lookup yields three outcomes: **active** — rotate it; **superseded or revoked** — this credential has been presented before, so revoke the whole family by its indexed `family_id`; **absent** — reject, and note you have no family to kill. The critical rule is that rotation never deletes the old row. Delete it and a replay becomes indistinguishable from noise, which is precisely the case you built the table to catch.

code

pseudocode · 25 lines

pseudocode

row = credentials.find_by_digest(hash(presented))   # unique index: one probe

if row is null:
    metrics.increment("refresh.unknown")            # not reuse: nothing to kill
    reject("unknown credential")

if row.state == ACTIVE:
    row.state = SUPERSEDED
    successor = mint_in_family(row.family_id)
    row.replaced_by = successor.digest
    return successor

# row.state is SUPERSEDED or REVOKED -> already presented once
successor = credentials.find(row.replaced_by)
if row.state == SUPERSEDED
   and successor.last_presented_at is null
   and now - row.superseded_at < GRACE
   and not row.grace_used:
    row.grace_used = true                            # single use per row
    metrics.increment("refresh.lost_ack")
    return mint_in_family(row.family_id)

credentials.revoke_family(row.family_id)             # indexed ranged update
metrics.increment("refresh.reuse_detected")
reject("credential already presented")

go deeper

for a junior

Know the three outcomes of the lookup and what each means: active means rotate, already-spent means replay, absent means reject. That vocabulary carries most of a first conversation about rotation.

for a middle

Design the artefact: name the columns, say which are indexed, and explain why deleting the superseded row destroys the very signal you are building. Pick a grace window and say what it trades.

for a senior

Talk about the fleet: what reuse detection firing on a lost acknowledgement costs in field visits, how you alarm on the family-kill rate rather than on each event, and how you keep the absent-digest counter out of that alarm.

for a principal

Decide who owns the grace window and the family-kill rate as operational numbers, and whether a fleet that trips them regularly is telling you the link budget, not the security model, is wrong.

The row

Using illustrative column names, one row per credential:

credential_digest — the one-way digest of the credential, with a unique index;
family_id — an identifier minted with the first credential of a lineage and copied into every successor, indexed;
device_id and subject — who this lineage belongs to;
state — active, superseded or revoked;
replaced_by — the digest or row id of the successor issued when this one was rotated;
issued_at, expires_at, last_presented_at.

Why the digest is the index key

The presented credential hashes to exactly one value, and that value is the key. Detection is therefore a B-tree probe, not a table scan, and it stays a probe when the fleet grows. This is the whole reason the digest is a plain deterministic hash rather than a per-row salted one: a salted column cannot be an index key for a value you have only just received.

One lookup, three outcomes

lookup result	what it means	what the server does
row found, `state = active`	the expected case	mark it `superseded`, set `replaced_by`, issue the successor in the same family
row found, `state` superseded or revoked	this credential has been presented before	revoke every row with that `family_id`, reject the request, raise the event
no row	forged, or purged long ago	reject; there is no family to kill and nothing to alarm on beyond a counter

The third row is the honest limit of the design: an absent digest cannot be distinguished from a credential that expired and was purged weeks ago, so it must be counted separately from reuse or your alerting drowns.

Never delete the superseded row

The instinct on rotation is to delete the spent row — it is used, after all. Do that and a replayed credential lands in the absent branch: the server sees an unknown value, rejects it, and learns nothing. Keeping the superseded row until its own expires_at is what converts a replay from noise into a signal with a family attached. Purging is a scheduled job driven by expires_at, not by rotation.

Killing the family is one indexed write

Because family_id is indexed, revoking a lineage is a single ranged update over a handful of rows — the depth of one sensor's rotation history, not the whole fleet. Without that index the kill degrades into a scan on the worst possible day, when something has already gone wrong.

The retry that is not a thief, answered by the row

On a weak radio link, the classic false positive is a sensor whose acknowledgement was lost: the server rotated, the successor never arrived, and the sensor retries with a credential the server has already burned. The row can distinguish that case without any guessing:

The superseded row names its successor in replaced_by.
If that successor has never been presented and the supersede happened within a short grace window — seconds, not minutes — the acknowledgement was probably lost in transit. Issue a fresh successor inside the same family and leave the family alive.
If the successor has been presented, two parties hold live credentials in one lineage. That is a genuine replay: revoke the family.

Make the grace single-use per row. A second presentation of the same superseded credential kills the family regardless, which bounds what an attacker gains from racing into the window: within those seconds a thief can obtain one successor, and the sensor's next retry then collapses the lineage and forces re-enrolment. That residual exposure is the price of not logging out working hardware on every dropped packet, and it is a number an operator should set deliberately rather than inherit.

Rotation on every use is only a security control if the server can tell that a credential has already been spent. On a fleet of forty thousand sensors that check runs on every wake, over a link that retries, so the design question is not *whether* to detect reuse but what row shape makes the detection a single key lookup. ## The row Using illustrative column names, one row per credential: - `credential_digest` — the one-way digest of the credential, with a **unique index**; - `family_id` — an identifier minted with the first credential of a lineage and copied into every successor, **indexed**; - `device_id` and `subject` — who this lineage belongs to; - `state` — `active`, `superseded` or `revoked`; - `replaced_by` — the digest or row id of the successor issued when this one was rotated; - `issued_at`, `expires_at`, `last_presented_at`. ## Why the digest is the index key The presented credential hashes to exactly one value, and that value is the key. Detection is therefore a B-tree probe, not a table scan, and it stays a probe when the fleet grows. This is the whole reason the digest is a plain deterministic hash rather than a per-row salted one: a salted column cannot be an index key for a value you have only just received. ## One lookup, three outcomes | lookup result | what it means | what the server does | |---|---|---| | row found, `state = active` | the expected case | mark it `superseded`, set `replaced_by`, issue the successor in the same family | | row found, `state` superseded or revoked | this credential has been presented before | revoke every row with that `family_id`, reject the request, raise the event | | no row | forged, or purged long ago | reject; there is no family to kill and nothing to alarm on beyond a counter | The third row is the honest limit of the design: an absent digest cannot be distinguished from a credential that expired and was purged weeks ago, so it must be counted separately from reuse or your alerting drowns. ## Never delete the superseded row The instinct on rotation is to delete the spent row — it is used, after all. Do that and a replayed credential lands in the *absent* branch: the server sees an unknown value, rejects it, and learns nothing. Keeping the superseded row until its own `expires_at` is what converts a replay from noise into a signal with a family attached. Purging is a scheduled job driven by `expires_at`, not by rotation. ## Killing the family is one indexed write Because `family_id` is indexed, revoking a lineage is a single ranged update over a handful of rows — the depth of one sensor's rotation history, not the whole fleet. Without that index the kill degrades into a scan on the worst possible day, when something has already gone wrong. ## The retry that is not a thief, answered by the row On a weak radio link, the classic false positive is a sensor whose acknowledgement was lost: the server rotated, the successor never arrived, and the sensor retries with a credential the server has already burned. The row can distinguish that case without any guessing: 1. The superseded row names its successor in `replaced_by`. 2. If that successor has **never been presented** and the supersede happened within a short grace window — seconds, not minutes — the acknowledgement was probably lost in transit. Issue a fresh successor inside the same family and leave the family alive. 3. If the successor **has** been presented, two parties hold live credentials in one lineage. That is a genuine replay: revoke the family. Make the grace single-use per row. A second presentation of the same superseded credential kills the family regardless, which bounds what an attacker gains from racing into the window: within those seconds a thief can obtain one successor, and the sensor's next retry then collapses the lineage and forces re-enrolment. That residual exposure is the price of not logging out working hardware on every dropped packet, and it is a number an operator should set deliberately rather than inherit.

What should the server do when a presented refresh credential is not found in the table at all?
Reject it and count it, but do not treat it as reuse. An absent digest is indistinguishable from a credential that expired and was purged, so there is no family to revoke and no subject to blame. Keep that counter separate from the reuse counter or the reuse signal loses its meaning.
Why index `family_id` as well as the digest?
Because the reaction to reuse is a write across the whole lineage, not a single row. With the index it is a ranged update over a few rows; without it, the kill becomes a full scan of the credential table at the exact moment you least want one.
How does the table stop growing as forty thousand sensors rotate for years?
A scheduled purge deletes rows past `expires_at`, since a credential that can no longer be accepted also cannot be replayed usefully. Rotation itself never deletes. The steady-state size is roughly the fleet size times the number of rotations that fit inside one refresh-credential lifetime.

saying these in an interview costs you the question

Delete the old row on rotation, it is spent
Scan the table for the presented value
Reuse detection runs as a nightly job over the rows
On replay, revoke that one row and leave the lineage
An unknown credential means forgery, so alert it as reuse

Which columns and index let a refresh-credential table answer "has this one been presented before?" in a single lookup?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The row

Why the digest is the index key

One lookup, three outcomes

Never delete the superseded row

Killing the family is one indexed write

The retry that is not a thief, answered by the row

▾common follow-ups3 questions

▾red flags5 flags

The row

Why the digest is the index key

One lookup, three outcomes

Never delete the superseded row

Killing the family is one indexed write

The retry that is not a thief, answered by the row

answer

go deeper