-- ════ PLENTIFUL TMS — MULTI-TENANT SCHEMA ════ -- Run this ONCE in Supabase SQL Editor -- COMPANIES (one row per customer) create table if not exists companies ( id bigserial primary key, name text not null, slug text unique not null, plan text default 'starter', active boolean default true, owner_email text, created_at timestamptz default now() ); -- CORE TABLES (all scoped to company_id) create table if not exists orders (id bigserial primary key, company_id bigint default 1, commodity text, status text default 'Open', customer text, destination text, ship_date date, del_date date, appt text, warehouse text, pt_num text, po_num text, carrier text, load_id integer, input_freight numeric default 0, misc numeric default 0, rate numeric default 0, total numeric default 0, case_cost numeric default 0, notes text, bol text, pf_po text, pod boolean default false, archived boolean default false, appt_confirmed boolean default false, appt_conf_num text, sent_at timestamptz, audit jsonb default '[]', rpc_g int default 0, lbs25_g int default 0, lbs25_r int default 0, minis int default 0, pepper_r int default 0, pepper_y int default 0, pepper_o int default 0, ryo_3ct int default 0, ryo_6ct int default 0, lbs11_org int default 0, lbs11_cv int default 0, asp_bags int default 0, lbs28 int default 0, costco int default 0, minis_x int default 0, g_rpc int default 0, lbs25_g_x int default 0, lbs25_r_x int default 0, tjs int default 0, hh int default 0, lbs11_cv_x int default 0, lbs11_org_x int default 0, lbs28_x int default 0, bags_1lb int default 0, corn int default 0, created_at timestamptz default now(), updated_at timestamptz default now()); create table if not exists app_users (id bigserial primary key, company_id bigint default 1, name text not null, password text not null, role text not null, display text, carrier_name text default '', created_at timestamptz default now(), unique(company_id, name)); create table if not exists carriers (id bigserial primary key, company_id bigint default 1, name text, email text, cc text); create table if not exists warehouses (id bigserial primary key, company_id bigint default 1, code text, name text, address text, city text, state text, hours text, instructions text); create table if not exists destinations (id bigserial primary key, company_id bigint default 1, name text, address text); create table if not exists rates (id bigserial primary key, company_id bigint default 1, carrier text, warehouse text, destination text, ltl numeric default 0, ftl numeric default 0, extra_drop numeric default 0); create table if not exists claims (id bigserial primary key, company_id bigint default 1, carrier text, load_id integer, pt_num text, amount numeric default 0, filed_date date, status text default 'Open', description text, notes text, paid_date date, paid_amount numeric, created_at timestamptz default now()); create table if not exists messages (id bigserial primary key, company_id bigint default 1, load_id integer, commodity text, carrier text, msg_text text, msg_type text default 'general', from_user text, from_role text, to_role text, read boolean default false, ts timestamptz default now()); create table if not exists dispatch_log (id bigserial primary key, company_id bigint default 1, ts timestamptz default now(), dispatcher text, carrier text, load_id integer, commodity text, orders_count integer, subject_base text, result text, is_update boolean default false); create table if not exists app_settings (key text, company_id bigint default 1, value text, primary key (key, company_id)); -- INDEXES for performance create index if not exists idx_orders_co on orders(company_id); create index if not exists idx_users_co on app_users(company_id); create index if not exists idx_carriers_co on carriers(company_id); create index if not exists idx_warehouses_co on warehouses(company_id); create index if not exists idx_destinations_co on destinations(company_id); create index if not exists idx_rates_co on rates(company_id); create index if not exists idx_claims_co on claims(company_id); create index if not exists idx_messages_co on messages(company_id); create index if not exists idx_log_co on dispatch_log(company_id); -- DISABLE RLS (app handles isolation) alter table companies disable row level security; alter table orders disable row level security; alter table app_users disable row level security; alter table carriers disable row level security; alter table warehouses disable row level security; alter table destinations disable row level security; alter table rates disable row level security; alter table claims disable row level security; alter table messages disable row level security; alter table dispatch_log disable row level security; alter table app_settings disable row level security; -- COMMODITIES (dynamic, per company) create table if not exists commodities ( id bigserial primary key, company_id bigint default 1, name text not null, slug text not null, max_pallets integer default 28, icon text default '📦', sort_order integer default 99, active boolean default true, skus jsonb default '[]', created_at timestamptz default now() ); alter table commodities disable row level security; create index if not exists idx_commodities_co on commodities(company_id); -- EMAIL THREADS (track Resend message IDs per load for reply threading) create table if not exists email_threads ( id bigserial primary key, company_id bigint default 1, load_id integer not null, commodity text, carrier text, resend_message_id text, subject text, sent_at timestamptz default now() ); alter table email_threads disable row level security; -- QUANTITIES column (replaces individual SKU columns — no SQL needed for new commodities) alter table orders add column if not exists quantities jsonb default '{}'; -- RECOVERY table (soft-deleted orders recoverable for 30 days) create table if not exists order_recovery ( id bigserial primary key, company_id bigint, order_data jsonb not null, deleted_by text, deleted_at timestamptz default now(), reason text ); alter table order_recovery disable row level security; -- UNDO log (short-term change buffer) create table if not exists undo_log ( id bigserial primary key, company_id bigint, order_id integer, previous_data jsonb, changed_by text, changed_at timestamptz default now() ); alter table undo_log disable row level security; -- MIGRATION: add company_id to existing tables if upgrading from v7 -- QUANTITIES column (replaces individual SKU columns — no SQL needed for new commodities) alter table orders add column if not exists quantities jsonb default '{}'; -- RECOVERY table (soft-deleted orders recoverable for 30 days) create table if not exists order_recovery ( id bigserial primary key, company_id bigint, order_data jsonb not null, deleted_by text, deleted_at timestamptz default now(), reason text ); alter table order_recovery disable row level security; -- UNDO log (short-term change buffer) create table if not exists undo_log ( id bigserial primary key, company_id bigint, order_id integer, previous_data jsonb, changed_by text, changed_at timestamptz default now() ); alter table undo_log disable row level security; -- MIGRATION: add company_id to existing tables if upgrading from v7 alter table orders add column if not exists company_id bigint default 1; alter table app_users add column if not exists company_id bigint default 1; alter table carriers add column if not exists company_id bigint default 1; alter table warehouses add column if not exists company_id bigint default 1; alter table destinations add column if not exists company_id bigint default 1; alter table rates add column if not exists company_id bigint default 1; alter table claims add column if not exists company_id bigint default 1; alter table messages add column if not exists company_id bigint default 1; alter table dispatch_log add column if not exists company_id bigint default 1;