● Full-Stack Case Study

Retail Leasing & Operations
Intelligence Dashboard

A production analytics platform that turns raw spreadsheet uploads into interactive leasing, sales, trading-density and receivables intelligence — all behind role-based access control.

Next.js 16React 19TypeScript Prisma + SQLiteRechartsTailwind v4JWT Auth

Confidential Retail Client · Commercial Retail Project — all names & figures anonymized

app.retailiq — Executive Overview
Executive analytics dashboard with KPI cards, a sales and footfall trend line, a category split donut and a top-brands ranking.
Industry
Retail · Real Estate
Project Type
Analytics Dashboard
Stack
Next.js · Prisma
My Role
Full-Stack (End-to-End)
Status
Live in Production
The Brief

From spreadsheet chaos to one source of truth

A large multi-tenant retail property ran almost entirely on spreadsheets — daily sales exports, monthly tenant reports, lease masters, rent ledgers and footfall counts — each in a different format and re-keyed by hand into ad-hoc reports that changed depending on who compiled them.

Business Problem

No single, trustworthy view of performance. Answering “how are we doing this month?” meant reconciling several spreadsheets manually, lease economics were buried in free-text clauses, and sensitive data had no access controls.

The Solution

A full-stack platform that ingests those same spreadsheets through a guided pipeline, reconciles the leasing domain in code, and renders a secure suite of interactive, exportable dashboards refreshed by a single upload.

Product Tour

Every section, end to end

Eleven modules span the full leasing-and-operations workflow — from data ingestion to executive reporting. Each screen below is an anonymized recreation: placeholder brands, fictional figures, zero client-identifying content.

Executive overview dashboard with KPIs, a sales and footfall trend, a category donut and a top-brands ranking.
01 · Executive Overview

A single-screen state of the property

Headline KPIs, trend, category split and a top-brands ranking — all responsive to two global slicers that filter every widget at once.

The landing screen stitches together the crux of every accessible module. Unbuilt modules appear as clearly-labeled coverage tiles, so the page doubles as a live map of the whole system. Clicking a company in the ranking refocuses the KPIs onto that entity.

Business Problem

Leadership had nowhere to see overall performance without manually reconciling several spreadsheets.

Solution

A server-rendered summary pulling pre-aggregated, cached data from every section through one filterable view.

Features
  • Global Unit + Brand slicers
  • KPI cards with period deltas
  • Sales & footfall trend line
  • Category split + brand ranking
  • Module-coverage tiles
  • Click-to-focus on a company
My Role

Designed the information architecture and built the aggregation loaders and global slicer state.

Challenge

Composing one fast, permission-aware screen from many independently-cached sections.

Outcome

A single authoritative view that replaced multi-spreadsheet consolidation as the daily starting point.

Next.js RSCRechartsPrisma cacheTailwind
Dashboard UXData aggregationInformation design
Sales analytics with a year-over-year line chart, company split donut and a sortable brand performance table.
02 · Sales Analytics

Mall-wide and per-brand sales, sliced any way

Year-over-year comparison, category and company splits, brand rankings and a sortable prev-vs-current table with cascading filters and one-click export.

Monthly sales and footfall become YoY trends and a sortable table (previous vs current, absolute difference, % change, MoM). A cascading filter bar — company, category, brand, unit type, floor, F&B / non-F&B — narrows the entire view, and any result set exports to styled Excel or branded PDF that matches the screen one-to-one.

Business Problem

Performance lived in monthly exports that were hard to compare across periods or slice by attribute.

Solution

A dedicated analytics view over SQL-aggregated data with a cascading filter engine and matching exports.

Features
  • Year-over-year trend + MoM KPIs
  • Category & company donuts
  • Brand ranking bars
  • Sortable prev-vs-current table
  • Cascading dependent filters
  • Styled Excel + PDF export
My Role

Built the filter-cascade engine, aggregation queries, sortable table and shared export layer.

Challenge

Keeping filter options coherent as selections cascade, and exports identical to the filtered view.

Outcome

Leasing could instantly rank tenants and hand finance an export that matched the dashboard exactly.

SheetJSjsPDFSQL aggregationRecharts
Complex filtering UXData vizReport generation
Trading-density occupancy-cost bubble scatter plotting monthly density against carpet area, each bubble a brand.
03 · Trading Density & Occupancy Cost

Which units actually earn their square footage

Sales-per-square-foot analytics with an area-vs-density scatter and occupancy-cost-ratio risk banding that flags under-performing units automatically.

Trading density (₹/sq ft) is rendered as an area-vs-density bubble scatter with outlier caps, layered with an Occupancy Cost Ratio analysis — (rent + CAM + promo) ÷ sales — bucketed into healthy, watch and risk. Where carpet area is missing, it is inferred from sales ÷ trading density so no unit drops out.

Business Problem

No easy way to see genuine productivity per square foot, or which tenants paid unsustainable rent vs sales.

Solution

A density module with an area-derivation fallback and automatic OCR risk classification.

Features
  • Area-vs-density bubble scatter
  • Outlier capping
  • OCR bands: healthy / watch / risk
  • Quartile & median KPIs
  • Grouped sortable per-unit table
  • Excel + PDF export
My Role

Implemented density & OCR maths, the area-derivation fallback, scatter and risk-banding logic.

Challenge

Missing carpet-area data and extreme outliers distorting the scatter.

Outcome

An at-a-glance productivity and risk view for renew / renegotiate / re-lease decisions.

Recharts scatterPrismaDerived metrics
Domain metric modelingStatistical vizDecision-support UX
Commercials MG-to-actual reconciliation bars bridging minimum-guarantee floor, revenue-share uplift and actual billed rent.
04 · Commercials & Lease Economics

How rent really works, modeled in code

Minimum guarantee, revenue share, escalations, CAM and group-retention accounting — headlined by an MG-buildup waterfall.

Expected rent is computed per unit as max(MG × area, revenue-share % × sales), visualized as a minimum-guarantee waterfall (base + escalations → billed) and a group-company billing split. Group-retention logic applies ownership shares to show what the group actually keeps, excluding placeholder / non-billing entities.

Business Problem

Lease economics lived in a spreadsheet of free-text clauses, and “billed” ≠ what the group actually retained.

Solution

A calculation engine that reconstructs MG, picks MG vs revenue-share correctly, and adds retention accounting.

Features
  • MG-buildup waterfall
  • Group-company billing split
  • MG vs revenue-share selection
  • Group-retention accounting
  • Per-brand lease-terms table
  • Excel + PDF export
My Role

Modeled the lease economics, the shared expected-rent formula, the waterfall and retention logic.

Challenge

Applying joint-ownership shares without double-counting while staying reconcilable with other sections.

Outcome

A single, defensible view of contracted vs billed vs retained rent.

Custom RechartsPrismaShared rent module
Financial modelingCustom chartsReconciliation logic
Collections receivables-aging bar chart showing outstanding balances by aging bucket in crores.
05 · Collections & Receivables

Aging and billing variance, reconciled to the lease

Billed-vs-collected rent tracking with aging buckets and lease-term reconciliation that flags under- and over-billing.

Reads the monthly rent ledger for billed / collected / outstanding / collection %, with aging buckets (0–30 / 31–60 / 61–90 / 90+ days). It reconciles actual billed against expected rent using the exact same formula as Commercials, so the two always agree. Aging and outstanding are computed at read time, never stored, so they can’t drift.

Business Problem

Receivables were tracked in isolation from lease terms, so billing errors and aging risk went unnoticed.

Solution

A receivables view pairing aging analysis with automatic billed-vs-expected reconciliation.

Features
  • Billed / collected / outstanding KPIs
  • Aging buckets (0–90+ days)
  • Billed-vs-expected reconciliation
  • Variance flags
  • Per-tenant outstanding table
  • Excel export
My Role

Built the aging computation, reconciliation against lease terms, and the receivables UI.

Challenge

Guaranteeing the reconciliation matched Commercials exactly.

Outcome

Finance sees aging risk and billing variance in one place, and acts before receivables age into losses.

PrismaShared expected-rentRead-time metrics
Financial reconciliationRisk analytics
Brand report with a minimum-guarantee reconstruction line chart, a statement summary panel and a monthly financial-year statement table.
06 · Brand Report & Statement Generator

A tenant statement, generated on demand

Parses free-text lease escalation clauses into a schedule, reconstructs minimum guarantee over time, and exports a branded statement.

A parser turns clauses like “Every 3 year” or “Every year 5%” into a concrete escalation schedule, reconstructs MG over the lease term, and reconciles it against actual sales month by month to decide whether each period bills on MG or revenue share. The result exports to Excel and branded PDF in financial-year (Apr→Mar) or calendar order.

Business Problem

Producing a per-tenant statement meant manually interpreting clauses and re-deriving MG for every month.

Solution

A clause parser + statement engine automating MG reconstruction and basis selection, with one-click export.

Features
  • Free-text clause parsing
  • MG reconstruction over the term
  • Monthly MG-vs-revenue-share basis
  • FY or calendar ordering
  • Branded Excel + PDF statement
My Role

Wrote the escalation-clause parser, MG-reconstruction logic and branded exporter.

Challenge

Turning inconsistent human-written clauses into a reliable schedule; rendering ₹ in core-font PDFs.

Outcome

A statement that once took manual effort per brand is now generated consistently on demand.

Custom parserSheetJSjsPDF + autotable
Text parsingSchedule modelingDocument generation
Sales comparison screen with two parsed file cards and two tables of top movers by percentage change and by rupee difference.
07 · Sales Comparison Tool

Diff two sales files — entirely in the browser

An ad-hoc tool to compare two sales spreadsheets and surface top movers, with the files never leaving the browser.

Drop two workbooks; it fuzzily detects the relevant columns, matches rows by brand, and computes top movers by percentage and by absolute value, plus new and dropped brands. All processing is in-browser — nothing is uploaded — making it safe for quick, unofficial “what changed?” checks.

Business Problem

Teams needed fast, throwaway comparisons without formally importing files or exposing raw data to a server.

Solution

A client-side comparison engine with fuzzy column detection and mover analysis, running entirely in-browser.

Features
  • Two-file drag-and-drop
  • Fuzzy column detection
  • Top movers by % and by ₹
  • New / dropped brand detection
  • 100% client-side — no upload
My Role

Built the in-browser parsing, fuzzy matching and mover-analysis UI.

Challenge

Matching columns across differently-structured files while staying fast and private.

Outcome

A zero-friction, privacy-safe tool for period-over-period sense checks.

Client-side SheetJSTypeScriptFuzzy matching
Client-side processingPrivacy-conscious design
Data upload center with an Excel drop zone, a five-step import pipeline diagram and an upload log table.
08 · Data Upload & Import Center

Resilient ingestion of imperfect spreadsheets

A guided, multi-module Excel import pipeline with fuzzy column detection, timezone-safe date parsing and a full audit log.

Accepts Excel for seven module types — daily sales, monthly reports, brand master, commercials, collections, parking, footfall — then parses, detects columns by hint, normalizes dates safely, validates, persists and invalidates caches. Every upload is logged, and the file is retained on disk as the source of truth; deleting an upload cascades away the rows it created.

Business Problem

The business would keep producing Excel, so the platform had to ingest imperfect, human-authored files reliably.

Solution

A pipeline that treats the uploaded file as truth, tolerates format variation, and gives traceable feedback.

Features
  • Seven-module Excel import
  • Fuzzy header detection
  • Timezone-safe date parsing
  • Row validation + skip logic
  • Upload audit log
  • On-disk retention + cascade delete
My Role

Designed and built the entire import pipeline, date parser and log-and-retain model.

Challenge

Mixed date formats caused off-by-one-day storage bugs.

Outcome

Non-technical staff refresh the whole platform by dropping a spreadsheet, with full traceability.

Route handlersSheetJSOn-disk storagePrisma
Robust ingestionError handlingAuditability
Master data management screen showing an editable grid of brands with category, company, floor, area, MG, revenue share, CAM and escalation columns.
09 · Master Data Management

The clean backbone the analytics depend on

An editable grid for brand attributes and lease terms, plus brand cleanup / dedupe and per-brand document management.

Maintains category, company, floor, area, MG, revenue share, CAM, promo, threshold and escalation terms inline. A cleanup tool renames and merges duplicate brand records (with sales-row counts), and per-brand LOI / agreement PDFs can be uploaded and viewed in-app, governed by separate view/download permissions.

Business Problem

Masters were scattered and duplicated across spreadsheets, with no controlled way to edit or clean them.

Solution

A single authoritative master surface with inline editing, dedupe and attached documents.

Features
  • Editable brand & commercials grid
  • Rename + duplicate merge
  • Per-brand document upload/viewer
  • Search & attribute filters
  • View/download-split permissions
My Role

Built the editable grid, dedupe/merge logic and the document-management layer.

Challenge

Merging duplicate brands without losing linked sales history.

Outcome

Clean, controlled master data that keeps every downstream analytic accurate.

Server actionsPrismaOn-disk docs
Admin/CRUD UXData deduplicationDocument management
Administration screen with a users table by role and a permissions panel of per-section and per-capability toggle switches.
10 · Administration & Access Control

Governed access for a multi-role team

User management with roles and fine-grained per-section permissions, plus split capabilities for uploads and document view vs download.

Users get a role (Admin / Leasing / Operations) and per-section permissions that tailor their sidebar and data access. On top sit split capability flags — uploads, document view and document download are independently grantable — all under a custom auth stack: JWT sessions in an HTTP-only cookie, bcrypt hashing, edge middleware and email-OTP reset.

Business Problem

Roles needed genuinely different access, and sensitive documents needed view vs download controlled separately.

Solution

A compact permission model (role + section + capability flags) enforced by guards on every protected route.

Features
  • Roles: Admin / Leasing / Operations
  • Per-section access toggles
  • Split capabilities
  • JWT + bcrypt + edge middleware
  • Email-OTP password reset
My Role

Designed and built the auth system, the permission model and the admin UI.

Challenge

Reconciling a shared, user-agnostic cache with strict per-user authorization.

Outcome

Auditable access across the team, with documents protected at the right granularity.

jose (JWT)bcryptjsEdge middlewarenodemailer
AuthenticationRBAC designSecurity engineering
11 · Operational Modules — Roadmap

Architected to grow into a full operations cockpit

Provisioned-but-forthcoming modules — F&B audits, fire-safety audits, electricity and parking — show the platform is built to extend.

The data model already provisions these operational modules, and they appear in the navigation as clearly-labeled “coming soon” sections with described intent. Because the schema and routing already accommodate them, each can be built out without re-architecting the system — a low-risk path from leasing analytics to full operations coverage.

My Role

Designed the extensible schema and navigation so future modules slot in cleanly.

Challenge

Balancing present scope against future extensibility without over-building.

Outcome

A clear, low-risk growth path from leasing intelligence to a complete operations platform.

Prisma schemaNext.js routingShared design system
Systems architectureForward-compatible designRoadmapping
Data Visualizations

Every section, every chart type

Each analytics module renders its own family of visualizations from the same governed data. The images below are captured straight from the running application — multi-year trend lines, occupancy-cost bubble scatters, category and ownership donuts, ranking and weighted bar charts, stacked group-company billing, MG-to-actual reconciliation, receivables aging, and dense sortable report tables.

Trading Density

Bubble scatter plot of monthly trading density against carpet area, each bubble a brand sized by turnover.
Occupancy-cost bubble scatter — trading density vs carpet area, per brand.
Multi-year line chart of average trading density per square foot per month, one line per year.
Multi-year trading-density trend, one line per year.
Five-segment donut of trading-area share by floor with percentage labels.
Trading-area share by floor — five-segment donut.
Horizontal bar chart of area-weighted trading density by floor in rupees per square foot per month.
Area-weighted trading density by floor.
Horizontal bar chart of area-weighted trading density by retail category.
Area-weighted trading density by category.
Horizontal bar chart of carpet trading area in square feet by floor.
Mall carpet (trading) area by floor.

Commercials — leasing & billing

Bar chart bridging MG floor to revenue-share uplift to actual billed rent in crores.
MG floor → revenue-share uplift → actual billed reconciliation.
Stacked horizontal bars of group-company billing split into minimum guarantee and revenue share.
Group-company billing — stacked MG + revenue share.
Donut showing the split of brands billed on MG-or-revenue-share versus pure revenue share.
Billing-model mix — MG-vs-revenue-share.
Horizontal bar chart of minimum-guarantee rent per square foot per month by floor.
MG rent per sq ft by floor.
Horizontal bar chart of billed rent by retail category in crores.
Billed rent by category.
Multi-year line chart of monthly billed rent in crores.
Monthly billed-rent trend.

Sales

Multi-year year-over-year sales line chart, one line per year across the calendar.
Year-over-year sales trend.
Nine-segment donut of sales split by retail category.
Sales split by category — nine-segment donut.
Horizontal ranking bar chart of top brands by sales in crores.
Top brands by sales — ranking bars.

Collections / Receivables

Horizontal bar chart of receivables aging by bucket in crores.
Receivables aging by bucket.
Horizontal ranking bar chart of top outstanding balances by tenant in crores.
Top outstanding by tenant.
Multi-segment donut reconciling billed versus expected collections.
Billed-vs-expected reconciliation donut.

Executive overview & brand report

Donut on the executive overview showing the mall-wide billing-model split.
Overview — mall-wide billing-model split.
Dense sortable brand master report table with sales, trading density, MG, revenue share, billed rent and rent-to-sales columns for every brand.
Brand master report — a dense, sortable, exportable metrics table.
How It Was Built

Process & approach

From an ambiguous, spreadsheet-driven business to a running, maintainable product.

1

Domain modeling

Modeled MG, revenue share, thresholds, CAM, escalations, occupancy cost and trading density into a normalized schema — with derived values never stored.

2

Ingest pipeline

A forgiving importer: parse, fuzzy-detect columns, normalize dates safely, validate, persist, invalidate — with the uploaded file kept as the source of truth.

3

Aggregation & performance

Monthly grouping pushed into SQL, wrapped in a tagged server cache with defined revalidation and invalidation on every mutation.

4

Analytics layer

Thin server components authorize, load cached data and render client views; one shared expected-rent module keeps three sections in lock-step.

5

UI & design system

A hand-built, theme-aware system on Tailwind: KPI cards, cascading filters, sortable tables, a preferences layer and a custom Recharts wrapper.

6

Security & delivery

Custom JWT auth with per-section permissions, shipped to a production VPS with deploy-on-push automation.

Under the Hood

Technologies used

Frontend

  • Next.js · App Router, RSC
  • React · UI components
  • TypeScript · end-to-end types
  • Tailwind CSS v4 · design system
  • Recharts · custom chart wrapper

Backend & Data

  • Prisma ORM · type-safe access
  • SQLite · PostgreSQL-portable
  • Server caching · tag-based + invalidation
  • Server actions · mutations & uploads
  • On-disk storage · guarded file paths

Security & Reporting

  • jose (JWT) · stateless sessions
  • bcryptjs · password hashing
  • nodemailer · email-OTP reset
  • SheetJS · Excel import/export
  • jsPDF + autotable · branded PDFs
The Hard Parts

Challenges & solutions

Messy, inconsistent spreadsheet inputs

Tenant-authored files with varying headers, mixed date formats and occasional bad rows.

Solution: a resilient pipeline with fuzzy column detection, a single timezone-safe date parser, row validation with skip logic, and the file retained as source of truth.

Off-by-one-day date bugs on the server

Daily dates were stored a day early because parsing used the server’s timezone.

Solution: a timezone-snap parser that pins calendar dates regardless of locale, plus a migration to repair previously-stored rows.

Keeping three sections’ numbers in agreement

MG, revenue share and expected rent appear in Commercials, Collections and Brand Report — any divergence destroys trust.

Solution: one shared expected-rent module imported verbatim into all three, so reconciliation is structural, not hoped-for.

Performance over large daily datasets

Hundreds of thousands of daily rows made client-side aggregation far too slow.

Solution: monthly aggregation pushed into SQL, results wrapped in a tagged cache with explicit invalidation on every mutation.

Caching vs per-user authorization

A shared cache is fast but risks serving data across permission boundaries.

Solution: a documented invariant — cached loaders take no user-specific args — with all authorization enforced at the page/route layer above the cache.

Encoding a real leasing domain

Escalations were free text, and group ownership meant billed ≠ retained revenue.

Solution: a clause parser that builds an escalation schedule, plus group-retention accounting that applies ownership shares and excludes placeholder entities.

Outcomes

Results & impact

Figures below are illustrative and anonymized — they describe the kind of impact delivered, not the confidential client’s actual numbers.

Days → minutes

Faster reporting

Manual multi-spreadsheet consolidation replaced by a single upload that refreshes every dashboard.

1 source

Reconciled truth

Leasing and finance read the same reconciled MG, revenue-share and expected-rent figures.

100%s of K rows

Fast at scale

Responsive dashboards over hundreds of thousands of daily rows via SQL aggregation + caching.

3 roles

Governed access

Per-section, per-capability permissions across a multi-role team, with documents protected.

Auto-flagged

Blind spots surfaced

Low-density units and at-risk / under-billed receivables surfaced automatically.

Everywhere

Exportable

Every analytical view produces styled Excel and branded PDF output.

In Closing

Conclusion

RetailIQ is a complete, production case study in turning a spreadsheet-run business into a secure, performant web application. It began as scattered Excel exports and became a single role-secured platform where one upload refreshes an entire suite of leasing, sales, trading-density, commercials and collections dashboards.

What makes it representative of my work is the coherence across the stack: a leasing domain modeled faithfully in the schema, an ingest pipeline forgiving enough for real spreadsheets, an aggregation-and-caching layer that stays fast at scale, reconciliation that guarantees three sections always tell the same story, and a hand-built design system that keeps every view exportable and consistent — all behind a custom auth and permission model, deployed to production.

It reflects how I like to build: understand the domain first, make correctness structural rather than incidental, keep the system fast and observable, and finish the job through to a running, maintainable deployment.

Have a spreadsheet problem like this?

Let's build your source of truth.

We turn scattered exports and manual reporting into secure, live dashboards — the same way we built RetailIQ. Tell us what's slowing your team down.

Book a call Chat on WhatsApp Back to Studio