Doggo Dashboard

A small PHP + SQLite dashboard for tracking dog‑related activities.
This repository is intended to be a polished, working site and a functional portfolio piece suitable for display on GitHub.

---

Requirements

• PHP 8.x (with PDO + SQLite enabled)
• SQLite 3.x

---

Running Locally

From the repository root, start the built‑in PHP server:

php -S localhost:8000 -t .

Ensure doggodashboard.db exists at the repository root and is readable/writable by PHP.
To generate a fresh database, see the db/ folder for schema.sql and data.sql.

---

Key Files & Conventions

• index.php, activities.php, appointments.php
  UI pages rendered server‑side.

• assets/utils/dal.php
  Data Access Layer (singleton).
  Use DAL::instance() for reads and DAL::insert() for writes.

• assets/utils/datehelper.php
  All timezone conversions.
  Store timestamps in UTC; use DateHelper::formatSql() for DB writes.

• assets/utils/helpers.php
  Contains h() for escaping, isPost(), redirect(), checkAuthAndRedirect(), and return‑path helpers.

• assets/actions/*.php
  POST endpoints that validate input, call DAL methods, and redirect.
  Follow existing patterns (auth enforcement + POST‑only).

---

Notes for Contributors

• Always escape output using h().
• Always convert timestamps using DateHelper before writing to the database.
• Keep changes minimal and focused — this project is also a portfolio piece.
• Maintain accessibility, consistent UI patterns, and clean code structure.
• Follow existing DAL and action‑handler conventions.

---

Database Schema Overview

The Doggo Dashboard uses a lightweight, normalized SQLite database designed for clarity, maintainability, and fast read performance. The schema models:

• Users — people who log activities
• Activities — types of events (Potty, Walk, Grooming, etc.)
• Activity Logs — timestamped entries created by users
• Suboptions — structured metadata for logs
• Files — attachments linked to log entries
• Upcoming Events — scheduled future activities

The schema is fully relational, with strict constraints and several denormalized views for efficient UI rendering.

A full ERD and schema documentation are available in db/README.md.

---

Table Descriptions

activities

Defines all activity types. Includes icon, color, and whether the activity can be scheduled.

activity_suboptions

Optional structured flags for each activity (pee/poop, bath/nails, etc.).
Displayed dynamically in the UI and stored as JSON arrays in views.

activity_log

Every logged event. Stores user, activity, timestamp, and optional notes.

activity_log_suboptions

Connects logs to selected suboptions.
Used to generate JSON arrays in denormalized views.

activity_files

Files uploaded for a specific log entry (vet records, photos).
Stored on disk under /uploads/activities/{activity_id}/.

users

People who can log activities. Color and button class are used for UI styling.

upcoming

Scheduled future events such as appointments, reminders, or medication doses.

---

Views

activities_with_suboptions

Activities with an ordered JSON array of suboptions.
Used by the modal to dynamically render checkboxes.

activity_log_denormalized

Flattened log entries with user and activity metadata.
Includes JSON arrays of selected suboptions.

activity_recent_logs

Last four logs per activity (excluding the most recent).
Used for hover tooltips and quick summaries.

latest_log_per_activity

Most recent log for each activity.
Used on dashboard cards.

latest_log_per_user

Most recent log per user.
Used for user summaries.

upcoming_denormalized

Upcoming events with activity metadata.
Used by the scheduler UI.

---

Indexing Strategy

Indexes optimize:

• Sorting logs by timestamp
• Filtering logs by activity
• Filtering logs by user

This keeps the dashboard responsive even with large datasets.

---

Seed Data

The project includes realistic seed data for:

• Activities
• Suboptions
• Logs
• Log suboptions
• Upcoming events
• File attachments

Users are intentionally dummy accounts for privacy.

---

Database Files

The db/ folder contains:

• schema.sql — full database initialization script
• data.sql — example/demo seed data
• erd.uml.txt — ASCII ERD
• db_schema.pdf — full schema reference

See db/README.md for details.