03Environmental incident platform

SpillSense

Spill response programs live or die on data quality: a responder needs to know what spilled, where, how much, and who is responsible — fast.

Open the live site Read the source

API endpoints: 12
Counties seeded: 39
Error format: RFC 9457
Map feed: RFC 7946

Context

SpillSense models the data systems behind Washington State's oil-spill prevention, preparedness and response programs. It is a full vertical slice: structured incident records with spatial coordinates, a validated ETL intake pipeline, a filterable REST API, an interactive GIS dashboard, and reporting and export tooling for program analysts.

The domain model follows how environmental agencies actually classify a spill — medium affected, substance category, source type, response status, and Department of Ecology regional jurisdiction — rather than a generic incident table with a free-text notes column.

How it works

01Integrity lives in the database

All 39 Washington counties are seeded with FIPS codes, Ecology region assignments and coastal flags, and coordinate sanity bounds catch swapped or malformed latitude/longitude at intake. Report numbers are unique natural keys, deletes on reference data are restricted, and enums are stored as strings so the database stays self-describing for anyone writing reports against it directly. Indexes are matched to the query paths the dashboard actually takes, not sprinkled over every column.

02Imports that can be re-run and audited

Every import is recorded as an ImportRun with insert, update, unchanged and rejected counts. Re-running the same file inserts nothing new and updates changed rows in place, matched on report number, so an import that half-finished can simply be run again. Rows that fail validation are quarantined with the raw line preserved verbatim alongside every reason it failed — a row is never silently dropped.

03Report every problem, not the first one

Validation catches malformed report numbers, unparseable or future dates, out-of-state or swapped coordinates, unknown counties, negative quantities, unrecognised classifications and in-file duplicates — and reports all of a row's problems at once. Filter parameters bind as strings on purpose so the parser can name every invalid value in one response rather than failing on the first with a framework binding error. Bad input comes back as RFC 9457 problem details listing the accepted values.

04One filter set, every panel

Search, county, region, medium, substance, source, status, date range, minimum volume and a bounding-box spatial filter combine with AND, and the same filter set drives the list endpoint, the RFC 7946 GeoJSON feed and the statistics endpoints. Because the filters are composable and shared, the map, the charts, the KPI tiles and the table cannot disagree with each other. Filter state lives in the querystring, so any view of the data is a permalink.

05Two hosts, one contract

The ASP.NET Core host owns the database, the intake pipeline and the interactive API docs. Vercel runs a read-only replica: Node serverless functions implementing the same contract against a published snapshot exported from the system of record. The dashboard is host-agnostic — it calls the same endpoints either way — and contract tests run against the serverless layer so the two deployments cannot drift apart.

Architecture

SpillSense.Domain: Entities, enums and geography rules. No dependencies.
SpillSense.Infrastructure: EF Core context, configurations, migrations, ETL pipeline.
SpillSense.Web: ASP.NET Core host — REST API plus the dashboard.
api/: Vercel serverless functions serving the same contract.
tests/: .NET integration tests plus node:test API contract tests.

Decisions

The choices worth defending, and what they cost.

SQLite as the default provider

The project runs anywhere with zero setup, and the EF model is kept provider-portable — pointing the connection string at SQL Server is a drop-in swap, which mirrors the deployment target an agency would actually have.

Enums stored as strings

A report writer querying the database directly sees 'Diesel', not 3. The database explains itself without a lookup table or a copy of the C# enum.

A dependency-light dashboard

Vanilla ES modules with Leaflet and Chart.js, no build step. The dashboard is the part an analyst has to trust; keeping it inspectable was worth more than a framework.

Scalar vendored, not loaded from a CDN

The API reference and explorer both work offline, which matters for a tool that might be opened somewhere with no connectivity.

Verification

Integration tests run against real migrations rather than an in-memory fake, so schema changes are exercised the way they will actually be applied. A separate node:test contract suite runs against the serverless layer in CI, which is what keeps the two hosts honest.

C#
.NET 10
ASP.NET Core
EF Core
SQL Server
Leaflet
OpenAPI

Next case study

Neo Tokyo Transit

3D rail network & route planner

Back to all work by Jonathan Cho