# AWS account migration — `lambda/datafy_new`

Planning document for moving this stack to a new AWS account with a new Supabase project and S3-backed file/image storage. **No code changes are included here** — this file inventories what must change and which functions are affected.

---

## Summary

| Area | Current pattern | Migration action |
|------|-----------------|------------------|
| Supabase connection | SSM `/supabase/url`, `/supabase/anon` (+ `/supabase/service_role` for weekly report) | New SSM params in new account |
| Upload / report files | Supabase Storage (`public/…`, `Reports`, `uploads/backups`) | **Code change required** if files move to S3 — use `boto3` S3 SDK |
| Task batch staging | S3 `CreateTasksStagingBucket` (SAM-managed) | Redeploy creates new bucket; env vars wired via `!Ref` |
| Lambda invoke chains | Mix of env ARNs (good) and **hardcoded function names** (bad) | Fix hardcoded names; redeploy updates env-based ARNs automatically |
| IAM / ARNs | Account `587594388832` throughout `template.yaml` | New account ID + prefer `!GetAtt` over literal ARNs |
| VPC | Same defaults as bedrock stack | New VPC/subnet parameters at deploy |
| Gmail notifications | SSM `/gmail/*` | Recreate in new account if email alerts continue |

---

## SSM Parameter Store (new account)

| Parameter | Used by |
|-----------|---------|
| `/supabase/url` | Nearly all Lambdas |
| `/supabase/anon` | Nearly all Lambdas |
| `/supabase/service_role` | `CreateCombinedWeeklyReport` only (RPC with elevated access) |
| `/gmail/username` | Functions with `email_service` |
| `/gmail/app_password` | Same |
| `/gmail/to_emails` | Same |
| `/gmail/to_emails_client` | Same |

---

## SAM / infrastructure (`template.yaml`)

### Must update

1. **AWS account ID** — all `arn:aws:ssm:eu-west-1:587594388832:parameter/...` entries.
2. **Hardcoded Lambda ARNs** (should become `!GetAtt` — today they break on every redeploy/account move):
   - `ReadFiles` → `arn:aws:lambda:...:function:datafyNew-BatchUpdateSjreport-utMCtdWkdNo8`
   - `CreateTasks` → `arn:aws:lambda:...:function:datafyNew-StartProcessQueue-r6W4pr83cx2y`
3. **VPC parameters** — `VpcId`, `PrivateSubnet1Id`, `PrivateSubnet2Id`.
4. **`CreateTasksStagingBucket`** — new physical bucket per stack deploy; lifecycle rule on `batches/` unchanged.
5. **API Gateway** (`ReadFilesApi`) — new base URL for:
   - `POST /trigger-read-files`
   - `POST /trigger-read-promos-kaching`
   - `POST /generate-combined-week-report`
6. **Step Functions** — `CreateTasksWorkflow` ARN passed via env to `CreateTasksBulk`.
7. **EventBridge schedules** — combined weekly report + Kaching daily report crons recreated on deploy.

### Env vars set correctly by SAM (no code change if redeployed same stack name pattern)

| Lambda | Env var | Source |
|--------|---------|--------|
| `CreateTasksBulk` | `CREATE_TASKS_STAGING_BUCKET`, `CREATE_TASKS_STATE_MACHINE_ARN`, `CREATE_TASKS_FINALIZE_ARN` | `!Ref` / `!GetAtt` |
| `CreateTasksListBatches` | `CREATE_TASKS_STAGING_BUCKET` | `!Ref` |
| `CreateTasksFinalize` | `START_PROCESS_QUEUE_ARN` | `!GetAtt StartProcessQueue` |
| `BatchUpdateSjreport` | `CREATE_TASKS_BULK_ARN` | `!GetAtt CreateTasksBulk` |
| `StartProcessQueue` | `BACKUP_SJREPORT_ARN` | `!GetAtt BackupSjreport` |
| `BackupSjreport` | `CLEAR_SJREPORT_OLD_DATA_ARN` | `!GetAtt SJReportClearOldData` |
| `SJReportClearOldData` | `BACKUP_TASKS_ARN` | `!GetAtt BackupTasks` |
| `BackupTasks` | `CLEAR_TASKS_OLD_DATA_ARN` | `!GetAtt TasksClearOldData` |
| `TriggerReadFiles` | `READ_FILES_ARN` | `!GetAtt ReadFiles` |
| `TriggerReadPromosKaching` | `READ_PROMOS_KACHING_ARN` | `!GetAtt ReadPromosKaching` |

---

## Lambda functions — full inventory

### A. Supabase Storage read/write — **update for S3 migration**

These use `supabase.storage.from_(...).download()`, `.upload()`, `.remove()`, or `.get_public_url()`.

| SAM resource | Code path | Storage buckets / pattern | Migration notes |
|--------------|-----------|---------------------------|-----------------|
| **ReadFiles** | `read_files/app.py` | `public/{bucket_name}` from `sjreport_uploads.payload` | Download Excel uploads; delete after processing. **S3 SDK** for reads/deletes if uploads land in S3. |
| **ReadPromosKaching** | `read_promo_kaching/app.py` | `public/{bucket_name}` from `promo_kaching_uploads.payload` | Same pattern as ReadFiles. |
| **CreateCombinedWeeklyReport** | `create_combined_weekly_report/app.py` | `Reports` bucket — upload Excel, `get_public_url()` | Needs `service_role` key; report links in emails depend on public URL shape. |
| **CreateKachingDailyReport** | `create_kaching_daily_report/app.py` | `Reports` bucket — upload + public URL | Same as weekly report. |
| **BackupTasks** | `backup_tasks/app.py` | `uploads/backups` — CSV upload | Backup files; move to S3 bucket + SDK. |
| **BackupSjreport** | `backup_sjreport/app.py` | `uploads/backups` — CSV upload | Same as BackupTasks. |

**Legacy / unused in template (still in repo):**

| File | Notes |
|------|-------|
| `sj_read_files/app.py` | Older SJ report reader; hardcoded Lambda name `datafyNew-CreateTasks-ccMR48YAjXLP` — not in current `template.yaml` |
| `read_files/app copy.py` | Copy; ignore unless reactivated |

---

### B. Supabase database / RPC only — **SSM URL/key update sufficient**

No storage SDK changes unless schema or RPC signatures change in new Supabase.

| SAM resource | Code path | Supabase usage |
|--------------|-----------|----------------|
| **StartProcessQueue** | `start_process_queue/app.py` | RPCs: `update_task_customer_ids`, `archive_old_tasks`, `populate_store_process_queue`; table `store_processing_queue` |
| **CreateTasks** | `create_tasks/app.py` | `sjreport` read, `task` upsert, `update_store_issue_counts` RPC |
| **CreateTasksBulk** | `create_tasks_bulk/app.py` | Same data path as CreateTasks; writes JSON batches to **S3** (already SDK) |
| **CreateTasksListBatches** | `create_tasks_list_batches/app.py` | S3 `ListBucket` only |
| **InsertTasksBatch** | `insert_tasks_batch/app.py` | S3 `GetObject` + Supabase `task` upsert |
| **CreateTasksFinalize** | `create_tasks_finalize/app.py` | `update_store_issue_counts` RPC; invokes StartProcessQueue |
| **BatchUpdateSjreport** | `batch_update_sjreport/app.py` | `sjreport` batch update; invokes CreateTasksBulk |
| **UpdateStoreIssues** | `update_store_issues/app.py` | Store issue RPCs / tables |
| **UpdateTaskIssues** | `update_task_issues/app.py` | `update_task_status_with_temp` RPC |
| **TasksClearOldData** | `task_clear_old_data/app.py` | Deletes old task rows |
| **SJReportClearOldData** | `sjreport_clear_old_data/app.py` | Deletes old sjreport rows |
| **TriggerReadFiles** | `trigger_read_files/app.py` | Invokes ReadFiles only |
| **TriggerReadPromosKaching** | `trigger_read_promos_kaching/app.py` | Invokes ReadPromosKaching only |

---

### C. Hardcoded Lambda function names — **must fix in code**

These bypass env vars and will fail in a new account/stack:

| File | Hardcoded `FunctionName` | Should use |
|------|--------------------------|------------|
| `read_files/app.py` | `datafyNew-BatchUpdateSjreport-utMCtdWkdNo8` | Env var e.g. `BATCH_UPDATE_SJREPORT_ARN` (add to template + `ReadFiles`) |
| `create_tasks/app.py` | `datafyNew-StartProcessQueue-r6W4pr83cx2y` | `START_PROCESS_QUEUE_ARN` (same pattern as CreateTasksFinalize) |
| `sj_read_files/app.py` | `datafyNew-CreateTasks-ccMR48YAjXLP` | Env var if this function is brought back |

`template.yaml` duplicates the first two ARNs in IAM policies — align policies with `!GetAtt` when fixing.

---

## S3 usage today (already on SDK)

| Function | Operations | Bucket |
|----------|------------|--------|
| `CreateTasksBulk` | `put_object` | `CREATE_TASKS_STAGING_BUCKET` |
| `CreateTasksListBatches` | `list_objects_v2` | Same |
| `InsertTasksBatch` | `get_object` | Same |

**Migration:** No logic change; new stack → new bucket name in env. Ensure IAM in template follows `!Sub` on bucket ref (already does).

---

## Supabase Storage → S3 SDK — recommended approach

### Upload flows (ReadFiles, ReadPromosKaching)

Today: client uploads to Supabase Storage → DB row in `sjreport_uploads` / `promo_kaching_uploads` with `payload.bucket_name`, `payload.file_name`.

After migration options:

1. **S3 event → Lambda** — payload contains `s3_bucket`, `s3_key` instead of Supabase storage paths.
2. **Dual-read transition** — if `payload` has `s3_key`, use SDK; else fall back to Supabase Storage API.

### Report flows (weekly + Kaching daily)

Today: build Excel in memory → `supabase.storage.from_('Reports').upload()` → `get_public_url()` → email link.

After migration:

- Upload via `s3.put_object` to a reports bucket.
- Public link via CloudFront, presigned URL, or stable `https://{cdn}/{key}` stored in `report_files` table.
- Update `email_service` templates if URL format changes.

### Backup flows

Today: `uploads/backups` Supabase bucket.

After migration: dedicated S3 prefix e.g. `s3://{datafy-backups}/tasks/` and `.../sjreport/`.

### IAM additions (template)

For each function that gains S3 reads/writes on **data** buckets (not just staging):

- `s3:GetObject`, `s3:PutObject`, `s3:DeleteObject` as needed
- Scope to new bucket ARNs via SAM parameters (e.g. `UploadsBucketName`, `ReportsBucketName`)

---

## Supabase tables & RPCs referenced (verify on new project)

| Name | Used by |
|------|---------|
| `sjreport_uploads` | ReadFiles |
| `promo_kaching_uploads` | ReadPromosKaching |
| `sjreport` | ReadFiles, CreateTasks, BatchUpdateSjreport |
| `task` | CreateTasks*, InsertTasksBatch |
| `report_files` | Combined weekly + Kaching reports |
| Promo dynamic tables | ReadPromosKaching (`payload.path_tokens[0]`) |
| `store_processing_queue` | StartProcessQueue |
| RPC `update_task_customer_ids` | StartProcessQueue |
| RPC `archive_old_tasks` | StartProcessQueue |
| RPC `populate_store_process_queue` | StartProcessQueue |
| RPC `update_store_issue_counts` | CreateTasks, CreateTasksFinalize |
| RPC `update_task_status_with_temp` | UpdateTaskIssues |
| RPC `get_old_task_rows_paged` | BackupTasks |
| RPC `raw_sql` (statement_timeout) | ReadFiles, BackupTasks |

---

## Shared layer & email

| Asset | Migration |
|-------|-----------|
| `lambda_layer/` (`supabase`, `pandas`, etc.) | Rebuild layer in new account on deploy |
| `*/email_service.py` | SSM `/gmail/*` only — no Supabase URL in module |
| `lambda_layer/python/email_service.py` | Duplicate of per-function copies |

---

## Files to touch (when implementing migration)

| Priority | File(s) | Reason |
|----------|---------|--------|
| High | `template.yaml` | Account ARNs, `!GetAtt` for Lambda policies, S3 bucket params, new IAM for image/data buckets |
| High | `read_files/app.py` | Storage SDK + hardcoded BatchUpdate ARN |
| High | `create_tasks/app.py` | Hardcoded StartProcessQueue ARN |
| High | `read_promo_kaching/app.py` | Storage SDK |
| High | `create_combined_weekly_report/app.py` | Reports storage + public URLs |
| High | `create_kaching_daily_report/app.py` | Reports storage + public URLs |
| Medium | `backup_tasks/app.py`, `backup_sjreport/app.py` | Backup uploads to S3 |
| Medium | `insert_tasks_batch/app.py` | Only if staging bucket naming changes (unlikely) |
| Low | `sj_read_files/app.py` | Only if re-enabled |
| Docs | `README.md`, `CREATE_TASKS_CONTEXT.md` | New endpoints and env vars |

---

## Suggested migration order

1. Provision new Supabase (schema + RPCs + storage buckets or S3-only design).
2. Create SSM parameters in new AWS account.
3. Deploy SAM stack with new VPC; confirm `CreateTasksStagingBucket` and Step Function ARNs.
4. Fix hardcoded Lambda names; redeploy.
5. Implement S3 SDK paths for uploads/reports/backups; migrate existing objects.
6. Point API Gateway triggers (and Supabase webhooks if any) to new URLs.
7. Run end-to-end: upload SJ report → ReadFiles → CreateTasksBulk workflow → StartProcessQueue chain.
8. Validate scheduled reports (combined weekly, Kaching daily).

---

## Cross-stack note (`lambda/bedrock_analysis`)

Shelf/product images analyzed by Bedrock are loaded from paths/URLs stored in Supabase (`product_images`, `product`). If this stack moves uploads to S3, coordinate DB column format and IAM with the bedrock_analysis migration doc in `lambda/bedrock_analysis/migration.md`.

---

*Generated for infrastructure migration planning. Application code unchanged in this commit.*