AR Collections

AR Collections & Aging Analysis pipeline for QBO clients. Produces a 7-tab Excel workbook with AR aging buckets (Current/1-30/31-60/61-90/90+), DSO, collecti...

Security Scan

The instructions describe a reasonable AR/aging pipeline for QBO clients, but the skill omits critical operational requirements (QBO auth/credentials and the referenced scripts) and assumes read/write access to local repo paths, so its requirements and runtime assumptions are incoherent with the declared metadata.

Purpose & Capability

The name, description, and runtime instructions consistently describe an AR Collections & Aging pipeline that pulls QBO data, computes metrics, and writes an Excel workbook and a local CDC cache. That purpose aligns with the commands shown (python script, node QBO client). However, the skill metadata declares no required credentials, binaries, or config paths even though the instructions explicitly require a Node.js QBO client to be authenticated and a local repository layout (scripts/pipelines/ar-collections.py, clients/{slug}/sop.md). This mismatch (expected QBO credentials and local scripts vs. declared 'none') is an incoherence.

Instruction Scope

SKILL.md instructs the agent/operator to run a local Python script (scripts/pipelines/ar-collections.py), call a Node.js QBO client (node bin/qbo info {slug}), read clients/{slug}/sop.md and write outputs to reports/... and .cache/ar-collections/{slug}.json. Those actions are within the stated purpose, but the skill gives the agent broad discretion to read and write repository files and caches and to contact QBO via the node client — none of which are declared in the skill metadata. Also, the actual script files are not included in the skill bundle; the instructions assume they exist in the runtime environment. This could cause failures or unexpected behavior if the runtime environment differs, and it hides required credentials and file access from the metadata.

Install Mechanism

Instruction-only skill (no install spec, no code files) — lower install risk because nothing is written by the skill bundle itself. The README suggests pip installing openpyxl and requires an authenticated Node.js QBO client; those are normal for this pipeline but are not enforced or declared in metadata. Because the skill will rely on external, out-of-band tooling and repo files, the installation/operational burden is on the host environment rather than the skill — this is expected but should be explicit.

Credentials

The pipeline needs authenticated access to QBO via a Node.js client, which implies API keys/tokens or OAuth credentials, but the skill declares no required environment variables or primary credential. That omission is disproportionate: a QBO-integrating pipeline should declare the type of credential it uses (and any config paths) so operators can review before enabling. The instructions also allow writing to .cache/ and reports/ without declaring or gating file paths.

Persistence & Privilege

always is false and there is no install-time modification of other skills or global agent settings. The pipeline writes to its own .cache and reports directories and suggests updating CLIENT_AR_CONFIG or clients/{slug}/sop.md, which is normal for a repo pipeline but does mean it will modify repository files if run with write permissions. That behavior is scoped to the project; it does not request elevated platform privileges in the metadata.

What to consider before installing

Before installing or invoking this skill, verify these points: (1) The skill assumes you have an existing repository with scripts/pipelines/ar-collections.py and a Node.js QBO client — the skill bundle does not include these files. Do not run it unless those scripts and the QBO client exist and are reviewed. (2) QBO access requires credentials (API keys or OAuth tokens); the skill does not declare or explain required env vars. Ensure you only provide least-privilege credentials scoped to read AR data and audit/rotate them. (3) The pipeline will read clients/{slug}/sop.md and may update CLIENT_AR_CONFIG and write .cache/ar-collections/{slug}.json and reports/ files — confirm you’re comfortable with those repository writes and with any PII in the outputs. (4) Run first in a sandboxed environment or QBO sandbox to confirm behavior and outputs. (5) If you intend to let an autonomous agent invoke this skill, restrict its permissions and network access and require an explicit review step before it runs against production credentials. If the author can provide the missing script files or document exactly what credentials/config are needed, re-evaluate; as-is the omission of declared credentials and the assumption of local scripts makes the skill's runtime requirements unclear.

AR Collections & Aging Analysis Skill

What This Does

Runs the AR Collections & Aging Analysis pipeline (scripts/pipelines/ar-collections.py) to produce a controller-level accounts receivable workbook from QBO data.

Produces:

1. AR aging bucketed into Current | 1-30 | 31-60 | 61-90 | 90+ days
2. Aging metrics: total AR, weighted average days outstanding, concentration risk
3. Collection priority scoring per customer (HIGH / MEDIUM / LOW / MONITOR)
4. Recommended collection actions per customer
5. Bad debt reserve (percentage-of-aging method)
6. Payment pattern analysis from GL history
7. DSO: current period and rolling 3-month
8. CDC: which customers improved or deteriorated since last run
9. Excel workbook (7 tabs)

When to Use

Use when:

• Client asks for AR aging report, collections status, or DSO
• Monthly close includes AR review
• Need to know who owes money and what to do about it
• Bad debt reserve needs to be calculated for month-end
• Auditors or investors request AR aging schedule

Do NOT use when:

• Client SOP says AR is not applicable (pipeline exits gracefully — no report needed)
• Client collects at point of sale (e.g., SB Paulson / Willo Salons)
• Running for a non-QBO client (no data source)
• User wants a P&L or balance sheet (use pl-deep-analysis or client-dashboard)

SOP Gate

The pipeline automatically checks the client SOP before pulling any data:

• sb-paulson → exits gracefully with explanation (POS collection, no AR)
• Unknown slugs → checks SOP markdown for AR-disabled signals, defaults to AR-applicable

To add a new client's AR status, update CLIENT_AR_CONFIG in the pipeline, OR add these markers to their clients/{slug}/sop.md:

**AR Aging:** ❌ Not applicable (POS collection)

Usage

# Standard run — as of end of month
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31

# With custom output directory
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --out ~/Desktop/reports

# Skip GL pull (faster, no payment pattern analysis)
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --skip-gl

# QBO sandbox
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --sandbox

# Client with no AR — exits gracefully
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31

Output

Default location: reports/ar-collections/ar-collections_{slug}_{as-of}.xlsx

Tabs:

Tab	Contents
AR Summary	Aging snapshot by bucket, key metrics, concentration risk
Aging Detail	Invoice-level list: customer, date, due date, balance, bucket
Collection Priority	Sorted action list: HIGH/MEDIUM/LOW/MONITOR with recommended actions
Payment Patterns	Avg days to pay per customer, vs. terms, reliability rating
Bad Debt Reserve	Percentage-of-aging reserve calc + suggested journal entry
DSO Analysis	Current and rolling 3-month DSO, monthly revenue detail
CDC Log	Changes since last run: improved / deteriorated / new / cleared

Collection Priority Logic

Priority	Criteria	Recommended Action
HIGH	90+ days past due OR balance > $5K	Escalate / demand letter / write-off review
MEDIUM	61-90 days OR balance > $2.5K	Follow-up call
LOW	31-60 days	Send email reminder
MONITOR	Current or 1-30 days	Standard review next cycle

Bad Debt Reserve Rates (Percentage-of-Aging)

Bucket	Rate
Current	1%
1-30	3%
31-60	10%
61-90	25%
90+	50%

DSO Formula

• Current DSO = (AR Balance ÷ Current Period Revenue) × Days in Period
• Rolling DSO = AR Balance ÷ (3-Month Revenue ÷ 90 days)

CDC Cache

Cached at: .cache/ar-collections/{slug}.json

Each run saves customer balances and worst buckets. Next run computes:

• Improved — balance decreased or bucket moved earlier
• Deteriorated — balance increased or bucket moved later
• New — first appearance in AR
• Cleared — balance went to zero (collected)

Requirements

pip install openpyxl
# Node.js QBO client must be auth'd
node bin/qbo info {slug}  # from your QBO integration directory

Related Pipelines

• pl-deep-analysis.py — GL drill-down, P&L variance, accrual proposals
• client-dashboard.py — KPI dashboard (includes DSO as a KPI)
• bank-reconciliation.py — Bank rec (not AR-specific)
• budget-vs-actual.py — BvA (revenue-side context for AR)

Clients

Configure AR applicability per client in CLIENT_AR_CONFIG or via clients/{slug}/sop.md.