# Running the AIDA Job Report Pipeline in Cowork — Sandbox Runbook

Read this alongside `AIDA_DB_Pipeline_Kit/INSTRUCTIONS_DB_PIPELINE.md` (the
real procedure) and `AIDA_Shared_Kit/NON_NEGOTIABLES.md` (identity, format
spec, permission gates). This file covers what's specific to running that
procedure from **Cowork**, in the sandboxed shell it gives Claude, against
this **multi-kit source layout** (three separate kit folders, not a
flattened installer build) — three real problems hit while building Luis
Guzman's Price Fixe report, all now fixed at the source, plus the parts of
the workflow that still take real judgment every time.

## The three sandbox-specific problems (now fixed — read for context, not as a to-do list)

1. **No npm registry access.** `resume_kit.js` needs the Node `docx`
   package; `npm install docx` returns `403 Forbidden` in this sandbox.
   **Fixed:** `AIDA_DB_Pipeline_Kit/scripts/python_resume_kit.py` is a
   faithful line-for-line port of `resume_kit.js` using `python-docx`
   instead — same measured format constants (Calibri, navy `#1F3864`, gray
   `#555555`, exact point sizes), same auto-fit-to-page-count loop via
   `soffice`/`pdfinfo`/`pdftotext`. `build_report.py`'s `run_resume_kit()`
   now tries Node first and falls back to this automatically — **you don't
   need to do anything differently**, just run `build_report.py` the way
   `INSTRUCTIONS_DB_PIPELINE.md` step 8 already says to.
2. **`products.json` path assumption.** `build_report.py` assumed
   `products.json` sits one level up from its own script directory — true
   in a flattened installer build, false in this three-folder source
   layout (it actually lives in `AIDA_Shared_Kit/`). **Fixed:**
   `load_products()` now checks both locations.
3. **Bare imports need `PYTHONPATH`.** `build_report.py` does
   `import career_bio` / `import search_strategy` with no path handling —
   fine in a flattened build where everything's one directory, not fine
   here since those two live in `AIDA_Shared_Kit/scripts/` while
   `build_report.py` lives in `AIDA_DB_Pipeline_Kit/scripts/`. **Not
   patched in the script itself** (a real path fix belongs in the
   installer's flattening step, not a source hack) — just export
   `PYTHONPATH` before running, see the exact command below.

None of this required any change to the actual *content* — Career Bio,
Search Strategy, Job Report keep coming out of `career_bio.py` /
`search_strategy.py` / `job_report.py` untouched except for one more real
fix:

4. **Two hardcoded em dashes in the shared templates.** `career_bio.py`'s
   slide subtitles (`"{tagline} — 1 of 2"`) and four lines in
   `job_report.py` (the page-subtitle format string and three body
   sentences on the "How AIDA Reviewed These" / "What's Working" slides)
   baked literal em dashes into every generated deck, which directly
   violates the kit's own "zero em dashes, every generated document, no
   exceptions" rule (`NON_NEGOTIABLES.md`, Addendum). Nobody had caught it
   because a human proofreading a slide doesn't usually grep for the
   character. **Fixed at the source** in both files — verified by
   text-extracting every PDF Luis's report produced and grepping for the
   UTF-8 em dash bytes (`grep -c $'\xe2\x80\x94'`), zero everywhere. If you
   ever touch either file again, re-run that same grep check before
   trusting a new build — it's cheap and it's the only way to actually
   know, reading the rendered PDF text beats reading the source strings
   since python-pptx/docx text runs can hide the character in surprising
   places.

## The exact command that now works end-to-end

```bash
export PYTHONPATH="/path/to/AIDA_Kits/AIDA_Shared_Kit/scripts:$PYTHONPATH"
cd /path/to/AIDA_Kits/AIDA_DB_Pipeline_Kit/scripts
python3 build_report.py --product "Price Fixe" \
  --data profile_data.json \
  --resume-data resume_data.json \
  --image ../../AIDA_Shared_Kit/assets/aida_cover_illustration.png \
  --top50-csv {profile}_top100.csv \
  --full-dataset-csv {profile}_full_scan.csv \
  --outdir ./output
```

Same pattern for `career_package` / `short_stack` (drop `--resume-data` for
career_package; both still need `PYTHONPATH` and the same `--image`).
`large_house_blend_pastry` doesn't need `PYTHONPATH` at all if you call
`resume_kit.js`/`python_resume_kit.py` directly instead of through
`build_report.py`, per that product's own note in the instructions file.

Confirmed available in this sandbox (check again if any of this changes):
`python-pptx` 1.0.2, `python-docx`, `Pillow`, LibreOffice (`/usr/bin/soffice`),
`pdfinfo`/`pdftotext` (poppler-utils), Node v22 (but **not** a working npm
registry — `pip`/`apt`/`npm install` all fail the same way, don't retry
them expecting a different result). No live Postgres connection.

## The parts that still take real judgment every run — no shortcut for these

The three fixes above remove *mechanical* friction. They don't touch the
actual work, which is still exactly what `INSTRUCTIONS_DB_PIPELINE.md`
describes and took most of the effort on Luis Guzman's report:

- **No live DB access from Claude in this environment.** Every step that
  needs a DB read or write (client/profile lookup, current keyword matrix,
  Stage 2 rescoring, employer toxicity rows) has to go through Michael:
  generate the `.sql` file, hand him the exact
  `docker exec -i data_vault_postgres psql -U vault_admin -d career_perfection -f /dev/stdin < file.sql`
  command, wait for pasted-back output, verify it before proceeding. Don't
  guess at schema or current data state — inspect first, every time.
- **Getting the CSVs.** The dashboard's client detail page has a **Top 100**
  button and a **Full Scan** button per profile (added this session) —
  use those rather than asking Michael to run `sql/03`/`sql/05` by hand.
  The Top 100 download already has employer-score and closed-listing
  filtering baked in; the Full Scan download is the complete unfiltered
  pool (matches `sql/05_export_full_dataset.sql`'s shape).
- **The manual 100-posting review (Best / Worth a Look / Removed).** This
  is real judgment against the resume, career bio, and keyword matrix —
  do it in Python from the downloaded CSV (`csv.DictReader`, one dict per
  rank, then tally), not by re-querying the DB per posting. Keep survival
  under ~50% (Luis's came out to 26%, 6 Best + 20 Worth a Look out of 100).
  Watch for two patterns Luis's run surfaced that are worth checking on
  every profile: (a) one employer name posting a disproportionate, wildly
  varied volume relative to its real size — real company, but a volume
  pattern worth a caveat, not automatic removal; (b) duplicate/reposted
  listings inflating the "removed" count more than genuine mismatches do.
- **Company-name normalization for the "What's Working" stats.** The
  dashboard's `normalize_company_name()` SQL function only affects the
  employer-exclusion *scoring* join — it does **not** rewrite the
  `company` values in the CSV exports, so "BMO" and "Bank of Montreal"
  (or "emergiTEL" / "emergiTEL Inc.") still show up as separate strings
  in the raw data. Re-derive the same normalization in Python before
  computing top-companies-by-score/volume/keyword — mirror the SQL
  function's logic (strip numeric prefix, strip legal suffix, check the
  same alias table: RBC/TD/BMO/CIBC/Capital One - CA) rather than trusting
  raw `GROUP BY company` counts.
- **The zero-em-dash sweep applies to *your* drafted content too, not just
  the templates.** Before writing `profile_data.json`/`resume_data.json`,
  walk the whole dict recursively and assert no `—` character anywhere
  (`career_bio`, `job_report.best`/`worth_a_look`/`why` text, everything).
  Catch it before generation, not by grepping the PDF after — cheaper to
  fix a Python string than to regenerate a whole deck.
- **The docx/PDF/pptx generation permission gate is separate from
  everything else.** Drafting and showing content in chat needs no
  permission; actually calling `build_report.py` does, every time, even if
  the client/profile/scoring work upstream was already confirmed.

## Reference example: Luis Guzman's run

Everything from that build — `profile_data.json`, `resume_data.json`, the
Python review/company-stats scripts, and the final 10-file zip — was in a
temporary Cowork session output folder that does **not** persist between
sessions, so it's not sitting anywhere in this `AIDA_Kits` folder to copy
from. The delivered zip itself
(`Luis_Guzman_AML_KYC_Financial_Crime_Compliance_Delivery.zip`) is in
`/Users/mikemacleod/career_perfection/` if you want to open it and see the
actual output quality/format as a reference point before starting the next
client.
