gckit 0.1.0

Consolidate outstanding Sage 50 invoices into one GoCardless direct debit payment per customer per collection date, net off credit notes automatically, and notify customers via Microsoft Graph.

gckit

Consolidate outstanding Sage 50 (UK/Ireland) invoices into one GoCardless direct debit payment per customer per collection date, netting off credit notes automatically, instead of GoCardless's default of one payment per invoice. Built on top of two other Duckboard toolkits: sagekit for the Sage connection and graphkit for customer notification emails.

This is a real, widely-felt gap in GoCardless's own integrations -- see this Sage Community Hub thread asking exactly this question. If a customer has three unpaid invoices due the same week, GoCardless's standard integration creates three separate direct debit collections; a lot of customers phone up confused about "three payments" when they expected one. gckit consolidates them into a single payment with a clear description, before anything gets sent.

If this saves you time, consider buying me a coffee.

What it does

1. Reads outstanding invoices and credit notes straight out of Sage 50 via ODBC.
2. Groups them by customer and working-day collection date, netting off any credit notes against that customer's invoice total.
3. Works out a valid charge date (skipping weekends and English/Welsh bank holidays, with a configurable lead time), and defers customers on monthly payment terms to their proper monthly date instead of collecting weekly.
4. Checks GoCardless itself for anything already submitted or already paid, on top of its own run log, so a re-run (scheduled weekly, or re-run after a crash) never double-charges anyone.
5. Submits one consolidated payment per customer, with an idempotency key GoCardless honours even if the same request is sent twice.
6. Emails each customer what's being collected and when, via Microsoft Graph.

Everything is driven by two CSVs (or Sage/GoCardless data directly) and one optional settings file -- there's no database to maintain.

Features

• One payment per customer per collection date, not per invoice
• Credit notes netted off automatically
• Bank-holiday-aware charge dates (fetched from gov.uk), with a configurable minimum lead time and a choice of England & Wales, Scotland, or Northern Ireland's calendar
• Customers on monthly payment terms collected on their monthly date, not swept into the weekly run
• Small remaining balances merged into a customer's next scheduled payment instead of triggering a tiny separate collection
• Double-charge protection: checks both a local run log and GoCardless's own payment list (submitted, pending, and failed) before sending anything
• A manual retry list, for pushing a specific invoice through again after fixing a one-off issue (bad mandate, etc.) without it being silently excluded as "already tried"
• --customer flag to test against a single account before trusting it with everyone
• A live-environment confirmation you have to type out, separate from the normal "submit?" prompt, so a fat-fingered sandbox vs live mistake doesn't move real money
• Column-name auto-detection for the tricky bits (Sage's DELETED_FLAG varies between numeric and string across installs) via sagekit
• No Outlook dependency for customer notifications -- sends via the Graph API directly, so it works unattended/headless (e.g. on Windows Task Scheduler)

Installation

pip install -r requirements.txt

or, as an editable package:

pip install -e .

You'll also need:

Requirement	Notes
Windows	sagekit's ODBC driver is Windows-only
Sage 50 (UK/Ireland)	Running on this PC or reachable on the network, with its ODBC driver installed (comes with Sage)
A GoCardless account	Sandbox is fine for testing; you'll need a live account with active customer mandates for real collections
An Azure app registration	With the Mail.Send Application permission (admin consent required) -- see graphkit's README for the registration walkthrough. Skip this if you don't want customer notification emails; leave email.sender_mailbox blank in settings.yaml

Setup

1. GoCardless API token

Generate an access token from your GoCardless dashboard (Developers -> Create access token). Start with a sandbox token and test a full run before switching to live.

GC_ACCESS_TOKEN=sandbox_your-token-here
GC_ENVIRONMENT=sandbox

2. Sage ODBC connection

gckit uses sagekit for the Sage connection, so set the same three environment variables sagekit reads (see its README for how to find your DSN):

SAGE_DSN=SageLine50v33
SAGE_UID=MANAGER
SAGE_PWD=

Leaving SAGE_PWD blank is fine for anything you run by hand -- you'll be prompted with hidden input instead. Only set it for unattended/scheduled runs.

3. Graph API app registration (optional, for customer emails)

gckit uses graphkit to send mail, so set the same three environment variables graphkit reads:

GRAPH_TENANT_ID=...
GRAPH_CLIENT_ID=...
GRAPH_CLIENT_SECRET=...

Copy .env.example to .env and fill in your values if you want to pre-fill all of the above (never commit the real .env -- it's already gitignored).

4. mandate_map.csv

gckit needs to know which GoCardless mandate belongs to which Sage customer. Copy examples/sample_mandate_map.csv and edit it by hand, five columns:

Column	What to enter	Example
Account Ref	The account reference from Sage -- must match exactly (case-sensitive)	ABC001
Mandate ID	From GoCardless's dashboard: Customers -> mandate -> Mandate ID	MD0001AAAA1AAA
Customer Name	For your reference / notification emails	ABC Distributors Ltd
Email	Where to send the collection notice	[e-mail]
CC Email	Optional second recipient	(blank)

If your Sage "Direct Debit Mandate Ref" field already matches the mandate reference you gave GoCardless when each mandate was set up, examples/build_mandate_map.py can build this file automatically from a Sage export -- see the comments in that script for the export format and extra dependencies (pandas, openpyxl).

5. settings.yaml (optional)

Copy examples/settings.yaml if you want to change where files are written, the charge-date lead time, the small-payment merge threshold, or the notification email addresses. Every key has a default -- the file itself is optional.

Usage

python -m gckit                        # preview + interactive confirm
python -m gckit --unattended           # no prompts (scheduled runs only)
python -m gckit --customer ABC001      # restrict to one customer (first live test)
python -m gckit --config examples/settings.yaml

Run against the sandbox GoCardless environment first, check the preview output and the log file in the logs folder, then switch GC_ENVIRONMENT=live once you're confident.

As a library, everything is importable directly -- see examples/run_collector.py for calling the CLI entry point from your own script, and examples/build_mandate_map.py for building mandate_map.csv automatically from a Sage export.

from gckit import consolidate_payments, merge_small_payments, calculate_charge_date

The consolidation logic itself (gckit.consolidate, gckit.models, gckit.workdays) has no dependency on Sage, GoCardless, or Graph, so it's usable on its own if your invoice data comes from somewhere else.

Double-charge protection

Before submitting anything, gckit excludes invoices/credits that are already accounted for, from two sources:

1. Its own collection_log.csv -- every payment it has successfully sent in a previous run.
2. GoCardless itself -- any payment currently submitted, pending, paid_out, or failed for these mandates, read directly from the GoCardless API. This catches cases the local log wouldn't, e.g. a payment sent from a different machine, or a crash after sending but before the log was written.

A failed GoCardless payment is excluded by default (something needs fixing first) -- add the invoice number to retry_invoices.txt (one per line, # for comments) to force it back into the next run once you've resolved the issue.

Scheduling

Windows Task Scheduler:

1. Win + R -> taskschd.msc
2. Create Basic Task -> Trigger: Weekly (or whatever cadence suits your terms), on the day you want the collection run to happen
3. Action: Start a program -> python with arguments -m gckit --unattended, "Start in" set to the folder containing your mandate_map.csv / settings.yaml
4. Tick "Run with highest privileges" and set SAGE_UID/SAGE_PWD as environment variables (System Properties -> Environment Variables) since there's no one to answer the Sage login prompt unattended

Test thoroughly with a normal (non---unattended) run against the sandbox environment before scheduling anything against live.

Log files

Every run creates a dated log file in the logs folder recording every step: invoices/credits loaded, payments consolidated, the full preview, and the result of each submission. collection_log.csv separately keeps a permanent record of every payment ever sent, which is what double-charge protection reads from.

Troubleshooting

Symptom	Likely cause	Fix
Missing required environment variable: GC_ACCESS_TOKEN	Token not set	Set GC_ACCESS_TOKEN (see Setup step 1)
Cannot connect to Sage via DSN ...	DSN wrong or Sage not running	See sagekit's README -- check "ODBC Data Sources (64-bit)" and that Sage is open
No mandate mappings loaded -- cannot continue	mandate_map.csv missing or empty	Check the path in settings.yaml and that the file has data rows
A customer's invoices aren't being collected	No mandate mapped for their Account Ref, or all their invoices already appear as sent in collection_log.csv	Check mandate_map.csv has a row for them; check collection_log.csv
Payment rejected with a mandate/validation error	Usually a lapsed or cancelled mandate in GoCardless	Check the mandate's status in the GoCardless dashboard; add the invoice number to retry_invoices.txt once fixed
PDFs -- wait, wrong toolkit	You want statements, not collections	See stmtkit

Testing

pip install pytest
pytest

Tests mock the Sage cursor, the GoCardless client, and the Graph client, so they run offline with zero real credentials, no real Sage install, and no real GoCardless account.

Notes

• Amounts assume GBP; GoCardless amounts are pence internally (gckit.gc_client handles the conversion).
• The SQL in gckit/sage_source.py assumes Sage 50's standard AUDIT_HEADER / SALES_LEDGER schema (UK/Ireland). If your install uses different column names, use sagekit's get_columns() / find_column() to check what's actually there.
• Bank holiday dates are fetched from the UK government's public bank-holidays.json feed. Defaults to england-and-wales; set collection.bank_holiday_region in settings.yaml to scotland or northern-ireland instead -- see examples/settings.yaml.

License

MIT -- see LICENSE. Do whatever you like with this; a credit or a Ko-fi tip is appreciated but never required.