# Technical documentation — intersect_backend

Evidence date: 2026-10-12. Revision: `main` `86347d4` (2024-09-26, Jaco Brits).

Source: `git@bitbucket.org:overdrive/intersect_backend.git`. Secret values that are committed in source are named by file and variable, and the values are omitted.

## Summary

A Deno / Supabase Edge Functions tree with two layouts side by side, a nodemailer dependency, and a Bitbucket Pipeline that only runs SonarQube. There is no README, no migration, no test, and no deploy step.

28 files are tracked.

## Repository layout

```text
.gitignore
.vscode/extensions.json
.vscode/settings.json
bitbucket-pipelines.yml
package.json
package-lock.json
sonar-project.properties
supabase/.gitignore
supabase/.vscode/
supabase/config.toml
supabase/seed.sql                          empty
supabase/functions/sent_que/index.ts        current queue drain
supabase/functions/sent_que/index_old.ts    previous drain, not the entry point
supabase/supabase/.branches/_current_branch
supabase/supabase/.temp/                    CLI metadata, committed
supabase/supabase/config.toml
supabase/supabase/seed.sql                  empty
supabase/supabase/functions/hello-world/index.ts
supabase/supabase/functions/send-email/index.ts
supabase/supabase/functions/send-email/.env
supabase/supabase/functions/send-aws-ses-email/index.ts
supabase/supabase/functions/send-aws-ses-email/.env
```

`sonarqube_scan.bat` was listed in the onboarding filesystem snapshot and is not in `git log --all`.

## Languages and runtime

| Item | Evidence |
|---|---|
| TypeScript on Deno | `Deno.serve`, `Deno.env.get`, URL imports |
| Node package manifest | `package.json` dependencies only. No `scripts`, `name`, `version`, or `engines` |
| Lockfile | `package-lock.json` lockfileVersion 3, `name` `intersect_backend`, `nodemailer` resolved `6.9.15` |
| No `deno.json`, `import_map.json`, `Dockerfile`, or `supabase/migrations` | Absent from `git ls-files` |

Editor support is the Deno VS Code extension (`denoland.vscode-deno`) in both `.vscode/extensions.json` files.

Root `.vscode/settings.json` sets `deno.enablePaths` to `supabase/functions` and turns on a list of `deno.unstable` features (`cron`, `kv`, `ffi`, and others). The function source that was read does not call those APIs. Nested `.vscode/settings.json` sets `deno.unstable` to `true` and `deno.enablePaths` to `supabase/functions`, which from the `supabase/` folder points at `supabase/supabase/functions`.

## Two Supabase trees

Supabase CLI loads `config.toml` from the working directory and functions from `./functions` beside it (or `supabase/functions` when the config lives at `supabase/config.toml`).

| Config | `project_id` | Functions it would see | Added |
|---|---|---|---|
| `supabase/config.toml` | `intersect_backend` | `supabase/functions/sent_que` only | `86347d4` (2024-09-26) |
| `supabase/supabase/config.toml` | `supabase` | `hello-world`, `send-email`, `send-aws-ses-email` | `33d1524` (2024-09-11) |

Diff of the two config files: `project_id`, and `secure_password_change = false` present only on the outer file. Everything else that was compared matches, including ports and auth flags. Those flags are the CLI template (see [Local config](#local-config-template)).

**Inferred:** Dirk initialised the CLI inside `supabase/`, which is why a second `supabase/` directory, `project_id = "supabase"`, and `.temp` files exist. Jaco later initialised (or copied) a config at the conventional `supabase/config.toml` path. Whether either directory was deployed is **Unknown / Requires Confirmation**.

### Linked project metadata

Committed under `supabase/supabase/.temp/` even though `supabase/.gitignore` lists `.temp` and `.branches`.

| File | Value (non-secret) |
|---|---|
| `project-ref` | `bjlmgdkcktvfshewkfgc` |
| `pooler-url` | `postgres://postgres.<project-ref>:[YOUR-PASSWORD]@aws-0-eu-central-1.pooler.supabase.com:6543/postgres` |
| `postgres-version` | `15.1.1.73` |
| `cli-latest` | `v1.192.5` |
| `gotrue-version` | `v2.160.0` |
| `rest-version` | `v12.2.1` |
| `storage-version` | `v1.11.0` |
| `.branches/_current_branch` | `main` |

The password is the CLI placeholder `[YOUR-PASSWORD]`, not a live secret. This metadata predates `sent_que`. It is not a deployment record for that function.

## Local config template

From `supabase/config.toml`. These are local CLI settings, not evidence of the hosted project.

| Area | Setting |
|---|---|
| API | Enabled, port `54321`, schemas `public` and `graphql_public`, `extra_search_path` `public` and `extensions`, `max_rows` 1000, TLS disabled |
| DB | Port `54322`, shadow port `54320`, `major_version` 15 |
| Pooler | Disabled, port `54329`, mode `transaction`, pool size 20, max client connections 100 |
| Realtime | Enabled |
| Studio | Enabled, port `54323`, `api_url` `http://127.0.0.1`, OpenAI key `env(OPENAI_API_KEY)` |
| Inbucket | Enabled, port `54324`. SMTP and POP3 ports are comments |
| Storage | Enabled, `file_size_limit` `50MiB`, image transformation enabled. No bucket blocks are uncommented |
| Auth | Enabled, `site_url` `http://127.0.0.1:3000`, extra redirect `https://127.0.0.1:3000`, `jwt_expiry` 3600, refresh-token rotation on, reuse interval 10, signup on, anonymous sign-in off, manual linking off |
| Auth email | Signup on, double confirm on, confirmations off, `secure_password_change` false, `max_frequency` `1s`. SMTP block is commented (SendGrid sample) |
| Auth SMS / Twilio | SMS section present. Twilio `enabled = false`. Token is `env(SUPABASE_AUTH_SMS_TWILIO_AUTH_TOKEN)` |
| MFA | `max_enrolled_factors` 10. TOTP enrol and verify enabled. Phone MFA block is commented |
| External auth | `[auth.external.apple]` disabled. Firebase, Auth0, and Cognito third-party blocks disabled |
| Edge runtime | Enabled, policy `oneshot`, inspector port `8083` |
| Analytics | Enabled, port `54327`, backend `postgres` |
| Experimental S3 | `s3_host`, `s3_region`, `s3_access_key`, `s3_secret_key` are `env(...)` placeholders. `orioledb_version` is empty |

No `[functions.<name>]` block. No `verify_jwt` key. No cron.

Both `seed.sql` files are the empty blob `e69de29`.

## Edge function: `sent_que`

Entry: `supabase/functions/sent_que/index.ts`.

| Import | Specifier |
|---|---|
| `createClient` | `https://esm.sh/@supabase/supabase-js@2` |
| `nodemailer` | `npm:nodemailer` (no version) |

The root lockfile pins nodemailer `6.9.15` for npm. The Deno `npm:` import does not use that lockfile unless the runtime is pointed at it. The version that would run in the Edge Runtime is **Unknown / Requires Confirmation**.

Environment variables read:

| Variable | If missing |
|---|---|
| `SUPABASE_URL` | `''` (`?? ''`) |
| `SUPABASE_SERVICE_ROLE_KEY` | `''` |
| `SES_SMTP_USERNAME` | `undefined` (no fallback) |
| `SES_SMTP_PASSWORD` | `undefined` |

Handler shape:

1. Build the client and the SMTP transport on every request.
2. `from('send_que').select('*').eq('status', 0)`.
3. On select error, `return []`.
4. Send, then update `status` and `result` by `id`.
5. `return new Response("OK", { headers: { 'Content-Type': 'application/json' } })`.

`index_old.ts` imports `npm:@supabase/supabase-js` (unpinned) and `https://deno.land/x/smtp@v0.7.0/mod.ts`. It contains SMTP credentials as string literals. Do not copy them into tickets, logs, or docs. Prefer reading env vars, which is what `index.ts` already does, and revoke the committed pair.

## Edge function: `send-aws-ses-email`

Path: `supabase/supabase/functions/send-aws-ses-email/index.ts`.

Import: `npm:@aws-sdk/client-ses` (`SESClient`, `SendEmailCommand`). The commented `sift` import is not used.

The client is created at load time from `AWS_SES_KEY_ID`, `AWS_SES_SECRET_KEY`, and `AWS_REGION`. The committed `.env` in the same directory sets all three. `AWS_REGION` there is `eu-west-1`. The key id and secret are non-placeholder and are omitted.

POST-only. JSON fields `to`, `subject`, `htmlContent`. `Source` is `no-reply@simpleflowhub.app`.

## Edge functions: `hello-world` and `send-email`

Both import `jsr:@supabase/functions-js/edge-runtime.d.ts` and serve the Supabase sample that echoes `name`. `send-email/.env` has the same three AWS variable names as the SES function, with non-placeholder values, and this function does not read them.

The sample `curl` in the comment targets `http://127.0.0.1:54321/functions/v1/<function-name>` and includes the stock local demo bearer from the CLI template. That token is not a project secret and is not copied here.

## Data

No DDL is in git. Columns this code uses on `send_que`:

| Column | Read | Written by `index.ts` | Written by `index_old.ts` |
|---|---|---|---|
| `id` | Yes, as the update filter | No | No |
| `status` | Filter `0` | `1` or `2` | `1` or `2` |
| `recipient` | Yes | No | No |
| `subject` | Yes | No | No |
| `body` | Yes | No | No |
| `attachment` | Yes, `index.ts` only | No | No |
| `result` | No | JSON string of `{ success, message }` | No |

`index_old.ts` types the id parameter as `number`. `index.ts` does not annotate it. The database type is **Unknown**.

Silicon Ark `intersect_cloud` documents a generated client row with the same six names (`recipient`, `subject`, `body`, `attachment`, `status`, `result`). That is evidence about the Flutter client, not proof that this function's Supabase project is the one at `https://alb.simpleflowhub.app`.

Row-level security, grants, triggers, and `send_custom_email` are **Unknown** from this repository.

## Amazon SES

| Path | Client | Identity |
|---|---|---|
| `sent_que/index.ts` | nodemailer SMTP, port 587, `secure: false` | From literal `no-reply@simpleflowhub.app`. Auth env `SES_SMTP_*` |
| `sent_que/index_old.ts` | `SmtpClient` STARTTLS, same host and port | Same from address. Auth literals in source |
| `send-aws-ses-email/index.ts` | `@aws-sdk/client-ses` `SendEmailCommand` | Same from address. Auth env `AWS_SES_*`. Region env `AWS_REGION` (`eu-west-1` in the committed `.env`) |

The SMTP host in both queue files is `email-smtp.eu-west-1.amazonaws.com`. The CLI pooler host is `eu-central-1`. Those are different regions. **Unknown / Requires Confirmation** which region the Supabase project and the SES identity actually use in production.

No SES identity, configuration set, or IAM policy is in git.

## Authentication

The functions do not implement login. Local GoTrue settings in `config.toml` are template defaults (email signup on, confirmations off, anonymous off, TOTP on, external providers off). They describe `supabase start` on a developer machine.

`sent_que` uses whatever `SUPABASE_SERVICE_ROLE_KEY` is in the function environment. That variable is not committed in `sent_que/`. It is not in the nested `.env` files either (those files only have the three AWS names).

## Pipeline and analysis

`bitbucket-pipelines.yml`:

| Item | Value |
|---|---|
| Image | `maven:3.3.9` |
| Clone | `depth: full` |
| Branches | `'{main}'` only |
| Step | `SonarQube analysis` |
| Pipe | `sonarsource/sonarqube-scan:2.0.1` |
| Variables | `SONAR_HOST_URL`, `SONAR_TOKEN` (repository or workspace variables; the YAML says not to put the token in the file) |
| Cache | `sonar: ~/.sonar` |

`sonar-project.properties` sets only:

```properties
sonar.projectKey=overdrive_intersect_backend_574dd118-0a6f-46b5-ae71-3d99f677206c
```

No `sonar.sources`, exclusions, or coverage settings. There is no Maven `pom.xml`. **Inferred:** the pipeline file is the Bitbucket "create SonarQube pipeline" sample (Maven image, pipe 2.0.1) with this project's key. Whether that image can execute the pipe for a Deno tree was not run here.

No deploy, lint, or test step.

## Environment and secrets

| Location | Names | In git |
|---|---|---|
| `sent_que/index.ts` | `SUPABASE_URL`, `SUPABASE_SERVICE_ROLE_KEY`, `SES_SMTP_USERNAME`, `SES_SMTP_PASSWORD` | Names only. Values are not in this file |
| `sent_que/index_old.ts` | SMTP username and password as literals. Commented `AWS_SES_KEY_ID2`, `AWS_SES_SECRET_KEY2` | Literals are committed. Omitted here |
| `send-aws-ses-email/.env` | `AWS_SES_KEY_ID`, `AWS_SES_SECRET_KEY`, `AWS_REGION` | Tracked. Values omitted. Region `eu-west-1` |
| `send-email/.env` | Same three names | Tracked. Values omitted. The secret is not the same string as the other `.env` |
| Pipeline | `SONAR_HOST_URL`, `SONAR_TOKEN` | Names only |
| `config.toml` | `env(OPENAI_API_KEY)` and other `env(...)` placeholders | Placeholders, not values |

Root `.gitignore` lists `.env`. `supabase/.gitignore` lists `.branches`, `.temp`, and `.env`. Git still tracks the nested `.env`, `.temp`, and `.branches` files (`git ls-files`).

**Human action:** revoke the AWS and SES credentials that are in those tracked files and in `index_old.ts`. Do not rotate them by committing a new secret.

## Storage

No bucket is created in config (the images bucket block is commented). `sent_que` does not call the Storage API. A row's `attachment` string is handed to nodemailer as `path`.

`file_size_limit = "50MiB"` applies to local Supabase Storage when the CLI template is used. It does not constrain the nodemailer attachment.

## Background work

No queue worker framework, no `pg_cron` file, no Supabase schedule, no Bitbucket schedule. `index_old.ts` comments that the function runs when triggered. The trigger is not defined.

`deno.unstable` includes `cron` in the root editor settings. No function calls `Deno.cron`.

## Security notes from the tree

These are observations, not a pentest.

1. Committed credentials in `index_old.ts` and both `.env` files. See [Environment and secrets](#environment-and-secrets).
2. `sent_que` and `send-aws-ses-email` do not authorize the caller in code. `send-aws-ses-email` will send as `no-reply@simpleflowhub.app` for any POST that reaches it.
3. `send-aws-ses-email` returns `error.message` to the client. `sent_que` does not put the SMTP error in `result` or in the HTTP body.
4. `sent_que` logs the nodemailer response. `send-aws-ses-email` logs the recipient address.
5. Service-role use bypasses RLS if the key is a service role and RLS exists. RLS is not in this repo.
6. No rate limit is implemented in the functions. A `sent_que` call walks every `status` 0 row in one request.
7. The local demo JWT in the template comments is the public Supabase demo anon key. It is not a credential for project ref `bjlmgdkcktvfshewkfgc`.

## Development and testing

No test script, no fixture for a `send_que` row, no fixture for `to` / `subject` / `htmlContent`.

What the comments say a developer can do locally:

1. `supabase start` (comment in the greeting functions).
2. POST `http://127.0.0.1:54321/functions/v1/hello-world` or `.../send-email`.

`sent_que` has no local-invoke comment. Nothing in git documents the env vars that function needs, beyond the `Deno.env.get` calls.

CI does not run tests. PHPUnit, Jest, Deno test, and TestSprite are not configured here.

## Operations that are absent

| Topic | Evidence |
|---|---|
| Monitoring or error tracking | No Sentry, log drain, or health check |
| Infrastructure as code for the hosted project | No Terraform, CloudFormation, or `supabase link` command in CI. Only a committed `.temp/project-ref` |
| Database migrations | No directory |
| Backup | Not described |
| Feature flags | None |

## Related systems (not this repo)

Documented elsewhere in Silicon Ark, and not imported by this code:

| System | Relevant fact | Where documented |
|---|---|---|
| `intersect_lambda` | Lambda drain of `send_que` and a direct SES send, last commit in that docs set 2024-12-13 | `intersect_lambda/` |
| `intersect_cloud` | Flutter client table `send_que` and RPC `send_custom_email`. Sender not in that repo | `intersect_cloud/` |
| `intersect_cloud_prod` | Same gap on the prod client | `intersect_cloud_prod/` |

Differences between `sent_que/index.ts` and the Lambda queue handler are listed in `project-plan.md`. They are separate implementations until someone confirms they share a database.