database-size-monitor

# database-size-monitor Skill

## When to Use

Use this skill when you need to:
- Track how individual database tables grow over time
- Get alerted when a table exceeds a size or row count threshold
- Find which tables are growing fastest before they cause storage incidents
- Compare two snapshots to see what changed between them
- Monitor multiple PostgreSQL and MySQL databases from a single dashboard

## Prerequisites

- Node.js 20+
- pnpm 9+
- At least one PostgreSQL or MySQL database to monitor (read-only access is sufficient)

## Quick Start

```bash
cd database-size-monitor
cp .env.example .env
pnpm install
pnpm --filter backend dev     # API on :3000
pnpm --filter frontend dev    # Dashboard on :5173
```

Add a database via the Databases page or API. The first poll runs immediately.

## Docker Quick Start

```bash
docker compose up
# Dashboard: http://localhost:8080
# API: http://localhost:3000
```

## Adding a Database

### Via the Dashboard

Open Databases, click "Add Database", fill in the name, type, and connection string, then click "Test Connection" to verify before saving.

### Via the API

```bash
curl -X POST http://localhost:3000/api/databases \
  -H "Content-Type: application/json" \
  -d '{
    "name": "prod-postgres",
    "type": "postgres",
    "connection_string": "postgresql://readonly_user:pass@db.example.com:5432/mydb",
    "poll_interval_seconds": 300
  }'
```

The backend uses `pg.Pool` for PostgreSQL and `mysql2` (promise interface) for MySQL. A read-only database user is recommended.

## API Reference

### Databases

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/databases` | List all databases |
| `POST` | `/api/databases` | Add a database |
| `PUT` | `/api/databases/:id` | Update database config |
| `DELETE` | `/api/databases/:id` | Remove database |
| `POST` | `/api/databases/:id/test` | Test connection |
| `POST` | `/api/databases/:id/poll` | Trigger immediate poll |

### Sizes

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/databases/:id/snapshots` | Snapshot list (paginated) |
| `GET` | `/api/databases/:id/tables` | Current table sizes with growth |
| `GET` | `/api/databases/:id/tables/:table/history` | Single table size history |

### Alerts

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/alerts` | All alert events |
| `GET` | `/api/alert-rules` | Alert rule list |
| `POST` | `/api/alert-rules` | Create a rule |
| `DELETE` | `/api/alert-rules/:id` | Delete a rule |

### Settings

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/settings` | Global settings |
| `POST` | `/api/settings` | Update settings |

## Alert Rules

Alert rules fire when a table's measured value crosses a threshold after each poll.

Create a rule via API:

```bash
curl -X POST http://localhost:3000/api/alert-rules \
  -H "Content-Type: application/json" \
  -d '{
    "database_id": 1,
    "table_pattern": "public.events",
    "metric": "size_bytes",
    "operator": "gt",
    "threshold": 8589934592,
    "severity": "critical"
  }'
```

### Metric Types

| Metric | Unit | Example threshold |
|--------|------|-------------------|
| `size_bytes` | bytes | `8589934592` (8 GB) |
| `row_count` | integer | `50000000` (50M rows) |
| `growth_pct` | percent per week | `10` (10% weekly growth) |

### Table Pattern

The `table_pattern` field uses glob syntax (via `minimatch`):
- `public.events` -- exact match
- `public.*` -- all tables in the `public` schema
- `*.audit_*` -- any table with "audit_" in the name across all schemas

### Alert Webhook

Set `ALERT_WEBHOOK_URL` to receive POST notifications when alerts fire:

```json
{
  "alert_id": 42,
  "database_name": "prod-postgres",
  "schema_name": "public",
  "table_name": "events",
  "metric": "size_bytes",
  "value": 9013043200,
  "threshold": 8589934592,
  "severity": "critical",
  "triggered_at": "2024-03-15T08:12:00Z"
}
```

This format is compatible with Slack incoming webhooks when wrapped in a simple proxy.

## Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `PORT` | `3000` | Backend API port |
| `DB_PATH` | `./data/monitor.db` | SQLite monitoring database path |
| `DEFAULT_POLL_INTERVAL` | `300` | Default poll interval in seconds |
| `RETENTION_DAYS` | `90` | Delete snapshot records older than N days |
| `ALERT_WEBHOOK_URL` | `` | POST alert payload here when rule fires |
| `CORS_ORIGIN` | `http://localhost:5173` | Dashboard CORS origin |
| `VITE_API_URL` | `http://localhost:3000` | API base URL for dashboard |

## Data Notes

### PostgreSQL Row Counts

The `row_count` value comes from `pg_stat_user_tables.n_live_tup`, which is an estimate maintained by autovacuum. After a large DELETE without VACUUM, this value may be stale. For exact counts on specific tables, trigger a poll after running `ANALYZE table_name`.

### MySQL Row Counts

MySQL's `information_schema.TABLES.TABLE_ROWS` is also an estimate for InnoDB tables. It is typically accurate within 20% of the true count.

### First Snapshot

Growth percentage is only available after at least two successful polls. The first snapshot shows "--" in the growth column.

## Troubleshooting

**Poll fails with "ECONNREFUSED"**
Check that the connection string hostname and port are reachable from the backend container. If using Docker, use service names or host network mode.

**Poll fails with "permission denied"**
The database user lacks read access to `pg_stat_user_tables` or `information_schema.TABLES`. Grant the required permissions:
- PostgreSQL: `GRANT SELECT ON pg_stat_user_tables TO monitor_user;`
- MySQL: `GRANT SELECT ON information_schema.* TO 'monitor_user'@'%';`

**Dashboard shows no data after adding a database**
The first poll may not have completed yet. Click "Poll Now" on the Databases page to trigger an immediate poll and wait a few seconds.

**Alerts not firing**
Verify the alert rule's `table_pattern` matches your schema.table format exactly. Test the pattern with `minimatch('public.events', 'public.*')` in a Node REPL.