Add Tokyo livehouse event aggregator service

Full-stack React Router v7 app that scrapes event listings from major
Tokyo live venues (Liquid Room, WWW/WWW X, Shibuya O-EAST, Shinjuku LOFT,
Club Quattro) and stores them in SQLite for browsing and search.

- Modular scraper architecture: add a new venue by dropping a file in
  app/scrapers/ and registering it in index.ts
- Routes: /events (filter by keyword/venue/date), /events/:id, /venues,
  GET /api/scrape
- EventCard shows artist, date/time, venue, ticket URL, and fee
- Post-scrape per-venue Markdown files generated to events/ (dev reference)
- /add-livehouse Claude Code skill defined in .claude/commands/

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

1 files changed, 192 insertions, 0 deletions

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
new file mode 100644
index 0000000..0c55991
--- /dev/null
+++ b/app/lib/db.server.ts
@@ -0,0 +1,192 @@
+import Database from "better-sqlite3";
+import path from "path";
+import { fileURLToPath } from "url";
+
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const DB_PATH = path.join(__dirname, "../../events.db");
+
+let _db: Database.Database | null = null;
+
+function getDb(): Database.Database {
+  if (!_db) {
+    _db = new Database(DB_PATH);
+    _db.pragma("journal_mode = WAL");
+    _db.pragma("foreign_keys = ON");
+    initSchema(_db);
+  }
+  return _db;
+}
+
+function initSchema(db: Database.Database) {
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS venues (
+      id    TEXT PRIMARY KEY,
+      name  TEXT NOT NULL,
+      url   TEXT NOT NULL,
+      area  TEXT
+    );
+
+    CREATE TABLE IF NOT EXISTS events (
+      id          INTEGER PRIMARY KEY AUTOINCREMENT,
+      venue_id    TEXT    NOT NULL REFERENCES venues(id),
+      title       TEXT    NOT NULL,
+      artist      TEXT,
+      date        TEXT    NOT NULL,
+      start_time  TEXT,
+      open_time   TEXT,
+      ticket_url  TEXT,
+      price       TEXT,
+      image_url   TEXT,
+      description TEXT,
+      source_url  TEXT,
+      fetched_at  TEXT    NOT NULL DEFAULT (datetime('now')),
+      UNIQUE(venue_id, title, date)
+    );
+
+    CREATE INDEX IF NOT EXISTS idx_events_date     ON events(date);
+    CREATE INDEX IF NOT EXISTS idx_events_venue_id ON events(venue_id);
+  `);
+}
+
+export interface Venue {
+  id: string;
+  name: string;
+  url: string;
+  area: string | null;
+  event_count?: number;
+}
+
+export interface Event {
+  id: number;
+  venue_id: string;
+  venue_name: string;
+  venue_area: string | null;
+  venue_url?: string;
+  title: string;
+  artist: string | null;
+  date: string;
+  start_time: string | null;
+  open_time: string | null;
+  ticket_url: string | null;
+  price: string | null;
+  image_url: string | null;
+  description: string | null;
+  source_url: string | null;
+  fetched_at: string;
+}
+
+export interface EventInput {
+  venue_id: string;
+  title: string;
+  artist?: string | null;
+  date: string;
+  start_time?: string | null;
+  open_time?: string | null;
+  ticket_url?: string | null;
+  price?: string | null;
+  image_url?: string | null;
+  description?: string | null;
+  source_url?: string | null;
+}
+
+export function upsertVenue(
+  id: string,
+  name: string,
+  url: string,
+  area?: string
+) {
+  getDb()
+    .prepare(
+      "INSERT OR REPLACE INTO venues (id, name, url, area) VALUES (?, ?, ?, ?)"
+    )
+    .run(id, name, url, area ?? null);
+}
+
+export function upsertEvent(event: EventInput) {
+  getDb()
+    .prepare(
+      `INSERT INTO events
+        (venue_id, title, artist, date, start_time, open_time,
+         ticket_url, price, image_url, description, source_url, fetched_at)
+       VALUES
+        (@venue_id, @title, @artist, @date, @start_time, @open_time,
+         @ticket_url, @price, @image_url, @description, @source_url, datetime('now'))
+       ON CONFLICT(venue_id, title, date) DO UPDATE SET
+         artist      = excluded.artist,
+         start_time  = excluded.start_time,
+         open_time   = excluded.open_time,
+         ticket_url  = excluded.ticket_url,
+         price       = excluded.price,
+         image_url   = excluded.image_url,
+         description = excluded.description,
+         source_url  = excluded.source_url,
+         fetched_at  = excluded.fetched_at`
+    )
+    .run(event);
+}
+
+export interface QueryEventsParams {
+  date_from?: string;
+  date_to?: string;
+  venue_id?: string;
+  keyword?: string;
+  limit?: number;
+  offset?: number;
+}
+
+export function queryEvents(params: QueryEventsParams = {}): Event[] {
+  const { date_from, date_to, venue_id, keyword, limit = 60, offset = 0 } =
+    params;
+
+  const clauses: string[] = [];
+  const args: unknown[] = [];
+
+  if (date_from) {
+    clauses.push("e.date >= ?");
+    args.push(date_from);
+  }
+  if (date_to) {
+    clauses.push("e.date <= ?");
+    args.push(date_to);
+  }
+  if (venue_id) {
+    clauses.push("e.venue_id = ?");
+    args.push(venue_id);
+  }
+  if (keyword) {
+    clauses.push("(e.title LIKE ? OR e.artist LIKE ?)");
+    args.push(`%${keyword}%`, `%${keyword}%`);
+  }
+
+  const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
+
+  return getDb()
+    .prepare(
+      `SELECT e.*, v.name AS venue_name, v.area AS venue_area
+       FROM events e JOIN venues v ON e.venue_id = v.id
+       ${where}
+       ORDER BY e.date ASC, e.start_time ASC
+       LIMIT ? OFFSET ?`
+    )
+    .all(...args, limit, offset) as Event[];
+}
+
+export function getEvent(id: number): Event | undefined {
+  return getDb()
+    .prepare(
+      `SELECT e.*, v.name AS venue_name, v.url AS venue_url, v.area AS venue_area
+       FROM events e JOIN venues v ON e.venue_id = v.id
+       WHERE e.id = ?`
+    )
+    .get(id) as Event | undefined;
+}
+
+export function getVenues(): Venue[] {
+  return getDb()
+    .prepare(
+      `SELECT v.*, COUNT(e.id) AS event_count
+       FROM venues v LEFT JOIN events e ON v.id = e.venue_id
+       GROUP BY v.id ORDER BY v.name`
+    )
+    .all() as Venue[];
+}