firefrost-services/services/arbiter-3.0/migrations/stripe-integration.sql

-- ============================================================================
-- STRIPE DIRECT INTEGRATION MIGRATION
-- ============================================================================
-- Date: April 3, 2026
-- Purpose: Eliminate Paymenter, integrate Stripe directly into Trinity Console
-- Gemini Reviewed: Yes (v2 with Sovereign one-time payment support)
--
-- This migration adds:
-- 1. stripe_products - Product catalog with recurring/one-time billing types
-- 2. webhook_events_processed - Idempotency tracking for webhooks
-- 3. subscriptions updates - Lifetime tier support (Sovereign $499)
-- 4. Performance indexes
-- ============================================================================

-- ============================================================================
-- 1. PRODUCT CATALOG
-- ============================================================================
CREATE TABLE IF NOT EXISTS stripe_products (
  tier_level INT PRIMARY KEY,
  tier_name VARCHAR(50) NOT NULL,
  fire_or_frost VARCHAR(10) NOT NULL CHECK (fire_or_frost IN ('fire', 'frost', 'both')),
  price_monthly DECIMAL(10,2) NOT NULL,
  stripe_product_id VARCHAR(255) UNIQUE NOT NULL,
  stripe_price_id VARCHAR(255) UNIQUE NOT NULL,
  benefits JSONB,
  billing_type VARCHAR(20) DEFAULT 'recurring' CHECK (billing_type IN ('recurring', 'one-time')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE stripe_products IS 'Stripe product catalog - maps tier levels to Stripe Price IDs';
COMMENT ON COLUMN stripe_products.billing_type IS 'recurring for monthly subscriptions (tiers 1-9), one-time for Sovereign (tier 10)';
COMMENT ON COLUMN stripe_products.price_monthly IS 'Monthly price for recurring, total price for one-time';

-- ============================================================================
-- 2. WEBHOOK IDEMPOTENCY TRACKING
-- ============================================================================
CREATE TABLE IF NOT EXISTS webhook_events_processed (
  stripe_event_id VARCHAR(255) PRIMARY KEY,
  event_type VARCHAR(100) NOT NULL,
  processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_webhook_processed_at ON webhook_events_processed(processed_at);

COMMENT ON TABLE webhook_events_processed IS 'Tracks processed Stripe webhook events to ensure idempotency';

-- ============================================================================
-- 3. SUBSCRIPTIONS TABLE UPDATES (Lifetime Support)
-- ============================================================================

-- Add lifetime flag and payment intent ID for one-time payments
ALTER TABLE subscriptions 
ADD COLUMN IF NOT EXISTS is_lifetime BOOLEAN DEFAULT FALSE;

ALTER TABLE subscriptions 
ADD COLUMN IF NOT EXISTS stripe_payment_intent_id VARCHAR(255);

-- Add unique constraints for both subscription IDs and payment intent IDs
-- Note: We use separate constraints because a record will have EITHER sub_id OR payment_intent_id, never both
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'unique_stripe_sub'
  ) THEN
    ALTER TABLE subscriptions ADD CONSTRAINT unique_stripe_sub UNIQUE (stripe_subscription_id);
  END IF;
END $$;

DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'unique_stripe_pi'
  ) THEN
    ALTER TABLE subscriptions ADD CONSTRAINT unique_stripe_pi UNIQUE (stripe_payment_intent_id);
  END IF;
END $$;

COMMENT ON COLUMN subscriptions.is_lifetime IS 'TRUE for Sovereign tier (one-time $499), FALSE for recurring subscriptions';
COMMENT ON COLUMN subscriptions.stripe_payment_intent_id IS 'Payment Intent ID for one-time payments (Sovereign), NULL for recurring';

-- ============================================================================
-- 4. PERFORMANCE INDEXES
-- ============================================================================

-- Index for webhook lookups by customer ID
CREATE INDEX IF NOT EXISTS idx_stripe_customer_id ON subscriptions(stripe_customer_id);

-- Index for grace period queries (excludes lifetime users)
CREATE INDEX IF NOT EXISTS idx_grace_period_lookup ON subscriptions(grace_period_ends_at, is_lifetime) 
WHERE grace_period_ends_at IS NOT NULL;

-- ============================================================================
-- 5. VALIDATION & CONSTRAINTS
-- ============================================================================

-- Ensure either subscription_id OR payment_intent_id exists, but not both
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'check_stripe_id_exists'
  ) THEN
    ALTER TABLE subscriptions ADD CONSTRAINT check_stripe_id_exists 
    CHECK (
      (stripe_subscription_id IS NOT NULL AND stripe_payment_intent_id IS NULL) OR
      (stripe_subscription_id IS NULL AND stripe_payment_intent_id IS NOT NULL)
    );
  END IF;
END $$;

COMMENT ON CONSTRAINT check_stripe_id_exists ON subscriptions IS 'Ensures exactly one of subscription_id or payment_intent_id exists';

-- ============================================================================
-- 6. MIGRATION VERIFICATION QUERIES
-- ============================================================================

-- Run these to verify migration succeeded:
-- SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename IN ('stripe_products', 'webhook_events_processed');
-- SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'subscriptions' AND column_name IN ('is_lifetime', 'stripe_payment_intent_id');
-- SELECT indexname FROM pg_indexes WHERE tablename = 'subscriptions' AND indexname IN ('idx_stripe_customer_id', 'idx_grace_period_lookup');

-- ============================================================================
-- END OF MIGRATION
-- ============================================================================