SQLite with EctoSessionStore

AgentSessionManager supports SQLite through AgentSessionManager.Adapters.EctoSessionStore and Ecto.Adapters.SQLite3.

This uses a single transactional persistence implementation shared across PostgreSQL and SQLite.

Prerequisites

Add Ecto and SQLite dependencies:

def deps do
  [
    {:agent_session_manager, "~> 0.8.0"},
    {:ecto_sql, "~> 3.12"},
    {:ecto_sqlite3, "~> 0.17"}
  ]
end

Define a Repo

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.SQLite3
end

Configure it (for example in config/runtime.exs):

config :my_app, MyApp.Repo,
  database: "priv/asm.sqlite3",
  pool_size: 5

Run Migrations

EctoSessionStore ships migration modules you can invoke from your project migrations. Apply Migration.

defmodule MyApp.Repo.Migrations.AddAgentSessionManager do
  use Ecto.Migration

  def up, do: AgentSessionManager.Adapters.EctoSessionStore.Migration.up()
  def down, do: AgentSessionManager.Adapters.EctoSessionStore.Migration.down()
end

Then run:

mix ecto.migrate

Start the Store

alias AgentSessionManager.Adapters.EctoSessionStore

store = {EctoSessionStore, MyApp.Repo}

With a registered name:

{:ok, _store} = EctoSessionStore.start_link(repo: MyApp.Repo, name: :session_store)

SessionStore Usage

alias AgentSessionManager.Core.{Event, Run, Session}
alias AgentSessionManager.Ports.SessionStore

{:ok, session} = Session.new(%{agent_id: "assistant"})
:ok = SessionStore.save_session(store, session)

{:ok, run} = Run.new(%{session_id: session.id})
:ok = SessionStore.save_run(store, run)

{:ok, event} =
  Event.new(%{
    type: :message_received,
    session_id: session.id,
    run_id: run.id,
    data: %{content: "hello"}
  })

{:ok, persisted} = SessionStore.append_event_with_sequence(store, event)

{:ok, sessions} = SessionStore.list_sessions(store, status: :pending)
{:ok, events} = SessionStore.get_events(store, session.id, after: 0, limit: 50)
{:ok, latest_seq} = SessionStore.get_latest_sequence(store, session.id)

Query and Maintenance

Query and maintenance are plain module adapters with explicit refs:

alias AgentSessionManager.Adapters.{EctoMaintenance, EctoQueryAPI}
alias AgentSessionManager.Persistence.RetentionPolicy
alias AgentSessionManager.Ports.{Maintenance, QueryAPI}

query = {EctoQueryAPI, MyApp.Repo}
maint = {EctoMaintenance, MyApp.Repo}

{:ok, %{sessions: sessions}} = QueryAPI.search_sessions(query, agent_id: "assistant")
{:ok, usage} = QueryAPI.get_usage_summary(query)

policy = RetentionPolicy.new(max_completed_session_age_days: 90)
{:ok, report} = Maintenance.execute(maint, policy)

Why this architecture

• One transactional implementation for all SQL backends
• Shared behavior/tests between SQLite and PostgreSQL
• No duplicated raw SQL store code to maintain
• Better long-term parity for flush/2 finalization and append_events/2 batch support