Climbing gym platform · one NestJS + Prisma API serving a React Native climber app, an admin web console, and always-on TV displays · gamified XP, live leaderboards, bilingual EN/KO
Problem to solve
Climbers inside bouldering gyms like to see the solution videos to a boulder problem in order to get the right techniques and reach the top in the intended way. But I've seen that they often lose a lot of time searching for the relevant video on Instagram, since it was scattered between multiple accounts, each with multiple posts containing multiple videos, which made the whole process very time consuming and annoying for the user.
Plus, another thing I've seen is that people love to record themselves and upload the videos on a separate climbing-only Instagram account, while also maintaining a counter of how many boulders they've done and the levels they've succeeded in, in their account bio or post captions. As well as creating crews and communities between climbers.
The last thing I've noted was the organization of climbing events and competitions being very messy and unorganized, with the scores written on paper and based on the trust of the participants, which could lead to inaccuracy in the final scores and overall confusion.
My idea
My core idea was to make the whole process of finding the relevant solution video fast and dead simple. As well as providing an alternative for people climbing without their phone, for them to see video solutions.
My second concept was to also create an environment for climbers to feel included in a community and engaged in their own climbing journey, by providing a sense of progress and reward when climbing.
And my last piece of the puzzle was to provide a proper admin dashboard and event creation system, in order to make event creation and registration a piece of cake.
Architecture
A climber logs a send in the Climber App: the proof video is queued on device, then uploaded to POST /submissions with a Clerk JWT once the gym WiFi cooperates. The NestJS API verifies the token, stores the row in PostgreSQL and the video in Cloudflare R2, and pushes submission.new over SSE to the Admin Dashboard, where a reviewer watches the video and approves it with an atomic status transition. Approval fires domain events: XP and achievements recompute, Expo Push notifies the climber, and every TV + Kiosk display in the gym updates its leaderboard and send feed live.
Deployed: API → Railway · Docker, Prisma migrations on boot · Web + TV → Vercel · Mobile → Expo EAS builds · Database → Railway PostgreSQL · Media → Cloudflare R2 + CDN · CI → GitHub Actions
Outcomes
- 3 clients · mobile, admin web, TV · one API contract
- 15 s · SSE heartbeat keeps gym TVs live all day
- EN / KO · bilingual end to end, database to UI
- offline-first · sends queue on device, survive app kills
Skills demonstrated: event-driven recomputation over incremental counters · TOCTOU-safe state transitions via conditional updates · SSE fan-out within browser connection budgets · offline queue with hydration-gated drain · soft-delete cascades with cache invalidation · webhook idempotency (P2002 upsert conflicts)
Problems and solutions
Every choice answers the same constraint: a real gym floor with flaky WiFi, shared TVs, and part-time admins · one API, three clients.
█ XP is derived, never incremented
Problem: Approvals, rejections, route resets, and challenge bonuses all change a climber's XP. Incrementing a counter on each event drifts the moment any event is missed, replayed, or reversed, and a leaderboard that is visibly wrong kills trust in the whole game.
The source of truth is the set of approved submissions plus challenge completions. Every domain event triggers a full recompute of that user's progress; the UserProgress row is only a cache for leaderboard reads.
- Rejected incremental counters: recompute makes every mutation idempotent and self-healing
- Accepted the extra query cost per approval event, cheap at gym scale
- Bought free backfills: one script re-derives all progress after any rule change
▚ Offline-first uploads for gym dead zones
Problem: Climbers record proof videos deep inside concrete buildings with no signal. A failed upload after a hard-won send means lost proof and an angry user, and mobile OSes kill backgrounded apps mid-upload.
Videos are copied into the app's document directory and tracked in a persisted Zustand queue. A drain hook waits for store hydration and Clerk auth, retries 3 times, and debounces 3 seconds so it never races a foreground upload.
- Rejected fail-and-toast: retry burden lands on code, not the user
- Interrupted uploads reset to queued on rehydration, so a crash mid-upload is recoverable
- Accepted queue-state complexity (hydration gates, dedup by UUID) as the price of zero lost sends
▤ SSE instead of WebSockets for everything live
Problem: Gym TVs, kiosks, and the admin review screen all need real-time updates, but browsers cap HTTP/1.1 at 6 connections per host, and WebSocket infra means sticky sessions and reconnect state on a single small container.
One-way Server-Sent Events with an RxJS Subject per gym location, a 15-second heartbeat, and an explicit per-page connection budget so no feature ships without accounting for its stream.
- Rejected WebSockets: no client-to-server messages were ever needed, so bidirectional infra was pure cost
- Rejected polling: 10+ TVs polling leaderboards would hammer the same queries all day
- Accepted the connection budget as a hard design constraint instead of a surprise in production
▓ Soft-delete because gyms reset walls
Problem: Real gyms strip and re-set walls every few weeks. Hard-deleting a route would cascade through submissions, XP, and achievements, erasing the history climbers care about most.
isActive flags on locations, walls, photos, and routes with explicit cascade rules (deactivating a route expires its challenges; reactivating a wall does not resurrect its routes). Archived routes still render in profiles with their original hold markers.
- Rejected hard delete + history snapshots: two copies of route data inevitably diverge
- Every public query filters on
isActive, an accepted tax on read paths - Bought permanent climb history and safe wall resets with zero data loss
▒ Clerk with auto-provisioning, not homegrown auth
Problem: Three clients need Google OAuth, email OTP, passwords, avatar sync, and account deletion. Hand-rolling that is weeks of work and a permanent security liability for a solo-maintained platform.
A global NestJS guard verifies the Clerk JWT and upserts the local user row on first sight, so the database never has to be pre-seeded. Webhooks keep email and avatar in sync; deletion cascades the local data first, then the Clerk account.
- Rejected Passport + own user table: auth bugs are the one category not worth owning
- Accepted vendor lock-in and webhook edge cases, upsert conflicts are handled explicitly
- Deletion order (DB first, Clerk second) guarantees no orphaned personal data
▞ Bilingual by returning both languages
Problem: A Korean gym with English-speaking climbers: the same wall name must render in Korean on the TV, in the viewer's language on mobile, and both must update instantly when an admin renames it.
Optional nameKo columns next to every name, and the API always returns both. Clients pick with a shared getLocalizedName() helper; TVs render bilingual payloads directly.
- Rejected Accept-Language negotiation: it splits the cache per language and breaks bilingual TV screens
- Accepted slightly fatter payloads for one cacheable response shape
- Null Korean name falls back to English, so translation can lag content