Chat History Search

Search and reference past conversations from the audit log database.

⚠️ Two Databases — Know the Difference

There are TWO PostgreSQL databases on port 15432:

For message search: always use the messages table.

Database Connection

• Host: 127.0.0.1, Port: 15432, User: postgres, DB: openclaw_audit
• psql: LC_ALL=C /opt/homebrew/Cellar/postgresql@18/18.2/bin/psql -h 127.0.0.1 -p 15432 -U postgres -d openclaw_audit
• Important: Must use PG 18 binary and LC_ALL=C prefix

Messages Table Schema

Indexes

• Full-text search: idx_messages_body_fts (GIN on to_tsvector('simple', body))
• By chat + time: idx_messages_chat (chat_id, ts)
• By sender: idx_messages_sender (sender_phone)
• By time: idx_messages_ts (ts)
• Unique message_id: idx_messages_unique_id

How to Run Queries

LC_ALL=C /opt/homebrew/Cellar/postgresql@18/18.2/bin/psql -h 127.0.0.1 -p 15432 -U postgres -d openclaw_audit -c "QUERY"

Always add LIMIT. Start with 20, increase if needed.

Query Patterns

Full-text search (preferred for keyword searches)

SELECT id, ts, chat_name, sender_name, is_from_me, LEFT(body, 200), message_id
FROM messages
WHERE to_tsvector('simple', body) @@ plainto_tsquery('simple', 'search terms')
ORDER BY ts DESC LIMIT 20;

Search by chat

-- Roy's direct messages
SELECT id, ts, LEFT(body, 200) FROM messages 
WHERE chat_id = '+972542440470' AND chat_type = 'direct'
ORDER BY ts DESC LIMIT 20;

-- A specific group
SELECT id, ts, sender_name, LEFT(body, 200) FROM messages 
WHERE chat_id = '[email protected]'
ORDER BY ts DESC LIMIT 20;

Search by date range

SELECT id, ts, chat_name, sender_name, is_from_me, LEFT(body, 200)
FROM messages WHERE ts BETWEEN '2026-02-20' AND '2026-02-21'
ORDER BY ts LIMIT 50;

ILIKE search (for phrases or partial matches)

SELECT id, ts, chat_name, sender_name, is_from_me, LEFT(body, 200)
FROM messages WHERE body ILIKE '%exact phrase%'
ORDER BY ts DESC LIMIT 20;

Get conversation context around a message

SELECT id, ts, chat_name, sender_name, is_from_me, LEFT(body, 300)
FROM messages WHERE id BETWEEN (TARGET_ID - 5) AND (TARGET_ID + 5)
ORDER BY ts;

List all chats

SELECT chat_id, chat_type, chat_name, COUNT(*) as msgs,
  MIN(ts) as first_msg, MAX(ts) as last_msg
FROM messages GROUP BY chat_id, chat_type, chat_name
ORDER BY msgs DESC;

Replying to Past Messages

When you find a message to reference, use message_id:

• Include [[reply_to:\x3Cmessage_id>]] in your response for a native WhatsApp reply

Known Limitations

• Messages before Feb 18, 2026 use old ingest format (chat_id from JSONL metadata)
• Messages from Feb 18+ use ingest-v2 (chat_id from gateway.log correlation)
• Sub-agent sessions show as unknown-* chat_id (no gateway log match)
• NO_REPLY / HEARTBEAT_OK messages are filtered out during ingest

Tips

• is_from_me = true → Nova sent it
• is_from_me = false → a human sent it
• For group chats, chat_name has the group name
• sender_name = 'assistant' → Nova's outbound messages
• Always respect child safety rules — never reveal info about Ben