YaoXiang/docs/database.md

数据库设计文档

1. 数据库概述

系统使用 SQLite 作为主数据库，支持未来升级到 PostgreSQL。数据库设计遵循第三范式，确保数据一致性和查询效率。

2. ER 图

┌─────────────────┐       ┌─────────────────┐
│     users       │       │   categories    │
├─────────────────┤       ├─────────────────┤
│ id (PK)         │       │ id (PK)         │
│ username        │       │ name            │
│ password_hash   │       │ parent_id (FK)  │
│ role            │       │ level           │
│ display_name    │       │ icon            │
│ email           │       │ sort_order      │
│ notification_   │       │ created_at      │
│   level         │       │ updated_at      │
│ is_active       │       └─────────────────┘
│ created_at      │              │
│ updated_at      │              │
└─────────────────┘              │
        │                        │
        │                        │
        ▼                        ▼
┌─────────────────────────────────────────┐
│              medicines                  │
├─────────────────────────────────────────┤
│ id (PK)                                 │
│ name                                    │
│ generic_name                            │
│ brand_name                              │
│ manufacturer                            │
│ specification                           │
│ category_id (FK)                        │
│ description                             │
│ indications                             │
│ adult_dose                              │
│ child_dose                              │
│ contraindications                       │
│ notes                                   │
│ image_front_path                        │
│ image_expiry_path                       │
│ image_leaflet_paths (JSON)              │
│ expiry_grace_days (默认0,最大60)         │
│ created_by (FK → users)                 │
│ created_at                              │
│ updated_at                              │
└─────────────────────────────────────────┘
        │
        │
        ▼
┌─────────────────┐       ┌─────────────────┐
│     batches     │       │  audit_logs     │
├─────────────────┤       ├─────────────────┤
│ id (PK)         │       │ id (PK)         │
│ medicine_id (FK)│       │ medicine_id (FK)│
│ batch_no        │       │ batch_id (FK)   │
│ production_date │       │ user_id (FK)    │
│ expiry_date     │       │ action          │
│ quantity        │       │ quantity_change │
│ location        │       │ quantity_after  │
│ is_expired      │       │ remark          │
│ created_at      │       │ created_at      │
│ updated_at      │       └─────────────────┘
└─────────────────┘
        │
        │
        ▼
┌─────────────────┐       ┌─────────────────┐
│notifications    │       │  settings       │
├─────────────────┤       ├─────────────────┤
│ id (PK)         │       │ id (PK)         │
│ type            │       │ key             │
│ title           │       │ value           │
│ content         │       │ description     │
│ is_read         │       │ updated_at      │
│ user_id (FK)    │       └─────────────────┘
│ created_at      │
└─────────────────┘

3. 表结构定义

3.1 users 表（用户表）

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user' CHECK(role IN ('admin', 'user', 'readonly')),
    display_name VARCHAR(100),
    email VARCHAR(100),
    notification_level VARCHAR(20) DEFAULT 'normal' CHECK(notification_level IN ('none', 'low', 'normal', 'high')),
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
username	VARCHAR(50)	是	-	用户名，唯一
password_hash	VARCHAR(255)	是	-	密码哈希值
role	VARCHAR(20)	是	'user'	角色：admin/user/readonly
display_name	VARCHAR(100)	否	NULL	显示名称
email	VARCHAR(100)	否	NULL	邮箱
notification_level	VARCHAR(20)	否	'normal'	通知等级
is_active	BOOLEAN	否	1	是否启用
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	否	CURRENT_TIMESTAMP	更新时间

3.2 categories 表（分类表）

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER,
    level INTEGER NOT NULL DEFAULT 1 CHECK(level IN (1, 2)),
    icon VARCHAR(50),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- 索引
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_level ON categories(level);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
name	VARCHAR(100)	是	-	分类名称
parent_id	INTEGER	否	NULL	父分类ID
level	INTEGER	是	1	分类层级：1或2
icon	VARCHAR(50)	否	NULL	图标
sort_order	INTEGER	否	0	排序顺序
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	否	CURRENT_TIMESTAMP	更新时间

预设分类数据：

-- 一级分类
INSERT INTO categories (name, level, icon, sort_order) VALUES
('药品', 1, 'medicine', 1),
('医疗器械', 1, 'medical', 2),
('应急用品', 1, 'emergency', 3),
('消耗品', 1, 'consumable', 4);

-- 二级分类 - 药品
INSERT INTO categories (name, parent_id, level, sort_order) VALUES
('感冒药', 1, 2, 1),
('退烧药', 1, 2, 2),
('止泻药', 1, 2, 3),
('消炎药', 1, 2, 4),
('外用药', 1, 2, 5);

-- 二级分类 - 医疗器械
INSERT INTO categories (name, parent_id, level, sort_order) VALUES
('血压计', 2, 2, 1),
('血糖仪', 2, 2, 2),
('体温计', 2, 2, 3);

-- 二级分类 - 应急用品
INSERT INTO categories (name, parent_id, level, sort_order) VALUES
('创可贴', 3, 2, 1),
('绷带', 3, 2, 2),
('止血带', 3, 2, 3);

-- 二级分类 - 消耗品
INSERT INTO categories (name, parent_id, level, sort_order) VALUES
('酒精棉片', 4, 2, 1),
('N95', 4, 2, 2),
('医用手套', 4, 2, 3);

3.3 medicines 表（药品表）

CREATE TABLE medicines (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(200) NOT NULL,
    generic_name VARCHAR(200),
    brand_name VARCHAR(200),
    manufacturer VARCHAR(200),
    specification VARCHAR(200),
    category_id INTEGER,
    description TEXT,
    indications TEXT,
    adult_dose TEXT,
    child_dose TEXT,
    contraindications TEXT,
    notes TEXT,
    image_front_path VARCHAR(500),
    image_expiry_path VARCHAR(500),
    image_leaflet_paths JSON,
    expiry_grace_days INTEGER DEFAULT 0 CHECK(expiry_grace_days >= 0 AND expiry_grace_days <= 60),
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- 索引
CREATE INDEX idx_medicines_name ON medicines(name);
CREATE INDEX idx_medicines_category_id ON medicines(category_id);
CREATE INDEX idx_medicines_created_by ON medicines(created_by);
CREATE INDEX idx_medicines_generic_name ON medicines(generic_name);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
name	VARCHAR(200)	是	-	药品名称
generic_name	VARCHAR(200)	否	NULL	通用名称
brand_name	VARCHAR(200)	否	NULL	商品名称
manufacturer	VARCHAR(200)	否	NULL	生产厂家
specification	VARCHAR(200)	否	NULL	规格
category_id	INTEGER	否	NULL	分类ID
description	TEXT	否	NULL	描述
indications	TEXT	否	NULL	适应症（用于搜索）
adult_dose	TEXT	否	NULL	成人用量
child_dose	TEXT	否	NULL	儿童用量
contraindications	TEXT	否	NULL	禁忌
notes	TEXT	否	NULL	注意事项
image_front_path	VARCHAR(500)	否	NULL	药盒正面图片路径
image_expiry_path	VARCHAR(500)	否	NULL	有效期图片路径
image_leaflet_paths	JSON	否	NULL	说明书图片路径数组
expiry_grace_days	INTEGER	否	0	有效期宽限天数（最大60天）
created_by	INTEGER	否	NULL	创建者用户ID
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	否	CURRENT_TIMESTAMP	更新时间

3.4 batches 表（批次表）

CREATE TABLE batches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    medicine_id INTEGER NOT NULL,
    batch_no VARCHAR(100),
    production_date DATE,
    expiry_date DATE NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0 CHECK(quantity >= 0),
    location VARCHAR(200),
    is_expired BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_batches_medicine_id ON batches(medicine_id);
CREATE INDEX idx_batches_expiry_date ON batches(expiry_date);
CREATE INDEX idx_batches_is_expired ON batches(is_expired);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
medicine_id	INTEGER	是	-	药品ID
batch_no	VARCHAR(100)	否	NULL	批次号
production_date	DATE	否	NULL	生产日期
expiry_date	DATE	是	-	过期日期
quantity	INTEGER	是	0	库存数量
location	VARCHAR(200)	否	NULL	存放位置
is_expired	BOOLEAN	否	0	是否已过期
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	否	CURRENT_TIMESTAMP	更新时间

3.5 audit_logs 表（审计日志表）

CREATE TABLE audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    medicine_id INTEGER NOT NULL,
    batch_id INTEGER,
    user_id INTEGER,
    action VARCHAR(50) NOT NULL CHECK(action IN ('add_stock', 'dispense', 'adjust', 'delete', 'modify')),
    quantity_change INTEGER NOT NULL,
    quantity_after INTEGER NOT NULL,
    remark TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON DELETE CASCADE,
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 索引
CREATE INDEX idx_audit_logs_medicine_id ON audit_logs(medicine_id);
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
medicine_id	INTEGER	是	-	药品ID
batch_id	INTEGER	否	NULL	批次ID
user_id	INTEGER	否	NULL	操作用户ID
action	VARCHAR(50)	是	-	操作类型
quantity_change	INTEGER	是	-	数量变化（正数增加，负数减少）
quantity_after	INTEGER	是	-	操作后数量
remark	TEXT	否	NULL	备注
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间

3.6 notifications 表（通知表）

CREATE TABLE notifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type VARCHAR(50) NOT NULL CHECK(type IN ('expiry_warning', 'low_stock', 'system')),
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT 0,
    user_id INTEGER,
    related_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);
CREATE INDEX idx_notifications_created_at ON notifications(created_at);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
type	VARCHAR(50)	是	-	通知类型
title	VARCHAR(200)	是	-	通知标题
content	TEXT	是	-	通知内容
is_read	BOOLEAN	否	0	是否已读
user_id	INTEGER	否	NULL	用户ID
related_id	INTEGER	否	NULL	关联ID（药品/批次）
created_at	TIMESTAMP	否	CURRENT_TIMESTAMP	创建时间

3.7 settings 表（系统设置表）

CREATE TABLE settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key VARCHAR(100) NOT NULL UNIQUE,
    value TEXT,
    description VARCHAR(500),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_settings_key ON settings(key);

字段说明：

字段	类型	必填	默认值	说明
id	INTEGER	是	自增	主键
key	VARCHAR(100)	是	-	设置键名，唯一
value	TEXT	否	NULL	设置值
description	VARCHAR(500)	否	NULL	设置描述
updated_at	TIMESTAMP	否	CURRENT_TIMESTAMP	更新时间

预设设置数据：

INSERT INTO settings (key, value, description) VALUES
('ai_provider', 'openai', 'AI 服务提供者'),
('openai_api_key', '', 'OpenAI API Key'),
('openai_model', 'gpt-4o', 'OpenAI 模型'),
('notification_providers', '[]', '启用的通知提供者列表'),
('expiry_warning_days', '90,30,7', '到期提醒天数（逗号分隔）'),
('low_stock_threshold', '5', '低库存阈值'),
('max_upload_size', '10485760', '最大上传文件大小（字节）'),
('expiry_grace_days_max', '60', '有效期宽限最大天数');

4. 关系说明

4.1 一对多关系

• users → medicines: 一个用户可以创建多个药品
• users → audit_logs: 一个用户可以有多条审计日志
• users → notifications: 一个用户可以有多条通知
• categories → medicines: 一个分类可以包含多个药品
• categories → categories: 一个分类可以有多个子分类
• medicines → batches: 一个药品可以有多个批次
• medicines → audit_logs: 一个药品可以有多条审计日志

4.2 级联操作

• 删除用户：相关药品、审计日志、通知保留（created_by/set NULL）
• 删除分类：相关药品的 category_id 设为 NULL
• 删除药品：相关批次、审计日志级联删除
• 删除批次：相关审计日志的 batch_id 设为 NULL

5. 视图设计

5.1 药品库存视图

CREATE VIEW v_medicine_stock AS
SELECT 
    m.id,
    m.name,
    m.generic_name,
    m.brand_name,
    m.specification,
    c.name as category_name,
    COALESCE(SUM(b.quantity), 0) as total_quantity,
    MIN(b.expiry_date) as nearest_expiry_date,
    COUNT(b.id) as batch_count
FROM medicines m
LEFT JOIN batches b ON m.id = b.medicine_id AND b.is_expired = 0
LEFT JOIN categories c ON m.category_id = c.id
GROUP BY m.id;

5.2 即将过期药品视图

CREATE VIEW v_expiring_medicines AS
SELECT 
    m.id,
    m.name,
    m.expiry_grace_days,
    b.id as batch_id,
    b.batch_no,
    b.expiry_date,
    b.quantity,
    julianday(b.expiry_date) - julianday('now') as days_until_expiry
FROM medicines m
JOIN batches b ON m.id = b.medicine_id
WHERE b.is_expired = 0
AND b.expiry_date <= date('now', '+' || (90 + m.expiry_grace_days) || ' days');

6. 索引策略

6.1 主要索引

表名	索引名	字段	用途
users	idx_users_username	username	用户登录查询
medicines	idx_medicines_name	name	药品搜索
medicines	idx_medicines_category_id	category_id	分类筛选
batches	idx_batches_medicine_id	medicine_id	药品批次查询
batches	idx_batches_expiry_date	expiry_date	到期提醒查询
audit_logs	idx_audit_logs_created_at	created_at	审计日志时间查询

6.2 复合索引

-- 药品搜索复合索引
CREATE INDEX idx_medicines_search ON medicines(name, generic_name, brand_name);

-- 批次库存查询复合索引
CREATE INDEX idx_batches_stock ON batches(medicine_id, is_expired, expiry_date);

7. 数据迁移策略

7.1 使用 Alembic

# 初始化 Alembic
alembic init alembic

# 生成迁移脚本
alembic revision --autogenerate -m "initial"

# 执行迁移
alembic upgrade head

# 回滚迁移
alembic downgrade -1

7.2 版本控制

• 每次数据库变更都生成迁移脚本
• 迁移脚本存储在 alembic/versions/ 目录
• 支持向前和向后迁移

8. 数据备份策略

8.1 备份方案

# SQLite 备份
cp data/yaoxiang.db data/yaoxiang_backup_$(date +%Y%m%d).db

# 或使用 sqlite3 命令
sqlite3 data/yaoxiang.db ".backup 'data/yaoxiang_backup_$(date +%Y%m%d).db'"

8.2 自动备份

可通过 cron 任务定期备份：

# 每天凌晨2点备份
0 2 * * * /path/to/backup_script.sh