Book Ingest

Book Ingest — Phase 03: MDX → Supabase

Purpose

Land the validated corpus in Supabase so the e-reader, commerce flow, and pgvector RAG can serve from a single source of truth.

Schema (Drizzle, src/lib/database/schema/books.ts):

• books — one row per book per language; embeds manifest (full book.json snapshot), commerce fields, reader defaults, RAG config.
• book_chapters — one row per chapter; mdx_content text, content_sha256 for drift detection, is_preview for paywalled chapters, generated fts tsvector.
• book_sections — display anchors (stable IDs the e-reader bookmarks against). Re-embedding does NOT regenerate sections.
• book_chunks — RAG retrieval units (regenerable). HNSW pgvector index on embedding (cosine, m=16, ef_construction=64).
• user_book_purchases | progress | bookmarks | highlights — user-scoped tables, RLS-protected.

Invocation

$ARGUMENTS:

• <book-slug-or-dir> — Required.
• --dry-run — Print SQL without executing.
• --force-rechunk — Force chunk regeneration even if content_sha256 unchanged.
• --tenant-org-id <uuid> — Override process.env.TENANT_ORG_ID. Default: Alan Hirsch tenant 6bc0fcf7-2e55-4914-b88d-c6eb49eb0d71.

Process

Step 1 — Pre-flight

1. Verify Supabase env vars: DATABASE_URL, SUPABASE_SERVICE_ROLE_KEY, TENANT_ORG_ID.
2. Verify the book has passed /book-frontmatter (look for <book-dir>/.ingest/frontmatter-*.json with verdict: "PASS").
3. If no .ingest/ dir exists, run /book-frontmatter first.

Step 2 — Upsert the book row

const manifest = BookManifest.parse(JSON.parse(await Bun.file(`${bookDir}/<book-slug>-book.json`).text()));

const [{ id: bookId }] = await db.insert(books).values({
  organization_id: process.env.TENANT_ORG_ID!,
  slug: manifest.slug,
  title: manifest.title,
  author: manifest.author,
  language: manifest.language,
  manifest,
  version: manifest.version,
  // commerce, reader, rag fields from manifest
}).onConflictDoUpdate({
  target: books.slug,
  set: { manifest, updated_at: new Date() },
}).returning();

Step 3 — Walk chapters, hash, upsert

For each <book-slug>-chNN-*.md file:

1. Parse frontmatter and body.
2. Validate frontmatter against ChapterFrontmatter Zod schema.
3. Compute content_sha256 of body (post-frontmatter).
4. Insert / update book_chapters row.
5. Capture the previous content_sha256 from the RETURNING clause to detect changes.

const [{ id: chapterId, content_sha256: prev }] = await db.insert(bookChapters).values({
  book_id: bookId,
  chapter_number: fm.chapter_number,
  slug: fm.chapter_slug,
  title: fm.chapter_title,
  summary: fm.summary,
  word_count: fm.word_count,
  reading_time_minutes: fm.estimated_reading_time,
  page_range_start: fm.page_range_start,
  page_range_end: fm.page_range_end,
  content_sha256: fm.content_sha256,
  mdx_content: content,
  is_preview: manifest.commerce.preview_chapters.includes(fm.chapter_number),
  metadata: {
    key_concepts: fm.key_concepts,
    themes: fm.secondary_topics,
    citations: fm.citations,
    primary_topic: fm.primary_topic,
    mentions_scriptures: fm.mentions_scriptures,
    mentions_figures: fm.mentions_figures,
  },
}).onConflictDoUpdate({
  target: [bookChapters.book_id, bookChapters.chapter_number],
  set: { mdx_content: content, content_sha256: fm.content_sha256, updated_at: new Date() },
}).returning();

Step 4 — Sections (only if content changed)

If prev !== fm.content_sha256 (or --force-rechunk):

1. Delete existing book_sections for this chapter (cascade-safe via FK).
2. Walk the chapter's MD using remark to extract every heading and produce stable anchor IDs.
3. Insert one book_sections row per heading with ordinal, heading_level, anchor, text, word_count.

import { remark } from "remark";
import { visit } from "unist-util-visit";

function sectionize(content: string, chapterId: string) {
  const sections: any[] = [];
  let ordinal = 0;
  remark().use(() => (tree) => {
    visit(tree, "heading", (node, _index, _parent) => {
      const text = (node.children as any[])
        .map((c) => c.value || "")
        .join("");
      const anchor = text
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, "-")
        .replace(/^-|-$/g, "");
      sections.push({
        chapter_id: chapterId,
        ordinal: ordinal++,
        heading_level: node.depth,
        anchor: `#${anchor}`,
        text,
      });
    });
  }).processSync(content);
  return sections;
}

Step 5 — Hand off to chunking + embedding

If sections changed, do not chunk + embed inline. Hand off to /book-chunk so chunking is a separate, retryable step:

/book-chunk corpus/alan_hirsch/<book-slug>/

This generates <book-dir>/.ingest/chunks.jsonl, then /book-rag-push uploads to providers and inserts into book_chunks.

Step 6 — Report

Phase 03 — Supabase ingest
  Book row:           inserted | updated   (id: <uuid>)
  Chapters upserted:  18 / 18
  Chapters changed:   3 (Ch.1, Ch.4, Notes)
  Sections regenerated: 3 chapters / 47 sections
  Skipped chunks:     existing pgvector entries kept (sha unchanged)

  Verdict: PASS — advance to /book-chunk for changed chapters

RLS Policy Reminder

When inserting, ensure the service-role key bypasses RLS. The runtime read path uses these policies:

-- Anyone can read preview chapters; purchasers can read all chapters of bought books.
create policy book_chapters_read on book_chapters
  for select using (
    is_preview or exists (
      select 1 from user_book_purchases p
      where p.user_id = auth.uid()
        and p.book_id = book_chapters.book_id
        and p.refunded_at is null
    )
  );

-- User-scoped tables — owner only.
create policy user_book_progress_owner on user_book_progress
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);

Verify these are deployed before exposing the e-reader.

Out of Scope

• Generating chunks / embeddings — /book-chunk.
• Pushing to OpenAI / Gemini / Claude — /book-rag-push.
• Stripe product / price provisioning — separate billing setup task.

References

• docs/html/books-pipeline.html#phase-3
• scripts/lib/frontmatter-schema.ts
• Drizzle schema: see src/lib/database/schema/books.ts in the consuming app