Async scraping, scrape_logs, and CLI

Background scraping:
- POST /api/scrape returns 202 immediately with run_id; scraping runs async
- GET  /api/scrape-status?run_id=xxx polls for results per venue
- scrape_logs table: per-venue status (running/ok/error), events_saved, error, timestamps

CLI (npm run scrape):
- npm run scrape              — 全会場をスクレイプ、結果を色付きで出力
- npm run scrape liquid-room  — 特定会場のみ
- npm run scrape -- --list    — 登録済み会場一覧を表示
- エラー時は exit code 1 + エラーメッセージを dim 表示

Venues page:
- 最終スクレイプ日時・成否をインラインで表示
- 会場ごとの「更新」ボタンを追加

Bug fix: upsertEvent に description/optional fields のデフォルト値を設定し
better-sqlite3 の "Missing named parameter" エラーを解消

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

1 files changed, 101 insertions, 1 deletions

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index 0c55991..26735c6 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -45,6 +45,21 @@ function initSchema(db: Database.Database) {
 
     CREATE INDEX IF NOT EXISTS idx_events_date     ON events(date);
     CREATE INDEX IF NOT EXISTS idx_events_venue_id ON events(venue_id);
+
+    CREATE TABLE IF NOT EXISTS scrape_logs (
+      id           INTEGER PRIMARY KEY AUTOINCREMENT,
+      run_id       TEXT    NOT NULL,
+      venue_id     TEXT    NOT NULL,
+      venue_name   TEXT    NOT NULL,
+      status       TEXT    NOT NULL DEFAULT 'running',  -- running | ok | error
+      events_saved INTEGER NOT NULL DEFAULT 0,
+      error        TEXT,
+      started_at   TEXT    NOT NULL DEFAULT (datetime('now')),
+      finished_at  TEXT
+    );
+
+    CREATE INDEX IF NOT EXISTS idx_scrape_logs_run_id   ON scrape_logs(run_id);
+    CREATE INDEX IF NOT EXISTS idx_scrape_logs_venue_id ON scrape_logs(venue_id);
   `);
 }
 
@@ -102,7 +117,19 @@ export function upsertVenue(
     .run(id, name, url, area ?? null);
 }
 
-export function upsertEvent(event: EventInput) {
+export function upsertEvent(raw: EventInput) {
+  // Ensure all named parameters exist (better-sqlite3 requires them all)
+  const event = {
+    artist: null,
+    start_time: null,
+    open_time: null,
+    ticket_url: null,
+    price: null,
+    image_url: null,
+    description: null,
+    source_url: null,
+    ...raw,
+  };
   getDb()
     .prepare(
       `INSERT INTO events
@@ -190,3 +217,76 @@ export function getVenues(): Venue[] {
     )
     .all() as Venue[];
 }
+
+// ---------- Scrape logs ----------
+
+export interface ScrapeLog {
+  id: number;
+  run_id: string;
+  venue_id: string;
+  venue_name: string;
+  status: "running" | "ok" | "error";
+  events_saved: number;
+  error: string | null;
+  started_at: string;
+  finished_at: string | null;
+}
+
+export function insertScrapeLog(
+  run_id: string,
+  venue_id: string,
+  venue_name: string
+): number {
+  const result = getDb()
+    .prepare(
+      `INSERT INTO scrape_logs (run_id, venue_id, venue_name, status)
+       VALUES (?, ?, ?, 'running')`
+    )
+    .run(run_id, venue_id, venue_name);
+  return result.lastInsertRowid as number;
+}
+
+export function updateScrapeLog(
+  id: number,
+  status: "ok" | "error",
+  events_saved: number,
+  error?: string
+) {
+  getDb()
+    .prepare(
+      `UPDATE scrape_logs
+       SET status = ?, events_saved = ?, error = ?, finished_at = datetime('now')
+       WHERE id = ?`
+    )
+    .run(status, events_saved, error ?? null, id);
+}
+
+export function getLatestScrapeRun(): ScrapeLog[] {
+  return getDb()
+    .prepare(
+      `SELECT * FROM scrape_logs
+       WHERE run_id = (SELECT run_id FROM scrape_logs ORDER BY started_at DESC LIMIT 1)
+       ORDER BY id ASC`
+    )
+    .all() as ScrapeLog[];
+}
+
+export function getScrapeRunById(run_id: string): ScrapeLog[] {
+  return getDb()
+    .prepare(
+      "SELECT * FROM scrape_logs WHERE run_id = ? ORDER BY id ASC"
+    )
+    .all(run_id) as ScrapeLog[];
+}
+
+export function getLastScrapePerVenue(): ScrapeLog[] {
+  return getDb()
+    .prepare(
+      `SELECT s.* FROM scrape_logs s
+       INNER JOIN (
+         SELECT venue_id, MAX(started_at) AS latest FROM scrape_logs GROUP BY venue_id
+       ) t ON s.venue_id = t.venue_id AND s.started_at = t.latest
+       ORDER BY s.venue_name ASC`
+    )
+    .all() as ScrapeLog[];
+}