# MikeSPA — Personal Assistant Agent
## Project Documentation

**Created:** 11 March 2026
**Last updated:** 26 March 2026 (auto-fail after nag limit; `fails?` command; re-done after fail)

---

## What It Does

MikeSPA is a WhatsApp-based personal assistant. It reads Google Calendar events, uses Claude AI (Anthropic API) to interpret them and compose natural-language messages, then delivers WhatsApp reminders via Twilio. It supports full two-way communication — you can reply to dismiss reminders, add items to lists, create calendar events, save notes, and send voice notes (transcribed via OpenAI Whisper).

**Multi-user:** The system supports multiple users sharing one Twilio sandbox. Each user has a row in the `users` table mapping their WhatsApp number to their Google Calendar ID and (optionally) a PWA login. All data — lists, alerts, dismissed events, clarifications — is isolated by `user_id` (the Twilio `From` number, e.g. `whatsapp:+447911123456`). Cron scripts loop over all active users; the webhook identifies the caller from their phone number.

### Schedules

| When | What | Format |
|---|---|---|
| Sunday 22:15 | Week ahead (Mon–Sun) with ref IDs | Single bundled summary |
| Daily 22:15 Mon–Sat | Tomorrow's full schedule with ref IDs | Single bundled summary |
| Daily 08:30 | Today's full schedule with ref IDs | Single bundled summary |
| Every 10 min (rolling) | **T-30 pre-alert** — event starting in ~30 mins | Per-event or bundled if multiple events at same time |
| Every 10 min (rolling) | **T+0 now-alert** — event starting now | Per-event or bundled |
| Every 10 min (rolling) | **T+30 nag** — event passed, not confirmed done | Per-event (skipped if `[CANCELLED]` in title) |

Events with `[CANCELLED]` in the title are skipped entirely by `pa_monitor.php` — no T-30, T+0, or T+30 alerts. They still appear in daily/weekly summaries (pa_agent.php). All-day events appear in daily/weekly summaries only — no T-30/T+0/T+30 alerts.

### Acknowledgement

Alerts can be resolved with two outcomes — both stop nagging immediately:

- **`done`** — event happened / task completed ✅
- **`fail`** — event didn't happen / task not happening ❌

The outcome is stored in `sent_alerts.outcome` for future reporting. Both set `acknowledged = 1` so the nag system stops.

Reply **"done"**, **"noted"**, **"dismiss"**, **"stop"**, **"ok"**, **"thanks"**, **"sorted"**, **"handled"**, or **"cheers"** to any reminder. The keyword **"fail"** (or **"failed"**) records the failure outcome. Common typos (e.g. "dimiss", "doen", "donr") are also recognised. Supports four targeting modes:

- **Default:** Resolves the most recent un-acknowledged alert
- **Ref targeting:** "done #47" or "fail #47" resolves a specific alert by its ref number
- **Multi-ref:** "done #125,#130,#129" resolves several alerts in one message (comma or space separated)
- **Reply-to-message:** Swipe a specific WhatsApp message and reply "done"/"fail" to resolve that message's events (uses Twilio's `OriginalRepliedMessageSid`)
- **Done/fail all:** "done all" or "fail all" resolves every overdue action item at once

Calendar queries show `[done]` or `[fail]` prefix against resolved events so you can see at a glance what happened.

### Action Items & Nag System

Events with summaries containing action-oriented words (e.g. "take", "call", "book", "check", "pay", "pills") are automatically flagged as action items. If an action item's event time passes and you haven't replied "done", `pa_monitor.php` sends up to 3 messages:

- **T-30 pre-alert:** ~30 minutes before event start
- **T+0 now-alert:** At event start time
- **T+30 nag:** ~30 minutes after event start — "Still to confirm: X. Done it? Reply 'done' or 'done #ref'"

**Maximum 3 messages per action item.** After the nag is sent, no further messages are sent. If no `done`/`fail` reply is received within 30 minutes of the nag, `pa_monitor.php` automatically marks the task as `outcome = 'fail'` and dismisses it — it will appear in `fails?` queries.

A task auto-failed this way can still be rescued: reply `done #ref` at any time to flip the outcome back to `done`.

The `nagged` column in `sent_alerts` is used as a counter (0 → 1 → 2). Nag alert rows are inserted with `nagged = 2` to prevent the nag itself from being re-nagged.

### Capture System (Two-Way Input)

Any WhatsApp message that isn't an acknowledgement keyword or direct command is routed through Claude's intent classifier (`ClaudeClient::routeCapture()`), which determines the action and passes it to `CaptureRouter` for execution. Supported intents:

| Intent | Trigger examples | What happens |
|---|---|---|
| `calendar` | "Dentist tomorrow at 14:00", "Dentist tomorrow at 14:00 in red" | Creates a Google Calendar event. Supports an optional colour name (e.g. "red", "blue", "green", "purple", "orange", "teal", "pink", "yellow", "grey") which maps to the matching Google Calendar colour ID |
| `calendar_multi` | "Add bank holiday 25 Apr and 26 Apr to calendar" | Creates multiple calendar events in a single message. Events can be all-day spans (e.g. "Easter break 18–21 Apr"). Shares the same colour support as `calendar` |
| `calendar_query` | "What's on today", "What have I got tomorrow", "Show next Friday", "Next week's events" | Reads calendar events for a given day or 7-day range and returns them as a formatted list. Events with an existing `sent_alerts` row show their ref ID (`[#87]`) and a `[done]` or `[fail]` prefix depending on how they were resolved |
| `list` | "Add milk to shopping" | Adds a single item to a named list |
| `list_multi` | "Add milk, eggs, bread to shopping" | Adds multiple items to a named list |
| `list_query` | "What's on my shopping list?" | Shows list contents (supports limit & random) |
| `list_filter` | "Any fruit on my shopping list?" | Semantic search within a list (uses a second Claude call) |
| `list_all` | "What lists do I have?" | Shows all active lists with item counts |
| `list_clear` | "Clear my shopping list" | Marks all items as done |
| `list_remove` | "Remove milk from shopping" | Marks a specific item as done |
| `list_remove_multi` | "Remove milk, eggs and bread from shopping" | Marks multiple specific items as done in one request; reports any items not found separately |
| `list_rollback` | "Undo", "Rollback" | Restores recently cleared/removed items |
| `note` | "Remember that Mike's birthday is in June" | Saves to the "notes" list |
| `clarify` | (Ambiguous input) | Asks user to choose between interpretations |
| `ignore` | (Conversational noise: "ok", "yes", "lol") | Does nothing — responds "Got it — nothing saved." |

The intent classifier no longer silently saves ambiguous input as notes. If Claude is unsure, it uses `clarify` to ask first (e.g. "Save to Notes" vs "Ignore"). The `note` intent is reserved for clearly intentional thoughts ("remember that...", "note to self...").

Voice notes are transcribed via OpenAI Whisper before being routed through the same intent classifier.

### Direct WhatsApp Commands

These are handled as keywords in `webhook.php` before reaching Claude's intent classifier:

| Command | What it does |
|---|---|
| `?` / `help` / `commands` | Shows a help menu of all commands |
| `done` | Resolves the most recent alert (outcome: completed) |
| `done #42` | Resolves a specific alert by ref number |
| `done #42,#43,#44` | Resolves multiple alerts in one message |
| `done all` | Resolves all overdue action items as completed |
| `fail #42` | Resolves a specific alert (outcome: failed/not happening) |
| `fail #42,#43` | Resolves multiple alerts as failed |
| `fail all` | Resolves all overdue action items as failed |
| `overdue` / `need doing` / `outstanding` | Shows overdue action items with ref numbers |
| `fails?` / `fails` / `what failed` | Shows all of today's tasks marked as failed (auto-failed or manually failed), with ref numbers and due times |
| `lists` / `my lists` | Shows lists with active items and IDs |
| `all lists` | Shows all lists including empty ones |
| `show #2` | Views a list by its ID |
| `clear #2` | Clears a list by its ID |
| `add to #2 item` | Adds item to a list by ID (skips Claude/clarify) |
| `add item to #2` | Same, reversed phrasing |
| `cancel #42` | Prepends `[CANCELLED]` to the event title on Google Calendar and dismisses all alerts for it — no further T-30/T+0/T+30 reminders will be sent |
| `#f message` | Strips the `#f ` prefix and proxies the full Twilio POST to the Food Log webhook (`https://food.naps.ai/food_webhook.php`). The food webhook receives the stripped body as if it had been sent directly. Note: `validate_twilio` must be `false` in the food webhook's `config.php` because Twilio's signature is signed against the PA webhook URL, not the food webhook URL |
| `1`–`9` | Answers a pending clarification question |

---

## Architecture

```
┌──────────────┐     ┌──────────────┐     ┌───────────┐     ┌──────────┐
│  Cron        │────>│ pa_agent.php │────>│ Claude API│────>│ Twilio   │──> WhatsApp
│ (5 cron jobs)│     │   (PHP CLI)  │────>│ (Sonnet)  │     │ WhatsApp │    to you
└──────────────┘     └──────┬───────┘     └───────────┘     └────┬─────┘
                             │                                     │
┌──────────────┐      ┌──────┴───────┐                      ┌──────┴──────┐
│  pa_nag.php  │─────>│    MySQL     │                      │ webhook.php │<── You reply
│ (nag script) │      │ (state/acks) │<─────────────────────│  (Apache)   │    on WhatsApp
└──────────────┘      └──────────────┘                      └─────────────┘
                                                                   │
                                                            ┌──────┴──────┐
                                                            │ Whisper API │ (voice notes)
                                                            └─────────────┘
```

---

## Server Details

- **Server:** AWS EC2 instance at `ip-172-31-33-116`
- **OS:** Ubuntu (with Apache + MySQL + PHP already running WordPress sites)
- **PHP version:** PHP 7 (do NOT use PHP 8 functions like `str_contains()`)
- **Timezone:** Set to `Europe/London` via `timedatectl`
- **Web server:** Apache2 with mod_ssl (Let's Encrypt)
- **Domain serving mikespa:** `gotohear.com` (SSL cert via Let's Encrypt)
- **Apache vhost:** `000-default-le-ssl.conf` → `ServerName gotohear.com` → DocumentRoot `/var/www/wordpress-platform`

---

## File Locations

### Application files

```
/var/www/wordpress-platform/mikespa/
├── pa_agent.php          # Main CLI entry point — morning, evening, weekly summaries
├── pa_monitor.php        # Rolling event monitor — T-30/T+0/T+30 alerts every 10 min
├── pa_nag.php            # [LEGACY] Nag script superseded by pa_monitor.php
├── webhook.php           # Twilio webhook endpoint (receives WhatsApp messages)
├── health.php            # Health check: https://gotohear.com/mikespa/health.php
├── config.php            # Live config (NOT in version control)
├── config.example.php    # Config template
├── database.php          # MySQL database layer (PDO)
├── calendar_client.php   # Google Calendar API client (pure cURL, JWT auth)
├── claude_client.php     # Anthropic Claude API client (cURL) — intent routing + summaries
├── capture_router.php    # Executes classified intents (calendar, list, note, etc.)
├── whatsapp_client.php   # Twilio WhatsApp client (cURL)
├── whisper_client.php    # OpenAI Whisper transcription client (voice notes)
├── logger.php            # Simple file logger
├── setup.php             # One-time database migration script
├── keep_import.php       # One-time utility: import Google Keep notes from a Takeout zip
├── takeout.zip           # Google Takeout archive (source data for keep_import.php — not in version control)
├── test_nag.php          # Test script for nag/dismiss flows (single, multi, bundle modes)
├── .htaccess             # Blocks web access to everything except webhook.php & health.php
├── google-credentials.json  # Google service account key (NOT in version control)
└── logs/
    ├── pa_agent.log      # Application log
    ├── webhook.log       # Incoming WhatsApp reply log
    └── cron.log          # Cron job output
```

### Key URLs

| URL | Purpose |
|---|---|
| `https://gotohear.com/mikespa/webhook.php` | Twilio posts WhatsApp messages here |
| `https://gotohear.com/mikespa/health.php` | Health check endpoint |

### Security

The `.htaccess` file blocks direct web access to all PHP files except `webhook.php` and `health.php`. Config files, credentials, and logs are not web-accessible.

---

## PWA (Progressive Web App)

A lightweight mobile web app for viewing and managing lists directly, without going through WhatsApp. Installable on Android/iOS as a home-screen app (standalone mode, no browser chrome).

### What it does

- Displays all active lists with item counts
- Open any list to see its items; tick items off as done; restore accidentally ticked items
- Add new items inline; edit existing items in place
- Reorder items via drag-and-drop within the same list
- Move items between different lists via cross-list drag-and-drop
- Create new lists; rename, recolour, pin, or archive existing lists
- Edit plain text notes
- Reads from and writes to the same MySQL database as the WhatsApp system — changes sync instantly in both directions

### Features

- **Full CRUD:** Create, rename, recolour, pin, archive, and delete lists
- **Item management:** Add, edit, tick/untick, and delete items
- **Drag-and-drop reordering:** Reorder items within a list using visual drag handles
- **Cross-list drag-and-drop:** Move items between different lists with visual feedback
- **Real-time sync:** Changes are immediately reflected in WhatsApp commands
- **Archive panel:** View and restore archived lists
- **Responsive design:** Works on mobile and desktop
- **Home-screen app:** Installable as a PWA with native app experience

### URLs

| URL | Purpose |
|---|---|
| `https://pwa.naps.ai` | Live PWA (login screen → list manager) |

### Server file locations

```
/var/www/mikespa-pwa/
├── index.html          # Single-page app (all HTML/CSS/JS in one file)
├── api.php             # REST API backend — all list/item CRUD
├── pwa_config.php      # Local config: mikespa_path + session_name (NOT web-accessible; no shared password)
├── manifest.json       # PWA manifest — app name "Lists", theme colour #1a73e8 (blue)
├── sw.js               # Service worker (enables offline/installable behaviour)
└── .htaccess           # Blocks direct web access to pwa_config.php
```

### Apache VirtualHost

Config file on server: `/etc/apache2/sites-available/mikespa-pwa.conf`

The live config (`mikespa-pwa.conf` in the workspace) points to `pwa.naps.ai` with DocumentRoot `/var/www/mikespa-pwa`. SSL cert via Let's Encrypt at `/etc/letsencrypt/live/pwa.naps.ai/`.

### Authentication

Per-user email + password login. The password is stored as a bcrypt hash in `users.pwa_password`. On success, a PHP session is created (`pwa_auth = true`, `pwa_user_id = 'whatsapp:+44...'`). All API calls except `login` and `logout` require an active session. Login attempts have a 1-second sleep to slow brute force.

To set a user's PWA password:
```bash
php -r "echo password_hash('yourpassword', PASSWORD_BCRYPT) . PHP_EOL;"
# Then:
UPDATE users SET pwa_password = '$2y$10$...' WHERE email = 'user@example.com';
```

`pwa_config.php` no longer contains a shared password — it only holds `mikespa_path` and `session_name`.

### Database connection

`api.php` reads `mikespa_path` from `pwa_config.php`, then `require`s the main `config.php` from the mikespa folder to get DB credentials. There is no separate database config — it shares the same MySQL connection details.

```
pwa_config.php → mikespa_path = /var/www/wordpress-platform/mikespa
                              → config.php → DB host/name/user/password
```

### API actions (api.php)

All reads are GET `?action=...`, all writes are POST with JSON body `{ "action": "...", ... }`.

| Action | Method | Purpose |
|---|---|---|
| `login` / `logout` | POST | Session management |
| `lists` | GET | All non-archived lists with active item counts |
| `add_list` | POST | Create a new list |
| `update_list` | POST | Rename, recolour, or pin a list |
| `archive_list` | POST | Archive a list (hides it from the UI) |
| `update_note` | POST | Save text content for a plain-text note list |
| `items` | GET | All items for a list (optionally include done items) |
| `add_item` | POST | Add an item to a list (source = `pwa`) |
| `update_item` | POST | Edit item text |
| `done_item` | POST | Mark item done |
| `restore_item` | POST | Un-tick a done item |
| `delete_item` | POST | Mark item done (same as done — preserves rollback from WhatsApp) |
| `reorder_items` | POST | Save new sort order after drag-and-drop |
| `move_item` | POST | Move an item from one list to another (cross-list drag-and-drop) |

### Installing as a home-screen app

**Android (Chrome):** Open `https://pwa.naps.ai` → tap ⋮ menu → *Add to Home screen*

**iOS (Safari):** Open `https://pwa.naps.ai` → tap Share → *Add to Home Screen*

The app installs under the name **"Lists"** (from `manifest.json` short_name). Icons require `icon-192.png` and `icon-512.png` in the document root — without them the app installs with a default browser icon.

### Apache logs

```
/var/log/apache2/mikespa-pwa-error.log
/var/log/apache2/mikespa-pwa-ssl-error.log
/var/log/apache2/mikespa-pwa-access.log
/var/log/apache2/mikespa-pwa-ssl-access.log
```

---

## Accounts & Services

### Google Cloud (Calendar API)

- **Console:** https://console.cloud.google.com
- **Project:** `mikespa`
- **API enabled:** Google Calendar API
- **Auth method:** Service account (JSON key file)
- **Service account email:** `mikespa@mikespa.iam.gserviceaccount.com`
- **Calendar shared with:** The service account email, permission "Make changes to events" (needed for event creation)
- **Calendar ID in config:** `miguel.butler.ink@gmail.com`
- **Scopes requested:** `calendar.events` (read/write) + `calendar.readonly`

### Anthropic (Claude API)

- **Console:** https://console.anthropic.com
- **Organisation:** ICC Digital Ltd
- **Workspace:** Default
- **Tier:** Tier 1
- **Model used:** `claude-sonnet-4-20250514`
- **API key name:** `mikespa` (regenerated after accidental exposure during initial setup)
- **Billing:** Prepaid credits ($12 purchased 11 Mar 2026)
- **Monthly spend limit:** $100
- **Note:** Credits took several hours to activate after first purchase. If this happens again, wait and retry. Contact support at support.anthropic.com if it persists.

### OpenAI (Whisper — voice transcription)

- **Console:** https://platform.openai.com
- **Model used:** `whisper-1`
- **Cost:** ~$0.006 per minute of audio — negligible
- **Used for:** Transcribing WhatsApp voice notes before routing through intent classifier

### Twilio (WhatsApp)

- **Console:** https://console.twilio.com
- **WhatsApp mode:** Sandbox (free for testing)
- **Sandbox number:** `whatsapp:+14155238886`
- **User numbers:** stored in `users.user_id` (one row per user). `config.php` no longer has a `whatsapp_to` field.
- **Webhook URL:** `https://gotohear.com/mikespa/webhook.php` (POST)
- **Sandbox limitation:** Expires after 72 hours of inactivity. Each user must re-send the `join <code>` message to reactivate. For permanent use, register a proper WhatsApp number (~£1/month). Only change needed: update `whatsapp_from` in `config.php` and get Meta approval — no other code changes.

### Claude.ai (this chat interface)

- **Account:** mikeb@gotohear.com
- **Plan:** Pro subscription (£18-30/month, separate from API credits)
- **Calendar connected:** Yes (Google Calendar via connectors — used to create events from chat)

---

## MySQL Database

- **Database name:** `mikespa`
- **Database user:** `mikespa` (dedicated user, not shared with WordPress)
- **Host:** `localhost`
- **Created using:** The WordPress database user (`aurora`) which had CREATE DATABASE privileges

### Tables

**`users`** — registered users (added for multi-user support)

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Surrogate PK |
| user_id | VARCHAR(50) | WhatsApp phone in Twilio format: `whatsapp:+447911123456` (UNIQUE) |
| name | VARCHAR(100) | Display name (e.g. "Mike") |
| email | VARCHAR(200) | Email address for PWA login (UNIQUE) |
| calendar_id | VARCHAR(200) | Google Calendar ID shared with the service account (e.g. `user@gmail.com`) |
| pwa_password | VARCHAR(255) | bcrypt hash for PWA login; NULL = no PWA access |
| active | TINYINT(1) | 0 = disabled; 1 = active |
| created_at | DATETIME | Row creation time |

To add a user: insert a row into `users`, and ensure their Google Calendar is shared with `mikespa@mikespa.iam.gserviceaccount.com` ("Make changes to events"). They join the Twilio sandbox normally (`join <code>`).

Run `migrate_multiuser.sql` to create this table on an existing install.

---

**`sent_alerts`** — tracks every reminder and nag sent

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key (also used as ref number for "done #47") |
| event_id | VARCHAR(255) | Google Calendar event ID |
| event_summary | VARCHAR(500) | Event title |
| event_date | VARCHAR(20) | Date key for deduplication |
| alert_type | VARCHAR(30) | Which schedule sent it. Legacy: `weekly`, `evening`, `upcoming-morning`, `upcoming-afternoon`, `upcoming-lateevening`, `nag`. Target: `weekly`, `evening`, `morning`, `pre_alert`, `now_alert`, `nag` |
| sent_at | DATETIME | When the alert was sent |
| message_sid | VARCHAR(64) | Twilio message SID (shared across bundled messages) |
| acknowledged | TINYINT(1) | Whether user resolved the alert (1 = resolved, regardless of outcome) |
| acknowledged_at | DATETIME | When resolved |
| outcome | VARCHAR(10) | How it was resolved: `done` = completed ✅, `fail` = didn't happen ❌, NULL = unresolved |
| is_action | TINYINT(1) | Whether this event was flagged as an action item |
| nagged | TINYINT(1) | Nag counter: 0 = not nagged, 1 = nagged once, 2 = nagged twice or is a nag row itself |
| event_start | DATETIME | Event start time (used by nag script to determine overdue status) |
| user_id | VARCHAR(50) | Owning user (WhatsApp number). Added by `migrate_multiuser.sql`. |

**`dismissed_events`** — events the user has dismissed

| Column | Type | Purpose |
|---|---|---|
| user_id | VARCHAR(50) | Owning user (part of composite PK) |
| event_id | VARCHAR(255) | Google Calendar event ID (part of composite PK) |
| event_summary | VARCHAR(500) | Event title |
| dismissed_at | DATETIME | When dismissed |

Primary key is `(user_id, event_id)` — two users can dismiss the same shared event independently.

**`lists`** — normalised list names with slugs for fuzzy matching

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key (used as list ID in "clear #2", "show #3") |
| user_id | VARCHAR(50) | Owning user. UNIQUE index on `(user_id, slug)` — two users can have same-named lists. |
| name | VARCHAR(200) | Display name (e.g. "libby's birthday") — title-cased for Keep imports, lowercased for WhatsApp-created lists |
| slug | VARCHAR(200) | Normalised for matching — apostrophes/quotes stripped, lowercased, dots preserved (e.g. "libbys birthday", "naps.ai ideas") |
| color | VARCHAR(20) | Keep note colour (e.g. "DEFAULT", "BLUE") — default "DEFAULT" |
| pinned | TINYINT(1) | Whether pinned in Keep — default 0 |
| archived | TINYINT(1) | Whether archived in Keep — default 0; archived lists are excluded from "lists" and "all lists" queries |
| is_note | TINYINT(1) | Whether this is a Keep text note (not a checklist) — default 0; text notes are excluded from list queries |
| text_content | MEDIUMTEXT | Full text body for Keep text notes (NULL for checklists) |
| created_at | DATETIME | When the list was first created |
| updated_at | DATETIME | Last edit timestamp from Keep (NULL for WhatsApp-created lists) |

**`items`** — stores list items (shopping, tasks, notes, ideas, etc.)

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key |
| list_name | VARCHAR(200) | List name (kept for backwards compatibility) |
| list_id | INT | Foreign key to `lists.id` — used for all queries |
| content | TEXT | Item content |
| sort_order | INT | Display ordering — WhatsApp items append at end; Keep imports preserve Keep's original order |
| added_at | DATETIME | When added |
| done | TINYINT(1) | Whether completed/removed |
| done_at | DATETIME | When marked done |
| source | VARCHAR(20) | How it was added: "text", "voice", "webhook", "keep" |

**`list_annotations`** — embedded URLs from Google Keep web clips / annotations

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key |
| list_id | INT | Foreign key to `lists.id` |
| url | VARCHAR(2000) | The clipped URL |
| title | VARCHAR(500) | Page title (from Keep annotation) |
| description | TEXT | Page description (from Keep annotation) |
| added_at | DATETIME | When imported |

**`list_attachments`** — image files attached to Keep notes

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key |
| list_id | INT | Foreign key to `lists.id` |
| item_id | INT | Optional foreign key to `items.id` (NULL if attached at note level) |
| filename | VARCHAR(255) | Original filename from Keep |
| mimetype | VARCHAR(100) | MIME type (default "image/jpeg") |
| file_data | MEDIUMBLOB | Binary image data |
| added_at | DATETIME | When imported |

**`pending_clarifications`** — stores multi-option questions awaiting a numeric reply

| Column | Type | Purpose |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary key |
| user_id | VARCHAR(50) | Owning user — clarifications are per-user |
| options | TEXT | JSON blob with question and option intents |
| created_at | DATETIME | When created |
| resolved | TINYINT(1) | Whether the user has replied |

### List name resolution

List names are normalised to slugs for matching. This means "Libby's birthday", "libbys birthday", and "LIBBY'S BIRTHDAY" all resolve to the same list (slug: "libbys birthday"). Apostrophes, quotes, and case differences are stripped during slugification. **Dots are preserved** so domain names like "naps.ai" stay intact as "naps.ai ideas" rather than being mangled.

There are two resolution methods in `database.php`:
- `resolveList()` — standard method used by WhatsApp capture; creates new lists with a lowercased display name.
- `resolveListPreserveCase()` — used by the Keep importer; creates new lists with the original title-casing preserved (e.g. "Libby's Birthday" stays as-is). Also returns an `exists` boolean so the importer knows whether to skip re-importing items.

---

## Cron Jobs

```cron
# PA Agent - Weekly summary Sunday 22:15
15 22 * * 0 /usr/bin/php /var/www/wordpress-platform/mikespa/pa_agent.php weekly >> /var/www/wordpress-platform/mikespa/logs/cron.log 2>&1

# PA Agent - Evening summary (tomorrow) Mon-Sat 22:15
15 22 * * 1-6 /usr/bin/php /var/www/wordpress-platform/mikespa/pa_agent.php evening >> /var/www/wordpress-platform/mikespa/logs/cron.log 2>&1

# PA Agent - Morning summary (today) daily 08:30
30 8 * * * /usr/bin/php /var/www/wordpress-platform/mikespa/pa_agent.php morning >> /var/www/wordpress-platform/mikespa/logs/cron.log 2>&1

# PA Event Monitor - T-30/T+0/T+30 rolling alerts, every 10 minutes
*/10 * * * * /usr/bin/php /var/www/wordpress-platform/mikespa/pa_monitor.php >> /var/www/wordpress-platform/mikespa/logs/cron.log 2>&1

# PA Agent - Cleanup old records monthly
0 3 1 * * /usr/bin/php /var/www/wordpress-platform/mikespa/pa_agent.php cleanup >> /var/www/wordpress-platform/mikespa/logs/cron.log 2>&1
```

Server timezone is `Europe/London` which handles GMT/BST automatically.

---

## CLI Commands

All commands run from the mikespa directory:

```bash
cd /var/www/wordpress-platform/mikespa

php pa_agent.php weekly          # Send weekly summary (Mon-Sun ahead) — 22:15 Sun
php pa_agent.php evening         # Send tomorrow's full schedule with IDs — 22:15 Mon-Sat
php pa_agent.php morning         # Send today's full schedule with IDs — 08:30 daily
php pa_agent.php upcoming        # [LEGACY] Window-based reminders — superseded by pa_monitor.php
php pa_agent.php overdue         # Show overdue items in terminal
php pa_agent.php test            # Send a test WhatsApp message
php pa_agent.php test-calendar   # Test Google Calendar access
php pa_agent.php cleanup         # Remove records older than 90 days

php pa_monitor.php               # Run the T-30/T+0/T+30 event monitor manually
php pa_nag.php                   # [LEGACY] Run the nag check manually

php test_nag.php                 # Send a single fake nag (test dismiss flow)
php test_nag.php multi           # Send 2 fake nags (test ref targeting)
php test_nag.php bundle          # Send a bundled alert with 2 events (test batch dismiss)

php keep_import.php              # Import Google Keep from takeout.zip in same directory
php keep_import.php /path/to/takeout.zip          # Explicit zip path
php keep_import.php /path/to/Takeout/Keep         # Pre-extracted Keep directory
php keep_import.php --dry-run    # Preview what would be imported, no writes
php keep_import.php --force      # Re-import items for existing lists (clears and replaces)
```

Note: `upcoming` and `pa_nag.php` are legacy commands superseded by `pa_monitor.php`. The new cron schedule uses `morning`, `evening`, `weekly` (pa_agent.php) and `pa_monitor.php` for rolling alerts.

---

## How It Works (Flow)

### Sending daily summaries (cron → you)

1. Cron triggers `pa_agent.php` with `weekly`, `evening`, or `morning`
2. Script fetches events from Google Calendar for the relevant date range
3. **Pre-inserts** a `sent_alerts` row for each event to obtain the auto-increment ref ID
4. Passes the events + their ref IDs to Claude, which composes one natural WhatsApp message listing all events with their ref numbers (e.g. `#45 — Physio at 11:00`)
5. Message sent via Twilio; all events in the bundle share the same `message_sid`
6. `sent_alerts` rows updated with the actual Twilio SID
7. You can pre-dismiss anything from the summary: "done #45" dismisses before the day even starts

### Event monitor flow — T-30 / T+0 / T+30 (cron → you)

`pa_monitor.php` runs every 10 minutes and processes three alert types for **all** calendar events (no keyword filtering):

**T-30 pre-alert** — fires ~30 minutes before the event starts:
1. Queries for events with `event_start` between `now + 20 mins` and `now + 50 mins`
2. Skips events already in `dismissed_events` or that already have a `pre_alert` row in `sent_alerts`
3. Multiple events in the same window are bundled into one message, each with its ref ID
4. Message sent: *"Coming up: Physio at 11:00 [#45]"*

**T+0 now-alert** — fires at approximately event start time:
1. Queries for events with `event_start` between `now - 10 mins` and `now + 5 mins`
2. Skips dismissed or already-sent `now_alert` rows
3. Message sent: *"Starting now: Physio at 11:00 [#45]"*

**T+30 nag** — fires ~30 minutes after the event:
1. Queries for events with `event_start` between `now - 50 mins` and `now - 20 mins`
2. Skips dismissed, already-nagged, or all-day events
3. **Skips events with `[CANCELLED]` in the title** — no T-30, T+0, or T+30 sent
4. Message sent: *"Still to confirm: Physio (11:00). Done it? Reply 'done' or 'done #45'"*

Replying "done" to any of these dismisses the event — the remaining alert types in the chain are automatically skipped because the event enters `dismissed_events`.

### Capture flow (you → action)

1. You send a WhatsApp message (text or voice note)
2. Webhook receives it via Twilio POST
3. If it's a voice note, audio is downloaded from Twilio (with retry for delayed availability) and transcribed via Whisper
4. Text is checked against direct command keywords first (done, help, overdue, lists, clear #N, show #N)
5. If a numeric reply (1-9) matches a pending clarification, executes the chosen option
6. Otherwise, text is sent to `ClaudeClient::routeCapture()` which returns a structured intent JSON
7. `CaptureRouter::execute()` processes the intent: creates calendar events, queries the calendar, adds/queries/clears lists, saves notes, etc.
8. If Claude is unsure, it uses `clarify` to ask (e.g. "Save to Notes" vs "Ignore") rather than silently saving
9. Confirmation message is returned via TwiML response

### Receiving ack replies (you → resolve)

1. You reply to a WhatsApp message (e.g. "done", "done #47", "done #47,#48,#49", "done all", "fail #47", "fail all", or swipe-reply + "done"/"fail")
2. Twilio POSTs the reply to the webhook
3. **Priority 0:** If "done all"/"fail all", resolves every overdue action item with the given outcome
4. **Priority 1:** If "done #47"/"fail #47" or multi-ref "done #47,#48,#49", resolves each specified alert by ref ID
5. **Priority 2:** If reply-to-message (swipe), uses `OriginalRepliedMessageSid` to find the alert. Nag replies resolve the single event; bundled message replies resolve the whole batch
6. **Priority 3:** Falls back to most recent un-acknowledged alert and resolves it
7. Sends a confirmation reply (e.g. "Got it — [done] #322, #323, #326." or "Got it — 'Physio' [ref:47] marked as failed.")
8. Future cron runs and nag checks skip dismissed events

---

## Dependencies

**Zero external PHP dependencies.** Everything uses built-in PHP + cURL. No Composer, no vendor directory, no package management.

| Component | How it's used |
|---|---|
| PHP 7 (CLI + Apache mod) | Runs the agent and webhook |
| cURL (PHP extension) | All external API calls (Google, Claude, Twilio, Whisper) |
| PDO + MySQL | Database layer |
| Apache + mod_ssl | Serves the webhook endpoint |
| Let's Encrypt | SSL certificate for gotohear.com |

---

## Known Issues & Gotchas

1. **Twilio sandbox expires after 72 hours of inactivity.** Re-send the join code to reactivate. Consider upgrading to a permanent number for ~£1/month.

2. **Anthropic API credits can take hours to activate** after first purchase. If you get "credit balance too low" errors, wait and retry. Contact support@anthropic.com if it persists.

3. **Recurring events:** The "Take morning pills" events were created as individual events, not a recurring series. For long-term use, create a proper recurring calendar event instead.

4. **"done" without ref resolves the most recent alert.** A plain "done" or "fail" resolves the single most recent un-acknowledged event. Use "done #ref" / "fail #ref" to target a specific event, "done #ref1,#ref2,#ref3" to resolve several at once, swipe-reply to resolve a whole bundle, or "done all" / "fail all" to clear everything overdue.

5. **Holiday/birthday calendars** (like Mother's Day) are not picked up by the service account. It only reads events from your personal calendar that you've explicitly shared with the service account.

6. **PHP 7 constraint.** The server runs PHP 7 — do not use PHP 8 functions like `str_contains()`, `str_starts_with()`, `match`, named arguments, etc. These cause silent parse failures. Check Apache error logs (`/var/log/apache2/error.log`) if webhook stops responding.

7. **Curly/smart quotes in generated code.** Code generation (including from Claude) can introduce typographic quotes (`""` `''`) into PHP string delimiters, causing parse errors. Always verify generated PHP has straight quotes. Apache error logs are the diagnostic path.

8. **Twilio voice note delay.** Media files are sometimes not immediately available when the webhook fires. The Whisper client retries up to 3 times with a 2-second delay to handle this.

9. **Temporary debug logging in webhook.php.** Lines near the top of `webhook.php` log the full `$_POST` keys and values on every incoming message — useful for confirming Twilio reply-context fields but verbose. Remove once reply-to-message behaviour is confirmed working.

11. **`fails` / `fail?` command never fires.** In `webhook.php` the fails-keyword check (lines ~159–183) runs before `$bodyLower` is defined (line ~191). Because `$bodyLower` is undefined at the point of the check PHP silently uses an empty string, so `in_array('', $failsKeywords)` always returns false — the command is unreachable. Fix: move the `$bodyLower = strtolower(trim($body));` assignment to immediately after `$body` is extracted (line ~93), above the STEP 0 `#f` check. The same change is needed for the `overdue` keyword check which also references `$bodyLower` before it is set — but `overdue` appears after line 191 so that one works correctly.

10. **WhatsApp message length limit (~1600 chars).** Twilio silently drops WhatsApp messages that exceed ~1600 characters. `webhook.php` handles this with `chunkMessage()` (splits at 1500 chars on newline/word boundaries) and `sendTwiml()`. Single-chunk messages go out via TwiML as normal. Multi-chunk messages are sent via the outbound REST API with a 750ms delay between sends (to preserve delivery order), and `webhook.php` returns an empty `<Response/>` to Twilio. The chunking functions use `strlen()`/`substr()`/`strrpos()` (not their `mb_*` equivalents) to avoid a dependency on PHP's `mbstring` extension — byte-counting is safe given the generous 100-byte buffer below the limit.

---

## Google Keep Import

`keep_import.php` is a one-time utility script that migrates existing Google Keep notes into MikeSPA's database so they're available via WhatsApp commands alongside anything added via WhatsApp.

### How to run

1. Request a Google Takeout export at https://takeout.google.com — select only Google Keep
2. Download the resulting zip and place it as `takeout.zip` in the mikespa directory
3. Run `php keep_import.php` (or `php keep_import.php --dry-run` to preview first)

### What gets imported

- **Checklists** → imported as list items (`source = 'keep'`); checked items arrive as `done = 1`, unchecked as `done = 0`
- **Text notes** → imported as a list with `is_note = 1` and `text_content` set; they don't appear in regular list queries
- **Archived notes** → imported with `archived = 1` on the list; excluded from all WhatsApp list queries
- **Trashed notes** → skipped entirely
- **List metadata** → colour, pinned status, archived status, and last-edited timestamp are stored on the `lists` row
- **Annotations (web clips)** → stored in `list_annotations` with URL, title, and description
- **Image attachments** → stored as binary data in `list_attachments`

### Idempotency

The import is safe to run multiple times. If a list already exists (matched by slug), its metadata is updated but items are **not** re-imported — so anything you've added via WhatsApp since the last import is preserved. Use `--force` to clear and re-import items for existing lists.

### Zip handling

Uses PHP's `ZipArchive` extension if available; falls back to the system `unzip` command. You can also extract the zip yourself and pass the `Keep` directory path directly.

---

## Future Enhancements Discussed

- **Event monitor redesign (`pa_monitor.php`):** Built 18 Mar 2026. Replaces legacy window-based `upcoming` cron + `pa_nag.php` with a single 10-minute rolling monitor sending T-30/T+0/T+30 alerts for ALL calendar events. All-day events and `[CANCELLED]`-prefixed events skip the T+30 nag. Daily summaries (22:15 evening, 08:30 morning) pre-insert `sent_alerts` rows so ref IDs appear in the composed message. Weekly summary now runs at 22:15 Sunday.
- **PWA drag-and-drop:** Built 24 Mar 2026. Added HTML5 drag-and-drop for reordering items within lists and moving items between lists. Includes visual feedback (blue/red highlighting), drag handles, and proper event handling. Backend supports `reorder_items` and `move_item` API endpoints.
- **Intent-aware undo/rollback:** Use a `list_ops` table storing Twilio message SIDs alongside list operations, so "undo" via reply-to-message restores the specific operation rather than blindly the most recent one (agreed design, not yet built)
- **Email triage:** Add IMAP scanner to pull important emails into the same pipeline
- **Invoice reminders:** Scan a folder for PDFs with due dates
- **Paid WhatsApp number:** Upgrade from Twilio sandbox to a permanent number (~£1/month). Only change: update `whatsapp_from` in `config.php` and get Meta approval — no code changes needed

---

## Troubleshooting

| Issue | Fix |
|---|---|
| No calendar events found | Check service account has calendar access. Run `php pa_agent.php test-calendar` |
| WhatsApp not delivering | Check Twilio sandbox hasn't expired (re-send join code). Check logs |
| Webhook replies not working | Test with `curl -X POST https://gotohear.com/mikespa/webhook.php`. Check webhook.log |
| Claude API errors | Check API key and balance at console.anthropic.com |
| Wrong timezone on messages | Run `timedatectl` to check server TZ is Europe/London |
| Cron not firing | Check `crontab -l` and inspect `/var/www/wordpress-platform/mikespa/logs/cron.log` |
| Nag not sending | Check `pa_nag.php` is in crontab. Verify `is_action=1`, `nagged<2`, `acknowledged=0` in `sent_alerts` |
| Voice notes failing | Check OpenAI API key in config.php. Check Whisper logs for HTTP errors |
| PHP parse errors | Check Apache error log. Look for curly quotes or PHP 8 syntax in generated code |
| List commands not working | Check Claude API is responding. Check webhook.log for intent JSON |
| List name not matching | Check `lists` table slugs. Run `SELECT * FROM lists;` to see stored names |

### Log locations

```
/var/www/wordpress-platform/mikespa/logs/pa_agent.log   # Main app log
/var/www/wordpress-platform/mikespa/logs/webhook.log    # Incoming WhatsApp messages
/var/www/wordpress-platform/mikespa/logs/cron.log       # Cron output (all jobs)
/var/log/apache2/error.log                              # Apache/PHP parse errors
```