AWS Architecture Blog — Real-time analytics: Oldcastle integrates Infor with Amazon Aurora and Amazon QuickSight¶

Summary¶

Oldcastle APG — one of the largest suppliers of construction materials in North America (150+ facilities; hundreds of daily operational- reporting users) — migrated from on-premises systems to Infor Cloud ERP on AWS and hit a reporting gap: Infor ERP Cloud's built-in configuration-based reporting covered "minimal reports of our operational needs" versus the hundreds of complex real-time reports their on-prem environment supported. The solution, built with AWS Solutions Architects, is a real-time streaming analytics stack that takes change events out of Infor's Data Fabric Stream Pipelines and lands them in Amazon Aurora PostgreSQL for Amazon QuickSight dashboards embedded back inside the Infor OS user experience. The post is a customer case study with concrete architectural primitives worth extracting: the static-EIP Network Load Balancer → EC2 iptables NAT router → RDS Proxy → Aurora ingress shape solves the "external SaaS → private-VPC database" problem without Direct Connect or VPN; the API Gateway + Lambda + GenerateEmbedUrlForRegisteredUser shape delivers embedded QuickSight inside Infor OS with session- token-driven row-level security; Aurora PostgreSQL's JSONB columns act as a flexible streaming-ingest buffer while still supporting indexed normalized-field queries; and SPICE in-memory caching gives subsecond dashboard latency over frequently-accessed aggregates. 50+ dashboards deployed in 8 months, 100+ concurrent users, millions of transactions/day. Customer-case-study genre — numbers are qualitative where most interesting (no p99 latency, no IOPS, no QuickSight embed-rate distribution).

Key takeaways¶

Static-EIP NLB + EC2 NAT router as a cross-VPC private- connectivity shim. "Because Infor can't reach our private VPC directly, we use Elastic Load Balancing (ELB) to distribute traffic and provide secure database access. We implemented a Network Load Balancer (NLB) with static Elastic IP addresses in public subnets, giving us stable, allowlisted IP addresses for Infor's outbound connections. We configured an Amazon Relational Database Service (Amazon RDS) router with Amazon Elastic Compute Cloud (Amazon EC2) instances as NLB targets. These routers forward traffic from the NLB to our Amazon Aurora database in the private subnet using iptables NAT rules. This makes sure that even if the IP of Aurora changes during failover, our static Elastic IPs remain constant." Two structural properties fall out: stable ingress addresses (static Elastic IPs) for the external SaaS allowlist, and failover-transparent backend routing (NAT on the EC2 router lets Aurora's private IP change during failover without disrupting the external-facing allowlist). Canonical new patterns/nat-router-for-static-ip-ingress.
Security-group chaining narrows the trust path. "We configured security groups to accept HTTPS traffic (port 443) only from Infor's IP ranges on the NLB and allow traffic only from the NLB to the RDS routers." Three security-group hops (Infor IPs → NLB → routers; routers → Aurora via RDS Proxy) each only allow the preceding tier as source, so no tier can be reached from the broader internet even if one hop is misconfigured. Standard AWS defense-in-depth, applied to a cross-boundary ingress path.
RDS Proxy as the connection-pooling gatekeeper under streaming ingest. "We use Amazon RDS Proxy to manage database connections and provide automatic failover. We deployed RDS Proxy in the private subnet between our RDS router instances and Aurora cluster to pool and reuse connections. This is critical for handling our high-frequency streaming data. We configured the proxy with IAM authentication for secure credentials and set connection pool parameters based on our expected concurrent stream volume to handle burst traffic without overwhelming the database. With automatic failover enabled, if our primary Aurora instance fails, RDS Proxy automatically redirects traffic to the promoted replica, maintaining continuous data flow." This is the canonical wiki production instance of RDS Proxy in the managed-failover + IAM-auth + burst-absorbing pool composition under continuous CDC-like ingest.
JSONB as streaming-ingest buffer. "We designed our database schema to handle the incoming streaming data, storing it in JSONB columns for flexible querying while using the native JSON functions of Aurora PostgreSQL when we need to parse and normalize specific fields. We created indexes on frequently queried fields to maintain query performance as our data volume grows." Two-phase schema discipline: ingest is schemaless (JSONB accepts whatever upstream ERP tables happen to emit), query is selectively normalized (extract + index only the fields dashboards actually touch). Common gotchas (jsonb_path_ops GIN vs per-field expression indexes, the schema-drift auditing problem) are not named. Canonical new concepts/jsonb-streaming-buffer.
SPICE caching + incremental refresh for dashboard latency. "We identified which datasets benefit from SPICE (Super-fast, Parallel, In-memory Calculation Engine) caching — typically aggregated or frequently accessed data — and configured incremental refresh schedules to keep them current." SPICE is QuickSight's in-memory columnar engine; loading aggregated / frequently-accessed data into SPICE delivers "subsecond response times on complex analytics across large datasets." Direct- query for fresh data, SPICE for hot aggregates — a canonical read-through caching split at the BI tier. Canonical new concepts/spice-in-memory-caching.
Embedded QuickSight via API Gateway + Lambda + session-token mapping. "We securely embedded Amazon QuickSight dashboards within Infor OS through Amazon API Gateway, which generates dynamic URLs for seamless user access. We enabled Quick Sight embedding in our AWS account and registered our Infor domain. We created an API Gateway REST API with Lambda functions that authenticate users, validate Infor session tokens, and call QuickSight's GenerateEmbedUrlForRegisteredUser API to produce time-limited, signed URLs with row-level security. Our Lambda function maps Infor user roles to Quick Sight permissions and applies dashboard filters based on the user's organizational context. We configured CORS settings in API Gateway to allow requests from our Infor domain and implemented rate limiting. On the Infor side, we embedded the Quick Sight dashboards using iframe elements that call our API Gateway endpoint, providing a seamless experience where our users access analytics without leaving the ERP interface." Canonical new patterns/signed-embed-url-with-role-mapping — the load- bearing architectural move is that the embed URL is minted per-user per-request with the viewer's role + filter context baked in, so every iframe load gets a short-lived signed URL that QuickSight enforces row-level security against. Infor's session token is the auth primitive; Lambda is the role-to- permission mapper.
Real-time streaming architecture uses NDJSON as the wire format. "Real-time streaming architecture using the NDJSON format makes sure decision-makers have access to current operational data when they need it most." Each event is one JSON object per line (newline-delimited JSON) — the operational property: a stream processor can split on newlines without parsing the whole buffer, and a partial write truncates at a line boundary instead of corrupting a whole document. Canonical new concepts/ndjson-streaming-format.
Reported outcomes. 50+ complex dashboards in 8 months across customer service, finance, logistics, manufacturing; 100+ concurrent users without degradation; millions of transactions/day processed in real time; "subsecond response times on complex analytics" via SPICE; Multi-AZ Aurora for HA; API surface through API Gateway for both internal and external application consumption.

Systems extracted¶

systems/infor-data-fabric-stream-pipelines (new stub) — Infor's managed real-time CDC-stream processing on ERP Cloud tables; captures insert/update/delete operations with metadata (operation type + timestamp); Oldcastle enabled it as an add-on feature on their Infor Cloud ERP environment.
systems/amazon-aurora — Aurora PostgreSQL-Compatible Edition in Multi-AZ (1 writer + multiple readers across AZs); JSONB columns as streaming-ingest buffer; native JSON functions for field extraction; automated backups + point-in- time recovery; auto-scaling storage.
systems/amazon-quicksight — interactive dashboards + pixel-perfect reports; SPICE in-memory engine; VPC connectivity; Secrets Manager for DB credentials; calculated fields + parameters + row-level security; embedded via GenerateEmbedUrlForRegisteredUser.
systems/amazon-api-gateway — REST API with Lambda integrations; CORS configured per-Infor-domain; rate limiting; generates dynamic embed-URL responses.
systems/aws-lambda — authz logic (validates Infor session tokens), role mapping (Infor roles → QuickSight permissions), calls GenerateEmbedUrlForRegisteredUser.
systems/aws-rds-proxy — connection pool + IAM auth + automatic failover between RDS routers and Aurora writer.
systems/aws-nlb (new stub) — Network Load Balancer with static Elastic IPs in public subnets as the stable ingress address for Infor's outbound connections.
systems/amazon-ec2 — EC2 RDS-router instances as NLB targets; iptables NAT rules forward NLB traffic to Aurora via RDS Proxy.
systems/amazon-route53 — DNS (referenced in architecture diagram alongside other networking primitives).
systems/amazon-cloudwatch — monitoring tier.
systems/aws-iam — access control + IAM auth for RDS Proxy and QuickSight-user mapping.
systems/aws-secrets-manager — stores Aurora credentials used by QuickSight's VPC connection.

Concepts extracted¶

concepts/ndjson-streaming-format (new) — newline-delimited JSON as the streaming wire format for Data Fabric → AWS.
concepts/spice-in-memory-caching (new) — QuickSight's Super-fast, Parallel, In-memory Calculation Engine; columnar in-memory cache for aggregated / frequently-accessed data.
concepts/static-ip-allowlisting (new) — static Elastic IPs on the NLB as the stable address the external SaaS can allowlist.
concepts/cross-vpc-private-connectivity (new) — the general problem of letting an external SaaS reach a database that lives in a private subnet of a customer VPC.
concepts/jsonb-streaming-buffer (new) — the two-phase schema discipline: JSONB ingest + selective normalized-field indexes for query performance.
concepts/embedded-analytics (new) — BI dashboards served inside a host application's chrome (iframe inside Infor OS), not as a standalone tool.
concepts/row-level-security (existing-or-new) — per-user data filtering enforced at the BI layer through signed URLs carrying the viewer's context.
concepts/signed-embed-url (new) — short-lived, signed, per-viewer URL that carries authorization context; QuickSight's GenerateEmbedUrlForRegisteredUser is the canonical instance.

Patterns extracted¶

patterns/nat-router-for-static-ip-ingress (new) — static-EIP NLB in public subnets + EC2 routers with iptables NAT + internal RDS Proxy + private Aurora. Combines stable external addresses with failover-transparent backend routing without Direct Connect or VPN.
patterns/signed-embed-url-with-role-mapping (new) — host application session-token → backend Lambda authz → role/ permission mapping → signed-URL mint with viewer context → iframe load. The embed URL is the authorization capability itself.
patterns/streaming-cdc-to-relational-buffer (new) — external CDC stream → connection-pool + failover proxy → relational store with JSONB ingest + selective normalized indexing. Pairs schemaless ingest with selective query-time structure.

Operational numbers¶

50+ complex dashboards / reports deployed in 8 months.
100+ concurrent users supported without performance degradation.
Millions of transactions processed daily in real time.
Dashboards embed in iframes inside Infor OS via API Gateway endpoint.
Aurora Multi-AZ: 1 writer + multiple reader replicas across AZs.
CORS allowlist: Oldcastle's registered Infor domain only.
Rate limiting on API Gateway endpoint (value not disclosed).
QuickSight authentication: GenerateEmbedUrlForRegisteredUser produces "time-limited, signed URLs" (TTL not disclosed).
Typical response time on SPICE-cached complex analytics: "subsecond" (distribution not disclosed).

Caveats¶

Customer-case-study genre — architectural shape is described qualitatively; no p50/p99 dashboard latency, no Aurora IOPS numbers, no QuickSight embed-request rate, no CORS failure rate, no per-stage failure-mode retrospective.
SPICE refresh staleness dimension not quantified — the post names "incremental refresh schedules" without specifying cadence or the staleness-vs-cost trade-off.
GenerateEmbedUrlForRegisteredUser URL TTL not disclosed — QuickSight's docs specify a 10-minute session-lifetime default but the post doesn't pin Oldcastle's chosen value.
No cost numbers — NLB + EC2 routers + RDS Proxy + QuickSight SPICE capacity-pricing + API Gateway + Lambda all cost separately; the architecture is cost-efficient "with infrastructure costs scaling efficiently with business growth" but no absolute $$$ numbers disclosed.
iptables NAT on EC2 routers is a maintenance burden — the post doesn't address patching / HA / monitoring of the EC2 router fleet, which is structurally a customer-managed dependency between two managed services.
Infor Data Fabric Stream Pipelines details are thin — the post describes what it does (capture insert/update/delete with metadata, emit NDJSON over HTTPS) but not how (at-least-once? exactly-once? back-pressure semantics? schema-change signalling?).
JSONB schema-drift auditing not discussed — storing everything in JSONB gives ingest flexibility but shifts the schema-contract burden to the application.
Single-region scope — the post mentions "designed to expand to additional regions" as future-ready architecture, but the case study is single-region.