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

```bash
cd dolibarr_shine_reconciliation
bundle install
cp .env.example .env
```

Edit `.env` and fill in your Dolibarr credentials:

```dotenv
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

```bash
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:

```bash
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:

```bash
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)

```dotenv
# 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.