# Comparative Memo: Zebrapedia → Local-First AI-Assisted Exegesis Workbench
*Practical build guidance. Not a literature review.*
*Date: 2026-03-08 | Derived from Zebrapedia / FromThePage audit*

---

## Purpose

This memo translates what Zebrapedia actually does into concrete decisions for building a local-first, AI-assisted research system for Philip K. Dick's *Exegesis* and similar esoteric-text corpora. Every decision is grounded in evidence from the audit.

---

## Part I: The Core Insight Zebrapedia Gets Right

**The killer insight** is the inversion of reading direction: instead of reading a 8,000-page manuscript sequentially (impossible), you read *thematically* — you navigate to a subject (e.g., "2-3-74") and the system assembles every mention of that subject across the whole corpus into a structured reading experience.

This requires three things to work:
1. A corpus-wide subject index (entity_mentions)
2. A canonical subject reference (subjects table with backlinks)
3. An aggregated reading view (Evidence Packet)

Zebrapedia implements all three, but does so manually — requiring humans to type `[[2-3-74]]` on every one of those 127 pages. This is the bottleneck the AI pipeline eliminates.

---

## Part II: What to Borrow Directly

### 1. Subject Backlink Architecture
**Borrow exactly.** The data structure — `subjects` + `entity_mentions` + `v_subject_mentions_with_context` view — is the right abstraction. The mention_count on the subject record, the per-workset distribution of mentions, the aggregated list view ("127 pages refer to 2-3-74") — all of these map directly to the SQLite schema in `zebrapedia_local_schema_draft.sql`.

*Decision: the `entity_mentions` table is the most important table in the schema. Design everything else to feed into it efficiently.*

### 2. The Three-Level Category Taxonomy
**Borrow the structure; design better content.** The 14-category Zebrapedia taxonomy (Characters, Philosophical Concepts, Notable Dates, Publication, etc.) is a reasonable starting ontology for the Exegesis. The adjacency list `categories` table with `parent_id` and `depth` is the right model.

*Decision: seed the taxonomy from Zebrapedia's observed categories. Allow LLM to suggest category assignments for new subjects. Use a many-to-many `subject_categories` table (improvement over FTP's single-category constraint).*

### 3. Subject Co-occurrence Graph
**Borrow the concept; rebuild the implementation.** The `related_subject_edges` table with `co_occurrence_count` and the IIIF-confirmed data model are correct. Zebrapedia's graph is static and unfiltered; this system needs a live D3.js force-directed graph with workset/date/category filters.

*Decision: compute co-occurrence at index time. Add Jaccard similarity and PMI as alternative edge weights. Cache graph data in `related_subject_edges`; recompute nightly or on-demand after new mentions are added.*

### 4. Page Status State Machine
**Borrow the concept; extend the vocabulary.** FTP's states (unedited/transcribed/needs_review/done/blank) are good for a human-crowdsourced workflow. Add AI-specific states: `ocr_raw`, `ai_transcribed`, `ai_tagged`.

*Decision: use the extended state machine from the schema: unprocessed → ocr_raw → ai_transcribed → ai_tagged → needs_human_review → human_reviewed → scholar_annotated → marked_blank. Each transition is logged in `status_log`.*

### 5. Multiple Export Formats from One Source
**Borrow the principle.** FTP's approach of storing `content_wiki` as the canonical form and deriving HTML, plaintext, and TEI from it is correct. Apply the same principle: store `content_plaintext` as the canonical processed form and generate Markdown, HTML, TEI-XML, and IIIF annotations from it.

*Decision: the transcriptions table stores both the raw OCR/AI output and multiple derived representations. TEI export is a post-processing step, not a primary storage format.*

---

## Part III: What to Rebuild as Deterministic Preprocessing

These are features Zebrapedia lacks or has in primitive form, where the solution is deterministic code (no LLM required).

### 1. Corpus Ingestion Pipeline
Zebrapedia relies on volunteers to upload images and manually transcribe. Build a deterministic preprocessing pipeline:

```
Stage 1: Image ingestion
  → Ingest IIIF manifests OR local image files
  → Create works/pages records in SQLite
  → Store image paths/URLs

Stage 2: OCR
  → Run Tesseract or Google Vision on each image
  → Store raw OCR in transcriptions.content_raw
  → Set page status = 'ocr_raw'

Stage 3: OCR post-processing
  → Clean artifacts, normalize whitespace, join split lines
  → Store in transcriptions.content_plaintext
  → Set page status = 'ai_transcribed'
```

This replaces the entire human transcription crowd entirely for getting text into the system.

### 2. Co-occurrence Graph Computation
The `related_subject_edges` table is populated deterministically from `entity_mentions`:

```sql
-- Recompute all edges after new mentions are added
INSERT OR REPLACE INTO related_subject_edges (subject_a_id, subject_b_id, co_occurrence_count)
SELECT
  LEAST(m1.subject_id, m2.subject_id),
  GREATEST(m1.subject_id, m2.subject_id),
  COUNT(DISTINCT m1.page_id)
FROM entity_mentions m1
JOIN entity_mentions m2 ON m1.page_id = m2.page_id
  AND m1.subject_id != m2.subject_id
GROUP BY 1, 2;
```

Add Jaccard and PMI as additional computed columns. This is pure SQL; no LLM needed.

### 3. FTS Index Maintenance
SQLite FTS5 index over `transcriptions.content_plaintext` and `subjects.description`. Triggers or a synchronization job keep this current after every transcription update.

### 4. Progress Metrics
Materialized statistics in `project_stats` and `workset_stats`. Recompute on a schedule or on status changes. These drive the Project Home dashboard without expensive real-time aggregation.

### 5. Date Tag Extraction
PKD's Exegesis pages often include date headers. A regex/pattern-matching pass can extract approximate dates from OCR output:

```python
# Patterns: "Feb 1974", "2-74", "March, 1975", "1/76", etc.
DATE_PATTERNS = [
    r'\b(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[a-z]*[.,]?\s*(\d{4})\b',
    r'\b(\d{1,2})[/-](\d{2,4})\b',
]
```

Store extracted date in `manuscript_pages.date_tag`. This enables chronological sorting and time-based filtering across the corpus — a feature Zebrapedia lacks entirely.

---

## Part IV: What to Support with LLM-Assisted Extraction / Tagging

These are features where the LLM adds irreplaceable value over pattern matching.

### 1. Named Entity Recognition and Subject Linking (HIGHEST PRIORITY)
**Replace Zebrapedia's Autolink** with a full LLM-based NER pipeline:

```
For each transcribed page:
  1. Run spaCy or a Claude API call to extract named entities
  2. For each entity, compute embedding and compare to existing subjects table
     - cosine > 0.85 → auto-link (store with source='ai_ner', confidence=score)
     - cosine 0.60–0.85 → suggest (store with source='ai_suggested', reviewed=0)
     - cosine < 0.60 → flag as new subject candidate
  3. Store all results in entity_mentions
  4. Set page status = 'ai_tagged'
```

This turns what Zebrapedia does in months of crowdsourcing into hours of compute time. The human's job shifts from *creating* the index to *reviewing and curating* it.

**Claude prompt template for extraction:**
```
You are annotating Philip K. Dick's Exegesis. Extract all named entities from this passage.
For each entity, provide: canonical_name, verbatim_text, entity_type (person/concept/date/publication/place/quote), confidence (0-1).

Passage:
---
{transcription_text}
---

Known subjects in this project: {comma_separated_subject_names}
```

### 2. Subject Description Generation (SEEDING, NOT FINAL)
**LLM-seed the subject articles** that Zebrapedia leaves as stubs:

```
For each subject with is_stub=1 and mention_count > 3:
  1. Fetch top 5 mention passages from entity_mentions
  2. Prompt Claude: "Write a 200-word scholarly description of [subject]
     based on these passages from PKD's Exegesis: [passages].
     Note its significance, recurrence, and relationship to adjacent concepts."
  3. Store result in subjects.ai_description
  4. Human can review and promote to subjects.description
```

This addresses Zebrapedia's most visible weakness: the nearly-empty subject articles.

### 3. Category Assignment Suggestion
```
For each new or uncategorized subject:
  Prompt: "Given these category options: {category_list},
  which category best fits the subject '{subject_name}' described as '{description}'?
  Return: primary_category, optional_secondary_category, confidence."
```

### 4. Subject Duplicate Detection
```
For each new subject added:
  1. Compute embedding of new subject name + description
  2. Find subjects with cosine similarity > 0.85
  3. Present to human: "These subjects may be duplicates: [A] and [B]. Merge?"
```

This scales the duplicate-detection that FTP does with pattern matching alone.

### 5. Annotation Seeding (SCHOLARLY ASSISTANT MODE)
When a scholar opens an Evidence Packet:
```
Prompt: "Based on these 127 passages from PKD's Exegesis mentioning '2-3-74',
identify: (1) how PKD's understanding of this experience evolves over time,
(2) which other subjects it is most frequently juxtaposed with,
(3) any contradictions or reversals in his thinking.
Return as structured markdown with cited passage references."
```

This is the LLM as a *scholarly research assistant*, not just a tagger — the most ambitious use case.

---

## Part V: What Should Live in SQLite Tables

| Data | Table | Rationale |
|------|-------|-----------|
| All entity metadata | `projects`, `worksets`, `works`, `manuscript_pages` | Structured, relational, fast joins |
| Transcription text (all formats) | `transcriptions` | Full-text via FTS5; version history via `revisions` |
| Revision history | `revisions` | Append-only; no updates needed |
| Subject taxonomy | `categories`, `subjects`, `subject_categories`, `subject_aliases` | Relational; hierarchical queries via recursive CTE |
| Entity mentions | `entity_mentions` | The most queried table; denormalized for speed |
| Co-occurrence graph edges | `related_subject_edges` | Computed at index time; fast graph queries |
| Annotations | `annotations` | Relational; linked to pages, subjects, text spans |
| Comments | `comments` | Threaded via parent_id; polymorphic target |
| Status transitions | `status_log` | Audit trail; never delete |
| Project metrics | `project_stats`, `workset_stats` | Materialized cache; recompute on schedule |
| Source links | `source_links` | Simple FK to subjects |
| Bibliography/references | `references` | Normalized citation records |

**Everything in SQLite.** No Postgres. No MongoDB. One file, portable, versionable with git-lfs or LiteFS.

---

## Part VI: What Should Live in Vector Search / Retrieval

| Data | Vector Store | Rationale |
|------|-------------|-----------|
| Transcription chunks | `chunks` table + external vector DB | Semantic similarity search across the corpus |
| Subject embeddings | Inline in `subjects` or external | Duplicate detection; category suggestion |
| Annotation embeddings | Optional | Find related annotations semantically |

**Recommended approach:** Use `sqlite-vec` extension to store embeddings directly in SQLite. This keeps the system fully local-first with no external vector DB dependency. If the corpus exceeds SQLite-vec's performance ceiling, migrate to a local Chroma or FAISS instance — but don't start there.

**Chunk strategy:** Chunk transcriptions by paragraph boundary or ~512 tokens, whichever is smaller. Store in `chunks` table with `page_id`, `start_offset`, `end_offset`, `content`. Embed using a local model (e.g., `nomic-embed-text`) or Claude's embedding API if online.

---

## Part VII: What Should Become React Dashboard Views

Mapped directly from `zebrapedia_react_views.md`. Priority stack:

| View | Priority | What Zebrapedia Has | What You're Adding |
|------|----------|--------------------|--------------------|
| Subject Page | P1 | Static article + link list + basic graph | AI-seeded descriptions, confidence badges, date filter, compare mode |
| Evidence Packet | P1 | Basic aggregated list (auth-gated) | Context window, annotation layer, export, compare mode |
| Manuscript Split View | P1 | Image + text side-by-side | Synchronized highlighting, AI confidence display, inline annotation |
| Workset Explorer | P2 | Document thumbnail grid | Top subjects per folder, status breakdown, date range |
| Category Explorer | P2 | Expandable category tree | Mention counts, stub flags, LLM suggest button |
| Subject Graph | P2 | Static image (no interaction) | D3.js interactive, filterable, exportable |
| Pipeline Status | P2 | Not present | Fully new; essential for AI-first system |
| Annotation Queue | P2 | Not present | Fully new; replaces crowdsourcing coordination |
| Project Home | P1 | Landing page + features | Corpus heatmap, live stats, activity feed |
| Activity Log | P3 | Volunteer metrics (auth-gated) | Human + AI activity unified |

---

## Part VIII: What Should Remain Manual Scholarly Curation

Some tasks should *not* be automated because they require interpretive judgment:

1. **Subject description editing**: The LLM seeds a draft; the scholar refines. The final text requires humanistic expertise.
2. **Subject merging**: The system suggests duplicates; the scholar decides. Merging the wrong subjects would corrupt the index.
3. **Category reassignment**: The LLM suggests; the scholar confirms. Category decisions reflect scholarly interpretation.
4. **Annotation authorship**: All scholarly annotations (glosses, cross-references, interpretive notes) are written by humans. The LLM can suggest starting points but annotations are the primary intellectual contribution.
5. **New subject creation**: When the LLM flags an entity not in the subjects table, a human decides whether it deserves its own entry. Not everything Dick mentions is worth indexing.
6. **Confidence threshold calibration**: A human decides what confidence level counts as "auto-confirm" vs. "needs review." This is a research policy decision.
7. **Taxonomy design**: Adding new top-level categories or restructuring the hierarchy is a scholarly judgment call about how to organize Dickian concepts.

---

## Part IX: What to Avoid

1. **Platform lock-in**: Don't replicate the FromThePage dependency on an external SaaS. The entire system runs locally.
2. **Over-reliance on manual markup**: Don't build a system that requires humans to type `[[subject]]` — build the AI pipeline first.
3. **Thin subject articles**: Don't launch with an empty knowledge base. Seed every subject with an AI-generated description on first indexing.
4. **Static graphs**: Don't build an SVG image of the co-occurrence graph. Build an interactive D3.js component from day one.
5. **The "Unknown / Can't read" garbage bin**: The Zebrapedia category taxonomy has three error-capturing categories ("Unknown / Can't read / Spelling"). Build review queues instead of error categories.
6. **Ignoring chronology**: The Exegesis spans 8 years. Build date extraction into the pipeline immediately. Every view that doesn't have a date filter will feel incomplete.
7. **Google Groups for discussion**: Embedded external discussion is a jarring UX break. Build native threaded comments from the start.

---

## Build Priorities

**Top 10 Zebrapedia-Inspired Features to Implement First**

Priority rankings are based on: (a) scholarly research value, (b) novelty vs. Zebrapedia, (c) implementation tractability.

---

### Priority 1: Subject Backlink Index + Entity Mention Table
*The single most important architectural decision.*

Build `entity_mentions` first, with `subject_id → page_id` as the core join. Without this, everything else is just a file browser. With it, you have the foundation for every research workflow.

**Deliverable:** SQLite schema live, with at minimum test data for one workset.

---

### Priority 2: LLM Named Entity Recognition Pipeline
*Replaces the entire human crowdsourcing bottleneck.*

Build the pipeline: OCR → plaintext → LLM NER → entity_mentions (with confidence scores). This is what Zebrapedia cannot do and what makes this system categorically different.

**Deliverable:** A Python script that processes a folder of page images and populates entity_mentions.

---

### Priority 3: Subject Page React View
*The core user-facing feature.*

Implement the Subject Page with: description, category path, mention count, sorted mention list with folder context, and a basic co-occurrence list (text-based, graph comes later). This is the view that makes the index usable.

**Deliverable:** React component wired to SQLite via Electron IPC or REST endpoint.

---

### Priority 4: Evidence Packet View
*The core reading workflow.*

Implement the aggregated reading view: "All passages mentioning [Subject]" sorted chronologically with source attribution and ±1 page context. Add export to Markdown.

**Deliverable:** React component; export button.

---

### Priority 5: Three-Level Category Taxonomy
*Essential for organizing a 1,000+ subject index.*

Implement the category tree seeded with Zebrapedia's 14 top-level categories. Add LLM category suggestion for new subjects. Implement the Category Explorer view.

**Deliverable:** categories + subject_categories tables; Category Explorer React view.

---

### Priority 6: Date Extraction + Chronological Navigation
*The feature Zebrapedia completely lacks.*

Extract date tags from OCR'd transcriptions using regex patterns for PKD's date notation (Feb 74, 2-74, 3/74, etc.). Store in `manuscript_pages.date_tag`. Add date filter to Subject Page and Evidence Packet.

**Deliverable:** Date extraction script; date_tag column populated; date range filter in two views.

---

### Priority 7: Subject Co-occurrence Graph (Interactive)
*Visualization of the conceptual neighborhoods.*

Implement force-directed graph in D3.js or vis.js. Wire to `related_subject_edges` table. Add basic filtering by minimum edge weight and category.

**Deliverable:** D3.js graph component embedded in Subject Page; separate full-screen graph view.

---

### Priority 8: Manuscript Split View
*The anchoring view for close reading and annotation.*

Implement side-by-side image + transcription view with OpenSeadragon (IIIF) or a simpler image viewer. Add entity mention highlighting in the transcription panel. Add "Add Annotation" button.

**Deliverable:** Split view React component; annotations stored in SQLite.

---

### Priority 9: AI Subject Description Seeding
*Eliminates the stub-article problem.*

For every subject with is_stub=1 and mention_count > 3, generate a LLM description from the top-5 mentions. Store in `subjects.ai_description`. Display in Subject Page with "AI-generated (unreviewed)" badge.

**Deliverable:** Python script; ai_description field in UI with curation interface.

---

### Priority 10: Project Home Dashboard + Workset Explorer
*Orientation and navigation.*

Implement the corpus heatmap (workset grid colored by % reviewed), top subjects list, recent activity feed, and pipeline status summary. Implement Workset Explorer with page thumbnail grid and status badges.

**Deliverable:** Project Home and Workset Explorer React views; workset_stats materialized view populated.

---

## Appendix: Key Technical Decisions Summary

| Decision | Choice | Rationale |
|----------|--------|-----------|
| Primary database | SQLite (WAL mode) | Local-first; portable; FTS5 built in |
| Vector storage | sqlite-vec extension | No external service; stays local |
| Image viewing | OpenSeadragon (IIIF) | Industry standard; handles large images |
| Graph visualization | D3.js force-directed | Interactive; filterable; exportable |
| OCR | Tesseract (local) or Vision API (if budget) | Tesseract for handwritten text + fine-tuning |
| NER / Entity extraction | Claude API (claude-haiku for speed) | Best accuracy on PKD's idiosyncratic writing |
| Embedding model | nomic-embed-text (local) | Privacy-preserving; good multilingual support |
| Frontend framework | React + TypeScript | Component-based; large ecosystem |
| Backend/API | Electron (desktop app) or FastAPI (local server) | Local-first; no cloud dependency |
| Export formats | Markdown, CSV, TEI-XML, JSON, GraphML | Matches FTP export spectrum; adds GraphML |
| Subject markup storage | Plain text + entity_mentions table | More queryable than wiki markup in SQLite |