Add /api/export and /api/import endpoints for DB backup and sync

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

1 files changed, 119 insertions, 0 deletions

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index bf63e70..e35bba6 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -408,3 +408,122 @@ export function updateArtist(id: string, input: UpdateArtistInput): void {
     ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
   })();
 }
+
+// ── Export / Import ───────────────────────────────────────────────────────────
+
+interface BandArtistRaw {
+  band_id: string;
+  artist_id: string;
+  role: string | null;
+  order_index: number;
+}
+
+export interface DbExport {
+  version: 1;
+  exported_at: string;
+  bands: Band[];
+  band_links: BandLink[];
+  artists: Artist[];
+  artist_links: ArtistLink[];
+  band_artists: BandArtistRaw[];
+  band_revisions: BandRevision[];
+  artist_revisions: ArtistRevision[];
+}
+
+export function exportDb(): DbExport {
+  const db = getDb();
+  return {
+    version: 1,
+    exported_at: new Date().toISOString(),
+    bands: db.prepare("SELECT * FROM bands").all() as Band[],
+    band_links: db.prepare("SELECT * FROM band_links ORDER BY band_id, order_index").all() as BandLink[],
+    artists: db.prepare("SELECT * FROM artists").all() as Artist[],
+    artist_links: db.prepare("SELECT * FROM artist_links ORDER BY artist_id, order_index").all() as ArtistLink[],
+    band_artists: db.prepare("SELECT * FROM band_artists ORDER BY band_id, order_index").all() as BandArtistRaw[],
+    band_revisions: db.prepare("SELECT * FROM band_revisions ORDER BY created_at").all() as BandRevision[],
+    artist_revisions: db.prepare("SELECT * FROM artist_revisions ORDER BY created_at").all() as ArtistRevision[],
+  };
+}
+
+export interface ImportResult {
+  bands: number;
+  artists: number;
+  band_links: number;
+  artist_links: number;
+  band_artists: number;
+  band_revisions: number;
+  artist_revisions: number;
+}
+
+export function importDb(data: DbExport): ImportResult {
+  if (data.version !== 1) throw new Error("Unsupported export version");
+  const db = getDb();
+  return db.transaction(() => {
+    db.prepare("DELETE FROM band_artists").run();
+    db.prepare("DELETE FROM band_revisions").run();
+    db.prepare("DELETE FROM artist_revisions").run();
+    db.prepare("DELETE FROM band_links").run();
+    db.prepare("DELETE FROM artist_links").run();
+    db.prepare("DELETE FROM bands").run();
+    db.prepare("DELETE FROM artists").run();
+
+    const insertBand = db.prepare(
+      "INSERT INTO bands (id, slug, name, area, description, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
+    );
+    for (const b of data.bands) {
+      insertBand.run(b.id, b.slug, b.name, b.area, b.description, b.status, b.created_at);
+    }
+
+    const insertArtist = db.prepare(
+      "INSERT INTO artists (id, slug, name, created_at) VALUES (?, ?, ?, ?)"
+    );
+    for (const a of data.artists) {
+      insertArtist.run(a.id, a.slug, a.name, a.created_at);
+    }
+
+    const insertBandLink = db.prepare(
+      "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+    );
+    for (const l of data.band_links) {
+      insertBandLink.run(l.id, l.band_id, l.label, l.url, l.order_index);
+    }
+
+    const insertArtistLink = db.prepare(
+      "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
+    );
+    for (const l of data.artist_links) {
+      insertArtistLink.run(l.id, l.artist_id, l.label, l.url, l.order_index);
+    }
+
+    const insertBandArtist = db.prepare(
+      "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
+    );
+    for (const ba of data.band_artists) {
+      insertBandArtist.run(ba.band_id, ba.artist_id, ba.role, ba.order_index);
+    }
+
+    const insertBandRev = db.prepare(
+      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)"
+    );
+    for (const r of data.band_revisions) {
+      insertBandRev.run(r.id, r.band_id, r.snapshot, r.message, r.ip_address, r.created_at);
+    }
+
+    const insertArtistRev = db.prepare(
+      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)"
+    );
+    for (const r of data.artist_revisions) {
+      insertArtistRev.run(r.id, r.artist_id, r.snapshot, r.message, r.ip_address, r.created_at);
+    }
+
+    return {
+      bands: data.bands.length,
+      artists: data.artists.length,
+      band_links: data.band_links.length,
+      artist_links: data.artist_links.length,
+      band_artists: data.band_artists.length,
+      band_revisions: data.band_revisions.length,
+      artist_revisions: data.artist_revisions.length,
+    };
+  })() as ImportResult;
+}