PartsInquiry/doc/database_documentation.md

数据库结构文档（生产同步版）

说明

• 本文档依据当前生产库 partsinquiry 实时结构生成，确保与线上一致。
• 结构脚本来源：backend/db/db.sql（已由 mysqldump 同步）。如需精确细节（触发器/索引选项/字符集等），以该 SQL 文件为准。
• 下文为核心业务表的字段、索引与外键说明；其余表已在 backend/db/db.sql 完整记录。

连接信息（只读）

• Host: mysql.tonaspace.com
• Database: partsinquiry
• Collation: utf8mb4_0900_ai_ci

目录（表清单）

• accounts
• admins
• attachments
• consults / consult_replies
• customers
• email_codes
• finance_categories
• global_skus
• inventories / inventory_movements
• normal_admin_audits
• notices
• other_transactions
• part_attribute_dictionary / part_attribute_templates / part_category_attributes / part_categories
• part_submissions / part_templates / part_template_params
• payments
• product_aliases / product_categories / product_images / product_prices / product_units / products
• purchase_orders / purchase_order_items / purchase_return_orders / purchase_return_order_items
• sales_orders / sales_order_items / sales_return_orders / sales_return_order_items
• shops / suppliers / users / user_identities / system_parameters / sms_codes / vip_price / vip_recharges / vip_users / wechat_sessions

—— 以下为核心表详情（节选） ——

表：accounts

字段

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AUTO_INCREMENT
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
name	varchar(64)	NO
type	enum('cash','bank','alipay','wechat','other')	NO	cash
bank_name	varchar(64)	YES
bank_account	varchar(64)	YES
balance	decimal(18,2)	NO	0.00
status	tinyint unsigned	NO	1
created_at	timestamp	NO	CURRENT_TIMESTAMP
updated_at	timestamp	NO	CURRENT_TIMESTAMP ON UPDATE
deleted_at	datetime	YES

Indexes

• PRIMARY KEY: id
• UNIQUE KEY: ux_accounts_shop_name (shop_id,name)
• KEY: idx_accounts_shop (shop_id)

Foreign Keys

• fk_accounts_shop: shop_id → shops(id)
• fk_accounts_user: user_id → users(id)

---

表：customers

字段

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
name	varchar(120)	NO
phone	varchar(32)	YES
address	varchar(255)	YES
mobile	varchar(32)	YES
contact_name	varchar(64)	YES
price_level	enum('零售价','批发价','大单报价')	NO	零售价	默认售价列
status	tinyint unsigned	NO	1
ar_opening	decimal(18,2)	NO	0.00
remark	varchar(255)	YES
created_at	timestamp	NO	CURRENT_TIMESTAMP
updated_at	timestamp	NO	CURRENT_TIMESTAMP ON UPDATE
deleted_at	datetime	YES

Indexes

• PRIMARY KEY: id
• KEY: idx_customers_shop (shop_id)
• KEY: idx_customers_phone (phone)
• KEY: idx_customers_mobile (mobile)

Foreign Keys

• fk_customers_shop: shop_id → shops(id)
• fk_customers_user: user_id → users(id)

---

表：products

字段

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
name	varchar(120)	NO
category_id	bigint unsigned	YES
template_id	bigint unsigned	YES
brand	varchar(64)	YES
model	varchar(64)	YES
spec	varchar(128)	YES
origin	varchar(64)	YES
barcode	varchar(32)	YES
dedupe_key	varchar(512)	YES
alias	varchar(120)	YES
is_blacklisted	tinyint(1)	NO	0
description	text	YES
global_sku_id	bigint unsigned	YES
source_submission_id	bigint unsigned	YES
attributes_json	json	YES
safe_min	decimal(18,3)	YES
safe_max	decimal(18,3)	YES
search_text	text	YES		供全文检索的聚合字段
created_at	timestamp	NO	CURRENT_TIMESTAMP
updated_at	timestamp	NO	CURRENT_TIMESTAMP ON UPDATE
deleted_at	datetime	YES
is_active	tinyint (generated)	YES		生成列：deleted_at 为空则 1，否则 0

Indexes

• PRIMARY KEY: id
• UNIQUE KEY: ux_products_template_name_model (template_id,name,model)
• UNIQUE KEY: ux_products_shop_barcode_live (shop_id,barcode,is_active)
• FULLTEXT: ft_products_search (name,brand,model,spec,search_text)
• KEY: idx_products_shop (shop_id), idx_products_category (category_id), idx_products_template (template_id), idx_products_dedupe (dedupe_key), idx_products_deleted_at (deleted_at), idx_products_shop_blacklist (shop_id,is_blacklisted)

Foreign Keys

• fk_products_shop: shop_id → shops(id)
• fk_products_user: user_id → users(id)
• fk_products_category: category_id → product_categories(id)
• fk_products_template: template_id → part_templates(id)
• fk_products_globalsku: global_sku_id → global_skus(id)
• fk_products_submission: source_submission_id → part_submissions(id)

触发器

• BEFORE INSERT trg_products_bi: 设置 search_text
• BEFORE UPDATE trg_products_au: 设置 search_text

---

表：sales_orders

字段（摘录）

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO		创建人
customer_id	bigint unsigned	YES
order_no	varchar(32)	NO
order_time	datetime	NO
status	enum('draft','approved','returned','void')	NO	draft
amount	decimal(18,2)	NO	0.00	应收合计
paid_amount	decimal(18,2)	NO	0.00	已收合计
remark	varchar(255)	YES
created_at	timestamp	NO	CURRENT_TIMESTAMP
updated_at	timestamp	NO	CURRENT_TIMESTAMP ON UPDATE
deleted_at	datetime	YES

Indexes

• PRIMARY KEY: id
• UNIQUE KEY: ux_sales_order_no (shop_id,order_no)
• KEY: idx_sales_shop_time (shop_id,order_time), idx_sales_customer (customer_id)

Foreign Keys

• fk_sales_shop: shop_id → shops(id)
• fk_sales_user: user_id → users(id)
• fk_sales_customer: customer_id → customers(id)

---

表：purchase_orders

字段（摘录）

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
supplier_id	bigint unsigned	YES
order_no	varchar(32)	NO
order_time	datetime	NO
status	enum('draft','approved','void','returned')	NO	draft
amount	decimal(18,2)	NO	0.00	应付合计
paid_amount	decimal(18,2)	NO	0.00	已付合计
remark	varchar(255)	YES
created_at	timestamp	NO	CURRENT_TIMESTAMP
updated_at	timestamp	NO	CURRENT_TIMESTAMP ON UPDATE
deleted_at	datetime	YES

Indexes

• PRIMARY KEY: id
• UNIQUE KEY: ux_purchase_order_no (shop_id,order_no)
• KEY: idx_purchase_shop_time (shop_id,order_time), idx_purchase_supplier (supplier_id)

Foreign Keys

• fk_purchase_shop: shop_id → shops(id)
• fk_purchase_user: user_id → users(id)
• fk_purchase_supplier: supplier_id → suppliers(id)

---

表：part_submissions（配件提交）

字段（摘录）

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
name	varchar(120)	YES
model_unique	varchar(128)	NO		型号（唯一）
brand	varchar(64)	YES
spec	varchar(128)	YES
template_id	bigint unsigned	YES
category_id	bigint unsigned	YES
status	enum('pending','approved','rejected')	NO	pending
product_id	bigint unsigned	YES
global_sku_id	bigint unsigned	YES
barcode	varchar(64)	YES
dedupe_key	varchar(512)	YES
images/tags/attributes	json	YES
created_at/updated_at	timestamp	NO	CURRENT_TIMESTAMP

Indexes（摘录）

• PRIMARY KEY: id
• UNIQUE KEY: ux_part_model_unique (model_unique)
• UNIQUE KEY: ux_ps_template_name_model (template_id,name,model_unique)
• KEY: idx_sub_shop_status (shop_id,status,updated_at), idx_part_brand_model (brand,model_unique), idx_ps_dedupe (dedupe_key)

Foreign Keys（摘录）

• fk_part_shop: shop_id → shops(id)
• fk_part_user: user_id → users(id)
• fk_part_submission_product: product_id → products(id)
• fk_part_submission_global_sku: global_sku_id → global_skus(id)
• fk_ps_template: template_id → part_templates(id)

---

表：product_aliases（别名）

字段（摘录）

Column Name	Data Type	Nullable	Default	Comment
id	bigint unsigned	NO	AI
product_id	bigint unsigned	NO
alias	varchar(120)	NO
shop_id	bigint unsigned	NO
user_id	bigint unsigned	NO
created_at	timestamp	NO	CURRENT_TIMESTAMP
deleted_at	datetime	YES

Indexes

• PRIMARY KEY: id
• UNIQUE KEY: ux_product_alias (product_id,alias)
• KEY: idx_product_alias_product (product_id)

Foreign Keys

• fk_alias_product: product_id → products(id)
• fk_alias_shop: shop_id → shops(id)
• fk_alias_user: user_id → users(id)

触发器

• AFTER INSERT/UPDATE/DELETE：trg_palias_ai/trg_palias_au/trg_palias_ad 用于回写 products.search_text

---

其余表

• 所有其它表（见“目录”）的字段/索引/外键与生产一致，完整 DDL 已收录在 backend/db/db.sql。若需要将本文件扩展为全量字段清单，可由该 SQL 一键生成（建议脚本化生成避免手工遗漏）。

生成时间

• 本文档基于线上结构同步，生成于本地更新时刻。