# Business rules — bigpond_backend

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bigpond_backend.git`, `master` at `f6a315c`.

Rules below are taken from executable code. None are copied from a business-rules document, because the repository does not have one. Each rule is **Inferred** as a business rule and **Implemented** as behaviour, unless a row says otherwise.

Do not treat a rule as approved product policy until someone confirms it. The code is the evidence of what the Lambdas do.

## Session and files

| ID | Rule | Evidence |
|---|---|---|
| BR-001 | The session id is the text after `:` in the second S3 path segment, with `-` changed to `_`. The table name is `master_table_` plus that id | Trigger handlers |
| BR-002 | Objects whose key contains `/DNP/` are not imported, not matched to the bank CSV, and not sent to Textract | XLSX, CSV, and Textract handlers |
| BR-003 | A workbook file name that starts with `Expense Testing` or `Revenue Testing` is not imported. The user row is still marked `xlsx_process = 1` | `s3TriggerLambdaXLSX/index.js` |
| BR-004 | A workbook whose file name is already in `xlsx_upload_file` for that session is not imported again. `xlsx_process` is still set to 1 | `checkIfImportedAlready.js`, `updateProcessDone.js` |
| BR-005 | Only spreadsheet rows with exactly 12 cells are inserted. The first row of the first sheet is treated as a header and removed | `updateMasterTableWithXlsx.js` |
| BR-006 | Inserted workbook columns, in order, are date, source, contact, description, invoice number, reference, debit, credit, running balance, gross, GST, account code. The file name is stored on `xlsx_upload_file`. `created_by` and `updated_by` are `1` | `updateMasterTableWithXlsx.js` |
| BR-007 | After insert, every row in the session table gets `ref_number = id`, including rows that already had a ref number | `updateMasterRefNumbers.js` |
| BR-008 | Quotes, commas, and line breaks in text cells are replaced with a space or removed before insert | `updateMasterTableWithXlsx.js` |

## Xero enrichment

| ID | Rule | Evidence |
|---|---|---|
| BR-009 | The Xero tenant and token come from `company_client` for `users.current_client`. A null `current_client` uses client id `1` | `getXeroDetails.js` |
| BR-010 | Rows with a non-empty invoice number are queried with Xero `InvoiceNumbers`. Rows whose invoice number is empty or the string `undefined` are queried by contact name and listing date | `getInvoicesToProcess.js`, `processContacts.js` |
| BR-011 | A contact match updates the master row whose contact equals the listing contact, whose `date` equals that day, and whose description contains the Xero line description | `addInvoice.js` |
| BR-012 | A matched invoice writes `invoice_date`, `invoice_description` (line descriptions joined), `tx_ccy`, `ccy_rate`, `local_invoice_total` (`Total`), and `aud_invoice_total` (`Total / CurrencyRate`) | `processInvoices.js` |
| BR-013 | The first payment is written onto the existing row when `receipt_amount` is empty, null, or the string `null`. Later payments insert extra rows that copy `ref_number` and the invoice number | `processInvoices.js` |
| BR-014 | Receipt bank number is `{payment account name} - {bank account number}`. Currency is the account currency, or the invoice currency when the account has none | `processInvoices.js` |
| BR-015 | Batch total is `BatchPayment.TotalAmount` when a batch payment exists, otherwise `BankAmount` | `processInvoices.js` |
| BR-016 | A credit note uses the same first-receipt-versus-extra-row rule. Bank date, description, and amount are the applied credit. `bank_date` and `bank_description` are the string `N/A`. `bank_number` is `Credit Note {invoice number}` | `processInvoices.js` |
| BR-017 | Invoice numbers that were not returned as Xero invoices are looked up as bank transactions with `Reference` equal to that number | `processSpendMoneyTransactions.js` |
| BR-018 | Xero attachments are downloaded only when `users.workflow_type` is exactly `Expense Document` and `HasAttachments` is true | `processInvoices.js`, `processSpendMoneyTransactions.js` |
| BR-019 | Attachment names containing `.pdf`, `.png`, `.jpeg`, `.jpg`, or `.tiff` are stored at `private/{session}/{file name}` in bucket `big-pond-uploads-bucket123209-dev`. Any other type is stored under `private/{session}/DNP/` | `saveXeroFiles.js` |
| BR-020 | The S3 key is appended to `support_documents`, comma-separated | `saveXeroFiles.js` |
| BR-021 | Token refresh runs for each distinct `contact_email` on `company_client` rows that have both a refresh token and an access token. The new tokens are saved onto every row with that email. `updated_by` is `1` | `XeroRefreshToken/index.js` |
| BR-022 | Xero client id and secret are read from `xero_app_access` and decrypted with SSM parameter `xeroHash` (AES-256-GCM, PBKDF2 SHA-256, 100000 iterations) | `XeroRefreshToken/index.js` |

`aud_invoice_total` is the field name in code. Whether the business expects Australian dollars specifically is **Requires Confirmation**. The value is `Total / CurrencyRate`.

## Bank CSV

| ID | Rule | Evidence |
|---|---|---|
| BR-023 | The bank account to match is `users.selected_bank_number` for `s3_session` | `s3TriggerLambdaCSV/index.js` |
| BR-024 | CSV columns are date, description, amount, with no reliable header. A first cell equal to `date` is skipped. The first empty first cell ends the file | `s3TriggerLambdaCSV/index.js` |
| BR-025 | Dates in `DD/MM/YYYY` are zoned `Australia/Sydney`. If that parse fails, `Date.parse` is tried. Otherwise the date is null | `s3TriggerLambdaCSV/index.js` |
| BR-026 | Commas and minus signs are removed from the amount before it is stored and compared | `s3TriggerLambdaCSV/index.js` |
| BR-027 | A master row matches when the receipt date is the same calendar day, `bank_number` equals the selected bank, and either `receipt_batch_total` or `receipt_amount` equals the CSV amount | `s3TriggerLambdaCSV/index.js` |
| BR-028 | Only rows with a null or empty `bank_date` are updated. The CSV file name is stored on `csv_upload_file`. `updated_by` is `1` | `s3TriggerLambdaCSV/index.js` |

The temporary table name uses the second `-` segment of `selected_bank_number`. **Inferred** from the Xero bank-number format `Name - number`. A value without ` - ` will throw. **Requires Confirmation** of the stored format.

## Textract matching

| ID | Rule | Evidence |
|---|---|---|
| BR-029 | The job type is expense analysis, not generic document text | `processDocuments.js` |
| BR-030 | Summary fields used are `INVOICE_RECEIPT_DATE`, `INVOICE_RECEIPT_ID`, `ITEM`, `TOTAL`, `VENDOR_NAME`, and tax. When several values share a type, the higher Textract confidence wins. `TOTAL` keeps the first value that was set | `UpdateDatabaseFromTextractResponse.js` |
| BR-031 | If the vendor text contains the first word of the file name, that vendor is forced to confidence 100 | `UpdateDatabaseFromTextractResponse.js` |
| BR-032 | Total-including-GST is the Textract total. Total-excluding-GST is total minus tax | `UpdateDatabaseFromTextractResponse.js` |
| BR-033 | Match order is invoice number, then vendor plus invoice date, then gross plus invoice date, then gross plus vendor. The first update that affects a row stops the cascade | `UpdateDatabaseFromTextractResponse.js` |
| BR-034 | Vendor and contact comparison is case-insensitive `LIKE %vendor%` | `UpdateDatabaseFromTextractResponse.js` |
| BR-035 | If no row matches and that PDF name is not already on `pdf_upload_file`, insert a row with only Textract columns and the file name | `UpdateDatabaseFromTextractResponse.js` |
| BR-036 | A JSON copy of the Textract summary is written to `{original key}.json` | `UpdateDatabaseFromTextractResponse.js` |

## Delete, zip, and workbook

| ID | Rule | Evidence |
|---|---|---|
| BR-037 | Deleting a CSV clears bank date, description, amount, and `csv_upload_file` on rows that name that file | `s3TriggerLambdaRemoveFiles/index.js` |
| BR-038 | Deleting an XLSX deletes master rows whose `xlsx_upload_file` is that file name | `s3TriggerLambdaRemoveFiles/index.js` |
| BR-039 | Deleting a PDF or other non-CSV, non-XLSX, non-JSON object clears Textract columns and `pdf_upload_file`, and deletes `{key}.json` | `s3TriggerLambdaRemoveFiles/index.js` |
| BR-040 | Deleting a `.json` object does not change the master table | `s3TriggerLambdaRemoveFiles/index.js` |
| BR-041 | The completion workbook repeats one master row as PBC columns, Xero invoice columns, Textract columns, payment columns, and bank columns. Empty numeric cells stay blank. Invoice number is filled on the PBC side only when `date` is set, and on the Xero side only when `invoice_date` is set | `GenerateFile/index.js` |
| BR-042 | Zip members that are not xlsx and not csv go under `PDF+JSON Files/ref - {ref or unmatched}/`. A name containing `Completion` is used only when the file is xlsx or csv, and then it goes under `Completion Document/`. Other xlsx files go under `Excel Files/`. Other csv files go under `Csv Files/` | `ZipObjects/index.js` |
| BR-043 | The zip is named `Support.zip`. An existing object at that key is deleted before the new zip is uploaded | `ZipObjects/index.js` |

## Retention and access

| ID | Rule | Evidence |
|---|---|---|
| BR-044 | `clearMasterTables` drops a `master_table_*` table when `mysql.metadata_md_table` says `TIMEDIFF(NOW(), FROM_UNIXTIME(last_update)) > 24` for a `./BigPond/` `.frm` path | `clearMasterTables/index.js` |
| BR-045 | No Lambda checks `role_id`, `active`, company membership, or a Cognito token | All handlers. `role_id` exists only in `BigPond.sql` and commented SQL |
| BR-046 | `updated_by` / `created_by` are the constants `0` or `1` depending on the writer. They are not the signed-in user id | Xero writers use `0` for invoice updates and `1` for credits, imports, CSV, and Textract |

BR-044 is **Implemented** SQL. Reading `> 24` as "older than 24 hours" is **Inferred** and **Requires Confirmation**. `mysql.metadata_md_table` is not a standard MySQL catalog view. Whether it exists on the RDS cluster is **Unknown**.

## Schema notes that affect rules

`BigPond.sql` creates `Company`, `users`, `sessions`, `master_table`, `user_role`, and `settings`. Live handlers also use `company` (lowercase), `company_client`, `xero_app_access`, `users.workflow_type`, `users.current_client`, `users.selected_bank_number`, `users.xlsx_process`, and session-table columns that `Createsessionmaster` does not create (`contact`, `ref_number`, `textract_*`, `xlsx_upload_file`, `pdf_upload_file`, `csv_upload_file`, `receipt_amount_fx`, `receipt_batch_total`, `support_documents`).

**Requires Confirmation** where the live session table is created and altered. This repository's `CREATE TABLE` is not sufficient for the triggers.

`BigPond.sql` foreign keys include `users.id` referencing `users.created_by`, which is not a usable self-reference as written. `createdb` does not run that script. The commented handler even notes that one `ALTER` was skipped.

## Validation the code does not do

- No check that the S3 caller owns the session.
- No check that the workbook currency, tax, or dates are internally consistent.
- No idempotency key for Textract inserts beyond "this PDF name is already on a row".
- No retry record when Xero returns an error. The error is logged.
- No test asserts these rules. The repository has no test suite.