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`](../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

```typescript
// 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:

```typescript
// 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](./project-overview.md) - Architecture and features
- [API Contracts](./api-contracts.md) - Endpoints using these models
- [Inngest Workflows](./inngest-workflows.md) - Background jobs