About the Project

Inspiration

When we met with ArtsWork, a UK charity delivering creative projects for underserved young people across Southeast England, we discovered a familiar problem: a single, overwhelming Excel spreadsheet had become the lifeblood of their operations.

Their frontline staff, Creative Producers running workshops in chaotic environments, had to wrangle data entry while simultaneously managing energetic young people, this led to heavy amounts of manual labour which was also chaotic. Meanwhile, their Communications and Funding teams needed entirely different views of the same data, but had no way to filter or customize what they saw, additionally, the data formats they saw differed based on each Creative Producer due to the different data entry formats that their Creative Producers used.

We were inspired by a simple question:

What if data collection could be invisible, and insights could be instant?

The inefficiency wasn't just frustrating, it was actively hindering their mission. Every hour spent wrestling with spreadsheets was an hour not spent helping young people.

What We Learned

Building for a real charity 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. A busy Creative Producer doesn't need 50 options, they need 3.
Data has gravity. Once data lives in a spreadsheet for years, extracting it requires careful schema design. We learned to model relationships:

$$ \text{Project} \rightarrow \text{Events} \rightarrow \text{Attendees} \rightarrow \text{Testimonials} $$

LLMs unlock qualitative data. Testimonials that once sat unanalyzed can now be thematically clustered and summarized at scale.

What we built

We built a role based web application for ArtsWork that replaces their single, unwieldy Excel spreadsheet with a structured relational database and intuitive dashboards. The platform simplifies frontline data collection for PCPs through clean project forms, attendee tracking, and fast testimonial capture, including voice to text for use during busy live events. This ensures impact data is captured consistently without interrupting delivery.

For Communications and Funding teams, the system provides tailored views and dashboards that surface the data they actually 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 easy to evidence impact or support grant applications.

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 customisable dashboards. We added AI powered analysis to automatically extract themes and insights from testimonials, removing the need for manual qualitative analysis.

Overall, the web app centralises data, reduces admin burden for PCPs, and turns previously siloed information into usable evidence for storytelling, funding, and strategic planning across the charity.

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 replacing monolithic spreadsheet   │
└─────────────────────────────────────────────────────────┘

Key Components

Interactive UK Choropleth Map: Visualizes impact across ArtsWork's five operational areas using Highcharts
Role-Based Dashboards: PCPs, Communications, and Funding 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

The existing spreadsheet had implicit relationships that weren't obvious until we tried to normalize them. We had to make rapid decisions:

Should Area be a separate table or an enum?
How do we handle attendees who participate across multiple projects?
What's the right granularity for demographic tags?

2. Balancing Flexibility vs. Simplicity

Funding teams wanted maximum query flexibility. Creative Producers wanted minimum cognitive load. These goals often conflicted. Our solution: sensible defaults with optional power-user features.

3. Visualization Performance

Rendering a choropleth map with real-time 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

Real testimonials are messy—incomplete sentences, mixed languages, and contextual references. Training our LLM prompts to extract meaningful themes required iteration.

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

Built with care at IC Hack 2025 for ArtsWork UK.