csv-explorer

# csv-explorer skill

## Overview

csv-explorer is a self-hosted web tool that accepts CSV file uploads, auto-detects column data types, and lets users explore data via a paginated table and chart builder. Data is never written to a database; CSV files are stored on disk and re-read per request.

## Base URL

```
http://localhost:3000
```

## TypeScript interfaces

```typescript
interface Dataset {
  id: string;             // nanoid(12)
  filename: string;       // original uploaded filename
  file_size: number;      // bytes
  row_count: number;      // data rows excluding header
  col_count: number;
  has_header: number;     // 0 or 1
  delimiter: string;      // ',' | '\t' | ';' | '|'
  encoding: string;       // 'utf-8'
  parsed_at: string;      // ISO 8601
  columns?: Column[];
}

interface Column {
  id: number;
  dataset_id: string;
  col_index: number;      // 0-based
  name: string;
  detected_type: 'string' | 'number' | 'boolean' | 'date' | 'mixed';
  overridden_type: string | null;
  null_count: number;
  unique_count: number | null;   // null if >100,000 distinct values
  profile: ColumnProfile;
}

interface ColumnProfile {
  type_counts: { string: number; number: number; boolean: number; date: number; null: number; };
  numeric?: { min: number; max: number; mean: number; sum: number; };
  top_values?: Array<{ value: string; count: number }>;
  sample_values?: string[];
}

interface ChartDataRequest {
  chart_type: 'bar' | 'line' | 'scatter';
  x_col: number;
  y_col: number;
  group_col?: number;
  max_points?: number;       // default 200, max 1000
  aggregate?: 'sum' | 'mean' | 'count' | 'min' | 'max';  // default 'sum'
}

interface ChartDataResponse {
  labels: string[];
  series: Array<{ name: string; data: number[] | Array<{ x: number; y: number }> }>;
  x_label: string;
  y_label: string;
}
```

## Datasets API

**List all datasets**
```bash
curl http://localhost:3000/api/datasets
```

Response: `Dataset[]` ordered by `parsed_at DESC`.

**Upload a CSV**
```bash
curl -X POST http://localhost:3000/api/datasets \
  -F "file=@/path/to/sales-q1-2026.csv"
```

Response: `Dataset` with `columns` array populated.

Upload process:
1. File saved to temp location.
2. Delimiter and encoding detected from first 10 rows.
3. Full parse with papaparse.
4. Per-column type detection and profiling.
5. Dataset and columns written to SQLite in a transaction.
6. File moved to `DATA_DIR/<id>.csv`.

**Get dataset with columns**
```bash
curl http://localhost:3000/api/datasets/abc123xyz456
```

Response: `Dataset` with `columns`.

**Delete dataset**
```bash
curl -X DELETE http://localhost:3000/api/datasets/abc123xyz456
```

Deletes the CSV file from disk and the database row. Returns `{ ok: true }`.

## Rows API

**Get paginated rows**
```bash
curl 'http://localhost:3000/api/datasets/abc123xyz456/rows?page=1&per_page=100'
```

Query params:
- `page`: 1-indexed (default: 1)
- `per_page`: 1-500 (default: 100)
- `columns`: comma-separated 0-based indexes, e.g. `0,3,5` (default: all)
- `filter_col`: column index to filter by
- `filter_val`: value to match (substring for string, exact for others)
- `sort_col`: column index to sort by
- `sort_dir`: `asc` or `desc` (default: `asc`)

Response:
```json
{
  "rows": [["West", "Pro Headset", "449.97"], ...],
  "total": 1412,
  "page": 1,
  "per_page": 100
}
```

Rows are arrays of raw string values. The client applies type formatting based on `detected_type`.

## Columns API

**Get column profiles**
```bash
curl http://localhost:3000/api/datasets/abc123xyz456/columns
```

Response: `Column[]`

**Override column type**
```bash
curl -X PUT http://localhost:3000/api/datasets/abc123xyz456/columns/3 \
  -H 'Content-Type: application/json' \
  -d '{ "overridden_type": "string" }'
```

Sets `overridden_type` on column at index 3. Use `null` to remove the override:
```bash
curl -X PUT http://localhost:3000/api/datasets/abc123xyz456/columns/3 \
  -H 'Content-Type: application/json' \
  -d '{ "overridden_type": null }'
```

## Chart data API

**Generate chart data**
```bash
curl -X POST http://localhost:3000/api/datasets/abc123xyz456/charts/data \
  -H 'Content-Type: application/json' \
  -d '{
    "chart_type": "bar",
    "x_col": 2,
    "y_col": 5,
    "aggregate": "sum",
    "max_points": 200
  }'
```

**Bar/line with grouping**
```bash
curl -X POST http://localhost:3000/api/datasets/abc123xyz456/charts/data \
  -H 'Content-Type: application/json' \
  -d '{
    "chart_type": "line",
    "x_col": 6,
    "y_col": 5,
    "group_col": 0,
    "aggregate": "sum"
  }'
```

**Scatter chart**
```bash
curl -X POST http://localhost:3000/api/datasets/abc123xyz456/charts/data \
  -H 'Content-Type: application/json' \
  -d '{
    "chart_type": "scatter",
    "x_col": 3,
    "y_col": 5,
    "max_points": 500
  }'
```

For scatter, `series[0].data` contains `{ x: number; y: number }` objects. `aggregate` is ignored.

## Error responses

| HTTP | Code | Cause |
|------|------|-------|
| 400 | `VALIDATION_ERROR` | Invalid request body or query params |
| 400 | `FILE_TOO_LARGE` | File exceeds `MAX_FILE_SIZE_MB` |
| 400 | `INVALID_CSV` | File cannot be parsed as CSV |
| 404 | `NOT_FOUND` | Dataset or column does not exist |
| 500 | `INTERNAL_ERROR` | Unexpected server error |

## Settings API

**Get settings**
```bash
curl http://localhost:3000/api/settings
```

Response: `{ max_file_size_mb: 50, max_preview_rows: 500 }`

**Update settings**
```bash
curl -X PUT http://localhost:3000/api/settings \
  -H 'Content-Type: application/json' \
  -d '{ "max_file_size_mb": 100, "max_preview_rows": 250 }'
```

## Environment variables

| Variable | Default | Description |
|----------|---------|-------------|
| `PORT` | `3000` | HTTP port |
| `DATABASE_PATH` | `./data/csv.db` | SQLite file |
| `DATA_DIR` | `./data/files` | CSV storage directory |
| `MAX_FILE_SIZE_MB` | `50` | Upload limit |
| `MAX_PREVIEW_ROWS` | `500` | Max rows per page |