#19462: feat(persist-postgres): PostgreSQL persistence plugin

## Summary Minimal PostgreSQL persistence plugin that records user prompts and assistant responses to PostgreSQL. Extension-only — no core schema changes. **Supersedes #16163** — this PR extracts only the `persist-postgres` extension from the original combined PR, dropping the core `SessionEntry.createdAt` / date-range filtering changes to keep scope atomic and low-risk. Session-level date filtering is deferred to a follow-up if/when core `createdAt` tracking lands. **Discussion**: #17785 ### What New `persist-postgres` extension that hooks into the gateway lifecycle: - **`before_agent_start`**: persists the user prompt (strips channel envelope headers via `stripEnvelope`, stores structured JSON when envelope is present) - **`agent_end`**: persists the last assistant response (extracts human-readable text via shared `extractTextFromChatContent`) - **`gateway_stop`**: cleanly closes the connection pool Implementation details: - Auto-creates `lp_conversations` and `lp_messages` tables (PostgreSQL 13+, `gen_random_uuid()`) - Upserts conversations by `session_key` with `ON CONFLICT` handling (updates both `channel` and `last_message_at`) - Atomic insert+count increment via CTE (single statement, no drift on crash) - `NOT NULL` FK constraint on `conversation_id` prevents orphaned messages - Channel derived from `ctx.messageProvider` with session-key parsing as fallback - Reads `databaseUrl` from plugin config with `DATABASE_URL` env fallback - Init error is cached — no retry storm on persistent DB failures ### Why Enables durable, queryable conversation storage in PostgreSQL — useful for analytics, compliance, and multi-gateway deployments where the default file-based persistence is insufficient. ### Scope Extension-only, zero core changes: - No modifications to `SessionEntry`, gateway protocol schemas, or session-utils - Date-range query helpers (`queryConversations`, `pgRowToSessionEntry`) deferred to a follow-up PR - Keeps the PR reviewable and low-risk ### Files | File | Purpose | |------|---------| | `openclaw.plugin.json` | Plugin config schema with `databaseUrl` UI hint | | `package.json` | Plugin manifest (`private: true`), `postgres` dependency | | `src/db.ts` | Schema DDL, upsert (with channel update), atomic insert | | `src/index.ts` | Plugin registration, lifecycle hooks, content extraction | | `src/plugin.test.ts` | 8 unit + 10 content-utility tests (no DB required) | | `src/integration.e2e.test.ts` | 5 CRUD e2e tests (requires live PostgreSQL) | | `src/plugin-sdk/index.ts` | Export `extractTextFromChatContent` and `stripEnvelope` for extensions | ### Commits 1. **`feat(plugin-sdk): export extractTextFromChatContent and stripEnvelope utilities`** — re-exports shared utilities so extensions can use them via `openclaw/plugin-sdk` 2. **`feat(persist-postgres): PostgreSQL persistence plugin`** — complete extension with all files ## Review feedback addressed All 4 items from the initial Greptile review have been incorporated: | Issue | Resolution | |-------|-----------| | Assistant content stored as raw JSON instead of text | Uses shared `extractTextFromChatContent` from plugin-sdk | | Redundant channel derivation from session key | Prefers `ctx.messageProvider` with `deriveChannel` as fallback | | `ON CONFLICT` doesn't update `channel` | Added `channel = EXCLUDED.channel` to upsert | | Missing `"private": true` | Added to `package.json` | ## Testing - [x] 8 unit tests — plugin registration, config validation, error handling - [x] 10 content utility tests — `extractTextFromChatContent` (string, array, mixed, edge cases) + `stripEnvelope` - [x] 5 gated e2e tests — CRUD, upsert conflict, atomic count, NOT NULL, CASCADE delete - [x] Lint passes ## AI Disclosure 🤖 AI-assisted: Implementation done with Claude Code. All code reviewed and understood. <!-- greptile_comment --> <h3>Greptile Summary</h3> Adds minimal PostgreSQL persistence plugin that records user prompts and assistant responses to PostgreSQL. This is an extension-only change with zero core modifications. - Uses shared `extractTextFromChatContent` and `stripEnvelope` utilities (newly exported from plugin-sdk) - Auto-creates schema (`lp_conversations`, `lp_messages`) with PostgreSQL 13+ support - Atomic insert+count with CTE prevents drift on crash - Prefers `ctx.messageProvider` over session-key parsing for channel derivation - All previous review feedback addressed (`channel` upsert update, `private: true`, content extraction) Issues found: - Envelope detection logic compares trimmed stripped text with raw prompt, causing false positives when prompts have leading/trailing whitespace but no envelope (low impact in practice) <h3>Confidence Score: 4/5</h3> - Safe to merge with one minor logical bug fix recommended - Extension-only change with no core modifications, thorough tests (18 tests total), and all previous review feedback addressed. One envelope detection edge case found (whitespace handling) but has minimal real-world impact. Schema design is solid (NOT NULL constraints, CASCADE deletes, atomic operations). Code follows project conventions. - extensions/persist-postgres/src/index.ts:74-76 has the envelope detection logic issue _{Last reviewed commit: 4f6f4da} <!-- greptile_other_comments_section --> <!-- /greptile_comment -->