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 five jobs: keep denormalized columns in sync, recompute rollups, notify an external system (usually N8N over an HTTP webhook), guard a column against direct changes, and record sign-ins.
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. |
| Brands | trg_add_super_admins_to_new_brand | AFTER INSERT | Give every NC Super Admin a userRoles row on the new account, so Super Admins are always on every account. Runs add_super_admins_to_new_brand(). |
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. Only the Orders one is switched on.
| Table | Trigger | Fires | What it does |
|---|---|---|---|
| Inventory | inventory_update_trigger | AFTER INSERT / UPDATE | Call notify_n8n_inventory_update() to tell N8N inventory changed. Disabled, so it never fires. |
| Orders | shipping_threshold_exceeded_trigger | AFTER UPDATE | POST to an N8N webhook when an order crosses its shipping threshold. |
The receiving discrepancy email is not trigger-driven. The Purchase Order Update Handler calls it once per closed PO, after it has written every line item.
Triggers reach outside the database
The two 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 or Inventory.
The staging database leaves both triggers out. See Staging and releases.
Guard a column
These triggers refuse a change by app users (the anon and authenticated
roles) so a column can only change through its function.
| Table | Trigger | Fires | What it does |
|---|---|---|---|
| Orders | orders_exclude_from_metrics_guard | BEFORE UPDATE OF exclude_from_metrics | Refuse a direct change to the flag. Fires only when the value changes. Runs guard_order_exclude_from_metrics(). |
| Orders | orders_exclude_from_metrics_guard_insert | BEFORE INSERT | Refuse a new order that is already flagged. Fires only when the flag is true. |
Sign-in and sessions
These triggers keep the auth_events history and send new-sign-in emails.
| Table | Trigger | Fires | What it does |
|---|---|---|---|
auth.sessions | trg_auth_session_created | AFTER INSERT | Write a sign_in row to auth_events (log_auth_session_event). |
auth.sessions | trg_auth_session_deleted | AFTER DELETE | Write a sign_out row. Also fires when a session expires. |
| auth_events | trg_notify_new_device | AFTER INSERT | On a sign_in from an address the account has never used, schedule send_new_device_notification to run 5 seconds later. Skips an account's first ever sign-in. |
The two auth.sessions triggers swallow their own errors, so a logging problem
can never stop someone signing in.
The 5-second delay gives middleware time to write the city and country onto the
row first. send_new_device_notification then posts the event to the web app's
/api/security/new-device route through pg_net, and the app sends the email
through Resend. The route only accepts calls carrying the shared secret stored
in Vault as notify_secret, which must match the app's
NEW_DEVICE_NOTIFY_SECRET.
A pg_cron job, prune-auth-events, deletes auth_events rows older than 12
months every night at 03:15 UTC.
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. Runs on the weekly schedule; only the service role and the database owner can call it. Skips an account that already has a row for that week. |
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. |
Exclude from metrics
| Function | Purpose |
|---|---|
set_order_exclude_from_metrics(p_order_id, p_exclude) | Switch an order's exclude_from_metrics flag. NC staff on the order's account only. SECURITY DEFINER, and the only way app users can change the flag. See Orders. |
These functions and views skip shipments on an order flagged Exclude from metrics:
- Dashboard and analytics:
get_summary_metrics_2,get_consolidated_dashboard_metrics,get_daily_shipped,get_monthly_shipped,orders_test_allocation,orders_test_allocation_by_store,todays_warehouse_stats,warehouse_stats_date_range,order_level_shipping_metrics,get_weekly_orders_by_store,get_weekly_orders_by_pack_size,get_product_shipments, and the threeget_shipping_cost_per_sku_allocatedmethods. - Product Analysis:
get_product_summary_metrics,get_product_daily_shipped,get_product_store_stats, andget_product_warehouse_stats. - Zone analysis: the four zone-analysis data functions and the
shipment_zone_analysisview. - Reporting:
net_revenue_calcandgenerate_weekly_report. - Daily demand:
get_daily_demand,get_bulk_daily_demand,get_bulk_daily_demand_all_periods,inventory_replen_report,inventory_replen_report_for_draft_PO_creation, and theinventory_replen_viewandinventory_summaryviews.
Each one adds a single filter line marked -- Exclude from metrics and is
otherwise unchanged. A shipment with no order still counts.
Not filtered
The legacy copies (get_summary_metrics, get_daily_shipped_test,
order_test_left, orders_test_no_allocation, orders_shipped_metrics)
and the customer, state, and pivot functions (get_state_stats,
get_customer_stats and _2, get_repeat_purchases_over_time,
get_repeated_orders, get_avg_time_between_reorders, shipped_by_pivot)
still count flagged orders. Nothing in the portal, the worker, or the MCP
server calls them. Add the filter before a screen starts using one.
The migrations are 20261111000000_exclude_orders_from_metrics.sql (the flag,
the function, the guards) and 20261111000100_exclude_orders_from_metrics_stats.sql
(the filters). supabase/rollbacks/ holds the file that puts every function and
view back, and supabase/tests/exclude_from_metrics.sql checks the whole
feature and rolls itself back.
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. It runs with the caller's own access, so it only counts shipments on accounts the caller can see. Signed-out callers can't run it. |
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. |
The four zone-analysis data functions (summary, states, and both uncategorized
functions) share the same filters. Pack size is units shipped: the sum of
SKU_Quantity times the Product SKU's summed bundle_count in
product_sku_base_sku_junction, capped at 10. That matches
sort_shipments_by_total_units. The Product SKU filter matches on
"Product SKUs"."Product_SKU".
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. |
The roll-up, shipment detail, and CSV functions don't check who is asking, so only the service role can run them. Signed-in and anonymous users can't.
Users and admin
| Function | Purpose |
|---|---|
is_admin_for_brand(...) | Whether the current user holds the NC Super Admin role on an account. It checks the role ID, so the role's name doesn't matter. |
is_nc_staff() | Whether the current user is NC staff (NC Super Admin or NC Internal User) on any active account. The Client User rules on brand and internal tables use it. |
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 the Super Admin user screens. It checks that the signed-in caller is an NC Super Admin on an active row and refuses anyone else. Signed-out callers can't run it. |
get_user_email_by_uuid(...) | Looks up a user's email from their user id. |
get_user_uuid_by_email(...) | Looks up a user id from an email. Only the service role can run it (the n8n invite flow uses it). Signed-in and anonymous users can't. |
Sessions and sign-in history
These back the Security screen and the
admin session controls. The ones without a user id only ever act on the caller's
own account. The admin ones check the NC Super Admin role ID inside the function
and return nothing (or false) for anyone else.
| Function | Purpose |
|---|---|
get_user_sessions() | The caller's own active sessions, with a flag on the current one. |
get_my_auth_events(p_limit) | The caller's own sign-in history from auth_events. |
revoke_my_session(p_session_id) | End one of the caller's own sessions. |
get_user_sessions_for_admin(p_user_id) | Another user's active sessions. NC Super Admins only. |
revoke_user_session_as_admin(p_user_id, p_session_id) | End another user's session. NC Super Admins only. |
record_sign_in_location(p_city, p_region, p_country) | Called by middleware to attach the edge location to the caller's latest sign-in. |
send_new_device_notification(p_event_id) | Post one sign-in to the app's new-sign-in email route. |
reject_password_sign_in(event) | Supabase Auth hook. Refuses any password sign-in with a 403. See Architecture. |
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. |