Designer Concepts - ERP, POS & CRM Suite

Branch Context:

Simulated SQL Server Online

Executive Performance Dashboard

Real-time business indicators aggregated across South African retail showrooms.

Period: Today

Total Sales (Today)

R 0.00

+12% compared to yesterday

Active Custom Orders

0

In fabrication / supplier workshop

Low Stock Items

0

Below minimum reorder safety limits

Active ClickUp Deals

0

In CRM sales pipeline

Revenue Distribution by Branch

Total System Invoices

Today's Cashup Status

Live System Sales Log

Doc Number	Customer	Branch	Date & Time	Type	Total Amount	Status	Actions

Inventory Management & Stock Transfers

Track and audit branch physical inventories, initiate transfers, and reconcile logistics.

Filter Branch:

SKU Code	Item Name	Category	Retail Price	Trade Price	Vereeniging HQ	Alberton	Randfontein	Protea Glen	Alert Threshold

Inter-Branch Stock Movement Requests

Managers of destination branches must physically check and approve receipt of transfers (acting as a Transfer GRN).

Transfer ID	Item Name	From Branch	To Branch	Transfer Qty	Date Raised	Status	Actions Required

Financial Management & Documents

Access, print, and audit Quote logs, Invoice states, Credit Notes, and Purchase Orders.

Document Reference	Customer/Supplier	Branch Context	Execution Date	Document Classification	Grand Total	Amount Paid / Status	Print Preview

Shift Cashup Submission

Log physical terminal drops and variance reviews at shift conclusion.

Opening Register Float

Actual Cash in Drawer

Actual Card slips

Payfast

Payflex

Laybuy

Expected Sales (Tills logged): R 0.00

Total Physical Submitted: R 0.00

Calculated Variance: R 0.00

Authorized Digital Signature (Draw below)

Z-Report Cashup Audit Logs

Browse verified closing declarations raised per branch terminal.

Submission ID	Branch	Operator Name	Float In	Total Actual Counted	Variance	Signature Audit	Status

System Architecture & Code Blueprint

This code/schema was created specifically for your production developers to build out the backend of this Designer Concepts application.

Production MySQL Schema

-- ==========================================
-- DESIGNER CONCEPTS DATABASE STRUCTURAL SCHEMA
-- ==========================================

CREATE DATABASE IF NOT EXISTS designer_concepts_erp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE designer_concepts_erp;

-- 1. Branches Reference Table
CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. System Users & Roles Matrix (RBAC)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    role ENUM('Super Admin', 'Manager', 'Stock Controller', 'Sales', 'Tills', 'Finance') NOT NULL,
    branch_id INT NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- 3. Core Products Table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    retail_price DECIMAL(10,2) NOT NULL,
    trade_price DECIMAL(10,2) NOT NULL,
    min_safety_level INT DEFAULT 5,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Multi-Branch Inventory Ledger
CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity_on_hand INT DEFAULT 0,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY branch_product_uq (branch_id, product_id)
);

-- 5. Inter-Branch Stock Transfers
CREATE TABLE stock_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_branch_id INT NOT NULL,
    to_branch_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    status ENUM('Requested', 'Dispatched', 'Received', 'Cancelled') DEFAULT 'Requested',
    raised_by INT NOT NULL,
    approved_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_branch_id) REFERENCES branches(id),
    FOREIGN KEY (to_branch_id) REFERENCES branches(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (raised_by) REFERENCES users(id)
);

-- 6. Customers Table (Wholesale & Retail support)
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    tier ENUM('Retail', 'Trade') DEFAULT 'Retail',
    custom_discount_pct DECIMAL(5,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 7. Document Ledgers (Quotes, Invoices, Orders)
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doc_number VARCHAR(50) NOT NULL UNIQUE,
    branch_id INT NOT NULL,
    customer_id INT NULL,
    creator_id INT NOT NULL,
    doc_type ENUM('Quote', 'Invoice', 'Credit Note', 'Purchase Order', 'Custom Order') NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    vat DECIMAL(10,2) NOT NULL,
    grand_total DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0.00,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (creator_id) REFERENCES users(id)
);

-- 8. Document Itemized Line Items
CREATE TABLE document_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    product_id INT NULL,
    product_name_override VARCHAR(255) NULL, -- Custom fabrication tracking
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- 9. Custom Tailored Product Specifications
CREATE TABLE custom_order_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    fabric_choice VARCHAR(150) NOT NULL,
    custom_dimensions VARCHAR(255) NOT NULL,
    production_lead_weeks INT DEFAULT 4,
    production_status ENUM('Quote', 'Sent to Workshop', 'In Production', 'Ready', 'Completed') DEFAULT 'Quote',
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

-- 10. Financial Payments & Split Gateway Log
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('Cash', 'Card', 'Payfast', 'Payflex', 'Laybuy', 'Split') NOT NULL,
    gateway_txn_ref VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

-- 11. Daily Shift Terminal Cashups (Z-Reports)
CREATE TABLE daily_cashups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    user_id INT NOT NULL,
    opening_float DECIMAL(10,2) NOT NULL,
    actual_cash DECIMAL(10,2) NOT NULL,
    actual_card DECIMAL(10,2) NOT NULL,
    actual_payfast DECIMAL(10,2) NOT NULL,
    actual_payflex DECIMAL(10,2) NOT NULL,
    actual_laybuy DECIMAL(10,2) NOT NULL,
    expected_totals DECIMAL(10,2) NOT NULL,
    counted_totals DECIMAL(10,2) NOT NULL,
    variance DECIMAL(10,2) NOT NULL,
    signature_data TEXT NOT NULL, -- Base64 Canvas Drawing String
    status ENUM('Approved', 'Flagged') DEFAULT 'Approved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 12. CRM Activity Tracker (Kanban Task cards)
CREATE TABLE crm_pipeline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(150) NOT NULL,
    deal_name VARCHAR(255) NOT NULL,
    expected_value DECIMAL(10,2) NOT NULL,
    stage ENUM('Lead', 'Quote', 'Fabrication', 'Delivered') DEFAULT 'Lead',
    assigned_user_id INT NULL,
    notes TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_user_id) REFERENCES users(id)
);

Complex Production Queries

-- ==========================================
-- DESIGNER CONCEPTS TRANSACTIONAL QUERIES
-- ==========================================

-- A. Multi-Branch Real-Time Inventory Value & Out-of-Stock Tracking
SELECT 
    b.name AS branch_name,
    p.sku,
    p.name AS product_name,
    p.category,
    i.quantity_on_hand,
    p.retail_price,
    (i.quantity_on_hand * p.retail_price) AS asset_value,
    CASE 
        WHEN i.quantity_on_hand <= p.min_safety_level THEN 'ALERT: LOW STOCK'
        ELSE 'OK'
    END AS safety_status
FROM inventory i
INNER JOIN products p ON i.product_id = p.id
INNER JOIN branches b ON i.branch_id = b.id
ORDER BY b.name ASC, i.quantity_on_hand ASC;


-- B. Complex POS Checkout Script (Transactional Safety Wrapper)
START TRANSACTION;

-- 1. Insert Core Document Ledger Record
INSERT INTO documents (doc_number, branch_id, customer_id, creator_id, doc_type, subtotal, vat, grand_total, amount_paid, status)
VALUES ('INV-V-10254', 1, 3, 2, 'Invoice', 8695.65, 1304.35, 10000.00, 10000.00, 'Fully Paid');

SET @last_doc_id = LAST_INSERT_ID();

-- 2. Link Custom Furniture Fabrication Rules (If Couch/Bed was Tailor-made)
INSERT INTO custom_order_details (document_id, fabric_choice, custom_dimensions, production_lead_weeks, production_status)
VALUES (@last_doc_id, 'Deep Mauve Velvet (Premium)', '2.4m Length x 0.9m Depth', 6, 'Sent to Workshop');

-- 3. Add Item Details
INSERT INTO document_items (document_id, product_id, quantity, unit_price, line_total)
VALUES (@last_doc_id, 2, 1, 10000.00, 10000.00);

-- 4. Deduct Inventory levels (Strict FIFO Allocation safety)
UPDATE inventory 
SET quantity_on_hand = quantity_on_hand - 1 
WHERE branch_id = 1 AND product_id = 2;

-- 5. Record Multi-Payment Method Routing (Split Payment Gateways)
INSERT INTO payments (document_id, amount, payment_method, gateway_txn_ref)
VALUES 
(@last_doc_id, 5000.00, 'Payflex', 'PFX-9824982424'),
(@last_doc_id, 5000.00, 'Card', 'CREDIT-928424-DC');

COMMIT;


-- C. Daily Cashup Reconcile Query (Checks Cash drawer variance)
SELECT 
    c.id AS cashup_id,
    b.name AS branch,
    u.full_name AS operator,
    c.opening_float,
    c.expected_totals,
    c.counted_totals,
    c.variance,
    CASE 
        WHEN ABS(c.variance) > 100 THEN 'FLAGGED FOR SECURITY EXPLANATION'
        ELSE 'RECONCILED'
    END AS audit_decision,
    c.created_at
FROM daily_cashups c
INNER JOIN branches b ON c.branch_id = b.id
INNER JOIN users u ON c.user_id = u.id
WHERE DATE(c.created_at) = CURRENT_DATE();