---
name: chat-history
description: "Search past WhatsApp/chat conversations stored in the audit log PostgreSQL database. Use when the user asks about past conversations, what was discussed, what someone said, finding a specific message, or referencing previous discussions. Also use to reply to or quote specific past messages."
---

# Chat History Skill

Search past WhatsApp messages stored in the audit log DB (`messages` table), with file-based fallback when DB is unavailable.

## Minimum Model
Any model. Operations are lookup/query based.

---

## Step 1: Detect PA_DB_URL availability

```python
import os, sys

def get_db_conn():
    url = os.environ.get('PA_DB_URL') or os.environ.get('HELENI_DB_URL')
    if not url:
        return None
    try:
        import psycopg2
        conn = psycopg2.connect(url)
        return conn
    except Exception:
        return None

conn = get_db_conn()
DB_AVAILABLE = conn is not None
```

---

## DB Mode (PA_DB_URL available)

All queries against the `messages` table. Output format: `timestamp | sender | chat | body`.

### Full-text search
```python
import os, psycopg2

url = os.environ.get('PA_DB_URL') or os.environ.get('HELENI_DB_URL')
conn = psycopg2.connect(url)
cur = conn.cursor()

search_term = "budget"  # replace with actual term

cur.execute("""
    SELECT ts, sender_name, chat_name, body
    FROM messages
    WHERE body ILIKE %s
    ORDER BY ts DESC
    LIMIT 20
""", (f'%{search_term}%',))

for ts, sender, chat, body in cur.fetchall():
    print(f"{ts} | {sender} | {chat} | {body[:100]}")

conn.close()
```

### Search by contact name or phone
```python
cur.execute("""
    SELECT ts, sender_name, chat_name, body
    FROM messages
    WHERE sender_name ILIKE %s OR sender_phone ILIKE %s OR chat_name ILIKE %s
    ORDER BY ts DESC
    LIMIT 20
""", (f'%{contact}%', f'%{contact}%', f'%{contact}%'))
```

### Search by date range
```python
cur.execute("""
    SELECT ts, sender_name, chat_name, body
    FROM messages
    WHERE ts BETWEEN %s AND %s
    ORDER BY ts ASC
    LIMIT 100
""", ('2026-04-01', '2026-04-07'))
```

### Find recent unanswered messages (last 24h)
```python
cur.execute("""
    SELECT chat_id, chat_name, MAX(ts) as last_msg, COUNT(*) as msg_count
    FROM messages
    WHERE is_from_me = false
      AND ts > NOW() - INTERVAL '24 hours'
      AND chat_id NOT IN (
          SELECT DISTINCT chat_id FROM messages
          WHERE is_from_me = true
            AND ts > NOW() - INTERVAL '24 hours'
      )
    GROUP BY chat_id, chat_name
    ORDER BY last_msg DESC
""")

for chat_id, chat_name, last_msg, count in cur.fetchall():
    print(f"{last_msg} | {chat_name or chat_id} | {count} unanswered")
```

### DB stats (quick overview)
```python
cur.execute("""
    SELECT
        COUNT(*) as total,
        COUNT(*) FILTER (WHERE ts > NOW() - INTERVAL '1 day') as today,
        MIN(ts) as first_msg,
        MAX(ts) as last_msg
    FROM messages
""")
row = cur.fetchone()
print(f"Total: {row[0]} | Today: {row[1]} | Range: {row[2]} → {row[3]}")
```

---

## File Fallback (no DB)

When `PA_DB_URL` is not set or DB is unreachable, search the file-based context files.

### Search context files with grep
```bash
SEARCH_TERM="budget"
WHATSAPP_DIR="/path/to/workspace/memory/whatsapp"

grep -r --include="*.md" -l "$SEARCH_TERM" "$WHATSAPP_DIR" | while read file; do
    echo "=== $file ==="
    grep -n "$SEARCH_TERM" "$file" | head -5
done
```

### Search by contact/group name
```bash
CONTACT="John"  # replace with actual contact name
find "$WHATSAPP_DIR" -name "meta.json" | xargs grep -l "$CONTACT" | while read meta; do
    dir=$(dirname "$meta")
    echo "=== $(cat $meta | python3 -c 'import sys,json; d=json.load(sys.stdin); print(d.get(\"name\",\"unknown\"))') ==="
    cat "$dir/context.md" | tail -20
done
```

---

## Output Format

Always present results as:
```
[2026-04-06 18:32] Daniel (Core Team) → "Can we review the budget today?"
[2026-04-06 18:45] Heleni (Core Team) → "Sure, I'll set it up"
```

If no results found → say "No messages found matching that query."


---
Source attribution: netanel-abergel
Original project: https://github.com/netanel-abergel/pa-skills/blob/ca4344f33175b78797352bb9e8cdec864eff492d/skills/chat-history-local/SKILL.md
License: MIT
License information: https://opensource.org/license/mit
Original upstream instructions; downloaded from Dave AI.

## Upstream license notice

MIT License

Copyright (c) 2025 Netanel Abergel

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.



# Dave AI resource metadata

Creator / publisher: netanel-abergel
Original project: https://github.com/netanel-abergel/pa-skills/blob/ca4344f33175b78797352bb9e8cdec864eff492d/skills/chat-history-local/SKILL.md
Attribution basis: Source repository record

Fields of work: Sales, Customer Support
Roles: Customer Success Manager, Sales Development Rep (SDR), Support Agent
Specialties: Database
Tools: PostgreSQL
Country applicability: Country not specified
Classification basis: Source catalog labels
Instruction SHA-256: 3a1c239b7e84897b91d1b799186554d99682ed2c170320f5a154cb9bc61e01df