Problem

SQL database actors start with an in-memory SQLite instance and only promote to file storage when data exceeds the memory_threshold (default: 10 MiB). For small databases — like a user with a handful of rows — data never crosses this threshold.

When the actor hits the 5-minute idle timeout, it shuts down and the in-memory data is silently lost. The next request spawns a fresh actor with an empty database. Users experience this as intermittent data loss: "my SQL data disappeared."

KV storage is unaffected because it writes to the server's main persistent database (sea-orm) immediately. This only impacts the per-space SQLite service.

Root Cause

In tinycloud-core/src/sql/database.rs, the actor loop exits on idle timeout (IDLE_TIMEOUT = 300s) and proceeds directly to cleanup without persisting in-memory state:

Err(_) => break,   // Idle timeout — in-memory data dropped here

The promote_to_file path only runs during the loop, gated by size > memory_threshold.

Fix

After the actor loop breaks, check if the database is still in-memory. If it contains any data, flush it to disk via the existing promote_to_file function (SQLite backup API) before the actor exits.

On the next request, spawn_actor sees the file on disk and opens from file — data survives across idle cycles.

if matches!(mode, StorageMode::InMemory) {
    if let Ok(size) = storage::database_size(&conn) {
        if size > 0 {
            storage::promote_to_file(&conn, &file_path)?;
        }
    }
}

What this doesn't change

• The memory_threshold promotion-during-writes path is untouched — large databases still promote eagerly
• The idle timeout duration is unchanged
• The actor self-removal from the DashMap registry (from chore: version packages #30) still runs after the flush
• File-backed databases are unaffected (already on disk)

Test plan

• cargo check passes on tinycloud-core
• cargo test sql passes
• Manual: create a few SQL rows via delegated access, wait >5 min, query again — data persists
• Verify logs show "Flushed in-memory database to file on shutdown" after idle timeout

🤖 Generated with Claude Code