The FAVRs — Search Small MDE Sales Tracker
The FAVRs
May 2026 – Present
The Organization
The FAVRs is an organization dedicated to supporting small and local businesses through programs, visibility, and community-driven initiatives. Search Small MDE is one of their flagship products, a structured sales and ambassador program that connects small businesses with resources and support. Operating across a bilingual (Spanish/English) team that includes sales reps in the field, community ambassadors building relationships, and management overseeing the full pipeline, The FAVRs needed infrastructure to match the scope of what Search Small MDE was becoming.
Please note: This case study reflects work completed during the active engagement. Metrics and systems reflect the platform as built and deployed during this period.
The Challenge
The entire Search Small MDE sales operation was running through a single Excel spreadsheet, one rep, one file, no real-time visibility. As the team grew to include multiple sales reps, community ambassadors, and a hybrid sales-ambassador role, the spreadsheet became a liability: managers couldn't see live pipeline data, there was no structured history of what happened to each lead, reporting required manual formula updates, and the ambassador referral pipeline had no home at all.
The goal was to replace it entirely, not patch it, with a purpose-built platform that reflected how the Search Small MDE team actually worked.
My Role
I served as the sole developer and systems architect for this engagement, responsible for every layer of the platform: database design, server infrastructure, UI/UX, authentication and role-based access, the reporting engine, and all third-party integrations. This was a greenfield build, every component was designed and built from scratch.
The Approach
The platform was built in four phases, each layering new capability onto a foundation designed for how the Search Small MDE team actually works.
Foundation & Core Pipeline
Before writing a single UI component, I designed the data model. The database schema was built around leads as the central object, each with a full status history: every stage a business ever moved through (Prospect, Contacted, Visited, Pitched, Closed Won, Closed Lost) was recorded as an individual timestamped entry, not an overwritten field. The business detail view separately tracks five milestones, Contact, Visit, Pitch, Sale, and Onboarding, representing the client's full activation lifecycle from first outreach through fully live. This gave managers a complete audit trail for every record in the pipeline. Role-based access was enforced consistently at the application layer, with every data query and every write action scoped to the requesting user's role: reps could only see their own leads; managers could see everything. Weekly and monthly reports were built as server-computed queries, replacing the manual formulas that had required someone to update the spreadsheet.
Multi-Role Architecture
The platform was built to support four distinct roles: Manager, Rep, Ambassador, and Sales Ambassador, a hybrid role combining full rep and ambassador capabilities. Each role received a purpose-built dashboard: reps track leads and submit handoff requests; ambassadors manage warm lead referrals, log events, and submit articles as required monthly deliverables; sales ambassadors see both views in one dashboard; managers get a team-level KPI overview, rep comparison chart, ambassador activity monitoring, and a warm leads panel for reviewing and assigning all incoming referrals. The handoff system always routes through management: reps cannot hand off leads directly to other reps.
Bilingual Operations & Auto-Translation
The team operates bilingually: reps and ambassadors add notes in Spanish; managers primarily work in English. I built a TranslateButton component, placed contextually on all rep-written and ambassador-written notes, that uses the Google Translate API to render an inline English translation on demand. No copy-pasting into Google Translate, no workflow interruptions. The platform UI itself is fully bilingual via next-intl, with Spanish as the default language and English as the secondary.
Historical Data & Advanced Filtering
I rebuilt the dashboard and reporting architecture to support a dashboard month picker (single-month selection via URL parameter, with a visual indicator when viewing a past month) and a multi-month report filter bar (select one, several, or all months to aggregate data across time periods). All filtering is server-side: the database query receives the month selection and returns only the relevant data, keeping the browser fast regardless of how much history exists.
Ambassador Program Module
The ambassador dashboard tracks six metrics across a dedicated KPI grid: Businesses Added, Events Attended, Articles Submitted (marked as Required), Insights Shared, Referrals Sent, and Referrals Accepted. Articles are the only metric with a Required badge, a visual indicator carried through both the ambassador's own dashboard and the manager's Ambassador Activity panel, including a red alert marker when an ambassador has submitted zero articles in the period.
Event logging captures the event name, date, location, and a required accomplishment note, so every Search Small MDE event has a documented outcome, not just a check in a box.
The Platform in Action
Weekly Report view showing pipeline metrics, visit tracking, and multi-week bar chart
7 tracked metrics per week: New Businesses, In Pipeline, Visits, Pitches, Sales, Onboarded, Conversion Rate
By the Numbers
250+
Businesses Tracked
Since launch
128
Published to Directory
Live on Search Small MDE
275
Total Directory Listings
Across the public Search Small MDE site
77
Fields Per Business
Tracked across the full lead lifecycle
547
Pipeline Status Entries
Logged by the team since launch
315
Audit Trail Actions
Logged across all users
5
Active Users
1 rep, 2 managers, 1 ambassador, 1 sales ambassador
4
User Roles
Manager, Rep, Ambassador, Sales Ambassador
2
Languages
English + Spanish (full bilingual UI)
14
Database Tables
Application schema
19
Application Pages
Role-scoped throughout
1
Spreadsheet Replaced
Entirely
Stack
Next.js · TypeScript · Supabase PostgreSQL · Supabase Storage · Vercel · Google Places API (New) · Google Translate API · Search Small MDE Ingestion API · next-intl · Tailwind CSS · Recharts · NextAuth · bcrypt
What Performed Best
The server-side filtering architecture was the highest-leverage technical decision in the build. Switching to URL-parameter-driven server rendering meant the database does the work, the browser receives only what's needed, and the URL is shareable and bookmarkable, a manager can send a direct link to a specific month's pipeline report and it opens in exactly that state. The TranslateButton component proved immediately useful in practice: placing translation on-demand rather than auto-translating everything kept the interface clean for Spanish speakers while giving English-speaking managers full context in one click.
What We Learned
- Role complexity compounds quickly. Designing shared role-check helper functions early, rather than hardcoding role checks throughout the codebase, made adding the fourth role (sales ambassador) straightforward instead of a refactor.
- Zero-data users must be explicit, not assumed. Without deliberate architecture to include all users regardless of activity, managers would see only reps who had data in a given period, a false picture of who was inactive versus who simply had no leads yet. Every team member appears in every report, always.
- Access control needs a single enforcement path. Early on, permission checks lived in individual pages and actions, which meant every new feature was a new opportunity to forget one. Consolidating writes behind shared guard functions made it possible to add read-only account states later by changing one file instead of thirty.
- Required deliverables need visual pressure. Marking articles as "Required" with a badge, and surfacing a red alert in the manager's panel when the count is zero, turned a soft expectation into a visible accountability mechanism.
- Metrics that measure the same thing twice are worse than no metric. Two dashboard figures were silently counting the same underlying data, so they always matched and neither meant anything. Naming what each number counts, in the interface itself, prevents a dashboard from quietly lying to the people depending on it.
- A page name is part of the data model. A page called "New Businesses" accumulated both newly discovered businesses and existing directory businesses added to a pipeline. The name quietly stopped matching the contents, and people misread the numbers for weeks. Renaming it and labeling each row's origin fixed a reporting problem that looked like a data problem.
What This Built
Beyond the features, this engagement produced a platform The FAVRs can grow into. The database is structured to support additional roles, new lead fields, or new report types without rebuilding core logic. The Search Small MDE ambassador program module is designed to expand: event types, article categories, and referral workflows can all be extended without touching the rest of the app. The spreadsheet is retired. The infrastructure is in place.
What Shipped After Launch
The platform launched in May 2026 as a spreadsheet replacement. Since then it has grown into the operational core of the Search Small MDE program, with each feature area below added in response to real production use.
Directory Publishing Integration
Closing a sale now publishes the business directly into the live Search Small MDE public directory through an ingestion API. A Listing Queue gives managers a review step before anything goes live, with stale data flags that surface when a listing needs to be refreshed after its underlying record changes. Sales work and the public directory used to be disconnected, requiring manual re-entry. Now they are one motion.
Automated Business Discovery
Paste a Google Maps link and the intake form auto-fills name, neighborhood, category, phone, website, and photos. A discovery script pulls candidate businesses from Google Places, filters by review count, and detects Instagram and WhatsApp presence. A manual entry path is also available for businesses not listed on Google, a deliberate inclusion for the informal small businesses this directory exists to surface.
Field Tools for Reps
Photo, video, and document uploads for site visit documentation. Nine stage-matched email templates written in both English and Spanish with copy to clipboard. Follow-up dates with overdue flagging. A built-in user guide with role-specific instructions and screenshots. The platform is mobile-responsive throughout.
Deeper Reporting
Conversion funnel visualization, per-rep comparison charts, neighborhood-level performance breakdowns, and team goal tracking against actuals. Per-rep detail pages, CSV export, and three views of the pipeline: table, cards, and Kanban board. Saved filters let a manager bookmark any combination of rep, month, and status.
Team Operations Layer
An activity feed logs every action in the system, filterable by action type with live counts. Team announcements, manager-to-rep notes, lead handoffs between reps, warm lead referrals flowing from ambassadors to reps, and notifications.
Account Lifecycle Controls
Two distinct states beyond simply deleting someone: deactivation, where the user cannot log in but all their data stays intact and attributed to them; and pause (read-only mode), where the user retains full visibility but cannot make any changes, enforced across every write path in the application rather than by hiding buttons. The distinction models real situations, such as a rep on leave who still needs to see their accounts.
The Search Small MDE Sales Tracker has been in continuous production use since May 2026. This case study is updated as the platform grows.