# 📊 Chart-Board

> A self-hosted, open-source data visualization and dashboard platform built with CodeIgniter 4 (CI4). Connect your databases and APIs, build stunning dashboards, and share insights with your team — all from a clean, modern interface.

---

## Table of Contents

1. [Overview](#overview)
2. [Features](#features)
3. [Tech Stack](#tech-stack)
4. [System Requirements](#system-requirements)
5. [Installation](#installation)
6. [Configuration](#configuration)
7. [Project Structure](#project-structure)
8. [UI typography (shell alignment)](#ui-typography-shell-alignment)
9. [Modules & Functionality](#modules--functionality)
   - [Authentication & User Management](#1-authentication--user-management)
   - [Workspace Management](#2-workspace-management)
   - [Data Source Connections](#3-data-source-connections)
   - [Query Builder](#4-query-builder)
   - [Chart Builder](#5-chart-builder)
   - [Dashboard Builder](#6-dashboard-builder)
   - [API Data Source Support](#7-api-data-source-support)
   - [Alerts & Notifications](#8-alerts--notifications)
   - [Sharing & Embedding](#9-sharing--embedding)
   - [Audit Logs](#10-audit-logs)
10. [Supported Chart Types](#supported-chart-types)
11. [Supported Data Sources](#supported-data-sources)
12. [REST API Reference](#rest-api-reference)
13. [Roles & Permissions](#roles--permissions)
14. [Security Considerations](#security-considerations)
15. [Roadmap](#roadmap)
16. [Contributing](#contributing)
17. [License](#license)

---

## Overview

**Chart-Board** is a lightweight, powerful, and extensible analytics dashboard platform. It is designed for developers, analysts, and teams who need to visualize data from multiple sources — databases, REST APIs, CSV files — without writing front-end code from scratch.

Inspired by tools like Metabase and Chartbrew, Chart-Board is tailored for teams who prefer a self-hosted solution with full control over their data and infrastructure, built on the familiar and lightweight **CodeIgniter 4** PHP framework.

---

## Features

- 🔌 **Multi-source Connections** — MySQL, PostgreSQL, MongoDB, REST APIs, CSV uploads
- 🧱 **Drag-and-Drop Dashboard Builder** — Arrange, resize, and compose charts freely
- 📈 **Rich Chart Types** — Bar, Line, Pie, Donut, Area, Scatter, Table, KPI Cards, Funnel, Gauge
- 🔍 **Visual Query Builder** — Build queries without SQL knowledge; raw SQL mode also supported
- 👥 **Team Workspaces** — Organize dashboards by team or project
- 🔐 **Role-Based Access Control** — Admin, Editor, Viewer roles with fine-grained permissions
- 🔔 **Alerts & Notifications** — Threshold-based alerts via Email and Slack Webhook
- 🔗 **Public Sharing & Embedding** — Share dashboards via public URL or embed via iframe
- 🕐 **Scheduled Refresh** — Auto-refresh dashboards at set intervals
- 📋 **Audit Logs** — Track all user actions across workspaces
- 🌙 **Dark Mode** — Built-in dark/light theme toggle
- 📤 **Export** — Export charts as PNG/SVG; export data as CSV/Excel

---

## Tech Stack

| Layer        | Technology                          |
|--------------|--------------------------------------|
| Backend      | PHP 8.1+, CodeIgniter 4             |
| Frontend     | HTML5, CSS3, Bootstrap 5, Alpine.js  |
| Charts       | ApexCharts.js / Chart.js             |
| Database     | MySQL 8.0+                           |
| Query Engine | Custom CI4 Query Builder + Raw SQL   |
| Auth         | CI4 Session-based + API Token Auth   |
| Cache        | CI4 File/Redis Cache                 |
| Scheduler    | CI4 Task Scheduler (Cron)            |
| Storage      | Local / AWS S3 (for exports/uploads) |

---

## System Requirements

- PHP >= 8.1 with extensions: `intl`, `mbstring`, `curl`, `pdo`, `pdo_mysql`, `json`, `xml`
- MySQL >= 8.0
- Composer >= 2.x
- Node.js >= 18.x (for front-end asset compilation, optional)
- Web Server: Apache 2.4+ or Nginx 1.18+
- Minimum 512MB RAM (1GB+ recommended for production)
- Disk Space: 500MB minimum

---

## Installation

```bash
# 1. Clone the repository
git clone https://github.com/yourorg/chart-board.git
cd chart-board

# 2. Install PHP dependencies
composer install

# 3. Copy and configure environment file
cp env .env
# Edit .env with your database credentials and app settings

# 4. Run database migrations
php spark migrate

# 5. Seed initial data (admin user, default roles)
php spark db:seed InitialSeeder

# 6. (Optional) Install front-end dependencies
npm install && npm run build

# 7. Set folder permissions
chmod -R 777 writable/

# 8. Start the development server
php spark serve
```

Visit `http://localhost:8080` to access Chart-Board.

Default admin credentials (change after first login):
- **Email:** `admin@chartboard.local`
- **Password:** `Admin@1234`

---

## Configuration

Key settings in `.env`:

```ini
# Application
app.baseURL = 'http://localhost:8080'
app.name = 'Chart-Board'

# Database
database.default.hostname = localhost
database.default.database = chartboard
database.default.username = root
database.default.password = secret
database.default.DBDriver = MySQLi

# Cache (file or redis)
cache.handler = file

# Mail (for alerts)
email.fromEmail = noreply@chartboard.local
email.fromName  = Chart-Board
email.SMTPHost  = smtp.mailtrap.io
email.SMTPPort  = 587

# Encryption key (generate with: php spark key:generate)
encryption.key = hex2bin:your_generated_key_here
```

---

## Project Structure

```
chart-board/
├── app/
│   ├── Config/             # CI4 configuration files
│   ├── Controllers/
│   │   ├── Auth/           # Login, Register, Password Reset
│   │   ├── Api/            # REST API controllers
│   │   ├── Dashboard/      # Dashboard CRUD
│   │   ├── Chart/          # Chart CRUD
│   │   ├── DataSource/     # Connection management
│   │   ├── Query/          # Query builder & executor
│   │   ├── Workspace/      # Workspace management
│   │   └── Admin/          # Admin panel
│   ├── Models/             # CI4 Model classes
│   ├── Views/
│   │   ├── auth/
│   │   ├── dashboard/
│   │   ├── chart/
│   │   ├── datasource/
│   │   ├── workspace/
│   │   └── layouts/
│   ├── Libraries/
│   │   ├── QueryEngine.php     # Runs queries against data sources
│   │   ├── ChartRenderer.php   # Formats data for chart types
│   │   ├── ApiConnector.php    # REST API data source handler
│   │   └── AlertEngine.php     # Threshold checking & notifications
│   ├── Filters/            # Auth, RBAC, API token filters
│   └── Database/
│       ├── Migrations/     # All migration files
│       └── Seeds/          # Seeders
├── public/                 # Web root
│   ├── assets/
│   │   ├── css/
│   │   ├── js/
│   │   └── images/
├── writable/               # Logs, cache, uploads
├── tests/                  # PHPUnit tests
├── .env
├── composer.json
└── README.md
```

---

## UI typography (shell alignment)

The main chrome uses a compact type scale so navigation stays scannable:

| Token | Variable | Size | Where it appears |
|-------|-----------|------|------------------|
| Top navigation links | `--cb-font-topnav` | **14px** | Header main menu (`.cb-top-main-link`) |
| Sidebar & dense UI | `--cb-font-sidebar` | **13px** | Left menu (`.cb-nav-link`), default body text in main content |

These variables live in `public/assets/css/variables.css`. **All authenticated screens** wrapped in `<main class="cb-page">` (workspaces, data sources, queries list/view, dashboard, profile, admin users, etc.) inherit this scale: page body at **13px**, full-size inputs/buttons and subsection titles (`h2.h5`, `.h6`) at **14px**, matching the shell. Login/register/forgot-password use `.cb-auth-body` on the auth card for the same rules.

The query builder adds `public/assets/css/query-builder.css` under `.qb-root` (max-width + preview/SQL-specific styles only; base type comes from `.cb-page`).

Page titles use Bootstrap’s `h1` + `.h4` pattern with **font-weight 700** (not `display-*` sizes).

---

## Modules & Functionality

### 1. Authentication & User Management

- Registration with email verification
- Login with session-based authentication
- Password reset via email token
- API Token generation for programmatic access
- Profile management (name, avatar, password change)
- Super Admin panel to manage all users

### 2. Workspace Management

A **Workspace** is the top-level organizational unit (similar to a team or project).

- Create and manage multiple workspaces
- Invite users to a workspace with a specific role (Admin / Editor / Viewer)
- Each workspace has its own data sources, charts, and dashboards
- Workspace-level settings: name, logo, timezone, default refresh interval

### 3. Data Source Connections

Connect to external data sources. All credentials are encrypted at rest.

**Supported types:**

| Type         | Details                              |
|--------------|--------------------------------------|
| MySQL        | Host, Port, DB name, User, Password  |
| PostgreSQL   | Host, Port, DB name, User, Password  |
| MongoDB      | Connection URI                       |
| REST API     | URL, Method, Headers, Auth (Bearer / Basic / API Key) |
| CSV Upload   | Upload CSV files as static datasets  |

- Test connection before saving
- Multiple data sources per workspace
- Connection health status indicator

### 4. Query Builder

Two modes for fetching data from a data source:

**Visual Builder (No-Code):**
- Select table → choose columns → apply filters → set grouping & sorting
- Filter conditions: equals, not equals, contains, greater than, less than, between, is null
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX
- Preview results in a table before saving

**Raw SQL Mode:**
- Write custom SQL with syntax highlighting (CodeMirror)
- Schema browser sidebar (tables & columns)
- Query result preview (limited to 100 rows)
- Save queries for reuse

**For API Sources:**
- Define endpoint URL (supports dynamic date variables like `{{today}}`, `{{last_30_days}}`)
- Map JSON response path to extract data array
- Define field aliases for chart use

### Query Variables

Query Variables allow chart creators to define named placeholders inside SQL or API queries that end users can fill in at dashboard view time — without touching the query itself.

**Syntax:** Use double curly braces `{{ variable_name }}` anywhere in a SQL query.
```sql
SELECT DATE(created_at) AS date, SUM(amount) AS revenue
FROM orders
WHERE
  created_at BETWEEN '{{ start_date }}' AND '{{ end_date }}'
  AND status = '{{ order_status }}'
  AND region = '{{ region }}'
GROUP BY DATE(created_at)
```

**Variable Types:**

| Type | Input Widget | Example |
|------|-------------|---------|
| `text` | Free text input | customer name, SKU |
| `number` | Number input | threshold, limit |
| `date` | Date picker | start date, end date |
| `date_range` | Dual date picker | from → to |
| `select` | Dropdown (options defined by creator) | status, region, category |
| `multi_select` | Multi-select dropdown | multiple statuses |

**How it works:**

1. Creator writes query with `{{ variable_name }}` placeholders
2. Chart-Board auto-detects all variables and prompts the creator to configure each one (type, label, default value, options for dropdowns)
3. Variable config is saved in `charts.display_config` as JSON
4. On dashboard view, users see a filter widget per variable above the chart
5. On change, Chart-Board re-runs the query with substituted safe-parameterized values (never raw string injection)

**Built-in System Variables (no config needed):**

| Variable | Resolves To |
|----------|------------|
| `{{ today }}` | Current date `YYYY-MM-DD` |
| `{{ now }}` | Current datetime `YYYY-MM-DD HH:MM:SS` |
| `{{ yesterday }}` | Yesterday's date |
| `{{ last_7_days_start }}` | 7 days ago |
| `{{ last_30_days_start }}` | 30 days ago |
| `{{ last_90_days_start }}` | 90 days ago |
| `{{ this_month_start }}` | First day of current month |
| `{{ this_year_start }}` | First day of current year |
| `{{ current_user_id }}` | Logged-in user's ID |
| `{{ current_workspace_id }}` | Active workspace ID |

**Security:** All user-supplied variable values are passed as PDO prepared statement bindings — never interpolated directly into SQL strings.

### 5. Chart Builder

Build charts from a saved query or direct query.

**Steps:**
1. Select Data Source + enter/select query
2. Choose Chart Type
3. Map X-axis, Y-axis, group-by fields
4. Configure labels, colors, legend, tooltips
5. Set refresh interval (manual / 1 min / 5 min / 15 min / 1 hour / 1 day)
6. Save chart with a name and description

**Chart Settings:**
- Title & subtitle
- Color palette (preset or custom)
- Show/hide legend
- Show/hide data labels
- Custom Y-axis min/max
- Number formatting (currency, percentage, decimal places)
- Date format for time-series X-axis

### 6. Dashboard Builder

A Dashboard is a collection of charts arranged in a grid layout.

- Drag-and-drop chart placement
- Resize charts (1×1 to 4×2 grid units)
- Add text/markdown widgets (for annotations and section titles)
- Add image widgets (logo, banner)
- Add filter widgets (date range picker, dropdown) that apply across all charts
- Dashboard-level refresh interval
- Fullscreen mode

### 7. API Data Source Support

A key differentiator — pull data directly from REST APIs:

- GET / POST support
- Auth modes: None, Bearer Token, Basic Auth, API Key (header or query param)
- Static or dynamic headers
- JSON path extraction (`data.results[*].value`)
- Pagination support (offset/cursor-based)
- Schedule automatic sync (cache API response for N minutes)
- Supports webhook-push mode (Chart-Board provides a unique URL to receive data)

### 8. Alerts & Notifications

Define threshold-based alerts on any chart metric.

- UI: **Alerts** in the top nav (`/alert`) — create, edit, mute (1h / 4h / 24h), history, delete
- Conditions: `gt`, `lt`, `eq`, `gte`, `lte` on the first row of the chart’s query (numeric column)
- Check interval: per-alert minutes (`check_interval`); cron runs `php spark alert:check` (intended every minute)
- Channels: Email (comma-separated addresses) and Slack incoming webhook
- Duplicate notifications suppressed if the same alert fired within the last 5 minutes
- History stored in `alert_history`; recent triggers shown on the alerts index; badge on **Alerts** for sends in the last hour

### 9. Sharing & Embedding

- **Public link** — `GET /share/{token}` (no auth). Optional password (`POST /share/{token}/unlock`) and expiry. Records in `shared_links`; view count incremented once per session per link.
- **Create / revoke** — Authenticated: `POST /sharing/generate`, `POST /sharing/revoke/{id}`, list: `GET /sharing/links?type=dashboard|chart&resource_id=…`. Share modal on dashboard view, chart list, and chart editor (QR via external API in modal).
- **Embed** — `?embed=1` on the same URL; response drops `X-Frame-Options` and sets CSP `frame-ancestors *`. Embed tab in the share modal with size presets and iframe HTML.
- **Chart data for public dashboards** — `POST /share/{token}/chart/{id}/data` (same variable JSON as authenticated charts; no CSRF when token is valid).
- **Export** — Chart editor: PNG (Apex `dataURI` + FileSaver.js, client-side). Server: `GET /chart/{id}/export?format=csv|excel` (PhpSpreadsheet for `.xlsx`); logged in `chart_exports` for CSV/Excel.

### 10. Audit Logs

Track all significant user actions:

- User login / logout
- Data source created / updated / deleted
- Chart created / updated / deleted
- Dashboard shared / unshared
- User role changes
- Alert triggered

Logs are viewable by workspace admins and filterable by user, action type, and date range.

---

## Supported Chart Types

| Chart Type    | Description                                      |
|---------------|--------------------------------------------------|
| Bar Chart     | Vertical or horizontal bar comparison            |
| Line Chart    | Trend over time                                  |
| Area Chart    | Filled line chart for volume trends              |
| Pie Chart     | Proportional distribution                        |
| Donut Chart   | Pie with center label (good for KPIs)            |
| Scatter Plot  | Correlation between two numeric variables        |
| Data Table    | Paginated tabular display of query results       |
| KPI Card      | Single metric with optional trend indicator      |
| Funnel Chart  | Step-by-step conversion visualization            |
| Gauge Chart   | Progress toward a goal (0–100%)                  |
| Heatmap       | Matrix of values colored by intensity            |
| Combo Chart   | Bar + Line overlaid on same axes                 |

---

## Supported Data Sources

| Source      | Connection Type     | Status   |
|-------------|---------------------|----------|
| MySQL       | Direct TCP          | ✅ v1.0  |
| PostgreSQL  | Direct TCP          | ✅ v1.0  |
| MongoDB     | URI                 | ✅ v1.0  |
| REST API    | HTTP/HTTPS          | ✅ v1.0  |
| CSV Upload  | File Upload         | ✅ v1.0  |
| SQLite      | File Path           | 🔜 v1.1  |
| BigQuery    | OAuth / Service Key | 🔜 v1.2  |
| Snowflake   | JDBC-style DSN      | 🔜 v1.2  |
| Google Sheets | OAuth             | 🔜 v1.2  |

---

## REST API Reference

Chart-Board exposes a REST API for programmatic access (authenticated via API token).

**Base URL:** `https://yourdomain.com/api/v1`

**Authentication Header:**
```
Authorization: Bearer <your_api_token>
```

| Method | Endpoint                             | Description                   |
|--------|--------------------------------------|-------------------------------|
| GET    | `/workspaces`                        | List all workspaces           |
| POST   | `/workspaces`                        | Create a workspace            |
| GET    | `/workspaces/{id}/datasources`       | List data sources             |
| POST   | `/workspaces/{id}/datasources`       | Add a data source             |
| GET    | `/workspaces/{id}/charts`            | List charts                   |
| POST   | `/workspaces/{id}/charts`            | Create a chart                |
| GET    | `/workspaces/{id}/dashboards`        | List dashboards               |
| POST   | `/workspaces/{id}/dashboards`        | Create a dashboard            |
| GET    | `/charts/{id}/data`                  | Fetch chart data (JSON)       |
| POST   | `/datasources/{id}/query`            | Run a query                   |
| GET    | `/alerts`                            | List alerts                   |

---

## Roles & Permissions

| Permission              | Super Admin | Workspace Admin | Editor | Viewer |
|-------------------------|:-----------:|:---------------:|:------:|:------:|
| Manage users (global)   | ✅          | ❌              | ❌     | ❌     |
| Manage workspaces       | ✅          | ✅              | ❌     | ❌     |
| Invite members          | ✅          | ✅              | ❌     | ❌     |
| Manage data sources     | ✅          | ✅              | ✅     | ❌     |
| Create/edit charts      | ✅          | ✅              | ✅     | ❌     |
| Create/edit dashboards  | ✅          | ✅              | ✅     | ❌     |
| View dashboards         | ✅          | ✅              | ✅     | ✅     |
| Share dashboards        | ✅          | ✅              | ✅     | ❌     |
| Manage alerts           | ✅          | ✅              | ✅     | ❌     |
| View audit logs         | ✅          | ✅              | ❌     | ❌     |

---

## Security Considerations

- All data source credentials are encrypted using CI4's Encryption library (AES-256)
- SQL queries run in read-only mode on connected databases (use a dedicated read-only DB user)
- CSRF protection enabled on all forms
- XSS filtering on all user inputs
- Rate limiting on API endpoints (60 req/min per token)
- Public dashboard links support optional password and expiry
- HTTP-only, SameSite=Strict session cookies
- Content Security Policy (CSP) headers configured

---

## Roadmap

| Version | Planned Features                                                        |
|---------|-------------------------------------------------------------------------|
| v1.0    | Core dashboard, MySQL/PostgreSQL/REST API sources, basic chart types    |
| v1.1    | SQLite support, improved mobile view, CSV export, chart comments        |
| v1.2    | BigQuery, Snowflake, Google Sheets connectors                           |
| v1.3    | AI-powered natural language query ("Show me sales last month")          |
| v1.4    | White-label mode, multi-tenant SaaS mode                                |
| v2.0    | Real-time streaming data (WebSocket), collaborative dashboard editing   |

---

## Contributing

Contributions are welcome! Please read [CONTRIBUTING.md](CONTRIBUTING.md) for guidelines.

```bash
# Run tests
php spark test

# Check code style
vendor/bin/phpcs --standard=PSR12 app/

# Fix code style
vendor/bin/phpcbf --standard=PSR12 app/
```

---

## License

Chart-Board is open-source software licensed under the [MIT License](LICENSE).

---

*Built with ❤️ using CodeIgniter 4*