Below is the comprehensive PostgreSQL schema for the Elvekas Marketplace, designed to support multi-vendor dynamics, fashion variants, Temu procurement, and rural logistics.
1. User & Wallet System
This core module handles digitized payments and user roles.
SQL
-- Core User Table
CREATE TYPE user_role AS ENUM ('customer', 'vendor', 'rider', 'admin');

CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
surname VARCHAR(255) NOT NULL,
first_name VARCHAR(255) NOT NULL,
other_name VARCHAR(255),
phone VARCHAR(20) UNIQUE,
address VARCHAR(255) NOT NULL, 
email VARCHAR(255) UNIQUE,
nin VARCHAR(11) UNIQUE,
password_hash TEXT NOT NULL,
role user_role DEFAULT 'customer',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Wallet for Cash Digitization
CREATE TABLE wallets (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    balance DECIMAL(12, 2) DEFAULT 0.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transaction History for Funding and Orders
CREATE TABLE wallet_transactions (
    id SERIAL PRIMARY KEY,
    wallet_id INTEGER REFERENCES wallets(id),
    amount DECIMAL(12, 2) NOT NULL,
    type VARCHAR(20) NOT NULL, -- 'credit' (funding), 'debit' (payment)
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'success', 'failed'
    reference VARCHAR(100) UNIQUE, -- Paystack reference
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Multi-Vendor & Product System
Supports local measurements (mudu) and fashion variants.
SQL
-- Vendor Profiles
CREATE TABLE vendors (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    business_name VARCHAR(255) NOT NULL,
    vendor_type VARCHAR(50) NOT NULL, -- 'farmer', 'restaurant', 'fashion', etc.
    location TEXT NOT NULL,
    service_area VARCHAR(100), -- 'Gembu', 'Sardauna', 'Jalingo'
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product Catalog
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendors(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    unit_type VARCHAR(20) DEFAULT 'piece', -- 'mudu', 'bag', 'kg', 'piece'
    base_price DECIMAL(10, 2) NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Fashion & Electronics Variants
CREATE TABLE product_variants (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    size VARCHAR(20),
    color VARCHAR(50),
    price_override DECIMAL(10, 2), -- If variant price differs from base
    stock_quantity INTEGER DEFAULT 0,
    sku VARCHAR(100) UNIQUE
);

3. Orders & Logistics
Handles the lifecycle of the order and real-time rider tracking.
SQL
-- Order Lifecycle
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount DECIMAL(12, 2) NOT NULL,
    delivery_address TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'paid', 'shipped', 'delivered'
    payment_status VARCHAR(20) DEFAULT 'unpaid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Items within an Order (Variant-Specific)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    variant_id INTEGER REFERENCES product_variants(id),
    vendor_id INTEGER REFERENCES vendors(id),
    quantity INTEGER NOT NULL,
    price_at_purchase DECIMAL(10, 2) NOT NULL
);

-- Delivery & Rider Tracking
CREATE TABLE deliveries (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    rider_id INTEGER REFERENCES users(id), -- User with 'rider' role
    status VARCHAR(50) DEFAULT 'assigned', -- 'assigned', 'picked_up', 'delivered'
    current_lat DECIMAL(9, 6),
    current_lng DECIMAL(9, 6),
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at TIMESTAMP
);

4. Procurement & Financial Ledger
Manages Temu imports and automated fund splitting.
SQL
-- Temu / External Procurement
CREATE TABLE external_orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_url TEXT NOT NULL,
    product_name VARCHAR(255),
    unit_price DECIMAL(10, 2) NOT NULL,
    quantity INTEGER DEFAULT 1,
    shipping_cost DECIMAL(10, 2),
    customs_fee DECIMAL(10, 2),
    service_fee DECIMAL(10, 2),
    total_amount DECIMAL(12, 2),
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Earnings Ledger (Automated Split)
CREATE TABLE earnings_ledger (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    vendor_id INTEGER REFERENCES vendors(id),
    rider_id INTEGER REFERENCES users(id),
    vendor_amount DECIMAL(12, 2),
    rider_amount DECIMAL(12, 2),
    platform_commission DECIMAL(12, 2),
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Dispute Management
CREATE TABLE disputes (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    user_id INTEGER REFERENCES users(id),
    reason TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'open', -- 'open', 'resolved', 'refunded'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key Schema Features for Elvekas
Decimal Precision: Uses DECIMAL(12, 2) for all financial values to prevent rounding errors common in rural fintech apps.
Order Splitting: The order_items and earnings_ledger tables allow the system to handle one checkout containing products from multiple vendors (e.g., meat from a butcher and shoes from a boutique).
Logistics Ready: The deliveries table includes latitude and longitude fields to support the real-time Socket.IO tracking for Gembu riders.
Procurement Support: The external_orders table is built to specifically calculate the complex costs associated with importing from Temu to Nigeria.