SKU Sync
Tables that record the daily ShipHero SKU-sync process and its human review queue.
Every day, a scheduled job pulls SKUs from ShipHero and reconciles them against the SKUs in this database. These three tables record what that process did: a run (sku_sync_runs) contains many items (sku_sync_items), and any SKU the sync can't recognize lands in a human review queue (sku_pending_queue).
A run groups the work for one account sync. Each item is one SKU the run processed. SKUs the sync can't classify are held in the pending queue until a person resolves them. For the end-to-end picture, see Data Flows; the review queue is worked from the Settings feature.
These tables have no triggers. They are written and read by the SKU-sync code and the daily cron job (see Data Flows).
sku_sync_runs
One row per sync run for one account. A run records when it started and finished, its status, and counters summarizing how many SKUs it saw and what it did with them.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. |
| brand_id | integer | → references Brands. The brand this run synced. |
| started_at | timestamptz | When the run began. |
| completed_at | timestamptz | When the run finished. Null while still running. |
| status | text | Run state. Defaults to running. Plain text (no CHECK constraint); typical values are running, completed, and failed. |
| error_message | text | Error detail if the run failed. |
| skus_found | integer | Count of SKUs seen from ShipHero. Defaults to 0. |
| skus_created | integer | Count of SKUs newly created. Defaults to 0. |
| skus_skipped | integer | Count of SKUs skipped (already known or ignored). Defaults to 0. |
| created_at | timestamptz | Row creation time. Defaults to now(). |
A run belongs to one brand. Its sku_sync_items and any sku_pending_queue entries reference it by id.
sku_sync_items
One row per SKU processed during a run. Each item records the raw ShipHero SKU and name, what kind of SKU it was, the action the sync took, and which local SKU record it touched.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. |
| sync_run_id | bigint | → references sku_sync_runs. The run that processed this item. |
| shiphero_sku | text | The SKU string from ShipHero. |
| shiphero_name | text | The product name from ShipHero. |
| sku_type | text | The kind of SKU processed. Plain text (no CHECK constraint) describing whether it was treated as a base, product, or case SKU. |
| action | text | What the sync did with this SKU. Plain text (no CHECK constraint), e.g. created, linked, or skipped. |
| base_sku_id | integer | → references Base SKUs. The base SKU this item touched, if any. |
| product_sku_id | integer | → references Product SKUs. The product SKU this item touched, if any. |
| created_at | timestamptz | Row creation time. Defaults to now(). |
Each item belongs to one sku_sync_runs row and may point at a Base SKU and/or a Product SKU depending on what the sync did.
sku_pending_queue
The human review queue. When the sync encounters a ShipHero SKU it can't automatically recognize, it stores the full ShipHero payload here and waits for a person to classify it. The system may attach a suggestion; once a reviewer decides, resolution_type and the resolved_* columns record the outcome.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. |
| brand_id | integer | → references Brands. The brand the SKU belongs to. |
| shiphero_sku | text | The unrecognized SKU string from ShipHero. |
| shiphero_name | text | The product name from ShipHero. |
| shiphero_product_json | jsonb | The full ShipHero product payload, kept so a reviewer has all the detail needed to classify. |
| sync_run_id | bigint | → references sku_sync_runs. The run that queued this SKU. Nullable. |
| suggestion | text | The system's guessed classification. CHECK-constrained to product, case, or base. |
| status | text | Queue state. Defaults to pending. CHECK-constrained to pending, classified, or dismissed. |
| resolution_type | text | How the SKU was resolved. CHECK-constrained to new_base, new_case, new_product, new_both, link_base, link_case, link_product, or dismissed. |
| resolved_base_sku_id | integer | → references Base SKUs. The base SKU chosen when resolving, if any. |
| resolved_case_sku_id | integer | → references Case SKUs. The case SKU chosen when resolving, if any. |
| resolved_product_sku_id | integer | → references Product SKUs. The product SKU chosen when resolving, if any. |
| resolved_by | uuid | The Supabase auth user (auth.users) who resolved the entry. |
| resolved_at | timestamptz | When the entry was resolved. |
| created_at | timestamptz | Row creation time. Defaults to now(). |
status, suggestion, and resolution_type are CHECK-constrained. Only these values are allowed:
- status:
pending,classified,dismissed - suggestion:
product,case,base - resolution_type:
new_base,new_case,new_product,new_both,link_base,link_case,link_product,dismissed
Each queue entry belongs to one brand and optionally links back to the sku_sync_runs row that queued it. On resolution it may point at a Base, Case, and/or Product SKU, and resolved_by records the Supabase auth user who made the decision. Reviewers work this queue from the Settings feature.
Purchase Orders
Tables that track imported purchase orders, draft replenishment POs, their line items, and the queues that keep them in sync with ShipHero.
Reporting & Financials
Precomputed financial reporting tables: weekly account rollups, net-revenue snapshots, and the global model inputs that feed the math.