bug(server): in-memory SQLite loses objects schema during Podman sandbox cleanup

[EmilienM]

Description

An OpenShell gateway started with --db-url sqlite::memory: can lose its
migrated schema during concurrent Podman sandbox cleanup. DeleteSandbox then
fails with no such table: objects, even though the same gateway successfully
created and operated the sandbox immediately beforehand.

Changing only the gateway database URL to a temporary file-backed SQLite
database eliminated the failure in the same end-to-end test suite.

Actual behavior

The sandbox and agent command complete successfully (Agent exit code: 0).
During cleanup, openshell sandbox delete fails:

Failed to delete sandbox ci: code: 'Internal error', message:
"fetch sandbox failed: database error: error returned from database:
(code: 1) no such table: objects"

subprocess.CalledProcessError: Command
['openshell', 'sandbox', 'delete', 'ci'] returned non-zero exit status 1.

The gateway log shows the container deletion event and subsequent persistence
failure:

Container already removed when inspecting after event, emitting deleted event
Failed to apply compute driver event ... no such table: objects
DeleteSandbox ... rpc.grpc.status_code=13
gateway shutdown cleanup failed: failed to list sandboxes ... no such table: objects

This affected two OpenCode-backed sandbox runs in the suite. The agent command
and plugin-filter assertions themselves succeeded; only gateway cleanup failed.

Failing run: https://github.com/opendatahub-io/agentic-ci/actions/runs/33809097313/job/100827079164

Result: 60 passed, 2 failed.

Expected behavior

An explicitly configured in-memory SQLite gateway should retain its migrated
schema for the gateway process lifetime. Concurrent sandbox cleanup must not
replace the database with a fresh schema-less in-memory connection.

If in-memory SQLite is not supported for a running gateway, the CLI should
reject that database URL rather than accept it and fail later during lifecycle
operations.

Controlled comparison

The integration changed the gateway from:

--db-url sqlite::memory:

to a unique temporary file:

--db-url sqlite:/path/to/gateway-<unique>.db?mode=rwc

No OpenShell version, sandbox image, supervisor image, harness, policy, or test
scenario changed.

Change: opendatahub-io/agentic-ci@90aa110

Passing run: https://github.com/opendatahub-io/agentic-ci/actions/runs/33823773323/job/100872224289

Result: 62 passed, 0 failed, with no no such table or database errors.

Suspected mechanism

This is a hypothesis based on the observed transition, not a confirmed source
attribution. OpenShell correctly limits in-memory SQLite to one pooled
connection. If SQLx discards that connection after a cancelled or failed
operation during concurrent deletion, its replacement is a new independent
in-memory database without the migrations or objects table.

Relevant upstream areas:

• crates/openshell-server/src/persistence/sqlite.rs: in-memory pool setup and migrations
• crates/openshell-server/src/compute/mod.rs: request-side deletion and compute-driver event reconciliation
• crates/openshell-driver-podman/src/watcher.rs: container deletion events

The cleanup path is related to the persistence contention discussed in #2999,
but the observed error differs: this report sees a missing schema with an
in-memory database rather than database is locked with file-backed SQLite.

Environment

• Linux GitHub Actions runner
• Privileged CI container
• Rootful Podman compute driver
• TLS and mTLS enabled
• Gateway launched directly with sqlite::memory:
• Persistent canonical sandbox main: --detach -- sleep infinity
• Agent commands executed through openshell sandbox exec

[EmilienM]

🏗️ build-plan

Implementation Plan

Issue type: fix
Complexity: Medium
Confidence: High — clear persistence invariant and deterministic regression

Summary

Keep in-memory SQLite state alive independently of the operational SQLx pool connection so connection replacement cannot destroy the migrated schema or stored objects. Preserve the existing single operational connection to avoid introducing additional SQLite write contention.

Scope

• crates/openshell-server/src/persistence/sqlite.rs: normalize anonymous memory URLs, retain an idle keepalive connection, and close it through test-support shutdown.
• crates/openshell-server/src/persistence/tests.rs: force operational connection replacement and verify schema, existing data, and subsequent writes survive.
• architecture/gateway.md: document the in-memory SQLite lifetime invariant.

Implementation Steps

1. Add a deterministic failing regression that writes an object, replaces the operational connection, and reads it back.
2. Retain a dedicated connection to the same named shared-cache in-memory database for the store lifetime.
3. Cover both accepted anonymous in-memory SQLite URL forms and continued writes after replacement.
4. Run persistence, deletion/reconciliation, formatting, lint, and repository verification.
5. Run independent correctness and architecture reviews, fix confirmed findings, and re-review.

Test Plan

• Unit tests: Persistence regression for schema/data retention and post-replacement writes.
• Integration tests: Existing explicit-delete and watcher-reconciliation compute tests.
• E2E tests: N/A — no e2e/ files change, and the current Podman E2E uses file-backed SQLite rather than the affected configuration.

Risks & Open Questions

• The exact production event that caused SQLx to discard the original connection is not captured in the reported logs; the fix protects the required lifetime invariant regardless of trigger.
• Keep the operational pool at one connection to avoid adding lock contention related to Control-plane read failures during sandbox SSH-session cleanup; delete acknowledgment can precede durable cleanup #2999.

Documentation Impact

• Update architecture/gateway.md. No gateway TOML field, default, Helm rendering, or driver option changes.

---

Revision 1 — initial plan

[EmilienM]

🏗️ build-from-issue-agent

Implementation Complete

PR: #3175

What was built

In-memory SQLite stores now retain a dedicated keepalive connection, so SQLx operational pool connection replacement preserves the migrated schema and existing objects. Anonymous mode=memory URLs are normalized to unique named databases, and operational concurrency remains serialized.

Tests

• Unit: 1 deterministic regression added; persistence suite 63 passed with 3 pre-existing ignored
• Integration: existing explicit-delete and watcher-reconciliation tests passed
• E2E: N/A; no E2E files changed and the current Podman lane uses file-backed SQLite

Docs updated

• architecture/gateway.md

Two independent adversarial reviews are clean after the confirmed alternate-URL edge case was fixed and re-reviewed. The issue will auto-close when the PR is merged.