← Back to PRs

#19418: feat(persist-postgres): PostgreSQL persistence plugin + session date-range filtering

by supmo668 open 2026-02-17 19:38 View on GitHub →
docs app: macos app: web-ui gateway size: XL
## Summary PostgreSQL persistence plugin and session date-range filtering for OpenClaw. ### What - **`persist-postgres` extension**: New plugin that persists user prompts (`before_agent_start`) and assistant responses (`agent_end`) to PostgreSQL using `porsager/postgres` tagged template literals. - Auto-creates `lp_conversations` and `lp_messages` tables (PostgreSQL 13+) - Upserts conversations by `session_key`, increments `message_count` on insert - Reads `databaseUrl` from plugin config with `DATABASE_URL` env fallback - Cleans up connection pool on `gateway_stop` - Atomic insert+count via CTE (no drift on crash) - **Session `createdAt` tracking**: Adds `createdAt` field to `SessionEntry`, preserved through merges. Implements `createdAfter`/`createdBefore`/`updatedAfter`/`updatedBefore` filter parameters in `listSessionsFromStore` with TypeBox schema extensions for the gateway API. ### Why Enables durable, queryable conversation storage in PostgreSQL — useful for analytics, compliance, and multi-gateway deployments where the default file-based persistence is insufficient. Session date-range filtering supports time-bounded queries needed by the persistence layer and general session management. ### Greptile Review Fixes All three Greptile comments addressed: 1. **`conversation_id NOT NULL`** — Added `NOT NULL` to the FK column in `lp_messages` to prevent orphaned rows at the schema level. 2. **Channel derivation from session key** — Replaced hardcoded `"gateway"` channel with `deriveChannel()` that parses the channel from session keys (e.g., `agent:main:telegram:user123` → `"telegram"`). Falls back to `"unknown"` for non-standard keys. 3. **`createdAt` null → epoch 0 behavior** — Intentional and already tested ("sessions without createdAt are treated as createdAt=0"). Added inline documentation explaining the rationale: `createdAfter` correctly excludes legacy sessions, `createdBefore` correctly includes them. This is the expected UX for deployments migrating from pre-`createdAt` sessions. ## Testing - [x] Unit tests for persist-postgres plugin (201 lines) - [x] Gated e2e integration tests (350 lines, requires live PostgreSQL) - [x] 10 new date-range filter tests (39 total session tests) - [x] Lint passes (`pnpm check`) - [x] Rebased onto latest `origin/main`, lockfile conflict resolved ## AI Disclosure 🤖 AI-assisted: Implementation and review feedback addressed with AI tooling. All code reviewed and understood.

Most Similar PRs