SOP-SUB-00: Subscription State Management System¶

Version: 3.0 FINAL
Status: 🟢 Phase 1, 2 & 5 Production Ready | 🟡 Phase 3-4 Pending
Created: 2026-01-16
Updated: 2026-02-03
Owner: Protocol Raw Operations
Classification: Core Infrastructure

Document Purpose¶

This SOP establishes the foundational subscription data infrastructure that enables: - Real-time visibility into all subscription states and changes - Payment failure detection and recovery (dunning) - Proactive card expiry warnings - Subscription analytics and cohort analysis - Automated monitoring and alerting (SOP-MON-01 compliant) - Single source of truth for subscription data across all systems

Design Philosophy: - Seal Subscriptions remains the primary system for subscription management - We maintain a synchronized replica in Supabase for querying, analytics, and automation - All Seal webhooks flow into our system creating an immutable event log - Downstream systems (dunning, analytics, proactive campaigns) query local data - Monitoring runs natively in Supabase — no Make.com dependency for alerting

1. Implementation Status¶

✅ Phase 1: Database & Webhooks — COMPLETE¶

Component	Status	Deployed
`seal_webhook_inbox` table	✅	2026-01-26
`billing_attempts` table	✅	2026-01-26
`subscription_events` table	✅	2026-01-26
`subscriptions` table columns	✅	2026-01-26
RPC: `get_new_payment_failures`	✅	2026-01-26
RPC: `get_dunning_stage_customers`	✅	2026-01-26
RPC: `mark_dunning_email_sent`	✅	2026-01-26
RPC: `log_subscription_event`	✅	2026-01-26
Edge Function: `seal-webhook-handler`	✅	2026-01-26
Seal webhooks registered	✅	2026-01-26
All views created	✅	2026-01-26

✅ Phase 2: Dunning System — COMPLETE¶

Component	Status	Deployed
SUB-01: Dunning Day 0 (15 min)	✅	2026-01-26
SUB-02: Dunning Day 3 (daily 9am)	✅	2026-01-26
SUB-03: Dunning Day 7 (daily 9am)	✅	2026-01-26
SUB-04: Dunning Day 10 (daily 9am)	✅	2026-01-26
Customer.io: `payment_failed`	✅	2026-01-26
Customer.io: `payment_failed_day3`	✅	2026-01-26
Customer.io: `payment_failed_day7`	✅	2026-01-26
Customer.io: `payment_failed_day10`	✅	2026-01-26
Customer.io: `subscription_paused_payment`	✅	2026-01-26
Customer.io: `payment_recovered`	✅	2026-01-26

â³ Phase 3: Card Expiry & Proactive — PENDING¶

Component	Status
SUB-05: Card Expiry Warning	â³
Customer.io: `card_expiring_soon`	â³
Customer.io: `card_expiring_final_warning`	â³

â³ Phase 4: Portal Integration — PENDING¶

Component	Status
Customer Portal: Payment warning banner	✅ Designed
Customer Portal: `get-payment-update-url` Edge Function	✅ Specified
Ops Portal: Dunning queue view	â³
Ops Portal: Subscription history view	â³

✅ Phase 5: Monitoring & Alerting — COMPLETE¶

Component	Status	Deployed
Index: `idx_subscriptions_dunning_active`	✅	2026-02-03
Index: `idx_subscription_events_type_created`	✅	2026-02-03
View: `v_subscription_monitoring_health`	✅	2026-02-03
Function: `fn_check_subscription_health_v2()`	✅	2026-02-03
Function: `fn_check_subscription_daily_v2()`	✅	2026-02-03
`run-monitor` dispatch updated	✅	2026-02-03
pg_cron: `monitor-subscription-health` (*/5 min)	✅	2026-02-03
pg_cron: `monitor-subscription-daily` (09:05 UTC)	✅	2026-02-03
Metabase dashboard: Subscription Health	â³	Future

2. Architecture Overview¶

2.1 Previous State (Pre-SOP-SUB-00)¶

Customer/Ops Portal
        ↕
    Make.com Webhook ──→ Seal API (write)
        ↑
    Seal API (read) â†── Portal page load

Problems: - No local subscription state = no analytics - No webhook listeners = blind to external changes - No payment failure visibility = silent revenue loss - Every read hits Seal API = performance bottleneck at scale

2.2 Current State (Post-SOP-SUB-00)¶

                    ┌─────────────────────────────────────────â”
                    │            Seal Subscriptions           │
                    │         (Primary Source of Truth)       │
                    └────────────────┬────────────────────────┘
                                     │
                    Webhooks (ALL subscription events)
                                     │
                                     ▼
              ┌──────────────────────────────────────────────â”
              │         Supabase Edge Function               │
              │         seal-webhook-handler                 │
              │  • HMAC validation                           │
              │  • Idempotency check                         │
              │  • State update + Event logging              │
              └──────────────────────┬───────────────────────┘
                                     │
                    ┌────────────────┼────────────────â”
                    ▼                ▼                ▼
           ┌─────────────â”  ┌─────────────â”  ┌─────────────â”
           │subscriptions│  │subscription │  │  billing    │
           │   (state)   │  │   _events   │  │  _attempts  │
           └──────┬──────┘  └──────┬──────┘  └──────┬──────┘
                  │                │                │
                  └────────────────┼────────────────┘
                                   │
         ┌─────────────────────────┼─────────────────────────â”
         ▼                         ▼                         ▼
┌─────────────────â”     ┌─────────────────â”     ┌─────────────────â”
│  Customer/Ops   │     │   Make.com      │     │    Metabase     │
│    Portals      │     │  Automations    │     │   Analytics     │
│ (read local)    │     │  (dunning etc)  │     │  (dashboards)   │
└─────────────────┘     └─────────────────┘     └─────────────────┘
                                                         ▲
                                                         │
                                              ┌──────────┴──────────â”
                                              │   pg_cron → pg_net  │
                                              │   → run-monitor     │
                                              │   → ops-alerter     │
                                              │   → Slack           │
                                              └─────────────────────┘

2.3 Integration Points¶

System	Integration	Direction	Purpose
Seal	Webhooks	Seal → Us	Receive all subscription events
Seal	API	Us → Seal	Trigger payment method update emails
Customer.io	Events	Us → Customer.io	Dunning emails, card expiry warnings
Customer Portal	Database	Portal â† Supabase	Read subscription state (faster)
Ops Portal	Database	Portal â† Supabase	Read subscription state + history
Metabase	Views	Metabase â† Supabase	Subscription analytics
Slack	ops-alerter	Supabase → Slack	Monitoring alerts (#ops-alerts, #ops-urgent, #daily-ops)

3. Database Schema¶

3.1 Schema Note: Actual Column Names¶

The subscriptions table uses the following column names (confirmed via information_schema):

Column	Type	Notes
`price_gbp`	numeric	Subscription price in pounds (NOT pence)
`box_size`	text	Box size identifier (NOT `box_size_kg`)

All monitoring functions, views, and queries in this document use these actual column names.

3.2 Core Tables¶

subscriptions¶

Local cache of current subscription state from Seal. Extended with dunning tracking columns.

-- Base table (existing)
-- These columns were ADDED to support SUB-00:

ALTER TABLE raw_ops.subscriptions ADD COLUMN IF NOT EXISTS
  seal_subscription_id BIGINT UNIQUE,
  seal_internal_id TEXT,
  shopify_customer_id BIGINT,
  delivery_interval TEXT,
  billing_interval TEXT,
  variant_id BIGINT,
  currency TEXT DEFAULT 'GBP',
  card_brand TEXT,
  card_last_digits TEXT,
  card_expiry_month INTEGER,
  card_expiry_year INTEGER,
  subscription_created_at TIMESTAMPTZ,
  seal_payload JSONB,
  last_synced_at TIMESTAMPTZ,
  payment_status TEXT DEFAULT 'healthy',
  failed_payment_count INTEGER DEFAULT 0,
  last_failed_at TIMESTAMPTZ,
  dunning_started_at TIMESTAMPTZ,
  last_error_message TEXT,
  dunning_email_day0_sent BOOLEAN DEFAULT FALSE,
  dunning_email_day3_sent BOOLEAN DEFAULT FALSE,
  dunning_email_day7_sent BOOLEAN DEFAULT FALSE,
  dunning_email_day10_sent BOOLEAN DEFAULT FALSE,
  pause_reason TEXT,
  paused_at TIMESTAMPTZ;

-- Key indexes for subscription queries (Phase 1)
CREATE INDEX IF NOT EXISTS idx_subscriptions_seal_id 
  ON raw_ops.subscriptions(seal_subscription_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_payment_status 
  ON raw_ops.subscriptions(payment_status) 
  WHERE payment_status != 'healthy';
CREATE INDEX IF NOT EXISTS idx_subscriptions_next_billing 
  ON raw_ops.subscriptions(next_billing_date) 
  WHERE status = 'ACTIVE';
CREATE INDEX IF NOT EXISTS idx_subscriptions_card_expiry 
  ON raw_ops.subscriptions(card_expiry_year, card_expiry_month) 
  WHERE status = 'ACTIVE';

-- Phase 5 monitoring indexes
CREATE INDEX IF NOT EXISTS idx_subscriptions_dunning_active
  ON raw_ops.subscriptions(dunning_started_at, last_failed_at)
  WHERE payment_status IN ('failing', 'failed') AND status = 'ACTIVE';

subscription_events¶

Immutable audit log of all subscription state changes.

CREATE TABLE raw_ops.subscription_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- References
  subscription_id UUID REFERENCES raw_ops.subscriptions(id),
  seal_subscription_id BIGINT NOT NULL,
  customer_id UUID REFERENCES raw_ops.customers(id),

  -- Event details
  event_type TEXT NOT NULL,
  event_source TEXT NOT NULL, -- 'seal_webhook', 'manual', 'system'

  -- State snapshot
  old_status TEXT,
  new_status TEXT,

  -- Full payload for debugging
  payload JSONB,

  -- Timestamps
  event_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Event types:
-- subscription_created
-- subscription_updated
-- subscription_paused
-- subscription_resumed
-- subscription_reactivated
-- subscription_cancelled
-- subscription_expired
-- payment_method_updated
-- shipping_address_updated
-- billing_attempt_succeeded
-- billing_attempt_failed
-- billing_attempt_upcoming

-- Indexes (Phase 1)
CREATE INDEX idx_subscription_events_subscription_id 
  ON raw_ops.subscription_events(subscription_id);
CREATE INDEX idx_subscription_events_customer_id 
  ON raw_ops.subscription_events(customer_id);
CREATE INDEX idx_subscription_events_type 
  ON raw_ops.subscription_events(event_type);
CREATE INDEX idx_subscription_events_created 
  ON raw_ops.subscription_events(created_at DESC);
CREATE INDEX idx_subscription_events_seal_id 
  ON raw_ops.subscription_events(seal_subscription_id);

-- Phase 5 monitoring index
CREATE INDEX IF NOT EXISTS idx_subscription_events_type_created
  ON raw_ops.subscription_events(event_type, created_at DESC);

billing_attempts¶

Detailed tracking of all billing attempts (successes and failures).

CREATE TABLE raw_ops.billing_attempts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- References
  subscription_id UUID REFERENCES raw_ops.subscriptions(id),
  seal_subscription_id BIGINT NOT NULL,
  seal_billing_attempt_id BIGINT UNIQUE,
  customer_id UUID REFERENCES raw_ops.customers(id),

  -- Attempt details
  scheduled_at TIMESTAMPTZ,
  attempted_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,

  -- Outcome
  status TEXT NOT NULL, -- scheduled, processing, succeeded, failed, skipped

  -- Success info
  shopify_order_id BIGINT,
  shopify_order_name TEXT,
  order_id UUID REFERENCES raw_ops.orders(id),

  -- Failure info
  error_code TEXT,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,

  -- 3DS challenge
  requires_authentication BOOLEAN DEFAULT FALSE,
  authentication_url TEXT,

  -- Amount
  amount_pence INTEGER,
  currency TEXT DEFAULT 'GBP',

  -- Dunning tracking
  is_retry BOOLEAN DEFAULT FALSE,
  original_attempt_id UUID REFERENCES raw_ops.billing_attempts(id),

  -- Metadata
  triggered_manually BOOLEAN DEFAULT FALSE,
  seal_payload JSONB,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_billing_attempts_subscription_id 
  ON raw_ops.billing_attempts(subscription_id);
CREATE INDEX idx_billing_attempts_customer_id 
  ON raw_ops.billing_attempts(customer_id);
CREATE INDEX idx_billing_attempts_status 
  ON raw_ops.billing_attempts(status);
CREATE INDEX idx_billing_attempts_scheduled 
  ON raw_ops.billing_attempts(scheduled_at);
CREATE INDEX idx_billing_attempts_failed 
  ON raw_ops.billing_attempts(subscription_id, status) 
  WHERE status = 'failed';
CREATE INDEX idx_billing_attempts_seal_id 
  ON raw_ops.billing_attempts(seal_billing_attempt_id);

-- Trigger for updated_at
CREATE TRIGGER billing_attempts_updated_at
  BEFORE UPDATE ON raw_ops.billing_attempts
  FOR EACH ROW EXECUTE FUNCTION raw_ops.update_updated_at();

seal_webhook_inbox¶

Raw webhook storage with processing status (mirrors pattern from SOP 00).

CREATE TABLE raw_ops.seal_webhook_inbox (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Webhook metadata
  webhook_topic TEXT NOT NULL,
  seal_subscription_id BIGINT,

  -- Raw payload
  payload JSONB NOT NULL,
  headers JSONB,

  -- Security
  hmac_signature TEXT,
  hmac_valid BOOLEAN,

  -- Processing
  processing_status TEXT DEFAULT 'pending', -- pending, processing, success, failed, dlq
  retry_count INTEGER DEFAULT 0,
  last_error TEXT,
  error_category TEXT,
  processed_at TIMESTAMPTZ,
  processing_duration_ms INTEGER,

  -- Idempotency
  idempotency_key TEXT UNIQUE,

  -- Timestamps
  received_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes (following SOP 00 pattern)
CREATE INDEX idx_seal_webhook_inbox_status 
  ON raw_ops.seal_webhook_inbox(processing_status);
CREATE INDEX idx_seal_webhook_inbox_retry_queue 
  ON raw_ops.seal_webhook_inbox(received_at DESC)
  WHERE processing_status IN ('pending', 'failed');
CREATE INDEX idx_seal_webhook_inbox_topic 
  ON raw_ops.seal_webhook_inbox(webhook_topic);
CREATE INDEX idx_seal_webhook_inbox_subscription 
  ON raw_ops.seal_webhook_inbox(seal_subscription_id);

3.3 Configuration Table¶

CREATE TABLE raw_ops.subscription_config (
  config_key TEXT PRIMARY KEY,
  config_value TEXT NOT NULL,
  description TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by TEXT
);

-- Default configuration
INSERT INTO raw_ops.subscription_config (config_key, config_value, description) VALUES
  ('dunning_max_retries', '4', 'Max payment retry attempts before pausing subscription'),
  ('dunning_pause_after_days', '14', 'Days after first failure to pause subscription'),
  ('card_expiry_warning_days', '30', 'Days before card expiry to send warning'),
  ('card_expiry_final_warning_days', '7', 'Days before card expiry for final warning'),
  ('webhook_retry_max_attempts', '5', 'Max retry attempts for failed webhooks'),
  ('payment_recovery_email_day_0', 'true', 'Send email immediately on payment failure'),
  ('payment_recovery_email_day_3', 'true', 'Send reminder email 3 days after failure'),
  ('payment_recovery_email_day_7', 'true', 'Send urgent email 7 days after failure'),
  ('payment_recovery_email_day_10', 'true', 'Send final warning 10 days after failure');

3.4 Key Views¶

v_subscription_health¶

Dashboard overview of subscription health.

CREATE OR REPLACE VIEW analytics.v_subscription_health AS
SELECT
  -- Totals
  COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_subscriptions,
  COUNT(*) FILTER (WHERE status = 'PAUSED') AS paused_subscriptions,
  COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_subscriptions,

  -- Payment health
  COUNT(*) FILTER (WHERE payment_status = 'healthy') AS healthy_payments,
  COUNT(*) FILTER (WHERE payment_status = 'failing') AS failing_payments,
  COUNT(*) FILTER (WHERE payment_status = 'failed') AS failed_payments,

  -- Dunning funnel
  COUNT(*) FILTER (WHERE dunning_started_at IS NOT NULL 
    AND dunning_started_at > NOW() - INTERVAL '14 days') AS in_dunning,

  -- Card expiry risk
  COUNT(*) FILTER (WHERE status = 'ACTIVE' 
    AND (card_expiry_year * 12 + card_expiry_month) <= 
        (EXTRACT(YEAR FROM NOW()) * 12 + EXTRACT(MONTH FROM NOW()) + 1)
  ) AS cards_expiring_soon,

  -- Churn indicators
  COUNT(*) FILTER (WHERE status = 'PAUSED' 
    AND paused_at < NOW() - INTERVAL '30 days') AS long_paused,

  NOW() AS report_time
FROM raw_ops.subscriptions;

GRANT SELECT ON analytics.v_subscription_health TO analytics_user;

v_subscriptions_in_dunning¶

Active dunning cases for operations.

CREATE OR REPLACE VIEW raw_ops.v_subscriptions_in_dunning AS
SELECT
  s.id,
  s.seal_subscription_id,
  s.customer_id,
  c.email,
  c.first_name,
  c.last_name,
  s.status AS subscription_status,
  s.payment_status,
  s.failed_payment_count,
  s.last_failed_at,
  s.dunning_started_at,
  EXTRACT(DAY FROM NOW() - s.dunning_started_at)::INTEGER AS days_in_dunning,
  s.card_brand,
  s.card_last_digits,
  s.card_expiry_month,
  s.card_expiry_year,
  s.box_size,
  s.price_gbp,

  -- Last billing attempt details
  ba.error_code AS last_error_code,
  ba.error_message AS last_error_message,

  -- Dunning stage
  CASE
    WHEN EXTRACT(DAY FROM NOW() - s.dunning_started_at) < 3 THEN 'Day 0-2: Initial'
    WHEN EXTRACT(DAY FROM NOW() - s.dunning_started_at) < 7 THEN 'Day 3-6: Reminder'
    WHEN EXTRACT(DAY FROM NOW() - s.dunning_started_at) < 10 THEN 'Day 7-9: Urgent'
    ELSE 'Day 10+: Final Warning'
  END AS dunning_stage

FROM raw_ops.subscriptions s
JOIN raw_ops.customers c ON c.id = s.customer_id
LEFT JOIN LATERAL (
  SELECT * FROM raw_ops.billing_attempts
  WHERE subscription_id = s.id AND status = 'failed'
  ORDER BY attempted_at DESC
  LIMIT 1
) ba ON TRUE
WHERE s.payment_status IN ('failing', 'failed')
  AND s.status = 'ACTIVE'
ORDER BY s.dunning_started_at ASC;

v_subscription_monitoring_health (Phase 5)¶

Operational dashboard view for Metabase — real-time subscription system status.

CREATE OR REPLACE VIEW raw_ops.v_subscription_monitoring_health AS
SELECT
  -- Subscription state breakdown
  COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_subscriptions,
  COUNT(*) FILTER (WHERE status = 'PAUSED') AS paused_subscriptions,
  COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_subscriptions,

  -- MRR
  COALESCE(SUM(price_gbp) FILTER (WHERE status = 'ACTIVE'), 0) AS mrr_gbp,

  -- Payment health
  COUNT(*) FILTER (WHERE status = 'ACTIVE' AND payment_status = 'healthy') AS healthy_payments,
  COUNT(*) FILTER (WHERE status = 'ACTIVE' AND payment_status = 'failing') AS failing_payments,
  COUNT(*) FILTER (WHERE status = 'ACTIVE' AND payment_status = 'failed') AS failed_payments,

  -- Dunning pipeline
  COUNT(*) FILTER (WHERE payment_status = 'failing' AND dunning_email_day0_sent = TRUE AND dunning_email_day3_sent = FALSE) AS dunning_day0_to_3,
  COUNT(*) FILTER (WHERE payment_status = 'failing' AND dunning_email_day3_sent = TRUE AND dunning_email_day7_sent = FALSE) AS dunning_day3_to_7,
  COUNT(*) FILTER (WHERE payment_status = 'failing' AND dunning_email_day7_sent = TRUE AND dunning_email_day10_sent = FALSE) AS dunning_day7_to_10,
  COUNT(*) FILTER (WHERE payment_status IN ('failing', 'failed') AND dunning_started_at < NOW() - INTERVAL '10 days') AS dunning_day10_plus,

  -- Dunning MRR at risk
  COALESCE(SUM(price_gbp) FILTER (WHERE payment_status IN ('failing', 'failed') AND status = 'ACTIVE'), 0) AS dunning_mrr_at_risk,

  -- Webhook health
  (SELECT COUNT(*) FROM raw_ops.seal_webhook_inbox WHERE processing_status IN ('pending', 'processing')) AS webhook_backlog,
  (SELECT COUNT(*) FROM raw_ops.seal_webhook_inbox WHERE processing_status IN ('failed', 'dlq') AND received_at > NOW() - INTERVAL '1 hour') AS webhooks_failed_1h,
  (SELECT COUNT(*) FROM raw_ops.seal_webhook_inbox WHERE received_at > NOW() - INTERVAL '24 hours') AS webhooks_24h,

  NOW() AS checked_at
FROM raw_ops.subscriptions;

v_cards_expiring_soon¶

Cards that need proactive warnings.

CREATE OR REPLACE VIEW raw_ops.v_cards_expiring_soon AS
WITH config AS (
  SELECT 
    (SELECT config_value::INTEGER FROM raw_ops.subscription_config 
     WHERE config_key = 'card_expiry_warning_days') AS warning_days,
    (SELECT config_value::INTEGER FROM raw_ops.subscription_config 
     WHERE config_key = 'card_expiry_final_warning_days') AS final_warning_days
)
SELECT
  s.id,
  s.seal_subscription_id,
  s.customer_id,
  c.email,
  c.first_name,
  s.card_brand,
  s.card_last_digits,
  s.card_expiry_month,
  s.card_expiry_year,

  -- Calculate expiry date (last day of expiry month)
  (DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
   + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS card_expires_on,

  -- Days until expiry
  ((DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
    + INTERVAL '1 month' - INTERVAL '1 day')::DATE - CURRENT_DATE) AS days_until_expiry,

  -- Warning stage
  CASE
    WHEN ((DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
           + INTERVAL '1 month' - INTERVAL '1 day')::DATE - CURRENT_DATE) <= cfg.final_warning_days
    THEN 'final_warning'
    WHEN ((DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
           + INTERVAL '1 month' - INTERVAL '1 day')::DATE - CURRENT_DATE) <= cfg.warning_days
    THEN 'initial_warning'
    ELSE 'upcoming'
  END AS warning_stage,

  s.next_billing_date,
  s.box_size,
  s.price_gbp

FROM raw_ops.subscriptions s
JOIN raw_ops.customers c ON c.id = s.customer_id
CROSS JOIN config cfg
WHERE s.status = 'ACTIVE'
  AND s.card_expiry_year IS NOT NULL
  AND s.card_expiry_month IS NOT NULL
  -- Card expires within warning window
  AND ((DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
        + INTERVAL '1 month' - INTERVAL '1 day')::DATE - CURRENT_DATE) <= cfg.warning_days
  -- Card hasn't already expired
  AND ((DATE_TRUNC('month', MAKE_DATE(s.card_expiry_year, s.card_expiry_month, 1)) 
        + INTERVAL '1 month' - INTERVAL '1 day')::DATE) >= CURRENT_DATE
ORDER BY days_until_expiry ASC;

v_subscription_events_recent¶

Recent subscription events for support context.

CREATE OR REPLACE VIEW raw_ops.v_subscription_events_recent AS
SELECT
  se.id,
  se.subscription_id,
  se.customer_id,
  s.seal_subscription_id,
  c.email,
  se.event_type,
  se.event_source,
  se.old_status,
  se.new_status,
  se.event_at,
  se.created_at,
  -- Human-readable description
  CASE se.event_type
    WHEN 'subscription_created' THEN 'Subscription created'
    WHEN 'subscription_paused' THEN 'Subscription paused'
    WHEN 'subscription_resumed' THEN 'Subscription resumed'
    WHEN 'subscription_cancelled' THEN 'Subscription cancelled'
    WHEN 'billing_attempt_succeeded' THEN 'Payment successful'
    WHEN 'billing_attempt_failed' THEN 'Payment failed'
    WHEN 'payment_method_updated' THEN 'Payment method updated'
    WHEN 'shipping_address_updated' THEN 'Address updated'
    ELSE se.event_type
  END AS event_description
FROM raw_ops.subscription_events se
JOIN raw_ops.subscriptions s ON s.id = se.subscription_id
JOIN raw_ops.customers c ON c.id = se.customer_id
WHERE se.created_at > NOW() - INTERVAL '30 days'
ORDER BY se.created_at DESC;

3.5 RPC Functions¶

get_new_payment_failures¶

Called by SUB-01 (Dunning Day 0) every 15 minutes.

CREATE OR REPLACE FUNCTION raw_ops.get_new_payment_failures(minutes_ago INTEGER DEFAULT 15)
RETURNS TABLE (
  id UUID,
  seal_subscription_id BIGINT,
  customer_id UUID,
  email TEXT,
  first_name TEXT,
  failed_payment_count INTEGER,
  last_error_message TEXT,
  card_last_digits TEXT,
  box_size TEXT,
  price_gbp NUMERIC
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    s.id,
    s.seal_subscription_id,
    s.customer_id,
    c.email,
    c.first_name,
    s.failed_payment_count,
    ba.error_message AS last_error_message,
    s.card_last_digits,
    s.box_size,
    s.price_gbp
  FROM raw_ops.subscriptions s
  JOIN raw_ops.customers c ON c.id = s.customer_id
  LEFT JOIN LATERAL (
    SELECT error_message FROM raw_ops.billing_attempts
    WHERE subscription_id = s.id AND status = 'failed'
    ORDER BY attempted_at DESC LIMIT 1
  ) ba ON TRUE
  WHERE s.payment_status = 'failing'
    AND s.failed_payment_count = 1  -- Only first failure (Day 0)
    AND s.dunning_email_day0_sent = FALSE
    AND s.last_failed_at > NOW() - (minutes_ago || ' minutes')::INTERVAL
    AND s.status = 'ACTIVE';
END;
$$;

get_dunning_stage_customers¶

Called by SUB-02/03/04 for Day 3/7/10 emails.

CREATE OR REPLACE FUNCTION raw_ops.get_dunning_stage_customers(stage_day INTEGER)
RETURNS TABLE (
  id UUID,
  seal_subscription_id BIGINT,
  customer_id UUID,
  email TEXT,
  first_name TEXT,
  failed_payment_count INTEGER,
  dunning_started_at TIMESTAMPTZ,
  days_in_dunning INTEGER,
  card_last_digits TEXT,
  box_size TEXT,
  price_gbp NUMERIC
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    s.id,
    s.seal_subscription_id,
    s.customer_id,
    c.email,
    c.first_name,
    s.failed_payment_count,
    s.dunning_started_at,
    EXTRACT(DAY FROM NOW() - s.dunning_started_at)::INTEGER AS days_in_dunning,
    s.card_last_digits,
    s.box_size,
    s.price_gbp
  FROM raw_ops.subscriptions s
  JOIN raw_ops.customers c ON c.id = s.customer_id
  WHERE s.payment_status IN ('failing', 'failed')
    AND s.status = 'ACTIVE'
    AND EXTRACT(DAY FROM NOW() - s.dunning_started_at) = stage_day
    -- Check appropriate email flag hasn't been sent
    AND (
      (stage_day = 3 AND s.dunning_email_day3_sent = FALSE) OR
      (stage_day = 7 AND s.dunning_email_day7_sent = FALSE) OR
      (stage_day = 10 AND s.dunning_email_day10_sent = FALSE)
    );
END;
$$;

mark_dunning_email_sent¶

Called after each dunning email is sent.

CREATE OR REPLACE FUNCTION raw_ops.mark_dunning_email_sent(
  p_subscription_id UUID,
  p_email_stage TEXT
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  CASE p_email_stage
    WHEN 'day_0' THEN
      UPDATE raw_ops.subscriptions 
      SET dunning_email_day0_sent = TRUE, updated_at = NOW()
      WHERE id = p_subscription_id;
    WHEN 'day_3' THEN
      UPDATE raw_ops.subscriptions 
      SET dunning_email_day3_sent = TRUE, updated_at = NOW()
      WHERE id = p_subscription_id;
    WHEN 'day_7' THEN
      UPDATE raw_ops.subscriptions 
      SET dunning_email_day7_sent = TRUE, updated_at = NOW()
      WHERE id = p_subscription_id;
    WHEN 'day_10' THEN
      UPDATE raw_ops.subscriptions 
      SET dunning_email_day10_sent = TRUE, updated_at = NOW()
      WHERE id = p_subscription_id;
  END CASE;
END;
$$;

log_subscription_event¶

Helper function to log subscription events consistently.

CREATE OR REPLACE FUNCTION raw_ops.log_subscription_event(
  p_seal_subscription_id BIGINT,
  p_subscription_id UUID DEFAULT NULL,
  p_customer_id UUID DEFAULT NULL,
  p_event_type TEXT DEFAULT NULL,
  p_event_source TEXT DEFAULT 'system',
  p_old_status TEXT DEFAULT NULL,
  p_new_status TEXT DEFAULT NULL,
  p_payload JSONB DEFAULT NULL,
  p_event_at TIMESTAMPTZ DEFAULT NOW()
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_event_id UUID;
BEGIN
  INSERT INTO raw_ops.subscription_events (
    seal_subscription_id,
    subscription_id,
    customer_id,
    event_type,
    event_source,
    old_status,
    new_status,
    payload,
    event_at
  ) VALUES (
    p_seal_subscription_id,
    p_subscription_id,
    p_customer_id,
    p_event_type,
    p_event_source,
    p_old_status,
    p_new_status,
    p_payload,
    p_event_at
  )
  RETURNING id INTO v_event_id;

  RETURN v_event_id;
END;
$$;

4. Seal Webhook Registration¶

4.1 Registered Webhooks¶

Webhook ID	Topic	Status	Priority
102680	subscription/created	✅ Active	High
102681	subscription/updated	✅ Active	High
102682	subscription/paused	✅ Active	High
102683	subscription/resumed	✅ Active	High
(check Seal)	subscription/cancelled	✅ Active	Critical
(check Seal)	subscription/reactivated	✅ Active	High
(check Seal)	subscription/expired	✅ Active	Medium
(check Seal)	subscription/payment_method_updated	✅ Active	High
(check Seal)	billing_attempt/failed	✅ Active	Critical
(check Seal)	billing_attempt/succeeded	✅ Active	High

4.2 Webhook Endpoint¶

https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/seal-webhook-handler

4.3 Verification Commands¶

Get current webhooks:

curl -X GET \
  "https://app.sealsubscriptions.com/shopify/merchant/api/webhooks" \
  -H "X-Seal-Token: [SEAL_API_TOKEN]"

Register a new webhook:

curl -X POST \
  https://app.sealsubscriptions.com/shopify/merchant/webhooks \
  -H 'Content-Type: application/json' \
  -H 'X-Seal-Token: [SEAL_API_TOKEN]' \
  -d '{
    "topic": "billing_attempt/failed",
    "address": "https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/seal-webhook-handler"
  }'

4.4 HMAC Signature Verification¶

All Seal webhooks are signed with HMAC-SHA256. The signature is in the X-Seal-Hmac-Sha256 header.

import { createHmac } from 'node:crypto';

function verifyHmac(payload: string, signature: string, secret: string): boolean {
  if (!signature || !secret) return false;
  const computed = createHmac('sha256', secret)
    .update(payload)
    .digest('base64');
  return computed === signature;
}

5. Webhook Ingestion Edge Function¶

5.1 Deployed Function¶

Name: seal-webhook-handler
Endpoint: https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/seal-webhook-handler

Environment Variables:

Variable	Purpose	Location
`SEAL_API_SECRET`	HMAC signature verification	Supabase Secrets
`CUSTOMERIO_API_KEY`	Trigger dunning events	Supabase Secrets
`SUPABASE_URL`	Database connection	Auto-injected
`SUPABASE_SERVICE_ROLE_KEY`	Database auth	Auto-injected

5.2 Function Code¶

// supabase/functions/seal-webhook-handler/index.ts

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
import { createHmac } from 'node:crypto';

const SEAL_API_SECRET = Deno.env.get('SEAL_API_SECRET');
const SUPABASE_URL = Deno.env.get('SUPABASE_URL');
const SUPABASE_SERVICE_ROLE_KEY = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY');
const CUSTOMERIO_API_KEY = Deno.env.get('CUSTOMERIO_API_KEY');

serve(async (req) => {
  const startTime = Date.now();

  try {
    // 1. Get raw body and headers
    const rawBody = await req.text();
    const signature = req.headers.get('X-Seal-Hmac-Sha256');
    const headers = Object.fromEntries(req.headers.entries());

    // 2. Parse payload
    const payload = JSON.parse(rawBody);

    // 3. Extract webhook topic from payload structure
    const topic = determineWebhookTopic(payload);
    const sealSubscriptionId = payload.id || payload.subscription_id;

    // 4. Generate idempotency key
    const idempotencyKey = `${sealSubscriptionId}-${topic}-${payload.order_placed || payload.date || Date.now()}`;

    // 5. Create Supabase client
    const supabase = createClient(SUPABASE_URL!, SUPABASE_SERVICE_ROLE_KEY!);

    // 6. Check for duplicate (idempotency)
    const { data: existing } = await supabase
      .from('seal_webhook_inbox')
      .select('id')
      .eq('idempotency_key', idempotencyKey)
      .single();

    if (existing) {
      return new Response(JSON.stringify({ status: 'duplicate' }), {
        status: 200,
        headers: { 'Content-Type': 'application/json' }
      });
    }

    // 7. Verify HMAC signature
    const hmacValid = verifyHmac(rawBody, signature || '', SEAL_API_SECRET || '');

    // 8. Store in inbox
    const { data: webhook, error: insertError } = await supabase
      .from('seal_webhook_inbox')
      .insert({
        webhook_topic: topic,
        seal_subscription_id: sealSubscriptionId,
        payload: payload,
        headers: headers,
        hmac_signature: signature,
        hmac_valid: hmacValid,
        processing_status: 'pending',
        idempotency_key: idempotencyKey
      })
      .select()
      .single();

    if (insertError) throw insertError;

    // 9. Process webhook
    const processResult = await processWebhook(supabase, webhook.id, topic, payload);

    // 10. Update status
    const processingDuration = Date.now() - startTime;
    await supabase
      .from('seal_webhook_inbox')
      .update({
        processing_status: processResult.success ? 'success' : 'failed',
        processed_at: new Date().toISOString(),
        processing_duration_ms: processingDuration,
        last_error: processResult.error || null
      })
      .eq('id', webhook.id);

    return new Response(JSON.stringify({ status: 'ok' }), {
      status: 200,
      headers: { 'Content-Type': 'application/json' }
    });

  } catch (error) {
    console.error('Webhook processing error:', error);
    return new Response(JSON.stringify({ error: error.message }), {
      status: 200, // Always return 200 to prevent Seal retries
      headers: { 'Content-Type': 'application/json' }
    });
  }
});

function determineWebhookTopic(payload: any): string {
  // Billing attempts have specific structure
  if (payload.processed_billing_attempt) {
    return payload.processed_billing_attempt.status === 'completed' 
      ? 'billing_attempt/succeeded' 
      : 'billing_attempt/failed';
  }

  // Check for status to determine subscription event
  if (payload.status) {
    switch (payload.status) {
      case 'ACTIVE':
        return payload.paused_on ? 'subscription/resumed' : 'subscription/created';
      case 'PAUSED':
        return 'subscription/paused';
      case 'CANCELLED':
        return 'subscription/cancelled';
      case 'EXPIRED':
        return 'subscription/expired';
    }
  }

  return 'subscription/updated';
}

function verifyHmac(payload: string, signature: string, secret: string): boolean {
  if (!signature || !secret) return false;
  const computed = createHmac('sha256', secret)
    .update(payload)
    .digest('base64');
  return computed === signature;
}

async function processWebhook(
  supabase: any, 
  webhookId: string, 
  topic: string, 
  payload: any
): Promise<{ success: boolean; error?: string }> {
  try {
    // Route to appropriate handler
    switch (topic) {
      case 'subscription/created':
        await handleSubscriptionCreated(supabase, payload);
        break;
      case 'subscription/updated':
      case 'subscription/paused':
      case 'subscription/resumed':
      case 'subscription/reactivated':
      case 'subscription/cancelled':
      case 'subscription/expired':
        await handleSubscriptionStateChange(supabase, payload, topic);
        break;
      case 'subscription/payment_method_updated':
        await handlePaymentMethodUpdated(supabase, payload);
        break;
      case 'billing_attempt/succeeded':
        await handleBillingSuccess(supabase, payload);
        break;
      case 'billing_attempt/failed':
        await handleBillingFailed(supabase, payload);
        break;
      default:
        console.log(`Unhandled topic: ${topic}`);
    }

    return { success: true };
  } catch (error) {
    return { success: false, error: error.message };
  }
}

5.3 Webhook Handlers¶

handleSubscriptionCreated¶

async function handleSubscriptionCreated(supabase: any, payload: any) {
  // 1. Find or create customer link
  const customerId = await findOrLinkCustomer(supabase, payload);

  // 2. Insert/update subscription
  const { error } = await supabase
    .from('subscriptions')
    .upsert({
      seal_subscription_id: payload.id,
      seal_internal_id: payload.internal_id,
      shopify_customer_id: payload.customer_id,
      customer_id: customerId,
      status: payload.status,
      email: payload.email,
      delivery_interval: payload.delivery_interval,
      billing_interval: payload.billing_interval,
      box_size: determineBoxSize(payload),
      variant_id: payload.items?.[0]?.variant_id,
      price_gbp: payload.total_value,
      currency: payload.currency,
      card_brand: payload.card_brand,
      card_last_digits: payload.card_last_digits,
      card_expiry_month: parseInt(payload.card_expiry_month),
      card_expiry_year: parseInt(payload.card_expiry_year),
      subscription_created_at: payload.order_placed,
      next_billing_date: getNextBillingDate(payload),
      seal_payload: payload,
      last_synced_at: new Date().toISOString()
    }, {
      onConflict: 'seal_subscription_id'
    });

  if (error) throw error;

  // 3. Log event
  await supabase.rpc('log_subscription_event', {
    p_seal_subscription_id: payload.id,
    p_customer_id: customerId,
    p_event_type: 'subscription_created',
    p_event_source: 'seal_webhook',
    p_new_status: payload.status,
    p_payload: payload,
    p_event_at: payload.order_placed || new Date().toISOString()
  });
}

function determineBoxSize(payload: any): string {
  // Extract from variant title or line items
  const variantTitle = payload.items?.[0]?.variant_title || '';
  if (variantTitle.includes('16')) return '16';
  if (variantTitle.includes('12')) return '12';
  return '8'; // Default
}

function getNextBillingDate(payload: any): string | null {
  return payload.next_billing_date || payload.next_order_date || null;
}

handleBillingFailed¶

async function handleBillingFailed(supabase: any, payload: any) {
  const billingAttempt = payload.processed_billing_attempt;

  // 1. Get subscription
  const { data: subscription } = await supabase
    .from('subscriptions')
    .select('*')
    .eq('seal_subscription_id', payload.id)
    .single();

  if (!subscription) {
    // Subscription doesn't exist yet - create it first
    await handleSubscriptionCreated(supabase, payload);
    return handleBillingFailed(supabase, payload); // Retry
  }

  // 2. Record billing attempt
  await supabase
    .from('billing_attempts')
    .upsert({
      seal_subscription_id: payload.id,
      seal_billing_attempt_id: billingAttempt.id,
      subscription_id: subscription.id,
      customer_id: subscription.customer_id,
      scheduled_at: billingAttempt.date,
      attempted_at: billingAttempt.completed_at || new Date().toISOString(),
      status: 'failed',
      error_code: billingAttempt.error_code,
      error_message: billingAttempt.error_message,
      requires_authentication: !!billingAttempt.customer_authentication_challenge_url,
      authentication_url: billingAttempt.customer_authentication_challenge_url,
      amount_pence: Math.round(payload.total_value * 100),
      seal_payload: payload
    }, {
      onConflict: 'seal_billing_attempt_id'
    });

  // 3. Update subscription payment status
  const failedCount = (subscription.failed_payment_count || 0) + 1;
  const isFirstFailure = failedCount === 1;

  await supabase
    .from('subscriptions')
    .update({
      payment_status: 'failing',
      failed_payment_count: failedCount,
      last_failed_at: new Date().toISOString(),
      last_error_message: billingAttempt.error_message,
      dunning_started_at: isFirstFailure ? new Date().toISOString() : subscription.dunning_started_at,
      // Reset email flags on new failure cycle
      dunning_email_day0_sent: isFirstFailure ? false : subscription.dunning_email_day0_sent,
      dunning_email_day3_sent: isFirstFailure ? false : subscription.dunning_email_day3_sent,
      dunning_email_day7_sent: isFirstFailure ? false : subscription.dunning_email_day7_sent,
      dunning_email_day10_sent: isFirstFailure ? false : subscription.dunning_email_day10_sent,
      seal_payload: payload,
      last_synced_at: new Date().toISOString()
    })
    .eq('seal_subscription_id', payload.id);

  // 4. Log event
  await supabase.rpc('log_subscription_event', {
    p_seal_subscription_id: payload.id,
    p_subscription_id: subscription.id,
    p_customer_id: subscription.customer_id,
    p_event_type: 'billing_attempt_failed',
    p_event_source: 'seal_webhook',
    p_payload: {
      error_code: billingAttempt.error_code,
      error_message: billingAttempt.error_message,
      failed_count: failedCount,
      requires_authentication: !!billingAttempt.customer_authentication_challenge_url
    },
    p_event_at: billingAttempt.completed_at || new Date().toISOString()
  });
}

handleBillingSuccess¶

async function handleBillingSuccess(supabase: any, payload: any) {
  const billingAttempt = payload.processed_billing_attempt;

  // 1. Get subscription
  const { data: subscription } = await supabase
    .from('subscriptions')
    .select('*')
    .eq('seal_subscription_id', payload.id)
    .single();

  // 2. Record billing attempt
  await supabase
    .from('billing_attempts')
    .upsert({
      seal_subscription_id: payload.id,
      seal_billing_attempt_id: billingAttempt.id,
      subscription_id: subscription?.id,
      customer_id: subscription?.customer_id,
      scheduled_at: billingAttempt.date,
      attempted_at: billingAttempt.completed_at,
      completed_at: billingAttempt.completed_at,
      status: 'succeeded',
      shopify_order_id: billingAttempt.order_id,
      shopify_order_name: billingAttempt.order_name,
      amount_pence: Math.round(payload.total_value * 100),
      seal_payload: payload
    }, {
      onConflict: 'seal_billing_attempt_id'
    });

  // 3. Reset payment health if was failing
  const wasInDunning = subscription?.payment_status !== 'healthy';

  await supabase
    .from('subscriptions')
    .update({
      payment_status: 'healthy',
      failed_payment_count: 0,
      last_failed_at: null,
      last_error_message: null,
      dunning_started_at: null,
      dunning_email_day0_sent: false,
      dunning_email_day3_sent: false,
      dunning_email_day7_sent: false,
      dunning_email_day10_sent: false,
      next_billing_date: getNextBillingDate(payload),
      seal_payload: payload,
      last_synced_at: new Date().toISOString()
    })
    .eq('seal_subscription_id', payload.id);

  // 4. Log event
  await supabase.rpc('log_subscription_event', {
    p_seal_subscription_id: payload.id,
    p_subscription_id: subscription?.id,
    p_customer_id: subscription?.customer_id,
    p_event_type: 'billing_attempt_succeeded',
    p_event_source: 'seal_webhook',
    p_payload: {
      order_id: billingAttempt.order_id,
      order_name: billingAttempt.order_name,
      was_in_dunning: wasInDunning
    },
    p_event_at: billingAttempt.completed_at
  });

  // 5. If was in dunning, trigger recovery success email via Customer.io
  if (wasInDunning && subscription?.customer_id) {
    await triggerCustomerIoEvent(subscription.customer_id, 'payment_recovered', {
      order_name: billingAttempt.order_name
    });
  }
}

async function triggerCustomerIoEvent(customerId: string, eventName: string, data: any) {
  const CUSTOMERIO_API_KEY = Deno.env.get('CUSTOMERIO_API_KEY');

  await fetch(`https://track.customer.io/api/v1/customers/${customerId}/events`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${CUSTOMERIO_API_KEY}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      name: eventName,
      data: data
    })
  });
}

handleSubscriptionStateChange¶

async function handleSubscriptionStateChange(supabase: any, payload: any, topic: string) {
  const eventType = topic.replace('subscription/', 'subscription_');

  // 1. Get current subscription state
  const { data: subscription } = await supabase
    .from('subscriptions')
    .select('*')
    .eq('seal_subscription_id', payload.id)
    .single();

  const oldStatus = subscription?.status;
  const newStatus = payload.status;

  // 2. Update subscription
  const updateData: any = {
    status: newStatus,
    seal_payload: payload,
    last_synced_at: new Date().toISOString()
  };

  // Add specific fields based on event type
  if (topic === 'subscription/paused') {
    updateData.paused_at = payload.paused_on || new Date().toISOString();
    updateData.pause_reason = payload.pause_reason || 'unknown';
  } else if (topic === 'subscription/cancelled') {
    updateData.cancelled_at = payload.cancelled_at || new Date().toISOString();
  } else if (topic === 'subscription/resumed') {
    updateData.paused_at = null;
    updateData.pause_reason = null;
  }

  await supabase
    .from('subscriptions')
    .update(updateData)
    .eq('seal_subscription_id', payload.id);

  // 3. Log event
  await supabase.rpc('log_subscription_event', {
    p_seal_subscription_id: payload.id,
    p_subscription_id: subscription?.id,
    p_customer_id: subscription?.customer_id,
    p_event_type: eventType,
    p_event_source: 'seal_webhook',
    p_old_status: oldStatus,
    p_new_status: newStatus,
    p_payload: payload,
    p_event_at: new Date().toISOString()
  });
}

6. Payment Recovery (Dunning)¶

6.1 Dunning Philosophy¶

Goals: 1. Recover revenue without damaging customer relationship 2. Give customers multiple opportunities to fix payment 3. Clear, escalating communication 4. Automatic resolution path

6.2 Dunning Timeline¶

Day	Action	Tone	Make.com Scenario
0	Immediate notification + payment update link	Helpful	SUB-01 (every 15 min)
3	Reminder email	Gentle urgency	SUB-02 (daily 9am)
7	Warning: subscription at risk	Clear urgency	SUB-03 (daily 9am)
10	Final warning: will pause tomorrow	Final notice	SUB-04 (daily 9am)
11+	Auto-pause subscription	Automated	SUB-04 (includes pause)

6.3 Dunning Flow Diagram¶

Payment Fails (Seal)
       │
       ▼
[Webhook: billing_attempt/failed]
       │
       ▼
Edge Function updates:
  - payment_status = 'failing'
  - failed_payment_count++
  - dunning_started_at = NOW()
  - dunning_email_day0_sent = FALSE
       │
       ▼
[SUB-01 runs every 15 min]
  - Finds subscriptions with recent failures
  - Fetches edit_url from Seal for payment update link
  - Sends payment_failed event to Customer.io
  - Marks dunning_email_day0_sent = TRUE
       │
       ├── Day 3 ──► [SUB-02] → payment_failed_day3
       ├── Day 7 ──► [SUB-03] → payment_failed_day7
       └── Day 10 ─► [SUB-04] → payment_failed_day10
                           │
                           ▼
                    [Day 11+ Auto-pause via Seal]
                           │
                           ▼
                subscription_paused_payment event

6.4 Recovery Flow¶

Customer updates payment method
       │
       ▼
Seal retries billing → Success
       │
       ▼
[Webhook: billing_attempt/succeeded]
       │
       ▼
Edge Function:
  - payment_status = 'healthy'
  - failed_payment_count = 0
  - dunning_started_at = NULL
  - All dunning_email flags = FALSE
  - Triggers payment_recovered event
       │
       ▼
Customer receives "You're all set" email

6.5 Customer.io Email Templates¶

payment_failed (Day 0)¶

Event: payment_failed
Subject: Quick action needed: Your Protocol Raw payment didn't go through

Hi {{customer.first_name}},

We tried to process your Protocol Raw subscription payment, but it didn't go through.

**What happened:** {{event.error_message}}

**Your subscription:** {{event.box_size}} box ({{event.amount_formatted}})

No worries—this happens sometimes. You can update your payment method here:

[Update Payment Method →]

If you've recently updated your card, the new details should work on our next automatic retry.

Questions? Just reply to this email.

The Protocol Raw Team

payment_failed_day3¶

Event: payment_failed_day3
Subject: Reminder: Your Protocol Raw payment still needs attention

Hi {{customer.first_name}},

Just a quick reminder—we still haven't been able to process your subscription payment.

To keep your subscription active and ensure {{dog_name}} doesn't miss any meals, 
please update your payment method:

[Update Payment Method →]

The Protocol Raw Team

payment_failed_day7¶

Event: payment_failed_day7
Subject: Your Protocol Raw subscription needs attention

Hi {{customer.first_name}},

We've tried a few times to process your subscription payment without success.

**To keep your subscription active**, please update your payment method in the next few days:

[Update Payment Method →]

If we don't hear from you, we'll need to pause your subscription on {{calculated_pause_date}} 
to prevent further failed charges.

Your dogs won't miss a meal—once you update your payment, everything will continue seamlessly.

The Protocol Raw Team

payment_failed_day10¶

Event: payment_failed_day10
Subject: Final notice: Your Protocol Raw subscription will pause tomorrow

Hi {{customer.first_name}},

This is a final reminder that we haven't been able to process your subscription payment.

**Your subscription will be paused tomorrow** unless you update your payment method now:

[Update Payment Method →]

Once paused, you can reactivate at any time—just update your payment details and 
we'll pick up where we left off.

The Protocol Raw Team

payment_recovered¶

Event: payment_recovered
Subject: You're all set—payment successful

Hi {{customer.first_name}},

Great news! Your payment has been processed successfully and your subscription is back on track.

**Order confirmed:** {{event.order_name}}

No further action needed. {{dog_name}}'s next delivery will arrive as scheduled.

The Protocol Raw Team

7. Make.com Automation Scenarios¶

7.1 Scenario Overview¶

Scenario	Folder	Trigger	Purpose
SUB-01 Dunning Day 0	Subscription Management	Every 15 min	Immediate payment failure notification
SUB-02 Dunning Day 3	Subscription Management	Daily 9am	Send 3-day reminder
SUB-03 Dunning Day 7	Subscription Management	Daily 9am	Send urgent 7-day warning
SUB-04 Dunning Day 10	Subscription Management	Daily 9am	Send final warning
SUB-05 Card Expiry	Subscription Management	Daily 9am	Send card expiry warnings (â³ pending)

7.2 Scenario: SUB-01 Dunning Day 0¶

Name: [SOP-SUB-00](SOP-SUB-00_Subscription_State_Management_v3_0_FINAL.md) Dunning Day 0 - Immediate Notification
Trigger: Schedule - Every 15 minutes
Status: ✅ Deployed

Module Flow:

1. Schedule (every 15 min)
       │
       ▼
2. HTTP: Call get_new_payment_failures RPC
       │
       ▼
3. Iterator (loop through results)
       │
       ▼
4. HTTP: Seal API - Get subscription edit_url
       │
       ▼
5. HTTP: Customer.io - Trigger payment_failed event
       │
       ▼
6. HTTP: Call mark_dunning_email_sent RPC (day_0)

Module 2 - Get New Payment Failures:

URL: https://znfjpllsiuyezqlneqzr.supabase.co/rest/v1/rpc/get_new_payment_failures
Method: POST
Headers:
  - apikey: [SUPABASE_ANON_KEY]
  - Authorization: Bearer [SUPABASE_ANON_KEY]
  - Content-Type: application/json
  - Content-Profile: raw_ops
Body: {"minutes_ago": 15}

Module 4 - Get Seal Edit URL:

URL: https://app.sealsubscriptions.com/shopify/merchant/subscription/{{3.seal_subscription_id}}
Method: GET
Headers:
  - X-Seal-Token: [SEAL_API_TOKEN]

Module 5 - Customer.io Event:

URL: https://track.customer.io/api/v1/customers/{{3.customer_id}}/events
Method: POST
Headers:
  - Authorization: Bearer [CUSTOMERIO_API_KEY]
  - Content-Type: application/json
Body:
{
  "name": "payment_failed",
  "data": {
    "failed_count": {{3.failed_payment_count}},
    "error_message": "{{3.last_error_message}}",
    "card_last_digits": "{{3.card_last_digits}}",
    "box_size": "{{3.box_size}}",
    "amount_formatted": "£{{3.price_gbp}}",
    "payment_update_url": "{{4.body.edit_url}}"
  }
}

Module 6 - Mark Email Sent:

URL: https://znfjpllsiuyezqlneqzr.supabase.co/rest/v1/rpc/mark_dunning_email_sent
Method: POST
Headers:
  - apikey: [SUPABASE_ANON_KEY]
  - Content-Type: application/json
  - Content-Profile: raw_ops
Body:
{
  "p_subscription_id": "{{3.id}}",
  "p_email_stage": "day_0"
}

7.3 Scenario: SUB-02/03/04 Dunning Day 3/7/10¶

Pattern: All three scenarios follow the same pattern, differing only in: - stage_day parameter (3, 7, or 10) - Customer.io event name (payment_failed_day3, payment_failed_day7, payment_failed_day10) - Email stage for marking (day_3, day_7, day_10)

Module 2 - Get Dunning Stage Customers:

URL: https://znfjpllsiuyezqlneqzr.supabase.co/rest/v1/rpc/get_dunning_stage_customers
Method: POST
Body: {"stage_day": 3}  // or 7 or 10

8. Proactive Card Expiry Warnings¶

Status: â³ Phase 3 - Pending Implementation

8.1 Warning Timeline¶

Days Before Expiry	Action
30	Initial warning email
7	Final warning email
0	Card expired (will fail on next billing)

8.2 Make.com Scenario: SUB-05 Card Expiry¶

Name: [SOP-SUB-00](SOP-SUB-00_Subscription_State_Management_v3_0_FINAL.md) Card Expiry Warnings
Trigger: Schedule - Daily at 9am

Module Flow:

1. Schedule (daily 9am)
       │
       ▼
2. HTTP: Query v_cards_expiring_soon view
       │
       ▼
3. Router (by warning_stage)
       │
       ├── initial_warning → Customer.io: card_expiring_soon
       │
       └── final_warning → Customer.io: card_expiring_final_warning
       │
       ▼
4. HTTP: Seal - Send payment method update email

8.3 Customer.io Templates (Pending)¶

card_expiring_soon¶

Subject: Your card ending in {{event.card_last_digits}} expires soon

Hi {{customer.first_name}},

Just a heads up—the card we have on file for your Protocol Raw subscription 
({{event.card_brand}} ending in {{event.card_last_digits}}) expires on {{event.expires_on}}.

To ensure your subscription continues without interruption, please update 
your payment method before then:

[Update Payment Method →]

Thanks for being a Protocol Raw customer.

The Protocol Raw Team

card_expiring_final_warning¶

Subject: Urgent: Your card expires in {{event.days_until_expiry}} days

Hi {{customer.first_name}},

Your {{event.card_brand}} card ending in {{event.card_last_digits}} expires very soon 
({{event.expires_on}}).

Please update your payment method now to avoid any interruption to your subscription:

[Update Payment Method →]

The Protocol Raw Team

9. Portal Integration¶

Status: â³ Phase 4 - Pending Implementation

When a customer's subscription has payment_status != 'healthy', display a prominent banner:

⚠ï¸ Payment Issue

We couldn't process your last payment. Please update your payment method to keep your subscription active.

[Update Payment Method]

9.2 Customer Portal: get-payment-update-url Edge Function¶

Endpoint: /functions/v1/get-payment-update-url

Returns the Seal edit_url for the customer to update their payment method.

// supabase/functions/get-payment-update-url/index.ts

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const SEAL_API_TOKEN = Deno.env.get('SEAL_API_TOKEN');

serve(async (req) => {
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  );

  // Get customer_id from auth
  const authHeader = req.headers.get('Authorization');
  const { data: { user } } = await supabase.auth.getUser(authHeader?.replace('Bearer ', ''));

  if (!user) {
    return new Response(JSON.stringify({ error: 'Unauthorized' }), { status: 401 });
  }

  // Get subscription
  const { data: subscription } = await supabase
    .from('subscriptions')
    .select('seal_subscription_id')
    .eq('customer_id', user.id)
    .single();

  if (!subscription) {
    return new Response(JSON.stringify({ error: 'No subscription found' }), { status: 404 });
  }

  // Get edit_url from Seal
  const sealResponse = await fetch(
    `https://app.sealsubscriptions.com/shopify/merchant/subscription/${subscription.seal_subscription_id}`,
    {
      headers: { 'X-Seal-Token': SEAL_API_TOKEN! }
    }
  );

  const sealData = await sealResponse.json();

  return new Response(JSON.stringify({ 
    edit_url: sealData.edit_url 
  }), {
    headers: { 'Content-Type': 'application/json' }
  });
});

9.3 Ops Portal: Dunning Queue View¶

Add to Support Workstation a "Dunning Queue" tab showing all subscriptions in dunning:

Query: SELECT * FROM raw_ops.v_subscriptions_in_dunning

Columns: - Customer name/email - Days in dunning - Dunning stage (Day 0-2, Day 3-6, Day 7-9, Day 10+) - Last error message - MRR at risk (price_gbp) - Card details (last 4, brand, expiry) - Actions: View in Seal, Manual outreach

10. Analytics & Reporting¶

10.1 Metabase Dashboard: Subscription Health¶

Status: â³ Pending Implementation

Metrics to Display:

Active Subscriptions - Total count and trend
Payment Health Distribution - Healthy / Failing / Failed
Dunning Funnel - Count at each stage (Day 0-2, 3-6, 7-9, 10+)
MRR at Risk - Sum of price_gbp for subscriptions in dunning
Recovery Rate - % of dunning subscriptions recovered (7-day rolling)
Cards Expiring Soon - Count expiring in next 30 days

10.2 Key Analytics Views¶

v_dunning_recovery_rate¶

CREATE OR REPLACE VIEW analytics.v_dunning_recovery_rate AS
WITH failures AS (
  SELECT 
    DATE_TRUNC('week', event_at) AS week,
    COUNT(DISTINCT subscription_id) AS started_dunning
  FROM raw_ops.subscription_events
  WHERE event_type = 'billing_attempt_failed'
  GROUP BY DATE_TRUNC('week', event_at)
),
recoveries AS (
  SELECT
    DATE_TRUNC('week', event_at) AS week,
    COUNT(DISTINCT subscription_id) AS recovered
  FROM raw_ops.subscription_events
  WHERE event_type = 'billing_attempt_succeeded'
    AND payload->>'was_in_dunning' = 'true'
  GROUP BY DATE_TRUNC('week', event_at)
)
SELECT
  f.week,
  f.started_dunning,
  COALESCE(r.recovered, 0) AS recovered,
  ROUND(COALESCE(r.recovered, 0)::NUMERIC / NULLIF(f.started_dunning, 0) * 100, 1) AS recovery_rate_pct
FROM failures f
LEFT JOIN recoveries r ON r.week = f.week
ORDER BY f.week DESC;

11. Monitoring & Alerting¶

Status: ✅ Phase 5 — COMPLETE (deployed 2026-02-03)

11.1 Architecture¶

Follows the SOP-MON-01 pattern — fully native Supabase, no Make.com dependency:

pg_cron (scheduled jobs)
    ↓
pg_net (async HTTP)
    ↓
run-monitor (Edge Function) — dispatches to correct PostgreSQL function
    ↓
fn_check_subscription_health_v2() / fn_check_subscription_daily_v2()
    ↓
monitoring_runs (logging table)
    ↓
ops-alerter (Edge Function) [if threshold breached / always for daily]
    ↓
Slack (#ops-alerts / #ops-urgent / #daily-ops)

11.2 Monitor Summary¶

Monitor	Check Name	Function	Schedule	Channel
Subscription Health	`subscription_health`	`fn_check_subscription_health_v2()`	Every 5 min (`/5 * * *`)	#ops-alerts / #ops-urgent
Subscription Daily	`subscription_daily`	`fn_check_subscription_daily_v2()`	Daily 09:05 UTC (`5 9 * * *`)	#daily-ops

11.3 Monitor 1: Subscription Health¶

Purpose: Detects three categories of subscription infrastructure failure:

Webhook Inbox Backlog — Seal webhooks piling up unprocessed
Dunning Pipeline Stalls — Subscriptions stuck in dunning stages without emails being sent
Failed Webhook Processing — Webhooks that have exhausted retries

Thresholds:

Check	Condition	Severity	Channel
Webhook backlog	>10 pending AND oldest >15 min	warning	#ops-alerts
Webhook backlog	>20 pending AND oldest >30 min	critical	#ops-urgent
Failed webhooks	Any in last 1 hour	warning	#ops-alerts
Failed webhooks	>5 in last 1 hour	critical	#ops-urgent
Dunning stall: Day 0	Failing subscription >30 min without day0 email	warning	#ops-alerts
Dunning stall: Day 0	Failing subscription >2 hours without day0 email	critical	#ops-urgent
Dunning stall: Day 3+	Subscription past stage threshold without email sent	warning	#ops-alerts
Critical dunning	Any subscription at Day 10+ (auto-pause imminent)	critical	#ops-urgent

Alert Fields: - 📨 Webhook Backlog (count + oldest age in minutes) - âŒ Failed Webhooks 1h (count) - â³ Dunning Stalls (count by stage) - 🚨 Day 10+ Critical (count + MRR at risk) - 💰 Total MRR in Dunning (£)

Function:

CREATE OR REPLACE FUNCTION raw_ops.fn_check_subscription_health_v2()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_run_id uuid;
  v_start_time timestamptz;
  v_duration_ms integer;
  v_alert_needed boolean := false;
  v_severity text := 'warning';
  v_channel text := 'ops-alerts';

  -- Webhook backlog metrics
  v_pending_webhooks integer;
  v_oldest_pending_minutes numeric;
  v_failed_webhooks_1h integer;

  -- Dunning stall metrics
  v_stalled_day0 integer;
  v_stalled_day3 integer;
  v_stalled_day7 integer;
  v_day10_critical integer;
  v_day10_mrr_at_risk numeric;
  v_total_dunning_mrr numeric;

  -- Alert message parts
  v_fields jsonb := '[]'::jsonb;
  v_messages text[] := '{}';
BEGIN
  v_start_time := clock_timestamp();

  INSERT INTO raw_ops.monitoring_runs (check_name, status)
  VALUES ('subscription_health', 'running')
  RETURNING id INTO v_run_id;

  -- CHECK 1: Webhook Inbox Backlog
  SELECT 
    COUNT(*),
    COALESCE(EXTRACT(EPOCH FROM (NOW() - MIN(received_at))) / 60, 0)
  INTO v_pending_webhooks, v_oldest_pending_minutes
  FROM raw_ops.seal_webhook_inbox
  WHERE processing_status IN ('pending', 'processing');

  SELECT COUNT(*)
  INTO v_failed_webhooks_1h
  FROM raw_ops.seal_webhook_inbox
  WHERE processing_status IN ('failed', 'dlq')
    AND received_at > NOW() - INTERVAL '1 hour';

  IF v_pending_webhooks > 20 AND v_oldest_pending_minutes > 30 THEN
    v_alert_needed := true;
    v_severity := 'critical';
    v_channel := 'ops-urgent';
    v_messages := array_append(v_messages, 
      format('Webhook backlog critical: %s pending, oldest %s min ago', 
        v_pending_webhooks, ROUND(v_oldest_pending_minutes, 1)));
  ELSIF v_pending_webhooks > 10 AND v_oldest_pending_minutes > 15 THEN
    v_alert_needed := true;
    v_messages := array_append(v_messages, 
      format('Webhook backlog warning: %s pending, oldest %s min ago', 
        v_pending_webhooks, ROUND(v_oldest_pending_minutes, 1)));
  END IF;

  IF v_failed_webhooks_1h > 5 THEN
    v_alert_needed := true;
    v_severity := 'critical';
    v_channel := 'ops-urgent';
    v_messages := array_append(v_messages, 
      format('Failed webhooks critical: %s in last hour', v_failed_webhooks_1h));
  ELSIF v_failed_webhooks_1h > 0 THEN
    v_alert_needed := true;
    v_messages := array_append(v_messages, 
      format('Failed webhooks: %s in last hour', v_failed_webhooks_1h));
  END IF;

  -- CHECK 2: Dunning Pipeline Stalls
  SELECT COUNT(*)
  INTO v_stalled_day0
  FROM raw_ops.subscriptions
  WHERE payment_status = 'failing'
    AND dunning_email_day0_sent = FALSE
    AND last_failed_at < NOW() - INTERVAL '30 minutes'
    AND status = 'ACTIVE';

  SELECT COUNT(*)
  INTO v_stalled_day3
  FROM raw_ops.subscriptions
  WHERE payment_status = 'failing'
    AND dunning_email_day3_sent = FALSE
    AND dunning_started_at < NOW() - INTERVAL '3 days'
    AND dunning_email_day0_sent = TRUE
    AND status = 'ACTIVE';

  SELECT COUNT(*)
  INTO v_stalled_day7
  FROM raw_ops.subscriptions
  WHERE payment_status = 'failing'
    AND dunning_email_day7_sent = FALSE
    AND dunning_started_at < NOW() - INTERVAL '7 days'
    AND dunning_email_day3_sent = TRUE
    AND status = 'ACTIVE';

  SELECT COUNT(*), COALESCE(SUM(price_gbp), 0)
  INTO v_day10_critical, v_day10_mrr_at_risk
  FROM raw_ops.subscriptions
  WHERE payment_status IN ('failing', 'failed')
    AND dunning_started_at < NOW() - INTERVAL '10 days'
    AND status = 'ACTIVE';

  SELECT COALESCE(SUM(price_gbp), 0)
  INTO v_total_dunning_mrr
  FROM raw_ops.subscriptions
  WHERE payment_status IN ('failing', 'failed')
    AND status = 'ACTIVE';

  IF EXISTS (
    SELECT 1 FROM raw_ops.subscriptions
    WHERE payment_status = 'failing'
      AND dunning_email_day0_sent = FALSE
      AND last_failed_at < NOW() - INTERVAL '2 hours'
      AND status = 'ACTIVE'
  ) THEN
    v_alert_needed := true;
    v_severity := 'critical';
    v_channel := 'ops-urgent';
    v_messages := array_append(v_messages, 
      format('CRITICAL: %s subscriptions failing >2h without Day 0 email (SUB-01 scenario may be down)', v_stalled_day0));
  ELSIF v_stalled_day0 > 0 THEN
    v_alert_needed := true;
    v_messages := array_append(v_messages, 
      format('Dunning stall: %s subscriptions >30min without Day 0 email', v_stalled_day0));
  END IF;

  IF v_stalled_day3 > 0 OR v_stalled_day7 > 0 THEN
    v_alert_needed := true;
    v_messages := array_append(v_messages, 
      format('Dunning stalls: Day 3=%s, Day 7=%s', v_stalled_day3, v_stalled_day7));
  END IF;

  IF v_day10_critical > 0 THEN
    v_alert_needed := true;
    v_severity := 'critical';
    v_channel := 'ops-urgent';
    v_messages := array_append(v_messages, 
      format('CRITICAL: %s subscriptions at Day 10+ dunning, £%s MRR at risk of auto-pause', 
        v_day10_critical, ROUND(v_day10_mrr_at_risk, 2)));
  END IF;

  -- BUILD ALERT FIELDS
  v_fields := jsonb_build_array(
    jsonb_build_object('label', '📨 Webhook Backlog', 'value', 
      format('%s pending (%s min oldest)', v_pending_webhooks, ROUND(COALESCE(v_oldest_pending_minutes, 0), 1)), 'inline', true),
    jsonb_build_object('label', 'âŒ Failed Webhooks 1h', 'value', 
      v_failed_webhooks_1h::text, 'inline', true),
    jsonb_build_object('label', 'â³ Dunning Stalls', 'value', 
      format('D0=%s D3=%s D7=%s', v_stalled_day0, v_stalled_day3, v_stalled_day7), 'inline', true),
    jsonb_build_object('label', '🚨 Day 10+ Critical', 'value', 
      format('%s (£%s at risk)', v_day10_critical, ROUND(COALESCE(v_day10_mrr_at_risk, 0), 2)), 'inline', true),
    jsonb_build_object('label', '💰 Total Dunning MRR', 'value', 
      format('£%s', ROUND(COALESCE(v_total_dunning_mrr, 0), 2)), 'inline', true)
  );

  -- LOG AND RETURN
  v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

  UPDATE raw_ops.monitoring_runs
  SET 
    status = 'success',
    duration_ms = v_duration_ms,
    alert_triggered = v_alert_needed,
    result_summary = CASE 
      WHEN v_alert_needed THEN array_to_string(v_messages, '; ')
      ELSE format('Healthy — %s pending webhooks, %s failed 1h, %s in dunning', 
        v_pending_webhooks, v_failed_webhooks_1h, 
        (v_stalled_day0 + v_stalled_day3 + v_stalled_day7 + v_day10_critical))
    END,
    result_json = jsonb_build_object(
      'pending_webhooks', v_pending_webhooks,
      'oldest_pending_minutes', ROUND(COALESCE(v_oldest_pending_minutes, 0), 1),
      'failed_webhooks_1h', v_failed_webhooks_1h,
      'stalled_day0', v_stalled_day0,
      'stalled_day3', v_stalled_day3,
      'stalled_day7', v_stalled_day7,
      'day10_critical', v_day10_critical,
      'day10_mrr_at_risk', ROUND(COALESCE(v_day10_mrr_at_risk, 0), 2),
      'total_dunning_mrr', ROUND(COALESCE(v_total_dunning_mrr, 0), 2)
    )
  WHERE id = v_run_id;

  RETURN jsonb_build_object(
    'success', true,
    'run_id', v_run_id,
    'check_name', 'subscription_health',
    'duration_ms', v_duration_ms,
    'alert_config', CASE 
      WHEN NOT v_alert_needed THEN NULL
      ELSE jsonb_build_object(
        'channel', v_channel,
        'severity', v_severity,
        'title', 'Subscription Health Alert',
        'message', array_to_string(v_messages, E'\n'),
        'fields', v_fields,
        'source_check', 'subscription_health'
      )
    END
  );

EXCEPTION WHEN OTHERS THEN
  v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

  UPDATE raw_ops.monitoring_runs
  SET status = 'error', duration_ms = v_duration_ms, error_message = SQLERRM
  WHERE id = v_run_id;

  RETURN jsonb_build_object(
    'success', false,
    'run_id', v_run_id,
    'check_name', 'subscription_health',
    'error', SQLERRM,
    'duration_ms', v_duration_ms
  );
END;
$$;

11.4 Monitor 2: Subscription Daily Digest¶

Purpose: Daily subscription metrics summary sent to #daily-ops at 09:05 UTC.

Health Indicators: - 🔴 Red: Any subscription at Day 10+ dunning OR failed webhooks in last 24h - 🟡 Yellow: >3 subscriptions currently in dunning OR any cancellations in 24h - 🟢 Green: All systems healthy

Alert Fields: - 📊 Active Subscriptions - 💰 MRR - ➕ New 24h - âŒ Cancelled 24h - â¸ï¸ Paused 24h - 🔄 Recovered 24h - ⚠ï¸ In Dunning - 📨 Webhooks 24h

Function:

CREATE OR REPLACE FUNCTION raw_ops.fn_check_subscription_daily_v2()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_run_id uuid;
  v_start_time timestamptz;
  v_duration_ms integer;

  v_active_count integer;
  v_mrr_gbp numeric;
  v_new_24h integer;
  v_cancelled_24h integer;
  v_paused_24h integer;
  v_recovered_24h integer;
  v_in_dunning integer;
  v_dunning_mrr numeric;
  v_day10_count integer;

  v_webhooks_24h integer;
  v_webhooks_failed_24h integer;

  v_health_emoji text;
  v_health_status text;
BEGIN
  v_start_time := clock_timestamp();

  INSERT INTO raw_ops.monitoring_runs (check_name, status)
  VALUES ('subscription_daily', 'running')
  RETURNING id INTO v_run_id;

  -- Active subscriptions and MRR
  SELECT COUNT(*), COALESCE(SUM(price_gbp), 0)
  INTO v_active_count, v_mrr_gbp
  FROM raw_ops.subscriptions
  WHERE status = 'ACTIVE';

  -- New subscriptions in last 24h
  SELECT COUNT(*)
  INTO v_new_24h
  FROM raw_ops.subscription_events
  WHERE event_type = 'subscription_created'
    AND created_at > NOW() - INTERVAL '24 hours';

  -- Cancelled in last 24h
  SELECT COUNT(*)
  INTO v_cancelled_24h
  FROM raw_ops.subscription_events
  WHERE event_type = 'subscription_cancelled'
    AND created_at > NOW() - INTERVAL '24 hours';

  -- Paused in last 24h
  SELECT COUNT(*)
  INTO v_paused_24h
  FROM raw_ops.subscription_events
  WHERE event_type = 'subscription_paused'
    AND created_at > NOW() - INTERVAL '24 hours';

  -- Recovered from dunning in last 24h
  SELECT COUNT(*)
  INTO v_recovered_24h
  FROM raw_ops.subscription_events
  WHERE event_type = 'billing_attempt_succeeded'
    AND created_at > NOW() - INTERVAL '24 hours'
    AND payload->>'was_in_dunning' = 'true';

  -- Currently in dunning
  SELECT COUNT(*), COALESCE(SUM(price_gbp), 0)
  INTO v_in_dunning, v_dunning_mrr
  FROM raw_ops.subscriptions
  WHERE payment_status IN ('failing', 'failed')
    AND status = 'ACTIVE';

  -- Day 10+ critical
  SELECT COUNT(*)
  INTO v_day10_count
  FROM raw_ops.subscriptions
  WHERE payment_status IN ('failing', 'failed')
    AND dunning_started_at < NOW() - INTERVAL '10 days'
    AND status = 'ACTIVE';

  -- Webhook metrics
  SELECT 
    COUNT(*),
    COUNT(*) FILTER (WHERE processing_status IN ('failed', 'dlq'))
  INTO v_webhooks_24h, v_webhooks_failed_24h
  FROM raw_ops.seal_webhook_inbox
  WHERE received_at > NOW() - INTERVAL '24 hours';

  -- Health indicator
  IF v_day10_count > 0 OR v_webhooks_failed_24h > 0 THEN
    v_health_emoji := '🔴';
    v_health_status := 'Needs Attention';
  ELSIF v_in_dunning > 3 OR v_cancelled_24h > 0 THEN
    v_health_emoji := '🟡';
    v_health_status := 'Monitor';
  ELSE
    v_health_emoji := '🟢';
    v_health_status := 'Healthy';
  END IF;

  -- LOG AND RETURN
  v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

  UPDATE raw_ops.monitoring_runs
  SET 
    status = 'success',
    duration_ms = v_duration_ms,
    alert_triggered = true,
    result_summary = format('%s Subscriptions: %s active, £%s MRR, +%s/-%s 24h, %s in dunning',
      v_health_emoji, v_active_count, ROUND(v_mrr_gbp, 2), v_new_24h, v_cancelled_24h, v_in_dunning),
    result_json = jsonb_build_object(
      'active_count', v_active_count,
      'mrr_gbp', ROUND(v_mrr_gbp, 2),
      'new_24h', v_new_24h,
      'cancelled_24h', v_cancelled_24h,
      'paused_24h', v_paused_24h,
      'recovered_24h', v_recovered_24h,
      'in_dunning', v_in_dunning,
      'dunning_mrr', ROUND(v_dunning_mrr, 2),
      'day10_count', v_day10_count,
      'webhooks_24h', v_webhooks_24h,
      'webhooks_failed_24h', v_webhooks_failed_24h,
      'health_status', v_health_status
    )
  WHERE id = v_run_id;

  RETURN jsonb_build_object(
    'success', true,
    'run_id', v_run_id,
    'check_name', 'subscription_daily',
    'duration_ms', v_duration_ms,
    'alert_config', jsonb_build_object(
      'channel', 'daily-ops',
      'severity', 'info',
      'title', format('%s Subscription Daily Digest', v_health_emoji),
      'message', format('Subscription system health: %s', v_health_status),
      'fields', jsonb_build_array(
        jsonb_build_object('label', '📊 Active Subscriptions', 'value', v_active_count::text, 'inline', true),
        jsonb_build_object('label', '💰 MRR', 'value', format('£%s', ROUND(v_mrr_gbp, 2)), 'inline', true),
        jsonb_build_object('label', '➕ New 24h', 'value', v_new_24h::text, 'inline', true),
        jsonb_build_object('label', 'âŒ Cancelled 24h', 'value', v_cancelled_24h::text, 'inline', true),
        jsonb_build_object('label', 'â¸ï¸ Paused 24h', 'value', v_paused_24h::text, 'inline', true),
        jsonb_build_object('label', '🔄 Recovered 24h', 'value', v_recovered_24h::text, 'inline', true),
        jsonb_build_object('label', '⚠ï¸ In Dunning', 'value', 
          format('%s (£%s MRR)', v_in_dunning, ROUND(v_dunning_mrr, 2)), 'inline', true),
        jsonb_build_object('label', '📨 Webhooks 24h', 'value', 
          format('%s (%s failed)', v_webhooks_24h, v_webhooks_failed_24h), 'inline', true)
      ),
      'source_check', 'subscription_daily'
    )
  );

EXCEPTION WHEN OTHERS THEN
  v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

  UPDATE raw_ops.monitoring_runs
  SET status = 'error', duration_ms = v_duration_ms, error_message = SQLERRM
  WHERE id = v_run_id;

  RETURN jsonb_build_object(
    'success', false,
    'run_id', v_run_id,
    'check_name', 'subscription_daily',
    'error', SQLERRM,
    'duration_ms', v_duration_ms
  );
END;
$$;

11.5 pg_cron Jobs¶

-- Subscription Health (every 5 minutes)
SELECT cron.schedule(
  'monitor-subscription-health',
  '*/5 * * * *',
  $$
  SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/run-monitor',
    headers := '{"Content-Type": "application/json", "apikey": "YOUR_ANON_KEY"}'::jsonb,
    body := '{"check": "subscription_health"}'::jsonb
  );
  $$
);

-- Subscription Daily Digest (09:05 UTC daily)
SELECT cron.schedule(
  'monitor-subscription-daily',
  '5 9 * * *',
  $$
  SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/run-monitor',
    headers := '{"Content-Type": "application/json", "apikey": "YOUR_ANON_KEY"}'::jsonb,
    body := '{"check": "subscription_daily"}'::jsonb
  );
  $$
);

-- Verify jobs
SELECT jobid, jobname, schedule, active
FROM cron.job 
WHERE jobname LIKE 'monitor-subscription%'
ORDER BY jobname;

11.6 Edge Function: run-monitor Update¶

The MONITOR_FUNCTIONS map in run-monitor/index.ts includes:

const MONITOR_FUNCTIONS: Record<string, string> = {
  // Existing monitors
  order_ingestion_slo: "fn_check_order_ingestion_slo_v2",
  outbox_health: "fn_check_outbox_health_v2",
  stock_levels: "fn_check_stock_levels_v2",
  lab_sla: "fn_check_lab_sla_v2",
  daily_snapshot: "fn_daily_snapshot_v2",
  working_capital: "fn_check_working_capital_v2",
  refund_health: "fn_check_refund_health_v2",

  // Subscription monitors (Phase 5)
  subscription_health: "fn_check_subscription_health_v2",
  subscription_daily: "fn_check_subscription_daily_v2",
};

11.7 Alert Response Runbook¶

Webhook Backlog Alert¶

Severity: warning (>10 pending, >15 min) / critical (>20 pending, >30 min)

Immediate Actions: 1. Check Edge Function logs: Supabase Dashboard → Edge Functions → seal-webhook-handler → Logs 2. Check for recent deploys that may have broken the handler 3. Verify Seal API Secret hasn't rotated: supabase secrets list 4. Check if a burst of webhooks overwhelmed processing

Manual Recovery:

-- Check backlog detail
SELECT webhook_topic, processing_status, COUNT(*), 
  MIN(received_at) as oldest, MAX(received_at) as newest
FROM raw_ops.seal_webhook_inbox
WHERE processing_status IN ('pending', 'processing', 'failed')
GROUP BY webhook_topic, processing_status;

-- Retry failed webhooks (reset to pending for reprocessing)
UPDATE raw_ops.seal_webhook_inbox
SET processing_status = 'pending', retry_count = retry_count + 1
WHERE processing_status = 'failed'
  AND retry_count < 5;

Root Causes: - seal-webhook-handler Edge Function crashed or undeployed - HMAC validation failing (Seal rotated API secret) - Database connection pool exhausted - Burst of subscription changes overwhelming handler

Dunning Pipeline Stall Alert¶

Severity: warning (Day 0 >30 min) / critical (Day 0 >2h)

Immediate Actions: 1. Check Make.com scenario history: Make.com → Scenarios → [SOP-SUB-00](SOP-SUB-00_Subscription_State_Management_v3_0_FINAL.md) Dunning Day 0 → History 2. Verify the scenario is active (not paused) 3. Check if Supabase RPC calls are returning data 4. Check Customer.io for event delivery

Manual Recovery:

-- Find stalled subscriptions
SELECT s.id, c.email, s.payment_status, s.failed_payment_count,
  s.last_failed_at, s.dunning_started_at,
  s.dunning_email_day0_sent, s.dunning_email_day3_sent,
  s.dunning_email_day7_sent, s.dunning_email_day10_sent
FROM raw_ops.subscriptions s
JOIN raw_ops.customers c ON c.id = s.customer_id
WHERE s.payment_status IN ('failing', 'failed')
  AND s.status = 'ACTIVE'
ORDER BY s.dunning_started_at;

-- Manually mark email as sent if it was sent but flag wasn't updated
SELECT raw_ops.mark_dunning_email_sent('<subscription_id>', 'day_0');

Root Causes: - Make.com scenario paused or errored - Customer.io API key expired - Supabase RPC not returning results (function error) - Scenario schedule disabled

Day 10+ Critical Alert¶

Severity: Always critical

Immediate Actions: 1. Review affected subscriptions immediately — these will auto-pause tomorrow 2. Consider manual outreach to high-value customers 3. Check if there's a systemic payment processor issue 4. Review if Seal auto-retry is still functioning

Investigation:

-- Day 10+ subscriptions with full context
SELECT s.id, c.email, c.first_name,
  s.price_gbp as subscription_value,
  s.failed_payment_count,
  s.last_error_message,
  EXTRACT(DAY FROM NOW() - s.dunning_started_at) as days_in_dunning,
  s.card_brand, s.card_last_digits
FROM raw_ops.subscriptions s
JOIN raw_ops.customers c ON c.id = s.customer_id
WHERE s.payment_status IN ('failing', 'failed')
  AND s.dunning_started_at < NOW() - INTERVAL '10 days'
  AND s.status = 'ACTIVE'
ORDER BY s.price_gbp DESC;

12. Credentials Reference¶

When credentials need updating, modify these locations:

Supabase Anon Key¶

Used in: SUB-01, SUB-02, SUB-03, SUB-04 (Modules 2 & 6)
Current: eyJhbG... (truncated - check Make.com)
Update: Edit HTTP module headers in Make.com

Customer.io API Key¶

Used in: SUB-01, SUB-02, SUB-03, SUB-04 (Module 5)
Used in: Supabase secret CUSTOMERIO_API_KEY
Current: 1f9b8dfae641275436ab721ae46bcea1
Update:
Edit HTTP module headers in Make.com
supabase secrets set CUSTOMERIO_API_KEY=new_value

Seal API Token¶

Used in: SUB-01, SUB-02, SUB-03, SUB-04 (Module 4)
Purpose: Fetch subscription edit_url for payment update link
Update: Edit HTTP module headers in Make.com

Seal API Secret¶

Used in: Supabase secret SEAL_API_SECRET
Purpose: Verify incoming webhook HMAC signatures
Update: supabase secrets set SEAL_API_SECRET=new_value

13. Troubleshooting¶

13.1 Webhook Not Received¶

Symptoms: Subscription changed in Seal but not reflected in our database

Check inbox:

SELECT * FROM raw_ops.seal_webhook_inbox
WHERE seal_subscription_id = [seal_id]
ORDER BY received_at DESC
LIMIT 10;

Check Edge Function logs: Supabase Dashboard → Edge Functions → seal-webhook-handler → Logs

Common Causes: - Webhook not registered with Seal - Edge function error - HMAC validation failing

Resolution: - Re-register webhook with Seal (see Section 4.3) - Check Edge Function logs in Supabase - Verify SEAL_API_SECRET environment variable

13.2 Subscription State Mismatch¶

Symptoms: Local subscriptions table shows different state than Seal

Check local state:

SELECT * FROM raw_ops.subscriptions
WHERE seal_subscription_id = [seal_id];

Check last webhook payload:

SELECT payload FROM raw_ops.seal_webhook_inbox
WHERE seal_subscription_id = [seal_id]
ORDER BY received_at DESC
LIMIT 1;

Resolution: - Manual sync: Call Seal API and update local record - Check for failed webhook processing in inbox

13.3 Dunning Email Not Sent¶

Symptoms: Customer in dunning but no email received

Check subscription state:

SELECT 
  payment_status,
  failed_payment_count,
  dunning_started_at,
  dunning_email_day0_sent,
  dunning_email_day3_sent,
  dunning_email_day7_sent,
  dunning_email_day10_sent
FROM raw_ops.subscriptions
WHERE seal_subscription_id = [seal_id];

Check Make.com scenario history: Make.com → Scenarios → SOP-SUB-00 Dunning Day X → History

Check Customer.io: Customer.io → People → [customer email] → Activity

Common Causes: - Make.com scenario paused or errored - Email flag already set (won't resend) - Customer.io event not triggered - Email bounced or blocked

13.4 Payment Recovery Not Detected¶

Symptoms: Customer fixed payment but still showing in dunning

Check for success webhook:

SELECT * FROM raw_ops.seal_webhook_inbox
WHERE webhook_topic = 'billing_attempt/succeeded'
AND seal_subscription_id = [seal_id]
ORDER BY received_at DESC;

Common Causes: - Success webhook not received - Webhook processing failed - Seal hasn't retried billing yet

Resolution: - Check if Seal shows successful billing - Manually trigger webhook retry or update subscription state

13.5 Manual Health Check Queries¶

Quick subscription system health:

SELECT * FROM raw_ops.fn_check_subscription_health_v2();

Subscription monitoring dashboard view:

SELECT * FROM raw_ops.v_subscription_monitoring_health;

Monitor execution history:

SELECT check_name, run_at, status, duration_ms, alert_triggered, result_summary
FROM raw_ops.monitoring_runs
WHERE check_name IN ('subscription_health', 'subscription_daily')
ORDER BY run_at DESC LIMIT 20;

Subscriptions in Dunning:

SELECT 
  s.id,
  c.email,
  s.payment_status,
  s.failed_payment_count,
  s.dunning_started_at,
  EXTRACT(DAY FROM NOW() - s.dunning_started_at) as days_in_dunning,
  s.price_gbp as mrr_at_risk
FROM raw_ops.subscriptions s
JOIN raw_ops.customers c ON c.id = s.customer_id
WHERE s.payment_status IN ('failing', 'failed')
AND s.status = 'ACTIVE'
ORDER BY s.dunning_started_at;

Webhook Processing Health:

SELECT 
  webhook_topic,
  processing_status,
  COUNT(*) as count,
  AVG(processing_duration_ms) as avg_duration_ms
FROM raw_ops.seal_webhook_inbox
WHERE received_at > NOW() - INTERVAL '24 hours'
GROUP BY webhook_topic, processing_status
ORDER BY webhook_topic;

Failed Webhooks:

SELECT 
  id,
  webhook_topic,
  seal_subscription_id,
  last_error,
  retry_count,
  received_at
FROM raw_ops.seal_webhook_inbox
WHERE processing_status = 'failed'
ORDER BY received_at DESC
LIMIT 20;

Document	Purpose
SOP 00	Shopify webhook ingestion pattern (template for seal-webhook-handler)
SOP-MON-01	Monitoring architecture (pattern for subscription monitors)
SOP CS-00	Customer operations (references dunning procedures)
Customer Portal Docs	Portal feature specifications including payment warning banner
Ops Portal Docs	Support workstation specifications including dunning queue
SOP-REF-01	Credit system (interacts with payment recovery)

Version History¶

Version	Date	Author	Changes
1.0 DRAFT	2026-01-16	Anton + Claude	Initial design document
1.1 FINAL	2026-01-26	Anton + Claude	Phase 1 & 2 deployed, operational reference
2.0 FINAL	2026-01-26	Anton + Claude	Comprehensive merge: full specifications + deployment status
3.0 FINAL	2026-02-03	Anton + Claude	Phase 5 monitoring deployed and integrated. Schema corrections: `price_gbp` (not `total_value_pence`), `box_size` (not `box_size_kg`). Removed separate Phase 5 document — single source of truth.

Next Steps¶

Test with live subscription — Trigger a test payment failure in Seal to verify end-to-end flow
Deploy SUB-05 Card Expiry — Proactive warnings before cards expire (Phase 3)
Build Ops Portal dunning queue — Support team visibility (Phase 4)
Create Metabase dashboard — Subscription health metrics using v_subscription_monitoring_health

Approval¶

Role	Name	Date	Signature
Founder	Anton
Technical Review

Document Owner: Protocol Raw Operations
Classification: Core Infrastructure
Review Cycle: Quarterly
Next Review: 2026-05-03

Protocol Raw — Verified safe, batch by batch.