Caching Steam Data with Prisma, Carefully

The Steam section on the games page looks like one data source, but lib/steam.ts assembles several responses: profile status, recently played games, owned games, Steam level, and badges. It also enriches selected games through Steam's public Store endpoint. Calling all of that on every render would add latency and unnecessary upstream traffic, so the site uses a small layered cache instead.

This is the Steam-specific counterpart to the source caches in Building a Unified Activity Feed. It also follows the lean API-route architecture described in Designing My Personal Portfolio Architecture and the server-side normalization approach from Integrating Lightweight GitHub Metrics.

What aggregateSteam() Actually Aggregates

lib/steam.ts reads STEAM_API_KEY and STEAM_ID from server environment variables. It calls five Steam Web API methods in parallel:

const [summary, recent, owned, level, badges] = await Promise.all([
  getPlayerSummary(),
  getRecentlyPlayed(),
  getOwnedStats(),
  getSteamLevel(),
  getBadges()
]);

The summary includes persona state, current game when Steam exposes one, profile URL, account creation time, and last logoff. Recently played data maps playtime_2weeks and playtime_forever. Owned-game data calculates total lifetime minutes, unplayed-library count, top games, and optional favorites selected by STEAM_FAVORITE_APPIDS.

For up to 12 unique app IDs, fetchGameDetails() calls the Store appdetails endpoint and extracts a short description plus at most three genres. Those details have their own in-memory 12-hour TTL because game metadata changes less often than profile state. Individual Store lookup failures are ignored, leaving the base game data intact.

The combined payload powers both the Steam cards on /games and Steam metrics on the dashboard. Internal-only genreCandidates are removed before the response is returned.

The Cache Order

The first layer is a module-level memory cache:

let _steamCache: { ts: number; data: any } | null = null;
const CACHE_TTL_MS = 5 * 60 * 1000;

If it is younger than five minutes, aggregateSteam() returns it immediately with cached: true. This is fast, but it is per server process: a restart, cold start, or another instance may not share it.

On a memory miss, the function lazily imports lib/prisma.ts and looks up the fixed key steam:aggregate. If the database row exists and expiresAt is still in the future, its serialized JSON is loaded into memory and returned with cached: true and source: 'db'.

Only after both layers miss does the function call Steam, enrich the result, calculate stats, and create a new payload with generatedAt. It updates memory synchronously, then starts a fire-and-forget Prisma upsert:

await prismaClient.steamAggregateCache.upsert({
  where: { key: 'steam:aggregate' },
  update: { json: JSON.stringify(payload), expiresAt },
  create: { key: 'steam:aggregate', json: JSON.stringify(payload), expiresAt }
});

Database loading and persistence are wrapped in empty catches. That keeps Steam rendering functional when Prisma is unavailable, but it also means persistence failures are not surfaced to the caller.

The Schema Matches Current Usage

prisma/schema.prisma uses SQLite at file:./dev.db and defines only SteamAggregateCache. The row stores a stringified payload plus lifecycle timestamps:

model SteamAggregateCache {
  key         String   @id
  json        String
  generatedAt DateTime @default(now())
  refreshedAt DateTime @updatedAt
  expiresAt   DateTime
}

The initial migration, prisma/migrations/20250809192815_init/migration.sql, created this table along with several experimental caches. A later Spotify migration added two more tables. The current cleanup migration, prisma/migrations/20260713220000_drop_unused_cache_tables/migration.sql, drops the unused Spotify, Steam metadata, snapshot, and generic API cache tables. It deliberately leaves SteamAggregateCache, the one table the application actually reads and writes.

There is an important limitation: an expired database row is not used as stale fallback if live aggregation fails. The durable cache reduces cold-start calls while valid, but it is not a stale-if-error store.

API and Refresh Routes

The public endpoint is exactly pages/api/steam.ts. It calls aggregateSteam() and sends:

res.setHeader('Cache-Control', 'public, s-maxage=600, stale-while-revalidate=300');

That shared-cache policy can keep an API response fresh for ten minutes, longer than the internal five-minute cache. The /games client also uses SWR with a five-minute refresh interval. These layers reduce traffic, but they mean “refresh” at one layer does not guarantee a new upstream Steam request.

The protected route is pages/api/refresh-cache.ts, and it accepts only POST. When REFRESH_SECRET exists, authorization accepts either the x-refresh-secret header or a matching secret query parameter. A header avoids putting the secret in URLs and logs. In production, an unset secret produces a 503; a wrong secret produces a 401. In development only, the route allows access when the variable is unset.

Despite its name, this route does not invalidate either cache. It calls aggregateSteam(), so a valid memory or database entry can be returned, and the response reports that payload's steamGeneratedAt. It is a protected cache-warming endpoint, not a guaranteed force refresh.

Honest Steam Limitations

Steam's APIs do not provide precise timestamps for each play session here. Recently played data is a rolling two-week playtime total, and current-game information is a profile snapshot. That is why the unified activity feed keeps Steam in an undated summary rather than inserting guessed events.

Privacy and credentials also affect completeness. Missing STEAM_API_KEY or STEAM_ID produces explicit error-shaped results for summary and recent data; private owned-game data can be absent. Some helpers catch network or parsing failures and return empty values, so a successful HTTP response does not guarantee every field is populated.

This design is intentionally modest: memory handles hot requests, Prisma carries valid aggregate data across process lifetimes, CDN and SWR layers reduce repeated delivery work, and the UI tolerates missing enrichment. It improves reliability without claiming stronger freshness or history than Steam actually supplies.