Automation

The triggers that keep derived data in sync and the database functions the app calls.

The database does real work on its own. Triggers fire on insert, update, and delete to keep derived columns correct and to notify external systems. Functions (many exposed as Supabase RPCs) run the heavier queries the portal and the worker rely on.

This page catalogs both. The domain pages link here for the details.

Scope

Only application logic in the public schema is listed. The auth, storage, realtime, net, cron, vault, pgsodium, extensions, and supabase_functions schemas hold Supabase and PostgreSQL platform internals, which this reference does not cover.

Triggers

Triggers group into three jobs: keep denormalized columns in sync, recompute rollups, and notify an external system (usually N8N over an HTTP webhook).

Keep copies in sync

These triggers copy a value from a related table so reads stay fast.

Table	Trigger	Fires	What it does
Base SKUs	`update_brand_name_for_base_sku` (+ `_2`)	AFTER UPDATE / INSERT	Sync `brand_name` from the linked Brand.
Product SKUs	`update_brand_name_for_product_sku` (+ `_2`)	AFTER UPDATE / INSERT	Sync `brand_name` from the linked Brand.
Product SKUs	`update_base_sku_inventory_name_for_product_sku` (+ `_2`)	AFTER UPDATE / INSERT	Push `inventory_name` onto the linked Base SKU.
End Customer (Amazon buyer)	`standardize_state_on_insert`	BEFORE INSERT / UPDATE	Normalize the `State` value.
Shipments	`trigger_sync_shipment_store`	BEFORE INSERT	Copy `store` from the linked Order.
Shipment Line Items	`trigger_update_tracking_id_for_shipment_line_item`	AFTER INSERT / UPDATE	Fill `shipment_tracking_id`.

The two-way `order_multiple` sync

order_multiple lives on both Base SKUs and Inventory, and two triggers keep them matched in both directions.

Table	Trigger	Fires	What it does
Base SKUs	`sync_base_sku_order_multiple_with_inventory`	AFTER UPDATE	Push a Base SKU's `order_multiple` down to its Inventory rows.
Inventory	`update_base_sku_order_multiple_for_inventory` (+ `_2`)	AFTER UPDATE / INSERT	Push an Inventory row's `order_multiple` up to its Base SKU.

Recompute rollups

These triggers recompute totals on a parent row when a child row changes.

Table	Trigger	Fires	What it does
Shipments	`trigger_update_orders`	AFTER UPDATE	Recompute the Order's totals (`update_orders_on_new_shipment`).
Shipments	`trigger_update_orders_on_deleted_shipment`	AFTER DELETE	Recompute the Order after a shipment is removed.
Shipment Line Items	`trigger_update_orders`	AFTER UPDATE	Recompute Order totals (`update_orders_on_new_line_item`).
Shipment Line Items	`trigger_update_orders_on_deleted_line_item`	AFTER DELETE	Recompute Order totals after a line item is removed.
Inventory Purchase Order Line Items	`line_item_status_update`	BEFORE INSERT / UPDATE	Set `discrepancy_detected` (`update_purchase_order_line_items`), only for closed POs. See When a discrepancy is flagged.
Inventory Purchase Order Line Items	`trigger_update_inventory_sum`	AFTER UPDATE / DELETE	Recompute the linked Inventory row's inbound sum.
Inventory Purchase Orders	`update_purchase_order_status_trigger`	AFTER UPDATE	Cascade PO status down to its line items.
audit_jobs	`trigger_update_lead_audit_count`	AFTER INSERT	Bump the lead's `total_audits_count` and `last_tool_use`.
`auth.users`	`user_email_change`	AFTER UPDATE	Sync the new email onto userParentBrands.

Notify an external system

Two triggers post to an N8N webhook so a notification workflow can run.

Table	Trigger	Fires	What it does
Inventory	`inventory_update_trigger`	AFTER INSERT / UPDATE	Call `notify_n8n_inventory_update()` to tell N8N inventory changed.
Orders	`shipping_threshold_exceeded_trigger`	AFTER UPDATE	POST to an N8N webhook when an order crosses its shipping threshold.
Inventory Purchase Order Line Items	`purchase_order_line_item_discrepancy_trigger`	AFTER UPDATE	POST to an N8N webhook when `discrepancy_detected` turns true, which only happens once the PO is closed.

Triggers reach outside the database

The three webhook triggers make outbound HTTP calls through supabase_functions.http_request. A change to those rows can fire an N8N workflow. Keep that in mind when bulk-updating Orders, Inventory, or PO line items.

Functions

The public schema holds many functions. Most are read-only RPCs the portal calls to build a screen; a few write data or drain a queue. They are grouped below by what they do.

Queue processors

Function	Purpose
`process_shipment_queue()`	Turn queued ShipHero shipment payloads into Shipments. Drains shipments_queue.
`process_po_updates_queue()`	Apply queued ShipHero PO-update payloads. Drains purchase_order_updates_queue.

Reporting and financials

Function	Purpose
`generate_weekly_report()`	Build the per-account weekly rollup rows in Weekly Reports.
`net_revenue_calc(...)`	Compute net revenue for a period from sale price, commission, invoice amounts, and credits.
`resend_shipping_threshold_notifications()`	Re-send shipping-threshold notifications for flagged orders.

Dashboard and analytics

Read-only functions that back the dashboard and reporting screens. Most take a date range and optional brand, store, and category filters.

Function	Purpose
`get_consolidated_dashboard_metrics(...)`	The main dashboard's combined metric bundle.
`get_summary_metrics(...)`, `get_summary_metrics_2(...)`	Summary KPI tiles (v2 adds brand/store/category filters).
`get_daily_shipped(...)`, `get_monthly_shipped(...)`	Units shipped by day / by month.
`get_product_shipments(...)`	Shipments broken out by product.
`get_state_stats(...)`	Shipments by destination state.
`get_customer_stats(...)`, `get_customer_stats_2(...)`	Customer counts and repeat-buyer stats.
`get_repeat_purchases_over_time(...)`, `get_repeated_orders(...)`	Repeat-purchase trends.
`get_avg_time_between_reorders(...)`	Average gap between customer reorders.
`shipped_by_pivot(...)`	Pivoted shipped-units breakdown.
`todays_warehouse_stats()`, `warehouse_stats_date_range(...)`	Warehouse throughput today / over a range.
`order_level_shipping_metrics(...)`	Shipping metrics aggregated per order.
`orders_test_allocation(...)`, `orders_test_allocation_by_store(...)`, `orders_test_no_allocation(...)`	Order/shipping-cost allocation experiments.
`get_weekly_orders_by_pack_size(...)`, `get_weekly_orders_by_store(...)`	Weekly order counts by pack size / store.
`get_shipping_cost_per_sku_allocated(...)` (+ `_option_1`, `_option_2`)	Per-SKU shipping-cost allocation methods.

Legacy analytics functions

Some functions are older or experimental, with names like orders_shipped_metrics(Old-Not-All), order_test_left, and get_daily_shipped_test. Prefer the current variants (for example get_summary_metrics_2 over get_summary_metrics) unless you are tracing old behavior.

Inventory and replenishment

Function	Purpose
`get_daily_demand(...)`	Average daily demand for one SKU over a trailing window.
`get_bulk_daily_demand(...)`, `get_bulk_daily_demand_all_periods(...)`	Daily demand for many SKUs at once (all-periods returns 15/30/60-day figures).
`inventory_replen_report()`	The replenishment worksheet behind the Replen Report.
`inventory_replen_report_for_draft_PO_creation(...)`	The replen report tuned for building draft POs, taking trailing-days, order-interval, and safety-stock inputs.

Shipment search and sorting

Function	Purpose
`search_shipment_ids(...)`	Find shipment ids by a search string over a date range.
`get_shipment_ids_by_product_skus(...)`	Shipment ids filtered by product SKUs (with store/warehouse/threshold filters).
`sort_shipments_by_foreign_column(...)`, `sort_shipments_by_total_units(...)`	Sort shipments by a joined column or by total units.

Shipping zones

Function	Purpose
`calculate_zone_from_distance(...)`	Map a distance in miles to a shipping zone (1 to 8; 9 for territories/military).
`get_missing_zip_codes(...)`	Destination ZIPs that could not be resolved to a zone.
`get_zone_analysis_summary(...)`, `get_zone_analysis_states(...)`	Zone-analysis totals and per-state breakdown.
`get_zone_analysis_warehouses(...)`, `get_zone_analysis_stores(...)`, `get_zone_analysis_product_skus(...)`	Filter options for the zone-analysis screen.
`get_zone_analysis_uncategorized_summary(...)`, `get_zone_analysis_uncategorized_breakdown(...)`	Shipments whose zone could not be categorized.

Shipping Audit Tool

These back the standalone Shipping Audit Tool.

Function	Purpose
`get_cost_for_shipment(...)`	Look up a provider's cost for a weight, zone, and date.
`aggregate_shipment_details(...)`, `aggregate_shipment_categories(...)`, `aggregate_shipment_monthly(...)`	Roll up a job's results overall, by category, and by month.
`get_shipment_details_batch(...)`, `get_shipment_details_count(...)`	Paged shipment detail for the results view.
`get_shipment_details_for_csv(...)`, `export_shipment_details_csv(...)`, `export_shipment_details_csv_chunk(...)`	Build the results CSV, whole or in chunks.
`increment_deliverable_download_count(...)`	Record a deliverable download.

Users and admin

Function	Purpose
`is_admin_for_brand(...)`	Whether the current user is an admin for an account.
`get_current_user_display_info()`	The signed-in user's display name and avatar.
`get_auth_users_for_admin(...)`	Auth-user details (email, ban status) for admin screens.
`get_user_email_by_uuid(...)`, `get_user_uuid_by_email(...)`	Translate between a user id and email.

ShipHero credentials and secrets

Function	Purpose
`save_shiphero_credentials(...)`	Store an account's encrypted ShipHero credentials and tokens.
`read_shiphero_credentials(...)`, `get_shiphero_credentials(...)`	Read them back for API calls.
`check_duplicate_credentials(...)`	Detect reused ShipHero credentials.
`insert_secret(...)`, `insert_secret_2(...)`, `read_secret(...)`	Wrappers over the vault for storing and reading secrets.
`generate_brand_slug()`	Generate the unique `slug` default for a new Brand.

Security-definer functions

The credential, secret, and admin functions run as SECURITY DEFINER so they can reach protected data on the caller's behalf. Treat them as trusted surface area and review changes carefully.

Utility

Function	Purpose
`list_products(...)`	Paged, searchable product listing for the Products screen.
`standardize_states()`	Backfill/normalize state values in bulk.
`to_date_time_safe(...)`	Parse a text value into a timestamp, tolerating bad input.
`get_complete_schema()`	Return the database schema as JSON.
`rls_auto_enable()`	Event trigger that enables Row-Level Security on new tables automatically.

On this page