Brands & Accounts
The Brands hub and its related tables: parent brands, stores, vendors, warehouses, and their junctions.
This group defines who you fulfill for. Brands is the hub: each row is one ShipHero connection, also called an account. Everything else here hangs off it: parent brands group brands, and stores, vendors, and warehouses attach to them.
Parent Brand
A high-level grouping over one or more brands. Used to roll several accounts up under a single owning entity.
| Column | Type | Notes |
|---|---|---|
id | integer | Primary key. Defaults from the Parent Brand_id_seq sequence. |
Title | text | Display name of the parent brand. |
created_at, updated_at, created_by, updated_by | audit | Standard row audit columns. |
Brands
The central table of the system. Each row is one account, a single ShipHero connection, and holds its encrypted ShipHero credentials, webhook addresses, per-account fulfillment cost and charge settings, and initial-import state. The Brands.id is referenced as brand across SKUs, inventory, orders, and more.
| Column | Type | Notes |
|---|---|---|
id | integer | Primary key. Defaults from the Brands_id_seq sequence. |
title | text | Account / brand name. Used as the connection nickname in the Client Connections screen and the account pickers. |
slug | text | Unique, not null. Auto-generated by generate_brand_slug(). |
Parent Brand_id | integer | → references Parent Brand. The owning parent brand. |
vendor_id | uuid | → references Vendors. See the circular link note below. |
n8n_id | text | Identifier for the linked n8n automation. |
shiphero_access_token | text | Encrypted ShipHero access token. |
shiphero_access_refresh_token | text | Encrypted ShipHero refresh token. |
shiphero_tokens_regenerated_at | timestamptz | When the ShipHero tokens were last regenerated. |
shiphero_shipment_handler_webhook_address | text | ShipHero webhook endpoint for shipment events. |
shiphero_purchase_order_handler_webhook_address | text | ShipHero webhook endpoint for purchase-order events. |
sku_sync_last_run_at | timestamptz | Last time SKU sync ran for this account. |
reorder_cadence_days | integer | How often this account reorders, in days. Set by the team on the Replenishment Accounts screen. Drives the due and overdue nudge and auto-fills a cycle's period end date. Null means the account is not tracked for due dates. |
initial_import_progress | numeric | Not null, defaults 0.00. Constrained to the range 0.00 to 1.00. |
initial_import_completed | boolean | Not null. Derived: defaults to initial_import_progress = 1. |
fix_cost_fullfilment_1st_pick | numeric | Fulfillment cost/charge setting. Defaults 0. |
unit_cost_fulfillment_next_pick | numeric | Fulfillment cost/charge setting. Defaults 0. |
charge_start_fulfillment_next_pick | numeric | Fulfillment cost/charge setting. Defaults 0. |
charge_every_fulfillment_next_pick | numeric | Fulfillment cost/charge setting. Defaults 0. |
unit_cost_misc_supplies | numeric | Misc supplies cost/charge setting. Defaults 0. |
charge_start_misc_supplies | numeric | Misc supplies cost/charge setting. Defaults 0. |
charge_every_misc_supplies | numeric | Misc supplies cost/charge setting. Defaults 0. |
unit_cost_misc_supplies_pick_fee | numeric | Misc supplies pick-fee setting. Defaults 0. |
charge_start_misc_supplies_pick_fee | numeric | Misc supplies pick-fee setting. Defaults 0. |
charge_every_misc_supplies_pick_fee | numeric | Misc supplies pick-fee setting. Defaults 0. |
cost_misc_rev | numeric | Misc revenue setting. Defaults 0. |
charge_start_misc_rev | numeric | Misc revenue setting. Defaults 0. |
charge_every_misc_rev | numeric | Misc revenue setting. Defaults 0. |
created_at | timestamptz | Defaults now(). |
updated_at, created_by, updated_by | audit | Standard row audit columns. |
Automation. slug is generated by generate_brand_slug() when not supplied and must be unique. initial_import_completed is derived from initial_import_progress reaching 1. Brands is referenced everywhere: its brand name is denormalized onto SKU and inventory tables and kept in sync by triggers documented on those pages and in Automation.
Encrypted credentials
shiphero_access_token and shiphero_access_refresh_token hold encrypted ShipHero credentials. Never log or expose them, and rely on Row-Level Security to keep accounts isolated.
Columns portal logins can't read
Portal logins (the authenticated role) can read only the everyday columns: id, title, slug, timestamps, import state, cadence, sync time, vendor and parent brand. The ShipHero tokens, the webhook addresses, and every cost and charge setting are readable only with the service key. Server code that needs a token reads it with the service role, after its own role and account checks, and the browser gets at most the token's last 4 characters. A new column on Brands is hidden until it is granted to authenticated.
Stores
Sales channels (for example, Shopify shops) attached to a brand. One brand can have many stores.
| Column | Type | Notes |
|---|---|---|
id | bigint | Primary key. Generated always as identity. |
brand | integer | Not null. → references Brands. |
shop_name | text | Not null. The store's shop identifier. |
display_name | text | Optional friendly name for the store. |
created_at | timestamptz | Defaults now(). |
Vendors
Suppliers a brand buys from. Vendors are the sources on inventory purchase orders and carry contact and address details.
| Column | Type | Notes |
|---|---|---|
id | uuid | Primary key. Defaults gen_random_uuid(). |
brand | integer | Not null, must be > 0. → references Brands. |
legacy_id | text | Prior-system identifier, for migration. |
name | text | Vendor name. |
account_number | text | Account number with the vendor. |
contact_email | text | Vendor contact email. |
contact_phone | text | Vendor contact phone. |
address, city, state, country, postal_code | text | Vendor mailing address. |
shiphero_vendor_id | text | External ShipHero vendor id. |
created_at | timestamptz | Defaults now(). |
Circular link
Brands.vendor_id → Vendors.id and Vendors.brand → Brands.id. A brand can point at a default vendor while each vendor also belongs to a brand. Watch this cycle when inserting or deleting.
Warehouses
Physical fulfillment locations. Warehouses are shared across accounts and linked to brands through warehouse_brands_junction. Warehouse_ID is the external ShipHero id, and zip_code feeds shipping-zone distance math.
| Column | Type | Notes |
|---|---|---|
id | integer | Primary key. Defaults from the Warehouses_id_seq sequence. |
Warehouse | text | Warehouse name. |
Warehouse_Operator | text | Operator running the warehouse. |
Warehouse_ID | text | External ShipHero warehouse id. |
Replen_Order_Lead_Time | bigint | Replenishment order lead time. |
zip_code | text | Used for shipping-zone distance calculations. |
address_line_1, address_line_2, city, state | text | Warehouse address. |
created_at | timestamptz | Defaults now(). |
updated_at | timestamp | Update timestamp. |
warehouse_brands_junction
Many-to-many join between Warehouses and Brands. Each row means one warehouse serves one brand.
| Column | Type | Notes |
|---|---|---|
id | bigint | Primary key. Generated always as identity. |
warehouse | integer | Not null. → references Warehouses. |
brand | integer | Not null. → references Brands. |
created_at | timestamptz | Defaults now(). |