Control Plane API — Lambda + Aurora Deployment Overview - Runtime: AWS Lambda (Node.js 20) with Fastify adapter. - Data: Aurora PostgreSQL Serverless v2 with RDS Proxy. - Ingress: API Gateway HTTP API; special route for Stripe webhooks to a raw Lambda. Prereqs - AWS account and CLI credentials for the target account. - An S3 bucket for Lambda code artifacts (ex: sip-lambda-artifacts-). Set secret LAMBDA_CODE_BUCKET in GitHub. - Stripe keys (test/prod) for `STRIPE_API_KEY` and `STRIPE_WEBHOOK_SECRET`. Build & Upload Artifacts - CI: Run workflow Deploy Control Plane API (Lambda) in GitHub → provide an S3 key prefix (e.g., `releases/cp-api/v0.1.0`). It builds: - `cp-api.zip` with handler `dist/src/lambda.handler`. - `stripe-webhook.zip` with handler `dist/src/stripe_webhook_lambda.handler`. - Local: `bash scripts/build-control-plane-api.sh` then `aws s3 cp dist-lambda/* s3:////`. Terraform Apply - Variables to provide (safest via TF_VAR_* env): - `project` (e.g., `sip`) - `region` (e.g., `eu-north-1`) - `db_password` (generate a strong secret) - `lambda_code_bucket`, `lambda_cp_api_key`, `lambda_stripe_webhook_key` - `stripe_api_key`, `stripe_webhook_secret` (optional for first apply) - `stripe_creator_price_ids`, `stripe_business_lever_price_ids`, `stripe_business_lever_scope` (seat|org) - Optional API CloudFront: `enable_api_cloudfront=true`, `api_domain_name=api.example.com`, `api_hosted_zone_id=Z...` - Optional S3 SSE-KMS: `s3_sse_mode=KMS`, `s3_kms_key_id=arn:aws:kms:...` - Optional email (Viewer invites): set `EMAIL_PROVIDER=ses` and `EMAIL_FROM=noreply@example.com` in API Lambda env; ensure SES identities are verified. Example apply: - `cd infra/terraform` - `terraform init` - `terraform apply -var project=sip -var region=eu-north-1 \ -var db_password='REDACTED' \ -var lambda_code_bucket='sip-lambda-artifacts' \ -var lambda_cp_api_key='releases/cp-api/v0.1.0/cp-api.zip' \ -var lambda_stripe_webhook_key='releases/cp-api/v0.1.0/stripe-webhook.zip'` Post-deploy verification ------------------------ 1. **Confirm Lambda picked up the new artifact** ```bash aws lambda get-function-configuration \ --function-name sip-cp-api \ --region eu-north-1 \ --query '{LastModified:LastModified,CodeSha256:CodeSha256}' ``` Compare the `CodeSha256` (Base64) with the locally built `cp-api.zip`: ```bash python3 - <<'PY' import base64, hashlib, pathlib data = pathlib.Path("dist-lambda/cp-api.zip").read_bytes() print(base64.b64encode(hashlib.sha256(data).digest()).decode()) PY ``` If they differ, update the function directly: ```bash aws lambda update-function-code \ --function-name sip-cp-api \ --s3-bucket \ --s3-key /cp-api.zip \ --region eu-north-1 ``` 2. **Smoke-test the new routes** ```bash curl -s https://w61xxw9j5b.execute-api.eu-north-1.amazonaws.com/healthz \ -H 'x-org-id:' curl -s -X POST https://w61xxw9j5b.execute-api.eu-north-1.amazonaws.com/actuals/query \ -H 'content-type: application/json' \ -H 'x-org-id:' \ -d '{"entities":[{"entityType":"objective","entityId":"sample"}]}' ``` Receiving `{"items":{...}}` confirms API Gateway is wired to the refreshed Lambda. A `404 Route ... not found` means the function bundle is stale or the gateway stage has not been redeployed. Manual schema hotfix via AWS console ------------------------------------ If `/graph` starts returning errors like `column "start_date" does not exist` immediately after deploying a new Lambda bundle, it means Aurora hasn’t picked up the latest migrations yet. When Terraform or CI access is unavailable, you can patch the schema directly in the AWS console: 1. Go to **RDS → Query Editor** (V2) in the eu-north-1 console. 2. Create/select a Secrets Manager secret for the `sip` database (`username=sip_admin`, password from `${project}/db/credentials`). 3. Connect to the `sip-aurora` cluster via the proxy. 4. Run the missing migration SQL manually. For the Timeline + Cost of Delay release: ```sql ALTER TABLE deliverables ADD COLUMN IF NOT EXISTS start_date timestamptz, ADD COLUMN IF NOT EXISTS end_date timestamptz, ADD COLUMN IF NOT EXISTS cost_of_delay numeric, ADD COLUMN IF NOT EXISTS wsjf_bv numeric, ADD COLUMN IF NOT EXISTS wsjf_tc numeric, ADD COLUMN IF NOT EXISTS wsjf_rroe numeric, ADD COLUMN IF NOT EXISTS wsjf_job_size numeric; ALTER TABLE key_results ADD COLUMN IF NOT EXISTS start_date timestamptz, ADD COLUMN IF NOT EXISTS end_date timestamptz; CREATE INDEX IF NOT EXISTS deliverables_start_date_idx ON deliverables (start_date); CREATE INDEX IF NOT EXISTS deliverables_end_date_idx ON deliverables (end_date); ``` 5. Re-run the `/graph` curl check to confirm the API returns data again. This manual path unblocks production quickly; once Terraform access is restored the same SQL should be codified/replayed via the migrations folder. Outputs - `api_base_url` — base URL for CP API (use in Admin consoles and tests). - `db_proxy_endpoint` — hostname for the Postgres RDS Proxy. Notes - NAT Gateway is provisioned to allow Lambda → Stripe connectivity from private subnets. - RDS Proxy reduces Lambda→Postgres connection churn; keep Pool max small in code. - For production: enable WAF on CloudFront SPA, add CloudWatch alarms, enable Security Hub/GuardDuty, and rotate DB secrets. DB schema init + RDS Proxy readiness 1) Wait for Aurora writer + Proxy target to be healthy - Cluster: - `aws rds describe-db-clusters --region --db-cluster-identifier ${project}-aurora --query 'DBClusters[0].Status'` - Proxy targets: - `aws rds describe-db-proxy-targets --region --db-proxy-name ${project}-pg-proxy --target-group-name default --query 'Targets[].{Host:Endpoint,Status:TargetHealth.State,Reason:TargetHealth.Reason}'` - Proceed when `Status = "AVAILABLE"`. 2) Secret correctness (same account) - Inspect secret JSON (must match the cluster master password): - `aws secretsmanager get-secret-value --region --secret-id ${project}/db/credentials --query 'SecretString' --output text` - If needed, rotate the cluster to match the secret: - `aws rds modify-db-cluster --region --db-cluster-identifier ${project}-aurora --master-user-password "$(aws secretsmanager get-secret-value --region --secret-id ${project}/db/credentials --query 'SecretString' --output text | jq -r '.password')" --apply-immediately` 3) Security groups (proxy→DB) - Ensure the DB security group allows Postgres (5432) from itself (or from a dedicated Proxy SG): - Terraform codifies this via a self-ingress on `${var.project}-db`. 4) Initialize schema - Option A (Lambda): - `aws lambda invoke --region --function-name ${project}-db-init --payload '{}' /dev/stdout` - Option B (psql): - Connect to `db_proxy_endpoint` and run `infra/db/schema.sql`. TLS and auth modes - Terraform sets `require_tls=true` and `PGSSL=true` by default. - RDS Proxy auth defaults to `POSTGRES_MD5` for broad compatibility. To migrate to `POSTGRES_SCRAM_SHA_256`: - In the DB, ensure `password_encryption = 'scram-sha-256'` and rotate the `sip_admin` password (so it is stored as SCRAM). - Then set `client_password_auth_type = "POSTGRES_SCRAM_SHA_256"` on the proxy in Terraform and apply. Encryption / KMS notes (read before apply) - RDS Aurora: Terraform sets `kms_key_id` on the cluster. Changing the KMS key of an existing cluster forces replacement in AWS. Plan first and schedule a maintenance window if replacement is required. - Dry‑run: `AWS_PROFILE=sip-provision terraform plan -target=aws_rds_cluster.aurora` - If the plan shows “forces replacement”, use this safe cut‑over: 1) Snapshot current cluster - `SNAP=sip-aurora-prekms-$(date +%Y%m%d%H%M%S)` - `aws rds create-db-cluster-snapshot --region --db-cluster-identifier sip-aurora --db-cluster-snapshot-identifier $SNAP` 2) Copy snapshot encrypted with the CMK - `aws rds copy-db-cluster-snapshot --region --source-db-cluster-snapshot-identifier $SNAP --kms-key-id $(aws kms describe-key --region --key-id alias/sip-rds --query 'KeyMetadata.Arn' --output text) --target-db-cluster-snapshot-identifier ${SNAP}-cmk` 3) Restore a new cluster from the encrypted snapshot (new identifier) - `aws rds restore-db-cluster-from-snapshot --region --db-cluster-identifier sip-aurora-cmk --snapshot-identifier ${SNAP}-cmk --engine aurora-postgresql` - `aws rds create-db-instance --region --db-cluster-identifier sip-aurora-cmk --db-instance-identifier sip-aurora-cmk-0 --db-instance-class db.serverless --engine aurora-postgresql` 4) Point RDS Proxy to the new cluster and wait HEALTHY - `aws rds register-db-proxy-targets --region --db-proxy-name sip-pg-proxy --target-group-name default --db-cluster-identifiers sip-aurora-cmk` - `aws rds describe-db-proxy-targets --region --db-proxy-name sip-pg-proxy --target-group-name default --query 'Targets[].{Host:Endpoint,Status:TargetHealth.State}'` 5) Adopt the new cluster in Terraform (align state) - `cd infra/terraform` - `terraform state rm aws_rds_cluster.aurora` - `terraform import aws_rds_cluster.aurora arn:aws:rds:::cluster:sip-aurora-cmk` - Then `terraform apply` (will update dependent refs; no replacement) 6) Deregister the old cluster from the proxy and decommission after validation - `aws rds deregister-db-proxy-targets --region --db-proxy-name sip-pg-proxy --target-group-name default --db-cluster-identifiers sip-aurora` - S3 buckets: switched to SSE‑KMS for SPA and audit buckets; audit bucket now logs to the central logs bucket. - SNS alerts topic: encrypted with CMK. API Gateway throttling - Default route throttling tuned to avoid 429s in bootstrap: burst 50, rate 25. WAF - CloudFront WAF and API WAF are optional. To enable the API WAF association, set `enable_api_waf=true` in `terraform.tfvars` and apply. Tune managed rule sets as needed. SSO/SCIM Endpoints (stubs) - `GET /auth/login` → 302 to Cognito Hosted UI (requires `COGNITO_DOMAIN`, `COGNITO_CLIENT_ID`, `COGNITO_REDIRECT_URL`). - `GET /sso/metadata` → Minimal SAML SP metadata (configure `SAML_ENTITY_ID`, `SAML_ACS_URL`). - `GET /sso/jwks` → 302 to `JWT_JWKS_URL` if set. - `GET /scim/v2/ServiceProviderConfig`, `GET /scim/v2/Users`, `POST /scim/v2/Users`, `PATCH /scim/v2/Users/:id`, `DELETE /scim/v2/Users/:id`, `GET /scim/v2/Groups`. IAM for Terraform Applies - The operator profile should have AdministratorAccess, or the following broad capabilities: - ec2:Describe*, ec2:Create*, ec2:AuthorizeSecurityGroupIngress/Egress - rds:Create*, rds:Describe*, rds:Modify*, rds:AddRoleToDB* (cluster/instance) - secretsmanager:Create*, secretsmanager:Put*, secretsmanager:Get* - kms:Create*, kms:Describe*, kms:PutKeyPolicy, kms:ScheduleKeyDeletion, kms:TagResource - iam:CreateRole, iam:AttachRolePolicy, iam:PutRolePolicy, iam:PassRole, iam:GetRole, iam:GetRolePolicy - apigateway:GET/POST/PUT/PATCH on HTTP APIs and stages - logs:CreateLogGroup, logs:Describe*, logs:PutRetentionPolicy - s3:CreateBucket, s3:Get*/Put*/List* on project buckets - cloudfront:CreateDistribution, cloudfront:UpdateDistribution, cloudfront:GetDistribution* - cognito-idp:Create*, Get*, Describe*, Update* - sns:CreateTopic, GetTopicAttributes, Subscribe If using a restricted role, grant missing permissions incrementally as plan/apply reveals gaps.