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) |
| 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 |
| 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 |
SellerBot Tables
These power the SellerBot enrichment funnel — taking raw Amazon seller listings, grouping them into business entities, running per-goal enrichment strategies to discover websites and contact emails, and routing candidates through review and verification before they land as enriched outputs. All tables use the seb_* prefix.
Funnel: seb_ingest_batch (CSV import) → seb_amazon_seller + seb_seller_snapshot → grouping job sets seb_amazon_seller.seller_group_id → one seb_state row per (seller_group, goal) → strategies tried in priority order, each run logged in seb_attempt → candidates routed through seb_review_event (website goal) or seb_verification_pass (email goal) → outputs land in seb_seller_group.domain or seb_email. SellerBot output feeds the Smartlead outreach platform (no direct FK; emails are added to jeff_sl_leads downstream).
State Machine
seb_state.status partitions every (seller_group, goal) pair into exactly one of:
| Status | Meaning |
|---|
pending | No strategy attempted yet |
in_progress | At least one strategy tried, none produced a candidate, more strategies eligible |
review | Candidate produced, awaiting human review (website goal only) |
enriched | Goal satisfied — domain landed (website) or verified email landed (email) |
exhausted | All eligible strategies tried, no candidate — terminal |
goal ∈ email. Email goal has no human review — verified candidate auto-transitions to enriched.
Ingest & Reference Tables
| Table | Description |
|---|
seb_ingest_batch | Async CSV import lifecycle. Status: pending → validating → processing → processed/failed. Tracks rows_inserted, rows_updated, error_count per file |
seb_ingest_batch_error | Per-row errors from a batch. FK to seb_ingest_batch (CASCADE). Capped (errors_truncated flag set when overflowed) |
seb_smartscout_category | SmartScout category dictionary. Composite PK (marketplace, smartscout_id) — IDs collide across marketplaces. Maps to Amazon amazon_node_id |
Seller Tables
| Table | Description |
|---|
seb_amazon_seller | One row per Amazon listing, unique on (amazon_seller_id, marketplace). Holds sales/review/category metrics from SmartScout. FK to seb_seller_group (nullable — attached async by grouping job) |
seb_seller_group | One row per business entity. domain is unique (partial index on NOT NULL). Members are seb_amazon_seller rows. merged_into_id self-FK reserved for future merge-soft-delete |
seb_seller_snapshot | Daily time-series of seller metrics. Composite PK (amazon_seller_id, marketplace, snapshot_date). FK to seb_ingest_batch for provenance |
Enrichment Engine Tables
| Table | Description |
|---|
seb_strategy | Code-owned catalog of enrichment strategies per goal. Unique on (goal, name). Has priority (execution order), config JSONB (eligibility predicate against seb_amazon_seller attributes), optional auto_approve_policy_key and prerequisite_strategy_id (self-FK). Created/toggled via Alembic seeders only — no API |
seb_state | Current status of one (seller_group, goal) pair. Unique on (seller_group_id, goal). Drives the /next and review queues. first_verified_at set when the first verified email lands |
seb_attempt | Append-only log of every strategy run against a state. FKs to seb_state and seb_strategy. outcome is candidate or no_candidate; result JSONB holds enriched data; metadata JSONB holds strategy-specific context. CHECK enforces no_candidate_reason populated iff outcome='no_candidate' |
seb_dataforseo_task | Transient tracking of in-flight DataForSEO API calls. Unique on (strategy_id, state_id). Status: submitted → result_ready → row deleted once seb_attempt written. Failed rows persist for operator inspection; durable record lives in seb_attempt.metadata.dataforseo |
Output & Approval Tables
| Table | Description |
|---|
seb_email | Approved enriched emails per seller group. Unique on (seller_group_id, email_address). verification_verdict (success/fail/inconclusive/NULL) is a cache of the latest seb_verification_pass verdict |
seb_email_attribution | Many-to-many log linking which seb_attempt(s) produced which seb_email. Composite PK (email_id, attempt_id), both FKs CASCADE. ~1.7% of emails have multiple attributions |
seb_review_event | Append-only log of every decision on a seb_attempt — approve, reject, edit, auto_decline. FK to seb_attempt; FK to users (reviewer_user_id, ON DELETE RESTRICT — auto-approve runs use sentinel user AUTO_APPROVE). metadata JSONB carries policy-run context when bot-written; match signals (logo_match, product_line_match, address_match, phone_match) populated only on approve |
seb_verification_pass | Append-only log of verification runs against seb_email. Unique on (email_id, verifier_name) — one verdict per verifier (e.g. RocketCheck, ZeroBounce). Latest row drives the seb_email.verification_verdict cache |
Smartlead Tables
Integration tables for the Smartlead email outreach platform. Most use the jeff_sl_* prefix (legacy from the Jeff outreach pipeline); meeting tables use plain jeff_*. jeff_sl_clients.agency_id and jeff_meetings.agency_id are the multi-tenant boundary.
| Table | Description |
|---|
jeff_sl_clients | Smartlead client accounts. FK to agencies (nullable — NULL means unmapped to a tenant). PK is the Smartlead ID |
jeff_sl_campaigns | Email campaigns. FK to clients via client_id. parent_campaign_id self-FK for series/sub-series hierarchy. deleted_at is a soft-delete marker |
jeff_sl_leads | One row per unique email (account-scoped in Smartlead, not per-campaign). PK is the Smartlead lead ID. Tracks is_unsubscribed |
jeff_sl_lead_campaign_map | Lead participation per campaign. Unique on (lead_id, campaign_id). Tracks campaign-specific status (STARTED/INPROGRESS/COMPLETED/PAUSED/STOPPED/BLOCKED), lead_category_id, last_activity_at. removed_at is a soft-delete marker |
jeff_sl_lead_messages | Individual email events for leads. Composite PK (lead_id, campaign_id, message_id, type) — each SENT/REPLY/BOUNCED is its own row. sequence_number positions within the drip sequence |
jeff_sl_email_accounts | Sender email accounts. FK to clients. Holds Smartlead warmup fields + JeffCRM enrichment (current_stage, status_last_changed_at, smartlead_created_at — enable maturity slicing) |
jeff_sl_categories | Lead category lookup table. Has sentiment_type (positive/negative/neutral) |
jeff_sl_lead_category_changes | Append-only event log of lead category transitions per campaign. Enables "ever been in category X" metrics rather than only current state |
jeff_meetings | Meetings booked from outreach. lead_id + campaign_id set when auto-created from a category-change webhook (source=AUTO); NULL when manually created (source=MANUAL, uses manual_email/manual_first_name/etc.). agency_id tenant assignment (NULL = unpublished). stage is the current pipeline stage; invoice_status for billing |
jeff_meeting_stage_events | Append-only stage-transition audit log per meeting. Tracks old_stage → new_stage, changed_by_user_id (NULL if system/webhook), event_metadata |
Deprecated Tables
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 |