Pulse — AI Ops Copilot for SMBs

Ask your Aurora PostgreSQL e-commerce data in plain English → safe SQL → charts + tables, plus a confirmed, audited action layer for safe writes.

Comment

Pulse — AI Ops Copilot for SMBs

🎯 Project Summary

Pulse is a natural-language interface to Amazon Aurora PostgreSQL that lets non-technical small-business operators ask their operational database in plain English and take safe, human-confirmed actions—all with a complete audit trail.

Ask: "Which orders are stuck?" → instant chart + visible SQL + latency badge
Act: "Flag them for review" → confirmation modal → parameterized transaction → audit log
Audit: Live timeline of every action with full payload visibility

The technical centerpiece is a five-layer safety pipeline that ensures AI-generated SQL can never write or access system catalogs, even if the model hallucinates destructive statements.

💡 The Problem

SMB operators sit on a goldmine of operational data—but the people who need answers can't write SQL, and the people who can write SQL are busy fielding ad-hoc requests. Dashboards are rigid. Spreadsheets are stale. Direct database access is too risky.

Existing "chat with your data" tools either:

  • Let the AI generate arbitrary SQL and run it as admin (a security nightmare)
  • Are read-only (no actions, no ops workflows)
  • Lack audit trails (compliance and accountability gone)

Why it matters: SMBs lose money to slow decisions. An ops copilot that answers questions instantly and lets teams act on them safely could unlock thousands of dollars in efficiency per month.

✨ The Solution: Pulse

Three connected surfaces:

1. Ask (NL → Safe SQL → Charts)

  • Type a question: "Revenue by category in the last 30 days"
  • Claude generates a structured JSON plan (intent, SQL, chart_spec, explanation)
  • Server validates the SQL: SELECT-only, allowlisted tables, single statement, forced LIMIT
  • Executes on a read-only Postgres role with a 4s statement_timeout
  • Renders chart + table + visible SQL + latency badge
  • Logged to query_history for auditing and replay

2. Act (Propose → Confirm → Execute → Audit)

  • AI proposes one of three allowlisted actions (flag stuck orders, create refund, adjust inventory)
  • UI shows a confirmation modal: "Flag 15 orders as 'no shipment in 5+ days'?"
  • User confirms → parameterized transaction runs on the RW role
  • Action recorded to audit_log with full payload (args + affected rows)
  • Audit panel updates live with the new entry

3. Audit (Real-time timeline)

  • Every action is immutable: actor, action_type, entity_type, created_at, full JSONB payload
  • Live audit panel shows recent actions with summary ("flagged 15 orders")
  • Searchable, filterable, compliance-ready

Plus: Query History panel

  • Shows last 5 executed NL questions
  • Displays the generated SQL, row count, latency
  • Proves the AI→SQL pipeline is working; enables replay

🛡️ The Safety Story (Five Independent Layers)

Layer 1: Structured JSON (no raw SQL strings)

  • Model returns { intent, sql?, action?, chart_spec?, explanation } validated by Zod
  • Never a raw SQL string that could be executed blindly

Layer 2: Zod Validation

  • Every JSON response is parsed against a strict schema
  • Invalid responses are rejected; user sees "I couldn't understand that safely"

Layer 3: SQL Parser Allowlist

  • Node-sql-parser checks: single statement, SELECT/WITH only, no DML/DDL keywords
  • Allowlist of tables: customers, products, orders, order_items, refunds, shipments, inventory_adjustments
  • Rejects any query not matching these rules

Layer 4: Read-Only Postgres Role

  • All AI-generated SQL runs as pulse_readonly, a role that:
    • Has SELECT-only privilege
    • Cannot READ system catalogs (no introspection)
    • Cannot WRITE anything
    • Even if the model emits DROP TABLE orders, the role lacks privilege → query fails safely

Layer 5: Statement Timeout

  • Role-level statement_timeout = 4s enforces a hard limit
  • Runaway queries (Cartesian products, infinite loops) cannot exhaust database resources

Result: An attacker would need to compromise all 5 layers simultaneously. Writes NEVER use model SQL — they go through parameterized, manually-audited action handlers. Audit log is immutable. This is production-ready.

🏗️ Tech Stack

Layer Choice Why
Frontend Next.js 16 App Router on Vercel Required by H0; rapid iteration with Tailwind
Database Amazon Aurora PostgreSQL (Serverless v2) Relational + transactional; lowest-latency co-location with Vercel; enables least-privilege RO role; JSONB audit_log for full payload visibility
DB ORM Drizzle ORM + pg Pool Typed schema migrations; raw pool for guarded AI queries
Two DB Roles pulse_app (RW) + pulse_readonly (RO, SELECT-only) The security centerpiece; AI can only read; humans approve all writes
AI Anthropic Claude (Opus 4.8 for SQL, Haiku for summaries) Best-in-class reasoning; structured output + caching for cost control
Charts Recharts Beautiful, responsive, handles all 4 chart types (number, bar, line, pie)
Validation Zod Type-safe parsing of model outputs and action args
SQL Parsing node-sql-parser Allowlist validation before execution
Styling Tailwind CSS Dark SaaS aesthetic, professional polish

Why Aurora PostgreSQL?

  • Relational model: Orders, customers, products, refunds, shipments—your operational data lives in foreign-key relationships. Postgres excels at this.
  • JSONB audit_log: Full payload visibility without schema sprawl. Perfect for compliance and debugging.
  • Least-privilege design: Postgres roles are the only production RDBMS where you can actually prevent the AI query path from writing. This is a tiebreaker for enterprise buyers.
  • Vercel integration: Aurora Marketplace auto-injects connection env vars; IAM auth via Vercel OIDC tokens; no SSH keys or manual provisioning.
  • Serverless pricing: Pay per second; no idle compute; perfect for a startup or SMB.

🎬 Demo Flow (Under 3 minutes)

  1. Show KPI home: Revenue today, stuck orders, low-stock SKUs, refund rate
  2. Ask "How many orders this week?" → Number chart (47)
  3. Ask "Revenue by category" → Bar chart with legend
  4. Point to visible SQL + latency badge — "The AI generated this; here's the proof"
  5. Ask "Flag all stuck orders for review" → Confirmation modal appears
  6. Confirm → Order flags inserted, audit log populates live
  7. Type "delete all orders" → Safely refused ("This action is not supported")
  8. Show Query History panel — Last 5 questions + SQL + latency
  9. Close: "Every question is audited, every action requires confirmation, and every SQL query runs as a read-only Postgres role. This is safe enough for your business-critical database."

🚀 What's Shipped & What's Tested

Offline Tests (no DB or API key needed):

  • ✅ SQL Guard: 14/14 test cases (deletion blocked, injection blocked, allowlist enforced)
  • ✅ Action validation: 9/9 test cases (Zod arg validation, bounds checking)
  • ✅ TypeScript: zero errors
  • ✅ Build: production Next.js build passes

Live Testing (requires Aurora + Claude):

  • ✅ Ask a question → chart appears
  • ✅ Propose an action → modal → confirm → audit entry
  • ✅ Unsafe query → safely refused
  • ✅ Query history → shows last executed questions
  • ✅ KPI cards → load real data

🎯 Why Pulse Wins

1. Solves a real problem: SMBs waste time on ad-hoc SQL requests. Pulse is immediately monetizable as a SaaS product.

2. Technical depth: Five-layer safety is not a marketing buzzword—it's built into every layer of the code. AWS database experts will appreciate the two-role design.

3. Differentiated: 99% of "chat with your data" projects are read-only. Pulse's Act + Confirm + Audit loop is complete ops workflow, not a chatbot.

4. Production-ready: Parameterized queries, JSONB audit log, Postgres role-level enforcement, compiled schemas, full type safety. This is not a weekend project; it's enterprise-quality code.

5. Aurora-focused: We chose Aurora PostgreSQL deliberately (not generic "database support"). The two-role design, JSONB audit_log, and Serverless pricing are differentiators.

Built With

  • amazon
  • anthropic
  • aurora
  • claude
  • drizzle
  • next.js
  • node-sql-parser
  • orm
  • postgresql
  • recharts
  • tailwind
  • zod
Share this project:

Updates