From 874749df46cc291ca61f2f98092ccabfdeffbf16 Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@hetzner.yyamashita.com>
Date: Sat, 26 Sep 2026 00:19:53 +0900
Subject: Add passkey login and favorite search terms
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Single-user auth: any registered passkey logs in as the owner. First
registration needs a one-time token that the app writes to
data/registration-token when no passkey exists yet; once logged in,
more passkeys can be added without a token.

Logged in, /favorites manages a list of terms (e.g. band names).
Events whose title or artist contains a term (NFKC + case-insensitive)
get a ★, and the event list gains an "お気に入りのみ" filter.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---
 app/lib/db.server.ts | 64 +++++++++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 61 insertions(+), 3 deletions(-)

(limited to 'app/lib/db.server.ts')

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index 82a0025..9d6597d 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -9,11 +9,22 @@ const DB_PATH = process.env.DB_PATH
 
 let _db: Database.Database | null = null;
 
-function getDb(): Database.Database {
+/** Directory holding the DB file — also used for other runtime state files. */
+export const DATA_DIR = path.dirname(DB_PATH);
+
+/** Normalization used for favorite matching: full/half-width and case insensitive. */
+export function normalizeTerm(s: string): string {
+  return s.normalize("NFKC").toLowerCase().trim();
+}
+
+export function getDb(): Database.Database {
   if (!_db) {
     _db = new Database(DB_PATH);
     _db.pragma("journal_mode = WAL");
     _db.pragma("foreign_keys = ON");
+    _db.function("nfkc_lower", { deterministic: true }, (s: unknown) =>
+      typeof s === "string" ? normalizeTerm(s) : null
+    );
     initSchema(_db);
   }
   return _db;
@@ -63,6 +74,36 @@ function initSchema(db: Database.Database) {
 
     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);
+
+    -- Single-user auth: any registered passkey logs in as "the" owner
+    CREATE TABLE IF NOT EXISTS passkeys (
+      id           TEXT PRIMARY KEY,           -- credential ID (base64url)
+      public_key   BLOB    NOT NULL,
+      counter      INTEGER NOT NULL DEFAULT 0,
+      transports   TEXT,                       -- JSON array
+      label        TEXT,
+      created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
+      last_used_at TEXT
+    );
+
+    CREATE TABLE IF NOT EXISTS sessions (
+      id         TEXT PRIMARY KEY,
+      created_at TEXT NOT NULL DEFAULT (datetime('now')),
+      expires_at TEXT NOT NULL
+    );
+
+    CREATE TABLE IF NOT EXISTS auth_challenges (
+      id         TEXT PRIMARY KEY,
+      challenge  TEXT NOT NULL,
+      expires_at TEXT NOT NULL
+    );
+
+    CREATE TABLE IF NOT EXISTS favorites (
+      id         INTEGER PRIMARY KEY AUTOINCREMENT,
+      term       TEXT NOT NULL,
+      term_norm  TEXT NOT NULL UNIQUE,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
   `);
 
   // Migration: add capacity column to existing venues tables
@@ -99,6 +140,8 @@ export interface Event {
   description: string | null;
   source_url: string | null;
   fetched_at: string;
+  /** 1 if title/artist matches a favorite term. Only computed when with_favorites is set. */
+  is_favorite?: number;
 }
 
 export interface EventInput {
@@ -173,13 +216,24 @@ export interface QueryEventsParams {
   keyword?: string;
   capacity_range?: CapacityRange;
   area?: string;
+  /** Compute is_favorite for each row */
+  with_favorites?: boolean;
+  /** Only events matching a favorite term */
+  favorites_only?: boolean;
   limit?: number;
   offset?: number;
 }
 
+const FAVORITE_MATCH_SQL = `EXISTS (
+  SELECT 1 FROM favorites f
+  WHERE instr(nfkc_lower(e.title || ' ' || COALESCE(e.artist, '')), f.term_norm) > 0
+)`;
+
 export function queryEvents(params: QueryEventsParams = {}): Event[] {
-  const { date_from, date_to, venue_id, keyword, capacity_range, area, limit = 60, offset = 0 } =
-    params;
+  const {
+    date_from, date_to, venue_id, keyword, capacity_range, area,
+    with_favorites, favorites_only, limit = 60, offset = 0,
+  } = params;
 
   const clauses: string[] = [];
   const args: unknown[] = [];
@@ -211,12 +265,16 @@ export function queryEvents(params: QueryEventsParams = {}): Event[] {
     clauses.push("v.area = ?");
     args.push(area);
   }
+  if (favorites_only) {
+    clauses.push(FAVORITE_MATCH_SQL);
+  }
 
   const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
 
   return getDb()
     .prepare(
       `SELECT e.*, v.name AS venue_name, v.area AS venue_area
+              ${with_favorites ? `, ${FAVORITE_MATCH_SQL} AS is_favorite` : ""}
        FROM events e JOIN venues v ON e.venue_id = v.id
        ${where}
        ORDER BY e.date ASC, e.start_time ASC
-- 
cgit v1.3