@agstack/storage-postgres

Version 1.0.0

Production-grade PostgreSQL storage plugin for the AGStack Runtime Kernel. Batch writes, connection pooling, partitioning, retry with exponential backoff, health monitoring, and automatic schema migrations.

Architecture

Runtime Kernel
    ↓
Plugin Pipeline
    ↓
PostgresStoragePlugin.save(transaction)
    ↓
BatchManager (buffers in memory)
    ↓
Flush (batchSize or flushInterval)
    ↓
ConnectionPool (pg.Pool)
    ↓
Prepared Bulk INSERT ... ON CONFLICT DO UPDATE
    ↓
PostgreSQL

All database work happens in background workers. HTTP requests are never blocked.

Installation

npm install @agstack/storage-postgres

Quick Start

import { initialize, getKernel, shutdown } from "@agstack/logger";
import { PostgresStoragePlugin } from "@agstack/storage-postgres";

const storage = new PostgresStoragePlugin({
  host: "localhost",
  port: 5432,
  database: "agstack",
  username: "postgres",
  password: "secret",
  schema: "agstack",
  autoMigrate: true,
});

await initialize();

getKernel().registerPlugin(storage);

// The plugin will automatically batch and flush transactions
await storage.save(transaction);
await storage.saveBatch(transactions);

// Force flush remaining
await storage.flush();

await shutdown();

Configuration

| Option | Default | Description | |--------|---------|-------------| | host | localhost | PostgreSQL host | | port | 5432 | PostgreSQL port | | database | agstack | Database name | | schema | agstack | Database schema | | username | postgres | Database user | | password | "" | Database password | | ssl | false | SSL/TLS config | | poolMin | 2 | Minimum pool connections | | poolMax | 10 | Maximum pool connections | | poolIdleTimeoutMs | 30000 | Idle connection timeout | | batchSize | 100 | Transactions per batch insert | | flushIntervalMs | 5000 | Max interval between flushes | | retryMaxAttempts | 3 | Max retry attempts | | retryBaseDelayMs | 100 | Base delay for exponential backoff | | retryJitter | true | Enable jitter for retry delay | | partitionBy | daily | Partition strategy (daily/monthly/none) | | partitionRetentionDays | 30 | Days to retain partitions | | autoMigrate | true | Auto-run schema migrations | | healthCheckIntervalMs | 30000 | Health check interval | | applicationName | agstack-storage-postgres | PG application name | | debug | false | Enable debug logging |

Database Schema

The plugin automatically creates and migrates the transactions table:

• 50+ columns covering request, response, headers, body, errors, events, metadata
• JSONB fields for query, headers, errors, events, metadata, client_info, geo_info, etc.
• GIN indexes on JSONB columns for efficient querying
• B-tree indexes on correlation_id, trace_id, status, method, status_code, client_ip, path, started_at, duration
• ON CONFLICT (id) DO UPDATE for idempotent inserts

Migrations

The plugin includes versioned migrations (currently v1-v3) that run automatically when autoMigrate: true. Migration history is tracked in _agstack_migrations table.

Partitioning

Supports daily or monthly partitioning of the transactions table:

• partitionBy: "daily" — creates partitions like transactions_20240101
• partitionBy: "monthly" — creates partitions like transactions_202401
• partitionBy: "none" — no partitioning

Old partitions are automatically dropped after partitionRetentionDays.

Batch Manager

• Transactions are buffered in memory and inserted in batches
• Configurable batch size (default 100) and flush interval (default 5s)
• Uses parameterized bulk INSERT with ON CONFLICT DO UPDATE
• Failed batches go to dead-letter queue (configurable max size)
• Dead-letter items can be retried via retryDeadLetter()

Retry Policy

Only transient failures are retried:

| Condition | Behavior | |-----------|----------| | Connection refused/timeout/reset | Retry with backoff | | Deadlock detected | Retry with backoff | | Too many connections | Retry with backoff | | Network errors (ECONNRESET, EPIPE, etc.) | Retry with backoff | | Syntax errors, constraint violations | No retry (immediate failure) | | Authentication failures | No retry |

Health Monitoring

const health = await storagePlugin.health();
console.log(health.status, health.metrics);

Exposed metrics:

• uptimeMs — plugin uptime
• transactionsProcessed — successful inserts
• queueSize — pending batch items
• errorRate — failure percentage
• avgLatencyMs — average insert latency
• totalCount, idleCount, waitingCount, activeCount — pool stats

Graceful Shutdown

// On shutdown:
await storagePlugin.flush();       // Flush remaining batches
await storagePlugin.shutdown();     // Close pool

The Runtime Kernel handles shutdown order automatically.

Testing

npm test

Tests cover: metadata validation, configuration, health reporting, retry policy (success, retry, permanent failure, transient detection), defaults, plugin interface compliance, config overrides, state transitions.

Benchmarks

node benchmarks/index.js

License

MIT