# PRD — bigpond_backend Evidence date: 2026-10-19. Source: `git@bitbucket.org:overdrive/bigpond_backend.git`, branch `master` at `f6a315c` (2024-07-12, `updates`). Provenance labels: - **Documented** — stated in a README, SQL comment, or commit message. Almost none of the product behaviour is documented. Lambda READMEs are the Serverless Framework template. - **Implemented** — present in code on this commit. - **Inferred** — reasonably derived from code, not written as a requirement. - **Planned** — described as future work. None was found. - **Unknown** — not established. There is no PRD in the source repository. ## Product purpose **Inferred.** BigPond backend loads an accounting extract for one user session, fills invoice, payment, and bank columns from Xero and from a bank CSV, reads invoice PDFs with Amazon Textract, and writes an expense completion workbook plus a zip of support files. **Documented** only as the repository name and the script name `BigPond.sql`. The words Audit Whizz do not appear in this repository. They do appear in `bigpond_frontend_qa`. Treat that as a different repository until someone confirms they are one product. ## Users and interactions This repository has no screens and no authorizer. | Actor | What the code allows | Provenance | |---|---|---| | S3 uploader | An object create is the input the four trigger handlers read. The uploader is not identified | Implemented shape. Binding **Unknown** | | Session user | `users.s3_session` must equal the session id taken from the S3 key. The row supplies `workflow_type`, `current_client`, and `selected_bank_number` | Implemented | | Xero-connected client | `company_client` holds `xero_token`, `xero_refresh_token`, `xero_tenant_id`, `contact_email` | Implemented | | Operator | Functions such as `createdb`, `readdb`, `excelRead`, `csvRead`, `emailTest`, `XeroTest`, and `texttract` ignore the event and use hardcoded keys or SQL | Implemented | | Anonymous or role-gated user | No check | Implemented absence | `BigPond.sql` defines `users.role_id`, `user_role`, and `users.cognito_uuid`. No handler in this repository reads `role_id` or calls Cognito. ## Major workflows ### 1. Excel upload and Xero enrichment **Trigger.** `s3TriggerLambdaXLSX` expects `event.Records[0].s3`. **Inputs.** Bucket and key. Session id is the segment after `:` in the second path part, with `-` replaced by `_`. Example shape used elsewhere in the repo: `private/ap-southeast-2:{uuid}/file.xlsx`. **Behaviour.** 1. Skip keys containing `/DNP/`, and file names starting with `Expense Testing` or `Revenue Testing`. Still set `users.xlsx_process = 1` for that session. 2. If `master_table_{session}.xlsx_upload_file` already contains the file name, skip the import and still set `xlsx_process = 1`. 3. Otherwise read the first sheet, drop the header row, and insert rows whose array length is 12. 4. Set `ref_number` to the row `id`. 5. Load `workflow_type` and `current_client` from `users`. If `current_client` is null, use client id `1`. 6. Load that client's Xero token and tenant. 7. For rows with an invoice number, call Xero `Invoices?InvoiceNumbers=`. 8. For rows with an empty invoice number, call Xero `Invoices?where=Contact.Name=... AND Date=DateTime(...)`. 9. Write invoice, payment, credit-note, and unmatched spend-money fields back to the session table. 10. When `workflow_type` is `Expense Document` and Xero reports attachments, save allowed file types under the session prefix and other types under `DNP/`. 11. Set `users.xlsx_process = 1`. **Output.** Rows in `master_table_{session}`. HTTP body `"XLSX processing done"` on the success path. Failures are logged. The `catch` does not return a non-200 body. **Evidence.** `Lambda Functions/s3TriggerLambdaXLSX/`. ### 2. Bank CSV match **Trigger.** `s3TriggerLambdaCSV`, same S3 event shape. Keys containing `/DNP/` are ignored. **Inputs.** CSV with no header required. Column 1 date, column 2 description, column 3 amount. A row whose first field is `date` (any case) is skipped. The first empty first field stops the file. **Behaviour.** Read `users.selected_bank_number` for the session. Load a temporary table. Update `master_table_{session}` where the receipt date matches, the bank number matches, and either `receipt_batch_total` or `receipt_amount` equals the CSV amount, and `bank_date` is null or empty. Store the CSV file name on `csv_upload_file`. **Output.** Body `"CSV processing done"`. ### 3. Invoice document analysis **Trigger.** `s3TriggerLambdaTextract`, same S3 event shape, skipping `/DNP/`. **Behaviour.** Create an SNS topic `AmazonTextract{timestamp}` and queue `AmazonTextractQueue-{timestamp}`, start Textract `StartExpenseAnalysis`, poll SQS, then match summary fields onto the session table. Write `{object key}.json` to the same bucket. Delete the topic and queue afterwards. **Match order** (`UpdateDatabaseFromTextractResponse.js`), stopping at the first update that affects a row: 1. `invoice_number` equals the Textract invoice id. 2. Contact contains the vendor name and `invoice_date` equals the Textract date. 3. `gross` equals the Textract total including GST and `invoice_date` equals the Textract date. 4. `gross` equals that total and contact contains the vendor name. If none match and that PDF name is not already stored, insert a new row that holds only the Textract columns and `pdf_upload_file`. Vendor selection prefers a `VENDOR_NAME` whose text contains the first word of the file name. Dates are parsed as `DD/MM/YYYY` in `Australia/Sydney` when possible. ### 4. Delete an upload **Trigger.** `s3TriggerLambdaRemoveFiles`. **Behaviour.** If the file name is on `xlsx_upload_file`, `pdf_upload_file`, or `csv_upload_file`: - `.csv` clears `bank_date`, `bank_description`, `bank_amount`, and `csv_upload_file`. - `.xlsx` deletes those master rows. - `.json` does nothing. - Any other extension clears the Textract columns and `pdf_upload_file`, and deletes `{key}.json` from S3. ### 5. Completion workbook and support zip `GenerateFile` reads one hardcoded session, loads `public/bigpond-uploads/Expense Completion Document Template.xlsx` from bucket `s3-bucket-uploads123209-dev`, appends master rows, and writes `public/test/sample.xlsx`. `ZipObjects` deletes and recreates `Support.zip` for one hardcoded session prefix in `big-pond-uploads-bucket123209-dev`. Files are placed in `PDF+JSON Files/`, `Completion Document/`, `Excel Files/`, or `Csv Files/` by name. ### 6. Keep Xero tokens fresh `XeroRefreshToken` selects `company_client` rows with a non-null refresh token. For each distinct `contact_email` that also has an access token, it decrypts `xero_app_access` client id and secret and posts to `https://identity.xero.com/connect/token`. It writes the new access and refresh tokens onto every `company_client` row with that email. No schedule is declared. ### 7. Drop old session tables `clearMasterTables` reads `mysql.metadata_md_table` for files like `./BigPond/master_table_%`. When the SQL expression `TIMEDIFF(NOW(), FROM_UNIXTIME(last_update)) > 24` is true, it drops that table. **Inferred** intent is retention of about a day. The comparison is against the number `24`, not an interval literal. **Requires confirmation** that this matches the intended retention, and that `mysql.metadata_md_table` exists. ## Functional requirements ### FR-001 — Session identity from the object key The trigger handlers derive the session from the second path segment. Text after `:` becomes `master_table_{id}` after `-` is replaced with `_`. The full second segment is kept when a private S3 prefix must be rebuilt. Evidence: `s3TriggerLambdaXLSX/index.js`, `s3TriggerLambdaCSV/index.js`, `ZipObjects/index.js`. Implementation: Implemented. Confidence: High for the parsing. The bucket notification that produces the key is Unknown. ### FR-002 — Do not process the DNP prefix Keys containing `/DNP/` are not imported, matched, or sent to Textract. Xero attachments that are not pdf, png, jpeg, jpg, or tiff are stored under `private/{session}/DNP/`. Evidence: trigger `index.js` files and `saveXeroFiles.js`. Implementation: Implemented. Confidence: High. ### FR-003 — Import only 12-column workbook rows After the header row is removed, a row is inserted only when it has 12 cells. Mapped positions are date, source, contact, description, invoice number, reference, debit, credit, running balance, gross, GST, account code. Evidence: `updateMasterTableWithXlsx.js`. Implementation: Implemented. Confidence: High. There is no written column specification beyond the code. ### FR-004 — Skip a workbook that was already imported A file name already present in `xlsx_upload_file` is not imported again. `xlsx_process` is still set to 1. Evidence: `checkIfImportedAlready.js`, `updateProcessDone.js`. Implementation: Implemented. Confidence: High. ### FR-005 — Skip Expense Testing and Revenue Testing file names File names starting with those prefixes are not imported. `xlsx_process` is still set to 1. Evidence: `s3TriggerLambdaXLSX/index.js`. Implementation: Implemented. Confidence: High. Why those names are excluded is not documented. ### FR-006 — Enrich from Xero invoices, payments, credits, and spend money Invoice numbers are sent to Xero in one `InvoiceNumbers` query. Empty invoice numbers are looked up by contact name and listing date. Payments and credit notes are written onto the receipt columns. Invoice numbers that Xero did not return as invoices are looked up as bank-transaction references. Evidence: `getInvoicesToProcess.js`, `processContacts.js`, `processInvoices.js`, `processSpendMoneyTransactions.js`. Implementation: Implemented. Confidence: High for the calls. Business approval of the matching rules is **Requires Confirmation**. ### FR-007 — Download Xero attachments only for Expense Document `HasAttachments` causes a download only when `users.workflow_type === "Expense Document"`. Evidence: `processInvoices.js`, `processSpendMoneyTransactions.js`. Implementation: Implemented. Confidence: High. Other legal values of `workflow_type` are Unknown. ### FR-008 — Match a bank CSV to unpaid receipt rows Date `DD/MM/YYYY` is interpreted in `Australia/Sydney`. Amounts lose commas and minus signs before compare. A row matches on date, bank number, and either batch total or receipt amount, and only when `bank_date` is empty. Evidence: `s3TriggerLambdaCSV/index.js`. Implementation: Implemented. Confidence: High. ### FR-009 — Match Textract expense fields in a fixed order The order is invoice number, then vendor plus invoice date, then gross plus invoice date, then gross plus vendor. Unmatched documents become a new row. Evidence: `s3TriggerLambdaTextract/util/UpdateDatabaseFromTextractResponse.js`. Implementation: Implemented. Confidence: High. ### FR-010 — Roll back a deleted file CSV delete clears bank fields. XLSX delete removes the imported rows. PDF delete clears Textract fields and the sibling JSON object. Evidence: `s3TriggerLambdaRemoveFiles/index.js`. Implementation: Implemented. Confidence: High. Whether the S3 delete event is connected is Unknown. ### FR-011 — Refresh Xero tokens per contact email One successful refresh updates every `company_client` row with that `contact_email`. Evidence: `XeroRefreshToken/index.js`. Implementation: Implemented. Confidence: High. The schedule is Unknown. ### FR-012 — Build a completion spreadsheet from the session table The handler loads a fixed template and writes a fixed key. The row layout in code is PBC listing, Xero invoice, Textract, Xero payments, bank statement. Evidence: `GenerateFile/index.js`. `customHeaders.js` describes four parts and is imported but not applied. Implementation: Implemented for one hardcoded session. Confidence: High that the hardcoded path is what the file does. A per-request session API is not in this file. ### FR-013 — Zip support files for a session `Support.zip` groups PDFs and JSON by `ref_number`, and separates Excel, CSV, and names containing `Completion`. Evidence: `ZipObjects/index.js`. Implementation: Implemented for one hardcoded session and bucket. Confidence: High. ## Integrations | System | Direction | Trigger in this repo | Data | Auth | Failure behaviour | |---|---|---|---|---|---| | Amazon S3 | Read and write | Handlers assume an S3 event or a hardcoded key | Workbooks, CSV, PDFs, JSON, xlsx template, zip | Lambda IAM role | Logged. Many handlers still return 200 | | Amazon Textract | Request | `s3TriggerLambdaTextract`, plus two older functions | `StartExpenseAnalysis` / `AnalyzeExpense` | IAM, plus `TextractSQSRole` for the notification channel | `processDocumment` returns false and the handler retries after 2 seconds | | Amazon SNS and SQS | Create, poll, delete | Textract job and the three cleanup Lambdas | Job notification | IAM | Cleanup functions delete names containing `Textract` | | Amazon SES | Send | `emailTest` only | HTML link "click to approve" to `dirk@overdrive.co.za` from the same address | IAM. VPC is commented out on this function | Log "Failed to send email." Return 200 | | AWS Secrets Manager and SSM | Read | Almost every DB function | RDS user and password, `rdsDetails`, `xeroHash` | IAM | Exception logged | | Xero Accounting API | Read, and token write-back to MySQL | After an XLSX import, refresh Lambda, XeroTest, OAuth sample | Invoices, payments, accounts, bank transactions, attachments, organisation | Bearer token from `company_client`, or the sample's session | Logged per call. Import continues where a `try` surrounds the payment | | Xero identity | Token refresh | `XeroRefreshToken` | `grant_type=refresh_token` | Basic client id and secret from `xero_app_access`, decrypted with `xeroHash` | Logged. That email is skipped | | MySQL | Read and write | DB Lambdas | Session tables, `users`, `company_client`, `xero_app_access` | Secret plus SSM host | Logged | Region on the clients and on the Serverless provider is `ap-southeast-2`. ## Constraints - Serverless stage is `dev`. Deployment bucket is `bigpond-serverless-deployment` with AES256. - No function declares an event. Runtime memory is 512 MB or 1024 MB. Timeouts are 10, 150, or 300 seconds depending on the function. `ZipObjects` and `GenerateFile` are 10 seconds. - SQL is built by string concatenation, including file names, invoice numbers, and session ids. - Handlers do not validate the caller. - `nodejs16.x` is the runtime on 18 of the 20 services. `readdb` and `clearMasterTables` use `nodejs18.x`. - The OAuth sample listens on port 5000 with session secret `something crazy` and `cookie.secure: false`. `config.json` holds the client id, secret, redirect URL, and scopes. Those values are not copied here. ## Error and edge behaviour - A workbook row that is not 12 cells long is dropped with no error returned to the caller. - CSV processing stops at the first blank date cell, so rows after a blank line are ignored. - If `selected_bank_number` is null, `split` throws before the CSV `try`. - `getXeroDetails` returns `false` on error. The caller still passes that value into `updateInvoiceFromXero`. - Xero payment failures are caught per payment. Credit-note handling is outside that inner `try`. - Textract job matching compares the notification job id with `startJobId`, which is left as `""`. Every job id contains the empty string, so the first notification is treated as a match. - `SNSClearTopics` and `SNSClearSubscriptions` delete only ARNs containing `Textract`. Other topics are logged. `SQSClearQueue` lists queues once and does not follow a pagination token. - `createdb` loads `BigPond.sql` and does not execute it. The running statement is `select * from company`. - `excelRead` and `csvRead` parse a hardcoded object and do not insert. The insert blocks are comments. - Most handlers answer `{ statusCode: 200, body: "Test" }` or a done string even after a caught exception. ## Related repositories `bigpond_frontend_qa` (Silicon Ark, evidence 2026-10-19) implements a React app and Amplify Lambdas named `generatefile` and `ZipObjects`, uses bucket `big-pond-uploads-bucket123209-dev`, and uses `master_table_{session}` with the same hyphen-to-underscore session id. That tree's document intelligence path calls OpenAI, not Textract. **Requires confirmation** whether `bigpond_backend` is still invoked in production or is an earlier Lambda set kept beside the Amplify functions.