# Invoice Match — System Contract (v1)

This is the single source of truth shared by the CodeIgniter PHP app, the Python
Strands service, and the infrastructure. Every component builds against the
definitions here. Keep this file and the implementations in lock-step.

## 1. Components & responsibilities

| Component | Container | Owns |
| --- | --- | --- |
| PHP UI + app | `app` (CodeIgniter 4 + Shield) | The browser UI, Shield auth, uploads, run orchestration from the user's side, report Excel generation (PhpSpreadsheet), and all MySQL **memory** writes (`name_map`, `memory_rules`) plus reviewer updates to `results`. Owns the DB **schema** (CI4 migrations). |
| Python service | `service` (FastAPI + Strands) | The only component that reaches Odoo or a model provider. Parses the workbook, pulls invoices, runs the deterministic + agent matching, writes the **run snapshot** (`runs`, `planned_lines`, `invoices`, `results`, `traces`), streams progress over SSE. |
| Database | `mysql` (MySQL 8) | Shared state. Schema created by CI4 migrations. |
| Reverse proxy | `web` (nginx) | Serves CI4 `public/` and proxies PHP-FPM. Only published port. |

Only `app`/`web` publishes a host port. `service` and `mysql` stay on the
internal Docker network. Egress from `service` is limited to Odoo + provider.

## 2. Database schema (canonical)

Created by CodeIgniter migrations in `app/app/Database/Migrations/`. The Python
service reads/writes the same tables but never migrates. All money columns are
`DECIMAL(14,2)`, VAT-exclusive. Charset `utf8mb4`.

### `runs`
| col | type | notes |
| --- | --- | --- |
| id | BIGINT PK AI | |
| month | CHAR(7) | `YYYY-MM`, the reconciled month |
| provider | VARCHAR(32) | `bedrock` \| `anthropic` \| `openai` |
| model | VARCHAR(128) | resolved model id |
| status | VARCHAR(24) | `pending` \| `parsing` \| `fetching` \| `matching` \| `done` \| `failed` |
| source_filename | VARCHAR(255) | uploaded workbook name |
| totals_json | JSON | `{green,amber,red,unplanned,total}` counts + amounts |
| error | TEXT NULL | |
| created_at | DATETIME | |
| finished_at | DATETIME NULL | |

### `planned_lines`
| col | type | notes |
| id | BIGINT PK AI |
| run_id | BIGINT FK runs.id |
| source_row | INT | 1-based worksheet row |
| client_name | VARCHAR(255) | raw name from column B |
| canonical_client | VARCHAR(255) NULL | resolved Odoo customer name, null if unresolved |
| odoo_customer_id | INT NULL | resolved id |
| planned_amount | DECIMAL(14,2) | month-column amount, ex-VAT |
| project_value | DECIMAL(14,2) NULL | column F total, context only |
| match_source | VARCHAR(24) | `map` \| `exact` \| `learned` \| `unresolved` |

### `invoices`
| col | type | notes |
| id | BIGINT PK AI |
| run_id | BIGINT FK runs.id |
| odoo_id | INT | Odoo move id |
| number | VARCHAR(64) | invoice number |
| customer | VARCHAR(255) |
| odoo_customer_id | INT NULL |
| invoice_date | DATE |
| amount_untaxed | DECIMAL(14,2) | ex-VAT, the matched figure |
| amount_total | DECIMAL(14,2) | incl VAT, context |
| sent | TINYINT(1) | emailed to client |

### `results`
| col | type | notes |
| id | BIGINT PK AI |
| run_id | BIGINT FK runs.id |
| planned_line_id | BIGINT NULL FK planned_lines.id | null when `billed_unplanned` |
| matched_invoice_id | BIGINT NULL FK invoices.id |
| status | VARCHAR(16) | `green` \| `amber` \| `red` \| `billed_unplanned` |
| variance | DECIMAL(14,2) NULL | planned - invoiced, ex-VAT |
| confidence | DECIMAL(4,3) NULL | 0..1 |
| tier | VARCHAR(16) | `deterministic` \| `agent` \| `reverse` |
| reasoning | TEXT NULL | agent or rule explanation |
| candidates_json | JSON NULL | ranked candidates considered |
| review_status | VARCHAR(16) | `pending` \| `confirmed` \| `corrected` (default `pending`) |
| reviewed_by | VARCHAR(255) NULL |
| reviewed_at | DATETIME NULL |

### `name_map`  (memory)
| col | type | notes |
| id | BIGINT PK AI |
| planning_name | VARCHAR(255) UNIQUE |
| odoo_customer_id | INT |
| odoo_customer_name | VARCHAR(255) |
| confidence | DECIMAL(4,3) | default 1.000 |
| source | VARCHAR(16) | `manual` \| `learned` |
| created_at | DATETIME |

### `memory_rules`  (memory)
| col | type | notes |
| id | BIGINT PK AI |
| scope | VARCHAR(255) | client name or `*` |
| rule_type | VARCHAR(24) | `quarterly` \| `partial` \| `bundled` \| `ignore` |
| params_json | JSON | e.g. `{"months":[3,6,9,12]}` |
| note | TEXT NULL |
| created_at | DATETIME |

### `traces`  (OTEL spans)
| col | type | notes |
| id | BIGINT PK AI |
| run_id | BIGINT FK runs.id |
| span_id | VARCHAR(32) |
| parent_span_id | VARCHAR(32) NULL |
| name | VARCHAR(255) |
| attributes_json | JSON |
| started_at | DATETIME(6) |
| duration_ms | INT |

### `connections`  (config, secret references only — never plaintext)
| col | type | notes |
| id | BIGINT PK AI |
| `key` | VARCHAR(128) UNIQUE |
| value_ref | VARCHAR(255) | env var name / secret-store ref |
| note | VARCHAR(255) NULL |

Shield adds its own auth tables via its migrations.

## 3. HTTP API (PHP app → Python service)

Base URL inside the network: `http://service:8000`. All JSON unless noted.

| Method | Path | Body / Query | Returns |
| --- | --- | --- | --- |
| `GET` | `/health` | — | `{"status":"ok"}` |
| `POST` | `/runs` | multipart: `file` (xlsx), `month` (`YYYY-MM`), `provider` | `{"run_id":N,"status":"pending"}` — starts async run |
| `GET` | `/runs/{id}` | — | full run: run row + `planned_lines`, `invoices`, `results` |
| `GET` | `/runs/{id}/stream` | — | `text/event-stream` SSE (see §4) |
| `GET` | `/runs` | `?limit=` | run list (for the left rail) |

The service persists everything to MySQL, so the PHP app can also read runs
directly from the DB for the list/detail views; the SSE stream is only for the
live conversation while a run is in flight.

Memory + review writes are done by PHP directly against MySQL (it owns those
tables): `name_map`, `memory_rules`, and `results.review_status/reviewed_by`.

## 4. SSE event stream (§3 `/runs/{id}/stream`)

Each event is `event: <type>\ndata: <json>\n\n`. Types:

| event | data |
| --- | --- |
| `run.status` | `{"status":"parsing"}` etc. |
| `agent.spawned` | `{"agent":"orchestrator"\|"worker-3","role":"...","lines":[12,13]}` |
| `agent.state` | `{"agent":"worker-3","state":"working"\|"done","line":12}` |
| `line.decided` | `{"planned_line_id":N,"status":"amber","confidence":0.7,"reasoning":"..."}` |
| `flow.span` | `{"name":"get_candidates","attributes":{...},"duration_ms":12}` |
| `run.done` | `{"totals":{...}}` |
| `run.error` | `{"error":"..."}` |

## 5. Matching logic (deterministic core — `service/app/matching/`)

Written once, framework-free (only `openpyxl` + stdlib), so it is unit-testable
with no DB, no Odoo, no model, and reusable by the later Pi-track build.

### Sheet parsing (`sheet_parser.py`)
- Read the `projects` worksheet, rows 1–138 only.
- **Name** = column B (`B{row}`), trimmed; collapse internal newlines to space.
- **Project value** = column F.
- **Month column**: locate by header, not index. Scan the header rows for a cell
  whose value parses to a date (or `MMM-YY` / `MMM YYYY` text) with
  `year == chosen.year and month == chosen.month`. The month columns span
  Jan-2024 … Mar-2027. Use that column for the planned amount.
- **Skip** a row when: name is blank, OR name matches a known non-project label
  (case-insensitive, trimmed): `SLA Total for each month`,
  `SLA + Ad Hoc + Project + EDI + Finished Projects`, `Target`, `Total`,
  `Grand Total`, or any row whose name starts with `Total`. Also skip when the
  month amount is zero/blank/non-numeric.
- A kept row → `PlannedLine(source_row, client_name, planned_amount, project_value)`.

### Client resolution (`classifier.py` + memory)
- Resolve `client_name` → Odoo customer via `name_map` (manual then learned),
  then exact canonical equality against the invoice customer set.
- No entry and no exact hit → `unresolved` (never a silent guess). Unresolved
  lines go to the agent tier / stay amber.

### Candidate ranking (`candidates.py`) — tool `get_candidates(planned_line)`
Return ranked invoice candidates for one planned line, each with:
`{invoice_id, amount_delta, date_proximity_days, desc_similarity, score}`.
Scoring is deterministic (client match weight, amount closeness, date, desc
similarity via difflib). No model.

### Variance (`variance.py`) — tool `variance(planned, invoiced)`
`variance = round(planned - invoiced, 2)` (ex-VAT). Within tolerance test:
`abs(variance) <= max(ABS_TOLERANCE, planned * REL_TOLERANCE)`.

### Three-tier decision (`classifier.py`)
1. **Deterministic** (`tier=deterministic`, no model): canonical client resolved
   AND exactly one candidate within tolerance → **green** (confidence 1.0).
   No candidate at all for a resolved client → **red**.
2. **Agent** (`tier=agent`): the ambiguous residue — unresolved client, multiple
   candidates, amount out of tolerance, partial/bundled — gets ranked candidates
   from `get_candidates` and the worker agent decides partial / bundled /
   unmatched / low-confidence-match → **amber** with reasoning + confidence.
3. **Reverse** (`tier=reverse`): Software invoices in the month with no planned
   line → **billed_unplanned** (shown as its own section, treated like amber/red
   for review).

Memory rules adjust tier 1/2: a `quarterly` rule suppresses a red in an off
month; `partial`/`bundled` rules pre-explain expected amber cases.

## 6. Configuration (env, see `.env.example`)

| key | default | meaning |
| --- | --- | --- |
| `MATCH_ABS_TOLERANCE` | `1.00` | absolute ex-VAT tolerance (R) |
| `MATCH_REL_TOLERANCE` | `0.005` | relative tolerance (0.5%) |
| `MATCH_ROUNDING` | `2` | decimal places |
| `ODOO_URL` / `ODOO_DB` / `ODOO_USERNAME` / `ODOO_API_KEY` | — | read-only Odoo |
| `ODOO_SOFTWARE_FILTER` | `journal:Software` | how a Software invoice is identified — `journal:<name>` or `category:<name>` or `analytic:<tag>` (OPEN QUESTION 1) |
| `ODOO_API_STYLE` | `jsonrpc` | `jsonrpc` (native) or `oca_rest` (OPEN QUESTION 2) |
| `UNRESOLVED_POLICY` | `amber` | `amber` (stay for review) or `fuzzy` (suggest only) (OPEN QUESTION 3) |
| `PROVIDER_DEFAULT` | `anthropic` | default run provider |
| `ANTHROPIC_API_KEY` / `OPENAI_API_KEY` / AWS creds | — | provider auth |
| `MYSQL_*` | — | shared DB |

## 7. Open questions (defaults chosen, flagged for Jaco)
1. Exact "Software invoice" rule in Odoo → `ODOO_SOFTWARE_FILTER`, default journal name "Software".
2. Odoo REST flavour (native JSON-RPC vs OCA) → `ODOO_API_STYLE`, default `jsonrpc`.
3. Unresolved mapping fallback → `UNRESOLVED_POLICY`, default `amber` (never silent guess).
4. Green/amber amount delta + rounding → `MATCH_ABS_TOLERANCE`/`MATCH_REL_TOLERANCE`/`MATCH_ROUNDING`.