Restaurant Management System - Backend API

Project Overview

Restaurant Management System API is a production-ready RESTful backend service designed for comprehensive restaurant operations management. The API handles end-to-end workflows from customer authentication and menu browsing to order processing, table management, and post-order reviews.

Key Challenge: Design a scalable, secure API with granular role-based access control that prevents data leakage between users while maintaining intuitive authorization rules across 3 roles and 30+ endpoints.

Solution: Implemented middleware-based RBAC with decorator pattern, JWT dual-token authentication for security-performance balance, and Prisma ORM with strategic cascade rules to maintain referential integrity across 7 related entities.

Technical Architecture

Core Technology Stack

Runtime & Framework:

• Node.js v20.x (LTS)
• Express.js v5.2.1
• Prisma ORM v6.19.0

Database:

• Development: SQLite (file-based for rapid prototyping)
• Production: PostgreSQL (AWS RDS-compatible)

Security & Validation:

• Authentication: JWT (jsonwebtoken v9.0.3) with dual-token system
• Password Hashing: bcrypt v6.0.0 with 10 salt rounds
• Input Validation: Zod v3.25 with custom refinements
• Security Headers: Helmet v8.1.0
• Rate Limiting: express-rate-limit v8.2.1
• CORS Configuration: cors v2.8.5 with origin whitelist

Performance:

• Response Compression: compression v1.8.1 (gzip/deflate)
• Database Connection Pooling: Prisma connection management
• Strategic Indexing: 14 database indexes on high-traffic queries

Database Architecture

Entity Relationship Design

The database follows a normalized relational schema with 7 core entities designed to minimize redundancy and ensure data integrity.

Schema Highlights:

• Users: Authentication with role-based permissions
• Categories: Menu item classification
• MenuItems: Restaurant offerings with availability tracking
• Tables: Physical table management with real-time status
• Orders: Customer order workflow state machine
• OrderItems: Junction table with price snapshot pattern
• Reviews: One-review-per-user-per-item constraint

Key Design Patterns

1. Price Snapshot Pattern

Problem: Menu item prices can change over time, causing historical order data to become inaccurate.

Solution: When an order is created, the current menu item price is copied into OrderItem.price field. This creates an immutable price snapshot, ensuring order totals remain accurate even if menu prices are updated later. The subtotal field stores quantity × snapshot_price, preventing recalculation errors.

2. Order Workflow State Machine

Orders follow a strict state transition pattern to maintain business logic integrity:

PENDING → CONFIRMED → PREPARING → READY → COMPLETED
    ↓
CANCELLED

Validation Rules:

• Cannot skip states (e.g., PENDING → READY is invalid)
• Only PENDING orders can be cancelled by customers
• Staff can cancel at any state
• Terminal states (COMPLETED, CANCELLED) trigger table status updates

3. Cascade Delete Strategy

Authentication & Authorization System

Dual-Token JWT Architecture

Challenge: Balance security with user experience. Short-lived tokens enhance security but require frequent re-authentication. Long-lived tokens are convenient but increase vulnerability window.

Solution: Dual-token system with access/refresh token pattern.

Access Token:

• Lifetime: 15 minutes
• Payload: { userId, email, role }
• Purpose: Authorize API requests
• Storage: Client-side memory (never localStorage/cookies)

Refresh Token:

• Lifetime: 7 days
• Payload: { userId }
• Purpose: Issue new access tokens without re-login
• Storage: HttpOnly secure cookie (recommended) or client-side storage

Authentication Flow:

1. User submits credentials via POST /auth/login
2. Server validates credentials with bcrypt
3. Server generates both access and refresh tokens
4. Client stores access token in memory, refresh token in secure storage
5. Client includes access token in Authorization: Bearer <token> header
6. When access token expires (15 min), client calls POST /auth/refresh with refresh token
7. Server validates refresh token and issues new access token
8. Client updates stored access token

Benefits:

• Security: Short access token lifetime limits exposure window
• UX: Users stay logged in for 7 days without re-entering credentials
• Granular Control: Can revoke refresh tokens without invalidating all sessions
• Scalability: Stateless authentication (no server-side session storage)

Role-Based Access Control (RBAC)

The API implements a 3-tier role hierarchy with granular permissions across 30+ endpoints.

Role Definitions

Authorization Middleware Implementation

The API uses composable middleware functions to enforce role requirements:

Middleware Chain Pattern:

• authenticateToken → Validates JWT and attaches req.user
• requireRole(['ADMIN', 'STAFF']) → Checks if user role is in allowed list
• ownershipCheck → Validates resource ownership for scope-limited operations

Example Endpoint Protection:

• GET /orders → Requires authentication (any role), returns user-scoped data for CUSTOMER
• PUT /orders/:id → Requires authentication + ownership check for CUSTOMER, full access for STAFF/ADMIN
• DELETE /categories/:id → Requires authentication + ADMIN role

Advanced Query Features

Comprehensive Filtering System

Every collection endpoint supports powerful filtering, sorting, and pagination to handle large datasets efficiently.

Supported Query Parameters:

Example Query:

GET /menu-items?categoryId=1&isAvailable=true&minPrice=10000&maxPrice=50000&sortBy=price&order=asc&page=1&limit=20

Returns: Available menu items in category 1, priced 10,000-50,000 IDR, sorted by price ascending, page 1 with 20 results.

Pagination Response Structure:

{
  "success": true,
  "data": [...],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 45,
    "totalPages": 3
  }
}

Order Management Workflow

Order Lifecycle State Machine

Orders progress through a defined state machine with validation at each transition:

State Transition Rules:

Order Creation & Validation

Order Creation Flow:

1. Client sends order request with tableId, orderType, items[], optional notes
2. Server validates:
   • All menu items exist and are available (isAvailable = true)
   • Quantities are within bounds (1-20 per item)
   • For DINE_IN: tableId is provided and table is AVAILABLE
   • For TAKEAWAY/DELIVERY: tableId is null
3. Server creates order with auto-generated orderNumber (format: ORD-YYYYMMDD-XXXX)
4. Server creates OrderItem records with price snapshot from current menu price
5. Server calculates totalAmount = sum of all OrderItem subtotals
6. If DINE_IN: Update table status to OCCUPIED
7. Return order with all items and table details

Validation Rules:

• Order must contain at least 1 item
• Cannot order unavailable menu items
• DINE_IN orders require an AVAILABLE table
• TAKEAWAY/DELIVERY orders must not specify a table
• Notes field limited to 500 characters

Review System

Review Eligibility Validation

Challenge: Prevent fake reviews from users who never ordered the item.

Solution: Server-side validation checks order history before allowing review creation.

Eligibility Criteria:

1. User must have at least one COMPLETED order
2. That order must contain the menu item being reviewed (via OrderItems junction table)
3. User has not already reviewed this menu item (enforced by unique constraint on userId + menuItemId)

Review Creation Flow:

1. Client sends review with menuItemId, rating (1-5), optional comment
2. Server queries database for COMPLETED orders by this user containing this menu item
3. If no matching order found: return 400 error "You can only review items from your completed orders"
4. If user already reviewed this item: return 400 error "You have already reviewed this item"
5. Create review record
6. Recalculate menu item average rating (aggregation query on all reviews)

Review Permissions:

• Create: Any authenticated user (with eligibility check)
• Update: Review owner only
• Delete: Review owner or ADMIN

Table Management

Real-Time Status Updates

Table statuses are automatically updated based on order lifecycle events to reflect actual occupancy.

Automated Status Transitions:

Business Logic:

• A table is considered "active" if it has orders with status PENDING, CONFIRMED, PREPARING, or READY
• When the last active order on a table reaches COMPLETED or CANCELLED, the table automatically becomes AVAILABLE
• Staff can manually override status to RESERVED for advance bookings
• RESERVED status prevents automatic status updates (must be manually changed back to AVAILABLE)

Security Implementation

Input Validation with Zod

All request bodies are validated using Zod schemas before reaching controller logic. This prevents injection attacks, type errors, and business logic violations.

Validation Coverage:

• Email format validation (RFC 5322 compliant)
• Password strength requirements (min 6 characters)
• Enum value validation (role, orderType, status, location)
• Number range constraints (price, quantity, capacity, rating)
• String length limits (name, description, notes)
• URL format validation (imageUrl fields)
• Custom refinements (e.g., DINE_IN orders must have tableId)

Error Response Example: Invalid input returns 400 with Zod error details for debugging.

Security Headers & Middleware

Helmet.js Configuration:

• Content Security Policy (CSP) to prevent XSS
• X-Frame-Options to prevent clickjacking
• X-Content-Type-Options to prevent MIME sniffing
• Strict-Transport-Security for HTTPS enforcement (production)

CORS Policy:

• Whitelist specific origins from environment variable ALLOWED_ORIGINS
• Default: http://localhost:3000,http://localhost:5173 (dev frontend URLs)
• Production: Set to actual frontend domain

Rate Limiting: Critical endpoints have rate limits to prevent abuse:

Password Hashing:

• bcrypt with 10 salt rounds (balance between security and performance)
• Passwords never stored in plaintext
• Password comparison uses constant-time algorithm to prevent timing attacks

Deployment Architecture

AWS EC2 Infrastructure

Instance Configuration:

• Instance Type: t2.micro (1 vCPU, 1 GB RAM) - AWS Free Tier eligible
• OS: Ubuntu Server 22.04 LTS
• Region: us-east-1a (N. Virginia)
• Storage: 8 GB gp2 SSD

Elastic IP Configuration:

• Static IP: 44.216.9.251
• Purpose: Ensures API endpoint remains stable across instance restarts
• Use Case: Ideal for DNS A records, SSL/TLS certificate issuance, and production documentation

Security Group Rules:

Production Environment Variables:

• NODE_ENV=production
• DATABASE_URL=postgresql://... (PostgreSQL connection string)
• JWT_SECRET & JWT_REFRESH_SECRET (cryptographically secure keys)
• ALLOWED_ORIGINS (frontend domain whitelist)

Process Management:

• PM2 for Node.js process management and auto-restart
• Nginx as reverse proxy (port 80 → localhost:3000)
• Systemd service for PM2 auto-start on server reboot

API Endpoints Overview

Authentication Routes (/auth)

• POST /auth/register - User registration
• POST /auth/login - Login with access/refresh tokens
• POST /auth/refresh - Refresh access token
• GET /auth/me - Get authenticated user profile

Category Routes (/categories)

• GET /categories - List all categories (public)
• GET /categories/:id - Get category with menu items (public)
• POST /categories - Create category (ADMIN)
• PUT /categories/:id - Update category (ADMIN)
• DELETE /categories/:id - Delete category (ADMIN)

Menu Item Routes (/menu-items)

• GET /menu-items - List menu with filters (public)
• GET /menu-items/:id - Get menu item with reviews (public)
• POST /menu-items - Create menu item (STAFF/ADMIN)
• PUT /menu-items/:id - Update menu item (STAFF/ADMIN)
• DELETE /menu-items/:id - Delete menu item (ADMIN)

Table Routes (/tables)

• GET /tables - List all tables (public)
• GET /tables/:id - Get table with active orders (public)
• POST /tables - Create table (ADMIN)
• PUT /tables/:id - Update table (STAFF/ADMIN)
• DELETE /tables/:id - Delete table (ADMIN)

Order Routes (/orders)

• GET /orders - List orders (authenticated, role-scoped)
• GET /orders/:id - Get order details (owner or STAFF/ADMIN)
• POST /orders - Create order (authenticated)
• PUT /orders/:id - Update order status (role-dependent)
• DELETE /orders/:id - Delete cancelled order (ADMIN)

Review Routes (/reviews)

• GET /reviews - List reviews with filters (public)
• GET /reviews/:id - Get review details (public)
• POST /reviews - Create review (authenticated, eligibility-checked)
• PUT /reviews/:id - Update review (owner)
• DELETE /reviews/:id - Delete review (owner or ADMIN)

Testing & Documentation

Manual Testing Setup

The repository includes a comprehensive HTTP request collection in tests/restaurant-api.http for manual API testing with VS Code REST Client extension.

Test File Coverage:

• Authentication flow (register → login → refresh → profile)
• CRUD operations for all resources
• Role-based authorization testing
• Filter/pagination/sort combinations
• Error cases (invalid input, unauthorized access, not found)

API Documentation

Comprehensive API Docs: API-DOCS.md

Includes:

• All 30+ endpoint specifications
• Request/response examples with actual data
• Validation rules and constraints
• Authorization requirements per endpoint
• Error response formats
• Query parameter documentation

Key Features

1. Authentication System

• Dual-token JWT (15-min access + 7-day refresh)
• bcrypt password hashing (10 salt rounds)
• Role-based user registration (default: CUSTOMER)
• Secure token refresh without re-login

2. Role-Based Access Control

• 3-tier role hierarchy (CUSTOMER, STAFF, ADMIN)
• Granular permissions across 30+ endpoints
• Ownership-based resource access (users can only modify their own data)
• Middleware-enforced authorization

3. Menu Management

• Hierarchical category organization
• Menu item availability toggling
• Preparation time tracking
• Average rating calculation from reviews
• Image URL support for all items

4. Order Processing

• 6-state workflow state machine
• Auto-generated order numbers (ORD-YYYYMMDD-XXXX)
• Price snapshot pattern (historical accuracy)
• 3 order types (DINE_IN, TAKEAWAY, DELIVERY)
• Per-item special notes

5. Table Management

• Real-time status tracking (AVAILABLE, OCCUPIED, RESERVED)
• Automated status updates based on order lifecycle
• 3 location types (INDOOR, OUTDOOR, VIP)
• Capacity management (1-20 seats)

6. Review System

• Verified reviews (only from completed orders)
• One-review-per-user-per-item constraint
• 1-5 star rating system
• Auto-calculated menu item average ratings
• Owner and ADMIN delete permissions

7. Advanced Querying

• Full-text search on name/description fields
• Multi-field filtering (price range, category, status, date range)
• Flexible sorting (any field, asc/desc)
• Cursor-based pagination with metadata
• Maximum 100 results per page limit

8. Security & Performance

• Helmet.js security headers
• CORS origin whitelist
• Rate limiting on critical endpoints
• Response compression (gzip/deflate)
• 14 strategic database indexes
• Zod input validation

Challenges & Solutions

Challenge 1: Order Total Calculation Consistency

Problem: Menu item prices can change over time. If Order.totalAmount is calculated dynamically by summing current menu prices, historical orders would show incorrect totals after price updates.

Solution: Price snapshot pattern. When an order is created, copy the current MenuItem.price into OrderItem.price field. Calculate subtotal = quantity × snapshot_price. Store Order.totalAmount as the sum of all OrderItem subtotals at creation time. This makes order totals immutable and historically accurate.

Challenge 2: Preventing Review Spam

Problem: Users could write fake reviews for menu items they never ordered, or spam multiple reviews for the same item.

Solution: Two-level validation:

1. Eligibility Check: Query database for COMPLETED orders by this user that contain the menu item (via OrderItems join). Only allow review if match found.
2. Unique Constraint: Database-level unique constraint on (userId, menuItemId) prevents duplicate reviews.

Challenge 3: Table Status Race Conditions

Problem: Multiple concurrent orders for the same table could create inconsistent status (e.g., two orders both try to set AVAILABLE → OCCUPIED).

Solution: Atomic status updates using Prisma's transactional mutations. Order creation and table status update happen in a single database transaction. If concurrent requests occur, the second one fails with a "table already occupied" error instead of silently overwriting.

Challenge 4: Role-Based Query Scoping

Problem: CUSTOMER role should only see their own orders, but STAFF/ADMIN should see all orders. This requires different queries based on role.

Solution: Conditional query building in controller. If req.user.role === 'CUSTOMER', add where: { userId: req.user.userId } filter. Otherwise, omit the filter. Single endpoint, dynamic authorization logic.

Performance Metrics

Database Query Optimization

Index Coverage Analysis:

• 14 indexes across 7 tables
• Average query time for filtered endpoints: 15-30ms
• Average query time for paginated list: 20-40ms
• Largest table (Order): sub-50ms queries even with 100,000+ rows

Key Optimizations:

• Composite index on Review(userId, menuItemId) for eligibility checks
• Index on Order(status, userId, createdAt) for dashboard queries
• Index on MenuItem(categoryId) for category page loading
• Index on Table(status) for available table filtering

API Response Times (Production)

Note: Response times measured on AWS EC2 t2.micro instance. Login is slower due to bcrypt hashing (intentional for security).

Key Insights

1. Price Snapshot Pattern is Critical: Without snapshotting menu prices in OrderItem, historical orders become inaccurate when prices change. This pattern ensures immutable financial records.

2. Dual-Token JWT Balances Security & UX: 15-minute access tokens limit exposure window, while 7-day refresh tokens prevent constant re-authentication. Best of both worlds.

3. Role-Based Scoping Must Be Explicit: Implicit role checks lead to data leakage. Always explicitly filter queries by userId for CUSTOMER role.

4. State Machine Prevents Invalid Transitions: Enforcing order status workflow at the API level prevents business logic violations (e.g., skipping preparation state).

5. Database Indexes Are Non-Negotiable: Without indexes on foreign keys and filter fields, query performance degrades exponentially with data growth.

6. Validation Should Fail Fast: Zod validation at middleware layer prevents invalid data from reaching controller logic, simplifying error handling.

7. Cascade Rules Require Careful Design: Wrong cascade strategy can cause accidental data loss (CASCADE) or prevent legitimate deletions (RESTRICT). Map out dependencies before schema design.

Conclusion

Restaurant Management System API demonstrates production-grade backend development with comprehensive RBAC, secure authentication, complex business logic enforcement, and scalable query patterns. The API successfully handles multi-role authorization, order workflow state machines, and real-time table status management while maintaining sub-100ms response times on modest hardware.

Key Achievements:

• 30+ REST endpoints with granular role-based permissions
• 7-entity normalized database with strategic cascade rules
• Dual-token JWT authentication system
• Order state machine with automated table status updates
• Review eligibility validation to prevent spam
• Deployed on AWS EC2 with Elastic IP for stable endpoints

Technical Highlights:

• Prisma ORM with 14 strategic indexes for query optimization
• Zod validation with custom refinements for business rules
• Middleware-based RBAC with ownership checks
• Price snapshot pattern for historical accuracy
• Rate limiting on critical endpoints (5-10 req/15min)
• Response compression reducing payload size by 60-80%

This project serves as a reference architecture for building secure, scalable RESTful APIs with Node.js, demonstrating best practices in database design, authentication, authorization, and cloud deployment.

Repository: GitHub - Restaurant API

Live API: http://44.216.9.251/api

Technologies: Node.js, Express.js, Prisma ORM, SQLite/PostgreSQL, JWT, Zod, bcrypt, AWS EC2

Duration: 1 month solo Development

Course: Web Programming, Hasanuddin University, 2025