Building a game server tracker: what I learned exposing a public JSON API

In my recent project of creating a game server tracker, I discovered valuable lessons while exposing a public JSON API for Lineage 2 private server openings. The tracker, L2 Calendar, made it easier for players to ask about upcoming server openings and for server owners to announce their events. The simple data model included server name, website, chronicle, rate, opening date, and labels such as PvP or RP.

The primary challenge was not the schema itself but the handling of dates. The dateStrings setting in mysql2 was intended to convert DATE columns to strings, but it also affected DATETIME columns which returned JavaScript Date objects instead of strings. This led to a TypeError: value.endsWith is not a function when my display code tried to use the .endsWith method on a Date object. To prevent such issues, I normalized the dates at the data boundary, ensuring that all dates were stored in UTC and displayed accordingly.

The API itself consisted of a single route handler in Next.js, which queried the database using LEFT JOINs for chronicles and labels, then returned the JSON response. To handle cross-origin resource sharing (CORS), I added CORS headers to the GET handler to allow requests from any origin. This decision was made to enable easy integration with browsers and Discord bots, as the data was already publicly available.

Key takeaways from my experience include normalizing dates at the data boundary, caching expensive queries, and sharing the data with others. Normalizing dates ensures proper handling of opening dates and timezones, while caching can improve performance by sharing an in-memory cache with a time-to-live (TTL) for both the API and the homepage. Finally, giving the data away leads to more utility as other developers build upon it; in this case, I provided the API for free, and it has already been used by a Discord bot.