SDK: FOREIGN KEY constraint failed (code 787) when saving LLM response to session database

[RamXX]

Goose FOREIGN KEY Constraint Failed Bug

Summary

When using Goose SDK programmatically with Synagoose Generator, sessions fail with "FOREIGN KEY constraint failed" (code: 787) after LLM response is received.

Environment

• Goose commit: a48fd4d (latest main)
• OS: macOS 25.3.0
• Provider: Groq (llama-3.3-70b-versatile)
• Configuration: GOOSE_DISABLE_KEYRING=true, GROQ_API_KEY from secrets.yaml

Reproduction Steps

1. Create temporary session with SessionManager:

let temp_dir = std::env::temp_dir().join(format!("test_{}", uuid::Uuid::new_v4()));
std::fs::create_dir_all(&temp_dir)?;
let session_manager = SessionManager::new(temp_dir.clone());
let session = session_manager.create_session(
    temp_dir.clone(),
    format!("test_{}", uuid::Uuid::new_v4()),
    SessionType::Hidden,
).await?;

2. Create agent with Groq provider:

std::env::set_var("GOOSE_DISABLE_KEYRING", "true");
std::env::set_var("GOOSE_PROVIDER", "groq");
std::env::set_var("GOOSE_MODEL", "llama-3.3-70b-versatile");

let provider = create_with_named_model("groq", "llama-3.3-70b-versatile").await?;
let agent = Agent::new();
agent.update_provider(provider, &session.id).await;

3. Send message and await response:

let message = Message::user().with_text("Say hello");
let config = SessionConfig {
    id: session.id.clone(),
    schedule_id: None,
    max_turns: Some(1),
    retry_config: None,
};
let mut stream = agent.reply(message, config, None).await?;
// Collect response...

Expected Behavior

LLM response is saved to session database successfully.

Actual Behavior

error[SG2011]: server error 0
= what: LLM API server returned an error
= why:  Server-side issue (status 0): Failed to execute Agent reply: error returned from database: (code: 787) FOREIGN KEY constraint failed
= fix:  This is a temporary server problem. The request will be retried automatically.

Observations

1. LLM call SUCCEEDS - takes 35+ seconds, returns valid response
2. Error occurs AFTER response received, during database save
3. Issue persists with single-threaded test execution
4. Clearing temp directories doesn't resolve issue
5. Code 787 = SQLite FOREIGN KEY constraint violation

Impact

• Blocks integration testing with real LLM providers
• Affects Synagoose-core and any SDK consumer using programmatic sessions

Workaround Attempted

• ✅ Cleaned up temp session directories
• ✅ Single-threaded execution (--test-threads=1)
• ❌ No successful workaround found

Related Commits

Possibly related to:

• Added transaction commits to multi sql functions in session_manager #5693: "Added transaction commits to multi sql functions in session_manager"
• feat(goose-acp): enable parallel sessions with isolated agent state #6392: "feat(goose-acp): enable parallel sessions with isolated agent state"

Full Error Context

The error comes from Goose's session database after LLM provider returns successfully. This suggests a schema mismatch or missing parent record for a foreign key relationship in the session messages table.

[DOsinga]

interesting. so in general this seems to work - this is basically what our smoke tests do (but then a bunch of other things). can you put up a demo PR where you capture this into a test so we can replay this easily? we can then fix it and keep it around against regressions

[RamXX]

Thanks for the quick response. I will close this now and re-file with more concrete ways to test it. It's currently also mixing two things, one related to the system I'm building, so I also need to separate them. I'll refile something cleaner and follow your suggestion. Thanks!