Database Schema Documentation

Complete documentation of the Sprout platform database schemas.

Overview

The Sprout platform uses PostgreSQL with Prisma ORM. There are two main database schemas:

• sprout_backend - Main application database
• sprout_etl - ETL service database (includes additional tables for geographic data)

sprout_backend Schema

Core Models

SkyboxInventory

Stores available inventory from Skybox platform.

model SkyboxInventory {
  id                  Int       @id @default(autoincrement())
  skybox_inventory_id Int       @unique
  skybox_event_id     Int
  listing_price       Decimal?  @db.Decimal(10, 2)
  event_date          DateTime?
  tags                String?
  metadata            Json
  created_at          DateTime  @default(now())
  last_update         DateTime  @updatedAt
  deleted_at          DateTime?
}

Key Fields:

• skybox_inventory_id - Unique Skybox inventory identifier
• skybox_event_id - Associated Skybox event ID
• listing_price - Current listing price
• metadata - Additional inventory data (JSON)

SkyboxSoldInventory

Stores sold inventory records from Skybox.

Structure: Similar to SkyboxInventory, tracks sold items.

AssignedSoldInventory

Tracks purchase assignments and order confirmations.

model AssignedSoldInventory {
  id                      Int      @id @default(autoincrement())
  skyboxInventoryId       Int
  totalOrderCost          Decimal
  orderConfirmationNumber String
  additionalOrderNumbers  String?
  multi_pack              Int?
  website                 String
  ticketsReplaced         Boolean
  userId                  String
  account                 String
  password                String
  created_at              DateTime @default(now())
  last_update             DateTime @updatedAt
  notes                   String?
  manuallyScheduled       Boolean  @default(false)
  integration             String
}

Key Fields:

• orderConfirmationNumber - Order confirmation from purchase
• integration - Which integration was used (TickOps, etc.)
• manuallyScheduled - Whether purchase was manually triggered

Event Models

TicketmasterEvent

Stores events from Ticketmaster Discovery API.

model TicketmasterEvent {
  id         Int      @id @default(autoincrement())
  event_id   String
  name       String?
  event_url  String?
  event_date String?
  venue      String?
  created_at DateTime @default(now())

  @@index([event_id])
}

SkyboxResult

Stores matched Skybox events with Ticketmaster events.

model SkyboxResult {
  id                    Int             @id @default(autoincrement())
  ticketmaster_event_id String
  skybox_event_id       Int
  skybox_event_name     String
  skybox_event_url       String
  skybox_event_date      String
  skybox_venue           String
  created_at            DateTime        @default(now())
  scrape_results        ScrapeResult[]
  tickops_results       TickopsResult[]

  @@index([skybox_event_id])
  @@index([ticketmaster_event_id])
}

Relationships:

• scrape_results - Scraped data for this event
• tickops_results - TickOps API results

CombinedResult

Combines Ticketmaster and Skybox event data with pricing information.

model CombinedResult {
  id                       Int                @id @default(autoincrement())
  ticketmaster_event_id    String
  skybox_event_id          Int
  skybox_event_name        String
  skybox_event_url         String
  skybox_event_date        String
  skybox_venue             String
  ticketmaster_event_name  String
  ticketmaster_event_url   String
  ticketmaster_venue       String
  ticketType               String             @default("")
  missingReason            String?
  tickopsTickets           Json
  vividTickets             Json
  final_data               Json
  processed                Boolean            @default(false)
  last_monitored_at        DateTime?
  next_monitored_at        DateTime?
  latest_remaining_tickets Int?
  monitoredListings        MonitoredListing[]
  eventRunItems            EventRunItem[]
  purchases                Purchase[]

  @@index([skybox_event_id])
  @@index([ticketmaster_event_id])
}

Key Fields:

• tickopsTickets - Ticket data from TickOps API
• vividTickets - Ticket data from VividSeats
• processed - Whether event has been processed
• last_monitored_at - Last price monitoring check

Pipeline Models

EventRun

Tracks ETL pipeline execution runs.

model EventRun {
  id                Int       @id @default(autoincrement())
  status            String? // 'running', 'completed', 'failed'
  metadata          Json?
  created_at        DateTime  @default(now())
  finished_at       DateTime?
  updated_at        DateTime  @updatedAt
  created_purchases Boolean   @default(false)

  runItems EventRunItem[]
}

EventRunItem

Individual items processed in an event run.

model EventRunItem {
  id               Int      @id @default(autoincrement())
  eventRunId       Int
  combinedResultId Int
  skybox_event_id  Int
  metadata         Json?
  created_at       DateTime @default(now())
  updated_at       DateTime @updatedAt

  eventRun       EventRun       @relation(fields: [eventRunId], references: [id])
  combinedResult CombinedResult @relation(fields: [combinedResultId], references: [id])

  @@index([eventRunId])
  @@index([combinedResultId])
  @@index([skybox_event_id])
}

Monitoring Models

MonitoredListing

Tracks inventory items being monitored for price changes.

model MonitoredListing {
  id                         Int               @id @default(autoincrement())
  skybox_inventory_id        Int
  skybox_event_id            Int
  combined_result_id         Int?
  is_monitoring_active       Boolean           @default(true)
  is_monitoring_enabled      Boolean           @default(true)
  monitoring_disabled_reason String?
  monitoring_disabled_at     DateTime?
  last_price_check_at        DateTime?
  last_our_price_at_check    Decimal?
  last_market_price_compared Decimal?
  last_check_status          PriceCheckStatus?
  notes                      String?

  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  combinedResult CombinedResult?                @relation(fields: [combined_result_id], references: [id])
  priceHistory   MonitoredListingPriceHistory[]

  @@index([skybox_inventory_id])
  @@index([skybox_event_id])
  @@index([combined_result_id])
  @@index([is_monitoring_active])
  @@index([is_monitoring_enabled])
  @@index([last_check_status])
}

MonitoredListingPriceHistory

Historical price check records.

model MonitoredListingPriceHistory {
  id                    Int              @id @default(autoincrement())
  monitored_listing_id  Int
  check_timestamp       DateTime
  our_price_at_check    Decimal
  market_price_compared Decimal?
  check_status          PriceCheckStatus
  details               Json?

  monitoredListing MonitoredListing @relation(fields: [monitored_listing_id], references: [id], onDelete: Cascade)

  @@index([monitored_listing_id])
  @@index([check_timestamp])
  @@index([check_status])
}

PriceCheckStatus Enum:

• COMPETITIVE - Price is competitive
• UNCOMPETITIVE - Price is not competitive
• NO_COMPETITORS_IN_SECTION - No competitors found
• MARKET_DATA_UNAVAILABLE_API - Market data unavailable
• OUR_DATA_MISSING - Our inventory data missing
• MARKET_DATA_FETCH_FAILED - Failed to fetch market data
• PROCESSING_ERROR - Error during processing
• EVENT_SOLD_OUT - Event is sold out
• PARTIAL_DATA_AVAILABLE - Only partial data available

User Models

User

Application users.

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  firstName String?
  lastName  String?
  picture   String?
  is_admin  Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Purchase Models

Purchase

Tracks purchase attempts and results.

model Purchase {
  id               Int            @id @default(autoincrement())
  combinedResultId Int            @unique
  status           String // 'SUCCESS', 'FAILED'
  purchasePrice    Decimal        @db.Decimal(10, 2)
  competitorPrice  Decimal        @db.Decimal(10, 2)
  errorMessage     String?
  metadata         Json?
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
  combinedResult   CombinedResult @relation(fields: [combinedResultId], references: [id])

  @@index([combinedResultId])
}

sprout_etl Schema

The ETL schema includes all models from sprout_backend plus additional models:

Geographic Models

Geo

Geographic locations for event discovery.

model Geo {
  id              Int                                          @id @default(autoincrement())
  latitude        Float
  longitude       Float
  location        Unsupported("public.geography(Point,4326)")? // PostGIS Point geography
  city            String?
  state           String?
  country         String?                                      @default("US")
  radius          Float // Search radius in miles/km
  last_checked_at DateTime?
  event_count     Int                                          @default(0)
  page_depth      Int                                          @default(0)
  active          Boolean                                      @default(true)
  frequency       Int                                          @default(6) // Hours between checks
  duplicate_count Int                                          @default(0)
  created_at      DateTime                                     @default(now())
  updated_at      DateTime                                     @updatedAt

  ticketmasterEvents TicketmasterEvent[]

  @@index([latitude, longitude])
  @@index([active, last_checked_at])
  @@index([frequency])
}

Key Features:

• Uses PostGIS for geographic queries
• Tracks event discovery by location
• Configurable check frequency

Venue

Venue information with geographic data.

model Venue {
  id              Int                                          @id @default(autoincrement())
  ticketmaster_id String?                                      @unique
  name            String
  address         String?
  city            String?
  state           String?
  country         String?                                      @default("US")
  postal_code     String?
  latitude        Float?
  longitude       Float?
  location        Unsupported("public.geography(Point,4326)")? // PostGIS Point geography
  timezone        String?
  capacity        Int?
  venue_type      String? // Arena, Stadium, Theater, etc.
  metadata        Json?
  created_at      DateTime                                     @default(now())
  updated_at      DateTime                                     @updatedAt

  ticketmasterEvents TicketmasterEvent[]

  @@index([ticketmaster_id])
  @@index([city, state])
  @@index([latitude, longitude])
  @@index([venue_type])
}

Additional Models

TicketmasterQuotaHistory

Tracks Ticketmaster API quota usage.

model TicketmasterQuotaHistory {
  id                 Int      @id @default(autoincrement())
  hour_timestamp     DateTime @unique
  daily_used         Int
  daily_limit        Int
  hourly_used        Int
  hourly_limit       Int
  is_quota_exhausted Boolean
  created_at         DateTime @default(now())
  updated_at         DateTime @updatedAt

  @@index([hour_timestamp, created_at])
}

TicketmasterRequestLog

Logs all Ticketmaster API requests for monitoring.

model TicketmasterRequestLog {
  id               Int      @id @default(autoincrement())
  sync_run_id      String
  request_url       String
  request_params    Json
  response_status   Int
  response_time_ms  Int
  total_elements    Int?
  total_pages       Int?
  page_number       Int
  page_size         Int
  events_returned   Int
  events_saved      Int
  events_duplicate  Int
  error_message     String?
  response_data     Json?
  created_at        DateTime @default(now())

  @@index([sync_run_id, created_at])
  @@index([created_at])
  @@index([response_status])
}

Strategy

Trading strategies for purchase decisions.

model Strategy {
  id          Int      @id @default(autoincrement())
  name        String   @unique
  description String?
  parameters  Json?
  active      Boolean  @default(true)
  priority    Int      @default(0)
  created_at  DateTime @default(now())
  updated_at  DateTime @updatedAt

  @@index([active, priority])
}

Relationships

Event Matching Flow

TicketmasterEvent
    ↓
SkyboxResult (matches Ticketmaster to Skybox)
    ↓
CombinedResult (combines all data sources)
    ↓
MonitoredListing (tracks price monitoring)
    ↓
Purchase (purchase attempts)

Pipeline Flow

EventRun
    ↓
EventRunItem (one per event processed)
    ↓
CombinedResult (references the combined event data)

Indexes

Key indexes for performance:

• Event IDs: event_id, skybox_event_id, ticketmaster_event_id
• Monitoring: is_monitoring_active, last_check_status
• Geographic: latitude, longitude (for PostGIS queries)
• Timestamps: created_at, last_checked_at (for time-based queries)

Migrations

Migrations are managed via Prisma:

# Create new migration
doppler run -- npx prisma migrate dev --name migration_name

# Apply migrations
doppler run -- npx prisma migrate deploy

# Check migration status
doppler run -- npx prisma migrate status

Data Access Patterns

Common Queries

Find events by location:

const events = await prisma.ticketmasterEvent.findMany({
  where: {
    geo: {
      active: true,
      last_checked_at: { lt: new Date() }
    }
  },
  include: { geo: true }
});

Get monitored listings:

const listings = await prisma.monitoredListing.findMany({
  where: {
    is_monitoring_active: true,
    combinedResult: {
      processed: false
    }
  },
  include: {
    combinedResult: true,
    priceHistory: {
      orderBy: { check_timestamp: 'desc' },
      take: 10
    }
  }
});

Next Steps

• Environment Variables - Database connection
• Docker Setup - Local database setup
• Troubleshooting - Database issues