个人博客系统 数据库设计

文档版本：V1.0 作者：王清国 日期：2026年7月 数据库：PostgreSQL 16 关联文档：《个人博客系统 PRD》V1.0、《个人博客系统 技术方案》V1.0

本文在 PRD 第六章「数据模型草案」与技术方案第三章基础上，给出可直接落地的 PostgreSQL 16 物理设计（DDL、约束、索引、触发器、初始化数据）。ORM 为 SQLModel，实际表结构由 SQLModel 模型 + Alembic 迁移生成，本文 DDL 为逻辑设计与对照基准，二者须保持一致。

---

一、设计约定

1.1 命名规范

对象	规范	示例
表名	小写蛇形、复数	articles、article_tags
字段名	小写蛇形	created_at、wechat_unionid
主键	id
外键字段	<单数实体>_id	article_id、category_id
主键约束	pk_<表>	隐式
唯一约束/索引	uq_<表>_<列>	uq_users_username
普通索引	idx_<表>_<列...>	idx_articles_status_published
外键约束	fk_<表>_<引用表>	fk_comments_articles
枚举类型	<语义>	user_role

1.2 类型选型

• 主键：bigint GENERATED ALWAYS AS IDENTITY（PG10+ 标准写法，优于 serial）。
• 时间：一律 timestamptz，存 UTC，应用层按 ISO8601 输出（对齐技术方案 §6.1）。默认 now()。
• 枚举：使用 PostgreSQL 原生 ENUM 类型（与技术方案一致）。注意 PG 中 enum 可加值（ALTER TYPE ... ADD VALUE）但不可删值；若预期取值频繁变动，可改用 varchar + CHECK。本项目取值稳定，采用原生 enum。
• 变长文本：短文本 varchar(n) 并加长度约束，长文本/Markdown 用 text。
• 结构化可扩展字段：jsonb（运动数据、图片列表、身份标签等），便于扩展与开源裁剪。
• 布尔：boolean，避免用 0/1 整数。

1.3 媒体字段约定

所有媒体字段（图片/视频/音频）在库中只存对象存储的 key（如 photography/2026/07/xxx.jpg），对外 URL 由后端按当前 storage provider 拼接（技术方案 §5）。字段以 _key 结尾以示语义。

1.4 删除策略

MVP 采用物理删除 + 外键级联，不做软删除。未来如需回收站，可统一加 deleted_at timestamptz（见第十二章）。

---

二、实体关系总览

                ┌──────────┐
                │ profile  │  单行记录（id=1），个人主页信息
                └──────────┘

┌──────────┐        ┌─────────────┐ N     1 ┌────────────┐
│  users   │        │  articles   │─────────│ categories │
└────┬─────┘        └──────┬──────┘         └────────────┘
     │ 1                   │ 1  N
     │                     │        ┌──────────────┐
     │ N                   │ N   N  │     tags      │
┌────▼─────┐          ┌────▼────────┴───┐  1   N ┌──▼──────────┐
│ comments │──────────│  article_tags   │────────│（多对多桥表） │
└──────────┘   N    1 └─────────────────┘        └─────────────┘
   （评论属于某篇文章、某个用户）

┌────────────────────┐   ┌──────────┐   ┌──────────────────┐
│ photography_works  │   │ projects │   │ background_music │
└────────────────────┘   └──────────┘   └──────────────────┘
   （独立内容表，无强关联，均由管理员维护）

关系说明：

• articles N:1 categories（一篇文章属一个分类，分类可空）。
• articles N:N tags，经桥表 article_tags。
• comments N:1 articles、N:1 users。
• photography_works、projects、background_music、profile 为独立表，无外键关联。

---

三、枚举类型

CREATE TYPE user_role      AS ENUM ('admin', 'visitor');
CREATE TYPE article_status AS ENUM ('draft', 'published');
CREATE TYPE media_type     AS ENUM ('photo', 'video');
CREATE TYPE comment_status AS ENUM ('normal', 'hidden');

---

四、通用机制：updated_at 自动维护

可变实体统一用触发器维护 updated_at，避免依赖应用层。

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

各表通过 CREATE TRIGGER trg_<表>_updated_at BEFORE UPDATE ... EXECUTE FUNCTION set_updated_at() 挂载（见各表 DDL）。

---

五、表结构

5.1 users（用户表）

角色仅两种：管理员（账号密码）与认证访客（微信）。

对 PRD 的修正：同一微信用户在 Web「网站应用」与小程序端拿到的 openid 不同，仅 unionid 相同。故将 PRD 草案的单个 wechat_openid 拆为 wechat_web_openid 与 wechat_mp_openid 两列，wechat_unionid 作为跨端唯一身份键（技术方案 §4.5）。

CREATE TABLE users (
    id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    role              user_role   NOT NULL DEFAULT 'visitor',
    -- 微信身份（认证访客）
    wechat_unionid    varchar(64),            -- 跨端唯一标识，打通 Web 与小程序
    wechat_web_openid varchar(64),            -- 网站应用 openid
    wechat_mp_openid  varchar(64),            -- 小程序 openid
    nickname          varchar(64),
    avatar_url        varchar(512),
    -- 管理员凭据（仅 role=admin）
    username          varchar(32),
    password_hash     varchar(255),           -- bcrypt
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT uq_users_unionid    UNIQUE (wechat_unionid),
    CONSTRAINT uq_users_web_openid UNIQUE (wechat_web_openid),
    CONSTRAINT uq_users_mp_openid  UNIQUE (wechat_mp_openid),
    CONSTRAINT uq_users_username   UNIQUE (username),
    -- 管理员必须有用户名+密码；访客必须有 unionid
    CONSTRAINT ck_users_admin_creds CHECK (
        (role = 'admin'   AND username IS NOT NULL AND password_hash IS NOT NULL)
     OR (role = 'visitor' AND wechat_unionid IS NOT NULL)
    )
);

CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMENT ON TABLE  users IS '用户表：管理员与微信认证访客';
COMMENT ON COLUMN users.wechat_unionid IS '微信开放平台 unionid，跨端唯一身份键';

PostgreSQL 唯一约束允许多个 NULL，因此大量访客的 username=NULL 或管理员的 wechat_*=NULL 不会互相冲突。

5.2 profile（个人主页信息表，单行）

CREATE TABLE profile (
    id            integer PRIMARY KEY DEFAULT 1,
    display_name  varchar(64),
    bio           text,
    mbti_type     varchar(8),                       -- 如 INTJ
    identity_tags jsonb NOT NULL DEFAULT '[]'::jsonb, -- 身份标签，如 ["摄影师","独立开发者"]
    sports_data   jsonb NOT NULL DEFAULT '{}'::jsonb, -- 运动数据，结构灵活便于扩展
    social_links  jsonb NOT NULL DEFAULT '{}'::jsonb, -- 可选：外链
    updated_at    timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_profile_singleton CHECK (id = 1)     -- 保证全表至多一行
);

CREATE TRIGGER trg_profile_updated_at BEFORE UPDATE ON profile
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMENT ON TABLE profile IS '个人主页信息，全表仅一行（id=1）';

identity_tags、mbti_type、sports_data 为个性化字段，开源者可按需裁剪（技术方案 §11）。

5.3 categories（文章分类表）

CREATE TABLE categories (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        varchar(64)  NOT NULL,
    slug        varchar(80)  NOT NULL,          -- SEO 友好 URL 片段
    description varchar(255),
    sort_order  integer      NOT NULL DEFAULT 0,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT uq_categories_name UNIQUE (name),
    CONSTRAINT uq_categories_slug UNIQUE (slug)
);

CREATE TRIGGER trg_categories_updated_at BEFORE UPDATE ON categories
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

5.4 articles（文章表）

CREATE TABLE articles (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title        varchar(200)   NOT NULL,
    slug         varchar(220)   NOT NULL,          -- SEO：/articles/{slug}
    summary      varchar(500),                     -- 摘要/列表页展示，可空
    content      text           NOT NULL,          -- Markdown 原文
    cover_key    varchar(512),                     -- 封面图对象 key，可空（对 PRD 的补充）
    category_id  bigint,
    status       article_status NOT NULL DEFAULT 'draft',
    view_count   integer        NOT NULL DEFAULT 0, -- 阅读量快照，见第十章
    published_at timestamptz,                       -- 发布时间；草稿为 NULL
    created_at   timestamptz    NOT NULL DEFAULT now(),
    updated_at   timestamptz    NOT NULL DEFAULT now(),
    CONSTRAINT uq_articles_slug UNIQUE (slug),
    CONSTRAINT fk_articles_categories
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TRIGGER trg_articles_updated_at BEFORE UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 列表页：按状态过滤 + 发布时间倒序
CREATE INDEX idx_articles_status_published
    ON articles (status, published_at DESC);
-- 分类筛选
CREATE INDEX idx_articles_category ON articles (category_id);

COMMENT ON COLUMN articles.content IS 'Markdown 原文；渲染由前端负责并做 XSS 净化';

5.5 tags / article_tags（标签与关联表）

CREATE TABLE tags (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       varchar(48) NOT NULL,
    slug       varchar(60) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_tags_name UNIQUE (name),
    CONSTRAINT uq_tags_slug UNIQUE (slug)
);

CREATE TABLE article_tags (
    article_id bigint NOT NULL,
    tag_id     bigint NOT NULL,
    CONSTRAINT pk_article_tags PRIMARY KEY (article_id, tag_id),
    CONSTRAINT fk_article_tags_article
        FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    CONSTRAINT fk_article_tags_tag
        FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- 反向查询：某标签下的文章
CREATE INDEX idx_article_tags_tag ON article_tags (tag_id);

5.6 comments（评论表）

CREATE TABLE comments (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    article_id bigint         NOT NULL,
    user_id    bigint         NOT NULL,
    content    text           NOT NULL,
    status     comment_status NOT NULL DEFAULT 'normal',  -- normal / hidden(违规)
    created_at timestamptz    NOT NULL DEFAULT now(),
    CONSTRAINT fk_comments_articles
        FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_users
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 文章评论分页：按文章 + 时间
CREATE INDEX idx_comments_article_created
    ON comments (article_id, created_at DESC);
-- 后台审核：按状态筛选
CREATE INDEX idx_comments_status ON comments (status);

COMMENT ON COLUMN comments.status IS 'normal=正常展示；hidden=内容安全命中或管理员隐藏';

5.7 photography_works（摄影作品表）

CREATE TABLE photography_works (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       varchar(200),                    -- 可空
    media_type  media_type   NOT NULL,           -- photo / video
    media_key   varchar(512) NOT NULL,           -- 照片或视频对象 key
    cover_key   varchar(512),                    -- 视频封面；photo 可空
    description text,
    category    varchar(64),                     -- 作品分类：人像/风光/延时…（字符串，非独立表）
    sort_order  integer      NOT NULL DEFAULT 0,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    -- 视频必须有封面
    CONSTRAINT ck_photography_video_cover CHECK (
        media_type = 'photo' OR cover_key IS NOT NULL
    )
);

CREATE TRIGGER trg_photography_updated_at BEFORE UPDATE ON photography_works
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 画廊按分类浏览 + 时间倒序
CREATE INDEX idx_photography_category_created
    ON photography_works (category, created_at DESC);

5.8 projects（项目展示表）

CREATE TABLE projects (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       varchar(200) NOT NULL,
    slug        varchar(220),
    description text         NOT NULL,           -- 图文说明（Markdown）
    cover_key   varchar(512),
    gallery     jsonb        NOT NULL DEFAULT '[]'::jsonb,  -- 图片 key 列表
    sort_order  integer      NOT NULL DEFAULT 0,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT uq_projects_slug UNIQUE (slug)
);

CREATE TRIGGER trg_projects_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMENT ON COLUMN projects.gallery IS '图片对象 key 数组，如 ["projects/a/1.jpg", ...]';

5.9 background_music（背景音乐表）

CREATE TABLE background_music (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      varchar(200) NOT NULL,
    artist     varchar(120),
    file_key   varchar(512) NOT NULL,            -- 音频对象 key
    is_default boolean      NOT NULL DEFAULT false,
    sort_order integer      NOT NULL DEFAULT 0,
    created_at timestamptz  NOT NULL DEFAULT now(),
    updated_at timestamptz  NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_music_updated_at BEFORE UPDATE ON background_music
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 部分唯一索引：全表至多一首默认曲目
CREATE UNIQUE INDEX uq_music_single_default
    ON background_music (is_default) WHERE is_default = true;

部分唯一索引（partial index）确保 is_default=true 至多一行；设新默认曲目时，应用需在同一事务内先把旧默认置 false。

---

六、索引汇总

表	索引	用途
users	uq_users_unionid / web_openid / mp_openid / username	身份唯一与登录查找
categories	uq_categories_name / slug	唯一 + SEO 查找
articles	uq_articles_slug	详情页 slug 查找
articles	idx_articles_status_published	已发布列表、时间倒序
articles	idx_articles_category	分类筛选
tags	uq_tags_name / slug	唯一
article_tags	pk (article_id, tag_id) / idx_article_tags_tag	正反向关联查询
comments	idx_comments_article_created	文章评论分页
comments	idx_comments_status	后台审核筛选
photography_works	idx_photography_category_created	画廊分类浏览
projects	uq_projects_slug	详情查找
background_music	uq_music_single_default	唯一默认曲目

---

七、全文检索（文章内搜索，P2）

文章内搜索为 P2 功能。PostgreSQL 16 内置全文检索对中文分词无原生支持，to_tsvector('simple', ...) 只能按空格/标点切分，对中文效果差。两种方案：

方案 A（MVP 简易版，推荐先用）：pg_trgm 扩展 + ILIKE 模糊匹配，零额外部署成本。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_title_trgm
    ON articles USING gin (title gin_trgm_ops);
CREATE INDEX idx_articles_content_trgm
    ON articles USING gin (content gin_trgm_ops);
-- 查询：WHERE title ILIKE '%关键词%' OR content ILIKE '%关键词%'

方案 B（正式全文检索）：安装中文分词扩展 zhparser（基于 SCWS），配置中文检索配置后，用 tsvector + GIN 索引。需在数据库容器内额外编译安装扩展，部署成本较高，可在 V3 阶段引入。

-- 前置：容器内安装并 CREATE EXTENSION zhparser; 创建 config 'chinese'
ALTER TABLE articles
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('chinese', coalesce(title,'') || ' ' || coalesce(content,''))
    ) STORED;
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);
-- 查询：WHERE search_vector @@ plainto_tsquery('chinese', '关键词')

GENERATED ... STORED 生成列为 PG12+ 特性，PG16 支持。

---

八、初始化数据

8.1 profile 单行

INSERT INTO profile (id, display_name, bio, mbti_type)
VALUES (1, '王清国', '', 'INTJ')
ON CONFLICT (id) DO NOTHING;

8.2 管理员账号

管理员由后端初始化脚本创建：读取环境变量 ADMIN_USERNAME / ADMIN_PASSWORD，用 bcrypt 生成 password_hash 后落库（技术方案 §3.3），不在迁移文件中硬编码明文密码。

-- 伪示意；password_hash 由应用用 bcrypt 生成后填入
INSERT INTO users (role, username, password_hash, nickname)
VALUES ('admin', :username, :bcrypt_hash, '管理员')
ON CONFLICT (username) DO NOTHING;

---

九、初始化执行顺序

Alembic 迁移（或初始化 SQL）须按依赖顺序执行：

1. CREATE EXTENSION（如 pg_trgm）
2. 枚举类型（第三章）
3. set_updated_at() 函数（第四章）
4. 无外键依赖的表：users、profile、categories、tags、photography_works、projects、background_music
5. 有外键依赖的表：articles（依赖 categories）、article_tags（依赖 articles/tags）、comments（依赖 articles/users）
6. 索引与触发器
7. 种子数据（profile、管理员）

---

十、阅读量计数策略

articles.view_count 若每次访问都 UPDATE，高频写会造成行锁与膨胀。采用技术方案 §6.3 方案：

• 访问文章详情时，在 Redis 中 INCR article:view:{id}。
• 定时任务（或阈值触发）批量把增量回写 PostgreSQL：UPDATE articles SET view_count = view_count + :delta WHERE id = :id，并清零 Redis 增量。
• 前端展示的阅读量 = 数据库快照 + Redis 当前增量。

因此库中 view_count 是准实时快照，允许短时滞后。

---

十一、与 SQLModel / Alembic 的对应

• 每张表对应一个 SQLModel(table=True) 模型；枚举用 Python enum.Enum 映射原生 enum。
• 表结构变更一律通过 Alembic 迁移，禁止手工改库；本文 DDL 作为首版迁移的对照基准。
• 对外 API 的响应模型（schema）与表模型解耦，password_hash、内部 key 等敏感字段不得直接序列化返回（技术方案 §2.2）。
• 触发器、部分唯一索引、CHECK 约束等 SQLModel 不直接表达的对象，在 Alembic 迁移中以 op.execute(...) 补充。

---

十二、未来演进（非 MVP）

方向	设计预留
软删除/回收站	各内容表加 deleted_at timestamptz，查询默认过滤非空
评论点赞（P2）	新增 comment_likes(comment_id, user_id) 联合主键表
楼中楼回复	comments 加 parent_id bigint 自引用外键
文章全文检索（P2/V3）	引入 zhparser + tsvector（第七章方案 B）
运动数据对接第三方	profile.sports_data 已用 jsonb，结构可平滑扩展
多级分类	categories 加 parent_id 自引用