Shipping Audit Tool
Database tables for the standalone Shipping Audit Tool that compares a lead's shipments against provider rate cards and returns savings deliverables.
The audit_* tables power the Shipping Audit Tool. A lead submits an audit job, uploads a shipment file, the tool scores each row against provider shipping-cost rate cards, and it produces downloadable deliverables. This page documents every table and how they connect.
Separate product
These audit_* tables belong to the standalone Shipping Audit Tool, which runs at audit.nationalcommerce.com. It is a separate product from the main portal, so these tables do not reference Brands or accounts. All foreign keys stay within this page. See /docs/data-flows for where the audit tool fits in the wider system, and /docs/database for the full schema index.
audit_leads
A person who used the audit tool, identified by a unique validated email. total_audits_count starts at 1 and is bumped by a trigger each time the lead submits a job.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| text | NOT NULL, UNIQUE. CHECK: must match an email regex | |
| referral_source | text | Where the lead came from |
| total_audits_count | integer | NOT NULL, default 1. Incremented by trigger |
| last_tool_use | timestamptz | NOT NULL, default now(). Updated by trigger |
| created_at | timestamptz | NOT NULL, default now() |
A lead has many audit_jobs. When a job is inserted, trigger_update_lead_audit_count runs update_lead_audit_count(), which increments total_audits_count and refreshes last_tool_use. See /docs/database/automation.
audit_jobs
One audit run for a lead. It tracks processing state, row counts, and the computed savings summary. job_status is the enum audit_job_status and defaults to 'pending'. view_token is a token used to build a shareable results link.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| lead_id | uuid | NOT NULL. → references audit_leads |
| job_status | enum audit_job_status | USER-DEFINED. NOT NULL, default 'pending' |
| view_token | uuid | NOT NULL, default gen_random_uuid(). Shareable-link token |
| workflow_id | uuid | External workflow run identifier |
| total_rows | integer | Rows in the uploaded file |
| processed_rows | integer | NOT NULL, default 0 |
| excluded_rows | integer | NOT NULL, default 0 |
| column_mappings | jsonb | How upload columns map to expected fields |
| results_summary | jsonb | Summarized audit results |
| ups_total_savings | numeric | Total UPS savings found |
| usps_total_savings | numeric | Total USPS savings found |
| ups_processed_shipments | integer | Default 0 |
| usps_processed_shipments | integer | Default 0 |
| error_message | text | Set when a run fails |
| submitted_at_formatted | text | Display-formatted submit time |
| started_processing_at | timestamptz | When processing began |
| completed_at | timestamptz | When the run finished |
| email_sent_at | timestamptz | When the results email was sent |
| created_at | timestamptz | NOT NULL, default now() |
A job belongs to one audit_leads (lead_id). It has many audit_uploads and many audit_deliverables. Its rows are scored against audit_provider_shipping_costs.
audit_uploads
Files uploaded for a job, stored in object storage at storage_path. upload_type is the enum upload_type and defaults to 'user_csv'.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| job_id | uuid | NOT NULL. → references audit_jobs |
| upload_type | enum upload_type | USER-DEFINED. NOT NULL, default 'user_csv' |
| original_filename | text | NOT NULL |
| storage_path | text | NOT NULL. Object storage location |
| file_size_bytes | integer | NOT NULL |
| created_at | timestamptz | NOT NULL, default now() |
An upload belongs to one audit_jobs (job_id).
audit_shipping_providers
A shipping carrier the tool compares against, such as UPS or USPS. Both name and short_code are unique and must be non-empty.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| name | text | NOT NULL, UNIQUE. CHECK: trimmed length > 0 |
| short_code | text | NOT NULL, UNIQUE. CHECK: trimmed length > 0 |
| description | text | Provider description |
| logo_url | text | Provider logo |
| brand_color | text | Default '#2CA01C' |
| display_order | integer | Default 0. Sort order in the UI |
| is_active | boolean | NOT NULL, default true |
| is_default | boolean | NOT NULL, default false |
| created_at | timestamptz | NOT NULL, default now() |
A provider has many audit_provider_shipping_costs and may be referenced by many audit_deliverables.
audit_provider_shipping_costs
The rate card: the cost for a given (provider, weight, zone) as of effective_date. Audit jobs use these rows to price each shipment.
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| provider_id | uuid | NOT NULL. → references audit_shipping_providers |
| weight_lbs | numeric | NOT NULL. CHECK: weight_lbs > 0 |
| zone | integer | NOT NULL. CHECK: zone >= 1 AND zone <= 9 |
| cost | numeric | NOT NULL. CHECK: cost >= 0 |
| effective_date | date | NOT NULL. Date this rate applies from |
| created_at | timestamptz | NOT NULL, default now() |
A cost row belongs to one audit_shipping_providers (provider_id).
audit_deliverables
Output files produced by a job, such as a report CSV or PDF, stored at storage_path. deliverable_type and file_type are Postgres enums. Downloads are tracked via download_count and the first/last download timestamps, bumped by increment_deliverable_download_count().
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK, default gen_random_uuid() |
| job_id | uuid | NOT NULL. → references audit_jobs |
| provider_id | uuid | Nullable. → references audit_shipping_providers |
| deliverable_type | enum deliverable_type | USER-DEFINED. NOT NULL |
| file_type | enum file_type | USER-DEFINED. Nullable |
| storage_path | text | NOT NULL. Object storage location |
| file_size_bytes | integer | NOT NULL |
| download_count | integer | NOT NULL, default 0 |
| first_downloaded_at | timestamptz | Set on first download |
| last_downloaded_at | timestamptz | Updated on each download |
| created_at | timestamptz | NOT NULL, default now() |
A deliverable belongs to one audit_jobs (job_id) and may optionally reference one audit_shipping_providers (provider_id).
Enums and constraints
The USER-DEFINED columns above are Postgres enums (audit_job_status, upload_type, deliverable_type, file_type). Insert only valid enum values. CHECK constraints also enforce a valid email on audit_leads, weight_lbs > 0, zone between 1 and 9, cost >= 0, and non-empty provider name and short_code.