wangjia
73955a139c
- inventories 加 status 枚举列（默认 stock，AutoMigrate 存量即全部「库存」）+ 索引 - 出库审核扣光置 sold 不再软删（留痕台账）；退单恢复行置回 stock；盘亏仍软删 - 新接口 PUT /inventory/:id/status（仅收 stock/on_sale，已售行拒改，租户校验） - List 默认排除已售（显式 status=sold 才显示）+ status 多值筛选 - Summary：SKU/货值/数量排除已售，新增 sold_count - 公开店铺页/API 只列 on_sale——酒单从全量裸奔改为人工精选 - 测试：卖光置售/部分不改/退单回库/接口边界/跨租户/汇总口径 8 用例 - 设计方案 docs/design/inventory-sale-status.html + db-schema/CLAUDE.md 同步 Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01JJ1g8XV1YhhmHRzhwWEW7o
707 lines
90 KiB
HTML
707 lines
90 KiB
HTML
<!DOCTYPE html>
|
||
<html lang="zh-CN">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>数据库 Schema — 酒库管理系统</title>
|
||
<style>
|
||
:root{
|
||
--primary:#2563AC; --primary-dark:#154072; --danger:#D14343; --danger-bg:#FDECEC;
|
||
--success:#2E8B57; --success-bg:#E6F3EC; --warn:#B45309; --warn-bg:#FFF4E5; --accent:#8B2331;
|
||
--ink:#232934; --muted:#6E7888; --border:#DCE2EB; --paper:#F5F7FA; --head:#F0F4FF;
|
||
}
|
||
*{box-sizing:border-box;font-family:-apple-system,"PingFang SC","Microsoft YaHei",sans-serif;}
|
||
body{margin:0;background:var(--paper);color:var(--ink);line-height:1.6;}
|
||
.layout{display:flex;align-items:flex-start;}
|
||
nav{position:sticky;top:0;align-self:flex-start;width:210px;height:100vh;overflow:auto;background:#fff;border-right:1px solid var(--border);padding:18px 14px;flex:0 0 auto;}
|
||
nav .navgroup{margin-bottom:14px;}
|
||
nav .navtitle{font-size:11px;font-weight:700;color:var(--primary-dark);text-transform:uppercase;letter-spacing:.5px;margin-bottom:5px;}
|
||
nav a{display:block;font-size:12px;color:var(--muted);text-decoration:none;padding:2px 0;}
|
||
nav a:hover{color:var(--primary);}
|
||
nav a code{font-family:ui-monospace,Menlo,monospace;font-size:11.5px;}
|
||
main{flex:1 1 auto;padding:26px 30px;max-width:1100px;min-width:0;}
|
||
h1{font-size:22px;margin:0 0 4px;}
|
||
.sub{color:var(--muted);font-size:13px;margin-bottom:6px;}
|
||
.stats{display:flex;gap:22px;font-size:12px;color:var(--muted);margin:10px 0 4px;}
|
||
.stats b{color:var(--ink);font-size:15px;}
|
||
h2{font-size:17px;margin:34px 0 6px;color:var(--primary-dark);border-bottom:2px solid var(--head);padding-bottom:6px;}
|
||
.gdesc{font-size:12.5px;color:var(--muted);margin:6px 0 12px;}
|
||
h3{font-size:14px;margin:0 0 10px;display:flex;align-items:baseline;gap:10px;flex-wrap:wrap;}
|
||
.tname{font-family:ui-monospace,Menlo,monospace;font-size:14px;background:var(--primary-dark);color:#fff;padding:2px 9px;border-radius:5px;}
|
||
.tcomment{font-size:12.5px;color:var(--muted);font-weight:400;}
|
||
.card{background:#fff;border:1px solid var(--border);border-radius:10px;padding:16px 18px;margin:14px 0;}
|
||
table{width:100%;border-collapse:collapse;font-size:12.5px;margin:6px 0;}
|
||
th{background:var(--head);color:var(--primary-dark);font-weight:600;font-size:11.5px;text-align:left;padding:7px 9px;border-bottom:1px solid var(--border);}
|
||
td{padding:6px 9px;border-bottom:1px solid #EEF1F6;vertical-align:top;}
|
||
tr:last-child td{border-bottom:0;}
|
||
.mono{font-family:ui-monospace,Menlo,monospace;}
|
||
td.ty{color:var(--accent);font-size:11.5px;}
|
||
td.df{color:var(--muted);font-size:11.5px;}
|
||
td.ctr{text-align:center;}
|
||
code{font-family:ui-monospace,Menlo,monospace;font-size:11.5px;background:#EEF2F8;padding:1px 5px;border-radius:4px;color:var(--primary-dark);}
|
||
.tag{display:inline-block;font-size:10px;padding:0 6px;border-radius:9px;font-weight:600;line-height:16px;}
|
||
.tag.pk{background:#FDECEC;color:var(--danger);}
|
||
.tag.uk{background:#E5EEF9;color:var(--primary);}
|
||
.tag.ix{background:#EEE;color:var(--muted);}
|
||
.tag.ft{background:var(--warn-bg);color:var(--warn);}
|
||
.tag.tnt{background:var(--success-bg);color:var(--success);}
|
||
.keys{margin-top:8px;font-size:11.5px;line-height:1.9;border-top:1px dashed var(--border);padding-top:8px;}
|
||
.keys .kd{color:var(--muted);}
|
||
.callout{border-left:4px solid var(--primary);background:#F0F6FF;padding:9px 13px;border-radius:4px;margin:8px 0 12px;font-size:12.5px;}
|
||
.callout.danger{border-color:var(--danger);background:var(--danger-bg);}
|
||
.callout.warn{border-color:var(--warn);background:var(--warn-bg);}
|
||
.callout.ok{border-color:var(--success);background:var(--success-bg);}
|
||
.legend{display:flex;gap:16px;flex-wrap:wrap;font-size:12px;color:var(--muted);margin:8px 0 0;}
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="layout">
|
||
<nav>
|
||
<div style="font-size:13px;font-weight:700;color:var(--primary-dark);margin-bottom:14px;">📊 数据库 Schema</div>
|
||
<div class="navgroup"><div class="navtitle">租户 · 用户 · 安全</div><a href="#shops"><code>shops</code></a><a href="#users"><code>users</code></a><a href="#user_sessions"><code>user_sessions</code></a><a href="#login_attempts"><code>login_attempts</code></a></div><div class="navgroup"><div class="navtitle">授权 · 兑换券</div><a href="#licenses"><code>licenses</code></a><a href="#license_devices"><code>license_devices</code></a><a href="#license_codes"><code>license_codes</code></a><a href="#license_purchases"><code>license_purchases</code></a></div><div class="navgroup"><div class="navtitle">商品主数据</div><a href="#products"><code>products</code></a><a href="#product_categories"><code>product_categories</code></a><a href="#product_images"><code>product_images</code></a><a href="#product_name_options"><code>product_name_options</code></a><a href="#product_series_options"><code>product_series_options</code></a><a href="#product_spec_options"><code>product_spec_options</code></a><a href="#product_origin_options"><code>product_origin_options</code></a><a href="#product_shelf_life_options"><code>product_shelf_life_options</code></a><a href="#product_storage_options"><code>product_storage_options</code></a><a href="#product_description_docs"><code>product_description_docs</code></a></div><div class="navgroup"><div class="navtitle">仓库 · 往来单位</div><a href="#warehouses"><code>warehouses</code></a><a href="#partners"><code>partners</code></a></div><div class="navgroup"><div class="navtitle">出入库单据</div><a href="#stock_in_orders"><code>stock_in_orders</code></a><a href="#stock_in_items"><code>stock_in_items</code></a><a href="#stock_out_orders"><code>stock_out_orders</code></a><a href="#stock_out_items"><code>stock_out_items</code></a></div><div class="navgroup"><div class="navtitle">库存 · 流水 · 盘点</div><a href="#inventories"><code>inventories</code></a><a href="#inventory_logs"><code>inventory_logs</code></a><a href="#inventory_checks"><code>inventory_checks</code></a><a href="#inventory_check_items"><code>inventory_check_items</code></a></div><div class="navgroup"><div class="navtitle">财务</div><a href="#finance_records"><code>finance_records</code></a></div><div class="navgroup"><div class="navtitle">配置 · 运维 · 反馈</div><a href="#number_rules"><code>number_rules</code></a><a href="#feedbacks"><code>feedbacks</code></a><a href="#error_reports"><code>error_reports</code></a></div>
|
||
</nav>
|
||
<main>
|
||
<h1>数据库 Schema · 酒库管理系统</h1>
|
||
<div class="sub">MySQL 8.0+ · 多租户（所有业务表含 <code>shop_id</code> 隔离）· 启动时 GORM AutoMigrate</div>
|
||
<div class="stats"><div><b>31</b> 张表</div><div><b>365</b> 个字段</div><div>来源：<code>backend/schema/schema.sql</code> + model AutoMigrate</div></div>
|
||
<div class="legend">
|
||
<span><span class="tag pk">PK</span> 主键</span>
|
||
<span><span class="tag uk">UNIQUE</span> 唯一键</span>
|
||
<span><span class="tag ix">INDEX</span> 普通索引</span>
|
||
<span><span class="tag ft">FULLTEXT</span> 全文索引</span>
|
||
<span><span class="tag tnt">租户</span> 多租户隔离列</span>
|
||
</div>
|
||
<section><h2>租户 · 用户 · 安全</h2><p class="gdesc">多租户根表与登录/会话/审计。所有业务表的 shop_id 都指向 shops。</p><div class="card" id="shops">
|
||
<h3><code class="tname">shops</code> <span class="tcomment">门店（租户）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">否</td><td class="mono df">—</td><td>门店名称</td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">否</td><td class="mono df">—</td><td>门店编号（唯一）</td></tr>
|
||
<tr><td class="mono">address</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">phone</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">manager_name</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>负责人</td></tr>
|
||
<tr><td class="mono">description</td><td class="mono ty">TEXT</td><td class="ctr">是</td><td class="mono df">NULL</td><td>店铺简介</td></tr>
|
||
<tr><td class="mono">logo_url</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">—</td><td>门店 logo URL</td></tr>
|
||
<tr><td class="mono">business_license</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>营业执照照片URL</td></tr>
|
||
<tr><td class="mono">shop_photos</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td>门店照片URL数组</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td>扩展字段</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_code</span> <span class="mono kd">(`code`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="users">
|
||
<h3><code class="tname">users</code> <span class="tcomment">用户</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">username</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">password_hash</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">否</td><td class="mono df">—</td><td>bcrypt</td></tr>
|
||
<tr><td class="mono">real_name</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">phone</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">role</td><td class="mono ty">ENUM('admin','operator','readonly','superadmin')</td><td class="ctr">否</td><td class="mono df">operator</td><td></td></tr>
|
||
<tr><td class="mono">is_active</td><td class="mono ty">TINYINT(1)</td><td class="ctr">否</td><td class="mono df">1</td><td></td></tr>
|
||
<tr><td class="mono">last_login_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>最近登录时间</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_shop_username</span> <span class="mono kd">(`shop_id`, `username`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="user_sessions">
|
||
<h3><code class="tname">user_sessions</code> <span class="tcomment">登录会话</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">user_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">sid</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">否</td><td class="mono df">—</td><td>嵌入 JWT 的会话标识</td></tr>
|
||
<tr><td class="mono">device_id</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">device_name</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">platform</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>windows|macos|linux|android|ios|web</td></tr>
|
||
<tr><td class="mono">platform_class</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>desktop|mobile|web</td></tr>
|
||
<tr><td class="mono">ip</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">user_agent</td><td class="mono ty">VARCHAR(512)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">refresh_jti</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>当前有效 refresh token 的 jti，用于轮换+重用检测</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">last_seen_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">revoked_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">revoked_reason</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>kicked|logout|admin|disabled|reuse|pwd_reset</td></tr>
|
||
<tr><td class="mono">revoked_by</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>吊销操作人 user_id；系统/自助吊销为 NULL</td></tr>
|
||
<tr><td class="mono">refresh_exp_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_session_sid</span> <span class="mono kd">(`sid`)</span><br><span class="tag ix">INDEX idx_session_shop_user</span> <span class="mono kd">(`shop_id`, `user_id`)</span><br><span class="tag ix">INDEX idx_session_class</span> <span class="mono kd">(`platform_class`)</span><br><span class="tag ix">INDEX idx_session_revoked</span> <span class="mono kd">(`revoked_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="login_attempts">
|
||
<h3><code class="tname">login_attempts</code> <span class="tcomment">登录尝试审计</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_code</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">username</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">ip</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">user_agent</td><td class="mono ty">VARCHAR(512)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">success</td><td class="mono ty">TINYINT(1)</td><td class="ctr">否</td><td class="mono df">0</td><td></td></tr>
|
||
<tr><td class="mono">reason</td><td class="mono ty">VARCHAR(40)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>invalid_shop|invalid_user|bad_password|locked|inactive|platform_not_allowed</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_attempt_user</span> <span class="mono kd">(`username`, `created_at`)</span><br><span class="tag ix">INDEX idx_attempt_ip</span> <span class="mono kd">(`ip`, `created_at`)</span></div>
|
||
</div></section><section><h2>授权 · 兑换券</h2><p class="gdesc">门店授权与时长兑换券码池（ed25519/HMAC 已退役，改时长兑换券模型）。</p><div class="card" id="licenses">
|
||
<h3><code class="tname">licenses</code> <span class="tcomment">许可证</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">license_key</td><td class="mono ty">VARCHAR(768)</td><td class="ctr">否</td><td class="mono df">—</td><td>Ed25519 signed token</td></tr>
|
||
<tr><td class="mono">device_id</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>deprecated: use license_devices</td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">ENUM('trial','monthly','annual','lifetime')</td><td class="ctr">否</td><td class="mono df">trial</td><td></td></tr>
|
||
<tr><td class="mono">tier</td><td class="mono ty">VARCHAR(32)</td><td class="ctr">否</td><td class="mono df">standard</td><td>权益档位钩子(pro/max-like)，当前默认 standard</td></tr>
|
||
<tr><td class="mono">expires_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>NULL=永久</td></tr>
|
||
<tr><td class="mono">is_active</td><td class="mono ty">TINYINT(1)</td><td class="ctr">否</td><td class="mono df">1</td><td></td></tr>
|
||
<tr><td class="mono">max_devices</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">3</td><td>最大绑定设备数</td></tr>
|
||
<tr><td class="mono">features</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td>功能开关 {"finance":true}</td></tr>
|
||
<tr><td class="mono">activated_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>deprecated</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_license_key</span> <span class="mono kd">(`license_key`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="license_devices">
|
||
<h3><code class="tname">license_devices</code> <span class="tcomment">许可证设备绑定</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">license_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">device_id</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">device_name</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">platform</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>windows|macos|android|ios|web</td></tr>
|
||
<tr><td class="mono">activated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">last_seen_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_license_device</span> <span class="mono kd">(`license_id`, `device_id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_license_id</span> <span class="mono kd">(`license_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="license_codes">
|
||
<h3><code class="tname">license_codes</code> <span class="tcomment">兑换券码池</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(32)</td><td class="ctr">否</td><td class="mono df">—</td><td>归一化大写无连字符</td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">ENUM('trial','monthly','annual','lifetime')</td><td class="ctr">否</td><td class="mono df">annual</td><td>计费/时长标签</td></tr>
|
||
<tr><td class="mono">tier</td><td class="mono ty">VARCHAR(32)</td><td class="ctr">否</td><td class="mono df">standard</td><td>档位钩子(pro/max-like)，当前默认 standard</td></tr>
|
||
<tr><td class="mono">duration_days</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">0</td><td>授予时长；0=永久(lifetime)</td></tr>
|
||
<tr><td class="mono">max_devices</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">0</td><td>授予设备上限；0=不改变现值</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('unused','redeemed','void')</td><td class="ctr">否</td><td class="mono df">unused</td><td></td></tr>
|
||
<tr><td class="mono">redeemed_shop_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>被哪个门店兑换</td></tr>
|
||
<tr><td class="mono">redeemed_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">redeemed_device_id</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>兑换设备(审计)</td></tr>
|
||
<tr><td class="mono">batch</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>发放批次/活动名</td></tr>
|
||
<tr><td class="mono">note</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_code</span> <span class="mono kd">(`code`)</span><br><span class="tag ix">INDEX idx_status</span> <span class="mono kd">(`status`)</span><br><span class="tag ix">INDEX idx_redeemed_shop</span> <span class="mono kd">(`redeemed_shop_id`)</span></div>
|
||
</div><div class="card" id="license_purchases">
|
||
<h3><code class="tname">license_purchases</code> <span class="tcomment">在线购买/续费记录</span></h3>
|
||
<div class="callout ok"><b>pay 收款中枢：</b>契约见 <code>~/code/pay-contract</code>（v1.0.0）。<code>out_trade_no</code> = pay 订单号，兼作对账键与幂等键（同一单只续期一次）；<code>amount</code> 为 pay 下单响应回传的权威金额，webhook 回调时逐分核对。</div>
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">user_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>下单管理员</td></tr>
|
||
<tr><td class="mono">product_biz_code</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">否</td><td class="mono df">—</td><td>套餐稳定码：monthly_standard / annual_standard / monthly_pro / annual_pro</td></tr>
|
||
<tr><td class="mono">amount</td><td class="mono ty">VARCHAR(16)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>pay 下单回传金额，如 2999.00</td></tr>
|
||
<tr><td class="mono">out_trade_no</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>pay 订单号（对账+幂等键）</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('pending','paid','failed')</td><td class="ctr">否</td><td class="mono df">pending</td><td></td></tr>
|
||
<tr><td class="mono">trade_no</td><td class="mono ty">VARCHAR(64)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>渠道交易号(支付宝/微信)</td></tr>
|
||
<tr><td class="mono">channel</td><td class="mono ty">VARCHAR(16)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">paid_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>软删除</td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_out_trade_no</span> <span class="mono kd">(`out_trade_no`)</span><br><span class="tag ix">INDEX idx_shop</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_status</span> <span class="mono kd">(`status`)</span></div>
|
||
</div></section><section><h2>商品主数据</h2><p class="gdesc">products 每行 = 一个特有产品/序列号（非 SKU），是商品信息的单一来源。基础数据字典为入库选文本服务。</p><div class="card" id="products">
|
||
<h3><code class="tname">products</code> <span class="tcomment">商品</span></h3>
|
||
<div class="callout ok"><b>单一来源铁律：</b>批次号 <code>batch_no</code>、生产日期 <code>production_date</code>、进价、图片、public_id 均以此表为准。每行 = 一个序列号（<code>uk_product_code</code> 同店唯一）。</div>
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品编号</td></tr>
|
||
<tr><td class="mono">barcode</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>条码</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>商品名称</td></tr>
|
||
<tr><td class="mono">series</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>系列</td></tr>
|
||
<tr><td class="mono">spec</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>规格</td></tr>
|
||
<tr><td class="mono">unit</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>单位</td></tr>
|
||
<tr><td class="mono">category_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">brand</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>品牌</td></tr>
|
||
<tr><td class="mono">purchase_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>参考进价</td></tr>
|
||
<tr><td class="mono">sale_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>参考售价</td></tr>
|
||
<tr><td class="mono">public_id</td><td class="mono ty">CHAR(36)</td><td class="ctr">否</td><td class="mono df">—</td><td>UUID，公开URL使用</td></tr>
|
||
<tr><td class="mono">min_stock</td><td class="mono ty">INT</td><td class="ctr">是</td><td class="mono df">0</td><td>库存预警值</td></tr>
|
||
<tr><td class="mono">description</td><td class="mono ty">TEXT</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品描述</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td>动态扩展字段</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_category</span> <span class="mono kd">(`category_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span><br><span class="tag uk">UNIQUE uk_product_code</span> <span class="mono kd">(`shop_id`, `code`)</span><br><span class="tag uk">UNIQUE uk_public_id</span> <span class="mono kd">(`public_id`)</span><br><span class="tag ft">FULLTEXT ft_name</span> <span class="mono kd">(`name`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_categories">
|
||
<h3><code class="tname">product_categories</code> <span class="tcomment">商品分类</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">parent_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>父分类，支持二级</td></tr>
|
||
<tr><td class="mono">sort_order</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">0</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_images">
|
||
<h3><code class="tname">product_images</code> <span class="tcomment">商品图片</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">url</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">否</td><td class="mono df">—</td><td>相对路径，如 /images/products/1/xxx.jpg</td></tr>
|
||
<tr><td class="mono">sort_order</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">0</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_product_id</span> <span class="mono kd">(`product_id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_name_options">
|
||
<h3><code class="tname">product_name_options</code> <span class="tcomment">商品名称选项</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>名称</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_series_options">
|
||
<h3><code class="tname">product_series_options</code> <span class="tcomment">商品系列选项</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>系列名称</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_spec_options">
|
||
<h3><code class="tname">product_spec_options</code> <span class="tcomment">商品规格选项</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>规格名称</td></tr>
|
||
<tr><td class="mono">quantity</td><td class="mono ty">INT</td><td class="ctr">是</td><td class="mono df">0</td><td>单品数量</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="product_origin_options">
|
||
<h3><code class="tname">product_origin_options</code> <span class="tcomment">商品产地选项（基础数据·AutoMigrate）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>租户隔离</td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">—</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>名称</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">created_at / updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>软删除</td></tr></tbody>
|
||
</table>
|
||
|
||
</div>
|
||
<div class="card" id="product_shelf_life_options">
|
||
<h3><code class="tname">product_shelf_life_options</code> <span class="tcomment">商品保质期选项（如「24个月」「无限期」·AutoMigrate）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>租户隔离</td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">—</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>名称</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">created_at / updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>软删除</td></tr></tbody>
|
||
</table>
|
||
|
||
</div>
|
||
<div class="card" id="product_storage_options">
|
||
<h3><code class="tname">product_storage_options</code> <span class="tcomment">商品储存条件选项（如「阴凉避光」·AutoMigrate）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>租户隔离</td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">—</td><td>选项编号</td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>名称</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">created_at / updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>软删除</td></tr></tbody>
|
||
</table>
|
||
|
||
</div>
|
||
<div class="card" id="product_description_docs">
|
||
<h3><code class="tname">product_description_docs</code> <span class="tcomment">商品介绍文档（富文本·AutoMigrate）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>租户隔离</td></tr>
|
||
<tr><td class="mono">title</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td>标题</td></tr>
|
||
<tr><td class="mono">content</td><td class="mono ty">TEXT</td><td class="ctr">是</td><td class="mono df">—</td><td>正文</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">created_at / updated_at / deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">—</td><td class="mono df">—</td><td></td></tr></tbody>
|
||
</table>
|
||
|
||
</div></section><section><h2>仓库 · 往来单位</h2><div class="card" id="warehouses">
|
||
<h3><code class="tname">warehouses</code> <span class="tcomment">仓库</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">location</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">is_default</td><td class="mono ty">TINYINT(1)</td><td class="ctr">否</td><td class="mono df">0</td><td></td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="partners">
|
||
<h3><code class="tname">partners</code> <span class="tcomment">往来单位</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">SET('supplier','customer')</td><td class="ctr">否</td><td class="mono df">supplier</td><td>可同时是供应商和客户</td></tr>
|
||
<tr><td class="mono">contact</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>联系人</td></tr>
|
||
<tr><td class="mono">phone</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">address</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">bank_account</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>银行账号</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('enabled','disabled')</td><td class="ctr">否</td><td class="mono df">enabled</td><td>正常/禁用</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div></section><section><h2>出入库单据</h2><p class="gdesc">单据明细含「快照列」(product_code/name/series/spec/batch_no/production_date)：导入/审核时拷贝，作历史保真+显示兜底。</p><div class="card" id="stock_in_orders">
|
||
<h3><code class="tname">stock_in_orders</code> <span class="tcomment">入库单</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">order_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">否</td><td class="mono df">—</td><td>入库单号</td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">否</td><td class="mono df">purchase</td><td>入库类型: purchase采购/return退货/other其他</td></tr>
|
||
<tr><td class="mono">warehouse_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">partner_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>供应商</td></tr>
|
||
<tr><td class="mono">operator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>经办人</td></tr>
|
||
<tr><td class="mono">creator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>制单人</td></tr>
|
||
<tr><td class="mono">reviewer_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>审核人</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('draft','pending','approved','rejected')</td><td class="ctr">否</td><td class="mono df">draft</td><td></td></tr>
|
||
<tr><td class="mono">order_date</td><td class="mono ty">DATE</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">cost_total</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>应付合计=Σ总进价（2026-07 消歧，旧列 total_amount 弃用待删）</td></tr>
|
||
<tr><td class="mono">reviewed_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_order_no</span> <span class="mono kd">(`shop_id`, `order_no`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_warehouse</span> <span class="mono kd">(`warehouse_id`)</span><br><span class="tag ix">INDEX idx_order_date</span> <span class="mono kd">(`order_date`)</span><br><span class="tag ix">INDEX idx_status</span> <span class="mono kd">(`status`)</span></div>
|
||
</div>
|
||
<div class="card" id="stock_in_items">
|
||
<h3><code class="tname">stock_in_items</code> <span class="tcomment">入库单明细</span></h3>
|
||
<div class="callout warn"><b>快照列：</b><code>product_code/product_name/series/spec/batch_no/production_date</code> 在导入/录入时拷贝，product_id 可为 0（历史导入占位）。<b>库存页的批次/生产日期实际从这里取（见 inventories 说明）。</b></div>
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">order_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品编号(源流水号，历史导入)</td></tr>
|
||
<tr><td class="mono">product_name</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品名称(历史导入快照)</td></tr>
|
||
<tr><td class="mono">series</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>系列(历史导入快照)</td></tr>
|
||
<tr><td class="mono">spec</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>规格(历史导入快照)</td></tr>
|
||
<tr><td class="mono">quantity</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td>数量</td></tr>
|
||
<tr><td class="mono">cost_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>进价·单瓶（旧列 unit_price 弃用待删）</td></tr>
|
||
<tr><td class="mono">cost_amount</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>总进价=quantity×cost_price（旧列 total_price 弃用待删）</td></tr>
|
||
<tr><td class="mono">batch_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>批次号</td></tr>
|
||
<tr><td class="mono">production_date</td><td class="mono ty">DATE</td><td class="ctr">是</td><td class="mono df">NULL</td><td>生产日期</td></tr>
|
||
<tr><td class="mono">expire_date</td><td class="mono ty">DATE</td><td class="ctr">是</td><td class="mono df">NULL</td><td>有效期</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_order_id</span> <span class="mono kd">(`order_id`)</span><br><span class="tag ix">INDEX idx_shop_product</span> <span class="mono kd">(`shop_id`, `product_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="stock_out_orders">
|
||
<h3><code class="tname">stock_out_orders</code> <span class="tcomment">出库单</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">order_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">否</td><td class="mono df">sale</td><td>sale销售/return退货/other其他</td></tr>
|
||
<tr><td class="mono">warehouse_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">partner_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>客户</td></tr>
|
||
<tr><td class="mono">operator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">creator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>制单人</td></tr>
|
||
<tr><td class="mono">reviewer_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('draft','pending','approved','rejected')</td><td class="ctr">否</td><td class="mono df">draft</td><td></td></tr>
|
||
<tr><td class="mono">order_date</td><td class="mono ty">DATE</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">sale_total</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>应收合计=Σ售价小计（旧列 total_amount 弃用待删）</td></tr>
|
||
<tr><td class="mono">profit_total</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>总利润=Σ(售价>0?(售价-成本)×数量:0)，建单落库、确认售价/进价联动重算；仅管理员可见（operator 响应抹零）</td></tr>
|
||
<tr><td class="mono">reviewed_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_order_no</span> <span class="mono kd">(`shop_id`, `order_no`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_warehouse</span> <span class="mono kd">(`warehouse_id`)</span><br><span class="tag ix">INDEX idx_order_date</span> <span class="mono kd">(`order_date`)</span><br><span class="tag ix">INDEX idx_status</span> <span class="mono kd">(`status`)</span></div>
|
||
</div>
|
||
<div class="card" id="stock_out_items">
|
||
<h3><code class="tname">stock_out_items</code> <span class="tcomment">出库单明细</span></h3>
|
||
<div class="callout danger"><b>当前问题相关：</b>出库行快照 <code>batch_no/production_date</code> 在 App 建单时常为空（选货按 product 聚合、未带批次）。打印回退到 <code>products</code> 主记录——若该 product 也没填，就显示空白，但库存页仍能显示（取自入库明细）。<code>cost_price</code>=成本、<code>sale_price</code>=售价（2026-07 已消歧改名）。</div>
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">order_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品编号(源流水号，历史导入)</td></tr>
|
||
<tr><td class="mono">product_name</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品名称(历史导入快照)</td></tr>
|
||
<tr><td class="mono">series</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>系列(历史导入快照)</td></tr>
|
||
<tr><td class="mono">spec</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>规格(历史导入快照)</td></tr>
|
||
<tr><td class="mono">quantity</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">cost_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>成本单价·入库成本快照（旧列 unit_price 弃用待删；仅管理员可见）</td></tr>
|
||
<tr><td class="mono">sale_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>售价(出库实际销售单价，应收按售价×数量)</td></tr>
|
||
<tr><td class="mono">cost_amount</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>成本小计=quantity×cost_price（旧列弃用待删；仅管理员可见）</td></tr>
|
||
<tr><td class="mono">sale_amount</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">0</td><td>售价小计=quantity×sale_price（待定价=0）</td></tr>
|
||
<tr><td class="mono">batch_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>批次号</td></tr>
|
||
<tr><td class="mono">production_date</td><td class="mono ty">DATE</td><td class="ctr">是</td><td class="mono df">NULL</td><td>生产日期</td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_order_id</span> <span class="mono kd">(`order_id`)</span><br><span class="tag ix">INDEX idx_shop_product</span> <span class="mono kd">(`shop_id`, `product_id`)</span><br><span class="tag ix">INDEX idx_so_items_shop_code</span> <span class="mono kd">(`shop_id`, `product_code`)</span></div>
|
||
</div></section><section><h2>库存 · 流水 · 盘点</h2><p class="gdesc">库存为批次模式：每条 inventories 记录 = 一个入库批次，经 stock_in_item_id 关联其入库明细。</p><div class="card" id="inventories">
|
||
<h3><code class="tname">inventories</code> <span class="tcomment">库存批次记录</span></h3>
|
||
<div class="callout warn"><b>批次模式 + 显示口径：</b>每行 = 一个入库批次。列表查询的批次/生产日期取 <code>COALESCE(DATE(sii.production_date), DATE(inv.production_date))</code>、<code>COALESCE(NULLIF(sii.batch_no,''), inv.batch_no)</code>——优先关联入库明细 <code>sii</code>，<b>不读 products</b>。本表 batch_no/production_date 为快照兜底。</div>
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td>主键</td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>所属门店（多租户隔离，所有查询必带）</td></tr>
|
||
<tr><td class="mono">warehouse_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>所在仓库；NULL 表示历史导入未指定仓</td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>指向 products 的特有产品/序列号；历史导入占位单据可为 NULL/0</td></tr>
|
||
<tr><td class="mono">stock_in_item_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>来源入库明细行。批次/生产日期显示优先从此关联取（sii），是本表的「真值上游」</td></tr>
|
||
<tr><td class="mono">inventory_check_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td>若由盘点调整产生，指向盘点单 inventory_checks</td></tr>
|
||
<tr><td class="mono">quantity</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">0</td><td>当前批次结存数量（出入库审核时在事务内增减）</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('stock','on_sale','sold')</td><td class="ctr">否</td><td class="mono df">'stock'</td><td>库存三态（2026-07-07）：stock=库存（在库不公开，默认）/ on_sale=在售（公开酒单展示，抽屉 switch 手动挂售）/ sold=已售（出库审核扣光自动置入，<b>留痕不软删</b>；列表默认排除，点已售卡才显示）。公开店铺页只列 on_sale；索引 idx_inv_status</td></tr>
|
||
<tr><td class="mono">product_code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品编号快照（拷贝自入库时），product 缺失时兜底显示</td></tr>
|
||
<tr><td class="mono">product_name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>商品名称快照，兜底显示</td></tr>
|
||
<tr><td class="mono">series</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>系列快照（语义=型号），兜底显示</td></tr>
|
||
<tr><td class="mono">spec</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>规格快照（语义=版本），兜底显示</td></tr>
|
||
<tr><td class="mono">unit</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>单位快照</td></tr>
|
||
<tr><td class="mono">warehouse_name</td><td class="mono ty">VARCHAR(100)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>仓库名快照，warehouse 关联缺失时兜底</td></tr>
|
||
<tr><td class="mono">unit_price</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>入库成本单价快照</td></tr>
|
||
<tr><td class="mono">production_date</td><td class="mono ty">DATE</td><td class="ctr">是</td><td class="mono df">NULL</td><td>生产日期快照兜底（显示口径见上方：优先取 stock_in_item）</td></tr>
|
||
<tr><td class="mono">batch_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>批次号快照兜底（显示口径见上方：优先取 stock_in_item）</td></tr>
|
||
<tr><td class="mono">supplier_name</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>供应商名快照（入库来源）</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>备注</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td>创建时间（FIFO 排序依据 idx_fifo 用到）</td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td>更新时间</td></tr>
|
||
<tr><td class="mono">deleted_at</td><td class="mono ty">DATETIME</td><td class="ctr">是</td><td class="mono df">NULL</td><td>软删除标记，NULL=有效</td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_fifo</span> <span class="mono kd">(`shop_id`, `warehouse_id`, `product_id`, `created_at`)</span><br><span class="tag ix">INDEX idx_shop_wh_product</span> <span class="mono kd">(`shop_id`, `warehouse_id`, `product_id`)</span><br><span class="tag ix">INDEX idx_stock_in_item</span> <span class="mono kd">(`stock_in_item_id`)</span><br><span class="tag ix">INDEX idx_inventory_check</span> <span class="mono kd">(`inventory_check_id`)</span><br><span class="tag ix">INDEX idx_deleted_at</span> <span class="mono kd">(`deleted_at`)</span></div>
|
||
</div>
|
||
<div class="card" id="inventory_logs">
|
||
<h3><code class="tname">inventory_logs</code> <span class="tcomment">库存流水</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">warehouse_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">direction</td><td class="mono ty">ENUM('in','out')</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">quantity</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">qty_before</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td>变动前数量</td></tr>
|
||
<tr><td class="mono">qty_after</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td>变动后数量</td></tr>
|
||
<tr><td class="mono">ref_type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">否</td><td class="mono df">—</td><td>stock_in/stock_out/check</td></tr>
|
||
<tr><td class="mono">ref_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td>关联单据ID</td></tr>
|
||
<tr><td class="mono">operator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_product</span> <span class="mono kd">(`shop_id`, `product_id`)</span><br><span class="tag ix">INDEX idx_ref</span> <span class="mono kd">(`ref_type`, `ref_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="inventory_checks">
|
||
<h3><code class="tname">inventory_checks</code> <span class="tcomment">库存盘点单</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">check_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">warehouse_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">operator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('draft','completed')</td><td class="ctr">否</td><td class="mono df">draft</td><td></td></tr>
|
||
<tr><td class="mono">check_date</td><td class="mono ty">DATE</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_check_no</span> <span class="mono kd">(`shop_id`, `check_no`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span></div>
|
||
</div>
|
||
<div class="card" id="inventory_check_items">
|
||
<h3><code class="tname">inventory_check_items</code> <span class="tcomment">盘点明细</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">check_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">system_qty</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td>系统数量</td></tr>
|
||
<tr><td class="mono">actual_qty</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">否</td><td class="mono df">—</td><td>实际盘点数量</td></tr>
|
||
<tr><td class="mono">diff_qty</td><td class="mono ty">DECIMAL(12,3)</td><td class="ctr">是</td><td class="mono df">计算列</td><td>差异</td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(255)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_check_id</span> <span class="mono kd">(`check_id`)</span></div>
|
||
</div></section><section><h2>财务</h2><div class="card" id="finance_records">
|
||
<h3><code class="tname">finance_records</code> <span class="tcomment">财务流水</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">partner_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">ENUM('receivable','payable','receipt','payment')</td><td class="ctr">否</td><td class="mono df">—</td><td>应收/应付/收款/付款</td></tr>
|
||
<tr><td class="mono">amount</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">balance</td><td class="mono ty">DECIMAL(16,2)</td><td class="ctr">否</td><td class="mono df">—</td><td>操作后余额</td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">ENUM('open','closed')</td><td class="ctr">否</td><td class="mono df">open</td><td>结清状态（payable/receivable 有效）</td></tr>
|
||
<tr><td class="mono">ref_type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td>关联单据类型</td></tr>
|
||
<tr><td class="mono">ref_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">operator_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">record_date</td><td class="mono ty">DATE</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">custom_fields</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">remark</td><td class="mono ty">VARCHAR(500)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_partner</span> <span class="mono kd">(`partner_id`)</span><br><span class="tag ix">INDEX idx_record_date</span> <span class="mono kd">(`record_date`)</span></div>
|
||
</div></section><section><h2>配置 · 运维 · 反馈</h2><div class="card" id="number_rules">
|
||
<h3><code class="tname">number_rules</code> <span class="tcomment">单号规则</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">否</td><td class="mono df">—</td><td>stock_in/stock_out/check</td></tr>
|
||
<tr><td class="mono">prefix</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">—</td><td>前缀</td></tr>
|
||
<tr><td class="mono">current_no</td><td class="mono ty">INT</td><td class="ctr">否</td><td class="mono df">0</td><td>当前序号</td></tr>
|
||
<tr><td class="mono">date_format</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">YYYYMMDD</td><td>日期格式</td></tr>
|
||
<tr><td class="mono">updated_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag uk">UNIQUE uk_shop_type</span> <span class="mono kd">(`shop_id`, `type`)</span></div>
|
||
</div>
|
||
<div class="card" id="feedbacks">
|
||
<h3><code class="tname">feedbacks</code> <span class="tcomment">用户意见反馈</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">否</td><td class="mono df">CURRENT_TIMESTAMP</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_code</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">user_id</td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">username</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">role</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">type</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">否</td><td class="mono df">—</td><td>bug / suggestion</td></tr>
|
||
<tr><td class="mono">content</td><td class="mono ty">TEXT</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">images</td><td class="mono ty">JSON</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">app_version</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">platform</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">client_ip</td><td class="mono ty">VARCHAR(60)</td><td class="ctr">是</td><td class="mono df">NULL</td><td></td></tr>
|
||
<tr><td class="mono">status</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">否</td><td class="mono df">new</td><td></td></tr></tbody>
|
||
</table>
|
||
<div class="keys"><span class="tag pk">PRIMARY</span> <span class="mono kd">(`id`)</span><br><span class="tag ix">INDEX idx_shop_id</span> <span class="mono kd">(`shop_id`)</span><br><span class="tag ix">INDEX idx_user_id</span> <span class="mono kd">(`user_id`)</span><br><span class="tag ix">INDEX idx_type</span> <span class="mono kd">(`type`)</span><br><span class="tag ix">INDEX idx_status</span> <span class="mono kd">(`status`)</span></div>
|
||
</div>
|
||
<div class="card" id="error_reports">
|
||
<h3><code class="tname">error_reports</code> <span class="tcomment">客户端异常上报（AutoMigrate，无 shop 软删除）</span></h3>
|
||
|
||
<table>
|
||
<thead><tr><th style="width:23%">列名</th><th style="width:20%">类型</th><th style="width:7%">可空</th><th style="width:16%">默认</th><th>说明</th></tr></thead>
|
||
<tbody><tr><td class="mono"><b>id</b> <span class="tag pk">PK</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">否</td><td class="mono df">AUTO_INC</td><td></td></tr>
|
||
<tr><td class="mono">error_type</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">否</td><td class="mono df">—</td><td>flutter_error / zone_error / caught_exception</td></tr>
|
||
<tr><td class="mono">app_version</td><td class="mono ty">VARCHAR(30)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">platform</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">—</td><td>ios/android/web/windows/macos</td></tr>
|
||
<tr><td class="mono">username</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_id <span class="tag tnt">租户</span></td><td class="mono ty">BIGINT UNSIGNED</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">shop_no</td><td class="mono ty">VARCHAR(50)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">role</td><td class="mono ty">VARCHAR(20)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">product_info</td><td class="mono ty">VARCHAR(200)</td><td class="ctr">是</td><td class="mono df">—</td><td>设备/系统信息</td></tr>
|
||
<tr><td class="mono">client_ip</td><td class="mono ty">VARCHAR(60)</td><td class="ctr">是</td><td class="mono df">—</td><td></td></tr>
|
||
<tr><td class="mono">error_msg</td><td class="mono ty">TEXT</td><td class="ctr">否</td><td class="mono df">—</td><td>错误信息</td></tr>
|
||
<tr><td class="mono">stack_trace</td><td class="mono ty">TEXT</td><td class="ctr">是</td><td class="mono df">—</td><td>堆栈</td></tr>
|
||
<tr><td class="mono">occurred_at</td><td class="mono ty">DATETIME</td><td class="ctr">—</td><td class="mono df">—</td><td>发生时间</td></tr>
|
||
<tr><td class="mono">created_at</td><td class="mono ty">DATETIME</td><td class="ctr">—</td><td class="mono df">—</td><td>上报时间</td></tr></tbody>
|
||
</table>
|
||
|
||
</div></section>
|
||
<p class="sub" style="margin-top:30px;">生成时间：2026-06-26 · 字段以 schema.sql 为准；产地/保质期/储存/介绍文档/异常上报等表由 GORM AutoMigrate 建，列信息取自 <code>backend/internal/model/</code>。</p>
|
||
</main>
|
||
</div>
|
||
</body>
|
||
</html> |