Database Schema

CutX uses a single PostgreSQL database accessed by all three services through Cloudflare Hyperdrive.

Table Overview

Users & Auth

Table	Purpose
`users`	User accounts, roles, auth credentials
`magic_link_tokens`	Passwordless login tokens

users stores authentication data (email, password hash, GitHub ID), role (user, admin, owner), and status (active, suspended, banned).

Credits & Billing

Table	Purpose
`credit_balances`	Current balance per user
`credit_transactions`	Full audit trail of all credit movements
`subscriptions`	Stripe subscription state and tier

A database trigger auto-creates a credit_balances row with 25 free credits when a user is inserted.

Generation Jobs

Table	Purpose
`generation_jobs`	Job state, type, input params, output
`assets`	Generated outputs (images, video, copy, audio)

generation_jobs tracks the full lifecycle:

type — copy, static_ad, ugc_video, tts, etc.
status — pending through completed/failed
input_params — JSONB with generation parameters
output_url — final asset URL
replicate_prediction_id — external job reference
idempotency_key — unique per user to prevent duplicates
credits_cost — credits deducted for this job

Products & Projects

Table	Purpose
`projects`	Organizational containers for products
`products`	Ingested product data (name, description, URL)
`product_images`	Product media files

Campaigns

Table	Purpose
`campaigns`	Ad campaigns across platforms
`ad_platform_connections`	OAuth tokens for Meta/TikTok/Google
`campaign_analytics`	Daily performance metrics
`campaign_assets`	Creative-to-campaign associations
`ad_spend_fees`	Platform fee tracking

Landing Pages

Table	Purpose
`landing_pages`	Published landing pages with subdomains

Whatnot Seller

Table	Purpose
`inventory_items`	Seller inventory with cost/value tracking
`shows`	Live stream events
`show_items`	Items assigned to shows with pricing
`payout_imports`	Uploaded payout CSVs
`payout_line_items`	Individual sale records from payouts
`competitor_streams`	Competitor tracking data

Admin

Table	Purpose
`admin_audit_log`	All privileged admin actions

Key Relationships

users
  ├── credit_balances (1:1)
  ├── credit_transactions (1:many)
  ├── subscriptions (1:many)
  ├── generation_jobs (1:many)
  │     └── assets (1:many)
  ├── projects (1:many)
  │     └── products (1:many)
  │           └── product_images (1:many)
  ├── campaigns (1:many)
  │     ├── campaign_analytics (1:many)
  │     └── campaign_assets (1:many)
  ├── ad_platform_connections (1:many)
  ├── inventory_items (1:many)
  ├── shows (1:many)
  │     └── show_items (many:many with inventory)
  └── landing_pages (1:many)

Important Indexes

Index	Table	Columns	Purpose
`idx_users_email`	users	email	Login lookup
`idx_users_github`	users	github_id	OAuth lookup
`idx_jobs_user_status`	generation_jobs	user_id, status	Dashboard queries
`idx_jobs_replicate`	generation_jobs	replicate_prediction_id	Webhook matching
`idx_jobs_idempotency`	generation_jobs	user_id, idempotency_key	Duplicate prevention (unique)
`idx_analytics_campaign_date`	campaign_analytics	campaign_id, date	Time-range queries
`idx_inventory_search`	inventory_items	name (trgm)	Full-text inventory search

Auto-Updated Timestamps

Ten tables have updated_at triggers that automatically set the timestamp on every UPDATE:

users, credit_balances, generation_jobs, products, campaigns, ad_platform_connections, landing_pages, inventory_items, shows, subscriptions

Cascade Deletes

Most foreign keys use ON DELETE CASCADE, meaning:

Deleting a user removes all their jobs, products, campaigns, etc.
Deleting a project removes all its products and product images
Deleting a show removes all show-item assignments