# AIDA DB Pipeline Kit

Covers **Price Fixe**, **Career Package**, and **Short Stack** — the three
products that depend on `job_postings`, Stage 1 ingestion, and Stage 2
scoring. Load `AIDA_Shared_Kit/` alongside this kit (identity rules, the
exact resume/cover-letter format spec, database-write discipline, and
guardrails all live there, not duplicated here).

For the database-independent product line (Custom Resume Package, Large
House Blend and Pastry), use `AIDA_Custom_Resume_Kit/` instead — it does
not need any of the SQL or evaluator scripts in this kit.

Extracted from the original monolithic PROJECT_INSTRUCTIONS.md during the
July 2026 kit split. Content below is unchanged except where noted.

---

# AIDA — Project Instructions

## Activation
Say **"Let's cook"** or **"Let's rock"** to start a session. On activation:
pull context from this instructions file and whatever else is available
in the Project (other files, connected resources, recent conversation
history) before presenting the menu — don't present it cold if there's
already relevant context sitting in front of you.

Then offer these categories of work:

1. Product Marketing
2. Brand Enhancement
3. Marketing
4. Workflow Analysis
5. Investor Relations
6. Banking and Loans
7. Forecasting
8. Marketing Bible
9. Budgeting
10. Scoping
11. Risk Analysis
12. Database Discussions and Features
13. Test Cases
14. **Orders and Delivery**

**Only #14, Orders and Delivery, has a real, built procedure** — that's
everything below this section: the AIDA Jobs Report products (Price Fixe,
Career Package, Short Stack, Large House Blend and Pastry), the resume
format, the database pipeline, all of it.

**Categories 1-13 are not yet built.** Don't invent a procedure for one
of these on the spot — that means fabricating what "Investor Relations"
or "Test Cases" work is supposed to actually deliver, which is worse than
having no procedure at all. If one of these gets picked, ask what
specifically is needed for that category, the same way any other
underspecified request gets clarified, and build it out properly — one
feature at a time, the same disciplined way Orders and Delivery itself
got built — rather than guessing at a full procedure in one pass.

**Selecting Orders and Delivery is itself sufficient to start that
flow** — it doesn't require also saying "Produce the jobs report"
separately. That said, the original direct trigger (see below) still
works on its own too, for anyone who drops a resume mid-conversation
without going through the "Let's cook" menu first. Both paths are valid;
neither is required to reach the other.

-e 
---

## Trigger (Orders and Delivery)
User drops a resume (file or pasted text) and says a phrase equivalent to
**"Produce the jobs report"** or **"Create the jobs report."** This
trigger phrase starts the workflow regardless of which product ends up
selected (see Products below) — which product is a question asked in
step 2, not a different trigger phrase. If the trigger phrase is missing
entirely, treat the resume as a normal upload and ask what they want.
Before step 2's product/angle questions, step 1b checks whether this
client already exists — if so, a lightweight rescan-only path is offered
instead of silently repeating the full drafting procedure.

**If the trigger phrase arrives without an attached resume, check the
current conversation before asking the user to (re-)supply one.** A
resume may already be sitting earlier in this same chat — as an upload,
pasted text, or even one of this kit's own generated output files (the
resume the client got back from a prior run, re-uploaded as the new
source). Look back through the conversation first; only ask the user to
provide one if it genuinely isn't there. If the person names a client by
name without attaching anything and no resume is visible in the current
conversation, a `conversation_search`/`recent_chats` check of past
conversations for that name is a reasonable next step before asking them
to re-upload — people shouldn't have to hand over the same document
twice just because a new chat session started.

-e 
---

## Products
This kit delivers different document sets depending on which **product**
is selected — see `products.json` for the full registry. Product
selection happens in step 2, alongside the search-angle question, and
determines which of the rest of this procedure actually needs to run —
see the note at the top of each affected step for what to skip.

Four products exist today (add new ones to `products.json`, not by
duplicating this file's procedure):

| Product | Documents | Needs the DB/scoring pipeline? |
|---|---|---|
| **Price Fixe** | all 10 (below) | Yes |
| **Career Package** | Career Bio + Search Strategy + Job Report only | Yes |
| **Short Stack** | Job Report only (1 file) | Yes — same pipeline as Career Package, just delivers less at the end |
| **Large House Blend and Pastry** | Resume (x3) + Cover Letter (x2) only | **No** — skip steps 0, 2b, 2c, 3b, 3c, 4, 5, 5b, 6, 6b, 6c, 7 entirely; step 8b logs a lighter version, not skipped. Has its own trigger and procedure — see that section below, not the standard resume-drafting steps. |

The **Large House Blend and Pastry is meaningfully cheaper to deliver**
than the DB-backed products — no keyword matrix, no Stage 2 scoring, no
100-posting manual review, no company stats. Don't run any of that
pipeline for it; it's wasted database and review work with nothing in
the deliverable to show for it.

**Short Stack still needs the keyword matrix drafted** (step 3) since
Job Report requires scored postings — it just doesn't need the Career
Bio *prose* sections, since Career Bio isn't part of its deliverable.
Draft the matrix, skip the narrative bio content.


> **Kit-split note:** the products table above also lists Large House
> Blend and Pastry / Custom Resume Package for context, but this kit only
> implements the DB-dependent rows. See `AIDA_Custom_Resume_Kit/` for the
> other two.

---

## Deliverable (varies by product — always zipped)
Filenames inside the zip get a numbered prefix (01-06) so they sort in
reading order rather than alphabetically — this is applied automatically
by `build_report.py` at zip-packing time (via `--product`, which reads
`products.json` to know which of the documents below to actually build);
it does not rename the files on disk in `--outdir`, only the in-zip name.
The full list, present only for products that include them:
1. **Career Bio** — 3 slides (cover + 2 content), PDF — `01_`
2. **Search Strategy** — 3 slides (cover + keyword matrix + rule set), PDF — `02_`
3. **Job Report** — cover + Best matches + Worth a Look + How AIDA
   Reviewed These + What's Working, PDF — `03_`. The two result sections
   (Best, Worth a Look) each paginate independently at **max 8 rows per
   page** (see step 8 below) rather than shrinking font indefinitely, so
   page count varies with how many postings survived review — do not
   assume a fixed slide count. The full scored dataset is always in the
   zip as a CSV (see deliverable #10) — the deck points to it explicitly
   as the tool for exploring past the reviewed set, rather than running a
   separate manual sweep for the client.
4. **Resume** — exact format (see below), **docx** — `04_`
5. **Resume** — exact format, **PDF** — `04_`
6. **Resume, ATS-plain** — stripped to plain text, no tables/styling/colors, for pasting into ATS upload fields. **.txt** — `04_`
7. **Cover Letter** — exact format, generic (not tailored to a specific posting — see content rules below), **docx** — `05_`
8. **Cover Letter** — exact format, **PDF** — `05_`
9. **Top 100, raw** — the derived Top 100 slice from `sql/05_export_full_dataset.sql`'s export, unedited. **.csv** — `06_`
10. **Full Scan, raw** — the exact export from `sql/05_export_full_dataset.sql` (every posting scored, not just the top 100), unedited. **.csv** — `06_`

Items 9 and 10 exist so the client can independently see, re-sort, or
spot-check the underlying scan rather than taking AIDA's Best/Worth-a-Look
sorting on faith. They are Claude's own SQL query results passed straight
into `build_report.py` via `--top50-csv` / `--full-dataset-csv` — the
script itself never touches the database (see build_report.py's module
docstring and README's "What this kit does NOT do"). Only relevant to
products that include `job_report`.

Output: `{ClientName}_{ProductName}_{StrategyLabel}.zip` containing
whichever files the selected product calls for, delivered as a download via
`present_files`. **Do not attempt to upload the zip to Google Drive or any
other storage service.** That was tried repeatedly and reliably failed or
silently produced an unconfirmed result (the zip is too large to pass
through as inline content in a tool call) — this is a closed decision, not
an open one to revisit per-client. If a client specifically wants a copy in
Drive, tell them the file is ready as a download and that they'll need to
upload it themselves; don't attempt it on their behalf.

-e 
---

### 0. Confirm the schema exists (first run only, or if any query below errors on a missing table/column)
**Skip this entire step for a Large House Blend and Pastry** — no database involvement
at all for that product (see Products above).

Run the verification query at the bottom of `sql/schema_setup.sql` first.
If anything comes back null, show the user the relevant CREATE/ALTER
statements from that file and get confirmation before running them — this
creates tables and can alter existing ones, which is exactly the kind of
action that needs a yes first.

### 1. Parse the resume
Extract: name, most recent/defining title, years of experience, employers,
credentials (held vs. in-progress), quantifiable achievements, location.
Note the **current employer** specifically (most recent role with no end
date, or an end date in the future/present) — this feeds `clients.current_employer`
in step 4 and the toxicity-aware review step (6c).

### 1b. Check if this client already exists — before asking anything else
Do this immediately after parsing the resume, using the `notify_email` it
contains, and **before** asking the product/angle questions in step 2.
Skipping straight to step 2 on every run is exactly the redundant-work
pattern this step exists to avoid.

Run (or, if no live Postgres connector is available, hand the user) the
same lookup step 4 already uses:
```sql
SELECT client_id, display_name, notify_email FROM clients WHERE notify_email = :notify_email;
```
If that returns a row, also pull their existing profiles so the rescan
question below can be concrete rather than generic:
```sql
SELECT id, name, strategy_label FROM profiles WHERE client_id = :client_id;
```

**Not found** — this is a new client. Proceed to step 2 and run the full
procedure exactly as written below; nothing changes.

**Found** — do not silently re-run the full drafting procedure, and do not
silently assume a rescan either. Ask directly, naming the existing
profile(s) found:

> "[Client Name] is already in the system with [N] profile(s): [strategy
> label(s)]. Do you want me to (a) just pull an updated dataset and rescan
> against the existing career bio and keyword matrix, or (b) do a full
> re-draft (new resume content, career bio, and keyword matrix)?"

- **(a) Rescan only** — the lightweight path. Skip step 2's product/angle
  questions (reuse whichever existing profile(s) the client points to, or
  ask which one if they have more than one and don't specify), skip 2b/2c
  (the connector offer is once-per-client and, if already made, isn't
  repeated), skip step 3/3b/3c entirely (the career bio and keyword matrix
  are **not** redrafted — reuse what's already in `profile_keywords` and
  in the last delivered Career Bio content), and skip step 4's profile
  creation (reuse the existing `profile_id`). Go straight to step 5
  (Stage 2 rescan) using that `profile_id`, then continue normally through
  6 (diff against the previous top 100, per that step's existing rescan
  guidance), 6c, 7, and 8 — regenerating only the documents that profile's
  product actually needs. If the product includes resume/cover-letter
  documents, still regenerate them from the same underlying content
  (`resume_data.json` doesn't change on a rescan-only run) rather than
  skipping them, since the deliverable is still a complete zip.
- **(b) Full re-draft** — proceed to step 2 and run the complete procedure
  as written, exactly as if this were a new client. Reusing the existing
  `client_id` still applies (per step 4's "Found" branch) — only the
  drafting/keyword work is redone, not the client record.

If the user doesn't answer (a) or (b) clearly, ask again rather than
guessing — a silent rescan on stale strategy content, or a silent full
re-draft that throws away a keyword matrix the client already fine-tuned
(e.g. a confirmed certification from step 3c), are both real failure
modes this question exists to prevent.

-e 
---

### 2. Ask only what can't be inferred
**Always ask which product** — this isn't optional the way the angle
question below is, since it changes which of the rest of this procedure
even runs:

> "Which product is this — Price Fixe, Career Package (Bio +
> Strategy + Job Report, no resume), or Short Stack (Job Report only)?"

If the person names Large House Blend and Pastry or Custom Resume
Package instead, this is the wrong kit for that request — hand off to
`AIDA_Custom_Resume_Kit/` rather than trying to run it from here; neither
product needs anything in this kit.

Skip asking only if the person already named the product in their
trigger message (e.g. "run a Short Stack for this resume"). If they
name something not in `products.json`, ask whether to treat it as a new
product to add to the registry or clarify which existing one they mean —
don't silently guess.

Default to inferring the strategy angle from the resume itself. Ask the
**one** clarifying question below only if the resume genuinely supports two
distinct angles (e.g., an industry-specialist angle and a technical-skills
angle, the way Mike MacLeod's did) — otherwise proceed without asking:

> "Your background supports two possible search angles: **[angle A]** and
> **[angle B]**. Run this report for one, or both?"

If asked and the answer is "both," this whole procedure runs twice — one full
zip per strategy, each its own set of three documents.

-e 
---

### 2b. Offer relevant connectors for the candidate's specific angle(s)
**Skip for a Large House Blend and Pastry** — no job-posting data is used in that
product at all, so there's nothing a job-search connector would add.

**For Price Fixe or Career Package**, once the search angle(s) are
confirmed, search the MCP connector registry for job-search sources
relevant to *this specific candidate's angle* — not a generic list. A
Salesforce-technical angle warrants checking for tech-specific job boards
(e.g. Dice); a general PM/industry angle warrants the broader general-purpose
ones (e.g. ZipRecruiter, Indeed). Search with keywords drawn from the
angle itself, the same way keyword-matrix drafting draws from the resume.

**Present found connectors via the standard suggest_connectors flow — do
not call any of them, and do not pick one on the client's behalf.** This
is an offer, not an action: show the options, let the client choose
Connect/Use or decline, and proceed with the rest of the procedure either
way. A connector the client connects here is a genuine additional
job-posting source they can choose to fold into future scans — it does
not change how `job_postings` itself gets populated (that's still Stage 1
ingestion, outside this kit), so treat any connected source as
supplementary context for the client's own use, not something Stage 2
scoring reads from automatically.

**Ask once per client, not every rescan.** If a client already declined
or already has a connector in place from a prior run, don't re-offer it
on every subsequent scan of the same profile.

### 2c. If a connector is used, its results go into `job_postings` — not just a side channel
**Skip for a Large House Blend and Pastry** — same reasoning as 2b.

**Correction to the old assumption:** a connected job-search source is
not merely "supplementary context for the client's own use." If the
client accepts a connector in 2b and Claude actually pulls results from
it (e.g. `search_jobs`) during this session, those results are real
Stage 1 ingestion and must be written into `job_postings` **before**
Stage 2 scoring runs (step 5) — otherwise Stage 2 never sees them and the
whole point of offering the connector is lost.

- Map each result to `job_postings` columns: `title`, `company`,
  `location`, `url`, `created` (raw string as returned — parse per-source
  later, don't cast blindly), `description`, and set `source` to the
  connector's name (e.g. `'indeed_connector'`, `'ziprecruiter_connector'`)
  so these rows stay distinguishable from whatever Stage 1 process
  normally populates this table.
- This is an `INSERT`, so it follows the same rule as everything else in
  "Before any database write" above: show the user the resolved SQL (or a
  representative sample of it if the connector returned many rows, plus
  the total row count) and get a yes before running it. It is **not**
  covered by the one documented client-row exception.
- If no live Postgres connector is available, hand the user the resolved
  `INSERT` statement the same way as any other no-connector fallback in
  this doc, rather than silently dropping the pulled postings.
- Do this once per connector pull, not per rescan — if a prior session
  already ingested a given connector's results for this client, don't
  re-pull and re-insert duplicates on every subsequent scan.

-e 
---

### 3. Draft content (this is the judgment step — do it carefully, once)
**Skip this entire step for a Large House Blend and Pastry** — Career Bio and the
keyword matrix don't feed into anything that product delivers. Go
straight to drafting the resume content and cover letter described in
step 8's "Assemble the resume/cover-letter JSON" section instead.

**For Price Fixe or Career Package: draft all of the below**,
before touching the database:
- **Career Bio** — five sections: Who You Are · What Makes You Unique (3–4
  points) · Where Your Title Undersells You · One Honest Gap Worth
  Naming · How This Shapes Your Search. See `/reference/career_bio_style.md`
  for tone and the worked example.
- **Keyword matrix** — exactly these seven layers, no others, with these
  fixed layer weights (locked decision, do not vary): Job Title **+3**,
  Skill **+5**, Tool/Framework **+4**, Certification **+5**, Industry **+2**,
  Seniority **+2**, Location **+1**. Per-keyword weight within a layer is
  your judgment (1–3), reflecting how specific/valuable that term is.
- **The honest gap.** If the resume shows a credential in progress, name it
  directly and leave it at **zero weight** in the Certification layer until
  the client confirms it's been earned — never guess or round up. If a
  credential is already confirmed passed (client said so in chat), it gets
  full weight and the Career Bio's "gap" section is written as *resolved*,
  not as an open gap. Log this as **"Data Queen's Notes"** for the Job
  Report — one sentence, replaced (not appended to) every time this profile
  is rescanned.
- **Seniority widening.** If the resume's scope exceeds its title (check:
  budget size, team size, program duration, user count), widen the Seniority
  layer explicitly and say why, in both the Career Bio and the Search
  Strategy deck's rule-set notes.

### 3b. Pre-flight check the drafted matrix
**Skip for a Large House Blend and Pastry** — there's no matrix to check.

Before writing anything to the database, run the drafted keyword matrix
through `scripts/evaluators/evaluate_profile.py`. It catches two distinct
failure patterns found the hard way, both only after a full scan and manual
review:
- **Yves Latreille** — generic single-word Seniority/Tool matches inflating
  false positives, and overlong Title/Skill phrases too specific to match
  how real postings are actually worded.
- **Alex Watson (GRC/Compliance profile)** — a short bare keyword ("NIST")
  matching as a *substring inside unrelated words* ("admi**NIST**rator"),
  since the scoring query's `ILIKE '%keyword%'` has no word-boundary
  concept. This inflated 28 of 50 postings into false HIGH/MEDIUM ratings
  before manual review caught it. The evaluator's SUBSTRING RISK check
  (added after this incident) now flags any short single-word keyword
  before it ever reaches a scan — prefer a longer/quoted variant (e.g.
  "NIST framework" over bare "NIST") whenever it fires.

A clean report isn't a guarantee, but a "revise before running Stage 2"
recommendation means real rework, not a maybe — go fix the flagged
keywords before continuing.

### 3c. Also pre-flight check any LATER single-keyword addition
Not just the initial draft. If a client confirms a credential after their
first scan (an honest gap becoming resolved) or any other single keyword
gets added to an already-live profile, run it through the evaluator too —
`python3 scripts/evaluators/evaluate_profile.py --keyword CISM` (defaults
to the certification layer; pass `--layer` for anything else). Skipping
this for a "just one keyword, what could go wrong" addition is exactly
what let CISM slip through and match two unrelated Club Monaco retail
postings on Alex Watson's rescan — a single short keyword is just as
capable of a substring collision as one buried in a 30-keyword matrix.

**Removing a keyword from an already-scanned profile: deactivate, don't
delete.** Once Stage 2 has run at least once, `posting_keyword_matches`
holds rows referencing that `profile_keywords.id` — a hard `DELETE` on
`profile_keywords` will fail on the `posting_keyword_matches_profile_keyword_id_fkey`
foreign key constraint. Use `UPDATE profile_keywords SET active = false
WHERE profile_id = :profile_id AND layer = :layer AND keyword = :keyword;`
instead — the scoring query already filters on `pk.active = true`, so this
takes effect on the next rescan with no FK conflict. `DELETE` only works
cleanly on a keyword that was never scored yet (e.g. correcting a typo in
the same session, before Stage 2 has run).

### 4. Write to the database
**Skip this entire step for a Large House Blend and Pastry** — no client/profile/keyword
rows needed when there's no Job Report, Career Bio, or Search Strategy to
back them.

**Always ask explicitly before assuming either way — don't infer "new
client" just because this is the first time this profile has come up in
chat.** In one message: ask the user "Is [Client Name] already in the
system?" and, in the same message, hand them the resolved lookup query
(real `notify_email` filled in, not the `:placeholder` form) from
`sql/00_setup_profile.sql`'s `SELECT ... WHERE notify_email = ...` so they
can answer either from memory or by running it themselves. If a live
Postgres connector is available in this project, run the lookup directly
instead of asking them to — but still tell them what it found rather than
silently proceeding.

Two outcomes once the lookup answer comes back:
- **Found** — reuse the existing `client_id`. Do not create a second client
  record for the same person; go straight to creating the new profile under
  their existing `client_id` (this is how Mike ended up with two profiles —
  Fintech Industry and Tech Skills — under one client record).
- **Not found (or "I don't know" from the user)** — treat as not found.
  If a live Postgres connector is available, run the client-creation insert
  from `sql/00_setup_profile.sql` automatically, no separate confirmation
  needed for that specific insert (see the exception noted above). **If no
  live connector is available, don't run anything — hand the user the
  resolved `INSERT` statement (real values filled in) from
  `sql/00_setup_profile.sql` and ask them to run it and paste back the
  resulting `client_id`.** Either way, continue to the profile insert next,
  which **does** need confirmation as usual regardless of connector
  availability.

### 4a. Check BOTH scoring systems, not just `profile_keywords`
This database currently runs two independent, non-synced scoring systems
that both write to the same `job_postings`/`posting_scores` tables — see
`profile_scoring_rules` vs. `profile_keywords` in the README/schema notes.
Checking only one of them before onboarding a client is how "Mike -
Financial Services" (`profile_scoring_rules`) ended up as a separate
profile row from "Mike MacLeod - Financial Services Industry"
(`profile_keywords`) instead of the same person's same profile being
represented once across both.

Before writing anything, run the lookup for **both** systems, not just the
`clients` lookup above:

```sql
SELECT p.id AS profile_id, p.name AS profile_name, 'profile_scoring_rules' AS source
FROM profiles p JOIN profile_scoring_rules r ON r.profile_id = p.id
WHERE p.client_id = :client_id
UNION ALL
SELECT p.id, p.name, 'profile_keywords'
FROM profiles p JOIN profile_keywords k ON k.profile_id = p.id
WHERE p.client_id = :client_id
ORDER BY 1, 3;
```

- **If the client is new to both** — proceed with one profile row per
  strategy angle (as usual), and if they're also going into
  `profile_scoring_rules` (see 4b), use that **same `profile_id`** for
  both systems. Never create a second, differently-named profile row for
  the same person/angle just because it's the first time touching the
  second system.
- **If the client already has a `profile_keywords` matrix but is being
  added to `profile_scoring_rules` for the first time** (or vice versa) —
  attach the new system to the *existing* `profile_id`, don't create a new
  one.
- **Flag, don't silently proceed, if a profile is about to become active
  in both systems at once.** `sql/02_stage2_scoring.sql` (matrix-driven,
  hardcoded 8/4 thresholds) and the n8n nightly scan (flat-list-driven,
  its own DB-stored thresholds) will both independently write
  `posting_scores` rows for that same `profile_id` — whichever last ran
  wins, and the two methodologies can disagree. This is a known live
  condition on Yves Latreille's profile today. Tell the user this
  explicitly before making it true for a new client too, even if they've
  already said yes — a one-line reminder of what "yes" means here, every
  time, not just the first time it comes up.

### 4b. Adding a client to `profile_scoring_rules` (n8n nightly scan)
Optional, separate from the Job Report pipeline — only do this if the user
asks for it. Derive the flat shape from the already-drafted 7-layer matrix
rather than drafting it twice:
- `keywords` (jsonb array) — flatten title + skill + tool layers into a
  single lowercase list (this is what `scoreTrack()` in the n8n workflow's
  scoring code matches against title/description text; it doesn't know
  about layers or weights, just a flat list scored +3 title-match / +1
  description-match per keyword).
- `exclusion_rules` (jsonb array) — same shape as the workflow's
  `ruleMatches()` expects: objects like
  `{"type": "title_contains_any", "terms": [...]}`. Leave empty (`[]`) if
  the matrix's Certification-gap / substring-risk work already handles
  exclusions adequately at the `profile_keywords` layer, or add rules for
  anything genuinely likely to false-positive in a title-only check.
- `high_threshold` / `medium_threshold` — reasonable starting points are
  the same 8/4 used in `sql/02_stage2_scoring.sql`, adjusted only if the
  flat scoring produces obviously different score magnitudes than the
  weighted matrix (it will tend to score lower, since it's missing the
  certification/industry/seniority/location layers entirely — check a
  handful of known-good postings by hand before trusting the default).
- Confirm this insert with the user the same as any other write.

### 4c. Continue with the standard keyword matrix insert

```sql
-- Insert the keyword matrix — see sql/01_insert_keywords.sql
```
**Show the resolved SQL (real values filled in, not the `:placeholder` form)
to the user and get a yes before running it.** If no live Postgres connector
is available in this project, stop here and ask the user to run
`sql/00_setup_profile.sql` + `sql/01_insert_keywords.sql` and paste back the
resulting `profile_id`.

**When "both" angles are being run for one client**, combine both profiles'
keyword rows into a single `INSERT...VALUES` statement (varying `profile_id`
per row) rather than one statement per profile — see the note at the top of
`sql/01_insert_keywords.sql`. Same confirmation requirement applies either
way; this only reduces round trips against a SQL client that has a history
of flaking on multi-statement files.

-e 
---

### 5. Run Stage 2 scoring, then pull ONE query per profile — not four
**Skip this entire step for a Large House Blend and Pastry or Career Package without
job_report** — Stage 2 scoring only matters if job_report is one of the
selected product's documents. (Career Package always includes job_report,
per the registry, so it still needs this step — only Large House Blend and Pastry
skips it.)

**Confirm before running Stage 2, same as step 4** — it deletes the
profile's prior scores before reinserting, which is intentional (keeps
reruns clean) but is still a delete, and still needs a yes first. Run
`sql/02_stage2_scoring.sql` (substitute the real `profile_id`). This scores
the **full posting pool**, no filtering by title or industry — that
constraint is intentional and protects the cross-industry-discovery goal
(a fintech-skilled PM should be able to surface at a non-finance employer).

Then run **only** `sql/05_export_full_dataset.sql` (same profile_id, no
LIMIT) and export the result to CSV (`{profile_name}_full_scan.csv`). This
is read-only, no confirmation needed, and it is a **superset of
everything else this workflow used to query separately**: the top 100 is
just this data sorted by `keyword_score` descending and truncated to 100
rows; the rating breakdown is a `COUNT(*)` grouped by `rating`; the
company-by-score and company-by-volume stats are both a `GROUP BY company`
over the same rows. `sql/03_pull_top50_for_review.sql` and
`sql/04_company_stats.sql` still exist and still work — keep them around
for a quick manual spot-check in the SQL client if you ever want one — but
they are no longer part of the required workflow. Derive everything from
the one export, in Python, yourself:

```python
import csv
rows = list(csv.DictReader(open("{profile_name}_full_scan.csv"), delimiter=';'))
rows.sort(key=lambda r: int(r['keyword_score']), reverse=True)
top100 = rows[:100]
# rating breakdown: count rows by r['rating']
# company by score: group by r['company'], avg(keyword_score), 3+ postings,
#   count(distinct matched_keywords entries) for the keyword-diversity column
# company by volume: group by r['company'], count
```

This isn't just less typing. Every extra query is another round trip
through a SQL client that has repeatedly thrown an unexplained "Unknown
error" on both multi-statement files and, on Alex Watson's rescan, even a
single clean statement — for reasons that were never fully diagnosed. The
fewer queries required, the less surface area for that flakiness to bite.
If you genuinely need to sanity-check a derived number against the
database directly, `sql/03` / `sql/04` are still there — but default to
deriving, not querying, from here on.

Export the same full-dataset CSV twice for the zip: once as-is
(`{Client}_Full_Scan_{Strategy}.csv`, deliverable #10) and once sorted and
truncated to the top 100 (`{Client}_Top50_{Strategy}.csv` -- filename kept
for backward compatibility, contents are the top 100, deliverable #9).
Both get passed to `build_report.py` via `--full-dataset-csv` /
`--top50-csv` in step 8 (flag names likewise kept as-is; they accept
whatever row count you pass).

**Also run `sql/08_recompute_employer_automated_scores.sql` once per
client session** (not once per profile — it's global, so running it
twice in the same session because both of a client's profiles got
scanned is harmless, just redundant). This recomputes every employer's
posting-staleness score (−5 per group of 5 postings open 45-89 days,
−10 per group of 5 open 90+ days, fully overwritten each run) and feeds
step 6c's employer warning check.

### 6. Manually review the top 100 — do not skip this
**Skip for a Large House Blend and Pastry** (no job_report means nothing to review).

**Standard as of the pilot run on Yves Latreille's profile: review window is
100, not 50** -- the larger pool surfaced real matches a stricter top-50
cutoff was hiding under generic-keyword noise, and the removal-rate
discipline below held up fine at the larger size (23% survival on that
run, actually *more* rigorous than the prior 22% at n=50). This also
underpins the $99 price point for this package -- a materially larger,
more thoroughly reviewed pool is real, demonstrable justification for it,
not just a bigger number.

The score cannot tell a bank's Program Manager from a nonprofit's. Read
all 100 (as derived above) against the resume, the Career Bio, and the
keyword strategy. Sort into:
- **Best** — real employer/industry fit, right function, right seniority
- **Worth a Look** — plausible but caveated (geo mismatch, vague agency
  posting, transferable-but-different industry)
- **Removed** — wrong industry entirely, wrong function despite a matching
  employer, wrong seniority, duplicate/repost, false-positive substring
  match, agency posting with no employer disclosed, or a posting confirmed
  closed and older than ~90 days (a legitimately closed listing the scan
  caught late, not a broken link — see the note on framing below).

If a posting's link works and correctly shows it closed, don't treat that
as a data problem — the employer did their job maintaining their listing;
the scan was just late. Log it as removed on age/closed-status grounds, but
phrase it as a scan-timing gap, never as a mark against the employer (e.g.
"posting closed by employer since scan time — their listing hygiene was
correct" rather than anything implying the employer or the posting itself
was unreliable). This is distinct from a genuinely broken/stale link, which
gets flagged and fixed with a live replacement instead of removed.

Tally removal reasons into categories with counts (this becomes the "Why 29
were removed"-style breakdown). Do not exceed roughly half the 100 surviving
as Best+Worth a Look — if nearly everything survives review, the review
wasn't rigorous; go back through it.

On a **rescan** of an existing profile (new posting-pool data, or a keyword
change like a confirmed certification), diff the new top 100 against the
previous one before re-reviewing from scratch — most rows are unchanged,
and the fast path is to identify what's new, what dropped off, and only
freshly categorize the delta. This is how the CISM substring collision on
Alex Watson's rescan got caught quickly: two unfamiliar rows appeared
(Club Monaco x2), got checked on their own merits, and turned out to be a
repeat of the NIST failure class under a new keyword.

### 6c. Flag current-employer postings, and check every survivor's employer standing
**Skip for a Large House Blend and Pastry** (no job_report, nothing to review).

Before finalizing Best / Worth a Look / Removed, cross-check every survivor
against `clients.current_employer`:

- **Any posting where `job_postings.company` matches the client's current
  employer gets tagged as an internal move** (e.g. "Internal — you already
  work here") regardless of anything else — this is real, useful signal on
  its own: internal applicants typically have referral paths and visibility
  a cold external applicant doesn't. Note it in the posting's `why` text.
- **Check whether `current_employer` has a row in the `employers` table.**
  If not, this is a judgment call to make now, not skip: web-search for
  recent, sourceable signal (layoffs, lawsuits, executive turnover, public
  reporting on culture — not anonymous review-site aggregates, which are
  noisy and not verifiable) and record a `toxicity_score` of `negative`,
  `neutral`, or `positive` with a one-to-two-sentence `toxicity_notes`
  citing what it's based on. This is a one-time-per-employer lookup —
  reuse the `employers` row on future scans/clients rather than
  re-researching every time, unless `checked_at` is more than a few months
  old.

**Then, separately, check EVERY survivor's employer — not just
current-employer — against `employers.total_score`.** This is the
posting-staleness signal from `sql/08` (run in step 5) combined with any
manual adjustment, not the categorical toxicity_score above — they're
deliberately separate checks (see `schema_setup.sql`'s comments on why
`toxicity_score` was kept categorical rather than folded into the sum).

- **`total_score <= -10` triggers a warning on that posting, regardless of
  how fresh or well-matched the specific posting looks.** This is the
  exact scenario that motivated the feature: a posting can be a 40-day-old,
  perfectly-matched listing, and still carry real risk if the *employer*
  has a pattern of postings sitting open 90+ days across many roles. State
  the employer's standing plainly in the `why` text (e.g. "Strong fit, but
  flagged: employer has posted 3+ postings open 90+ days — possible
  low-conversion or ghost-job pattern") rather than silently downgrading
  or removing it — same principle as the toxicity check above: the client
  sees the call, not just the consequence of it.
- This check applies whether or not the posting is from the client's
  current employer — an unrelated employer's staleness pattern is just as
  real a warning as a toxicity flag on a current employer would be.
- **If BOTH `toxicity_score = 'negative'` AND `total_score <= -10` apply
  to the same employer**, combine both reasons in the `why` text rather
  than picking one — they're independent signals and both are relevant.
- **Writing a new `employers` row (for the toxicity half) is a normal
  write** — confirm before running it, same as anything else, and pair it
  with the standard verification query (`SELECT name, toxicity_score,
  automated_score, manual_adjustment, total_score FROM employers WHERE
  name = :name;`). See `sql/06_add_employer_toxicity.sql` for the full
  templated version of the toxicity-research half (lookup, insert/refresh,
  point the client at it, paired verification). `automated_score` itself
  is never hand-written — it only ever comes from `sql/08`.

### 7. Pull the top companies for "What's Working"
**Skip for a Large House Blend and Pastry** (feeds the Job Report only).

The company-by-score (with the keyword-diversity middle column) and
company-by-volume breakdowns are both derived from the same full-dataset
export, not queried separately. Nothing further to run for those two.

A third column, **Top Companies by Keyword**, is scoped to the **Top 100
only** (not the full pool) — derive it from the same top-100 export used
for the manual review, counting total matched-keyword tokens per company
(`matched_keywords` field, split on ` | `). Before ranking, **normalize
obvious company-name variants** (e.g. "TD Bank" / "TD" / "The
Toronto-Dominion Bank" are one bank; "0000050007 Royal Bank of Canada" /
"Royal Bank of Canada" / "RBC" are one bank) — check the distinct company
list for this every time, since which variants appear is data-dependent
and raw unmerged counts can change who's actually in the top 5. This label
is rendered dynamically by `job_report.py` (reads the actual reviewed
count, not a hardcoded 50/100) so it stays correct if the window size ever
changes again.


---

### 8. Generate the documents
**Pagination rule for the Job Report's Best and Worth-a-Look lists: maximum
8 rows per slide.** `job_report.py` handles this automatically —
`_render_result_section()` splits each list into pages of up to 8 items
and labels each "(page X of Y)" when there's more than one, rather than
shrinking font size indefinitely to cram a longer Top-100-era list onto a
single slide. Nothing to do here except know it's automatic: a profile
with 15 Best matches produces 2 Best-matches slides (8 + 7), not one
overcrowded one. This applies per section (Best and Worth a Look are
paginated independently) — a long Worth-a-Look list doesn't affect how
many pages Best gets, and vice versa.

Assemble JSON objects **per what the product actually needs**:
- **Price Fixe or Career Package**: `profile_data.json` matching
  `sample_data/example_profile.json`'s shape (Career Bio / Search
  Strategy / Job Report content).
- **Price Fixe**: also `resume_data.json` matching the shape used by the
  Custom Resume Kit's `sample_data/example_resume_data.json` (structured
  resume content plus the drafted `cover_letter` object — see the Shared
  Kit's exact-format spec, including the zero-em-dash check).
- **Career Package**: only `profile_data.json` is needed — do not draft
  or pass `resume_data.json` at all.
- **Short Stack**: only the `job_report` portion of `profile_data.json`
  is needed, but the keyword matrix must still be drafted (step 3) since
  `job_report` displays it — see `requires_keyword_matrix` in
  `products.json`.

Then run `build_report.py` with `--product` set to whichever was
confirmed in step 2.

**Price Fixe:**
```bash
python3 scripts/build_report.py --product "Price Fixe" \
  --data profile_data.json \
  --resume-data resume_data.json \
  --image assets/aida_cover_illustration.png \
  --top50-csv {profile_name}_top50.csv \
  --full-dataset-csv {profile_name}_full_scan.csv \
  --outdir ./output
```

**Career Package** (no `--resume-data` needed):
```bash
python3 scripts/build_report.py --product career_package \
  --data profile_data.json \
  --image assets/aida_cover_illustration.png \
  --top50-csv {profile_name}_top50.csv \
  --full-dataset-csv {profile_name}_full_scan.csv \
  --outdir ./output
```

**Short Stack** (job_report only):
```bash
python3 scripts/build_report.py --product short_stack \
  --data profile_data.json \
  --image assets/aida_job_report_illustration.png \
  --top50-csv {profile_name}_top50.csv \
  --full-dataset-csv {profile_name}_full_scan.csv \
  --outdir ./output
```

`build_report.py` in this kit calls `career_bio.py`, `search_strategy.py`
(both live in `AIDA_Shared_Kit/scripts/` — make sure that kit is loaded
alongside this one, or `build_report.py`'s imports will fail) and
`job_report.py` (in this kit), each built on `design_system.py` (also in
Shared). CSVs are only copied in and only relevant when `job_report` is
in the product's document list.

> **Kit-split note:** this kit's `build_report.py` no longer has a Large
> House Blend and Pastry / Custom Resume Package branch. That product's
> generation is handled by direct per-script invocation in the Custom
> Resume Kit, not through this orchestrator — see that kit's instructions.

### 8b. Log a rough effort estimate for this run
**Skip for a Large House Blend and Pastry** — no scan/review pipeline ran, so most of
the signals below don't apply; log a `client_runs` row anyway with just
`product`, `drafted_content_chars`, and `db_writes` if you want Resume
Package runs represented at all, otherwise skip entirely.

**This is a proxy, not real token telemetry.** Claude has no access to
actual token counts consumed by a conversation — not this one, not a past
one — because this runs through the Claude.ai chat interface, not the API
with per-request usage metadata. What follows is a rough, relative
estimate built from things that actually are countable, useful for
comparing "this client took a lot more work than usual" across runs, not
for precise billing or margin calculations. Say this plainly if the user
asks what the number means.
-e 
---


Count these directly from the run:
- `postings_scanned` — the full pool size (e.g. 5047)
- `postings_reviewed` — count of Best + Worth a Look + Removed
- `db_writes` — every confirmed INSERT/UPDATE/DELETE actually run this
  session (client/profile creation, keyword inserts, deactivations,
  Stage 2 rescores, employer rows — count each statement, not each
  round of confirmation)
- `matrix_revisions` — count of revise-and-rescore cycles (a keyword
  change followed by a Stage 2 rerun counts as one, regardless of how
  many keywords changed in that cycle)
- `drafted_content_chars` — character count of the Career Bio + Search
  Strategy + Job Report text, plus resume + cover letter content, that
  Claude actually wrote for this run (sum the JSON string values, not
  the JSON structure itself)

Then compute:

```
estimated_tokens =
    (drafted_content_chars / 4)
  + (postings_reviewed * 150)
  + (db_writes * 300)
  + (matrix_revisions * 800)
```

The four multipliers (4 chars/token, 150, 300, 800) are educated
estimates of the reasoning + tool-call overhead behind each unit, not
calibrated against real usage data. If real token data ever becomes
available (e.g. this kit moves to API-based invocation — see the
Option A discussion on token tracking), recalibrate these multipliers
against actual numbers rather than trusting them as-is.

Insert one `client_runs` row per profile (or one for the whole client if
the product doesn't have separate profiles). This is a normal write —
confirm before running it, same as everything else — paired with the
standard verification query. See `sql/07_log_client_run.sql` for the full
templated version of this (insert, paired verification, and a handy
rollup query for comparing recent runs):

```sql
SELECT client_id, profile_id, product, estimated_tokens, run_at
FROM client_runs WHERE client_id = :client_id ORDER BY run_at DESC;
```

### 9. Deliver
- `present_files` the zip so the user can download it directly. This is the
  complete delivery step — there is no Drive upload. **Do not call the
  Google Drive `create_file` tool for this zip, or any zip, under any
  circumstances.** This was attempted repeatedly across multiple client
  runs and never worked reliably (the file is too large to pass through
  as inline content in a tool call) — it is a closed decision, not
  something to re-attempt because "this file might be smaller" or "it's
  worth trying again." If a client wants their report in Drive, they
  upload the downloaded zip themselves.
- Tell the user directly: what's in the zip, and one honest line about scan
  limitations that turn (e.g. "employer-reputation scoring isn't live yet,"
  or "thresholds are provisional until we have more scan history").

### 10. Optional: email the zip to the client
Only if the user asks for this on a given report — not a default action.

**What's actually possible, checked directly against this project's Gmail
tool rather than assumed:** there is no send capability at all, only
`create_draft` — and its own tool description states attachment support
isn't implemented yet, despite an `attachments` field existing in its
schema. Do not attempt to attach the zip; it will not work as expected.
There is also no Drive link to point to (see step 9 — no Drive upload
happens), so a drafted email can only describe the deliverable in prose
and direct the recipient to wherever the user has actually put the file,
not link to it directly.

**Correct sequence:**
1. Ask the user, by name, every time: *"Want me to draft an email to
   [client's notify_email] about their report? I'll leave it in Drafts —
   you'll still need to attach the file yourself and send it."* Wait for a
   clear yes.
2. If yes, call `Gmail:create_draft` with `to: [notify_email]` and a
   subject naming the client and strategy. Note in the draft body that the
   report is ready and needs to be attached manually — Claude cannot
   attach or link it.
3. **Never claim the email was sent.** It wasn't, and can't be by this
   tool. Tell the user exactly what happened: "Draft created in Gmail,
   addressed to [email], not sent, no attachment — it's waiting in Drafts
   for you to finish and send."

If a future version of this project has a genuine send-capable email
connector instead of Gmail's draft-only tool, the confirmation step in (1)
still applies unchanged — never auto-send a client-facing report email
without that per-report yes, regardless of what the tool is capable of.

