feat(`connector`): export Glow learning data to the NLDS warehouse S3 landing bucket

[nicholasjjlim]

User story

As an NLDS Product Ops analyst, I want Glow's learning data to land automatically in the NLDS S3 landing bucket on a daily batch cadence, so that it can be ingested into the NLDS data warehouse for centralized analytics and reporting without manual data pulls.

Background

NLDS wants Glow's learning data centralized in its warehouse, but Glow has no automated pipeline today: data is moved by hand or not at all.

ADR 0005 (accepted; spike in #597, documented in PR #602) settled the connector architecture: an S3 data-lake intermediary with RDS snapshot export as the producer. Glow's database exports whole-database snapshots as Parquet (no export code) cross-account into a scoped prefix of a landing bucket owned by the NLDS account (S3 Object Ownership: bucket-owner-enforced, SSE-KMS). NLDS ingests from there natively, on its own schedule. There is no requirement to curate what is sent.

This issue realizes that decision on the Glow side: the configured snapshot export, the cross-account write path, and monitoring of export success (a producer responsibility per the ADR). The NLDS-side bucket and ingestion are owned by NLDS; this issue covers agreeing the bucket/prefix/key contract with them, not building their side.

Acceptance criteria

Learning data lands in the NLDS bucket on schedule

• Given the NLDS landing bucket exists with Glow granted scoped write access to its dedicated prefix
• When the export runs on its daily snapshot schedule
• Then a complete whole-database export in Parquet format is present under Glow's prefix, owned by the NLDS account and encrypted at rest, and NLDS can read it without a cross-account role

Failed export is surfaced to Glow operators

• Given the connector is in place
• When an export run fails or does not complete
• Then Glow operators are alerted through the team's alerting channel, and the live application is unaffected

Live database stays unreachable from NLDS

• Given the connector is in place
• When NLDS consumes Glow data
• Then the only access path is the S3 landing bucket: no network path, database endpoint, or replica is reachable from the NLDS account

Out of scope

• NLDS-side ingestion, warehouse schema and table mapping (decided separately by NLDS)
• Historical backfill
• Curating the export to specific datasets (whole-DB export per the ADR; re-explorable later without superseding it)
• Near-real-time freshness (documented upgrade path is PrivateLink, per the ADR)
• An application-level extract API (set aside by the ADR; see API Endpoints for Training Records Data Extraction #522)

Design assets

flowchart LR
    subgraph GlowAcct["Glow AWS account"]
        Glow["Glow database<br/>(RDS snapshot export)"]
    end
    subgraph NldsAcct["NLDS AWS account (same Organization)"]
        Bucket["S3 landing bucket<br/>(Parquet, SSE-KMS,<br/>bucket-owner-enforced)"]
        Ingest["NLDS ingestion"]
        Warehouse["NLDS data warehouse"]
        Bucket --> Ingest
        Ingest --> Warehouse
    end
    Glow -->|"batch export (per snapshot schedule):<br/>cross-account write to a scoped prefix"| Bucket

Loading

---

Technical context

Implementation lives in the infra repo, not this application repo (no changes to src/ or prisma/ in onward): https://sgts.gitlab-dedicated.com/wog/moe/dxdtransform/dxd-transform/dxd-transform-infrastructure (Terraform + Terragrunt; Terragrunt states under infra/states/provider.aws/, reusable modules under infra/modules/, Lambda source under lambda/). Onward's per-environment stacks live under acct.lower/env.dev/svc.onward/, acct.stg/env.stg/svc.onward/, and acct.prd/env.prd/svc.onward/; the RDS instances (transform-{env}-onward-db, automated daily snapshots with 7-day retention) and KMS stacks already exist in all three.

RDS does not export snapshots automatically, so the producer needs a small scheduler. The end product lives in production: the standing daily export runs in the production account against transform-prd-onward-db. Dev is used only to test the pipeline end to end before promoting; it is not a standing data feed. Build:

1. IAM export role (new stack svc.onward/iam/rds-s3-export/): trusts the export.rds.amazonaws.com service principal; grants S3 write scoped to the agreed prefix on the NLDS landing bucket and the KMS actions the export task needs (Encrypt, Decrypt, ReEncrypt, GenerateDataKey, CreateGrant, DescribeKey, RetireGrant) on the NLDS-provided key. Use the repo's infra/modules/aws/iam/assumable-role module.
2. Export scheduler Lambda (new stack svc.onward/lambda/nlds-export/, source lambda/onward-nlds-export/): Python, no VPC, control-plane only. Looks up the latest automated snapshot of the environment's onward DB and calls rds:StartExportTask targeting the NLDS bucket with the export role and KMS key, landing each run under a Hive-style date-partitioned prefix (glow/year=YYYY/month=MM/day=DD/). Needs rds:DescribeDBSnapshots, rds:StartExportTask, rds:DescribeExportTasks, iam:PassRole (on the export role), kms:DescribeKey.
3. EventBridge daily rule (new stack svc.onward/eventbridge/nlds-export/): triggers the Lambda daily at 02:30 SGT (cron(30 18 * * ? *), UTC), after the RDS backup window (17:00-18:00 UTC / 01:00-02:00 SGT) so the Lambda exports the same night's snapshot.
4. CloudWatch alarms (added to the environment's cloudwatch/metric-alarms/ stack, module infra/modules/aws/cloudwatch/metric-alarms): see Error contract. Notify the account-level yellow/red alert SNS topics that stack already uses.
5. NLDS handshake: give NLDS the export role ARN; NLDS must grant that role and the export.rds.amazonaws.com service principal in their bucket policy and KMS key policy, and provide the KMS key ARN back. Working values to confirm with NLDS Product Ops: bucket glow-nlds-data-lake-prod (account 671830948389, ap-southeast-1), prefix glow.
6. End-to-end verification in dev before promoting to production: a scheduled run lands a complete Parquet export under the prefix and an NLDS principal can read it. Once production is live, the dev deployment serves testing only (for example, leave its schedule disabled outside test runs).

Deployment conventions: dev applies locally via aws-vault exec dxd-transform-lower-dev-role -- terragrunt plan|apply; production applies only through Atlantis on a GitLab MR (atlantis plan / atlantis apply comments, approval required). Commit .terraform.lock.hcl with darwin_arm64, darwin_amd64, and linux_amd64 hashes. Angular-style commits scoped like chore(dev/onward): ....

Patterns to follow: the per-service stack layout under svc.onward/ (existing rds/ and kms/ stacks); infra/states/provider.aws/acct.lower/env.dev/svc.onward/s3/app/terragrunt.hcl for S3/bucket-policy style; infra/states/provider.aws/acct.lower/env.dev/cloudwatch/metric-alarms/terragrunt.hcl for alarms (onward's DB is already listed there); terraform-aws-modules/lambda/aws and terraform-aws-modules/eventbridge/aws for the new stacks, matching versions used elsewhere in the repo. Architecture rationale and rejected alternatives are in ADR 0005; the data-flow diagram above is normative.

Data model

N/A: no application schema or Prisma changes. The export is a whole-database snapshot produced by RDS; its shape is the existing database schema serialized to Parquet by AWS under the agreed prefix.

API contract

N/A: no application API is added or changed. The interface between Glow and NLDS is the S3 landing prefix, date-partitioned Hive-style with one dated prefix per daily run, for example s3://glow-nlds-data-lake-prod/glow/year=2026/month=07/day=02/<table>/*.parquet (working values above; confirm with NLDS Product Ops).

Error contract

Operational (no HTTP surface). Alarms follow the repo's metric-alarms pattern and notify the account-level yellow/red alert SNS topics:

Failure	Detection	Operator-visible outcome
Lambda fails to start the export (snapshot lookup, permissions)	CloudWatch alarm on the Lambda Errors metric	Alarm enters ALARM state, notifies the alert SNS topic
Export task fails or is canceled after starting	CloudWatch alarm on export-task failure (RDS export task events/metrics)	Alarm enters ALARM state, notifies the alert SNS topic
Export does not run in its daily window	Alarm treats missing invocation data as breaching	Alarm enters ALARM state
Cross-account write denied (NLDS bucket policy or KMS drift)	Surfaces as export-task failure; covered by the same alarm	Alarm enters ALARM state

CloudWatch alarms only (per grooming decision): no new Slack/chat pipeline in this issue beyond the existing SNS alert topics.

Additional test scenarios

Exported objects are readable by the bucket owner

• Given an export run has completed
• When an NLDS-account principal reads an object under the agreed prefix
• Then the read succeeds without any cross-account role

Export role cannot write outside its prefix

• Given the Glow export role's credentials
• When a write is attempted to the landing bucket outside the agreed prefix
• Then the write is denied

Alarm fires on a failed export

• Given the CloudWatch alarms are deployed
• When an export run is forced to fail in dev (for example, temporarily revoking the KMS grant)
• Then the relevant alarm transitions to ALARM state within its evaluation period

Production export uses the production database

• Given the stacks are deployed to production
• When the daily export runs in the production account
• Then the export reads a snapshot of transform-prd-onward-db and lands under the agreed production prefix, with no cross-environment references copied from the dev stack

Hard constraints

• Do not change application code in this repo: no new endpoints, no Prisma changes, no export code in src/.
• Do not create a network path between the accounts: no VPC peering, PrivateLink endpoint, database replica, or DMS task (all rejected by ADR 0005).
• Do not add curation/filtering logic: the export is whole-database by decision.
• The landing bucket is NLDS-owned; do not create a Glow-owned long-lived landing bucket.
• Objects must be encrypted with the NLDS-provided KMS key; do not fall back to SSE-S3, and do not ship with a placeholder KMS key ARN.
• The export scheduler Lambda stays out of any VPC and never connects to the database: control-plane API calls only.
• Do not apply production changes locally: production goes through Atlantis on a GitLab MR only.

---

🤖 Generated with aif-create-issue
🤖 Groomed with aif-groom-issue

---

🌊 Managed by Currents · Task 82bdf8dd-d933-402a-abad-c69112e5164a

{"metadata":{"sourceType":"github"},"rank":"Zw","taskId":"82bdf8dd-d933-402a-abad-c69112e5164a","version":1}

[nicholasjjlim]

Error-contract note: 'export task fails after starting' has no CloudWatch metric

While implementing the alarms (build item 4), found that two of the four failure modes in the Error contract cannot be covered by a CloudWatch metric alarm, because RDS snapshot export tasks emit no CloudWatch metric for task success/failure:

• Export task fails or is canceled after starting
• Cross-account write denied (NLDS bucket policy / KMS drift) — surfaces only as an export-task failure

Once StartExportTask returns 200, the scheduler Lambda has already succeeded, so the async failure never touches the Lambda Errors metric. The only detection path is an EventBridge rule on the RDS event (source: aws.rds, detail-type RDS DB Snapshot Event, filtering the export-failed/canceled messages).

Implemented now (dev)

Two Lambda-metric alarms in acct.lower/env.dev/cloudwatch/metric-alarms, notifying the existing yellow-alerts SNS topic:

• onward-nlds-export-Errors — Lambda Errors >= 1 → covers fails to start the export (snapshot lookup / permissions)
• onward-nlds-export-NoInvocation — Invocations < 1, treat_missing_data=breaching over 24h → covers export does not run in its daily window

Also fixed the schedule to 02:30 SGT (cron(30 18 * * ? *)), after the RDS backup window, so the same night's snapshot is available.

Deferred (needs a design decision)

The export-task-failure EventBridge rule is deferred to the prod build phase because of a cross-account constraint: EventBridge → SNS only works same-account+region, but the transform-infra-yellow/red-alerts topics live in the mgmt account while the export rule runs in dev/prod. (This differs from CloudWatch→SNS, which already publishes cross-account fine.)

Two viable delivery paths to pick between when building this:

1. New same-account SNS topic per env (e.g. transform-<env>-onward-nlds-export-alerts) as the rule target, with its own subscription.
2. Cross-account event-bus hop: env rule → mgmt default event bus → mgmt rule → existing yellow/red topics (reuses existing topics; needs a mgmt-account rule applied by DevOps).

Flagging so the AC's 'export-task failure' row is understood to need the EventBridge-event approach, not a metric alarm.

[nicholasjjlim]

Status updated by @nicholasjjlim: icebox → in_progress