Case Study: Urban Bar CRM (UBCRM) - A Lightweight CRM and Mini-ERP for Urban Bars

Context & Motivation

Urban Bar CRM (UBCRM) was developed as part of my bachelor thesis, titled "A Lightweight CRM Application for Urban Bars: Supporting Digital Transformation, Customer Engagement, and Marketing Visibility."

While academic in origin, the real motivation came from practice. I managed a bar myself and experienced firsthand the operational fragmentation common in small and mid-sized hospitality businesses: customer data scattered across tools, loyalty programs run manually, staff schedules maintained informally, and little visibility into performance beyond daily revenue.

UBCRM was designed to solve that gap: a single, lightweight, cloud-based system that combines CRM, operational management, and customer engagement into a mini-ERP tailored specifically for urban bars and restaurants.

Problem Statement

Most urban bars and restaurants face the same constraints:

Limited budgets for enterprise ERP systems that cost thousands per month. Fragmented tooling with POS systems here, Excel spreadsheets there, Instagram messages elsewhere, and paper loyalty cards in a drawer. No centralized view of customers, staff, inventory, and marketing performance. Loyalty programs that are manual, inconsistent, or non-existent.

This fragmentation results in lost repeat customers because staff can't recognize regulars or recall preferences, poor staff coordination leading to scheduling conflicts and communication gaps, inefficient inventory usage with stockouts during peak hours and over-ordering waste, and minimal data-driven decision making, relying on intuition rather than actual metrics.

UBCRM was built to digitally transform hospitality operations without the overhead of enterprise software. The goal was to deliver ERP-level functionality in a lightweight, affordable package that small businesses could actually adopt and maintain.

Solution Overview

UBCRM is a two-panel web application designed to serve different user needs:

Admin / Employee Panel

Designed for owners, managers, and staff to manage operations, customers, and marketing. This is the operational hub where daily business activities happen.

Customer Panel

A dedicated interface where customers can view their activity, earn loyalty points, and engage with the venue digitally. This transforms passive loyalty cards into active digital engagement.

The system acts as a central operational hub, unifying CRM, staff management, inventory, loyalty, and analytics in one platform. I designed it to replace multiple disconnected tools with a single source of truth.

System Architecture & Technology Stack

Frontend

I chose Next.js 14 with the App Router for the frontend architecture. The App Router's server components allowed me to reduce client-side JavaScript while maintaining interactivity where needed. This was crucial for tablet performance during peak hours.

TypeScript provided end-to-end type safety, catching errors at compile time rather than runtime. This was especially valuable when working with Supabase's generated types and ensuring data consistency across the application.

Tailwind CSS enabled rapid UI development with utility-first styling. I could iterate quickly on tablet-optimized layouts without maintaining separate CSS files.

shadcn/ui provided an accessible, high-quality component system built on Radix UI primitives. This gave me professional components like dialogs, dropdowns, and data tables without building them from scratch.

Lucide React ensured consistent iconography throughout the application, maintaining visual coherence across both admin and customer panels.

Backend & Infrastructure

Supabase served as the complete backend infrastructure. It includes a PostgreSQL database with proper indexing for fast queries during peak traffic, built-in authentication with JWT tokens and session management, real-time subscriptions using PostgreSQL's replication slots for live updates, file storage for customer photos and inventory images, and row-level security (RLS) policies enforcing role-based access control at the database level.

The RLS policies were critical for security. I implemented them so that staff can only access data for their venue, and customers can only see their own data. This security is enforced at the database level, not just in the UI, preventing unauthorized access even if someone bypasses the frontend.

Development & Quality

ESLint ensured code consistency across the codebase, catching common mistakes and enforcing coding standards. Jest and React Testing Library validated component behavior and business logic. I wrote tests for critical workflows like booking creation, loyalty point calculations, and inventory alerts to ensure reliability.

Core Features & Business Impact

1. Admin / Employee Panel

I built a real-time dashboard that gives owners a comprehensive overview of their business. The dashboard displays daily and monthly performance indicators, customer activity and visit frequency, and revenue trends with forecasting.

The forecasting uses simple linear regression on historical data to predict future revenue. While not as sophisticated as enterprise forecasting tools, it provides actionable insights for small businesses. The dashboard replaces intuition-based management with data-informed decision making.

I implemented this using Supabase's PostgreSQL functions to perform aggregations on the database side, reducing data transfer and improving query performance. For time-series data, I used materialized views that refresh hourly, trading some real-time accuracy for query speed.

The CRM module includes detailed customer profiles with visit history tracking, spending patterns, and preferences. When staff search for a customer, the system uses PostgreSQL's full-text search (tsvector and tsquery) for name searches, combined with phone number normalization for phone lookups.

The search results display in a compact card format optimized for tablet screens, showing customer photos (optimized with Next.js Image), visit history, preferences, and spending patterns. This allows staff to recognize regulars instantly, personalize service, and increase repeat visits.

I denormalized frequently accessed data like visit counts and last visit dates directly on the customer table to avoid joins during peak traffic. This was a performance trade-off - slightly more storage for significantly faster queries.

The staff management system includes role-based access control, shift scheduling with conflict detection, and performance insights tied to activity and sales.

I implemented role-based permissions at both the UI and database levels. Staff can only see schedules and customer data for their venue, and managers have additional permissions for analytics and reporting. The RLS policies enforce this at the database level, so even direct database access is restricted.

The scheduling system prevents overlapping shifts and sends notifications for schedule changes. Performance insights show staff activity metrics, sales contributions, and customer feedback, improving accountability and workforce planning.

The inventory system monitors stock levels and sends automated low-stock alerts via email and in-app notifications. I built this using PostgreSQL triggers that fire when inventory levels drop below configured thresholds.

The alerts are sent asynchronously using Supabase Edge Functions to avoid blocking the main application. This reduces stockouts during peak hours and prevents over-ordering waste by providing timely visibility into inventory status.

2. Marketing & Analytics

I implemented customer segmentation based on visit frequency, spending behavior, and engagement level. The segmentation logic runs as a PostgreSQL function that categorizes customers into segments like "VIP Regulars", "Occasional Visitors", and "At-Risk" based on configurable thresholds.

This enables targeted campaigns instead of mass messaging. Owners can create campaigns for specific segments, increasing relevance and engagement rates.

The marketing module allows owners to launch promotions, announce events, and reward loyal customers without relying on third-party marketing tools. I integrated with email service providers (SendGrid) and SMS providers (Twilio) through Supabase Edge Functions.

Campaigns can be scheduled in advance and targeted to specific customer segments. The system tracks open rates and engagement, providing feedback on campaign effectiveness.

The analytics dashboard includes revenue forecasting, customer behavior analysis, and performance reporting. The forecasting uses historical data to predict future revenue, helping owners plan staffing needs, inventory purchases, and marketing spend.

I built the analytics using materialized views that aggregate data hourly, balancing query performance with data freshness. The dashboard uses Recharts for visualizations, but I optimized data fetching to only load what's needed for the visible time range.

3. Customer Panel

The customer panel displays loyalty points in real-time, tracks tier progression, and explains how rewards are earned. This creates trust and clear incentives to return, shifting loyalty from passive stamp cards to active digital engagement.

I implemented the loyalty system with configurable point rules (points per visit, points per dollar spent, bonus multipliers) stored in the database. Points are calculated server-side to prevent manipulation, and all point transactions are logged for audit purposes.

Customers can view their visit history, earned rewards, and active promotions. This shifts loyalty from passive to active engagement, giving customers a reason to return to the app and interact with the venue.

The visit history includes dates, spending amounts, and items purchased. I optimized this view to load incrementally, showing recent visits first and loading older history on demand.

The customer panel includes in-app feedback collection and Google Reviews integration. Customers can leave feedback directly in the app, and the system can prompt satisfied customers to leave Google Reviews, improving online visibility.

I built the feedback system with moderation capabilities, allowing owners to review feedback before it's displayed publicly. The Google Reviews integration uses the Google My Business API to track review status and send follow-up prompts.

Technical Implementation Details

Real-Time Updates

I implemented real-time updates using Supabase's real-time subscriptions, which use PostgreSQL's replication slots. When a staff member creates a booking, all other devices receive the update instantly, preventing double bookings.

I built a custom React hook that manages Supabase channel subscriptions, automatically reconnecting on network issues and cleaning up subscriptions when components unmount. This was crucial because bar staff move around, and tablets can lose WiFi connection temporarily.

The real-time system broadcasts events for booking creation, updates, and cancellations. Each event includes minimal data - just enough to update the UI without overwhelming the connection. I also implemented optimistic updates on the client side, so the UI feels instant even if the network is slow.

Booking System

The booking system handles table reservations, event bookings, and walk-ins. The core challenge was preventing double bookings while allowing multiple staff members to work simultaneously.

I solved this with database-level constraints and optimistic locking. When a staff member selects a time slot, the system creates a temporary reservation record with a 30-second expiration. This prevents other staff from booking the same slot while the first staff member completes the booking.

I used PostgreSQL transactions with appropriate isolation levels to ensure atomicity, and implemented retry logic for cases where two staff members attempt to book the same slot simultaneously.

Data Consistency

With real-time updates and multiple concurrent users, maintaining data consistency was crucial. I used Supabase's row-level security policies to ensure staff only see data they're authorized to access, and implemented conflict resolution for edge cases.

The conflict resolution strategy uses version fields to detect concurrent modifications. For critical operations like booking confirmations, if a conflict is detected, the system prompts staff to refresh and shows the current state.

Performance Optimization

During peak hours, multiple staff members use the system simultaneously. I optimized the most critical queries - customer lookups and booking availability checks - to use indexes effectively. These queries complete in under 100ms to maintain a responsive feel on tablets.

I also implemented connection pooling at the Supabase level and added request queuing for database writes to prevent connection exhaustion. The tablet-first design uses large touch targets (minimum 44x44px) and prioritizes the most common actions.

Value Delivered to Each Stakeholder

For Owners

For owners, the system provides one platform instead of multiple tools, eliminating the need for separate POS, scheduling, and marketing systems. It offers clear visibility into operations with a real-time dashboard showing what's happening in the business. Analytics are presented as actionable insights, not raw data, driving decisions. The system increases customer retention through better customer recognition and personalized service.

For employees, the system provides clear schedules and responsibilities with no confusion about shifts or duties. It offers faster access to customer context with instant recognition of regulars and their preferences. It reduces manual work and confusion through automated inventory alerts and real-time booking updates.

For customers, the system provides transparent loyalty rewards where they can see points and tier progression in real-time. It offers personalized engagement where the system remembers preferences and special occasions. It provides a modern, digital bar experience, shifting from paper loyalty cards to digital engagement.

Why This Project Matters

Although developed as part of an academic thesis, UBCRM was built with real operational pain points, production-grade architecture, and scalability and maintainability in mind.

It demonstrates:

It demonstrates product thinking beyond code, understanding actual business needs, not just technical requirements. It shows the ability to translate real-world business problems into software, moving from bar management experience to software solution. It demonstrates strong full-stack fundamentals using modern web technologies like Next.js, TypeScript, Supabase, and proper database design.

The system shows how lightweight, well-designed systems can deliver ERP-level value to small hospitality businesses without enterprise complexity. It's a strong example of digital transformation for SMEs and CRM systems tailored to niche industries, built with real operational experience.

Technical Stack

The frontend was built with Next.js 14 (App Router), TypeScript, Tailwind CSS, shadcn/ui, and Lucide React. The backend and infrastructure used Supabase (PostgreSQL, Authentication, Real-time, Storage), Row-Level Security (RLS) policies, and Supabase Edge Functions. For development and quality, I used ESLint and Jest with React Testing Library. The system is deployed on Vercel for the frontend and Supabase Cloud for the backend.

Key Takeaways

UBCRM demonstrates how lightweight, well-designed systems can deliver ERP-level value to small hospitality businesses without enterprise complexity. The two-panel architecture serves different user needs while sharing the same data model, and the real-time updates transform how staff coordinate during busy periods.

Building software grounded in real operational experience - managing a bar myself - gave me insights that wouldn't have come from requirements documents alone. Understanding the actual workflows, not just the theoretical requirements, was crucial for building something that staff would actually use.

The technical decisions - real-time subscriptions, database-level security, optimized queries, and tablet-first design - all came from understanding the constraints of the environment. Peak hours, unreliable WiFi, limited screen space, and split attention aren't abstract requirements; they're daily realities that the system needed to handle gracefully.