# PRD — unity-lambda-api

Evidence date: 2026-09-30.

Source: `git@bitbucket.org:overdrive/unity-lambda-api.git`, `master` at `fe4b095` (2021-03-17).

There is no prior PRD in the source repository or in Silicon Ark. Requirements below are recovered from `serverless.yml`, the handler files, `libs/`, `logging.js`, `mocks/`, and the five commits on that revision. Provenance is labelled. Mock identity values and the client rows in `mocks/saveimport.json` and `mocks/Untitled-1.json` are omitted.

`README.md` is the upstream Serverless Node.js Starter. It is not a requirements document for this service. Where it disagrees with the code, the code is the implemented behaviour and the README is stale starter text.

## Product purpose

**Inferred.** An AWS API, service name `unity-lambda-api`, that lets an IAM-authenticated caller read and write a MySQL database (system users, a generic table query, a CRM calendar) and store a DynamoDB note and an audit log.

**Implemented.** Thirteen HTTP functions in `serverless.yml`. Several other SQL files exist and are not functions in that file.

**Documented** only by comments in `serverless.yml` and by a block comment in `global.js`. There is no product brief.

**Unknown / Requires Confirmation:** the product owner, the caller, and whether this API is still the one a Unity client uses.

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| HTTP caller holding AWS IAM credentials | Every route sets `authorizer: aws_iam` | **Implemented**. The caller application is not in this repo |
| Cognito identity on the request | `create.js`, `get.js`, and `logging.js` read `event.requestContext.identity.cognitoIdentityId`. Comments on `getuseruuid`, `tableall`, and the calendar functions say "cognito must be logged in" | **Implemented** as a field read and as comments. No Cognito resource is in the template. The YAML authorizer is `aws_iam`, not a Cognito authorizer |
| System user row in `sysUser` | Selected by `UserID` or `strCognitoUuid` | **Implemented** as SQL. No screen is in this repo |
| Calendar owner | `crmCalendar.refSysUserID` compared to a path field `userID` | **Implemented** in `getcalendarevents.js`. The route does not define that path field |
| Member (`tbl_member`) | Queried only by unwired files | **Implemented** in those files. Not a deployed route |
| Person running Serverless locally | README `serverless offline start` and `serverless invoke local`. Mocks match that invoke shape | **Documented** in the starter README. Whether this team used it is **Unknown** |

No role check is applied on the deployed MySQL routes. `getrole.js` selects `tbl_member_role` and is not routed.

## Systems the product talks to

1. AWS API Gateway and Lambda. Service `unity-lambda-api`, runtime `nodejs12.x`, stage `prod`, region `eu-west-1`, memory 512 MB, profile `unity`. Each function is packaged alone (`package.individually: true`).
2. DynamoDB in `eu-west-1`. Table name is the literal `loging`. IAM allows describe, query, scan, get, put, update, and delete on `arn:aws:dynamodb:eu-west-1:*:*`. The table itself is not created by this template.
3. MySQL through `serverless-mysql`. Host, user, password, and database come from `DBHOST`, `DBUSERNAME`, `DBPASSWORD`, `DBNAME` via `serverless-dotenv-plugin`. The plugin loads `.env`. `env.example` does not list those names.
4. Optional VPC, commented out, using `SECURITYGROUPIDS`, `SUBNETIDS1`, `SUBNETIDS2`. Not active in the template.
5. S3 bucket `footprint-reboot-qa-zaf`, only as a URL string inside unwired `getuserdetails_example.js`. The S3 client is commented out. Not a deployed integration.

No queue, mailer, cache, or monitoring vendor is configured. There is no Bitbucket Pipelines file.

## Major workflows

Routes below are the path in `serverless.yml`. Serverless Framework REST APIs also prefix the stage, so the deployed base path is `/prod` unless a custom domain exists. No custom domain is in the template. **Requires confirmation** of the public URL.

### WF-1 — Create a DynamoDB note

**Implemented** in `create.js`, `POST /unity/create`.

1. Parse `event.body` as JSON. Fields used: `content`, `attachment`.
2. Put an item: `userId` = Cognito identity id, `noteId` = `uuid.v1()`, `content`, `attachment`, `createdAt` = `Date.now()`.
3. Table is `process.env.tableName`, which the template sets to `loging`.
4. On DynamoDB error, callback status 500 and body `{"status":false}`. On success, status 200 and the item JSON.
5. This handler does not use `libs/handler-lib.js`. The response is a single HTTP response.

This path was `POST /unity` until `19d769f`, which gave `POST /unity` to `global` and moved create to `POST /unity/create`.

### WF-2 — Read a DynamoDB note

**Implemented** in `get.js`, `GET /unity/{id}`.

1. Get item where `userId` is the Cognito identity id and `noteId` is `event.pathParameters.id`.
2. If `result.Item` is missing, throw `Error("Item not found.")`. `handler-lib.js` turns that into status 500 and body `{"error":"Item not found."}`. It is not a 404.
3. Otherwise return the item. The wrapper sets status 200. This path is not double-wrapped, because `get.js` does not call `success()`.

### WF-3 — Generic SQL (`global`)

**Implemented** in `global.js`, `POST /unity`. The file comment describes a different body (`data.a` nested under `data`, actions named Select/Update/Insert/Delete, plus group, having, and order). The function does not read that shape.

1. `JSON.parse(event.body)`.
2. `data.a === 's'` builds `SELECT {data.c} FROM {data.t}`.
3. `data.a === 'u'` builds `UPDATE {data.t} SET {data.c} `.
4. `data.a === 'i'` builds the string `INSERT INTO ` and stops.
5. Any other `data.a` returns `failure("No Action")`, then the wrapper (see WF-9).
6. If `data.j.length() > 6`, append ` LEFT JOIN ` plus `data.con`.
7. If `data.w.length() > 3`, append ` WHERE ` plus `data.con`.
8. Run the string with `mysql.query`, call `mysql.end()`, call `log(...)`.
9. If `results` is truthy, return `success(results)`, else `failure(results)`.

`length` is called as a function. On a string or array that throws, and `handler-lib.js` returns 500. Delete, group, having, order, and `uid` from the comment are not used. Insert does not append table, columns, or values.

Provenance: **Implemented** behaviour. The comment is **Documented** intent that the function does not follow.

### WF-4 — Read system users

**Implemented.**

| Route | File | Query |
|---|---|---|
| `GET /unity/user/{id}` | `getuser.js` | `SELECT * FROM sysUser WHERE UserID = {path id}` |
| `POST /unity/getusers/{id}` | `getusers.js` | `SELECT * FROM sysUser`. The path `id` is not used |
| `POST /unity/getdetails` | `getdetails.js` | `SELECT * FROM sysUser WHERE UserID = {body id}` |
| `GET /unity/uuid/{id}` | `getuseruuid.js` | `SELECT * FROM sysUser WHERE strCognitoUuid = "{path id}"` |

The comment above `getusers` says the path is `/unity/users` and the method is GET. The route is `POST /unity/getusers/{id}`.

Each of these returns through `success()` / `failure()` (WF-9) and writes an audit row (WF-8).

Columns the SQL names: `UserID`, `strCognitoUuid`. `SELECT *` can return more. Other columns are **Unknown** except where another file writes them (`strUser`, `refSecurityGroupID` in unwired `adduser.js` / `updateuser.js`).

### WF-5 — Query any table

**Implemented** in `tableall.js`, `POST /unity/tableall`.

1. Parse the body.
2. Run `SELECT * FROM `{data.table}` WHERE {data.where}`.
3. Same success/failure and audit pattern as WF-4.

No allow-list of table names. The comment above the function in `serverless.yml` was copied from `getuseruuid` ("System user's basic details is recalled") and does not describe this query.

### WF-6 — Calendar

**Implemented** in the four calendar files. All four routes are `POST` with no `{param}` in the path. All four read `event.pathParameters`.

| Route | File | SQL intent |
|---|---|---|
| `POST /unity/addcalendarevent` | `addcalendarevent.js` | `INSERT` into `crmCalendar` |
| `POST /unity/getcalendarevents` | `getcalendarevents.js` | `SELECT *` from `crmCalendar` |
| `POST /unity/updatecalendarevent` | `updatecalendarevent.js` | `UPDATE crmCalendar` |
| `POST /unity/removecalendarevent` | `removecalendarevent.js` | `DELETE FROM crmCalendar` |

Insert columns always written: `txtDescription`, `txtNotes`, `dtStartDate`, `dtEndDate`. Dates are passed through `DATE_FORMAT(..., '%Y/%m/%d %H:%i:%s')`. If `pathParameters` has `strFirstUser` or `refSysUserID`, those columns are added (`fe4b095`). Update sets the same fields and filters `CalendarID`. Delete filters `CalendarID` only.

List filter, as written:

```text
refSysUserID = {userID} OR refSysUserID = NULL
AND start date (day) is between the two request dates
AND end date (day) is between the two request dates
```

`refSysUserID = NULL` is the SQL text in the file. In MySQL a comparison with `= NULL` does not match null rows. **Inferred:** the author wanted events with no user as well as events for `userID`. **Implemented:** the predicate is `= NULL`.

An event is returned only when both its start day and its end day sit inside the requested window. **Inferred** from the two `BETWEEN` predicates. Overlap that starts before the window or ends after it is excluded by that SQL.

API Gateway does not copy a JSON body into `pathParameters`. These handlers throw if `pathParameters` is missing (`hasOwnProperty` or property read). **Unknown / Requires Confirmation:** whether callers were expected to use `serverless invoke` with a mock event, a mapping template that is not in git, or path parameters that were never added to the route.

There is no calendar mock under `mocks/`.

### WF-7 — Import

**Implemented** in `saveimport.js`, `POST /unity/import`.

1. `console.log(event.body)`.
2. The function returns nothing. MySQL setup and an `INSERT` into `tbl_permissions_group` are commented out.
3. `handler-lib.js` then JSON-encodes that return value. `JSON.stringify(undefined)` is `undefined`, so the body can be empty. Status is still 200 if `console.log` does not throw.

`mocks/saveimport.json` and `mocks/Untitled-1.json` are a wide list of client/account engagement rows (client, AWS account, contacts, country, APN stage, Silicon billing status, assessment and review dates). The stub does not read those fields. The rows are omitted from this PRD. Whether that spreadsheet was the intended import is **Unknown / Requires Confirmation**. The commented SQL inserts permission flags, which does not match those columns.

### WF-8 — Audit log

**Implemented** in `logging.js`, called after the MySQL query in the routed MySQL handlers except `saveimport` (the call is commented out there).

Puts into table `loging`:

- `userID`: Cognito identity id
- `logID`: `uuid.v1()`
- `event`: `JSON.stringify(event)` (the whole API Gateway event)
- `query`: the SQL string, JSON-encoded again
- `result`: the driver result, JSON-encoded
- `createdAt`: `Date.now()`

The put uses a callback and is not awaited. The HTTP response does not wait for DynamoDB and does not fail when the put fails. The callback is written as `(data) =>`, while the AWS SDK calls `(err, data)`.

`create.js` writes a different item shape (`userId`, `noteId`, `content`, `attachment`, `createdAt`) to the same table name.

### WF-9 — Response wrapping on MySQL handlers

**Implemented** in `libs/response-lib.js` and `libs/handler-lib.js`.

`success(body)` and `failure(body)` already return `{ statusCode, headers, body: JSON.stringify(body) }` with status 200 or 500. The MySQL handlers return that object from inside `handler()`. `handler()` treats whatever the function returns as the response body and always sets status 200 on the way out, unless the function throws.

So a `failure("No Action")` still leaves API Gateway status 200. The 500 sits inside the JSON body, which is itself a stringified response. A thrown error (bad JSON, failed query, missing `pathParameters`) is status 500 and `{ "error": "<message>" }` once.

`get.js` and `create.js` do not go through `success()`, so they are not double-wrapped. `create.js` does not go through `handler()` at all.

### WF-10 — Handlers present but not deployed

These files export `main` and are not functions in `serverless.yml`. They are not current HTTP behaviour. They are evidence of work that was started and not wired.

| File | What the SQL does | How the mock calls it |
|---|---|---|
| `adduser.js` | `INSERT INTO sysUser (strUser, refSecurityGroupID) VALUES ("{name}", 1)` | `mocks/adduser.json` path `name` |
| `updateuser.js` | `UPDATE sysUser SET strUser = "{name}" WHERE strCognitoUuid = "{userUuid}"` | `mocks/updateuser.json` |
| `getrole.js` | `SELECT MemberRoleID, Description FROM tbl_member_role WHERE MemberGroupID = "{path id}"` | No mock named for this file |
| `getuserdetails_example.js` | Large `SELECT` from `tbl_member` and lookups, `WHERE MemberID = "{path id}"`. Renamed from `getuserdetails.js` in `646ab62` | `mocks/getuserdetails.json` sends body `{ id, ModulePermission }`, which this file does not read |
| `savepermissions.js` | `INSERT` into `tbl_permissions_group` | No dedicated mock. The insert string is missing a comma before a trailing `id` token |
| `updatepermissions.js` | `UPDATE tbl_permissions_group` flags `PRIVATE`, `VIEW`, `EDIT`, `CREATE`, `DELETE` where `PermissionGroupID = data.id`. `ModifiedUserID` is a subquery on `tbl_member.CognitoUuid = data.personId` | `mocks/updatepermissions.json` |
| `updatecustompermissions.js` | If `tbl_permissions_member` has a row for `PermissionGroupID` and `MemberID`, update the five flags. Otherwise insert. The insert string puts a subquery in the `VALUES` list | `mocks/updatecustompermissions.json` |
| `updateuuid.js` | `UPDATE tbl_member SET CognitoUuid = "{uuid}" WHERE MemberID = {MemberID}` | `mocks/updateuuid.json` |

`handler.js` and `handler copy.js` export starter `hello`. Only `tests/handler.test.js` calls it. It is not in `serverless.yml`.

Mocks with no handler file: `deletepermission.json`, `getallcardpermissions.json`, `getalltypes.json`, `getbyemail.json`, `getfilteredusers.json`, `getmoduledetails.json`, `getpermissionmodules.json`, `getpermissions.json`, `getuserpermissions.json`, `listAll.json`. Their bodies name group, module, role, search text, or an email in `pathParameters.id`. No function reads them.

## Functional requirements recovered from the implementation

Deployed behaviour only. Unwired files are WF-10, not these requirements.

### FR-001 — IAM on every route

Every HTTP event in `serverless.yml` sets `authorizer: aws_iam` and `cors: true`.

Evidence: `serverless.yml`. Implementation: **Implemented**. Confidence: high.

### FR-002 — Stage, region, and runtime

The service deploys as stage `prod` in `eu-west-1` on `nodejs12.x` with 512 MB and AWS profile `unity`.

Evidence: `serverless.yml` `provider`. Implementation: **Implemented** as template values. Whether a stack exists in that account: **Unknown**. Confidence: high for the template, unknown for the account.

### FR-003 — Note create and get are per Cognito identity

Create stores `cognitoIdentityId` as `userId`. Get reads only the item with that `userId` and the path `noteId`. A missing item is an error string `Item not found.` and status 500.

Evidence: `create.js`, `get.js`, `libs/handler-lib.js`. Implementation: **Implemented**. Confidence: high.

### FR-004 — System user lookup by numeric id or Cognito uuid

`GET /unity/user/{id}` and `POST /unity/getdetails` filter `sysUser.UserID`. `GET /unity/uuid/{id}` filters `strCognitoUuid`. `POST /unity/getusers/{id}` returns every `sysUser` row and ignores `{id}`.

Evidence: the four handler files and their events. Implementation: **Implemented**. Confidence: high.

### FR-005 — Generic select, update, and insert strings

`POST /unity` accepts action codes `s`, `u`, and `i` as `data.a`. Any other code returns the failure payload described in WF-9. The insert string is incomplete. Join and where use `data.con` and call `.length()`.

Evidence: `global.js`. The comment in that file is a different contract. Implementation: **Implemented**. The comment is **not** what the function does. Confidence: high.

### FR-006 — Arbitrary table read

`POST /unity/tableall` runs `SELECT *` from `data.table` with `data.where` appended after `WHERE`.

Evidence: `tableall.js`. Implementation: **Implemented**. Confidence: high.

### FR-007 — Calendar rows in `crmCalendar`

The four POST routes are declared. The handlers insert, select, update, and delete `crmCalendar` using `event.pathParameters` (`txtDescription`, `txtNotes`, `dtStartDate`, `dtEndDate`, optional `strFirstUser` and `refSysUserID`, plus `userID` or `CalendarID`). The routes do not define those parameters.

Evidence: calendar handlers, `serverless.yml`, commit `fe4b095`. Implementation: **Implemented** in the functions. End-to-end HTTP behaviour: **Requires confirmation**. Confidence: high for the source, low for a working HTTP call.

### FR-008 — Import does not persist

`POST /unity/import` logs the body and does not query MySQL.

Evidence: `saveimport.js`. Implementation: **Implemented**. Confidence: high.

### FR-009 — MySQL handlers audit to DynamoDB

After a MySQL query, routed handlers call `log()` which puts the full event, the SQL, and the result into table `loging`. The put is not awaited.

Evidence: `logging.js` and the call sites. Implementation: **Implemented**. Confidence: high.

### FR-010 — MySQL HTTP status is 200 unless the function throws

`success` / `failure` status codes are wrapped inside a 200 body. Throws become 500 `{error}`.

Evidence: `libs/handler-lib.js`, `libs/response-lib.js`. Implementation: **Implemented**. Confidence: high.

### FR-011 — Gateway errors allow any origin

Default 4XX and 5XX gateway responses set `Access-Control-Allow-Origin` and `Access-Control-Allow-Headers` to `*`.

Evidence: `resources/api-gateway-errors.yml`. Implementation: **Implemented** in the template. Confidence: high.

### FR-012 — Environment names for MySQL

The template requires `DBHOST`, `DBUSERNAME`, `DBPASSWORD`, and `DBNAME` at deploy time. They are not in `env.example`.

Evidence: `serverless.yml`, `env.example`. Implementation: **Implemented** as references. Values: not in git. Confidence: high.

## Constraints

- Node.js 12.x is the configured runtime. It is past upstream maintenance. That is a template fact, not a decision to stay on it.
- Query text is built by concatenating request fields. There is no parameter binding and no table allow-list on `global` or `tableall`.
- IAM is the only authorizer. MySQL routes do not compare the Cognito identity to the row being read, except the calendar list SQL, which compares `refSysUserID` to a path field.
- `SELECT *` on `sysUser` returns every column of that table. Which columns exist beyond those named in SQL is **Unknown**.
- CORS origin is `*` and `Access-Control-Allow-Credentials` is `true` on handler responses.
- DynamoDB IAM is the whole region, not one table ARN.
- The audit item stores the full event JSON.
- One Jest test covers `handler.hello` only. It does not call the Unity routes.

## Discrepancies

| Topic | A | B |
|---|---|---|
| Product description | `README.md` and `package.json` name `serverless-nodejs-starter` and a public `hello` demo | `serverless.yml` service is `unity-lambda-api` |
| `POST /unity` | Starter comments in `serverless.yml` still talk about `/notes` | The route is `global.main` since `19d769f` |
| `getusers` comment | Path `/unity/users`, method GET | Route `POST /unity/getusers/{id}` |
| Calendar and `tableall` comments | "System user's basic details is recalled" and "cognito must be logged in" | Handlers are calendar SQL or `SELECT *` from a caller-supplied table. Authorizer is `aws_iam` |
| `global.js` comment | Nested `data` object, named actions, joins, group, having, order | Top-level `a`/`c`/`t`, codes `s`/`u`/`i`, `.length()` checks |
| `env.example` | `SAMPLE_ENV_VAR` | Template reads `DBHOST`, `DBUSERNAME`, `DBPASSWORD`, `DBNAME` |
| `getuserdetails` mock | Body `id` and `ModulePermission` | File reads `pathParameters.id` and does not read `ModulePermission` |
| `saveimport` mock | Client/account spreadsheet | Commented SQL is a permissions insert; live code only logs |
| Table name | Audit function is `logging.js` | Env value is `loging` |
| Notes vs logs | `create.js` uses `userId` / `noteId` | `logging.js` uses `userID` / `logID` on the same table name |
| Response helper | `failure()` sets status 500 | `handler()` wraps that object in status 200 |

## What requires human confirmation

- Is this API still deployed, and what is the base URL?
- Which MySQL database and which of `sysUser`, `crmCalendar`, `tbl_member`, `tbl_permissions_group`, and `tbl_permissions_member` exist?
- Was `loging` a typo for a logging table, and were notes and logs meant to share it?
- How should calendar fields be supplied on an HTTP POST?
- Were the unwired permission and member handlers abandoned on purpose?
- Is the import spreadsheet still a requirement?
- Which Unity client, if any, calls this service?
- Who accepts these recovered requirements as the product description?