40 lines
1.5 KiB
SQL
40 lines
1.5 KiB
SQL
-- ============================================================
|
|
-- Migration 048: 创建邮件验证码表
|
|
--
|
|
-- 存储邮箱验证码记录，支持注册/登录/重置密码/换绑邮箱等场景。
|
|
-- 与 sms_verifications 结构对称。
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS email_verifications (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
email VARCHAR(100) NOT NULL,
|
|
code VARCHAR(255) NOT NULL,
|
|
type VARCHAR(20) NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
verified_at TIMESTAMPTZ,
|
|
attempts INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_verif_email_type ON email_verifications (email, type);
|
|
CREATE INDEX IF NOT EXISTS idx_email_verif_expires ON email_verifications (expires_at);
|
|
|
|
-- ============================================================
|
|
-- Migration 049: 创建邮件发送日志表
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS email_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id UUID,
|
|
email VARCHAR(100) NOT NULL,
|
|
type VARCHAR(20) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
|
provider VARCHAR(50),
|
|
provider_id VARCHAR(200),
|
|
error_msg TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_logs_email ON email_logs (email);
|
|
CREATE INDEX IF NOT EXISTS idx_email_logs_created ON email_logs (created_at);
|