61 lines
2.3 KiB
MySQL
61 lines
2.3 KiB
MySQL
|
-- v1.0.6.2: Void subscription rows that sit in active/trialing state without
|
||
|
|
-- any effective payment linkage. Introduced to compensate for a bypass where
|
||
|
|
-- POST /subscribe could create 'active' rows on paid plans without invoking
|
||
|
|
-- the payment provider (e.g., when HYPERSWITCH_ENABLED=false or provider
|
||
|
|
-- unset). The runtime filter in GetUserSubscription (service.go) closes the
|
||
|
|
-- feature bypass going forward; this migration cleans up the rows already
|
||
|
|
-- written to the database pre-v1.0.6.2.
|
||
|
|
--
|
||
|
|
-- Fantôme selection criteria:
|
||
|
|
-- 1. status IN ('active', 'trialing')
|
||
|
|
-- 2. plan is paid (subscription_plans.price_monthly_cents > 0)
|
||
|
|
-- 3. no invoice attached carries a hyperswitch_payment_id (= PSP never reached)
|
||
|
|
-- 4. not a currently-valid trial (trial_end > NOW())
|
||
|
|
--
|
||
|
|
-- Audit table is dated so a future rerun doesn't collide. Rows here can be
|
||
|
|
-- used to notify affected users (if any were honest-path).
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS voided_subscriptions_20260417 (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
subscription_id UUID NOT NULL,
|
||
|
|
user_id UUID NOT NULL,
|
||
|
|
plan_id UUID NOT NULL,
|
||
|
|
previous_status VARCHAR(30) NOT NULL,
|
||
|
|
voided_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_voided_subscriptions_20260417_user
|
||
|
|
ON voided_subscriptions_20260417(user_id);
|
||
|
|
|
||
|
|
INSERT INTO voided_subscriptions_20260417 (subscription_id, user_id, plan_id, previous_status)
|
||
|
|
SELECT us.id, us.user_id, us.plan_id, us.status
|
||
|
|
FROM user_subscriptions us
|
||
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
||
|
|
WHERE us.status IN ('active', 'trialing')
|
||
|
|
AND sp.price_monthly_cents > 0
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM subscription_invoices si
|
||
|
|
WHERE si.subscription_id = us.id
|
||
|
|
AND si.hyperswitch_payment_id IS NOT NULL
|
||
|
|
AND si.hyperswitch_payment_id <> ''
|
||
|
|
)
|
||
|
|
AND NOT (
|
||
|
|
us.status = 'trialing'
|
||
|
|
AND us.trial_end IS NOT NULL
|
||
|
|
AND us.trial_end > NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
UPDATE user_subscriptions
|
||
|
|
SET status = 'expired',
|
||
|
|
canceled_at = COALESCE(canceled_at, NOW()),
|
||
|
|
updated_at = NOW()
|
||
|
|
WHERE id IN (SELECT subscription_id FROM voided_subscriptions_20260417);
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
DECLARE v_count INTEGER;
|
||
|
|
BEGIN
|
||
|
|
SELECT COUNT(*) INTO v_count FROM voided_subscriptions_20260417;
|
||
|
|
RAISE NOTICE 'v1.0.6.2: voided % pre-existing unpaid subscription row(s)', v_count;
|
||
|
|
END $$;
|