Postgres Conversation Persistence in an Agent

Context

The agent stored conversation history as a JSON file inside the pod. It worked — until the pod restarted. Any in-flight conversation was gone. Kubernetes is not a filesystem, so the fix was migrating to Postgres, which was already running via CloudNativePG.

The constraint: the agent code couldn't change how it accessed storage. Same interface, swapped backend.

Interface

ConversationStore

The agent used a simple storage interface. The JSON implementation wrote to disk; the Postgres implementation hits a table. Same contract, different driver.

type ConversationStore interface {
    Get(sessionID string) ([]Message, error)
    Append(sessionID string, msg Message) error
    Delete(sessionID string) error
}

Why this matters: keeping storage behind an interface meant the agent logic never needed to know where history lives. The swap was a one-line config change.

The migration

Schema

CREATE TABLE conversations (
  id          BIGSERIAL PRIMARY KEY,
  session_id  TEXT        NOT NULL,
  role        TEXT        NOT NULL,
  content     TEXT        NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_conv_session ON conversations (session_id, created_at);

The detail that bites you: define all columns inside CREATE TABLE first, then run CREATE INDEX as a separate statement. If you try to declare an index mid-migration before the table exists in full, Postgres returns a parse error that's easy to misread as a column type issue. Split them — table first, indexes after.

Postgres implementation

func (s *PgStore) Get(sessionID string) ([]Message, error) {
    rows, err := s.db.Query(`
        SELECT role, content FROM conversations
        WHERE session_id = $1
        ORDER BY created_at ASC`, sessionID)
    // scan into []Message...
}

func (s *PgStore) Append(sessionID string, msg Message) error {
    _, err := s.db.Exec(`
        INSERT INTO conversations (session_id, role, content)
        VALUES ($1, $2, $3)`,
        sessionID, msg.Role, msg.Content)
    return err
}

Lessons

JSON in a pod is not persistence. It's a convenience that works until the first restart. If the data matters, use a database from day one.

Columns before indexes, always. Write migrations as two blocks: table definition, then indexes. Never inline index creation with column declarations in a migration script.

Abstract storage on day one. If the first version had hit Postgres directly, the migration would have been harder. The interface made it a config swap.

CloudNativePG made this cheap. Postgres was already there via CNPG. Adding a table and a secret was the whole migration — no new infra.