# Features — bigpond_backend

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bigpond_backend.git`, `master` at `f6a315c` (2024-07-12).

Status values follow the code, not a feature register. The repository has no feature list of its own.

## Current features

### Excel session import

**Status:** Implemented.

**Where:** `Lambda Functions/s3TriggerLambdaXLSX/index.js`, `util/updateMasterTableWithXlsx.js`, `util/checkIfImportedAlready.js`, `util/updateMasterRefNumbers.js`, `util/updateProcessDone.js`.

Reads an S3 Excel object, inserts 12-column rows into `master_table_{session}`, copies `id` into `ref_number`, and sets `users.xlsx_process = 1`. Skips `/DNP/`, names starting `Expense Testing` or `Revenue Testing`, and a file name already stored on `xlsx_upload_file`.

The Serverless file does not attach an S3 event. The handler still requires `event.Records[0]`.

### Xero invoice, payment, credit note, and spend-money enrichment

**Status:** Implemented.

**Where:** `Lambda Functions/s3TriggerLambdaXLSX/util/Xero/`.

| Step | File | What it does |
|---|---|---|
| Choose rows | `getInvoicesToProcess.js` | Invoice numbers, or contact plus date when the number is empty |
| Contact search | `processContacts.js`, `addInvoice.js` | Xero invoice `where` on contact name and date, then match a line description |
| Invoice search | `processInvoices.js` | `Invoices?InvoiceNumbers=` |
| Payments | `processInvoices.js` | Payment, batch total, bank account `Name - BankAccountNumber` |
| Credit notes | `processInvoices.js` | Receipt columns, bank fields set to `N/A`, bank number `Credit Note {invoice}` |
| Spend money | `processSpendMoneyTransactions.js` | Bank transactions whose `Reference` equals an invoice number Xero did not return |
| Attachments | `getXeroAttatchemnet.js`, `getXeroSpendMoneyAttatchment.js`, `saveXeroFiles.js` | Only when `workflow_type` is `Expense Document` |

### Bank CSV reconciliation

**Status:** Implemented.

**Where:** `Lambda Functions/s3TriggerLambdaCSV/index.js`.

Fills `bank_date`, `bank_amount`, `bank_description`, and `csv_upload_file` on receipt rows that match date, amount, and `selected_bank_number`.

### Textract expense match

**Status:** Implemented.

**Where:** `Lambda Functions/s3TriggerLambdaTextract/` (`index.js`, `util/processDocuments.js`, `util/createSNSTopicAndSQSQueue.js`, `util/deleteSNSTopicAndSQSQueue.js`, `util/getTextraxtResults.js`, `util/UpdateDatabaseFromTextractResponse.js`).

Starts an expense-analysis job, polls SQS, writes Textract columns, stores a JSON sidecar, and removes the temporary topic and queue.

### Upload delete rollback

**Status:** Implemented.

**Where:** `Lambda Functions/s3TriggerLambdaRemoveFiles/index.js`.

Clears CSV bank fields, deletes XLSX rows, or clears PDF Textract fields and the `.json` object.

### Expense completion workbook

**Status:** Implemented, hardcoded session.

**Where:** `Lambda Functions/GenerateFile/index.js`, `customHeaders.js`.

Loads `Expense Completion Document Template.xlsx` and writes `public/test/sample.xlsx` in `s3-bucket-uploads123209-dev`. `customHeaders.js` is not used by the handler body.

### Support zip

**Status:** Implemented, hardcoded session.

**Where:** `Lambda Functions/ZipObjects/index.js`.

Writes `Support.zip` to `big-pond-uploads-bucket123209-dev` and sorts objects into PDF+JSON, Completion, Excel, and CSV folders. PDF and JSON names are prefixed with `ref - {ref_number}` when `pdf_upload_file` matches.

### Xero token refresh

**Status:** Implemented. Invocation schedule not in the repo.

**Where:** `Lambda Functions/XeroRefreshToken/index.js`.

Refreshes once per `contact_email` and updates every client row with that email. Client credentials are decrypted from `xero_app_access` with SSM `xeroHash`.

### Session-table cleanup

**Status:** Implemented. Schedule not in the repo.

**Where:** `Lambda Functions/clearMasterTables/index.js`.

Drops `master_table_*` names found in `mysql.metadata_md_table` under `./BigPond/` when the coded age expression is true.

### Textract resource cleanup

**Status:** Implemented.

**Where:** `Lambda Functions/SNSClearTopics/index.js`, `SNSClearSubscriptions/index.js`, `SQSClearQueue/index.js`.

Deletes SNS topics, subscriptions, and SQS queues whose ARN or URL contains `Textract`.

### Schema script

**Status:** Present, not executed by the handler.

**Where:** `Lambda Functions/createdb/BigPond.sql` and the same file under `Createsessionmaster/`.

Defines `sessions`, `users`, `Company`, `master_table`, `user_role`, and `settings`, plus foreign keys. `createdb/index.js` runs `select * from company` instead.

### Per-session table creator

**Status:** Implemented for one hardcoded session id.

**Where:** `Lambda Functions/Createsessionmaster/index.js`.

`CREATE TABLE if not exists master_table_{session}`. The column list is shorter than the columns the trigger functions write.

## Completed features

Git history shows these as added and still present, not as a closed product release:

| When | Commit | Feature |
|---|---|---|
| 2023-04-01 | `6b0ac81` | First Lambda set |
| 2023-04-05 | `42d3ae2` | `GenerateFile` |
| 2023-04-06 | `24f3de6` | `excelRead` and the Xero OAuth sample |
| 2023-04-13 to 2023-06-07 | `0f69400` … `312c5c8` | Xero calls, refresh, attachments |
| 2023-05-19 | `735d222` | Async Textract |
| 2023-06-05 | `672108a` | `ZipObjects` |
| 2023-08-18 | `f1b8b6f` | S3 trigger functions |
| 2023-08-22 | `60a1ba4` | XLSX trigger update and SNS/SQS cleanup |

## In development

No branch other than `master`. No TODO register. Commented blocks inside handlers are the only sign of unfinished experiments. They are not a stated plan.

## Recently changed

`f6a315c` (2024-07-12, `updates`) changed:

- `s3TriggerLambdaCSV/index.js`
- `s3TriggerLambdaXLSX/index.js`
- `s3TriggerLambdaXLSX/util/updateMasterTableWithXlsx.js`
- `s3TriggerLambdaXLSX/util/Xero/processInvoices.js`
- `s3TriggerLambdaXLSX/util/Xero/updateInvoiceFromXero.js`
- `readdb/index.js`

`51dd7f4` (2023-10-04) removed CORS handling from `SNSClearTopics` and `texttract`. `591a057` the same day edited many handlers under the message `fixing bugs,Vulnerabilities and Security hotspots`. The diff is mostly deleted lines. What the security finding was is not written down.

## Planned

None documented.

## Deprecated or removed

| Item | Evidence | Reading |
|---|---|---|
| CORS responses | Removed in `51dd7f4` from two functions | Removed |
| Synchronous Textract experiment | `Lambda Functions/texttract/index.js` still runs `AnalyzeExpense` on `private/test/Australian Wrapping Company.pdf` and writes `{key}.json`. It does not update MySQL | Still in the tree. Superseded in practice by `s3TriggerLambdaTextract` is **Inferred** |
| Earlier async Textract | `texttract-start-document/index.js` still exports a handler with a hardcoded bucket `big-pond-uploads-bucket123209-dev` and its own matching SQL | Still invocable. The S3-event version is the later file (`735d222` then `f1b8b6f`) |
| Workbook and CSV insert experiments | `excelRead` and `csvRead` parse hardcoded keys. Inserts are commented out | Still invocable. They log and return `"Test"` |
| `createdb` schema execution | `BigPond.sql` is read into `sqlBigpond` and never run. Live statement is `select * from company`. Large CREATE blocks are comments | The script remains as a record of an earlier schema |
| `sonarqube_scan.bat` | Named in the onboarding snapshot. Not in git at `f6a315c` | Not part of this commit |

## Not features of this repository

- A web UI, login, or role-based navigation.
- OpenAI document extraction. That path is in `bigpond_frontend_qa`, not here.
- Bitbucket deployment of the Lambdas. The pipeline only runs SonarQube.