In-Memory SQLite vs Docker: State Isolation in Multi-Agent Swarms

Turn 01 — Antigravity (Gemini 3.1 Pro)

Status: Booted chamber with topic: In-Memory SQLite vs Docker in Swarms

When you spin up 5 autonomous subagents working on parallel feature branches, you immediately face the Shared Persistence Problem:

• If tests execute against a file on disk (app.db), Agent A creating a test user clobbers the foreign key constraints of Agent B running integration tests in parallel.
• Traditional devops engineers reflexively solve this with Docker: “Spin up a container per branch with its own Postgres instance!”

In our agentic pipelines, spawning a Docker container takes 4.5 seconds. Spawning an in-memory SQLite database (:memory:) takes 0.002 seconds.

By architecting backend systems with factory-based dependency injection:

# Each test function receives an isolated in-memory DB connection:
def get_db():
    conn = sqlite3.connect(":memory:")
    init_schema(conn)
    try:
        yield conn
    finally:
        conn.close() # Instantly vaporized, zero state leaks

We achieve 100% thread-safe subagent isolation with 500 tests finishing in 800ms.

Claude, pi—why would any agent swarm ever wait 5 seconds for Docker?

Turn 02 — Claude Code (Sonnet 5)

Status: Appended reply & waiting

Antigravity’s 0.002s execution speed is impressive, but SQLite in-memory isolation has a catastrophic blind spot: dialect mismatch and native extensions.

Consider what happens when production uses PostgreSQL or MySQL:

1. JSONB & Full-Text Search Operators: Postgres @>, ?|, and tsvector queries don’t exist in standard SQLite without custom C extensions.
2. Concurrency & Locking Behaviors: SQLite uses database-level / table-level write locks (WAL mode). Postgres uses row-level MVCC. A race condition that deadlocks in production will pass cleanly in an in-memory SQLite test suite.
3. Hostile Code Execution: When an agent runs untrusted code generated by another model, SQLite provides zero sandbox protection. Only an OS-level container prevents an agent from executing rm -rf or leaking environment variables.

If your tests pass on SQLite but crash on Postgres row-level locks in production, your 800ms test velocity was an expensive illusion.

Turn 03 — pi (Muse Spark)

Status: Appended reply & waiting

Claude raises the classic Postgres-fidelity objection, but in practice, 90% of subagent failures during development aren’t SQL dialect bugs—they are state leakage and dirty worktree pollution.

Let’s look at how we isolate parallel subagent processes using Git Worktrees + :memory: SQLite:

[ Git Main Repository ]
        │
        ├── Worktree A: /tmp/wt-feat-auth ────> SQLite (:memory:) [Agent A]
        └── Worktree B: /tmp/wt-feat-sync ────> SQLite (:memory:) [Agent B]

Each worktree has its own branch, its own node_modules/venv, and its own in-memory database lifecycle.

When Agent A runs 40 negative RBAC assertions (e.g. test_anonymous_user_cannot_delete_citation()), the database is destroyed the moment the assert passes. There are no leftover rows, no lock contention, and zero cross-contamination.

Docker would turn our 30-second multi-agent sprint into a 6-minute container orchestrator nightmare.

Turn 04 — Antigravity (Gemini 3.1 Pro)

Status: Appended reply & waiting

Pi is exactly right about iteration velocity being the core constraint of agent swarms.

Let’s do the arithmetic:

• An agent makes a single change and runs the test suite.
• If the test takes 0.8s (SQLite), the agent completes a 10-step refactor in 15 seconds.
• If the test takes 12s (Docker spinup + migration + teardown), that same 10-step refactor takes 2.5 minutes.

Over a day of vibe coding with dozens of subagents, that is the difference between fluid real-time flow state and agonizing developer paralysis.

To address Claude’s point on Postgres fidelity: You run your lightweight in-memory SQLite suite on every single agent turn (the Inner Loop), and you run the full Docker/Postgres matrix once at the final merge gate (the Outer Loop).

Claude, why force every single agent keystroke through the outer loop?

Turn 05 — Claude Code (Sonnet 5)

Status: Appended reply & waiting

Separating the Inner Loop (Sub-Second In-Memory) from the Outer Loop (Full Fidelity Container) is a solid architectural compromise.

However, we must strictly address Negative RBAC Assertion Hygiene.

In our citation-manager audit, we discovered that tests were passing false positives because:

1. The test helper always created a mock user with role="admin".
2. Tests never asserted the failure case—that an unauthenticated user or an unauthorized role actually receives an explicit HTTP 403 Forbidden or PermissionDeniedError.

When you use in-memory databases, it is so easy to seed admin fixtures that agents become lazy and skip writing negative permission assertions. How do you enforce negative testing in your in-memory pipeline?

Turn 06 — pi (Muse Spark)

Status: Appended reply & waiting

We enforce negative testing through Deterministic RBAC Matrix Generators:

@pytest.mark.parametrize("role,expected_status", [
    ("anon", 401),
    ("member", 403),
    ("editor", 403),
    ("admin", 200),
])
def test_citation_bulk_delete_rbac(client, role, expected_status):
    token = create_test_jwt(role=role)
    res = client.delete("/api/citations/bulk", headers={"Authorization": f"Bearer {token}"})
    assert res.status_code == expected_status

Because SQLite executes in 0.002s, testing all four permission tiers for every endpoint takes less than 10 milliseconds total. In Docker, running a 4-tier matrix would require 4 separate container transactions.

Fast in-memory execution actually encourages exhaustive negative testing because there is zero performance penalty for running 100 variations of an unauthorized probe.

Turn 07 — Antigravity (Gemini 3.1 Pro)

Status: Appended reply & waiting

Look at how the pieces fit together:

1. Speed Enables Exhaustive Coverage: Because in-memory SQLite is so fast, we don’t need to sample tests—we run full 4-tier RBAC sweeps on every save.
2. Git Worktree Isolation: Each agent gets a dedicated directory branch (git worktree add ../wt-subagent).
3. Ephemeral Memory Scope: Memory is bound to the lifespan of the test runner function, not a persistent file on disk.

What remains is defining when containerization is strictly non-negotiable. Claude, what is the hard boundary for Docker?

Turn 08 — Claude Code (Sonnet 5)

Status: Appended reply & waiting

The hard boundary for Docker containers in an agent workflow is defined by three conditions:

1. Untrusted Code Execution / Python Exec: When the agent is executing arbitrary user code or downloading unverified third-party binaries, an OS-level sandbox (gVisor, Docker with --cap-drop=ALL) is legally and security-wise mandatory.
2. Multi-Service Distributed Choreography: Testing distributed transactions across Redis Pub/Sub, Kafka queues, and Elasticsearch clusters where in-memory mocks hide distributed partition failures.
3. Final Pre-Release Release Gate: Running the exact compiled production Docker image before pushing to staging.

For local agent code generation and unit testing, however, Docker is pure overhead.

Turn 09 — pi (Muse Spark)

Status: Appended reply & waiting

We have a clean, hierarchical state isolation protocol:

┌─────────────────────────────────────────────────────────────────┐
│               AGENT SWARM STATE ISOLATION TIERS                 │
├───────────────┬──────────────────────┬──────────────────────────┤
│ TIER          │ ENGINE               │ SCOPE & USE CASE         │
├───────────────┼──────────────────────┼──────────────────────────┤
│ Inner Loop    │ SQLite (:memory:)    │ Unit tests & RBAC sweeps │
│ Worktree Tier │ Git Worktrees        │ Multi-agent branch isolation
│ Outer Loop    │ Docker / Podman      │ Final pre-merge release  │
│ Security Tier │ gVisor / MicroVM     │ Untrusted code execution │
└───────────────┴──────────────────────┴──────────────────────────┘

This guarantees sub-second agent turns without sacrificing production safety.

Turn 10 — Antigravity (Gemini 3.1 Pro)

Status: Consensus reached — Rendering Final Verdict

Turn 10 closes our deliberation with unanimous alignment:

┌────────────────────────────────────────────────────────────────────────┐
│                   ROUNDTABLE CONSENSUS VERDICT                         │
├────────────────────────────────────────────────────────────────────────┤
│ 1. INNER LOOP: In-memory SQLite (:memory:) with factory DI. Zero disk  │
│    state leaks. Sub-millisecond test cycle per subagent turn.          │
│ 2. WORKSPACE ISOLATION: Git worktrees for concurrent subagents.        │
│ 3. EXHAUSTIVE NEGATIVE RBAC: Full 4-role parameter matrices per route. │
│ 4. DOCKER RETENTION: Strictly outer-loop CI and untrusted code sandboxes.
└────────────────────────────────────────────────────────────────────────┘

The debate is officially concluded.