fix(usage): a single failed usage-accounting write tears down the entire round; the default SQLite busy timeout is easily exhausted by concurrent writers

[jcs130]

Summary

On a SQLite-backed deployment with concurrent work, the daily-usage UPSERT can hit
database is locked. That exception aborts the whole generation round — all model work already
paid for is lost, and the server log shows tracebacks naming the usage table. Bookkeeping should
not be able to fail a user-visible operation.

Evidence

1. Server tracebacks naming the usage table with database is locked during rounds that were doing
   concurrent work (several scopes active, long embedding/generation calls in flight).
2. After raising the busy timeout (POWERCONTEXT_SERVER_DATABASE_BUSY_TIMEOUT_MS=60000) the same
   workload produced 0 database is locked errors and 0 tracebacks over the following runs
   (~3,000 model calls' worth).
3. Default in the installed source: builtin/persistence/sqlite/profile.py:50 —
   busy_timeout_ms: int = Field(default=5_000, ge=0), applied at profile.py:131
   (PRAGMA busy_timeout = ...). Under SQLite's single-writer model, 5 s is not much once one
   writer holds the lock across a slow model call.

Requests

1. Make usage accounting best-effort: record it in its own short transaction and log-and-continue on
   failure rather than failing the round.
2. If it must participate in the round, take the write lock early (before the expensive model calls)
   or batch accounting so the lock is held briefly.
3. Document, or raise, the busy-timeout default for multi-writer deployments.

[guamiao]

I'd like to work on this issue. The problem is clear: usage accounting failures shouldn't abort the entire round. I'll implement the best-effort approach by aligning the log levels in application.py and relational.py with family_processing.py (WARNING instead of ERROR), and add tests to verify the main business continues when usage recording fails.

[guamiao]

Correcting my earlier plan on this issue.

I proposed aligning the log levels in application.py and relational.py with family_processing.py (ERROR → WARNING). That is not a fix: every reporter binding already catches the exception, so changing the level does not change whether it propagates. I reproduced the baseline to confirm this, and that draft is not what I am proposing.

What I found instead, on daa20a6:

• All six statistics write sites (application.py:742/766/878, family_processing.py:150, relational.py:745, topic_memory_processing.py:1719) are already wrapped in try/except, so a database is locked failure does not propagate out of accounting. The tracebacks in the report are produced by those handlers logging at ERROR with exc_info.
• What does reach a round is a deadline effect: the accounting write runs inside the caller's model timeout. On SQLite it then waits up to the configured busy timeout for the write lock, and that wait is billed to the model call. Once a real embedding or generation call has consumed most of its deadline, the round times out — with the model work already completed and paid for.
• Reproduced with the real code and a real competing writer: with a 1.0s embedding timeout and another connection holding the write lock for 1.5s, concurrent topic-memory writes return 503 on daa20a6. They succeed after the change. The same shape exists on the background worker path: DreamService.execute() runs the model call inside its budget timeout and turns an overrun into budget_exceeded (or a retry), so accounting latency can fail a run whose model work already finished.

I am preparing a PR that makes accounting best-effort — its own short transaction, log-and-continue instead of failing the round — across all four reporter bindings, and that documents the busy-timeout behaviour for multi-writer deployments.

One thing I could not confirm: whether this is the path the report came from. I reproduced a mechanism that produces the reported outcome, but I never found an unguarded write that propagates. If you can share the failing workload, or the service names from the tracebacks, I can check that path directly.