46 lines
2.1 KiB
SQL
46 lines
2.1 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
CREATE TABLE public.push_delivery_attempts (
|
||
|
|
id text PRIMARY KEY,
|
||
|
|
notification_delivery_id text REFERENCES public.notification_deliveries(id) ON DELETE CASCADE,
|
||
|
|
push_device_id text NOT NULL REFERENCES public.push_devices(id) ON DELETE CASCADE,
|
||
|
|
trigger_type text NOT NULL DEFAULT 'delivery',
|
||
|
|
provider text NOT NULL,
|
||
|
|
platform text NOT NULL,
|
||
|
|
attempt_number integer NOT NULL DEFAULT 0,
|
||
|
|
attempted_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
next_retry_at timestamptz,
|
||
|
|
outcome text NOT NULL DEFAULT 'pending',
|
||
|
|
relay_request_id text,
|
||
|
|
upstream_status integer,
|
||
|
|
upstream_reason varchar(256),
|
||
|
|
failure_message varchar(256),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT push_delivery_attempts_trigger_check CHECK (trigger_type IN ('delivery', 'test')),
|
||
|
|
CONSTRAINT push_delivery_attempts_delivery_required_check CHECK (
|
||
|
|
(trigger_type = 'delivery' AND notification_delivery_id IS NOT NULL)
|
||
|
|
OR trigger_type = 'test'
|
||
|
|
),
|
||
|
|
CONSTRAINT push_delivery_attempts_provider_check CHECK (provider IN ('silo_relay')),
|
||
|
|
CONSTRAINT push_delivery_attempts_platform_check CHECK (platform IN ('apple')),
|
||
|
|
CONSTRAINT push_delivery_attempts_outcome_check CHECK (outcome IN ('pending', 'delivered', 'retrying', 'failed'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX push_delivery_attempts_delivery_unique
|
||
|
|
ON public.push_delivery_attempts (push_device_id, notification_delivery_id, attempt_number)
|
||
|
|
WHERE notification_delivery_id IS NOT NULL;
|
||
|
|
CREATE INDEX push_delivery_attempts_delivery_idx
|
||
|
|
ON public.push_delivery_attempts (notification_delivery_id)
|
||
|
|
WHERE notification_delivery_id IS NOT NULL;
|
||
|
|
CREATE INDEX push_delivery_attempts_retry_idx
|
||
|
|
ON public.push_delivery_attempts (outcome, next_retry_at);
|
||
|
|
CREATE INDEX push_delivery_attempts_device_history_idx
|
||
|
|
ON public.push_delivery_attempts (push_device_id, attempted_at DESC);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE IF EXISTS public.push_delivery_attempts;
|
||
|
|
-- +goose StatementEnd
|