Database Schema

Overview

• Store users separately from identities to allow multiple providers per user.
• Enforce uniqueness on normalized_email to avoid duplicate accounts.
• Record sessions for auditability (optional if using stateless JWT only).

Tables

-- users: core account record
CREATE TABLE users (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email             TEXT,
  normalized_email  TEXT,
  display_name      TEXT,
  is_guest          BOOLEAN NOT NULL DEFAULT false,
  guest_origin      TEXT,                     -- where guest started (optional)
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK ((is_guest = true AND normalized_email IS NULL) OR is_guest = false)
);

-- Enforce deduplication on normalized email (case-insensitive) for non-guest users
CREATE UNIQUE INDEX users_normalized_email_uidx ON users (normalized_email) WHERE is_guest = false;

-- identities: external provider links (Google, etc.)
CREATE TABLE identities (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider          TEXT NOT NULL,           -- e.g., 'google'
  provider_sub      TEXT NOT NULL,           -- unique id from provider
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (provider, provider_sub)
);

-- credentials: for local login only
CREATE TABLE credentials (
  user_id           UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  password_hash     TEXT NOT NULL,
  password_updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Stateless sessions with JWT: no `sessions` table
-- Use Redis (or similar) for token blacklist/revocation if needed.

-- OTP codes stored in Redis with TTL: no `otp_codes` table
-- Use Redis keys like `otp:<normalized_email>` → hashed code with expiry and attempt counters.

Upsert Logic

-- Pseudocode with SQL hints
-- Normalize email: lower(trim(email))

-- Google login (dedup by normalized_email)
WITH incoming AS (
  SELECT lower(trim($1)) AS normalized_email, $2::text AS email
)
INSERT INTO users (email, normalized_email, is_guest)
SELECT email, normalized_email, false FROM incoming
ON CONFLICT (normalized_email) DO NOTHING;

SELECT id FROM users WHERE normalized_email = lower(trim($1));

INSERT INTO identities (user_id, provider, provider_sub)
VALUES ($user_id, 'google', $sub)
ON CONFLICT (provider, provider_sub) DO NOTHING;

-- Guest start (no email)
INSERT INTO users (is_guest, guest_origin)
VALUES (true, $origin)
RETURNING id INTO $user_id;

-- Issue JWT (no DB sessions). Optionally record audit as events table.
-- Use claims: { sub: user_id, guest: true, iat, exp }

Redis OTP Flow (pseudocode)

// Request OTP
const email = normalizeEmail(req.body.email);
const code = generateCode();
const codeHash = hash(code);
await redis.set(`otp:${email}`, JSON.stringify({ codeHash, attempts: 0 }), {
  EX: 60 * 10,
});
await sendEmail(email, code);

// Verify OTP
const raw = await redis.get(`otp:${email}`);
if (!raw) return res.status(401).end();
const state = JSON.parse(raw);
if (state.codeHash !== hash(req.body.code)) return res.status(401).end();
await redis.del(`otp:${email}`); // consume

// Upsert user and issue JWT
await db.sql`INSERT INTO users (email, normalized_email, is_guest)
VALUES (${req.body.email}, ${email}, false)
ON CONFLICT (normalized_email) DO NOTHING;`;
const user = await db.sql`SELECT id FROM users WHERE normalized_email = ${email}`;
const jwt = issueJwt({ sub: user.id, exp: days(7) });
return res.json({ token: jwt, user });

Notes

• gen_random_uuid() requires the pgcrypto extension (CREATE EXTENSION IF NOT EXISTS pgcrypto;).
• For MySQL, replace types and functions appropriately and enforce a case-insensitive unique index.