Database Schemas

This page collects the database schemas used by the built-in Postgres and SQLite adapters. Eventually we will allow custom schemas to be used. (see Roadmap)

Keystore (Postgres)

CREATE TABLE IF NOT EXISTS usefulkey_keys (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  prefix TEXT NOT NULL,
  key_hash TEXT NOT NULL UNIQUE,
  created_at BIGINT NOT NULL,
  expires_at BIGINT,
  metadata JSONB,
  uses_remaining INTEGER,
  revoked_at BIGINT
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_keys_user_id ON usefulkey_keys(user_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_usefulkey_keys_key_hash ON usefulkey_keys(key_hash);

Notes:

metadata can be JSONB or TEXT; the adapter supports both. Prefer JSONB when available.
Ensure key_hash has a unique index to prevent duplicates.

Keystore (SQLite)

CREATE TABLE IF NOT EXISTS usefulkey_keys (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  prefix TEXT NOT NULL,
  key_hash TEXT NOT NULL UNIQUE,
  created_at INTEGER NOT NULL,
  expires_at INTEGER,
  metadata TEXT,
  uses_remaining INTEGER,
  revoked_at INTEGER
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_keys_user_id ON usefulkey_keys(user_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_usefulkey_keys_key_hash ON usefulkey_keys(key_hash);

Rate limit store — fixed window (Postgres)

CREATE TABLE IF NOT EXISTS usefulkey_rate_limits (
  namespace TEXT NOT NULL,
  identifier TEXT NOT NULL,
  count INTEGER NOT NULL,
  reset BIGINT NOT NULL,
  PRIMARY KEY(namespace, identifier)
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_rate_limits_reset ON usefulkey_rate_limits(reset);

Rate limit store — token bucket (Postgres)

CREATE TABLE IF NOT EXISTS usefulkey_rate_limits_buckets (
  namespace TEXT NOT NULL,
  identifier TEXT NOT NULL,
  tokens DOUBLE PRECISION NOT NULL,
  lastRefill BIGINT NOT NULL,
  capacity INTEGER NOT NULL,
  refillTokens DOUBLE PRECISION NOT NULL,
  refillIntervalMs BIGINT NOT NULL,
  PRIMARY KEY(namespace, identifier)
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_rate_limits_buckets_lastRefill ON usefulkey_rate_limits_buckets(lastRefill);

Rate limit store — SQLite

CREATE TABLE IF NOT EXISTS usefulkey_rate_limits (
  namespace TEXT NOT NULL,
  identifier TEXT NOT NULL,
  count INTEGER NOT NULL,
  reset INTEGER NOT NULL,
  PRIMARY KEY(namespace, identifier)
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_rate_limits_reset ON usefulkey_rate_limits(reset);

CREATE TABLE IF NOT EXISTS usefulkey_rate_limits_buckets (
  namespace TEXT NOT NULL,
  identifier TEXT NOT NULL,
  tokens REAL NOT NULL,
  lastRefill INTEGER NOT NULL,
  capacity INTEGER NOT NULL,
  refillTokens REAL NOT NULL,
  refillIntervalMs INTEGER NOT NULL,
  PRIMARY KEY(namespace, identifier)
);

CREATE INDEX IF NOT EXISTS idx_usefulkey_rate_limits_buckets_lastRefill ON usefulkey_rate_limits_buckets(lastRefill);

Recommendations

Keep timestamps in epoch milliseconds for portability.
Use connection pooling and run DDL at boot only when needed.
Consider periodic pruning of old rate limit rows by reset.

Database Schemas

Keystore (Postgres)

Keystore (SQLite)

Rate limit store — fixed window (Postgres)

Rate limit store — token bucket (Postgres)

Rate limit store — SQLite

Recommendations

On this page