buff3.0/backend-client/migrations/20260125_create_api_nonces.sql

-- Migration: create api_nonces table
-- Date: 2026-01-25
-- This file contains DDL for both Postgres and MySQL. Apply the appropriate section for your RDBMS.

/* ============================
   PostgreSQL
   ============================ */
-- Postgres: create table with timestamptz and index on expires_at
CREATE TABLE IF NOT EXISTS api_nonces (
  nonce VARCHAR(128) PRIMARY KEY,
  endpoint VARCHAR(128) NOT NULL,
  session_id VARCHAR(64),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_api_nonces_expires_at ON api_nonces (expires_at);

-- Optional: a simple cleanup function (run as cron/pgagent) example:
-- DELETE FROM api_nonces WHERE expires_at < now();


/* ============================
   MySQL / MariaDB (compatible with MySQL 5.7)
   ============================ */
-- Use DATETIME for compatibility with strict SQL modes in MySQL 5.7.
-- created_at defaults to CURRENT_TIMESTAMP; expires_at is a DATETIME that must be set by the inserter.
CREATE TABLE IF NOT EXISTS api_nonces (
  nonce VARCHAR(128) NOT NULL,
  endpoint VARCHAR(128) NOT NULL,
  session_id VARCHAR(64),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  PRIMARY KEY (nonce),
  INDEX idx_api_nonces_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional cleanup (run as event/cron):
-- DELETE FROM api_nonces WHERE expires_at < CURRENT_TIMESTAMP;