rckrdmrd
50a821a415
- HERENCIA-SIMCO.md actualizado con directivas v3.7 y v3.8 - Actualizaciones de configuracion Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
118 lines
4.1 KiB
PL/PgSQL
118 lines
4.1 KiB
PL/PgSQL
-- ============================================
|
|
-- WEBHOOKS: Delivery Log Table
|
|
-- ============================================
|
|
|
|
-- Delivery attempts table
|
|
CREATE TABLE webhooks.deliveries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
webhook_id UUID NOT NULL REFERENCES webhooks.webhooks(id) ON DELETE CASCADE,
|
|
tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Event info
|
|
event_type TEXT NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
|
|
-- Delivery status
|
|
status webhooks.delivery_status NOT NULL DEFAULT 'pending',
|
|
|
|
-- Response info
|
|
response_status INTEGER,
|
|
response_body TEXT,
|
|
response_headers JSONB,
|
|
|
|
-- Retry tracking
|
|
attempt INTEGER NOT NULL DEFAULT 1,
|
|
max_attempts INTEGER NOT NULL DEFAULT 5,
|
|
next_retry_at TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
delivered_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_deliveries_webhook ON webhooks.deliveries(webhook_id);
|
|
CREATE INDEX idx_deliveries_tenant ON webhooks.deliveries(tenant_id);
|
|
CREATE INDEX idx_deliveries_status ON webhooks.deliveries(status) WHERE status IN ('pending', 'retrying');
|
|
CREATE INDEX idx_deliveries_retry ON webhooks.deliveries(next_retry_at) WHERE status = 'retrying' AND next_retry_at IS NOT NULL;
|
|
CREATE INDEX idx_deliveries_created ON webhooks.deliveries(created_at DESC);
|
|
|
|
-- RLS
|
|
ALTER TABLE webhooks.deliveries ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY deliveries_tenant_isolation ON webhooks.deliveries
|
|
FOR ALL
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
-- Function to calculate next retry time with exponential backoff
|
|
CREATE OR REPLACE FUNCTION webhooks.calculate_next_retry(attempt INTEGER)
|
|
RETURNS TIMESTAMPTZ AS $$
|
|
BEGIN
|
|
-- Retry schedule: 1min, 5min, 30min, 2hours, 6hours
|
|
RETURN CASE attempt
|
|
WHEN 1 THEN NOW() + INTERVAL '1 minute'
|
|
WHEN 2 THEN NOW() + INTERVAL '5 minutes'
|
|
WHEN 3 THEN NOW() + INTERVAL '30 minutes'
|
|
WHEN 4 THEN NOW() + INTERVAL '2 hours'
|
|
WHEN 5 THEN NOW() + INTERVAL '6 hours'
|
|
ELSE NULL -- No more retries
|
|
END;
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Function to get pending deliveries for retry
|
|
CREATE OR REPLACE FUNCTION webhooks.get_pending_retries(batch_size INTEGER DEFAULT 100)
|
|
RETURNS SETOF webhooks.deliveries AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT *
|
|
FROM webhooks.deliveries
|
|
WHERE status = 'retrying'
|
|
AND next_retry_at <= NOW()
|
|
ORDER BY next_retry_at ASC
|
|
LIMIT batch_size
|
|
FOR UPDATE SKIP LOCKED;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to get delivery stats for a webhook
|
|
CREATE OR REPLACE FUNCTION webhooks.get_webhook_stats(p_webhook_id UUID)
|
|
RETURNS TABLE (
|
|
total_deliveries BIGINT,
|
|
successful_deliveries BIGINT,
|
|
failed_deliveries BIGINT,
|
|
pending_deliveries BIGINT,
|
|
success_rate NUMERIC,
|
|
last_delivery_at TIMESTAMPTZ
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
COUNT(*)::BIGINT as total_deliveries,
|
|
COUNT(*) FILTER (WHERE status = 'delivered')::BIGINT as successful_deliveries,
|
|
COUNT(*) FILTER (WHERE status = 'failed')::BIGINT as failed_deliveries,
|
|
COUNT(*) FILTER (WHERE status IN ('pending', 'retrying'))::BIGINT as pending_deliveries,
|
|
CASE
|
|
WHEN COUNT(*) FILTER (WHERE status IN ('delivered', 'failed')) > 0
|
|
THEN ROUND(
|
|
COUNT(*) FILTER (WHERE status = 'delivered')::NUMERIC /
|
|
COUNT(*) FILTER (WHERE status IN ('delivered', 'failed'))::NUMERIC * 100, 2
|
|
)
|
|
ELSE 0
|
|
END as success_rate,
|
|
MAX(delivered_at) as last_delivery_at
|
|
FROM webhooks.deliveries
|
|
WHERE webhook_id = p_webhook_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE webhooks.deliveries IS 'Webhook delivery attempts and status tracking';
|
|
COMMENT ON COLUMN webhooks.deliveries.attempt IS 'Current attempt number (1-based)';
|
|
COMMENT ON COLUMN webhooks.deliveries.next_retry_at IS 'When to retry if status is retrying';
|
|
COMMENT ON FUNCTION webhooks.calculate_next_retry IS 'Calculate next retry time with exponential backoff';
|
|
COMMENT ON FUNCTION webhooks.get_pending_retries IS 'Get deliveries ready for retry processing';
|
|
COMMENT ON FUNCTION webhooks.get_webhook_stats IS 'Get delivery statistics for a webhook';
|