Shitty Kitty Committee — Web App Project Spec

Status: Planning consolidated — v1 Purpose: Single reference document for stack, hosting, schema, and access-control decisions made so far, before build begins.

1. Background

SKC previously scoped a rescue-tracking database (see SKC.md) built around one core principle:

One permanent record per cat, with separate linked records for events and history.

This spec adapts that plan into a web app the group (5–10 people) can use to view and update the current state of any cat, with a calendar of upcoming events per cat.

2. Hosting & Stack Decisions

Constraint: App runs on an existing iMac (macOS 10.15 Catalina) that already hosts security cameras and three other websites via Apache + php-fpm. Avoid adding new runtimes/processes where possible.

Decided stack:

Layer Choice Why
Web server Apache + php-fpm (existing) Already installed and managed; no new service
Backend Plain PHP No new runtime (rejected Node.js/Next.js as unnecessary weight for an internal 5–10 user tool)
Database MySQL (via MacPorts) Familiar from work use; handles light concurrent multi-user writes better than SQLite
Frontend Vanilla HTML/CSS/JS (+ Alpine.js if needed for interactivity) No build tooling/npm ecosystem needed
Calendar FullCalendar (JS library, script-tag only) No build step required
Hosting path New vhost: cat.skunkworks.nz Name-based virtual hosting — no conflict with camera setup on bare skunkworks.nz domain
DNS Managed via Rocketspark webform Add A/CNAME record for cat.skunkworks.nz — confirm Rocketspark allows full DNS edits
SSL Certbot (existing Let's Encrypt setup) Add cat.skunkworks.nz as a new vhost + run certbot, same pattern as existing sites

Net result: everything runs inside infrastructure already operated — new MySQL database + new folder of PHP files under a new Apache vhost. Nothing fundamentally new to learn or maintain.

3. Database Schema

Based on the original 14-table plan, with two additions: Status History and Cat Events (replacing a rigid pathway model — see Section 4).

Core tables (build first)

  1. Cats — the central permanent record (Cat ID, name, sex, DOB, microchip, current status, current location, current foster, origin colony, etc.)
  2. Status History (new) — Cat ID, old status, new status, changed by, timestamp. Logs every status change instead of overwriting, so you always know when a cat moved from Quarantine → Foster, etc.
  3. Locations — Quarantine, Medical room, Foster home, Colony, etc.
  4. People — central contacts table (Name, Phone, Email, Role/Title, Active, Notes). Role here is descriptive only (Volunteer, Trapper, Feeder, Vet, Coordinator) — see Section 5 for why this is deliberately separate from access control.
  5. Fosters — foster carer capacity and specialisations
  6. Foster Placements — historical record of every foster stay (Cat ID, Foster ID, start/end date, reason) — never overwritten
  7. Event Types (new) — master library of possible events (Intake exam, Microchip, Vaccination, Desex, Ear-tip, Adoption home check, Return to colony, etc.). Superusers can add new types anytime.
  8. Cat Events (new) — links a Cat to an Event Type: due date, status (Pending / Done / Skipped / N/A), completed date, done by, notes. Drives the calendar and the "what's next for this cat" view.

Phase 2 tables

  1. Medical — full vet history per cat
  2. Medications — active/past treatment tracking
  3. TNR — dedicated community-cat TNR tracking
  4. Colonies — community-cat site tracking, independent of individual cats
  5. Trapping — per-operation trapping session records

Phase 3 tables

  1. Adoptions — application through to outcome
  2. Expenses — rescue expenditure, linked to Cat or Colony
  3. Documents — links files (vet invoices, contracts, permissions) to Cats or Colonies

Auth tables (new — see Section 6)

  1. Users — login accounts (name, email, password hash, active). No role/title field.
  2. Invites — superuser-generated invite tokens (email, token, expiry, used)
  3. Permissions — access control per user (see Section 5)

Linking: every table uses a unique prefixed ID (CAT-0001, COL-0001, MED-0001, etc.), with Cat ID as the key link across most tables — a single cat can have multiple medical records, foster placements, events, expenses, and documents without duplicating its core record.

4. Event / Pathway Model

Problem with a fixed pipeline: cats arrive through too many different routes (microchipped vs. not, wild-born vs. stray, TNR-to-wild vs. TNR-to-feeder, existing vet history vs. none) to hardcode a single checklist per "pathway."

Decided approach — flexible, per-cat checklist:

This reuses the same "permanent record + linked events" principle as the rest of the schema, applied to workflow instead of history — and it's what feeds the calendar (any Pending Cat Event with a due date shows up there).

5. Access Control

Key decision: a person's Role/Title (Volunteer, Trapper, Feeder, etc. — descriptive, stored in People) is completely separate from their access level (what they can actually view/edit in the app, stored in Permissions). A Trapper might have full edit access; a Coordinator might be read-only — title tells you nothing about access.

Starting model (deliberately simple, built to extend):

Permissions table: User ID + Access Level + Scope

Example — the known current case: a Trapper who should only ever see cat status and nothing else → Access = View, Scope = Cats only.

Why this scales without a rewrite: when a messier exception shows up later (e.g. someone who can edit Colonies but not Cats), the fix is to add another feature-scoped row for that user in the same table — not to redesign the whole permission system. Starting with one row per user; extending to one row per user-per-feature-area only when actually needed.

Two superusers: two Users rows with a "manage users" flag set. Default assumption: either superuser can edit or deactivate the other (no built-in safeguard against lockout) — flag if you'd prefer this to require confirmation from both instead.

Invite flow:

  1. Superuser creates an Invites row (email + single-use token + expiry, e.g. 7 days) and sends the link: cat.skunkworks.nz/set-password?token=...
  2. Invited user sets their password via that link, creating their Users row.
  3. Superuser deactivates a user by flipping active = 0 — history (who-did-what) is preserved, not deleted.

6. Open Questions / Not Yet Decided

7. Build Order (proposed)

  1. Core tables (Cats, Status History, Locations, People, Fosters, Foster Placements) + Users/Invites/Permissions + basic auth
  2. Cat Events + Event Types + calendar view (FullCalendar)
  3. Medical, Medications, TNR, Colonies, Trapping
  4. Adoptions, Expenses, Documents
  5. Dashboard (cat overview counts, colony/TNR overview, alerts, foster capacity — as originally scoped in SKC.md Section 6)

This document reflects decisions made through project planning conversation. It should be treated as a living reference — update it as decisions change rather than starting a fresh document.