Learn 2FA Authentication - Database Schema & Data Management

#Introduction

All the 2FA state discussed so far — the encrypted secret, the active flag, the recovery codes — lives in the database. Episode 12 designs that schema correctly: the right column types, indexes that support verification queries, constraints that preserve integrity, and migrations that can be run repeatedly.

Common schema mistakes — a secret column with too small a type, recovery codes stored as plain text, or queries that scan the whole table — will hit the application as production grows. By the end of the episode, you'll have a ready-to-use table design and a safe migration flow.

#The users Table Schema

#Columns for 2FA

The users table gains three 2FA-related columns on top of the regular authentication columns. The secret is stored encrypted, not in plaintext:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  totp_secret_encrypted TEXT,
  totp_enabled BOOLEAN NOT NULL DEFAULT false,
  last_used_step BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

totp_secret_encrypted stores the iv:tag:data string from the AES-256-GCM encryption (episode 7). The last_used_step column supports the replay protection from episode 10, and totp_enabled is the source of truth for the 2FA status.

#Why UUID and TIMESTAMPTZ

A UUID primary key avoids enumerating users from sequential ids. TIMESTAMPTZ stores time with a zone, so logic that compares time — like pending secret expiry — isn't broken by server zone differences.

#The recovery_codes Table Schema

#Hash and Single-Use Status

Recovery codes are stored one row per code, with the hash as the content:

CREATE TABLE recovery_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash TEXT NOT NULL,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX idx_recovery_codes_user ON recovery_codes(user_id);

The user_id REFERENCES users(id) ON DELETE CASCADE constraint ensures the codes are deleted when the account is deleted. The index on user_id makes finding a user's codes fast, without scanning the whole table.

#Migrating Legacy Data

If you already have a users table without the 2FA columns, the migration adds the columns with safe defaults. The new columns are null and totp_enabled is false — existing users aren't affected until they choose to enable 2FA.

#Migrations with Prisma

#Schema Definition

Prisma offers a schema that can be migrated repeatedly. The 2FA model definition:

model User {
  id                  String   @id @default(uuid())
  email               String   @unique
  passwordHash        String
  totpSecretEncrypted String?
  totpEnabled         Boolean  @default(false)
  lastUsedStep        BigInt?
  recoveryCodes       RecoveryCode[]
}
 
model RecoveryCode {
  id       String   @id @default(uuid())
  userId   String
  user     User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  codeHash String
  usedAt   DateTime?
  @@index([userId])
}

The totpSecretEncrypted String? schema is nullable because users without 2FA don't have it. The recoveryCodes relation exposes a user's codes through a foreign key.

#Running the Migration

Migrations run through Prisma commands that create the tables and track the history:

npx prisma migrate dev --name add-2fa-columns
npx prisma migrate deploy

prisma migrate dev writes migration files for development, while prisma migrate deploy applies them in production. Record the schema version in the migration history so rollbacks and collaboration between developers are easy.

#Backup and Recovery

#Encrypted Backups

Database backups must be encrypted too — the 2FA secrets inside are encrypted by the application, but a second layer protects the data when the backup is moved. Backup encryption also answers compliance requirements like SOC 2, which often demands encrypted data at rest.

#Recovery Testing Strategy

A backup without a restore test is just an illusion of security. Regularly restore to a test environment and confirm the TOTP codes still validate — this also tests that the separately stored MFA_ENCRYPTION_KEY can decrypt the backed-up data. Losing the encryption key means the secrets can't be read, so keep a key backup in a location separate from the database.

#Conclusion

Episode 12 designed the database schema for 2FA: the users table with an encrypted secret and status flag, the recovery_codes table with single-use hashes, indexes and constraints, Prisma migrations, and a tested backup policy.

The key takeaways:

• The 2FA secret is stored as an encrypted string in a TEXT column.
• last_used_step supports replay protection at the database level.
• Recovery codes are stored one row per code, as a hash.
• Use a foreign key with ON DELETE CASCADE for recovery codes.
• Prisma migrations record the schema change history.
• Encrypt backups and test recovery periodically.

In the next episode, episode 13, we will cover transport hardening: HTTPS, cookies, and CSP — installing TLS to protect TOTP codes in transit, making sure session cookies are secure, and setting a Content-Security-Policy for the enrollment page.