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

Core Prisma models involved in the submission system.

---

Submission

Table: Submission
Source: prisma/schema.prisma:316-382

Purpose

Core entity representing a clipper's video entry into a campaign.

Key Fields

Field	Type	Nullable	Default	Description
id	Int	No	autoincrement	Primary key
userId	String	No	—	Discord ID (legacy) or webUserId fallback
webUserId	String?	Yes	—	WebUser FK (preferred)
username	String	No	—	Clipper handle at submission time
videoLink	String	No	—	Original video URL
videoTitle	String?	Yes	—	From platform API
previewVideoUrl	String?	Yes	—	Direct media URL for moderation
previewImageUrl	String?	Yes	—	Thumbnail for previews
initialViews	Int	No	0	Views at submission time
currentViews	Int	No	0	Latest scraped views
manualViewCount	Int?	Yes	—	Admin override (takes priority)
initialLikes	Int	No	0	Likes at submission
currentLikes	Int	No	0	Latest scraped likes
currentComments	Int	No	0	Latest scraped comments
platform	String	No	"youtube"	youtube/tiktok/instagram
status	String	No	"PENDING"	PENDING/ACCEPTED/DENIED/FLAGGED
campaignId	Int	No	—	Campaign FK
messageId	String?	Yes	—	Legacy Discord log message ID
videoType	String	No	"short"	short/long
duration	Int	No	0	Video duration (seconds)
customRate	Float?	Yes	—	Custom RPM per 1k views (overrides campaign)
customViewCap	Int?	Yes	—	Custom view cap (overrides campaign)
addedByAdmin	Boolean	No	false	Manually added by staff
frozenViewCount	Int?	Yes	—	Budget clamp ceiling (payable views locked)
acceptedAt	DateTime?	Yes	—	When approved (drives polling)
lastPolledAt	DateTime?	Yes	—	Last successful scrape
nextPollAt	DateTime?	Yes	—	Scheduler target
trackingStoppedAt	DateTime?	Yes	—	Tracking ended (expiry/flag/freeze)
consecutiveScrapeFailures	Int	No	0	Auto-flag at 3
creatorHandle	String?	Yes	—	Author handle (captured at accept)
postedAt	DateTime?	Yes	—	Video publish date
paidOut	Boolean	No	false	Legacy single-shot payout flag
paidAmount	Float?	Yes	—	Legacy payout amount
paidAt	DateTime?	Yes	—	Legacy payout timestamp
payoutId	String?	Yes	—	Legacy Payout FK
paidViewsTotal	Int	No	0	Cumulative views cashed out (delta flow)
paidAmountTotal	Float	No	0	Cumulative dollars cashed out (delta flow)
lastPaidAt	DateTime?	Yes	—	Last delta payout
createdAt	DateTime	No	now()	Creation timestamp

Relationships

Relation	Target	Type	Cascade
webUser	WebUser	Many-to-1	—
campaign	Campaign	Many-to-1	—
viewSnapshots	ViewSnapshot	1-to-Many	Cascade delete
payoutItems	PayoutItem	1-to-Many	Cascade delete
payout	Payout	Many-to-1 (legacy)	—

Indexes

@@index([nextPollAt, status])      // Scheduler query
@@index([userId, status])          // User dashboard
@@index([campaignId, status])      // Campaign admin, budget calc
@@index([status, createdAt])       // Admin queue
@@index([videoLink])               // Duplicate detection

Field Behavior Notes

Field	Changing It Affects
status	Tracking eligibility, payout eligibility, UI visibility
currentViews	Earnings calc, budget clamp, payout delta
manualViewCount	Overrides currentViews/frozenViewCount for earnings & payout
frozenViewCount	Hard ceiling on payable views; stops tracking writes
customRate	Overrides campaign payout for this submission
customViewCap	Overrides campaign viewCap for this submission
acceptedAt	Polling cadence (12h/24h), tracking expiry (30 days)
paidViewsTotal	Baseline for delta payout math
consecutiveScrapeFailures	At 3 → auto FLAGGED, tracking stopped

---

Campaign

Table: Campaign
Source: prisma/schema.prisma:270-314

Key Fields (Submission-Relevant)

Field	Type	Description
id	Int	PK
name	String	Unique
budget	String	e.g. "$5000" — parsed by parseBudget
payout	String	Short-form RPM, e.g. "$0.30"
payoutLong	String?	Long-form RPM
active	Boolean	Global on/off
acceptingSubmissions	Boolean	Auto-set false at 95% budget
deadline	DateTime?	Auto-close timestamp
allowedPlatforms	String	Comma-separated: "youtube,tiktok,instagram"
viewCap	Int?	Max views per short submission
longViewCap	Int?	Max views per long submission
minViewsShorts	Int?	Minimum views to qualify (short)
minViewsLong	Int?	Minimum views to qualify (long)
requireNewUploads	Boolean	Only videos uploaded after campaign start
trackingDurationDays	Int	Default 30 — polling window
platformFeeRate	Float	Default 0.10 — accelerates budget burn
maxTotalViews	BigInt?	Calculated budget ceiling
viewsFrozen	Boolean	Budget exhausted — stops tracking
viewsFrozenAt	DateTime?	When freeze occurred
paused	Boolean	Manual pause submissions

Relationships

• submissions — Submission[]

---

ViewSnapshot

Table: ViewSnapshot
Source: prisma/schema.prisma:648-660

Purpose

Historical view/like/comment data points for charting and audit.

Fields

Field	Type	Description
id	String	UUID
submissionId	Int	FK → Submission
viewCount	Int	Total views at snapshot
likes	Int?	Like count
comments	Int?	Comment count
snapshotDate	DateTime	When captured
createdAt	DateTime	Row insert time

Indexes

@@index([submissionId, snapshotDate])
@@index([snapshotDate])

Written By

1. Submission create (initial scrape) — submissions.ts:515-527
2. Tracking tick success — runTrackingTick.ts:102-110
3. Payout rescrape success — rescrape.ts:107-115

---

Payout

Table: Payout
Source: prisma/schema.prisma:432-506

Purpose

Tracks money movement to clippers. Two coexisting flows:

1. Legacy (admin-initiated, single-shot): Submission.paidOut=true, Payout.status ∈
2. Delta (user-initiated): Payout.status ∈

Key Fields

Field	Type	Description
id	String	UUID
userId	String	Discord ID (legacy)
webUserId	String?	WebUser FK
amount	Float	Net sent (after clipper fee + surcharge)
grossAmount	Float?	Pre-fee total
feeAmount	Float?	7% clipper fee
stripeSurchargeAmount	Float?	Stripe 2.5% on top
method	String	stripe/paypal/usdt
status	String	See flows above
scrapeStartedAt	DateTime?	Delta flow
scrapeCompletedAt	DateTime?	Delta flow
reviewedAt	DateTime?	Legacy /approve timestamp
reviewedBy	String?	Admin who reviewed
approvedAt	DateTime?	Phase 8: bookkeeping done
approvedBy	String?	Admin who approved
sentAt	DateTime?	Phase 8: money dispatched
sentBy	String?	Admin who sent
rejectionReason	String?	If REJECTED/FAILED
referralBonusAmount	Float?	Affiliate swept into this payout
taxFormSnapshotId	String?	TaxFormSubmission at approval

Relationships

• submissions — Submission[] (legacy)
• items — PayoutItem[] (delta flow)

---

PayoutItem

Table: PayoutItem
Source: prisma/schema.prisma:508-541

Purpose

One row per submission in a user-initiated payout. Holds the rescrape snapshot, delta math, and admin decision.

Fields

Field	Type	Description
id	String	UUID
payoutId	String	FK → Payout
submissionId	Int	FK → Submission
viewsAtPayout	Int	Platform-reported views at rescrape
payableViews	Int	min(viewsAtPayout, viewCap, manualViewCount?, frozenViewCount?)
priorPaidViews	Int	Submission.paidViewsTotal at item creation
viewsCounted	Int	payableViews - priorPaidViews (≥0)
ratePerK	Float	RPM applied
grossAmount	Float	viewsCounted/1000 * ratePerK
netAmount	Float	grossAmount * (1 - 7%)
decision	String	PENDING/APPROVED/REJECTED/FLAGGED
decisionReason	String?	Admin note
decidedBy	String?	Admin Discord ID
decidedAt	DateTime?	Decision timestamp
videoUnavailableReason	String?	VIDEO_DELETED/VIDEO_PRIVATE/SCRAPE_FAILED
badges	String	Comma-separated: LOW_LIKE_RATIO,VIEW_SPIKE

Indexes

@@index([payoutId])
@@index([submissionId])

---

LinkedSocialAccount

Table: LinkedSocialAccount
Source: prisma/schema.prisma:667-680

Purpose

Proves a WebUser owns a social account (bio-code verification). Required before first submission per platform/account.

Fields

Field	Type	Description
id	String	CUID
webUserId	String	FK → WebUser
platform	String	youtube/tiktok/instagram
platformAccountId	String	YouTube channelId, TikTok authorMeta.id, IG ownerId
handle	String	Display handle
normalizedHandle	String	Lowercase for fallback matching
verifiedAt	DateTime	When verified

Unique Constraint

@@unique([platform, platformAccountId])

Enforcement: submissions.ts:438-459 — checks before create; returns 412 if missing, 403 if owned by another user.

---

WebUser (Submission-Relevant Fields)

Source: prisma/schema.prisma:31-129

Field	Relevance
id	Primary key (CUID)
discordId	Legacy userId on Submission
email / emailVerified	Email gate (currently disabled)
referralCode	Affiliate attribution
preferredPaymentMethod	Payout rail selection
isUSPerson	Tax routing hint
submissions	Submission[] via webUserId
linkedSocialAccounts	LinkedSocialAccount[]

---

UserChannel

Table: UserChannel
Source: prisma/schema.prisma:384-395

Purpose

Discord DM channel per user per campaign for expiry reports.

Field	Type	Description
id	Int	PK
userId	String	Discord ID
webUserId	String?	WebUser FK
campaignId	Int	FK → Campaign
channelId	String	Discord channel ID
reportSent	Boolean	Expiry report delivered

Unique

@@unique([userId, campaignId])

---

Relationship Diagram

erDiagram
    WebUser ||--o{ Submission : "webUserId"
    WebUser ||--o{ LinkedSocialAccount : "webUserId"
    WebUser ||--o{ Payout : "webUserId"
    WebUser ||--o{ UserChannel : "webUserId"
    Campaign ||--o{ Submission : "campaignId"
    Campaign ||--o{ UserChannel : "campaignId"
    Submission ||--o{ ViewSnapshot : "submissionId"
    Submission ||--o{ PayoutItem : "submissionId"
    Payout ||--o{ PayoutItem : "payoutId"
    Payout }o--|| TaxFormSubmission : "taxFormSnapshotId"

---

Enum-Like String Fields (Not Prisma Enums)

Model	Field	Values Observed
Submission	status	PENDING, ACCEPTED, DENIED, FLAGGED
Submission	platform	youtube, tiktok, instagram
Submission	videoType	short, long
Payout	status	REQUESTED, SCRAPING, READY_FOR_REVIEW, BELOW_THRESHOLD, AWAITING_SEND, PROCESSING, COMPLETED, REJECTED, FAILED, PENDING (legacy)
Payout	method	stripe, paypal, usdt
PayoutItem	decision	PENDING, APPROVED, REJECTED, FLAGGED
Campaign	allowedPlatforms	CSV of youtube/tiktok/instagram
UserNotification	type	SUBMISSION_ACCEPTED, CAMPAIGN_LAUNCH, PAYOUT_SENT, PAYOUT_REJECTED, ADMIN_WARNING, etc.

---

Cascade Behavior

Parent Delete	Child Effect
Campaign	Submissions: RESTRICT (FK required)
WebUser	Submissions: SET NULL on webUserId (but userId remains)
Submission	ViewSnapshots: CASCADE
Submission	PayoutItems: CASCADE
Payout	PayoutItems: CASCADE
LinkedSocialAccount	— (WebUser cascade)

---

Budget-Aware Earnings Calculation

Source: src/utils/calculateSubmissionEarnings.ts

Not stored — computed on read for dashboard, admin, payout.

Inputs per submission: currentViews, manualViewCount, frozenViewCount, customRate, customViewCap, videoType, createdAt, campaign config

Algorithm: First-come-first-earned by createdAt; budget burn accelerated by 1/(1-platformFeeRate).

Output: cappedEarnings (what clipper actually earns), isCapped (budget limited).