Build a production-ready B2B supply chain management platform for IXG Ltd.IXG Ltd is a UK-registered export orchestrator for Nigerian shea butter,
connecting Nigerian operations with UK wholesale and retail buyers.
PROJECT SETUP:
Create a Next.js 15 project with App Router, TypeScript (strict mode),
and Tailwind CSS. Install and configure shadcn/ui with the "new-york" style.
ixg-green: #1A3D2B
ixg-gold: #B07D0E
ixg-light: #F7F6F2
ixg-dark: #1A1A1A
Generate a complete PostgreSQL SQL script to create the IXG platform database schema.
id UUID references auth.users PRIMARY KEY
email TEXT NOT NULL UNIQUE
full_name TEXT
company_name TEXT
role TEXT NOT NULL DEFAULT "buyer" CHECK (role IN ("buyer","ops","admin"))
stripe_customer_id TEXT
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
user_id UUID references users(id) ON DELETE CASCADE
plan TEXT CHECK (plan IN ("observer","starter","professional","enterprise"))
stripe_subscription_id TEXT UNIQUE
stripe_price_id TEXT
status TEXT CHECK (status IN ("active","cancelled","past_due","trialing"))
current_period_end TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_reference TEXT NOT NULL UNIQUE (format: IXG-BATCH-YYYY-NNN)
harvest_date DATE
weight_kg DECIMAL(10,2)
grade TEXT CHECK (grade IN ("A","B","C"))
status TEXT CHECK (status IN ("harvested","processing","qc_pass","qc_fail",
"ready_to_ship","in_transit","at_uk_port","customs","delivered"))
origin_facility TEXT DEFAULT "ASI - Katsina State, Nigeria"
notes TEXT
created_by UUID references users(id)
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id) ON DELETE CASCADE
ffa_percentage DECIMAL(5,2)
moisture_percentage DECIMAL(5,2)
peroxide_value DECIMAL(5,2)
visual_grade TEXT CHECK (visual_grade IN ("A","B","C","Fail"))
overall_pass BOOLEAN
tested_by TEXT
tested_at TIMESTAMPTZ DEFAULT NOW()
lab_reference TEXT
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id) ON DELETE CASCADE
file_url TEXT NOT NULL
file_name TEXT NOT NULL
issuer TEXT
issue_date DATE
expiry_date DATE
uploaded_by UUID references users(id)
uploaded_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id) ON DELETE CASCADE
cert_type TEXT CHECK (cert_type IN ("phytosanitary","export_permit",
"organic","fair_trade","eudr_dds","other"))
file_url TEXT NOT NULL
file_name TEXT NOT NULL
cert_reference TEXT
issue_date DATE
expiry_date DATE
uploaded_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id) ON DELETE CASCADE
status TEXT CHECK (status IN ("booked","loading","departed",
"in_transit","arrived_uk","customs_clearance","delivered"))
origin_port TEXT DEFAULT "Apapa Port, Lagos"
destination_port TEXT DEFAULT "Port of Felixstowe"
vessel_name TEXT
container_ref TEXT
etd_nigeria DATE
eta_uk DATE
bl_number TEXT
freight_forwarder TEXT
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id)
doc_type TEXT CHECK (doc_type IN ("coa","regulatory","invoice",
"packing_list","bl","insurance","other"))
file_url TEXT NOT NULL
file_name TEXT NOT NULL
file_size_bytes INTEGER
visibility TEXT CHECK (visibility IN ("buyer","ops","admin")) DEFAULT "buyer"
uploaded_by UUID references users(id)
uploaded_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id)
issue_description TEXT NOT NULL
root_cause TEXT
corrective_action TEXT
preventive_action TEXT
status TEXT CHECK (status IN ("open","in_progress","closed")) DEFAULT "open"
assigned_to TEXT
due_date DATE
closed_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
batch_id UUID references processing_batches(id)
women_employed INTEGER
total_workers INTEGER
fair_wage_paid BOOLEAN
safety_equipment_used BOOLEAN
community_notes TEXT
reported_by TEXT
report_date DATE
created_at TIMESTAMPTZ DEFAULT NOW()
TABLE 11: ai_interactions
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
user_id UUID references users(id)
session_id TEXT
message_role TEXT CHECK (message_role IN ("user","assistant"))
message_content TEXT NOT NULL
tokens_used INTEGER
created_at TIMESTAMPTZ DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
user_id UUID references users(id)
type TEXT
title TEXT NOT NULL
message TEXT NOT NULL
is_read BOOLEAN DEFAULT FALSE
created_at TIMESTAMPTZ DEFAULT NOW()
users: Users can read/update their own row. Admins can read all.
subscriptions: Users can read their own. Admins can read all.
processing_batches: Buyers with active subscription can read all.
Ops and admin can insert/update/delete.
qc_results, shipments, capa_records, esg_field_reports:
Buyers can read. Ops/admin can insert/update.
certificates_of_analysis, regulatory_certs, documents:
Buyers can read files with visibility="buyer". Ops/admin can read/write all.
ai_interactions, notifications: Users can only see their own rows.
Also create a trigger function: when a new user signs up via Auth, automatically insert a matching row into the users table with role="buyer".