Enhancing the Restaurant Management System: Final Part

Real-time order tracking, automate inventory updates, and secure access with role-based access control.

Apr 01, 2025

In Part 2, we enhanced our restaurant management system with advanced queries for inventory management, order processing, and report generation. Now, in Part 3, we’re stepping things up! This time, we’re adding real-time order tracking, automating inventory updates, and implementing role-based access control (RBAC) to secure the system. If you’ve come too far, check out our Beginner’s Guide to Mastering SQLite.

Modern restaurant management system with real-time order tracking, inventory automation, and security.

These additions will make our system more responsive, secure, and efficient, ensuring that both customers and staff have an optimal experience. By the end of this blog, you’ll be able to:

Track orders in real-time, keeping both customers and staff updated.
Automate inventory updates when orders are placed, keeping stock accurate.
Implement role-based access control (RBAC), securing access to sensitive features of the system.

Step 1: Real-Time Order Tracking

Keeping track of orders in real time is crucial for both restaurant staff and customers. In a busy restaurant, knowing the status of each order—whether it's being prepared, ready for pickup, or completed—can save time, improve communication, and enhance customer satisfaction.

Let’s begin by modifying the orders table to include a status column:

-- Adding a Status column to track order progress
ALTER TABLE orders
ADD COLUMN Status TEXT DEFAULT 'Received'; -- Possible values: Received, Preparing, Ready, Completed

We can then create a Flask route that allows us to update and view the status of each order:

# Flask route to update the status of an order
@app.route('/update_order_status', methods=['POST'])
def update_order_status():
    order_id = request.form['order_id']
    status = request.form['status']

    # Update order status
    order = Order.query.get(order_id)
    if order:
        order.status = status
        db.session.commit()
        return "Order status updated!"
    else:
        return "Order not found!"

# Flask route to track the order status
@app.route('/track_order/<int:order_id>', methods=['GET'])
def track_order(order_id):
    order = Order.query.get(order_id)
    if order:
        return f"Order Status: {order.status}"
    else:
        return "Order not found!"

This setup allows us to track the order status in real-time. For example, as the order moves from Received to Preparing, to Ready, and finally Completed, the status gets updated and displayed to both staff and customers.

Real-Time Front-End Updates with JavaScript

To avoid constantly refreshing the page, we can use AJAX to periodically update the order status. This keeps the user experience seamless:

// JavaScript to periodically fetch order status
function updateOrderStatus(orderId) {
    setInterval(function() {
        fetch(`/api/orders/${orderId}`)
            .then(response => response.json())
            .then(data => {
                document.getElementById("orderStatus").innerText = data.status;
            });
    }, 5000); // Updates every 5 seconds
}

This way, customers can see their order status in real-time without refreshing their browsers!

Step 2: Automating Inventory Updates

Managing inventory is essential in a fast-paced restaurant environment. When an order is placed, you need to update your inventory to reflect the changes in stock levels automatically. Here’s how we can implement this with a trigger in SQLite.

Creating a Trigger for Automatic Inventory Updates

Let’s create a trigger that automatically updates the inventory once an order is placed:

-- Creating a trigger to update inventory when an order is completed
CREATE TRIGGER update_inventory_after_order
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    -- Deduct the quantity from the inventory based on the ordered items
    UPDATE inventory
    SET quantity_in_stock = quantity_in_stock - (SELECT quantity FROM order_items WHERE order_id = NEW.order_id AND item_id = inventory.item_id)
    WHERE item_id IN (SELECT item_id FROM order_items WHERE order_id = NEW.order_id);
END;

Every time an order status changes to Completed, this trigger automatically reduces the inventory count for the corresponding items in the order.

Flask Example for Placing an Order and Updating Inventory

Now let’s implement the Flask route to place an order and update inventory:

# Flask route for placing an order
@app.route('/place_order', methods=['POST'])
def place_order():
    item_id = request.form['item_id']
    quantity = request.form['quantity']

    # Check if enough stock is available
    inventory = Inventory.query.filter_by(item_id=item_id).first()
    if inventory.quantity_in_stock < quantity:
        return "Insufficient stock. Please reduce quantity or choose another item."

    # Create the order and update inventory
    new_order = Order(item_id=item_id, quantity=quantity)
    db.session.add(new_order)
    db.session.commit()

    # Update inventory
    inventory.quantity_in_stock -= quantity
    db.session.commit()

    return "Order placed successfully!"

In this example, when an order is placed, we first check if there’s enough stock. If so, we reduce the inventory and place the order.

Step 3: Role-Based Access Control (RBAC)

Role-Based Access Control (RBAC) ensures that only authorized staff members can access specific features of the system. We’ll use Admin, Manager, and Waitstaff as roles, each with different access privileges.

Creating Roles and Assigning Them to Users

Let’s start by creating the roles table and assigning roles to users:

-- Creating a roles table
CREATE TABLE roles (
    RoleID INTEGER PRIMARY KEY,
    RoleName TEXT NOT NULL
);

-- Assigning roles to users
INSERT INTO roles (RoleName) VALUES ('Admin'), ('Manager'), ('Waitstaff');

Now, we’ll assign these roles to users:

-- Creating a users table with roles
CREATE TABLE users (
    UserID INTEGER PRIMARY KEY,
    Username TEXT NOT NULL,
    Password TEXT NOT NULL,
    RoleID INTEGER,
    FOREIGN KEY (RoleID) REFERENCES roles(RoleID)
);

-- Assigning roles to users
INSERT INTO users (Username, Password, RoleID) VALUES ('admin_user', 'admin_password', 1);  -- Admin
INSERT INTO users (Username, Password, RoleID) VALUES ('manager_user', 'manager_password', 2);  -- Manager

Access Control Based on Roles

In Flask, we can protect specific routes by checking the user’s role:

# Flask route for managing orders (Admin and Manager roles)
@app.route('/manage_orders')
@require_role('Admin')  # Restrict access to Admin role
def manage_orders():
    return "Welcome to the Order Management Page!"

# Flask route for viewing reports (Admin and Manager roles)
@app.route('/view_reports')
@require_role('Manager')  # Restrict access to Manager and Admin roles
def view_reports():
    return "Welcome to the Reports Page!"

# Flask route for updating orders (Waitstaff role)
@app.route('/update_order_status')
@require_role('Waitstaff')  # Restrict access to Waitstaff role
def update_order_status():
    return "Update the status of an order"

This ensures that only users with the Admin role can access order management, while Managers can access reports, and Waitstaff can update order statuses.

Conclusion: Wrapping Up the Restaurant Management System Series

In Part 3, we’ve added several critical features to our restaurant management system:

Real-time order tracking to keep customers and staff updated on the status of orders.
Automated inventory updates to maintain stock levels without manual intervention.
Role-based access control (RBAC) to secure access to sensitive features and ensure only authorized users can perform certain actions.

This marks the final part of the Restaurant Management System series. We’ve covered everything from database setup, inventory management, payment processing, real-time updates, and security. You’re now ready to implement these features in real-world applications and create a fully functional, secure restaurant management system.

Thank you for following along with this journey! We hope you’ve gained valuable insights into SQLite and its practical applications.

Subscribe Now

Stay updated with the latest tips and tutorials on optimizing your restaurant management system with SQLite! Subscribe now to receive expert advice, step-by-step guides, and updates directly in your inbox. Don’t miss out on future projects, tutorials, and much more. Join our community at the SQLite Forum to ask questions, share experiences, and connect with fellow developers!

SQLite Forum