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.

Looking for something else? If you want to send customers their statements rather than collect payments from them, see stmtkit instead.

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. Each request is tagged so that if it's accidentally sent to GoCardless twice (a network hiccup, a re-run), GoCardless recognises it and won't create a second payment.
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 gckit

This installs gckit itself and everything it depends on (the GoCardless library, sagekit, msgraphkit) in one go.

If you've downloaded this GitHub repository's source folder instead of installing from PyPI, run this from inside it:

pip install -e .

That also installs gckit properly (so import gckit works from anywhere on your machine, not just from inside this folder). Running pip install -r requirements.txt on its own only installs gckit's dependencies, not gckit itself -- it happens to still work if you run commands from inside this folder, but pip install -e . is the one to use if you want it to behave like a normal installed package.

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	[email protected]
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.

The rest of this section is only relevant if you (or someone helping you) wants to customise how gckit runs, rather than using the three commands above as-is.

Everything is also importable directly from your own Python script -- see examples/run_collector.py and examples/build_mandate_map.py for working examples to copy.

from gckit import consolidate_payments, merge_small_payments, calculate_charge_date

The consolidation logic itself (gckit.consolidate, gckit.models, gckit.workdays) doesn't need Sage, GoCardless, or Graph at all, so it can be reused 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

Testing

This step is optional, but it's a good confidence check before you trust gckit with a real GoCardless run: it proves the code works correctly without touching your real Sage data, your real GoCardless account, or sending a single real email.

pip install pytest
pytest

The first line installs a testing tool (a one-off, the same way you installed gckit itself). The second line runs a batch of automated checks against sample data and tells you whether they all passed.

You should see a line of dots followed by a message like 61 passed in 0.23s. If anything says "failed" instead, something's wrong with the install -- stop and get in touch rather than running it against your real accounts.

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.