50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- ============================================================================
|
|
-- AUDIT SCHEMA - Tabla: api_request_logs
|
|
-- ============================================================================
|
|
-- Log de requests a la API (para debugging y análisis)
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS audit.api_request_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Request
|
|
request_id UUID NOT NULL,
|
|
method VARCHAR(10) NOT NULL,
|
|
path VARCHAR(500) NOT NULL,
|
|
query_params JSONB,
|
|
headers JSONB,
|
|
body_size INTEGER,
|
|
|
|
-- Actor
|
|
user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
api_key_id UUID,
|
|
ip_address INET NOT NULL,
|
|
user_agent TEXT,
|
|
|
|
-- Response
|
|
status_code INTEGER NOT NULL,
|
|
response_size INTEGER,
|
|
response_time_ms INTEGER NOT NULL,
|
|
|
|
-- Contexto
|
|
service_name VARCHAR(50),
|
|
version VARCHAR(20),
|
|
error_code VARCHAR(50),
|
|
error_message TEXT,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Índices optimizados para consultas de análisis
|
|
CREATE INDEX idx_api_logs_user ON audit.api_request_logs(user_id);
|
|
CREATE INDEX idx_api_logs_path ON audit.api_request_logs(path);
|
|
CREATE INDEX idx_api_logs_status ON audit.api_request_logs(status_code);
|
|
CREATE INDEX idx_api_logs_created ON audit.api_request_logs(created_at DESC);
|
|
CREATE INDEX idx_api_logs_ip ON audit.api_request_logs(ip_address);
|
|
CREATE INDEX idx_api_logs_slow ON audit.api_request_logs(response_time_ms) WHERE response_time_ms > 1000;
|
|
CREATE INDEX idx_api_logs_errors ON audit.api_request_logs(status_code) WHERE status_code >= 400;
|
|
|
|
COMMENT ON TABLE audit.api_request_logs IS 'Log de requests HTTP para análisis y debugging';
|
|
COMMENT ON COLUMN audit.api_request_logs.body_size IS 'Tamaño del body en bytes (no se guarda contenido por seguridad)';
|