Section 11 — Performance Monitor (`/admin/performance`)

Role access: ADMIN only. VIEWER/EDITOR/CLIENT redirected to /dashboard. Layout: 3-tab interface: Application, APIs, Database. 1200px max-width. Sidebar: carbon (#12161E) background. Notes: Real-time metrics for application health. 10s polling for active metrics. Historical data retained for 30 days. All metrics displayed in time-series charts.

---

11.1 Application Metrics Dashboard#

1. Purpose#

Real-time overview of application health — CPU, memory, request rate, error rate, active sessions, and worker throughput. The "mission control" screen for platform health.

2. Visual Layout#

Section A: KPI Cards (top, full width)

• 6 cards in a row (3x2 on mobile):
  • CPU Usage: {N}% with mini gauge (0-100%). Color: green < 50%, amber < 80%, red >= 80%.
  • Memory Usage: {N} MB / {N} MB with progress bar. Same color rules.
  • Request Rate: {N} req/min with trend arrow (↑/↓/— compared to last 5 min).
  • Error Rate: {N}% with sparkline (last 10 min). Red if > 1%, amber if > 0.1%.
  • Active Sessions: {N} users currently online. Subtitle: "{N} admins, {N} clients, {N} editors".
  • Worker Throughput: `{N} jobs/min" with status: "Healthy" / "Backlogged" / "Stalled".
• Each card: white background, shadow-sm, 8px radius. Value in text-2xl text-primary. Trend arrow in green (up = good for throughput, down = good for error rate) or red (reversed for errors).

Section B: Time-Series Charts (middle, full width)

• 4 charts in a 2x2 grid (desktop), stacked (mobile):
  • CPU & Memory: Dual-axis line chart. CPU (%, left axis, blue line) + Memory (MB, right axis, green line). Last 1 hour. Tooltip: both values at timestamp.
  • Request Rate: Area chart. Requests per minute over last 1 hour. Fill color: bg-blue-100. Error requests overlay as red line.
  • Error Rate: Line chart. Percentage over last 1 hour. Y-axis 0-5% (auto-scale if > 5%). Red line. Threshold reference line at 1%.
  • Worker Queue: Stacked area chart. Jobs processed (green) + jobs queued (amber) + jobs failed (red) per minute. Last 1 hour.
• Chart time range selector above all charts: "15 min", "1 hr", "4 hr", "24 hr", "7 days" (default: 1 hr).
• Each chart has a "Download" icon (small, secondary) to export PNG.

Section C: Active Sessions Table (bottom, full width)

• Table: User | Role | Session Started | Last Activity | IP | User Agent | Actions
• User: Avatar + name + email (truncated).
• Role: Badge (ADMIN = purple, CLIENT = blue, EDITOR = green, VIEWER = gray).
• Session Started: Relative time. "5 min ago".
• Last Activity: "Just now" or "{N} sec ago" or "{N} min ago".
• IP: Parsed geolocation flag (🇮🇳, 🇺🇸, etc.) + IP address.
• User Agent: Parsed browser/OS. "Chrome 125 / macOS".
• Actions: "Terminate" (danger button, red). Ends session immediately. Confirmation modal.
• Pagination: 20 per page. Real-time: new sessions appear, ended sessions disappear.
• Search: filter by user name or email.

3. Data Source (tRPC endpoint)#

admin.getApplicationMetrics.useQuery({
  timeRange: z.enum(["15MIN", "1HR", "4HR", "24HR", "7D"]).default("1HR"),
}, { refetchInterval: 10000 }); // 10s real-time

admin.getActiveSessions.useQuery(undefined, { refetchInterval: 10000 }); // 10s real-time
admin.terminateSession.useMutation({ sessionId: z.string() });

4. Zod Schema#

const ApplicationMetricsSchema = z.object({
  current: z.object({
    cpuPercent: z.number().min(0).max(100),
    memoryUsedMb: z.number(),
    memoryTotalMb: z.number(),
    requestRatePerMin: z.number(),
    errorRatePercent: z.number(),
    activeSessions: z.number(),
    activeAdmins: z.number(),
    activeClients: z.number(),
    activeEditors: z.number(),
    workerThroughputPerMin: z.number(),
    workerStatus: z.enum(["HEALTHY", "BACKLOGGED", "STALLED"]),
  }),
  history: z.object({
    timestamps: z.array(z.date()),
    cpuPercent: z.array(z.number()),
    memoryUsedMb: z.array(z.number()),
    requestRatePerMin: z.array(z.number()),
    errorRatePercent: z.array(z.number()),
    workerProcessed: z.array(z.number()),
    workerQueued: z.array(z.number()),
    workerFailed: z.array(z.number()),
  }),
  requestRateTrend: z.enum(["UP", "DOWN", "STABLE"]).optional(),
  errorRateTrend: z.enum(["UP", "DOWN", "STABLE"]).optional(),
});

const ActiveSessionSchema = z.object({
  sessionId: z.string(),
  userId: z.string().uuid(),
  userName: z.string(),
  userEmail: z.string().email(),
  userRole: z.enum(["ADMIN", "CLIENT", "EDITOR", "VIEWER"]),
  userAvatar: z.string().url().optional(),
  startedAt: z.date(),
  lastActivityAt: z.date(),
  ipAddress: z.string().ip(),
  countryCode: z.string().length(2).optional(), // ISO country code for flag
  userAgent: z.string(),
  parsedBrowser: z.string().optional(), // e.g., "Chrome 125"
  parsedOs: z.string().optional(), // e.g., "macOS"
  isImpersonating: z.boolean().default(false),
  impersonatingClientId: z.string().uuid().optional(),
  impersonatingClientName: z.string().optional(),
});

5. Fetch Frequency#

• Real-time (10000ms / 10s): Application metrics are critical for incident response. 10s polling.
• On-demand: Time range change (15min → 1hr → 4hr, etc.), terminate session.
• SSE: When new session starts or ends, push to active sessions table. When error rate spikes (> 1%), push alert to all admin sessions.
• WebSocket (optional): For metrics streaming, use WebSocket instead of polling for sub-second updates.

6. Data Manipulations#

• CPU gauge: SVG arc gauge. 0-100% with color zones. Animated on update.
• Memory bar: memoryUsed / memoryTotal → progress bar. Color zones: green < 50%, amber < 80%, red >= 80%.
• Request rate trend: Compare current to 5 min ago. > 10% up → green ↑; > 10% down → red ↓ (unless error rate down, then green). ±10% → gray —.
• Error rate sparkline: Sparkline in KPI card (10 data points, 1 min each). Red line. Immediate visual of recent error trend.
• Worker status: HEALTHY → green badge + "{N} jobs/min"; BACKLOGGED → amber badge + "{N} queued"; STALLED → red badge + "0 jobs/min".
• Time range: "15MIN" → 15 min history; "1HR" → 1 hour; "4HR" → 4 hours; "24HR" → 24 hours; "7D" → 7 days (data points aggregated to hourly).
• Chart tooltips: Hover → tooltip with timestamp + all values at that point. Formatted with units.
• Session last activity: < 10s → "Just now"; < 60s → "{N} sec ago"; < 60min → "{N} min ago".
• Geolocation: countryCode → flag emoji. If no country code, show globe icon.
• Impersonation indicator: If isImpersonating, purple badge "IMPERSONATING" next to user name. Hover shows "Impersonating: {clientName}".
• Terminate session: Confirmation modal: "End session for {userName}? They will be logged out immediately." → mutation → row removed with fade-out animation → toast "Session terminated."

7. Rationale#

• Mission control screen: This is the screen admins check first when "something is wrong." All critical health metrics in one place. 6 KPIs + 4 charts + active sessions.
• 10s polling: CPU, memory, request rate, error rate — these change fast during incidents. 10s gives near-real-time without server overload.
• Gauge for CPU: CPU is a 0-100% metric. Gauge is the most intuitive visualization. Circular arc with color zones is instantly readable.
• Dual-axis chart: CPU (%) and memory (MB) have different units. Dual-axis chart shows correlation. "CPU spiked at 14:32 — memory also spiked. Likely a memory leak causing CPU thrashing."
• Error rate threshold: 1% is the industry standard "healthy" threshold for web apps. Red line at 1% gives immediate context.
• Worker throughput: If jobs/min drops to 0, the platform is broken. Worker status is the most critical "is the platform working?" metric.
• Active sessions: Security and capacity planning. See who's online. If 500 active sessions and memory at 90%, time to scale. If unknown IP from unusual country, investigate.
• Terminate session: Security incident response. If an admin account is compromised, terminate all their sessions immediately. One-click.
• Time range: 15min for debugging an active incident. 7 days for capacity planning and trend analysis. Default 1hr is the sweet spot for general monitoring.
• Trend arrows: Context without needing to read a chart. "Error rate is ↑ — investigate now." "Request rate is ↓ — deployment may have caused a regression."
• Impersonation in sessions: If an admin is impersonating a client, their session shows both identities. Terminating the session ends both the admin session and the impersonation context.

8. Interaction Flows#

• Time range change: Click "4 hr" → charts reload with 4-hour data → loader overlay on charts → new data rendered → URL updated.
• Chart hover: Hover over any point → tooltip with timestamp + exact values → click to zoom to that 15-minute window.
• Download chart: Click download icon on chart → PNG download of that chart (1200x600px). Filename: {metric}_{timeRange}_{YYYYMMDD}.png.
• Terminate session: Click "Terminate" → confirmation modal → "Terminate" → mutation → row fades out → toast "Session for {userName} ended."
• Session filter: Type in search → table filters live (300ms debounce) → only matching users shown.
• Real-time update: New session appears at top with fade-in animation. Ended session disappears with fade-out.
• Error rate alert: If error rate > 1%, red banner appears at top of page: "⚠️ Error rate at {N}%. Check system logs." with link to /admin/audit logs.
• Impersonation: Read-only. Cannot terminate sessions. All metrics viewable. Charts and KPIs accessible. No actions available.

9. Error States#

• Loading: Skeleton KPI cards (6) + skeleton charts (4) + skeleton table (5 rows).
• Metrics unavailable: Red banner: "Metrics collector unavailable. Check system configuration." → link to /admin/system integrations.
• No sessions: "No active sessions. Platform may be idle or session tracking is down."
• Terminate fail: Toast "Failed to terminate session. Session may have already ended."
• High resource usage: If CPU > 90% or memory > 90%, card pulses red with animation + "Critical" label.
• SSE disconnect: "Live metrics paused. Reconnecting..." → auto-reconnect.
• Historical data gap: If metrics collector was down for a period, chart shows a gap (no line). Tooltip: "No data available for this period."
• No permission: Non-ADMIN redirected.

10. Role-Based Variations#

• ADMIN: Full access — view all metrics, change time ranges, download charts, terminate sessions, view active users.
• Other roles: No access.
• Impersonation: Read-only. All metrics viewable. No terminate action. All charts and KPIs accessible.

---

11.2 API Latency Monitor#

1. Purpose#

Track API endpoint performance — which endpoints are slow, which are failing, which are getting the most traffic. Used for optimization and debugging slow client experiences.

2. Visual Layout#

Section A: Endpoint Performance Table (top, full width)

• Table: Endpoint | Method | Calls (1hr) | Avg Latency | P95 Latency | P99 Latency | Error Rate | Status | Trend
• Endpoint: Route path. e.g., /api/trpc/content.generate or /api/trpc/gbp.list
• Method: HTTP method badge. GET, POST, PUT, DELETE.
• Calls: Number of calls in selected time range. "1,247" with trend arrow (↑/↓).
• Avg Latency: "{N}ms" with color: green < 200ms, amber < 1000ms, red >= 1000ms.
• P95 Latency: "{N}ms" — 95th percentile. Color same as avg.
• P99 Latency: "{N}ms" — 99th percentile. Always shown, even if high.
• Error Rate: "{N}%" with color: green 0%, amber < 1%, red >= 1%.
• Status: HEALTHY (green), DEGRADED (amber), CRITICAL (red). Based on error rate + latency composite.
• Trend: Mini sparkline (last 10 data points) of latency over time. 50px wide, 20px tall. Line chart.
• Sorting: Default by P95 latency (highest first). Clickable headers for any column.
• Pagination: 50 per page. Search filter by endpoint path.

Section B: Endpoint Detail Panel (slide-in from right)

• Click row → panel:
  • Endpoint path + method + full description.
  • Latency distribution: Histogram (bucket chart). Buckets: 0-100ms, 100-200ms, 200-500ms, 500-1000ms, 1000-2000ms, >2000ms. Bar chart.
  • Time-series chart: Latency (avg, P95, P99) over last 1 hour. Line chart with 3 lines.
  • Error breakdown: Pie chart (or donut) of error types: 500, 502, 503, 504, 400, 401, 403, 429, etc.
  • Caller breakdown: Table of top 10 clients by call volume for this endpoint. Client name + call count + avg latency.
  • Slowest calls: Table of last 10 calls > P99. Timestamp + latency + client + request ID.

Section C: API Overview Stats (above table)

• Horizontal row: "Total endpoints: {N}" | "Healthy: {N}" | "Degraded: {N}" | "Critical: {N}" | "Total calls (1hr): {N}" | "Avg platform latency: {N}ms"
• "Critical" in red if > 0. "Degraded" in amber if > 0.

3. Data Source (tRPC endpoint)#

admin.getApiLatencyMetrics.useQuery({
  timeRange: z.enum(["15MIN", "1HR", "4HR", "24HR"]).default("1HR"),
  sortBy: z.enum(["AVG_LATENCY", "P95_LATENCY", "P99_LATENCY", "ERROR_RATE", "CALL_COUNT"]).default("P95_LATENCY"),
}, { refetchInterval: 30000 }); // 30s frequent

admin.getEndpointDetail.useQuery({
  endpoint: z.string(),
  method: z.enum(["GET", "POST", "PUT", "DELETE", "PATCH"]),
  timeRange: z.enum(["15MIN", "1HR", "4HR", "24HR"]).default("1HR"),
}, { enabled: false }); // manual trigger when panel opens

4. Zod Schema#

const EndpointMetricSchema = z.object({
  endpoint: z.string(), // e.g., "content.generate" (trpc procedure name)
  method: z.enum(["GET", "POST", "PUT", "DELETE", "PATCH"]),
  callCount: z.number(),
  callCountTrend: z.enum(["UP", "DOWN", "STABLE"]).optional(),
  avgLatencyMs: z.number(),
  p95LatencyMs: z.number(),
  p99LatencyMs: z.number(),
  errorRatePercent: z.number(),
  status: z.enum(["HEALTHY", "DEGRADED", "CRITICAL"]),
  latencyTrend: z.array(z.object({
    timestamp: z.date(),
    avg: z.number(),
    p95: z.number(),
    p99: z.number(),
  })).optional(), // mini sparkline data
});

const EndpointDetailSchema = z.object({
  endpoint: z.string(),
  method: z.string(),
  description: z.string().optional(),
  latencyDistribution: z.array(z.object({
    bucket: z.string(), // e.g., "100-200ms"
    count: z.number(),
    percentage: z.number(),
  })),
  latencyHistory: z.array(z.object({
    timestamp: z.date(),
    avg: z.number(),
    p95: z.number(),
    p99: z.number(),
  })),
  errorBreakdown: z.array(z.object({
    statusCode: z.number(),
    count: z.number(),
    percentage: z.number(),
  })),
  topCallers: z.array(z.object({
    clientId: z.string().uuid(),
    clientName: z.string(),
    callCount: z.number(),
    avgLatencyMs: z.number(),
  })),
  slowestCalls: z.array(z.object({
    timestamp: z.date(),
    latencyMs: z.number(),
    clientId: z.string().uuid().optional(),
    clientName: z.string().optional(),
    requestId: z.string(),
    statusCode: z.number(),
  })),
});

const ApiLatencyOverviewSchema = z.object({
  endpoints: z.array(EndpointMetricSchema),
  totalEndpoints: z.number(),
  healthyCount: z.number(),
  degradedCount: z.number(),
  criticalCount: z.number(),
  totalCalls: z.number(),
  avgPlatformLatencyMs: z.number(),
});

5. Fetch Frequency#

• Frequent (30000ms / 30s): API metrics change as traffic patterns shift. 30s polling.
• On-demand: Sort change, time range change, detail panel open, search filter.
• SSE: When any endpoint status changes from HEALTHY to DEGRADED/CRITICAL, push update. When error rate spikes, push alert.

6. Data Manipulations#

• Latency color coding:
  • < 200ms: green
  • 200ms - 1000ms: amber
  • > 1000ms: red
• P95/P99 formatting: Same color rules. P99 is always shown even if red — that's the point (worst-case performance).
• Status composite: HEALTHY = error rate < 0.1% AND avg latency < 200ms. DEGRADED = error rate < 1% AND avg latency < 1000ms OR either metric elevated. CRITICAL = error rate >= 1% OR avg latency >= 1000ms.
• Sparkline: 10 data points, 50px wide, 20px tall. Line chart with no axes. Quick visual of recent trend.
• Call count trend: Compare to previous equal time range. 1hr current vs 1hr prior. ↑ = more traffic, ↓ = less traffic, — = stable.
• Latency histogram: Bucketed bar chart. Shows distribution shape. "Most calls are fast (0-200ms) but a long tail goes to 2s+" — tells you it's not consistently slow, just outliers.
• Error breakdown: Donut chart with status codes. 500 = red, 502 = dark red, 503 = orange, 429 = yellow, 400 = gray, 401/403 = blue.
• Top callers: Helps identify if one client is hammering an endpoint and causing latency for others. "Client X called /content.generate 500 times in 1 hour." → can rate-limit.
• Slowest calls: Links to system logs via requestId. Click requestId → opens System Log Viewer filtered to that request.

7. Rationale#

• Endpoint-level visibility: "The app is slow" is vague. "/content.generate has P99 of 4.2s because of OpenAI API latency" is actionable. Per-endpoint metrics identify the exact bottleneck.
• P95 and P99: Average latency is misleading. If 99% of calls are 100ms and 1% are 10s, average is 200ms — looks fine. P99 reveals the pain. P95 is the "almost everyone" experience. Both are essential.
• Latency histogram: Shows the full distribution, not just a single number. Identifies bimodal distributions (fast cache hits vs slow DB queries).
• 30s polling: API traffic patterns change with user behavior. 30s is frequent enough to catch degradations but not so fast it overwhelms the metrics DB.
• Caller breakdown: Multi-tenant platform. One client shouldn't degrade performance for others. If one client is calling an expensive endpoint 1000x/hour, they need rate limiting or plan upgrade.
• Slowest calls: Directly links to request tracing. Click request ID → see full log trace of that slow call. Debuggable in 3 clicks.
• Status composite: Single status per endpoint is easier to scan than comparing two numbers. HEALTHY/DEGRADED/CRITICAL is immediately actionable.
• Sparkline: A tiny chart in a table cell is a powerful pattern. Admin sees 50 endpoints — sparklines let them spot trends without clicking into each one.
• Time range: 15min for "what just happened?" 1hr for general health. 24hr for daily patterns. Default 1hr balances recency and context.

8. Interaction Flows#

• Sort table: Click "P95 Latency" header → sorts descending. Click again → ascending. Click "Error Rate" → sorts by error rate. URL updates with sort param.
• Filter search: Type endpoint path → table filters live (debounced 300ms). "content" → shows all content-related endpoints.
• Open detail: Click row → slide-in panel with latency distribution, history chart, error breakdown, callers, slowest calls.
• Trace request: Click requestId in slowest calls → navigates to System Log Viewer with that request ID pre-filtered.
• Time range: Click "4 hr" → table reloads with 4-hour metrics. Detail panel charts also update if open.
• Download data: "Export CSV" button above table → CSV with all endpoint metrics for selected time range.
• Real-time update: If endpoint status changes from HEALTHY to DEGRADED, row border turns amber + amber badge. If to CRITICAL, red pulse animation for 5 seconds.
• Impersonation: Read-only. Table viewable. Detail panel accessible. No actions. Export disabled.

9. Error States#

• Loading: Skeleton table (10 rows) + skeleton stats.
• No metrics: "No API metrics available. Check metrics collector configuration." → link to /admin/system.
• All healthy: Subtle green banner: "All {N} endpoints healthy. Last checked: {time}."
• Critical endpoints: If any CRITICAL, red banner at top: "⚠️ {N} endpoints critical. {endpointName} has {N}ms P95 latency and {N}% error rate." with link to detail.
• Detail fail: If detail panel fails to load, toast "Endpoint detail unavailable. Metrics may have expired." → panel shows error state with retry button.
• No calls: If endpoint has 0 calls in time range, show "—" for all metrics and grayed-out row. "No traffic in last {timeRange}."
• SSE disconnect: "Live endpoint monitoring paused. Reconnecting..." → auto-reconnect.
• No permission: Non-ADMIN redirected.

10. Role-Based Variations#

• ADMIN: Full access — view, sort, filter, detail panel, trace requests, export, time range.
• Other roles: No access.
• Impersonation: Read-only. All viewing features work. No export or actions.

---

11.3 Database Performance#

1. Purpose#

Monitor database health — query performance, slow queries, connection pool, table sizes, and index health. Critical for preventing DB-related outages.

2. Visual Layout#

Section A: DB Health Cards (top, full width)

• 4 cards in a row:
  • Connections: {N} / {N} max (e.g., 12 / 100). Progress bar. Amber if > 80%, red if >= 100%.
  • Query Rate: {N} queries/sec with trend arrow.
  • Slow Queries (1hr): {N} with color: green 0, amber < 10, red >= 10.
  • Replication Lag: {N}ms (if replica configured). Green if < 1000ms, amber < 5000ms, red >= 5000ms.
• Card style: same as other KPI cards (white, shadow, 8px radius, text-2xl value).

Section B: Slow Queries Table (middle, full width)

• Table: Query | Duration | Calls (1hr) | Total Time | First Seen | Last Seen | Actions
• Query: SQL query truncated to 80 chars with "..." + "View Full" link. Syntax highlighted (basic: keywords in blue, strings in green, numbers in orange).
• Duration: "{N}ms" with color: amber if > 500ms, red if > 1000ms.
• Calls: Number of times executed in time range.
• Total Time: "{N}ms" (duration × calls). Sortable.
• First Seen: Relative time. "3 days ago".
• Last Seen: "2 min ago" or "Just now".
• Actions: "Explain" (runs EXPLAIN ANALYZE on the query, returns plan in modal) + "View in Logs" (link to system logs with query hash filter).
• Sort: Default by total time (highest first). Clickable headers.
• Pagination: 25 per page.
• Filter: "Min duration" input (default: 100ms). Only show queries slower than threshold.

Section C: Table Size & Health (bottom, full width)

• Table: Table Name | Rows | Size | Indexes | Last Vacuum | Last Analyze | Status
• Table Name: Prisma table name. e.g., Client, Content, Lead, AuditLog.
• Rows: Approximate row count (e.g., "12,847" or "1.2M").
• Size: "{N} MB" or "{N} GB".
• Indexes: Number of indexes. "{N} indexes" + "View" link to index list.
• Last Vacuum: Relative time. "2 hr ago" or "Never" (warning if > 7 days).
• Last Analyze: Relative time. Warning if > 1 day.
• Status: HEALTHY (green), NEEDS_VACUUM (amber), NEEDS_ANALYZE (amber), BLOATED (red). Composite based on vacuum age, analyze age, and estimated bloat.
• Actions: "Vacuum" (runs VACUUM ANALYZE, async, shows progress) + "Analyze" (runs ANALYZE, quick).
• Pagination: 20 per page. Search by table name.

Section D: Connection Pool Chart (above slow queries, right side)

• Line chart: active connections over last 1 hour. Y-axis: connections. X-axis: time.
• Horizontal reference line at max connections (e.g., 100). If line approaches reference, amber warning. If exceeds, red alert.
• Tooltip: "Active: {N}, Idle: {N}, Waiting: {N}" at each timestamp.

3. Data Source (tRPC endpoint)#

admin.getDatabaseMetrics.useQuery(undefined, { refetchInterval: 30000 }); // 30s frequent
admin.getSlowQueries.useQuery({
  timeRange: z.enum(["1HR", "4HR", "24HR"]).default("1HR"),
  minDurationMs: z.number().min(1).default(100),
  page: z.number().min(1).default(1),
  pageSize: z.number().min(1).max(100).default(25),
}, { refetchInterval: 30000 });
admin.getTableHealth.useQuery(undefined, { refetchInterval: 300000 }); // 5min static
admin.runExplainQuery.useMutation({ queryHash: z.string() }); // returns EXPLAIN plan
admin.runVacuum.useMutation({ tableName: z.string() }); // async, returns job ID
admin.runAnalyze.useMutation({ tableName: z.string() });

4. Zod Schema#

const DatabaseMetricsSchema = z.object({
  connections: z.object({
    active: z.number(),
    idle: z.number(),
    waiting: z.number(),
    max: z.number(),
  }),
  queryRatePerSec: z.number(),
  queryRateTrend: z.enum(["UP", "DOWN", "STABLE"]).optional(),
  slowQueryCount1hr: z.number(),
  replicationLagMs: z.number().optional(),
  connectionHistory: z.array(z.object({
    timestamp: z.date(),
    active: z.number(),
    idle: z.number(),
    waiting: z.number(),
  })),
});

const SlowQuerySchema = z.object({
  queryHash: z.string(), // normalized query hash
  queryPreview: z.string(), // truncated SQL
  queryFull: z.string(), // full SQL (optional, may be large)
  avgDurationMs: z.number(),
  maxDurationMs: z.number(),
  callCount: z.number(),
  totalTimeMs: z.number(),
  firstSeenAt: z.date(),
  lastSeenAt: z.date(),
});

const TableHealthSchema = z.object({
  tableName: z.string(),
  approximateRows: z.number(),
  sizeBytes: z.number(),
  indexCount: z.number(),
  lastVacuumAt: z.date().optional(),
  lastAnalyzeAt: z.date().optional(),
  estimatedBloatPercent: z.number().optional(),
  status: z.enum(["HEALTHY", "NEEDS_VACUUM", "NEEDS_ANALYZE", "BLOATED"]),
});

const ExplainPlanSchema = z.object({
  queryHash: z.string(),
  plan: z.array(z.record(z.any())), // EXPLAIN output (nested JSON)
  executionTimeMs: z.number().optional(),
  planningTimeMs: z.number().optional(),
  rowsReturned: z.number().optional(),
  totalCost: z.number().optional(),
});

5. Fetch Frequency#

• Frequent (30000ms / 30s): Connection count and slow queries change as traffic shifts. 30s polling.
• Static (300000ms / 5min): Table health (vacuum, analyze, bloat) changes slowly. 5min polling.
• On-demand: EXPLAIN query, VACUUM, ANALYZE, filter changes, pagination.
• SSE: If connections approach max, push alert. If replication lag spikes, push alert.

6. Data Manipulations#

• Connection bar: active / max → progress bar. Color: green < 50%, amber < 80%, red >= 100%. Waiting connections shown in red segment.
• Query rate: "{N} q/s" (1 decimal if < 10). Trend arrow.
• Slow query color: avgDurationMs → amber if > 500ms, red if > 1000ms. maxDurationMs always shown in parentheses: "847ms (max: 4.2s)".
• SQL highlighting: Basic syntax highlighting in query preview. SELECT, FROM, WHERE, JOIN in blue. String literals in green. Numbers in orange. Table names in purple. No full SQL parser needed — regex-based is sufficient.
• Table size: < 1024 MB → "{N} MB"; >= 1024 MB → "{N} GB" (1 decimal). < 1 MB → "{N} KB".
• Row count: < 1000 → exact; 1000–1M → "{N}K" (e.g., "12.8K"); > 1M → "{N}M" (e.g., "1.2M").
• Status: HEALTHY → green badge. NEEDS_VACUUM → amber badge + vacuum icon. NEEDS_ANALYZE → amber badge + chart icon. BLOATED → red badge + warning icon.
• Connection chart: Line chart with 3 lines (active, idle, waiting). Reference line at max.
• EXPLAIN plan: Formatted tree view (indented). Each node shows operation, cost, rows, time. Slow nodes highlighted in red.
• Vacuum/Analyze progress: If async vacuum running, show progress bar in table row. "Vacuuming... 45%".

7. Rationale#

• DB is the bottleneck: In most web apps, the database is the first thing to fail under load. Connection exhaustion, slow queries, and table bloat are the top 3 causes. This screen surfaces all three.
• Connection monitoring: If active + waiting >= max, new requests queue and timeout. This is the most common outage pattern. The connection card + chart are the most important widgets on this screen.
• Slow query table: "Query optimization 101" — find the slowest queries, optimize them. Ordered by total time (avg × calls) because a query that's slightly slow but called 1000x/hour is worse than a very slow query called once.
• SQL preview with syntax highlighting: Admins need to read the query to understand it. Truncated to 80 chars but expandable. Syntax highlighting makes it readable.
• EXPLAIN plan: Running EXPLAIN ANALYZE on a slow query shows the execution plan. If it's doing a sequential scan on a 10M row table, the fix is obvious (add an index). This bridges the gap between "it's slow" and "here's why and how to fix it."
• Table health: PostgreSQL tables need periodic VACUUM and ANALYZE. Without them, tables bloat and query planner makes bad decisions. Status badges tell admin when maintenance is needed.
• 30s polling for connections: Connections change fast. If a traffic spike hits, connection count goes from 10 to 90 in seconds. 30s catches this. 5min would miss it entirely.
• 5min for table health: Vacuum and analyze are maintenance operations. Their status changes over hours/days, not seconds.
• Replication lag: If using read replicas, lag > 5s means clients see stale data. Critical for read-heavy workloads. Red if > 5s.
• Connection pool chart: Visualizes connection usage over time. If there's a pattern (e.g., spikes every hour on the hour), that's a cron job or scheduled task causing connection bursts.
• Vacuum/Analyze from UI: One-click maintenance. No need to SSH into the DB and run SQL commands. Accessible to non-DBA admins. Async with progress tracking.

8. Interaction Flows#

• Run EXPLAIN: Click "Explain" on slow query row → modal opens with EXPLAIN plan → formatted tree view. "Close" button. If query is too large, shows "Query is large. EXPLAIN may take 10+ seconds." with "Run anyway" button.
• Run VACUUM: Click "Vacuum" on table row → confirmation: "Vacuum {tableName}? This may lock the table briefly." → "Vacuum" → async job started → row shows progress bar → "Vacuuming..." → on complete, row updates (last vacuum timestamp, status changes to HEALTHY if it was NEEDS_VACUUM) → toast "Vacuum complete for {tableName}.".
• Run ANALYZE: Click "Analyze" → quick operation (usually < 1s) → row updates immediately → toast "Analyze complete for {tableName}.".
• View full query: Click "View Full" on slow query → modal with full SQL, syntax highlighted.
• Filter slow queries: Change "Min duration" input → 300ms debounce → table reloads with filtered results.
• Time range: Click "4 hr" → slow queries reload with 4-hour data.
• Connection chart hover: Hover → tooltip with active/idle/waiting breakdown at that timestamp.
• Impersonation: Read-only. EXPLAIN, VACUUM, ANALYZE buttons disabled. Table viewable. Metrics viewable. No actions.

9. Error States#

• Loading: Skeleton cards + table (8 rows) + skeleton chart.
• DB metrics unavailable: Red banner: "Database metrics collector unavailable. Check database connection." → link to /admin/system integrations.
• No slow queries: "No slow queries found in last {timeRange}. All queries under {minDuration}ms." (celebratory green message).
• EXPLAIN fail: Toast "EXPLAIN failed. Query may have expired from query cache." or "Insufficient permissions to run EXPLAIN."
• Vacuum fail: Toast "Vacuum failed. Table may be locked by another process. Retry in 5 minutes." → row shows "Vacuum failed" badge.
• Connection critical: If active connections >= 90% of max, red card pulse + red banner: "⚠️ Database connections at {N}%. New requests may queue." → link to active sessions to terminate idle sessions.
• Replication lag critical: If > 5s, red banner: "⚠️ Replication lag at {N}ms. Read replicas are stale."
• No replica: "Replication lag: N/A (no replica configured)." in gray.
• SSE disconnect: "Live DB monitoring paused. Reconnecting..." → auto-reconnect.
• No permission: Non-ADMIN redirected.

10. Role-Based Variations#

• ADMIN: Full access — view metrics, run EXPLAIN, VACUUM, ANALYZE, filter, time range, pagination.
• Other roles: No access.
• Impersonation: Read-only. All metrics and tables viewable. No maintenance actions (EXPLAIN, VACUUM, ANALYZE disabled). Cannot modify database state.

---