数据库设计

智能家庭饮食管家 App - 数据库设计文档

最后更新： 2026-05-19

一、文档修订记录

版本	日期	作者	修改说明
v1.0	2026-05-18		初始版本，定义全部表结构、关系与同步策略
v1.1	2026-05-18		修复设计缺陷：批次库存、birth_date、收藏表等 15 项修订
v1.2	2026-05-18		修复逻辑漏洞：核销快照、食材属性锁定、采购清单聚合、外键级联等 7 项修订
v1.3	2026-05-18		统一单位换算策略：明确以 g/ml 为营养基准，修正换算触发条件与示例
v1.5	2026-05-18		简化设计：删除 brand 字段及单位换算规则等冗余设计
v1.6	2026-05-18		统一权限模型：明确掌勺人/户主/成员权限层级，核销必须为当日掌勺人或户主
v1.9	2026-05-19		与PRD v1.0、详细设计文档 v1.1 对齐：确认核销权限描述一致（掌勺人或户主），Toast提示文案统一

二、设计概述

• 本地数据库：SQLite 3，用于离线缓存、本地操作与同步队列暂存。
• 云端数据库：PostgreSQL，用于中央数据存储与多设备一致性保障。
• 主键策略：统一采用 UUID 字符串（TEXT 类型，36 位），避免本地/云端 ID 冲突，简化多端同步。
• 时间戳同步：所有表均包含 created_at 与 updated_at，采用 ISO 8601 文本格式（2026-05-18T12:00:00Z），用于冲突解决。
• 软删除：核心业务表采用 is_deleted 字段标记删除，避免同步时的物理删除冲突。
• 乐观锁：所有双向同步的实体表均包含 version 字段，用于多端并发写入时的冲突检测。
• 外键级联：家庭解散时，关联的家庭数据（库存、菜单、心愿单等）应通过业务层执行「混合删除策略」，而非数据库层面 ON DELETE CASCADE，以确保同步队列能正确推送删除事件。
• 单位标准：所有食材的计量单位统一为 g（克）或 ml（毫升）。菜谱用量、库存数量、采购量均以 g/ml 为基准，无需单位换算。

三、实体关系总览 (ER)

users ──1:N── family_members ──N:1── families
  │                                      │
  │ 1:N                                1:N
  ▼                                     ▼
consumption_records               daily_menus ──1:N── menu_items ──N:1── recipes ──1:N── recipe_ingredients ──N:1── ingredients
  │                                      │                                                    │
  │                                      │                                                    │ 1:N
  │                                      │                                                    ▼
  │                                      │                                              recipe_steps
  │                                      │                                              recipe_tag_relations ──N:1── tags
  │                                      │
  │                                      │ 1:N
  │                                      ▼
  │                                  wishlist_items ──N:1── recipes
  │
  │ 1:N
  ▼
recipe_favorites ──N:1── recipes

families ──1:N── inventory_batches ──N:1── ingredients
  │                    │
  │                    │ 1:N
  │                    ▼
  │              inventory_logs
  │
  │ 1:N
  ▼
shopping_list_items ──N:1── ingredients

users ──1:1── taste_preferences
users ──1:N── ingredient_blacklist ──N:1── ingredients
users ──1:N── user_allergens ──N:1── ingredients
users ──1:1── nutrition_profiles

families ──1:N── health_reports

关系速查：

父表	子表	关系	说明
users	family_members	1:N	一个用户可加入多个家庭
families	family_members	1:N	一个家庭可包含多个成员
families	daily_menus	1:N	每个家庭每天最多 3 条（早/午/晚）
daily_menus	menu_items	1:N	一个餐别下包含多道菜品
recipes	menu_items	N:1	一道菜品可出现在多个菜单中
recipes	recipe_ingredients	1:N	一道菜的食材清单
ingredients	recipe_ingredients	N:1	一种食材可被多道菜引用
recipes	recipe_steps	1:N	一道菜的烹饪步骤
recipes	recipe_tag_relations	1:N	菜品与标签的多对多
tags	recipe_tag_relations	N:1
families	inventory_batches	1:N	每个家庭的冰箱库存（按批次）
ingredients	inventory_batches	N:1	一种食材可有多个批次
inventory_batches	inventory_logs	1:N	库存操作的审计日志
families	shopping_list_items	1:N	采购清单项
families	wishlist_items	1:N	家庭心愿单
users	wishlist_items	1:N	心愿单提交者
users	recipe_favorites	1:N	用户收藏的菜谱
recipes	recipe_favorites	N:1	菜谱被收藏
users	taste_preferences	1:1	每个用户一份口味偏好
users	ingredient_blacklist	1:N	用户黑名单食材
users	user_allergens	1:N	用户过敏源食材
users	nutrition_profiles	1:1	用户营养档案
users	consumption_records	1:N	饮食消费记录
families	health_reports	1:N	统计报告

---

四、完整表结构定义

4.1 users — 用户表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
login_id	TEXT	NOT NULL, UNIQUE	登录标识（手机号/邮箱/用户名）
nickname	TEXT	NOT NULL	用户昵称
avatar_url	TEXT		头像图片路径或 URL
gender	INTEGER	DEFAULT 0	0=未设置, 1=男, 2=女
height	REAL		身高 (cm)
weight	REAL		体重 (kg)
birth_date	TEXT		出生日期 YYYY-MM-DD，用于动态计算年龄
diet_goal	TEXT	DEFAULT ‘maintenance’	weight_loss / muscle_gain / maintenance
bmr	REAL		基础代谢率 (kcal/day)
tdee	REAL		每日总能量消耗 (kcal/day)
password_hash	TEXT	NOT NULL	加密后的密码
is_deleted	INTEGER	DEFAULT 0	0=正常, 1=已删除
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL	ISO 8601
updated_at	TEXT	NOT NULL	ISO 8601

索引：

• idx_users_login_id ON (login_id)
• idx_users_nickname ON (nickname)
• idx_users_is_deleted ON (is_deleted)

---

4.2 families — 家庭表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
name	TEXT	NOT NULL	家庭名称，默认 “{创建者昵称}的家”
invite_code	TEXT	NOT NULL, UNIQUE	6 位字母数字邀请码
invite_code_expires_at	TEXT		邀请码过期时间，NULL 表示永不过期
creator_id	TEXT	NOT NULL, FK → users.id	家庭创建者
is_deleted	INTEGER	DEFAULT 0	软删除标记
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

索引：

• idx_families_invite_code ON (invite_code)
• idx_families_creator ON (creator_id)

说明：成员总数通过 SELECT COUNT(*) FROM family_members WHERE family_id = ? 动态计算，不设冗余字段。

---

4.3 family_members — 家庭成员关系表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
user_id	TEXT	NOT NULL, FK → users.id
role	TEXT	NOT NULL, DEFAULT ‘member’	owner / member
can_manage_inventory	INTEGER	DEFAULT 0	冰箱入库权限（仅 member 角色生效）
can_confirm_menu	INTEGER	DEFAULT 0	菜单确认权（仅 member 角色生效）
joined_at	TEXT	NOT NULL	加入时间
version	INTEGER	DEFAULT 1	乐观锁版本号
updated_at	TEXT	NOT NULL

约束：

• UNIQUE(family_id, user_id) — 同一用户对同一家庭仅有一条关系记录

索引：

• idx_fm_family_id ON (family_id)
• idx_fm_user_id ON (user_id)
• idx_fm_role ON (role)

权限规则（业务层校验）：

核心原则：权限按三个维度分层——身份维度（掌勺人/户主）、权限维度（菜单确认权/库存管理权）、范围维度（菜单级/家庭级）。不同操作对应不同维度的组合。

操作	掌勺人（chef_id = 当前用户）	户主（role = owner）	can_confirm_menu = 1	can_manage_inventory = 1
确认菜单 (confirm-menu)	✅（仅本人菜单）	✅	✅	❌
烹饪核销 (complete-cooking)	✅（仅本人菜单）	✅	❌	❌
采纳心愿 (adopt wishlist)	✅（仅本人菜单）	✅	✅	❌
入库管理	✅（仅本人菜单对应库存）	✅	❌	✅
切换掌勺人	—	✅	❌	❌
修改成员权限	—	✅	❌	❌
移交户主权	—	✅	❌	❌
解散家庭	—	✅	❌	❌

权限层级说明：

• 掌勺人（Chef）：由 daily_menus.chef_id 指定，是菜单级别的身份。只有掌勺人才能核销（标记制作完成）。户主可以通过切换掌勺人来让其他成员承担烹饪任务。
• 户主（Owner）：由 family_members.role = 'owner' 指定，是家庭级别的身份。户主拥有家庭的最高管理权限，可以替任何成员核销（因为承担了管理责任）。
• 菜单确认权（can_confirm_menu）：是家庭级别的成员权限，允许非掌勺人提前确认菜单（适合委托场景），但不能用于核销，因为核销涉及食材消耗和库存扣减，必须由实际烹饪者执行。
• 库存管理权（can_manage_inventory）：是家庭级别的成员权限，允许添加/编辑冰箱库存。

重要约束：can_confirm_menu = 1 的成员可以确认菜单，但不能核销。核销权限与掌勺人身份严格绑定，防止非烹饪者替人核销（冒领功劳）。

说明：MVP 阶段仅设 owner / member 两级角色。后续如需中间角色（如 admin），可通过新增 role 枚举值扩展。

---

4.4 ingredients — 食材表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
name	TEXT	NOT NULL	食材名称
unit	TEXT	NOT NULL	计量单位，仅允许 g（克）或 ml（毫升）
calories_per_100	REAL		每 100g/ml 热量 (kcal)
protein_per_100	REAL		每 100g/ml 蛋白质 (g)
carbs_per_100	REAL		每 100g/ml 碳水 (g)
fat_per_100	REAL		每 100g/ml 脂肪 (g)
image_url	TEXT		食材图片
category	TEXT	NOT NULL	分类枚举
is_system	INTEGER	DEFAULT 1	0=用户自定义, 1=系统内置
creator_id	TEXT	FK → users.id	自定义食材的创建者
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

category 枚举值： vegetable（果蔬生鲜）、meat（肉禽蛋奶）、seafood（水产海鲜）、staple（主食谷物）、condiment（调味品）、dairy（乳制品）、other（其他）

单位标准（v1.4 简化）： 所有食材的 unit 字段仅允许填写 g（克）或 ml（毫升）。由于单位统一，无需 default_weight_per_unit 换算字段。菜谱用量、库存数量、采购量均以 g/ml 为基准直接计算。

索引：

• idx_ingredients_name ON (name)
• idx_ingredients_category ON (category)
• idx_ingredients_is_system ON (is_system)

---

4.5 recipes — 菜谱表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
name	TEXT	NOT NULL	菜名
cover_image_url	TEXT		封面图片
description	TEXT		简介描述
cooking_time	INTEGER		烹饪时长 (分钟)
servings	INTEGER	DEFAULT 2	默认几人份
meal_type	TEXT	DEFAULT ‘any’	breakfast / lunch / dinner / any
total_calories	REAL		总热量 (kcal)，由业务层维护
total_protein	REAL		总蛋白质 (g)，由业务层维护
total_carbs	REAL		总碳水 (g)，由业务层维护
total_fat	REAL		总脂肪 (g)，由业务层维护
is_system	INTEGER	DEFAULT 1	0=用户自定义, 1=系统内置
creator_id	TEXT	FK → users.id	自定义菜谱的创建者
family_id	TEXT	FK → families.id	归属家庭（家庭共享菜谱）
popularity	INTEGER	DEFAULT 0	累计被选次数
is_deleted	INTEGER	DEFAULT 0	软删除
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

聚合字段维护规则： total_calories、total_protein、total_carbs、total_fat 为反范式化缓存字段，用于首页快速加载。必须在以下时机由业务层自动重算：

1. 新增/删除/修改 recipe_ingredients 记录时
2. 修改 ingredients 的营养成分数据时
3. 修改 servings 字段时

重算公式：SUM(ri.amount × i.xxx_per_100 / 100)，其中 ri 为 recipe_ingredients，i 为 ingredients。

索引：

• idx_recipes_name ON (name)
• idx_recipes_meal_type ON (meal_type)
• idx_recipes_is_system ON (is_system)
• idx_recipes_family_id ON (family_id)
• idx_recipes_popularity ON (popularity DESC)

---

4.6 recipe_ingredients — 菜谱食材关联表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
recipe_id	TEXT	NOT NULL, FK → recipes.id
ingredient_id	TEXT	NOT NULL, FK → ingredients.id
amount	REAL	NOT NULL	所需用量（单位与 ingredients.unit 一致，为 g 或 ml）
created_at	TEXT	NOT NULL

约束：

• UNIQUE(recipe_id, ingredient_id) — 同一菜谱中同种食材仅一条记录

说明（v1.4 简化）： 删除了 recipe_ingredients.unit 字段。用量单位统一继承自 ingredients.unit（g 或 ml），无需在菜谱层级重复存储。

索引：

• idx_ri_recipe_id ON (recipe_id)
• idx_ri_ingredient_id ON (ingredient_id)

---

4.7 recipe_steps — 菜谱步骤表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
recipe_id	TEXT	NOT NULL, FK → recipes.id
step_number	INTEGER	NOT NULL	步骤序号（从 1 开始）
description	TEXT	NOT NULL	步骤文字说明
image_url	TEXT		步骤配图
created_at	TEXT	NOT NULL

约束：

• UNIQUE(recipe_id, step_number)

索引：

• idx_rs_recipe_id ON (recipe_id)

---

4.8 tags — 标签表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
name	TEXT	NOT NULL, UNIQUE	标签名（咸鲜、微辣、快手菜…）
type	TEXT	NOT NULL	标签类型
created_at	TEXT	NOT NULL

type 枚举值： flavor（口味）、cooking_method（烹饪方式）、occasion（场景/菜系）

索引：

• idx_tags_type ON (type)

---

4.9 recipe_tag_relations — 菜谱标签关联表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
recipe_id	TEXT	NOT NULL, FK → recipes.id
tag_id	TEXT	NOT NULL, FK → tags.id
created_at	TEXT	NOT NULL

约束：

• UNIQUE(recipe_id, tag_id)

索引：

• idx_rtr_recipe_id ON (recipe_id)
• idx_rtr_tag_id ON (tag_id)

---

4.10 recipe_favorites — 菜谱收藏表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, FK → users.id
recipe_id	TEXT	NOT NULL, FK → recipes.id
created_at	TEXT	NOT NULL

约束：

• UNIQUE(user_id, recipe_id) — 同一用户对同一菜谱仅收藏一次

索引：

• idx_fav_user_id ON (user_id)
• idx_fav_recipe_id ON (recipe_id)

---

4.11 daily_menus — 每日菜单表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
date	TEXT	NOT NULL	日期 YYYY-MM-DD
meal_type	TEXT	NOT NULL	breakfast / lunch / dinner
diner_count	INTEGER	DEFAULT 1	用餐人数（联动步进器）
diet_mode	TEXT	DEFAULT ‘maintenance’	weight_loss / muscle_gain / maintenance / gathering / cheat_day
is_confirmed	INTEGER	DEFAULT 0	掌勺人是否确认
confirmed_by	TEXT	FK → users.id	确认人
confirmed_at	TEXT		确认时间
chef_id	TEXT	FK → users.id	当前掌勺人
is_deleted	INTEGER	DEFAULT 0
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

约束：

• UNIQUE(family_id, date, meal_type) — 每个家庭每天每个餐别仅一条记录

索引：

• idx_dm_family_date ON (family_id, date)
• idx_dm_chef_id ON (chef_id)

diet_mode 对应系数（业务层）：

diet_mode	TDEE 系数
weight_loss	0.80
muscle_gain	1.10
maintenance	1.00
gathering	1.10
cheat_day	1.20

---

4.12 menu_items — 菜单菜品关联表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
menu_id	TEXT	NOT NULL, FK → daily_menus.id
recipe_id	TEXT	NOT NULL, FK → recipes.id
is_locked	INTEGER	DEFAULT 0	锁定后不参与”换一换”刷新
servings_override	INTEGER		覆盖菜谱默认份数，NULL 则使用 recipe.servings
status	TEXT	DEFAULT ‘pending’	pending / cooking / completed
sort_order	INTEGER	DEFAULT 0	展示排序
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

约束：

• UNIQUE(menu_id, recipe_id) — 同一菜单中同一菜谱仅出现一次

索引：

• idx_mi_menu_id ON (menu_id)
• idx_mi_status ON (status)

---

4.13 wishlist_items — 心愿单表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
recipe_id	TEXT	NOT NULL, FK → recipes.id
suggested_by	TEXT	NOT NULL, FK → users.id	推荐人
meal_type	TEXT		建议餐别（可选）
is_adopted	INTEGER	DEFAULT 0	是否已被掌勺人采纳
adopted_at	TEXT		采纳时间
adopted_menu_id	TEXT	FK → daily_menus.id	采纳后关联的菜单
created_at	TEXT	NOT NULL

约束：

• UNIQUE(family_id, recipe_id, suggested_by) — 同一用户对同一菜谱在同一家庭不重复推荐

索引：

• idx_wl_family_id ON (family_id)
• idx_wl_is_adopted ON (is_adopted)
• idx_wl_suggested_by ON (suggested_by)

---

4.14 inventory_batches — 库存批次表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
ingredient_id	TEXT	NOT NULL, FK → ingredients.id
batch_quantity	REAL	NOT NULL	本批次当前数量
unit	TEXT	NOT NULL	单位（固定为 g 或 ml，与 ingredients.unit 一致）
expiry_date	TEXT		本批次过期日期
purchase_date	TEXT		采购/入库日期
source	TEXT	DEFAULT ‘manual_add’	入库来源
added_by	TEXT	FK → users.id	入库操作人
is_deleted	INTEGER	DEFAULT 0
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

约束：

• UNIQUE(family_id, ingredient_id, purchase_date, expiry_date) — 同一食材同日入库同过期日仅一条批次记录，防止重复入库

source 枚举： manual_add（手动入库）、purchase_list_batch（采购清单批量入库）、scan_add（扫码入库）

库存状态（业务层动态计算，不持久化）：

条件	status
batch_quantity > 0 AND 未过期 AND 距过期 > 7天	fresh
batch_quantity > 0 AND 距过期 ≤ 7天 AND > 3天	low
batch_quantity > 0 AND 距过期 ≤ 3天	expiring_soon
batch_quantity > 0 AND 已过期	expired
batch_quantity ≤ 0	depleted

核销策略（业务层）： 采用 FIFO（先进先出）原则。核销时优先消耗 purchase_date 最早的批次；同日入库则优先消耗 expiry_date 最近的批次。

索引：

• idx_ib_family_id ON (family_id)
• idx_ib_ingredient_id ON (ingredient_id)
• idx_ib_expiry_date ON (expiry_date)
• idx_ib_family_ingredient ON (family_id, ingredient_id)
• idx_ib_fifo ON (family_id, ingredient_id, purchase_date ASC, expiry_date ASC)

---

4.15 inventory_logs — 库存操作日志表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
batch_id	TEXT	NOT NULL, FK → inventory_batches.id	关联批次记录
ingredient_id	TEXT	NOT NULL, FK → ingredients.id	冗余，便于查询
operation	TEXT	NOT NULL	操作类型
quantity_change	REAL	NOT NULL	变动量（正=入库, 负=核销）
quantity_before	REAL	NOT NULL	操作前批次库存
quantity_after	REAL	NOT NULL	操作后批次库存
reason	TEXT	NOT NULL	操作原因
related_menu_id	TEXT	FK → daily_menus.id	烹饪核销时关联菜单
operator_id	TEXT	NOT NULL, FK → users.id
snapshot_recipe_amount	REAL		核销时的食材用量快照（g 或 ml）
snapshot_servings	REAL		核销时的份数快照
snapshot_recipe_name	TEXT		核销时的菜谱名称快照
snapshot_ingredient_name	TEXT		核销时的食材名称快照
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

operation 枚举： add（入库）、deduct（核销扣减）、edit（手动编辑）、clear（一键清零）

reason 枚举： manual_add（手动入库）、purchase_list_batch（采购清单批量入库）、scan_add（扫码入库）、cooking_deduction（烹饪核销）、manual_edit（手动编辑）、manual_clear（手动清空）

核销快照规则（防幻读）：

当 reason = 'cooking_deduction' 时，以下快照字段必须在确认核销的瞬间固化，不得在扣减时重新读取菜谱数据：

快照字段	固化时机	数据来源
snapshot_recipe_amount	点击”确认核销”时	recipe_ingredients.amount（单位为 g 或 ml）
snapshot_servings	点击”确认核销”时	COALESCE(menu_items.servings_override, recipes.servings)
snapshot_recipe_name	点击”确认核销”时	recipes.name
snapshot_ingredient_name	点击”确认核销”时	ingredients.name

目的：防止用户在烹饪过程中修改菜谱用量或份数，导致历史核销记录与实际扣减量对不上。

索引：

• idx_il_batch_id ON (batch_id)
• idx_il_family_id ON (family_id)
• idx_il_created_at ON (created_at)
• idx_il_related_menu ON (related_menu_id)
• idx_il_family_created ON (family_id, created_at)

---

4.16 shopping_list_items — 采购清单项表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
ingredient_id	TEXT	NOT NULL, FK → ingredients.id
required_quantity	REAL	NOT NULL	需采购量（单位与 ingredients.unit 一致，为 g 或 ml）
is_purchased	INTEGER	DEFAULT 0	是否已购买
purchased_at	TEXT		购买时间
source_menu_id	TEXT	FK → daily_menus.id	来源菜单
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

说明（v1.4 简化）： 删除了 shopping_list_items.unit 字段。单位统一继承自 ingredients.unit，无需重复存储。

索引：

• idx_sli_family_id ON (family_id)
• idx_sli_is_purchased ON (is_purchased)

---

4.17 consumption_records — 饮食消费记录表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, FK → users.id
family_id	TEXT	NOT NULL, FK → families.id
date	TEXT	NOT NULL	YYYY-MM-DD
meal_type	TEXT	NOT NULL	breakfast / lunch / dinner / snack
recipe_id	TEXT	FK → recipes.id
servings	REAL	DEFAULT 1.0	食用份数
calories	REAL		摄入热量 (kcal)
protein	REAL		摄入蛋白质 (g)
carbs	REAL		摄入碳水 (g)
fat	REAL		摄入脂肪 (g)
menu_item_id	TEXT	FK → menu_items.id	关联菜单项（核销产生）
is_deleted	INTEGER	DEFAULT 0	支持撤销记录
version	INTEGER	DEFAULT 1	乐观锁版本号
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

索引：

• idx_cr_user_date ON (user_id, date)
• idx_cr_family_date ON (family_id, date)
• idx_cr_meal_type ON (meal_type)

---

4.18 taste_preferences — 口味偏好表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, UNIQUE, FK → users.id
salty	REAL	DEFAULT 0.5	咸 (0.0–1.0)
sweet	REAL	DEFAULT 0.5	甜 (0.0–1.0)
sour	REAL	DEFAULT 0.5	酸 (0.0–1.0)
spicy	REAL	DEFAULT 0.5	辣 (0.0–1.0)
umami	REAL	DEFAULT 0.5	鲜 (0.0–1.0)
version	INTEGER	DEFAULT 1	乐观锁版本号
updated_at	TEXT	NOT NULL

索引：

• idx_tp_user_id ON (user_id)

---

4.19 ingredient_blacklist — 食材黑名单表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, FK → users.id
ingredient_id	TEXT	NOT NULL, FK → ingredients.id
created_at	TEXT	NOT NULL

约束：

• UNIQUE(user_id, ingredient_id) — 同一食材不重复加入黑名单

索引：

• idx_ibl_user_id ON (user_id)

---

4.20 user_allergens — 用户过敏源表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, FK → users.id
ingredient_id	TEXT	NOT NULL, FK → ingredients.id
severity	TEXT	DEFAULT ‘mild’	mild / moderate / severe
created_at	TEXT	NOT NULL

约束：

• UNIQUE(user_id, ingredient_id) — 同一过敏源不重复记录

索引：

• idx_ua_user_id ON (user_id)

---

4.21 nutrition_profiles — 营养档案表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
user_id	TEXT	NOT NULL, UNIQUE, FK → users.id
daily_calorie_target	REAL		自定义每日热量目标，NULL 则使用 users.tdee
calorie_deficit	REAL	DEFAULT 0	自定义热量缺口 (kcal)
protein_ratio	REAL	DEFAULT 0.30	蛋白质供能比 (0.30 = 30%)
carbs_ratio	REAL	DEFAULT 0.45	碳水供能比 (0.45 = 45%)
fat_ratio	REAL	DEFAULT 0.25	脂肪供能比 (0.25 = 25%)
reminder_interval	INTEGER	DEFAULT 14	身体数据更新提醒周期 (天)
next_reminder_at	TEXT		下次提醒时间
version	INTEGER	DEFAULT 1	乐观锁版本号
updated_at	TEXT	NOT NULL

---

4.22 health_reports — 统计报告表

字段名	类型	约束	说明
id	TEXT	PK, NOT NULL	UUID
family_id	TEXT	NOT NULL, FK → families.id
user_id	TEXT	FK → users.id	个人报告时指定，家庭报告时为 NULL
report_type	TEXT	NOT NULL	weekly / monthly
report_scope	TEXT	NOT NULL	personal / family
start_date	TEXT	NOT NULL	YYYY-MM-DD
end_date	TEXT	NOT NULL	YYYY-MM-DD
report_data	TEXT	NOT NULL	JSON（SQLite 为 TEXT，PostgreSQL 为 JSONB）
generated_at	TEXT	NOT NULL

索引：

• idx_hr_family_id ON (family_id)
• idx_hr_user_id ON (user_id)
• idx_hr_period ON (start_date, end_date)

PostgreSQL 建表说明： report_data 列在 PostgreSQL 中应使用 JSONB 类型，以支持 JSON 内部字段索引和高效查询。

report_data JSON 结构示例：

{
  "total_calories": 15420,
  "avg_daily_calories": 2203,
  "calorie_compliance_rate": 0.92,
  "nutrition_breakdown": {
    "protein_avg": 75.2,
    "carbs_avg": 210.5,
    "fat_avg": 55.3
  },
  "top_recipes": [
    { "recipe_id": "xxx", "name": "番茄炒蛋", "count": 4 }
  ],
  "health_score": 85
}

---

4.23 sync_queue — 同步队列表（仅本地 SQLite）

字段名	类型	约束	说明
id	INTEGER	PK, AUTOINCREMENT	本地自增 ID
entity_type	TEXT	NOT NULL	实体表名
entity_id	TEXT	NOT NULL	实体 UUID
operation	TEXT	NOT NULL	INSERT / UPDATE / DELETE
payload	TEXT	NOT NULL	变更数据的完整 JSON
base_version	INTEGER	NOT NULL	同步时基于的实体版本号（乐观锁）
retry_count	INTEGER	DEFAULT 0	重试次数
max_retries	INTEGER	DEFAULT 5	最大重试次数
status	TEXT	DEFAULT ‘pending’	pending / syncing / completed / failed
error_message	TEXT		最近一次错误信息
created_at	TEXT	NOT NULL
last_retry_at	TEXT

索引：

• idx_sq_status ON (status)
• idx_sq_entity_type_id ON (entity_type, entity_id)
• idx_sq_created_at ON (created_at)

乐观锁工作流程：

1. 本地修改实体时，version 自增 +1，同时将变更写入 sync_queue，base_version 记录修改前的版本号。
2. 推送到云端时，云端比较 base_version 与当前云端 version：若一致则应用更新并 version +1；若不一致则返回冲突，由业务层按时间戳策略解决。

---

五、外键级联策略

核心原则：不使用数据库层面的 ON DELETE CASCADE，家庭解散采用混合删除策略：

数据类型	删除方式	理由
历史业务数据（菜单、库存、心愿单等）	软删除 (is_deleted = 1)	保留历史记录，支持审计追溯
临时性/关系型数据（采购清单、成员关系）	物理删除 (DELETE)	无需保留，减少冗余数据

理由：

1. 本项目采用混合删除策略：历史业务数据软删除，临时数据物理删除。
2. 数据库级联删除无法产生 sync_queue 记录，会导致其他设备无法同步到删除事件。
3. 业务层级联可以添加权限校验（如：仅 owner 可解散家庭）。

事务边界：家庭解散的软删除操作必须在单个数据库事务中执行，全部成功或全部回滚。事务外的 WebSocket 广播和 sync_queue 写入失败不影响事务成功状态。

家庭解散时的级联删除顺序（业务层，事务内按序执行）：

BEGIN TRANSACTION;

--- 历史业务数据：软删除（保留记录，支持审计） ---

(1) daily_menus (软删除, is_deleted = 1)
   → 批量 UPDATE，同时获取所有 menu_id

(2) menu_items (软删除, is_deleted = 1)
   → WHERE menu_id IN (上述menu_ids)

(3) wishlist_items (软删除, is_deleted = 1)
   → WHERE family_id = ?

(4) inventory_batches (软删除, is_deleted = 1)
   → WHERE family_id = ?

--- 临时性数据：物理删除（无需保留） ---

(5) shopping_list_items (物理删除)
   → WHERE family_id = ? (采购清单无需保留)

(6) family_members (物理删除)
   → WHERE family_id = ? (成员关系无需保留)

--- 家庭主体：软删除 ---

(7) families (软删除, is_deleted = 1)
   → WHERE id = ?

COMMIT;

事务外操作（事务成功后才执行）：

• 步骤 (1)-(7) 的每条变更逐一写入本地 sync_queue，确保其他设备能同步到删除事件
• WebSocket 广播 family_dissolved 消息给所有被删除家庭成员
• 客户端收到消息后清理 localStorage 中该家庭相关缓存

库存日志保留：inventory_logs 不执行任何删除操作，保留完整审计记录（family_id 字段保留，无需归档）。

错误处理：

• 事务执行失败 → 返回 5001，全部回滚，无任何数据变更
• 事务成功但 WebSocket 广播失败 → 不影响事务状态，成员下次上线时通过 GET /sync/delta 发现家庭已解散
• 事务成功但 sync_queue 写入失败 → 不影响事务状态，下次网络恢复后通过本地数据库状态同步到云端

---

六、本地 (SQLite) 与云端 (PostgreSQL) 映射

表名	本地 SQLite	云端 PostgreSQL	同步方向
users	✅	✅	双向
families	✅	✅	双向
family_members	✅	✅	双向
ingredients	✅ (缓存)	✅ (主)	云端 → 本地
recipes	✅ (缓存)	✅ (主)	云端 → 本地
recipe_ingredients	✅ (缓存)	✅ (主)	云端 → 本地
recipe_steps	✅ (缓存)	✅ (主)	云端 → 本地
tags	✅ (缓存)	✅ (主)	云端 → 本地
recipe_tag_relations	✅ (缓存)	✅ (主)	云端 → 本地
recipe_favorites	✅	✅	双向
daily_menus	✅	✅	双向
menu_items	✅	✅	双向
wishlist_items	✅	✅	双向
inventory_batches	✅	✅	双向
inventory_logs	✅	✅	双向
shopping_list_items	✅	✅	双向
consumption_records	✅	✅	双向
taste_preferences	✅	✅	双向
ingredient_blacklist	✅	✅	双向
user_allergens	✅	✅	双向
nutrition_profiles	✅	✅	双向
health_reports	✅	✅	双向
sync_queue	✅	❌	仅本地

同步策略要点：

• 全局数据（ingredients、recipes 及其关联表、tags）：云端为唯一数据源，本地作为只读缓存。用户自定义内容也通过云端中转共享。
• 家庭/个人数据：双向同步。离线时写入本地 SQLite 并加入 sync_queue；网络恢复后按 created_at 顺序依次推送到云端。
• 冲突解决：基于乐观锁 version 字段。推送时携带 base_version，云端比较后决定接受或拒绝；拒绝时比较 updated_at 时间戳，云端较新则覆盖本地，本地较新则覆盖云端，时间相同保留云端。
• 删除策略：采用软删除 (is_deleted = 1)，同步时检测该字段变化。定期由后台任务清理超过 30 天的已删除记录。

---

七、关键业务 SQL 示例

7.1 智能采购清单生成

SELECT
    ri.ingredient_id,
    i.name,
    SUM(ri.amount * COALESCE(mi.servings_override, r.servings)) AS required_total,
    COALESCE(inv.current_stock, 0) AS current_stock,
    MAX(SUM(ri.amount * COALESCE(mi.servings_override, r.servings)) - COALESCE(inv.current_stock, 0), 0) AS need_to_buy
FROM daily_menus dm
JOIN menu_items mi ON mi.menu_id = dm.id
JOIN recipes r ON r.id = mi.recipe_id
JOIN recipe_ingredients ri ON ri.recipe_id = r.id
JOIN ingredients i ON i.id = ri.ingredient_id
LEFT JOIN (
    SELECT family_id, ingredient_id, SUM(batch_quantity) AS current_stock
    FROM inventory_batches
    WHERE is_deleted = 0
    GROUP BY family_id, ingredient_id
) inv ON inv.family_id = dm.family_id AND inv.ingredient_id = ri.ingredient_id
WHERE dm.family_id = ?
  AND dm.date = ?
  AND dm.is_deleted = 0
  AND mi.status IN ('pending', 'cooking')
GROUP BY ri.ingredient_id
HAVING need_to_buy > 0
ORDER BY need_to_buy DESC;

7.2 库存过期预警查询（按批次）

SELECT
    ib.id AS batch_id,
    i.name,
    ib.batch_quantity,
    ib.unit,
    ib.expiry_date,
    ib.purchase_date,
    CASE
        WHEN ib.expiry_date IS NULL THEN 'no_expiry'
        WHEN ib.expiry_date < date('now') THEN 'expired'
        WHEN ib.expiry_date <= date('now', '+3 days') THEN 'expiring_soon'
        WHEN ib.expiry_date <= date('now', '+7 days') THEN 'low'
        ELSE 'fresh'
    END AS status
FROM inventory_batches ib
JOIN ingredients i ON i.id = ib.ingredient_id
WHERE ib.family_id = ?
  AND ib.is_deleted = 0
  AND ib.batch_quantity > 0
ORDER BY ib.expiry_date ASC;

7.3 今日营养摄入统计

SELECT
    SUM(cr.calories) AS total_calories,
    SUM(cr.protein) AS total_protein,
    SUM(cr.carbs) AS total_carbs,
    SUM(cr.fat) AS total_fat
FROM consumption_records cr
WHERE cr.user_id = ?
  AND cr.date = ?
  AND cr.is_deleted = 0;

7.4 心愿单待采纳列表

SELECT
    wl.id,
    r.name AS recipe_name,
    r.cover_image_url,
    r.total_calories,
    u.nickname AS suggested_by_name,
    wl.meal_type,
    wl.created_at
FROM wishlist_items wl
JOIN recipes r ON r.id = wl.recipe_id
JOIN users u ON u.id = wl.suggested_by
WHERE wl.family_id = ?
  AND wl.is_adopted = 0
ORDER BY wl.created_at DESC;

7.5 烹饪核销 — 计算消耗食材清单（FIFO 批次，含快照固化）

WITH consume_needs AS (
    SELECT
        ri.ingredient_id,
        i.name,
        ri.amount AS recipe_amount,
        COALESCE(mi.servings_override, r.servings) AS servings,
        ri.amount * COALESCE(mi.servings_override, r.servings) AS consume_amount
    FROM menu_items mi
    JOIN recipes r ON r.id = mi.recipe_id
    JOIN recipe_ingredients ri ON ri.recipe_id = r.id
    JOIN ingredients i ON i.id = ri.ingredient_id
    WHERE mi.menu_id = ?
      AND mi.status = 'cooking'
)
SELECT
    cn.ingredient_id,
    cn.name,
    cn.consume_amount,
    cn.servings,
    ib.id AS batch_id,
    ib.batch_quantity,
    ib.expiry_date
FROM consume_needs cn
JOIN inventory_batches ib ON ib.ingredient_id = cn.ingredient_id
    AND ib.family_id = ?
    AND ib.is_deleted = 0
    AND ib.batch_quantity > 0
ORDER BY cn.ingredient_id, ib.purchase_date ASC, ib.expiry_date ASC;

业务层快照固化步骤：

1. 执行上述 SQL 获取核销清单
2. 将 cn.consume_amount、cn.servings、cn.name 固化到 inventory_logs 的 snapshot_* 字段
3. 执行批次扣减
4. 不再重新读取 recipe_ingredients 或 menu_items

7.6 菜谱推荐过滤（排除黑名单食材 + 过敏源）

SELECT DISTINCT r.*
FROM recipes r
WHERE r.is_deleted = 0
  AND r.id NOT IN (
      SELECT ri.recipe_id
      FROM recipe_ingredients ri
      WHERE ri.ingredient_id IN (
          SELECT ingredient_id FROM ingredient_blacklist WHERE user_id = ?
          UNION
          SELECT ingredient_id FROM user_allergens WHERE user_id = ?
      )
  )
ORDER BY r.popularity DESC
LIMIT ?;

7.7 查询食材总库存（跨批次聚合）

SELECT
    i.id AS ingredient_id,
    i.name,
    i.unit,
    SUM(ib.batch_quantity) AS total_quantity,
    MIN(ib.expiry_date) AS earliest_expiry
FROM inventory_batches ib
JOIN ingredients i ON i.id = ib.ingredient_id
WHERE ib.family_id = ?
  AND ib.is_deleted = 0
  AND ib.batch_quantity > 0
GROUP BY i.id
ORDER BY earliest_expiry ASC;

7.8 用户收藏菜谱列表

SELECT r.*
FROM recipe_favorites rf
JOIN recipes r ON r.id = rf.recipe_id
WHERE rf.user_id = ?
  AND r.is_deleted = 0
ORDER BY rf.created_at DESC;

7.9 查询食材是否被库存或菜谱引用（属性锁定判断）

SELECT
    CASE
        WHEN EXISTS (SELECT 1 FROM inventory_batches WHERE ingredient_id = ? AND batch_quantity > 0 AND is_deleted = 0) THEN 1
        WHEN EXISTS (SELECT 1 FROM recipe_ingredients WHERE ingredient_id = ?) THEN 1
        ELSE 0
    END AS is_referenced;

---

八、数据迁移与初始化

8.1 系统内置数据预置

首次启动时，本地 SQLite 需预置以下基础数据：

• tags 表：预置口味标签（咸鲜、微辣、麻辣、清淡、酸甜、香辣、酱香）、烹饪方式标签（快手、慢炖、蒸、炒、烤、凉拌）以及场景标签（家常、宴客、便当、早餐推荐）。
• ingredients 表：预置约 100 种常用食材及标准营养成分（参考中国食物成分表），所有食材的 unit 统一为 g 或 ml。
• recipes 表：预置约 50 道家常菜谱及其关联的 recipe_ingredients 和 recipe_steps。

8.2 云端初始化

云端 PostgreSQL 使用相同的 DDL 建表脚本（report_data 列改用 JSONB 类型）。本地首次连接云端时，将本地的系统内置数据上传至云端（通过 entity_type 判断是否为系统数据），后续其他设备加入同一家庭时从云端拉取。

8.3 v1.4 → v1.5 迁移说明（简化版）

变更项	迁移操作
ingredients 删除 brand	ALTER TABLE ingredients DROP COLUMN brand;
ingredients 删除 default_weight_per_unit	ALTER TABLE ingredients DROP COLUMN default_weight_per_unit;
ingredients 删除 is_locked	ALTER TABLE ingredients DROP COLUMN is_locked;
recipe_ingredients 删除 unit	ALTER TABLE recipe_ingredients DROP COLUMN unit;
shopping_list_items 删除 unit	ALTER TABLE shopping_list_items DROP COLUMN unit;
inventory_logs 删除 snapshot_recipe_unit	ALTER TABLE inventory_logs DROP COLUMN snapshot_recipe_unit;
inventory_batches 新增唯一约束	需先清理重复数据，然后 CREATE UNIQUE INDEX idx_ib_unique_batch ON inventory_batches(family_id, ingredient_id, purchase_date, expiry_date);
inventory_logs 新增快照字段	ALTER TABLE inventory_logs ADD COLUMN snapshot_recipe_amount REAL; 及 snapshot_servings REAL;、snapshot_recipe_name TEXT;、snapshot_ingredient_name TEXT;
inventory_logs 新增复合索引	CREATE INDEX idx_il_family_created ON inventory_logs(family_id, created_at);
inventory_batches 新增 FIFO 索引	CREATE INDEX idx_ib_fifo ON inventory_batches(family_id, ingredient_id, purchase_date ASC, expiry_date ASC);

说明：v1.4 的迁移相对简单，主要是删除不再需要的字段。单位统一后，采购清单和库存核销的 SQL 不再需要按 unit 分组，逻辑大幅简化。

---

九、性能考量

场景	策略
食谱库加载	recipes 分页查询，每页 20 条；图片使用懒加载
冰箱库存列表	按 family_id 索引查询，跨批次 SUM(batch_quantity) 聚合（通常 < 200 条，性能可控）
同步冲突	使用 version 乐观锁字段 + base_version 比对，单条记录粒度
统计报告生成	异步后台计算，结果存入 health_reports 表缓存，避免每次实时聚合
搜索	对 name 列建立索引；不引入全文搜索（MVP 阶段数据量可控）
批次核销	FIFO 排序使用 (purchase_date, expiry_date) 复合索引，单次核销涉及批次数通常 ≤ 5
库存流水查询	使用 (family_id, created_at) 复合索引，支持按家庭维度快速生成流水报表
采购清单计算	按 ingredient_id 分组聚合，无需按 unit 分组（v1.4 单位统一后）