"When Was The Last Time" - A progressive web application for tracking occurrences of events, activities, and items.

A single-user event tracker that records when specific items/events last occurred, displays time elapsed, maintains full historical data, and provides statistical analysis of occurrence patterns. No authentication required - data is stored locally in the browser's SQLite database.

• Flexible tracking: Can track personal activities ("I went to the gym") or general events ("Dog was groomed", "It rained")
• Complete historical record with intervals between occurrences
• Statistical insights (averages, longest/shortest intervals, frequency)
• Offline-capable PWA accessible across devices
• Category-based organization with search and filtering
• No login required - simple, immediate access

• Deployment: Val Town (https://val.town)
• Runtime: Deno/TypeScript on Val Town infrastructure
• Application Type: Progressive Web App (PWA)

• Framework: Hono (lightweight web framework for Deno)
• Database: SQLite (Val Town's built-in SQLite via Turso)
  • Import: import { sqlite } from "https://esm.town/v/std/sqlite"
• No Authentication: Single-user app with no login required

• Framework: React 18+ (client-side rendering)
• Styling: Tailwind CSS (via CDN)
• State Management: React hooks (useState, useEffect, useContext)
• Build: No build step - direct ESM imports

• Manifest: /manifest.json with app metadata
• Service Worker: /sw.js for offline capability and caching
• Icons: Multiple sizes (192x192, 256x256, 384x384, 512x512)
• HTTPS: Required (Val Town provides this automatically)

username-lastTimeTracker/
├── backend/
│   ├── index.ts          # Main Hono server & routing
│   └── db.ts             # Database initialization & queries
├── frontend/
│   ├── index.html        # Main HTML entry point
│   ├── index.tsx         # React root component
│   ├── App.tsx           # Main React app component
│   ├── components/
│   │   ├── ItemList.tsx
│   │   ├── ItemDetail.tsx
│   │   ├── AddItem.tsx
│   │   ├── CategoryManager.tsx
│   │   └── ArchiveView.tsx
│   └── utils/
│       ├── api.ts        # API client functions
│       └── timeCalc.ts   # Time calculation utilities
├── public/
│   ├── manifest.json     # PWA manifest
│   ├── sw.js            # Service worker
│   └── icons/           # App icons (various sizes)
└── README.md

User (PWA) → HTTPS → Val Town Endpoint
                ↓
            Hono Router
                ↓
         Route Handler → SQLite (data operations)
                ↓
            JSON Response → React Frontend

Single-User Architecture: This app has no authentication system. All data is stored in a single SQLite database on Val Town. Anyone with access to the Val Town URL can view and modify the data.

Privacy Note: Since there's no authentication:

• Don't store sensitive personal information
• The app is best suited for personal use or trusted environments
• Consider the Val Town URL as private (don't share publicly)
• Val Town Pro users can add custom domains with additional security if needed

• Normalized structure to minimize redundancy
• Indexes on frequently queried fields (category_id, item_id)
• Date-only storage (no time component) as per requirements
• Soft delete for items (archived flag)
• Single-user: No user table needed

create table if not exists categories (
  id text primary key,
  name text not null unique,
  created_at text not null
)

Notes:

• id: UUID generated via crypto.randomUUID()
• created_at: ISO 8601 date string (YYYY-MM-DDTHH:mm:ss.sssZ)
• Unique constraint on name prevents duplicates

create table if not exists items (
  id text primary key,
  category_id text not null,
  name text not null,
  description text,
  created_at text not null,
  archived integer default 0,
  foreign key (category_id) references categories(id)
)

Notes:

• id: UUID
• archived: 0 = active, 1 = archived (SQLite boolean as integer)
• Foreign key to categories (no cascade delete - prevents accidental data loss)

create table if not exists occurrences (
  id text primary key,
  item_id text not null,
  occurrence_date text not null,
  note text,
  created_at text not null,
  foreign key (item_id) references items(id) on delete cascade
)

Notes:

• id: UUID
• occurrence_date: ISO 8601 date (YYYY-MM-DD)
• created_at: When this record was created
• Cascade delete: If item deleted, its occurrences are also deleted

-- Categories (no additional indexes needed - name is unique)

-- Items
create index if not exists idx_items_category_id on items(category_id);
create index if not exists idx_items_archived on items(archived);

-- Occurrences
create index if not exists idx_occurrences_item_id on occurrences(item_id);
create index if not exists idx_occurrences_date on occurrences(occurrence_date);

• One category → Many items
• One item → Many occurrences

1. Category deletion: Requires reassignment or deletion of items in that category
2. Item "deletion": Sets archived = 1, preserves all data
3. Occurrence deletion: Cascade when item is hard-deleted
4. At least one occurrence should exist per item (enforced in application logic)

Note: No authentication required. All endpoints are publicly accessible.

Purpose: Get all categories Response:

{
  "categories": [
    {
      "id": "cat-uuid",
      "name": "Health",
      "created_at": "2024-01-01T00:00:00Z"
    }
  ]
}

Purpose: Create new category Body:

{
  "name": "Health"
}

Response:

{
  "category": {
    "id": "cat-uuid",
    "name": "Health",
    "created_at": "2024-01-01T00:00:00Z"
  }
}

Purpose: Update category name Body:

{
  "name": "Updated Name"
}

Purpose: Delete category Query params: ?reassign_to=<CATEGORY_ID> (optional) Notes: If items exist in category, must provide reassign_to or it will fail

Purpose: Get all items with their latest occurrence Query params:

• ?category=<CATEGORY_ID> - Filter by category
• ?search=<QUERY> - Search item names, descriptions, and notes
• ?sort=<alpha|recent|oldest> - Sort order
• ?archived=<true|false> - Include/exclude archived (default: false)

Response:

{
  "items": [
    {
      "id": "item-uuid",
      "name": "Went to the gym",
      "description": "Morning workout routine",
      "category_id": "cat-uuid",
      "category_name": "Health",
      "latest_occurrence": "2024-11-13",
      "days_since": 0,
      "months_since": 0,
      "years_since": 0,
      "total_occurrences": 5,
      "archived": false,
      "created_at": "2024-01-01T00:00:00Z"
    }
  ]
}

Purpose: Create new item Body:

{
  "name": "Went to the gym",
  "description": "Morning workout",
  "category_id": "cat-uuid",
  "initial_date": "2024-11-13"  // or "now" for today
}

Response:

{
  "item": { /* item object */ }
}

Notes: Automatically creates first occurrence

Purpose: Get item details with all occurrences and statistics Response:

{
  "item": {
    "id": "item-uuid",
    "name": "Went to the gym",
    "description": "Morning workout",
    "category_id": "cat-uuid",
    "category_name": "Health",
    "archived": false,
    "statistics": {
      "total_occurrences": 10,
      "average_interval_days": 3.5,
      "longest_interval_days": 7,
      "shortest_interval_days": 1,
      "occurrences_per_month": 8.6,
      "occurrences_per_year": 104
    },
    "occurrences": [
      {
        "id": "occ-uuid",
        "occurrence_date": "2024-11-13",
        "note": "Great workout!",
        "days_since_previous": 3,
        "created_at": "2024-11-13T10:00:00Z"
      }
    ]
  }
}

Purpose: Update item details Body:

{
  "name": "Updated name",
  "description": "Updated description",
  "category_id": "new-cat-uuid"
}

Purpose: Archive item (soft delete) Response:

{
  "success": true
}

Notes: Sets archived = 1, keeps all data

Purpose: Restore archived item

Purpose: Log new occurrence Body:

{
  "occurrence_date": "2024-11-13",  // or "now" for today
  "note": "Felt great today"
}

Response:

{
  "occurrence": {
    "id": "occ-uuid",
    "item_id": "item-uuid",
    "occurrence_date": "2024-11-13",
    "note": "Felt great today",
    "created_at": "2024-11-13T10:00:00Z"
  }
}

Purpose: Update occurrence Body:

{
  "occurrence_date": "2024-11-12",
  "note": "Updated note"
}

Purpose: Delete occurrence (hard delete) Notes: Cannot delete if it's the only occurrence for an item

App.tsx
├── Router (client-side)
│   ├── HomeView
│   │   ├── Header (with search, filter, sort controls)
│   │   ├── ItemList
│   │   │   └── ItemCard (shows time elapsed)
│   │   └── AddItemButton
│   ├── ItemDetailView
│   │   ├── ItemHeader (name, description, edit)
│   │   ├── StatisticsPanel
│   │   ├── OccurrenceList
│   │   │   └── OccurrenceCard (with edit/delete)
│   │   └── AddOccurrenceButton
│   ├── AddItemView
│   │   ├── ItemForm
│   │   └── CategorySelector (with create new)
│   ├── CategoryManagementView
│   │   └── CategoryList (edit/delete/create)
│   └── ArchiveView
│       └── ArchivedItemsList (with restore option)

• Categories list (cached)
• App settings (theme, sort preferences)

• UI state (modals, loading, errors)
• Form inputs
• Search/filter/sort parameters
• Item and occurrence data (fetched per view)

Use React Router or similar library for single-page app routing:

• / - Main item list (home)
• /items/:id - Item detail view
• /items/new - Add new item
• /categories - Category management
• /archive - Archived items

• Service worker caches app shell (HTML, CSS, JS)
• API requests queue when offline, sync when online
• Show offline indicator in UI
• Optimistic UI updates with rollback on sync failure

Breakdown format: X years, Y months, Z days

function calculateElapsedTime(occurrenceDate: string, currentDate: string) {
  const start = new Date(occurrenceDate);
  const end = new Date(currentDate);
  
  let years = end.getFullYear() - start.getFullYear();
  let months = end.getMonth() - start.getMonth();
  let days = end.getDate() - start.getDate();
  
  // Adjust for negative days
  if (days < 0) {
    months--;
    const prevMonth = new Date(end.getFullYear(), end.getMonth(), 0);
    days += prevMonth.getDate();
  }
  
  // Adjust for negative months
  if (months < 0) {
    years--;
    months += 12;
  }
  
  return { years, months, days };
}

• Show all three units: "1 year, 2 months, 5 days"
• Zero values included: "0 years, 0 months, 3 days"
• Singular/plural: "1 year" vs "2 years"
• Manual refresh: User can manually update times (no auto-refresh)

// Between two occurrences
function calculateInterval(date1: string, date2: string) {
  const d1 = new Date(date1);
  const d2 = new Date(date2);
  const diffMs = Math.abs(d2.getTime() - d1.getTime());
  const diffDays = Math.floor(diffMs / (1000 * 60 * 60 * 24));
  return diffDays;
}

// Average interval
function calculateAverageInterval(occurrences: Occurrence[]) {
  if (occurrences.length < 2) return null;
  
  const sortedDates = occurrences
    .map(o => o.occurrence_date)
    .sort();
  
  let totalDays = 0;
  for (let i = 1; i < sortedDates.length; i++) {
    totalDays += calculateInterval(sortedDates[i-1], sortedDates[i]);
  }
  
  return totalDays / (sortedDates.length - 1);
}

function calculateFrequency(occurrences: Occurrence[]) {
  if (occurrences.length < 2) return { perMonth: 0, perYear: 0 };
  
  const sortedDates = occurrences
    .map(o => new Date(o.occurrence_date))
    .sort((a, b) => a.getTime() - b.getTime());
  
  const firstDate = sortedDates[0];
  const lastDate = sortedDates[sortedDates.length - 1];
  const totalDays = calculateInterval(
    firstDate.toISOString().split('T')[0],
    lastDate.toISOString().split('T')[0]
  );
  
  const totalMonths = totalDays / 30.44; // Average days per month
  const totalYears = totalDays / 365.25; // Account for leap years
  
  return {
    perMonth: occurrences.length / totalMonths,
    perYear: occurrences.length / totalYears
  };
}

{
  "name": "When Was The Last Time",
  "short_name": "Last Time",
  "description": "Track when you last did things",
  "start_url": "/",
  "display": "standalone",
  "background_color": "#ffffff",
  "theme_color": "#4f46e5",
  "orientation": "portrait-primary",
  "icons": [
    {
      "src": "/icons/icon-192x192.png",
      "sizes": "192x192",
      "type": "image/png",
      "purpose": "any"
    },
    {
      "src": "/icons/icon-256x256.png",
      "sizes": "256x256",
      "type": "image/png",
      "purpose": "any"
    },
    {
      "src": "/icons/icon-384x384.png",
      "sizes": "384x384",
      "type": "image/png",
      "purpose": "any"
    },
    {
      "src": "/icons/icon-512x512.png",
      "sizes": "512x512",
      "type": "image/png",
      "purpose": "any"
    },
    {
      "src": "/icons/icon-512x512-maskable.png",
      "sizes": "512x512",
      "type": "image/png",
      "purpose": "maskable"
    }
  ],
  "categories": ["productivity", "utilities"],
  "lang": "en-US"
}

Caching Strategy:

• App Shell (HTML, CSS, JS): Cache-first with network fallback
• API Requests: Network-first with cache fallback
• Static Assets (icons, fonts): Cache-first

Basic Implementation:

const CACHE_NAME = 'lasttime-v1';
const APP_SHELL = [
  '/',
  '/index.html',
  '/index.js',
  '/styles.css',
  '/icons/icon-192x192.png',
  '/manifest.json'
];

// Install event - cache app shell
self.addEventListener('install', (event) => {
  event.waitUntil(
    caches.open(CACHE_NAME).then((cache) => {
      return cache.addAll(APP_SHELL);
    })
  );
  self.skipWaiting();
});

// Fetch event - serve from cache or network
self.addEventListener('fetch', (event) => {
  const { request } = event;
  const url = new URL(request.url);
  
  // API requests: network-first
  if (url.pathname.startsWith('/api/')) {
    event.respondWith(
      fetch(request)
        .then(response => {
          const responseClone = response.clone();
          caches.open(CACHE_NAME).then(cache => {
            cache.put(request, responseClone);
          });
          return response;
        })
        .catch(() => caches.match(request))
    );
  } else {
    // App shell: cache-first
    event.respondWith(
      caches.match(request).then(response => {
        return response || fetch(request);
      })
    );
  }
});

// Activate event - clean up old caches
self.addEventListener('activate', (event) => {
  event.waitUntil(
    caches.keys().then(keys => {
      return Promise.all(
        keys.filter(key => key !== CACHE_NAME)
          .map(key => caches.delete(key))
      );
    })
  );
  self.clients.claim();
});

if ('serviceWorker' in navigator) {
  window.addEventListener('load', () => {
    navigator.serviceWorker.register('/sw.js')
      .then(reg => console.log('SW registered:', reg))
      .catch(err => console.log('SW registration failed:', err));
  });
}

<!-- iOS-specific meta tags -->
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="default">
<meta name="apple-mobile-web-app-title" content="Last Time">
<link rel="apple-touch-icon" href="/icons/icon-192x192.png">

<!-- Android/Chrome -->
<meta name="mobile-web-app-capable" content="yes">
<meta name="theme-color" content="#4f46e5">

Important: This app has no authentication system. Consider it a personal, private app.

Security Implications:

• Anyone with the Val Town URL can access and modify data
• Treat the URL as a secret (like a password)
• Don't share the URL publicly
• Don't store sensitive personal information
• Best for personal use or trusted environments only

• Dates: ISO 8601 format (YYYY-MM-DD) validation
• Text fields: Sanitize HTML, max lengths enforced
• Category/item names: 100 character limit
• Notes: 1000 character limit
• No special characters that could break SQL

• Use parameterized queries exclusively
• Val Town's sqlite library handles this automatically:

await sqlite.execute({
  sql: 'select * from items where category_id = ?',
  args: [categoryId]
});

• API requests: Max 100 per minute per IP (Val Town handles this)
• Consider adding client-side throttling for expensive operations

• Allow only same-origin requests by default
• Val Town handles this automatically for Val endpoints

Since there's no authentication:

• Don't store: passwords, SSN, credit cards, medical info
• Safe to store: general activity tracking, non-sensitive notes
• Consider: who has physical access to devices using this app

Layout:

┌─────────────────────────────┐
│ [≡] Last Time      [+] [⚙] │  ← Header
├─────────────────────────────┤
│ 🔍 Search...                │  ← Search bar
│ [All Categories ▼] [Sort ▼]│  ← Filters
├─────────────────────────────┤
│ ┌─────────────────────────┐ │
│ │ Went to the gym         │ │
│ │ Health                  │ │
│ │ 0 years, 0 months, 3 days│ │
│ └─────────────────────────┘ │
│ ┌─────────────────────────┐ │
│ │ Watered plants          │ │
│ │ Home                    │ │
│ │ 0 years, 0 months, 7 days│ │
│ └─────────────────────────┘ │
│                             │
└─────────────────────────────┘

Interactions:

• Tap item → View detail
• Swipe item → Quick "Log now" action
• Pull to refresh → Update elapsed times
• Tap [+] → Add new item
• Tap [≡] → Open menu (Categories, Archive)

Layout:

┌─────────────────────────────┐
│ [←] Went to the gym    [⋮]  │
├─────────────────────────────┤
│ Health | Edit               │
│ Morning workout routine     │
├─────────────────────────────┤
│ 📊 STATISTICS               │
│ Average: 3.5 days           │
│ Longest: 7 days             │
│ Shortest: 1 day             │
│ Per month: 8.6              │
│ Per year: 104               │
├─────────────────────────────┤
│ 📅 HISTORY                  │
│ ┌─────────────────────────┐ │
│ │ Nov 13, 2024 (3 days)   │ │
│ │ Great workout!          │ │
│ │                    [✏][🗑]│ │
│ └─────────────────────────┘ │
│ ┌─────────────────────────┐ │
│ │ Nov 10, 2024 (2 days)   │ │
│ │                    [✏][🗑]│ │
│ └─────────────────────────┘ │
├─────────────────────────────┤
│ [✓ Log it now]              │
└─────────────────────────────┘

Interactions:

• Tap [✓ Log it now] → Log occurrence with today's date
• Tap [✏] on occurrence → Edit date/note
• Tap [🗑] on occurrence → Confirm delete
• Tap [⋮] → Options (Edit item, Archive, Delete)

Screen 1: Basic Info

┌─────────────────────────────┐
│ [×] Add New Item            │
├─────────────────────────────┤
│ When was the last time...   │
│ ┌─────────────────────────┐ │
│ │ I went to the gym       │ │
│ └─────────────────────────┘ │
│                             │
│ Category                    │
│ ┌─────────────────────────┐ │
│ │ Health              [▼] │ │
│ └─────────────────────────┘ │
│ [+ Create new category]     │
│                             │
│ Description (optional)      │
│ ┌─────────────────────────┐ │
│ │ Morning workout routine │ │
│ └─────────────────────────┘ │
│                             │
│ [Next]                      │
└─────────────────────────────┘

Screen 2: First Occurrence

┌─────────────────────────────┐
│ [←] Add First Occurrence    │
├─────────────────────────────┤
│ When did you last:          │
│ "Went to the gym"           │
│                             │
│ ○ Today                     │
│ ○ Set a date                │
│                             │
│ [Date picker]               │
│                             │
│ Note (optional)             │
│ ┌─────────────────────────┐ │
│ │                         │ │
│ └─────────────────────────┘ │
│                             │
│ [Create Item]               │
└─────────────────────────────┘

Search:

• Searches item names AND descriptions AND notes
• Real-time filtering as user types
• Show count: "5 results for 'gym'"
• Clear button (×) to reset search

Category Filter:

• Dropdown with all categories
• "All Categories" option to show everything
• Show item count per category in dropdown

Sort Options:

• Alphabetical (A-Z)
• Most recent (shortest time since last occurrence)
• Least recent (longest time since last occurrence)
• Persist sort preference in localStorage

• Research complete
• Set up Val Town account
• Create Val with folder structure
• Initialize SQLite database with schema
• Create database initialization script
• Test basic CRUD operations

• Create Hono server with routing
• Implement category endpoints
• Implement item endpoints
• Implement occurrence endpoints
• Add error handling and validation
• Test all endpoints with Postman/curl

• Set up React app structure
• Create routing system
• Build home screen with item list
• Implement time calculation utilities
• Create item detail view
• Build add item flow
• Implement search and filtering

• Category management UI
• Archive functionality
• Edit item/occurrence forms
• Statistics calculations
• Error handling and loading states
• Responsive design refinement

• Create manifest.json
• Generate app icons (all sizes)
• Implement service worker
• Test offline functionality
• Test installation on iOS and Android

• UI/UX refinement based on usage
• Performance optimization
• Cross-browser testing
• iOS PWA specific testing
• Documentation
• Deployment to production Val

• Export data feature (CSV/JSON)
• Import data feature
• Dark mode
• Multiple themes
• Data visualization (charts/graphs)
• Tags in addition to categories
• Bulk operations

• Zero infrastructure management
• Built-in SQLite
• Instant HTTPS deployment
• Automatic scaling
• Perfect for single-user PWAs

• Simplicity: Immediate access, no signup friction
• Personal use: Designed for individual tracking
• Privacy: No email collection or user data
• Speed: Faster development and simpler codebase
• Note: URL acts as the security - keep it private

• Requirement simplification per user specs
• Easier calculations (no timezone complexity)
• Sufficient granularity for use case
• Reduces data size

• Preserves historical data for statistics
• Allows users to restore accidentally deleted items
• "Deleted" items can still contribute to overall insights
• User requested this feature explicitly

• Simplifies UI/UX (no "uncategorized" state)
• Ensures data organization from start
• Easy to create categories during item creation
• Per user request for simplicity

• Indexes on all foreign keys and frequently queried fields
• Batch operations where possible
• Limit query results (pagination if needed)
• Use SQLite's EXPLAIN QUERY PLAN to optimize queries

• Lazy load components with React.lazy()
• Memoize expensive calculations (React.useMemo)
• Debounce search input (300ms delay)
• Virtual scrolling for long item lists (if >100 items)
• Optimize re-renders with React.memo()

• Cache categories in memory (they change rarely)
• Cache item list with timestamp, refresh on manual pull
• Invalidate cache after mutations
• Service worker caches static assets aggressively

• Minimize API calls by combining data in responses
• Use conditional requests (If-None-Match headers)
• Compress responses (Val Town handles this)
• Batch operations where appropriate

• Friendly error messages, no technical jargon
• Actionable guidance ("Check your internet connection")
• Toast notifications for transient errors
• Modal dialogs for critical errors
• Retry buttons where appropriate

• Console.error() for client-side errors
• Server-side logging to Val Town logs
• Include context (user ID, action attempted, timestamp)
• Never log sensitive data (emails, tokens)

• Show offline indicator when no connection
• Queue mutations for later sync
• Show optimistic UI updates
• Explain sync status to user

1. Add item → Log occurrence → View statistics
2. Search and filter functionality
3. Category management
4. Archive and restore
5. Offline functionality
6. Cross-browser compatibility
7. iOS PWA installation and usage

• Empty states (no items, no categories)
• Single occurrence (statistics should handle gracefully)
• Same-day multiple occurrences
• Date edge cases (leap years, month boundaries)
• Very old occurrences (years ago)
• Very large text in notes
• Network interruption during submission
• Very large numbers of items (100+)

• Val automatically deploys on save
• URL format: https://username-valname.val.run
• Environment variables: Set in Val Town UI
• Monitoring: Available in Val Town dashboard

• Can add custom domain in Val Town Pro
• Requires DNS configuration
• SSL automatically provisioned

• Use Git-style branches in Val Town
• Tag stable versions
• Keep changelog in README.md

Current design is single-user, but could scale:

• Add team/family sharing features
• Shared item ownership
• Permission system (view-only vs edit)
• User profiles

• Trends over time (am I doing X more or less?)
• Correlations (when I do X, I also do Y)
• Recommendations (you haven't done X in a while)
• Goal setting and tracking

• Google Calendar sync
• IFTTT/Zapier webhooks
• Health app data import
• SMS logging interface

This document should be sufficient to begin implementation with Claude Code or another AI coding assistant. The specification prioritizes clarity, completeness, and adherence to the user's requirements while incorporating best practices discovered through research.

Next Steps:

1. User provides UI mockups (optional but helpful)
2. Begin Phase 1 implementation
3. Iterate based on user feedback during development