Book Database Appendices

A. ZModel Schemas (camelCase, pgvector)

enum TaxonRole {
  ISSUE
  ASPIRATION
  PRACTICE
}

model Book {
  id               String   @id @default(cuid())
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt
  active           Boolean  @default(true)

  title            String
  subtitle         String?
  authors          String[] @default([])
  publisher        String?
  publishedYear    Int?
  isbn10           String?
  isbn13           String?
  language         String   @default("en")
  description      String?
  purchaseLinks    Json
  rating           Float?
  reviewsCount     Int?
  tags             String[] @default([])

  // Embeddings (pgvector)
  titleEmbedding   Unsupported("vector(1536)")
  summaryEmbedding Unsupported("vector(1536)")

  taxons           BookTaxonomy[]

  @@index([language])
  @@index([publishedYear])
  @@index([isbn13])
  @@index([isbn10])
}

model BookTaxonomy {
  bookId    String
  taxonId   String
  role      TaxonRole
  weight    Int       @default(100) @gte(0) @lte(100)
  note      String?   @db.Text

  book      Book     @relation(fields: [bookId], references: [id], onDelete: Cascade)
  taxon     Taxonomy @relation(fields: [taxonId], references: [id], onDelete: Cascade)

  @@id([bookId, taxonId, role])
  @@index([taxonId, role])
}

/// Reference: from Document 1 (Taxonomy)
enum TaxonomyType {
  issue
  aspiration
  practice
}

model Taxonomy {
  id          String          @id @default(cuid())
  createdAt   DateTime        @default(now())
  name        String          @db.VarChar(50)
  description String          @db.Text
  domain      LifeDomain
  type        TaxonomyType
  tags        String[]        @default([])
  active      Boolean         @default(true)
  mapsFrom    TaxonomyMap[]   @relation("mapsFrom")
  mapsTo      TaxonomyMap[]   @relation("mapsTo")
}

If your ORM supports pgvector natively, replace Unsupported("vector(1536)") with @db.Vector(1536).

---

B. SQL: pgvector Extension and Indexes

-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Example table DDL snippets if created via SQL (ORM will differ)
-- ALTER TABLE "Book" ADD COLUMN "titleEmbedding" vector(1536);
-- ALTER TABLE "Book" ADD COLUMN "summaryEmbedding" vector(1536);

-- ivfflat index for fast approximate nearest neighbor (requires ANALYZE)
CREATE INDEX IF NOT EXISTS book_title_embedding_idx
  ON "Book" USING ivfflat ("titleEmbedding" vector_cosine_ops) WITH (lists = 100);

CREATE INDEX IF NOT EXISTS book_summary_embedding_idx
  ON "Book" USING ivfflat ("summaryEmbedding" vector_cosine_ops) WITH (lists = 100);

-- Supporting btree/hash indexes
CREATE INDEX IF NOT EXISTS book_isbn13_idx ON "Book" ("isbn13");
CREATE INDEX IF NOT EXISTS book_published_year_idx ON "Book" ("publishedYear");

---

C. Query Examples

C.1 Nearest-Neighbor Search (Summary Embedding)

-- $1 :: vector(1536) = query embedding
SELECT id, title, rating
FROM "Book"
ORDER BY "summaryEmbedding" <-> $1
LIMIT 20;

C.2 Constrained NN by Taxon (Issue)

-- $1 :: vector(1536)  |  $2 :: text = taxonId
SELECT b.id, b.title
FROM "Book" b
JOIN "BookTaxonomy" bt ON bt."bookId" = b.id
WHERE bt."taxonId" = $2 AND bt.role = 'ISSUE'
ORDER BY b."summaryEmbedding" <-> $1
LIMIT 20;

C.3 Pull Practices from Books for a Given Issue

-- $1 :: text = issueTaxonId
SELECT DISTINCT t.name AS practice
FROM "BookTaxonomy" bi
JOIN "BookTaxonomy" bp ON bi."bookId" = bp."bookId"
JOIN "Taxonomy" t ON bp."taxonId" = t.id
WHERE bi.role = 'ISSUE' AND bi."taxonId" = $1
  AND bp.role = 'PRACTICE';

---

D. Ingestion Checklist (Condensed)

• Pull from APIs (Google Books, Open Library, etc.).
• Normalize authors/publishers; compute embeddings.
• Dedupe by isbn13, then fuzzy (title+authors).
• Auto-map to Taxonomy (thresholds); queue low-confidence for review.
• Editorial review of high-impact titles.
• Create BookTaxonomy rows with role+weight+note.
• Generate purchaseLinks (Amazon, Audible, Bookshop).
• Run ANALYZE; refresh ivfflat lists if needed.