Node.js 内容管线从 JSON 迁移到 SQLite：表结构、事务、备份与回滚

export interface ArticleRepository {
  list(options): Promise<Array>;
  getBySlug(slug): Promise<object | null>;
  upsert(article): Promise<void>;
  publish(id): Promise<void>;
}

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS articles (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  summary TEXT NOT NULL DEFAULT '',
  content TEXT NOT NULL DEFAULT '',
  source TEXT NOT NULL,
  author TEXT NOT NULL DEFAULT '',
  category TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL CHECK (status IN ('active', 'draft', 'archived')),
  workflow_status TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'guide',
  primary_keyword TEXT NOT NULL DEFAULT '',
  search_intent TEXT NOT NULL DEFAULT '',
  article_url TEXT NOT NULL DEFAULT '',
  cover_url TEXT NOT NULL DEFAULT '',
  published_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  next_review_at TEXT,
  content_version TEXT NOT NULL DEFAULT '1.0',
  reviewed_by TEXT NOT NULL DEFAULT '',
  tags_json TEXT NOT NULL DEFAULT '[]',
  sources_json TEXT NOT NULL DEFAULT '[]',
  test_notes_json TEXT NOT NULL DEFAULT '[]',
  related_site_ids_json TEXT NOT NULL DEFAULT '[]'
);

CREATE INDEX IF NOT EXISTS idx_articles_publish
  ON articles(status, workflow_status, published_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_category
  ON articles(category, published_at DESC);

// scripts/import-articles-to-sqlite.mjs
import { readFile } from 'node:fs/promises';
import { DatabaseSync } from 'node:sqlite';

const input = process.argv[2] ?? 'data/articles.json';
const output = process.argv[3] ?? 'data/content.db';
const parsed = JSON.parse(await readFile(input, 'utf8'));
const articles = Array.isArray(parsed) ? parsed : parsed.articles;
if (!Array.isArray(articles)) throw new Error('articles 必须是数组');

const db = new DatabaseSync(output);
db.exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;');
db.exec(`CREATE TABLE IF NOT EXISTS articles (
  id TEXT PRIMARY KEY, slug TEXT NOT NULL UNIQUE, title TEXT NOT NULL,
  summary TEXT NOT NULL DEFAULT '', content TEXT NOT NULL DEFAULT '',
  source TEXT NOT NULL, author TEXT NOT NULL DEFAULT '',
  category TEXT NOT NULL DEFAULT '', status TEXT NOT NULL,
  workflow_status TEXT NOT NULL, content_type TEXT NOT NULL DEFAULT 'guide',
  primary_keyword TEXT NOT NULL DEFAULT '', search_intent TEXT NOT NULL DEFAULT '',
  article_url TEXT NOT NULL DEFAULT '', cover_url TEXT NOT NULL DEFAULT '',
  published_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
  next_review_at TEXT, content_version TEXT NOT NULL DEFAULT '1.0',
  reviewed_by TEXT NOT NULL DEFAULT '', tags_json TEXT NOT NULL DEFAULT '[]',
  sources_json TEXT NOT NULL DEFAULT '[]', test_notes_json TEXT NOT NULL DEFAULT '[]',
  related_site_ids_json TEXT NOT NULL DEFAULT '[]'
)`);

const insert = db.prepare(`INSERT INTO articles (
  id, slug, title, summary, content, source, author, category, status,
  workflow_status, content_type, primary_keyword, search_intent, article_url,
  cover_url, published_at, created_at, updated_at, next_review_at,
  content_version, reviewed_by, tags_json, sources_json, test_notes_json,
  related_site_ids_json
) VALUES (${Array(25).fill('?').join(',')})`);

const required = value => typeof value === 'string' && value.trim();
const now = new Date().toISOString();

db.exec('BEGIN IMMEDIATE');
try {
  for (const a of articles) {
    if (!required(a.id) || !required(a.slug) || !required(a.title)) {
      throw new Error(`文章缺少 id/slug/title: ${JSON.stringify(a.id ?? null)}`);
    }
    insert.run(
      a.id, a.slug, a.title, a.summary ?? '', a.content ?? '',
      a.source ?? 'original', a.author ?? '', a.category ?? '',
      a.status ?? 'draft', a.workflowStatus ?? 'idea', a.contentType ?? 'guide',
      a.primaryKeyword ?? '', a.searchIntent ?? '', a.articleUrl ?? '',
      a.coverUrl ?? '', a.publishedAt ?? null, a.createdAt ?? now,
      a.updatedAt ?? now, a.nextReviewAt ?? null, a.contentVersion ?? '1.0',
      a.reviewedBy ?? '', JSON.stringify(a.tags ?? []),
      JSON.stringify(a.sources ?? []), JSON.stringify(a.testNotes ?? []),
      JSON.stringify(a.relatedSiteIds ?? [])
    );
  }
  db.exec('COMMIT');
} catch (error) {
  db.exec('ROLLBACK');
  throw error;
} finally {
  db.close();
}

SELECT COUNT(*) AS total FROM articles;
SELECT COUNT(DISTINCT slug) AS unique_slugs FROM articles;
SELECT status, workflow_status, COUNT(*)
FROM articles GROUP BY status, workflow_status;
SELECT id FROM articles
WHERE json_valid(tags_json) = 0
   OR json_valid(sources_json) = 0
   OR json_valid(test_notes_json) = 0;

sqlite3 data/content.db '.backup backups/content-2026-08-13.db'
sqlite3 backups/content-2026-08-13.db 'PRAGMA integrity_check;'

const rows = db.prepare('SELECT * FROM articles ORDER BY slug').all();
const articles = rows.map(row => ({
  ...row,
  workflowStatus: row.workflow_status,
  tags: JSON.parse(row.tags_json),
  sources: JSON.parse(row.sources_json),
  testNotes: JSON.parse(row.test_notes_json)
}));