# FinOps Insight Web Agent

A powerful, natural language interface designed to democratize access to multi-cloud cost data. This web application acts as an intelligent agent, translating human questions into precise BigQuery SQL queries to analyze billing data from AWS, GCP, Azure, and Alibaba Cloud.

## ✨ Features

-   **Natural Language Querying**: Ask questions like "Show me the top 5 most expensive AWS services last month" in plain English.
-   **Intelligent SQL Generation**: Uses LLMs (via OpenRouter or Google Gemini) to generate SQL based on your specific BigQuery schema.
-   **Smart Context Loading**: Dynamically injects only the relevant schema documentation (AWS, GCP, etc.) into the LLM prompt to ensure accuracy and reduce token usage.
-   **Interactive SQL Workspace**:
    -   **Syntax Highlighting**: Clean, readable SQL display.
    -   **Dry Run Validation**: Automatically validates generated SQL against BigQuery to check for syntax errors and estimate bytes processed *before* execution.
    -   **Auto-Fix SQL**: When validation fails, automatically retries with LLM to fix errors (up to 3 attempts).
    -   **Logic Explanation**: Provides a collapsible explanation of *how* the query works.
-   **Data Visualization**: Renders query results in responsive, sortable, paginated tables.
-   **Result Export**: Download query results as CSV or JSON files.
-   **Session Management**:
    -   **Chat History**: Persistent session storage in localStorage with automatic save.
    -   **Session Search**: Search through historical sessions by title or content.
    -   **Pin & Organize**: Pin important sessions to the top for quick access.
    -   **Import/Export**: Backup and restore session history as JSON files.
-   **Responsive Design**: Desktop sidebar with collapsible mode, mobile drawer navigation.
-   **Quick Actions**: Preset query shortcuts for common cost analysis tasks.
-   **Google Cloud Integration**: Seamlessly authenticates using Application Default Credentials (ADC) and auto-detects your quota project.

## 🛠️ Tech Stack

-   **Framework**: [Next.js 14](https://nextjs.org/) (App Router)
-   **Language**: TypeScript
-   **Styling**: [Tailwind CSS](https://tailwindcss.com/)
-   **Icons**: [Lucide React](https://lucide.dev/)
-   **Data Tables**: [@tanstack/react-table](https://tanstack.com/table)
-   **Markdown**: `react-markdown`
-   **Code Highlighting**: `react-syntax-highlighter` (Prism)
-   **Authentication**: [NextAuth.js](https://next-auth.js.org/) (Google OAuth)
-   **Database**: Google BigQuery (via `@google-cloud/bigquery`)
-   **AI/LLM**: OpenRouter API or Google Gemini (`@google/genai`)

## 🚀 Getting Started

### Prerequisites

1.  **Node.js**: v18 or higher.
2.  **Google Cloud Project**:
    -   A BigQuery dataset containing your billing data.
    -   `bigquery.jobs.create` permissions.
3.  **Google Cloud SDK**: Installed and authenticated locally.

### Installation

1.  Navigate to the web directory:
    ```bash
    cd data-agent
    ```

2.  Install dependencies:
    ```bash
    npm install
    ```
    Or use the Makefile:
    ```bash
    make install
    ```

### Configuration

1.  **Environment Variables**:
    Create a `.env.local` file in the `data-agent` directory:
    ```bash
    cp ../env.example .env.local
    ```

    Configure your LLM provider (choose one):
    ```env
    # Option 1: OpenRouter (recommended)
    OPENROUTER_API_KEY=sk-or-v1-your-key-here

    # Option 2: Google Gemini (alternative)
    GEMINI_API_KEY=your-gemini-api-key

    # Optional: Override default model (default: google/gemini-3-flash-preview)
    LLM_MODEL=google/gemini-3-flash-preview

    # Optional: server-side caps and context limits
    BIGQUERY_MAX_ROWS=1000
    LLM_CONTEXT_MAX_CHARS=60000
    LLM_JSON_DOC_MAX_CHARS=12000
    LLM_JSON_DOCS_PER_CLOUD=1
    ```

    Configure Google Auth for internal access:
    ```env
    GOOGLE_CLIENT_ID=your-google-oauth-client-id
    GOOGLE_CLIENT_SECRET=your-google-oauth-client-secret
    NEXTAUTH_SECRET=your-random-secret
    NEXTAUTH_URL=http://localhost:3000
    ALLOWED_GOOGLE_DOMAIN=your-company.com
    ALLOWED_GOOGLE_EMAILS=
    ```
    *Note: if `ALLOWED_GOOGLE_EMAILS` is set, only those emails can sign in. Otherwise, `ALLOWED_GOOGLE_DOMAIN` supports comma-separated domains (e.g. `corp.com,subsidiary.com`).*

2.  **Google Cloud Authentication**:
    This app relies on Application Default Credentials (ADC). Run the following command in your terminal:
    ```bash
    gcloud auth application-default login
    ```
    *Note: The app will automatically use the project set in your gcloud config as the quota project for running queries.*

3.  **BigQuery Permissions**:
    The authenticated user must have the following permissions in the `gcp-digital-bi` project:
    -   **Project Level**: `roles/bigquery.jobUser` (to run queries).
    -   **Dataset Level**: `roles/bigquery.dataViewer` for the following datasets:
        -   **AWS**: `aws_nonprod_billing` (us-west1), `aws_prod_billing` (us-west2), `aws_billing_no_payer` (us-west2)
        -   **GCP**: `gcp_billing_v2` (US multi-region)
        -   **Azure**: `azure_billing` (us-west2)
        -   **Alibaba**: `alibaba_cloud` (us-west2)

    *Important: Some AWS datasets are in `us-west1`. Keep each SQL statement within one BigQuery location; mixed-location statements are not supported by BigQuery.*

4.  **Google OAuth App Setup**:
    Create an OAuth Client ID in Google Cloud Console:
    - **Application type**: Web application
    - **Authorized redirect URIs**:
      - `http://localhost:3000/api/auth/callback/google`
      - `https://your-production-domain/api/auth/callback/google`

### Running the Application

Start the development server:

```bash
npm run dev
```
Or with Make:
```bash
make dev
```

Open [http://localhost:3000](http://localhost:3000) in your browser.

### Docker Deployment (Service Account)

This is the recommended deployment model for VM/docker-compose. The container uses ADC via a service account JSON key.

1.  Create a GCP service account and grant BigQuery permissions (jobUser + dataViewer on datasets).
2.  Download the JSON key to the VM, e.g. `/opt/finops/sa.json`.
3.  Create a `.env` next to `docker-compose.yml` (repo root) with:
    ```env
    OPENROUTER_API_KEY=sk-or-v1-your-key-here
    GEMINI_API_KEY=your-gemini-api-key
    LLM_MODEL=google/gemini-3-flash-preview
    GOOGLE_CLIENT_ID=your-google-oauth-client-id
    GOOGLE_CLIENT_SECRET=your-google-oauth-client-secret
    NEXTAUTH_SECRET=your-random-secret
    NEXTAUTH_URL=https://your-domain
    ALLOWED_GOOGLE_DOMAIN=your-company.com
    ALLOWED_GOOGLE_EMAILS=
    BIGQUERY_MAX_ROWS=1000
    LLM_CONTEXT_MAX_CHARS=60000
    LLM_JSON_DOC_MAX_CHARS=12000
    LLM_JSON_DOCS_PER_CLOUD=1
    SA_JSON_PATH=/opt/finops/sa.json
    ```
4.  Build and run:

    ```bash
    docker-compose up --build
    ```

Notes:

-   `GOOGLE_APPLICATION_CREDENTIALS` is set by `docker-compose.yml` and points to `/secrets/sa.json`.
-   The image bundles `docs/` so schema references are available at runtime.

#### Updating after code changes (docker-compose)

This `docker-compose.yml` builds the image from local source (no source bind-mount), so **code changes require a rebuild** to take effect.

-   **Fast redeploy (recommended)**:

    ```bash
    docker-compose up -d --build data-agent
    ```

-   **Force recreate the container (more deterministic)**:

    ```bash
    docker-compose up -d --build --force-recreate --no-deps data-agent
    ```

-   **Only changed env/config (no image rebuild needed)**:

    ```bash
    docker-compose up -d data-agent
    # or:
    docker-compose restart data-agent
    ```

-   **Verify after deploy**:

    ```bash
    docker-compose ps
    docker-compose logs -f data-agent
    ```

Tip:

-   If you want "edit code and refresh immediately", that's a dev workflow. Run locally with `make dev` (recommended), or create a separate compose override that bind-mounts the source and runs `npm run dev`.

### Build and Lint

```bash
make build
make lint
```

### Test

```bash
make test
```

This runs automated checks for API request validation, SQL read-only guards, and docs context loading behavior.

## 📂 Project Structure

```
data-agent/
├── src/
│   ├── app/
│   │   ├── api/
│   │   │   ├── auth/[...nextauth]/  # NextAuth authentication routes
│   │   │   ├── bigquery/route.ts    # BigQuery dry-run & execute API
│   │   │   └── chat/route.ts        # LLM chat completion API
│   │   ├── auth/signin/page.tsx     # Custom sign-in page
│   │   ├── globals.css              # Global styles & Tailwind config
│   │   ├── layout.tsx               # Root layout with providers
│   │   ├── page.tsx                 # Main chat page composition
│   │   └── providers.tsx            # NextAuth SessionProvider wrapper
│   ├── components/
│   │   ├── chat/
│   │   │   ├── history-panel.tsx    # Sidebar history UI (full + collapsed)
│   │   │   └── message-bubble-content.tsx  # Assistant message content blocks
│   │   ├── result-table.tsx         # Query result table with export
│   │   └── sql-viewer.tsx           # SQL syntax highlighting
│   ├── hooks/
│   │   └── use-auto-scroll.ts       # Shared auto-scroll behavior
│   ├── lib/
│   │   ├── auth.ts                  # NextAuth configuration
│   │   ├── auth-allowed.ts          # Domain whitelist validation
│   │   ├── chat-request.ts          # Chat API payload parsing helpers
│   │   ├── bigquery-request.ts      # BigQuery API payload/safety helpers
│   │   ├── chat-session.ts          # Chat session types and persistence helpers
│   │   └── docs-loader.ts           # Schema documentation loader
│   └── middleware.ts                # Route protection middleware
├── public/                          # Static assets
├── package.json
├── tailwind.config.ts
└── tsconfig.json
```

## 🛡️ Security Note

-   **Credentials**: Never commit your `.env.local` file. It is already ignored by `.gitignore`.
-   **BigQuery Access**: The app runs queries with the permissions of the locally authenticated user. Ensure your user has appropriate read access to the billing datasets.
-   **Auth Restriction**: Access is gated by Google OAuth and allowlist settings (`ALLOWED_GOOGLE_DOMAIN` or `ALLOWED_GOOGLE_EMAILS`).
-   **Session Data**: Chat history is stored in browser localStorage only; no server-side persistence.
-   **Dependency Hardening**: `lodash` and `preact` are pinned via `package.json > overrides` to patched versions.
-   **Next.js Vulnerabilities**: Current `next@14.2.35` advisories require major upgrade (`next@16.x`) for full package-level remediation. Runtime mitigation is applied by disabling built-in image optimizer in `next.config.mjs` (`images.unoptimized = true`).

## 📋 TODO

-   [ ] Dark mode toggle (CSS classes prepared but no toggle UI)
-   [ ] Query result visualization charts (recharts dependency installed but unused)
-   [ ] Save frequently used query templates
-   [ ] LLM response streaming output

## 🧯 Troubleshooting

-   **Sign-in keeps returning `NotAllowed`**
    -   Check `ALLOWED_GOOGLE_EMAILS` first. If set, only those exact emails can access.
    -   If `ALLOWED_GOOGLE_EMAILS` is empty, verify `ALLOWED_GOOGLE_DOMAIN` values (comma-separated, no `@` prefix required).

-   **BigQuery auth errors (`Unauthorized`, ADC missing)**
    -   Local dev: run `gcloud auth application-default login`.
    -   Docker: confirm `SA_JSON_PATH` points to a valid service account key and is mounted to `/secrets/sa.json`.
    -   Verify required roles: `roles/bigquery.jobUser` at project level and `roles/bigquery.dataViewer` on target datasets.

-   **Query fails with multi-location error**
    -   BigQuery cannot run one statement across mixed locations (`US` + `us-west1`/`us-west2`).
    -   Split the request into separate SQL queries per location and run them independently.

## 📄 License

Internal use only.