dolibarr_shine_reconciliation/docs/reconciliation_plan.md

Invoice Reconciliation — Design & Implementation Plan

Context

Stack: Ruby (Rails or standalone script) Purpose: Reconcile GoCardless payments, Shine bank statements, and Dolibarr invoices to ensure all three systems are in sync. Billing flow: Customers are billed manually via GoCardless direct debit. Invoices are managed in Dolibarr. Cash lands in a Shine business bank account.

---

The Problem

Three systems hold overlapping financial data but are not automatically linked:

Shine bank ◄─── GoCardless payouts ◄─── GoCardless payments ◄─── Dolibarr invoices

Discrepancies arise when:

• A GoCardless payment is collected but Dolibarr is not marked as paid
• A Dolibarr invoice is marked paid but no GoCardless payment exists
• A GoCardless payout (batch) never landed in the Shine account

---

Data Sources

Source	What it contains	How to export
Dolibarr API	Invoices, payment status, customer info	Fetched live via REST API (/invoices)
GoCardless payments CSV	Individual payment ID, amount, date, status, customer	Dashboard → Payments → Export CSV
GoCardless payouts CSV	Payout ID, total amount, arrival date (batched bank transfers)	Dashboard → Payouts → Export CSV
Shine bank CSV	Bank transactions: date, description, amount, balance	App → Comptes → Exporter le relevé

---

Architecture

lib/tasks/reconcile.rake                ← CLI entry point (rake task)
app/services/reconciliation/
  ├── dolibarr_fetcher.rb               ← Fetch invoices + payment records via Dolibarr API
  ├── gocardless_parser.rb              ← Parse GoCardless payments CSV
  ├── gocardless_payout_parser.rb       ← Parse GoCardless payouts CSV
  ├── shine_parser.rb                   ← Parse Shine bank statement CSV
  ├── engine.rb                         ← Core matching logic (3 passes)
  └── reporter.rb                       ← Terminal summary + CSV output

---

Reconciliation Passes

Pass 1 — Dolibarr open invoice audit

• Fetch all Dolibarr invoices with status=1 (validated/open) for the date range
• Flag any that are past their due date: these should have a GoCardless payment but don't
• Output: list of overdue open invoices needing action

Pass 2 — GoCardless ↔ Dolibarr matching

For each GoCardless payment in the CSV:

Matching strategy (in order of confidence):

1. Strong match: num_payment field in Dolibarr payment record == GoCardless payment ID
2. Soft match: amount_ttc + customer_name + date within ±5 days

Flags:

• GC_PAID_DOLIBARR_OPEN — GoCardless collected payment, Dolibarr invoice still open → needs mark_as_paid call
• DOLIBARR_PAID_NO_GC — Dolibarr marked paid but no GoCardless payment found → manual payment, verify
• GC_FAILED — GoCardless payment failed, check if Dolibarr invoice is still open
• MATCHED — Both sides agree, no action needed

Pass 3 — Shine bank ↔ GoCardless payout verification

• GoCardless batches individual payments into payouts and deposits them as a single bank transfer
• Each GoCardless payout should appear as a credit in Shine (description contains "GOCARDLESS" or similar)
• Match by: payout arrival date ±2 days + amount

Flags:

• PAYOUT_MISSING — GoCardless payout not found in Shine bank statement
• AMOUNT_MISMATCH — Payout found but amounts differ (possible fee deduction issue)
• VERIFIED — Payout confirmed in bank account

---

Dolibarr API Endpoints Used

GET  /invoices?status=1&limit=500        # Open/validated invoices
GET  /invoices?status=2&limit=500        # Paid invoices
GET  /invoices/{id}                      # Single invoice with payment history
GET  /invoices/{id}/payments             # Payment records for an invoice
POST /invoices/paymentsdistributed       # Record a payment (for --fix mode)

Key Dolibarr invoice fields:

• ref — Invoice reference (e.g., FA2601-0034)
• total_ttc — Total amount including tax (euros, float)
• paye — Payment flag: 1 = paid, 0 = unpaid
• sumpayed — Total amount already paid
• remaintopay — Remaining balance due
• socid — Customer (third-party) ID
• status — 0=draft, 1=validated, 2=paid, 3=cancelled

Dolibarr mark-as-paid payload:

{
  "arrayofamounts": {
    "{INVOICE_ID}": {
      "amount": 19.99,
      "multicurrency_amount": null
    }
  },
  "datepaye": 1748524447,
  "paymentid": 6,
  "closepaidinvoices": "yes",
  "accountid": 1,
  "num_payment": "PM01234567",
  "comment": "GoCardless payment — auto-reconciled"
}

(paymentid: 6 = GoCardless payment method ID in Dolibarr — verify this value in your instance)

---

GoCardless CSV Format

GoCardless payment export columns (verify against your actual export):

id, amount, currency, status, charge_date, description, reference,
customer_id, customer_name, mandate_id, payout_id

Relevant statuses: paid_out, confirmed, failed, cancelled, pending_submission

GoCardless payout export columns:

id, amount, currency, status, arrival_date, reference

---

Shine CSV Format

Shine bank export columns (verify against your actual export):

Date, Libellé, Montant, Catégorie, Notes, Solde

GoCardless payouts typically appear with Libellé containing GOCARDLESS or GC.

Note: Column names may vary by export language/version. Make the parser configurable.

---

CLI Interface

# Dry run — report only, no changes
bin/rails reconcile:run \
  FROM=2026-01-01 \
  TO=2026-01-31 \
  GC=tmp/gocardless_payments.csv \
  GC_PAYOUTS=tmp/gocardless_payouts.csv \
  SHINE=tmp/shine_january.csv

# Auto-fix — mark Dolibarr invoices as paid where GC payment is confirmed
bin/rails reconcile:run \
  FROM=2026-01-01 \
  TO=2026-01-31 \
  GC=tmp/gocardless_payments.csv \
  GC_PAYOUTS=tmp/gocardless_payouts.csv \
  SHINE=tmp/shine_january.csv \
  FIX=true

---

Expected Report Output

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

DOLIBARR SUMMARY
  Total invoices validated:   47
  Total invoices paid:        43
  Open and overdue:            4  ← ACTION NEEDED

GOCARDLESS ↔ DOLIBARR
  Matched (no action):        43  ✓
  GC paid / Dolibarr open:    3  ← Dolibarr needs marking as paid
  Dolibarr paid / no GC:      1  ← Verify manual payment
  GC failed:                   0

SHINE ↔ GOCARDLESS PAYOUTS
  Payouts expected:            8
  Payouts found in Shine:      8  ✓
  Total amount expected:   €842.58
  Total amount received:   €842.58
  Difference:              €0.00  ✓

ACTIONS NEEDED:
  1. [GC_PAID_DOLIBARR_OPEN] FA2601-0034  €19.99  DUPONT Jean     GC: PM01234567  2026-01-05
  2. [GC_PAID_DOLIBARR_OPEN] FA2601-0041  €29.99  MARTIN Paul     GC: PM05678901  2026-01-12
  3. [GC_PAID_DOLIBARR_OPEN] FA2601-0052  €19.99  DURAND Marie    GC: PM09012345  2026-01-19
  4. [DOLIBARR_PAID_NO_GC]   FA2601-0023  €19.99  LEROY Claude    Paid: 2026-01-08 (no GC match)

Report saved to: tmp/reconciliation_2026-01-31.csv

---

Output CSV Format

invoice_ref,customer_name,amount_ttc,invoice_date,dolibarr_status,gc_payment_id,gc_status,gc_charge_date,match_status,action
FA2601-0034,DUPONT Jean,19.99,2026-01-01,open,PM01234567,paid_out,2026-01-05,GC_PAID_DOLIBARR_OPEN,mark_dolibarr_paid
FA2601-0041,MARTIN Paul,29.99,2026-01-08,open,PM05678901,paid_out,2026-01-12,GC_PAID_DOLIBARR_OPEN,mark_dolibarr_paid
FA2601-0023,LEROY Claude,19.99,2026-01-07,paid,,,,DOLIBARR_PAID_NO_GC,verify_manually
FA2601-0012,DUPONT Jean,19.99,2025-12-01,paid,PM98765432,paid_out,2025-12-05,MATCHED,none

---

Implementation Notes

Matching tolerance

• Amount: exact match (compare in cents to avoid float issues — multiply Dolibarr's euro amounts by 100)
• Date: ±5 days between GoCardless charge_date and Dolibarr invoice date
• Customer: normalize names before comparing (strip accents, lowercase, trim)

Dolibarr client setup

The Dolibarr::Client requires:

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

Edge cases to handle

• One GoCardless payment covering multiple invoices (rare but possible)
• Credit notes in Dolibarr offsetting invoice balances
• GoCardless payment retried after initial failure (same invoice, multiple GC payment IDs)
• Shine CSV encoding (often ISO-8859-1, convert to UTF-8)
• GoCardless fees: payouts may be slightly less than sum of payments due to GC fees

Dolibarr payment method ID

When calling paymentsdistributed, paymentid must match the Dolibarr payment method ID for GoCardless in your instance. Check:

GET /setup/dictionary/payment_types

Find the entry for GoCardless (often named "Prélèvement GoCardless" or similar).

---

Future Enhancements

• Scheduled run: Run monthly via cron, email report to admin
• Web interface: Upload CSVs via admin UI, view reconciliation in browser
• Stripe support: Same engine, add a StripeParser for Stripe payouts
• Webhook-driven: Instead of CSV imports, consume GoCardless webhooks in real time to auto-reconcile as payments land