npm package: @idempotix/postgres
Description: PostgreSQL storage adapter for Idempotix idempotency
Author: stacks0x
Latest version: 1.0.0

<p align="center">
  <a href="https://github.com/stacks0x/idempotix"><img src="https://raw.githubusercontent.com/stacks0x/idempotix/main/assets/idempotix.jpg" alt="Idempotix" width="80" /></a>
</p>

# @idempotix/postgres

PostgreSQL storage adapter for Idempotix.

[![npm version](https://img.shields.io/npm/v/@idempotix/postgres.svg)](https://www.npmjs.com/package/@idempotix/postgres)

## Installation

```bash
npm install @idempotix/core @idempotix/postgres pg
```

## Quick Start

```typescript
import { postgres } from '@idempotix/postgres';
import { express as idempotent } from '@idempotix/express';

// From environment variable (IDEMPOTIX_POSTGRES_URL)
app.post('/orders', idempotent({ storage: postgres() }), handler);
```

## Why PostgreSQL?

If your app already uses PostgreSQL, you don't need additional infrastructure:

- ✅ No Redis/Upstash to manage
- ✅ Uses your existing database
- ✅ ACID transactions for atomic locking
- ✅ Works with any PostgreSQL-compatible DB

## Configuration

```typescript
import { postgres } from '@idempotix/postgres';

// From environment variable
const storage = postgres();

// From URL
const storage = postgres('postgres://user:pass@localhost:5432/mydb');

// With options
const storage = postgres({
  url: 'postgres://localhost:5432/mydb',
  tableName: 'my_idempotency_keys', // default: 'idempotix_keys'
  schema: 'app', // default: 'public'
  autoCreateTable: true, // default: true
});

// With existing pg Pool
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const storage = postgres({ pool });
```

## Environment Variables

| Variable                 | Description               |
| ------------------------ | ------------------------- |
| `IDEMPOTIX_POSTGRES_URL` | PostgreSQL connection URL |

## Table Schema

The adapter auto-creates this table (disable with `autoCreateTable: false`):

```sql
CREATE TABLE IF NOT EXISTS idempotix_keys (
  key TEXT PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('in_progress', 'completed')),
  hash TEXT,
  data JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idempotix_keys_expires_at_idx ON idempotix_keys (expires_at);
```

## Cleanup

Unlike Redis, PostgreSQL doesn't auto-expire rows. Call `cleanup()` periodically:

```typescript
import { postgres } from '@idempotix/postgres';

const storage = postgres();

// In a cron job or scheduled task
const deleted = await storage.cleanup();
console.log(`Cleaned up ${deleted} expired entries`);
```

Or set up a PostgreSQL cron extension (pg_cron):

```sql
SELECT cron.schedule('cleanup-idempotix', '0 * * * *',
  $$DELETE FROM idempotix_keys WHERE expires_at < NOW()$$
);
```

## Cloud Providers

### Supabase

```typescript
const storage = postgres(process.env.SUPABASE_DB_URL);
```

### Neon

```typescript
const storage = postgres(process.env.DATABASE_URL);
```

### AWS RDS

```typescript
const storage = postgres('postgres://user:pass@mydb.region.rds.amazonaws.com:5432/mydb');
```

### Vercel Postgres

```typescript
import { postgres } from '@idempotix/postgres';

const storage = postgres(process.env.POSTGRES_URL);
```

## Usage with Express

```typescript
import { express as idempotent, configure } from '@idempotix/express';
import { postgres } from '@idempotix/postgres';

const idempotent = configure({
  storage: postgres(),
  ttl: '1h',
});

app.post('/orders', idempotent(), orderHandler);
app.post('/payments', idempotent({ required: true }), paymentHandler);
```

## Usage with Next.js

```typescript
import { next } from '@idempotix/next';
import { postgres } from '@idempotix/postgres';

export const POST = next({ storage: postgres() })(handler);
```

## Performance Considerations

PostgreSQL is excellent for idempotency but has different characteristics than Redis:

| Aspect         | PostgreSQL          | Redis           |
| -------------- | ------------------- | --------------- |
| Latency        | ~1-5ms              | ~0.1-1ms        |
| Throughput     | Good                | Excellent       |
| Persistence    | Built-in            | Optional        |
| Infrastructure | Likely already have | May need to add |

For most applications, PostgreSQL is fast enough. Use Redis if you need sub-millisecond latency at very high throughput.

## Connection Pooling

The adapter uses a connection pool. For serverless environments, consider using a pooler:

```typescript
// With PgBouncer or Supabase pooler
const storage = postgres('postgres://user:pass@pooler.host:6543/mydb?pgbouncer=true');
```

## License

MIT