CREWERA Reality

Introduction

CREWERA Reality is the most technically complex project I've built at Crewultima Technologies . It's a production-grade, full-stack real estate web platform — not a tutorial clone, not a CRUD app with a nice UI. It's a system with 120+ REST API endpoints, 30+ normalized relational database tables, a complete Role-Based Access Control (RBAC) admin panel with 15 modules, JWT authentication with refresh token rotation, Mapbox geolocation, a multi-step property creation workflow, a blog engine with comment moderation, an audit activity logger, in-app notifications, and a CSS-only analytics dashboard powered by 24 parallel SQL queries.

This document breaks down how it's built, why every major architectural decision was made, and what I learned building a platform of this scale from scratch — without a CMS, without an ORM, and without a UI framework.

Why this project exists

Real estate platforms are one of the most technically demanding categories in web development. They require complex relational data modeling (a single property spans 12+ database tables), high-performance search with 15+ simultaneous filter parameters, SEO-critical server-side rendering (property pages must be indexed by Google), secure multi-role authentication, heavy media management, geolocation, and a full internal CMS for content and lead management.

I built CREWERA Reality to prove I could architect and ship a system at that level of complexity — end to end, without a team.

Tech stack

• Next.js 15 (App Router) — Primary React framework. Handles SSR/SSG for property and blog pages, ensuring every listing is fully crawlable by Google on first request. The decision to use Next.js over plain React was entirely SEO-driven: real estate lives and dies by organic search.
• React 19 — Extensive use of useState, useEffect, useContext, and custom hooks. The entire user dashboard, admin panel, and multi-step property form are managed with React Context — no Redux, no Zustand.
• Node.js + Express.js (v4.18) — RESTful API backend. 120+ endpoints organized across domain-specific routers: /api/auth, /api/properties, /api/admin, /api/blog, /api/contact, /api/notifications.
• MySQL + mysql2 (raw SQL) — Relational database with 30+ normalized tables. Deliberately chose raw SQL over Prisma or Sequelize for full control over complex multi-table joins. The property detail query alone joins 12 tables in a single optimized statement — an ORM would generate catastrophically inefficient N+1 queries for this.
• JWT (jsonwebtoken v9) — Stateless authentication with access tokens and refresh tokens. The authMiddleware verifies the token signature on every protected route. Admin routes add a second adminMiddleware layer checking the role payload.
• bcryptjs — Passwords are hashed with 10 salt rounds before database insertion. Plain text passwords never touch the database.
• Multer — Handles multipart/form-data for property image galleries and user profile avatars. Files are saved to a structured local directory: /uploads/properties/{city}/{property_slug}/.
• Nodemailer — Outbound SMTP emails for email verification on registration, password reset links, and admin contact form alerts.
• Mapbox GL JS / Google Maps — Interactive property map with cluster views, individual property markers with click-to-view popups, and map/list toggle on the search results page.
• express-validator — Input validation middleware on every write endpoint. Registration, login, property submission, contact form — all validated before touching the database.
• Custom CSS (no Tailwind) — The entire UI is built with custom CSS variables and CSS modules. Bypassing Tailwind was a deliberate choice to build a genuine design system with full control over every visual token.

Database architecture

The database is the most architecturally significant part of CREWERA Reality. With 30+ tables and a strict normalization strategy, every domain is cleanly separated — no bloated monolithic property table.

A single property is spread across 12 tables:

properties           → core record (name, slug, type, status)
property_details     → builder, units, towers, description
property_construction → stage, completion %, possession date
property_locations   → address, city, state, latitude, longitude
property_legal       → RERA number, RERA status, title clearance, NOCs
property_pricing     → BHK-wise pricing (1BHK, 2BHK, 3BHK, carpet/super built-up/per sqft)
property_media       → image URLs, file type, is_primary, display_order
property_amenities   → many-to-many join to amenity_master (36+ amenities)
property_analytics   → view_count, inquiry_count, trending_score
property_building_specs → storeys, lifts, flooring type, balconies
property_utilities   → electricity provider, water source, gas pipeline
amenity_master       → amenity catalog with SVG icons and categories

Other key tables include users, user_profiles, user_favorites, inquiries, property_reviews, user_saved_searches, property_requests, blog_posts, blog_comments, blog_tags, contact_messages, faq_categories, faqs, testimonials, notifications, audit_activity, and leads.

Connection pooling via mysql2 ensures the database layer performs under concurrent load by reusing connections rather than establishing a new TCP handshake on every request. All queries use parameterized ? placeholders to prevent SQL injection at the driver level.

Property search engine

The search system is the technical centerpiece of the public-facing product. The GET /api/properties endpoint accepts 15+ simultaneous filter parameters and constructs a dynamic SQL WHERE clause at runtime:

// Filter parameters accepted:
property_type    → Residential / Commercial / Mixed
city, state, locality → location-based
min_price, max_price  → INR price range
bhk              → 1BHK, 2BHK, 3BHK, 4BHK+
construction_stage → Pre-Launch / Under Construction / Ready to Move
special_type     → Villa, Duplex, Penthouse, Bungalow
builder          → developer/builder name
amenity          → CSV of amenity IDs (many-to-many filter)
q                → full-text search across project name, description, builder, city
sort             → created_at, min_price, view_count, trending_score
order            → ASC / DESC
page, limit      → server-side pagination

The controller builds the WHERE clause array dynamically based on which params are present, then assembles the final SQL string with parameterized values. The full-text search uses LIKE '%keyword%' across multiple columns simultaneously. The trending algorithm weights view count, inquiry count, and favorites by recency to surface relevant properties at the top.

A separate GET /api/properties/filters endpoint returns available cities, builders, property types, and construction stages dynamically from the current database state — so the frontend filter dropdowns are always accurate with no hardcoding.

Property detail page

The property detail page is driven by a single, highly optimized SQL query that joins all 12 property tables and returns a complete data object in one round trip. This is the architectural decision that justified using raw SQL over an ORM — no ORM would generate a query like this without falling back to N+1 or multiple sequential fetches.

The page renders: image gallery with lightbox, BHK-wise pricing table, 36+ amenities with SVG icons categorized into Recreation / Fitness / Safety / Convenience, Google Maps embed, construction timeline with completion percentage, RERA and legal status, building specs, utilities, user reviews with star ratings, similar property recommendations, and a direct inquiry submission form. All dynamic meta tags, Open Graph data, and structured schema markup are generated server-side via Next.js for full SEO indexing.

Authentication and RBAC

Authentication is stateless JWT-based with a two-token pattern: a short-lived access token and a refresh token. On login, the server verifies the password against the bcrypt hash, then signs a JWT containing the user ID and role. The frontend stores the access token and attaches it as Authorization: Bearer [token] on protected API calls. A global AuthContext manages the session state across the app, auto-refreshes the token on 401 responses, and exposes isAuthenticated and isAdmin flags for frontend route guards.

The backend uses a two-layer middleware chain for admin routes: authMiddleware verifies the token signature first, then adminMiddleware checks the role payload. Non-admin users hitting admin routes receive a 404 — not a 403 — to prevent role enumeration. Every admin action is additionally logged to the audit_activity table with user ID, action type, severity level, affected entity, and IP address.

Admin dashboard and analytics

The admin panel has 15 fully functional modules accessible from a sidebar-navigated dashboard. The landing page shows live KPI cards (Active Properties, Total Users, Total Inquiries) fetched from 4 API endpoints simultaneously.

The analytics dashboard at /admin/analytics is the most technically impressive single page in the project. A single endpoint GET /api/admin/dashboard/comprehensive executes 24 parallel SQL queries — user growth timelines, inquiry funnels, price tier distributions, lead pipeline breakdowns, property distribution by city and type, top properties by view count, blog performance, and agent metrics. All 24 queries run concurrently via Promise.all() and return as a single JSON payload. The visualizations are rendered as pure CSS bar charts and progress indicators — no Chart.js dependency, keeping the admin bundle lean.

Admin property management

The property management module at /admin/properties supports full CRUD operations across all 12 property tables via a tabbed edit modal with 8 sections: Basic, Details, Construction, Location, Legal, Building Specs, Utilities, and Media. Every section has its own dedicated PUT endpoint so changes can be saved independently without overwriting unrelated data.

The multi-step property creation form at /admin/properties/submit collects data across all 8 sections before firing a single transactional payload to the backend. This ensures all 12 tables are populated atomically — if any insert fails, the entire transaction rolls back. The backend handles 40+ property-related API endpoints covering list, detail, filter options, pricing, amenities, status toggle, cascade delete, media upload, media delete, and primary image assignment.

Media management system

Multer handles all file uploads with configurable storage. Per-property directories are auto-created at /uploads/properties/{city}/{slug}/, keeping files organized and serving static assets efficiently through Express. The system supports JPEG, PNG, WebP, GIF, SVG, PDF, MP4, and WebM up to 50MB per file, with up to 20 files per upload batch.

Each property has one image designated as primary via the is_primary boolean in property_media. Display order is stored per image and supports drag-to-reorder via API. Bulk delete operations clean both the database record and the file system entry simultaneously. User profile avatars use a canvas-based 1:1 square crop modal with touch support, and a hash-based two-letter initials system (e.g., "HP") serves as the fallback avatar when no photo is uploaded.

Blog engine

The blog system supports full CRUD from the admin panel with title, slug, rich HTML content, featured image upload, tag categorization, and view count tracking. The public blog listing page renders paginated post cards with cover image, excerpt, tags, read time, and date. Individual post pages track views on load, display nested comments with reply threading, suggest related posts by tag overlap, and generate dynamic meta and Open Graph tags for SEO. Comment moderation is handled from /admin/comments with approve/reject controls.

Contact, FAQ, and notification systems

The contact form collects name, email, phone, subject, and message — validated by express-validator, stored in contact_messages, and triggers an admin email notification via Nodemailer. The FAQ system serves categorized, accordion-style answers fetched dynamically from faq_categories and faqs tables, fully manageable from the admin panel.

The in-app notification center delivers targeted messages to users and admins: inquiry status updates, property alerts, and system messages. Unread count is displayed in the header. Admins can broadcast targeted notifications from /admin/notifications. Individual and bulk mark-as-read operations are supported via PATCH endpoints.

SEO and performance

Every property and blog page generates dynamic <title>, <meta description>, Open Graph, and Twitter Card tags server-side via Next.js. Slug-based URLs (/property/luxury-tower-mumbai) are SEO-friendly and human-readable. The Next.js Image component handles WebP conversion and lazy loading. Semantic HTML with proper heading hierarchy and ARIA labels is enforced throughout. CSS-only animations keep the bundle lean, and a custom 404 page handles missing routes gracefully.

Security

Every security layer is implemented at the appropriate level of the stack. CORS is configured explicitly via the cors middleware. All write endpoints are protected by express-validator before touching any controller logic. SQL injection is prevented at the driver level with parameterized queries — not just escaped strings. Passwords use bcrypt with 10 rounds. The audit logger middleware captures every admin action with severity classification (Info, Warning, Critical, Emergency), the affected entity, IP address, and session context — giving a complete activity trail browseable at /admin/audit-logs.

Scale of the project

• 120+ REST API endpoints across 6 domain routers
• 30+ relational database tables with strict normalization
• 45+ frontend pages across the public site, user dashboard (11 modules), and admin panel (15 modules)
• 26 React components including FilterBar, PropertyCard, MapboxMap, HeroSlider, AdminSidebar, MediaUploader, CropModal, and more
• 12-table single-query joins for property detail pages
• 24 parallel SQL queries for the analytics dashboard
• 15+ search filter parameters with dynamic SQL construction
• 36+ amenities with SVG icons and category grouping
• Full audit trail on all admin actions with 4 severity levels

What I'd improve

• Redis caching — the property list and filter endpoints are queried heavily. A Redis layer in front of frequently-hit endpoints would dramatically reduce database load at scale.
• Elasticsearch for search — the current LIKE-based full-text search works but doesn't support fuzzy matching, typo tolerance, or weighted field scoring. Elasticsearch would make the search experience significantly better for end users.
• Migrate file storage to S3 — local Multer storage works in development but doesn't scale horizontally. S3 or Cloudinary would decouple media from the server and enable CDN delivery globally.
• WebSocket notifications — the current notification system is pull-based (polling). Real-time push via Socket.io would make the inbox and inquiry status updates feel instant.
• Rate limiting and throttling — the API currently has no rate limiting. Adding express-rate-limit on auth and search endpoints would protect against brute force and scraping.
• End-to-end testing — with 120+ endpoints, a Playwright or Cypress test suite would be the most valuable engineering investment for long-term maintainability.

Conclusion

CREWERA Reality is the project that taught me what production software actually looks like. Not just features — but the decisions underneath them: why you skip the ORM when your joins span 12 tables, why stateless JWT beats session cookies at scale, why you structure your database around domains instead of convenience, why you validate at the middleware layer before your business logic ever runs. Building a platform this large solo forces you to think about architecture first, because bad decisions at the foundation cost weeks to fix later. If you want to understand how I think about software systems, this project is the best answer I have.