512 行
34 KiB
Markdown
512 行
34 KiB
Markdown
|
|
# A4 — 数据库层审计(模型 / 仓储 / 迁移 / 事务)
|
|||
|
|
|
|||
|
|
> 审计范围(实际逐行读过的文件):
|
|||
|
|
> - `database/db_helper.py`(全文 473 行)
|
|||
|
|
> - `database/models.py`(全文 498 行)
|
|||
|
|
> - `database/seed.py`(全文 165 行)
|
|||
|
|
> - `database/migrations/0001..0018_*.sql`(全部 18 个迁移脚本全文)
|
|||
|
|
> - `database/repositories/job_repository.py`(全文 628 行)
|
|||
|
|
> - `database/repositories/profile_repository.py`(全文 367 行)
|
|||
|
|
> - `database/repositories/image_repository.py`(全文 311 行)
|
|||
|
|
> - `database/repositories/workflow_round_repository.py`(1-1180 行)
|
|||
|
|
> - `database/repositories/workflow_repository.py`(82-231、280-324 行)
|
|||
|
|
> - `database/repositories/profile_card_repository.py`(80-290 行)
|
|||
|
|
> - `database/repositories/advanced_material_repository.py`(471-760、1218-1400、2078-2123 行 + 全文方法轮廓)
|
|||
|
|
> - `database/repositories/workflow_cross_review_repository.py`(方法轮廓 + 348-770 结构扫描)
|
|||
|
|
> - `database/repositories/wardrobe_repository.py`、`capability_repository.py`、`material_repository.py`(表名/占位符相关片段)
|
|||
|
|
> - 交叉验证:`backend/services/queue_service.py`、`backend/services/generation_service.py`、`backend/services/image_service.py`、`backend/services/workflow_service.py`、`backend/services/chat_workflow_service.py`、`backend/services/cross_review_service.py`、`backend/services/material_analytics_service.py`、`backend/services/orphan_media_service.py`、`config.yaml`
|
|||
|
|
|
|||
|
|
## 摘要
|
|||
|
|
|
|||
|
|
参数化做得相当干净:全部 SQL 都走 `{placeholder}` 绑定,唯一的动态表名(`wardrobe_repository`)有白名单,未发现可利用的 SQL 注入。真正的问题集中在三处:**(1) PostgreSQL 分支实际是坏的**——`profile_repository` 的三个 CASE 列里有字面量 `%`,在 psycopg 的 `%s` paramstyle 下会直接抛异常,而 `config.yaml` 把 PostgreSQL 作为正式可选后端对外提供;**(2) SQLite 没有开 WAL**,却同时存在 4 线程并发写(`ThreadPoolExecutor(max_workers=4)`)和大量 `BEGIN IMMEDIATE` 长事务,写锁会阻塞全部读;**(3) JSON 列(`metadata_json`)是"整列覆盖"语义**,而服务层普遍在两个独立连接之间做"读—改—写",`generation_service` 甚至专门加了一把进程内 `threading.Lock` 来打补丁,说明丢更新已经是已知痛点但只堵住了一条路径。
|
|||
|
|
|
|||
|
|
事务边界方面,仓储内部的 `*_atomic` 方法写得很认真(`BEGIN IMMEDIATE` + `FOR UPDATE` + `rollback`),但跨仓储的组合操作完全没有事务包裹,`delete_image_metadata` 这类"先删图、再单独清 node 指针"的序列会留下悬挂引用——而 `workflow_nodes.selected_image_id` 恰恰没有外键。
|
|||
|
|
|
|||
|
|
工程上最突出的是重复度:14 个仓储手写了 **131 处** `connect()/try/finally close()`、**12 份** `_json_or_none`、**13 份** JSON 解码助手、**4 份** `_page/_page_size`(且分页上限不一致:100 vs 200)、**3 份** `_rollback`。整个数据层没有任何 context manager 或基类。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## 发现
|
|||
|
|
|
|||
|
|
### [P1-严重] `profile_repository` 的 SQL 含字面量 `%`,PostgreSQL 后端下角色列表/详情 100% 报错
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/profile_repository.py:46`、`:73`、`:99`(同一条谓词被复制三份)
|
|||
|
|
- **问题**:三个 CASE 列常量里写死了 `LIKE '%"final_archive_history_id"%'`。当 `backend == "postgresql"` 时 `Database._placeholder` 返回 `%s`(`db_helper.py:288-290`),psycopg/psycopg2 会把整条 query 当作 `%`-format 模板去展开参数。SQL 文本里的 `%"` 是非法格式符,驱动会在 `ProfileRepository.get()` / `.list()` 传参时直接抛 `ValueError: unsupported format character '"'`(psycopg2)或 `IndexError`(psycopg3)。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/profile_repository.py:40-47
|
|||
|
|
WHEN EXISTS (
|
|||
|
|
SELECT 1
|
|||
|
|
FROM profile_workflows AS legacy_card_workflow
|
|||
|
|
WHERE legacy_card_workflow.profile_id = character_profiles.profile_id
|
|||
|
|
AND legacy_card_workflow.workflow_type = 'character_gradual'
|
|||
|
|
AND LOWER(COALESCE(legacy_card_workflow.status, '')) IN ('completed', 'archived')
|
|||
|
|
AND COALESCE(legacy_card_workflow.metadata_json, '') LIKE '%"final_archive_history_id"%'
|
|||
|
|
) THEN 'detailed'
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/profile_repository.py:186-194 —— 带参调用,触发 %-展开
|
|||
|
|
row = conn.execute(
|
|||
|
|
f"""
|
|||
|
|
SELECT {PROFILE_COLUMNS}, {PROFILE_GENERATION_KIND_COLUMN},
|
|||
|
|
{PROFILE_CATEGORY_COLUMN}, {DETAILED_WORKFLOW_STATE_COLUMN}
|
|||
|
|
FROM character_profiles
|
|||
|
|
WHERE profile_id = {placeholder}
|
|||
|
|
""",
|
|||
|
|
(profile_id,),
|
|||
|
|
).fetchone()
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:`config.yaml:153-167` 明确把 `backend: auto | sqlite | postgresql` 作为受支持配置对外暴露(`postgresql.enabled: true` 即自动切换)。一旦用户切到 PG,角色详情、角色列表这两个最核心的读接口立刻 500,且错误信息是驱动层的格式化异常,完全指向不了根因。这不是"性能退化",是功能性完全不可用。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 立刻把三处字面量改成参数绑定,彻底避开 `%` 转义问题:
|
|||
|
|
```python
|
|||
|
|
LEGACY_ARCHIVE_MARKER = '%"final_archive_history_id"%'
|
|||
|
|
# SQL 中写 AND COALESCE(..., '') LIKE {placeholder}
|
|||
|
|
# 调用处按 CASE 列出现次数补齐参数(或改用 json_extract / jsonb 谓词)
|
|||
|
|
```
|
|||
|
|
2. 更好的做法是干掉 `LIKE '%json%'` 这种反模式:`final_archive_history_id` 应该提升为 `profile_workflows` 的真实列(配 `0019` 迁移 + 回填),既解决转义又解决全表扫描。
|
|||
|
|
3. 加一条冒烟测试:用 `DatabaseSettings(backend="postgresql")` 构造 `_placeholder == "%s"`,断言所有仓储 SQL 常量中不含未转义的裸 `%`(可用正则 `%(?!s)` 静态扫描 `database/repositories/*.py`)。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P1-严重] SQLite 未启用 WAL,却同时存在多线程并发写与 `BEGIN IMMEDIATE` 长事务
|
|||
|
|
|
|||
|
|
- **位置**:`database/db_helper.py:159-171`
|
|||
|
|
- **问题**:`connect()` 只设置了 `foreign_keys` 和 `busy_timeout`,**没有 `PRAGMA journal_mode = WAL`**。默认 rollback journal 模式下,任何一个持有 RESERVED/EXCLUSIVE 锁的写事务会阻塞**全库所有读**。而代码里到处是长写事务:`workflow_round_repository.confirm_candidate_atomic` 在一个 `BEGIN IMMEDIATE` 里跑了 12 条语句(`:815-1120`),`get_consistent_round_snapshot` 甚至为了**纯读**也去拿 `BEGIN IMMEDIATE` 写锁(`:312-313`)。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/db_helper.py:159-171
|
|||
|
|
def connect(self):
|
|||
|
|
if self.backend == "sqlite":
|
|||
|
|
db_path = self._resolve_sqlite_path()
|
|||
|
|
db_path.parent.mkdir(parents=True, exist_ok=True)
|
|||
|
|
# Parallel image tasks briefly overlap while recording queue,
|
|||
|
|
# node, image, and audit rows. Give SQLite enough time to hand the
|
|||
|
|
# writer lock to the next task instead of failing immediately.
|
|||
|
|
conn = sqlite3.connect(db_path, timeout=30.0)
|
|||
|
|
conn.row_factory = sqlite3.Row
|
|||
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
|||
|
|
conn.execute("PRAGMA busy_timeout = 30000")
|
|||
|
|
return conn
|
|||
|
|
return self._connect_postgresql()
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/chat_workflow_service.py:216-220 —— 4 线程并发跑整条生成链路
|
|||
|
|
with ThreadPoolExecutor(max_workers=4, thread_name_prefix="imagestudio-chat-fast") as executor:
|
|||
|
|
futures = {
|
|||
|
|
executor.submit(self.generate_node, str(node["node_id"]), chat): node
|
|||
|
|
for node in nodes
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
(`backend/services/cross_review_service.py:62` 同样是 `ThreadPoolExecutor(max_workers=min(len(routes), 4) or 1)`。)
|
|||
|
|
|
|||
|
|
- **影响**:注释里"give SQLite enough time"这句话本身就是症状——他们观察到了锁竞争,选择了把超时调到 30s 而不是消除竞争。实际后果:快速角色四图并发生成时,任意一个线程进入 `confirm_candidate_atomic`,其余 3 个线程连"读节点状态"都会被阻塞;一旦某个 provider 调用在事务内耗时较久,其他线程 30s 后抛 `sqlite3.OperationalError: database is locked`,而这个异常在 `generation_service.py:1866`、`:1890` 的 `except Exception: pass` 里被静默吞掉,表现为"节点状态莫名其妙没更新"。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. `connect()` 里补:
|
|||
|
|
```python
|
|||
|
|
conn.execute("PRAGMA journal_mode = WAL")
|
|||
|
|
conn.execute("PRAGMA synchronous = NORMAL")
|
|||
|
|
```
|
|||
|
|
WAL 让读写互不阻塞,读并发不再被写事务卡住。注意 `journal_mode` 是持久化属性,只需成功执行一次,但每次连接都执行是幂等的。
|
|||
|
|
2. `get_consistent_round_snapshot`(`workflow_round_repository.py:312`)改用 `BEGIN DEFERRED`——它只读不写,在 WAL 下天然拿到一致性快照,不需要写锁。
|
|||
|
|
3. 把 provider 调用移出事务边界(目前 `confirm_candidate_atomic` 内部没有网络调用,但 `generate_node` 链路上的 `update_node` → `HTTP` → `update_node` 序列值得复查)。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P1-严重] `metadata_json` 整列覆盖 + 跨连接读改写 = 丢更新,且只有一条路径加了进程内锁
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/workflow_repository.py:298-322`(`update_node`)、`:154-178`(`update_workflow`)
|
|||
|
|
- **问题**:仓储的 `update` 语义是**整列覆盖** `metadata_json = ?`,没有任何 merge / 版本号 / CAS。服务层则普遍是"读 DTO → `{**node.metadata, ...}` → 写回",两步跨两个独立连接、两个独立事务。任意两个并发写入者都会互相覆盖对方的键。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/workflow_repository.py:298-318 —— 整列覆盖
|
|||
|
|
for key in allowed:
|
|||
|
|
if key not in payload:
|
|||
|
|
continue
|
|||
|
|
column = "metadata_json" if key == "metadata" else key
|
|||
|
|
value = _json_or_none(payload[key]) if key == "metadata" else payload[key]
|
|||
|
|
assignments.append(f"{column} = {placeholder}")
|
|||
|
|
params.append(value)
|
|||
|
|
...
|
|||
|
|
conn.execute(
|
|||
|
|
f"""
|
|||
|
|
UPDATE workflow_nodes
|
|||
|
|
SET {", ".join(assignments)}
|
|||
|
|
WHERE node_id = {placeholder}
|
|||
|
|
""",
|
|||
|
|
tuple(params),
|
|||
|
|
)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/generation_service.py:1870-1886 —— 典型的跨连接读-改-写
|
|||
|
|
node = self.workflow_service.get_node(node_id) # 连接 A:读
|
|||
|
|
node_metadata = node.metadata if isinstance(node.metadata, dict) else {}
|
|||
|
|
if (_optional_text(node_metadata.get("last_task_id")) == task.task_id
|
|||
|
|
and node.status in {"queued", "running"}):
|
|||
|
|
self.workflow_service.update_node( # 连接 B:整列覆盖
|
|||
|
|
node_id,
|
|||
|
|
{"status": "failed",
|
|||
|
|
"metadata": {**node_metadata, "generation_failure": failure,
|
|||
|
|
"last_task_id": task.task_id}},
|
|||
|
|
ctx,
|
|||
|
|
)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
作者显然知道这个问题——但只在一条路径上用**进程内**锁打了补丁:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/generation_service.py:1145-1149
|
|||
|
|
lock_entry = _retain_quick_character_workflow_lock(workflow.workflow_id)
|
|||
|
|
lock_entry.lock.acquire()
|
|||
|
|
try:
|
|||
|
|
current_workflow = self.workflow_service.get_workflow(workflow.workflow_id).workflow
|
|||
|
|
metadata = dict(current_workflow.metadata or {})
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:`profile_workflows.metadata_json` 里装着 `branch_state / stage_inputs / character_execution / completed_node_ids` 这些**工作流状态机的真身**(见 `workflow_round_repository.py:1097-1119`)。`confirm_candidate_atomic` 在自己的 DB 事务里正确地写了 `stage_inputs`,但只要 `generation_service._prepare_quick_character` 在此期间用它更早读到的 `metadata` 快照调用 `update_workflow`,`stage_inputs` 就被整个抹掉 —— 表现为"确认了候选图但下一节点拿不到上游图"。`threading.Lock` 只覆盖同一进程内的同一条代码路径,`confirm_candidate_atomic` 完全不参与这把锁。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 短期:给 `update_workflow` / `update_node` 增加乐观并发控制。表已有 `updated_at`,可直接做:
|
|||
|
|
```python
|
|||
|
|
# WHERE workflow_id = ? AND updated_at = ? (expected_updated_at 由 DTO 带回)
|
|||
|
|
changed = int(cursor.rowcount or 0) > 0
|
|||
|
|
```
|
|||
|
|
写失败则由调用方重读重试,而不是静默覆盖。
|
|||
|
|
2. 中期:给仓储加 `patch_metadata(id, patch: dict)` 语义——在**同一个** `BEGIN IMMEDIATE` 事务里 `SELECT metadata_json` → merge → `UPDATE`,把读改写收进仓储,服务层不再手工 `{**node.metadata}`。
|
|||
|
|
3. 长期:把 `stage_inputs` / `completed_node_ids` 这类真状态从 JSON blob 里拆成真表(`workflow_branch_state`、`workflow_stage_input`),行级写入天然无冲突。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P2-中等] 跨仓储组合写没有事务边界:删图与清 node 指针分属两次提交,且 `selected_image_id` 无外键
|
|||
|
|
|
|||
|
|
- **位置**:`backend/services/image_service.py:216-234`、`database/repositories/image_repository.py:256-270`、`database/migrations/0003_profile_workflows_and_nodes.sql:36`
|
|||
|
|
- **问题**:`delete_image_metadata` 先在一个连接里 DELETE 并 `commit()`,再在另一个连接里把 `workflow_nodes.selected_image_id` 置空。两步之间进程崩溃 / 第二步抛异常,就留下指向不存在图片的指针。而 `selected_image_id` 在建表时**没有声明外键**,数据库层不会兜底。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```sql
|
|||
|
|
-- database/migrations/0003_profile_workflows_and_nodes.sql:36 —— 无 FOREIGN KEY
|
|||
|
|
selected_image_id TEXT,
|
|||
|
|
```
|
|||
|
|
该表末尾只有一条外键(`:41`):`FOREIGN KEY(workflow_id) REFERENCES profile_workflows(workflow_id) ON DELETE CASCADE`。
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/image_repository.py:260-270 —— 独立事务提交
|
|||
|
|
conn = self.database.connect()
|
|||
|
|
try:
|
|||
|
|
conn.execute(
|
|||
|
|
f"DELETE FROM generation_images WHERE image_id = {placeholder}",
|
|||
|
|
(image_id,),
|
|||
|
|
)
|
|||
|
|
conn.commit()
|
|||
|
|
finally:
|
|||
|
|
conn.close()
|
|||
|
|
return before
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/image_service.py:219-225 —— 第二次独立提交
|
|||
|
|
before = self.image_repository.delete_metadata(image_id)
|
|||
|
|
if before is None:
|
|||
|
|
raise ApiError(ErrorCode.RESOURCE_NOT_FOUND, "图片不存在", {"image_id": image_id})
|
|||
|
|
if before.node_id:
|
|||
|
|
node = self.workflow_repository.get_node(before.node_id)
|
|||
|
|
if node and node.selected_image_id == image_id:
|
|||
|
|
self.workflow_repository.update_node(before.node_id, {"selected_image_id": None})
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
同样的模式在批量清理里被放大:`backend/services/generation_service.py:1789-1804` 循环调用 `delete_metadata`,每张图一个独立事务,中途失败只把错误塞进 `errors` 列表继续走。
|
|||
|
|
- **影响**:`workflow_nodes.selected_image_id` 悬挂后,`node_dependency_context_service.py:81-83` 会去 `get_image(node.selected_image_id)` 拿到 `None`,下游节点的参考图静默丢失;`workflow_service` 判断"节点是否已完成"也会得出错误结论。另外 `character_profile_card_assets.image_id` 是 `ON DELETE RESTRICT`(`0015:32`),所以同一个 `delete_metadata` 在图片被角色卡引用时会抛 `IntegrityError` 且没有任何捕获,直接 500。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 把这组操作下沉成仓储的一个方法 `ImageRepository.delete_with_node_release(image_id)`,在**同一连接**里 DELETE + `UPDATE workflow_nodes SET selected_image_id = NULL WHERE selected_image_id = ?`,一次 `commit()`。参照 `job_repository.delete_if_status`(`:382-431`)已经这么做了——同一个仓库里两种写法并存,说明是遗漏而非有意。
|
|||
|
|
2. 补迁移 `0019`:给 `workflow_nodes.selected_image_id` 加 `REFERENCES generation_images(image_id) ON DELETE SET NULL`(SQLite 需 `ALTER TABLE` 重建表,或至少加一条一次性的孤儿清理 UPDATE)。
|
|||
|
|
3. `delete_metadata` 捕获 `IntegrityError`,翻译成"该图片已被角色卡引用,无法删除"的业务错误。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P2-中等] 陈旧任务恢复的时间戳比较绑死了 SQLite 的 UTC 语义,PG 下会静默失效
|
|||
|
|
|
|||
|
|
- **位置**:`backend/services/queue_service.py:457-468`、`database/repositories/job_repository.py:180-201`
|
|||
|
|
- **问题**:`cutoff_value` 在 Python 侧按 **UTC** 格式化成 `"%Y-%m-%d %H:%M:%S"` 字符串,与列 `updated_at` 做比较。SQLite 的 `CURRENT_TIMESTAMP` 恰好是 UTC 且恰好是这个格式,所以能工作。PostgreSQL 里 `updated_at TIMESTAMP`(无时区)的 `CURRENT_TIMESTAMP` 返回的是**会话本地时间**,两者相差一个时区偏移。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/queue_service.py:456-468
|
|||
|
|
observed_at = now or datetime.now(timezone.utc)
|
|||
|
|
...
|
|||
|
|
cutoff = observed_at.astimezone(timezone.utc) - timedelta(
|
|||
|
|
seconds=max(int(stale_after_seconds), 1)
|
|||
|
|
)
|
|||
|
|
cutoff_value = cutoff.strftime("%Y-%m-%d %H:%M:%S")
|
|||
|
|
candidates = self.repository.list_stale(
|
|||
|
|
statuses=("running", "retrying"),
|
|||
|
|
updated_before=cutoff_value,
|
|||
|
|
limit=min(max(int(limit), 1), 500),
|
|||
|
|
)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/job_repository.py:182, 191-201
|
|||
|
|
age_clause = f"AND updated_at <= {placeholder}" if updated_before else ""
|
|||
|
|
...
|
|||
|
|
WHERE status IN ({status_placeholders})
|
|||
|
|
{age_clause}
|
|||
|
|
ORDER BY updated_at ASC, created_at ASC, task_id ASC
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:PG 服务器在 UTC+8 时,库里的 `updated_at` 永远比 UTC 的 `cutoff` 大 8 小时,`updated_at <= cutoff` 恒不成立 → **`recover_stale_tasks` 永远返回空**,上个进程遗留的 `running` 任务永久卡死,且不报错、日志里只会显示"recovered: 0"。反向时区(UTC-x)则会把刚提交 1 分钟的任务当成陈旧任务抢走,造成重复执行。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 统一在 SQL 侧算时间差,不要在 Python 侧格式化字符串:
|
|||
|
|
```sql
|
|||
|
|
AND updated_at <= CURRENT_TIMESTAMP - {interval_expr}
|
|||
|
|
```
|
|||
|
|
SQLite 用 `datetime('now', ?)`,PG 用 `NOW() - (?::text)::interval`,由 `Database.backend` 分派。
|
|||
|
|
2. 或者退一步:把所有 `TIMESTAMP` 列迁移成 `TIMESTAMPTZ`(PG)并在建连时 `SET TIME ZONE 'UTC'`,让两个后端的 `CURRENT_TIMESTAMP` 语义对齐。
|
|||
|
|
3. 补一条断言:`_row_to_task` 里 `str(row["created_at"])` 在 PG 下返回的是 `datetime` 的 repr(`2026-07-27 03:48:00`),在 SQLite 下是原始字符串——目前碰巧格式一致,但这是巧合而非契约,值得写进测试。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P2-中等] `MaterialAnalyticsService.summarize` 无条件全表扫描 9 张表,含 `generation_tasks` 的 JSON blob
|
|||
|
|
|
|||
|
|
- **位置**:`backend/services/material_analytics_service.py:16-47`
|
|||
|
|
- **问题**:一次汇总调用里发了 9 条 SELECT,全部**没有 WHERE 时间窗、没有 LIMIT、没有聚合下推**,把整张表拉进 Python 内存后用 `Counter` 统计。其中 `generation_tasks` 还带上了 `request_json` / `metadata_json` 两个大 JSON 列。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# backend/services/material_analytics_service.py:41-45
|
|||
|
|
style_validation_cases = conn.execute(
|
|||
|
|
"SELECT status, fidelity_score FROM advanced_style_validation_cases"
|
|||
|
|
).fetchall()
|
|||
|
|
audits = conn.execute("SELECT action, after_snapshot, metadata_json FROM audit_logs WHERE action IN ('wardrobe_match','apply_wardrobe_template','recognize_material','renormalize_material')").fetchall()
|
|||
|
|
tasks = conn.execute("SELECT model_route, request_json, metadata_json FROM generation_tasks").fetchall()
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:`generation_tasks` 和 `audit_logs` 是**只增不删**的表(`job_repository.delete_if_status` 只删终态任务且需显式调用)。运行几个月后单次调用就会拉几百 MB 进内存;在没有 WAL 的前提下(见 P1-2),这个长读事务还会把整库读锁住,阻塞所有写。`audit_logs` 那条虽然有 `action IN (...)`,但 `idx_audit_logs_action ON audit_logs(action, created_at)`(`0001:16`)只能过滤,仍要回表读 `after_snapshot`。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 加时间窗参数(默认最近 30 天)并走已有索引:`WHERE created_at >= ?`。`generation_tasks` 需要补 `idx_generation_tasks_created ON generation_tasks(created_at)`(现有三个索引都以 `status`/`workflow_id`/`profile_id` 打头)。
|
|||
|
|
2. 能下推的统计直接下推:`recognition_status` 计数、`fidelity_score` 的 AVG/COUNT 全部改成 `GROUP BY` + 聚合函数,只有必须解析 JSON 的部分才拉行。
|
|||
|
|
3. 结果加缓存(这是 dashboard 类只读指标,秒级过期完全够用)。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P2-中等] `profile_repository` 每行 10 个相关子查询,且同一组谓词被复制三份
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/profile_repository.py:32-117`(三个常量)、`:186-194`、`:222-232`
|
|||
|
|
- **问题**:`PROFILE_GENERATION_KIND_COLUMN`(3 个 EXISTS)、`PROFILE_CATEGORY_COLUMN`(3 个 EXISTS,谓词与前者几乎逐字相同)、`DETAILED_WORKFLOW_STATE_COLUMN`(4 个 EXISTS,前两个又是同样的谓词)被拼进同一条 SELECT。结果集每一行都要跑 10 个相关子查询,其中"sealed 角色卡"和"legacy 归档工作流"这两个判断各被重复计算 3 次。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/profile_repository.py:33-39 与 :60-66 —— 逐字重复
|
|||
|
|
WHEN EXISTS (
|
|||
|
|
SELECT 1
|
|||
|
|
FROM character_profile_cards AS profile_card
|
|||
|
|
WHERE profile_card.profile_id = character_profiles.profile_id
|
|||
|
|
AND LOWER(COALESCE(profile_card.status, '')) = 'sealed'
|
|||
|
|
) THEN 'detailed' # 第二处只有 THEN 分支不同:THEN 'profile_card'
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/profile_repository.py:222-232 —— 三份常量同时拼入
|
|||
|
|
rows = conn.execute(
|
|||
|
|
f"""
|
|||
|
|
SELECT {PROFILE_COLUMNS}, {PROFILE_GENERATION_KIND_COLUMN},
|
|||
|
|
{PROFILE_CATEGORY_COLUMN}, {DETAILED_WORKFLOW_STATE_COLUMN}
|
|||
|
|
FROM character_profiles
|
|||
|
|
{where}
|
|||
|
|
ORDER BY updated_at DESC, created_at DESC
|
|||
|
|
LIMIT {normalized_page_size} OFFSET {offset}
|
|||
|
|
""",
|
|||
|
|
tuple(params),
|
|||
|
|
).fetchall()
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:`_page_size` 上限 100(`:362-367`),即最坏一次列表请求触发 1000 次相关子查询;其中 3 次带 `LIKE '%...%'` 前缀通配符,无法走索引,必须读出 `profile_workflows.metadata_json` 全文逐行匹配。加上 P1-2 的无 WAL,列表接口会成为写路径的主要阻塞源。另外三份重复的 CASE 是纯粹的维护地雷——改判定规则要同步改三处,而 `'archived'` 这个状态目前只出现在 `PROFILE_CATEGORY_COLUMN:80` 而不在 `PROFILE_GENERATION_KIND_COLUMN:53`,两处已经开始漂移了。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 用一次 `LEFT JOIN LATERAL` / 派生表把三个 EXISTS 结果算一遍,再在外层 CASE 里复用:
|
|||
|
|
```sql
|
|||
|
|
FROM character_profiles cp
|
|||
|
|
LEFT JOIN (
|
|||
|
|
SELECT profile_id, 1 AS has_sealed_card FROM character_profile_cards
|
|||
|
|
WHERE LOWER(COALESCE(status,'')) = 'sealed' GROUP BY profile_id
|
|||
|
|
) sc ON sc.profile_id = cp.profile_id
|
|||
|
|
```
|
|||
|
|
三个输出列都从这几个标志位派生,子查询数从 10 降到 3。
|
|||
|
|
2. `LIKE '%"final_archive_history_id"%'` 提升为真实列(与 P1-1 的修复合并做)。
|
|||
|
|
3. `character_profile_cards` 补 `idx_profile_cards_profile_status ON character_profile_cards(profile_id, status)`——现有的 `idx_profile_cards_profile_version(profile_id, version)`(`0015:17-18`)对这个谓词只能用到前缀。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P2-中等] 14 个仓储零抽象:131 处手写连接样板、13 份 JSON 助手、分页上限还不一致
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/` 全目录
|
|||
|
|
- **问题**:没有基类、没有 context manager、没有共享 helper 模块。实测重复量:
|
|||
|
|
|
|||
|
|
| 重复项 | 份数 | 证据 |
|
|||
|
|
|---|---|---|
|
|||
|
|
| `conn = self.database.connect()` + `try/finally close()` | **131** | `advanced_material_repository` 37、`workflow_repository` 16、`workflow_round_repository` 14、`job_repository` 12、`model_provider_repository` 10 … |
|
|||
|
|
| `def placeholder(self)` property | 13 | 每个仓储一份,实现全部是 `return self.database._placeholder` |
|
|||
|
|
| `_json_or_none` | 12 | 实现全部是 `json.dumps(value, ensure_ascii=False, sort_keys=True)` |
|
|||
|
|
| JSON 解码助手(`_json_loads` / `_json_object` / `_json`) | 13 | 且语义**不一致**(见下) |
|
|||
|
|
| `_page` / `_page_size` | 4 组 | 且上限不一致 |
|
|||
|
|
| `_rollback(conn)` | 3 | `advanced_material:2119`、`cross_review:1464`、`round:1378` |
|
|||
|
|
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/job_repository.py:617-622 —— 上限 100
|
|||
|
|
def _page_size(value: Any) -> int:
|
|||
|
|
try:
|
|||
|
|
page_size = int(value)
|
|||
|
|
except (TypeError, ValueError):
|
|||
|
|
page_size = 30
|
|||
|
|
return min(max(page_size, 1), 100)
|
|||
|
|
```
|
|||
|
|
```python
|
|||
|
|
# database/repositories/advanced_material_repository.py:2115-2116 —— 上限 200,且不吞异常
|
|||
|
|
def _page_size(value: int) -> int:
|
|||
|
|
return min(max(int(value or 30), 1), 200)
|
|||
|
|
```
|
|||
|
|
两者行为差两处:上限(100 vs 200)、非法输入处理(回落 30 vs 抛 `ValueError`)。
|
|||
|
|
|
|||
|
|
JSON 解码同样分裂:
|
|||
|
|
```python
|
|||
|
|
# database/repositories/job_repository.py:625-628 —— 脏数据直接抛
|
|||
|
|
def _json_loads(value: str | None) -> dict[str, Any]:
|
|||
|
|
if not value:
|
|||
|
|
return {}
|
|||
|
|
return json.loads(value)
|
|||
|
|
```
|
|||
|
|
```python
|
|||
|
|
# database/repositories/workflow_round_repository.py:1324-1328 —— 脏数据降级为 {}
|
|||
|
|
def _json_object(value: str | None) -> dict[str, Any]:
|
|||
|
|
if not value:
|
|||
|
|
return {}
|
|||
|
|
parsed = json.loads(value)
|
|||
|
|
return parsed if isinstance(parsed, dict) else {}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
另外 `for key in allowed:` 里的 `allowed` 是 **set**(`workflow_repository.py:281-294`、`job_repository.py:569-577`、`profile_repository.py:243-256` 等),Python 的字符串哈希随机化让 SET 子句顺序**每个进程都不同**,生成的 SQL 文本随之变化,SQLite 的 statement cache 命中率被打散。
|
|||
|
|
|
|||
|
|
- **影响**:
|
|||
|
|
- `_json_loads` 遇到非 dict 的 JSON(比如历史遗留写进去的数组)会把 `list` 塞进 `GenerationTask.metadata`,后续 `task.metadata.get(...)` 抛 `AttributeError`;同样的数据在 round 仓储里则安全降级为 `{}` —— 同一份脏数据的行为取决于走哪个仓储。
|
|||
|
|
- 分页上限不一致意味着 `/advanced-materials` 单页最多 200 条而其他接口 100 条,前端分页组件无法统一。
|
|||
|
|
- 131 处样板意味着任何一次连接层改动(比如加 WAL、加 retry、加慢查询日志)都要改 131 个地方,而这次审计已经发现 `image_repository.delete_metadata` 少了 `rollback`、`profile_repository` 全文没有 `_rollback` 而 `advanced_material_repository` 有——遗漏正是重复的直接产物。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 新建 `database/repositories/base.py`:
|
|||
|
|
```python
|
|||
|
|
class BaseRepository:
|
|||
|
|
def __init__(self, database: Database):
|
|||
|
|
self.database = database
|
|||
|
|
|
|||
|
|
@property
|
|||
|
|
def placeholder(self) -> str:
|
|||
|
|
return self.database._placeholder
|
|||
|
|
|
|||
|
|
@contextmanager
|
|||
|
|
def connection(self, *, write: bool = False):
|
|||
|
|
conn = self.database.connect()
|
|||
|
|
try:
|
|||
|
|
if write and self.database.backend == "sqlite":
|
|||
|
|
conn.execute("BEGIN IMMEDIATE")
|
|||
|
|
yield conn
|
|||
|
|
if write:
|
|||
|
|
conn.commit()
|
|||
|
|
except Exception:
|
|||
|
|
with suppress(Exception):
|
|||
|
|
conn.rollback()
|
|||
|
|
raise
|
|||
|
|
finally:
|
|||
|
|
with suppress(Exception):
|
|||
|
|
conn.close()
|
|||
|
|
|
|||
|
|
def lock_clause(self) -> str:
|
|||
|
|
return " FOR UPDATE" if self.database.backend == "postgresql" else ""
|
|||
|
|
```
|
|||
|
|
131 处 `try/finally` 塌缩成 `with self.connection(write=True) as conn:`,`BEGIN IMMEDIATE` / `FOR UPDATE` / `rollback` 三件事再也不会各写各的。
|
|||
|
|
2. 新建 `database/repositories/_sql.py`,唯一实现 `json_or_none` / `json_object` / `page` / `page_size` / `optional_text`,并把分页上限统一成一个常量。JSON 解码统一采用防御式(`_json_object` 那版)。
|
|||
|
|
3. `_update_assignments` 里的 `allowed` 一律改成 `tuple`(有序),让同一批字段生成稳定的 SQL 文本。
|
|||
|
|
4. 抽出 `build_update(table, pk, allowed, payload)` 通用构造器——目前 `workflow_repository` 里 `update_workflow` / `update_workflow_if_status` / `update_node` / `update_node_if_status` 四份 assignment 循环几乎逐字相同(`:154-160`、`:203-209`、`:298-304`、`:357-363`)。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P3-轻微] 关键词过滤未转义 LIKE 通配符
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/profile_repository.py:305-310`、`database/repositories/advanced_material_repository.py:493-498`
|
|||
|
|
- **问题**:用户输入直接包进 `%...%`,输入里的 `%` 和 `_` 会被当作通配符。
|
|||
|
|
- **代码**:
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/repositories/profile_repository.py:305-310
|
|||
|
|
if keyword:
|
|||
|
|
clauses.append(
|
|||
|
|
f"(name LIKE {placeholder} OR description LIKE {placeholder})"
|
|||
|
|
)
|
|||
|
|
like_value = f"%{keyword}%"
|
|||
|
|
params.extend([like_value, like_value])
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- **影响**:不是注入(参数是绑定的),但搜索 `50%` 会匹配到所有以 `50` 开头的记录,搜索 `_` 匹配任意单字符——用户可以用单个 `%` 拉出全表。结合前缀通配符导致的全表扫描,是个小的 DoS 面。
|
|||
|
|
- **修复建议**:`keyword.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")`,SQL 侧加 `ESCAPE '\'`(SQLite 和 PG 都支持)。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P3-轻微] PostgreSQL 迁移执行器按 `;` 裸切分;psycopg2 适配器不关闭游标
|
|||
|
|
|
|||
|
|
- **位置**:`database/db_helper.py:280-286`、`:301-317`
|
|||
|
|
- **问题**:
|
|||
|
|
```python
|
|||
|
|
# database/db_helper.py:280-286
|
|||
|
|
def _execute_script(self, conn, script: str) -> None:
|
|||
|
|
if self.backend == "sqlite":
|
|||
|
|
conn.executescript(script)
|
|||
|
|
return
|
|||
|
|
statements = [statement.strip() for statement in script.split(";") if statement.strip()]
|
|||
|
|
for statement in statements:
|
|||
|
|
conn.execute(statement)
|
|||
|
|
```
|
|||
|
|
按字面 `;` 切分,任何出现在字符串字面量、`$$ ... $$` 函数体或注释里的分号都会把语句劈成两半。当前 18 个迁移恰好没有触发(唯一带引号的是 `WHERE status IN ('queued', 'running')`),但下一条含触发器 / 函数 / 默认值字符串的迁移就会炸。
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# database/db_helper.py:305-308 —— 每次 execute 新建游标,从不关闭
|
|||
|
|
def execute(self, query: str, params: tuple[Any, ...] | list[Any] | None = None):
|
|||
|
|
cursor = self.connection.cursor()
|
|||
|
|
cursor.execute(query, tuple(params or ()))
|
|||
|
|
return cursor
|
|||
|
|
```
|
|||
|
|
- **影响**:迁移切分是一颗定时炸弹(现在无害、下一次改动即爆);游标不关闭因为连接是每次调用即建即关,实际泄漏窗口很短,但 `advanced_material_repository` 有 37 处连接、单个方法内可能连发 5-6 条语句,psycopg2 路径下这些游标要等 `close()` 才释放。
|
|||
|
|
- **修复建议**:
|
|||
|
|
1. 迁移执行改用 `sqlparse.split()`,或退而求其次:约定迁移脚本用 `-- statement-breakpoint` 分隔符显式切分。
|
|||
|
|
2. 适配器改成 `with self.connection.cursor() as cursor:`,或返回一个持有游标并在 `__del__` / 上下文退出时关闭的包装。更彻底的做法是删掉 psycopg2 回退分支——psycopg3 已经是标准,维护两条驱动路径的收益不抵成本。
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
### [P3-轻微] `list_stale` 与图库列表的排序键与现有索引不匹配
|
|||
|
|
|
|||
|
|
- **位置**:`database/repositories/job_repository.py:191-201`、`database/repositories/image_repository.py:171-180`
|
|||
|
|
- **问题**:
|
|||
|
|
- `list_stale` 是 `WHERE status IN (...) AND updated_at <= ? ORDER BY updated_at ASC`,但 `generation_tasks` 上只有 `idx_generation_tasks_status(status, created_at)`(`0005:22-23`),`updated_at` 完全没索引 → 过滤后必须 filesort。
|
|||
|
|
- `list_images` 的 `ORDER BY is_archived ASC, created_at DESC, image_id DESC`,首排序键 `is_archived` 不在 `idx_images_profile(profile_id, created_at)`(`0004:30-31`)里,索引的有序性被浪费。
|
|||
|
|
- **影响**:任务表只增不删,`recover_stale_tasks` 在启动时还会以 `updated_before=None, limit=None` 全量扫(`advanced_material_worker_runtime.py:82-84`),随数据量线性劣化;图库分页在归档图较多时同理。
|
|||
|
|
- **修复建议**:迁移 `0019` 补两条索引:
|
|||
|
|
```sql
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_generation_tasks_status_updated
|
|||
|
|
ON generation_tasks(status, updated_at);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_images_profile_archived
|
|||
|
|
ON generation_images(profile_id, is_archived, created_at DESC);
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
---
|
|||
|
|
|
|||
|
|
## 未发现问题的项(已核查,供交叉验证)
|
|||
|
|
|
|||
|
|
- **SQL 注入**:全部 SQL 走 `?`/`%s` 绑定;唯一的动态表名 `wardrobe_repository.py:36-42` 有 `WARDROBE_TABLES` 白名单并对非法值 `raise ValueError`;`orphan_media_service.py:33-64` 的 `{table}` / `{columns}` 全部来自代码内硬编码元组;`LIMIT/OFFSET` 的直接插值(`profile_repository.py:229`)经过 `_page/_page_size` 的 `int()` + `min/max` 钳制。**未发现可利用点。**
|
|||
|
|
- **外键与级联**:`PRAGMA foreign_keys = ON` 在每次连接时执行(`db_helper.py:168`),且是在事务外执行的,生效。级联策略设计合理(工作流 CASCADE、图片 SET NULL、已接受素材 RESTRICT)。除 `workflow_nodes.selected_image_id`(见 P2-1)和 `advanced_material_imports.current_attempt_id / accepted_material_id`(0016 未声明外键,但受 CASCADE 保护,风险低)外无缺失。
|
|||
|
|
- **迁移可重入性**:18 个脚本全部使用 `CREATE TABLE/INDEX IF NOT EXISTS`,无破坏性 DDL、无 DROP、无数据变更。`initialize()` 的双重判定(`_migration_applied` + `_migration_tables_exist`,`db_helper.py:180-184`)在部分失败后能自愈重放。**这块做得好。**
|
|||
|
|
- **仓储内部原子性**:`create_round_with_candidates`、`apply_feedback_atomic`、`confirm_candidate_atomic`、`profile_card_repository.finalize`、`workflow_cross_review_repository.*_atomic` 均正确使用 `BEGIN IMMEDIATE`(SQLite)/ `FOR UPDATE`(PG)+ `rollback` + 幂等键重放,且 `self.get_*()` 的二次连接调用都发生在 `commit()`/`rollback()` **之后**,不会自锁。**这块也做得好。**
|
|||
|
|
- **连接泄漏**:131 处 `connect()` 全部有 `finally: conn.close()` 配对,未发现漏关。
|