ALTER TABLE public.carriers ADD COLUMN IF NOT EXISTS company_code text;
ALTER TABLE public.shippers ADD COLUMN IF NOT EXISTS company_code text;
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS driver_code text;
ALTER TABLE public.carriers ADD COLUMN IF NOT EXISTS archived_at timestamptz;
ALTER TABLE public.shippers ADD COLUMN IF NOT EXISTS archived_at timestamptz;
ALTER TABLE public.manifests ADD COLUMN IF NOT EXISTS requested_pickup_date date;
ALTER TABLE public.manifests ADD COLUMN IF NOT EXISTS driver_assigned_at timestamptz;
ALTER TABLE public.shipments ADD COLUMN IF NOT EXISTS hawb text;
ALTER TABLE public.shipments ADD COLUMN IF NOT EXISTS pro_number text;
CREATE UNIQUE INDEX IF NOT EXISTS carriers_company_code_idx ON public.carriers (lower(company_code));
CREATE UNIQUE INDEX IF NOT EXISTS shippers_company_code_idx ON public.shippers (lower(company_code));
CREATE UNIQUE INDEX IF NOT EXISTS drivers_driver_code_idx ON public.drivers (lower(driver_code));
ALTER TABLE public.shipper_users ADD COLUMN IF NOT EXISTS username text;
CREATE UNIQUE INDEX IF NOT EXISTS shipper_users_company_username_idx ON public.shipper_users (shipper_id, lower(username));
CREATE TABLE IF NOT EXISTS public.carrier_users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
carrier_id uuid REFERENCES public.carriers(id) ON DELETE CASCADE NOT NULL,
name text NOT NULL,
username text NOT NULL,
pin text NOT NULL,
is_master boolean NOT NULL DEFAULT false,
created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS carrier_users_company_username_idx ON public.carrier_users (carrier_id, lower(username));
ALTER TABLE public.carrier_users ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_all" ON public.carrier_users;
CREATE POLICY "anon_all" ON public.carrier_users FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE OR REPLACE FUNCTION public.audit_login_code_indexes()
RETURNS TABLE(missing text) LANGUAGE sql STABLE SECURITY DEFINER AS $$
WITH expected(name) AS (VALUES
('carriers_company_code_idx'),
('shippers_company_code_idx'),
('drivers_driver_code_idx'),
('carrier_users_company_username_idx'),
('shipper_users_company_username_idx'))
SELECT e.name FROM expected e
WHERE NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE schemaname='public' AND indexname=e.name
AND indexdef ILIKE '%UNIQUE%' AND indexdef ILIKE '%lower(%'
);
$$;
GRANT EXECUTE ON FUNCTION public.audit_login_code_indexes() TO anon;
-- Task #36: Driver GPS tracking — denormalized current position on drivers + breadcrumb table.
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS last_lat double precision;
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS last_lng double precision;
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS last_loc_at timestamptz;
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS last_loc_accuracy_m real;
ALTER TABLE public.drivers ADD COLUMN IF NOT EXISTS tracking_enabled boolean DEFAULT true;
CREATE TABLE IF NOT EXISTS public.driver_locations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
driver_id uuid REFERENCES public.drivers(id) ON DELETE CASCADE NOT NULL,
carrier_id uuid REFERENCES public.carriers(id) ON DELETE CASCADE NOT NULL,
lat double precision NOT NULL,
lng double precision NOT NULL,
accuracy_m real,
speed_kph real,
heading_deg real,
source text DEFAULT 'foreground',
captured_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS driver_locations_driver_idx ON public.driver_locations(driver_id, captured_at DESC);
CREATE INDEX IF NOT EXISTS driver_locations_carrier_idx ON public.driver_locations(carrier_id, captured_at DESC);
ALTER TABLE public.driver_locations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_all" ON public.driver_locations;
CREATE POLICY "anon_all" ON public.driver_locations FOR ALL TO anon USING (true) WITH CHECK (true);
DO $mig$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname='supabase_realtime' AND schemaname='public' AND tablename='drivers') THEN
EXECUTE 'ALTER PUBLICATION supabase_realtime ADD TABLE public.drivers';
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname='supabase_realtime' AND schemaname='public' AND tablename='driver_locations') THEN
EXECUTE 'ALTER PUBLICATION supabase_realtime ADD TABLE public.driver_locations';
END IF;
END $mig$;
-- Task #38: Server-side authorization for sensitive GPS data + auth principals.
-- Threat model: the embedded anon key can be extracted from index.html. After this block, an
-- attacker holding the anon key cannot:
-- 1. Read GPS data — driver_locations and the cached drivers.last_* columns are anon-revoked.
-- 2. Read any PIN — drivers.pin / carriers.pin / shippers.pin / carrier_users.pin / shipper_users.pin
-- are revoked from SELECT.
-- 3. WRITE any PIN, or create/update auth principals — every mutation on carrier_users /
-- shipper_users and on the `pin` column of drivers/carriers/shippers is gated behind
-- SECURITY DEFINER RPCs that re-prove the caller's identity (dispatcher PIN, carrier auth,
-- shipper auth, or the driver's own PIN). This closes the bypass where anon could INSERT a
-- carrier_users row with a chosen PIN and then call gps_drivers_for_carrier with it.
CREATE TABLE IF NOT EXISTS public.app_settings (
key text PRIMARY KEY,
value text
);
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_all" ON public.app_settings;
REVOKE ALL ON public.app_settings FROM anon;
-- ⚠️ ONE-TIME BOOTSTRAP: a random unguessable dispatcher PIN is generated on first run and shown
-- via RAISE NOTICE so the operator can copy it. Sign in once with this value, then rotate it
-- through the broker UI (Settings → Change PIN). Re-running this block does NOT regenerate it.
DO $bootstrap$
DECLARE v_pin text;
BEGIN
IF NOT EXISTS (SELECT 1 FROM public.app_settings WHERE key='dispatcher_pin') THEN
v_pin := encode(gen_random_bytes(6),'hex');
INSERT INTO public.app_settings(key,value) VALUES ('dispatcher_pin', v_pin);
RAISE NOTICE '────────────────────────────────────────────────────────────';
RAISE NOTICE 'DISPATCHER PIN bootstrapped to: %', v_pin;
RAISE NOTICE 'Copy it now — log in once, then change it via the broker UI.';
RAISE NOTICE '────────────────────────────────────────────────────────────';
END IF;
END $bootstrap$;
-- driver_locations: no direct REST access at all. Only RPCs may read/write.
DROP POLICY IF EXISTS "anon_all" ON public.driver_locations;
REVOKE ALL ON public.driver_locations FROM anon;
-- drivers: hide pin + GPS columns from SELECT, AND lock pin from anon INSERT/UPDATE so an
-- attacker cannot write a known PIN to take over a driver's identity. Non-pin / non-GPS columns
-- stay writable so the carrier UI can still create driver shells / rename / regen codes.
DO $sec$
DECLARE cols_sel text; cols_iu text;
BEGIN
SELECT string_agg(quote_ident(column_name), ', ') INTO cols_sel
FROM information_schema.columns
WHERE table_schema='public' AND table_name='drivers'
AND column_name NOT IN ('pin','last_lat','last_lng','last_loc_at','last_loc_accuracy_m','tracking_enabled');
SELECT string_agg(quote_ident(column_name), ', ') INTO cols_iu
FROM information_schema.columns
WHERE table_schema='public' AND table_name='drivers'
AND column_name NOT IN ('pin','last_lat','last_lng','last_loc_at','last_loc_accuracy_m','tracking_enabled');
IF cols_sel IS NOT NULL THEN
EXECUTE 'REVOKE SELECT, INSERT, UPDATE ON public.drivers FROM anon';
EXECUTE 'GRANT SELECT (' || cols_sel || ') ON public.drivers TO anon';
EXECUTE 'GRANT INSERT (' || cols_iu || ') ON public.drivers TO anon';
EXECUTE 'GRANT UPDATE (' || cols_iu || ') ON public.drivers TO anon';
END IF;
END $sec$;
-- carriers / shippers: hide pin from SELECT AND lock pin from anon INSERT/UPDATE. The non-pin
-- columns (name, company_code, contact, archived_at, etc.) stay anon-writable so the broker UI
-- can still create/rename/archive companies; the carrier+shipper PINs go in via the
-- admin_*_create RPCs which require the dispatcher PIN.
DO $sec$
DECLARE t text; cols_sel text; cols_iu text;
BEGIN
FOREACH t IN ARRAY ARRAY['carriers','shippers'] LOOP
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name=t) THEN CONTINUE; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name=t AND column_name='pin') THEN CONTINUE; END IF;
SELECT string_agg(quote_ident(column_name), ', ') INTO cols_sel
FROM information_schema.columns
WHERE table_schema='public' AND table_name=t AND column_name <> 'pin';
cols_iu := cols_sel;
EXECUTE format('REVOKE SELECT, INSERT, UPDATE ON public.%I FROM anon', t);
EXECUTE format('GRANT SELECT (%s) ON public.%I TO anon', cols_sel, t);
EXECUTE format('GRANT INSERT (%s) ON public.%I TO anon', cols_iu, t);
EXECUTE format('GRANT UPDATE (%s) ON public.%I TO anon', cols_iu, t);
END LOOP;
END $sec$;
-- carrier_users / shipper_users: completely lock anon out of writes. SELECT non-pin columns is
-- fine (lists, dropdowns); every INSERT / UPDATE / DELETE goes through the admin_* and
-- carrier_user_* / shipper_user_* RPCs below. This is the critical fix the security review
-- demanded — without it, a leaked anon key could mint a carrier_users row and call
-- gps_drivers_for_carrier with the chosen PIN.
DO $sec$
DECLARE t text; cols_sel text;
BEGIN
FOREACH t IN ARRAY ARRAY['carrier_users','shipper_users'] LOOP
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name=t) THEN CONTINUE; END IF;
SELECT string_agg(quote_ident(column_name), ', ') INTO cols_sel
FROM information_schema.columns
WHERE table_schema='public' AND table_name=t AND column_name <> 'pin';
EXECUTE format('REVOKE SELECT, INSERT, UPDATE, DELETE ON public.%I FROM anon', t);
EXECUTE format('GRANT SELECT (%s) ON public.%I TO anon', cols_sel, t);
END LOOP;
END $sec$;
-- gps_record_fix: driver writes a fix; auth via driver_code + pin.
CREATE OR REPLACE FUNCTION public.gps_record_fix(
p_driver_code text, p_pin text,
p_lat double precision, p_lng double precision,
p_accuracy_m real, p_speed_kph real, p_heading_deg real,
p_source text, p_captured_at timestamptz
) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
v_drv_id uuid;
v_car_id uuid;
v_id uuid;
v_at timestamptz := COALESCE(p_captured_at, now());
BEGIN
SELECT d.id, d.carrier_id INTO v_drv_id, v_car_id
FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE lower(d.driver_code)=lower(p_driver_code) AND d.pin=p_pin
AND c.archived_at IS NULL;
IF v_drv_id IS NULL THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
INSERT INTO public.driver_locations(driver_id,carrier_id,lat,lng,accuracy_m,speed_kph,heading_deg,source,captured_at)
VALUES (v_drv_id,v_car_id,p_lat,p_lng,p_accuracy_m,p_speed_kph,p_heading_deg,COALESCE(p_source,'foreground'),v_at)
RETURNING id INTO v_id;
-- Newer-only update so an offline-queued fix can't regress a fresher online pin.
UPDATE public.drivers
SET last_lat=p_lat, last_lng=p_lng, last_loc_at=v_at,
last_loc_accuracy_m=p_accuracy_m, tracking_enabled=true
WHERE id=v_drv_id AND (last_loc_at IS NULL OR v_at > last_loc_at);
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.gps_record_fix(text,text,double precision,double precision,real,real,real,text,timestamptz) TO anon;
-- gps_set_tracking: driver flips their own tracking_enabled flag.
CREATE OR REPLACE FUNCTION public.gps_set_tracking(p_driver_code text, p_pin text, p_enabled boolean)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_id uuid;
BEGIN
SELECT d.id INTO v_id FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE lower(d.driver_code)=lower(p_driver_code) AND d.pin=p_pin AND c.archived_at IS NULL;
IF v_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
UPDATE public.drivers SET tracking_enabled=p_enabled WHERE id=v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.gps_set_tracking(text,text,boolean) TO anon;
-- gps_drivers_for_dispatcher: every active driver's denormalized current position.
CREATE OR REPLACE FUNCTION public.gps_drivers_for_dispatcher(p_pin text)
RETURNS TABLE(
id uuid, name text, phone text, carrier_id uuid, driver_code text,
last_lat double precision, last_lng double precision, last_loc_at timestamptz,
last_loc_accuracy_m real, tracking_enabled boolean,
carrier_name text)
LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
DECLARE v_pin text;
BEGIN
SELECT value INTO v_pin FROM public.app_settings WHERE key='dispatcher_pin';
-- No silent fallback: if the row is missing, the dispatcher must run the bootstrap SQL first.
IF v_pin IS NULL OR p_pin IS NULL OR p_pin <> v_pin THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
RETURN QUERY
SELECT d.id, d.name, d.phone, d.carrier_id, d.driver_code,
d.last_lat, d.last_lng, d.last_loc_at, d.last_loc_accuracy_m, d.tracking_enabled,
c.name
FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE c.archived_at IS NULL
ORDER BY d.name ASC;
END $$;
GRANT EXECUTE ON FUNCTION public.gps_drivers_for_dispatcher(text) TO anon;
-- gps_drivers_for_carrier: drivers in the authenticated carrier company.
-- Also returns `pin` because the carrier Drivers panel needs it for the in-place PIN editor;
-- carriers already have direct REST access to driver PINs (column-level GRANT), so this is no
-- additional exposure — it just keeps the panel on a single roundtrip.
CREATE OR REPLACE FUNCTION public.gps_drivers_for_carrier(p_company_code text, p_username text, p_pin text)
RETURNS TABLE(
id uuid, name text, phone text, carrier_id uuid, driver_code text, pin text,
last_lat double precision, last_lng double precision, last_loc_at timestamptz,
last_loc_accuracy_m real, tracking_enabled boolean,
carrier_name text)
LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
DECLARE v_car_id uuid;
BEGIN
SELECT cu.carrier_id INTO v_car_id
FROM public.carrier_users cu
JOIN public.carriers c ON c.id=cu.carrier_id
WHERE lower(c.company_code)=lower(p_company_code)
AND lower(cu.username)=lower(p_username)
AND cu.pin=p_pin
AND c.archived_at IS NULL;
IF v_car_id IS NULL THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
RETURN QUERY
SELECT d.id, d.name, d.phone, d.carrier_id, d.driver_code, d.pin,
d.last_lat, d.last_lng, d.last_loc_at, d.last_loc_accuracy_m, d.tracking_enabled,
c.name
FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE d.carrier_id=v_car_id
ORDER BY d.name ASC;
END $$;
GRANT EXECUTE ON FUNCTION public.gps_drivers_for_carrier(text,text,text) TO anon;
-- gps_breadcrumbs_for_dispatcher: last N fixes for one driver.
CREATE OR REPLACE FUNCTION public.gps_breadcrumbs_for_dispatcher(p_pin text, p_driver_id uuid, p_limit int)
RETURNS TABLE(lat double precision, lng double precision, accuracy_m real, captured_at timestamptz)
LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
DECLARE v_pin text;
BEGIN
SELECT value INTO v_pin FROM public.app_settings WHERE key='dispatcher_pin';
IF v_pin IS NULL OR p_pin IS NULL OR p_pin <> v_pin THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
RETURN QUERY
SELECT dl.lat, dl.lng, dl.accuracy_m, dl.captured_at
FROM public.driver_locations dl
WHERE dl.driver_id = p_driver_id
ORDER BY dl.captured_at DESC
LIMIT GREATEST(1, COALESCE(p_limit, 20));
END $$;
GRANT EXECUTE ON FUNCTION public.gps_breadcrumbs_for_dispatcher(text,uuid,int) TO anon;
-- gps_breadcrumbs_for_carrier: same but only if the carrier owns the driver.
CREATE OR REPLACE FUNCTION public.gps_breadcrumbs_for_carrier(p_company_code text, p_username text, p_pin text, p_driver_id uuid, p_limit int)
RETURNS TABLE(lat double precision, lng double precision, accuracy_m real, captured_at timestamptz)
LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
DECLARE v_car_id uuid; v_owns boolean;
BEGIN
SELECT cu.carrier_id INTO v_car_id
FROM public.carrier_users cu
JOIN public.carriers c ON c.id=cu.carrier_id
WHERE lower(c.company_code)=lower(p_company_code)
AND lower(cu.username)=lower(p_username)
AND cu.pin=p_pin
AND c.archived_at IS NULL;
IF v_car_id IS NULL THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
SELECT EXISTS(SELECT 1 FROM public.drivers WHERE id=p_driver_id AND carrier_id=v_car_id) INTO v_owns;
IF NOT v_owns THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
RETURN QUERY
SELECT dl.lat, dl.lng, dl.accuracy_m, dl.captured_at
FROM public.driver_locations dl
WHERE dl.driver_id = p_driver_id
ORDER BY dl.captured_at DESC
LIMIT GREATEST(1, COALESCE(p_limit, 20));
END $$;
GRANT EXECUTE ON FUNCTION public.gps_breadcrumbs_for_carrier(text,text,text,uuid,int) TO anon;
-- dispatcher_pin_set: change the server-side dispatcher PIN. Requires the current PIN; if no row
-- exists yet (i.e. the bootstrap SQL above wasn't run), this RPC refuses — the dispatcher cannot
-- bootstrap themselves through the UI, otherwise an attacker holding the anon key on a fresh DB
-- could steal the dispatcher role by calling this first.
CREATE OR REPLACE FUNCTION public.dispatcher_pin_set(p_old_pin text, p_new_pin text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_cur text;
BEGIN
IF p_new_pin IS NULL OR length(trim(p_new_pin))=0 THEN
RAISE EXCEPTION 'invalid_pin' USING ERRCODE='22023';
END IF;
SELECT value INTO v_cur FROM public.app_settings WHERE key='dispatcher_pin';
IF v_cur IS NULL OR p_old_pin IS NULL OR p_old_pin <> v_cur THEN
RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501';
END IF;
UPDATE public.app_settings SET value = p_new_pin WHERE key='dispatcher_pin';
END $$;
GRANT EXECUTE ON FUNCTION public.dispatcher_pin_set(text,text) TO anon;
-- ============================================================================
-- Auth RPCs: replace the prior REST `pin=eq.<>` filter with SECURITY DEFINER
-- functions so the PIN columns can stay revoked from anon SELECT. None of these
-- ever return a `pin` value to the client.
-- ============================================================================
-- auth_driver: returns the driver identity if (driver_code, pin) match and the carrier is active.
CREATE OR REPLACE FUNCTION public.auth_driver(p_code text, p_pin text)
RETURNS TABLE(id uuid, name text, carrier_id uuid, driver_code text)
LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
BEGIN
RETURN QUERY
SELECT d.id, d.name, d.carrier_id, d.driver_code
FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE lower(d.driver_code)=lower(p_code)
AND d.pin=p_pin
AND c.archived_at IS NULL
LIMIT 1;
END $$;
GRANT EXECUTE ON FUNCTION public.auth_driver(text,text) TO anon;
-- auth_carrier_user: returns the carrier-user identity. Bootstraps a master row from
-- carriers.pin on first login (when no carrier_users exist yet for that carrier and the
-- typed PIN matches the legacy single-pin column). The legacy bootstrap path is preserved
-- so existing carriers with only `carriers.pin` set keep working.
CREATE OR REPLACE FUNCTION public.auth_carrier_user(p_code text, p_username text, p_pin text)
RETURNS TABLE(
id uuid, carrier_id uuid, name text, username text, is_master boolean,
carrier_name text, carrier_company_code text)
LANGUAGE plpgsql SECURITY DEFINER VOLATILE AS $$
DECLARE
v_car_id uuid; v_car_name text; v_car_code text; v_car_pin text;
v_uid uuid; v_user_name text; v_user_un text; v_is_master boolean; v_uname_canon text;
BEGIN
SELECT c.id, c.name, c.company_code, c.pin
INTO v_car_id, v_car_name, v_car_code, v_car_pin
FROM public.carriers c
WHERE lower(c.company_code)=lower(p_code) AND c.archived_at IS NULL;
IF v_car_id IS NULL THEN RETURN; END IF;
v_uname_canon := lower(COALESCE(p_username,'master'));
SELECT cu.id, cu.name, cu.username, cu.is_master
INTO v_uid, v_user_name, v_user_un, v_is_master
FROM public.carrier_users cu
WHERE cu.carrier_id=v_car_id
AND lower(cu.username)=v_uname_canon
AND cu.pin=p_pin;
-- Bootstrap: only when the user is asking for `master`, no users exist yet, and the typed PIN
-- matches the legacy `carriers.pin`. After this, login retries against the new carrier_users row.
IF v_uid IS NULL AND v_uname_canon='master' AND v_car_pin IS NOT NULL AND v_car_pin=p_pin THEN
IF NOT EXISTS (SELECT 1 FROM public.carrier_users WHERE carrier_id=v_car_id) THEN
INSERT INTO public.carrier_users (carrier_id, name, username, pin, is_master)
VALUES (v_car_id, COALESCE(v_car_name,'')||' (Master)', 'master', p_pin, true)
RETURNING carrier_users.id, carrier_users.name, carrier_users.username, carrier_users.is_master
INTO v_uid, v_user_name, v_user_un, v_is_master;
END IF;
END IF;
IF v_uid IS NULL THEN RETURN; END IF;
-- Backfill missing usernames so legacy rows from before we required a username get one.
PERFORM public._backfill_usernames(v_car_id, NULL);
RETURN QUERY SELECT v_uid, v_car_id, v_user_name, v_user_un, v_is_master, v_car_name, v_car_code;
END $$;
GRANT EXECUTE ON FUNCTION public.auth_carrier_user(text,text,text) TO anon;
-- auth_shipper_user: mirror of auth_carrier_user for shippers.
CREATE OR REPLACE FUNCTION public.auth_shipper_user(p_code text, p_username text, p_pin text)
RETURNS TABLE(
id uuid, shipper_id uuid, name text, username text, is_master boolean,
shipper_name text, shipper_company_code text)
LANGUAGE plpgsql SECURITY DEFINER VOLATILE AS $$
DECLARE
v_sp_id uuid; v_sp_name text; v_sp_code text; v_sp_pin text;
v_uid uuid; v_user_name text; v_user_un text; v_is_master boolean; v_uname_canon text;
BEGIN
SELECT s.id, s.name, s.company_code, s.pin
INTO v_sp_id, v_sp_name, v_sp_code, v_sp_pin
FROM public.shippers s
WHERE lower(s.company_code)=lower(p_code) AND s.archived_at IS NULL;
IF v_sp_id IS NULL THEN RETURN; END IF;
v_uname_canon := lower(COALESCE(p_username,'master'));
SELECT su.id, su.name, su.username, su.is_master
INTO v_uid, v_user_name, v_user_un, v_is_master
FROM public.shipper_users su
WHERE su.shipper_id=v_sp_id
AND lower(su.username)=v_uname_canon
AND su.pin=p_pin;
IF v_uid IS NULL AND v_uname_canon='master' AND v_sp_pin IS NOT NULL AND v_sp_pin=p_pin THEN
IF NOT EXISTS (SELECT 1 FROM public.shipper_users WHERE shipper_id=v_sp_id) THEN
INSERT INTO public.shipper_users (shipper_id, name, username, pin, is_master)
VALUES (v_sp_id, COALESCE(v_sp_name,'')||' (Master)', 'master', p_pin, true)
RETURNING shipper_users.id, shipper_users.name, shipper_users.username, shipper_users.is_master
INTO v_uid, v_user_name, v_user_un, v_is_master;
END IF;
END IF;
IF v_uid IS NULL THEN RETURN; END IF;
PERFORM public._backfill_usernames(NULL, v_sp_id);
RETURN QUERY SELECT v_uid, v_sp_id, v_user_name, v_user_un, v_is_master, v_sp_name, v_sp_code;
END $$;
GRANT EXECUTE ON FUNCTION public.auth_shipper_user(text,text,text) TO anon;
-- ============================================================================
-- Task #38: Authorized mutation RPCs. anon has no direct INSERT/UPDATE/DELETE on
-- carrier_users / shipper_users, and no INSERT/UPDATE on the `pin` column of
-- drivers / carriers / shippers. Every legitimate write goes through one of the
-- RPCs below, which re-prove the caller's identity (dispatcher PIN, carrier
-- code+username+pin, shipper code+username+pin, or the driver's own PIN).
-- ============================================================================
-- Helpers (kept SECURITY DEFINER + STABLE so they can be reused inside other RPCs).
CREATE OR REPLACE FUNCTION public._verify_dispatcher(p_pin text) RETURNS boolean
LANGUAGE sql SECURITY DEFINER STABLE AS $$
SELECT EXISTS (SELECT 1 FROM public.app_settings WHERE key='dispatcher_pin' AND value=p_pin)
$$;
-- dispatcher_verify: anon-callable wrapper used by the broker login UI to validate the typed PIN
-- against the server-side dispatcher_pin (the source of truth) before granting broker access.
-- Without this the random bootstrap PIN would be unusable on a fresh DB.
CREATE OR REPLACE FUNCTION public.dispatcher_verify(p_pin text) RETURNS boolean
LANGUAGE sql SECURITY DEFINER STABLE AS $$
SELECT public._verify_dispatcher(p_pin)
$$;
GRANT EXECUTE ON FUNCTION public.dispatcher_verify(text) TO anon;
CREATE OR REPLACE FUNCTION public._verify_carrier_user(p_code text, p_user text, p_pin text)
RETURNS TABLE(carrier_id uuid, user_id uuid, is_master boolean)
LANGUAGE sql SECURITY DEFINER STABLE AS $$
SELECT cu.carrier_id, cu.id, cu.is_master
FROM public.carrier_users cu
JOIN public.carriers c ON c.id=cu.carrier_id
WHERE lower(c.company_code)=lower(p_code) AND c.archived_at IS NULL
AND lower(cu.username)=lower(COALESCE(p_user,'master')) AND cu.pin=p_pin
LIMIT 1
$$;
CREATE OR REPLACE FUNCTION public._verify_shipper_user(p_code text, p_user text, p_pin text)
RETURNS TABLE(shipper_id uuid, user_id uuid, is_master boolean)
LANGUAGE sql SECURITY DEFINER STABLE AS $$
SELECT su.shipper_id, su.id, su.is_master
FROM public.shipper_users su
JOIN public.shippers s ON s.id=su.shipper_id
WHERE lower(s.company_code)=lower(p_code) AND s.archived_at IS NULL
AND lower(su.username)=lower(COALESCE(p_user,'master')) AND su.pin=p_pin
LIMIT 1
$$;
-- ──────────── Broker (dispatcher) admin RPCs ────────────
-- admin_carrier_setpin: rotate the legacy carriers.pin value (used as the master-bootstrap PIN).
CREATE OR REPLACE FUNCTION public.admin_carrier_setpin(p_disp_pin text, p_carrier_id uuid, p_new_pin text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_new_pin IS NULL OR length(p_new_pin)<1 THEN RAISE EXCEPTION 'pin required' USING ERRCODE='22023'; END IF;
UPDATE public.carriers SET pin=p_new_pin WHERE id=p_carrier_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_carrier_setpin(text,uuid,text) TO anon;
CREATE OR REPLACE FUNCTION public.admin_shipper_setpin(p_disp_pin text, p_shipper_id uuid, p_new_pin text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_new_pin IS NULL OR length(p_new_pin)<1 THEN RAISE EXCEPTION 'pin required' USING ERRCODE='22023'; END IF;
UPDATE public.shippers SET pin=p_new_pin WHERE id=p_shipper_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_shipper_setpin(text,uuid,text) TO anon;
-- admin_carrier_user_upsert: create OR update a carrier_users row. Pass NULL p_user_id to insert.
CREATE OR REPLACE FUNCTION public.admin_carrier_user_upsert(
p_disp_pin text, p_user_id uuid, p_carrier_id uuid,
p_name text, p_username text, p_pin text, p_is_master boolean
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_id uuid;
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_user_id IS NULL THEN
IF p_carrier_id IS NULL OR p_pin IS NULL OR length(p_pin)<1 THEN RAISE EXCEPTION 'carrier_id and pin required' USING ERRCODE='22023'; END IF;
INSERT INTO public.carrier_users (carrier_id, name, username, pin, is_master)
VALUES (p_carrier_id, p_name, lower(COALESCE(p_username,'master')), p_pin, COALESCE(p_is_master,false))
RETURNING id INTO v_id;
ELSE
UPDATE public.carrier_users SET
name = COALESCE(p_name, name),
username = COALESCE(lower(p_username), username),
pin = COALESCE(p_pin, pin),
is_master = COALESCE(p_is_master, is_master)
WHERE id = p_user_id
RETURNING id INTO v_id;
END IF;
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_carrier_user_upsert(text,uuid,uuid,text,text,text,boolean) TO anon;
CREATE OR REPLACE FUNCTION public.admin_carrier_user_delete(p_disp_pin text, p_user_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
DELETE FROM public.carrier_users WHERE id=p_user_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_carrier_user_delete(text,uuid) TO anon;
CREATE OR REPLACE FUNCTION public.admin_shipper_user_upsert(
p_disp_pin text, p_user_id uuid, p_shipper_id uuid,
p_name text, p_username text, p_pin text, p_is_master boolean
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_id uuid;
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_user_id IS NULL THEN
IF p_shipper_id IS NULL OR p_pin IS NULL OR length(p_pin)<1 THEN RAISE EXCEPTION 'shipper_id and pin required' USING ERRCODE='22023'; END IF;
INSERT INTO public.shipper_users (shipper_id, name, username, pin, is_master)
VALUES (p_shipper_id, p_name, lower(COALESCE(p_username,'master')), p_pin, COALESCE(p_is_master,false))
RETURNING id INTO v_id;
ELSE
UPDATE public.shipper_users SET
name = COALESCE(p_name, name),
username = COALESCE(lower(p_username), username),
pin = COALESCE(p_pin, pin),
is_master = COALESCE(p_is_master, is_master)
WHERE id = p_user_id
RETURNING id INTO v_id;
END IF;
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_shipper_user_upsert(text,uuid,uuid,text,text,text,boolean) TO anon;
CREATE OR REPLACE FUNCTION public.admin_shipper_user_delete(p_disp_pin text, p_user_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
IF NOT public._verify_dispatcher(p_disp_pin) THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
DELETE FROM public.shipper_users WHERE id=p_user_id;
END $$;
GRANT EXECUTE ON FUNCTION public.admin_shipper_user_delete(text,uuid) TO anon;
-- ──────────── Carrier-scoped mutation RPCs ────────────
-- carrier_user_upsert: master-only when p_user_id refers to someone else; non-master can only
-- target their own row. p_target_user_id NULL = create new (master only).
CREATE OR REPLACE FUNCTION public.carrier_user_upsert(
p_code text, p_user text, p_pin text,
p_target_user_id uuid, p_name text, p_username text, p_new_pin text, p_is_master boolean
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_target record; v_id uuid;
BEGIN
SELECT * INTO v_caller FROM public._verify_carrier_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.carrier_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_target_user_id IS NULL THEN
IF NOT v_caller.is_master THEN RAISE EXCEPTION 'master required to create accounts' USING ERRCODE='42501'; END IF;
IF p_new_pin IS NULL OR length(p_new_pin)<1 THEN RAISE EXCEPTION 'pin required' USING ERRCODE='22023'; END IF;
INSERT INTO public.carrier_users (carrier_id, name, username, pin, is_master)
VALUES (v_caller.carrier_id, p_name, lower(COALESCE(p_username,'master')), p_new_pin, COALESCE(p_is_master,false))
RETURNING id INTO v_id;
ELSE
SELECT id, carrier_id, is_master INTO v_target FROM public.carrier_users WHERE id=p_target_user_id;
IF v_target IS NULL OR v_target.carrier_id <> v_caller.carrier_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
-- non-master can only update their own row, and only the pin (never name/username/is_master).
IF NOT v_caller.is_master AND v_target.id <> v_caller.user_id THEN RAISE EXCEPTION 'master required' USING ERRCODE='42501'; END IF;
IF NOT v_caller.is_master THEN
UPDATE public.carrier_users SET pin=COALESCE(p_new_pin,pin) WHERE id=p_target_user_id RETURNING id INTO v_id;
ELSE
UPDATE public.carrier_users SET
name = COALESCE(p_name, name),
username = COALESCE(lower(p_username), username),
pin = COALESCE(p_new_pin, pin),
is_master = COALESCE(p_is_master, is_master)
WHERE id = p_target_user_id
RETURNING id INTO v_id;
END IF;
END IF;
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.carrier_user_upsert(text,text,text,uuid,text,text,text,boolean) TO anon;
CREATE OR REPLACE FUNCTION public.carrier_user_delete(p_code text, p_user text, p_pin text, p_target_user_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_target record;
BEGIN
SELECT * INTO v_caller FROM public._verify_carrier_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.carrier_id IS NULL OR NOT v_caller.is_master THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
SELECT carrier_id, is_master INTO v_target FROM public.carrier_users WHERE id=p_target_user_id;
IF v_target IS NULL OR v_target.carrier_id <> v_caller.carrier_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
IF v_target.is_master THEN RAISE EXCEPTION 'cannot delete master' USING ERRCODE='42501'; END IF;
DELETE FROM public.carrier_users WHERE id=p_target_user_id;
END $$;
GRANT EXECUTE ON FUNCTION public.carrier_user_delete(text,text,text,uuid) TO anon;
-- carrier_driver_upsert: any logged-in carrier user can manage their own carrier's drivers.
-- p_driver_id NULL = create. Required for create: p_name, p_new_pin, p_driver_code.
CREATE OR REPLACE FUNCTION public.carrier_driver_upsert(
p_code text, p_user text, p_pin text,
p_driver_id uuid, p_name text, p_phone text, p_new_pin text, p_driver_code text
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_drv_carrier uuid; v_id uuid;
BEGIN
SELECT * INTO v_caller FROM public._verify_carrier_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.carrier_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_driver_id IS NULL THEN
IF p_name IS NULL OR p_new_pin IS NULL OR p_driver_code IS NULL THEN RAISE EXCEPTION 'name/pin/code required' USING ERRCODE='22023'; END IF;
INSERT INTO public.drivers (carrier_id, name, phone, pin, driver_code)
VALUES (v_caller.carrier_id, p_name, p_phone, p_new_pin, p_driver_code)
RETURNING id INTO v_id;
ELSE
SELECT carrier_id INTO v_drv_carrier FROM public.drivers WHERE id=p_driver_id;
IF v_drv_carrier IS NULL OR v_drv_carrier <> v_caller.carrier_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
UPDATE public.drivers SET
name = COALESCE(p_name, name),
phone = COALESCE(p_phone, phone),
pin = COALESCE(p_new_pin, pin),
driver_code = COALESCE(p_driver_code, driver_code)
WHERE id = p_driver_id
RETURNING id INTO v_id;
END IF;
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.carrier_driver_upsert(text,text,text,uuid,text,text,text,text) TO anon;
CREATE OR REPLACE FUNCTION public.carrier_driver_delete(p_code text, p_user text, p_pin text, p_driver_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_drv_carrier uuid;
BEGIN
SELECT * INTO v_caller FROM public._verify_carrier_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.carrier_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
SELECT carrier_id INTO v_drv_carrier FROM public.drivers WHERE id=p_driver_id;
IF v_drv_carrier IS NULL OR v_drv_carrier <> v_caller.carrier_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
DELETE FROM public.drivers WHERE id=p_driver_id;
END $$;
GRANT EXECUTE ON FUNCTION public.carrier_driver_delete(text,text,text,uuid) TO anon;
-- ──────────── Shipper-scoped mutation RPCs ────────────
CREATE OR REPLACE FUNCTION public.shipper_user_upsert(
p_code text, p_user text, p_pin text,
p_target_user_id uuid, p_name text, p_username text, p_new_pin text, p_is_master boolean
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_target record; v_id uuid;
BEGIN
SELECT * INTO v_caller FROM public._verify_shipper_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.shipper_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
IF p_target_user_id IS NULL THEN
IF NOT v_caller.is_master THEN RAISE EXCEPTION 'master required to create accounts' USING ERRCODE='42501'; END IF;
IF p_new_pin IS NULL OR length(p_new_pin)<1 THEN RAISE EXCEPTION 'pin required' USING ERRCODE='22023'; END IF;
INSERT INTO public.shipper_users (shipper_id, name, username, pin, is_master)
VALUES (v_caller.shipper_id, p_name, lower(COALESCE(p_username,'master')), p_new_pin, COALESCE(p_is_master,false))
RETURNING id INTO v_id;
ELSE
SELECT id, shipper_id, is_master INTO v_target FROM public.shipper_users WHERE id=p_target_user_id;
IF v_target IS NULL OR v_target.shipper_id <> v_caller.shipper_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
IF NOT v_caller.is_master AND v_target.id <> v_caller.user_id THEN RAISE EXCEPTION 'master required' USING ERRCODE='42501'; END IF;
IF NOT v_caller.is_master THEN
UPDATE public.shipper_users SET pin=COALESCE(p_new_pin,pin) WHERE id=p_target_user_id RETURNING id INTO v_id;
ELSE
UPDATE public.shipper_users SET
name = COALESCE(p_name, name),
username = COALESCE(lower(p_username), username),
pin = COALESCE(p_new_pin, pin),
is_master = COALESCE(p_is_master, is_master)
WHERE id = p_target_user_id
RETURNING id INTO v_id;
END IF;
END IF;
RETURN v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.shipper_user_upsert(text,text,text,uuid,text,text,text,boolean) TO anon;
CREATE OR REPLACE FUNCTION public.shipper_user_delete(p_code text, p_user text, p_pin text, p_target_user_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_caller record; v_target record;
BEGIN
SELECT * INTO v_caller FROM public._verify_shipper_user(p_code,p_user,p_pin);
IF v_caller IS NULL OR v_caller.shipper_id IS NULL OR NOT v_caller.is_master THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
SELECT shipper_id, is_master INTO v_target FROM public.shipper_users WHERE id=p_target_user_id;
IF v_target IS NULL OR v_target.shipper_id <> v_caller.shipper_id THEN RAISE EXCEPTION 'not found' USING ERRCODE='42501'; END IF;
IF v_target.is_master THEN RAISE EXCEPTION 'cannot delete master' USING ERRCODE='42501'; END IF;
DELETE FROM public.shipper_users WHERE id=p_target_user_id;
END $$;
GRANT EXECUTE ON FUNCTION public.shipper_user_delete(text,text,text,uuid) TO anon;
-- ──────────── Driver self-service ────────────
-- driver_setpin: a driver changes their own PIN. Verifies the current PIN, then writes the new one.
CREATE OR REPLACE FUNCTION public.driver_setpin(p_code text, p_old_pin text, p_new_pin text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_id uuid;
BEGIN
IF p_new_pin IS NULL OR length(p_new_pin)<1 THEN RAISE EXCEPTION 'pin required' USING ERRCODE='22023'; END IF;
SELECT d.id INTO v_id
FROM public.drivers d
JOIN public.carriers c ON c.id=d.carrier_id
WHERE lower(d.driver_code)=lower(p_code) AND d.pin=p_old_pin AND c.archived_at IS NULL;
IF v_id IS NULL THEN RAISE EXCEPTION 'unauthorized' USING ERRCODE='42501'; END IF;
UPDATE public.drivers SET pin=p_new_pin WHERE id=v_id;
END $$;
GRANT EXECUTE ON FUNCTION public.driver_setpin(text,text,text) TO anon;
-- ──────────── Self-service username backfill ────────────
-- Replaces the legacy client-side UPDATE loops in ensureCarMaster/ensureSPMaster. The auth
-- RPCs invoke this on every successful login so missing usernames get fixed transparently.
CREATE OR REPLACE FUNCTION public._backfill_usernames(p_carrier_id uuid, p_shipper_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
IF p_carrier_id IS NOT NULL THEN
UPDATE public.carrier_users
SET username = CASE WHEN is_master THEN 'master' ELSE lower(regexp_replace(coalesce(name,''),'[^a-zA-Z0-9]+','','g')) END
WHERE carrier_id=p_carrier_id AND (username IS NULL OR username='');
END IF;
IF p_shipper_id IS NOT NULL THEN
UPDATE public.shipper_users
SET username = CASE WHEN is_master THEN 'master' ELSE lower(regexp_replace(coalesce(name,''),'[^a-zA-Z0-9]+','','g')) END
WHERE shipper_id=p_shipper_id AND (username IS NULL OR username='');
END IF;
END $$;
D&REnvision LLC
🗂 Dispatcher
📊 Dashboard
📋 Manifest
🗃 History
🗺 Live Map
🚚 Carriers
📦 Shippers
📇 Directory
Documents
⚙️ Settings
⚠️ Login-code uniqueness check failed
One or more unique indexes that prevent duplicate login codes are missing. Open Supabase → SQL Editor and run the SQL below, then refresh this page.
Active Loads
Email intake
Waiting for an intake health check.
Last successful Gmail check: not available
Total Shipments: Loading…
Loading…
No active manifests.
📋 Create New Manifest
HRS
Shipments
🗃 Full History
No records.
🗺 Live Driver Map
Visible to dispatchers and carriers only
Active <10m
<1h
Stale
No fix
Loading drivers…
➕ Add Carrier
A unique Company Code will be generated automatically. The carrier signs in with that code, username master, and this PIN. If you provide an email it will be used for manifest / status / archive / note notifications (all on by default).
🚚 Carriers
Loading…
No carriers yet.
➕ Add Shipper
A unique Company Code will be generated automatically. The shipper signs in with that code, username master, and this PIN. If you provide an email it will be used for status change / archive / note notifications (all on by default).
📦 Shippers
Loading…
No shippers yet.
📇 Directory
Quick name-only entries for carriers and shippers you don't need to give a login to. They'll appear in the Carrier and Shipper dropdowns when you create a manifest, marked with 📇.
Loading…
No directory carriers yet.
Loading…
No directory shippers yet.
Email recipients
Open Settings to load shared carrier contacts and saved recipients.
🔐 Dispatcher PIN
✉️ Email Notifications
Loading…
📥 Auto-Manifest from Email
Loading…
✈️ Airlines & MAWB Prefixes
Set a 3-digit MAWB prefix per airline (e.g. American → 001, Delta → 006) and an optional default Building #. When you pick the airline on a new shipment, both fields prefill automatically — you only have to type the post-prefix MAWB digits. Empty fields are kept blank so the dispatcher can still type anything manually.
Open Settings to load your carrier’s saved recipients and driver contacts.
🆔 Your Sign-In
Company Code
—
Username
—
🔐 Change Your PIN
✉️ Email Notifications
Loading…
👥 Manage Accounts
➕ Add Sub-Account
D&REnvision LLC
📦 Shipper
📦 Active
🗃 History
📎 Documents
⚙️ Settings
Loading…
No active shipments.
Loading…
No delivered shipments yet.
Email recipients
Only recipients saved to your shipper account appear here.
🔐 Change Your PIN
🆔 Your Sign-In
Company Code
—
Username
—
✉️ Email Notifications
Loading…
👥 Manage Accounts
➕ Add Sub-Account
D&REnvision LLC
🧑✈️ Driver
📦 Open Loads0
🗃 History
🧑✈️ Drivers
⚙️ Settings
Loading…
Total Shipments: Loading…
✅ No open loads assigned to you.
Loading…
No delivered or closed-out shipments yet.
🧑✈️ Drivers
View-only — your carrier manages this list.
Loading…
No drivers on file yet.
🔐 Change Your PIN
⟳ Refreshing…
Updating…
📋 Invoice Checklist
Pick a service week (Monday → Sunday). Loads put out for delivery (Recovered) on the Sunday are included even if they were marked Delivered the following Monday.
⬇ Recovery Report
Todays Assignment: All loads originally assigned for today's pickup date (Eastern), including delivered, not available, and rescheduled loads. Updates appear in Comments.
Open: All remaining active loads across dates, including not available.
Both include your carrier's full report regardless of dashboard filters and are sorted by ETA.
Spreadsheet of every open shipment to recover under the date filter currently selected on screen.
Download Spreadsheet
Todays Assignment and Completed Loads use today's originally assigned pickup date (Eastern time). All spreadsheets are sorted by ETA.
All remaining active loads, including not available, across every date and carrier, regardless of dashboard filters.
Today's originally scheduled loads, including delivered, not available, and rescheduled loads. Status details and revised ETAs appear in Comments.
Today's originally scheduled loads already marked Delivered.
Manifest History
✍️ Capture Proof of Delivery
Have the receiver sign below. Tap ⛶ for a larger signing area — best held sideways.