Act 1: does memory help an LLM write SQL?

Act 1 is a diagnostic experiment. A single frozen ReAct agent (schema-exploration tools + SQL execution, one model) answers natural-language questions over a SQL database. We measure whether giving that agent memory — schema notes, past solved trajectories, or derived join routes — changes its execution accuracy, and if so, why (does it retrieve the right thing? does it actually use it?).

Everything varies by arm (memory policy); the agent substrate itself never changes. The primary dataset is FIBEN (IBM's Financial Benchmark, 152 tables).

---

1. Prerequisites

• Python 3.10+
• An OpenAI API key (the agent and the embedding retriever both call the API):

  export OPENAI_API_KEY=sk-...

• Install dependencies:

  python -m venv .venv && source .venv/bin/activate
  pip install -r act1/requirements.txt

  (langgraph / langchain-* are only needed for the alternative LangGraph substrate; the primary study runs without them.)

All commands below are run from the repository root and use the act1.* module path (e.g. python -m act1.run ...), so the package resolves correctly.

---

2. One-time data build

The BIRD financial database ships ready to use. FIBEN must be built once from its DDL + CSV dump into a SQLite file:

python -m act1.scripts.build_fiben_db

This reads act1/data/fiben/FIBEN.sql and act1/data/fiben/data/*.csv and writes act1/data/fiben/fiben.sqlite. If the CSVs are missing, extract data.zip into act1/data/fiben/data/ first (one file per table, no header row). You only ever run this once; the built fiben.sqlite is reused by every arm.

---

3. Run the whole pipeline (recommended)

The pipeline orchestrator runs every stage back-to-back for one dataset: baseline → warm-up + push memory → failure report → derived-route arm → pull arm(s) → comparison report. This is the single command to get everything up and running:

python -m act1.scripts.run_pipeline --dataset fiben --joinpath --pull

Stages it runs, in order:

Stage	What it does
M1	no_memory baseline arm (agent explores fresh every query)
M2a	Warm up the memory store from a held-out question pool
M2b	always_on_memory arm (top-k memory injected into every prompt)
M3	Failure-mode report: no_memory vs always_on_memory
M4a	Build + NL-re-key the derived join-path store (FIBEN only)
M4b	derived_joinpath_memory arm (join routes derived from the FK graph)
M5	agent_invoked_memory pull arm (agent fetches memory via a tool)
M5′	agent_invoked_memory_prompted (same, but the tool is named in the prompt)
Report	joinpath_report — the full multi-arm comparison

Useful flags:

• --limit N — cap to the first N eval questions (smoke test; omit for the full 115-question slice).
• --warmup-limit N — how many held-out questions warm up the store (default in config.json).
• --skip-warmup — reuse the existing memory store instead of rebuilding it.
• --joinpath / --pull — enable the M4 / M5 stages (FIBEN only; --pull requires --joinpath or a reused join-path run).
• --no-memory-run <dir> / --always-on-run <dir> / --joinpath-run <dir> — reuse an existing run instead of paying API cost to re-run it. Only the new arms then cost anything.
• --rebuild-joinpath-store — force a rebuild of the derived join-path store.

Quick smoke test before a full run:

python -m act1.scripts.run_pipeline --dataset fiben --joinpath --pull --limit 5

Outputs land in act1/runs/ (see §6).

---

4. Run stages manually (finer control)

Each stage is a standalone script; the pipeline just chains them. To drive them yourself:

A single arm (writes a timestamped run dir + episodes.jsonl + summary.json):

python -m act1.run --arm no_memory              --dataset fiben
python -m act1.run --arm always_on_memory       --dataset fiben
python -m act1.run --arm derived_joinpath_memory --dataset fiben
python -m act1.run --arm agent_invoked_memory   --dataset fiben

Add --limit N for a smaller slice. Memory arms require the relevant store to exist first (the script tells you which build command to run if it's missing).

Build the memory stores:

# Warm-up store (schema summaries + trajectories from held-out successes)
python -m act1.scripts.warmup_memory --dataset fiben --write-strategy store_on_success

# Derived join-path store (routes enumerated offline from the FK graph),
# then re-keyed with NL questions so retrieval matches (both steps required):
python -m act1.scripts.build_joinpath_memory --dataset fiben
python -m act1.scripts.nlq_joinpath_keys --types join_path

Held-out discipline: warm-up only ever draws from questions outside the eval slice, so a stored trajectory can never be the exact eval question it's tested on.

Reports:

# Two-arm failure breakdown (no_memory vs always_on)
python -m act1.scripts.m3_report \
    --no-memory-run  act1/runs/no_memory_<ts> \
    --always-on-run  act1/runs/always_on_memory_<ts>

# Full multi-arm comparison (join-path + pull arms)
python -m act1.scripts.joinpath_report \
    --no-memory-run       act1/runs/no_memory_<ts> \
    --always-on-run       act1/runs/always_on_memory_<ts> \
    --joinpath-run        act1/runs/derived_joinpath_memory_<ts> \
    --pull-run            act1/runs/agent_invoked_memory_<ts> \
    --pull-prompted-run   act1/runs/agent_invoked_memory_prompted_<ts>

joinpath_report is pure log analysis (no API calls), so it's cheap to re-run over existing run dirs. It writes a timestamped act1/runs/joinpath_report_<ts>/ holding report.txt and a summary.json that records exactly which input run dirs were compared, and prints a WARNING if the compared runs disagree on any comparison-critical config (model, seed, max_steps, top_k, min_score, eval_slice_size, dataset).

---

5. Configuration

act1/config.json holds the defaults: model (gpt-4o-mini), embedding model, temperature/seed, eval_slice_size (115), max_steps, retrieval top_k / min_score, warm-up write strategy, and all dataset paths. CLI flags (--model, --seed, --max-steps, --limit) override per run. The exact config and system prompt a run used are frozen into that run's config.json for auditability.

---

6. Outputs

Everything is written under act1/runs/<arm>_<timestamp>/:

• episodes.jsonl — one JSON record per question (predicted SQL, gold SQL, ex_match, retrieved/injected memory items, per-step tool trace, token cost).
• summary.json — run-completeness marker with EX accuracy, mean steps/tokens, and the question_ids covered (lets reports verify two runs share a slice).
• config.json — the fully resolved config + frozen system prompt for that run.

Report dirs (m3_report_<ts>/, joinpath_report_<ts>/) hold the printed report, a summary.json, and any plots.

---

7. Tests

python -m pytest act1/tests -q