GET /api/v1/dramas 500: drama.tags stored as plain text, route does JSON.parse(tags)

发生场景

Importing a new short film (drama #2) into the huobao-drama demo platform via a Python import script. The 5-minute health-check cron reported backend 500 right after the import; the frontend was fine.

问题

After importing drama #2, GET /api/v1/dramas returned 500 on every request. The route does JSON.parse(row.tags) for each row, but the import script had written tags as a plain comma-separated string 'sci-fi,ambulance,flying car' instead of a JSON array string. Because rows are mapped inside one Promise.all, a single malformed row throws and the whole list endpoint 500s.

报错原文（已脱敏/规范化）

GET /api/v1/dramas 500 94ms
SyntaxError: Unexpected token 's', "sci-fi,amb"... is not valid JSON
    at JSON.parse (<anonymous>)
    at <anonymous> (/home/<user>/workspace/huobao-drama/backend/src/routes/dramas.ts:37:31)
    at process.processTicksAndRejections (node:internal/process/task_queues:104:5)
    at async Promise.all (index 0)

适用环境

huobao-drama backend: Node 24, tsx, Hono, better-sqlite3/SQLite. Linux container. SQLite file at data/huobao.sqlite3.

如何确认是这个问题

curl -s -o /dev/null -w "%{http_code}" http://127.0.0.1:5679/api/v1/dramas → expect: 500. Then grep -m1 -A3 'is not valid JSON' backend/backend.log → expect: SyntaxError: Unexpected token ... at JSON.parse (.../routes/dramas.ts:37:31). Then sqlite3 data/huobao.sqlite3 "SELECT id,title,tags FROM dramas WHERE tags NOT LIKE '[%';" → expect: rows whose tags do not start with '['.

不是这个问题时 / 边界

If the endpoint is unreachable / connection refused, the process is down — different problem, restart it. If the 500 comes with a different error (e.g. SQLITE_ERROR: no such table), different cause. If only one drama's detail page 500s while the list endpoint works, the bad row is not in the list path.

根因

Write path and read path disagreed on the column format: the Python import script wrote tags as a human-readable comma-separated string, while the TypeScript route assumed a JSON array string and called JSON.parse unconditionally. One bad row rejects the whole Promise.all, so a single drama poisons the entire list endpoint.

修复步骤

1. Confirm it is this problem: curl -s -o /dev/null -w "%{http_code}" http://127.0.0.1:5679/api/v1/dramas → expect: 500
2. Check the backend log: grep -m1 -A3 'is not valid JSON' backend/backend.log → expect: SyntaxError: Unexpected token ... at JSON.parse (.../routes/dramas.ts:37:31)
3. Find the bad rows: sqlite3 data/huobao.sqlite3 "SELECT id,title,tags FROM dramas WHERE tags NOT LIKE '[%';" → expect: rows whose tags do not start with [
4. Back up the DB: cp data/huobao.sqlite3 /tmp/huobao.bak.sqlite3 → expect: (no output)
5. Fix the value to a JSON array string: sqlite3 data/huobao.sqlite3 "UPDATE dramas SET tags='[\"sci-fi\",\"ambulance\",\"flying car\"]' WHERE id=<id>;" → expect: (no output)
6. Restart the backend: systemctl restart huobao-backend (or restart the node process) → expect: service active
7. Verify: curl -s -o /dev/null -w "%{http_code}" http://127.0.0.1:5679/api/v1/dramas → expect: 200

如何验证

GET /api/v1/dramas returns 200 and the response JSON contains "tags":[...] as an array; no new 'is not valid JSON' lines appear in backend/backend.log.

试过没用的办法

Restarting the backend alone changed nothing — this is a data bug, not a process bug. The ensure-up.sh health check only probed TCP port connectivity, so it kept reporting 'healthy' while the app returned 500 on every request. Had to read the application log (backend/backend.log), not the port probe, to see it.

如何回滚

Restore the backup: cp /tmp/huobao.bak.sqlite3 data/huobao.sqlite3, then restart the backend process.

风险提示

Direct DB writes bypass application validation — back up the sqlite file first. An UPDATE without WHERE would rewrite every row; always scope by id.

验证范围

huobao-drama (Hono + SQLite), Oct 2026

何时失效

When the route stops calling JSON.parse on tags (e.g. tags become a normalized relation/table or are parsed defensively with try/catch + fallback).