Data model

TwistCue’s DB is Postgres 15+, snake_case, managed via Prisma. This page describes the tables the admin panel touches. The source of truth is services/api/prisma/schema.prisma.

Users & identity

• users: one row per user (guest or registered). Key columns: id, email, phone_e164, display_name, country_code, language, is_guest, status (active / suspended / banned / deleted), suspended_until, ban_reason, token_version (bumped on force-logout), created_at, last_seen_at.
• profiles: extended profile info (bio, prefs).
• devices: one row per registered device. platform, app_version, os_version, device_model, fingerprint, ip_last, first_seen_at, last_seen_at.

Catalog

• series: one row per show. slug (unique, immutable), title_i18n (jsonb), synopsis_i18n, maturity, status (draft|in_review|scheduled|published|archived), market_availability (string[]), price_coins_override, free_episode_count, editorial_hero_score.
• episodes: series children. episode_number, title_i18n, synopsis_i18n, cliffhanger_score, price_coins_override, media_asset_id, thumb_asset_id, status.
• genres, tropes: taxonomy. slug, name_i18n, sort, active.
• series_genres, series_tropes: joins.
• cast_members: cast. slug, name, role_default, photo_asset_id, bio_i18n.
• series_cast: join with role, character_name, sort.

Media

• media_assets: kind (video|poster|hero|thumb|caption), series_id, episode_id, provider (cf_stream|gcs), provider_id, playback_id, status (uploading|processing|ready|failed), duration_s, width, height, lang.

Publishing

• publish_schedules: ref_type (series|episode), ref_id, action (publish|unpublish|free_rotation), run_at, status (pending|executed|failed), last_error, retry_count.

Curation

• collections: slug, title_i18n, explanation, type (manual|rule), rule (jsonb), status, sort.
• collection_items: (collection_id, series_id, sort) for manual collections.
• rails: surface (for_you|discover), collection_id, sort, status.
• free_series_rotations: (date, series_id, market_code?, notes).

Coin economy

• coins_wallets: legacy per-user wallet totals; balance is derived from ledger, this table is deprecated for reads.
• coin_ledger: the source of truth. Double-entry rows: id, user_id, account (wallet_paid|wallet_bonus|source_*|sink_*), amount (int, signed), reason, ref_type, ref_id, event_id, created_at. Group by event_id → all rows sum to zero.
• episode_unlocks: one row per (user, episode) permanent unlock. purchase_id links to funding source.

See Coin ledger: double-entry for the full model.

Payments

• purchases: one row per completed purchase. platform, product_id, transaction_id, coins_granted, bonus_coins, amount_cents, currency, region, status.
• subscriptions: one row per subscription (per platform). platform, product_id, status, auto_renew, current_period_start/end, trial_end, canceled_at, price_cents, currency.
• refund_requests: purchase_id, amount_cents, reason, status (pending|approved|executed|rejected), underpaid_cents, requested_by, approved_by, executed_at.
• price_overrides: scope (market|series|episode), scope_ref, coins, currency_hint, active_from, active_to, active.
• coin_packages: sku, coins, bonus_coins, price_cents, currency, rails (string[]), status.
• promos: code, type (bonus_multiplier|coin_grant|trial_ext), config (jsonb), budget_coins, spent_coins, window (jsonb), segment_id.

Admin / RBAC

• admin_users: email, name, password_hash, role, role_id (FK to admin_roles), is_active, token_version.
• admin_roles: name, description, permissions (string[]), is_system.
• admin_approvals: action_type, payload (jsonb), requested_by, approved_by, status, resolved_at.
• audit_logs: admin_id, action, ref_type, ref_id, before (jsonb), after (jsonb), ip, user_agent.

Analytics

• analytics_event: raw event stream. user_id, device_id, event, properties (jsonb), client_dedup_key, created_at.
• rollup_revenue_daily: day, rail, country, gross_cents, net_cents, refunds_cents, tx_count.
• rollup_series_daily: day, series_id, starts, completes, unlocks, coins_burned, watch_seconds.
• rollup_funnel_daily: day, country, previews, ep1_starts, paywall_hits, unlocks, repeat_unlocks.

Engagement

• segments: name, description, query (jsonb), cached_size, cached_at.
• push_campaigns: name, segment_id, title_i18n, body_i18n, deep_link, category, status (draft|scheduled|sending|sent|failed), scheduled_at, metrics (jsonb: sent, delivered, opened).
• comments: user_id, episode_id, body, status (visible|hidden|deleted), report_count.
• reports: reporter_user_id, ref_type, ref_id, reason, status, resolved_by.
• user_strikes: user_id, kind (comment|report), reason, admin_id.
• support_notes: user_id, admin_id, body.
• feature_flags: key, value (jsonb), updated_by, updated_at.
• reward_configs: singleton row with the tuning JSON.

Interaction surfaces

• watch_history, watch_progress: playback state.
• follows, saves, likes: user reactions on series.
• rewards, daily_streaks: engagement bonuses.
• notifications: in-app notifications feed.

Naming conventions

• Tables and columns are snake_case.
• API responses are camelCase (Prisma converts automatically via the response DTOs in packages/contracts).
• Foreign keys are <table>_id.
• Booleans are is_* or has_*.
• Timestamps are _at suffix (created_at, updated_at, deleted_at, resolved_at).