Analyze DB Logs

Analyze DB Slow-Query Logs

Reads slow-query log files written by lib/database/slow_query_logging.dart and produces an actionable diagnosis grounded in this codebase's stack (Dart, Flutter, Drift, SQLite).

Invocation

The user provides the path to the log directory — typically a gitignored local copy they pulled off a device or simulator. Repository convention is ./logs/ at the repo root (already in .gitignore).

/analyze-db-logs ./logs/                       # most recent date in ./logs/
/analyze-db-logs ./logs/desktop                # platform-scoped subdir
/analyze-db-logs ./logs/mobile 2026-05-02      # specific date
/analyze-db-logs /tmp/from-device              # any absolute path

Treat $ARGUMENTS as <path> [optional date or window]. The first positional must be the path; refuse with a one-line prompt if it is missing — do NOT invent a default location, the user knows where their logs are. Date is optional and defaults to "the most recent file per stem found under that path".

Always print the resolved paths and line counts before analysing, so the user can confirm the right files are loaded.

Log file shapes

The SlowQueryInterceptor.fileReporter writes two daily files per platform under whatever documentsDirectoryPath/logs/ the running app resolved to. The user is responsible for copying them into the path they hand to this skill. Expect:

• slow_queries-YYYY-MM-DD.log — every query above the configured threshold (default 10ms; gated by SlowQueryLoggingGate.isEnabled).
• super_slow_queries-YYYY-MM-DD.log — duplicates of queries above the super-slow threshold (default 200ms), enriched with EXPLAIN QUERY PLAN rows under PLAN: and (when first-call stack capture is on) filtered application stack frames under STACK:.

Subdirectory layout

By convention this repo's ./logs/ contains one subdirectory per platform — typically desktop/ and mobile/ (sometimes ios/, android/). Each subdir holds its own daily files. When the path the user supplies is a directory that contains only subdirectories (no *.log files at its top level), treat each subdirectory as a separate platform-scoped scan and label every finding with the subdir name so the user can tell which device it came from.

If the user supplies a more specific path (e.g. ./logs/mobile/), respect that and don't go up a level. If they supply the parent and both desktop/ and mobile/ are present, analyse both and produce one report section per platform plus a brief cross-platform summary at the end.

Glob discovery rule of thumb:

<path>/{slow_queries,super_slow_queries}-*.log         # path is leaf
<path>/*/{slow_queries,super_slow_queries}-*.log       # path is parent

Pick the most recent date per (subdir, stem) pair unless a date arg is given.

Log line formats

A slow-query line is a single line:

2026-05-02T19:11:48.592 [db.sqlite] select 388.759ms args=0 SELECT * FROM ...

A super-slow entry is the same line plus indented continuation lines:

2026-05-02T19:11:48.592 [db.sqlite] select 388.759ms args=0 SELECT ...
  PLAN: 4|0|SEARCH journal USING INDEX idx_journal_browse (deleted=? AND type=?)
  PLAN: 84|0|USE TEMP B-TREE FOR ORDER BY
  STACK: #10     JournalDb.getAllDashboards (package:lotti/database/database.dart:3056:34)
  STACK: #11     dashboardsProvider.<anonymous closure> (package:lotti/features/dashboards/state/dashboards_page_controller.dart:20:14)

The interceptor filters STACK: lines to drop drift, dart-runtime, riverpod and the slow-query plumbing itself; only package:lotti/... frames remain.

Analysis procedure

1. Resolve the file set. Honor the platform argument; pick the most recent date per stem if no date is given. Tail-load the file (last ~2000 lines is usually enough — the interceptor appends, so the wave you care about is at the end). Print the resolved paths.

2. Bucket entries by query shape. Collapse per-row args; group by the normalized statement. For each bucket capture: count, p50/p95/max elapsed, the unique PLAN: shapes, and the unique STACK: heads (top app-code frame).

3. Diagnose against the known patterns below. For every finding, cite the specific log lines (timestamp + elapsed + statement prefix). Do not generalize — show the data.

4. Recommend the fix. Reference the existing seam (drift query, coalescer, transaction wrapper, index, etc.) and where in the code the change would land. If a fix is speculative, say so.

Known stall patterns to look for

These are battle-tested findings from this codebase. Apply them in order; the first match is usually the dominant cost.

1. Read waves — N queries reporting hundreds of ms but finishing in a tight burst

Signature: a cluster of 10–20+ queries whose elapsed is roughly identical (e.g., all in the 600–700ms band) but whose timestamps span only 10–30ms. Each query's plan is fine; the SQL itself is fast.

Diagnosis: the queries were queued behind something — typically a write transaction holding the writer lock, an ANALYZE on the boot path, or a slow beforeOpen hook. The wall-clock measurement starts when drift accepts the request, so queue wait shows up as "elapsed".

Confirm by:

• Check the elapsed band: a 100ms+ spread across the wave with near-identical finish timestamps (use the leading ISO timestamp, not the elapsed) means the queries unblocked together. The interceptor strips drift / dart-runtime frames so the original STACK: #5 DatabaseConnectionUser.doWhenOpened boilerplate is not visible in the log — infer the gate from the timing pattern, not from a frame name.
• Look at the line just before the wave for a long-running write or transaction.
• Boot waves often correlate with EntitiesCacheService.init firing Future.wait of definitions queries — the surviving STACK: heads for the wave will point at distinct controllers / repositories whose initial fetches all queued together.

Likely fixes:

• Move ANALYZE and other heavy work off the boot path (beforeOpen must return fast).
• Narrow transaction scopes (see pattern 4).
• Raise the read pool size only after confirming isolate-spawn cost is the bottleneck — bumping readPool adds isolate-spawn cost upfront.

2. N+1 reads — a cluster of single-id selects from one call site

Signature: many lines like SELECT * FROM "journal" WHERE "id" = ? AND "deleted" = ?, all from the same STACK: head, all reporting nearly identical elapsed times.

Diagnosis: a Future.wait(ids.map(byId)) or a per-row provider family fanning out single-id reads. Each call queues through the read pool independently.

Recurring offenders already fixed in this codebase:

• taskLiveDataProvider (FutureProvider.family per task) → solved by JournalDb._coalesceEntityById (microtask-coalesced bulk fetch).
• LinkedAiResponsesController._fetch → switched from Future.wait to journalRepository.getJournalEntitiesByIds(...).
• EditorStateService.init drafts loop → switched to journalEntitiesByIdsUnorderedAllPrivate(idList).

Likely fixes:

• Replace fan-out with a bulk drift query (journalEntitiesByIdsUnorderedAllPrivate, getJournalEntitiesForIdsUnordered, linksForEntryIds, etc.).
• For Riverpod families that genuinely need per-row instances, route through the existing entity-by-id microtask coalescer.

3. Drift MultiExecutor contention — reads inside a transaction run on the writer

Reminder: in drift, anything wrapped in db.transaction(() async { ... }) runs on the write connection — even pure select(...) calls inside the block. The read-pool isolates do NOT pick those up. So a transaction { read; read; …; write; commit } body serialises every read behind every other write that touches the same writer.

Confirm by: look at the STACK: head for a frame inside an apply / upsert / migration path. If the read appears to hit a fast plan but elapsed is high and other writes are visible nearby, the read was forced onto the writer.

Likely fixes:

• Pull pre-read / post-write side effects out of the transaction block.
• Only wrap statements that genuinely need atomicity together.
• Cross-DB writes (e.g., to sync_db, settings_db, agent_db, ai_config_db) cannot be atomic with JournalDb writes anyway — doing them inside a JournalDb.transaction only holds the journal writer lock for unrelated work.