Track 1099 contractor payments against the $600 threshold

On the 2nd of every month at 8am, build a running 1099 readiness worksheet for our contractors from our Puzzle books, write it to Google Sheets, and post one summary to Slack. Every step is a fixed lookup, a sum, or a threshold comparison, so build this as a deterministic code workflow rather than an agent. Use a cron trigger.

Expose these as editable inputs: the 1099 reporting threshold as a number defaulting to 600, the Google Sheet id and tab name for the worksheet, and the Slack channel to post to. The threshold has to be editable so we can set it lower, for example 500, to get an early warning before a contractor actually crosses.

Start with Puzzle Get Current User to resolve the company, taking the company id from companies[].id (ids are prefixed co_). If the account has access to more than one company, use the one matching an optional company name input, otherwise the first one. If any data read comes back 409 RESOURCE_PROCESSING, the company is still in Syncing status, so wait and retry with backoff a few times before failing. Never report a partial picture as if it were complete.

Call Puzzle Get Vendors with the 1099 filter to list every 1099 eligible vendor, keeping each vendor's id, name, type and status. This list is cursor paginated, so page through it fully using pageInfo.endCursor with the after parameter before moving on.

For each vendor, call Puzzle Get Transactions filtered by that vendor id and by a date range covering the current calendar year, from January 1 of the current year through the run date. Page through every result with pageInfo.endCursor and after before totalling anything. Stopping at the first page understates a total and silently misses a threshold crossing, which is the one thing this workflow exists to catch.

Puzzle monetary amounts are objects shaped { amount, currency }, where amount is an arbitrary precision decimal serialized as a string. Sum them as decimals, not as floating point numbers, so cents do not drift across hundreds of transactions. Net refunds and credits against payments so each total reflects what we actually paid that vendor. If a vendor has transactions in more than one currency, total each currency separately rather than adding them together, and compare the USD total to the threshold. A 1099 eligible vendor with no transactions this year still belongs in the table with a zero total.

Before writing anything, read the previous run's block back from the same sheet with Google Sheets Get Values so we can work out who is newly over the line. A vendor counts as crossed since last month if it is at or above the threshold now and was below it, or absent entirely, in the most recent previous block. On the very first run there is no prior block to compare against, so treat that run as the baseline and say so in the Slack message rather than reporting every vendor as newly crossed.

Now write the full table to the Google Sheet. If the tab is empty, first write a header row with Update Values: run date, vendor, vendor type, status, year to date paid, currency, and over threshold. Then append one row per vendor with Append Values, each stamped with the run date, so every month adds a dated block and the sheet becomes a running worksheet instead of a snapshot that overwrites itself. Sort the rows by year to date paid, highest first.

Finish with a single Slack Send a Message to the configured channel. Include the company name and the year to date period covered, the count of vendors at or over the threshold, the count still under it, and then name the vendors that crossed since last month along with their year to date totals. If nobody crossed this month, say that in one line rather than posting an empty section. Keep it to one message and format all money to two decimal places.

This is a read and report workflow. Do not create, update or file anything in Puzzle.