026 - Email OTPs

• Tanggal: 2026-08-20
• DB: mpcdb
• Ringkas: Tabel email_otps untuk kode verifikasi email (register + reset password); hash + expiry, bukan plaintext.

ALTER (jalankan di DB server)

CREATE TABLE IF NOT EXISTS email_otps (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL,
  code_hash text NOT NULL,
  attempts integer NOT NULL DEFAULT 0,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT email_otps_email_key UNIQUE (email)
);

CREATE INDEX IF NOT EXISTS idx_email_otps_expires_at ON email_otps (expires_at);

Rollback (opsional tapi disarankan)

DROP INDEX IF EXISTS idx_email_otps_expires_at;
DROP TABLE IF EXISTS email_otps;

Catatan

• Dipakai mpc-be POST /auth/otp/send, /auth/otp/verify, /auth/password/reset. Kode 4 digit, TTL 10 menit, cooldown kirim 60 detik, max 5 percobaan.
• code_hash = SHA-256(JWT_SECRET + email + kode). Jangan simpan OTP plaintext.
• Production AUTO_MIGRATE=false — jalankan ALTER ini manual di server.
• mpc-admin: model Prisma EmailOtp saja, tanpa UI.