How to Build a Hotel ERP System from Scratch (React + Node.js)

What Is a Hotel ERP and Why Build One?

ERP stands for Enterprise Resource Planning — software that unifies all operational data and workflows into a single system. For a hotel, that means every department — reception, housekeeping, restaurant, procurement, HR, and finance — sharing one database and one interface instead of a fragmented stack of spreadsheets and standalone tools.

The market problem is obvious once you look at it: the dominant hotel management software options (Opera PMS, Cloudbeds, RMS Cloud) range from $500 to $3,000+ per month, charge per-property fees, lock data in proprietary formats, and require training programs. For independent hotels, boutique properties, and small hotel chains in emerging markets, these costs are prohibitive.

A custom Hotel ERP built on modern open-source technology costs a fraction to maintain, can be deployed on a single AWS EC2 instance, and becomes a fully owned asset — not a recurring SaaS liability. I built exactly this for a hospitality client, and the case for it was compelling from day one.

Core Modules to Build

A production-ready Hotel ERP needs six interconnected modules. Each maps to a real department and a real workflow:

1. Front Desk / Reception

The reception module is the operational core. It handles check-in and check-out flows, room assignment (with a visual floor plan grid), walk-in booking creation, and booking modification. Key UX requirement: a receptionist should be able to complete a standard check-in in under 60 seconds. That means minimal clicks, keyboard shortcuts, and smart defaults (today's date, first available room of the requested type).

2. Room & Housekeeping Management

Every room has a status — available, occupied, cleaning, maintenance, or inspecting. Housekeeping staff see their assigned rooms on a mobile-friendly view. When a guest checks out, the room automatically transitions to cleaning and gets assigned to the next available housekeeper based on their floor assignment. The front desk sees this status in real time before assigning the room to a new guest.

3. Restaurant & POS

The restaurant module has two interfaces: a waiter-facing order tablet (table selection, item selection from menu, modifier notes) and a kitchen display system (KDS) that shows pending orders in real time. Billing supports split bills, charge-to-room, cash, card, and UPI. A daily restaurant summary rolls up into the financial dashboard.

4. Inventory & Procurement

Track consumables (toiletries, F&B ingredients, cleaning supplies) with real stock levels. When stock drops below a configurable threshold, a low-stock alert triggers and optionally creates a draft purchase order. Vendors are associated with items, and purchase order history is tracked for auditing.

5. Staff Management

Manage employee profiles, role assignments, shift scheduling, and attendance marking. A basic payroll summary calculates hours worked and generates a downloadable report. Leave requests go through a manager approval workflow before affecting scheduling.

6. Financial Reporting

The finance dashboard aggregates all revenue streams: room revenue, F&B revenue, ancillary services. Key metrics: occupancy rate by day/week/month, RevPAR (revenue per available room), average daily rate (ADR), and P&L by department. Data export to CSV for accounting handoff.

Tech Stack Decision

The stack choices here are deliberate — not trendy defaults, but tools that match the operational requirements of a multi-user, real-time, relational-data application:

Why MySQL over MongoDB? Hotel data is deeply relational — a booking connects to a room, a guest, a rate plan, a payment method, and possibly a channel (OTA, direct, walk-in). Relational integrity matters enormously here. A guest who's been charged twice or a room double-booked is a crisis. MySQL's ACID transactions and foreign key constraints give us that safety net.

Why Redux Toolkit? The front desk view aggregates room status, active bookings, pending check-outs, and restaurant orders simultaneously. React local state doesn't scale to this cross-module data sharing cleanly. RTK with RTK Query for API calls keeps the state layer manageable.

Database Schema Overview

The schema centers on a few core tables. Here are the most critical ones to get right early — changing them later is expensive:

CREATE TABLE rooms (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  hotel_id  INT NOT NULL,
  room_number VARCHAR(10),
  type      ENUM('single','double','suite','deluxe'),
  status    ENUM('available','occupied','maintenance','cleaning'),
  floor     TINYINT,
  price_per_night DECIMAL(10,2),
  FOREIGN KEY (hotel_id) REFERENCES hotels(id)
);

CREATE TABLE bookings (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  room_id     INT NOT NULL,
  guest_name  VARCHAR(100),
  guest_email VARCHAR(150),
  guest_phone VARCHAR(20),
  check_in    DATE NOT NULL,
  check_out   DATE NOT NULL,
  adults      TINYINT DEFAULT 1,
  status      ENUM('confirmed','checked_in','checked_out','cancelled','no_show'),
  total_amount DECIMAL(10,2),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(id)
);

CREATE TABLE inventory_items (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  hotel_id    INT NOT NULL,
  name        VARCHAR(100),
  category    VARCHAR(50),
  quantity    INT DEFAULT 0,
  min_quantity INT DEFAULT 10,
  unit        VARCHAR(20),
  cost_per_unit DECIMAL(10,2)
);

CREATE TABLE transactions (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  hotel_id    INT NOT NULL,
  booking_id  INT,
  type        ENUM('room','restaurant','service','refund'),
  amount      DECIMAL(10,2),
  payment_mode ENUM('cash','card','upi','charge_to_room'),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

API Design (REST)

The REST API follows a resource-oriented design. Here are the core endpoints for the initial modules:

• GET /api/rooms List rooms with status filter
• POST /api/bookings Create new booking
• PUT /api/rooms/:id/status Update room status
• GET /api/reports/daily Daily revenue summary
• POST /api/restaurant/orders Create restaurant order
• PUT /api/bookings/:id/checkin Execute check-in
• GET /api/inventory/alerts Low-stock items

RBAC Implementation

Role-Based Access Control is non-negotiable in a multi-staff system. A housekeeper shouldn't be able to access financial reports, and a restaurant waiter shouldn't be able to modify bookings. JWT tokens carry the user's role, and each Express route runs a middleware check before processing:

// Middleware factory: requireRole(['hotel_manager', 'superadmin'])
const requireRole = (allowedRoles) => (req, res, next) => {
  if (!req.user) return res.status(401).json({ error: 'Unauthorized' });
  if (!allowedRoles.includes(req.user.role)) {
    return res.status(403).json({ error: 'Forbidden: insufficient role' });
  }
  next();
};

// Usage on a route
router.get('/api/reports/daily',
  verifyToken,
  requireRole(['superadmin', 'hotel_manager']),
  getDailyReport
);

Key Challenges & How I Solved Them

Real-Time Room Status Updates

When a housekeeper marks a room as "clean" on their phone, the front desk should see that update immediately — not on the next page refresh. I implemented this with Socket.io for the primary real-time layer, with a 30-second polling fallback for clients behind strict firewalls. The room status grid on the reception dashboard updates without any manual action.

Multi-Branch Support

Every query is scoped with WHERE hotel_id = ?. Sounds simple, but it requires discipline: every middleware must resolve the active hotel_id from the JWT, every new table must include the hotel_id column with a foreign key constraint, and the superadmin can switch contexts between branches without logging out.

Concurrent Booking Conflicts

Double-booking is a nightmare scenario. Two receptionists booking the same room at the exact same moment. The solution: database-level row locking during the booking transaction:

START TRANSACTION;

-- Lock this room row for the duration of the transaction
SELECT * FROM rooms
  WHERE id = ? FOR UPDATE;

-- Check for overlapping bookings
SELECT COUNT(*) FROM bookings
  WHERE room_id = ?
  AND status NOT IN ('cancelled', 'checked_out')
  AND (check_in < ? AND check_out > ?);

-- If count = 0, safe to insert. Otherwise, rollback.
INSERT INTO bookings (...) VALUES (...);

COMMIT;