Flowbit AI — Invoice Analytics Dashboard

A production-ready invoice analytics dashboard with AI-powered chat-with-data. Ask questions about your invoices in plain English, and the AI generates SQL, executes it, and visualizes the results with auto-rendered charts.

Features

Dashboard

• KPI Cards: Total spend (YTD), invoice count, documents uploaded, average invoice value
• Invoice Trends: Monthly spend over time (area chart)
• Top Vendors: Horizontal bar chart of top 10 vendors by spend
• Category Spend: Donut chart of spending across Technology, Operations, Marketing, Facilities
• Cash Outflow Forecast: Bar chart of upcoming payments by aging bucket (Overdue, 0-7 days, 8-30 days, etc.)
• Invoices by Vendor: Scrollable table with vendor, category, invoice count, and total spend

Invoice Management

• List View: Searchable, filterable, sortable table with pagination
  • Filter by status (PENDING, PAID, OVERDUE, PARTIAL, CANCELLED)
  • Sort by issue date, due date, total amount, or status
  • Color-coded status badges and overdue highlighting
• Create Invoice: Multi-section form with dynamic line items, auto-calculated totals, vendor/customer dropdowns
• Invoice Detail: Full view with vendor/customer info, line items, payments, outstanding amount, status management, and print support
• Delete Invoice: Confirmation dialog with toast feedback

Chat with Data (AI-Powered)

• Natural Language Queries: Ask questions like "Show me all overdue invoices" or "Top 5 vendors by spending"
• SQL Generation: Uses z-ai-web-dev-sdk LLM to generate SQLite-safe SQL from your question
• Auto Visualization: Automatically renders the appropriate chart (bar, line, pie, or table) based on the data shape
• Smart Insights: Calculates totals, averages, min/max for numeric columns
• SQL Transparency: View the generated SQL in a collapsible code block
• Safety: Only read-only SELECT queries are allowed — INSERT/UPDATE/DELETE/DDL are rejected

Authentication

• Clerk Integration: Set CLERK_SECRET_KEY and NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY to use Clerk
• Demo Mode: Without Clerk keys, the app runs in demo mode with a local user (demo@analytics.app / demo1234)

UI/UX

• Responsive: Mobile-first design that works on all screen sizes
• Dark Mode: Toggle between light and dark themes
• Accessible: Semantic HTML, ARIA labels, keyboard navigation
• Polished: Subtle animations, hover effects, loading skeletons, toast notifications

Tech Stack

Layer	Technology
Framework	Next.js 16 (App Router)
Language	TypeScript 5
Styling	Tailwind CSS 4 + shadcn/ui (New York)
Database	SQLite via Prisma ORM
Charts	Recharts
AI	z-ai-web-dev-sdk (LLM for SQL generation)
Auth	Clerk (with demo-mode fallback)
State	TanStack Query (server), React state (client)
Icons	Lucide React

Quick Start (Local Development)

Prerequisites

• Node.js 22+ or Bun 1.3+
• SQLite (included — no separate DB server needed)

Install & Run

# Install dependencies
bun install
# Set up the database
bun run db:push
bun run db:seed # Creates 120 sample invoices, 12 vendors, 4 customers# Start the dev server
bun run dev

Open http://localhost:3000 and sign in with:

• Email: demo@analytics.app
• Password: demo1234

Deployment with Docker

Using Docker Compose (recommended)

1. Create a .env file (optional — for Clerk auth):

CLERK_SECRET_KEY=sk_test_your_key_hereNEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=pk_test_your_key_here

2. Build and run:

docker compose up --build -d

3. Access the app at http://localhost:3000

The SQLite database is persisted in a Docker volume (analytics-data), so your data survives container restarts.

Using Docker directly

# Build the image
docker build -t flowbit-analytics .# Run the container
docker run -d \
--name flowbit-analytics \
-p 3000:3000 \
-v flowbit-data:/app/data \
-e DATABASE_URL=file:/app/data/custom.db \
flowbit-analytics

Environment Variables

Variable	Required	Default	Description
DATABASE_URL	Yes	file:./db/custom.db	SQLite database connection string
CLERK_SECRET_KEY	No	—	Clerk server-side key (enables Clerk auth)
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY	No	—	Clerk client-side key (enables Clerk auth)
NODE_ENV	No	development	Set to production for production builds

Project Structure

src/
├── app/
│ ├── api/ # API routes (REST endpoints)
│ │ ├── auth/ # Auth endpoints (me, sign-in, sign-out)
│ │ ├── invoices/ # Invoice CRUD
│ │ ├── stats/ # Dashboard KPIs
│ │ ├── invoice-trends/ # Monthly trend data
│ │ ├── vendors/ # Vendors + top 10 by spend
│ │ ├── category-spend/ # Spend by vendor category
│ │ ├── cash-outflow/ # Payment forecast by aging bucket
│ │ ├── invoices-by-vendor/ # Aggregated invoices per vendor
│ │ ├── customers/ # Customer list
│ │ ├── chat-with-data/ # AI-powered natural language query
│ │ └── health/ # Health check
│ ├── dashboard/ # Dashboard page
│ ├── invoices/ # Invoice list, new, detail pages
│ ├── chat/ # Chat with Data page
│ ├── vendors/ # Vendors page
│ ├── settings/ # Settings page
│ ├── sign-in/ # Sign-in page
│ ├── layout.tsx # Root layout (providers, fonts)
│ ├── page.tsx # Home (redirects to /dashboard)
│ └── globals.css # Global styles + Tailwind
├── components/
│ ├── ui/ # shadcn/ui components
│ ├── dashboard/ # Dashboard-specific components
│ ├── invoices/ # Invoice management components
│ ├── chat/ # Chat interface components
│ ├── vendors/ # Vendor components
│ ├── settings/ # Settings components
│ ├── sidebar.tsx # App sidebar
│ ├── top-bar.tsx # App top bar
│ ├── dashboard-shell.tsx # Layout wrapper
│ ├── app-providers.tsx # Clerk + QueryClient providers
│ └── theme-provider.tsx # next-themes provider
├── lib/
│ ├── auth.ts # Auth abstraction (Clerk + demo)
│ ├── auth-client.ts # Client-side auth helpers
│ ├── analytics.service.ts # Business logic / data access
│ ├── db.ts # Prisma client
│ ├── format.ts # Currency/date/status formatters
│ └── utils.ts # shadcn utility
├── hooks/
│ ├── use-analytics.ts # TanStack Query hooks for analytics
│ ├── use-mobile.ts # Mobile breakpoint hook
│ └── use-toast.ts # Toast hook
├── middleware.ts # Auth middleware (Clerk or demo)
prisma/
└── schema.prisma # Database schema (Vendor, Customer, Invoice, LineItem, Payment, User)
scripts/
└── seed.ts # Database seed script

API Endpoints

Endpoint	Method	Description
/api/health	GET	Health check
/api/auth/me	GET	Current user info
/api/auth/sign-in	POST	Sign in (demo mode)
/api/auth/sign-out	POST	Sign out
/api/stats	GET	Dashboard KPIs
/api/invoices	GET, POST	List / create invoices
/api/invoices/[id]	GET, PATCH, DELETE	Get / update / delete invoice
/api/invoice-trends	GET	Monthly invoice trends
/api/vendors	GET	All vendors
/api/vendors/top10	GET	Top 10 vendors by spend
/api/category-spend	GET	Spend by category
/api/cash-outflow	GET	Cash outflow forecast
/api/invoices-by-vendor	GET	Invoices aggregated by vendor
/api/customers	GET	All customers
/api/chat-with-data	POST	AI-powered natural language query

Chat with Data Examples

Try these questions in the Chat page:

• "Show me all overdue invoices"
• "Top 5 vendors by spending"
• "Monthly invoice trends for the last 6 months"
• "What's the average invoice amount?"
• "Total spend by category"
• "Show me upcoming payment forecasts"
• "How many invoices are pending?"
• "Which vendor has the most invoices?"

Database Schema

Vendor ──< Invoice >── Customer
│
├──< LineItem
└──< Payment

• Vendor: name, category (Technology/Operations/Marketing/Facilities), taxId, address
• Customer: name, address, email
• Invoice: invoiceNumber, issueDate, dueDate, subTotal, taxAmount, totalAmount, status
• LineItem: description, quantity, unitPrice, totalPrice
• Payment: paidDate, amount, paymentMethod

Invoice statuses: PENDING, PAID, OVERDUE, PARTIAL, CANCELLED

Switching to Clerk Auth

1. Get your API keys from Clerk Dashboard
2. Set environment variables:

CLERK_SECRET_KEY=sk_test_your_keyNEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=pk_test_your_key

3. Restart the app. The middleware will automatically use Clerk instead of demo mode.

You can customize the sign-in page by replacing the form in src/app/sign-in/page.tsx with Clerk's <SignIn /> component from @clerk/nextjs.

License

MIT