# Project plan — bigpond_backend

Evidence date: 2026-10-19.

Source repository: `git@bitbucket.org:overdrive/bigpond_backend.git`.

This plan describes branch `master` at `f6a315c7009c434319775e1cb7da1604bee19d37` (2024-07-12 15:41 +0200, message `updates`, author Dirk). That commit matches the tip recorded when this onboarding task was created. `master` is the only branch on the remote.

The local scan path `~/projects/sonar_scan/repos/bigpond_backend` was not on this machine. Evidence is a read-only clone of the remote. The source repository was not modified.

Silicon Ark had no `bigpond_backend/` directory before this set. `bigpond_frontend` and `bigpond_frontend_qa` are separate Silicon Ark directories. They share bucket names, session-table naming, and Xero table names with this repository. Whether this repository is still the deployed backend is **Unknown / Requires Confirmation**.

## Product purpose

There is no product README. The name BigPond appears in `Lambda Functions/createdb/BigPond.sql` and in the cleanup query path `./BigPond/master_table_%`.

**Inferred.** The Lambdas turn an uploaded accounting workbook, Xero invoices and payments, invoice images, and a bank CSV into one per-session table, then fill an expense completion spreadsheet and a support zip.

The repository has no user interface. Who uploads the files is not defined here.

## Scope

In this `master` tree:

- Twenty Serverless Framework services under `Lambda Functions/`, each with its own `package.json` and `serverless.yml`.
- Four S3-shaped handlers that read `event.Records[0].s3`: Excel import plus Xero enrichment, bank CSV match, Textract expense analysis, and file-delete rollback.
- Workbook generation (`GenerateFile`) and a session zip (`ZipObjects`).
- A Xero refresh-token Lambda and a Xero attachment experiment (`XeroTest`).
- Database helpers that open MySQL with a Secrets Manager password and an SSM parameter named `rdsDetails`.
- SNS/SQS cleanup Lambdas aimed at Textract topics and queues.
- A vendored Xero OAuth sample in `node-oauth2-example-master/`.
- Bitbucket Pipelines SonarQube scan on `master`, and `sonar-project.properties`.

Not in this tree:

- An HTTP API, a frontend, or Cognito configuration. `users.cognito_uuid` exists only in the unused `BigPond.sql` script.
- S3, schedule, or HTTP event bindings. Every `serverless.yml` defines a function and no `events`.
- A deploy step in `bitbucket-pipelines.yml`.
- Automated tests.
- A root README. Each Lambda `README.md` is the Serverless Framework Node.js example page.
- `sonarqube_scan.bat`. The onboarding snapshot listed it. It is not in the git tree at `f6a315c`.

## System architecture

Node.js Lambdas in `ap-southeast-2`, stage `dev`, deployed with Serverless Framework 3 into bucket `bigpond-serverless-deployment`. Most functions sit in a fixed VPC and use a pre-created IAM role. MySQL credentials come from SSM `rdsDetails` plus a hardcoded Secrets Manager secret. Xero client secrets are decrypted with SSM `xeroHash`.

```text
S3 object (key contains {region}:{session-uuid}/filename)
        │
        ├── s3TriggerLambdaXLSX  →  master_table_{session}  →  Xero Accounting API
        ├── s3TriggerLambdaCSV   →  match bank lines onto the same table
        ├── s3TriggerLambdaTextract → SNS + SQS + Textract StartExpenseAnalysis
        │                              → write textract_* columns and {key}.json
        └── s3TriggerLambdaRemoveFiles → clear or delete those rows

GenerateFile  →  Excel template in S3  →  public/test/sample.xlsx
ZipObjects    →  Support.zip under the session prefix
XeroRefreshToken → identity.xero.com/connect/token → company_client
clearMasterTables → drop old master_table_* from mysql.metadata_md_table
SNSClear* / SQSClearQueue → delete Textract topics, subscriptions, queues
```

The S3 notification that would call the trigger Lambdas is not in this repository.

## Major components

| Component | Role | Status |
|---|---|---|
| `s3TriggerLambdaXLSX` | Import a 12-column workbook and enrich it from Xero | Implemented. Event-driven shape, trigger binding absent |
| `s3TriggerLambdaCSV` | Match a bank CSV to receipt amounts | Implemented. Same trigger gap |
| `s3TriggerLambdaTextract` | Async Textract expense analysis and match | Implemented. Same trigger gap |
| `s3TriggerLambdaRemoveFiles` | Roll back a deleted upload | Implemented. Same trigger gap |
| `GenerateFile` | Fill the expense completion workbook | Implemented. Hardcoded session and output key |
| `ZipObjects` | Build `Support.zip` | Implemented. Hardcoded session and bucket |
| `XeroRefreshToken` | Refresh Xero tokens stored on `company_client` | Implemented. No schedule in the repo |
| `clearMasterTables` | Drop old `master_table_*` tables | Implemented. No schedule in the repo |
| `createdb`, `readdb`, `Createsessionmaster`, `excelRead`, `csvRead`, `texttract`, `texttract-start-document`, `XeroTest`, `emailTest` | Ad-hoc or hardcoded experiments | Implemented as invoke-and-log functions |
| `SNSClearTopics`, `SNSClearSubscriptions`, `SQSClearQueue` | Delete Textract SNS/SQS resources | Implemented |
| `node-oauth2-example-master` | Local Xero OAuth sample on port 5000 | Vendored example |

## Milestones

Taken from `git log`. There is no roadmap file.

| When | Commit | What landed |
|---|---|---|
| 2023-03-28 | `6eae0be`, `d8a197a`, `340cc9c` | Initial commit, Sonar properties, pipeline |
| 2023-04-01 | `6b0ac81` | First Lambda functions |
| 2023-04-02 to 2023-04-06 | `a3e4cff` … `24f3de6` | Textract test, Xero test, `GenerateFile`, `excelRead`, Xero example |
| 2023-04-13 to 2023-06-07 | `0f69400` … `312c5c8` | Xero function, token refresh, attachments, `ZipObjects` |
| 2023-05-19 | `735d222` | Async Textract function |
| 2023-08-18 to 2023-08-22 | `f1b8b6f`, `60a1ba4` | S3 trigger functions and SNS/SQS cleanup |
| 2023-09-12 | `f5b49c2`, `d2089d9` | Sonar and pipeline edits in Bitbucket |
| 2023-10-04 | `591a057`, `51dd7f4` | Security-hotspot edits and CORS removal |
| 2024-07-12 | `f6a315c` | CSV trigger, XLSX import, and Xero invoice processing |

No commits after 2024-07-12.

## Implementation plan

Nothing in the repository describes future work. Commented SQL and commented handlers are experiments left in the files, not a written backlog.

## Known dependencies

- AWS Lambda, S3, Textract, SNS, SQS, SES, Secrets Manager, SSM, IAM, VPC. Account `173510526870` is hardcoded in the role ARNs.
- MySQL (`mysql2`), reached with SSM `rdsDetails` and a Secrets Manager RDS secret.
- Xero Accounting API (`api.xero.com/api.xro/2.0`) and Xero identity (`identity.xero.com`).
- Serverless Framework 3, Node.js 16.x except `readdb` and `clearMasterTables` (`nodejs18.x`).
- Libraries used by the live paths: `xlsx`, `exceljs`, `csvtojson`, `moment-timezone`, `axios`, `archiver`, AWS SDK v3, and AWS SDK v2 in a few functions.
- SonarQube, via Bitbucket Pipelines pipe `sonarsource/sonarqube-scan:2.0.1`.

## Current project status

**Implemented.** The processing pipeline is present as Lambda source. Deployment wiring (S3 events, schedules, API Gateway) is not in the repo. Several functions still target hardcoded sessions, buckets, and files and return a body of `"Test"`.

**Unknown / Requires Confirmation.**

- Whether any of these functions are still deployed, and which S3 bucket notifications or schedules invoke them.
- Whether `bigpond_frontend` / `bigpond_frontend_qa` Amplify functions replaced this repository.
- The live database name, and whether tables are `company` or `Company`.
- Who the operators and end users are. No role checks exist in these handlers.