SafeCommerce-LA

Overview

Authenticated dashboards, maps, charts, filters, and CSV export over a PostgreSQL schema that joins LA crime data with California retail sales records. Graded 99/100.

Problem

Crime and retail data live in separate silos with different shapes, so neighborhood-level questions like 'where do high-crime areas overlap with declining retail sales?' are hard to answer without expensive joins and slow ad-hoc queries.

What I built

Designed a PostgreSQL schema that normalizes both datasets, exposed reusable query endpoints behind JWT-authenticated APIs, and built a React dashboard with maps, charts, filters, and CSV export so users could drill from city-wide trends to a single store.

Architecture

• React frontend with authenticated dashboards, maps, charts, filters, and CSV export
• Python REST API layer with JWT-protected endpoints
• PostgreSQL schema with joins, CTEs, aggregations, and supporting indexes
• Query layer uses caching and restructured SQL to keep dashboard queries under interactive latency

Technical highlights

• Designed the PostgreSQL schema and optimized SQL with joins, subqueries / CTEs, aggregation, indexes, caching, and query restructuring.
• Built authenticated workflows for neighborhood risk analysis, sales trends, high-crime areas, and store-level reporting.
• Implemented CSV export so analysts could pull filtered slices into downstream tools.

Impact

• Project earned 99/100 in the Database and Information Systems course.
• Made cross-domain LA crime and CA retail questions answerable interactively.