Database Query Monitoring

Free — GPL coresize Mplanned, not built

Generated from docs/plan/core/database-query-monitoring/PLAN.md in the AstroBaaS repository. Nothing described below is implemented — it is the written plan for it.

Developers need to identify slow database queries causing performance bottlenecks. This feature profiles query execution time, identifies N+1 patterns, and logs slow queries for debugging.

The problem

My storefront is slow, but I don’t know if it’s database queries or app logic. A missing index could be killing page load time, and I have no way to see what queries are running or how long they take.

What it does

• Hook all storage driver queries (lowdb, libSQL, SqlStorage) to log execution time and query signature
• Collect slow queries (>100ms, configurable threshold) into query_profile collection
• Track query counts per entity type (orders, products, customers) to detect N+1 patterns
• Display top 20 slowest queries by total time in admin dashboard with frequency and avg duration
• Show query execution timeline (waterfall) for a single page request to visualize database wait
• Flag N+1 patterns: if same query runs >5 times in a single page render, highlight in UI
• Export query profile as JSON for performance analysis tools

What it deliberately does NOT do

Each boundary carries its reason. A boundary without a reason gets crossed by the next person who reads this.

• Automatic index recommendations — requires storage driver-specific knowledge (SqlStorage column analysis, libSQL pragma analysis); defer to per-driver plugin extensions
• Query rewriting or caching optimization — that is app-logic tuning, not profiling infrastructure
• Real-time query tracing across distributed systems — AstroBaaS is single-server by design

Data model

query_profile collection: {id, timestamp, entity_type, operation (read|write|delete), query_ms, count (how many times in page render), query_signature}. No migration; new collection for profiling data only.

API

• GET /api/admin/queries/slow — list slow queries with count and avg duration
• GET /api/admin/queries/n-plus-one — list detected N+1 patterns
• GET /api/admin/queries/timeline/:pageId — fetch query waterfall for a page render
• POST /api/admin/queries/threshold — set slow query threshold (ms)

Every route added here must also appear in src/pages/openapi.json.ts — a test fails the build if it does not.

Admin

Admin panel shows ‘Database’ section with query profile dashboard. Cards show: (1) Slowest queries (top 20, ranked by total ms), (2) N+1 detections with entity type and frequency, (3) Query count breakdown by operation type (read/write). Clicking a query shows timeline, frequency graph, and sample execution on different pages.

The seam — why this is core

Core owns profiling UI and collection schema. Profiling hooks are in storage drivers (lowdb, libSQL, SqlStorage) — each driver logs to query_profile via the same interface. No paid seam — this is developer infrastructure.

Core owns the interface + honest query profiler; performance debugging is infrastructure, not a per-country obligation or credential.

Dependencies

• storage-drivers (lowdb, libSQL, SqlStorage must have query-time hooks)
• Existing admin dashboard

Acceptance checks

Each of these must be able to fail. Before claiming this is done, break the code deliberately and watch each one go red.

• Run a query that takes >100ms and confirm it appears in slow queries within 1 minute
• Execute same query 6 times in a single page render and confirm N+1 pattern is flagged
• Verify query_profile stores real execution times (not hardcoded), confirmed by running different queries
• Confirm query signature matches entity_type (e.g., orders query has entity_type=‘orders’)
• Verify threshold setting changes which queries appear in slow queries list

Risks

Profiling overhead can add 5-10% latency if not sampled correctly. Use statistical sampling (log 1 in 10 queries in production, all in dev) to avoid performance regression. Storing all queries can bloat database; implement retention policy (30 days max) and auto-purge old profiles.

Commercial context

The anchor is what the nearest equivalent charges on Shopify or Magento today. It is context for a pricing decision, not the decision.

---

Generated from the commerce plan. See docs/COMMERCE-PLAN.md for the full catalogue and ../../AI-GUIDE.md for how to work on this repository.