When the dashboard turned red
It started with a simple observation: the Azure SQL database was running at 100% DTU, all the time. Connection pools were exhausted. Users were seeing timeout errors. The server CPU was pinned at 390%.
For a platform serving open data to an island of 84,000 people, this shouldn't happen. But Smart Island isn't just serving islanders anymore. Since launching, the platform has grown to 35+ datasets, 140+ pages, and 93 MCP tools — and the audience has grown with it. Researchers, policymakers, AI assistants, search crawlers, and the general public were all hitting the site simultaneously.
The irony? Every feature we'd built to make data accessible was now contributing to the problem. Every page view was a live database query. Every crawler visit was another connection. Success was, quite literally, our enemy.
The diagnosis
The architecture had a fundamental flaw: almost every page was configured as force-dynamic in Next.js, meaning every single page view triggered a fresh database query. For pages like the AI Observatory — which runs 18 parallel Prisma queries to build its dashboard — this was catastrophic under load.
One page in particular was the worst offender. The Skills Intelligence page, with its complex junction table queries across canonical skill and knowledge registries, was being hammered relentlessly — likely by crawlers exploring every possible filter combination. Each visit spawned multiple heavy queries, and the in-memory cache couldn't help because crawlers were hitting unique URL parameter combinations, bypassing the cache entirely.
The connection pool — capped at 20 connections — was being drained dry by this single page.
The fix: from live queries to intelligent caching
The solution came in waves, applied while the site was under load.
First, we switched 27 pages from live database queries to Incremental Static Regeneration (ISR). Instead of hitting the database on every page view, these pages now serve cached responses and revalidate periodically. For job market data that updates daily, a one-hour cache is more than sufficient — and it reduces database load from thousands of queries per hour to one.
Second, we took the Skills page offline entirely as an emergency measure, redirecting to the Jobs page while we rebuilt the architecture from scratch.
Third, we rebuilt the Skills Intelligence page using a completely different pattern: precomputed static JSON. A pipeline now runs after the daily data refresh, queries all the skill and knowledge data once, and writes a single JSON file. The page reads this file from disk — zero database queries, ever. All filtering happens client-side in the browser.
Finally, we made the entire build process resilient to database outages. Every data-fetching function now gracefully falls back to empty data if the database is unreachable during deployment, rather than crashing the build. The first visitor after the database comes back triggers a fresh ISR revalidation, and the site seamlessly recovers.
The result
Database DTU usage dropped from 100% to effectively zero for page-serving. The connection pool went from perpetually exhausted to barely touched. Server CPU load fell from 390% to normal levels.
The site now serves the same data, to the same audience, with the same features — but the database only gets queried when data actually changes, not when someone views a page.
The lesson
When you're building a data platform, the instinct is to always show the freshest data. Live queries feel honest. But for data that updates once a day, querying the database thousands of times to return the same result isn't freshness — it's waste.
The better pattern: compute once, cache intelligently, and make your infrastructure resilient to the traffic that success brings. Because if you build something useful, people will come — and so will the bots.