┌───────────────────────────────────────────────────────────────────────    ┐
  │                                                                           │
  │   ██╗  ██╗███████╗██████╗ ███████╗██████╗ ██╗  ██╗███████╗██╗     ██████╗ │
  │   ██║  ██║██╔════╝██╔══██╗██╔════╝╚════██╗██║  ██║██╔════╝██║     ██╔══██╗
  │   ███████║█████╗  ██████╔╝█████╗   █████╔╝███████║█████╗  ██║     ██████╔╝
  │   ██╔══██║██╔══╝  ██╔══██╗██╔══╝  ██╔═══╝ ██╔══██║██╔══╝  ██║     ██╔═══╝ │
  │   ██║  ██║███████╗██║  ██║███████╗███████╗██║  ██║███████╗███████╗██║     │
  │   ╚═╝  ╚═╝╚══════╝╚═╝  ╚═╝╚══════╝╚══════╝╚═╝  ╚═╝╚══════╝╚══════╝╚═╝     │
  │                                                                           │
  │          E · T · L   ·   L A M B D A                                      │
  └───────────────────────────────────────────────────────────────────────┘

An AWS Lambda that reads full names from a Google Sheet, matches them against the here2help Postgres residents table (read-only), and writes the matched resident records back into the same spreadsheet.

_{TypeScript · Node.js 24 · esbuild · pg · googleapis · Terraform · AWS Secrets Manager}

---

Contents

• What it does
• Data flow
• Sheet input
• Sheet output
• Configuration
• Secrets Manager vs. .env
• Local development
• Local Docker database
• Build · Invoke · Package
• Deployment (Terraform)
• CI/CD
• Project layout
• Gotchas

---

What it does

The Lambda reads full names from the names tab of a designated Google Sheet, finds matching rows in the residents table of the here2help Postgres database (using the read-only user), and replaces the tab named by GOOGLE_SHEET_TAB with those matches.

The export is a header row followed by these columns, ordered by first_name then last_name:

id · first_name · last_name · dob_day · dob_month · dob_year ·
contact_mobile_number · contact_telephone_number · email_address · uprn

Matching is case-insensitive on both first and last name.

---

Data flow

        ┌──────────────────────┐
        │   Google Sheet       │
        │   tab: "names"       │      1. read full names
        │   ┌────────────┐     │  ───────────────────────────┐
        │   │ names      │     │                             │
        │   │ Jane Smith │     │                             ▼
        │   │ John Doe   │     │                    ┌──────────────────┐
        │   └────────────┘     │                    │   ETL  Lambda    │
        └──────────────────────┘                    │  (index.handler) │
                    ▲                                └──────────────────┘
                    │                                   │           │
   4. clear + write │                    2. SELECT      │           │ (cold start)
      matched rows  │                    matching rows  ▼           ▼
        ┌───────────┴──────────┐             ┌────────────────┐  ┌──────────────────┐
        │   Google Sheet       │             │   Postgres     │  │  Secrets Manager │
        │   tab: GOOGLE_SHEET_ │◀── 3. rows ─│   here2help    │  │  (AWS only)      │
        │        TAB (Export)  │             │   residents    │  │  db pwd + google │
        │   id, first_name…    │             │  (read-only)   │  │  creds           │
        └──────────────────────┘             └────────────────┘  └──────────────────┘

1. Read the names tab, parse First Last values, dedupe.
2. SELECT matching residents via a single parameterized unnest join (case-insensitive).
3. Rows returned, ordered by name.
4. Clear the target tab (A:Z) and write the header + matched rows from A1.

Secrets Manager is only consulted on AWS (when H2H_SECRET_ID is set), once per warm container.

---

Sheet input

The Google Sheet must include a tab named names whose first row contains a names header (matched case-insensitively).

Values below that header should be a first name and surname separated by whitespace, e.g. Jane Smith. Rows that don't have exactly two whitespace- separated parts are skipped, so incomplete rows never block the export. Duplicate names are collapsed to one lookup.

If the names header is missing, the Lambda throws Missing names sheet header: expected "names".

---

Sheet output

The Lambda clears and replaces the tab named by GOOGLE_SHEET_TAB. If that tab does not exist, it is created. The output contains only these columns:

id
first_name
last_name
dob_day
dob_month
dob_year
contact_mobile_number
contact_telephone_number
email_address
uprn

On success the handler returns:

{
  "ok": true,
  "exportedTable": "residents",
  "rowCount": 12,
  "sheetId": "…",
  "sheetTab": "Export"
}

---

Configuration

Every variable below is required — the Lambda throws Missing required config value: <name> at startup if one is missing or blank.

Variable	Example	Notes
H2H_DB_HOST	localhost	RDS hostname on AWS
H2H_DB_PORT	5432
H2H_DB_DATABASE	here2help
H2H_DB_USER	here_readonly	read-only user
H2H_DB_PASSWORD	help_readonly	🔒 can come from Secrets Manager
GOOGLE_SHEET_ID	12dkE0…jxGSg	spreadsheet ID
GOOGLE_SHEET_TAB	Export	tab to clear + replace
GOOGLE_SERVICE_ACCOUNT_EMAIL	…@…iam.gserviceaccount.com	🔒 can come from Secrets Manager
GOOGLE_PRIVATE_KEY	-----BEGIN PRIVATE KEY-----\n…	🔒 can come from Secrets Manager
H2H_SECRET_ID	(unset locally)	switch → Secrets Manager on AWS

Share the target Google Sheet with GOOGLE_SERVICE_ACCOUNT_EMAIL before invoking the Lambda, or the Sheets API calls will 403.

GOOGLE_PRIVATE_KEY may contain literal \n sequences — the Lambda decodes them to real newlines at load time.

---

Secrets Manager vs. .env

The three sensitive values (H2H_DB_PASSWORD, GOOGLE_SERVICE_ACCOUNT_EMAIL, GOOGLE_PRIVATE_KEY) can come from AWS Secrets Manager instead of the environment. The optional H2H_SECRET_ID variable is the switch:

• Local dev: leave H2H_SECRET_ID unset — every value is read from .env.
• AWS: a JSON secret named here2help-etl-secrets (i.e. <function_name>-secrets) is created and maintained manually in Secrets Manager. Terraform looks it up with data.aws_secretsmanager_secret.etl, grants the Lambda role secretsmanager:GetSecretValue on it, and sets H2H_SECRET_ID to its ARN. At cold start the Lambda fetches the secret (cached across warm invocations); the remaining, non-sensitive values stay as plain Lambda environment variables.

The secret's SecretString must be a single JSON object:

{
  "H2H_DB_PASSWORD": "your-readonly-db-password",
  "GOOGLE_SERVICE_ACCOUNT_EMAIL": "your-service-account@your-project.iam.gserviceaccount.com",
  "GOOGLE_PRIVATE_KEY": "-----BEGIN PRIVATE KEY-----\nMIIE...\n-----END PRIVATE KEY-----\n"
}

Values from the secret take precedence over any matching environment variable.

---

Local development

Create .env from .env.example (or run npm run setup:env):

npm install
npm run setup:env      # copies .env.example → .env (won't clobber an existing .env)
# then fill in the Google credentials in .env

H2H_DB_HOST=localhost
H2H_DB_PORT=5432
H2H_DB_DATABASE=here2help
H2H_DB_USER=here_readonly
H2H_DB_PASSWORD=help_readonly

GOOGLE_SHEET_ID=your_google_sheet_id
GOOGLE_SHEET_TAB=Export
GOOGLE_SERVICE_ACCOUNT_EMAIL=your-service-account@your-project.iam.gserviceaccount.com
GOOGLE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\nreplace_me\n-----END PRIVATE KEY-----\n"

---

Local Docker database

The local Docker Postgres must include a production-shaped residents table, and the read-only user must have SELECT on it. The current local DB was updated directly in the running here2help-postgres container.

The full production residents shape (the Lambda exports only the subset in Sheet output):

id · first_name · last_name · dob_day · dob_month · dob_year ·
contact_mobile_number · contact_telephone_number · email_address ·
address_first_line · address_second_line · address_third_line · postcode ·
uprn · ward · is_pharmacist_able_to_deliver · name_address_pharmacist ·
gp_surgery_details · number_of_children_under_18 · consent_to_share ·
record_status · nhs_number

The local table has 16 dummy residents seeded with IDs 990001–990016:

Alice Anderson   Ben Bennett     Clara Cole      Daniel Davis
Eva Edwards      Farah Foster    George Green    Hannah Hill
Isaac Irving     Jasmine Jones   Kieran King     Lina Lewis
Maya Moore       Noah Nelson     Olivia Owens    Priya Patel

---

Build · Invoke · Package

npm run typecheck      # tsc --noEmit
npm test               # node --test (no test framework; Node 24 runs the .ts directly)
npm run build          # esbuild → dist/index.js (bundled, CJS, node24)

npm run invoke:local   # builds, then runs handler() against .env
                       # requires the local Postgres container running
                       # + valid Google credentials in .env
                       # writes to the sheet: clears & replaces GOOGLE_SHEET_TAB

npm run package        # builds, then zips dist/index.js → lambda.zip

Lambda handler entry point: index.handler.

---

Deployment (Terraform)

Infrastructure lives in terraform/ and wraps Hackney's shared ce-aws-lambda-lbh module (pinned to v1.4.0).

Note — this is a public copy. The module source above is a private repo and the values in tfvars/ and backend/ are placeholders, so terraform init will not resolve here; the config is included to document the architecture.

It provisions:

• the Lambda (from lambda.zip), VPC-attached, 256 MB, 60 s timeout;
• a security group for the Lambda, with all egress (RDS on 5432, and the Google API via the shared transit gateway);
• a self-referencing ingress rule on 5432 in that same security group — the RDS instance is attached to it too (see below);
• an IAM policy granting secretsmanager:GetSecretValue on the manually-managed secret, wired in via the module's additional_policies.

The RDS instance ships with only the VPC's default security group, which an org-level SCP (DenySGRulesOnDefaultSG) forbids anyone in the account from adding rules to. So the DB is attached to the Lambda's security group instead — a one-off manual step, since the instance is not managed by any Terraform state:

aws rds modify-db-instance \
  --db-instance-identifier here2help \
  --vpc-security-group-ids <here2help-etl-sg id> \
  --region eu-west-2 --apply-immediately

Dropping the default SG loses nothing (it has no rules), and an RDS security group change applies without a restart.

State is stored in S3 (example-terraform-state) with DynamoDB locking, region eu-west-2.

cd terraform
terraform init  -backend-config=backend/config.prod.tfbackend
terraform plan  -var-file=tfvars/prod.tfvars
terraform apply -var-file=tfvars/prod.tfvars

The secret (<function_name>-secrets) must exist in Secrets Manager before apply — Terraform reads it, it does not create it.

---

CI/CD

GitHub Actions in .github/workflows/:

Workflow	Trigger	Does
build_lambdas.yaml	reusable (workflow_call)	npm ci → typecheck + test → npm run package → upload lambda.zip
sandbox_plan.yml	manual (workflow_dispatch)	Build, then terraform plan (prod)
deploy.yml	manual (workflow_dispatch)	Build, then terraform apply (prod)

Both terraform workflows call a reusable workflow that lives in a private repo and assume a deployment role in the internal AWS account, so neither can run from this copy — they are manual-only here, and kept for reference.

---

Project layout

.
├── index.ts                      # the Lambda: config → read sheet → query DB → write sheet
├── index.test.ts                 # node:test coverage of the pure logic (names in, sheet values out)
├── package.json                  # scripts: build / typecheck / setup:env / invoke:local / package
├── tsconfig.json
├── .env.example                  # template for local config
├── dist/index.js                 # esbuild output (bundled handler)
├── terraform/
│   ├── main.tf                   # provider, networking, Secrets Manager lookup + IAM
│   ├── lambda.tf                 # the shared Hackney Lambda module + env wiring
│   ├── variables.tf
│   ├── tfvars/prod.tfvars
│   └── backend/config.prod.tfbackend
└── .github/workflows/            # build + plan + (disabled) deploy

---

Gotchas

• No TLS to the database. Deploy the Lambda into the same VPC subnet as the DB, and on AWS RDS make sure the parameter group does not enforce SSL (rds.force_ssl=0).
• Share the sheet with the service account email, or every Sheets call 403s.
• The secret is not managed by Terraform — create/rotate it by hand in Secrets Manager.
• invoke:local writes to the real sheet it points at (clears + replaces the tab). Point GOOGLE_SHEET_ID/GOOGLE_SHEET_TAB at a scratch sheet while testing.