Historical snapshot archived 2026-09-25. This records an earlier review or plan, not current implementation or live ticket state. For current work, follow root AGENTS.md, the relevant BloxClips skill, and owning repository source/tests. Preserve approved decisions as evidence; verify their present authority before acting.

Data Model

Overview

The backend uses Prisma against PostgreSQL. prisma/schema.prisma is the model source and prisma/migrations/ adds important database-only constraints such as case-insensitive uniqueness, external-table RLS, and partial uniqueness for open payouts. Application statuses are mostly strings rather than Prisma enums; callers must preserve exact spellings.

erDiagram
    WebUser ||--o{ AuthAccount : authenticates_with
    WebUser ||--o{ Submission : creates
    WebUser ||--o{ LinkedSocialAccount : proves_ownership
    Campaign ||--o{ Submission : receives
    Submission ||--o{ ViewSnapshot : measured_by
    Submission ||--o{ PayoutItem : paid_through
    WebUser ||--o{ Payout : requests
    Payout ||--o{ PayoutItem : contains
    WebUser ||--o| TaxForm : current_form
    TaxForm ||--o{ TaxFormSubmission : retains
    TaxForm ||--o{ TaxFormStateTransition : audits
    TaxFormSubmission ||--o{ Payout : snapshotted_by
    WebUser ||--o{ Referral : referrer
    WebUser ||--o| Referral : referred_user
    Payout ||--o| ReferralCommission : triggers
    WebUser ||--o{ ReferralCommission : earns

Core entities

WebUser and AuthAccount

WebUser is the application identity. Important fields include legacy discordId, normalized email, profile/onboarding fields, jwtVersion, TOS acceptance, preferred payout method, tax/address data, and referral linkage. AuthAccount represents Discord or Google provider accounts and stores encrypted provider tokens. Provider/account ID is unique; a user can have multiple providers.

Important constraints exist in migrations for case-insensitive handles, normalized phone, and normalized email. Major mutations are owned by src/api/routes/auth.ts, onboarding.ts, users.ts, moderation admin routes, and tax recollection utilities.

Campaign

Represents an operational paid creator campaign—not a public marketing case study. Key fields are name/game, string-formatted budget/rates, platform/video-type gates, minimum views, caps, deadline, submission toggles, tracking duration, fee rate, and budget-freeze state. A campaign has submissions and legacy Discord user channels.

active, acceptingSubmissions, paused, isDeleted, and viewsFrozen are separate switches. Their combinations matter and are not represented by one status enum.

Precise campaign rates are append-only CampaignRateVersion rows effective at a timestamp. CPM and creator RPM are independent DECIMAL(20,6) USD-per-1,000-view values, with nullable long-form variants. API values are canonical decimal strings. The rate migration creates an initial version for every existing campaign: safely parsed payout text becomes RPM, CPM receives the same neutral break-even value, and malformed payout text receives a $0.30 CPM/RPM default. Runtime APIs have no legacy migration state or fallback path.

Submission

Links a user/video to a campaign. It retains both userId (historically Discord) and nullable webUserId. Metrics have several layers: initial/current counts, optional manual override, optional frozen count, polling fields, and snapshots. customRate/customViewCap override campaign rules. Legacy one-shot payout fields coexist with cumulative delta-payout fields.

Lifecycle: PENDING → ACCEPTED | DENIED | FLAGGED. Tracking is independent of moderation status: every submitted clip remains eligible until its submission window expires or its campaign finishes. Polling becomes less frequent as the clip ages. Scrape failures may still create operational stop/flag outcomes, and payout-item review may deny/flag the submission without itself ending metric collection.

New individual RPM overrides are append-only SubmissionRateVersion rows. A version with null RPM explicitly removes the override prospectively. The legacy Float customRate is retained for historical reads but is not written by the precise-rate endpoint.

LinkedSocialAccount and verification challenges

LinkedSocialAccount proves one WebUser owns a platform account; (platform, platformAccountId) is globally unique. PendingVerification stores a ten-minute bio code and is unique per user/platform/account. VerificationAttempt provides the rolling failure/lockout audit. These are mutated by src/api/routes/verifications.ts after platform profile scraping.

Payout and PayoutItem

Payout is both a legacy transfer record and the aggregate for the current review/send workflow. Current lifecycle:

REQUESTED → SCRAPING → READY_FOR_REVIEW → AWAITING_SEND → PROCESSING → COMPLETED
                    ↘ BELOW_THRESHOLD / FAILED
READY_FOR_REVIEW → REJECTED

Legacy rows use PENDING, PROCESSING, COMPLETED, and FAILED. Migrations enforce at most one open current-flow payout per creator, including AWAITING_SEND.

PayoutItem snapshots views, caps, prior paid views, rate, gross/net amount, scrape failure reason, badges, and admin decision (PENDING, APPROVED, REJECTED, FLAGGED). Approval increments cumulative submission payout fields before the separate send step; terminal rail failure includes reversal logic.

Payment methods

StripeAccount, PayPalAccount, and UsdtPayoutMethod are one-per-user destinations. Legacy Discord IDs still appear in Stripe/PayPal. PayPal schema retains OAuth token columns, while current route comments describe an email-based flow. PaymentSystemConfig is a singleton for PayPal cycle volume and circuit-breaker state.

Tax entities

TaxForm is mutable current state; TaxFormSubmission is an immutable submission/PDF/TIN-verification record; TaxFormStateTransition is append-only history. Important states are:

pending_submission, submitted, pending_verification, verified,
mismatch, verification_error, manually_verified, manually_failed,
manual_review_required, invalidated, expired

verified and manually_verified are the accepted form states. W-8BEN expiration and retention timestamps are explicit. FtinCountryConfig controls country-specific FTIN guidance.

Referrals

Referral is one immutable attribution per referred user and records campaign-program cohort plus anti-fraud evidence. ReferralCommission is a payout-triggered accrual with PENDING → PAID; unique sourcePayoutId prevents duplicate accrual. ReferralLinkAlias maps admin-created slugs to a canonical user referral code.

Communication and operations

• Announcement fans out to UserNotification; preferences are one per user.
• WhopIdentity is the one-to-one BloxClips WebUser to Whop connected-account owner mapping. Support conversation data belongs to Whop and is not stored locally.
• BookingRequest, BookingAvailabilityConfig, ContactAttempt, FeaturedGame, and FeaturedService support marketing operations.
• AdminAuditLog and newer AuditLog are distinct ledgers.
• ExternalLeaderboardPayment represents off-platform payout history matched optionally to a user.
• GuildConfig stores one Discord guild/channel configuration row.

Tables intentionally outside the diagram

Discord tickets/bans, notifications, marketing records, and operational singleton tables matter but do not define the main campaign-to-payout path. PV tracker records are not database entities at all; they live in JSON state under assets/.

Difficult-to-infer or drifted fields

• Monetary campaign values are strings (budget, payout, payoutLong) and parsed in utilities; their accepted formats are an application contract.
• Submission.userId and several notification/payment userId fields historically mean Discord ID, while newer code prefers webUserId.
• Payout.amount means net on current rows but may equal gross on legacy rows; nullable gross/fee fields distinguish them.
• PayPalAccount retains token fields even though current comments describe no OAuth.
• UserChannel and Ticket belong to Discord-era workflows; their current production relevance is unknown.
• Many state fields are unvalidated database strings, so mutation code—not the schema—owns allowed transitions.