# PRD — ipw-backend

Evidence date: 2026-10-11.

Source: `git@bitbucket.org:overdrive/ipw-backend.git`, `main` at `f5ff052` (2025-06-03).

There is no prior PRD in Silicon Ark. The source README describes setup, environment variables, and a Supabase-triggered deploy loop. Requirements below are recovered from that README, `package.json`, `src/`, `deploy/`, `migration/`, `bitbucket-pipelines.yml`, and the nine commits on `main`. Provenance is labelled. Secrets are omitted.

## Product purpose

**Documented.** Backend for the IPW system. The frontend is separate.

**Implemented.** HTTP API (`/api`) that signs users in, manages IPW staff and member accounts, joins users to groups, uploads documents, and reports control products from evaluation data already stored in MySQL.

**Inferred.** Callers are a Nuxt application. Login returns `{ redirect: '/member/guidelines', ...tokens }`. Confirmation links are built from `NUXT_CLIENT_LOCATION`. Development static files are read from `client/.output/static`.

**Unknown / Requires Confirmation from this repository:** product owner, and whether this API replaced a PHP application. Comments in `AuthService` refer to a "legacy system", "Joomla / PHP", and "v1.0/php".

**Documented elsewhere.** Silicon Ark `ipw_new` quotes that repository's README: a rewrite of the old IPW website for Integrated Production of Wine. This README does not use that phrase. `ipw-frontend` is a Nuxt client of `{NUXT_API_LOCATION}/api`. `ipw-php` is a separate CodeIgniter 4 tree. Treating any of those as this API's frontend or predecessor is **Requires Confirmation**. The split commit in this repo is `1aafc59` (2025-05-26).

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| IPW staff (`user_class_id` 1) | `EUserClass.IPWStaff`. Admin queries filter `user_class_id = 1` | **Implemented** as data and as the class created by `PUT /api/admin/create-user` |
| IPW member (`user_class_id` 2) | `EUserClass.IPWMember`. Member list filters `user_class_id = 2` | **Implemented** as data and as the class created by `PUT /api/user/member` |
| IPW Administrator | Role name `IPW Administrator`. Class-level `@Roles` on `AdminController` | **Implemented** gate for `/api/admin/*` |
| System Developer | Role name. Required together with IPW Administrator on some user and role routes | **Implemented** where `@Roles` is active |
| Member Administrator, Document Uploads, auditors, content, data capturer, lead auditor, report and evaluation roles | `ERole` in `src/roles/roles.service.ts` | **Implemented** as names checked by `RolesGuard` when a route sets `@Roles` |
| Signed-in user with any role | Routes with no `@Roles` | **Implemented**. `RolesGuard` allows the request when no roles are required |
| Anonymous caller | `@Public()` on login, reset, refresh, confirm, and CSRF | **Implemented** |
| Operator of Bitbucket Pipelines | `bitbucket-pipelines.yml` on branch `main` | **Implemented** as YAML. Who may run it is **Unknown** |
| Operator of `deploy/deploy.sh` | README and the script | **Documented** and **implemented**. Whether it is scheduled is **Unknown** |

`@UserClasses(...)` is applied on `AdminController`, `GET /api/user/available-profiles`, and `PUT /api/user/member`. The guard that reads `USER_CLASS_KEY` is `src/users/users.guard.ts`. `AppModule` does not register that guard. The registered `RolesGuard` is `src/roles/roles.guard.ts`, which does not read user class. **Implemented:** user-class metadata is not enforced.

## Systems the product talks to

1. MySQL via `DATABASE_URL`. Prisma provider `mysql`. Schema is pulled (`npm run prisma` = `prisma db pull && prisma generate`). No `prisma/migrations` directory.
2. SMTP (`SMTP_HOST`, `SMTP_PORT`, `SMTP_USER`, `SMTP_PASS`, `SMTP_FROM`). `secure: false` with TLS ciphers `SSLv3` in `EmailService`.
3. Browser or Nuxt client. CORS `origin` is `JSON.parse(CLIENT_ORIGIN).origin`. Methods `OPTIONS`, `GET`, `PUT`, `POST`, `PATCH`, `DELETE`. `credentials: true`.
4. Filesystem directory `ipw_uploads`, resolved relative to the compiled file. `UPLOAD_DIRECTORY` is named in the README and in the `ProcessEnv` interface and is not read by `UploadService`.
5. Supabase REST, only from `deploy/deploy.sh`, against tables `code_deploy` and `migrations`. URL and key in the script are placeholders (`your_repo_name`, `your_supabase_service_role_key`).
6. SonarQube through Bitbucket pipe `sonarsource/sonarqube-scan:3.0.2`. Project key `overdrive_ipw-backend_1bfa3ab2-b112-4f1e-bb4c-1eaabeb1eff1`.

No queue broker, object store, or payment provider appears in the tree. `express-session` and a MySQL session store are commented out in `src/main.ts`. `ipw_sessions` is still read by `GET /api/admin/live-users`.

## Major workflows

### WF-1 — Sign in

**Implemented** in `LocalStrategy`, `AuthService.validateUser`, `AuthController.login`.

1. `POST /api/auth/login` is public and uses the local guard. Passport reads `username` and `password`.
2. Accounts must have `status = 1` and `suspended = 0`. Match is on `username` or `email`.
3. If a `user_password` row exists and argon2 verifies, that user is chosen.
4. Otherwise the `users.password` column is compared to MD5 of the password. On success, if no `user_password` row exists, one is created with argon2.
5. `users.last_logon_date` is set to now.
6. `LocalStrategy` then reloads the user with roles. If `status === 0` it throws `ForbiddenException`. `validateUser` already required `status = 1`, so this branch is not reached by the current login query.
7. Access JWT expiry `15m`, refresh JWT expiry `1d`. Claims: `sub`, `username`, `user_class_id`, `roles`. Secrets: `JWT_ACCESS_TOKEN_SECRET` and `JWT_REFRESH_TOKEN_SECRET`.
8. Refresh token is stored as argon2 in `user_refresh_token`.
9. Cookies (names `__${DOMAIN}-auth`, `__${DOMAIN}-access`, `__${DOMAIN}-refresh`): the auth cookie holds both signatures, is `httpOnly`, and lasts 1 day. Access and refresh cookies hold `header.payload`, are not `httpOnly`, and are not signed. The access cookie `maxAge` is `1000 * 60 * 2` (two minutes). The comment on that line says "15 minutes".
10. Response body is `{ redirect: '/member/guidelines', access_token, refresh_token }`. `MemberController` has no routes.

**Error.** Unknown user or bad password: `401` `INVALID_CREDENTIALS`. The guard does not distinguish those cases.

### WF-2 — Call a protected route

**Implemented.**

1. `AccessTokenGuard` runs unless `@Public()`.
2. The JWT is rebuilt by `parseReqToken`: signature from the signed `auth` cookie, header and payload from `Authorization: Bearer`. Bearer-only and cookie-only requests do not authenticate.
3. The user is loaded again by id, with roles. Missing user: `401`.
4. If the handler or class has `@Roles`, one of those role names must appear on `req.user.roles`. No `@Roles`: any authenticated user.
5. Mutating methods also pass `doubleCsrfProtection`. `GET`, `HEAD`, and `OPTIONS` are ignored. Failure JSON: `401` and message `INVALID_CSRF`.
6. `GET /api/csrf` is public and returns a generated token.

### WF-3 — Refresh and logout

**Implemented.**

1. `POST /api/auth/refresh` is public and uses `RefreshTokenGuard`. The refresh JWT is checked. The stored argon2 hash of the refresh token must verify. New tokens are issued and cookies are set. Body is `{ tokens }`.
2. `POST /api/auth/logout` requires an access token. It sets `user_refresh_token.refresh_token` to null and clears auth, access, refresh, and CSRF cookies. Status `204`.

### WF-4 — Reset and confirm

**Implemented as stubs.**

1. `POST /api/auth/reset` accepts `ResetPasswordDto.username` and returns `{ redirect: '/reset-instructions' }`. It does not look up the user, write `user_reset_token`, or queue mail.
2. `POST /api/auth/confirm/:type/:userHash/:token` returns an empty body. It does not read `user_registration_token`.
3. Templates `registration.html.ejs` and `reminder.html.ejs` say the link is valid for 1 day. No expiry check exists in code. `reminder` is not referenced by any service.

### WF-5 — Create a user

**Implemented** in `UsersService.create`.

1. `PUT /api/admin/create-user` requires role `IPW Administrator` (class-level). It rejects duplicate username with `EXISTS_USERNAME` and calls `create(..., true)`.
2. `PUT /api/user/member` has no active `@Roles`. It rejects duplicate username and calls `create(..., false)`. A comment says this should be forbidden unless the caller is a member admin or IPW administrator. That check is not in the method.
3. `create(..., true)` sets `user_class_id = 1` and inserts `users_has_group` for group id `1364` (`UsersService.ipwGroupID`, comment "database id for ipw.user_group").
4. `create(..., false)` sets `user_class_id = 2` and does not attach a group.
5. A random 20-byte hex string is stored as MD5 on `users.password` and as argon2 on `user_password`. The random value is not included in the email payload.
6. A registration token (32-byte hex) is stored in plaintext on `user_registration_token`. The link is `${NUXT_CLIENT_LOCATION}/confirm/registration/${sqids}/${token}`. Sqids uses `SQIDS_SALT` as `alphabet` and `minLength: 10`. The README lists `HASHID_SALT`. `HASHID_SALT` is not read.
7. An `email` row is inserted with template `registration` and subject lookup "Welcome to IPW".
8. `status` is set to `1`. The nearby comment says `0 = suspended`. Login treats `status = 0` as inactive and uses the separate `suspended` column.

**Unknown / Requires Confirmation:** how a new person learns a password they can type.

### WF-6 — Update or delete a user

**Implemented.**

1. `PATCH /api/admin/update-user/:id` (IPW Administrator) and `PATCH /api/user/:id` (any authenticated user) copy changed title, first name, last name, telephone (`tell`), email, and username. No changes: `400` `NO_CHANGE`. Duplicate username: `EXISTS_USERNAME`. Duplicate email: `EXISTS_EMAIL`. Missing user: `404`.
2. If `password` is non-empty, `AuthService.updatePassword` writes MD5 to `users.password` only. It does not update `user_password`.
3. `UsersController.update` has a comment that the route should be limited to the signed-in user. The method updates the id in the path.
4. Admin update has a comment "Handle un/suspension" and no suspension assignment.
5. `DELETE /api/user/:id` requires `System Developer` or `IPW Administrator` and deletes the `users` row.

### WF-7 — Groups and roles

**Implemented.**

1. Signed-in user: `GET /api/user/groups-and-roles`, `GET /api/user/available-groups`, `POST /api/user/join-group`, `GET /api/user/pending-groups`, `POST /api/user/cancel-pending-group`.
2. Join inserts `users_has_group_pending` with `status` 0. If a pending or active membership already exists: `400` `EXISTS_GROUP_ID`. Available groups exclude ids `1364` and `7361` (comments: IPW, and IPW suspended users).
3. Pending list is `status <> 1`. Cancel deletes the pending row by id. The handler does not check that the row belongs to the caller.
4. Group-and-role flags on the self-service response: role id `14` account administrator, `10` evaluation form manager, `11` view only, `16` status view only. The lookup uses `roleIds.find((r) => r.roles_id)`, which selects the first row that has a `roles_id`, not the row for the current group.
5. IPW Administrator: `POST /api/admin/add-member-role` allows only role ids `14`, `10`, `11`, `16`, then upserts one `rbac_users_has_roles` row for that membership. `POST /api/admin/role` and `DELETE /api/admin/role` add or remove a role on group `1364`.
6. `POST /api/admin/add-group-membership` inserts `users_has_group` with no duplicate check. `POST /api/admin/remove-group-membership` deletes one role row if present, then the membership.
7. `POST /api/admin/all-groups` lists `user_group` joined to `user_group_sn` where `organisation_type_id = 0`. `DELETE /api/admin/group` refuses with `EXISTS_EVAL_FORM_ENTRIES` when `group_eval_form` rows exist, otherwise deletes the `user_group` row. No audit row is written.

### WF-8 — Documents

**Implemented** in `UploadController`. Role gates are active.

1. Categories: IPW Administrator, IPW Auditor, IPW Lead Auditor, Member Administrator, Document Uploads. Filter `document_category.category_type`.
2. Own documents: Member Administrator or Document Uploads, scoped to `user_group_id` and the caller.
3. All profiles and all documents for a group: IPW Administrator, IPW Auditor, or IPW Lead Auditor.
4. Upload field name `file`. Writes `${base}\\${uuid}-${originalname}` and a `document_upload` row. Base path is `ipw_uploads` next to the project in staging/production and one directory higher in other `NODE_ENV` values. Path separator in the filename string is a Windows backslash.
5. Download sets content type and `res.download`. The comment in the controller says the download headers did not reach the frontend. The method still calls `res.download`.

### WF-9 — Control-product report

**Implemented** in `ReportsController`. Allowed roles are the staff roles plus Member Administrator, Edit Evaluation Forms, Member Reports, View Evaluation Forms, and View Status Only. `Document Uploads` is not in the list.

1. Years: distinct `eval_form.assessment_year`.
2. Group names: groups where `organisation_type_id = 0` and the caller has `users_has_group`.
3. Control products: distinct products on `group_eval_form` with `status` in `(50, 55)` for the caller's groups. Product group ids: 1 Herbicides, 2 Pesticides, 3 Fungicides, 4 Nematicides.
4. Table and CSV accept `year`, `user_group_id`, and `control_product_id`. Value `-1` means that filter is omitted. CSV `locale` `en` or `afr` changes headers only.

### WF-10 — Profiles for navigation

**Implemented** in `GET /api/user/available-profiles`. `@UserClasses(IPWMember)` is not enforced (see users table above).

The current assessment year is the latest `eval_form` with `status = 10`. Each of the caller's groups is returned with a status label from `group_eval_form` for that year, excluding status `90`.

| `group_eval_form.status` | Label in SQL |
|---|---|
| 0 | New |
| 10 | Busy |
| 20 | Submitted for Processing |
| 30 | Audit Busy |
| 50 | Pass |
| 51 | Pass Probation |
| 55 | Audit Pass |
| 60 | Fail |
| 61 | Probation |
| 65 | Audit Fail |
| 90 | Deleted/Removed (excluded from the subquery) |

If no `eval_form` with `status = 10` exists, `result` is null and the query string interpolates `assessment_year` from that null. **Implemented** without a guard.

`selectProfile` and `selectedProfile` are commented out. The comment refers to a `user_selected_group` model that is not in `schema.prisma`.

### WF-11 — Email outbox

**Implemented.**

1. On listen, if `cronjob` id `1` has `processing` true, it is set false, then `startProcessing` runs.
2. Schedule: `*/1 * * * *` when `NODE_ENV` is `staging` or `production`. Otherwise `5 * * * * *` (the comment says 20 seconds; the cron expression is second 5 of every minute).
3. Unprocessed `email` rows are sent in ascending `email_id`. The row is marked `processed` with SMTP `meta` or an error object.
4. After a batch, `toggleProcessing(false)` is commented out. The fatal `catch` does clear the flag. A successful batch can leave `processing` true until the next process start.
5. Subject map contains only `registration`. Any other template name yields an undefined subject. `reminder` templates exist and are unused.

### WF-12 — Deploy loop

**Documented** in the README as a 15-minute cron. **Implemented** in `deploy/deploy.sh` as `while true`.

When Supabase returns an unprocessed `code_deploy` row for `REPO_NAME`: stop whatever is listening on port `9000`, `git pull`, `mysqldump` to `backup/`, apply each unused file in `migration/` through the `mysql` client, `npm install`, start `npm run start:production`, health-check `http://localhost:9000`, retry start up to 3 times. Status is patched back to Supabase.

`package.json` `start:production` is `pm2 start production.config.js`. That file is not in the repository.

`migration/001_example__action_timestamp.sql` and `deploy/*.sql` use PostgreSQL (`public`, `SERIAL`, `TEXT`). The application database is MySQL. **Requires Confirmation** whether those statements are applied anywhere.

### WF-13 — Pipeline

**Implemented.** Branch `main` only. Full clone. One step: SonarQube scan, Docker memory 3072 MB, embedded Java forced. No test, build, or deploy step.

## Functional requirements

### FR-001 — API prefix and CORS

The server listens on `PORT` and `ADAPTER_IP` and serves routes under `/api`. CORS allows the origins in `CLIENT_ORIGIN` and credentialed requests.

Evidence: `src/main.ts`. Implementation: Implemented. Confidence: High.

### FR-002 — Authenticated access by default

Every route requires a reconstructed access JWT unless marked public.

Evidence: `AccessTokenGuard`, `AppModule`. Implementation: Implemented. Confidence: High.

### FR-003 — Role gate when declared

If `@Roles` is set, the caller's role names must include one required name. If `@Roles` is absent, authentication is enough.

Evidence: `src/roles/roles.guard.ts`. Implementation: Implemented. Confidence: High.

### FR-004 — Staff administration

An IPW Administrator can list staff (`suspended <> 2`, class 1), create a class-1 user in group 1364, update that user, list member activity from `auditlog` (50 rows), list `ipw_sessions`, and assign or remove roles on group 1364.

Evidence: `src/admin/admin.controller.ts`, `src/admin/admin.service.ts`. Implementation: Implemented. Confidence: High.

### FR-005 — Member and group administration

An IPW Administrator can search members (class 2, `status = 1`, `suspended <> 2`), list a user's groups, add or remove membership, set a member role to id 10, 11, 14, or 16, search groups, and delete a group that has no evaluation forms.

Evidence: `AdminController`, `GroupService`. Implementation: Implemented. Confidence: High.

### FR-006 — Self-service group join

A signed-in user can list available groups, create a pending join, list pending joins with `status <> 1`, and delete a pending row.

Evidence: `UsersController`, `UsersService`. Implementation: Implemented. Confidence: High.

### FR-007 — Registration mail

Creating a user stores a random password hash and queues a registration email containing a Nuxt confirmation URL.

Evidence: `UsersService.create`, `EmailService`. Implementation: Implemented. The confirm route does not consume the token. Confidence: High.

### FR-008 — Control-product report

A caller with a report role can list years, their organisation groups, control products on forms in status 50 or 55, and download the same rows as CSV in English or Afrikaans headers.

Evidence: `src/reports/`. Implementation: Implemented. Confidence: High.

### FR-009 — Document upload

Callers with the upload roles can list categories, store a file under `ipw_uploads`, list documents, and download by `document_upload_id`.

Evidence: `src/upload/`. Implementation: Implemented. `UPLOAD_DIRECTORY` is unused. Confidence: High.

### FR-010 — CSRF on unsafe methods

POST, PUT, PATCH, and DELETE require a CSRF token except where the middleware is not applied. GET `/api/csrf` issues a token.

Evidence: `src/main.ts`, `src/csrf/`. Implementation: Implemented. Confidence: High.

## Constraints

- Global validation pipe is not registered in `bootstrap`. DTO classes have no `class-validator` decorators. `class-validator` is not a direct dependency (it appears only as a Nest peer in the lockfile).
- `BigInt.prototype.toJSON` is patched to `parseInt` so JSON responses can include Prisma bigints.
- `admin_sessions` and `tmptbl` are `@@ignore` because Prisma reports no valid unique identifier.
- Cookie `httpOnly` and `secure` follow `NODE_ENV` in `cookieOptions`, then auth-cookie code forces `httpOnly` true for the signature cookie only.
- `jwtConstants.secret` in `src/auth/constants.ts` is not used by `JwtService.signAsync`. Signing uses the environment secrets. The constant value is omitted here.

## Empty or commented behaviour

These are in the tree and are not live routes:

- `AppController` has no handlers. `test/app.e2e-spec.ts` expects `GET /` to return `Hello World!`.
- Controllers `member`, `management`, `public`, and `audit` declare prefixes and no methods.
- Commented: select profile, username exists, public confirmation GETs, session middleware, CASL (dependency only).

Evaluation forms, content pages, menus, SAWIS farm and compliance rows, probation, and most audit writes exist as tables only.