Keep your own user ID

How to do it

Make a random ID, such as a UUID, at sign-up. Store each provider login as a row with the issuer and the subject, iss and sub in OpenID Connect. Make the pair unique, because a subject is only unique within its issuer. Point every other table at your own ID. Then a new email, a second login method or a new provider changes one table.

Example

CREATE TABLE identities (
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  issuer text NOT NULL,
  subject text NOT NULL,
  PRIMARY KEY (issuer, subject)
);

export async function userIdForLogin(db, { iss, sub }) {
  const row = await db.oneOrNone('SELECT user_id FROM identities WHERE issuer = $1 AND subject = $2', [iss, sub]);
  return row?.user_id ?? null;
}

def user_id_for_login(db, iss: str, sub: str) -> str | None:
    row = db.execute("SELECT user_id FROM identities WHERE issuer = %s AND subject = %s", (iss, sub)).fetchone()
    return row[0] if row else None

How to check

Search the schema for foreign keys to an email column or a provider subject. None exist. Change a test user's email, and every record stays linked.