PostgreSQL Store

The PostgreSQL store provides a full store.Store implementation using grove ORM with the pgdriver backend. It includes embedded SQL migrations and is the recommended backend for production.

Setup

From a DSN

import "github.com/xraph/keysmith/store/postgres"

pgStore, err := postgres.NewFromDSN(ctx, "postgres://user:pass@localhost:5432/mydb")
if err != nil {
    log.Fatal(err)
}
defer pgStore.Close()

From an existing grove.DB

If your application already manages a grove.DB connection (e.g. via the Grove extension), pass the unwrapped pgdriver instance:

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

db, err := grove.Open(pgdriver.New(
    pgdriver.WithDSN("postgres://user:pass@localhost:5432/mydb"),
))
if err != nil {
    log.Fatal(err)
}

pgStore := postgres.New(pgdriver.Unwrap(db))

Migrations

The store embeds SQL migrations. Run them on startup:

if err := pgStore.Migrate(ctx); err != nil {
    log.Fatal(err)
}

This creates seven tables:

Table	Description
`keysmith_keys`	API keys with hash, state, and metadata
`keysmith_policies`	Policy definitions
`keysmith_scopes`	Scope definitions
`keysmith_key_scopes`	Key-scope junction table
`keysmith_usage`	Per-request usage records
`keysmith_usage_agg`	Aggregated usage (daily/monthly)
`keysmith_rotations`	Rotation history records

Migrations are idempotent and safe to run on every startup.

Health checks

if err := pgStore.Ping(ctx); err != nil {
    log.Fatal("database unreachable:", err)
}

Usage with the engine

eng, err := keysmith.NewEngine(keysmith.WithStore(pgStore))

Schema overview

keysmith_keys

CREATE TABLE keysmith_keys (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    hash        TEXT NOT NULL UNIQUE,
    prefix      TEXT NOT NULL,
    environment TEXT NOT NULL,
    state       TEXT NOT NULL DEFAULT 'active',
    app_id      TEXT NOT NULL,
    tenant_id   TEXT NOT NULL,
    policy_id   TEXT,
    expires_at  TIMESTAMPTZ,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_keys_hash ON keysmith_keys (hash);
CREATE INDEX idx_keys_tenant ON keysmith_keys (app_id, tenant_id);
CREATE INDEX idx_keys_state ON keysmith_keys (state);

PostgreSQL Store

On this page