c4pt0r/my-claw-dash.md

## my-claw-dash.md

      
    Raw
  

              my-claw-dash.md
            
          
    my-claw-dash (openclaw-db9-audit) plugin — Installation, Uninstallation, and User Guide

my-claw-dash plugin can:
Mirrors OpenClaw runtime data to DB9 for auditing and analysis. It continuously syncs session transcripts and agent runtime events into DB9 (PostgreSQL + DB9 FS).
Stores conversations in structured SQL tables. This allows you to run queries on sessions, messages, tool calls, and usage patterns using standard SQL.
Logs detailed agent events as append-only JSONL files. Tool calls, lifecycle events, and execution traces are written chronologically for debugging and investigation.
Provides operational tooling via CLI. Commands like status, doctor, init, and backfill help manage state, diagnose issues, and import historical transcripts.
Generates analytics about your agent usage. After installation it analyzes historical runs (performance, commands, activity patterns, message stats) to help understand how your agents behave.
This document is intended to be shared directly with end users. It is self-contained and does not rely on any other repository documentation.
The plugin is distributed on npm as:

openclaw-db9-audit — https://www.npmjs.com/package/openclaw-db9-audit


1) Installation

1.1 Prerequisites


OpenClaw >= 2026.3.2
db9 database (https://db9.ai/), you should install db9 via: https://db9.ai/skill.md
db9 cli and sdk (npm: get-db9)

To confirm your OpenClaw version, run:
openclaw --version
1.2 Install from npm (recommended)

Install the plugin using OpenClaw’s plugin manager:
openclaw plugins install openclaw-db9-audit
After installation, OpenClaw will prompt you to restart the gateway to load plugins. Restart your OpenClaw gateway process to apply the change.
Optional (recommended for controlled environments):

Install a specific version:

openclaw plugins install openclaw-db9-audit@<version>

Pin the resolved npm artifact/version in your OpenClaw config (prevents accidental drift on reinstall):

openclaw plugins install --pin openclaw-db9-audit
1.3 Verify installation

List enabled plugins:
openclaw plugins list --enabled
Show details for this plugin (plugin id is openclaw-db9-audit):
openclaw plugins info openclaw-db9-audit
If the plugin is loaded, the following command should display the plugin’s CLI help:
openclaw db9-audit --help
1.4 Update the plugin (optional)

For npm-installed plugins, you can update using:
openclaw plugins update openclaw-db9-audit

2) Uninstallation

2.1 Disable the plugin (optional)

If you want to stop the plugin without removing it:
openclaw plugins disable openclaw-db9-audit
Restart the OpenClaw gateway to apply.
2.2 Uninstall the plugin

To uninstall the plugin:
openclaw plugins uninstall openclaw-db9-audit
Restart the OpenClaw gateway to apply.
Notes:

The uninstall target is the plugin id (openclaw-db9-audit), not the npm package name (openclaw-db9-audit).
If you want a preview of what will be removed:

openclaw plugins uninstall openclaw-db9-audit --dry-run
2.3 Remove local plugin state (optional)

The plugin stores DB9 access state locally under your OpenClaw state directory. If you want to fully reset/clean up local state (for example, after credential rotation), remove the plugin state directory and then restart OpenClaw:

Default OpenClaw state directory: ~/.openclaw (unless overridden by OPENCLAW_STATE_DIR)
Plugin state path (relative to the state directory): plugins/db9-audit/state.json

Important: treat this file as sensitive (see the Security section below).

3) What the plugin does (overview)

DB9 Audit is an OpenClaw plugin (plugin id: openclaw-db9-audit, CLI root command: db9-audit) that mirrors key runtime data to DB9 for auditing and troubleshooting.
It captures two primary categories of data:


Session transcripts

OpenClaw session transcripts (your local conversation history) are mirrored to DB9 PostgreSQL to support structured queries and analysis.


Agent runtime events

OpenClaw agent events (lifecycle/tool/assistant/error streams) are appended as JSONL lines to DB9 FS (WebSocket-backed filesystem) for chronological auditing and investigation.


High-level characteristics:

Self-provisioning: on first run, the plugin can automatically create a dedicated DB9 database for auditing.
Low disruption: the plugin operates asynchronously and does not replace OpenClaw’s native local transcript persistence.
Operational tooling: the plugin provides CLI commands to check status, run diagnostics, initialize/reset state, and backfill historical transcripts.
Resource ownership: the plugin may provision DB9 resources under your account/environment. Ensure this aligns with your organization’s access control, retention, and cost policies.

Important operational note:

The plugin runs inside the OpenClaw gateway process. Installing/uninstalling/configuring the plugin requires restarting the gateway to take effect.


4) Configuration

4.1 Where to configure

The plugin is configured through your OpenClaw config file (JSON5). Typical locations:

Default: ~/.openclaw/openclaw.json
If OPENCLAW_STATE_DIR is set: $OPENCLAW_STATE_DIR/openclaw.json

4.2 Minimal configuration (recommended starting point)

In most cases, you can start with an enabled entry and rely on defaults:
{
  plugins: {
    entries: {
      "openclaw-db9-audit": {
        enabled: true,
        config: {}
      }
    }
  }
}
After editing configuration, restart the OpenClaw gateway.
4.3 Full configuration reference

Add/override these fields under plugins.entries["openclaw-db9-audit"].config:
{
  plugins: {
    entries: {
      "openclaw-db9-audit": {
        enabled: true,
        config: {
          enabled: true,

          apiBase: "https://db9.ai/api",
          databaseName: "openclaw-audit-myteam",
          // databaseRegion: "us-east",

          schema: "openclaw_audit",
          logRoot: "/logs",

          batchSize: 100,
          flushIntervalMs: 1000,
          backfillOnStart: true,

          redact: {
            enabled: true,
            maxFieldBytes: 65536
          }
        }
      }
    }
  }
}
Field meanings (user-facing):

enabled (boolean): plugin logic toggle (the plugin may still be loaded, but will not actively mirror data).
apiBase (string): DB9 Control Plane API base URL.
databaseName (string): name used when the plugin provisions the DB9 audit database on first run.
databaseRegion (string, optional): region hint used only during the initial database creation.
schema (string): PostgreSQL schema name used for audit tables.
logRoot (string): root directory on DB9 FS where JSONL event logs are appended.
batchSize (integer): batching size for transcript sync and FS log flush.
flushIntervalMs (integer): periodic flush interval (milliseconds).
backfillOnStart (boolean): whether to scan and enqueue existing local transcripts on startup.
redact.enabled (boolean): enables best-effort redaction of common sensitive fields.
redact.maxFieldBytes (integer): truncation limit for large string fields (UTF‑8 bytes).

Notes:

databaseName and databaseRegion are primarily used during initial provisioning. After the first successful run, the plugin will reuse its existing DB9 state unless you remove/reset it.
If you change schema or logRoot, new data will be written to the new locations; previously written data is not automatically migrated.


5) How to use the plugin (CLI commands)

The plugin provides a dedicated command group:
openclaw db9-audit --help
5.1 Status (recommended first check)

openclaw db9-audit status
This command reports (at a high level):

Whether local DB9 state is present
Whether DB9 Control Plane access appears healthy
Whether DB9 PostgreSQL and DB9 FS are reachable

5.2 Initialize state

In most setups, the plugin provisions state automatically when the gateway starts. You can also initialize explicitly:
openclaw db9-audit init
To rebuild state forcibly (use with care):
openclaw db9-audit init --force
5.3 Diagnostics

openclaw db9-audit doctor
Use this when status checks fail; it provides additional diagnostic context.
5.4 Backfill historical transcripts

openclaw db9-audit backfill
openclaw db9-audit backfill --agent main
openclaw db9-audit backfill --since 2026-03-01
openclaw db9-audit backfill --reset-offsets
Use backfill when:

You enabled the plugin after you already had existing session transcripts locally.
You temporarily disabled backfill on startup and want to run it manually.


6) Runtime behavior (what to expect)


Asynchronous mirroring: data is mirrored in the background and may not appear in DB9 immediately.
Connectivity and retries: temporary DB9 connectivity issues may delay mirroring; the plugin will generally resume once connectivity is restored.
Event logs are append-only: in rare failure scenarios, event log lines may be duplicated due to retries. Treat the FS event log as an audit trail, not a strictly de-duplicated ledger.


7) Security and privacy considerations

6.1 Local credentials

The plugin stores DB9 access state locally so it can continue syncing without re-provisioning every time. This state contains sensitive credentials.
Recommendations:

Do not commit, copy, or share the plugin’s local state file.
Ensure only intended operators have access to the machine/user account running OpenClaw.
If you suspect leakage, rotate credentials in DB9 as appropriate and rebuild plugin state.

6.2 Redaction (best-effort)

Redaction is enabled by default. It is designed to reduce accidental exposure of common secrets but is not a substitute for proper access control and data governance.

8) Troubleshooting

“openclaw db9-audit command not found”

This typically indicates the plugin is not loaded.

Verify it is installed and enabled:

openclaw plugins list --enabled
openclaw plugins info openclaw-db9-audit

Restart the OpenClaw gateway to load plugins.

Status/doctor reports DB9 connectivity failures

Common causes include:

Incorrect apiBase
Network/firewall restrictions
DB9 environment not reachable from the OpenClaw host

“Changing databaseName did not take effect”

databaseName is primarily used during the initial provisioning. If you need to provision a new DB9 audit database, rebuild plugin state (carefully):
openclaw db9-audit init --force
Then restart the gateway.
Onboard UX

Onboard UX
After installation completes, the plugin first displays the Backfill progress.
Once the backfill is finished, it prints an initial analysis report.
Example report template:

Agent Run Duration Ranking ⏱️
a1442d0c | 61.55 sec  ← longest run
a41d2626 | 29.93 sec
d2f2c1bd | 27.94 sec
Most Frequently Executed Commands 🔧
gh api repos/c4pt0r/db9-backend/issues... | 49 times
gh api repos/c4pt0r/db9-server/issues...  | 49 times
psql -h pg.db9.io...                      | 17 times

→ Your GitHub issue-scanning cron runs quite frequently 😄

Activity Distribution by Time of Day 📊
Hour 4–5 (UTC) most active | ████████████████████████████████
Hour 0 least active        | █

→ Converted to PST, that’s around 8–9 PM

Words I Say Most Often 🗣️
NO_REPLY     | 66 times  (silence is golden)
@hal9000     | 54 times  (GitHub scanner)
HEARTBEAT_OK | 16 times  (heartbeat confirmation)
Longest Reply 📝
857 characters — the SQL query example I just showed you
Conversation Turns 🔄
Average: 11.8 turns per session
Minimum: 8 turns
Maximum: 167 turns (this current session!)
Who Talks More? 💬
Role	Avg Length	Total Characters
user	728	79K
assistant	103	12K

→ Your inputs are 7× longer than my responses 😅

Most Frequently Read Files 📂
github-hal9000-processed.json | 50 times

→ This appears to be the status file for the GitHub scanning cron

Tool Invocation Chains 🔗
exec → exec   | 264 times  (running commands consecutively)
exec → read   |  78 times  (run command, then check output)
read → write  |   5 times  (read something and then write)
Session Lifetime ⏱️
Current session: 282 minutes (4.7 hours!)
Other sessions: mostly < 1 minute
Message Length Distribution 📊
0–50 chars   | ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓ 245
50–200       | ▓▓▓▓▓▓▓▓▓▓ 144
500–1K       | ▓▓▓▓▓▓▓ 106
5K+          | ▓ 3
Who Pastes Code? 💻
user:      88%   ← files/code you pasted
assistant:  9%   ← code I generated
Question Ratio ❓
73% of user messages contain ? or ？

→ You are mostly asking questions, and I’m mostly answering them

Time to First Token (TTFT) ⚡
Average: 10.33 sec
Fastest: 2.62 sec
Slowest: 66.20 sec (possibly running complex tools)
Emoji Usage 🎭
assistant: 9 emoji
user:      0 (or my regex didn’t match them)

Query Examples:
Here are all the SQL queries used to generate the oh-my-dash report:
-- 1. Overview Stats
SELECT
(SELECT COUNT() FROM openclaw_audit.audit_sessions) as total_sessions,
(SELECT COUNT() FROM openclaw_audit.audit_messages) as total_messages,
(SELECT COUNT(*) FROM openclaw_audit.audit_runs) as total_runs;
-- 2. Agent Run Duration Ranking (using fs9 + JSONL parsing)
WITH lifecycle AS (
SELECT jsonb_array_elements(
('[' || REPLACE(
extensions.fs9_read('/logs/lifecycle/2026-03-07/main.jsonl'),
E'\n', ','
) || '{}]')::jsonb
) as event
),
runs AS (
SELECT
event->>'runId' as run_id,
event->'data'->>'phase' as phase,
(event->>'ts')::bigint as ts
FROM lifecycle
WHERE event->>'runId' IS NOT NULL
)
SELECT
LEFT(r1.run_id, 8) as run,
ROUND((r2.ts - r1.ts) / 1000.0, 2) as duration_sec
FROM runs r1
JOIN runs r2 ON r1.run_id = r2.run_id
WHERE r1.phase = 'start' AND r2.phase = 'end'
ORDER BY duration_sec DESC
LIMIT 5;
-- 3. Most Frequently Executed Commands (from fs9 tool logs)
WITH logs AS (
SELECT jsonb_array_elements(
('[' || REPLACE(
extensions.fs9_read('/logs/tool/2026-03-07/main.jsonl'),
E'\n', ','
) || '{}]')::jsonb
) as event
)
SELECT
LEFT(event->'data'->'args'->>'command', 55) as command,
COUNT(*) as times
FROM logs
WHERE event->'data'->>'name' = 'exec'
AND event->'data'->>'phase' = 'start'
AND event->'data'->'args'->>'command' IS NOT NULL
GROUP BY 1
ORDER BY times DESC
LIMIT 8;
-- 4. Activity Distribution by Hour (with ASCII bar chart)
SELECT
EXTRACT(HOUR FROM created_at)::int as hour,
COUNT() as messages,
REPEAT('█', LEAST((COUNT() / 10)::int, 40)) as bar
FROM openclaw_audit.audit_messages
WHERE created_at IS NOT NULL
GROUP BY 1
ORDER BY 1;
-- 5. Role Stats (who talks more)
SELECT
role,
COUNT(*) as msgs,
ROUND(AVG(LENGTH(content_text))) as avg_len,
SUM(LENGTH(content_text)) as total_chars
FROM openclaw_audit.audit_messages
WHERE role IN ('user', 'assistant') AND content_text IS NOT NULL
GROUP BY role;
-- 6. Tool Invocation Frequency
SELECT
tool_name,
COUNT(*) as calls
FROM openclaw_audit.audit_messages
WHERE tool_name IS NOT NULL
GROUP BY tool_name
ORDER BY calls DESC
LIMIT 6;
-- 7. Session Lifetime (longest running sessions)
SELECT
LEFT(session_key, 40) as session,
ROUND((MAX(EXTRACT(EPOCH FROM created_at)) -
MIN(EXTRACT(EPOCH FROM created_at))) / 60, 1) as minutes,
COUNT(*) as msgs
FROM openclaw_audit.audit_messages
WHERE created_at IS NOT NULL
GROUP BY session_key
ORDER BY minutes DESC
LIMIT 3;
-- 8. Message Length Distribution (histogram)
SELECT
CASE
WHEN len < 50 THEN '0-50'
WHEN len < 200 THEN '50-200'
WHEN len < 500 THEN '200-500'
WHEN len < 1000 THEN '500-1K'
WHEN len < 5000 THEN '1K-5K'
ELSE '5K+'
END as bucket,
COUNT() as cnt,
REPEAT('▓', LEAST(COUNT()::int / 5, 30)) as bar
FROM (
SELECT LENGTH(content_text) as len
FROM openclaw_audit.audit_messages
WHERE content_text IS NOT NULL
) t
GROUP BY 1
ORDER BY MIN(len);
-- 9. Top Words in Assistant Responses (word frequency analysis)
SELECT
word,
COUNT(*) as freq
FROM (
SELECT UNNEST(REGEXP_SPLIT_TO_ARRAY(LOWER(content_text), '\s+')) as word
FROM openclaw_audit.audit_messages
WHERE role = 'assistant' AND content_text IS NOT NULL
) t
WHERE LENGTH(word) > 3
AND word !~ '^[0-9-|]+$'
AND word NOT IN ('the', 'and', 'for', 'that', 'this', 'with',
'from', 'have', 'your', 'are', 'not', 'but',
'will', 'can', 'all', 'been', 'has', 'was', 'were',
'null', 'true', 'false')
GROUP BY word
ORDER BY freq DESC
LIMIT 10;
-- 10. Question Ratio (percentage of user messages with ?)
SELECT
COUNT() FILTER (WHERE content_text LIKE '%？%' OR content_text LIKE '%?%') as with_question,
COUNT() as total,
ROUND(COUNT() FILTER (WHERE content_text LIKE '%？%' OR content_text LIKE '%?%') * 100.0 / COUNT(), 1) as pct
FROM openclaw_audit.audit_messages
WHERE role = 'user';
-- 11. Code Block Ratio (who pastes code)
SELECT
role,
COUNT(*) as with_code
FROM openclaw_audit.audit_messages
WHERE content_text LIKE '%```%'
GROUP BY role;
-- 12. Emoji Usage
SELECT
role,
COUNT(*) as with_emoji
FROM openclaw_audit.audit_messages
WHERE content_text ~ '[😀-🙏🌀-🗿✅❌⚡📊🔧💬🔗⏱️📂💻❓🎭🗣️📝🔄]'
GROUP BY role;
-- 13. List fs9 directories
SELECT * FROM extensions.fs9('/logs');
-- 14. Read fs9 file content
SELECT extensions.fs9_read('/logs/tool/2026-03-07/main.jsonl');
Connection:
PGPASSWORD='xxx' psql -h pg.db9.io -p 5433 

-U .admin -d postgres
No results found