Database Reference

Full schema reference for the Affiliate Platform PostgreSQL database (hosted on Neon). All tables are managed by Prisma ORM. The database has two environments: staging and production — each is a separate Neon project.

---

Auth Tables

These tables are managed by BetterAuth and handle identity, sessions, and email verification.

User

The central user record for every account on the platform — both creators and admins.

Field	Column	Type	Notes
id	id	String (UUID)	Primary key
email	email	String	Login email. Unique. Read-only after creation.
emailVerified	emailVerified	Boolean	Set to true after first magic-link sign-in
name	name	String	Display name shown across the platform
profilePhotoUrl	image	String?	URL to profile photo in Vercel Blob storage
role	role	String	"user" (default) or "admin". Controls access to /admin.
tier	tier	Int	1, 2, or 3. Controls slug customization. See Auth → Creator Tiers.
legalName	legal_name	String?	Full legal name for payout records
phone	phone	String?	Phone number
bio	bio	String?	Creator bio
status	status	String	"active", "inactive", or "pending"
internalNotes	internal_notes	String?	Admin-only notes, not visible to creators
internalPlatforms	internal_platforms	TEXT[]	Array of platform keys. Controls dashboard access and magic-link email content. Values: "Affiliate Platform", "Ps-Workflow".
airtableEmail	airtable_email	String?	Email used in the Airtable creator database for sync matching
createdAt	createdAt	DateTime	Account creation timestamp
updatedAt	updatedAt	DateTime	Last update timestamp
lastSignedInAt	last_signed_in_at	DateTime?	Most recent sign-in timestamp

→ See Auth, Admin Panel → Users, Creator Experience → Settings

---

Session

Active authenticated sessions. One row per active sign-in.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
token	String	Session token stored in the browser cookie
expiresAt	DateTime	When the session expires
ipAddress	String?	IP address at sign-in time
userAgent	String?	Browser/device user agent string
createdAt	DateTime
updatedAt	DateTime

→ Created on every sign-in (magic link or Google OAuth). Also created when an admin impersonates a creator. See Auth → Sessions.

---

Verification

Short-lived tokens used for magic-link emails. One row per pending verification.

Field	Type	Notes
id	String	Primary key
identifier	String	The email address being verified
value	String	The one-time token embedded in the magic-link URL
expiresAt	DateTime	Token expiry — short window (minutes)
createdAt	DateTime?
updatedAt	DateTime?

→ Created when a creator requests a magic link. Consumed (deleted) on first use. See Auth.

---

Creator Tables

Profile data, channels, and platform associations for creator accounts.

CreatorChannel

Social media channels a creator has registered for link generation.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
platform	String	e.g. "youtube", "tiktok", "instagram_reels"
handle	String	Creator's channel name / handle
channelId	String?	Platform-specific channel ID
url	String?	URL to the channel
isPrimary	Boolean	Whether this is the default channel for link generation
followerCount	Int?	Follower/subscriber count (informational)
createdAt	DateTime
updatedAt	DateTime

→ Populated during onboarding and editable from Settings → Channels. See Creator Experience.

---

CreatorPlatform

Alternative creator channel table (legacy/parallel to CreatorChannel).

Field	Column	Type	Notes
id	id	String	Primary key
userId	user_id	String	FK → User.id
platform	platform	Enum	Platform enum value
handle	handle	String	Creator handle
url	url	String?	Channel URL
isPrimary	is_primary	Boolean	Primary channel flag
createdAt	created_at	DateTime
updatedAt	updated_at	DateTime

→ Referenced by OfferApproval.creatorPlatformIds when a creator requests offer access.

---

UserSecondaryEmail

Additional email addresses associated with a user account.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
email	String	Secondary email address
createdAt	DateTime

---

Offer Tables

Financial product offers available on the platform, their providers, access controls, and approval tracking.

Offer

Each row represents one financial product offer (e.g. "SoFi Personal Loans", "QuickBooks").

Field	Type	Notes
id	String	Primary key
name	String	Display name of the offer
brand	String?	Brand/company name
description	String?	Description shown to creators
category	String	One of: investing, credit_cards, banking, insurance, loans, business, general
featured	Boolean	If true, shown in the Featured Offers grid on creator dashboards
status	String	"active" or "inactive"
visibility	String	"public" (visible to all creators) or "private" (allowlist-only)
approvalType	String	"none" (auto-approve), "standard" (admin review), "approval_required" (review + admin-provided URL)
imageUrl	String?	Offer logo/image URL
slug	String?	URL-safe identifier used in routing
payout	String?	Human-readable payout description (e.g. "Up to $150 per conversion")
conversionEvent	String?	What counts as a conversion (e.g. "Approved application")
howToPromote	String?	Admin notes on best promotion practices, shown to creators
createdAt	DateTime
updatedAt	DateTime

→ See Offers & Providers, Admin Panel → Offers.

---

OfferProvider

One row per affiliate network/partner configured for an offer. An offer can have multiple providers.

Field	Type	Notes
id	String	Primary key
offerId	String	FK → Offer.id
displayName	String	Human-readable provider name
dataSource	String	How links are generated. Values: custom, manual, redeventures, money_com, bankrate, quinnstreet, impact, partnerstack
defaultWeight	Int	Percentage of traffic this provider receives (0–100). All active providers for an offer must sum to 100.
active	Boolean	Inactive providers are excluded from weight validation and traffic routing
affiliateUrlTemplate	String?	For custom data source — URL with {slug} placeholder
baseUrl	String?	For RedVentures/Money.com/Bankrate/QuinnStreet — base URL to append creator slug to
loanPurpose	String?	For QuinnStreet/Bankrate — loan purpose parameter
configJson	JSON?	For impact: { campaignId, adId }. For partnerstack: { partnershipKey, dest, destinationGateKey }
createdAt	DateTime
updatedAt	DateTime

→ See Offers & Providers → Data Sources, Admin Panel → Providers tab.

---

OfferAllowlist

Restricts offer visibility to specific creators. If any rows exist for an offer, only listed creators can see it.

Field	Type	Notes
id	String	Primary key
offerId	String	FK → Offer.id
userId	String	FK → User.id — the creator being granted access
createdAt	DateTime

→ See Offers & Providers → Offer Allowlist, Admin Panel → Details tab.

---

OfferApproval

Tracks a creator's access request to an offer that requires approval.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
offerId	String	FK → Offer.id
offerName	String	Snapshot of offer name at request time
creatorPlatformIds	TEXT[]	Array of CreatorPlatform IDs the creator selected when requesting
notes	String?	Creator-provided notes with the request
status	String	"pending", "approved", "denied"
denialReason	String?	Admin-provided reason when denied
requestedAt	DateTime	When the creator submitted the request
reviewedAt	DateTime?	When an admin approved or denied it

→ See Offers & Providers → Approval Types, Admin Panel → Approvals, Creator Experience → Offers Page.

---

CreatorWeights

Per-creator traffic split overrides for an offer. When present, these override OfferProvider.defaultWeight for that specific creator.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
offerId	String	FK → Offer.id
weightsJson	JSON	Array of { providerId: string, weight: number }. Weights must sum to 100.
createdAt	DateTime

→ See Offers & Providers → Per-creator overrides, Admin Panel → Split tab.

---

Link Tables

Affiliate links created by (or for) creators, and the resolved destination URLs per provider.

AffiliateLink

One row per creator+offer+channel combination. The slug is what appears in secure.moneymatchup.com/{slug}.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
offerId	String	FK → Offer.id
slug	String	The URL slug (e.g. caleb-sofi-youtube). Used for routing and analytics matching.
slugStatus	String	"active", "pending", etc.
platform	String	Platform the link is for (e.g. "youtube", "tiktok")
channelId	String?	FK → CreatorChannel.id
label	String?	Optional display label
createdAt	DateTime
updatedAt	DateTime

→ See Creator Experience → Link Generation, Admin Panel → Links, Analytics.

---

GeneratedLink

The actual resolved affiliate URL for a specific link+provider combination. Created when a link is generated (for auto-generate providers) or when a manual offer is approved.

Field	Type	Notes
id	String	Primary key
affiliateLinkId	String	FK → AffiliateLink.id
providerId	String	FK → OfferProvider.id
dataSource	String	Mirrors the provider's data source type
link	String?	The actual destination URL. Null for dynamic providers (Impact, PartnerStack) that resolve at click time. For manual offers, this is the admin-pasted URL.
createdAt	DateTime

→ Created at link generation time for most data sources. For manual offers, created when an admin approves the request. Deleting an OfferProvider cascades to delete its GeneratedLink rows.

---

Analytics

affiliate_analytics

One row per affiliate link + provider + date. Written by the daily analytics cron job.

Field	Column	Type	Notes
id	id	String	Primary key
userId	userId	String	FK → User.id — denormalized for fast creator-scoped queries
affiliateLinkId	affiliateLinkId	String	FK → AffiliateLink.id
providerId	providerId	String	FK → OfferProvider.id
date	date	Date	The calendar date this row covers
clicks	clicks	Int	Click count for this link+provider+date
conversions	conversions	Int	Conversion count
earnings	earnings	Decimal	Gross commission amount. Creators see earnings × 0.8 (agency keeps 20%).
createdAt	createdAt	DateTime
updatedAt	updatedAt	DateTime

→ See Analytics & Reporting, Admin Panel → Crons.

---

Support Tables

SupportTicket

Creator-submitted support requests.

Field	Type	Notes
id	String	Primary key
userId	String	FK → User.id
message	String	The creator's support message
category	String?	Ticket category (e.g. "links", "payouts", "account")
status	String	"open", "resolved", etc.
notes	String?	Internal admin notes
resolvedAt	DateTime?	When the ticket was marked resolved
createdAt	DateTime
updatedAt	DateTime

→ See Admin Panel → Support, Creator Experience → Support.

---

SupportReply

Admin replies to support tickets.

Field	Type	Notes
id	String	Primary key
ticketId	String	FK → SupportTicket.id
body	String	The reply text
createdAt	DateTime

→ Created when an admin replies from the Admin → Support panel. Replies are shown to the creator in their Support page.

---

Misc Tables

SponsoredVideo

Videos submitted to the platform that contain sponsored content. Used by the VidIQ integration and the sponsored video tracking system.

Field	Type	Notes
id	String	Primary key
socialMediaId	String?	Platform-native video ID
userId	String	FK → User.id
platform	String	"youtube", "youtube_shorts", "tiktok", etc. Auto-set to "youtube_shorts" if duration ≤ 60s
platformOption	String?	Sub-type (e.g. long-form vs shorts)
videoId	String?	YouTube video ID
link	String?	URL to the video
title	String?	Video title
description	String?	Video description
thumbnail	String?	Thumbnail URL
views	Int?	View count
likes	Int?	Like count
shares	Int?	Share count
comments	Int?	Comment count
duration	Int?	Video duration in seconds. Fetched via VidIQ API.
source	String?	How this record was created
affiliateLinkIds	TEXT[]	Array of AffiliateLink IDs featured in the video
offerIds	TEXT[]	Array of Offer IDs featured in the video
channelIds	TEXT[]	Array of channel IDs
storyLinks	TEXT[]	Story-related links
createdAt	DateTime
updatedAt	DateTime

→ See Integrations → VidIQ.

---

AppSession

Key-value store for long-lived application credentials and session tokens that need to persist across deploys and serverless cold starts.

Field	Type	Notes
key	String	Primary key. Currently one active key: "partnerstack_cookie"
value	String	The stored value (e.g. the _dwrf JWT from PartnerStack)
updatedAt	DateTime	Last time this value was written

Active keys:

Key	Value	Refreshed by
partnerstack_cookie	PartnerStack _dwrf session JWT	Weekly cron (every Monday 9am UTC)

→ See Integrations → PartnerStack, Admin Panel → PartnerStack Auth Cron.