About the Project

Inspiration

When we partnered with a community organization, we encountered a common operational challenge: a single shared spreadsheet had become the primary way teams tracked projects, participants, and outcomes.

Field staff needed to capture information while delivering sessions, and back-office teams needed different ways to view and report on the same dataset. Because data was entered by multiple people with different habits, it was hard to keep formats consistent and difficult to produce tailored views without extra effort.

We were inspired by a simple question:

What if data collection could be seamless, and insights could be immediate?

What We Learned

Building for a real organization taught us lessons no tutorial ever could:

User research is non-negotiable. The gap between what we assumed users needed and what they actually needed was massive. Role-based views weren’t a nice-to-have — they were essential.
Simplicity beats features. Our first designs were feature-packed. After talking to the team, we stripped them down. Busy users don’t need dozens of options — they need a small set of clear actions that match their workflow.
Data has gravity. Once data has lived in a spreadsheet for years, moving it into a structured system requires careful schema design. We learned to model relationships:

$$ \text{Project} \rightarrow \text{Sessions} \rightarrow \text{Participants} \rightarrow \text{Testimonials} $$

LLMs unlock qualitative data. Testimonials that previously required significant time to review can now be clustered and summarized at scale.

What we built

We built a role-based web application that replaces a single shared spreadsheet with a structured relational database and intuitive dashboards.

The platform streamlines frontline data collection through clean project forms, participant tracking, and fast testimonial capture, including voice-to-text for use during busy live delivery. This helps teams capture impact data consistently without slowing down their work.

For reporting and stakeholder-facing teams, the system provides tailored views and dashboards that surface the information they need — such as impact summaries, demographic reach, project history by area, and themed testimonial insights. Data can be filtered by region, project type, demographic group, and time period, making it easier to evidence impact and support reporting.

On the technical side, we implemented Supabase for authentication and a relational database, FastAPI for a clear API layer, and a frontend that supports role-based views and customizable dashboards. We added AI-powered analysis to automatically extract themes and insights from testimonials, reducing the need for manual qualitative review.

Overall, the web app centralizes data, reduces admin overhead, and turns previously scattered information into usable evidence for storytelling, reporting, and planning.

How We Built It

Architecture

┌─────────────────────────────────────────────────────────┐
│                     Frontend (Next.js 15)               │
│   React 19 + TypeScript + Tailwind CSS + Recharts       │
├─────────────────────────────────────────────────────────┤
│                     Backend (FastAPI)                   │
│         RESTful API + Authentication + LLM Layer        │
├─────────────────────────────────────────────────────────┤
│                     Database (PostgreSQL)               │
│           Relational schema + structured records        │
└─────────────────────────────────────────────────────────┘

Key Components

Interactive Region Map: Visualizes activity across operational areas using Highcharts
Role-Based Dashboards: Field staff, communications, and reporting teams each see tailored views
Drag-and-Drop Widget Customization: Users can personalize their dashboard layout
Testimonial Capture & Analysis: Voice-to-text input with LLM-powered thematic extraction
Flexible Filtering: Date ranges, demographics, project types, and geographic areas

Tech Stack

Layer	Technology
Frontend	Next.js 15, React 19, TypeScript, Tailwind CSS
Visualization	Highcharts, Recharts
Drag & Drop	dnd-kit
Backend	FastAPI (Python)
Auth	JWT-based authentication
Database	PostgreSQL

Challenges We Faced

1. Schema Design Under Time Pressure

Legacy spreadsheet data often contains “hidden” structure that only becomes clear during migration and normalization. We had to make rapid decisions:

Should Area be a separate table or an enum?
How do we handle participants who attend multiple projects?
What’s the right granularity for demographic tags?

2. Balancing Flexibility vs. Simplicity

Reporting teams wanted maximum query flexibility. Field teams wanted minimal cognitive load. These goals can conflict, so we focused on sensible defaults with optional power-user features.

3. Visualization Performance

Rendering a map with live data updates required careful optimization. We used:

SWR for smart data fetching and caching
Memoization to prevent unnecessary re-renders
Lazy loading for heavy chart libraries

4. Testimonial Data Quality

Testimonials can be inconsistent — incomplete sentences, mixed languages, and contextual references. Iterating on prompts to reliably extract meaningful themes took multiple rounds of refinement.

VIDEO : https://youtu.be/cvCOYzPLK-8

Built with care at IC Hack 2025