@pafi-dev/issuer-postgres

Postgres-backed IPointLedger implementation for @pafi-dev/issuer. TypeORM entities, migrations, and a fully-baked PostgresPointLedger service issuers can drop in directly — no need to reverse-engineer the schema from the reference issuer.

Server-only. Pulls in TypeORM (peer-dep). Do not bundle into a browser app.

Compatible with @pafi-dev/issuer. Schema baseline includes user_op_hash columns + indexes for the mobile prepare/submit bundler-receipt fallback.

Why this exists

Every issuer needs the same ledger schema:

• user_balances (per (user, token))
• locked_mint_requests (PENDING reservations during mint)
• pending_credits (reverse flow — burn → off-chain credit)
• ledger_journal (append-only audit trail)
• indexer_cursors (block cursors for PointIndexer / BurnIndexer)

Plus the same race-safe transactions, expired-lock sweeps, multi-token guards, and userOpHash binding for bundler-receipt fallback. This package ships all of it.

Requirements

• Node.js >= 18
• TypeScript >= 5.0
• typeorm ^0.3.0 (peer)
• viem ^2.0.0 (peer, transitively via @pafi-dev/issuer)
• Postgres 13+ (for gen_random_uuid() / pgcrypto)

Installation

pnpm add @pafi-dev/issuer-postgres @pafi-dev/issuer typeorm viem

Quick start (NestJS)

import { TypeOrmModule } from "@nestjs/typeorm";
import {
  PostgresPointLedger,
  PostgresCursorStore,
  PAFI_ENTITIES,
  PAFI_MIGRATIONS,
} from "@pafi-dev/issuer-postgres";

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: "postgres",
      url: process.env.DATABASE_URL,
      entities: [...PAFI_ENTITIES /*, ...yourCustomEntities */],
      migrations: [...PAFI_MIGRATIONS /*, ...yourCustomMigrations */],
      migrationsRun: true,
      // Required — some shipped migrations use `CREATE INDEX
      // CONCURRENTLY` which cannot run inside a transaction.
      // The default "all" mode wraps every migration in one tx
      // and rejects per-migration `transaction = false` opt-outs.
      migrationsTransactionMode: "each",
    }),
  ],
  providers: [
    {
      provide: "POINT_LEDGER",
      useFactory: (dataSource: DataSource) =>
        new PostgresPointLedger(dataSource, { logger: console }),
      inject: [DataSource],
    },
    {
      provide: "INDEXER_CURSOR_STORE",
      useFactory: (dataSource: DataSource) =>
        new PostgresCursorStore(dataSource),
      inject: [DataSource],
    },
  ],
})
export class LedgerModule {}

Then wire POINT_LEDGER into createIssuerService({ ledger, ... }) from @pafi-dev/issuer.

What you get

PostgresPointLedger

Implements every method on IPointLedger from @pafi-dev/issuer:

• Reads — getBalance, getLockedRequests, getMintLock, getPendingCredit, listUserTransactions
• Writes — lockForMinting, releaseLock, deductBalance, creditBalance, updateMintStatus
• Reverse flow — reservePendingCredit, resolveCreditByBurnTx, findPendingCreditLockId
• Mobile flow — bindMintUserOpHash, bindCreditUserOpHash (bundler-receipt fallback against PointIndexer's amount race)

All mutating methods run inside a TypeORM transaction() so balance

• lock + journal updates land atomically.

PostgresCursorStore

Implements IIndexerCursorStore. Default key is "default" for the primary mint indexer. Use .forKey(id) to derive sibling stores for the burn indexer or per-token shards:

const mintCursor = new PostgresCursorStore(dataSource);
const burnCursor = mintCursor.forKey(`burn:${pointTokenAddress.toLowerCase()}`);

Entities + migrations

Drop into your TypeORM config:

import { PAFI_ENTITIES, PAFI_MIGRATIONS } from "@pafi-dev/issuer-postgres";

new DataSource({
  entities: [...PAFI_ENTITIES, ...yourCustomEntities],
  migrations: [...PAFI_MIGRATIONS, ...yourCustomMigrations],
});

InitialSchema1700000000000 is one consolidated migration covering all five tables + indexes (including the user_op_hash columns and the bundler-fallback indexes). For schema changes after this baseline, generate a follow-up migration in your own repo — never edit shipped migrations in place.

Multi-token

Every mutating method requires tokenAddress. There is no "default token" bucket — single-token issuers pass the same address every call. The package throws if you forget, with a clear error message:

PostgresPointLedger: tokenAddress is required on every call (multi-token ledger)

Balances are keyed by composite (userAddress, tokenAddress) PK.

Issuer-specific extensions

Don't subclass PostgresPointLedger. Instead:

1. Add your tables in your own repo (campaign_rules, kyc_status, etc.) with their own TypeORM entities and a follow-up migration.
2. Wrap or compose PostgresPointLedger if you need to layer business rules on top of creditBalance / lockForMinting.
3. For audit fields specific to your issuer (e.g. partner_id, campaign_id), extend LedgerJournalEntity via inheritance in your own entity, register it in your entities[], and add a migration that ALTERs the column.

The shipped entities use TypeORM Discriminator-friendly defaults — extending without breaking the schema is straightforward.

Production deployment checklist

Recommended Postgres + TypeORM config for an issuer running real-money balances.

1. Connection pool sizing

TypeORM defaults inherit from pg's defaults (max: 10, no idle timeout) which silently throttles under load. Tune via DataSourceOptions.extra:

const dataSource = new DataSource({
  type: "postgres",
  url: process.env.DATABASE_URL,
  entities: [...PAFI_ENTITIES, ...yourEntities],
  migrations: [...PAFI_MIGRATIONS],
  extra: {
    // Per-pod pool max — total = max × pod count. Stay under
    // Postgres's `max_connections` (default 100; raise via
    // `ALTER SYSTEM SET max_connections = 200` if needed).
    max: 20,
    // Drop idle connections after 30s — frees slots for other pods
    // and avoids hitting per-connection memory bloat.
    idleTimeoutMillis: 30_000,
    // Connection acquisition deadline — fail fast instead of
    // queueing requests indefinitely under back-pressure.
    connectionTimeoutMillis: 5_000,
    // Per-statement deadline (also see "statement_timeout" below).
    // 30s is a generous cap for read queries; tighten to 5s in
    // production once you've measured tail latencies.
    statement_timeout: 30_000,
  },
});

2. Server-side timeouts

Set at the database role level (run once during issuer onboarding):

-- Per-statement deadline — backstop against runaway queries holding
-- a connection indefinitely. 30s is conservative; tune lower once
-- you've measured your p99.
ALTER ROLE issuer_app SET statement_timeout = '30s';

-- Auto-rollback transactions that idle without sending another query.
-- Critical pairing with FOR UPDATE: a stale transaction holds row
-- locks, blocking concurrent claim/redeem. 60s is safe; aggressive
-- shops set 10s.
ALTER ROLE issuer_app SET idle_in_transaction_session_timeout = '60s';

-- Lock acquisition deadline — fail fast on deadlock-adjacent waits
-- instead of waiting for Postgres's default deadlock_timeout (1s)
-- followed by automatic retry.
ALTER ROLE issuer_app SET lock_timeout = '10s';

3. Expired lock sweep

getBalance is a pure read (does not transition expired locks itself). Schedule markExpiredLocks() periodically to keep the locked_mint_requests table from growing unbounded:

import { Interval } from "@nestjs/schedule";

@Injectable()
export class LockSweepService {
  private readonly logger = new Logger(LockSweepService.name);

  constructor(@Inject("PAFI_LEDGER") private readonly ledger: PostgresPointLedger) {}

  // Every 5 minutes — cheap UPDATE, single round trip.
  @Interval(5 * 60 * 1000)
  async sweep() {
    try {
      const sweptLocks = await this.ledger.markExpiredLocks();
      const sweptCredits = await this.ledger.markExpiredCredits();
      if (sweptLocks > 0 || sweptCredits > 0) {
        this.logger.debug(
          `expired ${sweptLocks} mint locks, ${sweptCredits} pending credits`,
        );
      }
    } catch (err) {
      this.logger.error("ledger sweep failed", err);
    }
  }
}

Audit PACI5-20 — both markExpiredLocks() (mint side) and markExpiredCredits() (burn/credit side) MUST be wired. Without the credit-side sweep, abandoned PendingCredit reservations accumulate forever and findPendingCreditLockId would risk attributing fresh on-chain burns to stale (user, amount) rows. As of @pafi-dev/[email protected] the lookup also filters expires_at > now() and orders newest-first as defense-in-depth in case the sweep tick is delayed.

4. Required indexes

Shipped migrations (InitialSchema + AddLockedMintCompositeIndexes) create these indexes on locked_mint_requests. Verify after migration:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'locked_mint_requests'
ORDER BY indexname;

Expected output:

-- Hot path: sumPendingLocks (during getBalance + lockForMinting)
CREATE INDEX "IDX_locked_mint_user_token_status_expires"
  ON locked_mint_requests (user_address, token_address, status, expires_at);

-- Hot path: PointIndexer.pickMatchingLock + deductBalance findOne
CREATE INDEX "IDX_locked_mint_user_token_amount_status"
  ON locked_mint_requests (user_address, token_address, amount, status);

-- Sweep path: markExpiredLocks UPDATE (partial — only PENDING rows)
CREATE INDEX "IDX_locked_mint_pending_expires"
  ON locked_mint_requests (expires_at) WHERE status = 'PENDING';

-- Bundler-receipt fallback in statusHandlers
CREATE INDEX "IDX_locked_mint_user_op_hash"
  ON locked_mint_requests (user_op_hash);

Plus the partial unique index on ledger_journal that enforces indexer idempotency (MINT_CONFIRMED, BURN_FOR_CREDIT):

CREATE UNIQUE INDEX "UQ_ledger_journal_user_tx_reason"
  ON ledger_journal (user_address, tx_hash, reason)
  WHERE tx_hash IS NOT NULL;

5. Monitoring queries

Surface key health metrics to your APM (Datadog / Grafana / Sentry):

-- Outstanding PENDING locks per token (alert if > N)
SELECT token_address, COUNT(*) AS pending_count, SUM(amount::numeric) AS total_locked
FROM locked_mint_requests
WHERE status = 'PENDING' AND expires_at > NOW()
GROUP BY token_address;

-- Pool saturation
SELECT count(*), state FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

-- Long-running locks (potential deadlock indicator)
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE state IN ('active','idle in transaction')
  AND now() - xact_start > interval '10 seconds'
ORDER BY duration DESC;

6. Backup + recovery

Ledger is the source of truth for off-chain points balance. Configure:

• Continuous WAL archiving (pg_basebackup + archive_command)
• Daily logical dumps (pg_dump --format=custom) retained 30+ days
• Point-in-time recovery validated quarterly via dry-run restore

Loss of user_balances rows = customer rebate liability proportional to historic mints. Treat backup verification as a security control, not an operational nice-to-have.

License

Apache-2.0