mirror of
https://github.com/christianvidalwolf-prog/CrazeAnalytix.git
synced 2026-08-03 14:05:24 +02:00
Christian Vidal WolfandClaude Opus 4.6
2bce0a839b
- 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>
109 lines
3.5 KiB
Markdown
109 lines
3.5 KiB
Markdown
# 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)`:
|
|
|
|
```sql
|
|
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
|
|
1. User clicks "Upload Vendor CSV" in FileUpload.tsx
|
|
2. Client sends file to `/api/upload-vendor-data` (POST)
|
|
3. API parses CSV, extracts 15 columns, converts EU numbers
|
|
4. Upserts to Supabase in batches of 500
|
|
5. Returns `{ inserted, updated }` counts
|
|
|
|
### Display
|
|
1. VendorDataView queries Supabase directly (anon key, read-only)
|
|
2. Client aggregates daily -> weekly (AVG for ranks/ratings, latest for buybox)
|
|
3. Renders Recharts charts: BSR trend, ratings, buy box status
|
|
|
|
## View: VendorDataView.tsx
|
|
|
|
Three chart panels:
|
|
1. **BSR Trend** - Line chart, Y-axis inverted, lines per marketplace
|
|
2. **Ratings & Reviews** - Dual axis: avg rating + review count
|
|
3. **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
|