Shop System Example

This example demonstrates how to build a complete shop system with inventory management, transactions, and pricing using Sleet ORM.

Schema Definition

Define the shop-related tables:

-- server/schema.lua
local sl = require 'sleet'

return {
    -- Shop locations and metadata
    shops = sl.table('shops', {
        id = sl.serial().primaryKey(),
        name = sl.varchar(100).notNull(),
        label = sl.varchar(100).notNull(),
        type = sl.varchar(50).notNull(), -- 'general', 'clothing', 'weapons', etc.
        blip_sprite = sl.integer().default(52),
        blip_color = sl.integer().default(2),
        coords = sl.text().notNull(), -- JSON: {"x": 1.0, "y": 2.0, "z": 3.0}
        opening_hours = sl.text(), -- JSON: {"open": 8, "close": 22}
        is_active = sl.boolean().default(true),
        created_at = sl.timestamp().default(sl.raw('CURRENT_TIMESTAMP'))
    }),
    
    -- Items that can be sold in shops
    items = sl.table('items', {
        name = sl.varchar(50).primaryKey(),
        label = sl.varchar(100).notNull(),
        description = sl.text(),
        category = sl.varchar(50).notNull(),
        weight = sl.float().default(0.0),
        max_stack = sl.integer().default(1),
        can_remove = sl.boolean().default(true),
        is_unique = sl.boolean().default(false),
        metadata = sl.text() -- JSON for additional properties
    }),
    
    -- Shop inventory (what items each shop sells)
    shop_items = sl.table('shop_items', {
        id = sl.serial().primaryKey(),
        shop_id = sl.integer().notNull(),
        item_name = sl.varchar(50).notNull(),
        price = sl.integer().notNull(), -- Price in cents/pence
        stock = sl.integer().default(-1), -- -1 = unlimited
        min_stock = sl.integer().default(0),
        max_stock = sl.integer().default(100),
        restock_rate = sl.integer().default(1), -- Items per restock cycle
        is_available = sl.boolean().default(true),
        
        -- Indexes for performance
        index = {
            sl.index(['shop_id', 'item_name']).unique(),
            sl.index('shop_id'),
            sl.index('item_name')
        }
    }),
    
    -- Player inventory
    user_inventory = sl.table('user_inventory', {
        id = sl.serial().primaryKey(),
        identifier = sl.varchar(64).notNull(),
        item_name = sl.varchar(50).notNull(),
        count = sl.integer().default(1),
        slot = sl.integer(),
        metadata = sl.text(), -- JSON for item-specific data
        
        index = {
            sl.index(['identifier', 'item_name']),
            sl.index('identifier')
        }
    }),
    
    -- Transaction history
    shop_transactions = sl.table('shop_transactions', {
        id = sl.serial().primaryKey(),
        shop_id = sl.integer().notNull(),
        player_identifier = sl.varchar(64).notNull(),
        item_name = sl.varchar(50).notNull(),
        quantity = sl.integer().notNull(),
        unit_price = sl.integer().notNull(),
        total_price = sl.integer().notNull(),
        transaction_type = sl.varchar(20).notNull(), -- 'buy' or 'sell'
        timestamp = sl.timestamp().default(sl.raw('CURRENT_TIMESTAMP')),
        
        index = {
            sl.index('shop_id'),
            sl.index('player_identifier'),
            sl.index('timestamp')
        }
    })
}

Shop Management System

-- server/modules/shop.lua
local Schema = require('server.schema')

local ShopManager = {}

-- Initialize shops (run on server start)
function ShopManager.initialize()
    -- Create default shops if they don't exist
    local defaultShops = {
        {
            name = 'twentyfourseven',
            label = '24/7 Supermarket',
            type = 'general',
            coords = '{"x": 25.7, "y": -1347.3, "z": 29.49}',
            opening_hours = '{"open": 0, "close": 24}' -- 24/7
        },
        {
            name = 'ltdgasoline',
            label = 'LTD Gasoline',
            type = 'general',
            coords = '{"x": -48.519, "y": -1757.514, "z": 29.421}',
            opening_hours = '{"open": 6, "close": 23}'
        }
    }
    
    for _, shopData in ipairs(defaultShops) do
        local existing = Schema.shops:where('name', shopData.name):exec()[1]
        if not existing then
            Schema.shops:insert(shopData)
        end
    end
    
    -- Initialize shop inventories
    ShopManager.initializeInventories()
end

-- Initialize default shop inventories
function ShopManager.initializeInventories()
    local generalStoreItems = {
        {item_name = 'bread', price = 200, stock = 50},
        {item_name = 'water', price = 100, stock = 100},
        {item_name = 'bandage', price = 500, stock = 25},
        {item_name = 'phone', price = 15000, stock = 10}
    }
    
    local shops = Schema.shops:where('type', 'general'):exec()
    for _, shop in ipairs(shops) do
        for _, itemData in ipairs(generalStoreItems) do
            local existing = Schema.shop_items:where('shop_id', shop.id)
                :where('item_name', itemData.item_name):exec()[1]
            
            if not existing then
                itemData.shop_id = shop.id
                Schema.shop_items:insert(itemData)
            end
        end
    end
end

-- Get shop by ID
function ShopManager.getShop(shopId)
    return Schema.shops:where('id', shopId):where('is_active', true):exec()[1]
end

-- Get shop by name
function ShopManager.getShopByName(shopName)
    return Schema.shops:where('name', shopName):where('is_active', true):exec()[1]
end

-- Get all active shops
function ShopManager.getAllShops()
    return Schema.shops:where('is_active', true):orderBy('name'):exec()
end

-- Get shop inventory
function ShopManager.getShopInventory(shopId, category)
    local query = Schema.shop_items
        :join('items', 'shop_items.item_name', 'items.name')
        :select(
            'shop_items.*',
            'items.label',
            'items.description',
            'items.category',
            'items.weight'
        )
        :where('shop_items.shop_id', shopId)
        :where('shop_items.is_available', true)
    
    if category then
        query = query:where('items.category', category)
    end
    
    return query:orderBy('items.category'):orderBy('items.label'):exec()
end

-- Check if shop is open
function ShopManager.isShopOpen(shopId)
    local shop = ShopManager.getShop(shopId)
    if not shop or not shop.opening_hours then
        return true -- Always open if no hours specified
    end
    
    local hours = json.decode(shop.opening_hours)
    local currentHour = tonumber(os.date('%H'))
    
    if hours.open <= hours.close then
        return currentHour >= hours.open and currentHour < hours.close
    else
        -- Overnight hours (e.g., 22 to 6)
        return currentHour >= hours.open or currentHour < hours.close
    end
end

-- Purchase item from shop
function ShopManager.buyItem(playerId, shopId, itemName, quantity)
    local playerIdentifier = GetPlayerIdentifier(playerId, 0)
    if not playerIdentifier then
        return false, 'Invalid player'
    end
    
    -- Check if shop is open
    if not ShopManager.isShopOpen(shopId) then
        return false, 'Shop is closed'
    end
    
    -- Get shop item
    local shopItem = Schema.shop_items:where('shop_id', shopId)
        :where('item_name', itemName)
        :where('is_available', true)
        :exec()[1]
    
    if not shopItem then
        return false, 'Item not available'
    end
    
    -- Check stock
    if shopItem.stock >= 0 and shopItem.stock < quantity then
        return false, 'Insufficient stock'
    end
    
    -- Calculate total price
    local totalPrice = shopItem.price * quantity
    
    -- Check if player has enough money
    local playerMoney = exports.esx_legacy:getPlayerMoney(playerId, 'money')
    if playerMoney < totalPrice then
        return false, 'Insufficient funds'
    end
    
    -- Process transaction
    local success = ShopManager.processTransaction(playerId, shopId, itemName, quantity, shopItem.price, 'buy')
    if success then
        -- Remove money from player
        exports.esx_legacy:removePlayerMoney(playerId, 'money', totalPrice)
        
        -- Add item to player inventory
        ShopManager.addItemToPlayer(playerIdentifier, itemName, quantity)
        
        -- Update shop stock
        if shopItem.stock >= 0 then
            Schema.shop_items:where('id', shopItem.id):update({
                stock = shopItem.stock - quantity
            })
        end
        
        return true, 'Purchase successful'
    end
    
    return false, 'Transaction failed'
end

-- Sell item to shop
function ShopManager.sellItem(playerId, shopId, itemName, quantity)
    local playerIdentifier = GetPlayerIdentifier(playerId, 0)
    if not playerIdentifier then
        return false, 'Invalid player'
    end
    
    -- Check if shop accepts this item
    local shopItem = Schema.shop_items:where('shop_id', shopId)
        :where('item_name', itemName)
        :exec()[1]
    
    if not shopItem then
        return false, 'Shop does not buy this item'
    end
    
    -- Check if player has the item
    local playerItem = Schema.user_inventory:where('identifier', playerIdentifier)
        :where('item_name', itemName)
        :exec()[1]
    
    if not playerItem or playerItem.count < quantity then
        return false, 'You do not have enough of this item'
    end
    
    -- Calculate sell price (typically 50% of buy price)
    local sellPrice = math.floor(shopItem.price * 0.5)
    local totalEarnings = sellPrice * quantity
    
    -- Process transaction
    local success = ShopManager.processTransaction(playerId, shopId, itemName, quantity, sellPrice, 'sell')
    if success then
        -- Add money to player
        exports.esx_legacy:addPlayerMoney(playerId, 'money', totalEarnings)
        
        -- Remove item from player inventory
        ShopManager.removeItemFromPlayer(playerIdentifier, itemName, quantity)
        
        -- Update shop stock
        if shopItem.stock >= 0 then
            Schema.shop_items:where('id', shopItem.id):update({
                stock = math.min(shopItem.stock + quantity, shopItem.max_stock)
            })
        end
        
        return true, 'Sale successful'
    end
    
    return false, 'Transaction failed'
end

-- Process transaction and log it
function ShopManager.processTransaction(playerId, shopId, itemName, quantity, unitPrice, transactionType)
    local playerIdentifier = GetPlayerIdentifier(playerId, 0)
    local totalPrice = unitPrice * quantity
    
    local transactionId = Schema.shop_transactions:insert({
        shop_id = shopId,
        player_identifier = playerIdentifier,
        item_name = itemName,
        quantity = quantity,
        unit_price = unitPrice,
        total_price = totalPrice,
        transaction_type = transactionType
    })
    
    return transactionId ~= nil
end

-- Add item to player inventory
function ShopManager.addItemToPlayer(identifier, itemName, quantity)
    local existing = Schema.user_inventory:where('identifier', identifier)
        :where('item_name', itemName)
        :exec()[1]
    
    if existing then
        -- Update existing stack
        Schema.user_inventory:where('id', existing.id):update({
            count = existing.count + quantity
        })
    else
        -- Create new inventory entry
        Schema.user_inventory:insert({
            identifier = identifier,
            item_name = itemName,
            count = quantity
        })
    end
end

-- Remove item from player inventory
function ShopManager.removeItemFromPlayer(identifier, itemName, quantity)
    local existing = Schema.user_inventory:where('identifier', identifier)
        :where('item_name', itemName)
        :where('count', '>=', quantity)
        :exec()[1]
    
    if existing then
        if existing.count == quantity then
            -- Remove entirely
            Schema.user_inventory:where('id', existing.id):delete()
        else
            -- Reduce quantity
            Schema.user_inventory:where('id', existing.id):update({
                count = existing.count - quantity
            })
        end
        return true
    end
    return false
end

-- Get player inventory
function ShopManager.getPlayerInventory(identifier)
    return Schema.user_inventory
        :join('items', 'user_inventory.item_name', 'items.name')
        :select(
            'user_inventory.*',
            'items.label',
            'items.description',
            'items.category',
            'items.weight'
        )
        :where('user_inventory.identifier', identifier)
        :orderBy('items.category')
        :orderBy('items.label')
        :exec()
end

-- Restock shops (run periodically)
function ShopManager.restockShops()
    local shopItems = Schema.shop_items:where('stock', '>=', 0)
        :where('stock', '<', 'max_stock')
        :exec()
    
    for _, item in ipairs(shopItems) do
        local newStock = math.min(
            item.stock + item.restock_rate,
            item.max_stock
        )
        
        Schema.shop_items:where('id', item.id):update({
            stock = newStock
        })
    end
    
    print(('Restocked %d shop items'):format(#shopItems))
end

-- Get shop statistics
function ShopManager.getShopStats(shopId, days)
    days = days or 7
    local cutoffDate = os.date('%Y-%m-%d %H:%M:%S', os.time() - (days * 24 * 60 * 60))
    
    -- Sales statistics
    local sales = Schema.shop_transactions
        :select(
            'item_name',
            Schema.raw('SUM(quantity) as total_sold'),
            Schema.raw('SUM(total_price) as revenue'),
            Schema.raw('COUNT(*) as transaction_count')
        )
        :where('shop_id', shopId)
        :where('transaction_type', 'buy')
        :where('timestamp', '>=', cutoffDate)
        :groupBy('item_name')
        :orderBy('revenue', 'DESC')
        :exec()
    
    -- Total revenue
    local totalRevenue = Schema.shop_transactions
        :where('shop_id', shopId)
        :where('transaction_type', 'buy')
        :where('timestamp', '>=', cutoffDate)
        :sum('total_price')
    
    return {
        sales = sales,
        totalRevenue = totalRevenue,
        period = days
    }
end

return ShopManager

Usage Example

-- server/main.lua
local ShopManager = require('server.modules.shop')

-- Initialize shops on server start
CreateThread(function()
    ShopManager.initialize()
end)

-- Restock shops every 30 minutes
CreateThread(function()
    while true do
        Wait(30 * 60 * 1000) -- 30 minutes
        ShopManager.restockShops()
    end
end)

-- Shop interaction events
RegisterServerEvent('shop:buy')
AddEventHandler('shop:buy', function(shopId, itemName, quantity)
    local success, message = ShopManager.buyItem(source, shopId, itemName, quantity)
    TriggerClientEvent('shop:buyResult', source, success, message)
end)

RegisterServerEvent('shop:sell')
AddEventHandler('shop:sell', function(shopId, itemName, quantity)
    local success, message = ShopManager.sellItem(source, shopId, itemName, quantity)
    TriggerClientEvent('shop:sellResult', source, success, message)
end)

-- Get shop data
RegisterServerCallback('shop:getShop', function(source, cb, shopId)
    local shop = ShopManager.getShop(shopId)
    local inventory = ShopManager.getShopInventory(shopId)
    local isOpen = ShopManager.isShopOpen(shopId)
    
    cb({
        shop = shop,
        inventory = inventory,
        isOpen = isOpen
    })
end)

-- Admin commands
RegisterCommand('restockshops', function(source, args)
    if source == 0 or IsPlayerAdmin(source) then
        ShopManager.restockShops()
        print('All shops restocked')
    end
end)

RegisterCommand('shopstats', function(source, args)
    if source == 0 or IsPlayerAdmin(source) then
        local shopId = tonumber(args[1])
        local days = tonumber(args[2]) or 7
        
        if shopId then
            local stats = ShopManager.getShopStats(shopId, days)
            print(json.encode(stats, {indent = true}))
        end
    end
end)

Key Features

This shop system demonstrates:

1. Multi-Shop Support: Different shop types and locations
2. Dynamic Inventory: Stock management with automatic restocking
3. Flexible Pricing: Different buy/sell prices
4. Transaction Logging: Complete audit trail of all transactions
5. Opening Hours: Time-based shop availability
6. Category System: Organized item categories
7. Performance Optimized: Proper database indexes for fast queries
8. Admin Tools: Shop management and statistics
9. Player Inventory: Complete inventory management system
10. Economic Balance: Built-in mechanisms to prevent economic exploits

The system is designed to be scalable and can easily support hundreds of items across multiple shop locations.