feat: query-on-zoom for stored session viewer

[tylerkron]

Problem

When viewing stored logging sessions with large datasets (e.g., 48 hours at 1000hz = 172M+ samples per channel), the app loads all samples into memory before rendering. This doesn't scale — it causes excessive memory usage and slow load times.

After #457 and #458, the app decimates and displays a minimap overview, but still loads everything into memory first.

Goal

• Initial load: Stream through DB rows once, building a low-resolution overview without holding all data in memory
• Zoom in: Re-query the DB for only the visible time window, LTTB-decimate to ~5000 points — full fidelity in small windows (e.g., 5 seconds of a 48-hour session shows all 5000 data points)
• Memory stays bounded regardless of session size

Prior work

• fix: improve chart performance with large datasets using LTTB downsampling #457 — LTTB downsampling algorithm (DataPointDecimator), reused for zoom re-query
• feat: add overview minimap for large dataset navigation #458 — Minimap overview with MinMaxDownsampler, becomes the full-session context view

Implementation plan

1. Add composite DB index

Add (LoggingSessionID, TimestampTicks) index to DataSamples for efficient range queries. Use CREATE INDEX IF NOT EXISTS since the app uses Database.EnsureCreated(). If index creation fails, log a warning and continue — queries still work, just slower.

2. New SessionDataService

DB query abstraction using IDbContextFactory<LoggingContext>:

• GetChannelMetadata(sessionId) — distinct channels with color/type
• GetSessionBounds(sessionId) — total count, min/max ticks
• StreamSamples(sessionId) — IEnumerable via AsEnumerable() for streaming (no ToList())
• QueryTimeWindow(sessionId, minTicks, maxTicks, CancellationToken) — range query for zoom

Failed zoom queries should log the error and keep displaying current data — not crash.

3. New StreamingOverviewBuilder

Fed row-by-row during initial streaming pass, accumulates in bounded memory:

• 800-bucket MinMax per channel for minimap (~1600 points)
• Every-Nth-sample pre-filter → LTTB to 5000 points for main plot overview

4. Rewrite DisplayLoggingSession

• Small sessions (< 50K total samples across all channels): Load directly as today
• Large sessions: Stream once through StreamingOverviewBuilder, populate minimap + main plot from builder output. Eliminates _allSessionPoints.

5. Debounced zoom re-query

Extend OnMainTimeAxisChanged (from #458):

• 200ms debounce timer on axis changes
• Convert visible range (ms) back to ticks
• Query visible window via SessionDataService.QueryTimeWindow()
• LTTB-decimate result to 5000 points per channel
• Cancel stale queries via CancellationTokenSource — dispose previous CTS when creating a new one on each zoom

6. Reset zoom → instant overview

Use cached overview data immediately, no DB query needed.

Acceptance criteria

• 1M+ sample session loads quickly with bounded memory
• Zooming into a 5-second window of a large session shows ~5000 points at full resolution
• Minimap interaction (drag/resize) triggers zoom re-query
• Reset zoom instantly restores overview
• Rapid mouse-wheel zoom debounces correctly (no UI freeze)
• Small sessions (< 50K samples) still load normally
• Unit tests for StreamingOverviewBuilder and SessionDataService

[daqifi-aos]

🔍 Refinement Notes

Gaps

• No error handling specified for SessionDataService database connection failures during zoom operations
• StreamingOverviewBuilder thread safety not addressed - unclear if it needs concurrent access protection during active streaming
• No specification for handling partial query results when QueryTimeWindow encounters database locks or timeouts
• Missing details on how to integrate new SessionDataService with existing LoggingContext and dependency injection setup
• No specification for cleanup/disposal strategy for CancellationTokenSource in debounced zoom operations
• Index creation failure handling not specified - should the app gracefully degrade or fail hard when composite index creation fails

Risks

• Composite index creation on large existing databases could cause temporary application freezes during startup
• QueryTimeWindow range queries might return unexpectedly large result sets if ticks-to-milliseconds conversion has precision errors
• Concurrent zoom requests during active DatabaseLogger bulk inserts could create SQLite database lock contention
• IDbContextFactory context lifecycle misalignment could cause 'context disposed' exceptions during long-running zoom queries

Acceptance Criteria

• Memory constraint: Peak memory usage during large session load does not exceed baseline + streaming buffer size regardless of session size
• Database performance: Composite index creation succeeds on existing databases without corruption

Execution Contract

• Complexity: large
• Required Tools: Entity Framework, SQLite, OxyPlot, WPF
• Affected Files: Daqifi.Desktop/Loggers/LoggingContext.cs, Daqifi.Desktop/ViewModels/PlottingViewModel.cs, Daqifi.Desktop/Loggers/DatabaseLogger.cs
• Dependencies: Issues fix: improve chart performance with large datasets using LTTB downsampling #457 and feat: add overview minimap for large dataset navigation #458 must be completed first

Open Questions

• How should the system handle zoom requests when the SessionDataService DbContext is disposed during streaming operations?
• What specific memory threshold should trigger the large vs small session decision point (issue mentions < 50K but doesn't specify units - samples per channel or total)?
• Should the composite index be added through Entity Framework migrations or only via Database.EnsureCreated() - what happens to existing production databases?

---

Refined by DAQiFi AOS at 2026-04-06T04:36:09.191278+00:00

[tylerkron]

Closed by PR #467. Full implementation delivered:

• ✅ Composite DB index IX_Samples_SessionTime on (LoggingSessionID, TimestampTicks)
• ✅ Two-phase progressive loading (replaces load-all-into-memory)
• ✅ Debounced zoom re-query: 200ms settle timer + async background DB fetch with CancellationToken
• ✅ Sampled index seeks for both overview loading and viewport fetch (caps reads at ~4K rows)
• ✅ Reset zoom instantly restores overview from in-memory sampled data
• ✅ Minimap drag uses in-memory data only; DB fetch on mouse-up
• ✅ Small sessions still load normally (skip Phase 2 if < 100K samples)

The implementation differs from the original spec (no separate SessionDataService/StreamingOverviewBuilder — the logic lives in DatabaseLogger directly) but achieves all the same goals.