# Tools — Bill Through Credit Card Expenses

**Status:** Phase 1 + Phase 2 shipped 2026-05-25.
**Lives at:** `/tools` (landing) + `/tools/cc-expenses` (list) +
`/tools/cc-expenses/[id]` (detail) + `/tools/cc-expenses/rules`
(highlight keywords + auto-assign rules) · `app/(app)/tools/...` ·
`lib/cc-expenses.ts` · uses `cc_expense_batches`, `cc_expense_lines`,
`cc_highlight_keywords`, `cc_auto_rules` tables. Top-nav "Tools"
dropdown surfaces it.

## Summary
A pass-through billing helper for the legacy PlusROI sheet. Paste raw
lines from a credit-card statement into a batch, assign each line to
a (client × project) + description + category, then copy the
tab-separated paste block into the destination sheet. Replaces a
manual transcription step.

Same paste-block shape as the invoice export (see
[invoices.md](invoices.md)) but column 2 holds the line's
assignment description ("Google", "Facebook") and column 7 is the
category ("Client Advertising Spend") instead of fixed strings.

## Why
2026-05-25 — "I need a tools menu item. I'll put a bunch of custom
tools in there as submenu items. The first one is Bill Through
Credit Card Expenses... I need to paste in items from my credit
card statement... ability to assign some of those to a project...
generate a tab-separated paste block I can copy into another sheet."

## Data model

### Tables
- `cc_expense_batches(id, org_id, name, created_by, created_at)` —
  one batch per CC statement (or arbitrary group). RLS scoped to
  org members.
- `cc_expense_lines(id, batch_id, line_index, raw_line,
  expense_date?, description?, amount_usd?, balance_usd?,
  project_id?, assignment_description?, category?, auto_assigned,
  created_at)` — one row per parsed line. `line_index` preserves
  paste order across multiple appends. `raw_line` retained for
  audit / re-parse. `auto_assigned` is TRUE when the assignment
  was filled in by a rule (paste-time or Re-apply) and FALSE the
  moment a user saves the row — drives the orange-vs-green row
  color. RLS inherits via batch_id → batch.org_id.
- `cc_highlight_keywords(id, org_id, keyword UNIQUE per org,
  created_at)` — substring matchers that paint matching rows
  yellow on the detail page. Owner-managed via
  `/tools/cc-expenses/rules`.
- `cc_auto_rules(id, org_id, keyword UNIQUE per org, project_id,
  assignment_description?, category?, created_at)` — substring →
  assignment template. Applied at paste time (only) by
  `appendCcLines`. `project_id` is required and ON DELETE CASCADE
  (deleting a project drops its rules; merge via
  /projects/manage if you want continuity).

### Index
- `cc_expense_lines_batch_idx` on `(batch_id)`.
- `cc_expense_lines_project_idx` partial on `(project_id) WHERE
  project_id IS NOT NULL` for fast "which expenses for this project?"
  lookups.

### Permissions
- Both tables: RLS allows any org member to select/insert/update/delete.
- The UI gates everything behind `canTransfer(eu)` (owner-only)
  — there's no use case for managers here yet.

## Behavior

### Top-nav
- "Tools" appears as a hover-dropdown in the top nav (owner-only).
  Hover → submenu lists each tool. Clicking the top-level item
  goes to `/tools` (landing).

### `/tools` landing
- Card list of available tools. Just one for now: Bill Through
  Credit Card Expenses.

### `/tools/cc-expenses` (list)
- Table of batches sorted newest-first. Columns: name (link),
  lines, assigned (count + %), total amount, created date, delete.
- Inline "+ New batch" form (toggled by `?new=1`). Required: name.

### `/tools/cc-expenses/[id]` (detail)
- Header: batch name + breadcrumb back + summary line (totals) +
  **Re-apply rules** button + **Manage rules** link.
- **Re-apply rules** posts to the `reapplyRules` server action.
  Scans every UNASSIGNED line (`project_id IS NULL`) in this batch
  against the current `cc_auto_rules`, applies any match, and
  stamps `auto_assigned=true`. Assigned lines are not touched —
  this is purely "fill in the blanks" with the current rule set.
  The post-action info banner reports the count of newly-assigned
  lines (or a friendly message if there were no matches / no
  unassigned lines / no rules to apply).
- **Paste-in panel** — textarea + Add lines button. Pastes are
  parsed by `parseCcPaste()` and matched against
  `cc_auto_rules` (case-insensitive substring, first-match-wins
  by created_at desc); matched lines get `project_id` +
  `assignment_description` + `category` pre-filled. Multiple
  pastes are additive; `line_index` continues from the current max.
  The post-add info banner reports how many were auto-assigned.
- **Lines table** — one row per `cc_expense_lines` row. Columns:
  Date / Description / Amount / Assignment (Project combobox +
  Description text + Category text-with-datalist + Save) / Delete.
  - Row backgrounds (priority — assigned state dominates):
    - **Green** if `project_id` is set AND `auto_assigned = FALSE`
      — user-saved (confirmed). Wins because the user has
      explicitly taken ownership of the row.
    - **Orange** if `project_id` is set AND `auto_assigned = TRUE`
      — the row was filled in by a rule but the user hasn't
      saved it yet.
    - **Yellow** if `project_id IS NULL` (unassigned) AND the
      description matches any `cc_highlight_keywords` entry —
      "look at this; it's probably assignable." Yellow ONLY
      paints when no assignment color applies.
    - Neutral otherwise.
    - Hover on a yellow row shows the matched keyword as a tooltip.
    - The lines-section header carries a small color legend so
      the scheme is discoverable.
  - Date that failed to parse is shown as a red `?` so it's
    obvious which lines need a fix.
  - The Project combobox uses the same `SearchableCombobox` as
    the /entries bulk-reassign UI (ADR #042) — type to filter,
    label `{client} : {project}`, value `projects.id`.
  - Category input has a `<datalist>` of distinct categories used
    across the org so frequently-used names autocomplete.
  - Per-row Save submits just that row's `updateCcLine` action.
- **Paste block panel** — tab-separated text for the PlusROI
  sheet. One line per ASSIGNED row that has a parsed
  `expense_date` (lines missing the date are silently skipped —
  the destination needs the date column). Copy-to-clipboard
  button + a `<pre>` for triple-click fallback.

### `/tools/cc-expenses/rules` (rules management)
- Header + breadcrumb back to `/tools/cc-expenses`.
- **Highlight keywords** section: simple list with inline "add
  keyword" form and per-row delete. Keyword is the only field.
  Sample rows hint at the yellow color via background.
- **Auto-assign rules** section: list with inline "add rule" form
  (keyword + project combobox + description + category) and
  per-row Edit + Delete. Editing uses `?edit=<rule_id>` (same
  URL-state pattern as the /team page). Category input has a
  shared `<datalist>` populated from existing rule categories.
- Both lists are owner-only (`canTransfer` server-side check;
  page renders an "owner only" message for non-owners).

### Parser (`lib/cc-expenses.ts`)
- Tab-separated columns recognized (greedy): `date, description,
  amount, credit?, balance?`. Trailing tabs tolerated.
- Date supports long format ("May 20, 2026", case-insensitive,
  comma optional) and ISO (YYYY-MM-DD).
- Amount strips `$` and commas. If debit column is blank, the
  credit column is used (no sign distinction in Phase 1 — Phase 2
  may add it).
- Partial parses keep the row: e.g., unparseable date doesn't
  block the row from being inserted. The user sees the gap and
  can fix manually.

## Paste block format

Same 7-column layout as the invoice paste block (see
[invoices.md](invoices.md)):

```
{client} : {project}\t{assignment_description}\t{amount}\t{date}\t\tBowden Works\t{category}
```

Implementation: `formatCcPasteBlock(lines)` in
`lib/cc-expenses.ts`. Tested by CCE-X-001 … CCE-X-004.

## Constraints & edge cases
- Same batch can hold lines from multiple paste operations —
  `line_index` keeps order. Re-pasting the same CSV creates
  duplicates (no dedupe at parse time; user removes via per-row 🗑️).
- Lines without `project_id` are skipped in the paste block (you
  can't bill a line that isn't assigned). Lines without
  `expense_date` are also skipped to protect the destination
  sheet's date column.
- The Project combobox sources from existing `projects` entities
  (same as ADR #042's bulk-reassign UI). No way to create a new
  project from this page — use `/projects/manage` for that.
- Category is free-text. The autocomplete datalist pulls distinct
  values from `cc_expense_lines.category` across the org, so the
  first time you type "Client Advertising Spend" you have to
  spell it; subsequent uses suggest it.
- Deleting a batch cascades to its lines (FK ON DELETE CASCADE).
  No "are you sure?" beyond the DeleteForm confirmation.

## Tests
- ✅ CCE-D-001 … CCE-D-005 — date parser (long, ISO, garbage, padding).
- ✅ CCE-C-001 … CCE-C-003 — currency parser ($, commas, rounding).
- ✅ CCE-L-001 … CCE-L-005 — single-line parser (standard, credit,
  empty, raw preservation, partial parse).
- ✅ CCE-P-001 … CCE-P-002 — multi-line paste (blanks, CRLF).
- ✅ CCE-X-001 … CCE-X-004 — paste-block formatter (empty, shape,
  no $, joins).
- ✅ CCE-H-001 … CCE-H-005 — highlight matcher (substring,
  case-insensitive, null/empty, first-in-list-wins, empty-keyword
  skip).
- ✅ CCE-R-001 … CCE-R-005 — auto-rule matcher (matched fields,
  case-insensitive, no-match, first-match-wins, null/empty desc).
- 🟡 CCE-A-001 Adding lines twice preserves paste order via
  `line_index`.
- 🟡 CCE-A-002 Per-row Save updates only that row.
- 🟡 CCE-A-003 Delete batch cascades to lines.
- 🟡 CCE-A-004 Auto-rule fires on paste and pre-fills assignment.
- 🟡 CCE-A-005 Adding a new rule doesn't touch existing lines
  (rules apply only at paste time).

## Open considerations
- **Sign-aware amounts** — Phase 1 treats both debit and credit
  columns as positive amounts. If refunds become a regular
  workflow, distinguish them so refunds export as negative.
- **Dedupe at paste time** — fingerprint `date + description +
  amount` and skip exact matches that already exist in the batch.
- **Rule priority** — v1 uses created_at desc for tiebreak. An
  explicit `priority` integer column would let you reorder rules
  without delete-and-recreate. Add when conflicts come up.
- **Reassign rules on project merge** — `/projects/manage` →
  Delete with reassignment doesn't currently rewrite
  `cc_auto_rules.project_id` to the merge target. The CASCADE on
  the FK drops them instead. Probably fine for v1 since the
  reassignment is rare; revisit if it bites.
- **Bulk "mark all auto-matched as confirmed"** — if there are
  many orange rows you trust, a one-click "confirm all"
  (flip every `auto_assigned=true` in this batch to false) would
  beat per-row Save clicks. Hold off until the workflow actually
  bottlenecks here.

## Changelog
- **2026-05-25** — Phase 1 shipped. Tools nav dropdown,
  `/tools` landing, CC expenses batches CRUD, paste-in parser,
  per-row manual assignment with project combobox + category
  datalist, paste block out for the PlusROI sheet.
- **2026-05-25** — **Phase 2 shipped: highlight keywords +
  auto-assign rules.** New tables `cc_highlight_keywords` +
  `cc_auto_rules`. New page `/tools/cc-expenses/rules` for both
  lists (highlight = single keyword, auto-rules = keyword +
  project + description + category). `appendCcLines` now matches
  each parsed line against rules and pre-fills the assignment;
  the post-paste info banner reports how many got auto-assigned.
  The detail-page lines table paints highlight-matched rows
  yellow. New `findHighlightMatch` / `findAutoRuleMatch` pure
  helpers in `lib/cc-expenses.ts` (10 new tests).
- **2026-05-25** — **Re-apply rules button + 3-color row scheme.**
  Added `cc_expense_lines.auto_assigned` boolean (default false)
  to distinguish auto-rule-stamped rows from user-confirmed rows.
  New `reapplyRules` server action scans unassigned lines in a
  batch against the current rule set. `updateCcLine` now sets
  `auto_assigned=false` on every save so confirmed rows flip
  orange → green. Small color legend in the lines-section header.
- **2026-05-25** — Color priority flipped from
  `yellow > orange > green` to `green > orange > yellow`.
  Reasoning: the previous priority painted every assigned row
  yellow whenever its description happened to match any highlight
  keyword (which is common — keywords like "GOOGLE" overlap with
  many descriptions), making the assignment state invisible. The
  new priority surfaces the assignment color first; yellow paints
  only on still-unassigned rows.