Project · CPSC 304
StrayCare — database-first platform for stray animal care
StrayCare is a community-driven system that treats stray animal care like a proper data problem. Every feeding event, vaccination, and volunteer action is logged in an Oracle-backed schema so that no animal quietly drops off the map.
The app models animals, locations, volunteers, communities, feeding events, and vaccinations in a single relational graph. Volunteers can see which animals were fed, when they were last vaccinated, and which communities need more help – all grounded in real SQL instead of spreadsheets.
Each node represents a stray animal or active volunteer. StrayCare maps animals by Plus Code so feeding, vaccination, and community activity are tied to real neighborhoods instead of vague labels.
Dogs and cats with detailed attributes: sex, age, traits, training, and location, all linked to feeding and vaccination history.
Volunteers grouped into geographic communities with role-based permissions so people only manage animals in their area.
Every animal and volunteer is anchored to a Plus Code location, enabling precise filtering by neighborhood or cluster.
Full vaccination records with vaccine type, dosage, date, and vet. Under-vaccinated animals can be surfaced quickly.
Feeding frequency, volunteer activity, and vaccination coverage are queryable through Oracle views and aggregation queries.
Session-backed auth and role checks so only authorized volunteers can edit data for their assigned communities.
Database-first design
StrayCare was built around the Oracle schema first: strong entities for animals, volunteers, communities, vets, and locations, plus relationship tables for feeding and vaccination events. The UI simply surfaces what the database already models precisely.
The schema uses Plus Codes for location keys, separates animal attributes from community mappings, and stores vaccination events as their own records so they can be analyzed independently from feeding history.
- Normalized tables for animals, volunteers, communities, vets.
- Bridge tables for volunteer–community and animal–location links.
- Separate vaccination and feeding event tables with timestamps.
- Views for “last fed”, “vaccination gaps”, and volunteer activity.
StrayCare ER Diagram (Abstract Version)
Backend architecture
- Express API: layered into controller + service modules for clean separation between HTTP and database logic.
- Oracle DB: SQL-backed operations with prepared statements and views for analytics.
- Session auth: cookie-backed sessions with server-side checks before any mutation.
- Static frontend: vanilla JS, HTML, and CSS consuming the Express endpoints directly.
// High-level flow
Volunteer → browser form → Express route → service layer →
Oracle query → commit → updated animal / feeding / vaccine view.
Designing around data
The UI exists to expose the schema, not the other way around. Most features started as SQL queries and only then became screens.
Real-world constraints
Oracle, not Postgres or SQLite, drove decisions: explicit schemas, verbose SQL, and attention to indexing and joins.
Community impact
The system is built so communities can reason about their stray populations with actual numbers instead of intuition.