Papa

Posted on Jun 29

I turned café wifi speeds into a metro map — on Aurora Serverless v2 + Vercel

#ai #aws #postgres #postgressql

I Built Lattency: A Crowdsourced Metro Map for Café Wi-Fi Using Aurora PostgreSQL Serverless v2 and Vercel

Built for the H0: Hack the Zero Stack hackathon (Vercel × AWS Databases).

Stack: Amazon Aurora PostgreSQL Serverless v2 + PostGIS + Next.js on Vercel

Live Demo: https://lattency.vercel.app/

Source: https://github.com/thisyearnofear/lattency

#H0Hackathon

Finding a café with reliable Wi-Fi (in Nairobi) is surprisingly difficult.

You buy a coffee, settle in, open your laptop, and discover the network can't survive a video call. Twenty minutes later you're packing up and looking for another café.

It's a problem that quietly steals hours every week.

So I built Lattency.

Instead of reviews saying "the Wi-Fi is good", Lattency is a crowdsourced metro map where every café is a station and every line represents a Wi-Fi speed tier—not geography.

🚄 Express — 50 Mbps and above
🚉 Local — 10–49 Mbps
🚧 Suspended — Below 10 Mbps

Anyone can run a speed test, submit a measurement, and within seconds the café is reclassified for everyone else.

The interesting part wasn't drawing the map.

It was building a backend that could answer "What's the fastest café near me?", update itself in near real time, resist bad data, and cost almost nothing when nobody was using it.

This post is about the infrastructure that makes that possible.

Choosing the database

The hackathon offered three AWS database options:

Amazon DynamoDB
Amazon Aurora DSQL
Amazon Aurora PostgreSQL

For Lattency, the decision came down to one requirement:

Find cafés within a radius of the user's location.

With PostGIS, that's almost trivial.

SELECT id, name, lat, lng
FROM cafes
WHERE ST_DWithin(
    geog,
    ST_MakePoint($1, $2)::geography,
    $3
);

ST_DWithin() is exactly the kind of query PostGIS was built for.

DynamoDB has no native geospatial radius queries, and Aurora DSQL doesn't currently support PostGIS.

Once location search became a requirement, Aurora PostgreSQL wasn't simply the easiest choice—it was the only database that naturally fit the problem.

The second reason was economics.

Aurora PostgreSQL Serverless v2 scales all the way down to 0 ACUs when idle.

A project like Lattency doesn't receive traffic around the clock. It wakes up during working hours, slows down overnight, and may eventually expand city by city.

Paying only when the database is actually serving requests is exactly the pricing model I wanted.

The trade-off is a cold start of roughly 15–30 seconds after long periods of inactivity.

Fortunately, there are ways to hide that from users.

Let PostgreSQL decide the line colour

Raw speed tests are noisy.

One person on hotel Wi-Fi, a VPN, or a congested network shouldn't immediately downgrade an entire café.

Instead of calculating speed tiers on every request, Lattency maintains a materialized view that stores per-café statistics.

Every new measurement refreshes that view.

REFRESH MATERIALIZED VIEW CONCURRENTLY cafe_speed_stats;

The important part is CONCURRENTLY.

Without it, PostgreSQL would lock the view while rebuilding it.

With it, visitors continue reading from the old version while PostgreSQL prepares the new one in the background.

No downtime.

As more measurements came in, I made the aggregation smarter.

Once a café has enough submissions, measurements marked as outliers are ignored when calculating the median.

That means:

one accidental upload won't move a café into Suspended
genuine network upgrades still appear naturally over time

Outliers are excluded from the calculation—not deleted—so the underlying data remains intact.

Making serverless behave like a long-running app

Connecting a serverless application to PostgreSQL introduces three practical problems.

1. Too many database connections

Every serverless function can create its own PostgreSQL connection.

That doesn't scale very well.

Instead, I cache a single pg.Pool on globalThis, allowing warm instances to reuse existing connections.

const globalForPg = globalThis as { pool?: Pool };

export const pool =
    globalForPg.pool ??
    new Pool({
        connectionString: process.env.DATABASE_URL,
        ssl: { rejectUnauthorized: true },
        max: 1,
    });

globalForPg.pool = pool;

Keeping each instance to a single pooled connection dramatically reduces connection pressure.

2. Don't wake the database for every visitor

Most people viewing the homepage are looking at the same information.

There's no reason every request should hit Aurora.

The homepage is statically rendered using Incremental Static Regeneration.

export const revalidate = 60;

Most visitors receive a cached page from Vercel's edge network.

Aurora only needs to wake once every minute instead of once per visitor, reducing both cost and cold starts.

3. Never fail because the database is sleeping

Cold starts happen.

Instead of showing an error page while Aurora wakes up, Lattency falls back to a bundled snapshot included with the application.

The map still loads.

Users can still explore cafés.

For a hackathon demo, that's the difference between an impressive first impression and a blank screen.

4. Refresh after the response

Refreshing the materialized view isn't part of the user's request.

Using Next.js' after() API, the response is sent immediately while the refresh runs afterwards.

after(async () => {
    await refreshSpeedStats();
});

Users don't wait for maintenance work.

Crowdsourced data only works if people can't game it

Allowing anyone to contribute data is both the best feature and the biggest security problem.

I wanted automatic submissions to be more trustworthy than manually typed numbers.

Instead of asking contributors to copy results from another speed test, Lattency performs the test directly in the browser against a Vercel Edge region.

It measures:

download speed
upload speed
latency
jitter
packet loss

More importantly, the server—not the client—decides whether the submission counts as an automatic test.

const testMethod =
    body.downloadBytes &&
    body.downloadDurationMs
        ? "auto"
        : "manual";

A user can't simply claim they performed an automatic test.

The evidence has to exist.

To prevent spam without storing personal information, every IP address is salted and hashed before comparison.

createHash("sha256")
    .update(ip + process.env.RATE_LIMIT_SALT)
    .digest("hex");

The raw IP is never stored.

Rate limiting becomes:

One measurement per IP, per café, every ten minutes.

Privacy is preserved while abuse becomes significantly harder.

Adding a brand-new café is also transactional.

Creating the café and inserting its first measurement happen inside the same database transaction.

If either operation fails, everything rolls back.

The map never ends up with empty cafés that have no measurements attached.

The production architecture I almost shipped

One rabbit hole consumed far more time than I expected.

RDS Proxy.

Initially, I wanted every database connection to pass through an RDS Proxy instead of exposing Aurora directly.

I configured:

a dedicated security group
Secrets Manager credentials
IAM permissions
the proxy itself

Everything looked correct.

Nothing connected.

Eventually I realised why.

RDS Proxy is intentionally private.

Its endpoint lives inside the VPC.

That's perfect for Lambda, ECS, and EC2.

It's not designed for platforms like Vercel running outside your AWS network.

Connecting to it requires additional networking such as PrivateLink or a load balancer.

That lesson ended up being more valuable than the configuration itself.

If I were taking Lattency to production today, I'd choose one of these architectures:

Vercel × AWS Marketplace Aurora Integration — Aurora provisioned through Vercel using PrivateLink. No public database endpoint.
PrivateLink — More infrastructure, but the same private networking model.
Network Load Balancer + RDS Proxy — Works without reprovisioning, although it adds cost and operational complexity.

For the hackathon, opening PostgreSQL on port 5432 was the pragmatic decision.

I think it's more useful to explain that trade-off honestly than pretend the demo shipped with perfect infrastructure.

What I ended up with

The metro-map interface is what people notice first.

The infrastructure is what makes it believable.

Lattency combines:

Aurora PostgreSQL Serverless v2 + PostGIS for fast geospatial searches and scale-to-zero pricing.
Materialized views for outlier-aware café classification without blocking reads.
Incremental Static Regeneration so most visitors never touch the database.
Connection reuse to keep PostgreSQL healthy in a serverless environment.
Server-side trust verification so automatic measurements can't be trivially faked.
Graceful fallbacks so the application continues working even while Aurora is waking up.