Database Query Monitoring

Free — GPL coresize Midea, not a commitment

Generated from docs/plan/core/database-query-monitoring/PLAN.md in the project's planning notes, which are not part of the public repository. Nothing described below is implemented — it is the written plan for it.

An idea, not a commitment. It has a written plan but no place in the build order and no date. What is committed.

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 CONTRIBUTING.md for how to work on this repository.