Data Model

TL;DR -All of Social Live's data lives in one PostgreSQL database, defined in a single schema file. The tables group into clear domains: identity, social/messaging, live rooms, the economy, and moderation. The economy tables are append-only where money is concerned -you never edit a money row, you add a correcting one.

Who this is for -Product & Business: read "How the data is organized" and skim the domain tables to understand what the system remembers about a user. Engineering & Backend: every table, the ledger types, and the idempotency anchors.

How the data is organized

A data model is just the list of tables the app keeps and what each one stores -think of it as the app's filing system. Social Live uses PostgreSQL (a relational database) and describes every table in one place: backend/prisma/schema.prisma. Prisma is the tool that turns that file into database tables and into typed code the backend uses.

The tables cluster into five domains, and the rest of this page walks through them in order:

1. Identity & auth -who you are and how you log in.
2. Social graph & messaging -follows, blocks, DMs, calls.
3. Live -live rooms and what happens in them.
4. Economy -the money: wallets, the ledger, gifts, payouts.
5. Moderation -reports and the actions staff take.

Identity & auth

Everything about an account and how it proves who it is. A User is the root record; the others hang off it.

Social graph & messaging

Who follows whom, who's blocked, and the direct-message and call history.

Live

The state of live rooms -who's hosting, who's on stage, and head-to-head battles between rooms.

Economy -the money-critical tables

This is the heart of the system's integrity. Two currencies are in play: coins are what users buy with real money and spend on gifts; diamonds are what hosts earn when they receive gifts, and can cash out. The tables below track balances, and -crucially -every single movement of value.

Ledger transaction types

The LedgerTransaction table is a permanent, append-only journal: one row for every time value moves, tagged with a type that says why. These are all the types:

COIN_PURCHASE, COIN_BONUS_GRANTED, GIFT_SPEND, DIAMOND_EARNING, DIAMOND_SETTLED, DIAMOND_TO_COIN_CONVERSION, REFUND, WITHDRAWAL_HOLD, WITHDRAWAL_PAID, WITHDRAWAL_REVERSED, ADMIN_ADJUSTMENT, DM_UNLOCK_SPEND, PAID_MEDIA_UNLOCK_SPEND.

Reading the ledger for one user in createdAt order reconstructs their entire financial history -that is the design intent. Never mutate ledger rows; corrections are new rows (REFUND, ADMIN_ADJUSTMENT).

Why diamond micros?

Hosts earn a fraction of a diamond per coin gifted, and fractions don't survive well as whole numbers. So diamonds are stored as micros -tiny integer units -to keep every fractional credit exact and avoid rounding drift.

Hosts earn 0.5 diamonds per paid coin gifted. Storing diamonds as BigInt micros (1 diamond = 1,000,000 micros) keeps every fractional credit exact. Conversion helpers live in economy.constants.ts; nothing else in the codebase does diamond arithmetic by hand.

Idempotency anchors

"Idempotent" means the same operation can safely run twice without doubling its effect. Each money operation builds a unique key so a retry, a replay, or a duplicate tap lands on the same row instead of creating a second one. Here is how each key is constructed:

• Coin purchase: coin_purchase:<channel>:<externalRef> where externalRef is the Apple transaction id or Google order id — platform-wide unique, so a replayed receipt can never double-credit and a token redeemed on account A is rejected on account B.
• Gift send: gift_send:<userId>:<clientIdempotencyKey> (client sends a UUID per tap).
• Refund: coin_refund:<channel>:<externalRef>.
• DM unlock: dm_unlock:<payer>:<peer>.
• Paid-media unlock: paid_media_unlock:<viewer>:<messageId>.

Moderation

The records behind keeping the platform safe: user reports, actions staff take, badges, and app-wide settings.

Migrations

When the schema changes, the database has to be updated to match -that process is called a migration. These are the commands that regenerate the typed client and apply schema changes locally and in production:

npm --workspace backend run prisma:generate   # regenerate client after schema edits
npx prisma migrate dev --name <change>        # create a migration locally
npx prisma migrate deploy                     # apply in production (runs in Docker entrypoint)

If backend tests fail with missing enum members, the generated client is stale -run prisma:generate.

Related pages

• Backend Module Map -the modules that read and write these tables.
• The economy -the rules layered on top of the economy tables.
• System Architecture -where the database sits in the overall topology.