Add lists feature (band recommendation lists with history)

New lists, list_entries, list_revisions tables; full CRUD routes under /lists; nav link in root.

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

1 files changed, 150 insertions, 0 deletions

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index ab32126..c4b11b4 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -86,6 +86,31 @@ function initSchema(db: Database.Database) {
     );
 
     CREATE INDEX IF NOT EXISTS idx_band_links_band_id ON band_links(band_id);
+
+    CREATE TABLE IF NOT EXISTS lists (
+      id          TEXT PRIMARY KEY,
+      slug        TEXT UNIQUE NOT NULL,
+      title       TEXT NOT NULL,
+      description TEXT NOT NULL DEFAULT '',
+      created_at  TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+
+    CREATE TABLE IF NOT EXISTS list_entries (
+      id          TEXT PRIMARY KEY,
+      list_id     TEXT NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
+      band_name   TEXT NOT NULL,
+      note        TEXT NOT NULL DEFAULT '',
+      order_index INTEGER NOT NULL DEFAULT 0
+    );
+
+    CREATE TABLE IF NOT EXISTS list_revisions (
+      id         TEXT PRIMARY KEY,
+      list_id    TEXT NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
+      snapshot   TEXT NOT NULL,
+      message    TEXT NOT NULL,
+      ip_address TEXT NOT NULL,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
   `);
 
   // migrations
@@ -115,6 +140,8 @@ function initSchema(db: Database.Database) {
     CREATE INDEX IF NOT EXISTS idx_members_artist_id ON members(artist_id);
     CREATE INDEX IF NOT EXISTS idx_band_revisions_band_id ON band_revisions(band_id);
     CREATE INDEX IF NOT EXISTS idx_artist_revisions_artist_id ON artist_revisions(artist_id);
+    CREATE INDEX IF NOT EXISTS idx_list_entries_list_id ON list_entries(list_id);
+    CREATE INDEX IF NOT EXISTS idx_list_revisions_list_id ON list_revisions(list_id);
   `);
 }
 
@@ -534,6 +561,129 @@ export function updateArtist(id: string, input: UpdateArtistInput): void {
   })();
 }
 
+// ── List queries ──────────────────────────────────────────────────────────────
+
+export interface BandList {
+  id: string;
+  slug: string;
+  title: string;
+  description: string;
+  created_at: string;
+}
+
+export interface ListEntry {
+  id: string;
+  list_id: string;
+  band_name: string;
+  note: string;
+  order_index: number;
+}
+
+export interface ListRevision {
+  id: string;
+  list_id: string;
+  snapshot: string;
+  message: string;
+  ip_address: string;
+  created_at: string;
+}
+
+export interface ListEntryInput {
+  band_name: string;
+  note: string;
+}
+
+export interface CreateListInput {
+  id: string;
+  slug: string;
+  title: string;
+  description: string;
+  entries: ListEntryInput[];
+  message: string;
+  ip_address: string;
+}
+
+export interface UpdateListInput {
+  slug: string;
+  title: string;
+  description: string;
+  entries: ListEntryInput[];
+  message: string;
+  ip_address: string;
+}
+
+export function listBandLists(): BandList[] {
+  return getDb().prepare("SELECT * FROM lists ORDER BY created_at DESC").all() as BandList[];
+}
+
+export function getBandListById(id: string): BandList | null {
+  return getDb().prepare("SELECT * FROM lists WHERE id = ?").get(id) as BandList | null;
+}
+
+export function getBandListBySlug(slug: string): BandList | null {
+  return getDb().prepare("SELECT * FROM lists WHERE slug = ?").get(slug) as BandList | null;
+}
+
+export function getListEntries(listId: string): ListEntry[] {
+  return getDb()
+    .prepare("SELECT * FROM list_entries WHERE list_id = ? ORDER BY order_index")
+    .all(listId) as ListEntry[];
+}
+
+export function getListRevisions(listId: string): ListRevision[] {
+  return getDb()
+    .prepare("SELECT * FROM list_revisions WHERE list_id = ? ORDER BY created_at DESC")
+    .all(listId) as ListRevision[];
+}
+
+export function createBandList(input: CreateListInput): BandList {
+  const db = getDb();
+  return db.transaction(() => {
+    db.prepare("INSERT INTO lists (id, slug, title, description) VALUES (?, ?, ?, ?)").run(
+      input.id, input.slug, input.title, input.description
+    );
+    input.entries.forEach((e, i) => {
+      db.prepare(
+        "INSERT INTO list_entries (id, list_id, band_name, note, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), input.id, e.band_name, e.note, i);
+    });
+    const list = getBandListById(input.id)!;
+    const entries = getListEntries(input.id);
+    db.prepare(
+      "INSERT INTO list_revisions (id, list_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), input.id, buildListSnapshot(list, entries), input.message, input.ip_address);
+    return list;
+  })() as BandList;
+}
+
+export function updateBandList(id: string, input: UpdateListInput): void {
+  const db = getDb();
+  db.transaction(() => {
+    db.prepare("UPDATE lists SET slug = ?, title = ?, description = ? WHERE id = ?").run(
+      input.slug, input.title, input.description, id
+    );
+    db.prepare("DELETE FROM list_entries WHERE list_id = ?").run(id);
+    input.entries.forEach((e, i) => {
+      db.prepare(
+        "INSERT INTO list_entries (id, list_id, band_name, note, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), id, e.band_name, e.note, i);
+    });
+    const list = getBandListById(id)!;
+    const entries = getListEntries(id);
+    db.prepare(
+      "INSERT INTO list_revisions (id, list_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), id, buildListSnapshot(list, entries), input.message, input.ip_address);
+  })();
+}
+
+function buildListSnapshot(list: BandList, entries: ListEntry[]): string {
+  return JSON.stringify({
+    title: list.title,
+    description: list.description,
+    entries: entries.map((e) => ({ band_name: e.band_name, note: e.note })),
+  });
+}
+
 // ── Export / Import ───────────────────────────────────────────────────────────
 
 export interface DbExport {