Database

A reference for the National Commerce PostgreSQL database: tables, relationships, and the conventions that run through them.

This section documents the National Commerce database. It is the PostgreSQL database that Supabase hosts and the portal reads and writes. Use it to find a table, understand a column, and see how the pieces link together.

The Data Flows page explains how records move between ShipHero, N8N, the worker, and this database. This section explains what those records look like once they land.

How the tables are grouped

The tables split into a few domains. Each page below covers one domain.

Brands & Accounts

Parent brands, brands (accounts), stores, vendors, and warehouses: the tenancy backbone.

Products & SKUs

Base SKUs, Product SKUs, Case SKUs, categories, and the bundle bill of materials.

Inventory

Stock per SKU per warehouse, plus the reorder-point settings.

Orders & Shipments

Orders, shipments, line items, customers, and the shipment intake queue.

Purchase Orders

Real and draft replenishment POs, their line items, and the update queue.

SKU Sync

Runs, per-SKU results, and the review queue for new ShipHero SKUs.

Reporting & Financials

Weekly rollups, net-revenue snapshots, and the financial model inputs.

Users & Access

Roles, per-account grants, parent-brand access, and user settings.

Shipping Audit Tool

The standalone audit product: leads, jobs, provider rates, and deliverables.

Automation

Every trigger and the key functions that keep derived data in sync.

Maintenance & Scratch

Backups, staging tables, the error log, and the test table.

The shape of it all

Almost everything hangs off Brands. A Brand row is one ShipHero connection, what the app calls an account. SKUs, inventory, orders, shipments, purchase orders, stores, and access grants all carry a brand.

Conventions to know first

A few patterns repeat across the whole database. Learn them once and every page reads more easily.

Mixed naming styles

The database grew over time, so names are not uniform.

Some tables use quoted, capitalized names with spaces: Brands, Base SKUs, Product SKUs, End Customer (Amazon buyer). Others use plain snake_case: product_categories, sku_sync_runs, audit_jobs.
Column names mix styles too: Order_Number, Bundle_Count, brand_id, and userId all appear.
A handful of sequences keep an old name after a rename. For example, Base SKUs draws its id from Base Item SKUs_id_seq, and both the real and draft PO tables share PO sequences. This is a historical artifact, not a bug.

Names are exact

Table and column identifiers are shown exactly as they exist in the database, spaces and capitals included. Quote them in SQL ("Product SKUs") when a name contains spaces or uppercase letters.

Standard audit columns

Most tables carry the same four bookkeeping columns. To keep the reference readable, the domain pages usually collapse them into a single row rather than repeating them.

Column	Type	Meaning
`created_at`	timestamp	When the row was created (usually defaults to `now()`).
`updated_at`	timestamp	When the row last changed.
`created_by`	varchar	Who created it.
`updated_by`	varchar	Who last changed it.

Brand scoping and Row-Level Security

Nearly every business table has a brand (or brand_id) column pointing at Brands. This is how the app keeps one account's data separate from another's.

Row-Level Security (RLS) is enabled on tables and keys off a user's grants in userRoles and userParentBrands. A user sees only the accounts they may access.

Denormalized columns kept in sync by triggers

Some columns copy a value that lives on another table: brand_name on the SKU tables, inventory_name shared between Product and Base SKUs, email on userParentBrands. Triggers keep these copies current. The Automation page lists every trigger and what it maintains.

Queues

Several tables ending in _queue hold raw JSON payloads waiting to be processed: shipments_queue, purchase_order_updates_queue. A database function drains each one. See the relevant domain page and Automation.

Keep this reference current

When the schema changes, update the MDX files under apps/docs/content/docs/database. Treat these pages as a living reference.