SQLite as Local Persistent Cache (Redis-like) for Agentic Workloads. (fast, local, persistent cache + state layer)

Sqlit-Cache.md

# SQLite as Local Persistent Cache (Redis-like) for Agentic Workloads

## Purpose

Use SQLite as a **fast, local, persistent cache + state layer** for long-running agentic tasks.

This replaces Redis for:
- single-machine execution
- low ops overhead
- durability (resume after crash)
- debuggability (inspect DB directly)

---

## When to Use

Use SQLite if:

- Single machine / local agent
- Need persistence across restarts
- Dataset ≤ few million rows
- Moderate concurrency (not heavy multi-writer)

Avoid if:
- High concurrent writes across processes
- Need sub-millisecond latency at scale

---

## Core Design

### Table: cache

```sql
CREATE TABLE cache (
  cache_key TEXT PRIMARY KEY,
  value BLOB,
  namespace TEXT,
  expires_at INTEGER,
  created_at INTEGER,
  updated_at INTEGER
);

Indexes

CREATE INDEX idx_namespace ON cache(namespace);
CREATE INDEX idx_expires ON cache(expires_at);

---

Key Patterns

1. Get

SELECT value FROM cache 
WHERE cache_key = ? 
AND (expires_at IS NULL OR expires_at > ?);

2. Set (Upsert)

INSERT INTO cache (cache_key, value, namespace, expires_at, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(cache_key) DO UPDATE SET
  value = excluded.value,
  expires_at = excluded.expires_at,
  updated_at = excluded.updated_at;

3. Delete expired

DELETE FROM cache WHERE expires_at IS NOT NULL AND expires_at < ?;

---

Usage Patterns for Agents

1. Tool Call Cache

• Key: tool:{tool_name}:{hash(input)}
• Value: JSON result
• TTL: short (minutes/hours)

---

2. Step Checkpoints

• Key: run:{run_id}:step:{step_id}
• Value: state snapshot
• TTL: none

---

3. Memory / Context

• Key: memory:{agent_id}:{topic}
• Value: structured JSON
• TTL: long / none

---

4. Locks

• Key: lock:{resource}
• Value: owner_id
• TTL: short

---

5. Queue (simple)

• Table: queue
• Use status column (pending, processing, done)

---

Performance Setup (MANDATORY)

Run once per connection:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA temp_store=MEMORY;
PRAGMA cache_size=-20000; -- ~20MB

---

Critical Practices

1. Keep Connection Open

• DO NOT open/close per query
• Reuse a single connection per process

---

2. Use Prepared Statements

• Avoid dynamic SQL
• Use parameter binding

---

3. Avoid Full Table Scans

• Always query by cache_key or indexed fields

---

4. Store Opaque Values

• Store JSON as BLOB/TEXT
• Avoid querying inside JSON

---

5. Batch Writes When Possible

• Group inserts/updates in transactions

---

Expected Performance

Operation	Latency
Primary key read	~1–2 ms
Indexed read	~2–5 ms
Write	~2–10 ms

For 100K records → trivial load

---

TTL Strategy

SQLite has no native TTL.

Implement manually:

• Always check expires_at on read
• Run periodic cleanup (cron / background thread)

---

Optional: In-Memory Hot Layer

For ultra-fast access:

memory cache (dict / diskcache)
    ↓ miss
SQLite

Use this for:

• LLM responses
• repeated tool outputs

---

Concurrency Notes

• WAL allows concurrent reads + single writer
• Avoid many parallel writers
• Use retry logic on database is locked

---

File Placement

• Store DB locally:

  • ./agent_state.db

• Backup if needed

• Can be inspected manually via CLI

---

Debugging

sqlite3 agent_state.db
SELECT * FROM cache LIMIT 10;

---

Summary

SQLite gives you:

• Redis-like API (get/set/ttl patterns)
• Persistence
• Low latency (~1–5 ms)
• Zero infra

Best fit for: → local agent → long-running tasks → recoverable execution → structured caching + state

Recommended stack:

SQLite = durable cache + state
(optional) in-memory = hot cache