@abbacchio/sqlite-api

SQLite-backed log storage API for Abbacchio. Receives logs via HTTP and persists them in SQLite for later querying by other services.

Compatible with all Abbacchio transports (Pino, Winston, Bunyan, Console).

Features

• SQLite Persistence: Logs stored on disk with WAL mode for performance
• Compatible API: Same ingestion endpoint as @abbacchio/api - transports work without changes
• Query & Filter: Search logs by channel, level, text, time range with pagination
• Auto-Prune: Configurable max log count to prevent unbounded growth
• Production Ready: Rate limiting, API key auth, input validation, graceful shutdown
• CLI Executable: Run directly with npx abbacchio-sqlite

Installation

npm install @abbacchio/sqlite-api
# or
pnpm add @abbacchio/sqlite-api

Quick Start

# Start the server
npx abbacchio-sqlite

# Or with environment variables
PORT=4002 SQLITE_PATH=./logs.db npx abbacchio-sqlite

Send a test log:

curl -X POST http://localhost:4002/api/logs \
  -H "Content-Type: application/json" \
  -d '{"level": 30, "msg": "Hello world"}'

Query it back:

curl http://localhost:4002/api/logs

API Endpoints

| Method | Endpoint | Description | |--------|----------|-------------| | POST | /api/logs | Ingest single or batch logs | | GET | /api/logs | Query logs with filters and pagination | | DELETE | /api/logs | Clear logs (all or by channel) | | GET | /api/channels | List registered channels | | GET | /api/stats | Database statistics | | GET | /api/generate-key | Generate encryption key | | GET | /health | Health check |

Log Ingestion

Single log:

curl -X POST http://localhost:4002/api/logs \
  -H "Content-Type: application/json" \
  -H "X-Channel: my-app" \
  -d '{"level": 30, "msg": "User logged in", "userId": 123}'

Batch logs:

curl -X POST http://localhost:4002/api/logs \
  -H "Content-Type: application/json" \
  -d '{
    "logs": [
      {"level": 30, "msg": "Request started"},
      {"level": 50, "msg": "Something failed"}
    ]
  }'

Querying Logs

# All logs (newest first, default limit 100)
curl http://localhost:4002/api/logs

# Filter by channel
curl "http://localhost:4002/api/logs?channel=my-app"

# Filter by minimum level (40 = warn and above)
curl "http://localhost:4002/api/logs?level=40"

# Search in message text
curl "http://localhost:4002/api/logs?search=error"

# Time range (unix timestamps in ms)
curl "http://localhost:4002/api/logs?since=1700000000000&until=1700100000000"

# Pagination
curl "http://localhost:4002/api/logs?limit=50&offset=100"

# Oldest first
curl "http://localhost:4002/api/logs?order=asc"

# Combine filters
curl "http://localhost:4002/api/logs?channel=my-app&level=40&search=timeout&limit=20"

Query Parameters:

| Parameter | Type | Default | Description | |-----------|------|---------|-------------| | channel | string | - | Filter by channel name | | level | number | - | Minimum log level (10=trace, 20=debug, 30=info, 40=warn, 50=error, 60=fatal) | | search | string | - | Search text in message (case-insensitive) | | limit | number | 100 | Results per page (max 1000) | | offset | number | 0 | Pagination offset | | since | number | - | Start timestamp in ms | | until | number | - | End timestamp in ms | | order | string | desc | Sort order: asc or desc |

Response:

{
  "logs": [
    {
      "id": "abc123",
      "level": 30,
      "levelLabel": "info",
      "time": 1700000000000,
      "msg": "User logged in",
      "channel": "my-app",
      "data": { "userId": 123 }
    }
  ],
  "count": 1,
  "total": 5000,
  "limit": 100,
  "offset": 0
}

Using with Transports

The ingestion endpoint is compatible with @abbacchio/transport. Just point to this server's URL:

import pino from "pino";

const logger = pino({
  transport: {
    target: "@abbacchio/transport/transports/pino",
    options: {
      url: "http://localhost:4002/api/logs",
      channel: "my-app",
    },
  },
});

logger.info("This log is persisted in SQLite");

Configuration

| Variable | Default | Description | |----------|---------|-------------| | PORT | 4002 | Server port | | SQLITE_PATH | ./data/logs.db | SQLite database file path | | SQLITE_WAL | true | Enable WAL mode for better write performance | | SQLITE_MAX_LOGS | 100000 | Max logs before auto-prune (0 = unlimited) | | API_KEY | - | API key for authentication | | CORS_ORIGIN | * (dev) | Allowed CORS origins | | ENABLE_RATE_LIMIT | true | Enable rate limiting | | RATE_LIMIT_WINDOW | 60000 | Rate limit window in ms | | RATE_LIMIT_MAX | 1000 | Max requests per window | | MAX_PAYLOAD_SIZE | 1048576 | Max payload size (1MB) | | MAX_BATCH_SIZE | 1000 | Max logs per batch | | TRUST_PROXY | false | Trust proxy headers for IP detection | | SHUTDOWN_TIMEOUT | 30000 | Graceful shutdown timeout in ms |

Statistics

curl http://localhost:4002/api/stats

{
  "totalLogs": 5000,
  "dbSizeBytes": 2097152,
  "channels": [
    { "name": "my-app", "count": 3000, "lastActivity": 1700000000000 },
    { "name": "default", "count": 2000, "lastActivity": 1699999000000 }
  ]
}

Health Check

curl http://localhost:4002/health

{
  "status": "ok",
  "uptime": 3600.5,
  "totalLogs": 5000,
  "dbSizeBytes": 2097152,
  "channels": 2
}

License

MIT