PostgreSQL Store

The PostgreSQL Store is Ledger's production storage backend. It uses the grove ORM with the PostgreSQL driver, providing persistent storage with automatic migrations, multi-tenant isolation, and optimized queries for high-throughput billing operations.

Setup

import (
    "github.com/xraph/grove"
    "github.com/xraph/grove/drivers/pgdriver"
    "github.com/xraph/ledger"
    "github.com/xraph/ledger/store/postgres"
)

db, err := grove.Open(pgdriver.Open(os.Getenv("DATABASE_URL")))
if err != nil {
    log.Fatal(err)
}

s := postgres.New(db)

engine := ledger.New(s,
    ledger.WithMeterConfig(100, 5*time.Second),
    ledger.WithEntitlementCacheTTL(30*time.Second),
)

// Start runs migrations automatically
if err := engine.Start(ctx); err != nil {
    log.Fatal(err)
}
defer engine.Stop()

Migrations

Migrations run automatically when engine.Start() is called via the Migrate(ctx) method. The store uses the grove orchestrator with programmatic migrations that create and update tables as needed.

For manual migration control:

if err := s.Migrate(ctx); err != nil {
    log.Fatal("migration failed:", err)
}

Migrations are idempotent -- safe to run on every startup.

Database schema

The PostgreSQL store creates and manages the following tables:

• ledger_plans -- Plans with features (JSONB), pricing, currency, and billing cycle
• ledger_subscriptions -- Customer subscriptions with period tracking and trial support
• ledger_usage_events -- High-throughput usage event ingestion with idempotency
• ledger_invoices -- Invoices with line items (JSONB) and payment tracking
• ledger_coupons -- Discount coupons with redemption limits
• ledger_entitlement_cache -- TTL-based entitlement result caching

All tables include tenant_id and app_id columns for multi-tenant isolation, with composite indexes for efficient queries.

Multi-tenancy

All queries enforce tenant isolation via tenant_id and app_id filtering. Composite indexes on (tenant_id, app_id) ensure efficient multi-tenant queries.

Implements store.Store

// Compile-time check
var _ store.Store = (*Store)(nil)

Characteristics

When to use

• Production deployments requiring ACID transactions and full SQL query power.
• Multi-process environments with connection pooling.
• High-throughput billing with usage event ingestion.

See Memory Store for a comparison of implementations.