mirror of https://github.com/christianvidalwolf-prog/CrazeAnalytix.git synced 2026-08-03 12:25:22 +02:00

Files

T

Christian Vidal WolfandClaude Opus 4.6 2bce0a839b feat: add Supabase vendor data integration (BSR, ratings, buy box)

- Create vendor_daily_data table schema and Supabase client service
- Add upload API route for Vendor Central CSV parsing and upsert
- Add VendorDataView with BSR trend, ratings, and buy box charts
- Integrate new Vendor tab into app navigation (desktop + mobile)
- Add vendor CSV upload card to FileUpload modal

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

2026-02-20 13:19:25 +01:00

3.5 KiB

Raw Permalink Blame History

Supabase Vendor Daily Data Integration

Overview

Store daily Vendor Central export data (BSR, ratings, buy box) in Supabase. Upload via CSV, display aggregated to weekly in a new "Vendor Data" view.

Database Schema

Single flat table with composite unique key (date, market, asin):

CREATE TABLE vendor_daily_data (
  id BIGSERIAL PRIMARY KEY,
  date DATE NOT NULL,
  market TEXT NOT NULL,
  asin TEXT NOT NULL,
  product_title TEXT,
  tags TEXT,
  bsr_top_rank INTEGER,
  bsr_top_category TEXT,
  bsr_detail_rank INTEGER,
  bsr_detail_category TEXT,
  avg_rating NUMERIC(3,1),
  num_reviews INTEGER,
  buybox_owner TEXT,
  buybox_price NUMERIC(10,2),
  amazon_has_buybox BOOLEAN,
  glance_views INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, market, asin)
);

CREATE INDEX idx_vendor_daily_market_date ON vendor_daily_data(market, date);
CREATE INDEX idx_vendor_daily_asin ON vendor_daily_data(asin);
CREATE INDEX idx_vendor_daily_tags ON vendor_daily_data(tags);

CSV Column Mapping

CSV Column	DB Column	Transform
Date	date	Parse as DATE
Market	market	Direct (DE, UK, IT, FR, ES)
ASIN	asin	Direct
Product Title	product_title	Direct
Tags	tags	Direct (product line)
Top Level Category (Rank)	bsr_top_rank	Parse INT
Top Level Category (Name)	bsr_top_category	Direct
Detail Level Category (Rank)	bsr_detail_rank	Parse INT
Detail Level Category (Name)	bsr_detail_category	Direct
Average Rating	avg_rating	EU decimal (3,8 -> 3.8)
Number of Reviews	num_reviews	Parse INT
Buybox Seller Name	buybox_owner	Direct
Buybox Price	buybox_price	EU decimal (2,49 -> 2.49)
Amazon Has Buybox	amazon_has_buybox	1/0 -> boolean
Glance Views	glance_views	Parse INT

New Files

File	Purpose
`services/supabase.ts`	Client init + query helpers
`api/upload-vendor-data.ts`	Parse CSV, upsert to Supabase
`components/VendorDataView.tsx`	BSR/ratings/buybox charts

Data Flow

Upload

User clicks "Upload Vendor CSV" in FileUpload.tsx
Client sends file to /api/upload-vendor-data (POST)
API parses CSV, extracts 15 columns, converts EU numbers
Upserts to Supabase in batches of 500
Returns { inserted, updated } counts

Display

VendorDataView queries Supabase directly (anon key, read-only)
Client aggregates daily -> weekly (AVG for ranks/ratings, latest for buybox)
Renders Recharts charts: BSR trend, ratings, buy box status

View: VendorDataView.tsx

Three chart panels:

BSR Trend - Line chart, Y-axis inverted, lines per marketplace
Ratings & Reviews - Dual axis: avg rating + review count
Buy Box Status - % days Amazon vs Seller vs Other

Filters: marketplace, product line, ASIN, date range (reuse MultiSelectDropdown).

Integration Points

App.tsx: New vendor view state + nav button
FileUpload.tsx: 4th upload card for Vendor CSV
package.json: Add @supabase/supabase-js
Vercel env vars: SUPABASE_URL, SUPABASE_ANON_KEY, SUPABASE_SERVICE_KEY

Environment Variables

Variable	Where Used	Purpose
SUPABASE_URL	Client + API	Supabase project URL
SUPABASE_ANON_KEY	Client	Read-only access
SUPABASE_SERVICE_KEY	API routes only	Write access for upserts

Security

Anon key for client reads, service key for server writes
No RLS (private internal tool)
Env vars in Vercel, not hardcoded

3.5 KiB Raw Permalink Blame History