feat: add durable database-backed ConversationMemory backends

[HYwan123]

• [*] I have looked for existing issues (including closed) about this

Feature Request

Add one or more durable database-backed implementations of ConversationMemory.

Rig already has rig-core::memory::ConversationMemory and an in-process
InMemoryConversationMemory backend, but there does not appear to be an
official durable backend for storing conversation history in a database.

This would let agents persist conversation memory across process restarts and
share memory across multiple app instances or workers.

Motivation

For production agents, in-memory conversation history is often not enough.

A database-backed memory backend would make it possible to:

• preserve conversation history across process restarts
• share memory across multiple app instances or workers
• use existing operational databases such as SQLite, Postgres, or MongoDB
• compose durable storage with existing rig-memory policies such as
  PolicyMemory, DemotingPolicyMemory, and CompactingMemory

ConversationMemory already provides the right lifecycle:

• load(conversation_id) before sending a prompt
• append(conversation_id, messages) after a successful turn
• clear(conversation_id) to delete stored history

Agents already integrate with this lifecycle through .memory(...), so this
request is mainly about providing reusable durable backends.

Proposal

Add a durable backend implementing ConversationMemory, starting with a small,
well-scoped backend such as SQLite or Postgres.

Possible API shape:

use rig::client::{CompletionClient, ProviderClient};
use rig::completion::Prompt;
use rig::providers::openai;
use rig_sqlite::SqliteConversationMemory;
// or: use rig_memory_sqlite::SqliteConversationMemory;

let memory = SqliteConversationMemory::new(pool_or_connection)
    .with_table("rig_conversation_messages")
    .migrate()
    .await?;

let agent = openai::Client::from_env()?
    .agent(openai::GPT_4O)
    .memory(memory)
    .build();

agent
    .prompt("My name is Alice.")
    .conversation("user-123")
    .await?;

A minimal schema could store:

• conversation_id
• monotonically increasing sequence number or creation timestamp
• serialized rig_core::completion::Message
• optional metadata/version column for forward compatibility

Suggested acceptance criteria:

• add at least one durable ConversationMemory backend
• implement load, append, and clear
• preserve per-conversation message order
• support concurrent appends without corrupting ordering
• map backend failures into MemoryError::Backend
• include tests using an in-memory or temporary database
• include a small example showing .memory(db_memory).conversation("...")
• document how this composes with existing rig-memory policies

Open design questions:

1. Should database-backed conversation memory live in rig-memory, or in the
   existing database crates such as rig-sqlite, rig-postgres, etc.?
2. Should the first implementation target SQLite for easier local/CI testing,
   or Postgres for production multi-worker deployments?
3. Should Rig provide a generic SQL memory abstraction, or keep each database
   backend independent like the existing vector store crates?
4. Should the backend auto-create/migrate its table, or require users to run
   migrations explicitly?
5. Is JSON serialization of Message acceptable as the first storage format,
   with a schema/version column for future compatibility?

Alternatives

Users can implement ConversationMemory themselves for their database, but
that leads to repeated custom implementations for a common production need.

Another option is to keep only InMemoryConversationMemory in Rig and document
how to build custom backends. The drawback is that durable memory is a common
enough use case that users may expect at least one official reference
implementation.

A third option is to store conversation history in a vector store, but that is
better suited for semantic recall than exact ordered chat history. A database
backend would preserve the full ordered message stream and still compose with
existing memory policies.

I am interested in implementing this if the maintainers agree on crate
placement and the first backend target.

[linear-code]

RIG-1358