PROJECT CASE STUDY · FULL-STACK WEB APPLICATION
Hotel Information & Table Booking System
A hospitality website concept for the hotel in Tribeni, Nepal. It brings the guest-facing hotel story, photo gallery, menu, Google reviews, visit information and table-request journey into one responsive experience, with a separate owner area for managing reservations and key site content.
Project at a glance
- Project
- the hotel website and table-booking experience
- Location represented
- Triveni Road, Tribeni, Nepal
- Application type
- Responsive, full-stack hospitality web application
- Frontend
- React 19, TypeScript and TanStack Start
- Routing and data loading
- TanStack Router and TanStack Query
- Database
- Supabase-managed PostgreSQL, using UUID records, JSONB settings, constraints, triggers and Row Level Security
- Authentication
- Supabase Auth with email/password sign-in and an admin/user role model
- Database change history
- Three checked-in SQL migrations under Drizzle migrations
- Styling and UI
- Tailwind CSS 4, Radix UI components and custom styling
- Public demonstration
- patrickdev.lovable.app
- Source availability
- Private repository; source code intentionally not linked
- Project status
- Live demonstration available; operational readiness items remain to be verified
The brief and the problem
Guests often need to search across separate channels to understand a hotel or restaurant: what the place offers, what is on the menu, where it is, when it is open, what other visitors say, and how to reserve a table. This project brings those discovery steps together in one place and gives the venue a central interface for handling incoming table requests and updating important public information.
The experience is shaped around two audiences: guests who need a simple, trustworthy way to plan a visit, and the owner who needs a manageable way to review reservations and keep the website information current.
Guest-facing experience
Hospitality-led landing page
A full-screen photographic hero introduces the hotel, its restaurant and stay offering, location context and primary actions to book a table or explore guest reviews.
Hotel story and highlights
A story section presents the venue description and supporting highlights such as Google rating, review count, menu-item count and the number of operating days returned by the site's settings.
Photo gallery
Google Place photos can populate a responsive gallery. Guests can open images in a full-screen lightbox, move between images with the keyboard and close the view with Escape.
Editable menu presentation
Menu items are grouped by category and show a dish name, short description and displayed price. The page builds its category navigation from the menu data.
Google review integration
The reviews area presents the Google rating, available review excerpts and reviewer attribution, and links visitors to Google to write a review rather than collecting a separate on-site review.
Visit information
The visit section presents the venue address, configured opening hours, directions and a tap-to-call phone action. An embedded Google map is shown when its browser key is configured.
Table reservation journey
The booking interface is designed as a request flow rather than an instant, automatically confirmed reservation. It collects the information the venue needs to contact the guest and confirm the table.
- Choose a date. A calendar disables dates in the past and days marked as closed in the configured weekly hours.
- Choose a time. Available-looking time choices are generated in 30-minute increments from the configured opening time, stopping one hour before closing.
- Set the party size. The guest can select between 1 and 20 guests in the current form.
- Enter contact details. The form requires the guest's name and telephone number.
- Add a note if needed. An optional short note lets the guest share additional context with the venue.
- Submit a request. The application writes a pending booking record to the bookings table and displays a request acknowledgement when the database accepts it.
- Confirm with the guest. The interface tells the guest that the venue will call to confirm; a successful submission is not itself a confirmed table.
The time choices are calculated from opening hours. They are not a live inventory engine: the current client-side slot generator does not remove times that already have another booking. The owner must review requests and confirm availability before treating a reservation as final.
Owner dashboard and content management
A separate /owner route provides a sign-in screen and a dashboard designed for the venue operator. The current dashboard is organized into three working areas rather than exposing a large, complicated administration interface.
Bookings
View upcoming requests or switch to all bookings, search by guest name or phone, inspect the date, time, party size and note, then mark a request confirmed or cancelled, or delete it.
Menu and prices
Add a menu entry, edit its category, name, description, price and display order, save changes, or remove an item. Public menu content is read from the same data.
Opening hours and venue information
Edit each day's opening and closing times, mark a day closed, update the contact phone number and edit the venue story displayed on the public page.
Owner account
The account area supports updating the owner's username and email, and changing the password. Email changes require confirmation through the account email flow.
The dashboard includes controls for these workflows, but owner-account provisioning and a complete signed-in owner acceptance test are still outstanding. The case study does not represent those steps as verified production operations.
Data model and application architecture
The application uses TanStack Start for the full-stack React application structure. TanStack Router defines the public home route and owner route. TanStack Query loads site settings and Google Place data, while server functions keep the site-data and Google Places requests on the server side.
The main data records are separated by responsibility:
- site_settings: the venue phone number, story text and weekly opening-hours configuration.
- menu_items: menu category, dish name, description, price and sort order.
- bookings: guest name, phone, booking date, requested time, guest count, note, status and creation timestamp.
- user_roles: owner/admin role assignments used to authorize dashboard operations.
- profiles: the signed-in account's editable username and profile timestamp.
- guest_reviews: a separate local review table with reviewer name, 1–5 rating, review body and timestamp. The current public reviews presentation is driven by Google Places; this local table is present in the database schema but is not the primary displayed review source.
PostgreSQL migrations define the tables, constraints, role helper and access policies. Row Level Security allows public visitors to read the public site settings and menu and submit a pending booking request, while owner-only policies protect booking records and management actions. The owner role is checked through the authenticated user's role record; a public username alias is not a substitute for password authentication.
Database technology and schema — PostgreSQL via Supabase
The persistence layer is PostgreSQL, provided as part of Supabase. Supabase is used for the hosted database, authentication and the generated, typed JavaScript client. The application does not use MySQL or SQL Server for this project. The checked-in database change history is stored as SQL migration files in drizzle/migrations; Drizzle Kit is included as migration tooling. The application accesses the data through Supabase's PostgREST client rather than using Drizzle ORM as its day-to-day query layer.
The schema uses PostgreSQL-native features where they are useful: UUID primary keys, foreign keys to Supabase Auth users, JSONB for weekly opening hours, timestamptz for audit timestamps, a PostgreSQL role enum, check constraints, database triggers and Row Level Security (RLS).
bookings
UUID id; guest name (1–80 characters); phone (5–30 characters); booking_date (PostgreSQL date); booking_time (HH:MM text); guests (database constraint 1–30); optional note (maximum 300 characters); status (pending, confirmed or cancelled); and created_at (timestamptz). New rows default to pending.
menu_items
UUID id; category; dish name; description; displayed price stored as text; integer sort order; and created_at. Public pages read these records, while administrators can add, edit, reorder and delete them.
site_settings
A single settings row (id is constrained to 1) stores the contact phone, venue story, weekly hours as JSONB and updated_at. This keeps public information editable without changing the application code.
user_roles
UUID id, user_id linked to auth.users with cascade deletion, and an app_role enum containing admin and user. A uniqueness rule prevents the same user from receiving the same role twice.
profiles
The profile id is also a foreign key to auth.users. It stores the username and updated_at timestamp. A database trigger creates a profile row when an Auth account is created, and users can read or update only their own profile.
guest_reviews
UUID id, reviewer name (1–60 characters), integer rating (1–5), review body (1–500 characters) and created_at. This table exists in the SQL schema; the current visible review section instead uses Google Places review data.
Database-side booking validation: a trigger rejects newly inserted bookings whose date is earlier than the current date in the Asia/Kathmandu time zone. Check constraints also validate the booking time format, guest count, name, phone, note length and allowed status values. These checks protect the database even if a client bypasses the form's normal validation.
Authentication, authorization and data protection
Sign-in is handled by Supabase Auth using username-to-login-email mapping in the owner form and Supabase email/password authentication underneath. The dashboard checks the signed-in user's role through the PostgreSQL has_rolefunction before rendering owner controls. The role model is an enum with admin and user values, and role records are associated with the authenticated Supabase user ID.
- Public reads: anonymous and authenticated visitors can read the public site settings, menu items and guest-review rows.
- Public booking requests: anonymous and authenticated visitors may insert a booking only when its initial status is pending. Public clients are not granted booking read, update or delete permissions.
- Owner booking access: authenticated administrators can read bookings, update their status and delete records. These operations are additionally guarded by RLS policies.
- Owner content access: menu changes and site-settings updates require the admin role; public visitors receive read-only access to these records.
- Profile isolation: profile read, insert and update policies compare the row id with auth.uid(), keeping each account's profile scoped to itself.
- Server credentials: a separate server-only Supabase admin client reads the service-role key from the server environment. Its code comments explicitly warn that this key bypasses RLS and must never be exposed to browser code.
The SQL also creates helper and account triggers: a role-check function, an initial owner-assignment trigger and a profile-creation trigger. Initial owner provisioning must therefore be handled carefully: verify the intended owner account and role assignment before opening the dashboard to real business use. Secrets and service-role keys are environment configuration, not values that belong in the public portfolio.
The guest_reviews table currently grants public insert access with field-length and rating constraints, and allows the administrator to delete entries. If a public customer-review submission UI is enabled later, it should also receive moderation, abuse prevention and rate limiting before being used in production.
Database migrations and data lifecycle
- Initial migration: creates guest_reviews, the app_role enum, user_roles, the has_role function and the initial owner-assignment trigger.
- Owner dashboard and booking migration: creates site_settings, menu_items and bookings; adds public-read and owner-management policies; inserts editable starter venue settings and menu rows; and adds the booking-date validation trigger.
- Owner profiles migration: creates profiles, adds per-user RLS policies, installs the profile-creation trigger and backfills profiles for existing Auth users.
These migration files document the intended schema and setup sequence in the source project. Their presence in Git does not, by itself, prove that every migration has been applied to the currently connected hosted database. A production handover should compare the live Supabase schema with the migration history and verify backups before making changes.
Application data flow and server boundaries
- Public site data: a TanStack Start server function reads the single site_settings row and ordered menu_items from Supabase, then returns a typed data object to the React page.
- Google data: a separate server function calls the Google Places integration. Connector credentials remain server-side; the returned place information is cached for up to one hour.
- Booking creation: the browser submits a booking row through the Supabase client. The database supplies the UUID, pending status and creation timestamp, then applies RLS and database constraints.
- Owner operations: the authenticated dashboard reads and changes bookings, menu items and site settings through Supabase. RLS is the database-level authorization boundary, not just a hidden frontend tab.
- Typed data contract: the generated Supabase TypeScript Database type describes the public tables, insert/update shapes, role enum and has_role function to reduce mismatched field usage in the application.
Google Places and external data
The site requests the venue's Google Place details, including formatted address, rating, review count, Google Maps link, available review excerpts and photo references. The server caches the returned place data for up to one hour to reduce repeated external requests. The gallery requests up to six available photos.
If the required Google connector credentials are not configured or the external request fails, the server returns a limited fallback identity and an availability error rather than inventing review or photo data. Google review availability is constrained by the data Google makes available to the configured integration; access to a larger review set requires the appropriate Google Business Profile connection.
Visual design and interaction details
- A Nepali-inspired visual identity, including a custom flag mark, prayer-flag bunting and mandala-style section dividers.
- A hospitality-focused palette with deep secondary surfaces, warm gold accents and high-contrast calls to action.
- Cormorant Garamond display typography paired with Karla for readable interface text.
- Responsive layouts for the hero, story, gallery, menu, reviews, booking form and visit information.
- Subtle entrance reveals, animated counters, image zoom and a desktop-only card tilt effect.
- Keyboard support for the photo lightbox, visible focus styles in form controls and semantic labels for booking inputs.
- Clear booking submission, loading and error feedback, plus an explicit message that the venue will call to confirm.
Technology stack
The implementation combines a typed React application, server-side data functions, managed authentication and a PostgreSQL data store. The interface uses utility-first styling and accessible component primitives; date-fns handles date formatting and the booking time calculations are kept in a small, testable helper.
Engineering decisions and trade-offs
- Request before confirmation: guests can send a booking request without being shown a false promise that a table is already reserved.
- Owner-managed content: opening hours, menu information and the venue story are stored as editable site data instead of being hard-coded into the public page.
- Role-based access: owner operations are protected with Supabase authentication, an admin role and database-level policies.
- External review attribution: guests are directed to Google for review submission, keeping public reviews tied to the external source.
- Graceful external-data fallback: the page can still return a limited venue identity when Google data is unavailable, rather than fabricating ratings or reviews.
- Operational caution: the interface separates submitted requests from confirmed bookings and leaves the final confirmation to the venue.
Current limitations and production-readiness checklist
The public demonstration is available, but a live preview should not be confused with a fully verified production handover. The following items remain important before relying on the system for day-to-day restaurant operations:
- Have the venue owner confirm the real opening hours, menu names, descriptions, prices and story text. Initial database entries are editable starter content, not a substitute for owner approval.
- Complete owner account creation and verify sign-in, role assignment, account updates and all dashboard actions using the authorized owner account.
- Test booking submission, owner review, confirmation, cancellation and deletion against the intended production database. The form currently offers 1–20 guests, while the database constraint accepts 1–30; keep these limits aligned if the UI is changed.
- Verify the first-owner assignment process and ensure the intended owner is the only account with the admin role before public operation.
- Add moderation and abuse protection before exposing the guest_reviews insert policy through a public review form.
- Add a true availability check if the business needs to prevent two requests from being accepted for the same table or time. The current time list is generated from opening hours only.
- Confirm Google Places and Maps credentials, photo attribution, review availability and fallback behavior in the deployed environment.
- Verify database migrations, backups, access policies, environment variables, monitoring and deployment recovery before a production handover.
- Use owner-approved real operating information and imagery; do not treat seeded values or sample content as confirmed business facts.
What this project demonstrates
- Planning a multi-section, responsive website around a real hospitality use case.
- Building a typed React application with route-level data loading and server functions.
- Designing a guest booking journey with date selection, time-slot generation, validation and submission feedback.
- Connecting a web interface to Supabase authentication, PostgreSQL records and row-level access policies.
- Creating owner-facing workflows for booking review and public content management.
- Integrating third-party place information while handling unavailable external data carefully.
- Documenting the boundary between implemented interface behavior and work that still requires real-world verification.