Case study No. 01 · 2026 · Academic · System architecture

Inventory system

StockFlow Cloud

Inventory for a multi-branch electronics retailer. The NestJS API runs inside Lambda behind API Gateway; heavy jobs such as Excel imports, reports and reconciliation run as Lambda workers driven by Step Functions and SQS. No VPC, NAT, load balancer or always-on container, so an idle month costs close to nothing.

Read the case ↓ Source code

Role: Backend Developer
Timeline: 4 months · 2026
Team: 2 people
Platforms: Web · Cloud · API
Stack: NestJS · Lambda · Step Functions · Neon Postgres

The problem

What had to be solved, and what made it hard.

Stock across many branches has to stay consistent in real time, large Excel imports must land without choking the system, and the whole thing has to run on an AWS budget of zero.

The goal of StockFlow Cloud was to manage stock levels and inter-branch transfers in real time, and to automate the Excel stock-in process end to end.

The naive version puts everything behind one API: upload the file, parse it in the request, write it to the database. That works for a hundred rows. At tens of thousands it ties up the API, times out, and a single network blip halfway through leaves stock counted twice.

The first version was a hybrid: NestJS on ECS Fargate behind an ALB, with Aurora in a private VPC. Aurora already scaled to zero, but being in a VPC dragged in a NAT Gateway, the ALB and Fargate, all billed by the hour. The system could only be stood up briefly and destroyed. So it was rebuilt fully serverless (ADR-001): Neon Postgres over TLS, Lambdas outside any VPC, and the API itself on Lambda.

How it is built

Drawn to match the system as deployed. Numbers on the drawing refer to the notes below.

The drawing reads left to right. Everyday traffic takes the top path: CloudFront serves the static web, API Gateway forwards requests to the NestJS Lambda. Imports take the bottom path: the browser uploads straight to S3, and from there an event starts the state machine, so the API never touches the file.

Sheet1 / 2StockFlow Cloud inventory systemFig. 1. Full serverless, system overviewDrawn byV. D. Anh · 2026Scalenot to scale

↔ Scroll sideways to see the whole drawing

1
Static web on the edge
Next.js is exported to a private S3 bucket and served by CloudFront with a custom domain and ACM certificate.
2
API on Lambda
NestJS runs in a Lambda (Node 22, arm64) behind an API Gateway HTTP API, throttled at 20 rps to stop a denial-of-wallet. Cognito handles sign-in and three roles.
3
Upload around the API
The API only signs a presigned URL. The browser puts the Excel file straight into the ingestion bucket.
4
Event-driven start
S3 'object created' goes to EventBridge, which starts a Step Functions execution for that import job.
5
Workers and a pooled database
Validator, Parser and Writer Lambdas reach Neon Postgres through its PgBouncer endpoint, one connection per container. The URL lives in SSM Parameter Store.
6
Failures have a home
Failed executions and report messages land in SQS and a dead-letter queue. A recovery console lets an admin replay, discard or redrive them with a reason and an audit log.
7
Live updates
Pusher Channels pushes job progress, new transfer requests and low-stock alerts to every open screen.

Sheet2 / 2StockFlow Cloud inventory systemFig. 2. Ingestion state machine with human approvalDrawn byV. D. Anh · 2026Scalenot to scale

The import pipeline, step by step

01
Trigger
S3 · EventBridge
The file lands in S3 via a presigned URL. The S3 event starts the state machine through EventBridge, adding no load to the main API.
02
Validator
Lambda
Checks the MIME type and that the column headers match the template before reading a single row.
03
Parser
Lambda
Stream-parses the file to keep memory flat, validates each row and stages it in import_job_rows with a unique idempotency key.
04
HaltForUserApproval
waitForTaskToken
Stores the task token against the job and pauses. Nothing runs, nothing is billed per second, while the execution waits.
❚❚ Waits for a human
05
Confirm
Warehouse keeper · API
The keeper pages through valid rows, rejected rows and the projected stock impact, then confirms. The API calls SendTaskSuccess.
06
Writer
Lambda
Resumes and commits in batches of 500 rows, each inside one database transaction. Any failure rolls the batch back.

Stack, by layer

Backend & APIcore business on Lambda

NestJS 10TypeScriptPrisma ORMREST APIserverless-express

Cloud · AWSinfrastructure & orchestration

LambdaAPI GatewayStep FunctionsEventBridgeS3SQS (DLQ)SESCognitoCloudFrontSSM

Data & realtimestorage & sync

Neon PostgresPgBouncerPusher Channels

Observabilityno server to run

CloudWatchX-RayLambda PowertoolsEMF metrics

Build, IaC & CI/CDpackaging & deploy

TerraformGitHub ActionsOIDCesbuildTurborepo

What it does

Six capabilities the architecture above makes possible.

No. 01

Real-time multi-branch stock

Three separate figures: physical quantity, reserved, and available to sell. Low-stock alerts fire per branch threshold, based on available stock.

No. 02

Asynchronous Excel import

Direct-to-S3 upload, streamed parsing, and an import preview to approve before anything is written.

No. 03

Atomic transfers

A request reserves stock at once; approval moves it from sender to receiver in one transaction, and every move is written to a ledger.

No. 04

Nightly reconciliation

A cron Lambda compares current stock with the sum of ledger movements and opens an issue on any mismatch.

No. 05

Recovery console

EventBridge routes terminal Step Functions failures into a recovery queue. Admins replay or discard with a mandatory reason, a replay cap and an audit log; committed rows are never written twice.

No. 06

Async reports & RBAC

Large reports go SQS → Lambda → S3 → presigned link, with partial batch failures and a real DLQ. Cognito user pool with ADMIN, STORE_MANAGER and WAREHOUSE roles, plus a forgot-password flow.

No. 07

Observability without servers

JSON logs with a correlation ID from API to worker, X-Ray tracing, six business metrics via EMF, one CloudWatch dashboard and nine alarms that email through SNS.

No. 08

Daily ops email

At 07:00 Vietnam time, EventBridge Scheduler runs a Lambda that sums up traffic, errors, latency, DLQ depth, imports and the month's AWS bill, then sends it through SES with an OK / Needs a look / Incident headline.

No. 09

Push to deploy

A push to main runs quality checks, then GitHub Actions assumes an AWS role over OIDC (no access keys), migrates Neon, applies Terraform behind a guard against deleting stateful resources, and smoke-tests the new commit SHA.

Hard parts & solutions

Each problem, the approach taken, and the number that shows it worked.

4.1

0duplicate rows when the writer retries

Retries that never double-count

Problem

If the Writer fails on a network error halfway, Step Functions retries it and the same rows are handed over again. Without a guard, rows already written are written again and stock goes up twice.

Approach

Each staged row has a unique idempotency key built from the job and row number. Before adding stock, the writer claims the row with a conditional update; a duplicate run sees zero rows claimed and adds nothing.

TypeScript// key = `${importJobId}:${rowNumber}` (unique)
const claimed = await tx.importJobRow.updateMany({
  where: { id: row.id, validationStatus: "VALID" },
  data: { validationStatus: "COMMITTED" },
});
if (claimed.count === 0) continue; // retry → skip

4.2

Two branches, one SKU, same second

Problem

Two transfer requests on the same component can both read the same available quantity and both reserve it, overselling stock that is not there.

Approach

The check and the reservation are one atomic UPDATE. PostgreSQL locks the row; the second request gets zero affected rows when there is not enough left, and its transaction rolls back. Items are locked in a fixed order to avoid deadlocks.

SQLUPDATE inventory
SET reserved_quantity = reserved_quantity + $qty,
    version = version + 1
WHERE branch_id = $branch
  AND component_id = $component
  AND quantity - reserved_quantity >= $qty;
-- affected_rows = 0 → not enough stock, rollback

4.3

1connection per Lambda container

Lambda scale-out vs. the connection pool

Problem

Lambda scales with concurrent work. A hundred containers each opening a pool of connections will exhaust Postgres long before CPU is the bottleneck.

Approach

Runtime traffic goes through Neon's pooled (PgBouncer) endpoint and each container keeps a single connection; migrations use the direct endpoint. The URL is read from SSM at start-up, and only then is PrismaClient created.

env# SSM /stockflow/database-url (SecureString)
DATABASE_URL=postgres://…-pooler.neon.tech/db
  ?sslmode=require&pgbouncer=true&connection_limit=1
# one Lambda container → at most 1 connection

4.4

0resources billed by the hour

Zero budget, still always on

Problem

The hybrid design paid by the hour for a NAT Gateway, an ALB and Fargate even with no users, only because Aurora sat in a VPC. Turning them off meant no demo.

Approach

Move the database to Neon over TLS, take every Lambda out of the VPC, and run NestJS on Lambda behind API Gateway. Cost now follows requests; API throttling and $1 / $5 budget alerts cap the downside.

4.5

0workers running while it waits

Waiting for a human without paying for it

Problem

Approval can take minutes or hours. Polling from a Lambda, or holding a request open, wastes money and breaks on timeouts.

Approach

The state machine pauses on waitForTaskToken. The token is stored with the job; the confirm button simply calls SendTaskSuccess and the execution resumes exactly where it stopped.

Results

Duplicate rows on retry: every staged row has its own idempotency key and is claimed before stock moves

500 rows

Per write batch, wrapped in one DB transaction: a failure rolls the whole batch back

Resources billed by the hour: no VPC, NAT, ALB or container, so idle cost is close to zero

What I learned

I
Separate what is slow from what people wait on
The API answers quickly because it never does the heavy lifting. Splitting the system by latency, not by feature, made both halves simpler.
II
Design for the retry, not the happy path
On the cloud, at-least-once delivery is the default. Idempotency keys cost a column and a lookup; recovering double-counted stock costs a weekend.
III
The network decides the bill
Compute had already scaled to zero; the cost came from putting the database in a VPC, which pulled in NAT, a load balancer and containers. Changing where the database lives removed about 25 Terraform resources and the hourly bill with them.
IV
Green health checks can lie
After the move, /api/health was green while nine workers could not reach the database: PrismaClient was created before the URL came back from SSM. The daily email caught it, not the health check. Smoke tests now have to touch the database.
V
Put the human in the right place
Reviewing a preview before writing is cheaper than reverting after. With waitForTaskToken the pause is part of the workflow itself, not a flag the code has to remember to check.

Next case study · No. 02

Healthcare platform

Sepolia Health

End-to-end digital care on web and mobile, with an AI chatbot, StreamChat messaging and Zoom video visits.

Open case

Plate II. Mortar, chamomile & stethoscope

StockFlow Cloud

The problem

How it is built

Static web on the edge

API on Lambda

Upload around the API

Event-driven start

Workers and a pooled database

Failures have a home

Live updates

The import pipeline, step by step

Trigger

Validator

Parser

HaltForUserApproval

Confirm

Writer

Stack, by layer

What it does

Real-time multi-branch stock

Asynchronous Excel import

Atomic transfers

Nightly reconciliation

Recovery console

Async reports & RBAC

Observability without servers

Daily ops email

Push to deploy

Hard parts & solutions

Retries that never double-count

Two branches, one SKU, same second

Lambda scale-out vs. the connection pool

Zero budget, still always on

Waiting for a human without paying for it

Results

What I learned

Separate what is slow from what people wait on

Design for the retry, not the happy path

The network decides the bill

Green health checks can lie

Put the human in the right place

Sepolia Health