Modelling Multi-Device Passkeys in Your Schema

An account with passkeys is not an account with devices, and the difference shapes the schema. A synced credential lives on several devices while remaining one row; a hardware key holds several credentials while remaining one device. This page covers the entities, the relationships that actually exist, and how to build a settings page from data that does not quite describe what users think they have. It sits under Credential Indexing and Database Schema Design.

Entity Reference

Entity Cardinality Keyed by Notes
account one your own identifier holds the opaque user handle
credential many per account credential id, unique the authentication lookup
session many per credential session or refresh id carries the credential reference
device not modelled — inferred from labels, never authoritative

The last row is the important one. There is no device entity, because nothing in the protocol gives you a device identity to key it on.

One account, several credentials, several devices The schema has to express a relationship that is one-to-many in two different ways at once. Four entities; the credential sits at the centre account one row per person or organisation member credential many per account; the authentication lookup key device context attachment, transports and backup state, per credential session many per credential; carries the credential reference

Root Cause Analysis

1. Modelling devices as first-class rows. Tempting, because it matches how users talk, and unworkable because you have no device identifier. A synced credential appears on several devices; a hardware key holds several credentials. Any device table you build is a guess maintained by hand.

2. Keying credentials by account. Authentication starts from a credential identifier supplied by the client and must resolve in one indexed read. A schema whose primary access path is the account cannot serve a usernameless ceremony at all.

3. Storing the user handle on the credential instead of the account. It identifies the account, must be identical across every credential the account holds, and belongs on the account row. Duplicating it invites divergence, and divergence means a usernameless ceremony that resolves to nothing.

4. Not recording device context. Attachment, transports and the backup bits are the only material available for labelling a credential in a way a user can recognise. Discarding them at registration leaves the settings page with four identical rows called “Passkey”.

What a synced passkey does to the model One credential can be present on several devices, so device count and credential count are not the same number. Credentials are not devices, and the difference matters one credential one key pair, one row platform sync copied to the user account many devices all present the same credential your database still one row Which is why "devices on this account" cannot be answered from the credential table alone — and usually should not be asked.

Step-by-Step Resolution

Step 1 — put the user handle on the account

One opaque, stable, non-personal identifier per account, shared by every credential.

Step 2 — make the credential the central entity

Unique index on the credential identifier, foreign key to the account, and every ceremony fact recorded at registration.

Step 3 — record the context you will need for labelling

Reported attachment, transports, the backup bits, the resolved model name if attestation supplied one, and the creation time.

Step 4 — let users name credentials

A nullable label column, editable from account settings. It will beat any name you derive.

Step 5 — reference the credential from the session

A session knows which credential minted it, which is what makes “revoke this passkey and end its sessions” possible.

Step 6 — track last use

A timestamp updated on successful assertion. It drives the settings page ordering, the archival sweep and most support conversations.

Two questions your settings page must answer Users think in devices; your schema thinks in credentials, and the gap is filled by labels rather than by data. The gap between the two is the labelling problem the row knows when it was created when it was last used its attachment and transports whether it is backed up the user needs "which of my devices is this?" "can I remove it safely?" "is this the one I lost?" a name they chose Let the user rename credentials; no derived label will ever beat the name they recognise.

Verification and Testing

Model the two awkward cases explicitly in test data: one account with a synced credential and one with several credentials on a single hardware key. Assert that the settings page renders sensibly for both, because they are the shapes that break a naive device-oriented design.

Assert that revoking one credential leaves the others usable and ends only the sessions that credential minted. This is the single most valuable schema test, because it exercises the credential-to-session reference that the design exists to support.

Check the query plan for the authentication path against a table with a realistic row count. A schema that performs well with a hundred credentials and scans with a million is a schema whose index is not doing what you think.

Pitfalls

1. Deriving a device count from credentials. They are not the same number in either direction.

2. Cascading deletes from the account. Retention and audit usually want the rows to outlive an account deletion by a documented period.

3. Unbounded credential growth. A settings page nobody can use produces accounts with dozens of rows and oversized exclude lists.

Frequently Asked Questions

Should I store which device a credential was created on?

You can store what the ceremony reported — attachment, transports and a user agent if you must — but treat it as a label rather than as identity. None of it is stable, none of it is verified, and a synced credential will be used from devices that had nothing to do with its creation.

How do I show “last used on” per device?

You cannot, reliably. What you can show is when the credential was last used, which is the honest version of the same information. Attempting to attribute a use to a particular device requires signals the protocol deliberately withholds.

Should credentials be soft-deleted or removed?

Soft-deleted, with a revocation timestamp and reason, then purged on your normal retention schedule. The row is needed for the audit trail and for keeping revoked entries out of exclude lists; the identifying fields should not outlive the retention window.

Does an account need a limit on credential count?

A practical one, yes — not for storage reasons but because oversized exclude lists degrade ceremonies on constrained transports. A ceiling in the low tens, combined with an archival sweep for credentials unused for a long period, keeps the numbers sane without ever telling a user they have too many passkeys.

Where should the backup bits live?

On the credential row, updated on every assertion where they change. They describe the credential rather than the account, they move over time, and they are what tells you whether an account genuinely needs a second credential.

How does this interact with organisations and shared accounts?

The credential still belongs to a person; the account may belong to an organisation. Model membership separately and let a credential reference the membership rather than the organisation, so revoking a person’s access does not require reasoning about which of an organisation’s credentials were theirs.

How should the schema handle a user who signs in on a brand-new machine?

It should not need to do anything special, and that is the point of modelling credentials rather than devices. A synced passkey presented from a machine your database has never seen produces exactly the same assertion as one presented from a familiar machine, resolves to the same credential row, and updates the same last-used timestamp. If your design has anywhere that needs to “register” a device before a sign-in can proceed, that is a signal the device concept has crept into a place the protocol does not support it, and the usual result is a user who cannot sign in on the laptop they bought yesterday.

The same principle applies in reverse when a user removes a device from their platform account: nothing reaches your server, the credential row is unchanged, and the credential may simply stop appearing in that person’s chooser. Designing the settings page so that removal is always available from your side — rather than assuming the platform will tell you — is what keeps the two views from drifting apart.

Related