38 lines
2.4 KiB
SQL
38 lines
2.4 KiB
SQL
-- ============================================
|
||
-- 证书管理功能 - 数据库迁移脚本
|
||
-- 执行日期: 2026-06-11
|
||
-- ============================================
|
||
|
||
USE nginxserver;
|
||
|
||
-- 1. 给 certificates 表增加通知相关字段
|
||
ALTER TABLE certificates
|
||
ADD COLUMN IF NOT EXISTS cert_type VARCHAR(20) DEFAULT 'internal_ca' COMMENT '证书类型: internal_ca / letsencrypt',
|
||
ADD COLUMN IF NOT EXISTS notify_email VARCHAR(500) DEFAULT NULL COMMENT '通知收件人邮箱，多个用逗号分隔',
|
||
ADD COLUMN IF NOT EXISTS notify_enabled TINYINT DEFAULT 1 COMMENT '是否启用到期通知: 0-禁用, 1-启用',
|
||
ADD COLUMN IF NOT EXISTS notify_days_before INT DEFAULT 15 COMMENT '提前多少天开始通知',
|
||
ADD COLUMN IF NOT EXISTS last_renew_time DATETIME DEFAULT NULL COMMENT '上次续期时间',
|
||
ADD COLUMN IF NOT EXISTS renew_log TEXT DEFAULT NULL COMMENT '续期/签发日志(JSON)';
|
||
|
||
-- 2. 创建证书通知发送日志表
|
||
CREATE TABLE IF NOT EXISTS cert_notify_log (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
||
cert_id BIGINT NOT NULL COMMENT '关联 certificates.id',
|
||
domain VARCHAR(255) NOT NULL COMMENT '域名',
|
||
days_left INT NOT NULL COMMENT '到期剩余天数',
|
||
email_to VARCHAR(500) NOT NULL COMMENT '收件人邮箱',
|
||
success TINYINT DEFAULT 1 COMMENT '是否发送成功: 0-失败, 1-成功',
|
||
response_msg VARCHAR(1000) DEFAULT NULL COMMENT '网关API返回信息',
|
||
sent_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
|
||
INDEX idx_cert_id (cert_id),
|
||
INDEX idx_domain (domain),
|
||
INDEX idx_sent_at (sent_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='证书到期通知发送日志';
|
||
|
||
-- 3. 插入 zgitm.com 系统证书记录（Let's Encrypt 类型）
|
||
INSERT IGNORE INTO certificates (domain, cert_type, notify_email, notify_enabled, notify_days_before, status)
|
||
VALUES ('zgitm.com', 'letsencrypt', 'admin@mokee.com', 1, 15, 'active');
|
||
|
||
INSERT IGNORE INTO certificates (domain, cert_type, notify_email, notify_enabled, notify_days_before, status)
|
||
VALUES ('www.zgitm.com', 'letsencrypt', 'admin@mokee.com', 1, 15, 'active');
|