dolibarr_shine_reconciliation/README.md

Dolibarr / GoCardless / Shine Reconciliation

A standalone Ruby script that cross-checks three financial systems and flags discrepancies.

Manual tool — Export CSVs from GoCardless and Shine, run the script locally, review the report. No API integrations with payment providers, no background processes, no coupling to other projects.

The problem it solves

Payments flow through three separate systems that are not automatically linked:

Shine bank account  ←  GoCardless payouts  ←  GoCardless payments  ←  Dolibarr invoices

Typical workflow:

1. GoCardless initiates a direct debit for a customer
2. If the debit succeeds, you create the corresponding invoice in Dolibarr and mark it paid
3. If the debit fails, you cancel or delete the draft invoice
4. GoCardless batches collected payments into payouts and transfers them to your Shine account

Discrepancies arise when any of these steps is missed:

• A GoCardless payment succeeded but the Dolibarr invoice was never created
• A Dolibarr invoice is marked paid but no GoCardless payment can be found for it
• A GoCardless payout never appeared in the Shine bank account

---

Requirements

• Ruby 3.x
• Bundler (gem install bundler)
• Network access to your Dolibarr instance

---

Installation

cd dolibarr_shine_reconciliation
bundle install
cp .env.example .env

Edit .env and fill in your Dolibarr credentials:

DOLIBARR_URL=https://your-dolibarr.example.com/api/index.php
DOLIBARR_API_KEY=your_api_key

# GoCardless payment method ID in Dolibarr (used by --fix mode)
# Find it: GET /setup/dictionary/payment_types
# Look for the "Prélèvement GoCardless" entry
DOLIBARR_GC_PAYMENT_ID=6

# Bank account ID in Dolibarr (used by --fix mode)
# Find it: GET /bankaccounts
DOLIBARR_BANK_ACCOUNT_ID=1

---

Exporting the data

GoCardless — payments CSV

Dashboard → Payments → filter by date range → Export CSV

Place the file in gocardless/. The expected columns are:

Column	Description
id	Payment ID, e.g. PM014J7X4PY98T
charge_date	Date the customer was debited (YYYY-MM-DD)
amount	Amount in euros (30.52)
description	Free text — used as the primary match key against the Dolibarr invoice ref
status	paid_out, confirmed, failed, cancelled
links.payout	Payout ID this payment belongs to
payout_date	Date the payout was sent to your bank
customers.given_name	Customer first name
customers.family_name	Customer last name

Shine — bank statement CSV

App → Comptes → Exporter le relevé → select year → download

Place the annual CSV in shine/. The expected columns are:

Column	Description
Date de la valeur	Value date (DD/MM/YYYY)
Crédit	Credit amount in French format (51,10)
Débit	Debit amount
Libellé	Transaction description
Nom de la contrepartie	Counterparty name — GoCardless payouts show GOCARDLESS SAS here

The Shine CSV uses semicolons as separator (;), UTF-8 encoding, and Windows CRLF line endings. The script handles all of this automatically.

---

Usage

Dry run — report only, no changes to Dolibarr

ruby bin/reconcile \
  --from 2026-01-01 \
  --to   2026-01-31 \
  --gc   gocardless/payments_export.csv \
  --gc-payouts gocardless/payouts_export.csv \
  --shine shine/Cyanet_2026-01-01_2026-12-31_EXPORT/BANQUE_2026-01-01_2026-12-31/BQ_2026-01-01_2026-12-31.csv

Recommended: Always provide the GoCardless payouts CSV (--gc-payouts) for exact fee tracking. Without it, the script can only estimate fees by comparing amounts.

The Shine file is optional. Without it, payout verification (Pass 3) is skipped:

ruby bin/reconcile \
  --from 2026-01-01 \
  --to   2026-01-31 \
  --gc   gocardless/payments_export.csv

Fix mode — auto-mark Dolibarr invoices as paid

When the script detects a GoCardless payment that was collected but the matching Dolibarr invoice is still open, --fix records the payment in Dolibarr via the API:

ruby bin/reconcile \
  --from 2026-01-01 \
  --to   2026-01-31 \
  --gc   gocardless/payments_export.csv \
  --gc-payouts gocardless/payouts_export.csv \
  --shine shine/.../BQ_2026-01-01_2026-12-31.csv \
  --fix

--fix only affects invoices flagged GC_PAID_DOLIBARR_OPEN. All other entries are reported only.

Environment variables (optional)

# Tolerance for soft date matching (default: 7 days)
RECONCILIATION_DATE_TOLERANCE=7

# Tolerance for payout date matching (default: 2 days)
RECONCILIATION_PAYOUT_TOLERANCE=2

---

How matching works

The script runs three passes over the data.

Pass 1 — GoCardless ↔ Dolibarr

For each GoCardless payment, an attempt is made to find a matching Dolibarr invoice in two steps:

Strong match — the GoCardless description field equals the Dolibarr invoice ref exactly (case-insensitive). This fires when you put the invoice reference in the GoCardless payment description at creation time.

Soft match — if no strong match is found, the script looks for a Dolibarr invoice where:

• The amount is identical (compared in cents to avoid floating-point errors)
• The invoice date is within 7 days of the GoCardless charge_date
• The customer name on the Dolibarr invoice matches the GoCardless customer name (accent-insensitive, word-order-insensitive)

Once matched (or not), each payment is assigned one of these flags:

Flag	Meaning	Action
MATCHED	GC payment collected, Dolibarr invoice paid	None
GC_PAID_DOLIBARR_OPEN	GC collected but Dolibarr invoice is still open	Create the invoice / use --fix
GC_NO_INVOICE	GC payment collected, no Dolibarr invoice found at all	Create the invoice in Dolibarr
GC_FAILED	GC payment failed	Check if Dolibarr invoice was correctly cancelled
GC_CANCELLED	GC payment was cancelled before collection	No action
DOLIBARR_PAID_NO_GC	Dolibarr invoice paid (in the date range), no GC payment found	Verify — may be a manual or cash payment

After processing all GC payments, open Dolibarr invoices with no GC counterpart are flagged:

Flag	Meaning	Action
DOLIBARR_OPEN_NO_GC	Dolibarr invoice open, no GC payment found	Follow up — missed debit or GC export is incomplete

Pass 2 — open Dolibarr invoice audit

All invoices fetched from Dolibarr with status open (validated, not yet paid) that were not matched by any GC payment are listed as DOLIBARR_OPEN_NO_GC. Overdue invoices (due date in the past) are highlighted.

Pass 3 — GoCardless payouts ↔ Shine bank

With payouts CSV (recommended): Match by payout reference (exact) and net amount. Fee breakdown is taken directly from the payouts CSV columns:

• amount = net payout after all fees
• total_payment_amount = gross amount before fees
• transaction_fee_debit, surcharge_fee_debit, tax_debit = fee breakdown

Without payouts CSV (fallback): Groups paid_out payments by payout ID, sums amounts, and looks for a matching credit in Shine:

1. Exact match — same amount, date within 2 days → PAYOUT_VERIFIED
2. Date match only — date within 2 days but amount differs → PAYOUT_AMOUNT_MISMATCH (expected: GoCardless deducts its fee from the payout)
3. No match found → PAYOUT_MISSING

PAYOUT_AMOUNT_MISMATCH is the normal case when GoCardless fees are deducted. The difference shown in the report is the total fee charged for the period.

---

Advanced features

Retry detection

If multiple GoCardless payments exist for the same invoice reference (e.g., a failed payment was retried), the report flags them with [RETRY: PM0123, PM0456]. This helps identify when a payment was re-submitted after an initial failure.

Partial payment detection

Invoices where remain_to_pay > 0 but some payment has been made are flagged as PARTIAL. This can happen when:

• A customer paid only part of the invoice
• Multiple GoCardless payments cover a single invoice
• A credit note was applied to reduce the balance

Credit notes excluded

Credit notes (invoices with negative total_ttc) are automatically excluded from reconciliation since they don't correspond to GoCardless payments. They're handled internally in Dolibarr.

---

Output

Terminal report

============================================================
  RECONCILIATION REPORT: 2026-01-01 to 2026-01-31
============================================================

DOLIBARR
  Total invoices in scope:   9
  Open (no GC match):        2  ← needs attention
  Paid (GC matched):         3

GOCARDLESS ↔ DOLIBARR
  Matched (paid both sides): 3  ✓
  GC paid / Dolibarr open:   0
  Dolibarr paid / no GC:     0
  GC failed:                 1
  GC cancelled:              0
  GC payment / no invoice:   4  ← investigate

SHINE ↔ GOCARDLESS PAYOUTS
  Payouts expected:   2
  Verified:           0
  Amount mismatch:    2  ← check GC fees
  Missing in Shine:   0
  Expected total:     €107.74
  Actual total:       €104.91
  Difference:         €-2.83  ← GoCardless fees

ACTIONS NEEDED (6)
------------------------------------------------------------
  1. [DOLIBARR_OPEN_NO_GC]  FA2502-0075  €29.44  ARTHUR Muriel  overdue since 2025-02-01
  2. [GC_NO_INVOICE]        GC: PM01RE90...  €26.10  MARIE RIVIERE  2026-01-05
  ...

CSV export

Two CSV files are written after every run:

tmp/reconciliation_YYYY-MM-DD.csv — One row per invoice/payment with columns:

• invoice_ref, customer_name, amount_eur, invoice_date, due_date
• dolibarr_status, gc_payment_id, gc_status, gc_charge_date
• match_type, flag, action
• partial — "yes" if invoice is partially paid
• retry_group — comma-separated GC payment IDs if retries detected

tmp/payouts_fees_YYYY-MM-DD.csv — One row per payout with fee breakdown:

• payout_id, payout_date
• gross_amount_eur, net_amount_eur, fee_eur
• fee_percentage — GC fee as percentage of gross
• shine_reference — the matching Shine transaction label
• status — payout_verified, payout_missing, or payout_amount_mismatch

Both files are suitable for importing into a spreadsheet for manual review.

---

Project structure

bin/reconcile                          Entry point — parses CLI arguments, orchestrates the run
lib/
  boot.rb                              Loads all dependencies
  dolibarr/
    client.rb                          HTTP client for the Dolibarr REST API (HTTParty)
  reconciliation/
    dolibarr_fetcher.rb                Fetches invoices and customer names via Dolibarr API
    gocardless_parser.rb               Parses the GoCardless payments CSV
    shine_parser.rb                    Parses the Shine bank statement CSV
    engine.rb                          3-pass matching logic, produces flagged result set
    reporter.rb                        Formats and prints the terminal report, writes CSV
    fixer.rb                           Calls Dolibarr API to record payments (--fix mode)
gocardless/                            Drop GoCardless CSV exports here
shine/                                 Shine annual export directories (as downloaded)
tmp/                                   Output CSVs written here
.env.example                           Environment variable template
docs/
  reconciliation_plan.md               Original design document
  dolibarr.json                        Dolibarr Swagger API spec

---

Dolibarr API notes

The script uses the Dolibarr REST API with an API key (DOLAPIKEY header). Key endpoints:

Method	Path	Purpose
GET	/invoices?status=1	Fetch all non-draft invoices (open and paid)
GET	/thirdparties	Fetch customer names for invoice matching
POST	/invoices/paymentsdistributed	Record a payment against an invoice (--fix)
GET	/setup/dictionary/payment_types	Look up the GoCardless payment method ID
GET	/bankaccounts	Look up the bank account ID

The status=1 query in Dolibarr returns all non-draft invoices regardless of payment state. The script uses the statut field in the response (1=open, 2=paid, 3=cancelled) to distinguish them. Cancelled invoices are excluded from reconciliation.

---

Limitations and known behaviour

GoCardless fee deductions — Payout amounts in Shine are always slightly less than the sum of the underlying payments because GoCardless deducts its transaction fee from the payout. This is expected and reported as PAYOUT_AMOUNT_MISMATCH, not an error.

Incomplete GoCardless export — If your CSV export does not cover the full date range, payments from outside the export window will cause open Dolibarr invoices to appear as DOLIBARR_OPEN_NO_GC. Export all payments for the period you are reconciling.

Customer name matching — The soft match normalises names by stripping accents, lowercasing, and sorting words, so "DUPONT Jean" matches "Jean Dupont". If a customer's name is spelled differently in GoCardless vs Dolibarr, the soft match will fail and the payment will appear as GC_NO_INVOICE. Correct the name in one of the systems and rerun.

Credit notes — Dolibarr credit notes (AV... prefix) with negative amounts are included in the invoice fetch and will appear as DOLIBARR_PAID_NO_GC if they fall in the reconciliation period with no corresponding GoCardless refund. This is normal — credit notes are typically settled internally, not via GoCardless.

Supplier invoices — Dolibarr supplier invoices (/supplierinvoices endpoint) are on a completely separate API path and are never fetched or considered by this script.