BioF3 数据面演进：SQLite → PostgreSQL（阶段 B）

目标：用户、权限、项目元数据不再绑在单台计算节点磁盘上。Dell 挂了可换机恢复 API/算力，账号与配置仍在中心库。

本文是执行清单，不是立即要做的代码变更。当前生产仍以 r-server/biof3.db（SQLite）为准。

1. 现状（2026-06）

项	说明
库文件	`BIOF3_DB_PATH` 或 `/opt/biof3-r-server/biof3.db`（Dell）
驱动	`better-sqlite3`，同步 API
入口	`r-server/db.js`（核心业务表 + 迁移）
扩展表	`routes/analytics.js`（`page_views`、`daily_stats`、`user_heartbeats` 等）
AI 相关	`helpers/user-facts.js`、`helpers/memory-vectors.js`、`routes/ai-*.js` 等另有表
静态站	阿里云 ECS，不持库
部署	`./deploy.sh --site`（前端） / `--push r-server/...`（Dell API）

计算节点挂了：静态教程可读；登录、Copilot、单细胞云、工具任务 不可用（API + 库 + R 池同机）。

2. 目标架构（阶段 B 最小可行）

浏览器 → biof3.com (ECS nginx)
              → /api/r/* → Dell:3001 (r-server，可无状态化倾向)
              → PostgreSQL（RDS 或 VPC 内自建，**不在** Dell 本地盘当唯一副本）
              → OSS（数据集/结果，已有）

仍可在 Dell 跑 r-server，但 biof3.db 改为 连远程 PostgreSQL。换 Dell = 重装进程 + 同一 DATABASE_URL，不必从坏盘抠 SQLite。

阶段 C（后续）：API 轻量实例与 R worker 分离、任务队列 — 见前序讨论，不在本文展开。

3. 表清单（迁移范围需 100% 覆盖）

3.1 `db.js` 内建 + 迁移

表	用途
`projects`	单细胞/空间等项目
`datasets`	数据集元数据
`analyses`	分析记录
`users`	账号、角色、`permissions`、`quota_mb`、`enabled`
`site_settings`	游客权限等站点配置
`subsets`	细胞子集
`cell_labels`	注释列

3.2 其他模块（须在 inventory 阶段扫全）

来源	典型表
`routes/analytics.js`	`page_views`, `daily_stats`, `user_heartbeats`
AI 记忆/会话	`user_facts`, `memory_vectors`, conversations/messages（以仓库 `CREATE TABLE` 为准）
`routes/feedback.js`	`feedbacks`
`task-store` / `task-queue`	任务状态表（若有）

动作：迁移前在 Dell 执行：

sqlite3 /opt/biof3-r-server/biof3.db ".tables"
sqlite3 /opt/biof3-r-server/biof3.db ".schema" > /tmp/biof3-schema-dump.sql

把输出存档，作为 PostgreSQL DDL 与数据导出的依据。

4. 代码触点（改库时要动的地方）

优先级	路径	说明
P0	`r-server/db.js`	全部 CRUD；SQLite 专有：`pragma`、部分 `datetime('now')`
P0	`r-server/helpers/guest-permissions.js`	`site_settings`
P0	`r-server/helpers/permissions.js`	读 `users.permissions`
P0	`r-server/auth.js`	用户校验
P1	`r-server/routes/analytics.js`	运营统计
P1	`r-server/server.js`	admin users、直连 `db` 片段
P1	`r-server/agent-tools/query-tools.js`	SQL 查询
P2	`helpers/memory`、`routes/ai-*`	向量/会话表

建议实现方式（二选一）：

A. 渐进：db.js 抽象 query(sql, params)，SQLite/PG 双后端，环境变量 BIOF3_DB_DRIVER=sqlite|postgres。
B. 一刀切：迁移窗口内直接换 pg + 改 SQL 方言（? → $1，INTEGER 布尔等）。

团队规模小选 B 更快；要零停机倾向选 A。

5. SQL 方言差异（必查）

SQLite	PostgreSQL
`TEXT` 主键	`TEXT` / `UUID`
`INTEGER` 当布尔	`BOOLEAN` 或 `SMALLINT`
`datetime('now')`	`NOW()` / `TIMESTAMPTZ`
`INSERT ... ON CONFLICT`	同语法，注意约束名
`AUTOINCREMENT`	`SERIAL` / `GENERATED`
`JSON` 在 TEXT 里	可用 `JSONB`（可选优化）
WAL 模式	不需要，用 RDS 配置

6. 环境与部署

变量	说明
`DATABASE_URL`	`postgresql://user:pass@host:5432/biof3`
`BIOF3_DB_PATH`	迁移后仅用于导入工具或只读兼容，生产 API 应停用 SQLite
`/etc/biof3.env`	与 `JWT_SECRET`、`INVITE_CODE` 同级管理；不要提交 git

网络：Dell → RDS 需安全组/白名单放行；延迟通常可接受（元数据查询为主）。

nginx：一般 不用改（仍反代到 Dell:3001）；若将来 API 迁 ECS，再改 proxy_pass。

7. 迁移步骤（建议维护窗口 1–3 小时）

准备

公告维护时段（或只读模式，若实现了只读开关）。
pm2 stop biof3-r-server 或停写流量（nginx 503 维护页可选）。
冷备份：cp biof3.db biof3.db.pre-pg-$(date +%Y%m%d) + 现有 OSS 备份脚本再跑一遍。

导出 / 导入

用 pgloader 或自写脚本：sqlite3 .dump → 清洗 → psql。
校验行数：users、projects、site_settings（含 guest_permissions key）。
在 PG 上跑一遍应用 CREATE INDEX（与现网索引对齐）。

切换

Dell /etc/biof3.env 增加 DATABASE_URL，部署新 db.js（或 feature 分支）。
pm2 restart biof3-r-server → ./deploy.sh --health。
冒烟：登录、admin 游客权限、创建项目、提交一条小任务、Copilot 一条消息、/api/public/guest-permissions。

回滚

保留 biof3.db.pre-pg-*，.env 去掉 DATABASE_URL、恢复旧代码，pm2 restart。
PostgreSQL 实例可保留作二次尝试，不删。

8. 验收标准

所有原 SQLite 表在 PG 中有对应表且行数一致（±迁移期间增量说明）
新用户注册、登录、/auth/me 带 permissions
Admin 修改游客 Copilot 开关后，匿名 GET /api/public/guest-permissions 立即生效
单细胞：建项目、传数据集、跑一条分析（任务状态回写）
故意 停掉 PostgreSQL 时 API 明确 503；停 Dell worker 但 PG 在 时至少登录与列表可用（阶段 B 仅保证 PG 独立，worker 分离属阶段 C）

9. 与「计算节点挂了」的关系

场景	阶段 B 后
Dell 整机宕机	静态站可读；若 API 仍只在 Dell，登录/Copilot 仍不可用 — 需阶段 C 把 API 迁出或 Dell 双机
Dell 磁盘坏、机子可换	新 Dell + 同 DATABASE_URL + OSS 可恢复业务，无需从坏盘救 SQLite
RDS 多 AZ	库侧高可用；R 池仍需自己的恢复流程

结论：阶段 B 解决的是 数据与权限不跟单机磁盘绑定；API/算力高可用 要靠阶段 C/D（API 与 worker 分离、多 worker）。

10. 建议排期（参考）

周	内容
1	表清单冻结、`pgloader` 试跑、测试库验证
2	`db.js` PG 版 + 核心路径改 SQL
3	analytics / AI 表 + 集成测试
4	维护窗口上线 + 监控 `DATABASE_URL` 连接池

11. 相关仓库命令

# 健康检查（Dell）
./deploy.sh --health

# 仅前端（不改库）
./deploy.sh --site

# 后端单文件（仍连 SQLite，直到本迁移完成）
./deploy.sh --push r-server/db.js

文档版本：2026-06-04 · 与 deploy.sh / r-server/db.js 现状对齐

静态文件

离线资料下载

手册 HTML / PDF 已在后台预生成，点击后直接下载网站静态资源。

本篇 HTML 本篇 PDF说明

1. 现状（2026-06）​

2. 目标架构（阶段 B 最小可行）​

3. 表清单（迁移范围需 100% 覆盖）​

3.1 db.js 内建 + 迁移​

3.2 其他模块（须在 inventory 阶段扫全）​

4. 代码触点（改库时要动的地方）​

5. SQL 方言差异（必查）​

6. 环境与部署​

7. 迁移步骤（建议维护窗口 1–3 小时）​

准备​

导出 / 导入​

切换​

回滚​

8. 验收标准​

9. 与「计算节点挂了」的关系​

10. 建议排期（参考）​

11. 相关仓库命令​

让 AI 陪我学这一篇