Petlibro Water Fountain Dashboard

A self-hosted dashboard for tracking your cat's hydration habits using the Petlibro water fountain API. Features real-time data, historical trend charts, and proactive maintenance alerts.

Features

• Live Water Consumption — Today's total mL, drinking sessions, total time, and average duration
• Yesterday Comparison — Side-by-side comparison with trend indicators
• Fountain Status — Water level, filter life, cleaning schedule, WiFi signal
• Maintenance Alerts — Proactive notifications before thresholds are reached, with clear "overdue by X days" messaging when past due
• Weekly/Monthly/Yearly Trends — Visualize consumption patterns over time
• Time-of-Day Analysis — Discover when your cat drinks most frequently
• Device Events Log — Recent fountain events and errors
• Data Export — Download historical data as CSV or JSON
• Automatic Sync — Cron endpoint for scheduled data collection
• Proactive Session Repair — Automatic detection and re-sync of missing drinking sessions
• Token Auto-Refresh — Seamless re-authentication on token expiry

Tech Stack

• Frontend: React 19 + Tailwind CSS 4 + Recharts
• Backend: Express + tRPC 11
• Database: MySQL/TiDB (Drizzle ORM)
• Auth: Simple username/password with JWT sessions

---

Deployment Guide

Prerequisites

• A TiDB Serverless account (free tier)
• A Render.com account (free tier)
• (Optional) A Cloudflare account for DNS/CDN

1. Database Setup (TiDB Serverless)

1. Create a free TiDB Serverless cluster at tidbcloud.com
2. Once created, go to Connect and copy the connection string (MySQL compatible)
3. The connection string looks like:

   mysql://username:password@gateway.region.prod.aws.tidbcloud.com:4000/database_name?ssl={"rejectUnauthorized":true}
   

4. Run the database migration. You can use the TiDB SQL editor or connect via CLI:

   CREATE TABLE IF NOT EXISTS users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     username VARCHAR(64) NOT NULL UNIQUE,
     passwordHash VARCHAR(128) NOT NULL,
     name TEXT,
     email VARCHAR(320),
     role ENUM('user', 'admin') NOT NULL DEFAULT 'admin',
     createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     lastSignedIn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );
   
   CREATE TABLE IF NOT EXISTS daily_water_log (
     id INT AUTO_INCREMENT PRIMARY KEY,
     userId INT NOT NULL,
     date DATE NOT NULL,
     totalMl FLOAT NOT NULL DEFAULT 0,
     drinkingCount INT NOT NULL DEFAULT 0,
     totalDrinkingTime INT NOT NULL DEFAULT 0,
     avgDrinkDuration INT NOT NULL DEFAULT 0,
     createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   
   CREATE TABLE IF NOT EXISTS hourly_water_log (
     id INT AUTO_INCREMENT PRIMARY KEY,
     userId INT NOT NULL,
     date DATE NOT NULL,
     hour INT NOT NULL,
     totalMl FLOAT NOT NULL DEFAULT 0,
     drinkingCount INT NOT NULL DEFAULT 0,
     createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   
   CREATE TABLE IF NOT EXISTS petlibro_credentials (
     id INT AUTO_INCREMENT PRIMARY KEY,
     userId INT NOT NULL UNIQUE,
     email VARCHAR(320) NOT NULL,
     password VARCHAR(256) NOT NULL,
     region VARCHAR(20) NOT NULL DEFAULT 'US',
     deviceSn VARCHAR(128),
     timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
     lastSyncAt TIMESTAMP,
     createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   
   CREATE TABLE IF NOT EXISTS drinking_sessions (
     id INT AUTO_INCREMENT PRIMARY KEY,
     userId INT NOT NULL,
     sessionId VARCHAR(64) NOT NULL,
     deviceSn VARCHAR(128) NOT NULL,
     sessionTime BIGINT NOT NULL,
     date DATE NOT NULL,
     amountMl FLOAT NOT NULL DEFAULT 0,
     durationSec INT NOT NULL DEFAULT 0,
     createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );

2. Deploy to Render

Option A: Blueprint (Recommended)

1. Push this repo to GitHub
2. Go to Render Dashboard → New → Blueprint
3. Connect your GitHub repo — Render will detect render.yaml
4. Fill in the environment variables:
   • DATABASE_URL: Your TiDB connection string
   • JWT_SECRET: Auto-generated by Render
   • CRON_SECRET: Auto-generated by Render

Option B: Manual Setup

1. Go to Render → New → Web Service
2. Connect your GitHub repo
3. Configure:
   • Build Command: pnpm install && pnpm build
   • Start Command: pnpm start
   • Plan: Free
4. Add environment variables:
   • NODE_ENV = production
   • DATABASE_URL = your TiDB connection string
   • JWT_SECRET = any random 32+ character string
   • CRON_SECRET = any random 32+ character string

Setting Up the Cron Job (GitHub Actions — Recommended)

The repo includes a GitHub Actions workflow (.github/workflows/sync.yml) that handles scheduled syncs with a wake-then-sync pattern to avoid Render free tier cold-start issues.

1. In your GitHub repo, go to Settings → Secrets and variables → Actions
2. Add these repository secrets:
   • RENDER_APP_URL: Your full Render app URL (e.g., https://petlibro-dashboard.onrender.com)
   • CRON_SECRET: The same CRON_SECRET value from your Render environment variables
3. The workflow runs automatically every 4 hours. It:
   • Pings /api/health to wake the Render instance from sleep
   • Waits 45 seconds for cold start to complete
   • Calls /api/cron/sync with the cron secret header
   • Retries once on failure
4. You can also trigger it manually from the Actions tab → Petlibro Sync → Run workflow

Why GitHub Actions over cron-job.org? Render's free tier sleeps after 15 min of inactivity. External cron services (cron-job.org, EasyCron) often fail because the instance is asleep and returns a large HTML error page or times out. GitHub Actions handles this gracefully with the wake-wait-sync pattern.

Alternative: Render Native Cron ($1/month)

If you prefer not to use GitHub Actions:

1. In Render, go to New → Cron Job
2. Schedule: 0 */4 * * *, Command:

   curl -sf -H "x-cron-secret: $CRON_SECRET" "$RENDER_EXTERNAL_URL/api/cron/sync"

3. Cloudflare Setup (Optional)

1. Add your custom domain to Cloudflare
2. In Render, go to Settings → Custom Domains → add your domain
3. In Cloudflare DNS, add a CNAME record pointing to your Render URL
4. Enable Cloudflare proxy (orange cloud) for CDN/DDoS protection

4. First Login

1. Visit your deployed URL
2. Click "Don't have an account? Register"
3. Create your admin account (first user gets admin role)
4. Go to Settings → enter your Petlibro email, password, and region
5. Click "Test Connection" to verify, then save
6. Select your fountain device from the dropdown
7. Return to the Dashboard to see live data

---

Local Development

# Clone the repo
git clone <your-repo-url>
cd petlibro-dashboard

# Install dependencies
pnpm install

# Create .env file
cp .env.example .env
# Edit .env with your DATABASE_URL and JWT_SECRET

# Run development server
pnpm dev

The app will be available at http://localhost:3000.

---

Environment Variables

Variable	Required	Description
DATABASE_URL	Yes	MySQL/TiDB connection string
JWT_SECRET	Yes	Secret for signing session tokens
CRON_SECRET	Yes	Secret for authenticating cron requests
PORT	No	Server port (defaults to 3000)

---

API Endpoints

Endpoint	Method	Description
/api/trpc/*	POST	tRPC API (all app functionality)
/api/cron/sync	GET	Cron sync endpoint (requires x-cron-secret header)
/api/migrate	GET	Schema migration endpoint (requires secret query param)
/api/health	GET	Health check endpoint

---

Data Integrity: Proactive Session Sync

The dashboard uses a moderate hybrid strategy to ensure drinking session data stays consistent with the daily summary log. This addresses cases where the Petlibro API returns daily totals (session count, total intake) but individual session records are missing or incomplete.

How It Works

The system compares daily_water_log.drinkingCount (expected sessions) against the actual count of rows in drinking_sessions for each date. When stored < expected, it triggers a re-fetch from the Petlibro workRecord API.

Two repair mechanisms work together:

1. Cron Integrity Check — During each scheduled sync, the system verifies today and yesterday's session counts. If a gap is detected, it automatically re-fetches the missing sessions from the API. This runs proactively without user intervention.

2. Lazy Repair on Read — When a user views the Daily tab for any date, the drinkingSessions query checks integrity before returning results. If stored sessions are fewer than expected (or zero), it fetches from the API, upserts any new records, and returns the updated list. This self-heals older dates on first view.

Trigger Conditions (Moderate Strategy)

Condition	Action
stored == 0 and no daily log	Fetch on-demand (discovery)
stored < expected	Re-fetch and upsert missing sessions
stored >= expected	No action needed

Manual Re-sync

Users can also click the Re-sync Sessions button on the Daily tab to force a fresh fetch for any specific date, regardless of integrity status.

Design Decisions

See docs/session-sync-strategies.md for the full analysis of alternative approaches considered.

---

Project Structure

├── .github/workflows/   # GitHub Actions (cron sync)
├── client/              # React frontend
│   ├── src/
│   │   ├── pages/       # Dashboard, Trends, Settings, Login
│   │   ├── components/  # UI components + DashboardLayout
│   │   ├── hooks/       # useAuth hook
│   │   └── lib/         # tRPC client
│   └── index.html
├── server/              # Express + tRPC backend
│   ├── index.ts         # Server entry point
│   ├── routers.ts       # tRPC procedures
│   ├── db.ts            # Database helpers
│   ├── auth.ts          # Password hashing + JWT
│   ├── cron.ts          # Cron sync endpoint
│   ├── timezone.ts      # DST-safe timezone utilities
│   ├── petlibro-api.ts  # Petlibro API client
│   ├── context.ts       # tRPC context
│   └── trpc.ts          # tRPC setup
├── docs/                # Design decisions & architecture
├── drizzle/             # Database schema + migrations
├── shared/              # Shared constants
├── render.yaml          # Render deployment config
└── package.json

---

Cron Sync Strategy

The recommended approach is the included GitHub Actions workflow (.github/workflows/sync.yml), which uses a wake-then-sync pattern to handle Render's free tier cold starts. See the deployment section above for setup.

Why not cron-job.org? External cron services have response size limits and don't handle Render's sleep/wake cycle well. When the instance is sleeping, Render returns a large HTML error page that exceeds cron-job.org's response limit, causing the job to be disabled after repeated failures.

Fallback options (if GitHub Actions isn't suitable):

• Render Native Cron ($1/month) — handles cold starts internally
• Keep-alive + cron-job.org — a separate job pings /api/health every 14 min to prevent sleep, then the sync job runs normally

---

License

MIT