Haven/docs/backend/data-model.md

data-model — 数据库 schema

本文件回答：数据库有哪些表、每张表有哪些字段、约束、索引与业务规则。 依赖：无

一、书写约定

每张表统一按四部分书写：用途、字段表、索引、规则。

全局约定：

• 主键统一用 uuid，默认 gen_random_uuid()。需要 PostgreSQL 13+，或启用 pgcrypto 扩展。
• 时间字段统一用 timestamptz。
• 命名统一 snake_case。
• 枚举值用 text + CHECK，不用 PostgreSQL 原生 enum（便于后续加值不改类型）。
• 本期所有删除为硬删除，不做软删除。如需软删除，先改本文件再实现。
• 搜索依赖 pg_trgm 扩展，数据库初始化时必须执行 CREATE EXTENSION IF NOT EXISTS pg_trgm;。
• 不安装任何中文分词扩展（zhparser / pg_jieba / pg_bigm 等）。搜索走 PostgreSQL 自带的模糊匹配，理由见第八节。
• 所有业务数据表都带 user_id 归属字段，即使本期单人自用。这是技术预留：注册功能本期不实现，users 表只放唯一账号，但数据模型从第一天就按“未来可能有第二个账号”设计，避免以后改表。
• 数据表之间的依赖顺序：users → sessions / pending_logins / trusted_devices / tokens / audit_events → notes / tags / portfolio_items → note_tags / portfolio_repos。migration 必须按此顺序创建。

二、枚举值汇总

各表小节引用此处，不重复定义。

枚举名	取值	说明
note_visibility	private / public	笔记可见性
repo_platform	gogs / github	作品集仓库托管平台
token_scope	read / read_write	API Token 权限档位（用于 tokens 表，待补）

三、notes — 笔记

用途：存储所有笔记内容，私有与公开共用一张表，靠 visibility 区分。

字段	类型	约束	默认值	说明
id	uuid	PRIMARY KEY	gen_random_uuid()	主键
user_id	uuid	NOT NULL, FK → users(id) ON DELETE CASCADE	—	所属用户
title	text	NOT NULL	—	标题
content	text	NOT NULL	''	正文，允许空串
visibility	text	NOT NULL, CHECK (visibility IN ('private','public'))	'private'	可见性，默认私有
published_at	timestamptz	NULL	NULL	首次公开发布时间，仅 public 时有意义
search_text	text	GENERATED ALWAYS AS (`title		' '
created_at	timestamptz	NOT NULL	now()	创建时间
updated_at	timestamptz	NOT NULL	now()	最后更新时间，由应用层在每次写入时更新

索引：

-- 公开列表：按用户 + 发布时间倒序
CREATE INDEX idx_notes_public ON notes (user_id, published_at DESC)
  WHERE visibility = 'public' AND published_at IS NOT NULL;

-- 私有工作台：按用户 + 最近更新排序
CREATE INDEX idx_notes_updated_at ON notes (user_id, updated_at DESC);

-- 模糊搜索：search_text trigram 索引
CREATE INDEX idx_notes_search_trgm ON notes USING gin (search_text gin_trgm_ops);

规则：

1. 新建笔记一律 private：数据库层 DEFAULT 'private' + NOT NULL 兜底；创建接口不接受 visibility 参数（见 conventions.md 第四节）。
2. 公开是显式动作：private → public 通过独立的 publish 操作完成，该操作在 published_at IS NULL 时写入当前时间。
3. 撤回不清空 published_at：public → private 时 published_at 保留，语义为“首次公开发布时间”；再次公开不覆盖。
4. 公开可见的判定条件唯一：visibility = 'public' AND published_at IS NOT NULL。任何公开读路径必须使用此条件，不得只看 visibility。
5. published_at 对 private 笔记无意义：private 笔记不因 published_at 是否存在而改变可见性，该字段只服务于公开列表排序与未来 RSS。
6. search_text 由数据库自动维护：应用层不得在 INSERT / UPDATE 时写入该字段，也不得在 Go 代码中手动拼接。搜索查询统一使用 search_text，不直接查 title 或 content。
7. 搜索结果受可见性约束：搜索查询同样要经过授权中间件的 visibility 过滤（未登录只能搜到 public 笔记）。具体过滤规则见 docs/backend/auth.md，本表不重复定义。
8. 笔记归属唯一用户：所有笔记读写操作必须以当前登录用户为过滤条件，即 WHERE user_id = current_user_id。授权中间件在 visibility 过滤之外，必须叠加用户归属过滤。跨用户访问视为越权，返回 404 而非 403（不泄露笔记存在性）。

四、tags — 标签

用途：存储所有标签名，标签本身独立于笔记存在（允许创建后暂不关联任何笔记）。

字段	类型	约束	默认值	说明
id	uuid	PRIMARY KEY	gen_random_uuid()	主键
user_id	uuid	NOT NULL, FK → users(id) ON DELETE CASCADE	—	所属用户
name	text	NOT NULL	—	标签名
created_at	timestamptz	NOT NULL	now()	创建时间

索引：

-- 同一用户下标签名不区分大小写唯一
CREATE UNIQUE INDEX idx_tags_user_name_lower ON tags (user_id, lower(name));

-- 标签名模糊搜索
CREATE INDEX idx_tags_name_trgm ON tags USING gin (name gin_trgm_ops);

规则：

1. 标签按用户隔离：不同用户可拥有同名标签，互不影响。唯一性判断范围是 (user_id, lower(name))。
2. 标签名不区分大小写：Go 和 go 在同一用户下视为同一个标签。写入时应用层可保留原样，但唯一性判断以 lower(name) 为准。
3. 不提供重命名接口：本期标签只能创建和删除。删除标签时，note_tags 中的关联由外键 ON DELETE CASCADE 自动清理。如需重命名，先改本文件。
4. 标签查询受用户归属约束：所有标签查询必须加 WHERE user_id = current_user_id。

五、note_tags — 笔记与标签关联

用途：多对多关联表，一篇笔记可挂多个标签，一个标签可用于多篇笔记。

字段	类型	约束	默认值	说明
note_id	uuid	NOT NULL, FK → notes(id) ON DELETE CASCADE	—	笔记
tag_id	uuid	NOT NULL, FK → tags(id) ON DELETE CASCADE	—	标签

主键：(note_id, tag_id) 复合主键，天然防止重复关联。

索引：

-- 复合主键已覆盖 note_id 开头的查询；单独为 tag_id 建索引，支持"按标签查笔记"
CREATE INDEX idx_note_tags_tag_id ON note_tags (tag_id);

规则：

1. 关联无额外字段，保持最简。
2. 删除笔记或标签时关联自动清理，不在应用层做级联删除。
3. 跨用户关联防护：note_tags 本身没有 user_id，用户归属由 notes.user_id 和 tags.user_id 分别保证。应用层在创建关联时必须校验 notes.user_id = tags.user_id = current_user_id，防止把 A 用户的标签挂到 B 用户的笔记上。这一校验在应用层完成，数据库层不强制（可以用触发器强制，但增加复杂度，本期不做）。

六、portfolio_items — 作品集条目

用途：主页作品集的条目本体，一个作品可以关联多个仓库链接。

字段	类型	约束	默认值	说明
id	uuid	PRIMARY KEY	gen_random_uuid()	主键
user_id	uuid	NOT NULL, FK → users(id) ON DELETE CASCADE	—	所属用户
title	text	NOT NULL	—	作品标题
description	text	NOT NULL	''	简介
sort_order	int	NOT NULL	0	主页排序，小的在前
created_at	timestamptz	NOT NULL	now()	创建时间

索引：

CREATE INDEX idx_portfolio_items_sort ON portfolio_items (user_id, sort_order);

规则：

1. 作品不直接持有平台信息，平台信息在 portfolio_repos 表中。
2. 一个作品至少关联一个仓库（应用层校验，不在数据库层强制，避免“先建作品后加仓库”的两步操作被卡住）。
3. 作品归属唯一用户：本期主页按站点聚合展示（站点只有一个用户），查询时仍建议加 WHERE user_id = ? 作为未来扩展的保险。

七、portfolio_repos — 作品关联的仓库

用途：一个作品可关联多个仓库链接（如同时托管在 Gogs 和 GitHub）。

字段	类型	约束	默认值	说明
id	uuid	PRIMARY KEY	gen_random_uuid()	主键
portfolio_item_id	uuid	NOT NULL, FK → portfolio_items(id) ON DELETE CASCADE	—	所属作品
platform	text	NOT NULL, CHECK (platform IN ('gogs','github'))	—	托管平台
repo_path	text	NOT NULL	—	如 username/repo-name
is_primary	boolean	NOT NULL	false	是否主链接，主页默认跳转这一个

索引：

-- 同一作品下平台 + 路径唯一
CREATE UNIQUE INDEX idx_portfolio_repos_unique ON portfolio_repos (portfolio_item_id, platform, repo_path);

-- 每个作品最多一个主链接
CREATE UNIQUE INDEX idx_portfolio_repos_primary ON portfolio_repos (portfolio_item_id)
  WHERE is_primary = true;

规则：

1. 仓库地址只存 repo_path，不存完整 URL：完整 URL 由 platform + 配置中的 base URL 拼接。Gogs 的 base URL 放在应用配置里（见 architecture.md），GitHub 固定为 https://github.com。
2. 每个作品最多一个主链接：由部分唯一索引保证。没有主链接时，主页按平台顺序取第一个。
3. 主链接不强制存在：作品可以没有 is_primary = true 的仓库，主页展示逻辑需处理这种情况。
4. 仓库归属通过作品间接确定：portfolio_repos 不单独带 user_id，归属由所属作品决定。任何仓库操作必须先校验所属作品的 user_id = current_user_id。

八、搜索设计说明

范围：笔记标题、笔记正文、标签名。三处都支持模糊包含查询。

技术：

• 笔记标题 + 正文：通过 notes.search_text 生成列合并，对合并字段建 pg_trgm GIN 索引，查询 WHERE user_id = ? AND search_text ILIKE '%关键词%'。
• 标签名：对 tags.name 建 pg_trgm GIN 索引，查询 WHERE user_id = ? AND name ILIKE '%关键词%'。
• 不安装中文分词扩展。1–2 个字的查询会退化为全表扫描，接受这一代价（数据量小，且实际使用中很少只搜一两个字）。

返回：

• 笔记搜索结果返回笔记列表，同时返回每篇笔记的标签（联查 note_tags + tags）。
• 标签搜索结果返回标签列表，点进去看该标签下的笔记。
• 搜索本身是一个接口还是两个接口，由 api.md 定义，本文件不规定。

九、鉴权与审计相关表

以下表的完整字段与规则见 docs/backend/auth.md 与 docs/backend/audit.md：

• users — 用户账号（见 auth.md 第九节）
• sessions — 正式登录会话（见 auth.md 第三节）
• pending_logins — 待验证登录（账密已过、TOTP 待验，见 auth.md 第 3.1 节）
• trusted_devices — 可授信浏览器设备（见 auth.md 第四节）
• tokens — 长期 API Token（见 auth.md 第五节）
• audit_events — 审计事件（见 audit.md 第二节）

migration 顺序：users 必须最先创建；sessions、pending_logins、trusted_devices、tokens、audit_events 依赖 users；notes、tags、portfolio_items 也依赖 users。