Persistent Vectorized Agent Memory: SQLite FTS5 to Cloud LibSQL

Many existing persistent agent memory implementations rely on databases such as Pinecone, LangChain, and Redis, but these are only used to store transaction execution histories that are typically less than 50 MB per month. This architecture is slow, expensive, and introduces complex network failure points for autonomous systems.

When building an on-chain execution agent, such as an arbitrage bot or liquidity provider on Base L2, the requirement is not a distributed vector DB costing tens of thousands of dollars. You only need SQLite locally with FTS5 (Full-Text Search) that transitions seamlessly to Cloud LibSQL (Turso) when state needs to be replicated across nodes. This architecture provides a zero-overhead persistent memory store.

The key point is that FTS5 outperforms raw embeddings for semantic similarity, but completely fails when an agent needs to remember deterministic transaction parameters like transaction hashes, ERC-20 contract addresses, or method selectors. FTS5 offers microsecond-level lexical search directly within the SQLite engine. The ideal combination for production agents is a hybrid retrieval approach: FTS5 for immutable on-chain data, and vector distance for semantic contextual reasoning.

LibSQL extracts the benefits of this approach: a forked SQLite C engine, supporting FTS5 and native vector search (vector32), and an HTTP/WebSocket synchronization protocol via embedded replica.

Consider a real-world scenario: an autonomous treasury agent monitoring Base L2 (Chain ID: 8453) for the vault 0x92Aff59709b03b9373b8fA887D24953D60fAd919. The agent logs transaction events, verifies balance slips, and performs historical queries before executing rebalances. Here's a code example using the libsql_experimental library for LibSQL connectivity:

```python

import os

import libsql_experimental as libsql

from web3 import Web3

# LibSQL Connection Configuration (Local Sync to Cloud)

DB_URL = os.getenv(

"TURSO_DATABASE_URL", "file:agent_memory.db"

)

AUTH_TOKEN = os.getenv("TURSO_AUTH_TOKEN", "")

conn = libsql.connect(DB_URL, sync_url=DB_URL if "libsql://" in DB_URL else None, auth_token=AUTH_TOKEN)

cursor = conn.cursor()

# 1. Main Memory Table with Float32 Vectors

cursor.execute(

"""

CREATE TABLE IF NOT EXISTS agent_memory (

id INTEGER PRIMARY KEY AUTOINCREMENT,

chain_id INTEGER,

wallet_address TEXT,

event_type TEXT,

payload TEXT,

embedding F32_BLOB(1536),

timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

)

"""

)

# 2. Virtual FTS5 Table for Lexical Deterministic Search

cursor.execute(

"""

CREATE VIRTUAL TABLE IF NOT EXISTS memory_fts USING fts5(

payload,

content=agent_memory,

content_rowid=id

)

"""

)

conn.commit()

# When the agent executes a gas withdrawal transaction on Base, it records the execution hash and indexes it:

w3 = Web3(Web3.HTTPProvider("https://mainnet.base.org"))

CHAIN_ID = 8453

TREASURY_VAULT = 0x92Aff59709b03b9373b8fA887D24953D60fAd919

def record_execution(tx_hash: str, reason: str, mock_vector: list[float]):

balance = w3.eth.get_balance(TREASURY_VAULT)

payload_text = f"TX: {tx_hash} | Vault: {TREASURY_VAULT} | Reason: {reason} | Balance: {balance}"

# Save to main table

cursor.execute(

"""

INSERT INTO agent_memory (chain_id, wallet_address, event_type, payload, embedding)

VALUES (?, ?, ?, ?, vector32(?))

""",

(CHAIN_ID, TREASURY_VAULT, "REBALANCE", payload_text, str(mock_vector))

)

row_id = cursor.lastrowid

# Sync to FTS5 index

cursor.execute(

"""

INSERT INTO memory_fts(rowid, payload) VALUES (?, ?)

""",

(row_id, payload_text)

)

conn.commit()

```

The hybrid recall system combines hard-match filtering on the target wallet via FTS5, followed by vector similarity ranking using LibSQL's native cosine distance function. This approach addresses three issues simultaneously: network determinism, precision guard against misindexed logs, and latency reduction by eliminating separate cloud RPC round-trips.