National Commerce Docs
Database

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.

TableTriggerFiresWhat it does
Base SKUsupdate_brand_name_for_base_sku (+ _2)AFTER UPDATE / INSERTSync brand_name from the linked Brand.
Product SKUsupdate_brand_name_for_product_sku (+ _2)AFTER UPDATE / INSERTSync brand_name from the linked Brand.
Product SKUsupdate_base_sku_inventory_name_for_product_sku (+ _2)AFTER UPDATE / INSERTPush inventory_name onto the linked Base SKU.
End Customer (Amazon buyer)standardize_state_on_insertBEFORE INSERT / UPDATENormalize the State value.
Shipmentstrigger_sync_shipment_storeBEFORE INSERTCopy store from the linked Order.
Shipment Line Itemstrigger_update_tracking_id_for_shipment_line_itemAFTER INSERT / UPDATEFill shipment_tracking_id.
Brandstrg_add_super_admins_to_new_brandAFTER INSERTGive 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.

TableTriggerFiresWhat it does
Base SKUssync_base_sku_order_multiple_with_inventoryAFTER UPDATEPush a Base SKU's order_multiple down to its Inventory rows.
Inventoryupdate_base_sku_order_multiple_for_inventory (+ _2)AFTER UPDATE / INSERTPush 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.

TableTriggerFiresWhat it does
Shipmentstrigger_update_ordersAFTER UPDATERecompute the Order's totals (update_orders_on_new_shipment).
Shipmentstrigger_update_orders_on_deleted_shipmentAFTER DELETERecompute the Order after a shipment is removed.
Shipment Line Itemstrigger_update_ordersAFTER UPDATERecompute Order totals (update_orders_on_new_line_item).
Shipment Line Itemstrigger_update_orders_on_deleted_line_itemAFTER DELETERecompute Order totals after a line item is removed.
Inventory Purchase Order Line Itemsline_item_status_updateBEFORE INSERT / UPDATESet discrepancy_detected (update_purchase_order_line_items), only for closed POs. See When a discrepancy is flagged.
Inventory Purchase Order Line Itemstrigger_update_inventory_sumAFTER UPDATE / DELETERecompute the linked Inventory row's inbound sum.
Inventory Purchase Ordersupdate_purchase_order_status_triggerAFTER UPDATECascade PO status down to its line items.
audit_jobstrigger_update_lead_audit_countAFTER INSERTBump the lead's total_audits_count and last_tool_use.
auth.usersuser_email_changeAFTER UPDATESync 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.

TableTriggerFiresWhat it does
Inventoryinventory_update_triggerAFTER INSERT / UPDATECall notify_n8n_inventory_update() to tell N8N inventory changed. Disabled, so it never fires.
Ordersshipping_threshold_exceeded_triggerAFTER UPDATEPOST 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.

TableTriggerFiresWhat it does
Ordersorders_exclude_from_metrics_guardBEFORE UPDATE OF exclude_from_metricsRefuse a direct change to the flag. Fires only when the value changes. Runs guard_order_exclude_from_metrics().
Ordersorders_exclude_from_metrics_guard_insertBEFORE INSERTRefuse 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.

TableTriggerFiresWhat it does
auth.sessionstrg_auth_session_createdAFTER INSERTWrite a sign_in row to auth_events (log_auth_session_event).
auth.sessionstrg_auth_session_deletedAFTER DELETEWrite a sign_out row. Also fires when a session expires.
auth_eventstrg_notify_new_deviceAFTER INSERTOn 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

FunctionPurpose
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

FunctionPurpose
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.

FunctionPurpose
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

FunctionPurpose
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

FunctionPurpose
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 three get_shipping_cost_per_sku_allocated methods.
  • Product Analysis: get_product_summary_metrics, get_product_daily_shipped, get_product_store_stats, and get_product_warehouse_stats.
  • Zone analysis: the four zone-analysis data functions and the shipment_zone_analysis view.
  • Reporting: net_revenue_calc and generate_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 the inventory_replen_view and inventory_summary views.

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

FunctionPurpose
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

FunctionPurpose
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.

FunctionPurpose
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

FunctionPurpose
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.

FunctionPurpose
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

FunctionPurpose
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

FunctionPurpose
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