Plan -- Shelfie

The thesis

It's 2026. Every other book catalog wants you to scan barcodes or punch in ISBNs one at a time. Shelfie doesn't. You photograph a shelf. Claude reads every spine. We attach a confidence to each detection and color-code the ones worth a glance.

No 1D barcode dance. No 13-digit ISBN typing. Cameras have gotten too good and Claude vision has gotten too smart to keep pretending otherwise. OCR all the way.

The verb is "shelfie." You shelfie a shelf. You shelfied your office last weekend. Use it everywhere -- buttons, copy, marketing.

Architecture

[Phone camera, PWA]
        |
        v
+--------------------+
|  /api/spines       |
|  Claude vision     |
|  -> [{title, author, confidence}]
+--------+-----------+
         |
         v
+--------------------+
|  Review UI         |
|  green / yellow / red bands
|  one-tap fix on yellow + red
+--------+-----------+
         |
         v
+--------------------+
|  /api/enrich       |
|  OpenLibrary -> GB |
|  -> Book metadata  |
+--------+-----------+
         |
         v
+--------------------+        +-----------------+
|  /library          |------->|  Vector store   |
|  cover grid (home) |        |  (lance)        |
|  spines / stacks   |        +-----------------+
|  table view        |
|  search + filters  |
|  subjects + gaps   |
|  graph (optional)  |
+--------------------+

Tech stack

Data model

type Book = {
  id: string;              // hash(title + author)
  title: string;
  author: string;
  isbn13?: string;
  publishedYear?: number;
  subjects: string[];
  cover_url?: string;
  influences?: string[];   // book ids this book references or argues with
  shelf_id: string;
  source_photo_id: string;
  spine_confidence: number;
  read_status: "unread" | "reading" | "read" | "lent";
  lent_to?: string;
  lent_at?: string;
  embedding?: number[];    // 1024-dim, voyage-3
};

type Photo = {
  id: string;
  uploaded_at: string;
  shelf_label?: string;    // "TTRPG cabinet"
  width: number;
  height: number;
  blob_path: string;
};

type Edge = {
  from: string;
  to: string;
  type: "shared_subject" | "author_overlap" | "explicit_reference" | "co_purchased";
  weight: number;
};

Confidence bands

Each detected spine carries a spine_confidence in 0..1. The review UI groups them into three bands.

Bands are derived at render time from spine_confidence. Thresholds tunable in lib/confidence.ts.

Spine-reading prompt (system, cached)

You are reading photographs of bookshelves. Each image contains book spines, often at angles, sometimes blurry, sometimes upside-down. Your job: list every visible book.

Output via the emit_spines tool. For each spine: title, author (best guess), and confidence 0..1. Confidence below 0.5 means "I see text but cannot resolve it." Always include those rows so the user can correct them. Confidence above 0.85 means "I am sure of this title and author."

Never invent. If a spine is fully obscured, omit it.

Edward voice: terse JSON only. No prose.

The model gets called with tool_choice: { type: "tool", name: "emit_spines" } and the photo as an image block. Photos are downsampled to 1568px on the long edge before sending.

Enrichment pipeline

1. Look up the spine result (title, author) against OpenLibrary /search.json?q=.... Take the top hit if Levenshtein distance under 4 on title.
2. If no OpenLibrary hit, fall back to Google Books volumes?q=intitle:"...".
3. If neither, mark metadata_status: "unknown" and surface for one-tap user correction.
4. Pull subjects, publication year, ISBN, cover URL.
5. Compute the embedding once and store in LanceDB.
6. For "influences," do a second-pass call to Claude with the book's existing description and ask for the three most-referenced or most-argued-against books. Opt-in (slower, costs more).

UX shape

Primary surfaces

1. /scan -- Camera. One big "Shelfie a shelf" button. Camera or batch upload.
2. /scan/review/[id] -- The shelf you just shelfied. Cover grid with green / yellow / red outlines. Two taps to fix any yellow or red. Confirm shelf label, save.
3. /library -- Home after the first scan. Cover grid by default, with toggle to Spines / Stacks / Table.
4. /library/[id] -- A single book. Cover, full metadata, your shelf location, related books you also own, three suggestions you don't.
5. /library/subjects -- Topical browse.
6. /library/gaps -- For each cluster, Claude proposes books you're missing. One safe pick, one ambitious pick, one heretical pick.
7. /library/stats -- Counts, oldest book, longest-running subject, most-owned author, last shelfie.
8. /library/graph -- Optional Cosmograph render of the whole library. Bury it behind a button on /library.

The mockup at mockups/library-home.html shows the cover-grid home, the green / yellow / red review screen, the gaps view, and the four library shapes (Grid, Spines, Stacks, Table). Mockup runs off the 37 real books in fixtures/spines/.

Milestones

The order leads with everything needed to render a real, deduped library on screen, because that is the demo. Review (the green/yellow/red UI) is M6 and ships once the home page works -- it is the magic moment but it depends on a believable library to land into.

M1 -- Spine fixtures, dedupe, enrichment (done)

• Spine readings cached at fixtures/spines/IMG_*.json for every shelfie.
• lib/dedupe.ts collapses cross-photo duplicates by normalized title + last-name author.
• lib/enrich.ts queries OpenLibrary first then Google Books. Title-only fallback handles spines where the author is occluded; OpenLibrary public API supplies likely author, subjects, publication year, ISBN, and cover.
• npm run enrich walks the deduped library and writes fixtures/library.json so the rest of the app renders without live API calls.
• Same dedupe semantics applied at the SQLite write path: upsertBook keeps the higher-confidence reading and merges source_photo_ids so the same book never gets stored twice.

M2 -- Library home, four views (the demo's lead)

• /library is a server component reading fixtures/library.json.
• View toggle: Grid (covers), Spines (vertical), Stacks (horizontal), Table.
• Top-bar search filters across title, author, subjects.
• Sort: title, author, year, confidence, last-shelfied.
• Sidebar filters: subject, shelf, year, status, confidence band.
• Each card opens /library/[id].

M3 -- Subjects and stats

• /library/subjects clusters by OpenLibrary subject, shows counts, drills into a filtered grid.
• /library/stats summarises totals, oldest, longest-running subject, most-owned author, last shelfie.

M4 -- Book detail

• /library/[id] renders cover, full metadata, every shelf the book was photographed on, related-by-subject books from the same library.

M5 -- Gaps

• /library/gaps reads the deduped library, sends top subject clusters to Claude Sonnet 4.6 with prompt caching, returns one safe / one ambitious / one heretical pick per cluster, each with citations to existing books.
• Streamed so the user watches the model work.

M6 -- Review screen (the magic moment)

• /scan/review/[id] renders detected spines as confidence-banded cards (green / yellow / red).
• Tap any yellow/red card, the system queries OpenLibrary for likely matches (title + author), the user picks one in two taps.
• Saving promotes the spines into the library via /api/enrich and dedupes against existing books.

M7 -- Search

• Lexical search ships first: title, author, subject, OR-substring. Wired into the M2 search bar.
• Voyage embeddings on every book add semantic search later. "Big red book about traps" should find Grimtooth's.

M8 -- Export

• CSV (universal).
• BibTeX (academic).
• Goodreads import format so leavers can bring their reading history.

M9 -- Graph (optional, behind a button)

• Edges from shared_subject and author_overlap, computed on the fly.
• /library/graph renders Cosmograph. Surfaced as a "View as graph" button on /library.

M10 -- Status, polish (ongoing)

• Read / reading / unread / lent. Filter by status. Lent: free-text "to whom" plus a date.
• Influence edges via a second-pass Claude call (opt-in).
• Per-shelf retake. Static-site export so Edward can host his own catalog.

Hosted multi-tenant build-out

After the local Next.js app worked end-to-end on the deduped 73-book library, the next phase is to host it at shelfiebook.com with multi-tenant accounts, real persistence, public sharing, and security worth trusting.

Stack

• Vercel for the Next.js app at shelfiebook.com. SSL, edge caching, atomic deploys, rollback.
• Supabase for data: Postgres + Row-Level Security + Auth + Storage + Realtime in one. RLS gives per-user isolation at the database, not the application -- the part that prevents "library got deleted" disasters.
• Cloudflare R2 for shelfie photos. Cheap, fast, no egress fees.
• Anthropic API unchanged.
• Local mirror. SHELFIE_DB_MODE=local keeps today's JSON-fixture path working so Edward can run a local copy that survives even if the hosted service ever goes away.

Schema

users                 -- Supabase Auth
libraries(id, user_id, name, is_public, slug, created_at)
photos(id, library_id, storage_path, shelf_label, scanned_at, ...)
books(id, library_id, normalized_key, title, author, isbn13, year,
       subjects[], cover_url, metadata_status, spine_confidence,
       read_status, rating, notes, lent_to, lent_at,
       override_origin, deleted_at, ...,
       UNIQUE(library_id, normalized_key))
book_photos(book_id, photo_id)
embeddings(book_id, voyage_v3 vector(1024))   -- pgvector
smart_shelves(id, library_id, name, query_json, position)

Row-Level Security

• libraries: select where user_id = auth.uid() OR is_public = true. Mutate only when user_id = auth.uid().
• All child tables: select where library_id IN (visible-libraries). Mutate only on owned libraries.
• A misconfigured client query cannot return another user's data. Postgres enforces it.

Security baseline

• HTTPS-only __Host--prefixed cookies.
• CSRF: bearer-token in cookie + same-origin check on mutating routes via Sec-Fetch-Site.
• CSP locked to self, allowed image hosts (covers.openlibrary.org, books.google.com, R2 bucket), nonce-based for inline.
• Rate limit at Vercel edge: 60 req/min/user mutating, 200 req/min/user reading.
• Soft-delete only: deleted_at columns, restore-from-trash UI.
• Daily Supabase Postgres snapshots retained 30 days. R2 versioning 14 days. Monthly restore drill in RUNBOOK.md.
• INFO log scrubber strips ISBN/title/author by default.

Migration path

1. lib/db.ts chokepoint. Behind SHELFIE_DB_MODE, every page reads through it. Today's JSON path stays; Supabase is the new branch.
2. Auth pages (/login, /signup), middleware gates everything except marketing//, /u/[username], /api/public/*.
3. First-login migration: read shelfie_overrides_v1 and shelfie_reading_v1 from localStorage, upload to the user's books rows, mark migrated.
4. Move photos into R2 bucket shelfie-photos/<library_id>/<photo_id>.jpeg. Signed URLs for private libraries; public-read for shared.
5. Vercel cron re-enriches metadata_status='unknown' books on a 6-hour cadence.
6. Backups + restore drill before public launch.

Public sharing

• is_public=true flips a library to read-only-shareable.
• URL: shelfiebook.com/u/<username> (default library) or shelfiebook.com/u/<username>/<slug> (named library).
• Statically rendered with ISR, 60-second revalidate. Searchable, filterable, no edit affordances.

Build sequence

The order leads with the data layer because everything else depends on it.

1. PLAN.md, git init, push to GitHub. Local copy stays canonical.
2. Supabase chokepoint at lib/db.ts. Dual-mode (local/supabase) so dev does not depend on a network.
3. Schema + RLS migrations (supabase/migrations/0001_init.sql).
4. Auth pages and middleware. Reserved usernames blocked.
5. localStorage → server migration on first login.
6. Real camera flow. /scan capture → /api/spines (now also emitting per-spine bbox [x,y,w,h]) → /scan/review/[id] (live, not fixture) → /api/enrich. Pixel-true highlight in the review modal.
7. Bundle release: smart shelves, lent-to, notes, wishlist, reading list. All read/written through lib/db.ts, all rendered through the existing LibraryBrowser filter pipeline.
8. Public profiles at /u/[username]. ISR.
9. UX modernization pass: type system (Fraunces+Inter), color tokens, dark mode, a11y baseline, keyboard shortcuts, URL-driven filters, toast+undo, mobile drawer, lucide icons.
10. Vercel deploy + shelfiebook.com domain attach + Supabase site_url.
11. Voyage embeddings into pgvector; "Books like this" on detail page.
12. Re-shelfie diff (added/removed/moved). /library/me Claude profile of subject distribution against an aggregate baseline.
13. Backups, soft-delete, restore drill in RUNBOOK.md.

Steps 1-5 unblock everything else and ship in the first sprint. 6-9 are the visible product. 10-13 are launch-grade polish.

Hardest problem

Spine reading at scale, with confidence the user can act on. A typical bookshelf photo has 25 to 50 books. Half the spines are partially obscured, tilted, or printed in decorative typography that confuses OCR. Claude vision is the best general option; the failure mode is the model confidently inventing plausible-sounding titles that don't exist.

The fix is twofold. First, structured output via tool use forces the model to attach a confidence to every entry, with explicit instructions that "I see text but can't resolve it" is an acceptable answer. Second, the enrichment pipeline only treats a spine as "known" once it matches a real OpenLibrary or Google Books record. Anything that doesn't match goes into the yellow or red queue for two-tap correction. The user's feedback teaches the system about edge fonts (dot-matrix Penguin Classics covers, decorative D&D hardcovers) and gradually tightens accuracy.

The green / yellow / red bands expose this cleanly. The user knows what to ignore and what to fix at a glance. Edward's two real shelves came back 30 green, 5 yellow, 2 red across 37 books, which is a representative ratio.

Risks

1. Cover-only books with no spine text. The photo of the front cover is the only signal. Mitigation: per-book "tap to retake" lets the user shoot the cover instead.
2. Cost. A house with 1500 books at ~50 spines per photo = 30 photos at maybe 2k tokens each. About $0.20 to shelfie a whole house. Real-world: maybe ten times that with retries. Still cheap.
3. Privacy. Photos of bookshelves leak political and religious info. Mitigation: an "encrypt at rest" mode that runs the embeddings client-side and stores everything in a local SQLite. Skip cloud sync entirely.
4. OpenLibrary blanks for niche books. Mitigation: Google Books is a strong fallback. If both fail, the book is still indexed with whatever Claude saw on the spine, flagged red until the user fixes it. Edward's TTRPG indie titles (Lairs & Legends, Secret Art series) hit this case in testing; the user-correction flow handled them in two taps each.

Out of scope (v1)

• Reading progress beyond simple status.
• E-book ingestion (this is about physical books on shelves; e-books are already cataloged by the device).
• Barcode scanning. Deliberately. The whole point is that you don't need it.

Post-launch roadmap (Phases 1-2 done, 2026-04-30)

Resume here on next session

When Edward says "read and execute PLAN.md," the next work is Phase 6 Session C (deploys / migrations / support / health / spines). Phases 1-5 and Phase 6 (Session A + Session B.1 + Session B.2) shipped to production on 2026-05-03. The Phase 6 design pre-flight (mockups/admin.html) is the source of truth for visual decisions -- do not relitigate them. Phase 7 (user-site UX review) follows Phase 6. The Bug queue (Graph view) and the soft-delete gate (block soft-deleted users from the user-facing app at the middleware layer) are open follow-ups -- ship Session C first unless Edward says otherwise.

Standing rules every session must follow (also in memory):

• Migrations run via npm run migrate against the production DB before the deploy that uses them.
• After every git push origin main, run vercel --prod (no GitHub auto-deploy is wired). The latest commit's git-author email must be eroske@interrel.com.
• AI / Claude / language-model branding stays out of user-facing copy. The privacy page's "third-party recognition service (Anthropic)" line is the only exception (legal disclosure).
• Tier names in user-facing copy: Reader / Bibliophile / Collector. Internal plan ids stay free / plus / pro.
• Every new feature must keep the existing UI as easy to use as before. Hide chrome from non-users; gate by plan and per-user opt-ins.
• Voice: short sentences, no em dashes, no "Not X. Y." constructions, no AI/buzzwords. Run the edward-voice-check skill on any prose users will read.

Phase 1 -- Multi-media classification (DONE, 2026-04-29)

books.media_type column. Spine reader classifies each item as book / boardgame / video_game / dvd / cd / vinyl / comic / other. LiveReview lets the user fix the type per spine. Library has a Type filter and a small uppercase pill on non-book grid cards.

Phase 2 -- Collector mode (DONE, 2026-04-30)

Migration 0008_collector.sql shipped: books.collector_meta jsonb, book_copies table with RLS, profiles.collector_mode boolean. Per-user Collector mode toggle on Settings, surfaced only on Pro (Collector tier). BookEditor reveals condition / edition / printing / signed / dust jacket / asking + acquired price / notes when collector mode is on. BookDetailView has an "I have another copy" panel that does CRUD against /api/books/[id]/copies. Library has "Multiple copies" and "Signed" chip filters, hidden unless collector mode is on. API: /api/books/[id] PATCH accepts collector_meta; /api/books/[id]/copies for copy CRUD. Both Pro-gated server-side.

Phase 3 -- Multi-media enrichment adapters (DONE, 2026-04-30)

lib/enrich-providers/ ships with five files: cache.ts (24h in-process Map cache + ProviderResult shape), bgg.ts (BoardGameGeek XML2, keyless), discogs.ts (vinyl + CD with format filter), igdb.ts (Twitch OAuth + IGDB v4, token cached in module memory), tmdb.ts (movies + TV, picks the higher-popularity hit, genre IDs translated via cached lookup tables), index.ts (lookupByMediaType dispatcher + liveProviders() health check). lib/enrich.ts exposes lookupBook(title, author) for the OL/GB fallback; enrichSpine now branches by media_type and short-circuits non-book/non-comic when nonBookEnabled is false. /api/suggest accepts media_type= query param, gates non-book/non-comic suggestions behind currentPlan() !== 'free', returns empty for free users on non-book queries (no error). LiveReview.enrichInBackground threads media_type per spine. Missing keys silently degrade to metadata_status='unknown'. Env vars for production: DISCOGS_TOKEN, IGDB_CLIENT_ID, IGDB_CLIENT_SECRET, TMDB_API_KEY -- need provisioning by Edward; until set, only OpenLibrary + BGG run live.

Phase 3 (original brief, kept for reference)

Phase 1 classifies items but only books get covers and metadata fetched. Phase 3 adds per-type providers so a board game, vinyl, video game, or DVD picks up a real cover and subjects.

Adapters (one file each, all exporting the same shape):

• lib/enrich-providers/bgg.ts -- BoardGameGeek XML2 API at boardgamegeek.com/xmlapi2/search?type=boardgame&query=... then /thing?id=.... Keyless. Boardgame.
• lib/enrich-providers/discogs.ts -- Discogs at api.discogs.com/database/search?type=release. Needs DISCOGS_TOKEN. Vinyl + CD.
• lib/enrich-providers/igdb.ts -- IGDB at api.igdb.com/v4/games. Twitch OAuth: needs IGDB_CLIENT_ID + IGDB_CLIENT_SECRET, exchange for a bearer token. Video game.
• lib/enrich-providers/tmdb.ts -- TMDB at api.themoviedb.org/3/search/movie and /search/tv. Needs TMDB_API_KEY. DVD / Blu-ray.
• lib/enrich-providers/index.ts -- exports lookupByMediaType(mediaType, title, author): Promise<{ cover_url?, year?, subjects?, isbn? }> that dispatches to the right adapter, falls back to OpenLibrary/Google Books for book and comic, returns null for other.

Each adapter exports the same signature: lookup(title: string, author: string): Promise<{ cover_url?: string; year?: number; subjects?: string[]; isbn?: string } | null>. Errors return null and log; never throw to callers.

Wiring:

• /api/suggest (used by BookEditor's auto-fill search) branches on media_type from the request body and routes to the right adapter.
• LiveReview enrichment loop already calls /api/suggest per spine; thread media_type through so each spine looks up its own provider.
• lib/enrich.ts (the bulk-enrich path used by /api/scans and re-shelfie) gets the same media-type dispatch.
• Free tier stays book-only. Bibliophile/Collector tiers unlock the non-book providers. Plan check goes in /api/suggest and in the enrich loop (silently skip non-book items for free users -- don't error).

Env vars to add via vercel env add ... production:

DISCOGS_TOKEN
IGDB_CLIENT_ID
IGDB_CLIENT_SECRET
TMDB_API_KEY

If a key is missing in production, the adapter returns null and the item stays at metadata_status='unknown' rather than failing the request. Add a one-line health check in the README's Status section listing which providers are live.

Caching: wrap each adapter's HTTP call in the existing 24-hour fetch cache (lib/enrich.ts has the helper). Identical title+author lookups should not re-hit the upstream API.

Out of scope for Phase 3: import from collector services (LibraryThing, BoxedCollector). Cross-provider deduplication (a vinyl on Discogs that's also a CD on Discogs is two rows). Audio fingerprinting.

Phase 4 -- Shared libraries (TWO sessions)

Edward's policy: every member must have a paid tier; quota is the largest member's quota (not summed); a "Household Library" tier comes later.

Session A -- schema + queries (DONE, 2026-05-02)

Migration 0009_sharing.sql shipped: library_members(library_id, user_id, role check in ('owner','editor'), joined_at), book_user_state(book_id, user_id, read_status, rating, notes, lent_to, lent_at, wishlist), owner backfill, RLS helpers rewritten (visible_library_ids / owned_library_ids pivot through library_members; new admin_library_ids for owner-only ops), on_library_created trigger seeds the owner row, libraries_update/libraries_delete policies now gate on admin_library_ids. Server pivot done in lib/current-library.ts, lib/api-key.ts, app/api/books/route.ts, app/api/scans/route.ts, app/api/demo-shelfie/route.ts, app/api/migrate-local/route.ts. app/api/books/[id] PATCH splits per-user fields (read_status, rating, notes, lent_to, lent_at, wishlist) into book_user_state upserts; shared fields stay on books. app/api/user-state GET returns the user's state; components/UserStateHydrator.tsx (mounted in Providers) seeds shelfie_reading_v1 and shelfie_book_meta_v1 localStorage stores from server truth on first load so reading hooks reflect book_user_state. A follow-up migration drops the moved columns from books after Session B ships.

Session B -- invite flow + plan policy (DONE, 2026-05-02)

Migration 0010_sharing_invites.sql shipped: library_invites(id, library_id, email, role, token, invited_by, invited_at, expires_at, accepted_at, accepted_by) with a partial unique index on (library_id, lower(email)) WHERE accepted_at IS NULL and admin-only RLS. lib/sharing.ts exposes mintInviteToken, memberPlans, effectiveQuota, checkWriteAllowance (shared library writes blocked when no member is paid; insert blocked over max(member.books_quota)), and sendInviteEmail (Resend, no-op without RESEND_API_KEY). Routes: /api/sharing GET (members + pending invites + quota) and POST (mint token, email link), /api/sharing/invites/[id] DELETE (revoke), /api/sharing/members/[userId] DELETE (remove or self-leave with last-owner protection), /api/sharing/accept POST (consume token + add member with paid-member check). /auth/accept-share?token=... page handles the redirect-through-login flow via the existing magic-link ?next= plumbing. SettingsForm gained a Sharing section listing members, pending invites, role + email invite form (gated on Bibliophile/Collector). /api/quota and QuotaIndicator now report shared quota with a "shared · upgrade required" pill when no member is paid. /api/books and /api/scans call checkWriteAllowance before insert. Settings page reads libraries through library_members so shared libraries appear there too. Env: RESEND_API_KEY, RESEND_FROM_EMAIL, NEXT_PUBLIC_SITE_URL already provisioned (used by other email flows).

Phase 5 -- Marketing landing pages (DONE, 2026-05-03)

Four audience pages live: /for/board-gamers, /for/collectors, /for/audiophiles, /for/film-collectors. Single dynamic route at app/for/[audience]/page.tsx with generateStaticParams for SSG and per-audience generateMetadata (title, description, canonical, OpenGraph, Twitter card). Audience copy + cover walls live in lib/audiences.ts (one record per audience: pageTitle, pageDescription, hero h1/sub, three stats, three steps, pull-quote with bullets, five FAQs, twelve cover-wall items). Layout reuses the homepage hero + 3-step + pricing + final CTA shape. Voice pass applied: every em-dash-shaped -- removed from rendered prose, parenthetical asides added per section, "Short answer: yes/no" in FAQs, longer hero subs tightened. Sitemap added at app/sitemap.ts (homepage, marketing pages, the four audience pages, with audience pages at priority 0.8). Homepage gets a four-pill discoverability strip ("Not just books") above the footer; footer nav lists all four audience pages on every audience page and on home.

Phase 6 -- Admin interface

This is for Edward (and any future co-admins), not end users. The goal is to keep Shelfie supportable: see what is happening, find a user fast, fix data when something breaks, and watch the deploy/release stream without leaving the app.

Scope priority. Build the dashboard and the user table first; everything else slots in after. The whole admin lives at /admin with a sidebar and is gated by an is_admin flag on profiles. No mention of "admin" appears anywhere in the user-facing UI.

Access control

• Migration 0011_admin.sql:
  • alter table profiles add column is_admin boolean not null default false.
  • alter table profiles add column admin_granted_by uuid references auth.users(id).
  • alter table profiles add column admin_granted_at timestamptz.
  • One-time backfill: update profiles set is_admin = true where id in (select id from auth.users where email = 'edward@dawnwardpr.com'). Wrapped in a do $$ ... $$ block so re-runs are safe.
  • admin_audit_log(id, actor_user_id, action, target_type, target_id, payload jsonb, created_at). Every grant, revoke, refund, soft-delete reversal, and impersonation event writes a row. Append-only RLS (admins read; service-role only writes).
  • RLS helper is_admin() returns exists(select 1 from profiles where id = auth.uid() and is_admin).
• lib/admin.ts:
  • requireAdmin() server helper: throws redirect to /library for non-admins. Used at the top of every admin server component and every /api/admin/* route. Belt and suspenders alongside RLS.
  • logAdminAction({ action, target, payload }) writes one admin_audit_log row.
• Middleware: /admin/* paths short-circuit with a 404 (not 403) for non-admins so the surface is invisible to crawlers and curious users.
• Anything destructive ships behind a typed-confirmation dialog ("type DELETE to confirm"). Aligns with the "carefully consider blast radius" rule.

/admin -- dashboard (the at-a-glance home)

A single dense page that is the right answer to "is anything weird?" at a glance.

• KPI strip across the top, cached for 60 seconds:
  • Total users (and breakdown by Reader / Bibliophile / Collector).
  • Trial-paid conversions in the last 7/30/90 days.
  • MRR + new MRR + churned MRR (pulled from Stripe; we already store stripe_subscription_id).
  • Books shelfied today / this week / this month.
  • Active users in the last 24 hours / 7 days / 30 days.
• Time-series charts (recharts; we don't have a chart lib yet, add it):
  • Signups by day, last 90 days, stacked by plan.
  • Books shelfied by day.
  • MRR by day.
• "Things that need attention" panel:
  • Spine reads with confidence < 0.4 in the last 24h (model regressions surface here).
  • Failed Stripe webhooks.
  • Support tickets unanswered > 24h.
  • Quota-exceeded write rejections in the last 7d (sales signal).
• Recent deploys list (see deploy log section below) on the right rail.

Design pre-flight (done, 2026-05-03). Mockup at mockups/admin.html covers the Dashboard, Users table, and single-user drill-in. Reviewed via the frontend-design skill against Stripe / Linear / Vercel. The findings below are baked into the spec; do not reopen them in implementation.

/admin/users -- the user table

The core support tool. One row per user, server-rendered, sortable, filterable, paginated. Columns:

• Email (with copy-on-click).
• Username + display name.
• Signup date.
• Days active (now() - first signup).
• Plan (badge).
• Subscription status (active / past_due / canceled).
• Last activity (max of last book add, last login, last quota check).
• Items shelfied (books count across libraries the user is a member of).
• Library count.
• Storage used (Supabase storage bytes; later, R2).
• Country (from the existing geo route's IP lookup at signup, if available).
• Actions menu.

Filters above the table:

• Plan (multi-select).
• Subscription status.
• Activity bucket (active 7d, 30d, dormant 90d+, churned).
• Has issues (failed payments, exceeded quota, no shelfies).
• Date range on signup.

Search is one input across email + username + display_name + library name. Hits Supabase via a single SQL function with pg_trgm for fuzzy matching.

Table-level affordances (all decided in pre-flight):

• Right-align numeric columns (text-right tabular-nums) consistently. This is the default for any column whose values are numbers.
• Whole-row click navigates to the drill-in. The ⋯ action menu calls stopPropagation. Hover reveals a › chevron at the right edge so the click target is signaled.
• Sparklines are 16-20px tall, 1px bars with 2px gaps. Hover the row highlights the spike.
• Avatar tinted by plan (Reader = stone, Bibliophile = amber, Collector = ink) so the table reads by tier even before the Plan column.
• Search input has live fuzzy typeahead -- typing reveals a dropdown of matches with email + plan + last-active. Saves a click on the most-common path.
• No traditional pagination. Virtualize the row list with infinite scroll. The footer shows "Showing N of 1,287" and a small page jumper, but the primary motion is search + scroll.
• Bulk action bar appears as a sticky footer when any rows are selected ("3 selected · Email · Export · Tag · Cancel").
• Saved views as URL. Filter/search state is in searchParams; "Save view" pins the current URL with a name into a per-admin admin_saved_views table.
• Filter tabs above the table are plain text by default and only show a chip on the active filter. Reduces visual weight when not engaged.

Click a row to drill into /admin/users/[id]:

• All the column data.
• All libraries the user is a member of, with role and shelfie counts.
• Stripe link (stripe_customer_id -> dashboard.stripe.com deep-link, opens in new tab).
• Recent activity feed (last 50 events: signups, shelfies, plan changes, support tickets, login attempts).
• Action surface is weighted by blast radius:
  • "Send magic link" (Supabase admin API) is the primary button. Solves 60% of support without any audit trail risk.
  • "Sign in as user" is a ghost button that opens a typed-confirmation modal ("type IMPERSONATE to continue"). On accept: mints a short-lived service-role session, sets a cookie, and the user-facing app shows a screen-spanning red banner ("You are signed in as @kjell · End session") on every page until the session is ended or expires. Logged to audit.
  • "Grant 30-day Bibliophile" is a ghost button. Overrides books_quota and plan; expiry handled by the cron/drip cron. Logged to audit.
  • "Refund last invoice" gets the danger treatment (rose border, rose text). Stripe API call. Typed confirmation. Logged.
  • "Soft-delete account" gets the danger treatment, typed confirmation, fires an undo toast.
  • "Restore account" un-soft-deletes (no confirmation; already a recovery action).
  • "Promote to admin" / "Revoke admin" only visible to existing admins; writes audit.
• Internal notes (cross-admin notes, audit-adjacent). Free-text per user. Not visible to the user. Used for "do not auto-suspend, partner inquiry" type context.

/admin/admins -- admin roster

• Table of profiles where is_admin = true: email, username, granted_by, granted_at.
• "Add admin" by email. Looks up the profile by email and flips the bit. Writes to audit.
• "Revoke" beside every row except the requester's own (no foot-gun).
• Edward seeded at edward@dawnwardpr.com via the migration backfill.

/admin/deploys -- release log

The "what just changed?" feed. Pulled live from Vercel + git so Edward can see, on one page, what shipped and when.

• List view: SHA short, commit subject, author email, deploy state (queued / building / ready / error), preview URL, production URL, started_at, ready_at, duration. Newest first.
• Filter: production-only by default; toggle to include preview deploys.
• Click a deploy: full commit body, files changed (from git show --stat via a serverless route that reads the bare repo through git ls-remote), the deploys before and after for diff, build logs link.
• Source: vercel.com/api/v1/deployments?projectId=...&limit=50 proxied through /api/admin/deploys so the Vercel access token never reaches the browser. Fallback: git log --pretty=format:'%H|%an|%ae|%at|%s' -50 if Vercel API is unreachable.
• "Open in Vercel" button on every row. We do not try to recreate the Vercel UI.

/admin/migrations -- schema log

• Table of supabase/migrations/*.sql files, status (applied / pending), applied_at (read from a small migrations log table the existing npm run migrate script writes), and SHA of the file.
• Quick-glance health: red banner if any production migration is missing.

/admin/support -- inbox

• Reads support_tickets (already exists). Columns: status (open/closed), email, subject, body excerpt, age, user link.
• "Mark resolved" closes the ticket. "Reply" opens a mailto link with a templated body.
• Bulk close + label later.

/admin/health -- ops checks

Single page that runs the existing verify-db / smoke-db checks on demand and reports pass/fail with timing. Add a "Provider status" tile that runs the liveProviders() health check from lib/enrich-providers/index.ts so Edward can see at a glance whether DISCOGS / IGDB / TMDB are responding.

/admin/spines -- model performance

For tuning the spine reader. Only visible to admins.

• Aggregate confidence histogram over the last 30 days.
• Worst 100 reads (lowest confidence) with thumbnail and the user's correction (if any). Click to flag for the next eval set.
• Trend line of "% green band" across deploys, so we know when a model upgrade regresses accuracy.

/admin/audit -- audit log

Read-only table of admin_audit_log, sortable by actor, action, time. Filter by actor or by target user. Every destructive admin action lands here.

Design system for the admin (decisions from pre-flight)

The admin uses the same warm-stone + amber palette as the user app, but commits more aggressively. Defaults to dial in on day one:

• Type altitudes (three, not two). Page heading: Fraunces 32px / 600. Section heading: Fraunces 16px / 600 (currently sans -- promote it). Body and table: Inter 13-14px / 400-500. KPI numerals: Fraunces 32px / 600 with font-feature-settings: "tnum","ss01" and font-variant-numeric: tabular-nums.
• Mono for machine data, sans for human data. JetBrains Mono on user IDs, Stripe IDs, library IDs, audit-log refs, and precise timestamps. Inter on relative times like "38s ago" and human names. Apply the rule consistently.
• Color and weighting.
  • Amber is diagnostic, not decorative. Use it on the active sidebar item underline, the MRR KPI value, and the active filter chip. Nowhere else.
  • One inverted region: the sidebar runs full height in deep ink (#22201d). Breaks the whitewash and gives the eye relief.
  • Status pills keep their soft fills but the inner dot grows to 6px so it actually reads.
• Cards aren't the only structural element. The KPI strip is bare (no card walls, just a bottom hairline). The "Things that need attention" panel uses left-edge color bars (severity = bar color and width) instead of inline pills. Mix card / no-card / inverted regions on every screen.
• Dark mode is mandatory at launch, not retrofit. Background #1a1716, surface #22201d, ink #f5f4f1, amber unchanged. Build both palettes in the same Tailwind config; never ship the admin without it.
• Iconography: Lucide only. Already in deps. The mockup's geometric placeholder symbols (◉ ◎ ▦ etc.) are placeholders -- replace with Lucide. If a label is clear without an icon, drop the icon.
• Shelfie-shaped flourishes that tie the admin to the product:
  • Stylized book-spine SVG as the Libraries nav icon (one custom SVG, kept in components/admin/icons.tsx).
  • Hairline rules under page headings shaped like a bookshelf rule (a 1px line with two 2px verticals at each end -- subtle, not cartoonish).
  • Covers-as-avatars on the libraries panel of the user drill-in (show the top 4 covers in a tiny grid as the visual identity for that library).
• Voice carries through. Section labels mirror the user-app voice: "What just shipped" not "Recent deploys"; "Five things to look at" not "Things that need attention"; time-of-day-aware greeting ("Sunday afternoon", "Monday morning") on the dashboard.

UX principles for the admin

• One layout: inverted left sidebar (sections), top bar (search + admin avatar), main content. Same Tailwind tokens as the user app, extended with admin-specific surface variables.
• Keyboard-first. ⌘K opens a real command palette with admin commands -- "find user by email", "open last deploy", "grant 30 days to ...", "go to audit log filtered by me", "switch to dark mode". ? opens a shortcuts overlay listing every binding. j/k move row selection in tables; enter drills in; esc returns; g d for dashboard, g u for users, g a for audit.
• Undo on every destructive action. Toast appears in the bottom-right with a 10-second window: "Refunded $4 to @kjell · Undo." Beyond 10s, the action is committed and only the audit log can show what happened.
• Recent items / pinned users. A small "Recent" section in the sidebar persists the last five users the admin viewed across sessions. Pin a user with p while viewing them.
• Server-rendered tables with searchParams-driven filters so every view URL is shareable with co-admins.
• Every destructive action has a typed-confirmation modal AND fires an undo toast AND writes audit before executing.
• Default to read mode. The admin should feel like Linear or Stripe Dashboard: dense, calm, never one click from a regret.
• Mobile-passable but desktop-first. The admin is a desk job. Tablet should work; phone is acknowledged-broken.

Required loading, empty, and edge-case states

Every screen ships with these states designed (not "we'll add them later"):

• Skeleton loading on the KPI strip, charts, user table, drill-in. Skeleton bones tinted to match section, animated with a subtle shimmer.
• Empty users table (search returns nothing; first-day install).
• A user with zero libraries in the drill-in.
• A user with 50+ libraries (drill-in collapses; Show all toggle).
• A user mid-soft-delete: a warm-amber banner across the drill-in -- "Soft-deleted 2026-05-01 by edward@dawnwardpr.com. Auto-purges 2026-06-01. Restore?"
• Active impersonation banner: a screen-spanning red bar in the user-facing app (not just admin) on every page. "You are signed in as @kjell · End session". Persistent until end-session is clicked or session expires.
• A failed Stripe webhook detail (deep-link from the attention panel into a single-event view).
• Migration drift: a red banner across the admin shell (not just the migrations page) when production schema does not match the latest committed migration. Links to the diff.
• An admin with no MRR yet (early days): "Awaiting first paid subscriber" beats "$0".
• Dark and light for every screen, no exceptions.

Things that need to exist that the mockup did not show

Tracked here so they ship in the right session, not retrofit later.

• Impersonation banner in the user-facing app (Session B prereq).
• Command palette with admin commands (Session A foundation, populated as routes ship).
• Keyboard shortcuts overlay (? to open) (Session A).
• Global undo toast helper (lib/admin-undo.ts) (Session A).
• Saved filter views (Session B, with admin_saved_views table added in 0011).
• Internal notes on users (Session B; admin_user_notes(user_id, author_id, body, created_at)).
• Cohort comparison toggle on charts -- "this 30d vs prior 30d" overlay (Session A polish).
• Provider rate-limit visibility on the health page -- show remaining quota for Discogs / IGDB / TMDB and a 7d trend (Session C).
• Migration diff view: SQL preview for each migration, not just status (Session C).
• Audit log deeplinks: every row resolves to its target (user, deploy, invite) (Session A).
• "Five things to look at" weekly digest on the dashboard, generated by a Sunday-morning cron (Session A polish).

Build sequence (sessions)

1. Session A -- foundations and the dashboard. (DONE, 2026-05-03) Migration 0011_admin.sql shipped (profiles.is_admin + admin_granted_by + admin_granted_at, admin_audit_log with append-only RLS, admin_saved_views, admin_user_notes, admin_pinned_users; Edward backfilled via a do-block). lib/admin.ts exposes requireAdmin() (notFound on non-admin, redirect on unauth), currentAdmin(), isAdminUser(), logAdminAction(), undoToken(). Middleware short-circuits /admin and /api/admin to /404 for non-admins. The shell at /admin ships with an inverted-ink left sidebar (#22201d), Lucide iconography, a custom shelfie-spine SVG, dark + light parity, and a topbar that opens cmdk. The dashboard renders a bare KPI strip (total users, signups 7d, books today/week, active 24h/7d, MRR placeholder), plan-mix bar (Reader/Bibliophile/Collector), "Five things to look at" with severity-coded left-edge bars, two recharts time series (signups area, books bars over 30d), a "What just shipped" rail mixing audit + recent shelfies, and a time-of-day greeting. /admin/admins lists is_admin profiles, supports add-by-email + revoke (typed dialog + undo toast). /api/admin/admins POST + DELETE write audit. /admin/audit is the read-only log with searchParams-driven actor + action filters and target deeplinks. Command palette (cmdk), ? shortcuts overlay, and g d / g a / g u / g m sequential shortcuts wired in AdminShell. Recharts added to deps. Bumped to 0.7.0.
2. Session B -- user table and drill-in. Split into B.1 + B.2 because the action surface deserves its own review.
   • Session B.1 (DONE, 2026-05-03). Read-only user surfaces. /admin/users ships with lib/admin-users.ts driving the queries (profile join with auth-admin emails, books_count + library_count + last_active aggregated from library_members + books, 7-day spark per user). The page renders with searchParams-driven plan / activity-bucket filters, plain-text filter tabs (UsersFilterTabs), live fuzzy typeahead (UserSearchInput -> /api/admin/users/search), virtualized first-page server render plus IntersectionObserver-driven infinite scroll fetching /api/admin/users, plan-tinted avatars (PlanAvatar: stone / amber / ink for Reader / Bibliophile / Collector), PlanPill, right-aligned numerics (tabular-nums), inline-SVG sparklines (Sparkline), j/k row navigation with row-into-view scroll, x to toggle select, sticky bulk action bar (counts + disabled Email/Export/Tag for B.2), saved views (SavedViewsBar -> /api/admin/saved-views, backed by admin_saved_views). /admin/users/[id] drill-in shows the same row data large, KeyStats bar, libraries grid with covers-as-avatars (LibraryCard, top 4 covers in 2x2), activity timeline (ActivityTimeline: books + audit + invites + memberships), internal notes (UserNotes -> /api/admin/users/[id]/notes, audit-logged), Stripe deeplink, and a stubbed action surface (UserActions) showing the B.2 menu disabled. Sidebar Users row enabled. Command palette + g u shortcut + ? overlay updated. Bumped to 0.8.0.
   • Session B.2 (DONE, 2026-05-03). Migration 0012_admin_actions.sql shipped: profiles.soft_deleted_at + soft_deleted_by, plan_grant_expires_at + plan_grant_original_plan + plan_grant_original_quota + plan_granted_by, and the admin_impersonations audit table (admin-readable, service-role write only). lib/impersonate.ts mints + verifies an HMAC-signed cookie payload ({adminId, targetId, exp}, 1h TTL) keyed off IMPERSONATION_SECRET (or CRON_SECRET / service role as fallback). <ImpersonationBanner /> server component mounted in the root layout shows a sticky rose-600 bar across every user-facing page with End session (<EndImpersonationButton /> -> DELETE /api/admin/impersonate). Action surface lives in <UserActions user={user} /> (rewritten from B.1 stub): magic-link (clipboard copy of the generated auth.admin.generateLink action_link), 30-day Bibliophile grant (snapshots original plan + quota; reverts via Settings or cron), revert-grant, refund-last-invoice (Stripe charges.list + refunds.create), soft-delete + restore, promote / revoke admin. Destructive actions go through <TypedConfirmDialog> (IMPERSONATE / REFUND / DELETE phrases). One unified /api/admin/users/[id]/actions route dispatches each action; every successful action writes admin_audit_log. The drill-in surfaces soft-delete + grant banners in the page header. The drip cron (/api/cron/drip) gained a revertExpiredGrants pre-pass so granted plans auto-revert. Bumped to 0.9.0. New env var to set in production for cookie HMAC stability across deploys: IMPERSONATION_SECRET (random 32-byte hex). Falls back to CRON_SECRET / service-role key, but a dedicated value is cleaner.
3. Session C -- secondary tools.
   • /admin/deploys (Vercel API proxy + git fallback, click-through diff).
   • /admin/migrations (status + SQL diff view, drift banner).
   • /admin/support (inbox, mark-resolved, mailto reply).
   • /admin/health (verify-db, smoke-db, providers + rate-limit trends).
   • /admin/spines (confidence histogram, worst 100, % green trend across deploys).
   • Weekly "Five things" digest cron.

Skip nothing in Session A: foundations, palette, dark mode, command palette, undo, skeleton states, audit. Sessions B and C can interleave with other work.

Out of scope (v1)

• Multi-tenant admin (one Shelfie instance, one admin pool).
• A/B test orchestration.
• A/B feature-flag UI (env-var based for now).
• Full email composer. Mailto opens the user's mail client; Resend handles transactional from elsewhere.

Phase 7 -- User-site UX review

Once the admin ships and stabilizes, take the same frontend-design skill that critiqued the admin and turn it on the rest of the app: home, marketing pages, library views, scan flow, review screen, settings. The admin pre-flight forced a design conversation that improved the spec; the user-facing app deserves the same audit before we start swinging at "more features."

Out-of-scope for Phase 7: rewriting the brand. The voice, palette, and Fraunces-Inter pair stay. This phase polishes the existing surface, doesn't restart it.

Step 1 -- Capture state (one session)

• Build a mockups/user-app-current.html snapshot of every primary surface: /, /library (Covers / Spines / Stacks / Table), /library/[id], /library/subjects, /library/gaps, /library/stats, /scan, /scan/review/[id], /settings, /u/[username], plus the four /for/<audience> pages. Each one is a single self-contained page so the design skill can walk them in a browser.
• For dynamic surfaces (review screen, scan), screenshot the live app on Edward's real shelf data and embed the images in the mockup.
• Light + dark for every screen.

Step 2 -- Critique pass

• Run frontend-design against the mockups with the same rubric used on the admin: information density and hierarchy, typography pairing, color and tonal range, data craft (table + cards + cover wall), interaction surface, missing states, mobile behavior, places that look like a generic Tailwind template instead of Shelfie.
• Capture the punch-list as docs/user-ux-punchlist.md with priority tiers (must-fix, polish, defer).

Step 3 -- Implementation passes

Each of these is its own session. Do not bundle.

• Pass A -- structural. Things that change layout or component shape: hierarchy fixes, density adjustments, mobile re-flow, drawer / sheet patterns, missing empty / loading / error states, accessibility baseline (focus rings, aria, keyboard navigation across every interactive element).
• Pass B -- typographic. Three altitudes of type, mono-vs-sans rules, OpenType features (tnum, ss01, cv11), heading rhythm, line-length caps, optical sizing on Fraunces.
• Pass C -- color and surface. Dark mode parity (Shelfie has half-baked dark today), amber as a diagnostic accent rather than decoration, inverted regions where they earn their keep, status pill consistency, hover and pressed states on every interactive surface.
• Pass D -- motion and delight. Transitions on view-toggle, cover-card hover treatment, scroll-triggered reveals on marketing pages (subtle), the shelfie! button moment when a scan completes (one big satisfying animation; product-defining), toast and undo patterns. Use Motion library if React-side animation gets complex.
• Pass E -- accessibility audit. Real audit, not a checklist: keyboard navigation on every page, color contrast at AA minimum, screen reader pass on /library and /scan/review, focus management in modals and palettes, reduced-motion support.

Step 4 -- Regression catch

After each pass: re-screenshot the surfaces, diff against the captured baseline, run npm run typecheck and the existing smoke tests. The user-site review must not break the existing UI -- the keep-UI-simple rule still applies.

Out of scope (Phase 7)

• New features. This is a polish phase only. New features go through their own design pre-flight when they ship.
• Brand redesign. The voice, palette, and font pair stay.
• A/B testing the changes. Edward's call on what lands.

Bug queue

Graph view renders blank

/library/graph shows a black panel with no nodes. Cause: components/LibraryGraph.tsx calls the v1 Cosmograph API (new Cosmograph(container, config)) but we're on @cosmograph/cosmograph ^2.3.0, which moved to new Cosmograph({ container, ...config }) and renamed .remove() to .destroy(). The onClick handler signature also changed.

Fix steps:

1. Read the v2.3 docs to confirm the constructor shape, the data-setter (setData vs setConfig({ data })), and the click-event signature.
2. Rewrite components/LibraryGraph.tsx to match: container goes inside the config object; replace graph.remove() with graph.destroy(); update onClick call.
3. Verify in the browser at /library/graph -- nodes visible, click selects a book, the right-hand detail panel populates.
4. If the v2 API is materially different (e.g. requires a separate React wrapper), consider switching to @cosmograph/react instead of the vanilla package.

Until fixed, the link stays in the More menu and on the book-detail "View as graph" button -- it does not return errors, just renders empty.