# ai_data_analysis

**Repository Path**: cat-lin/ai_data_analysis

## Basic Information

- **Project Name**: ai_data_analysis
- **Description**: ai agent 分析数据元信息，整理数据表关系，输出SQL，Chart和Dashboard
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2026-06-29
- **Last Updated**: 2026-06-29

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# AI Data Analytics

## Description

An AI-powered data analytics platform that helps developers explore and analyze their databases intelligently.

**Workflow**

1. Users connect to a database via the front-end by providing connection details (e.g., from BI tools).
2. Database metadata is sent to a pipeline of specialized AI agents for analysis.
3. Agents collaborate to:
   - Discover correlations between tables
   - Infer the industry or domain of the data
   - Recommend suitable analysis types
   - Generate SQL for data analysis
4. Results are consolidated into **charts** and an auto-generated **draft dashboard** (12-column grid).

| charts | dashboards | dashboard_components |
|--------|------------|----------------------|
| `query_sql`, `viz_type`, `viz_config` | `grid_columns=48`, `row_height=20px`, `global_filters` | `layout` JSONB per tile |

5. The front-end executes SQL against the back-end and renders ECharts visualizations; dashboards support drag-resize layout editing and filter placeholders in SQL (`{{ filter_* }}`).

## Architecture

```
ai_data_analysis/
├── backend/          # Flask API, agents, services
├── frontend/         # React + ECharts UI
├── docs/             # API contract and design notes
├── docker-compose.yml
└── docker-compose.prod.yml
```

**Data flow**

```mermaid
flowchart LR
  subgraph FE [Frontend]
    Conn[Connect]
    Units[Charts library]
    Dash[Dashboards]
    Charts[ECharts]
  end
  subgraph BE [Backend Flask]
    API[REST API]
    Meta[Metadata]
    SQL[SQL executor]
  end
  subgraph AI [Agents]
    Pipeline[Agent pipeline]
  end
  UserDB[(User database)]
  AppDB[(PostgreSQL)]

  Conn --> API
  API --> Meta --> UserDB
  Meta --> Pipeline --> AppDB
  Units --> API --> SQL --> UserDB
  SQL --> Charts
```

| Layer | Responsibility |
|-------|----------------|
| Frontend | Connection UI, workspace, chart library, dashboard grid editor, explore |
| API | Auth boundaries, orchestration, read-only SQL execution |
| Agents | Schema analysis, recommendations, SQL generation → charts + draft dashboard |
| App DB | Connections, charts, dashboards, datasets, agent run history |

API endpoints are documented in [docs/api.md](docs/api.md).

## Tech Stack

### Backend
- Database
    - PostgreSQL
- Language
    - Python
- Framework
    - Flask

### Frontend
- React
- ECharts

### Development
- Docker Compose

### Production
- Docker Compose (`docker-compose.prod.yml`)
- Nginx (static frontend + API reverse proxy)
- Gunicorn (WSGI)

## Quick Start

1. Copy environment variables:

   ```bash
   cp .env.example .env
   ```

2. Start all services:

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

3. Open the app:

   - Frontend: http://localhost:5173
   - API health: http://localhost:5000/api/health

## Local Development (without Docker)

**Backend**

```bash
cd backend
python -m venv .venv
.venv\Scripts\activate   # Windows
pip install -r requirements.txt
set DATABASE_URL=postgresql://ai_data:ai_data@localhost:5432/ai_data
flask --app wsgi run --debug
```

**Frontend**

```bash
cd frontend
npm install
npm run dev
```

PostgreSQL must be running locally (or use `docker compose up postgres` only).

## Frontend (Phase 3)

Routes:

| Path | Page |
|------|------|
| `/` | Landing + saved connections |
| `/connect` | Database connection form |
| `/settings/llm` | LLM profiles (multiple OpenAI-compatible endpoints) |
| `/workspace/:connectionId` | Schema tree, analysis units, chart preview |

Open http://localhost:5173 after `docker compose up`. The workspace runs agent analysis, polls pipeline status, executes unit SQL, and renders ECharts (or a table for `chart_type: table`).

Use the **明亮 / 深沉** toggle in the navigation bar to switch light and dark themes. Your choice is saved in `localStorage`.

## Quality & integration (Phase 4)

| Area | Behavior |
|------|----------|
| End-to-end | Connect → analyze → list units → execute SQL → chart |
| Error messages | Actionable copy for connection, query timeout, and agent failures |
| Metadata performance | Paginated schema API (`limit` / `offset`); agent pipeline loads all pages internally |
| SQL safety | Read-only `SELECT` sandbox, row cap, statement timeout |
| Credential hygiene | Passwords encrypted at rest; errors redact connection URLs |

Run the full test suite inside Docker:

```bash
docker compose exec backend sh -c "RUN_INTEGRATION_TESTS=1 TEST_DATABASE_URL=postgresql://ai_data:ai_data@postgres:5432/ai_data_test USER_TEST_DB_HOST=postgres pytest tests/ -v"
```

Services expose health checks: Postgres (`pg_isready`), backend (`/api/health`), frontend (dev server).

## Production deployment (Phase 5)

Production uses multi-stage images: Nginx serves the built React app and reverse-proxies `/api` to Gunicorn. PostgreSQL and the API are not published to the host by default.

1. Copy production environment variables and **set strong `SECRET_KEY` and `POSTGRES_PASSWORD`**:

   ```bash
   cp .env.production.example .env
   ```

2. Build and start the production stack:

   ```bash
   docker compose -f docker-compose.prod.yml up --build -d
   ```

3. Open the app:

   - Application: http://localhost (or `http://localhost:$APP_PORT` if `APP_PORT` is not 80)

| Mode | Command | Frontend | API |
|------|---------|----------|-----|
| Development | `docker compose up` | Vite `:5173` | Flask `:5000` |
| Production | `docker compose -f docker-compose.prod.yml up` | Nginx `:80` | internal only |

Production frontend calls `/api` on the same origin — no `VITE_API_URL` required.

## Testing (Phase 1)

**Unit tests** (SQL validation, encryption):

```bash
cd backend
pip install -r requirements.txt
pytest tests/test_sql_executor.py -v
```

**Integration tests** (requires PostgreSQL; uses `ai_data_test` database):

```bash
# Create test database once (if not created by init script)
docker compose exec postgres psql -U ai_data -d postgres -c "CREATE DATABASE ai_data_test;"

cd backend
set RUN_INTEGRATION_TESTS=1
set TEST_DATABASE_URL=postgresql://ai_data:ai_data@localhost:5432/ai_data_test
set USER_TEST_DB_HOST=localhost
pytest tests/ -v
```

Inside Docker:

```bash
docker compose exec backend sh -c "RUN_INTEGRATION_TESTS=1 TEST_DATABASE_URL=postgresql://ai_data:ai_data@postgres:5432/ai_data_test USER_TEST_DB_HOST=postgres pytest tests/ -v"
```

### Trigger AI analysis (Phase 2)

```bash
# Replace CONNECTION_ID with a real connection id
curl -X POST http://localhost:5000/api/connections/CONNECTION_ID/analyze

# Poll run status
curl http://localhost:5000/api/agent-runs/RUN_ID

# List generated units
curl "http://localhost:5000/api/analysis-units?connection_id=CONNECTION_ID"
```

Set `AI_API_KEY` in `.env` as a fallback, or configure one or more **LLM profiles** under `/settings/llm`. Each database connection can select which profile powers its analysis agents. Without a key, heuristic agents run automatically.