From Passwords to Multi‑Provider OAuth: A Zero‑Downtime Auth Migration at Comet

Context and objectives

I joined Comet to refactor authentication across a split stack (Node/Express + Sequelize backend, Vue frontend). The mission explicitly required migrating from password‑based authentication to multi‑provider OAuth with account linking. My mandate: design the database split, identify breaking assumptions, plan the migration and rollout, implement, test, and deploy with near zero downtime.

Why migrate (mission scope)

This was a stated project requirement. The goals were clear: remove password handling, support multiple providers (Google, Facebook, etc.), allow linking several authentication accounts to one user, and decouple identity from email. I focused on delivering the architecture and execution to meet those constraints, even where legacy paths felt brittle becasue they assumed email as the primary key.

Architecture and data model

I redesigned the identity layer by separating human identity from authentication handles.

Core design choices I made

• I split the monolithic user table into user (human identity) and account (authentication handle)
• I allowed multiple account rows per user (Google + Facebook + legacy password)
• I made email optional on user and removed uniqueness/foreign key assumptions tied to email
• I introduced provider‑agnostic identifiers: (provider, providerAccountId) as a unique key in account

These choices changed both schema and mental model across services and UI.

Data model at a glance

• user: id, createdAt, updatedAt, profile (name, avatar, etc.), optional email
• account: id, userId (FK), provider, providerAccountId (unique per provider), metadata (scopes, tokens)
• Legacy password credentials retained as a dedicated account type for a gradual phase‑out

The account table became the join point from authentication to user identity so all providers—including legacy password—converge on the same pipeline. I still like how clean this model reads, even if a few early edge cases were a bit of a pain teh first week.

OAuth adapters and identity normalization

I used generic OAuth 2.0 clients and wrote thin provider adapters: authorization code flow, state/nonce, PKCE when available, token exchange, and a uniform (provider, providerAccountId) identity. I normalized provider profiles (names/avatars) into a consistent shape and treated providers as attachable login handles, not sources of truth for profile fields.

Migration strategy and execution

I designed and executed a zero‑/low‑downtime rollout with reversible steps and clear operator runbooks.

Pre‑migration: schema and gates

• I added the new tables and constraints behind capability flags
• I scheduled any schema locks/index builds during low‑traffic windows to reduce downtmie risk
• I made email optional and removed email‑based uniqueness dependencies in code paths

Backfill and dual‑read/write

• I backfilled account rows for existing users (one per legacy password)
• I dual‑read/dual‑wrote during the transition so both paths stayed consistent
• I added idempotent upserts to avoid duplicate account entries under concurrency

Feature‑flagged rollout

• Backend: prefer account joins when the flag is on, fall back to legacy otherwise
• Frontend (Vue): enable “Continue with …” providers gated by user cohorts
• I owned exposure dashboards and widened traffic as metrics stayed green

Account linking flows

• Link on first OAuth sign‑in if a session exists
• If a different session exists, prompt for confirmation and merge (audit‑logged)
• Resolve conflicts when providers return the same email across distinct users

I designed the merge logic to be idempotent with strong audit trails. It felt wierd to users if too much was silent; explicit prompts reduced support.

Cutover and rollback drills

• Cutover steps: enable new sign‑in, complete backfill, switch reads to prefer account, disable new legacy passwords
• Each step had a pre‑computed rollback: drop new traffic, replay differential copy‑backs, re‑enable old code paths
• I rehearsed rollbacks as production runbooks, not theoretical docs

Minimizing user impact

• Session continuity: existing cookies remained valid; new logins issued compatible sessions
• Consistent UX: same entry points, clearer provider options
• No hard email requirement: users could authenticate without exposing email

I rate‑limited corner cases (e.g., rapid provider switching) to keep the experience stable under load.

Operations, observability, and outcomes

Safeguards and metrics I put in place

• Metrics for link rates, duplicate‑email conflicts, token exchanges, error codes per provider
• Dedicated alert for spikes in unlinked sign‑ins
• Audit events for merges and unlink operations for forensics

Monitoring paid for itself quickly; provider‑specific quirks surfaced early, especially around token lifetimes and scope changes.

Results

• Safer authentication with reduced password handling
• Faster sign‑in rates and lower reset‑ticket volume
• A normalized identity layer enabling future SSO

Final note

At the time, plug‑and‑play open‑source options didn’t match the constraints, so I chose to build this in‑house on top of generic OAuth clients. That kept control where it mattered and avoided a long vendor evaluation cycle.