-- ============================================================
|
-- 数据库变更记录 (changeSql.sql)
|
-- 规则:每次表结构/基础数据变更,在文件【末尾追加】一个新块,
|
-- 不要修改或删除历史块。执行时按从上到下顺序依次执行新增部分。
|
-- 数据库: PostgreSQL (park_bike)
|
-- ============================================================
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-22 v3.0.1 接入抖音券核销 —— 新增抖音商品表
|
-- 作者: rk
|
-- 说明: 用于落地抖音「查询商品线上数据列表」接口同步过来的团购商品
|
-- ------------------------------------------------------------
|
|
-- 抖音商品主表
|
CREATE TABLE "douyin_product" (
|
"id" varchar(64) NOT NULL,
|
"product_id" varchar(64) NOT NULL,
|
"out_id" varchar(128),
|
"product_name" varchar(255),
|
"category" varchar(64),
|
"product_type" smallint,
|
"online_status" smallint DEFAULT 1,
|
"account_id" varchar(64),
|
"sync_date" timestamp,
|
"raw_content" text,
|
"create_date" timestamp DEFAULT CURRENT_TIMESTAMP,
|
"creator" varchar(64),
|
"edit_date" timestamp,
|
"editor" varchar(64),
|
"isdeleted" smallint DEFAULT 0,
|
PRIMARY KEY ("id")
|
);
|
CREATE UNIQUE INDEX uk_douyin_product_product_id ON "douyin_product" ("product_id") WHERE "isdeleted" = 0;
|
COMMENT ON TABLE "douyin_product" IS '抖音商品(团购)线上数据';
|
COMMENT ON COLUMN "douyin_product"."product_id" IS '抖音商品ID(业务唯一键,用于upsert)';
|
COMMENT ON COLUMN "douyin_product"."out_id" IS '外部商品ID';
|
COMMENT ON COLUMN "douyin_product"."online_status" IS '在线状态 1在线 2下线 3封禁';
|
COMMENT ON COLUMN "douyin_product"."account_id" IS '来客商户根账户ID';
|
COMMENT ON COLUMN "douyin_product"."sync_date" IS '最近同步时间';
|
COMMENT ON COLUMN "douyin_product"."raw_content" IS '抖音原始响应快照(便于追溯)';
|
COMMENT ON COLUMN "douyin_product"."isdeleted" IS '是否已删除 0未删除 1已删除';
|
|
-- 抖音商品 SKU 子表
|
CREATE TABLE "douyin_product_sku" (
|
"id" varchar(64) NOT NULL,
|
"product_id" varchar(64) NOT NULL,
|
"sku_id" varchar(64),
|
"title" varchar(255),
|
"third_sku_id" varchar(128),
|
"sku_out_id" varchar(128),
|
"market_price" bigint,
|
"groupon_type" smallint,
|
"voucher_type" smallint,
|
"create_date" timestamp DEFAULT CURRENT_TIMESTAMP,
|
"edit_date" timestamp,
|
"isdeleted" smallint DEFAULT 0,
|
PRIMARY KEY ("id")
|
);
|
CREATE INDEX idx_douyin_product_sku_pid ON "douyin_product_sku" ("product_id");
|
COMMENT ON TABLE "douyin_product_sku" IS '抖音商品SKU';
|
COMMENT ON COLUMN "douyin_product_sku"."product_id" IS '关联 douyin_product.product_id(抖音商品ID)';
|
COMMENT ON COLUMN "douyin_product_sku"."market_price" IS '市场价(分)';
|
COMMENT ON COLUMN "douyin_product_sku"."isdeleted" IS '是否已删除 0未删除 1已删除';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-22 v3.0.1 接入抖音券核销 —— 新增抖音券核销记录表
|
-- 作者: rk
|
-- 说明: 记录抖音团购券的验券(核销)/撤销流水,撤销核销与对账依赖本表
|
-- ------------------------------------------------------------
|
|
CREATE TABLE "douyin_verify_record" (
|
"id" varchar(64) NOT NULL,
|
"verify_id" varchar(64),
|
"certificate_id" varchar(64),
|
"order_id" varchar(64),
|
"origin_code" varchar(128),
|
"encrypted_code" varchar(255),
|
"poi_id" varchar(64),
|
"account_id" varchar(64),
|
"product_id" varchar(64),
|
"product_name" varchar(255),
|
"pay_amount" bigint,
|
"verify_status" smallint DEFAULT 1,
|
"verify_time" timestamp,
|
"verify_user_id" varchar(64),
|
"verify_msg" varchar(255),
|
"cancel_status" smallint DEFAULT 0,
|
"cancel_time" timestamp,
|
"cancel_user_id" varchar(64),
|
"cancel_msg" varchar(255),
|
"raw_request" text,
|
"raw_response" text,
|
"create_date" timestamp DEFAULT CURRENT_TIMESTAMP,
|
"creator" varchar(64),
|
"edit_date" timestamp,
|
"editor" varchar(64),
|
"isdeleted" smallint DEFAULT 0,
|
PRIMARY KEY ("id")
|
);
|
CREATE INDEX idx_dvr_verify_id ON "douyin_verify_record" ("verify_id");
|
CREATE INDEX idx_dvr_cert_id ON "douyin_verify_record" ("certificate_id");
|
CREATE INDEX idx_dvr_order_id ON "douyin_verify_record" ("order_id");
|
COMMENT ON TABLE "douyin_verify_record" IS '抖音券核销记录';
|
COMMENT ON COLUMN "douyin_verify_record"."verify_id" IS '验券返回的一次核销唯一标识,撤销必用';
|
COMMENT ON COLUMN "douyin_verify_record"."certificate_id" IS '券标识,撤销必用';
|
COMMENT ON COLUMN "douyin_verify_record"."encrypted_code" IS '加密券码(prepare返回,verify入参)';
|
COMMENT ON COLUMN "douyin_verify_record"."pay_amount" IS '实付金额(分)';
|
COMMENT ON COLUMN "douyin_verify_record"."verify_status" IS '核销结果 0成功 1失败';
|
COMMENT ON COLUMN "douyin_verify_record"."cancel_status" IS '撤销状态 0未撤销 1已撤销';
|
COMMENT ON COLUMN "douyin_verify_record"."raw_request" IS '请求快照(便于追溯)';
|
COMMENT ON COLUMN "douyin_verify_record"."raw_response" IS '响应快照(便于追溯)';
|
COMMENT ON COLUMN "douyin_verify_record"."isdeleted" IS '是否已删除 0未删除 1已删除';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-25 v3.0.1 抖音商品 out_id 语义变更 —— 改由管理端绑定本地套餐
|
-- 作者: rk
|
-- 说明: out_id 不再由抖音同步写入,改为管理端绑定本地套餐主键(discount.id)。
|
-- 字段结构不变,仅更新列注释(覆盖旧注释,幂等可重复执行);
|
-- 同步逻辑 upsertProduct 已停止用抖音返回的 out_id 覆盖本地值。
|
-- ------------------------------------------------------------
|
|
COMMENT ON COLUMN "douyin_product"."out_id" IS '绑定本地套餐ID(discount.id,管理端维护,抖音同步不写入)';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-25 v3.0.1 抖音验券操作日志表 —— web 端接口操作流水
|
-- 作者: rk
|
-- 说明: 记录 web 端 prepare/verify/cancel 每次调用的操作流水(谁/何时/结果/耗时/IP)。
|
-- 完整审计:含请求入参与抖音响应原文;核销业务数据仍由 douyin_verify_record 承载。
|
-- ------------------------------------------------------------
|
|
CREATE TABLE "douyin_verify_log" (
|
"id" varchar(64) NOT NULL,
|
"operate_type" smallint,
|
"api_path" varchar(64),
|
"member_id" varchar(64),
|
"verify_record_id" varchar(64),
|
"poi_id" varchar(64),
|
"origin_code" varchar(128),
|
"result" smallint,
|
"error_msg" varchar(500),
|
"raw_request" text,
|
"raw_response" text,
|
"ip" varchar(64),
|
"cost_ms" integer,
|
"create_date" timestamp DEFAULT CURRENT_TIMESTAMP,
|
"isdeleted" smallint DEFAULT 0,
|
PRIMARY KEY ("id")
|
);
|
CREATE INDEX idx_dvl_member ON "douyin_verify_log" ("member_id");
|
CREATE INDEX idx_dvl_record ON "douyin_verify_log" ("verify_record_id");
|
COMMENT ON TABLE "douyin_verify_log" IS '抖音验券操作日志(web端接口操作流水)';
|
COMMENT ON COLUMN "douyin_verify_log"."operate_type" IS '操作类型 0验券准备 1核销 2撤销核销';
|
COMMENT ON COLUMN "douyin_verify_log"."api_path" IS '接口路径';
|
COMMENT ON COLUMN "douyin_verify_log"."member_id" IS '操作人会员ID';
|
COMMENT ON COLUMN "douyin_verify_log"."verify_record_id" IS '关联核销记录 douyin_verify_record.id';
|
COMMENT ON COLUMN "douyin_verify_log"."poi_id" IS '核销门店';
|
COMMENT ON COLUMN "douyin_verify_log"."origin_code" IS '券码快照';
|
COMMENT ON COLUMN "douyin_verify_log"."result" IS '操作结果 0成功 1失败';
|
COMMENT ON COLUMN "douyin_verify_log"."error_msg" IS '失败描述';
|
COMMENT ON COLUMN "douyin_verify_log"."raw_request" IS '请求入参快照(JSON)';
|
COMMENT ON COLUMN "douyin_verify_log"."raw_response" IS '抖音响应原文快照';
|
COMMENT ON COLUMN "douyin_verify_log"."ip" IS '请求IP';
|
COMMENT ON COLUMN "douyin_verify_log"."cost_ms" IS '耗时(毫秒)';
|
COMMENT ON COLUMN "douyin_verify_log"."isdeleted" IS '是否已删除 0未删除 1已删除';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-25 v3.0.1 抖音核销开套餐 —— 核销记录加套餐卡ID列
|
-- 作者: rk
|
-- 说明: 核销成功开通 discount_member 后,回填套餐卡ID到核销记录,便于追溯。
|
-- 注意: goodsorder.payWay 新增取值 2=抖音券核销,前端支付方式展示需配合(若存在字典表则同步新增)。
|
-- ------------------------------------------------------------
|
|
ALTER TABLE "douyin_verify_record" ADD COLUMN IF NOT EXISTS "discount_member_id" varchar(64);
|
COMMENT ON COLUMN "douyin_verify_record"."discount_member_id" IS '核销成功开通的套餐卡ID(discount_member.id)';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-25 v3.0.1 抖音核销配置入字典 —— 改后台维护、免重启
|
-- 作者: rk
|
-- 说明: client_key / client_secret / account_id / poi_id 从 yml 迁到字典
|
-- (system_dict + system_dict_data);门店ID等变动时,后台 /system/dictData 改即可,无需重启。
|
-- 执行前:把 4 处「待填」替换为真实值(client_secret 勿提交真值到仓库)。
|
-- 环境:PostgreSQL 13+ 内置 gen_random_uuid();低版本需先 CREATE EXTENSION pgcrypto。
|
-- 创建人/更新人:固定 system_user.id = 722ecd1e-e903-45b1-a839-c591c0af0d7e。
|
-- ------------------------------------------------------------
|
|
-- 父:抖音核销配置字典(已存在则跳过,幂等)
|
INSERT INTO system_dict (code, name, remark, create_user, update_user, create_time, update_time, deleted)
|
SELECT 'DOUYIN_CONFIG', '抖音核销配置',
|
'抖音生活服务团购核销配置(client_key/client_secret/account_id/poi_id)',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict WHERE code = 'DOUYIN_CONFIG' AND deleted = 0);
|
|
-- 子:四项配置(label 全大写,与 Java 常量 Constants.DOUYIN_* 对应;code 字段存「值」)
|
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
|
SELECT gen_random_uuid()::text, d.id, '待填', 'CLIENT_KEY', 1, 0, '抖音应用 client_key',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
|
AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'CLIENT_KEY' AND deleted = 0);
|
|
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
|
SELECT gen_random_uuid()::text, d.id, '待填', 'CLIENT_SECRET', 2, 0, '抖音应用 client_secret',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
|
AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'CLIENT_SECRET' AND deleted = 0);
|
|
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
|
SELECT gen_random_uuid()::text, d.id, '待填', 'ACCOUNT_ID', 3, 0, '来客商户根账户ID',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
|
AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'ACCOUNT_ID' AND deleted = 0);
|
|
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
|
SELECT gen_random_uuid()::text, d.id, '待填', 'POI_ID', 4, 0, '核销门店ID(单门店)',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
|
AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'POI_ID' AND deleted = 0);
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-25 v3.0.1 订单轨迹 —— 新增电车骑行轨迹表
|
-- 作者: rk
|
-- 说明: 仅电车(type=1,走 JT/T 808)骑行中产生实时轨迹;自行车(type=0)走 MQTT 无 GPS 上报,不产生。
|
-- 写入点唯一:Jtt808Service.updateBikesInfo(0200 位置报文 for 循环内)。
|
-- 订单关联经 Redis 缓存(ride:active:{bikeCode})获取,避免秒级上报高频查 member_rides。
|
-- 关联字段说明:rides_id=骑行订单 member_rides.id;order_id=支付订单 member_rides.ordre_id→goodsorder.id(开锁时未绑定则为空)。
|
-- ------------------------------------------------------------
|
|
CREATE TABLE "member_rides_track" (
|
"id" varchar(64) NOT NULL,
|
"rides_id" varchar(64),
|
"order_id" varchar(64),
|
"bike_id" varchar(64),
|
"bike_code" varchar(64),
|
"longitude" numeric(10,7),
|
"latitude" numeric(10,7),
|
"report_time" timestamp,
|
"create_date" timestamp DEFAULT CURRENT_TIMESTAMP,
|
"isdeleted" smallint DEFAULT 0,
|
PRIMARY KEY ("id")
|
);
|
CREATE INDEX idx_mrt_rides_time ON "member_rides_track" ("rides_id", "report_time");
|
CREATE INDEX idx_mrt_order ON "member_rides_track" ("order_id");
|
CREATE INDEX idx_mrt_bike ON "member_rides_track" ("bike_id");
|
COMMENT ON TABLE "member_rides_track" IS '电车骑行轨迹(JT/T 808 位置上报)';
|
COMMENT ON COLUMN "member_rides_track"."rides_id" IS '骑行订单主键① member_rides.id';
|
COMMENT ON COLUMN "member_rides_track"."order_id" IS '支付订单主键② member_rides.ordre_id → goodsorder.id(可能为空)';
|
COMMENT ON COLUMN "member_rides_track"."bike_id" IS '车辆主键 bikes.id';
|
COMMENT ON COLUMN "member_rides_track"."bike_code" IS '车辆编码 bikes.code';
|
COMMENT ON COLUMN "member_rides_track"."longitude" IS '经度(高德 GCJ02,WGS84 转换后)';
|
COMMENT ON COLUMN "member_rides_track"."latitude" IS '纬度(高德 GCJ02,WGS84 转换后)';
|
COMMENT ON COLUMN "member_rides_track"."report_time" IS '设备上报时间 deviceTime';
|
COMMENT ON COLUMN "member_rides_track"."isdeleted" IS '是否已删除 0未删除 1已删除';
|
|
|
-- ------------------------------------------------------------
|
-- 2026-06-26 v3.0.1 小程序首页增加「抖音券兑换说明」字典项
|
-- 作者: rk
|
-- 说明: 归入既有 MINI_PROGRAMME 字典(与 STOP_SERVE_TIPS 同源);
|
-- 小程序首页 /web/home/home 读取后下发,展示抖音券兑换规则说明。
|
-- label 全大写,与 Java 常量 Constants.DOUYIN_EXCHANGE_TIPS 对应;
|
-- code 字段存「值」(说明文案),执行前可先置默认文案。
|
-- 环境:PostgreSQL 13+ 内置 gen_random_uuid();低版本需先 CREATE EXTENSION pgcrypto。
|
-- ------------------------------------------------------------
|
|
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
|
SELECT gen_random_uuid()::text, d.id,
|
'抖音券兑换规则说明请咨询门店', 'DOUYIN_EXCHANGE_TIPS', 99, 0, '抖音券兑换说明(小程序首页展示)',
|
'722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
|
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
|
FROM system_dict d WHERE d.code = 'MINI_PROGRAMME' AND d.deleted = 0
|
AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'DOUYIN_EXCHANGE_TIPS' AND deleted = 0);
|
