53 lines
2.7 KiB
SQL
Executable file
53 lines
2.7 KiB
SQL
Executable file
-- Payments Database Schema
|
|
-- Standard PostgreSQL tables for transaction and webhook tracking
|
|
|
|
SET search_path TO payments, public;
|
|
|
|
-- =============================================================================
|
|
-- PAYMENT WEBHOOK EVENTS: Audit trail for all incoming webhook events
|
|
-- =============================================================================
|
|
CREATE TABLE IF NOT EXISTS payment_webhook_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider VARCHAR(50) NOT NULL,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
processing_status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
idempotency_key VARCHAR(255) NOT NULL UNIQUE,
|
|
error_message TEXT,
|
|
processed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Composite index for common query pattern (provider + time range)
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_provider_created
|
|
ON payment_webhook_events(provider, created_at DESC);
|
|
|
|
-- Index for filtering by event type and status
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_type_status
|
|
ON payment_webhook_events(event_type, processing_status);
|
|
|
|
-- Index for fast idempotency checks (already covered by UNIQUE constraint, but explicit)
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_idempotency
|
|
ON payment_webhook_events(idempotency_key);
|
|
|
|
-- Index for time-based queries
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_created
|
|
ON payment_webhook_events(created_at DESC);
|
|
|
|
-- Index for finding failed webhooks
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_status
|
|
ON payment_webhook_events(processing_status)
|
|
WHERE processing_status = 'failed';
|
|
|
|
-- =============================================================================
|
|
-- Comments for documentation
|
|
-- =============================================================================
|
|
COMMENT ON TABLE payment_webhook_events IS 'Audit trail for all payment provider webhooks (Segpay, NOWPayments, etc.)';
|
|
COMMENT ON COLUMN payment_webhook_events.provider IS 'Payment provider name (segpay, nowpayments)';
|
|
COMMENT ON COLUMN payment_webhook_events.event_type IS 'Webhook event type (subscription.created, payment.succeeded, etc.)';
|
|
COMMENT ON COLUMN payment_webhook_events.payload IS 'Full webhook payload as JSON for replay capability';
|
|
COMMENT ON COLUMN payment_webhook_events.processing_status IS 'Processing status: pending, success, failed';
|
|
COMMENT ON COLUMN payment_webhook_events.idempotency_key IS 'Unique key to prevent duplicate processing (typically external transaction ID)';
|
|
COMMENT ON COLUMN payment_webhook_events.error_message IS 'Error details if processing failed';
|
|
COMMENT ON COLUMN payment_webhook_events.processed_at IS 'When the webhook was successfully processed';
|
|
COMMENT ON COLUMN payment_webhook_events.created_at IS 'When the webhook was received';
|