asin-check

Amazon product analysis and lead finder agent. Reads product leads from a CSV/XLSX file, enriches them with Keepa pricing and sales data, caches results in Redis, and runs each product through a local LLM to get an FBA/FBM/SKIP verdict.

Requirements

• Bun runtime
• Redis (local or Docker)
• LM Studio running locally with a model loaded
• Keepa API key (keepa.com)
• Amazon SP-API private app credentials (LWA + refresh token + IAM)

Setup

bun install
cp .env.example .env
# Edit .env and set your KEEPA_API_KEY and SP-API credentials

Usage

bun start <input.csv|xlsx> [--out results.xlsx]

Add --claude to use Anthropic Claude instead of local LM Studio for LLM analysis. Bare input and output filenames use the input/ and output/ directories. Pass a path containing a directory to override those defaults.

Examples:

bun start leads.xlsx
bun start leads.csv --out results.xlsx
bun start leads.xlsx --claude
bun start archive/leads.xlsx --out exports/results.xlsx

Large-file behavior:

• If the input has more than 50 products, processing is done in chunks of 50.
• Each chunk is analyzed and written to a numbered output file under output/, for example: output/results_part_001.xlsx, output/results_part_002.xlsx, ...
• If --out is omitted for large files, the base output name defaults to output/<input>_results.xlsx and chunk files are still written with numbered suffixes.

Quick SP-API connectivity tests:

bun run src/sp-test.ts               # Auth + sellers endpoint
bun run src/sp-test.ts B07SN9BHVV    # Auth + sellers endpoint + pricing offer check
bun run src/sp-test.ts --sellability B07SN9BHVV  # Standalone sellability check

Category Pipelines

Run category-focused discovery flows with Keepa + SP-API + LLM:

bun run bestsellers
bun run monthly-sold
bun run mid-range

Use Claude for category LLM analysis:

bun run bestsellers --claude
bun run monthly-sold --claude
bun run mid-range --claude

Mid-range process:

• Script: bun run mid-range
• Source: src/mid-range-sellers-by-category.ts
• Default filters:
  • Monthly sold between 100 and 1000
  • Price between $15 and $200 (using Keepa current price, fallback avg 90d)
  • Seller count between 3 and 20
  • If Amazon is a seller, Amazon buy box share must be between 15% and 85%
• Sellability behavior:
  • Sellability is still fetched and saved (can_sell, sellability_status, sellability_reason)
  • Matching products are persisted regardless of sellability status
• Caching behavior:
  • Uses Redis to cache Keepa + SP-API API enrichment per ASIN
  • Cache TTL is fixed at 12 hours

Example:

bun run mid-range --category-limit 10 --per-category-top 50 --category-candidate-pool 250 --min-monthly-sold 100 --max-monthly-sold 1000 --min-price 15 --max-price 200 --min-seller-count 3 --max-seller-count 20 --min-amazon-buybox-share-pct 15 --max-amazon-buybox-share-pct 85

UPC to ASIN Mapping

You can map UPCs to ASINs directly through the Keepa integration in src/keepa.ts.

import { mapUpcsToAsins, lookupKeepaUpcs } from "./src/keepa.ts";

const upcs = ["012345678901", "098765432109", "112233445566"];

// Simple map output (UPC -> ASIN) for clean one-to-one matches only.
const asinMap = await mapUpcsToAsins(upcs);
for (const [upc, asin] of asinMap.entries()) {
  console.log(`UPC ${upc} -> ASIN ${asin}`);
}

// Rich output includes status for every UPC (invalid, not found, collisions, etc.).
const details = await lookupKeepaUpcs(upcs);
for (const [upc, detail] of details.entries()) {
  console.log(upc, detail.status, detail.asin, detail.reason ?? "");
}

Behavior:

• Strict validation accepts only 12, 13, or 14 digit UPC values.
• If a UPC resolves to multiple ASINs, it is excluded from the simple map.
• The rich lookup returns all candidate ASINs and status per UPC.

CLI usage:

bun run upc 012345678901 098765432109
bun run upc 012345678901,098765432109 --detailed
bun run upc --file upcs.txt --detailed --json

API usage (when bun run start:web is running):

# Simple one-to-one mapping (GET)
curl "http://localhost:3000/api/upc/map?upc=012345678901&upc=098765432109"

# Detailed lookup with statuses (GET)
curl "http://localhost:3000/api/upc/lookup?upcs=012345678901,098765432109"

# Detailed lookup (POST JSON)
curl -X POST "http://localhost:3000/api/upc/lookup" \
  -H "content-type: application/json" \
  -d '{"upcs":["012345678901","098765432109"]}'

Run the web server with Claude-backed LLM calls:

bun run start:web -- --claude

Large UPC File Analysis (XLS/XLSX)

For supplier price lists that contain UPC/EAN values and unit cost, use the dedicated UPC-file process. It runs in batches and produces a deterministic ranked sourcing workbook:

1. Reads UPC rows in batches (.xlsx uses streaming reader, .xls uses fallback row-window parsing).
2. Resolves UPCs to ASINs with SP-API catalog lookup first, then falls back to Keepa for no-match/request-failure cases.
3. Enriches resolved ASINs with Keepa demand/competition data and SP-API sellability + FBA fees.
4. Scores products with deterministic BUY/WATCH/SKIP logic; this path does not call LM Studio.
5. Writes a ranked Excel workbook and persists rows through unified runs, UPC resolution, product observation, and scoring-history tables.

CLI usage:

bun run upc-file --input input/huge-upcs.xlsx
bun run upc-file --input input/supplier.xlsx --out output/supplier_ranked.xlsx
bun run upc-file --input input/huge-upcs.xls --input-batch-size 500 --upc-lookup-batch-size 100 --max-rows 10000

Workbook output includes Ranked Leads, Skipped, and Summary sheets with UPC, ASIN, cost, sale price, FBA fee, profit, margin, ROI, BSR, rank drops, monthly sold, seller count, Amazon Buy Box share, sellability, score, verdict, and reason columns.

API usage (when bun run start:web is running):

curl -X POST "http://localhost:3000/api/process/upc-file" \
  -H "content-type: application/json" \
  -d '{
    "inputFile": "/absolute/path/to/input/huge-upcs.xlsx",
    "inputBatchSize": 300,
    "upcLookupBatchSize": 100
  }'

Request body fields:

• inputFile (required): server-local path to .xls or .xlsx file.
• outputFile (optional): stored in run metadata.
• inputBatchSize (optional): number of input rows per processing batch (default 200).
• upcLookupBatchSize (optional): UPC chunk size per Keepa lookup call (default 100).
• maxRows (optional): cap processed valid UPC rows for dry runs.

Response includes run metadata and status counts, including unresolved UPC reasons and lead verdict totals.

Input file format

Accepts .csv or .xlsx files. Column names are matched case-insensitively. Required column:

Column	Aliases
ASIN	—

Optional but recommended:

Column	Aliases
Product Name	Name, Title
Unit Cost	Cost, Price, Buy Cost
Brand	—
Category	—
Amazon Rank	Amazon Rank, BSR, Sales Rank
FBA NET	—
Gross Profit $	Gross Profit
Gross Profit %	—
MOQ	Min Order Qty
MOQ Cost	—
Total Qty Avail	Qty Available
Link	URL, Source

Lead-list format aliases (supported):

Column	Aliases
Name	Product Name, Title, Product Title
ASIN Link	ASIN URL, Amazon Link
Source URL	Source Link, Supplier URL
90 Day Average	90-day Average, Avg Price 90d, 90d Average
Cost	Unit Cost, Buy Cost, Price
Selling Price	Sale Price, Sell Price
Net Profit	Gross Profit
ROI	Gross Profit %, Return on Investment
Supplier	Vendor
Promo/Coupon Code	Promo Code, Coupon Code
Notes	Note
Date	Lead Date

Numeric parsing accepts plain numbers as well as formatted values like $12.50, 1,209.60, and 27.5%.

Pipeline

1. Read — parse input file, validate ASINs
2. Cache check — look up each ASIN in Redis (24h TTL by default)
3. Sellability gate — check all uncached ASINs against SP-API getListingsRestrictions (concurrency: 5 workers); immediately skip ASINs with status not_available and canSell=false (no Keepa/fees wasted)
4. Keepa fetch — batch the sellable (uncached) ASINs in a single API call (up to 100 per request)
5. Enrich — fetch SP-API pricing + FBA/FBM fees for sellable ASINs; combine with Keepa data and spreadsheet data
6. LLM analysis — send batches of 5 sellable products to LM Studio for FBA/FBM/SKIP verdict; skipped ASINs get auto-SKIP verdict (confidence 100) and bypass LLM entirely
7. Output — print results table to console (includes all ASINs), optionally write CSV/XLSX, and persist products, observations, run items, and analysis revisions to PostgreSQL.

Persistent Storage

PostgreSQL persistence is managed with Drizzle in src/db/schema.ts and src/db/persistence.ts. ASINs are canonical product identities: all inputs normalize to uppercase 10-character alphanumeric keys before any product reference is stored.

Core tables:

• products: one canonical row per ASIN with latest descriptive metadata.
• product_observations: append-only marketplace, pricing, fee, and sellability snapshots.
• runs and run_items: unified lifecycle/history for lead, category, supplier UPC, and stalker workflows.
• analysis_revisions and supplier_scores: append-only analysis results; reanalysis does not overwrite prior decisions.
• sourcing_inputs, upc_resolutions, and product_identifiers: source-row and confirmed identifier data kept separate from catalog products.
• stalker_run_details, stalker_scans, and stalker_inventory_items: seller workflow provenance linked back to products and observations.

Unresolved or ambiguous supplier UPCs stay on their run item and resolution records; a UPC is never stored as an ASIN.

Web endpoints use unified identifiers:

• GET /api/runs, GET /api/runs/:runId, GET /api/runs/:runId/items
• GET /api/products, GET /api/products/:asin
• POST /api/run-items/:itemId/reanalyze

Output columns

ASIN, Name, Brand, Category, Unit Cost, Current Price, Avg Price 90d, Sales Rank, Rank Avg 90d, Sellers, Monthly Sold, Rank Drops 30d, Rank Drops 90d, FBA Net (sheet), Gross Profit $, Gross Profit %, MOQ, MOQ Cost, Qty Available, FBA Fee, FBM Fee, Referral %, Verdict, Confidence, Reasoning

Environment variables

Variable	Default	Description
KEEPA_API_KEY	—	Required. Keepa API key
SP_API_CLIENT_ID	—	LWA app client id from Solution Provider Portal
SP_API_CLIENT_SECRET	—	LWA app client secret from Solution Provider Portal
SP_API_REFRESH_TOKEN	—	Refresh token from self-authorization
SP_API_REGION	na	SP-API endpoint region (na, eu, fe; us is accepted as na)
SP_API_MARKETPLACE_ID	ATVPDKIKX0DER	Marketplace id used for pricing and fee calls (default: US)
SP_API_SELLER_ID	—	Seller ID used for listing restrictions eligibility checks
SP_API_USE_SANDBOX	false	Enable SP-API sandbox mode (true/false)
AWS_ACCESS_KEY_ID	—	AWS credentials for SigV4 signing (required in most private app setups)
AWS_SECRET_ACCESS_KEY	—	AWS credentials for SigV4 signing
AWS_SESSION_TOKEN	—	Optional session token when using STS credentials
REDIS_URL	redis://localhost:6379	Redis connection URL
LLM_URL	http://localhost:1234/v1	LM Studio API base URL
LLM_MODEL	default	Model name to pass to LM Studio
ANTHROPIC_API_KEY	—	Required when running any LLM script with --claude
ANTHROPIC_MODEL	claude-3-5-sonnet-20241022	Claude model ID used with --claude
CACHE_TTL	86400	Redis cache TTL in seconds

Notes

• Available-only processing: SP-API getListingsRestrictions is checked first and only ASINs with sellabilityStatus=available are enriched, analyzed, and included in outputs. Restricted, not_available, and unknown items are excluded.
• SP-API concurrency: fetchSellabilityBatch limits concurrent requests to 5 workers to avoid 429 throttling. Pricing+fees fetches also use 5 concurrent workers.
• No batch endpoint: Amazon SP-API does not provide batch endpoints for getListingsRestrictions or getMyFeesEstimate*. Concurrency limiting with the library's built-in auto_request_throttled safety net prevents overwhelming the API.
• Keepa rate limiting: The client reads tokensLeft and refillRate from each API response and waits automatically when tokens are exhausted. With a Pro subscription (1 token/min), all 100 ASINs in a batch cost 1 token.
• Redis is optional: If Redis is unavailable the tool runs without caching — every run re-fetches from Keepa.
• SP-API: src/sp-api.ts provides fetchSellability, fetchSellabilityBatch, and fetchSpApiPricingAndFees functions. If SP-API credentials are missing or a call fails, the tool falls back to conservative fee defaults and keeps processing.
• Sandbox vs production: When SP_API_USE_SANDBOX=true, production ASIN calls can be denied. Use sandbox-compatible test data or set it to false for live marketplace connectivity.