/*
 TechStackBase – Rate Limits
 Version: v1.0.0
 Purpose: Server-side rate limiting & abuse protection

 DESIGN PRINCIPLES
 -----------------
 • Stateless client, stateful server
 • Key-based (endpoint + actor)
 • Supports authenticated & unauthenticated traffic
 • High-write, short-lived data
 • Safe to purge aggressively
*/

/* ============================================================
   RATE LIMITS TABLE
   ============================================================ */

CREATE TABLE rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    /* Rate limit key (hashed composite) */
    rate_key CHAR(64) NOT NULL,

    /* Context (optional but useful for audits/debugging) */
    endpoint VARCHAR(128) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_id BIGINT UNSIGNED NULL,

    /* Counters */
    attempts INT UNSIGNED NOT NULL DEFAULT 1,

    /* Window expiry */
    expires_at DATETIME NOT NULL,

    /* Metadata */
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    /* Constraints & indexes */
    UNIQUE KEY uq_rate_limits_key (rate_key),
    INDEX idx_rate_limits_ip (ip_address),
    INDEX idx_rate_limits_user (user_id),
    INDEX idx_rate_limits_expiry (expires_at),
    INDEX idx_rate_limits_endpoint (endpoint),

    /* Foreign key (soft integrity) */
    CONSTRAINT fk_rate_limits_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='API rate limiting & abuse prevention';
