ALHUSNA Logistics Marketplace Starter Schema

PostgreSQL and Supabase starter schema for the ALHUSNA Logistics Marketplace, including tables for profiles, partner applications, bookings, booking status events, and payment records.

- Enable RLS on every table. Customers read only their own bookings. - Partners read only bookings assigned to them. Admin permissions must be granted server-side to trusted users only. - Never allow clients to change role, commission, payment status or verification status. - Verify payment-provider webhooks on the server. Keep secret keys server-side. - Do not store BVN, NIN, card data, passwords or Identity documents here.

create extension if not exists pgcrypto; create table public.profiles ( id uuid primary key references auth.users(id) on delete cascade, full_name text not null, phone text, role text not null default 'customer' check (role in ('customer','partner','admin')), created_at timestamptz not null default now() ); create table public.partner_applications ( id uuid primary key default gen_random_uuid(), user_id uuid references auth.users(id) on delete set null, business_name text not null, contact_name text not null, phone text not null, service_areas text[] not null default '{}', service_types text[] not null default '{}', verification_status text not null default 'pending' check (verification_status in ('pending','approved','rejected','suspended')), created_at timestamptz not null default now() ); create table public.bookings ( id uuid primary key default gen_random_uuid(), reference text unique not null, customer_id uuid references auth.users(id) on delete set null, customer_name text not null, customer_phone text not null, pickup_address text not null, destination_address text not null, service_type text not null, parcel_description text not null, notes text, quoted_amount numeric(12,2), currency char(3) not null default 'NGN', commission_rate numeric(5,2) not null default 10.00, commission_amount numeric(12,2), partner_id uuid references public.partner_applications(id) on delete set null, status text not null default 'requested' check (status in ('requested','quoted','accepted','picked_up','in_transit','delivered','cancelled')), created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create table public.booking_status_events ( id uuid primary key default gen_random_uuid(), booking_id uuid not null references public.bookings(id) on delete cascade, status text not null, note text, updated_by uuid references auth.users(id) on delete set null, created_at timestamptz not null default now() ); create table public.payment_records ( id uuid primary key default gen_random_uuid(), booking_id uuid not null references public.bookings(id) on delete restrict, provider text not null, provider_reference text unique, amount numeric(12,2) not null, currency char(3) not null default 'NGN', status text not null default 'pending' check (status in ('pending','successful','failed','refunded')), created_at timestamptz not null default now() );