asd-backend/docs/MIGRATIONS.md

数据库迁移

概述

所有数据库结构变更通过 SQL 迁移脚本管理，存储在 migrations/ 目录，按序号命名。

部署时 deploy.sh 自动检测并执行未运行的迁移：

upload_dir "迁移文件" migrations    → 上传到服务器
run_migrations                       → 逐条执行未迁移的脚本

已执行的迁移记录在数据库 _migrations 表中。

迁移文件清单

序号	文件	操作	说明
001	001_add_payment_fields.sql	ALTER TABLE users ADD COLUMN	添加 is_member, is_admin, membership_expires_at 字段
002a	002_add_payment_orders.sql	CREATE TABLE	创建 payment_orders 表
002b	002_add_user_profile_fields.sql	ALTER TABLE users ADD COLUMN	添加 avatar_url, nickname 字段
003	003_set_all_users_paid.sql	SELECT 1（空操作）	历史遗留：原将所有用户设为付费，已改为空操作
004	004_add_refresh_tokens.sql	CREATE TABLE	创建 refresh_tokens 表（双 Token 机制）
005	005_add_web_login_codes.sql	CREATE TABLE	创建 web_login_codes 表（网页端扫码登录）
006	006_add_performance_indexes.sql	CREATE INDEX	添加性能索引（user_id, date, is_favorite）
007	007_add_spot_check_count.sql	ALTER TABLE	weather_data 添加 spotcheckcount 字段
009	009_add_payment_audit_log.sql	CREATE TABLE	创建 payment_audit_log 表（支付审计日志）
010	010_rename_paid_fields.sql	ALTER TABLE RENAME COLUMN	is_paid → is_member, paid_expires_at → membership_expires_at
011	011_add_notifications.sql	CREATE TABLE	创建 notifications 表（通知系统）

注意：序号不连续（无 008）是因为跳过了一个已撤销的迁移。新迁移请继续使用下一可用序号（如 012）。

迁移说明

003 — 历史遗留

此迁移最初执行了 UPDATE users SET is_paid = true，将所有已有用户设为永久付费会员（用于早期测试）。发现后已改为空操作 SELECT 1 WHERE 1 = 1。已有的被修改的数据需要用脚本手动回退。

009 — 自动建表

此迁移对应的代码中有幂等 CREATE TABLE IF NOT EXISTS 逻辑，即使未通过迁移系统执行，代码也会自动建表。迁移文件本身保留以确保正式部署时 _migrations 表记录完整。

010 — 字段重命名

is_paid → is_member（会员身份凭证，即使过期也保留） paid_expires_at → membership_expires_at（NULL 表示永久会员）

如何创建新迁移

# 1. 创建迁移文件
touch migrations/012_your_description.sql

# 2. 写入 SQL
vim migrations/012_your_description.sql

# 3. 部署时自动执行
./deploy.sh production          # 会自动运行新迁移
./deploy.sh development         # 开发环境同理

迁移编写规范

规则	说明
幂等	使用 IF NOT EXISTS / IF EXISTS，可重复执行
事务	关键操作使用 BEGIN / COMMIT
回滚	考虑失败场景，避免不可逆操作
注释	文件头部写清目的、日期、依赖关系

示例：

-- ============================================
-- 迁移: 012_add_example.sql
-- 目的: 创建示例表
-- 日期: 2026-05-26
-- ============================================

CREATE TABLE IF NOT EXISTS example (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

如何手动执行迁移

在服务器上直接运行：

ssh root@1panel-server
docker exec -i 1Panel-postgresql-FtMo psql -U milkydata -d milkydata \
  < /root/rust/rust_backend/migrations/012_your_migration.sql

# 记录到 _migrations 表
docker exec 1Panel-postgresql-FtMo psql -U milkydata -d milkydata \
  -c "INSERT INTO _migrations (name) VALUES ('012_your_migration.sql');"

迁移测试

部署后 test_migration_status 会自动检查：

• _migrations 表是否存在
• 已执行的迁移数量
• 最后一次执行的迁移文件名

当前执行状态

开发库 milkydata_dev

001_add_payment_fields.sql
002_add_payment_orders.sql
002_add_user_profile_fields.sql
003_set_all_users_paid.sql
004_add_refresh_tokens.sql
005_add_web_login_codes.sql
006_add_performance_indexes.sql
007_add_spot_check_count.sql
009_add_payment_audit_log.sql
010_rename_paid_fields.sql

生产库 milkydata

_migrations 表尚未创建。缺失的迁移：

• 004 (refresh_tokens)
• 005 (web_login_codes)
• 009 (payment_audit_log)
• 011 (notifications)