API Rate-Limiting as a Multi-Region Service

-- Rate Limiting Platform Schema

-- Rate limit policies with hierarchical inheritance
CREATE TABLE rate_policies (
    policy_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    algorithm VARCHAR(50) NOT NULL DEFAULT 'token_bucket',
    -- CHECK (algorithm IN ('token_bucket', 'sliding_window_log', 'sliding_window_counter', 'fixed_window')),
    limit_value INT NOT NULL,
    window_seconds INT NOT NULL,
    burst_size INT NOT NULL DEFAULT 0,
    refill_rate DECIMAL(10,4), -- tokens per second for token bucket
    parent_policy_id UUID REFERENCES rate_policies(policy_id),
    hierarchy_level VARCHAR(20) NOT NULL DEFAULT 'user',
    -- CHECK (hierarchy_level IN ('user', 'api_key', 'organization', 'global')),
    targeting_rules JSONB DEFAULT '{}',
    is_active BOOLEAN NOT NULL DEFAULT true,
    version INT NOT NULL DEFAULT 1,
    created_by VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_policies_active ON rate_policies (is_active, hierarchy_level);
CREATE INDEX idx_policies_parent ON rate_policies (parent_policy_id) WHERE parent_policy_id IS NOT NULL;
CREATE INDEX idx_policies_targeting ON rate_policies USING GIN (targeting_rules);

-- Policy audit log for compliance and debugging
CREATE TABLE policy_audit_log (
    audit_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    policy_id UUID NOT NULL REFERENCES rate_policies(policy_id),
    action VARCHAR(20) NOT NULL, -- 'created', 'updated', 'deleted', 'activated', 'deactivated'
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(255) NOT NULL,
    change_reason TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_policy ON policy_audit_log (policy_id, created_at DESC);
CREATE INDEX idx_audit_time ON policy_audit_log (created_at DESC);

-- Counter sync state for cross-region reconciliation
CREATE TABLE counter_sync_state (
    counter_key TEXT NOT NULL,
    region VARCHAR(50) NOT NULL,
    local_count BIGINT NOT NULL DEFAULT 0,
    last_sync_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    sync_generation BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (counter_key, region)
);

CREATE INDEX idx_sync_stale ON counter_sync_state (last_sync_at) WHERE last_sync_at < NOW() - INTERVAL '10 seconds';

-- Hot key tracking for automatic sharding decisions
CREATE TABLE hot_key_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    counter_key TEXT NOT NULL,
    region VARCHAR(50) NOT NULL,
    checks_per_second INT NOT NULL,
    shard_count INT NOT NULL DEFAULT 1,
    action VARCHAR(20) NOT NULL, -- 'detected', 'sharded', 'unsharded'
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_hotkey_recent ON hot_key_events (counter_key, created_at DESC);

-- Sampled decision log for troubleshooting and quota analytics
CREATE TABLE rate_check_log (
    check_id BIGSERIAL PRIMARY KEY,
    checked_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    counter_key TEXT NOT NULL,
    policy_id UUID REFERENCES rate_policies(policy_id),
    region VARCHAR(50) NOT NULL,
    decision VARCHAR(16) NOT NULL, -- 'allowed', 'denied', 'degraded_allow'
    checks_per_second INT,
    observed_latency_ms DECIMAL(10,3)
);

CREATE INDEX idx_rate_check_denied_recent ON rate_check_log (checked_at DESC, counter_key)
WHERE decision = 'denied';

-- Redis key schemas (documented for operations)
-- rate:{level}:{id} => HASH {tokens: float, last_refill: timestamp}
-- rate:{level}:{id}:shard:{n} => HASH (sharded counter)
-- policy:{policy_id} => JSON (cached policy)
-- hotkey:{key} => INT (check count in current window)