#19418: feat(persist-postgres): PostgreSQL persistence plugin + session date-range filtering
docs
app: macos
app: web-ui
gateway
size: XL
Cluster:
Mattermost and Zulip Enhancements
## 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
#19462: feat(persist-postgres): PostgreSQL persistence plugin
by supmo668 · 2026-02-17
84.7%
#19948: feat: add PostHog LLM Analytics extension
by andrewm4894 · 2026-02-18
70.2%
#12296: security: persistence-only secret redaction for session transcripts
by akoscz · 2026-02-09
70.1%
#18595: feat: native PostgreSQL + pgvector memory backend
by IrriVisionTechnologies · 2026-02-16
68.7%
#12884: Feature/named persistent sessions
by dylanb · 2026-02-09
68.5%
#9011: fix(session): auto-recovery for corrupted tool responses [AI-assisted]
by cheenu1092-oss · 2026-02-04
68.1%
#14309: fix(ui): resolve chat event session key mismatch
by justonlyforyou · 2026-02-11
67.8%
#13889: feat: Slack channel cache, session cost alerts & checkpoint/recover...
by trevorgordon981 · 2026-02-11
67.6%
#13540: feat: persist channel histories to disk
by carrotRakko · 2026-02-10
67.3%
#12358: fix: Sessions format timestamps in sessions_history using userTimezone
by xialonglee · 2026-02-09
67.0%