Use this skill whenever a user wants to review overdue invoices, collect on outstanding AR, follow up with clients on unpaid balances, generate collection emails, or understand aging receivables from TPS Cloud Axis. Trigger this skill for any request involving: overdue invoices, aging buckets, AR collection, payment follow-up emails, past-due clients, outstanding balances, or collection letters. Also trigger when the user says things like "who owes us money", "send collection emails", "follow up on unpaid invoices", or "show me aging AR".
Reads overdue invoices from TPS Cloud Axis, classifies them into aging tiers, and drafts a professional collection email for each tier. Optionally sends or saves drafts via Gmail.
Call TPS Cloud Axis: me to get the firm name and current user. Use this in
email signatures and subject lines.
firm_name = data.firm.name
user_name = data.name
user_email = data.email
Run the following SQL against TPS Cloud Axis: query. This joins
fact_invoice with dim_client to get client contact info alongside the
aging data. Adjust column names if the schema differs — see the column
reference in references/schema-notes.md.
SELECT
c.client_id,
c.client_name,
c.contact_name,
c.contact_email,
c.assigned_staff,
i.invoice_id,
i.invoice_number,
i.invoice_date,
i.due_date,
i.total_amount,
i.outstanding_balance,
i.payment_status,
CURRENT_DATE - i.due_date AS days_overdue,
CASE
WHEN CURRENT_DATE - i.due_date BETWEEN 1 AND 30 THEN '1-30'
WHEN CURRENT_DATE - i.due_date BETWEEN 31 AND 60 THEN '31-60'
WHEN CURRENT_DATE - i.due_date BETWEEN 61 AND 90 THEN '61-90'
WHEN CURRENT_DATE - i.due_date > 90 THEN '90+'
ELSE 'current'
END AS aging_bucket
FROM fact_invoice i
JOIN dim_client c ON i.client_id = c.client_id
WHERE i.outstanding_balance > 0
AND i.due_date < CURRENT_DATE
ORDER BY days_overdue DESC, i.outstanding_balance DESC;
If the query fails due to permissions, tell the user clearly:
"The TPS Cloud Axis connector needs query permissions. Ask your firm administrator to grant access, then disconnect and reconnect the connector."
Group results by client_id so a client with multiple overdue invoices gets
one email (not one per invoice). For each client, compute:
total_outstanding = sum of outstanding_balanceoldest_invoice_days = max of days_overdueinvoice_count = count of invoicesinvoice_list = formatted list: "#INV-XXX — $X,XXX (due DATE)"aging_bucket = use the worst (highest) bucket across all their invoicesBucket hierarchy (worst first): 90+ > 61-90 > 31-60 > 1-30
Use the templates in references/email-templates.md. Substitute all
{{placeholders}} with real data before presenting to the user.
Select the template based on aging_bucket:
| Bucket | Template | Tone |
|---|---|---|
| 1–30 | gentle-nudge | Friendly reminder |
| 31–60 | firm-followup | Professional, direct |
| 61–90 | escalation | Firm, sense of urgency |
| 90+ | final-notice | Formal, consequences |
Show the user a summary table first:
CLIENT | BUCKET | INVOICES | TOTAL OUTSTANDING
-------------------|--------|----------|------------------
Acme Corp | 31-60 | 2 | $4,250.00
Smith & Assoc. | 90+ | 1 | $12,000.00
...
Then show each drafted email clearly separated, labeled with the client name and tier. Ask the user:
"Would you like me to save these as Gmail drafts, or make any changes before sending?"
If the user wants to save drafts or send, use the Gmail MCP connector:
Gmail: gmail_create_draftto = contact_email from dim_clientsubject and body from the drafted emailIf Gmail is not connected, tell the user to enable it in their connectors menu and provide the email text to copy manually.
| Situation | Response |
|---|---|
| No overdue invoices found | "Great news — no overdue invoices found in TPS." |
| Client has no contact email | Draft the email, note the missing email, ask user to supply it |
| Query permission denied | Explain the permissions issue (see Step 2) |
| Gmail not connected | Provide email text for manual use |
$X,XXX.XX (two decimal places, comma separators)Month DD, YYYY (e.g., "March 15, 2025")references/email-templates.md before drafting any emailreferences/schema-notes.md if a query fails or columns are missing