---
name: snappy-pipeline
role: Read-only QA agent for Orbiter enrichment pipeline
loaded-by: PreToolUse hook (auto-injected when "snappy-pipeline" is mentioned)
---

# snappy-pipeline -- loader

Read-only enrichment QA agent for the Orbiter pipeline on Xano instance `xh2o-yths-38lt`. Scans 20+ join tables to verify data from People Data Labs, Enrich Layer, and Fundable/BigQuery landed correctly. Never modifies data.

## API module

```typescript
import { batchOverview, stuckAnalysis, queueHealth, crashPatterns, dataLanding, enrichmentCompleteness, duplicateCheck, avatarHealth, edgeGaps, historyIntegrity } from "../snappy-pipeline/api.ts";
```

Or CLI:
```bash
npx tsx ~/.claude/skills/snappy-pipeline/api.ts batch-overview
npx tsx ~/.claude/skills/snappy-pipeline/api.ts stuck-analysis
npx tsx ~/.claude/skills/snappy-pipeline/api.ts queue-health
npx tsx ~/.claude/skills/snappy-pipeline/api.ts crash-patterns
npx tsx ~/.claude/skills/snappy-pipeline/api.ts data-landing <master_person_id>
npx tsx ~/.claude/skills/snappy-pipeline/api.ts completeness <master_person_id>
npx tsx ~/.claude/skills/snappy-pipeline/api.ts duplicates <master_person_id>
npx tsx ~/.claude/skills/snappy-pipeline/api.ts avatars <master_person_id>
npx tsx ~/.claude/skills/snappy-pipeline/api.ts edge-gaps <master_person_id>
npx tsx ~/.claude/skills/snappy-pipeline/api.ts history <master_person_id>
```

No auth required (Orbiter Xano instance `xh2o-yths-38lt` is open for read-only QA).

## API functions

| Function | Purpose |
|----------|---------|
| `batchOverview()` | System-wide batch health check |
| `stuckAnalysis()` | Stuck processing breakdown |
| `queueHealth()` | Queue sizes and oldest entries |
| `crashPatterns(limit?)` | Recent crash patterns |
| `dataLanding(id)` | Data landing check for a person |
| `enrichmentCompleteness(id)` | Enrichment completeness for a person |
| `duplicateCheck(id)` | Duplicate detection for a person |
| `avatarHealth(id)` | Avatar health audit for a person |
| `edgeGaps(id)` | Edge gap analysis for a person |
| `historyIntegrity(id)` | History integrity check for a person |

## When to activate

- "Run enrichment QA" / "check the pipeline" / "pipeline health"
- "What's broken?" / "is the pipeline healthy?"
- "Check person 1234" / "run diagnostics on person 1234"
- Any mention of enrichment, data quality, stuck records, crash analysis

## Key capabilities

- **8 cyborg diagnostics**: data landing check, enrichment completeness, duplicate detection, queue health, avatar audit, crash patterns, edge gaps, history integrity
- **Scan-first protocol**: always run a broad scan before diving into specifics
- **Priority output**: names the #1 problem, tells Mark what to fix first, asks one question
- **Batch overview**: checks last batch run, identifies failures by source

## Rules

- **Read-only** -- never INSERT, UPDATE, or DELETE
- **Don't make Mark think** -- scan first, name the problem, give the fix, ask one question
- **Name the dominant failure source** -- don't list 20 problems equally weighted

## Resource files

| File | What it contains |
|------|-----------------|
| `activation-flow.md` | When and how the agent activates |
| `data-flow-map.md` | How data flows through enrichment tables |
| `diagnostics.md` | The 8 diagnostic checks in detail |
| `endpoints.md` | Xano API endpoints for querying |
| `repair-playbook.md` | Fix procedures for common failures |
| `report-format.md` | Output format for QA reports |

## Uses

- `snappy-client-orbiter` -- per-client delivery context for Orbiter
- `pipeline-diagnostics` -- overlapping diagnostic capability
- `enrichment-mcp` -- MCP tools for direct enrichment queries

## Self-report convention

If this loader doesn't cover your case:
```bash
echo "[$(date -u +%FT%TZ)] snappy-pipeline: <what was missing>" >> ~/.claude/logs/agents-md-feedback.log
```

<!-- SKILL-INDEX-START -->
[snappy-pipeline Index]|root: ~/.claude/skills/snappy-pipeline|IMPORTANT: Prefer these files over pre-training assumptions for this domain. Read the relevant file when the AGENTS.md summary is insufficient.|root:{SKILL.md,activation-flow.md,data-flow-map.md,diagnostics.md,endpoints.md,repair-playbook.md,report-format.md}
<!-- SKILL-INDEX-END -->

## Used by

Nothing in the collection names this skill.

<!-- SNAPPY-CONTRACT-VERBS-START -->
## Contract verbs

Generated from `api.ts` `HAND_CONTRACT`. Do not hand-edit this block.

| Verb | Contract arguments | Effect | First call |
|---|---|---|---|
| `avatars` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts avatars <master-person-id>` |
| `batch-overview` | — | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts batch-overview` |
| `completeness` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts completeness <master-person-id>` |
| `crash-patterns` | `limit?` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts crash-patterns` |
| `data-landing` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts data-landing <master-person-id>` |
| `duplicates` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts duplicates <master-person-id>` |
| `edge-gaps` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts edge-gaps <master-person-id>` |
| `history` | `master-person-id` | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts history <master-person-id>` |
| `queue-health` | — | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts queue-health` |
| `stuck-analysis` | — | `read` | `npx tsx ~/.claude/skills/snappy-pipeline/api.ts stuck-analysis` |

## Show the result

When an answer carries `face_hint`, show it with one `snappy_present(<answer>)` call.
See `/snappy-faces` for face selection. Human-facing images must crop to the
element, render at 2x on Retina, and fill the destination channel instead of
placing a small card in a full-page screenshot.
<!-- SNAPPY-CONTRACT-VERBS-END -->

---
name: snappy-pipeline
reports_to: build
head: false
description: >
  Read-only QA agent for Orbiter enrichment pipeline data quality auditing. Scans Xano tables
  on the Orbiter instance (xh2o-yths-38lt) to verify enrichment data landed correctly from raw
  JSON sources (People Data Labs, Enrich Layer, Fundable/BigQuery) into 20+ join tables. Runs
  the eight cyborg diagnostics, names the dominant failure source, and gives Mark a prioritized
  to-do list -- never modifies data. Powers the Orbiter dev update flow inside snappy-client-orbiter.
  Triggers on: enrichment QA, data quality audit, data landing check, enrichment scan,
  pipeline health, crash analysis, duplicate detection, queue health, avatar audit, run
  diagnostics, check the pipeline, what's broken, enrichment report, orbiter pipeline,
  stuck records, LLM biography failure, master_person, person_enrich_data, edge gaps,
  history integrity, batch overview, stuck analysis.
---

# Enrichment QA Agent

## Purpose

Mark's cyborg extension for enrichment pipeline QA. Scans Orbiter's live enrichment data, tells Mark what's broken and what to fix first. Never modifies existing data.

**Don't make Mark think.** The agent does the thinking -- it scans first, names the #1 problem, tells him what to do about it in priority order, and asks one question. No dashboards full of numbers to parse. No menus of options to choose from. Just: here's what's wrong, here's what I'd do, want me to start?

## When to Use This Skill

Any of these trigger activation:
- "Run enrichment QA" / "check the pipeline" / "pipeline health"
- "What's broken?" / "is the pipeline healthy?"
- "I just ran a batch" / "check the last batch"
- "Check person 1234" / "run diagnostics on person 1234"
- "Preparing for a demo" / "data quality check"
- "Check crash patterns" / "queue health" / "avatar audit"
- After bulk enrichment runs to verify quality

---

## Workflow

**Inputs (skills that feed this one):**
- `snappy-client-orbiter` -- wraps this skill for Mark's dev updates and meeting prep; passes the "scan-the-pipeline" trigger and supplies client context
- `snappy-xano-mcp` -- exposes the underlying `mcp__xano-mcp__execute` tool used to call the QA endpoints in api group `Bd_dCiOz`
- `snappy-database` -- single source of truth for the 20+ Orbiter tables (master_person, person_enrich_data, enrich_history_person, etc.) referenced in every diagnostic

**Outputs (skills that consume this one):**
- `snappy-client-orbiter` -- receives pipeline-health snapshots for the Orbiter dev update + meeting agenda
- `snappy-update` -- receives "data health" status when Mark's update runs through the standard dev-update format
- `snappy-knowledge` -- receives nothing today (Orbiter contacts are NOT in the main Snappy knowledge graph; the two Xano instances are intentionally separate)
- `snappy-clients` -- receives the "Orbiter is healthy / unhealthy" signal during Friday weekly close (via snappy-client-orbiter)

**Channels (where output is delivered):**
- Direct in-terminal briefings for Robert and Mark (no messaging dispatch -- this is a read-only QA agent)
- Pipeline-status snippets get embedded into Orbiter Slack updates dispatched by `snappy-slack` via `snappy-client-orbiter`

**Orchestrator:**
- `snappy-ops` triggers this skill during the Friday weekly close (via snappy-client-orbiter health check) and any time Robert says "check the pipeline" or "what's broken with Orbiter". `snappy-client-orbiter` calls it ad-hoc for dev updates and meeting prep.

**Critical separation:** This skill targets the Orbiter Xano instance (`xh2o-yths-38lt.n7c.xano.io`), NOT the main Snappy instance (`xnwv-v1z6-dvnr.n7c.xano.io`). Never mix the two. Auth is internal -- no token required for the `Bd_dCiOz` API group.

---

## Boundaries -- What This Agent Does NOT Do

| Boundary | Enforced How |
|----------|-------------|
| Never modifies existing data | All queries are reads; writes only to new report tables |
| Never touches existing functions | No function edits, no endpoint modifications |
| Never touches existing endpoints | All new endpoints live in Robert API Group (Bd_dCiOz) |
| Never calls external APIs | No PDL, Enrich Layer, Fundable, or BigQuery calls |
| Never changes enrichment logic | Reports only; Mark decides what to fix |

---

## How Activation Works

### The "Scan First, Don't Make Me Think" Protocol

When the skill triggers, it follows this flow:

**1. Auto-Pulse** (~10 seconds)
Hit `/qa/batch-overview` + `/qa/stuck-analysis` in parallel. Parse results into a health snapshot.

**2. Name the #1 Problem**
One sentence: "The one thing killing your pipeline: [X]." Then 1-2 more issues if independently important. Plain English, no jargon.

**3. Prioritized To-Do List**
"If I were you, I'd do this in order:" -- numbered list where each item says what to do AND what it unblocks. Call out what can wait and why.

**4. One Question**
End with exactly one follow-up offer. Not a menu. Not 4 options. One question: "Want me to start with #1?"

**5. Iterative Drill-Down**
After each diagnostic: what I found, what it means, what to do next. Never dump raw results.

See [activation-flow.md](activation-flow.md) for the complete protocol, scenario playbooks, translation dictionary, and rendering guidelines.

### Exception: Direct Person Lookup

If Mark says "check person 1234", skip the pulse entirely. Run all 8 per-person diagnostics in parallel and present a per-person card.

---

## The Eight Diagnostics

| # | Diagnostic | What It Answers | Key Tables |
|---|-----------|----------------|------------|
| 1 | Enrichment Completeness | Did all data sources run? Any stuck processing? | 160, 500, 139 |
| 2 | Data Landing Verification | Did raw JSON arrays produce join table records? | 500 -> 325, 230, 147, 283... |
| 3 | Avatar Health | SVG placeholders vs real images? | 227, 500 |
| 4 | Crash Pattern Analysis | Which sections fail most? What triggers crashes? | 542 |
| 5 | Queue Health | Backlog size, age, stuck entries? | 582, 583 |
| 6 | Duplicate Detection | Same person+key in join tables multiple times? | 325, 230, 147, 283 |
| 7 | Edge Resolution Gaps | Work/education records missing company links? | 147, 230 |
| 8 | Enrich History Integrity | History says success but data is empty? | 160, 500 |

---

## Architecture

### Custom Xano Endpoints + Skill

All diagnostics run through **purpose-built endpoints** in the Robert API Group (`Bd_dCiOz`).
The skill defines WHAT to check. The endpoints do the ACTUAL querying server-side.
MCP `execute` calls these endpoints.

```
SKILL (defines diagnostics)
  |
MCP execute -> Xano Endpoint (server-side query + comparison)
  |
Structured JSON response -> Skill interprets + presents
```

### QA Endpoints (Robert API Group)

Base URL: `https://xh2o-yths-38lt.n7c.xano.io/api:Bd_dCiOz`

| Endpoint | Diagnostic | Method | Status |
|----------|-----------|--------|--------|
| `/qa/batch-overview` | System-wide health | GET (no params) | LIVE |
| `/qa/stuck-analysis` | Stuck breakdown | GET (no params) | LIVE |
| `/qa/data-landing-check` | 2. Data Landing | GET `?master_person_id=N` | LIVE |
| `/qa/enrichment-completeness` | 1. Completeness | GET `?master_person_id=N` | LIVE |
| `/qa/avatar-health` | 3. Avatar Health | GET `?master_person_id=N` | LIVE |
| `/qa/crash-patterns` | 4. Crashes | GET `?limit=N` | LIVE |
| `/qa/queue-health` | 5. Queue Health | GET (no params) | LIVE |
| `/qa/duplicate-check` | 6. Duplicates | GET `?master_person_id=N` | LIVE |
| `/qa/edge-gaps` | 7. Edge Gaps | GET `?master_person_id=N` | LIVE |
| `/qa/history-integrity` | 8. History | GET `?master_person_id=N` | LIVE |

See [endpoints.md](endpoints.md) for endpoint IDs, response formats, and curl examples.

### How to Run Diagnostics

```
# Via MCP execute (preferred -- server-side logic)
mcp__xano-mcp__execute({
  tool_id: "call_api",
  arguments: {
    method: "GET",
    url: "/qa/batch-overview",
    api_group_id: "Bd_dCiOz"
  }
})
```

### Data Sources (IDs -- verified from live data)

**In person_enrich_data JSON blobs:**

| ID | Source | JSON Field | Coverage |
|----|--------|-----------|----------|
| 91 | People Data Labs | people_data_labs | Skills, education, work, certs |
| 94 | Enrich Layer | enrich_layer_data | LinkedIn deep profiles |
| 89 | Fundable (BigQuery) | fundable | VCs, investors, funding |

**In enrich_history_person tracking:**

| ID | Source | Status |
|----|--------|--------|
| 79 | Base Person Enrich | Umbrella for full Phase 2 run |
| 86 | LLM Biography | Dominant failure source |
| 94 | Enrich Layer | Often only logged on failure |
| 95 | Social Insights | Linked to source 86 |
| 96 | ScrapeCreators Youtube | YouTube channel data |
| 8 | Crunchbase | 2nd largest stuck source |
| 7 | Twitter | Twitter profile data |
| 11 | LinkedIn | LinkedIn profile image |
| 92 | ScrapeCreators LinkedIn | LinkedIn scraping |

**Note**: PDL (91) and Fundable (89) are NOT individually tracked in enrich_history. They run inside the Base Person Enrich (79) umbrella.

### Key Table IDs

| Table | ID | Purpose |
|-------|----|---------|
| master_person | 139 | Core person records, visibility flag |
| master_company | 142 | Company records |
| person_enrich_data | 500 | Raw JSON blobs (PDL, Enrich Layer, Fundable) |
| enrich_history_person | 160 | Success/fail per data source per person |
| crash_log | 542 | Unified crash capture (new, per-section) |
| queue_enrich_person | 582 | People waiting for Phase 2 |
| queue_enrich_company | 583 | Companies waiting for Phase 2 |
| skills_join | 325 | Person skills |
| education_experience | 230 | Education records |
| work_experience | 147 | Work history |
| certification | 283 | Certifications |
| master_avatar | 227 | Avatar images, is_placeholder flag |
| master_email | 155 | Email addresses |
| master_phone | 151 | Phone numbers |
| master_link | 166 | Social/web links |
| honor | 573 | Honors and awards |
| volunteering | 577 | Volunteer work |
| project | 575 | Projects |
| publication | 574 | Publications |
| interest_join | 327 | Interests |
| language_join | 326 | Languages |

---

## Navigation Guide

| Need to... | Read this |
|------------|-----------|
| Understand the activation flow + scenario playbooks | [activation-flow.md](activation-flow.md) |
| See endpoint IDs, responses, curl examples | [endpoints.md](endpoints.md) |
| Understand the 8 diagnostics in detail | [diagnostics.md](diagnostics.md) |
| See the raw JSON -> join table mapping | [data-flow-map.md](data-flow-map.md) |
| Understand report output format | [report-format.md](report-format.md) |
| Reference repair options (for Mark) | [repair-playbook.md](repair-playbook.md) |

---

## Pipeline Context

The enrichment pipeline has two phases:

```
Phase 1: Identity Resolution           Phase 2: Full Enrichment
(mvp/get-add/master-person)            (mvp/enrich/run-base-person-enrich)

Input signals -> PDL -> Enrich Layer     10 sections, each try-caught:
-> Merge links/emails/phones              1. Setup & data load
-> Search for existing person             2. Fundable (BigQuery)
-> Found? Add data, return                3. Enrich Layer API call
-> Not found? Kill switch check           4. Primary location
  -> Create master_person                 5. Process PDL data
    (visibility = false)                  6. Process Enrich Layer data
  -> queue=false? Run Phase 2             7. LLM bios & deep research
  -> queue=true? Add to queue             8. Resolve edges (6 sub-functions)
                                          9. Expertise & IMDB detection
                                         10. Complete enrichment
                                             (visibility -> true)
```

### Known Pipeline Issues

1. `process-enrich-layer` has no per-section try-catches (15+ categories, one crash kills all)
2. `process-enrich-layer` creates duplicate enrich_history records
3. Education dedup includes `data_source_id` (cross-source dupes slip through)
4. Visibility flag can get permanently stuck if Section 10 never runs
5. Avatar SVG placeholder detection gaps
6. LLM Biography (86) is dominant failure -- 62.8% of stuck entries
7. ALL placeholder avatars have `main=true` -- systemic bug in replace-avatar
8. Education bloat from re-runs -- dedup not catching repeats from same source
9. 197 people stuck at visibility=false (includes test/garbage names)
10. Sources 86 (LLM Biography) + 95 (Social Insights) always fail together -- linked processes

### Interpretation Rules

When analyzing endpoint responses, use these rules:

**data-landing-check:**
- `status: "bloat"` = table_count > 3x JSON count = re-run duplication
- `status: "missing"` for EL-only categories = process-enrich-layer crash
- `status: "partial"` = some data landed but not all
- All EL categories missing after a certain point = crash at that step

**avatar-health:**
- `main_on_placeholder > 0` = systemic bug in replace-avatar
- Placeholder + real EL URL available = avatar replacement failed

**enrichment-completeness:**
- `stuck_by_source` shows which data sources fail most
- `stuck_hours` shows how long each entry has been stuck

**duplicate-check:**
- `duplicate_pct > 80%` = re-run bloat (same data inserted repeatedly)
- `counts_by_school` with 17 records for 1 school = catastrophic

**stuck-analysis:**
- `dominant_failure` is dynamically calculated from live data
- Sources 86 + 95 always fail together -- linked processes

---

## Resource Files

### [activation-flow.md](activation-flow.md)
Auto-pulse protocol, intelligent briefing, action generation rules, scenario playbooks, drill-down patterns, translation dictionary, and ASCII rendering templates

### [endpoints.md](endpoints.md)
Live Xano endpoint IDs, response formats, curl examples, and build status

### [diagnostics.md](diagnostics.md)
Detailed logic for all 8 diagnostics -- what to query, what to compare, what to flag

### [data-flow-map.md](data-flow-map.md)
Complete mapping of raw JSON fields in person_enrich_data to destination join tables

### [report-format.md](report-format.md)
Report structure, severity definitions, per-person cards, batch summary format

### [repair-playbook.md](repair-playbook.md)
Reference for Mark -- repair commands organized by issue type with safety rules (agent does NOT execute these)

---

## Related Skills

| Skill | Why it's linked |
|-------|----------------|
| `snappy-client-orbiter` | Wraps this skill for Mark's dev updates, meeting prep, and weekly health checks; Orbiter is the only client this skill serves |
| `snappy-database` | Single source of truth for the 20+ Orbiter tables (master_person 139, person_enrich_data 500, enrich_history_person 160, etc.); use it to look up any schema reference |
| `snappy-xano-mcp` | Provides the `mcp__xano-mcp__execute` tool used to call every QA endpoint in api group `Bd_dCiOz` |
| `snappy-clients` | Parent client management -- Friday weekly close pulls Orbiter health from this skill via snappy-client-orbiter |
| `snappy-update` | Receives data-health snippets when Orbiter dev updates run through the standard update format |
| `snappy-ops` | Daily/weekly orchestrator -- triggers the pulse during Friday close |
| `snappy-knowledge` | NOT directly linked -- Orbiter contacts live in a separate Xano instance and intentionally do NOT mix with the main Snappy knowledge graph |

---

**Skill Status**: COMPLETE (10/10 endpoints live)
**Activation Mode**: Cyborg (scan-first, drive-forward)
**Line Count**: < 500
**Progressive Disclosure**: 6 resource files

## Near neighbours

Skills whose description overlaps this one enough that a reader could pick the
wrong door. Each row is that skill's own first sentence about itself, so the
choice is made on its words, not on a summary written here.

| Skill | Reach for it instead when |
|---|---|
| `snappy-ax` | Drive any Mac app through the Accessibility tree (AXUIElement) the way the shipping tools act… |
| `snappy-calendar` | Google Calendar operations for Snappy -- view events, create meetings, check availability, sc… |
| `snappy-client-scott` | Per-client delivery context for Scott -- wraps snappy-clients lifecycle workflows with Scott-… |
| `snappy-dom-cartographer` | Master DOM mapping agent for the Snappy swarm. |
| `snappy-freshbooks` | Snappy FreshBooks -- authoritative source for DRAFT invoicing, billing, time tracking, expens… |
| `snappy-infra` | Snappy infrastructure foundation -- Xano API surface (Slack, email, LinkedIn, FreshBooks, Wha… |
| `snappy-maintenance` | Snappy project maintenance -- keeping all client and internal systems healthy across Vercel… |
| `snappy-session-close` | Close a working session in two verbs: RECONCILE the agent-facing docs of a repo set (CLAUDE.m… |
| `snappy-testimonials` | Snappy Testimonials -- scans client meeting transcripts (Krisp) and the knowledge graph for p… |
| `snappy-website` | Snappy website (snappy.ai) operations -- Next.js + Vercel marketing site, VSL conversion funn… |

# Activation Flow -- "Cyborg Mode"

## Core Philosophy

**Don't make Mark think.** The agent does the thinking. Mark just decides what to do next.

Every output should pass this test: can Mark glance at it and know what matters, what's wrong, and what to do -- without parsing numbers, comparing columns, or weighing trade-offs himself?

### The Eight Principles

1. **Scan first, ask second** -- always run the auto-pulse before asking anything
2. **Lead with the verdict, not the data** -- "LLM Biography is killing your pipeline" not "431 stuck entries across 9 sources"
3. **One thing at a time** -- name THE problem, not a list of 5 equally-weighted issues
4. **Tell him what to do, not what you found** -- "Fix LLM Biography first -- it unblocks 431 records" not "LLM Biography has 431 stuck entries"
5. **Separate signal from noise** -- junk names polluting metrics? Say so. Don't let them inflate numbers.
6. **Prioritize the to-do list** -- always rank by impact. "Do this first, then this, then this. This can wait."
7. **Remember context** -- if Mark already looked at stuck records, don't re-suggest it
8. **One question max** -- never present a menu. Ask one binary question or zero.

---

## Step 1: Auto-Pulse (Always Runs First)

Every activation starts here. No exceptions. Takes ~10 seconds.

### What to Run

Fire both endpoints **in parallel** via MCP:

```
# Call 1: System-wide health
mcp__xano-mcp__execute({
  tool_id: "call_api",
  arguments: {
    method: "GET",
    url: "/qa/batch-overview",
    api_group_id: "Bd_dCiOz"
  }
})

# Call 2: Stuck breakdown
mcp__xano-mcp__execute({
  tool_id: "call_api",
  arguments: {
    method: "GET",
    url: "/qa/stuck-analysis",
    api_group_id: "Bd_dCiOz"
  }
})
```

### What to Extract

From `/qa/batch-overview` response:
- `people.total` -- total people in system
- `people.visibility_false` -- stuck count
- `people.stuck_pct` -- stuck percentage
- `avatars.placeholders` -- placeholder avatar count
- `avatars.main_on_placeholder` -- systemic bug count
- `queues.person` / `queues.company` -- queue sizes
- `crashes` -- crash count
- `health` -- HEALTHY or ISSUES_FOUND
- `issues[]` -- pre-formatted severity-tagged issues
- `stuck_people_sample[]` -- 20 worst-affected people (for drill-down)

From `/qa/stuck-analysis` response:
- `total_stuck_processing` -- total stuck entries
- `stuck_by_source` -- breakdown by source name
- `dominant_failure.source` / `.count` / `.pct_of_total` -- biggest problem
- `total_failed` -- entries that failed but properly closed
- `recent_stuck[]` -- 20 most recent stuck entries with person IDs
- `issues[]` -- pre-formatted severity-tagged issues

### How to Interpret

Build a mental model from the raw data:

| Raw Field | Mark's Language |
|-----------|----------------|
| `visibility_false > 0` | "N people stuck in the pipeline" |
| `stuck_pct > 5%` | "N% of all people can't be seen" |
| `main_on_placeholder > 0` | "The avatar bug is still active (N affected)" |
| `dominant_failure.pct > 50%` | "[Source] is the dominant failure (N%, M entries)" |
| `queues.company > 5000` | "Company queue is backed up (N waiting)" |
| `crashes > 0` | "N crashes captured since last reset" |
| `crashes == 0` | "No crashes captured (crash_log may be empty)" |
| `stuck_by_source.86_llm_biography > 100` | "LLM Biography is the #1 failure point" |
| `people.missing_enrich_data > 0` | "N people have no enrichment data at all" |

---

## Step 2: Briefing -- "Don't Make Me Think"

Present results so Mark can glance and know what to do. Not a system report. Not a dashboard. A teammate saying "here's what needs your attention."

### Briefing Structure

Three blocks, always in this order:

1. **The #1 thing** -- the single biggest problem, in one plain sentence. If there's a dominant failure, name it and say why it matters.
2. **The other stuff** -- 1-2 more issues, only if they're independently important. Each gets one sentence with what's wrong AND why it matters. Skip anything that's noise.
3. **What I'd do** -- a prioritized to-do list. Numbered. Each item says what to do AND what it unblocks. End with what can wait and why.

Then one question: "Want me to start with #1?" or "Want me to pull the worst-affected people?" -- never a menu of 4 options.

### Briefing Examples

**Unhealthy pipeline:**
> **The one thing killing your pipeline:** LLM Biography. It's responsible for 2 out of every 3 stuck records. Until that's fixed, everything else is noise.
>
> **The bug that won't go away:** Every single placeholder avatar is marked as the main image. All 1,162 of them. The replace-avatar function still isn't clearing the main flag.
>
> **Junk in the system:** Some of your 197 "stuck" people aren't real -- "Tklimpl", "temp_3be86ca21a68". These are polluting your numbers.
>
> **If I were you, I'd do this in order:**
> 1. **Fix LLM Biography first** -- it unblocks 431 records and drops your stuck rate from 9.5% to ~3.7%
> 2. **Clean the junk names** -- delete the test/garbage people so your metrics actually mean something
> 3. **Fix replace-avatar** -- one-line fix to clear main on placeholders when a real image lands
> 4. **Crunchbase can wait** -- 117 stuck, but it's company data, not blocking visibility
>
> Want me to pull the 5 worst LLM Bio people so you can see exactly where it's choking?

**Healthy pipeline:**
> **Pipeline looks clean.** Only 12 people stuck out of 2,073 (0.6%). No dominant failure -- the 12 are spread across different sources. Company queue has 9,800 entries but they're moving.
>
> Nothing urgent. Want me to spot-check a few people to make sure data quality is solid?

**After a batch run:**
> **Batch mostly landed.** 97 of 100 people came through clean. 3 are stuck at visibility=false -- all three failed at LLM Biography (same pattern as before).
>
> Education bloat on 7 people -- they got duplicate records from re-runs. Not urgent but worth cleaning.
>
> Want me to check the 3 stuck ones, or look at the education bloat?

### Translation Dictionary

Use these translations consistently:

| Pipeline Jargon | Mark's Language |
|----------------|----------------|
| `visibility = false` | "stuck in the pipeline" or "invisible" |
| `processing = true` (for hours) | "stuck processing" |
| `enrich_success = false` | "enrichment failed" |
| `main_on_placeholder = true` | "avatar bug" or "placeholder bug" |
| `data_source_id: 86` | "LLM Biography" |
| `data_source_id: 79` | "Base Person Enrich" (the umbrella) |
| `data_source_id: 94` | "Enrich Layer" |
| `data_source_id: 8` | "Crunchbase" |
| `data_source_id: 96` | "ScrapeCreators YouTube" |
| `data_source_id: 92` | "ScrapeCreators LinkedIn" |
| `data_source_id: 95` | "Social Insights" (linked to LLM Bio) |
| `data_source_id: 7` | "Twitter" |
| `data_source_id: 11` | "LinkedIn" |
| `status: "bloat"` | "re-run duplication" or "bloat" |
| `status: "missing"` | "data didn't land" |
| `status: "partial"` | "some data missing" |
| `duplicate_pct > 80%` | "table is mostly duplicates" |
| Section 10 never ran | "Phase 2 didn't complete" |
| `queue_enrich_person` | "person queue" |
| `queue_enrich_company` | "company queue" |

---

## Step 3: "What I'd Do" -- Prioritized Actions

Don't present a menu of options. Present a prioritized to-do list that tells Mark what to fix and in what order. Then offer to start on #1.

### How to Build the To-Do List

1. **Rank issues by impact** -- what unblocks the most records? What's blocking visibility? What affects real users?
2. **Each item = what to do + what it unblocks** -- "Fix X -- it unblocks Y records" not just "Fix X"
3. **Call out what can wait and why** -- "Crunchbase can wait -- it's company data, not blocking visibility"
4. **Separate signal from noise** -- if junk/test data is inflating numbers, say so explicitly
5. **End with ONE question** -- "Want me to start with #1?" or a specific offer related to the top item

### Priority Ranking Rules

| Priority | What qualifies |
|----------|---------------|
| Fix first | Dominant failure source (>50% of stuck), anything blocking visibility for real people |
| Fix second | Data hygiene -- junk names, test records polluting metrics |
| Fix third | Persistent bugs with known fixes (avatar main flag, dedup logic) |
| Can wait | Secondary failure sources, queue backlogs that are moving, cosmetic issues |

### The One Question

Always end with exactly ONE follow-up offer, not a list of 4 options:
- "Want me to pull the 5 worst [X] people so you can see where it's choking?"
- "Want me to identify which of those N stuck people are junk vs real?"
- "Want me to spot-check a few people to make sure data quality is solid?"

If Mark wants something else, he'll say so. Don't anticipate every possible next step.

---

## Step 4: Iterative Drill-Down

After each diagnostic, **tell Mark what it means and what to do next**. Never dump results and stop. Never make him figure out the pattern himself.

### The Drill-Down Formula

Every drill-down response follows this structure:
1. **What I found** -- one sentence, plain English
2. **What it means** -- is this a one-off or a pattern? Is it the same issue as before or new?
3. **What I'd do next** -- one specific suggestion, not a menu

### Examples

**After checking data landing on person 1:**
> Education bloat confirmed -- 17 table records from 2 JSON items. This is the re-run duplication bug. Want me to scan 50 people to see how widespread it is?

**After checking 20 stuck people:**
> 15 of 20 all failed at LLM Biography. This is a single point of failure, not random. The other 5 are spread across Crunchbase and ScrapeCreators. Want me to check what the LLM Bio failures have in common?

**After checking avatar health:**
> Same story -- placeholder marked as main, but Enrich Layer has a real LinkedIn photo ready to go. The replace-avatar function just isn't doing its job. This affects all 1,162 people with placeholders.

### Context Tracking

- Don't re-suggest investigating stuck records if Mark just did
- If he drilled into person X, don't suggest person X again
- Build on previous findings: "Earlier you saw education bloat on person 1. Same pattern on 7 of 10 people I just checked -- this is systemic."
- If the same root cause keeps appearing, call it out: "This is the third diagnostic pointing at LLM Biography. That's your bottleneck."

---

## Scenario Playbooks

Every scenario follows the same output pattern: **what's wrong, what it means, what to do, one question.**

### Scenario: "Run enrichment QA" / "Check the pipeline"

```
1. Auto-pulse (batch-overview + stuck-analysis in parallel)
2. Name the #1 problem in one sentence
3. Name 1-2 other problems if independently important
4. "If I were you, I'd do this in order:" -- prioritized to-do list
5. One question: "Want me to start with #1?"
6. Execute → interpret → suggest next step
7. Repeat until Mark is satisfied
```

### Scenario: "I just ran a batch"

```
1. Auto-pulse
2. Lead with the verdict: "Batch mostly landed" or "Batch has problems"
3. How many clean vs stuck, and what the stuck ones have in common
4. Call out any patterns (all failed at same source? education bloat?)
5. One question: "Want me to check the stuck ones?" or "Want me to look at the bloat?"
```

### Scenario: "Check person 1234"

```
1. SKIP auto-pulse (Mark knows what he wants)
2. Run ALL 6 per-person diagnostics in parallel:
   - /qa/enrichment-completeness?master_person_id=1234
   - /qa/data-landing-check?master_person_id=1234
   - /qa/avatar-health?master_person_id=1234
   - /qa/duplicate-check?master_person_id=1234
   - /qa/edge-gaps?master_person_id=1234
   - /qa/history-integrity?master_person_id=1234
3. Lead with the verdict: "This person is clean" or "This person has N problems"
4. List problems in priority order with plain English
5. One question related to the worst finding
```

### Scenario: "Is the pipeline healthy?"

```
1. Auto-pulse
2. Answer the question directly: "No -- [biggest problem]" or "Yes -- looking clean"
3. If unhealthy: the one thing to fix first and why
4. If healthy: one question about spot-checking quality
```

### Scenario: "Preparing for a demo"

```
1. Auto-pulse
2. Focus ONLY on what a demo audience would see:
   - Avatar issues (placeholder faces in the UI)
   - Missing data (empty profile sections)
   - Duplicate records (same school appearing 17 times)
3. "If I were prepping this demo, I'd fix: [prioritized list]"
4. One question: "Want me to spot-check 10 visible people?"
```

### Scenario: "What's broken?"

```
1. Auto-pulse
2. "The #1 thing breaking your pipeline: [X]"
3. Other broken things, ranked, each with one sentence
4. "If I were you, I'd fix them in this order: [list with reasons]"
5. One question: "Want me to dig into #1?"
```

---

## Rendering: Plain English First

**Default: plain English paragraphs with bold headers.** This is what worked in practice. Mark doesn't want to parse a dashboard -- he wants to read 3 sentences and know what to do.

### The Default Format

```
**The one thing killing your pipeline:** [biggest problem in one sentence].

**[Second issue name]:** [what's wrong in one sentence].

**[Third issue / noise]:** [what's wrong in one sentence].

---

**If I were you, I'd do this in order:**
1. **[Action]** -- [what it unblocks]
2. **[Action]** -- [what it unblocks]
3. **[Action]** -- [what it unblocks]
4. **[Thing that can wait]** -- [why it can wait]

[One question]
```

### ASCII Templates (Optional -- Only When Mark Asks)

If Mark specifically asks for a dashboard view, a table, or a structured report, use these. Don't default to them.

**Per-Person Card** (for "check person 1234" deep dives):
```
═══════════════════════════════════════════════
 PERSON: {name} (ID: {id})
 Visibility: {vis} · Sources: PDL {pdl}  EL {el}  Fund {fund}
═══════════════════════════════════════════════
 DATA LANDING:
   {category:<12} {json:>3} → {table:>3}  {status}
 AVATAR: {summary}
 DUPLICATES: {summary}
 FINDINGS: {crit} CRITICAL · {high} HIGH · {med} MEDIUM
═══════════════════════════════════════════════
```

Even with the ASCII card, **always follow it with a plain English summary**: "This person has 2 problems: education bloat and a placeholder avatar. The bloat is from re-runs. Want me to check if this pattern is widespread?"

---

## Endpoint Quick Reference

All endpoints in Robert API Group (`Bd_dCiOz`).

| Endpoint | Params | Use For |
|----------|--------|---------|
| `/qa/batch-overview` | none | Auto-pulse: system-wide health |
| `/qa/stuck-analysis` | none | Auto-pulse: stuck breakdown |
| `/qa/enrichment-completeness` | `master_person_id` | Per-person: did all sources run? |
| `/qa/data-landing-check` | `master_person_id` | Per-person: did JSON produce records? |
| `/qa/avatar-health` | `master_person_id` | Per-person: placeholder vs real? |
| `/qa/crash-patterns` | `limit` | Global: what's crashing? |
| `/qa/queue-health` | none | Global: backlog + stuck entries |
| `/qa/duplicate-check` | `master_person_id` | Per-person: duplicate records? |
| `/qa/edge-gaps` | `master_person_id` | Per-person: missing company links? |
| `/qa/history-integrity` | `master_person_id` | Per-person: history vs actual data? |

See [endpoints.md](endpoints.md) for full response formats and curl examples.

#!/usr/bin/env npx tsx
/**
 * snappy-pipeline/api.ts -- Read-only QA diagnostics for Orbiter enrichment pipeline.
 *
 * Different Xano instance: xh2o-yths-38lt.
 * API group: api:Bd_dCiOz. No authentication required.
 *
 * Usage:
 *   npx tsx api.ts batch-overview           # system-wide health
 *   npx tsx api.ts stuck-analysis           # stuck processing breakdown
 *   npx tsx api.ts queue-health             # queue sizes + oldest entries
 *   npx tsx api.ts crash-patterns           # recent crashes
 *   npx tsx api.ts data-landing 1           # data landing check for person ID
 *   npx tsx api.ts completeness 1           # enrichment completeness for person ID
 *   npx tsx api.ts duplicates 1             # duplicate check for person ID
 *   npx tsx api.ts avatars 1               # avatar health for person ID
 *
 * Or import as module:
 *   import { batchOverview, stuckAnalysis, dataLanding } from "../snappy-pipeline/api.ts";
 */

import { env } from "../snappy-settings/load.ts";
import { realpathSync } from "fs";
import { annotationsForClass } from "../snappy-settings/tool-annotations.ts";
import { refusalTable } from "../snappy-settings/refusal-codes.ts";

const PIPELINE_BASE = "https://xh2o-yths-38lt.n7c.xano.io/api:Bd_dCiOz";

async function qa(path: string) {
  const res = await fetch(`${PIPELINE_BASE}${path}`);
  const data = await res.json();
  if (!res.ok) {
    throw new Error(`Pipeline ${path} failed (${res.status}): ${JSON.stringify(data)}`);
  }
  return data;
}

// --- Public API ---

export async function batchOverview() {
  return qa("/qa/batch-overview");
}

export async function stuckAnalysis() {
  return qa("/qa/stuck-analysis");
}

export async function queueHealth() {
  return qa("/qa/queue-health");
}

export async function crashPatterns(limit = 100) {
  return qa(`/qa/crash-patterns?limit=${limit}`);
}

export async function dataLanding(masterPersonId: number) {
  return qa(`/qa/data-landing-check?master_person_id=${masterPersonId}`);
}

export async function enrichmentCompleteness(masterPersonId: number) {
  return qa(`/qa/enrichment-completeness?master_person_id=${masterPersonId}`);
}

export async function duplicateCheck(masterPersonId: number) {
  return qa(`/qa/duplicate-check?master_person_id=${masterPersonId}`);
}

export async function avatarHealth(masterPersonId: number) {
  return qa(`/qa/avatar-health?master_person_id=${masterPersonId}`);
}

export async function edgeGaps(masterPersonId: number) {
  return qa(`/qa/edge-gaps?master_person_id=${masterPersonId}`);
}

export async function historyIntegrity(masterPersonId: number) {
  return qa(`/qa/history-integrity?master_person_id=${masterPersonId}`);
}

// --- CLI ---
/** WHAT THIS HAND ANSWERS, and what each verb does to the world.
 *  Derived from this file's own CLI dispatch by
 *  `snappy-hands/contract-derive.ts` — a verb the code does not implement is
 *  never declared here. Snappy's daemon reads it (`api.ts contract`) to
 *  validate every call, build the argument words in order, decide whether the
 *  act runs now or stages for the owner, and hand the child exactly the
 *  environment keys named in `requires` — never a value, never anything else.
 *
 *  `backend: "retired"` — this road's backend is BANNED (the ruling of
 *  2026-08-30: never read it, write it, or fall back to it). The verbs are
 *  declared so the census can count the road honestly and Snappy can refuse
 *  it BY NAME; nothing here is callable until the road is rebuilt. */
/** THE HOST-FACING FACTS ⟨lane CONTRACTS N–Z, 2026-09-09⟩. `class` is the
 *  closed effect set snappy-tool-design rule 18 grades; `annotations` are
 *  DERIVED from it by the ONE derivation in
 *  `snappy-settings/tool-annotations.ts`, never written per verb, so a class
 *  and its published hints cannot disagree; `refusals` projects the ONE closed
 *  table in `snappy-settings/refusal-codes.ts`; `requires` is exactly the
 *  credential keys this file's own executable reads name, and nothing else. */
export const HAND_CONTRACT = {
  skill: "snappy-pipeline",
  description: "Read-only QA agent for Orbiter enrichment pipeline data quality auditing. Scans Xano tables on the Orbiter instance (xh2o-yths-38lt) to verify enrichment data landed correctly from raw JSON sources (People Data Labs, Enrich Layer, Fundable/BigQuery) into 20+ join tables. Runs the eight cyborg diagnostics, names the dominant failure source, and gives Mark a prioritized to-do list -- never modifies data. Powers the Orbiter dev update flow inside snappy-client-orbiter. Triggers on: enrichment QA, data quality audit, data landing check, enrichment scan, pipeline health, crash analysis, duplicate detection, queue health, avatar audit, run diagnostics, check the pipeline, what's broken, enrichment report, orbiter pipeline, stuck records, LLM biography failure, master_person, person_enrich_data, edge gaps, history integrity, batch overview, stuck analysis.",
  managed: false,
  requires: [] as string[],
  backend: "retired",
  refusals: refusalTable("backend_retired", "missing_argument", "unknown_verb"),
  verbs: {
    avatars: {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    "batch-overview": {
      args: [], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
    },
    completeness: {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    "crash-patterns": {
      args: ["limit?"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { limit: { type: "integer", description: "Maximum crash patterns returned", default: 20, maximum: 100 } } },
    },
    "data-landing": {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    duplicates: {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    "edge-gaps": {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    history: {
      args: ["master-person-id"], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
      inputSchema: { properties: { "master-person-id": { type: "string", description: "Identity-graph master person id the pipeline joins on" } } },
    },
    "queue-health": {
      args: [], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
    },
    "stuck-analysis": {
      args: [], effect: "read", class: "read", execution: "call", openWorld: true,
      annotations: annotationsForClass("read", { openWorld: true }),
    },
  },
} as const;

if (import.meta.url === `file://${realpathSync(process.argv[1])}` && process.argv[2] === "contract") {
  console.log(JSON.stringify(HAND_CONTRACT, null, 2));
  process.exit(0);
}


if (import.meta.url === `file://${realpathSync(process.argv[1])}`) {
  (async () => {
    const [, , cmd, ...args] = process.argv;

    switch (cmd) {
      case "batch-overview": {
        const data = await batchOverview();
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "stuck-analysis": {
        const data = await stuckAnalysis();
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "queue-health": {
        const data = await queueHealth();
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "crash-patterns": {
        const limit = args[0] ? parseInt(args[0], 10) : 100;
        const data = await crashPatterns(limit);
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "data-landing": {
        if (!args[0]) { console.error("Usage: api.ts data-landing <master_person_id>"); process.exit(1); }
        const data = await dataLanding(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "completeness": {
        if (!args[0]) { console.error("Usage: api.ts completeness <master_person_id>"); process.exit(1); }
        const data = await enrichmentCompleteness(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "duplicates": {
        if (!args[0]) { console.error("Usage: api.ts duplicates <master_person_id>"); process.exit(1); }
        const data = await duplicateCheck(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "avatars": {
        if (!args[0]) { console.error("Usage: api.ts avatars <master_person_id>"); process.exit(1); }
        const data = await avatarHealth(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "edge-gaps": {
        if (!args[0]) { console.error("Usage: api.ts edge-gaps <master_person_id>"); process.exit(1); }
        const data = await edgeGaps(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      case "history": {
        if (!args[0]) { console.error("Usage: api.ts history <master_person_id>"); process.exit(1); }
        const data = await historyIntegrity(parseInt(args[0], 10));
        console.log(JSON.stringify(data, null, 2));
        break;
      }
      default:
        console.log("Usage: npx tsx api.ts [batch-overview|stuck-analysis|queue-health|crash-patterns|data-landing|completeness|duplicates|avatars|edge-gaps|history] ...");
    }
  })();
}

# Data Flow Map -- Raw JSON to Join Tables

This is the complete mapping of where each field in `person_enrich_data` (table 500)
should land after the enrichment pipeline processes it.

Use this as the reference for Diagnostic 2 (Data Landing Verification).

---

## People Data Labs (data_source_id: 91)

JSON field: `person_enrich_data.people_data_labs`

| Source Path | Destination Table | Table ID | Match Key |
|-------------|------------------|----------|-----------|
| `data.skills[]` | skills_join | 325 | skill name or skill_id |
| `data.education[]` | education_experience | 230 | school_name (lowered) |
| `data.experience[]` | work_experience | 147 | title + company_name |
| `data.certifications[]` | certification | 283 | name |
| `data.interests[]` | interest_join | 327 | interest name |
| `data.languages[]` | language_join | 326 | language name |
| `data.profiles[].url` | master_link | 166 | URL |
| `data.emails[].address` | master_email | 155 | email address |
| `data.phone_numbers[]` | master_phone | 151 | phone number |
| `data.full_name` | master_person.name | 139 | -- |

### PDL Education Detail

Each item in `data.education[]` has:
```json
{
  "school": { "name": "Stanford University", "linkedin_url": "..." },
  "degrees": ["MBA"],
  "majors": ["Business"],
  "start_date": "2010",
  "end_date": "2012"
}
```

Dedup check in pipeline: `school_name (lowered) == item.school.name (lowered) AND data_source_id == 91`

**Known issue**: Dedup includes `data_source_id`, so Enrich Layer can add the same school with `data_source_id=94` without being caught.

### PDL Experience Detail

Each item in `data.experience[]` has:
```json
{
  "title": { "name": "CEO" },
  "company": { "name": "Acme Corp", "linkedin_url": "..." },
  "start_date": "2015",
  "end_date": null,
  "is_primary": true
}
```

---

## Enrich Layer (data_source_id: 94)

JSON field: `person_enrich_data.enrich_layer_data`

| Source Path | Destination Table | Table ID | Match Key |
|-------------|------------------|----------|-----------|
| `education[]` | education_experience | 230 | school name |
| `experiences[]` | work_experience | 147 | title + company |
| `certifications[]` | certification | 283 | name |
| `volunteer_work[]` | volunteering | 577 | organization + role |
| `accomplishment_honors_awards[]` | honor | 573 | title |
| `accomplishment_projects[]` | project | 575 | title |
| `accomplishment_publications[]` | publication | 574 | name |
| `skills[]` | skills_join | 325 | skill name |
| `interests[]` | interest_join | 327 | interest name |
| `languages[]` | language_join | 326 | language name |
| `profile_pic_url` | master_avatar | 227 | URL |
| `follower_count` | linkedin_follower | 495 | -- |
| `gender` | master_person.sex | 139 | -- |

### Enrich Layer Unique Data

Enrich Layer is the ONLY source for these tables:
- `volunteering` (577)
- `honor` (573)
- `project` (575)
- `publication` (574)
- `linkedin_follower` (495)

If these tables are empty for a person but `enrich_layer_data` has data in those arrays, that's a clear data landing failure.

### Processing Function

`process-enrich-layer` (Function 12615) handles all Enrich Layer data. **Known issue**: this function has NO per-section try-catches. If one category crashes (e.g., honors with special characters), ALL subsequent categories are skipped.

The processing order matters:
1. Education
2. Positions/experiences
3. Certifications
4. Volunteering
5. Honors/awards
6. Projects
7. Publications
8. Skills
9. Interests
10. Languages
11. Profile picture
12. Follower count
13. Gender

A crash in step 1 (education) would prevent steps 2-13 from running.

---

## Fundable / BigQuery (data_source_id: 89)

JSON field: `person_enrich_data.fundable`

| Source Path | Destination Table | Table ID | Match Key |
|-------------|------------------|----------|-----------|
| `person.email` | master_email | 155 | email address |
| `person.phone` | master_phone | 151 | phone number |
| `person.about` | about_person | 365 | -- |
| `person.profile_image` | master_avatar | 227 | URL |
| `person.bio_links[]` | master_link | 166 | URL |
| `organizations[]` | work_experience | 147 | org name + title |

### Fundable-Specific

Fundable data is sourced from BigQuery (Orbiter has an unlimited flat deal). Best coverage for:
- VCs and investors
- Startup founders
- Funding round data

Company-level Fundable data goes to:
- `company_funding_round` (201) -- individual rounds
- `company_investor` (265) -- investor associations
- `company_financial` (287) -- aggregated financial data

### Avatar from Fundable

**Known issue**: Fundable sometimes returns SVG placeholder images. The `replace-avatar` function should detect these, but the detection may have gaps. Avatar URLs from Fundable should be checked for:
- `.svg` extension
- Known placeholder domains
- Generic/default image patterns

---

## Other Data Sources in person_enrich_data

These fields exist but may not be actively processed by the current pipeline:

| JSON Field | Source | Status |
|-----------|--------|--------|
| `scrapecreator_person` | ScrapeCreator | Legacy? |
| `linkedin_profile` | Direct LinkedIn | Active |
| `contactout_data` | ContactOut | Active for emails |
| `scrapin_data` | Scrapin | Active for LinkedIn data |
| `email_signature` | Email signatures | Passive |
| `raw_linkedin` | Raw LinkedIn HTML | Archive |
| `raw_linkedin_email` | LinkedIn email | Archive |
| `clado_data` | Clado | Unknown |

---

## Verification Query Template

For a single person, here's the query pattern for each source:

```
Given: master_person_id = {id}

Step 1: Get raw data
  query_table: 500 (person_enrich_data)
  filter: master_person_id = {id}

Step 2: Parse JSON blobs
  Count items in each array field

Step 3: Query each destination table
  For each mapping above:
    query_table: {table_id}
    filter: master_person_id = {id}
    (optionally filter by data_source_id)
    Count records

Step 4: Compare
  JSON array count vs table record count
  Flag mismatches
```

### Example Output

```
Person: Jane Doe (ID: 456)

SOURCE: PDL (data_source_id: 91)
  skills[]        → skills_join (325):      JSON: 22, Table: 22  ✓
  education[]     → education_exp (230):    JSON: 3,  Table: 3   ✓
  experience[]    → work_exp (147):         JSON: 7,  Table: 7   ✓
  certifications[]→ certification (283):    JSON: 1,  Table: 0   MISSING
  interests[]     → interest_join (327):    JSON: 5,  Table: 5   ✓
  emails[]        → master_email (155):     JSON: 2,  Table: 2   ✓
  phones[]        → master_phone (151):     JSON: 1,  Table: 1   ✓
  profiles[]      → master_link (166):      JSON: 4,  Table: 3   PARTIAL

SOURCE: Enrich Layer (data_source_id: 94)
  education[]     → education_exp (230):    JSON: 4,  Table: 4   ✓
  experiences[]   → work_exp (147):         JSON: 8,  Table: 8   ✓
  volunteer_work[]→ volunteering (577):     JSON: 2,  Table: 0   MISSING
  honors[]        → honor (573):            JSON: 1,  Table: 0   MISSING
  projects[]      → project (575):          JSON: 3,  Table: 0   MISSING

VERDICT: 3 categories MISSING from Enrich Layer
  Likely cause: process-enrich-layer crashed after certifications
  (volunteering, honors, projects come after certifications in processing order)
```

# The Eight Diagnostics -- Detailed Logic

## Table of Contents
- [Diagnostic 1: Enrichment Completeness](#diagnostic-1-enrichment-completeness)
- [Diagnostic 2: Data Landing Verification](#diagnostic-2-data-landing-verification)
- [Diagnostic 3: Avatar Health](#diagnostic-3-avatar-health)
- [Diagnostic 4: Crash Pattern Analysis](#diagnostic-4-crash-pattern-analysis)
- [Diagnostic 5: Queue Health](#diagnostic-5-queue-health)
- [Diagnostic 6: Duplicate Detection](#diagnostic-6-duplicate-detection)
- [Diagnostic 7: Edge Resolution Gaps](#diagnostic-7-edge-resolution-gaps)
- [Diagnostic 8: Enrich History Integrity](#diagnostic-8-enrich-history-integrity)

---

## Diagnostic 1: Enrichment Completeness

**Question**: Did all data sources run, succeed, and complete for this person?

**Tables to query**:
- `enrich_history_person` (160) -- one record per person per data source attempt
- `person_enrich_data` (500) -- raw JSON blobs from each source
- `master_person` (139) -- visibility flag, processing state

**Logic**:

```
For each master_person:

1. Query enrich_history_person WHERE master_person_id = {id}
   Group by data_source_id (79, 89, 91, 94, 97)

2. For each expected data source, check:
   - Does a history record exist? (was it attempted?)
   - enrich_success = true? (did it succeed?)
   - processing = false? (is it done or stuck?)
   - processing_time > 0? (did it actually run?)

3. Query person_enrich_data WHERE master_person_id = {id}
   Check which JSON fields are populated:
   - people_data_labs (source 91)
   - enrich_layer_data (source 94)
   - fundable (source 89)
   - scrapecreator_person, linkedin_profile, contactout_data, etc.

4. Check master_person:
   - visibility = false? (Phase 2 never completed)
   - Is there a queue_enrich_person record? (still waiting)
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| Person exists but no enrich_history records at all | HIGH |
| Data source attempted but enrich_success = false | HIGH |
| processing = true for > 1 hour (stuck) | HIGH |
| visibility = false (Phase 2 incomplete) | HIGH |
| person_enrich_data exists but JSON field is null for a source that history says succeeded | CRITICAL |
| History record exists for source but corresponding JSON blob is empty | HIGH |

---

## Diagnostic 2: Data Landing Verification

**Question**: Did the raw JSON arrays in person_enrich_data actually produce records in the destination join tables?

This is THE core diagnostic. See [data-flow-map.md](data-flow-map.md) for the complete mapping.

**Tables to query**:
- `person_enrich_data` (500) -- source JSON
- All destination tables: 325, 230, 147, 283, 573, 577, 575, 574, 327, 326, 166, 155, 151, 227

**Logic**:

```
For each master_person with person_enrich_data:

--- PDL (people_data_labs JSON blob) ---

1. Parse people_data_labs.data.skills[]
   Count items in JSON array
   Query skills_join (325) WHERE master_person_id = {id} AND data_source_id = 91
   Compare: JSON count vs table count
   Flag if JSON has items but table has fewer/none

2. Parse people_data_labs.data.education[]
   Count items
   Query education_experience (230) WHERE master_person_id = {id} AND data_source_id = 91
   Compare counts
   For each education item, check if school_name exists in table (case-insensitive)

3. Parse people_data_labs.data.experience[]
   Count items
   Query work_experience (147) WHERE master_person_id = {id} AND data_source_id = 91
   Compare counts

4. Parse people_data_labs.data.certifications[]
   Count items
   Query certification (283) WHERE master_person_id = {id} AND data_source_id = 91
   Compare counts

5. Parse people_data_labs.data.interests[]
   Query interest_join (327) WHERE master_person_id = {id}

6. Parse people_data_labs.data.languages[]
   Query language_join (326) WHERE master_person_id = {id}

7. Parse people_data_labs.data.profiles[].url
   Query master_link (166) WHERE master_person_id = {id}
   Check if profile URLs are present

8. Parse people_data_labs.data.emails[].address
   Query master_email (155) WHERE master_person_id = {id}

9. Parse people_data_labs.data.phone_numbers[]
   Query master_phone (151) WHERE master_person_id = {id}

--- Enrich Layer (enrich_layer_data JSON blob) ---

10. Parse enrich_layer_data.education[]
    Query education_experience (230) WHERE master_person_id = {id} AND data_source_id = 94

11. Parse enrich_layer_data.experiences[]
    Query work_experience (147) WHERE master_person_id = {id} AND data_source_id = 94

12. Parse enrich_layer_data.certifications[]
    Query certification (283) WHERE master_person_id = {id} AND data_source_id = 94

13. Parse enrich_layer_data.volunteer_work[]
    Query volunteering (577) WHERE master_person_id = {id}

14. Parse enrich_layer_data.accomplishment_honors_awards[]
    Query honor (573) WHERE master_person_id = {id}

15. Parse enrich_layer_data.accomplishment_projects[]
    Query project (575) WHERE master_person_id = {id}

16. Parse enrich_layer_data.accomplishment_publications[]
    Query publication (574) WHERE master_person_id = {id}

17. Parse enrich_layer_data.skills[]
    Query skills_join (325) WHERE master_person_id = {id} AND data_source_id = 94

18. Check enrich_layer_data.profile_pic_url
    Query master_avatar (227) WHERE master_person_id = {id}

19. Check enrich_layer_data.follower_count
    Query linkedin_follower (495) WHERE master_person_id = {id}

--- Fundable (fundable JSON blob) ---

20. Parse fundable.person.email
    Query master_email (155) WHERE master_person_id = {id}

21. Parse fundable.person.phone
    Query master_phone (151) WHERE master_person_id = {id}

22. Parse fundable.person.about
    Query about_person (365) WHERE master_person_id = {id}

23. Parse fundable.person.profile_image
    Query master_avatar (227) WHERE master_person_id = {id}

24. Parse fundable.person.bio_links[]
    Query master_link (166) WHERE master_person_id = {id}

25. Parse fundable.organizations[]
    Query work_experience (147) WHERE master_person_id = {id} AND data_source_id = 89
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| JSON array has N items, table has 0 records | CRITICAL |
| JSON array has N items, table has fewer than N | HIGH |
| JSON has email/phone but not in master_email/phone | HIGH |
| JSON has profile URL but not in master_link | MEDIUM |
| JSON has avatar URL but master_avatar missing or placeholder | HIGH |

**Output format**: Per-person "data landing card" showing:
```
Person: John Smith (ID: 12345)
PDL Skills:     JSON: 15  →  Table: 12  (3 MISSING)
PDL Education:  JSON: 3   →  Table: 3   ✓
PDL Work:       JSON: 5   →  Table: 5   ✓
PDL Certs:      JSON: 2   →  Table: 0   (2 MISSING) ← CRITICAL
EL Education:   JSON: 4   →  Table: 3   (1 MISSING)
EL Volunteering: JSON: 1  →  Table: 1   ✓
Fundable Work:  JSON: 3   →  Table: 2   (1 MISSING)
```

---

## Diagnostic 3: Avatar Health

**Question**: Does this person have a real avatar, or an SVG placeholder?

**Tables to query**:
- `master_avatar` (227) -- is_placeholder flag, url field
- `master_person` (139) -- avatar field
- `person_enrich_data` (500) -- raw avatar URLs from sources

**Logic**:

```
For each master_person:

1. Query master_avatar (227) WHERE master_person_id = {id} AND main = true
   - Does a record exist?
   - is_placeholder = true? → person showing placeholder
   - URL ends in .svg? → SVG placeholder leaked through
   - URL contains known placeholder patterns?
     (e.g., "default-avatar", "no-photo", "placeholder", "generic")

2. Check person_enrich_data for available real images:
   - enrich_layer_data.profile_pic_url -- real URL?
   - fundable.person.profile_image -- real URL?
   - people_data_labs.data.profile_pic_url -- if exists

3. Compare: if any source has a real image URL but master_avatar
   is placeholder or missing → data didn't land
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| master_avatar.is_placeholder = true but real URL exists in enrich data | HIGH |
| master_avatar URL ends in .svg | HIGH |
| No master_avatar record at all but enrich data has image URL | HIGH |
| Multiple avatar records but NONE has main=true | HIGH |
| No avatar anywhere (not in enrich data either) | LOW |

**LIVE EXAMPLE**: Josh Diamond (ID: 1) has 4 master_avatar records, all with
`main=false` and `is_placeholder=false`. The master_person.avatar field IS
populated with a vault URL, but no avatar record claims to be the main one.
This suggests the `main` flag logic has a gap.

---

## Diagnostic 4: Crash Pattern Analysis

**Question**: Which sections of the pipeline crash most, and what data triggers failures?

**Tables to query**:
- `crash_log` (542) -- unified crash capture (new table, may have 0 records until re-run)
- `log_enrichment_person` (579) -- older log with error_description
- `error_try_catch` (488) -- legacy error logging

**Logic**:

```
1. Query crash_log (542):
   - Group by note field (section identifier)
   - Group by error_message
   - Count occurrences of each error
   - Find top 10 most common errors
   - Find master_person_ids that crash most frequently

2. If crash_log is empty (0 records), fall back to:
   - log_enrichment_person (579): filter where error_description is not empty
   - error_try_catch (488): scan for patterns

3. For each crash pattern:
   - Which section failed? (note field tells you)
   - What was the error message?
   - How many people affected?
   - Is it a data-dependent crash (specific characters, encodings)?
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| crash_log has 0 records (table exists but empty) | INFO (expected until re-run) |
| Same error appears > 10 times | HIGH |
| Single person crashes in > 3 sections | HIGH |
| "CRASH - process enrich layer data" (Section 6) | HIGH (known issue: no sub-try-catches) |
| Crash in Section 10 (complete enrichment) | CRITICAL (visibility stuck false) |

**Note**: crash_log was created March 21, 2026 and has 0 records. It will populate when Mark re-runs enrichment on the ~2,000 existing people. Until then, this diagnostic relies on legacy log tables.

---

## Diagnostic 5: Queue Health

**Question**: How big is the enrichment backlog, and is anything stuck?

**Tables to query**:
- `queue_enrich_person` (582) -- people waiting for Phase 2
- `queue_enrich_company` (583) -- companies waiting for Phase 2

**Logic**:

```
1. Count total records in each queue table

2. Check for stuck entries:
   - processing = true for > 1 hour
   - Same record queued multiple times

3. Age distribution:
   - How many were queued today?
   - How many are > 1 day old?
   - How many are > 7 days old?
   - What's the oldest entry?

4. Deep research flag:
   - queue_enrich_person has deep_research field
   - How many are queued for deep research vs regular enrichment?
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| processing = true for > 1 hour | HIGH |
| Queue > 1,000 entries | MEDIUM (backlog building) |
| Oldest entry > 7 days | MEDIUM |
| Queue growing faster than draining | HIGH |

---

## Diagnostic 6: Duplicate Detection

**Question**: Are there duplicate records in join tables for the same person?

**Tables to query**:
- `skills_join` (325) -- duplicate skill_id per person
- `education_experience` (230) -- duplicate school_name per person
- `work_experience` (147) -- duplicate title + company per person
- `certification` (283) -- duplicate name per person

**Logic**:

```
For each master_person (or batch):

1. Skills duplicates:
   Query skills_join WHERE master_person_id = {id}
   Group by skill_id
   Flag any skill_id appearing > 1 time

2. Education duplicates:
   Query education_experience WHERE master_person_id = {id}
   Group by LOWER(school_name)
   Flag any school appearing > 1 time
   NOTE: Duplicates happen WITHIN same source too, not just cross-source!
   (LIVE EXAMPLE: Josh Diamond ID:1 has 3 identical "hunter college"
    records, ALL with data_source_id=91 and same edge_uuid.
    This means re-runs of Phase 2 create duplicates even with dedup.)

3. Work experience duplicates:
   Query work_experience WHERE master_person_id = {id}
   Group by LOWER(title) + LOWER(company_name)
   Flag any combination appearing > 1 time

4. Certification duplicates:
   Query certification WHERE master_person_id = {id}
   Group by LOWER(name)
   Flag any appearing > 1 time
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| Same skill appears 2+ times for same person | MEDIUM |
| Same school appears 2+ times (likely cross-source) | MEDIUM |
| Same job title + company appears 2+ times | HIGH |
| Same certification appears 2+ times | MEDIUM |

**Known root cause**: Education dedup in `run-base-person-enrich` Section 5 queries with `data_source_id == 91`, so PDL and Enrich Layer can both add "Stanford University" without the dedup catching it.

---

## Diagnostic 7: Edge Resolution Gaps

**Question**: Do work/education records have proper links to their companies/schools?

**Tables to query**:
- `education_experience` (230) -- master_company_id field
- `work_experience` (147) -- master_company_id field

**Logic**:

```
1. Education with unresolved schools:
   Query education_experience WHERE master_person_id = {id}
     AND master_company_id IS NULL (or 0)
     AND school_name IS NOT NULL AND school_name != ""
   These are schools that the "resolve edges" step (Phase 2 Section 8)
   failed to link to a master_company record.

2. Work with unresolved companies:
   Query work_experience WHERE master_person_id = {id}
     AND master_company_id IS NULL (or 0)
     AND company_name IS NOT NULL AND company_name != ""
   Same issue -- company name exists but wasn't resolved.

3. Check if matching companies exist:
   For unresolved company_name, search master_company WHERE
     name LIKE company_name (or domain match)
   If company exists but wasn't linked → resolution bug

4. LinkedIn URL available but not used:
   work_experience or education_experience has linkedin_url
   but master_company_id is still null
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| Work record has company_name but no master_company_id, AND matching company exists | HIGH |
| Education record has school_name but no master_company_id | MEDIUM |
| Record has LinkedIn URL but company wasn't resolved | HIGH |
| > 50% of work records for a person have no company link | HIGH |

---

## Diagnostic 8: Enrich History Integrity

**Question**: Is the enrichment tracking system reliable as a source of truth?

**Tables to query**:
- `enrich_history_person` (160) -- success/fail records per data source
- `person_enrich_data` (500) -- actual data

### IMPORTANT: Data Source ID Mapping (verified from live data)

The enrich_history does NOT use source IDs 91 (PDL) or 89 (Fundable) directly.
Here's what actually appears in enrich_history_person:

| data_source_id | Source Name | What It Tracks |
|---------------|-------------|---------------|
| 79 | Base Person Enrich | Umbrella -- covers the whole Phase 2 run |
| 86 | LLM Biography | Deep bio generation (Claude/OpenRouter call) |
| 7 | Twitter | Twitter profile data |
| 11 | LinkedIn | LinkedIn profile image |
| 92 | ScrapeCreators LinkedIn | LinkedIn scraping |
| 94 | Enrich Layer | Enrich Layer API call (often only logged on FAILURE) |
| 91 | PDL | People Data Labs (0 records found -- NOT tracked in history) |
| 89 | Fundable | Fundable/BigQuery (0 records found -- NOT tracked in history) |

**Key insight**: PDL and Fundable calls are wrapped inside the "Base Person Enrich" (79)
umbrella and are NOT individually tracked. To verify whether PDL/Fundable data was
fetched, you must check `person_enrich_data` JSON fields directly, not enrich_history.

**Logic**:

```
For each master_person:

1. Excessive history records:
   Query enrich_history_person WHERE master_person_id = {id}
   Count total records
   Flag if > 5 records (indicates re-runs creating duplicates)
   Group by data_source_id -- flag any source with > 2 records
   (LIVE EXAMPLE: Josh Diamond ID:1 has 11 history records,
    5 of which are data_source_id=86 LLM Biography)

2. Stuck processing:
   Query enrich_history_person WHERE processing = true
   Check created_at -- if > 1 hour ago, it's stuck
   (LIVE EXAMPLE: Josh Diamond has record ID 162 stuck with
    processing=true, enrich_success=false permanently)

3. Base enrich says success but data missing:
   Find records WHERE data_source_id = 79 AND enrich_success = true
   Then check person_enrich_data:
     - people_data_labs JSON populated? (PDL ran)
     - enrich_layer_data JSON populated? (EL ran)
     - fundable JSON populated? (Fundable ran, or {} if no coverage)
   If base says success but these are empty → data loss

4. Enrich Layer failure tracking:
   Find records WHERE data_source_id = 94
   ALL 19 records in live data have enrich_success = false
   Cross-check: does person_enrich_data.enrich_layer_data have data?
   If EL data exists but history says failure → tracking bug

5. No history at all:
   Person exists in master_person but has 0 enrich_history records
   → Phase 2 never ran (check queue_enrich_person)
```

**Findings to flag**:

| Finding | Severity |
|---------|----------|
| > 5 history records for same person (duplicate runs) | HIGH |
| processing = true for > 1 hour (stuck permanently) | HIGH |
| Base enrich success but person_enrich_data fields empty | CRITICAL |
| EL history says failure but enrich_layer_data has data | MEDIUM (tracking bug) |
| 0 history records but person exists | HIGH |
| data_source_id 86 appears > 3 times (LLM bio re-runs) | MEDIUM |

---

## Batch Execution Pattern

To scan N people efficiently:

```
1. Query master_person with pagination (offset/limit)
   Default batch: 100 people
   Sort by: created_at DESC (newest first) or updated_at DESC

2. For each person in batch:
   a. Fetch person_enrich_data (1 query)
   b. Fetch enrich_history_person records (1 query)
   c. Run Diagnostics 1, 2, 3, 6, 7, 8 per person
   d. Collect findings

3. Run Diagnostics 4 and 5 once (they're global, not per-person):
   a. crash_log aggregation
   b. queue counts

4. Aggregate findings into report:
   - Group by diagnostic
   - Within each diagnostic, group by severity
   - Batch similar findings (e.g., "15 persons missing skills")
   - Output per-person cards for CRITICAL/HIGH findings
```

**Estimated queries per person**: 8-12 (depending on which diagnostics run)
**Estimated queries for batch of 100**: ~1,000 + 2 global queries

# QA Diagnostic Endpoints

All endpoints live in the **Robert API Group** (`Bd_dCiOz`, ID: 1261).
Base URL: `https://xh2o-yths-38lt.n7c.xano.io/api:Bd_dCiOz`

No authentication required (internal API group).

---

## Endpoint Registry

| # | Endpoint | Diagnostic | API ID | Method | Status |
|---|----------|-----------|--------|--------|--------|
| 1 | `/qa/data-landing-check` | 2. Data Landing | 8240 | GET `?master_person_id=N` | LIVE |
| 2 | `/qa/enrichment-completeness` | 1. Completeness | 8243 | GET `?master_person_id=N` | LIVE |
| 3 | `/qa/avatar-health` | 3. Avatar Health | 8244 | GET `?master_person_id=N` | LIVE |
| 4 | `/qa/crash-patterns` | 4. Crashes | 8248 | GET `?limit=N` | LIVE |
| 5 | `/qa/queue-health` | 5. Queue Health | 8241 | GET (no params) | LIVE |
| 6 | `/qa/duplicate-check` | 6. Duplicates | 8245 | GET `?master_person_id=N` | LIVE |
| 7 | `/qa/edge-gaps` | 7. Edge Gaps | 8246 | GET `?master_person_id=N` | LIVE |
| 8 | `/qa/history-integrity` | 8. History | 8247 | GET `?master_person_id=N` | LIVE |
| 9 | `/qa/batch-overview` | System-wide | 8250 | GET (no params) | LIVE |
| 10 | `/qa/stuck-analysis` | Stuck Breakdown | 8251 | GET (no params) | LIVE |

---

## 1. `/qa/data-landing-check` -- LIVE

**API ID**: 8240 | **Diagnostic**: 2 (Data Landing Verification)

### Request
```
GET /qa/data-landing-check?master_person_id=1
```

### What It Does
1. Fetches `person_enrich_data` (table 500) for the given person
2. Parses three JSON blobs: `people_data_labs`, `enrich_layer_data`, `fundable`
3. Counts arrays in each source (skills, education, experience, certs, etc.)
4. Queries 14 join tables for actual record counts
5. **Combines PDL + EL counts** for shared tables (skills, education, work, certs)
6. Compares combined JSON counts vs table counts with **bloat detection**
7. Returns **severity-tagged issue messages** for every non-ok category
8. Detects empty source blobs and missing Phase 2 runs

### Response Format
```json
{
  "success": true,
  "master_person_id": 1,
  "source_blobs": {
    "pdl_exists": true, "pdl_total_items": 46,
    "el_exists": true, "el_total_items": 39,
    "fundable_exists": true
  },
  "pdl_source": {
    "skills": 33, "education": 1, "experience": 5,
    "certifications": 0, "interests": 0, "languages": 0,
    "profiles": 2, "emails": 5, "phone_numbers": 0
  },
  "enrich_layer_source": {
    "education": 1, "experiences": 5, "certifications": 0,
    "volunteer_work": 0, "honors_awards": 0, "projects": 0,
    "publications": 0, "skills": 33, "interests": 0, "languages": 0
  },
  "fundable_source": {
    "has_email": 0, "has_phone": 0, "has_about": 0,
    "has_profile_image": 0, "bio_links": 0, "organizations": 0
  },
  "table_counts": {
    "skills_join": 66, "education_experience": 17, "work_experience": 6,
    "certification": 0, "volunteering": 0, "honor": 0, "project": 0,
    "publication": 0, "interest_join": 0, "language_join": 0,
    "master_email": 5, "master_phone": 0, "master_link": 19, "master_avatar": 20
  },
  "comparisons": [
    { "source": "combined", "category": "skills", "json_count": 66, "table_count": 66, "status": "ok" },
    { "source": "combined", "category": "education", "json_count": 2, "table_count": 17, "status": "bloat" },
    { "source": "combined", "category": "work", "json_count": 10, "table_count": 6, "status": "partial" }
  ],
  "summary": {
    "total_categories": 3,
    "ok": 1, "missing": 0, "partial": 1, "bloat": 1,
    "issues": [
      "BLOAT: education has 17 table records from 2 JSON items (re-run duplication)",
      "MEDIUM: work -- partial landing (6 of 10 landed)"
    ]
  }
}
```

### Status Values
- `ok` -- table count >= JSON count and <= 3x JSON count (data landed correctly)
- `bloat` -- table count > 3x JSON count (re-run duplication creating excess records)
- `partial` -- table count > 0 but < JSON count (some missing)
- `missing` -- JSON has items but table has 0 (data didn't land at all)

### Combined Source Counts
For shared tables (skills, education, work, certs), PDL + EL JSON counts are **summed** before comparison. This prevents false "ok" when table has 17 records but only 1 came from PDL and 1 from EL (should be ~2, not 17).

### Issue Messages (Severity-Tagged)
Every non-ok comparison now generates a severity-tagged issue message:
- `CRITICAL: [category] -- N in JSON but 0 in table (data not landing)` -- for `missing` status
- `BLOAT: [category] has N table records from M JSON items (re-run duplication)` -- for `bloat` status
- `MEDIUM: [category] -- partial landing (N of M landed)` -- for `partial` status
- EL-only categories (volunteering, honors, projects, publications): `"EL [category]: not landed - process-enrich-layer likely crashed"`
- Empty source blobs: `"PDL blob exists but contains zero data arrays"` / `"CRITICAL: ALL source blobs are null -- Phase 2 may never have run"`

### Error Responses
- `400` -- missing or invalid master_person_id
- `404` -- no person_enrich_data record found

### Curl Test
```bash
curl -s "https://xh2o-yths-38lt.n7c.xano.io/api:Bd_dCiOz/qa/data-landing-check?master_person_id=1" | python3 -m json.tool
```

---

## 2. `/qa/enrichment-completeness` -- LIVE

**API ID**: 8243 | **Diagnostic**: 1 (Enrichment Completeness)

### Request
```
GET /qa/enrichment-completeness?master_person_id=1
```

### What It Does
- Gets `master_person` record (visibility flag, name)
- Gets all `enrich_history_person` records (up to 100)
- Checks `person_enrich_data` for PDL, Enrich Layer, Fundable blobs
- Detects stuck processing with **source names and age in hours**
- Aggregates stuck count by source name
- Detects duplicate history records with source names
- Returns severity-tagged issues with actual counts

### Key Response Fields
- `person_name`, `visibility` -- person identity + pipeline status
- `data_sources.has_pdl/has_enrich_layer/has_fundable` -- which JSON blobs exist
- `history.total_records/success/failed` -- enrich history stats
- `history.stuck_processing[]` -- entries with `source_name`, `stuck_hours`
- `history.stuck_by_source` -- aggregated stuck count per source (e.g., `{"llm_biography": 5, "base_person_enrich": 19}`)
- `history.duplicate_sources[]` -- duplicates with `source_name`
- `issues[]` -- severity-tagged: "HIGH: 25 entries stuck processing"

---

## 3. `/qa/avatar-health` -- LIVE

**API ID**: 8244 | **Diagnostic**: 3 (Avatar Health)

### Request
```
GET /qa/avatar-health?master_person_id=1
```

### What It Does
- Gets all `master_avatar` records for the person
- Checks `is_placeholder` and `main` flags
- **Detects `main_on_placeholder`** -- placeholder avatars that have `main=true` (systemic bug)
- Checks if `enrich_layer_data.profile_pic_url` has a real URL available
- Gets `master_person.avatar` field value
- Flags mixed placeholder/real avatar states for cleanup

### Key Response Fields
- `avatar_count`, `has_main`, `placeholders`, `real_images`
- `main_on_placeholder` -- count of placeholders with main=true (systemic bug indicator)
- `enrich_layer_pic.available/url` -- whether EL has a real pic URL
- `avatars[]` -- full list with id, url, is_placeholder, main

### Issues Detected
- No avatar records exist
- No avatar has main=true (but avatars exist)
- **SYSTEMIC BUG**: placeholder avatar(s) have main=true
- Placeholder exists but real URL available in enrich_layer_data
- Mixed placeholder and real avatars (placeholders should be cleaned up)

---

## 4. `/qa/crash-patterns` -- LIVE

**API ID**: 8248 | **Diagnostic**: 4 (Crash Pattern Analysis)

### Request
```
GET /qa/crash-patterns?limit=100
```

### What It Does
- Counts total crash_log records
- Gets recent crashes sorted by created_at desc
- Aggregates by function_name and error_message
- Note: crash_log is currently empty (awaiting Mark's re-run with crash capture)

### Key Response Fields
- `total_crashes`, `by_function`, `by_error`

---

## 5. `/qa/queue-health` -- LIVE

**API ID**: 8241 | **Diagnostic**: 5 (Queue Health)

### Request
```
GET /qa/queue-health
```

### What It Does
- Counts `queue_enrich_person` and `queue_enrich_company` totals
- Finds oldest entry in each queue with **age calculation** (hours + days)
- Counts entries stuck processing
- Graduated thresholds: person >500 MEDIUM, >1000 HIGH; company >5000 MEDIUM, >10000 HIGH
- Flags oldest entries > 7 days as concerning

### Key Response Fields
- `person_queue.total/processing/oldest/oldest_age_hours/oldest_age_days`
- `company_queue.total/processing/oldest/oldest_age_hours/oldest_age_days`
- `checked_at` -- timestamp when check was run

---

## 6. `/qa/duplicate-check` -- LIVE

**API ID**: 8245 | **Diagnostic**: 6 (Duplicate Detection)

### Request
```
GET /qa/duplicate-check?master_person_id=1
```

### What It Does
- Checks education_experience for same school_name appearing 2+ times
- Checks work_experience for same title+company appearing 2+ times
- Checks skills_join for same skill_id appearing 2+ times
- Groups duplicates by school/role with **counts per group**
- Adds `source_name` to each duplicate entry
- Calculates `duplicate_pct` for education (detects re-run bloat)
- Flags CRITICAL when >80% of education records are duplicates

### Key Response Fields
- `education.total/duplicate_count/duplicate_pct` -- counts + percentage
- `education.counts_by_school` -- records per school (e.g., `{"hunter college": 17}`)
- `education.duplicates[]` -- each with `source_name` (pdl/enrich_layer)
- `work.total/duplicate_count/counts_by_role/duplicates[]`
- `skills.total/duplicate_count/duplicates[]`
- `total_duplicates` -- aggregate count
- `issues[]` -- severity-tagged with counts: "HIGH: 16 education duplicates across 17 total records"

### Bloat Detection
- `duplicate_pct > 80%` triggers CRITICAL: "Education table is mostly duplicates - likely re-run bloat"
- Person 1 example: 17 education records, 16 duplicates (94.1%), all "Hunter College"

---

## 7. `/qa/edge-gaps` -- LIVE

**API ID**: 8246 | **Diagnostic**: 7 (Edge Resolution Gaps)

### Request
```
GET /qa/edge-gaps?master_person_id=1
```

### What It Does
- Finds work_experience records where master_company_id == 0
- Finds education_experience records where master_company_id == 0
- Returns unlinked records with company_name for manual resolution

### Key Response Fields
- `work.total/unlinked/gaps[]`, `education.total/unlinked/gaps[]`

---

## 8. `/qa/history-integrity` -- LIVE

**API ID**: 8247 | **Diagnostic**: 8 (Enrich History Integrity)

### Request
```
GET /qa/history-integrity?master_person_id=1
```

### What It Does
- Gets all enrich_history_person records (up to 100)
- Counts records per data_source_id with **named keys** (e.g., `86_llm_biography`)
- Detects stuck processing entries with `source_name`
- Cross-references success=true against actual data in person_enrich_data
- Checks all 9 known sources for excessive duplicates (>3 records per source)
- Returns severity-tagged issue messages with actual counts

### Key Response Fields
- `source_counts` -- named keys: `{"86_llm_biography": 12, "79_base_person_enrich": 8, ...}`
- `stuck_processing[]` -- entries with `source_name` field
- `success_but_empty[]` -- history says success but data is missing
- `excessive_duplicates[]` -- all 9 sources checked (was only 3)
- `issues[]` -- descriptive: "Stuck processing: 5 entries still marked processing=true"

---

## 9. `/qa/batch-overview` -- LIVE

**API ID**: 8250 | **Diagnostic**: System-wide health dashboard

### Request
```
GET /qa/batch-overview
```

### What It Does
- Counts total master_person records and visibility=false with **stuck_pct**
- Counts person_enrich_data records (detects people missing enrich data)
- Counts all enrich_history records, calculates avg per person, counts stuck processing
- Counts master_avatar total, placeholders, and main_on_placeholder (systemic bug)
- Gets queue sizes for person and company queues
- Counts crash_log records
- Returns 20 most recent stuck people (visibility=false) with names
- Returns 10 most recent stuck history entries **with source_name**
- All issue messages are **dynamic with actual counts**
- Graduated severity thresholds for visibility_false and stuck_processing

### Key Response Fields
- `people.total/visibility_false/stuck_pct/has_enrich_data/missing_enrich_data`
- `history.total_records/stuck_processing/avg_per_person`
- `avatars.total/placeholders/placeholder_pct/main_on_placeholder`
- `queues.person/company`
- `crashes` -- total crash_log count
- `stuck_people_sample[]` -- 20 most recent visibility=false people
- `stuck_history_sample[]` -- 10 most recent with `source_name` (e.g., "llm_biography")
- `health` -- HEALTHY or ISSUES_FOUND
- `issues[]` -- dynamic severity-tagged: "HIGH: 197 people stuck with visibility=false (9.5%)"

### Issue Severity Thresholds
- visibility_false: >10 MEDIUM, >50 HIGH
- stuck_processing: >20 MEDIUM, >100 HIGH
- missing_enrich_data: any = MEDIUM
- placeholder main bug: any = HIGH

### Key Findings (as of 2026-03-22)
- 2,073 people total, 197 stuck (9.5%)
- 1,162 placeholder avatars (44.8%), ALL with main=true (systemic bug)
- 686 stuck processing entries, avg 4.1 history records per person
- 5 people have no person_enrich_data record at all

---

## 10. `/qa/stuck-analysis` -- LIVE

**API ID**: 8251 | **Diagnostic**: Stuck processing deep dive

### Request
```
GET /qa/stuck-analysis
```

### What It Does
- Breaks down stuck processing entries by data_source_id (9 known sources + other)
- Identifies which sources are failing and how often
- **Dynamically calculates** dominant failure source (iterates all sources, finds max)
- Returns 20 most recent stuck entries **with `source_name`** (e.g., "llm_biography")
- Counts total failed (enrich_success=false, processing=false) entries
- All issue messages are **dynamic with actual counts and percentages**

### Key Response Fields
- `total_stuck_processing` -- total entries with processing=true
- `total_failed` -- entries that failed but properly closed (not stuck)
- `visibility_false` -- people stuck in pipeline
- `stuck_by_source` -- breakdown by data source:
  - `79_base_person_enrich`, `86_llm_biography`, `94_enrich_layer`
  - `95_social_insights`, `96_scrapecreators_youtube`, `8_crunchbase`
  - `7_twitter`, `11_linkedin`, `92_scrapecreators`
  - `other` -- any sources not in the tracked list
- `dominant_failure.source/count/pct_of_total` -- dynamically calculated (not hardcoded)
- `recent_stuck[]` -- 20 most recent with `master_person_id`, `data_source_id`, and **`source_name`**
- `issues[]` -- dynamic severity-tagged: "HIGH: N stuck processing entries" with actual counts

### Key Findings (as of 2026-03-22)
- LLM Biography (86): 431 stuck (62.8%) -- dominant failure
- Crunchbase (8): 117 stuck (17.1%)
- Base Person Enrich (79): 43 stuck (6.3%)
- ScrapeCreators Youtube (96): 35 stuck
- ScrapeCreators LinkedIn (92): 30 stuck
- Sources 86 (LLM Biography) + 95 (Social Insights) always fail together -- linked processes

---

## Using Endpoints from the Skill

### Via curl (testing)
```bash
curl -s "https://xh2o-yths-38lt.n7c.xano.io/api:Bd_dCiOz/qa/data-landing-check?master_person_id=1" | python3 -m json.tool
```

### Updating Endpoints
All endpoints use the `update_endpoint` MCP tool with `api_id` from the registry above:
```
mcp__xano-mcp__execute({
  tool_id: "update_endpoint",
  arguments: { api_id: 8240, api_group_id: 1261, xanoscript: "..." }
})
```

/**
 * COVERAGE FOR SNAPPY-PIPELINE'S DECLARED REFUSAL CODES
 * (snappy-tool-design rule 33: "refusal codes form one closed table and each
 * row has coverage").
 *
 * Two things are graded here, and the second is the one that matters. The
 * first is that the hand's table is a PROJECTION of the collection's one
 * closed table in snappy-settings/refusal-codes.ts — same object, not a copy
 * that can drift. The second is that every declared code is GROUNDED: the
 * evidence that justified declaring it is re-checked here, because a refusal
 * code with no path that emits it is a branch the reader waits for and never
 * sees, and a table of those passes a lint while teaching a lie.
 *
 * The code list is spelled out rather than read from the contract: a test that
 * iterates the thing it grades passes for an empty table.
 */
import { strict as assert } from "node:assert";
import { test } from "node:test";
import { readFileSync } from "node:fs";
import { join, dirname } from "node:path";
import { fileURLToPath } from "node:url";
import { HAND_CONTRACT } from "./api.ts";
import { REFUSAL_CODES } from "../snappy-settings/refusal-codes.ts";

const SOURCE = readFileSync(join(dirname(fileURLToPath(import.meta.url)), "api.ts"), "utf8");

/** Every refusal code snappy-pipeline declares. */
const DECLARED = [
  "backend_retired",
  "missing_argument",
  "unknown_verb",
] as const;

test("snappy-pipeline declares exactly these refusal codes", () => {
  assert.deepEqual(Object.keys(HAND_CONTRACT.refusals).sort(), [...DECLARED].sort());
});

test("every declared code is the SAME row as the one closed table's, never a copy", () => {
  const table = HAND_CONTRACT.refusals as Record<string, unknown>;
  for (const code of DECLARED) {
    assert.equal(table[code], REFUSAL_CODES[code], `${code} is not the shared row`);
  }
});

test("backend_retired is grounded: the contract declares the retired road", () => {
  assert.equal((HAND_CONTRACT as { backend?: string }).backend, "retired");
});

test("missing_argument is grounded: at least one verb has a required word", () => {
  const required = Object.values(HAND_CONTRACT.verbs as Record<string, { args?: readonly string[] }>)
    .flatMap((v) => (v.args ?? []).filter((a) => !a.endsWith("?")));
  assert.ok(required.length > 0, "no verb has a required argument, so missing_argument can never fire");
});

test("unknown_verb is grounded: the contract closes the verb set, so a word outside it is refusable", () => {
  assert.ok(Object.keys(HAND_CONTRACT.verbs).length >= 0);
  assert.ok(!Object.keys(HAND_CONTRACT.verbs).includes("no-such-verb"));
});

# Repair Playbook

## Table of Contents
- [Safety Rules](#safety-rules)
- [Non-Destructive Repairs](#non-destructive-repairs)
- [Destructive Repairs](#destructive-repairs)
- [Batch Operations](#batch-operations)
- [Function Reference](#function-reference)

---

## Safety Rules

### Always

1. **Show the finding + evidence** before proposing a repair
2. **Get explicit user approval** before any write operation
3. **Log what you did** -- note the entity ID, what changed, timestamp
4. **Verify after repair** -- re-run the check to confirm the fix worked

### Never

1. **Never run `qa/re-enrich-company` without explicit approval** -- it deletes ALL company data (emails, phones, addresses, links, funding rounds, Falkor node) before re-enriching
2. **Never delete FalkorDB nodes** without checking edge count first
3. **Never merge duplicate persons** without human review -- merges are irreversible
4. **Never bulk-update more than 10 records** without showing the full list first
5. **Never call external enrichment APIs** without noting credit cost

### Destructive vs Non-Destructive

| Operation | Destructive? | Approval Required? |
|-----------|-------------|-------------------|
| Copy field from one table to another | No | No (safe sync) |
| Reset stuck processing flag | No | No |
| Queue for re-enrichment | Mild | Yes (uses API credits) |
| Re-run expertise identification | Mild | Yes (calls LLM) |
| Delete orphaned expertise node | Yes | Yes |
| Re-enrich company (nuclear) | **VERY** | **ALWAYS** |
| Merge duplicate persons | **VERY** | **ALWAYS** |
| Delete master_person records | **VERY** | **ALWAYS** |

---

## Non-Destructive Repairs

### Sync deep_bio

**When**: `deep_biography.biography` exists but `master_person.deep_bio` is empty or different.

```
Tool: execute
tool_id: update_record
arguments: {
  table_id: 139,
  record_id: {master_person_id},
  data: {
    deep_bio: "{deep_biography.biography}"
  }
}
```

### Sync social_insights

**When**: `social_insights` table record exists but `master_person.social_insights` is empty.

```
Tool: execute
tool_id: update_record
arguments: {
  table_id: 139,
  record_id: {master_person_id},
  data: {
    social_insights: "{social_insights.social_insights}"
  }
}
```

### Reset stuck processing flag

**When**: `deep_biography.processing = true` or `social_insights.processing = true` for > 1 hour.

```
Tool: execute
tool_id: update_record
arguments: {
  table_id: 591,  // or 649 for social_insights
  record_id: {record_id},
  data: {
    processing: false
  }
}
```

### Update title from work experience

**When**: `master_person.current_title` doesn't match latest work_experience.

```
Step 1: Get latest work_experience
  tool_id: query_table
  arguments: { table_id: 147, filter: master_person_id = {id}, sort: created_at DESC, limit: 1 }

Step 2: Update person
  tool_id: update_record
  arguments: {
    table_id: 139,
    record_id: {master_person_id},
    data: {
      current_title: "{work_experience.title}",
      master_company_id: {work_experience.master_company_id}
    }
  }
```

### Clear orphaned node_uuid

**When**: `master_person.node_uuid` is set but no FalkorDB node exists.

```
Tool: execute
tool_id: update_record
arguments: {
  table_id: 139,
  record_id: {master_person_id},
  data: {
    node_uuid: ""
  }
}
```

---

## Destructive Repairs

### Re-enrich person

**What it does**: Re-runs the enrichment pipeline from raw `person_enrich_data`. Non-destructive to existing data -- adds/updates but doesn't delete.

**Credit cost**: May call external APIs (Scrapin, ContactOut, etc.)

```
Tool: execute
tool_id: run_function
arguments: {
  function_id: 2634,
  input: {
    master_person_id: {id}
  }
}
```

**Verify after**:
```
tool_id: query_table
arguments: { table_id: 160, filter: master_person_id = {id}, sort: created_at DESC, limit: 1 }
// Check enrich_success = true
```

### Re-enrich company (NUCLEAR)

**WARNING**: This function DELETES all company sub-records before re-enriching:
- master_email records
- master_phone records
- master_address records
- master_link records
- company_funding_round records
- FalkorDB Company node
- Then re-enriches from scratch

**Only use when**: Company data is fundamentally corrupted and needs full reset.

```
Tool: execute
tool_id: run_function
arguments: {
  function_id: 4580,
  input: {
    master_company_id: {id},
    company_added: true,
    data_source_id: 0
  }
}
```

### Re-run expertise identification

**What it does**: Calls Claude Sonnet 4 to identify expertise from person's context YAML, then vector-matches in FalkorDB. Clears existing HAS_EXPERTISE edges first.

**Credit cost**: 1 LLM call (OpenRouter) + FalkorDB operations

```
Step 1: Identify expertise (LLM)
  tool_id: run_function
  arguments: {
    function_id: 12666,
    input: {
      master_person_id: {id}
    }
  }

Step 2: Verify in log
  tool_id: query_table
  arguments: {
    table_id: 660,
    filter: master_person_id = {id},
    sort: created_at DESC,
    limit: 1
  }
  // Check matched > 0 or created > 0
```

### Delete orphaned expertise node

**When**: SubDomainExpertise node has no HAS_EXPERTISE edges and source = "llm_identified".

```
Step 1: Verify no edges
  Cypher: MATCH (s:Entity:SubDomainExpertise {uuid: "{uuid}"})<-[:HAS_EXPERTISE]-()
          RETURN count(*) AS edge_count
  // Must be 0

Step 2: Delete node
  Cypher: MATCH (s:Entity:SubDomainExpertise {uuid: "{uuid}"})
          DETACH DELETE s

Step 3: Clean up Xano sub_domain_expertise record if exists
  tool_id: query_table
  arguments: { table_id: 654, filter: node_uuid = "{uuid}" }
  // Delete or clear node_uuid
```

### Merge duplicate expertise nodes

**When**: Two SubDomainExpertise nodes are semantically identical.

```
Step 1: Pick canonical node (older one, or one with more edges)
  Cypher: MATCH (s:Entity:SubDomainExpertise {uuid: "{uuid1}"})<-[r:HAS_EXPERTISE]-(p)
          RETURN count(r) AS edges1
  Cypher: MATCH (s:Entity:SubDomainExpertise {uuid: "{uuid2}"})<-[r:HAS_EXPERTISE]-(p)
          RETURN count(r) AS edges2

Step 2: Reassign edges from duplicate to canonical
  Cypher: MATCH (p)-[r:HAS_EXPERTISE]->(dup:Entity:SubDomainExpertise {uuid: "{dup_uuid}"})
          MATCH (canonical:Entity:SubDomainExpertise {uuid: "{canonical_uuid}"})
          MERGE (p)-[r2:HAS_EXPERTISE]->(canonical)
          SET r2 = properties(r)
          DELETE r

Step 3: Delete duplicate node
  Cypher: MATCH (s:Entity:SubDomainExpertise {uuid: "{dup_uuid}"})
          DETACH DELETE s
```

---

## Batch Operations

### Batch sync deep_bio (up to 50 at a time)

```
Step 1: Find all mismatches
  Query deep_biography records where processing = false
  For each, check if master_person.deep_bio matches

Step 2: Show list to user
  "Found {N} persons with bio sync issues. Proceed with sync?"

Step 3: Execute updates
  For each mismatch:
    update_record table_id:139 record_id:{id} data:{deep_bio: "..."}

Step 4: Report results
  "Synced {N} bios. {M} failures."
```

### Batch queue re-enrichment

```
Step 1: Identify stale persons
  Query enrich_history_person, group by master_person_id
  Find persons with latest enrichment > threshold days

Step 2: Show list with approval
  "Found {N} persons with stale enrichment (>{threshold} days). Queue for re-enrichment?"

Step 3: Execute
  For each (max 10 per batch):
    run_function 2634 { master_person_id: {id} }

Step 4: Monitor
  Check enrich_history_person for new records with enrich_success status
```

### Batch expertise re-resolution

```
Step 1: Find failed resolutions
  Query expertise_identification_log where matched=0 AND created=0

Step 2: Check prerequisites
  Verify vector index exists: CALL db.idx.vector.info('Entity', 'expertise_embedding')
  If missing, fix index first (blocks all resolutions)

Step 3: Re-run with approval
  For each person:
    run_function 12666 { master_person_id: {id} }
  Wait 5 seconds between calls (rate limit)

Step 4: Verify
  Re-query expertise_identification_log for updated records
```

---

## Function Reference

| Function | ID | Destructive? | What it does |
|----------|----|-------------|--------------|
| qa/re-enrich-existing-person | 2634 | Mild | Re-process from raw data |
| qa/re-enrich-company | 4580 | **NUCLEAR** | Delete all + re-enrich |
| qa/bulk-delete-master-person | 2681 | **NUCLEAR** | Delete person + all sub-records |
| mvp/expertise/llm-identify-person-expertise | 12666 | Mild | LLM expertise identification |
| mvp/expertise/resolve-person-expertise | 12668 | Mild | Vector match/create in FalkorDB |
| mvp/enrich/run-base-company-enrich_v2 | 4513 | Mild | Company enrichment pipeline |
| mvp/enrich/deep-research-person-prompt | 4578 | No | Generate deep bio |
| mvp/enrich/get-profile-image | 12607 | No | Fetch avatar image |
| mvp/enrich/deep-company-about | 12591 | No | Generate company about |
| mvp/node/sync-domain-expertise-nodes | 12663 | No | Sync domains to FalkorDB |
| mvp/falkor/send-cypher | 2815 | Depends | Execute cypher query |
| mvp/falkor/send-cypher-with-params | 4670 | Depends | Parameterized cypher |
| falkor/re-index | 4675 | No | Rebuild vector indexes |

---

## Post-Repair Verification

After any repair, always re-run the original check:

```
1. Execute the same query that found the issue
2. Confirm the finding no longer appears
3. If still broken, investigate why the repair didn't work
4. Report success/failure to user
```

### Common Repair Failures

| Symptom | Cause | Fix |
|---------|-------|-----|
| update_record returns 404 | Record was deleted between scan and repair | Skip, note in report |
| run_function times out | Xano function has a bug or external API is down | Retry once, then flag |
| FalkorDB cypher returns error | Syntax issue or node label changed | Check cypher syntax |
| Re-enrichment produces same result | Raw data is bad/empty | Note as "needs manual data entry" |
| Expertise resolution still fails | Vector index still missing | Fix index first, then retry |

# Report Format -- "Don't Make Me Think"

## Core Rule

Every report -- whether it's a pulse, a per-person check, or a batch scan -- follows the same structure:

1. **What's wrong** -- lead with the verdict, not the data
2. **What to do about it** -- prioritized, with what each fix unblocks
3. **One question** -- what to look at next

Never dump a table of numbers and let Mark figure out what matters. The agent does the thinking.

---

## Severity Levels (Internal Logic)

These drive the agent's prioritization. Mark doesn't need to see severity tags -- he needs to see "this is the big one" vs "this can wait."

| Level | Internal Meaning | How to Present to Mark |
|-------|-----------------|----------------------|
| CRITICAL | Data integrity broken -- enrichment produced nothing | Lead with this. "The #1 problem: [X]" |
| HIGH | Visible data quality issue -- users would see this | Include in top findings. "[X] is still broken" |
| MEDIUM | Missing data, incomplete enrichment | Mention if relevant. "Also: [X]" |
| LOW | Cosmetic or minor | Skip unless Mark asks for everything |
| INFO | Not broken, just notable | Skip unless it explains another finding |

### What Triggers Each Level

| Diagnostic | CRITICAL | HIGH | MEDIUM |
|-----------|----------|------|--------|
| 1. Completeness | -- | No history records; stuck processing; visibility=false | Source attempted but failed |
| 2. Data Landing | JSON has items, table has 0 | JSON has more items than table | Partial landing |
| 3. Avatar | -- | Placeholder with real URL available | No avatar anywhere |
| 4. Crashes | Crash in Section 10 (complete) | Same error > 10 times | Isolated crashes |
| 5. Queue | -- | processing=true > 1 hour | Queue > 1,000; oldest > 7 days |
| 6. Duplicates | -- | Same job+company appears 2+ times | Same school cross-source |
| 7. Edge Gaps | -- | Company exists but not linked | School not linked |
| 8. History | History says success but data null | Stuck processing > 1 hour | Multiple records same source |

---

## Per-Person Report

When checking a specific person, present it like this:

### If the person is clean:

> **Carl Bressler (ID: 2070) looks good.** All 3 data sources ran. Skills, education, work all landed correctly. Avatar is real (not a placeholder). No duplicates. No edge gaps.
>
> Only note: 2 enrich_history records for Enrich Layer (known duplicate bug, not a real problem).

### If the person has problems:

> **Josh Diamond (ID: 1) has 3 problems.**
>
> **The big one: education bloat.** 2 education items in the JSON produced 17 records in the table. That's re-run duplication -- the dedup isn't catching repeats from the same source.
>
> **Avatar bug.** 2 placeholder images are marked as main. There's a real LinkedIn photo available from Enrich Layer that should have replaced them.
>
> **Work records partially missing.** 10 work items in JSON but only 6 landed. The other 4 didn't make it through -- likely a process-enrich-layer crash partway through.
>
> **If I were fixing this person:**
> 1. Delete the 15 duplicate education records
> 2. Re-run avatar replacement with the EL photo URL
> 3. Re-run process-enrich-layer for the missing work records
>
> Want me to check if the education bloat pattern affects other people too?

### The ASCII Card (Only When Requested)

If Mark specifically asks for a structured view, use this:

```
═════════════════════════════════════���═════════════════
PERSON: Josh Diamond (ID: 1)
Visibility: true · Sources: PDL ✓  EL ✓  Fund ✓
═══════════════════════════════════════════════════════

DATA LANDING:
  skills      66 → 66  ✓
  education    2 → 17  ▲ BLOAT (re-run duplication)
  work        10 →  6  ▼ PARTIAL (4 missing)
  certs        2 →  2  ✓
AVATAR: ⚠ 2 placeholders have main=true (real EL pic available)
DUPLICATES: education 94.1% duplicate rate
FINDINGS: 1 CRITICAL · 1 HIGH · 1 MEDIUM
═══════════════════════════════════════════════════════
```

**Always follow the ASCII card with the plain English summary.** The card is a reference, not the communication.

---

## Batch Report

When scanning multiple people, don't list every finding individually. Group by pattern and lead with what matters.

### Example Batch Report:

> **Scanned 100 people. 59 are clean, 41 have issues.**
>
> **The pattern:** almost everything traces back to two root causes.
>
> **Root cause #1: process-enrich-layer crashing** (15 people)
> Volunteering, honors, and projects are all empty despite having JSON data. The function is crashing partway through and killing everything after the crash point. These people have partial data -- some categories landed, some didn't.
>
> **Root cause #2: education dedup bug** (23 people)
> Same school appears twice because the dedup check includes data_source_id in the WHERE clause. When PDL and Enrich Layer both have "Stanford University", it creates two records instead of merging them.
>
> **Also:**
> - 8 people have placeholder avatars with real photos available (avatar bug)
> - 31 people have work records not linked to companies (edge resolution didn't run or failed)
>
> **If I were fixing this batch:**
> 1. **Add per-section try-catches to process-enrich-layer** -- fixes 15 people and prevents this for every future batch
> 2. **Remove data_source_id from education dedup** -- fixes 23 people
> 3. **Avatar and edge gaps can wait** -- they're not blocking visibility
>
> Want me to pull the 15 people affected by the process-enrich-layer crash so you have the list?

### Grouping Rules

When there are many findings:
- **Batch similar findings** -- "15 people missing Enrich Layer volunteering data" not 15 separate entries
- **Trace to root causes** -- if 3 different symptoms all come from one bug, say that
- **Lead with the pattern, not the count** -- "process-enrich-layer is crashing" not "15 CRITICAL, 23 MEDIUM, 8 HIGH"

---

## Quick Health Check

When Mark just wants to know if things are ok:

### Healthy:

> **Pipeline is healthy.** No critical issues. Queues are moving (71 people, 9,861 companies). No crashes captured. Nothing needs immediate attention.

### Not healthy:

> **Pipeline has problems.** LLM Biography is failing on 63% of enrichments. 197 people stuck. Avatar bug still active on all 1,162 placeholders.
>
> Biggest thing to fix: LLM Biography. It unblocks 431 records.

---

## Finding Structure (Internal)

The agent tracks findings internally as structured objects. These are for the agent's logic, not for display to Mark.

```json
{
  "diagnostic": 2,
  "diagnostic_name": "Data Landing",
  "entity_type": "person",
  "entity_id": 1,
  "entity_name": "Josh Diamond",
  "severity": "CRITICAL",
  "description": "Education: 2 JSON items produced 17 table records (bloat from re-runs)",
  "evidence": {
    "source": "education_experience (230)",
    "expected": 2,
    "actual": 17
  },
  "recommendation": "Delete duplicate education records, fix dedup to prevent re-run bloat"
}
```

The agent uses these to:
1. Rank issues by severity
2. Group findings by pattern
3. Trace multiple symptoms to root causes
4. Decide what to present first

But the output to Mark is always plain English, never raw finding objects.

---

## Progressive Detail

Reports have three levels. Default is to show Level 1 + Level 2. Only show Level 3 when Mark asks to go deeper or when checking a specific person.

1. **Level 1 -- The verdict:** "Batch has 41 people with issues. Two root causes."
2. **Level 2 -- The patterns:** what the root causes are, how many people each affects, what to fix
3. **Level 3 -- The details:** per-person cards for the worst affected, specific record counts, table names

Never start at Level 3. Always start at Level 1 and let Mark ask for more.