StickyMetrics - Architecture Overview¶

What It Is¶

StickyMetrics is an Amazon seller analytics platform. It pulls data from Amazon's SP-API and Advertising API, stores it in Postgres, runs ML forecasts (Chronos-Bolt-mini), and pushes results to Google Sheets dashboards.

Tech Stack¶

Language: Python 3 (scripts + forecasts)
Database: PostgreSQL 17 (local, stickymetrics db)
ML: Chronos-Bolt-mini (Amazon's time-series foundation model)
Secrets: Vaultwarden (self-hosted Bitwarden at https://100.77.44.93:8443)
Scheduling: macOS launchd (com.stickymetrics.daily, 6am)
Output: Google Sheets (via gog CLI)
Hosting: Mac Mini (local, not cloud-deployed)

Data Flow¶

Amazon SP-API ──→ pull_*.py scripts ──→ Postgres (raw.* schema)
                                              │
                                              ▼
                                    forecasts/forecast_all_lifecycle.py
                                    (Chronos-Bolt-mini, 365-day horizon)
                                              │
                                              ▼
                                    Postgres (forecasts.* schema)
                                              │
                                              ▼
                                    push_*.py scripts ──→ Google Sheets
                                              │
                                              ▼
                                    score_run.py (accuracy tracking)
                                    calibrate.py (1st of month, self-healing)

Schemas¶

raw.* - source data from Amazon (orders, restock, inventory, ads, products, sellers)
forecasts.* - predictions, runs, scoring, calibration

Key Scripts¶

Script	Purpose
`nightly_refresh.sh`	Orchestrates the full pipeline (10 steps)
`lib_sticky.py`	Shared helpers: vault decrypt, SP-API auth, Ads API auth
`add_seller.py`	Onboard a new tenant (validates creds, stores in vault, inserts DB row)
`pull_orders.py`	SP-API orders (last N days)
`pull_restock_report.py`	FBA restock recommendations
`pull_awd_inventory.py`	AWD warehouse inventory
`pull_ad_spend.py`	Advertising API spend reports
`pull_inventory_ledger.py`	Monthly inventory ledger
`compute_po_recommendations.py`	Per-ASIN PO recs (MOQ-gated)
`build_po_dashboard_xlsx.py`	Styled Excel export
`push_claude_mrp.py`	Push forecast to MRP source sheet
`push_forecast_to_sheet.py`	Push to purchasing sheet
`push_restock_to_sheet.py`	Push restock raw data

Tenants¶

Currently single-tenant: ecomhd_us (eComHD, US marketplace ATVPDKIKX0DER).

Multi-tenant by design - raw.sellers table + --seller flag on all scripts.

Secrets Access Pattern¶

All secrets live in Vaultwarden. Scripts access them via lib_sticky.vault_read() which decrypts the vault using the master key at ~/.bw-master-pw. Naming convention: - SP-API: {PREFIX}_SP_API_CLIENT_ID, _CLIENT_SECRET, _REFRESH_TOKEN - Ads API: {PREFIX}_ADS_CLIENT_ID, _CLIENT_SECRET, _REFRESH_TOKEN