# Technical documentation — bigpond_backend

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bigpond_backend.git`, `master` at `f6a315c7009c434319775e1cb7da1604bee19d37` (2024-07-12 15:41 +0200, `updates`, author Dirk). Only remote branch: `master`.

The scan-host path `~/projects/sonar_scan/repos/bigpond_backend` was not on this machine. This note is from a read-only clone.

## What this repository is

A collection of independent AWS Lambda services, plus a local Xero OAuth sample. There is no shared package, no monorepo tool, and no root application. Each directory under `Lambda Functions/` is its own Serverless Framework service.

```text
bitbucket-pipelines.yml
sonar-project.properties
.gitignore
node-oauth2-example-master/
Lambda Functions/
  clearMasterTables/
  createdb/
  Createsessionmaster/
  csvRead/
  emailTest/
  excelRead/
  GenerateFile/
  readdb/
  s3TriggerLambdaCSV/
  s3TriggerLambdaRemoveFiles/
  s3TriggerLambdaTextract/
  s3TriggerLambdaXLSX/
  SNSClearSubscriptions/
  SNSClearTopics/
  SQSClearQueue/
  texttract/
  texttract-start-document/
  XeroRefreshToken/
  XeroTest/
  ZipObjects/
```

`sonarqube_scan.bat` is not in this commit.

## Runtime and framework

| Item | Value | Evidence |
|---|---|---|
| Language | JavaScript (CommonJS) | `require` in every handler |
| Lambda runtime | `nodejs16.x` on 18 services. `nodejs18.x` on `readdb` and `clearMasterTables` | `serverless.yml` |
| Framework | Serverless Framework `frameworkVersion: '3'` | `serverless.yml` |
| Cloud | AWS, region `ap-southeast-2`, stage `dev` | `provider` block |
| Deploy bucket | `bigpond-serverless-deployment`, SSE AES256 | `provider.deploymentBucket` |
| Account | `173510526870` | IAM role ARNs |
| Pipeline image | `maven:3.3.9` | `bitbucket-pipelines.yml` |

IAM roles referenced:

| Role | Services |
|---|---|
| `S3TriggerLambdaRole` | S3 triggers, Textract functions, zip, SNS/SQS cleanup, `excelRead`, `csvRead` |
| `bigpondLambdaRole672490e5-dev` | `createdb`, `readdb`, `Createsessionmaster`, `clearMasterTables`, `GenerateFile` |
| `bigpondLambdaRole00a4203c-dev` | `emailTest`, `XeroTest`, `XeroRefreshToken` |
| `TextractSQSRole` | Notification channel inside `processDocuments.js` |

VPC, on every service except `emailTest` (those lines are commented out):

- Security group `sg-0bf09b847f27ef29c`
- Subnets `subnet-046f4d336cfd7071e`, `subnet-0a8e823399dcc5b1f`

No function defines `events`, environment variables, or `iamRoleStatements`. The role is an existing ARN. Memory and timeout:

| Timeout | Memory | Services |
|---|---|---|
| 10 seconds | 512 MB | `texttract`, `texttract-start-document`, `readdb`, `excelRead`, `emailTest`, `csvRead`, `createdb`, `ZipObjects`, `XeroTest`, `XeroRefreshToken`, `GenerateFile`, `Createsessionmaster` |
| 150 seconds | 1024 MB | `clearMasterTables` |
| 300 seconds | 1024 MB | `s3TriggerLambdaXLSX`, `s3TriggerLambdaTextract`, `s3TriggerLambdaRemoveFiles`, `s3TriggerLambdaCSV`, `SQSClearQueue`, `SNSClearTopics`, `SNSClearSubscriptions` |

`ZipObjects` zips a whole prefix with a 10-second configuration. Whether the deployed timeout was changed outside git is **Unknown**.

## Function catalogue

| Service | Handler behaviour | Reads the event |
|---|---|---|
| `s3TriggerLambdaXLSX` | Import xlsx, assign ref numbers, call Xero, set `xlsx_process` | Yes, `Records[0].s3` |
| `s3TriggerLambdaCSV` | Match bank CSV | Yes |
| `s3TriggerLambdaTextract` | Textract expense job and DB match | Yes |
| `s3TriggerLambdaRemoveFiles` | Reverse an upload | Yes |
| `GenerateFile` | Fill the completion workbook for session `ca074a08-4731-4ee5-8d19-037746bcea2f` | No |
| `ZipObjects` | `Support.zip` for session `1fff4178-ef40-49ba-b73d-b031b6f1d5b8` | No |
| `XeroRefreshToken` | Refresh tokens for `company_client` | No |
| `clearMasterTables` | Drop old session tables | No |
| `Createsessionmaster` | `CREATE TABLE` for the `ca074a08-…` session | No |
| `createdb` | `select * from company` | No |
| `readdb` | `select * from company_client` | No |
| `excelRead` | Parse `s3-bucket-uploads123209-dev` key `private/test/UserData.xlsx`. Insert is commented out | No |
| `csvRead` | Parse `private/test/Acc 306234.csv` in the same bucket. Insert is commented out | No |
| `texttract` | Synchronous `AnalyzeExpense` on `private/test/Australian Wrapping Company.pdf` in `s3-bucket-uploads123209-dev`, write `{key}.json` | No |
| `texttract-start-document` | Hardcoded async expense job against `big-pond-uploads-bucket123209-dev` | No |
| `XeroTest` | Fetch invoice `INV-0077` for `company_client.id = 1` and save attachments under a hardcoded prefix | No |
| `emailTest` | SES mail to `dirk@overdrive.co.za` with a Xero authorize link | No |
| `SNSClearTopics` | Delete topics whose ARN contains `Textract` | No |
| `SNSClearSubscriptions` | Unsubscribe those subscriptions | No |
| `SQSClearQueue` | Delete queues whose URL contains `Textract`. One `ListQueues` page | No |

S3 key convention used by the four triggers:

```text
{anything}/{region}:{uuid}/[DNP/]{file name}
```

`session` is path part 2. `sessionid` is the substring after `:`, hyphens replaced with underscores. `ZipObjects` hardcodes `private/ap-southeast-2:{uuid}/`.

## Data stores

### Connection

Repeated in the DB handlers:

1. SSM `GetParameter` `rdsDetails` in `ap-southeast-2`, decryption off.
2. Split the value on commas. Part 0 after `=` is the host, part 1 the port, part 2 the database name.
3. Secrets Manager `GetSecretValue` for a hardcoded secret id beginning `rds!cluster-` (the same id in each handler). Username and password come from the secret JSON.

`xeroHash` is a second SSM parameter, used as the passphrase for AES-256-GCM.

### Tables the running code touches

| Table | How it is used |
|---|---|
| `users` | `s3_session`, `workflow_type`, `current_client`, `selected_bank_number`, `xlsx_process` |
| `company_client` | Xero token, refresh token, tenant id, contact email, company id |
| `xero_app_access` | `xero_client_id`, `xero_client_secret` (encrypted) |
| `company` | `createdb` selects it. `BigPond.sql` creates `Company`. Case **Requires Confirmation** |
| `master_table_{session}` | Per-session working table |
| `mysql.metadata_md_table` | Cleanup listing. Not defined in this repo |

`Createsessionmaster` creates a session table that does not include `contact`, `ref_number`, upload-file columns, Textract columns, `receipt_amount_fx`, `receipt_batch_total`, or `support_documents`. The triggers write those columns. Something outside this `CREATE TABLE` must add them, or those writes fail. **Requires Confirmation.**

`BigPond.sql` (copied in `createdb` and `Createsessionmaster`) also defines `sessions`, `user_role`, and `settings`. No current handler queries them. The script is loaded and not executed.

Columns written on `master_table_{session}` by the trigger path include:

`date`, `source`, `contact`, `description`, `invoice_number`, `referance`, `debit`, `credit`, `running_balance`, `gross`, `gst`, `account_code`, `xlsx_upload_file`, `ref_number`, `invoice_date`, `invoice_description`, `tx_ccy`, `ccy_rate`, `local_invoice_total`, `aud_invoice_total`, `receipt_date`, `receipt_amount`, `receipt_amount_fx`, `receipt_currency`, `receipt_details`, `receipt_batch_total`, `bank_number`, `bank_date`, `bank_description`, `bank_amount`, `textract_date`, `textract_vendor`, `textract_invoice`, `textract_total_excl`, `textract_incl`, `pdf_upload_file`, `csv_upload_file`, `support_documents`, `created_by`, `created_at`, `updated_by`, `updated_at`.

`referance` is the spelling in the insert.

### S3 buckets named in source

| Bucket | Use |
|---|---|
| `bigpond-serverless-deployment` | Serverless deploy artifacts |
| `s3-bucket-uploads123209-dev` | `GenerateFile` template and output, `excelRead`, `csvRead`, `texttract` |
| `big-pond-uploads-bucket123209-dev` | `ZipObjects`, Xero attachment saves, `texttract-start-document`, `XeroTest` |

Trigger handlers use `Record.s3.bucket.name` and do not hardcode the bucket. Which bucket notifies them is **Unknown**.

## External APIs

Xero Accounting, bearer token plus `Xero-tenant-id`:

- `GET /api.xro/2.0/Invoices?InvoiceNumbers=`
- `GET /api.xro/2.0/Invoices?where=Contact.Name=... AND Date=DateTime(...)`
- `GET /api.xro/2.0/Invoices/{id}`
- `GET /api.xro/2.0/Invoices/{id}/Attachments` and the file URL
- `GET /api.xro/2.0/Payments/{id}`
- `GET /api.xro/2.0/Accounts/{id}`
- `GET /api.xro/2.0/BankTransactions?where=Reference=`
- `GET /api.xro/2.0/BankTransactions/{id}` and that resource's attachments

Token endpoint: `POST https://identity.xero.com/connect/token` with `grant_type=refresh_token`.

Textract: `StartExpenseAnalysis` with an SNS notification channel (`s3TriggerLambdaTextract`), and `AnalyzeExpense` (`texttract`). Results are paged with `GetExpenseAnalysis` in `getTextraxtResults.js`.

SES: `SendEmail` in `emailTest` only. The authorize URL points at API Gateway host `8yl30vwc7i.execute-api.ap-southeast-2.amazonaws.com`, path `/dev/xerocallback`. That API is not defined in this repository.

SQL text is concatenated. Session ids, file names, invoice numbers, and Xero strings are interpolated. A failed statement is logged. Callers are not authenticated in code.

`processDocuments.js` sets `startJobId` to `""` and never assigns `response.JobId`. The later `includes` check therefore matches any notification job id.

## OAuth sample

`node-oauth2-example-master/` is a standalone Express app (`npm start`, port 5000). `package.json` name is `node_demo`. Routes:

| Path | Behaviour |
|---|---|
| `/` | Xero authorization URL |
| `/callback` | Code exchange, then `GET https://api.xero.com/connections`, first tenant stored on the session |
| `/home` | Links to the calls below |
| `/getOrganisation` | `GET /api.xro/2.0/organisation` |
| `/getInvoices` | `GET /api.xro/2.0/invoices/INV-0028` |
| `/refreshToken` | Refresh the in-memory token |

`readme.md` says only `npm install` and `npm start`. `config.json` is committed and contains the client id, client secret, redirect URL, and scopes. The session secret in `index.js` is the literal `something crazy`.

Dependencies: `express`, `express-session`, `openid-client` 2.x, `request`.

## Dependencies by service

Versions below are the ranges in each `package.json`. Lockfiles are committed.

| Service | Notable dependencies |
|---|---|
| `s3TriggerLambdaXLSX` | AWS SDK v3 S3, Secrets Manager, SSM; `axios`; `mysql2`; `xlsx` |
| `s3TriggerLambdaCSV` | AWS SDK v3 S3, Secrets Manager, SSM; `csvtojson`; `moment-timezone`; `mysql2` |
| `s3TriggerLambdaTextract` | AWS SDK v3 S3, Secrets Manager, SNS, SQS, SSM, Textract; `moment-timezone`; `mysql2` |
| `s3TriggerLambdaRemoveFiles` | AWS SDK v3 S3, Secrets Manager, SSM; `mysql2` (`csvtojson` and `moment-timezone` are declared and unused by `index.js`) |
| `GenerateFile` | AWS SDK v3 and AWS SDK v2 S3; `exceljs`; `mysql2`; `xlsx` declared |
| `ZipObjects` | AWS SDK v3 and v2; `archiver`; `mysql2`; `s3-zip` declared |
| `XeroRefreshToken` | `axios`; `mysql2`; AWS SDK v3. `xero-node`, `openid-client`, and SES client are declared. The handler uses `axios` |
| `XeroTest` | `axios`, `pdf-lib`, `pdfkit`, `pdfmake`, `base64topdf`, `mysql2`, AWS SDK v3 |
| `emailTest` | `@aws-sdk/client-ses`. `xero-node` and `openid-client` are declared and unused by `index.js` |
| `texttract` | `@aws-sdk/client-s3`, `@aws-sdk/client-textract` |
| `texttract-start-document` | Textract, S3, SNS, SQS, SSM, Secrets Manager, `mysql2`, `moment-timezone` |
| DB helpers | `mysql2`, Secrets Manager, SSM |
| SNS/SQS cleanup | The matching AWS SDK v3 client only |

## Build, deploy, and quality

`bitbucket-pipelines.yml` runs on branch `master` only. Clone depth is full. The step is the SonarQube pipe `sonarsource/sonarqube-scan:2.0.1` with repository variables `SONAR_HOST_URL` and `SONAR_TOKEN`. Cache path `~/.sonar`.

`sonar-project.properties` sets only:

```text
sonar.projectKey=overdrive_bigpond_backend_AYqLPFjuuNwRc2P2S9QY
```

There is no compile step, no `npm ci`, no test step, and no `serverless deploy` in the pipeline. Each Lambda README tells the reader to run `serverless deploy`. That text is the upstream template.

No test files, no CI test script, and no coverage configuration were found.

`.gitignore` ignores `node_modules/`, `dist/`, logs, IDE files, and media. It does not ignore `config.json`.

## Security observations

These are properties of the source, not a penetration procedure.

- Database password source and Xero client secret passphrase are named in source. The secret id is repeated in many handlers.
- `node-oauth2-example-master/config.json` contains Xero app credentials and is tracked by git.
- `emailTest` embeds a Xero client id and redirect URL.
- SQL is concatenated. File names and Xero text become part of the statement.
- Lambda handlers do not check an application user. Protection is whatever the AWS resource policy says, and that policy is not in the repo.
- The Textract SQS queue policy allows `SQS:SendMessage` for principal `AWS: "*"` with a condition that the source ARN is the topic just created (`createSNSTopicAndSQSQueue.js`).
- The OAuth sample stores access tokens in an Express session cookie that is not marked secure, and logs the access token and refresh token.
- Cleanup Lambdas can delete every SNS topic, subscription, and SQS queue in the account whose name contains `Textract`, not only resources this code created.

## Development and operations gaps

- No root README, no architecture decision record, no `.env.example`.
- No local compose file and no migration tool. Schema changes are commented SQL inside `createdb` and `readdb`.
- No structured logger. `console.log` is the error path, and the HTTP-style return is often 200.
- Node.js 16 is the configured runtime on most services. AWS end of support for that runtime is outside this repo. Whether the functions were upgraded in the console is **Unknown**.
- How S3 notifications, EventBridge schedules, or API Gateway invoke these functions is **Unknown**.

## Relationship to other BigPond repositories

Silicon Ark `bigpond_frontend_qa` documents Amplify Lambdas that use the same region, the bucket `big-pond-uploads-bucket123209-dev`, `master_table_{session}`, `company_client` Xero columns, and `workflow_type`. Its later document path uses OpenAI rather than Textract. This repository's last commit is 2024-07-12. That frontend history was collapsed on 2025-07-21.

**Requires confirmation** before treating either tree as the current production backend.