Issue Invoice

issue-invoice

Monthly hourly invoicing for contract clients. One Google Sheet per client, one tab per month (202607, 202608, ...). Rows are billable work reconciled from GitHub PRs, linked to their tracker tickets, plus meeting rows.

Every client-specific value - spreadsheet id, org, repos, Jira host, rate - lives in a private rules file outside this repo. This skill ships no client data.

Client rules

~/.config/vd/invoice-rules/<client>.invoice-rules.md

YAML frontmatter is machine-read by the harvest script; the prose below it is for you. See references/client.invoice-rules.example.md for the schema and copy it to onboard a new client.

scripts/harvest-prs.py --list          # configured clients

Read the whole rules file before drafting rows. It defines the tab layout, column formats, hour calibration, meeting cadence, and client quirks - all of which vary.

Never write a client value into this skill. If something is true for one client only, it belongs in that client's rules file.

Workflow

1. Read current state first

export GOG_HOME="$HOME/.config/vd/gog"
ACCT=<gog_account from rules, --account org with --user person>
SID=<spreadsheet from rules frontmatter>

gog --account "$ACCT" sheets metadata "$SID" --json

# FORMULA or the next write flattens HYPERLINK/SUM
gog --account "$ACCT" sheets get "$SID" '<tab>!A1:F60' --render FORMULA --json

Locate the Total row and its exact SUM ranges - they drift as rows are inserted.

2. Harvest PRs

scripts/harvest-prs.py --client <alias> 2026-08

Groups PRs by the client's local working day across all configured repos, citing each with its repo label, and flags dependency-bump PRs.

All PR states are harvested, not just merged - a superseded or closed PR still represents work done. Unmerged ones are marked [not merged] so you can judge whether they are billable or were abandoned.

3. Map to tickets and hours

Pull assigned tickets for context (env var names come from the rules frontmatter):

source ~/.envrc
curl -sS -u "$JIRA_X_USER_EMAIL:$JIRA_X_API_TOKEN" \
  -G "<base_url>/rest/api/3/search/jql" \
  --data-urlencode 'jql=project = <KEY> AND assignee = currentUser() ORDER BY updated DESC' \
  --data-urlencode 'fields=summary,status,resolutiondate' \
  | jq -r '.issues[] | [.key,.fields.status.name,.fields.summary] | @tsv'

Most PR titles carry the ticket id; otherwise check the body (gh pr view N --repo <slug> --json body) for a Jira: line. When neither exists, infer from domain and tell the user which rows were inferred.

Size hours against the client's calibration table. Always present the proposed rows and the new invoice total for approval before writing - this is money.

4. Write

Rows must fit between the header and the Total row; insert first if not.

export GOG_HOME="$HOME/.config/vd/gog"
# insert N rows before the Total row (start is 0-based, same as startIndex)
gog --account "$ACCT" sheets insert "$SID" <tab> ROWS <start> --count N --inherit-from-before

gog --account "$ACCT" sheets copy-paste "$SID" '<tab>!A11:F11' '<tab>!A12:F49' --type FORMAT

gog --account "$ACCT" sheets update "$SID" '<tab>!A11:F49' --input USER_ENTERED --values-json @/tmp/values.json

gog --account "$ACCT" sheets update "$SID" '<tab>!E50:F50' --input USER_ENTERED --values-json '[["=SUM(E11:E49)","=SUM(F11:F49)"]]'

5. Verify

Re-read the block and assert: row count, hours sum equals the Total cell, dates ascending, no dates outside the month, no blank ticket/note cells. Then screenshot with ego-browser for a visual pass.

Monthly rollover

export GOG_HOME="$HOME/.config/vd/gog"
gog --account "$ACCT" api call sheets v4 spreadsheets.batchUpdate --allow-write \
  --params "{\"spreadsheetId\":\"$SID\"}" \
  --body '{"requests":[{"duplicateSheet":{"sourceSheetId":OLD_SHEET_ID,"insertSheetIndex":0,"newSheetName":"202609"}}]}'

Then: update the invoice number and date cells, values clear the old data block (formatting survives), write the new month from the first data row, leave the remaining rows blank and inside the SUM range so later additions total automatically, and carry over any meeting belonging to the new month.

Gotchas

Each of these cost real time. Do not rediscover them.

• Always export GOG_HOME=$HOME/.config/vd/gog. A bare gog stores tokens under ~/.config/gogcli and this skill will not see them.
• Person-user alias only (refresh token). Do not use *-sa or --access-token for invoice writes. See vd:gog person-user auth.
• A failing jq pipe does not mean the API call failed. Re-read state before retrying - a blind row insert doubles rows.
• gh pr list defaults to 30 and truncates silently. The harvest script guards this; if you query by hand, pass --limit 400.
• PR timestamps are UTC; the working day is the client's timezone. Off-by-one here misfiles work across day and month edges.
• Token death (invalid_grant / invalid_rapt) needs gog auth add --force-consent for that person alias. Weekly death means the OAuth app is still in Testing - publish it; do not keep re-authing.
• Calendar is a separate scope. If gog calendar 403s, ask for meeting dates or re-add with calendar in --services.
• The jira CLI may point at a different instance. Use the REST call above with the client's env vars; jira me can report the wrong user.
• Total SUM ranges go stale. One client's read =SUM(E11:E18) while data ran to row 25. Verify hours x rate equals the amount.
• PR numbers collide across repos. Cite with the repo label from the rules file.

Convention: private config for skills

This skill follows a pattern worth reusing whenever a skill needs real credentials, hosts, org names, or customer identifiers:

~/skills/skills/<skill>/                        tracked, public, zero private data
  references/<thing>.<skill>-rules.example.md   placeholder schema
~/.config/vd/<skill>-rules/<alias>.<skill>-rules.md   private, per-instance

The private half lives outside the repo, so it is excluded by construction - no .gitignore entry to forget, nothing to leak in a diff, and adding a client never touches version control. The skill resolves an alias at runtime and fails with the list of configured aliases when one is missing. vd:jira uses the same layout with ~/.config/vd/jira-rules/.