Database Tables
Core Tables
| Table | Description |
|---|---|
users | App users with email/password auth (JWT). Each user belongs to an agencies via agency_id FK and has a role |
sellers | Amazon seller accounts, unique on (seller_id, marketplace). Root entity — referenced by report tables and mappings |
brands | Canonical brand definitions with aliases for fuzzy matching. Central entity — referenced by new_asins, and both mapping tables |
agencies | Agencies that use the platform, with feature flags (jeff_enabled, lex_enabled). Referenced by users, agency_brand_mapping, and jeff_jobs |
Mapping Tables
| Table | Description |
|---|---|
seller_brand_mapping | Links sellers to brands they sell, unique on (seller_id, brand_id). FKs to sellers, brands, and users (created_by) |
lex_agency_brand_mapping | Links agencies to brands they manage for review removal. brand_id is unique — enforces one agency per brand. FKs to agencies, brands, users |
Upload Tracking Tables
| Table | Description |
|---|---|
report_upload_history | Audit trail for report uploads: UUID, report type, file hash (dedup), blob path, uploader. uploaded_by and reversed_by FK to users. Supports upload reversal |
report_row_changes | Row-level change tracking per upload: change type (CREATED/UPDATED/UNCHANGED), previous and new values. FKs to report_upload_history via upload_id. Enables complete rollback |
ASIN Ingestion Tables
These tables power the ASIN ingestion system (ScrapingBee-based), centered around new_asins as the parent table. All history tables use composite primary keys with (new_asin_id, timestamp) for time-series storage.
| Table | Description |
|---|---|
new_asins | Scraped ASIN data, unique on (asin, marketplace). FK to brands. Has parent_asin for variation grouping |
new_asin_changelog | Field-level change log for new_asins. FKs to new_asins. Tracks what changed, old/new values, and when |
new_asin_signals | Computed signals per ASIN (title length, bullet quality, keyword density, etc.). Composite PK (new_asin_id, signal_name). FKs to new_asins |
new_asin_offers | Current offers/sellers on an ASIN listing. FKs to new_asins. Each offer has a seller_id |
new_asin_offer_history | Time-series of offer snapshots. FKs to new_asin_offers via offer_id. Tracks price/buybox changes per offer over time |
new_asin_price_history | Price over time by price_type. Composite PK (new_asin_id, price_type, timestamp) |
new_asin_sales_rank_history | BSR history per category. Composite PK (new_asin_id, category_id, timestamp) |
new_asin_rating_history | Overall star rating over time |
new_asin_rating_distribution_history | Per-star (1-5) rating counts over time |
new_asin_review_count_history | Total review count over time |
new_asin_monthly_sold_history | Monthly sold count over time |
new_asin_coupon_history | Coupon availability/details over time |
dynamic_attributes_values | Multi-entity SCD Type 2 attribute tracking with effective_from/effective_to. Sparse FKs to new_asins / brands / sellers (exactly one populated per row, matching entity_type); FK to users (modified_by). CHECK constraint enforces FK ↔ entity_type. |
new_asin_variation_families | Variation family definitions. Composite PK (parent_asin, marketplace) |
new_asin_variation_family_changelog | Change log for variation families. Composite FK to new_asin_variation_families with CASCADE delete |
new_asin_variation_rating_history | Rating history at variation level. FKs to new_asins |
new_asin_variation_review_history | Review count history at variation level. FKs to new_asins |
Review Removal Tables
| Table | Description |
|---|---|
lex_reviews | Amazon reviews tracked for removal. FKs to asins via asin_id. Tracks removal_state, invoice_state, and has_reappeared |
lex_review_lifecycle_events | Lifecycle events per review (appeal submitted, removed, invoiced, etc.). FKs to lex_reviews and users |
SP Structure Tables
These model the Amazon Sponsored Products campaign hierarchy. The chain is: sp_portfolios → sp_campaigns → sp_ad_groups → targeting/keyword/product entities. All scoped by (seller_id, marketplace).
| Table | Description |
|---|---|
sp_portfolios | Top-level portfolio containers. Unique on (seller_id, marketplace, amazon_portfolio_id) |
sp_campaigns | Campaigns within portfolios. FK to sp_portfolios (nullable). Unique on (seller_id, marketplace, amazon_campaign_id) |
sp_ad_groups | Ad groups within campaigns. FK to sp_campaigns. Unique on (campaign_id, amazon_ad_group_id) |
sp_product_ads | Individual product ads. FK to sp_ad_groups |
sp_keywords | Keyword targets within ad groups. FK to sp_ad_groups |
sp_negative_keywords | Negative keyword targets at ad group level. FK to sp_ad_groups |
sp_product_targetings | Product/ASIN targets within ad groups. FK to sp_ad_groups |
sp_negative_product_targetings | Negative product targets at ad group level. FK to sp_ad_groups |
sp_campaign_negative_keywords | Negative keywords at campaign level. FK to sp_campaigns |
sp_campaign_negative_product_targetings | Negative product targets at campaign level. FK to sp_campaigns |
sp_campaign_bidding_adjustments | Placement bid adjustments per campaign. FK to sp_campaigns. Unique on (campaign_id, placement) |
sp_name_history | Tracks name changes for any SP entity (campaign, ad group, etc.) over time. Indexed on (entity_type, entity_id, effective_from) |
Report Tables
All report tables use shared mixins: seller_id/marketplace (ReportMetadata), report_date (ReportDate), uploaded_by/upload_timestamp (UploadTracking).
Business Reports
| Table | Description |
|---|---|
rpt_br_detail_page_sales_traffic | Parent ASIN sales/traffic: sessions, page views, buy box %, units, revenue |
rpt_br_detail_page_sales_traffic_by_child | Child ASIN level sales metrics |
rpt_br_all_orders | Order-level data: order ID, SKU, ASIN, quantity, revenue. Indexed on (seller_id, marketplace, amazon_order_id, sku, asin) |
Sponsored Products Reports
| Table | Description |
|---|---|
rpt_sponsored_products_campaign | Campaign overview with budget/status |
rpt_sponsored_products_search_term | Search term performance by match type |
rpt_sponsored_products_targeting | Targeting clause performance |
rpt_sponsored_products_advertised_product | Product-level ASIN/SKU performance |
rpt_sponsored_products_budget | Budget optimization and missed opportunities |
rpt_sponsored_products_placement | Performance by ad placement |
rpt_sponsored_products_purchased_product | Cross-sell analysis |
rpt_sponsored_products_performance_over_time | Time-series minimal metrics |
rpt_sponsored_products_search_term_impression_share | Competitive positioning |
rpt_sponsored_products_gross_and_invalid_traffic | Traffic quality metrics |
Search Query Performance
| Table | Description |
|---|---|
rpt_search_query_performance_brand | Brand-level search analytics |
rpt_search_query_performance_asin | ASIN-level search analytics |
Inventory Reports
| Table | Description |
|---|---|
rpt_restock_inventory | Restock recommendations and inventory levels |
Other Reports
| Table | Description |
|---|---|
rpt_azure_usage | Makeshift table for tracking Azure API usage/costs. Not related to seller analytics |
Jeff Tables
These power the Jeff AI SDR system. jeff_jobs is the entry point — each job contains rows (jeff_job_rows) that go through seller selection and audit generation.
| Table | Description |
|---|---|
jeff_jobs | Bulk processing jobs (CSV upload). FK to agencies. Contains status and config |
jeff_job_rows | Individual rows within a job, one per seller. FKs to jeff_jobs (CASCADE) and jeff_audits (CASCADE, nullable). Unique on (job_id, row_number) |
jeff_prospect_selections | Selected prospect ASINs per seller. Indexed on (seller_id, keepa_domain_id, selected_at) |
jeff_competitor_selections | Competitor ASINs chosen for each prospect. FK to jeff_prospect_selections (CASCADE) |
jeff_selection_xray | Debug/explainability data for selection scoring. FK to jeff_prospect_selections (CASCADE). Unique on (prospect_selection_id, step_type) |
jeff_audits | Generated audit documents per prospect selection. FK to jeff_prospect_selections (CASCADE) |
jeff_refresh_dispatch_logs | Logs for ASIN data refresh dispatches. No FKs, standalone |
jeff_posthog_events | PostHog analytics events related to Jeff |
Smartlead Tables
Integration tables for the Smartlead email outreach platform. Prefixed with jeff_sl_ since they serve the Jeff outreach pipeline. jeff_sl_clients links to agencies for multi-tenant access.
| Table | Description |
|---|---|
jeff_sl_clients | Smartlead client accounts. FK to agencies (nullable). PK is the Smartlead ID |
jeff_sl_campaigns | Email campaigns. Linked to clients via client_id. Has parent_campaign_id for series/sub-series hierarchy |
jeff_sl_leads | Email leads per campaign. Composite PK (id, campaign_id). Tracks status, category, and last activity |
jeff_sl_lead_messages | Individual email messages for leads |
jeff_sl_email_accounts | Sender email accounts. Linked to clients. Indexed on domain and type |
jeff_sl_categories | Lead categorization labels |
jeff_sl_meetings | Meetings booked from outreach |
jeff_sl_meeting_stage_events | Stage transitions for meetings (pipeline tracking) |
Deprecated Tables
warning
These tables are from the legacy ASIN system and have been superseded by the ASIN Ingestion Tables (new_asins and family). The new_asins system provides richer data via ScrapingBee-based ingestion — including time-series history, signals, offers, and variation tracking — replacing the flat asins table and its lifecycle events. These tables still exist in the database but should not be used for new development.
| Table | Description |
|---|---|
asins | Legacy product catalog with parent-child relationships (via parent_asin self-reference). FKs to brands. Replaced by new_asins which has richer scraped data, marketplace scoping, and full history tracking |
asin_lifecycle_events | Legacy lifecycle events for ASINs (launch, discontinue, restock, etc.). FKs to asins and users. Replaced by new_asin_changelog which provides field-level change tracking automatically |