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:
| Database | Table | Purpose | Use when |
|---|
openclaw_audit | messages | WhatsApp/chat messages — who said what, when, in which chat | Searching conversations, finding what someone said, quoting messages |
openclaw_audit | audit_log | LLM API costs — model usage, tokens, cost per call | Checking spending, model usage stats, cost analysis |
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
| Column | Type | Description |
|---|
| id | bigint | Auto-increment PK |
| ts | timestamptz | Message timestamp |
| message_id | text | WhatsApp message ID (use for reply_to) |
| chat_id | text | Chat identifier (+972... for direct, ...@g.us for groups) |
| chat_type | text | direct / group / device / unknown |
| chat_name | text | Group name or chat label |
| sender_phone | text | Sender phone number |
| sender_name | text | Sender display name / 'assistant' for Nova |
| body | text | Message text content |
| media_type | text | image/audio/etc or null |
| is_from_me | boolean | true = assistant's messages |
| session_key | text | OpenClaw session UUID |
| tokens_in | integer | Input tokens (assistant msgs only) |
| tokens_out | integer | Output tokens (assistant msgs only) |
| cost_usd | numeric | Cost of response |
| model | varchar(80) | Model used |
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 = '120363423630333430@g.us'
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:<message_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 itis_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
