Credential Indexing and Database Schema Design

Aligning your relational schema with the WebAuthn specification is the foundation of reliable, auditable passkey infrastructure. The decisions made here — binary column types, index selectivity, COSE field constraints, lifecycle columns — cascade directly into authentication latency, signCount replay detection, and regulatory compliance. This page is part of Backend Verification and Secure Credential Storage, which covers the full server-side verification pipeline.

Concept Definition and Spec Grounding

WebAuthn defines a public key credential as the tuple (credentialId, credentialPublicKey, signCount) returned during the registration ceremony (W3C WebAuthn §6.1). Each field has strict type semantics that must be honoured in your persistence layer:

The credentialId is opaque binary output from the authenticator — it must not be decoded, normalised, or truncated before storage. Base64url encoding is only used at the transport layer (JSON payload); by the time data reaches your persistence layer, it must be raw Buffer/BYTEA.

The aaguid (Authenticator Attestation GUID) is a 16-byte identifier that maps to an authenticator model in the FIDO Alliance Metadata Service (MDS3). Store it even if you do not currently query it — retroactive MDS3 lookups and enterprise authenticator policy enforcement depend on it.

Base64url-to-binary conversion

/**
 * Converts a base64url-encoded WebAuthn payload to a raw binary Buffer.
 * Call this before any database write — never persist the base64url string.
 * Node.js Buffer.from() handles the URL-safe alphabet natively.
 */
export function base64urlToBuffer(value: string): Buffer {
  return Buffer.from(value, 'base64url');
}

export function bufferToBase64url(buf: Buffer): string {
  return buf.toString('base64url');
}

Architecture and Data Flow

The diagram below shows how a registration payload flows from the WebAuthn client response into the credential schema, and how the same schema fields are read back during the authentication ceremony.

Implementation Guide

Step 1 — Create the core credentials table (§6.1 compliance)

The DDL below maps each WebAuthn spec field to its correct PostgreSQL type. The alg column is constrained to known COSE algorithm identifiers so that algorithm downgrade attacks are rejected at the persistence layer rather than in application code.

CREATE TABLE credentials (
  id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  credential_id BYTEA       NOT NULL,
  public_key    BYTEA       NOT NULL,                    -- full COSE_Key CBOR map
  alg           INTEGER     NOT NULL
                            CHECK (alg IN (-7, -8, -257, -35, -36, -37, -38, -39)),
  aaguid        UUID,                                    -- nullable: not all flows provide it
  transports    TEXT[]      NOT NULL DEFAULT '{}',
  sign_count    INTEGER     NOT NULL DEFAULT 0,
  backup_eligible BOOLEAN   NOT NULL DEFAULT FALSE,      -- WebAuthn §6.1 BE flag
  backup_state    BOOLEAN   NOT NULL DEFAULT FALSE,      -- WebAuthn §6.1 BS flag
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  revoked_at    TIMESTAMPTZ,                             -- soft-delete; NULL = active
  revoked_reason TEXT,
  CONSTRAINT uq_credential_id UNIQUE (credential_id)
);

Spec annotations:

• backup_eligible / backup_state map to the BE and BS flags in the authenticatorData byte layout (§6.1, bits 3 and 4 of the flags byte). These are required for passkey sync detection.
• alg IN (-7, -8, -257, ...) — COSE algorithm registry (IANA COSE Algorithms). -7 = ES256, -8 = EdDSA, -257 = RS256.
• The aaguid column is nullable because none-attestation and indirect-attestation flows do not yield a verified AAGUID.

Step 2 — Apply B-tree, partial, and GIN indexes

Index selection for the credentials table is unusual because the read pattern is asymmetric: authentication lookups are an exact equality match on credential_id, while management queries scan by user_id and filter by revocation status.

-- Primary authentication path: exact binary equality on credential_id
-- The partial filter eliminates revoked rows from the index entirely
CREATE UNIQUE INDEX idx_credentials_active
  ON credentials (credential_id)
  WHERE revoked_at IS NULL;

-- User management queries: list all credentials for a user
CREATE INDEX idx_credentials_user_id
  ON credentials (user_id);

-- Covering index for the full authentication hot path — avoids heap fetch
-- for the public_key and sign_count columns (PostgreSQL 11+)
CREATE INDEX idx_credentials_lookup
  ON credentials (credential_id)
  INCLUDE (public_key, sign_count, alg)
  WHERE revoked_at IS NULL;

-- AAGUID lookups for MDS3 policy enforcement or revocation by model
CREATE INDEX idx_credentials_aaguid
  ON credentials (aaguid)
  WHERE aaguid IS NOT NULL;

-- GIN index for transport-aware credential selection
CREATE INDEX idx_credentials_transports
  ON credentials USING GIN (transports);

-- Partial index for recently created credentials (migration validation window)
CREATE INDEX idx_credentials_recent
  ON credentials (created_at DESC)
  WHERE created_at > NOW() - INTERVAL '30 days';

Step 3 — Enforce signCount monotonicity

The WebAuthn spec (§6.3.3, step 17) requires the RP to compare the incoming signCount from the authenticator against the stored value and reject authentication if the stored value is greater than or equal to the incoming value (for authenticators that support counters). A database trigger provides a defence-in-depth guarantee independent of application code:

CREATE OR REPLACE FUNCTION enforce_sign_count_monotonic()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Allow update only if the new sign_count is strictly greater than current,
  -- OR if both are 0 (authenticator does not implement counter, per §6.3.3).
  IF NEW.sign_count != 0 AND OLD.sign_count != 0
     AND NEW.sign_count <= OLD.sign_count THEN
    RAISE EXCEPTION
      'sign_count regression: stored=%, incoming=%. Possible cloned authenticator.',
      OLD.sign_count, NEW.sign_count;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sign_count_monotonic
  BEFORE UPDATE OF sign_count ON credentials
  FOR EACH ROW EXECUTE FUNCTION enforce_sign_count_monotonic();

Step 4 — Row-level security for multi-tenant isolation

When your RP serves multiple organisations, credential_id uniqueness across the full table is insufficient. Enable PostgreSQL RLS to ensure that queries from one tenant’s application user cannot read or modify another tenant’s credentials:

ALTER TABLE credentials ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own credentials
CREATE POLICY credentials_user_isolation
  ON credentials
  USING (user_id = current_setting('app.current_user_id')::UUID);

-- Policy: service accounts with elevated role bypass RLS for admin operations
CREATE POLICY credentials_service_bypass
  ON credentials
  TO service_role
  USING (TRUE);

Set app.current_user_id in your connection wrapper before executing any credential query:

await db.query(`SET LOCAL app.current_user_id = $1`, [userId]);
const credential = await db.query(
  `SELECT credential_id, public_key, sign_count, alg
   FROM credentials
   WHERE credential_id = $1 AND revoked_at IS NULL`,
  [credentialIdBuffer]
);

Step 5 — Prisma schema with correct type mapping

model Credential {
  id             String    @id @default(uuid()) @db.Uuid
  userId         String    @db.Uuid
  credentialId   Bytes                              // BYTEA — raw binary, not base64
  publicKey      Bytes                              // BYTEA — full COSE_Key CBOR map
  alg            Int                                // COSE algorithm ID: -7, -8, -257
  aaguid         String?   @db.Uuid                // nullable: none/indirect attestation
  transports     String[]  @default([])
  signCount      Int       @default(0)
  backupEligible Boolean   @default(false)         // authenticatorData BE flag
  backupState    Boolean   @default(false)         // authenticatorData BS flag
  revokedAt      DateTime?
  revokedReason  String?
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([credentialId])
  @@index([userId])
  @@index([aaguid])
}

Validation Checklist

• credential_id column is BYTEA, not TEXT or VARCHAR
• public_key column stores the raw COSE-encoded key bytes, not a decoded integer or PEM string
• alg column has a CHECK constraint listing only the COSE algorithm IDs your RP accepts
• aaguid is stored and not discarded, even if not queried today
• backup_eligible and backup_state columns are present for passkey sync detection
• sign_count has a monotonicity check (trigger or application guard)
• A partial index on credential_id WHERE revoked_at IS NULL exists for the authentication hot path
• A covering INCLUDE (public_key, sign_count, alg) index eliminates heap fetches on the auth path
• RLS or application-layer scoping prevents cross-tenant credential reads
• Cascade ON DELETE CASCADE on user_id FK is present so user deletion does not orphan credentials
• revoked_at and revoked_reason columns are present for soft-delete and audit trail

Error Reference Table

Platform and Library Notes

@simplewebauthn/server returns credentialID as a Uint8Array. Convert with Buffer.from(credentialID) before passing to pg — the pg driver accepts Buffer for BYTEA columns but not Uint8Array directly in all versions.

fido2-lib returns id as a base64url string in the assertion result. Always call base64urlToBuffer() before persistence.

py_webauthn (Python) returns credential_id as bytes. Use psycopg2.Binary(credential_id) or the bytes type with asyncpg — both map to PostgreSQL BYTEA correctly.

WebAuthn4J (Java/Kotlin) maps credentialId to byte[]. Use PreparedStatement.setBytes() or Spring Data’s @Lob with byte[] — avoid mapping to String with any encoding.

iOS passkeys (iCloud Keychain) set backup_eligible = true and backup_state = true and report transports = ['internal', 'hybrid']. Android passkeys (Google Password Manager) behave similarly. Your index on transports using GIN allows filtering by transport type for policy decisions.

Windows Hello does not typically set rk (resident key) flag for platform authenticators registered without explicit discoverable credential policy. The backup_eligible and backup_state flags are both false for Windows Hello FIDO2 platform credentials.

Index Selectivity Validation

Run this query after deploying to staging to confirm your indexes are actually being used:

SELECT
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  CASE
    WHEN idx_scan = 0    THEN 'UNUSED'
    WHEN idx_scan < 100  THEN 'LOW_USAGE'
    ELSE                      'ACTIVE'
  END AS health_status
FROM pg_stat_user_indexes
WHERE tablename = 'credentials'
ORDER BY idx_scan DESC;

An UNUSED result on idx_credentials_active after load testing indicates the query planner is not selecting the partial index — check that your authentication query uses WHERE revoked_at IS NULL explicitly and that table statistics are current (ANALYZE credentials).

Pitfalls and Security Hardening

1. Storing credential_id as base64url TEXT Root cause: copying the id field from the JSON payload directly into a VARCHAR column. During authentication, the incoming rawId (binary) is re-encoded to base64url for the JSON transport, but the stored TEXT value may use standard base64 (with + and /) or have different padding. Equality comparisons silently fail. Mitigation: decode to Buffer/BYTEA at the registration endpoint before any persistence call.

2. Missing backup_eligible and backup_state columns Root cause: implementing against an older WebAuthn Level 2 spec without the BE/BS flags (added in Level 3). Passkeys that sync via iCloud or Google Password Manager will have these flags set; ignoring them means you cannot distinguish a roaming hardware key from a synced passkey, breaking enterprise authenticator policy. Mitigation: add both columns with DEFAULT FALSE; populate from authenticatorData flags byte bit 3 and bit 4 during registration.

3. Hard-deleting credentials on user account deletion Root cause: ON DELETE CASCADE without revoked_at means the credential record is gone before audit logging captures the deletion event. Mitigation: use a deferred deletion pattern — set revoked_at = NOW() on account deletion, run a scheduled archival job after the GDPR Article 17 retention minimum, then hard-delete. See Credential Revocation and Account Recovery for the full revocation workflow.

4. No covering index — heap fetch on every authentication Root cause: a bare CREATE INDEX ON credentials (credential_id) forces PostgreSQL to fetch public_key and sign_count from the heap after the index lookup, adding a second I/O round-trip per authentication. Mitigation: use CREATE INDEX ... INCLUDE (public_key, sign_count, alg) WHERE revoked_at IS NULL to make the authentication query index-only.

5. Omitting AAGUID from the schema Root cause: treating AAGUID as an attestation curiosity rather than an operational field. FIDO Alliance MDS3 publishes security advisories keyed on AAGUID — if you do not store it, you cannot perform retroactive revocation when a specific authenticator model is compromised. Mitigation: store aaguid as a nullable UUID column and index it. Cross-validate against MDS3 during attestation statement validation.

6. signCount stored as BIGINT without monotonicity enforcement Root cause: assuming the application layer will always perform the comparison. When multiple services write to the same database, or when a migration script updates rows, the trigger guard is bypassed. Mitigation: the database trigger in Step 3 above provides the defence-in-depth guarantee. Complement it with application-layer validation in your authentication verification logic.

---

Related

• Implementing Credential ID Lookup at Scale — B-tree vs. hash index benchmarks, PgBouncer tuning, and cache-aside patterns for sub-50ms credential_id resolution at high RPS
• Handling Public Key Storage and Rotation — COSE key lifecycle management, rotation triggers, and schema migration patterns for algorithm deprecation
• Credential Revocation and Account Recovery — soft-delete schema patterns, GDPR Article 17 retention, and recovery ceremony design
• Designing Secure Registration Endpoints — full registration pipeline including attestationObject parsing, rpId validation, and idempotency keys
• Implementing Authentication Verification Logic — signCount comparison, signature verification, and session token issuance after successful credential lookup