BioF3 阶段 B：SQLite → PostgreSQL 迁移

目标：用户、权限、项目元数据、运营统计不再绑定在 Dell 计算节点本地磁盘上的 biof3.db；计算节点可更换/扩容，控制面数据在 PostgreSQL（建议阿里云 RDS）。

状态：进行中（落地起步）
相关：架构背景见对话记录；日常备份见 deploy.sh --backup-r、r-server/backup.sh。

1. 为什么要做

现状	迁库后
真相在 Dell 磁盘 `biof3.db`	真相在 PostgreSQL
Dell 挂了 → 登录、权限、Copilot、项目列表全挂	换 worker、连同一库即可恢复算力侧
备份 = 拷 sqlite 文件	pg_dump / RDS 自动备份

不变：静态站在 ECS；大文件在 OSS；R 池仍在计算节点。

2. 拓扑（当前 vs 目标）

当前:
  biof3.com (ECS) ──proxy──► Dell:3001 (r-server + biof3.db + R 池)

目标（阶段 B）:
  biof3.com (ECS) ──proxy──► Dell:3001 (r-server + R 池)
                                    │
                                    └──► PostgreSQL (RDS, VPC)

阶段 C（后续）：API 轻量上 ECS、Dell 纯 worker + 队列——建议在 B 完成后再做。

3. 表清单（代码库静态盘点）

运行 node scripts/sqlite-list-tables.mjs 可与生产库对照。下表来自仓库 CREATE TABLE 检索（2026-06-04）。

3.1 `r-server/db.js`（核心）

表	说明
`projects`	项目；含 `user_id`, `modality`, `is_public`
`datasets`	数据集元数据、OSS URL
`analyses`	分析任务、`results` JSON
`users`	账号、role、quota、permissions、enabled
`site_settings`	游客权限 `guest_permissions` 等
`subsets`	细胞子集
`cell_labels`	用户注释列

3.2 `r-server/routes/analytics.js`

表	说明
`user_heartbeats`	在线心跳
`page_views`	含 `ip`（ALTER 迁移）
`daily_stats`	DAU 等

3.3 `r-server/helpers/`

表	文件
`user_facts`	`user-facts.js`
`chat_summaries`	`chat-summary.js`
`memory_vectors`	`memory-vectors.js`
`ai_usage`	`ai-usage.js`
`ai_audit_log`	`ai-audit.js`
`ai_global_pause`	`ai-governance.js`
`user_ai_settings`	`ai-governance.js`
`user_progress`	`user-progress.js`
`review_queue`	`review-queue.js`
`copilot_plans` / `copilot_plan_steps`	`server.js` setup + `helpers/plans.js`
`copilot_conversations` / `copilot_messages`	`helpers/conversations.js`
`agent_audit` / `copilot_settings`	`helpers/act-tool.js`

3.4 `r-server/routes/`

表	文件
`feedbacks`	`feedback.js`

3.5 可能仅在运行时 / 其他文件

说明
`task-store.js` / `task-queue.js` — 确认是否落库
生产库执行 `.tables` 为准，补到本文档 §3.6

3.6 生产核对（待填）

# Dell 上
sqlite3 /opt/biof3-r-server/biof3.db ".tables"
sqlite3 /opt/biof3-r-server/biof3.db "SELECT COUNT(*) FROM users;"

表名	行数（生产）	已纳入 PG schema
		☐

4. 代码触点

优先级	路径	动作
P0	`r-server/db.js`	抽象数据层或 `pg` 替换 `better-sqlite3`
P0	`/etc/biof3.env`	`DATABASE_URL=postgres://...`
P1	`routes/analytics.js`	`ensureSchema` → PG DDL；`AUTOINCREMENT` → `SERIAL`
P1	`helpers/*` 各 `ensureSchema`	同上
P1	`routes/feedback.js`	同上
P2	`agent-tools/query-tools.js`	仅通过 db 模块，随 P0
P2	`deploy.sh` / `backup.sh`	备份改为 `pg_dump`
—	`py-server`	若独立库则单独规划（本阶段以 r-server 为主）

原则：业务代码不直接 require('better-sqlite3')，只走 db.js（及明确的 schema 模块）。

4.1 P2 骨架（Copilot / analytics）

组件	说明
`r-server/db-p2.js`	登记 P2 模块、校验 PG 表 `page_views` / `memory_vectors`、可选 aux SQLite
`db.getSqliteForLegacyModules()`	P2 表读写用 aux 或本地 `biof3.db`（过渡）
`db.runPgStartupChecks()`	`DATABASE_URL` 时启动校验 migrations 002–004
已接 `getSqliteForLegacyModules`	`memory-vectors`, `act-tool`, `user-facts`, `chat-summary`, `ai-usage`, `memory-privacy`, `conversations`, `plans`, `routes/analytics`, `routes/feedback`, `routes/ai-progress`, `server.js`（plan 建表/归档、conv PATCH）
待接 / 仍用 `dbMod.db`	`ai-conversations.js` 等 route 内 `require('../db')` 的 `db.prepare`（与 conversations helper 重复时可逐步改）；`server.js` 中 analyses 查询走 P1 API

环境变量（生产过渡）

变量	含义
`DATABASE_URL`	P0+P1 主数据在 PG
`BIOF3_PG_AUX_SQLITE_PATH`	Copilot/analytics 侧车 sqlite（从 `biof3.db` 拷贝或 `db:sqlite-to-pg` 后新建空库再导 P2 表）
`BIOF3_PG_STRICT=1`	未设 aux 路径则拒绝启动
`BIOF3_PG_SKIP_AUX_CHECK=1`	跳过 PG 表存在性检查（仅调试）

目标态：P2 查询改 db-p2-pg.js（TODO），不再依赖 aux sqlite。

5. SQLite → PostgreSQL 差异

SQLite	PostgreSQL
`INTEGER PRIMARY KEY AUTOINCREMENT`	`BIGSERIAL` / `GENERATED ALWAYS AS IDENTITY`
`datetime('now')`	`NOW()` / `CURRENT_TIMESTAMP`
`PRAGMA table_info`	`information_schema.columns`
`TEXT` 存 JSON	可选 `JSONB`
`INTEGER` 0/1 布尔	`BOOLEAN` 或保持 smallint
同步 `better-sqlite3`	`pg`（建议 Promise 包装或 `postgres.js`）

6. 实施步骤（勾选跟踪）

6.1 准备

开通 RDS PostgreSQL（与 Dell 同 VPC / 安全组放行 5432）
创建库与用户，最小权限
在 Dell /etc/biof3.env 预留 DATABASE_URL（先不切换）
文档化 RPO/RTO（备份保留天数、恢复联系人）

6.2 Schema

PG 建表 → r-server/migrations/pg/001–004.sql + scripts/apply-pg-migrations.sh
cd r-server && npm install（含 pg）
DATABASE_URL=... npm run db:pg-migrate
P0 代码：DATABASE_URL 时 users + site_settings 走 PG；projects/analyses 仍 SQLite
勿在生产长期半 PG；全量导入 + P1 后再开 DATABASE_URL
cd r-server && DATABASE_URL=... npm run test:db-p0

6.3 数据迁移（维护窗口）

公告或维护模式（可选：只读）
停 pm2 → 最后一次 sqlite 备份
空库：npm run db:pg-migrate
试跑：npm run db:sqlite-to-pg:dry → 正式：npm run db:sqlite-to-pg（scripts/migrate-sqlite-to-pg.mjs）
校验：users 数、projects 数、site_settings 中 guest_permissions

6.4 应用切换

双驱动（推荐）：DATABASE_URL 有则 PG，无则 SQLite（本地开发）
./deploy.sh --push 相关文件 + pm2 restart
./deploy.sh --health + 手工：登录、admin 游客权限、Copilot、单细胞 demo

6.5 收尾

保留 Dell 上旧 biof3.db 只读 30 天
更新 cron：pg_dump → OSS
更新本文档 §3.6 生产表清单

7. 回滚

DATABASE_URL 注释，恢复 BIOF3_DB_PATH 指向备份 sqlite
pm2 restart biof3-r-server
仅在 PG 未产生新写入时安全；否则需反向导出

8. 工作量粗估

角色	人天
后端 db 层 + helpers/routes schema	8–15
运维 RDS、网络、备份	2–4
测试	3–5

9. 本周可立刻做（零架构）

node scripts/sqlite-list-tables.mjs（本地或 BIOF3_DB_PATH 指向备份）
Dell：./deploy.sh --backup-r，确认 OSS 有最新 db
填 §3.6 生产 .tables 输出

10. 变更记录

日期	说明
2026-06-04	初版；PG `001–004` + `apply-pg-migrations.sh`；`npm run db:inventory` / `db:pg-migrate`
2026-06-04	初版入库；表清单来自代码检索

静态文件

离线资料下载

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

本篇 HTML 本篇 PDF说明

1. 为什么要做​

2. 拓扑（当前 vs 目标）​

3. 表清单（代码库静态盘点）​

3.1 r-server/db.js（核心）​

3.2 r-server/routes/analytics.js​

3.3 r-server/helpers/​

3.4 r-server/routes/​

3.5 可能仅在运行时 / 其他文件​

3.6 生产核对（待填）​

4. 代码触点​

4.1 P2 骨架（Copilot / analytics）​

5. SQLite → PostgreSQL 差异​

6. 实施步骤（勾选跟踪）​

6.1 准备​

6.2 Schema​

6.3 数据迁移（维护窗口）​

6.4 应用切换​

6.5 收尾​

7. 回滚​

8. 工作量粗估​

9. 本周可立刻做（零架构）​

10. 变更记录​

让 AI 陪我学这一篇