chat-history

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

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

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

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

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)

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)

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

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

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."