Implement band/artist management with version history

Full CRUD for bands and artists: UUID + slug URLs, dynamic link
editor, band-artist associations with roles, per-edit revision
snapshots (message + IP). Add README and CLAUDE.md.

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

1 files changed, 368 insertions, 20 deletions

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index 6aa9313..f1cacbb 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -17,29 +17,377 @@ export function getDb(): Database.Database {
 function initSchema(db: Database.Database) {
   db.exec(`
     CREATE TABLE IF NOT EXISTS bands (
-      id        TEXT PRIMARY KEY,
-      name      TEXT NOT NULL,
-      name_kana TEXT,
-      formed_at TEXT,
-      area      TEXT,
-      genre     TEXT,
-      url       TEXT,
-      created_at TEXT NOT NULL DEFAULT (datetime('now')),
-      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
-    );
-
-    CREATE TABLE IF NOT EXISTS members (
       id         TEXT PRIMARY KEY,
-      band_id    TEXT NOT NULL REFERENCES bands(id),
+      slug       TEXT UNIQUE NOT NULL,
       name       TEXT NOT NULL,
-      name_kana  TEXT,
-      role       TEXT,
-      joined_at  TEXT,
-      left_at    TEXT,
-      created_at TEXT NOT NULL DEFAULT (datetime('now')),
-      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
+      area       TEXT,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
     );
 
-    CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
+    CREATE TABLE IF NOT EXISTS band_links (
+      id          TEXT PRIMARY KEY,
+      band_id     TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
+      label       TEXT NOT NULL,
+      url         TEXT NOT NULL,
+      order_index INTEGER NOT NULL DEFAULT 0
+    );
+
+    CREATE TABLE IF NOT EXISTS artists (
+      id         TEXT PRIMARY KEY,
+      slug       TEXT UNIQUE NOT NULL,
+      name       TEXT NOT NULL,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+
+    CREATE TABLE IF NOT EXISTS artist_links (
+      id          TEXT PRIMARY KEY,
+      artist_id   TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
+      label       TEXT NOT NULL,
+      url         TEXT NOT NULL,
+      order_index INTEGER NOT NULL DEFAULT 0
+    );
+
+    CREATE TABLE IF NOT EXISTS band_artists (
+      band_id     TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
+      artist_id   TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
+      role        TEXT,
+      order_index INTEGER NOT NULL DEFAULT 0,
+      PRIMARY KEY (band_id, artist_id)
+    );
+
+    CREATE TABLE IF NOT EXISTS band_revisions (
+      id         TEXT PRIMARY KEY,
+      band_id    TEXT NOT NULL REFERENCES bands(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'))
+    );
+
+    CREATE TABLE IF NOT EXISTS artist_revisions (
+      id         TEXT PRIMARY KEY,
+      artist_id  TEXT NOT NULL REFERENCES artists(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'))
+    );
+
+    CREATE INDEX IF NOT EXISTS idx_band_links_band_id ON band_links(band_id);
+    CREATE INDEX IF NOT EXISTS idx_artist_links_artist_id ON artist_links(artist_id);
+    CREATE INDEX IF NOT EXISTS idx_band_artists_band_id ON band_artists(band_id);
+    CREATE INDEX IF NOT EXISTS idx_band_artists_artist_id ON band_artists(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);
   `);
 }
+
+export interface Band {
+  id: string;
+  slug: string;
+  name: string;
+  area: string | null;
+  created_at: string;
+}
+
+export interface BandLink {
+  id: string;
+  band_id: string;
+  label: string;
+  url: string;
+  order_index: number;
+}
+
+export interface Artist {
+  id: string;
+  slug: string;
+  name: string;
+  created_at: string;
+}
+
+export interface ArtistLink {
+  id: string;
+  artist_id: string;
+  label: string;
+  url: string;
+  order_index: number;
+}
+
+export interface BandArtistRow {
+  band_id: string;
+  artist_id: string;
+  role: string | null;
+  order_index: number;
+  artist_name: string;
+  artist_slug: string;
+}
+
+export interface ArtistBandRow {
+  band_id: string;
+  artist_id: string;
+  role: string | null;
+  band_name: string;
+  band_slug: string;
+}
+
+export interface BandRevision {
+  id: string;
+  band_id: string;
+  snapshot: string;
+  message: string;
+  ip_address: string;
+  created_at: string;
+}
+
+export interface ArtistRevision {
+  id: string;
+  artist_id: string;
+  snapshot: string;
+  message: string;
+  ip_address: string;
+  created_at: string;
+}
+
+export function getIpAddress(request: Request): string {
+  return (
+    request.headers.get("x-forwarded-for")?.split(",")[0].trim() ??
+    request.headers.get("x-real-ip") ??
+    "unknown"
+  );
+}
+
+export function toSlug(name: string): string {
+  return name
+    .trim()
+    .toLowerCase()
+    .replace(/\s+/g, "-")
+    .replace(/[^\w぀-ヿ一-鿿＀-￯-]/g, "")
+    .replace(/^-+|-+$/g, "");
+}
+
+// ── Band queries ──────────────────────────────────────────────────────────────
+
+export function listBands(): Band[] {
+  return getDb().prepare("SELECT * FROM bands ORDER BY name").all() as Band[];
+}
+
+export function getBandById(id: string): Band | null {
+  return getDb().prepare("SELECT * FROM bands WHERE id = ?").get(id) as Band | null;
+}
+
+export function getBandBySlug(slug: string): Band | null {
+  return getDb().prepare("SELECT * FROM bands WHERE slug = ?").get(slug) as Band | null;
+}
+
+export function getBandLinks(bandId: string): BandLink[] {
+  return getDb()
+    .prepare("SELECT * FROM band_links WHERE band_id = ? ORDER BY order_index")
+    .all(bandId) as BandLink[];
+}
+
+export function getBandArtists(bandId: string): BandArtistRow[] {
+  return getDb()
+    .prepare(
+      `SELECT ba.*, a.name AS artist_name, a.slug AS artist_slug
+       FROM band_artists ba
+       JOIN artists a ON a.id = ba.artist_id
+       WHERE ba.band_id = ?
+       ORDER BY ba.order_index`
+    )
+    .all(bandId) as BandArtistRow[];
+}
+
+export function getBandRevisions(bandId: string): BandRevision[] {
+  return getDb()
+    .prepare("SELECT * FROM band_revisions WHERE band_id = ? ORDER BY created_at DESC")
+    .all(bandId) as BandRevision[];
+}
+
+export interface CreateBandInput {
+  id: string;
+  slug: string;
+  name: string;
+  area: string | null;
+  links: { label: string; url: string }[];
+  artists: { id: string; role: string | null }[];
+  message: string;
+  ip_address: string;
+}
+
+export function createBand(input: CreateBandInput): Band {
+  const db = getDb();
+  return db.transaction(() => {
+    db.prepare("INSERT INTO bands (id, slug, name, area) VALUES (?, ?, ?, ?)").run(
+      input.id, input.slug, input.name, input.area
+    );
+    input.links.forEach((link, i) => {
+      db.prepare(
+        "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), input.id, link.label, link.url, i);
+    });
+    input.artists.forEach((artist, i) => {
+      db.prepare(
+        "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
+      ).run(input.id, artist.id, artist.role, i);
+    });
+    const band = getBandById(input.id)!;
+    const links = getBandLinks(input.id);
+    const artists = getBandArtists(input.id);
+    const snapshot = JSON.stringify({
+      name: band.name,
+      area: band.area,
+      links: links.map((l) => ({ label: l.label, url: l.url })),
+      artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
+    });
+    db.prepare(
+      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), input.id, snapshot, input.message, input.ip_address);
+    return band;
+  })() as Band;
+}
+
+export interface UpdateBandInput {
+  slug: string;
+  name: string;
+  area: string | null;
+  links: { label: string; url: string }[];
+  artists: { id: string; role: string | null }[];
+  message: string;
+  ip_address: string;
+}
+
+export function updateBand(id: string, input: UpdateBandInput): void {
+  const db = getDb();
+  db.transaction(() => {
+    db.prepare("UPDATE bands SET slug = ?, name = ?, area = ? WHERE id = ?").run(
+      input.slug, input.name, input.area, id
+    );
+    db.prepare("DELETE FROM band_links WHERE band_id = ?").run(id);
+    input.links.forEach((link, i) => {
+      db.prepare(
+        "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), id, link.label, link.url, i);
+    });
+    db.prepare("DELETE FROM band_artists WHERE band_id = ?").run(id);
+    input.artists.forEach((artist, i) => {
+      db.prepare(
+        "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
+      ).run(id, artist.id, artist.role, i);
+    });
+    const band = getBandById(id)!;
+    const links = getBandLinks(id);
+    const artists = getBandArtists(id);
+    const snapshot = JSON.stringify({
+      name: band.name,
+      area: band.area,
+      links: links.map((l) => ({ label: l.label, url: l.url })),
+      artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
+    });
+    db.prepare(
+      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
+  })();
+}
+
+// ── Artist queries ────────────────────────────────────────────────────────────
+
+export function listArtists(): Artist[] {
+  return getDb().prepare("SELECT * FROM artists ORDER BY name").all() as Artist[];
+}
+
+export function getArtistById(id: string): Artist | null {
+  return getDb().prepare("SELECT * FROM artists WHERE id = ?").get(id) as Artist | null;
+}
+
+export function getArtistBySlug(slug: string): Artist | null {
+  return getDb().prepare("SELECT * FROM artists WHERE slug = ?").get(slug) as Artist | null;
+}
+
+export function getArtistLinks(artistId: string): ArtistLink[] {
+  return getDb()
+    .prepare("SELECT * FROM artist_links WHERE artist_id = ? ORDER BY order_index")
+    .all(artistId) as ArtistLink[];
+}
+
+export function getArtistBands(artistId: string): ArtistBandRow[] {
+  return getDb()
+    .prepare(
+      `SELECT ba.*, b.name AS band_name, b.slug AS band_slug
+       FROM band_artists ba
+       JOIN bands b ON b.id = ba.band_id
+       WHERE ba.artist_id = ?
+       ORDER BY b.name`
+    )
+    .all(artistId) as ArtistBandRow[];
+}
+
+export function getArtistRevisions(artistId: string): ArtistRevision[] {
+  return getDb()
+    .prepare("SELECT * FROM artist_revisions WHERE artist_id = ? ORDER BY created_at DESC")
+    .all(artistId) as ArtistRevision[];
+}
+
+export interface CreateArtistInput {
+  id: string;
+  slug: string;
+  name: string;
+  links: { label: string; url: string }[];
+  message: string;
+  ip_address: string;
+}
+
+export function createArtist(input: CreateArtistInput): Artist {
+  const db = getDb();
+  return db.transaction(() => {
+    db.prepare("INSERT INTO artists (id, slug, name) VALUES (?, ?, ?)").run(
+      input.id, input.slug, input.name
+    );
+    input.links.forEach((link, i) => {
+      db.prepare(
+        "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), input.id, link.label, link.url, i);
+    });
+    const artist = getArtistById(input.id)!;
+    const links = getArtistLinks(input.id);
+    const snapshot = JSON.stringify({
+      name: artist.name,
+      links: links.map((l) => ({ label: l.label, url: l.url })),
+    });
+    db.prepare(
+      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), input.id, snapshot, input.message, input.ip_address);
+    return artist;
+  })() as Artist;
+}
+
+export interface UpdateArtistInput {
+  slug: string;
+  name: string;
+  links: { label: string; url: string }[];
+  message: string;
+  ip_address: string;
+}
+
+export function updateArtist(id: string, input: UpdateArtistInput): void {
+  const db = getDb();
+  db.transaction(() => {
+    db.prepare("UPDATE artists SET slug = ?, name = ? WHERE id = ?").run(
+      input.slug, input.name, id
+    );
+    db.prepare("DELETE FROM artist_links WHERE artist_id = ?").run(id);
+    input.links.forEach((link, i) => {
+      db.prepare(
+        "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+      ).run(crypto.randomUUID(), id, link.label, link.url, i);
+    });
+    const artist = getArtistById(id)!;
+    const links = getArtistLinks(id);
+    const snapshot = JSON.stringify({
+      name: artist.name,
+      links: links.map((l) => ({ label: l.label, url: l.url })),
+    });
+    db.prepare(
+      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
+    ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
+  })();
+}