wangjia/jiu
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases 130 Wiki Activity
Files
main
jiu/backend/schema/schema.sql
T
wangjia 858f24f74b fix(backend): license_purchases.pay_url 512→TEXT——支付宝网关 URL 超列宽导致下单 1406 失败 
真单联调发现：pay v2 redirect payload.url 是支付宝网关完整签名 URL（2KB+），
回填 pay_url 报 Data too long，且残留 out_trade_no='' 行使重试撞唯一索引。
model + schema.sql + db-schema.html 三处同步，AutoMigrate 自动 ALTER。

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-14 23:58:57 +08:00

623 lines
34 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- ============================================================
-- 酒水仓库管理系统 数据库 Schema
-- MySQL 8.0+
-- 说明：所有业务表含 shop_id 实现多租户隔离
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------------------------------------------
-- 门店（租户）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `shops` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL COMMENT '门店名称',
`code` VARCHAR(50) NOT NULL COMMENT '门店编号（唯一）',
`address` VARCHAR(255) DEFAULT NULL,
`phone` VARCHAR(30) DEFAULT NULL,
`manager_name` VARCHAR(50) DEFAULT NULL COMMENT '负责人',
`description` TEXT DEFAULT NULL COMMENT '店铺简介',
`logo_url` VARCHAR(500) DEFAULT '' COMMENT '门店 logo URL',
`business_license` VARCHAR(500) DEFAULT NULL COMMENT '营业执照照片URL',
`shop_photos` JSON DEFAULT NULL COMMENT '门店照片URL数组',
`custom_fields` JSON DEFAULT NULL COMMENT '扩展字段',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='门店（租户）';
-- ------------------------------------------------------------
-- 用户（操作人员）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`username` VARCHAR(50) NOT NULL,
`password_hash` VARCHAR(255) NOT NULL COMMENT 'bcrypt',
`real_name` VARCHAR(50) DEFAULT NULL,
`phone` VARCHAR(30) DEFAULT NULL,
`role` ENUM('admin','operator','readonly','superadmin') NOT NULL DEFAULT 'operator',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`last_login_at` DATETIME DEFAULT NULL COMMENT '最近登录时间',
`custom_fields` JSON DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_shop_username` (`shop_id`, `username`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户';
-- ------------------------------------------------------------
-- 登录会话（支持登出/踢人/在线状态监控；JWT 的 sid claim 指向此表）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_sessions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`sid` VARCHAR(64) NOT NULL COMMENT '嵌入 JWT 的会话标识',
`device_id` VARCHAR(255) DEFAULT NULL,
`device_name` VARCHAR(255) DEFAULT NULL,
`platform` VARCHAR(50) DEFAULT NULL COMMENT 'windows|macos|linux|android|ios|web',
`platform_class` VARCHAR(20) DEFAULT NULL COMMENT 'desktop|mobile|web',
`ip` VARCHAR(64) DEFAULT NULL,
`user_agent` VARCHAR(512) DEFAULT NULL,
`refresh_jti` VARCHAR(64) DEFAULT NULL COMMENT '当前有效 refresh token 的 jti，用于轮换+重用检测',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`revoked_at` DATETIME DEFAULT NULL,
`revoked_reason` VARCHAR(30) DEFAULT NULL COMMENT 'kicked|logout|admin|disabled|reuse|pwd_reset|relogin',
`revoked_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '吊销操作人 user_id；系统/自助吊销为 NULL',
`refresh_exp_at` DATETIME DEFAULT NULL,
`kind` VARCHAR(10) DEFAULT 'login' COMMENT 'login=正常登录；sso=App换票的网页短时会话（不计设备配额）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_session_sid` (`sid`),
KEY `idx_session_shop_user` (`shop_id`, `user_id`),
KEY `idx_session_class` (`platform_class`),
KEY `idx_session_revoked` (`revoked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录会话';
-- ------------------------------------------------------------
-- 登录尝试审计（当前只记失败尝试，用于风控/排查；由保留期清理任务定期删旧）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `login_attempts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_code` VARCHAR(64) DEFAULT NULL,
`username` VARCHAR(50) DEFAULT NULL,
`ip` VARCHAR(64) DEFAULT NULL,
`user_agent` VARCHAR(512) DEFAULT NULL,
`success` TINYINT(1) NOT NULL DEFAULT 0,
`reason` VARCHAR(40) DEFAULT NULL COMMENT 'invalid_shop|invalid_user|bad_password|locked|inactive|platform_not_allowed',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_attempt_user` (`username`, `created_at`),
KEY `idx_attempt_ip` (`ip`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录尝试审计';
-- ------------------------------------------------------------
-- 许可证
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `licenses` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`license_key` VARCHAR(768) NOT NULL COMMENT 'Ed25519 signed token',
`device_id` VARCHAR(255) DEFAULT NULL COMMENT 'deprecated: use license_devices',
`type` ENUM('trial','monthly','annual','lifetime') NOT NULL DEFAULT 'trial',
`tier` VARCHAR(32) NOT NULL DEFAULT 'standard' COMMENT '权益档位钩子(pro/max-like)，当前默认 standard',
`expires_at` DATETIME DEFAULT NULL COMMENT 'NULL=永久',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`max_devices` INT NOT NULL DEFAULT 3 COMMENT '最大绑定设备数',
`features` JSON DEFAULT NULL COMMENT '功能开关 {"finance":true}',
`activated_at` DATETIME DEFAULT NULL COMMENT 'deprecated',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_license_key` (`license_key`),
KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='许可证';
CREATE TABLE IF NOT EXISTS `license_devices` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`license_id` BIGINT UNSIGNED NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`device_id` VARCHAR(255) NOT NULL,
`device_name` VARCHAR(255) DEFAULT NULL,
`platform` VARCHAR(50) DEFAULT NULL COMMENT 'windows|macos|android|ios|web',
`activated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_license_device` (`license_id`, `device_id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_license_id` (`license_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='许可证设备绑定';
-- 兑换券（激活码）码池：平台方批量生成，用户兑换后叠加时长到门店授权
CREATE TABLE IF NOT EXISTS `license_codes` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`code` VARCHAR(32) NOT NULL COMMENT '归一化大写无连字符',
`type` ENUM('trial','monthly','annual','lifetime') NOT NULL DEFAULT 'annual' COMMENT '计费/时长标签',
`tier` VARCHAR(32) NOT NULL DEFAULT 'standard' COMMENT '档位钩子(pro/max-like)，当前默认 standard',
`duration_days` INT NOT NULL DEFAULT 0 COMMENT '授予时长；0=永久(lifetime)',
`max_devices` INT NOT NULL DEFAULT 0 COMMENT '授予设备上限；0=不改变现值',
`status` ENUM('unused','redeemed','void') NOT NULL DEFAULT 'unused',
`redeemed_shop_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '被哪个门店兑换',
`redeemed_at` DATETIME DEFAULT NULL,
`redeemed_device_id` VARCHAR(255) DEFAULT NULL COMMENT '兑换设备(审计)',
`batch` VARCHAR(64) DEFAULT NULL COMMENT '发放批次/活动名',
`note` VARCHAR(255) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
KEY `idx_status` (`status`),
KEY `idx_redeemed_shop` (`redeemed_shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='兑换券码池';
-- ------------------------------------------------------------
-- 在线购买/续费记录（pay 收款中枢，契约 pay-contract v1.0.0）
-- out_trade_no = pay 订单号，对账键 + 幂等键；amount 为 pay 回传权威金额
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `license_purchases` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '下单管理员',
`product_biz_code` VARCHAR(64) NOT NULL COMMENT '套餐稳定码(monthly_standard/annual_standard/monthly_pro/annual_pro)',
`amount_minor` BIGINT NOT NULL DEFAULT 0 COMMENT 'v2 口径：最小单位金额(CNY=分)',
`currency` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'v2 口径：币种，如 CNY',
`pay_url` TEXT DEFAULT NULL COMMENT 'v2 下单 session payload.url（支付宝网关 URL 带签名参数可超 2KB），订单管理「继续支付」复用',
`renewed_to` DATETIME DEFAULT NULL COMMENT 'settle 续期后的授权到期日（订单流水「授权续期至」展示）',
`amount` VARCHAR(16) DEFAULT NULL COMMENT 'Deprecated: v1 元字符串，如 2999.00，观察一版后 DROP',
`out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT 'pay 订单号',
`status` ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
`trade_no` VARCHAR(64) DEFAULT NULL COMMENT '渠道交易号(支付宝/微信)',
`channel` VARCHAR(16) DEFAULT NULL,
`paid_at` DATETIME DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
KEY `idx_shop` (`shop_id`),
KEY `idx_status` (`status`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='在线购买/续费记录';
-- ------------------------------------------------------------
-- 商品分类
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(100) NOT NULL,
`parent_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '父分类，支持二级',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类';
-- ------------------------------------------------------------
-- 商品
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`code` VARCHAR(50) DEFAULT NULL COMMENT '商品编号',
`barcode` VARCHAR(100) DEFAULT NULL COMMENT '条码',
`name` VARCHAR(200) NOT NULL COMMENT '商品名称',
`series` VARCHAR(100) DEFAULT NULL COMMENT '系列',
`spec` VARCHAR(100) DEFAULT NULL COMMENT '规格',
`unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
`category_id` BIGINT UNSIGNED DEFAULT NULL,
`brand` VARCHAR(100) DEFAULT NULL COMMENT '品牌',
`purchase_price` DECIMAL(16,2) DEFAULT NULL COMMENT '参考进价',
`sale_price` DECIMAL(16,2) DEFAULT NULL COMMENT '参考售价',
`public_id` CHAR(36) NOT NULL DEFAULT '' COMMENT 'UUID，公开URL使用',
`min_stock` INT DEFAULT 0 COMMENT '库存预警值',
`description` TEXT DEFAULT NULL COMMENT '商品描述',
`custom_fields` JSON DEFAULT NULL COMMENT '动态扩展字段',
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_category` (`category_id`),
KEY `idx_deleted_at` (`deleted_at`),
UNIQUE KEY `uk_product_code` (`shop_id`, `code`),
UNIQUE KEY `uk_public_id` (`public_id`),
FULLTEXT KEY `ft_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品';
-- ------------------------------------------------------------
-- 商品图片
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_images` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`product_id` BIGINT UNSIGNED NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`url` VARCHAR(500) NOT NULL COMMENT '相对路径，如 /images/products/1/xxx.jpg',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品图片';
-- ------------------------------------------------------------
-- 仓库
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouses` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(100) NOT NULL,
`location` VARCHAR(200) DEFAULT NULL,
`is_default` TINYINT(1) NOT NULL DEFAULT 0,
`custom_fields` JSON DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓库';
-- ------------------------------------------------------------
-- 往来单位（供应商/客户）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `partners` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`code` VARCHAR(50) DEFAULT NULL,
`name` VARCHAR(200) NOT NULL,
`name_pinyin` VARCHAR(400) DEFAULT NULL COMMENT '名称全拼（搜索用，写入时自动生成）',
`name_initials` VARCHAR(100) DEFAULT NULL COMMENT '名称拼音首字母（搜索用）',
`type` SET('supplier','customer') NOT NULL DEFAULT 'supplier' COMMENT '可同时是供应商和客户',
`contact` VARCHAR(50) DEFAULT NULL COMMENT '联系人',
`phone` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(255) DEFAULT NULL,
`bank_account` VARCHAR(100) DEFAULT NULL COMMENT '银行账号',
`status` ENUM('enabled','disabled') NOT NULL DEFAULT 'enabled' COMMENT '正常/禁用',
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='往来单位';
-- ------------------------------------------------------------
-- 入库单
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_in_orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`order_no` VARCHAR(50) NOT NULL COMMENT '入库单号',
`type` VARCHAR(30) NOT NULL DEFAULT 'purchase' COMMENT '入库类型: purchase采购/return退货/other其他',
`warehouse_id` BIGINT UNSIGNED NOT NULL,
`partner_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '供应商',
`operator_id` BIGINT UNSIGNED NOT NULL COMMENT '经办人',
`creator_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '制单人',
`reviewer_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '审核人',
`status` ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
`order_date` DATE NOT NULL,
`cost_total` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '应付合计=Σ总进价(2026-07 消歧，旧列 total_amount 弃用)',
`reviewed_at` DATETIME DEFAULT NULL,
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`shop_id`, `order_no`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_warehouse` (`warehouse_id`),
KEY `idx_order_date` (`order_date`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单';
-- ------------------------------------------------------------
-- 入库单明细
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_in_items` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`product_code` VARCHAR(50) DEFAULT NULL COMMENT '商品编号(源流水号，历史导入)',
`product_name` VARCHAR(255) DEFAULT NULL COMMENT '商品名称(历史导入快照)',
`series` VARCHAR(100) DEFAULT NULL COMMENT '系列(历史导入快照)',
`spec` VARCHAR(100) DEFAULT NULL COMMENT '规格(历史导入快照)',
`quantity` DECIMAL(12,3) NOT NULL COMMENT '数量',
`cost_price` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '进价·单瓶(2026-07 消歧，旧列 unit_price 弃用)',
`cost_amount` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '总进价=quantity×cost_price(旧列 total_price 弃用)',
`batch_no` VARCHAR(50) DEFAULT NULL COMMENT '批次号',
`production_date` DATE DEFAULT NULL COMMENT '生产日期',
`expire_date` DATE DEFAULT NULL COMMENT '有效期',
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_shop_product` (`shop_id`, `product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='入库单明细';
-- ------------------------------------------------------------
-- 出库单
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_out_orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`order_no` VARCHAR(50) NOT NULL,
`type` VARCHAR(30) NOT NULL DEFAULT 'sale' COMMENT 'sale销售/return退货/other其他',
`warehouse_id` BIGINT UNSIGNED NOT NULL,
`partner_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '客户',
`operator_id` BIGINT UNSIGNED NOT NULL,
`creator_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '制单人',
`reviewer_id` BIGINT UNSIGNED DEFAULT NULL,
`status` ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
`order_date` DATE NOT NULL,
`sale_total` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '应收合计=Σ售价小计(2026-07 消歧，旧列 total_amount 弃用)',
`profit_total` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '总利润=Σ(sale_price>0?(sale_price-cost_price)×qty:0)，建单落库、确认售价/进价联动重算',
`reviewed_at` DATETIME DEFAULT NULL,
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`shop_id`, `order_no`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_warehouse` (`warehouse_id`),
KEY `idx_order_date` (`order_date`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单';
-- ------------------------------------------------------------
-- 出库单明细
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_out_items` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`product_code` VARCHAR(50) DEFAULT NULL COMMENT '商品编号(源流水号，历史导入)',
`product_name` VARCHAR(255) DEFAULT NULL COMMENT '商品名称(历史导入快照)',
`series` VARCHAR(100) DEFAULT NULL COMMENT '系列(历史导入快照)',
`spec` VARCHAR(100) DEFAULT NULL COMMENT '规格(历史导入快照)',
`quantity` DECIMAL(12,3) NOT NULL,
`cost_price` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '成本单价·入库成本快照(旧列 unit_price 弃用)',
`sale_price` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '售价(出库实际销售单价，应收按售价×数量)',
`cost_amount` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '成本小计=quantity×cost_price(旧列 total_price 弃用)',
`sale_amount` DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT '售价小计=quantity×sale_price(待定价=0)',
`batch_no` VARCHAR(50) DEFAULT NULL COMMENT '批次号',
`production_date` DATE DEFAULT NULL COMMENT '生产日期',
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_shop_product` (`shop_id`, `product_id`),
KEY `idx_so_items_shop_code` (`shop_id`, `product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='出库单明细';
-- ------------------------------------------------------------
-- 库存（批次模式：每条记录代表一个批次/入库批）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`warehouse_id` BIGINT UNSIGNED DEFAULT NULL,
`product_id` BIGINT UNSIGNED DEFAULT NULL,
`stock_in_item_id` BIGINT UNSIGNED DEFAULT NULL,
`inventory_check_id` BIGINT UNSIGNED DEFAULT NULL,
`quantity` DECIMAL(12,3) NOT NULL DEFAULT 0,
`status` ENUM('stock','on_sale','sold') NOT NULL DEFAULT 'stock' COMMENT '库存/在售(公开)/已售(卖光留痕)',
`product_code` VARCHAR(50) DEFAULT NULL,
`product_name` VARCHAR(200) DEFAULT NULL,
`series` VARCHAR(100) DEFAULT NULL,
`spec` VARCHAR(100) DEFAULT NULL,
`unit` VARCHAR(20) DEFAULT NULL,
`warehouse_name` VARCHAR(100) DEFAULT NULL,
`unit_price` DECIMAL(16,2) DEFAULT NULL,
`production_date` DATE DEFAULT NULL,
`batch_no` VARCHAR(50) DEFAULT NULL,
`supplier_name` VARCHAR(200) DEFAULT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_fifo` (`shop_id`, `warehouse_id`, `product_id`, `created_at`),
KEY `idx_shop_wh_product` (`shop_id`, `warehouse_id`, `product_id`),
KEY `idx_stock_in_item` (`stock_in_item_id`),
KEY `idx_inventory_check` (`inventory_check_id`),
KEY `idx_deleted_at` (`deleted_at`),
KEY `idx_inv_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存批次记录';
-- ------------------------------------------------------------
-- 库存流水（出入库记录明细）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`warehouse_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`direction` ENUM('in','out') NOT NULL,
`quantity` DECIMAL(12,3) NOT NULL,
`qty_before` DECIMAL(12,3) NOT NULL COMMENT '变动前数量',
`qty_after` DECIMAL(12,3) NOT NULL COMMENT '变动后数量',
`ref_type` VARCHAR(30) NOT NULL COMMENT 'stock_in/stock_out/check',
`ref_id` BIGINT UNSIGNED NOT NULL COMMENT '关联单据ID',
`operator_id` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_shop_product` (`shop_id`, `product_id`),
KEY `idx_ref` (`ref_type`, `ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存流水';
-- ------------------------------------------------------------
-- 库存盘点
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_checks` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`check_no` VARCHAR(50) NOT NULL,
`warehouse_id` BIGINT UNSIGNED NOT NULL,
`operator_id` BIGINT UNSIGNED NOT NULL,
`status` ENUM('draft','completed') NOT NULL DEFAULT 'draft',
`check_date` DATE NOT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_check_no` (`shop_id`, `check_no`),
KEY `idx_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存盘点单';
CREATE TABLE IF NOT EXISTS `inventory_check_items` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`check_id` BIGINT UNSIGNED NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`system_qty` DECIMAL(12,3) NOT NULL COMMENT '系统数量',
`actual_qty` DECIMAL(12,3) NOT NULL COMMENT '实际盘点数量',
`diff_qty` DECIMAL(12,3) GENERATED ALWAYS AS (`actual_qty` - `system_qty`) STORED COMMENT '差异',
`remark` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_check_id` (`check_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='盘点明细';
-- ------------------------------------------------------------
-- 财务流水
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `finance_records` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`partner_id` BIGINT UNSIGNED DEFAULT NULL,
`type` ENUM('receivable','payable','receipt','payment') NOT NULL COMMENT '应收/应付/收款/付款',
`amount` DECIMAL(16,2) NOT NULL,
`balance` DECIMAL(16,2) NOT NULL COMMENT '操作后余额',
`status` ENUM('open','closed') NOT NULL DEFAULT 'open' COMMENT '结清状态（payable/receivable 有效）',
`ref_type` VARCHAR(30) DEFAULT NULL COMMENT '关联单据类型',
`ref_id` BIGINT UNSIGNED DEFAULT NULL,
`operator_id` BIGINT UNSIGNED NOT NULL,
`record_date` DATE NOT NULL,
`custom_fields` JSON DEFAULT NULL,
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_partner` (`partner_id`),
KEY `idx_record_date` (`record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务流水';
-- ------------------------------------------------------------
-- 编号规则配置
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `number_rules` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`type` VARCHAR(30) NOT NULL COMMENT 'stock_in/stock_out/check',
`prefix` VARCHAR(20) DEFAULT '' COMMENT '前缀',
`current_no` INT NOT NULL DEFAULT 0 COMMENT '当前序号',
`date_format` VARCHAR(20) DEFAULT 'YYYYMMDD' COMMENT '日期格式',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_shop_type` (`shop_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='单号规则';
-- ------------------------------------------------------------
-- 基础数据选项
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_name_options` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`code` VARCHAR(50) DEFAULT NULL COMMENT '选项编号',
`name` VARCHAR(200) NOT NULL COMMENT '名称',
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品名称选项';
CREATE TABLE IF NOT EXISTS `product_series_options` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`code` VARCHAR(50) DEFAULT NULL COMMENT '选项编号',
`name` VARCHAR(200) NOT NULL COMMENT '系列名称',
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品系列选项';
CREATE TABLE IF NOT EXISTS `product_spec_options` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`shop_id` BIGINT UNSIGNED NOT NULL,
`code` VARCHAR(50) DEFAULT NULL COMMENT '选项编号',
`name` VARCHAR(200) NOT NULL COMMENT '规格名称',
`quantity` INT DEFAULT 0 COMMENT '单品数量',
`remark` VARCHAR(500) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格选项';
CREATE TABLE IF NOT EXISTS `feedbacks` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`shop_id` BIGINT UNSIGNED NOT NULL,
`shop_code` VARCHAR(50) DEFAULT NULL,
`user_id` BIGINT UNSIGNED DEFAULT NULL,
`username` VARCHAR(50) DEFAULT NULL,
`role` VARCHAR(20) DEFAULT NULL,
`type` VARCHAR(20) NOT NULL COMMENT 'bug / suggestion',
`content` TEXT,
`images` JSON,
`app_version` VARCHAR(30) DEFAULT NULL,
`platform` VARCHAR(20) DEFAULT NULL,
`client_ip` VARCHAR(60) DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'new',
PRIMARY KEY (`id`),
KEY `idx_shop_id` (`shop_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_type` (`type`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户意见反馈';
-- ------------------------------------------------------------
-- App→网页免登录一次性换票（magic-link；TTL 60s 单次可用，used_at 原子置位防重放）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `web_tickets` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`ticket` VARCHAR(64) NOT NULL,
`shop_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`expires_at` DATETIME NOT NULL,
`used_at` DATETIME DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_web_ticket` (`ticket`),
KEY `idx_web_ticket_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='App→网页免登录一次性票据';
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue View Git Blame Copy Permalink