National Commerce Docs
Database

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.

Brand = Account

The glossary marks Brand as deprecated in favor of Account. They are the same thing: a Brands row is an account, one ShipHero connection with its own credentials and settings. New code should say "account".

Parent Brand

A high-level grouping over one or more brands. Used to roll several accounts up under a single owning entity.

ColumnTypeNotes
idintegerPrimary key. Defaults from the Parent Brand_id_seq sequence.
TitletextDisplay name of the parent brand.
created_at, updated_at, created_by, updated_byauditStandard 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.

ColumnTypeNotes
idintegerPrimary key. Defaults from the Brands_id_seq sequence.
titletextAccount / brand name. Used as the connection nickname in the Client Connections screen and the account pickers.
slugtextUnique, not null. Auto-generated by generate_brand_slug().
Parent Brand_idinteger→ references Parent Brand. The owning parent brand.
vendor_iduuid→ references Vendors. See the circular link note below.
n8n_idtextIdentifier for the linked n8n automation.
shiphero_access_tokentextEncrypted ShipHero access token.
shiphero_access_refresh_tokentextEncrypted ShipHero refresh token.
shiphero_tokens_regenerated_attimestamptzWhen the ShipHero tokens were last regenerated.
shiphero_shipment_handler_webhook_addresstextShipHero webhook endpoint for shipment events.
shiphero_purchase_order_handler_webhook_addresstextShipHero webhook endpoint for purchase-order events.
sku_sync_last_run_attimestamptzLast time SKU sync ran for this account.
reorder_cadence_daysintegerHow 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_progressnumericNot null, defaults 0.00. Constrained to the range 0.00 to 1.00.
initial_import_completedbooleanNot null. Derived: defaults to initial_import_progress = 1.
fix_cost_fullfilment_1st_picknumericFulfillment cost/charge setting. Defaults 0.
unit_cost_fulfillment_next_picknumericFulfillment cost/charge setting. Defaults 0.
charge_start_fulfillment_next_picknumericFulfillment cost/charge setting. Defaults 0.
charge_every_fulfillment_next_picknumericFulfillment cost/charge setting. Defaults 0.
unit_cost_misc_suppliesnumericMisc supplies cost/charge setting. Defaults 0.
charge_start_misc_suppliesnumericMisc supplies cost/charge setting. Defaults 0.
charge_every_misc_suppliesnumericMisc supplies cost/charge setting. Defaults 0.
unit_cost_misc_supplies_pick_feenumericMisc supplies pick-fee setting. Defaults 0.
charge_start_misc_supplies_pick_feenumericMisc supplies pick-fee setting. Defaults 0.
charge_every_misc_supplies_pick_feenumericMisc supplies pick-fee setting. Defaults 0.
cost_misc_revnumericMisc revenue setting. Defaults 0.
charge_start_misc_revnumericMisc revenue setting. Defaults 0.
charge_every_misc_revnumericMisc revenue setting. Defaults 0.
created_attimestamptzDefaults now().
updated_at, created_by, updated_byauditStandard 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.

ColumnTypeNotes
idbigintPrimary key. Generated always as identity.
brandintegerNot null. → references Brands.
shop_nametextNot null. The store's shop identifier.
display_nametextOptional friendly name for the store.
created_attimestamptzDefaults now().

Vendors

Suppliers a brand buys from. Vendors are the sources on inventory purchase orders and carry contact and address details.

ColumnTypeNotes
iduuidPrimary key. Defaults gen_random_uuid().
brandintegerNot null, must be > 0. → references Brands.
legacy_idtextPrior-system identifier, for migration.
nametextVendor name.
account_numbertextAccount number with the vendor.
contact_emailtextVendor contact email.
contact_phonetextVendor contact phone.
address, city, state, country, postal_codetextVendor mailing address.
shiphero_vendor_idtextExternal ShipHero vendor id.
created_attimestamptzDefaults 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.

ColumnTypeNotes
idintegerPrimary key. Defaults from the Warehouses_id_seq sequence.
WarehousetextWarehouse name.
Warehouse_OperatortextOperator running the warehouse.
Warehouse_IDtextExternal ShipHero warehouse id.
Replen_Order_Lead_TimebigintReplenishment order lead time.
zip_codetextUsed for shipping-zone distance calculations.
address_line_1, address_line_2, city, statetextWarehouse address.
created_attimestamptzDefaults now().
updated_attimestampUpdate timestamp.

warehouse_brands_junction

Many-to-many join between Warehouses and Brands. Each row means one warehouse serves one brand.

ColumnTypeNotes
idbigintPrimary key. Generated always as identity.
warehouseintegerNot null. → references Warehouses.
brandintegerNot null. → references Brands.
created_attimestamptzDefaults now().

On this page