job-tracker

What this skill does

# Job Application Tracker

Track job application status by scanning Gmail and updating the Google Sheets "Job Tracker" in the hojicha Drive folder.

## Target Email

[email protected]

## Sheet Schema

The Job Tracker sheet has these columns (in order):

| Column | Type | Values |
|--------|------|--------|
| A: Company | Text | Company name |
| B: Role | Text | Role/position title |
| C: Stage | Dropdown | Applied, Phone Screen, Online Assessment, Behavioral Interview, Onsite Interview, Rejected, Ghosted, Offered |
| D: Last Contact Date | Date | YYYY-MM-DD format |
| E: Notes | Text | Append-only summary notes |

**Sheet tab name:** `job-tracker`. Always use `list_sheets` to confirm if unsure.

## Email Classification

Classify each email into a Stage using signals from the subject line, body, and sender. Refer to `references/email-patterns.md` for the full pattern catalog.

**Stage mapping priority:** If an email matches multiple stages, use the most advanced stage. For example, an email mentioning both "application received" and "online assessment link" maps to Online Assessment.

## Stage Progression Rules

Stages have a natural forward order:

```
Applied → Phone Screen → Online Assessment → Behavioral Interview → Onsite Interview → Offered
(Rejected/Ghosted can override any stage — they are terminal states)
```

**Rules:**
1. A stage update can only move **forward** in the progression above.
2. **Rejected** and **Ghosted** can override any stage (they are terminal states).
3. Never regress a stage (e.g., if currently at Onsite Interview, ignore an old "application received" email).
4. **Ghosted** is only suggested when there is no email activity for >30 days since the Last Contact Date. Always ask the user for confirmation before marking Ghosted.

## Entity Extraction

For each email, extract:
- **Company** — the hiring company name
- **Role** — the position title
- **Stage** — inferred from email classification
- **Date** — the email's sent date (for Last Contact Date)
- **Note** — a one-line summary of the email content (e.g., "Received OA link via HackerRank", "Rejection — position filled")

Use the fallback order in `references/email-patterns.md` for entity extraction when company/role are not obvious.

## Matching Rules

Match emails to existing sheet rows by **Company + Role** (case-insensitive, fuzzy). Examples:
- "Citadel Securities" matches "Citadel" — same company
- "Software Engineer, Infrastructure" matches "SWE Infra" — use judgment on abbreviations

**When in doubt, flag as ambiguous.** If you are not confident that a company or role name refers to an existing row, do not silently create a duplicate — present it as ambiguous and let the user confirm the match or create a new row.

If no match is found and the email indicates a new application, create a new row with Stage = Applied.

## Update Rules

1. **Existing row, stage change:** Update Stage column. Update Last Contact Date. Append to Notes.
2. **Existing row, no stage change:** Update Last Contact Date. Append to Notes.
3. **New application:** Add row with Company, Role, Stage = Applied, Last Contact Date = email date, Notes = summary.
4. **Ambiguous email:** Cannot determine company, role, or stage — flag for user with email subject, sender, and a snippet. Never skip silently.

## Notes Column

- **Append, never overwrite.** Add new notes on a new line prefixed with the date.
- Format: `YYYY-MM-DD: <summary>`
- Example: `2026-02-26: Received OA link via CodeSignal`

## Output Format

After scanning, present results to the user in this format:

### Updates (existing rows)

```
| Company | Role | Old Stage | New Stage | Note |
|---------|------|-----------|-----------|------|
| Citadel | Quant Researcher | Applied | Online Assessment | Received OA link |
```

### New Applications

```
| Company | Role | Note |
|---------|------|------|
| Jane Street | SWE Intern | Application confirmation email |
```

### Ambiguous (needs your input)

```
- Email from [email protected] (subject: "Quick question") — cannot determine role. Skip or provide details?
```

### No Changes

If no relevant emails found, say so.

**After presenting:** Ask the user to confirm before writing any changes to the sheet.

## Write Safety Rules

### NEVER use `add_rows` to append data

The `add_rows` tool inserts blank rows at the TOP of the sheet by default (when `start_row` is omitted), which shifts all existing data down and corrupts pre-calculated cell ranges. Instead:

1. **To append new applications:** Use `update_cells` targeting the first empty row. If the last data row is row 32, write to range `A33:E33`.
2. **To update existing rows:** Use `update_cells` with the exact cell range (e.g., `C5` to update a stage).
3. **`add_rows` is banned** unless the sheet grid physically has no empty rows left. If you must use it, you MUST set `start_row` to the last row index so rows are added at the bottom, never the top.

### Pre-write snapshot

Immediately before ANY write operation, re-read the full sheet with `get_sheet_data`. This fresh snapshot is your baseline — do not rely on earlier reads (data may have changed).

**Important:** Tell the user not to edit the sheet until writes are complete. There is a short window between reading the snapshot and writing where manual edits could be overwritten.

Calculate all target cell ranges from this snapshot. For appends, find the last occupied row and target the next row.

### Post-write verification (new rows only)

After appending new rows, re-read the sheet and verify:
- All pre-existing rows from the snapshot are unchanged (same values, same positions)
- New rows appear at the expected positions with correct values

### On verification failure

If any mismatch is detected:
1. **Stop immediately** — do not attempt further writes
2. **Present the mismatch** — show expected vs actual values, which rows shifted or were overwritten
3. **Propose a recovery plan** — list specific `update_cells` calls that would restore the original data from the cached snapshot
4. **Wait for user confirmation** — do NOT execute recovery automatically

## Tool Reference

Tool name prefixes vary by environment. On local Claude Code they use `mcp__plugin_aerion_gmail__` and `mcp__plugin_aerion_google-sheets__`. On Cowork they may use different prefixes (e.g., connector names or UUIDs). Look up available tools by function name, not prefix.

| Action | Function name to look for |
|--------|--------------------------|
| Search emails | `gmail_search_messages` or `Search Gmail Emails` |
| Read email | `gmail_read_message` or `Read Gmail Email` |
| Read email thread | `gmail_read_thread` or `Read Gmail Thread` |
| List spreadsheets | `list_spreadsheets` |
| List sheet tabs | `list_sheets` |
| Read sheet data | `get_sheet_data` |
| Update cells | `update_cells` |
| Add rows | `add_rows` |