The Identity Data Model

Separate the person from the ways they sign in. A user is one row. Identifiers, credentials, and federated links are many rows that point to it. Teams that put email and password_hash on the user table spend years undoing it.

The core entities

Entity	What it is	Key rules
Tenant	An organization or environment (for B2B, one customer; for B2C, often one)	Every other row carries tenant_id
User	The person or service account	Opaque random ID, never an email or sequence number
Identifier	Email, phone, or username used to find the user	Many per user; unique per tenant once verified
Password credential	One hashed password	Zero or one per user; stores algorithm and parameters
WebAuthn credential	One passkey or security key	Many per user; credential ID unique per relying party
TOTP factor	One authenticator app enrollment	Secret encrypted with a KMS key
Recovery code	Single-use backup code	Stored hashed, marked used, never shown twice
Federated identity	A link to Google, Apple, or an enterprise IdP	Unique on (provider, subject), never on email
Session	A signed-in browser or device	Revocable; tied to device and authentication level
Refresh token	Long-lived OAuth credential	Stored hashed, rotated, grouped in families
Audit event	Anything security-relevant	Append-only, never updated

A starting schema

This is PostgreSQL. Adapt types to your database, but keep the shape.

CREATE TABLE tenants (
  id            uuid PRIMARY KEY,
  slug          text UNIQUE NOT NULL,
  data_region   text NOT NULL,            -- 'us', 'eu', 'in', ...
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id            uuid PRIMARY KEY,         -- random, not sequential
  tenant_id     uuid NOT NULL REFERENCES tenants(id),
  data_region   text NOT NULL,
  status        text NOT NULL DEFAULT 'active', -- active, locked, deleted
  created_at    timestamptz NOT NULL DEFAULT now(),
  deleted_at    timestamptz
);

CREATE TABLE identifiers (
  id            uuid PRIMARY KEY,
  user_id       uuid NOT NULL REFERENCES users(id),
  tenant_id     uuid NOT NULL,
  kind          text NOT NULL,            -- email, phone, username
  value_norm    text NOT NULL,            -- normalized for lookup
  value_display text NOT NULL,            -- as the user typed it
  verified_at   timestamptz,
  is_primary    boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX identifiers_verified_unique
  ON identifiers (tenant_id, kind, value_norm)
  WHERE verified_at IS NOT NULL;

CREATE TABLE password_credentials (
  user_id       uuid PRIMARY KEY REFERENCES users(id),
  hash          text NOT NULL,            -- PHC string: $argon2id$v=19$m=...
  updated_at    timestamptz NOT NULL,
  compromised   boolean NOT NULL DEFAULT false
);

CREATE TABLE webauthn_credentials (
  credential_id bytea PRIMARY KEY,
  user_id       uuid NOT NULL REFERENCES users(id),
  public_key    bytea NOT NULL,           -- COSE key
  sign_count    bigint NOT NULL DEFAULT 0,
  aaguid        uuid,
  transports    text[],
  backup_eligible boolean,
  backup_state  boolean,
  nickname      text,
  created_at    timestamptz NOT NULL DEFAULT now(),
  last_used_at  timestamptz
);

CREATE TABLE federated_identities (
  id            uuid PRIMARY KEY,
  user_id       uuid NOT NULL REFERENCES users(id),
  provider      text NOT NULL,            -- issuer URL or connection ID
  subject       text NOT NULL,            -- 'sub' or SAML NameID
  UNIQUE (provider, subject)
);

CREATE TABLE sessions (
  id_hash       bytea PRIMARY KEY,        -- hash of the session token
  user_id       uuid NOT NULL REFERENCES users(id),
  aal           smallint NOT NULL,        -- 1, 2, or 3
  amr           text[] NOT NULL,          -- pwd, otp, hwk, swk ...
  auth_time     timestamptz NOT NULL,
  last_seen_at  timestamptz NOT NULL,
  expires_at    timestamptz NOT NULL,
  device_label  text,
  revoked_at    timestamptz
);

Rules that save you later

1. Normalize identifiers once, in one function. Lowercase the domain part of emails, apply Unicode NFKC, and store E.164 for phones. Do not strip dots or plus tags from Gmail addresses unless you want to merge accounts users think are separate.
2. Uniqueness only after verification. If an unverified email blocks signups, attackers can pre-register victims' addresses.
3. Store the algorithm with every hash. Use the PHC string format so you can migrate later (Chapter 4).
4. Record how the user authenticated. Store authentication level and methods on the session, not just the user ID. Step-up decisions need it.
5. Soft delete, then hard delete on a schedule. Privacy laws require real deletion; incident response needs a short grace period. Write down both timelines.
6. Never use email as a foreign key. Emails change. User IDs do not.

Key takeaways

• A user is one row; identifiers, credentials, and federated links are many rows that point to it.
• Enforce identifier uniqueness only after verification, or attackers pre-register victims' emails.
• Store the hash algorithm and parameters with every password, and the auth level on every session.
• Never use email as a key or a foreign key. Emails change; user IDs do not.