# System Administration

> **Location:** `/admin/system`
> **Added:** 2026-04-21
> **Auth:** Administrator or Super Admin role required

## Overview

The System Administration page provides platform administrators with real-time visibility into database and object storage consumption across all platform collections. It includes visual charts, detailed metrics, and maintenance tools.

---

## Features

### 1. Overview Tab (Visual Dashboard)

The overview tab provides at-a-glance metrics with interactive charts:

#### Summary Cards
| Metric | Description |
|--------|-------------|
| Database Size | Total PostgreSQL database size (formatted) |
| Tables | Total number of tables across all schemas |
| Schemas | Number of database schemas (predict, auth, trading, etc.) |
| Total Rows | Sum of all rows across all tables |
| S3 Storage | Total object storage size |
| S3 Objects | Total number of stored objects |

#### Visual Charts

1. **Storage by Schema (Pie Chart)**
   - Color-coded breakdown of storage per schema
   - Schemas: `predict`, `auth`, `trading`, `marketing`, `billing`, `derivatives`, `omniscient`
   - Hover for exact sizes

2. **Top Tables by Size (Horizontal Bar Chart)**
   - Top 8 largest tables
   - Shows table name and size
   - Color-coded by schema

3. **Row Counts by Schema (Bar Chart)**
   - Visualizes data distribution
   - Helps identify which schemas have the most data

4. **S3 Bucket Distribution (Bar Chart)**
   - Storage usage per bucket
   - Only shown if S3/MinIO is connected

---

### 2. Database Tab (Detailed View)

#### Database Statistics
- **Database name** — `predict_db`
- **Total size** — Formatted (e.g., "245.67 MB")
- **Connections** — Current / Maximum
- **Uptime** — Time since last restart

#### Schema Filter
Click schema badges to filter the table list:
- All schemas shown by default
- Color-coded badges match chart colors
- Shows table count per schema

#### Table List
Full inventory of all tables with columns:

| Column | Description |
|--------|-------------|
| Schema | Database schema (color-coded badge) |
| Table | Table name |
| Rows | Live row count |
| Table Size | Data size (excluding indexes) |
| Index Size | Index storage |
| Total | Combined size |
| Last Vacuum | Date of last VACUUM operation |

Tables with "Never" vacuumed are highlighted in yellow.

#### Maintenance Button
- **Vacuum All** — Runs `VACUUM ANALYZE` on entire database
- Reclaims storage and updates query planner statistics
- Shows spinner while running

---

### 3. Object Storage Tab (S3/MinIO)

#### Connection Status
- **Status** — Connected / Disconnected indicator
- **Endpoint** — S3/MinIO endpoint URL
- **Total Storage** — Combined size across all buckets
- **Total Objects** — Total object count

#### Bucket Cards
Each bucket displays:
- Bucket name with folder icon
- Size (formatted)
- Object count
- Last modified timestamp
- Storage usage bar (relative to total)

#### Not Configured State
If S3 environment variables are not set, shows configuration guidance:
- `S3_ENDPOINT` or `MINIO_ENDPOINT`
- `S3_ACCESS_KEY` or `MINIO_ACCESS_KEY`
- `S3_SECRET_KEY` or `MINIO_SECRET_KEY`

---

## API Endpoints

### GET `/api/predict/v1/admin/storage`

Returns complete storage overview.

**Response:**
```json
{
  "database": {
    "stats": {
      "name": "predict_db",
      "sizeBytes": 257425408,
      "sizeFormatted": "245.42 MB",
      "connections": 5,
      "maxConnections": 100,
      "uptime": "2 days 14:32:15"
    },
    "schemas": [
      {
        "schema": "predict",
        "tables": 28,
        "totalRows": 15234,
        "totalSizeBytes": 125829120,
        "totalSizeFormatted": "120.00 MB"
      }
    ],
    "tables": [
      {
        "schema": "predict",
        "table": "events",
        "rows": 5799,
        "sizeBytes": 45056000,
        "sizeFormatted": "42.97 MB",
        "indexSizeBytes": 8192000,
        "totalSizeBytes": 53248000,
        "lastVacuum": "2026-04-20T15:30:00Z",
        "lastAnalyze": "2026-04-20T15:30:00Z"
      }
    ],
    "topTablesBySize": [...],
    "topTablesByRows": [...]
  },
  "s3": {
    "connected": true,
    "endpoint": "http://minio:9000",
    "buckets": [
      {
        "name": "agencio-uploads",
        "objectCount": 1250,
        "sizeBytes": 524288000,
        "sizeFormatted": "500.00 MB",
        "lastModified": "2026-04-21T10:15:00Z"
      }
    ],
    "totalSizeBytes": 524288000,
    "totalSizeFormatted": "500.00 MB",
    "totalObjects": 1250
  },
  "timestamp": "2026-04-21T12:00:00Z"
}
```

### POST `/api/predict/v1/admin/storage`

Run database maintenance operations.

**Request:**
```json
{
  "action": "vacuum_analyze",
  "schema": "predict",  // optional
  "table": "events"     // optional
}
```

**Actions:**
- `vacuum` — Reclaim storage from dead tuples
- `analyze` — Update query planner statistics
- `vacuum_analyze` — Both operations combined

**Response:**
```json
{
  "success": true,
  "action": "vacuum_analyze",
  "target": "\"predict\".\"events\"",
  "timestamp": "2026-04-21T12:00:00Z"
}
```

---

## Database Schemas

The platform uses 7 PostgreSQL schemas:

| Schema | Purpose | Key Tables |
|--------|---------|------------|
| `predict` | Core prediction data | `events`, `ai_predictions`, `computed_insights` |
| `auth` | User authentication | `users`, `sessions`, `api_keys` |
| `trading` | Trading operations | `trades`, `positions`, `algorithm_runs` |
| `marketing` | Marketing analytics | `campaigns`, `attribution_events` |
| `billing` | Subscription & payments | `subscriptions`, `invoices`, `usage_records` |
| `derivatives` | Market derivatives | `funding_rates`, `liquidations`, `open_interest` |
| `omniscient` | All-Seeing Eye data | `insights`, `black_swan_events` |

---

## Monitoring Best Practices

### Regular Maintenance
- Run `VACUUM ANALYZE` weekly on high-churn tables
- Monitor tables with "Never" vacuumed status
- Watch for tables growing unexpectedly

### Storage Alerts
Set alerts when:
- Database size exceeds 80% of allocated storage
- Any single table exceeds 10GB
- S3 bucket exceeds quota

### Query Performance
- Tables without recent `ANALYZE` may have stale statistics
- Large index sizes relative to table size may indicate index bloat
- High row counts with small sizes may indicate wide tables

---

## Environment Variables

### Database
PostgreSQL connection configured via:
- `DATABASE_URL` — Full connection string
- Or individual: `DB_HOST`, `DB_PORT`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`

### Object Storage (S3/MinIO)
```bash
# MinIO (dev)
MINIO_ENDPOINT=http://minio:9000
MINIO_ACCESS_KEY=minioadmin
MINIO_SECRET_KEY=minioadmin

# AWS S3 (prod)
S3_ENDPOINT=  # Leave empty for AWS
S3_REGION=us-east-1
AWS_ACCESS_KEY_ID=AKIA...
AWS_SECRET_ACCESS_KEY=...
```

---

## Troubleshooting

### "Failed to fetch storage data"
1. Check admin authentication (must be `administrator` or `super_admin` role)
2. Verify database connection in container logs
3. Check `/api/predict/v1/admin/storage` returns 200

### S3 Shows "Not Configured"
1. Verify S3/MinIO environment variables are set
2. Check endpoint URL is valid (must be full URL with protocol)
3. Test MinIO console at `http://localhost:9001`

### Slow Loading
1. Large databases may take time to aggregate stats
2. S3 bucket enumeration scales with object count
3. Consider caching for production (Redis)

---

## Related Documentation

- [Database Schema](./03-database-schema.md) — Full table definitions
- [Infrastructure Deployment](./10-infrastructure-deployment.md) — Production setup
- [API Routes](./24-api-routes.md) — Complete API inventory