docs/data-models.md

Vault - Data Models

Generated: 2026-02-11
ORM: Drizzle ORM 0.45.1
Database: PostgreSQL (Supabase)

---

Schema Overview

The database schema is defined in src/lib/db/schema.ts (590 lines).

Entity Relationship Diagram

┌─────────────┐       ┌──────────────────┐       ┌─────────────────┐
│    users    │───┬───│ youtubeChannels  │───┬───│  youtubeVideos  │
└─────────────┘   │   └──────────────────┘   │   └────────┬────────┘
                  │                          │            │
                  │   ┌──────────────────┐   │            │
                  └───│    sessions      │   │   ┌────────┴────────┐
                  │   └──────────────────┘   │   │ detectedObjects │
                  │                          │   └────────┬────────┘
                  │   ┌──────────────────┐   │            │
                  └───│    accounts      │   │   ┌────────┴────────┐
                  │   └──────────────────┘   │   │marketplaceMatches│
                  │                          │   └────────┬────────┘
                  │   ┌──────────────────┐   │            │
                  └───│  verifications   │   │   ┌────────┴────────┐
                      └──────────────────┘   │   │  productClicks  │
                                             │   └─────────────────┘
                                             │
                                             │   ┌─────────────────┐
                                             │   │ interestPledges │
                                             │   └─────────────────┘
                                             │
                                             │   ┌─────────────────┐
                                             │   │ bountyPledges   │
                                             │   └─────────────────┘
                                             │
                                             │   ┌─────────────────┐
                                             └───│ productRequests │
                                                 └─────────────────┘

┌─────────────────┐     ┌─────────────────────┐
│ adminModeration │─────│   detectedObjects   │
└─────────────────┘     └─────────────────────┘

┌─────────────────────┐     ┌─────────────────┐
│ affiliateProposals  │─────│  youtubeVideos  │
└─────────────────────┘     └─────────────────┘

---

Core Tables

Users & Authentication

users

Better Auth user table.

Column	Type	Constraints	Description
id	text	PRIMARY KEY	User ID
name	text	NOT NULL	Display name
email	text	NOT NULL, UNIQUE	Email address
emailVerified	boolean	NOT NULL	Email verification status
image	text	NULLABLE	Avatar URL
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Update timestamp

session

Better Auth session table.

Column	Type	Constraints	Description
id	text	PRIMARY KEY	Session ID
expiresAt	timestamp	NOT NULL	Expiration time
token	text	NOT NULL, UNIQUE	Session token
userId	text	NOT NULL, FK → users.id	User reference
ipAddress	text	NULLABLE	Client IP
userAgent	text	NULLABLE	Client user agent
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Update timestamp

account

Better Auth OAuth account table.

Column	Type	Constraints	Description
id	text	PRIMARY KEY	Account ID
accountId	text	NOT NULL	Provider account ID
providerId	text	NOT NULL	Provider name (e.g., "google")
userId	text	NOT NULL, FK → users.id	User reference
accessToken	text	NULLABLE	OAuth access token
refreshToken	text	NULLABLE	OAuth refresh token
idToken	text	NULLABLE	OAuth ID token
accessTokenExpiresAt	timestamp	NULLABLE	Token expiration
refreshTokenExpiresAt	timestamp	NULLABLE	Refresh token expiration
scope	text	NULLABLE	OAuth scopes
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Update timestamp

verification

Better Auth verification table.

Column	Type	Constraints	Description
id	text	PRIMARY KEY	Verification ID
identifier	text	NOT NULL	Email or identifier
value	text	NOT NULL	Verification code
expiresAt	timestamp	NOT NULL	Expiration time
createdAt	timestamp	NULLABLE	Creation timestamp
updatedAt	timestamp	NULLABLE	Update timestamp

---

Content Tables

youtube_channels

Creator's connected channels (YouTube, Instagram, TikTok, Facebook).

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Internal channel ID
creatorId	text	NOT NULL, FK → users.id	Owner user ID
channelId	text	NOT NULL	Platform channel ID
channelName	text	NOT NULL	Display name
creatorSlug	text	NOT NULL, UNIQUE	URL-friendly slug
subscriberCount	integer	NULLABLE	Follower count
thumbnailUrl	text	NULLABLE	Avatar URL
connectedAt	timestamp	NOT NULL, DEFAULT NOW	Connection time
syncStatus	enum	NOT NULL, DEFAULT 'idle'	idle, syncing, errored
channelType	enum	NOT NULL, DEFAULT 'external'	verified, external
platform	enum	NOT NULL, DEFAULT 'youtube'	youtube, instagram, tiktok, facebook

Indexes:

• creator_channel_idx UNIQUE (creatorId, channelId)

youtube_videos

Videos from connected channels.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Internal video ID
channelId	text	NOT NULL, FK → youtube_channels.id	Parent channel
videoId	text	NOT NULL	Platform video ID
title	text	NOT NULL	Video title
description	text	NULLABLE	Video description
thumbnailUrl	text	NULLABLE	Thumbnail URL
duration	text	NULLABLE	ISO 8601 duration (PT15M33S)
viewCount	integer	NULLABLE	View count
availabilityStatus	text	NOT NULL, DEFAULT 'unknown'	available, private, unknown
scanStatus	enum	NOT NULL, DEFAULT 'pending'	See scan status enum
publishedAt	timestamp	NULLABLE	Publication date
platform	enum	NOT NULL, DEFAULT 'youtube'	Platform enum
url	text	NULLABLE	Canonical URL (non-YouTube)
width	integer	NULLABLE	Video width
height	integer	NULLABLE	Video height
isInWorkbench	boolean	NOT NULL, DEFAULT true	Workbench status
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Scan Status Enum: pending_analysis, in_progress, awaiting_approval, completed, failed, idle, pending

Indexes:

• channel_video_idx UNIQUE (channelId, videoId)

video_scan_jobs

Progress tracking for archive scans.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Job ID
channelId	text	NOT NULL, FK → youtube_channels.id	Channel being scanned
userId	text	NOT NULL, FK → users.id	User who initiated
status	enum	NOT NULL, DEFAULT 'pending'	Scan status enum
progress	integer	NOT NULL, DEFAULT 0	Progress percentage (0-100)
totalVideos	integer	NULLABLE	Total videos to scan
scannedVideos	integer	NOT NULL, DEFAULT 0	Videos processed
errorMessage	text	NULLABLE	Error message if failed
inngestRunId	text	NULLABLE	Inngest run ID
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

---

Detection Tables

detected_objects

AI-detected products in videos.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Detection ID
videoId	text	NOT NULL, FK → youtube_videos.id	Parent video
objectName	text	NOT NULL	Detected object name
category	enum	NOT NULL	Category enum
confidenceScore	real	NOT NULL	AI confidence (0.0-1.0)
frameTimestamp	integer	NOT NULL	Seconds into video
detectionMetadata	jsonb	NULLABLE	Bounding box, model info
thumbnailUrl	text	NULLABLE	Cropped thumbnail URL
status	enum	NOT NULL, DEFAULT 'pending_review'	Detection status
moderationStatus	enum	NOT NULL, DEFAULT 'PENDING'	Moderation status
moderatedAt	timestamp	NULLABLE	Moderation timestamp
moderatedBy	text	NULLABLE, FK → users.id	Moderator user ID
moderationMetadata	jsonb	NULLABLE	Edit history, notes
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Category Enum: Tech, Fashion, Furniture, Audio, Other, Person, Apparel

Detection Status Enum: pending_review, approved, rejected, flagged

Moderation Status Enum: PENDING, APPROVED, REJECTED

---

Marketplace Tables

marketplace_matches

Product listings matched to detected objects.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Match ID
objectId	text	NOT NULL, FK → detected_objects.id	Detection reference
marketplace	enum	NOT NULL	amazon, ebay, etsy
productId	text	NOT NULL	ASIN, eBay Item ID, Etsy Listing ID
productName	text	NOT NULL	Product title
price	real	NOT NULL	Price in USD
availabilityStatus	enum	NOT NULL	IN_STOCK, SOLD_OUT, DISCONTINUED
affiliateUrl	text	NOT NULL	Affiliate link
imageUrl	text	NULLABLE	Product image URL
linkStatus	enum	NOT NULL, DEFAULT 'ACTIVE'	Link health status
lastCheckedAt	timestamp	NULLABLE	Last health check
checkAttempts	integer	NOT NULL, DEFAULT 0	Check retry count
checkMetadata	jsonb	NULLABLE	HTTP status, error info
matchedAt	timestamp	NOT NULL, DEFAULT NOW	Match timestamp
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Link Status Enum: ACTIVE, CHECKING, BROKEN

Indexes:

• marketplace_matches_link_status_idx (linkStatus)
• marketplace_matches_last_checked_at_idx (lastCheckedAt)

product_clicks

Affiliate link click tracking.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Click ID
marketplaceMatchId	text	NOT NULL, FK → marketplace_matches.id	Match reference
clickedAt	timestamp	NOT NULL, DEFAULT NOW	Click timestamp
viewerIp	text	NULLABLE	Anonymized IP
userAgent	text	NULLABLE	Client user agent
referrer	text	NULLABLE	Referrer URL
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Indexes:

• product_clicks_marketplace_match_id_idx (marketplaceMatchId)
• product_clicks_clicked_at_idx (clickedAt)

---

Interest & Demand Tables

interest_pledges

"I want this" waitlist entries.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Pledge ID
marketplaceMatchId	text	NULLABLE, FK → marketplace_matches.id	Match reference
detectedObjectId	text	NULLABLE, FK → detected_objects.id	Detection reference
emailHash	text	NOT NULL	SHA-256 hash for deduplication
emailEncrypted	text	NOT NULL	AES-256 encrypted email
status	enum	NOT NULL, DEFAULT 'ACTIVE'	ACTIVE, NOTIFIED, EXPIRED
consentTimestamp	timestamp	NOT NULL, DEFAULT NOW	GDPR consent time
consentIp	text	NULLABLE	Anonymized consent IP
unsubscribeToken	text	NOT NULL, UNIQUE	One-click unsubscribe
consentMetadata	jsonb	NULLABLE	User agent, referrer
notifiedAt	timestamp	NULLABLE	Notification timestamp
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Indexes:

• interest_pledges_marketplace_match_id_idx (marketplaceMatchId)
• interest_pledges_detected_object_id_idx (detectedObjectId)
• interest_pledges_unsubscribe_token_idx UNIQUE (unsubscribeToken)

bounty_pledges

Monetary pledges for rare items.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Pledge ID
productId	text	NOT NULL	Product identifier
marketplaceMatchId	text	NOT NULL, FK → marketplace_matches.id	Match reference
emailHash	text	NOT NULL	SHA-256 hash
encryptedEmail	text	NOT NULL	AES-256 encrypted
pledgeAmount	real	NOT NULL	Pledge in USD
currency	text	NOT NULL, DEFAULT 'USD'	Currency code
status	enum	NOT NULL, DEFAULT 'ACTIVE'	ACTIVE, WITHDRAWN, FULFILLED
consentTimestamp	timestamp	NOT NULL, DEFAULT NOW	Consent time
consentIp	text	NULLABLE	Consent IP
unsubscribeToken	text	NOT NULL, UNIQUE	Unsubscribe token
notifiedAt	timestamp	NULLABLE	Notification time
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Indexes:

• bounty_pledges_product_id_idx (productId)
• bounty_pledges_status_idx (status)
• bounty_pledges_email_hash_idx (emailHash)

---

Revenue & Admin Tables

affiliate_revenue

Affiliate commission tracking.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Revenue ID
marketplaceMatchId	text	NOT NULL, FK → marketplace_matches.id	Match reference
amount	real	NOT NULL	Commission in USD
recordedAt	timestamp	NOT NULL, DEFAULT NOW	Recording time
orderId	text	NULLABLE	Marketplace order ID
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

Indexes:

• affiliate_revenue_marketplace_match_id_idx (marketplaceMatchId)
• affiliate_revenue_recorded_at_idx (recordedAt)

admin_moderation

Admin moderation actions.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Action ID
detectionId	text	NOT NULL, FK → detected_objects.id	Detection reference
adminId	text	NOT NULL, FK → users.id	Admin user
action	enum	NOT NULL	corrected, marked_incorrect, approved, rejected
reasonCode	enum	NULLABLE	wrong_object, wrong_category, false_positive, unclear_image, duplicate, out_of_scope
originalValues	jsonb	NOT NULL	Snapshot before action
correctedValues	jsonb	NULLABLE	New values (if corrected)
trainAiFlag	boolean	NOT NULL, DEFAULT true	Future: AI feedback
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

---

Request & Proposal Tables

product_requests

Viewer product requests.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Request ID
videoId	text	NOT NULL, FK → youtube_videos.id	Video reference
creatorId	text	NOT NULL, FK → users.id	Creator reference
viewerName	text	NULLABLE	Viewer name
viewerEmail	text	NULLABLE	Viewer email
note	text	NOT NULL	Request message
imageUrl	text	NULLABLE	Image snapshot
frameTimestamp	integer	NULLABLE	Video timestamp
status	enum	NOT NULL, DEFAULT 'PENDING'	PENDING, FULFILLED, DISMISSED
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

affiliate_proposals

User-submitted affiliate links.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Proposal ID
objectId	text	NULLABLE, FK → detected_objects.id	Detection reference
videoId	text	NOT NULL, FK → youtube_videos.id	Video reference
creatorId	text	NOT NULL, FK → users.id	Creator reference
submitterName	text	NULLABLE	Submitter name
submitterEmail	text	NULLABLE	Submitter email
productUrl	text	NOT NULL	Original product URL
affiliateUrl	text	NOT NULL	Affiliate URL
productName	text	NOT NULL	Product name
price	real	NULLABLE	Price
imageUrl	text	NULLABLE	Product image
note	text	NULLABLE	Submission note
status	enum	NOT NULL, DEFAULT 'PENDING'	PENDING, APPROVED, REJECTED
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

analysis_notifications

Email notifications for video analysis.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, DEFAULT uuid	Notification ID
videoId	text	NOT NULL, FK → youtube_videos.id	Video reference
emailHash	text	NOT NULL	SHA-256 hash
emailEncrypted	text	NOT NULL	AES-256 encrypted
status	enum	NOT NULL, DEFAULT 'PENDING'	PENDING, SENT
unsubscribeToken	text	NOT NULL	Unsubscribe token
createdAt	timestamp	NOT NULL, DEFAULT NOW	Creation timestamp
updatedAt	timestamp	NOT NULL, DEFAULT NOW	Update timestamp

---

Drizzle Relations

// Channel -> User
youtubeChannelsRelations.user = one(users)
youtubeChannelsRelations.videos = many(youtubeVideos)

// Video -> Channel
youtubeVideosRelations.channel = one(youtubeChannels)
youtubeVideosRelations.detections = many(detectedObjects)
youtubeVideosRelations.requests = many(productRequests)
youtubeVideosRelations.proposals = many(affiliateProposals)

// Detection -> Video
detectedObjectsRelations.video = one(youtubeVideos)
detectedObjectsRelations.marketplaceMatches = many(marketplaceMatches)

// Match -> Detection
marketplaceMatchesRelations.object = one(detectedObjects)

// Request -> Video, Creator
productRequestsRelations.video = one(youtubeVideos)
productRequestsRelations.creator = one(users)

// Proposal -> Video, Creator, Object
affiliateProposalsRelations.video = one(youtubeVideos)
affiliateProposalsRelations.creator = one(users)
affiliateProposalsRelations.object = one(detectedObjects)

---

TypeScript Type Exports

The schema exports TypeScript types for all tables:

// User types
export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

// Content types
export type YoutubeChannel = typeof youtubeChannels.$inferSelect;
export type YoutubeVideo = typeof youtubeVideos.$inferSelect;
export type VideoScanJob = typeof videoScanJobs.$inferSelect;

// Detection types
export type DetectedObject = typeof detectedObjects.$inferSelect;

// Marketplace types
export type MarketplaceMatch = typeof marketplaceMatches.$inferSelect;
export type ProductClick = typeof productClicks.$inferSelect;

// Interest types
export type InterestPledge = typeof interestPledges.$inferSelect;
export type BountyPledge = typeof bountyPledges.$inferSelect;

// Revenue types
export type AffiliateRevenue = typeof affiliateRevenue.$inferSelect;

// Request types
export type ProductRequest = typeof productRequests.$inferSelect;
export type AdminModerationRecord = typeof adminModeration.$inferSelect;

---

Related Documentation

• Project Overview - Architecture and features
• API Contracts - Endpoints using these models
• Inngest Workflows - Background jobs