chat-history

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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)

From netanel-abergel/pa-skills, MIT.

More from netanel-abergel/pa-skills