Supabase and the data model
Postgres does most of the work
Overview
Prysm is three pieces: the mobile app, a Supabase backend, and this website on Vercel. The backend uses four parts of Supabase: managed Postgres, Auth (email code, Apple and Google), Storage for profile pictures and post images, and edge functions. Four principles shape it. The app never holds a third-party secret. The database enforces the rules. The game catalog is local. Slow or unreliable work runs on a schedule, never inside a tap.
How it works
- 1
Search ranking, title normalisation, the feed, notification fan-out and the free-tier cap are all SQL. Each rule has one implementation, in the database, so the edge functions, the seed scripts and the app cannot drift apart.
- 2
Three extensions carry the load: pg_trgm for fuzzy matching, unaccent, and pg_cron with pg_net for the schedules.
- 3
The app reads and writes your own data (library, wishlist, follows, likes, posts) directly through the Supabase client, and row-level security decides what each user may touch. Feed building, notifications and challenge progress are database functions called the same way.
- 4
Twenty-one edge functions exist where something needs a secret, an outside API or a service role: IGDB, Steam, Xbox, PlayStation, the language model, push, account deletion and RevenueCat's webhook. They share code for the IGDB client, the shape of a game, link previews, the platform clients, push and the model.
- 5
Functions that act for a user build their database connection with that user's own session, so the same row rules apply inside a function as inside the app. The scheduled and vendor-facing functions are closed to the app and reachable only by the systems they serve.
- 6
Storage has two public buckets: profile pictures (2 MB limit) and post images (5 MB). Each user can only write inside their own folder, and only common image types are accepted.
- 7
The schema lives in more than sixty SQL migrations. The one exception is the sweep schedules, which reference credentials that must not be committed. Bulk work, such as seeding the catalog, runs in Node scripts, because edge functions have CPU and time limits a bulk load would exceed.
Architecture flow
- 1
The app
Expo and React Native on iOS and Android. It shows the screens, keeps local state, and hosts the widgets and the share target.
- 2
Auth
Issues the session token the app sends with every request. Tokens are short-lived and refresh on their own.
- 3
Postgres
The catalog, every user's data, and the rules: row-level security, triggers and ranking functions.
- 4
Edge functions
The only place third-party credentials exist: IGDB, Steam, Xbox, PlayStation, the model, push and RevenueCat.
- 5
Scheduled sweeps
Push delivery, vague-search jobs and release-day alerts, each a pg_cron entry that calls a function.
What is stored, and who can read it
| Group | What is in it | Who can read |
|---|---|---|
| Catalog | Games, platforms, alternate titles, external ids, genres | Any signed-in user, never anonymous visitors |
| Your library | Library entries, wishlist, recently viewed, share intake | Owner only |
| Social | Profiles, follows, blocks, posts, likes, comments, reposts, polls, reports | Profiles and posts follow visibility rules; blocks and reports are owner-only |
| Notifications | One row per notification | The recipient only |
| Events | Seasonal challenges, game watches | Challenges are visible to users; watches are owner-only |
| Linked platforms | Steam and Xbox links and their one-time link tokens | Owner only |
| Subscriptions | Subscription state and its event history | Owners read their own; only the payment webhook writes |
| Jobs and cache | Queued vague-search jobs, a search cache | The job's owner; the cache is not readable by users |
The edge functions
| Group | Functions | What they do |
|---|---|---|
| Catalog | search, games, game-artwork, roulette | Search, game detail, popular and popular-with-friends lists, recently viewed and watched lists, artwork, and a weighted roulette draw |
| Share | share-resolve, share-confirm | Turn a shared link into candidates, then add the one you confirm |
| Vague search | vague-search, vague-search-sweep | Accept a description, and answer queued descriptions on a schedule |
| Platforms | steam-link-*, steam-import, xbox-link-*, xbox-import, psn-import, android-import | Connect an account and import owned games, or import what is installed on an Android phone |
| Account | account-delete | Delete the signed-in account and its data |
| Background | push-sweep, game-release-sweep, revenuecat-webhook | Send pending pushes, ring release-day alerts, receive subscription changes from RevenueCat |
Key decisions
Our id is the identity, IGDB's is a reference
Every game has Prysm's own UUID, and nothing outside the sync layer reads the IGDB id. If the catalog ever changes provider, only the sync code changes, not the library, the feed or the widgets.
Rules in the database, not the app
Rules written in the app can be bypassed by a modified app. Rules in the database can't, because every request from any client passes them. Tests sign in two real accounts and check that neither can read, change or delete the other's rows.
A sweep instead of a trigger that calls out
A trigger that called an outside service would make your action depend on that service being up. With a sweep, the action only writes a row; delivery happens a moment later, and an outage just delays it. The cost is a short delay, which is fine for notifications and background jobs.
Deleting a user cascades
Every row that references an account is removed with it, including rows other people own that point at the deleted person, such as their follow of you or a notification about you.