Postgrex-backed Raxol.Agent.ThreadLog adapter.

Persists events in a PostgreSQL table so agents on separate processes or BEAM nodes can share a durable thread log. payload and metadata are stored as bytea via :erlang.term_to_binary/1; recorded_at is a real timestamptz.

Optional dependency

Postgrex is declared optional: true. Consumers must add :postgrex to their own deps and start a connection.

Config

%{
  conn: MyApp.Postgrex,
  table: "raxol_agent_threads"
}

:table defaults to "raxol_agent_threads". Table name is validated against a safe-identifier regex; config-level injection is not possible.

Schema

Run the SQL returned by create_table_sql/1 once:

iex> Raxol.Agent.ThreadLog.Postgrex.create_table_sql()
"""
CREATE TABLE IF NOT EXISTS raxol_agent_threads (
  thread_id   text NOT NULL,
  sequence    bigint NOT NULL,
  kind        text NOT NULL,
  payload     bytea,
  metadata    bytea NOT NULL,
  recorded_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (thread_id, sequence)
);
CREATE INDEX IF NOT EXISTS raxol_agent_threads_kind_idx
  ON raxol_agent_threads (thread_id, kind, sequence);
"""

The secondary index on (thread_id, kind, sequence) makes list_by_kind/4 an index-only scan.

Sequence allocation

append/5 uses INSERT ... VALUES (..., COALESCE((SELECT MAX(sequence) + 1 FROM <table> WHERE thread_id = $1), 0), ...) inside a single statement. This is racy between concurrent writers to the same thread_id: two writers can both read MAX = N and both try to insert at N + 1, producing a unique_violation on the second.

The adapter catches unique_violation and retries up to three times with a 5ms backoff. Under heavier contention the third attempt fails with {:error, :sequence_conflict} and the caller is expected to serialize through a single owning process. The single-writer-per-thread contract is documented on the behaviour.

Lazy expiry, retention, audit

No automatic expiry. Retention is a consumer responsibility via truncate/3 (cheap, indexed) or a scheduled DELETE FROM <table> WHERE recorded_at < now() - interval 'N days'.