# PRD — intersect_backend

Evidence date: 2026-10-12.

Source: `git@bitbucket.org:overdrive/intersect_backend.git`, `main` at `86347d4` (2024-09-26, Jaco Brits, "Update new code").

There is no prior PRD in the source repository or in Silicon Ark for this directory. Requirements below are recovered from the tracked tree at that revision. Provenance is labelled. Secret values committed in `supabase/functions/sent_que/index_old.ts` and in the two nested `.env` files are omitted.

## Product purpose

**Inferred.** Send email for a product that uses the sender `no-reply@simpleflowhub.app`.

**Implemented.** `supabase/functions/sent_que/index.ts` loads Supabase rows from `send_que` where `status` is 0 and sends each one through Amazon SES SMTP. `supabase/supabase/functions/send-aws-ses-email/index.ts` sends one HTML message from a POST body and does not read the table. `send-email` and `hello-world` return a greeting and do not send mail.

**Documented** only by commit subjects ("Adding edge functions", "Update new code"), by comments inside the functions, and by the Supabase CLI template comments in `config.toml`. There is no README.

**Unknown / Requires Confirmation:** the product owner, the caller, and whether this code is still the mailer. Silicon Ark for `intersect_cloud` and `intersect_lambda` describes a `send_que` table and a later Lambda worker. This repository does not name those projects. See `project-plan.md`, Related documentation.

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| HTTP caller of `sent_que` | `Deno.serve(async (req) =>` in `supabase/functions/sent_que/index.ts` | **Implemented**. `req` is unused. The caller is not named |
| HTTP caller of `send-aws-ses-email` | POST body `{ to, subject, htmlContent }` | **Implemented**. No authentication check in the function |
| HTTP caller of `hello-world` or `send-email` | JSON body `{ name }` | **Implemented**. Template code |
| Process that inserts `send_que` rows | Columns the function reads must already exist | **Unknown**. Not in this repo |
| Operator of Bitbucket Pipelines | `bitbucket-pipelines.yml` runs on branch `main` | **Implemented** as YAML. Who may run it is **Unknown** |
| Person using the Supabase CLI locally | `supabase/config.toml`, `project_id = "intersect_backend"` | **Implemented** as local config. No `supabase/migrations` |

No user-admin screen, session store, or role check exists in this repository.

## Systems the product talks to

1. Supabase, from `sent_que/index.ts` and `index_old.ts`. URL `SUPABASE_URL`. Key `SUPABASE_SERVICE_ROLE_KEY`. Table `send_que`. The client in the current file is `https://esm.sh/@supabase/supabase-js@2`.
2. Amazon SES SMTP, host `email-smtp.eu-west-1.amazonaws.com`, port `587`, `secure: false`. Current function reads `SES_SMTP_USERNAME` and `SES_SMTP_PASSWORD`. The old file hardcodes both. Values omitted.
3. Amazon SES API, only in `send-aws-ses-email/index.ts`, via `npm:@aws-sdk/client-ses` `SendEmailCommand`. Region and keys from `AWS_REGION`, `AWS_SES_KEY_ID`, and `AWS_SES_SECRET_KEY`. Both committed `.env` files set `AWS_REGION` to `eu-west-1`.
4. Bitbucket Pipelines, image `maven:3.3.9`, pipe `sonarsource/sonarqube-scan:2.0.1`. Sonar project key `overdrive_intersect_backend_574dd118-0a6f-46b5-ae71-3d99f677206c`. `SONAR_HOST_URL` and `SONAR_TOKEN` are pipeline variables, not files.

A committed CLI temp file names Supabase project ref `bjlmgdkcktvfshewkfgc` and a pooler host `aws-0-eu-central-1.pooler.supabase.com`. The password in that URL is the placeholder `[YOUR-PASSWORD]`. That metadata was added on 2024-09-11, before `sent_que`. It does not prove the later function was deployed there.

No S3 client, queue broker, or HTTP API client other than the Supabase and SES clients above is present. Nodemailer may fetch an attachment when `path` is a URL. This code does not call Supabase Storage.

## Major workflows

### WF-1 — Drain `send_que`

**Implemented** in `supabase/functions/sent_que/index.ts`. Trigger: any HTTP request that reaches the function. The body and method are ignored. No schedule is in git.

1. Create a Supabase client with `SUPABASE_URL` and `SUPABASE_SERVICE_ROLE_KEY`. Missing env values become `''` because of `?? ''`.
2. Create a nodemailer transport to the SES SMTP host above. SMTP user and password are the env vars named in the previous section, with no `??` fallback (unset becomes `undefined`).
3. Select `*` from `send_que` where `status` equals `0`. No `limit`. No `order`.
4. If the select returns an error, log it and `return []`. That value is a JavaScript array, not a `Response`. What the Edge Runtime does with it was not executed here. **Unknown / Requires Confirmation** at runtime.
5. For each row, read `recipient`, `subject`, and `body`. Pass `body` as both the text part and the HTML part.
6. If `attachment` is truthy, add one nodemailer attachment. `filename` is the last segment of `attachment.split("/")`. `path` is the raw `attachment` string. The comment in code says "Attachment URL or local path".
7. `sendMail` from `no-reply@simpleflowhub.app`.
8. If `sendMail` resolves, set `status` to `1` and `result` to the JSON string `{"success":true,"message":"Email sent successfully"}`.
9. If `sendMail` throws, the inner catch returns `{ success: false, message: 'Failed to send email' }`. The row is then updated to `status` `2` with that object JSON-encoded. The SES error text is logged and is not stored.
10. If the update errors, log it and continue with the next row. The function does not change `status` again.
11. After the loop, return HTTP body `OK` with header `Content-Type: application/json`. The body is the characters `OK`, not a JSON value. No status code is set (the `Response` default is 200).

There is no lock and no in-progress status. A second invocation can select the same `status` 0 rows. **Inferred** race.

If the send succeeds and the update fails, the row stays at `status` 0. The next run can send it again. **Inferred** from the update-error branch, which only logs.

### WF-2 — Previous drain (`index_old.ts`)

**Implemented** in the file. **Not** the Supabase entry point: the platform loads `index.ts` from the function folder.

1. SMTP via `https://deno.land/x/smtp@v0.7.0/mod.ts`, STARTTLS, same host and port, same from address.
2. Username and password are string literals in the file. Omitted here. They are committed at HEAD and in history.
3. Select is the same `send_que` / `status` 0 query. A select error returns an empty array from the helper, and the handler still responds 200 with `Email queue processed successfully`.
4. Send uses a text `content` only. No HTML part. No attachment.
5. Success sets `status` to `1`. Failure sets `status` to `2`. `result` is not written.
6. A throw around the whole loop returns 500 and the text `Error processing email queue`.

Commented imports show an earlier SES SDK attempt (`AWS_SES_KEY_ID2`, `AWS_SES_SECRET_KEY2`, region default `eu-west-1`). Those lines are comments. They do not run.

### WF-3 — Direct HTML send

**Implemented** in `supabase/supabase/functions/send-aws-ses-email/index.ts`.

1. If `req.method` is not `POST`, return 405 and `Method not allowed`.
2. Parse JSON. Read `to`, `subject`, `htmlContent`. No check that they are present or that `to` is an email address.
3. `SendEmailCommand` with `Source` `no-reply@simpleflowhub.app`, `ToAddresses: [to]`, subject charset `UTF-8`, HTML body `htmlContent`. No text part, no attachment, no cc, no bcc.
4. Log `to` before the send (`console.log("to", to)`).
5. Success: 200 and the text `Email sent successfully: ` plus `JSON.stringify` of the SES response.
6. Any throw, including a JSON parse failure, returns 500 and `Error sending email: ` plus `error.message`.

The SES client is constructed at module scope from the three env vars. The function does not read the `.env` file beside it. How those variables are injected when the function runs is **Unknown / Requires Confirmation**. The file on disk has non-placeholder values. They are omitted.

### WF-4 — Greeting templates

**Implemented** in `hello-world/index.ts` and `send-email/index.ts`. The two files match, apart from the function name in the sample `curl` comment.

1. Log `Hello from Functions!` at load.
2. `await req.json()`, read `name`, return JSON `{ "message": "Hello <name>!" }` with `Content-Type: application/json`.
3. No method check. A body that is not JSON throws. There is no catch.

`send-email/.env` holds AWS SES variable names and non-placeholder values. `send-email/index.ts` does not read them.

The sample comment includes the stock local demo bearer from the Supabase function template. It is not copied here.

### WF-5 — Pipeline analysis

**Implemented** in `bitbucket-pipelines.yml`:

1. Image `maven:3.3.9`. Clone depth `full`.
2. Branch pipeline `'{main}'` only. No pull-request pipeline. No default pipeline.
3. One step, "SonarQube analysis", pipe `sonarsource/sonarqube-scan:2.0.1`.
4. Cache `sonar: ~/.sonar`.
5. No test command, no `npm install`, no `supabase functions deploy`.

Whether a pipeline has succeeded is **Unknown / Requires Confirmation**.

## Functional requirements

| ID | Requirement | Evidence | Implementation |
|---|---|---|---|
| FR-001 | An invocation of `sent_que` selects every `send_que` row with `status` 0 | `index.ts` `.from("send_que").select("*").eq("status", 0)` | Implemented. No limit and no order |
| FR-002 | Each selected row is sent as mail from `no-reply@simpleflowhub.app` to `recipient`, with `subject`, and with `body` used as both text and HTML | `sendEmail` in `index.ts` | Implemented |
| FR-003 | A truthy `attachment` is one nodemailer attachment. The filename is the last `/`-separated segment. The path is the stored string | `index.ts` | Implemented. Download, content type, and size limits are not in this file |
| FR-004 | A send that does not throw sets `status` 1 and stores a fixed success JSON string in `result` | `index.ts` update | Implemented. The nodemailer response is logged, not stored |
| FR-005 | A send that throws sets `status` 2 and stores `{"success":false,"message":"Failed to send email"}` | `index.ts` | Implemented. The underlying error is not stored |
| FR-006 | An update error is logged and the loop continues | `index.ts` | Implemented |
| FR-007 | When the select itself errors, the handler returns a bare array | `return []` | Implemented in source. The HTTP result is **Unknown** |
| FR-008 | After a processed loop, the handler returns the body `OK` | `new Response("OK", …)` | Implemented |
| FR-009 | `POST` to `send-aws-ses-email` sends one HTML email to `to` from the same from-address | `SendEmailCommand` | Implemented. Required-field checks are absent |
| FR-010 | Any other method to `send-aws-ses-email` returns 405 and does not send | `req.method !== "POST"` | Implemented |
| FR-011 | `hello-world` and `send-email` return a greeting built from JSON `name` | Both `index.ts` files | Implemented. They do not send mail |
| FR-012 | A push to `main` is the only pipeline, and it runs SonarQube | `bitbucket-pipelines.yml` | Implemented as YAML |
| FR-013 | Local Supabase API exposes schemas `public` and `graphql_public`, max 1000 rows | `supabase/config.toml` `[api]` | Implemented as CLI template settings. See constraints |

## Validation and error conditions

| Case | Behaviour in `sent_que/index.ts` | Label |
|---|---|---|
| Select error | Log, return `[]` | Implemented |
| `data` empty | Loop does not run, return `OK` | Implemented |
| `recipient` missing or not an email | Still passed to `sendMail`. No format check | Implemented as the absence of a check |
| `body` null | Passed through as text and HTML | Implemented. Nodemailer's reaction was not executed |
| `attachment` empty string or null | No attachment (`if (email.attachment)`) | Implemented |
| SMTP env vars unset | Transport is still created | Implemented. Send then fails and the row goes to `status` 2 if `sendMail` throws |
| Supabase env vars unset | Client is created with empty strings | Implemented. Select then likely errors. Runtime confirmation was not run |
| Update fails after a successful send | Row can remain `status` 0 | Implemented log-only branch. Duplicate send is **Inferred** |
| Request is not authenticated inside the function | `req` is unused. No JWT read | Implemented in the function. Platform JWT verification is not set in config. See constraints |

| Case | Behaviour in `send-aws-ses-email/index.ts` | Label |
|---|---|---|
| Not POST | 405, no send | Implemented |
| Body is not JSON | 500, `error.message` included | Implemented |
| `to`, `subject`, or `htmlContent` missing | Still sent to SES with those values | Implemented. No 400 branch |
| SES or credential error | 500, `error.message` included | Implemented |
| `to` logged | `console.log("to", to)` | Implemented |

## Integrations

| System | Purpose | Trigger | Direction | Authentication | Failure |
|---|---|---|---|---|---|
| Supabase PostgREST table `send_que` | Queue read and status write | Function invocation | This repo → Supabase | `SUPABASE_SERVICE_ROLE_KEY` in the current and old queue functions | Select error: `sent_que` returns `[]`. Update error: logged, row unchanged |
| Amazon SES SMTP | Deliver queued mail | After a `status` 0 row is loaded | This repo → SES | `SES_SMTP_USERNAME` / `SES_SMTP_PASSWORD` in `index.ts`. Literals in `index_old.ts` | Throw becomes `status` 2 in `index.ts` |
| Amazon SES API | Deliver one HTML mail | POST to `send-aws-ses-email` | This repo → SES | Env `AWS_SES_KEY_ID` / `AWS_SES_SECRET_KEY` | 500 with `error.message` |
| SonarQube | Static analysis | Push to `main` | Pipeline → Sonar host | `SONAR_TOKEN` pipeline variable | Not described in the YAML |
| Nodemailer attachment `path` | Fetch or read the attachment | Truthy `attachment` | This process → that URL or path | None in this code | A throw is the same failure path as a send error |

Expected SES and Supabase success payloads are not asserted by tests. None exist.

## Constraints

1. **No schema in git.** Column types, nullability, indexes, and RLS are **Unknown**. The service-role key is what a Supabase service role uses to bypass RLS. Whether the deployed key is that role is **Unknown / Requires Confirmation**.
2. **No per-function block in either `config.toml`.** There is no `verify_jwt` override. The function source does not check `Authorization`. Whether the hosted gateway requires a JWT is **Unknown / Requires Confirmation**.
3. **`supabase/config.toml` matches the Supabase CLI local template**, with `project_id` set to `intersect_backend`. Compared with `supabase/supabase/config.toml`, the only other difference is `secure_password_change = false`, which the older nested file does not contain. Auth values that are present (signup enabled, email confirmations disabled, anonymous sign-in disabled, JWT expiry 3600, TOTP enrol and verify enabled, Apple and third-party providers disabled, local ports) are template defaults. They are not a written product policy.
4. **Two function roots.** The CLI config at `supabase/config.toml` discovers `supabase/functions/` (`sent_que` only). The nested functions are discovered only if the CLI is run against `supabase/supabase/`. **Inferred** from the directory layout. Which directory anyone deployed is **Unknown**.
5. **Secrets in git.** `index_old.ts` and both `.env` files contain live-looking credentials at HEAD. Root `.gitignore` and `supabase/.gitignore` both name `.env`, and `supabase/.gitignore` also names `.temp` and `.branches`, but those paths are still tracked. Values are omitted. Rotation is required. This PRD does not treat the values as acceptable configuration.
6. **Pipeline image is Maven.** The repository has no Java sources. **Inferred:** the image is the Bitbucket SonarQube sample, left unchanged.
7. **Status 2 is not selected.** Only `status` 0 is read. Nothing in this repo moves a row back to 0. **Inferred:** a failed row is not retried by this function. **Requires Confirmation** that the inserter agrees.
8. **`send-email` does not send email.** The name of the folder is not the behaviour.

## Discrepancies

| Topic | This repository | Other Silicon Ark evidence |
|---|---|---|
| Who drains `send_que` | `sent_que/index.ts` can | `intersect_lambda` documents a Lambda that also can. `intersect_cloud` says the sender is unknown. **Requires Confirmation.** Do not treat both as the live worker |
| Batch and success rule | All pending rows; success means `sendMail` did not throw | Lambda docs: at most 15; success means `accepted.length > 0`. Both are implemented in their own repos. They are not the same rule |
| From address on the queue path | Literal in source | Lambda docs: SSM `/intersectcloud-ses/no-reply-email`. Whether the parameter equals the literal is **Unknown** there |
| `send-email` | Greeting template | The folder name implies sending. Code wins. The name is misleading |
| `sonarqube_scan.bat` | Not in git history | Listed by the onboarding filesystem scan. **Unknown** whether it was only a local untracked file |

## Unknown / Requires Confirmation

- Product owner, and whether "Intersect" and "Simpleflow Hub" are one product.
- Deployed or not. Which Supabase project. Whether project ref `bjlmgdkcktvfshewkfgc` is that project.
- The caller, the schedule, and whether JWT verification is on at the gateway.
- The inserter of `send_que`, the full column list, and the meaning of `status` to that inserter.
- RLS, triggers, and `send_custom_email` (named in the `intersect_cloud` PRD, absent here).
- Whether the committed credentials have been revoked.
- Whether a SonarQube project for this key still exists, and whether the Maven pipeline image can run the scan.