Spaces:
Running
Running
File size: 3,027 Bytes
d8ffec9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | -- 0024_subscriptions.sql
-- Subscription & Recurring Billing Engine for Rtix
-- Supports SaaS merchants offering recurring payment products.
CREATE TABLE IF NOT EXISTS subscription_plans (
id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
merchant_id VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
description TEXT,
price_inr NUMERIC(12, 2) NOT NULL CHECK (price_inr > 0),
interval_days INT NOT NULL CHECK (interval_days > 0), -- e.g. 30 = monthly, 365 = annual
trial_days INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS subscriptions (
id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
merchant_id VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
plan_id VARCHAR(36) NOT NULL REFERENCES subscription_plans(id),
subscriber_email VARCHAR(255) NOT NULL,
subscriber_phone VARCHAR(20),
subscriber_name VARCHAR(255) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'TRIAL'
CHECK (status IN ('TRIAL', 'ACTIVE', 'PAST_DUE', 'CANCELLED', 'EXPIRED')),
current_period_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
current_period_end TIMESTAMPTZ NOT NULL,
cancelled_at TIMESTAMPTZ,
cancel_reason TEXT,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS subscription_billing_events (
id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
subscription_id VARCHAR(36) NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
merchant_id VARCHAR(36) NOT NULL,
amount_inr NUMERIC(12, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
CHECK (status IN ('PENDING', 'SUCCESS', 'FAILED', 'REFUNDED')),
transaction_id VARCHAR(36), -- links to orders.transaction_id on success
attempt_count INT NOT NULL DEFAULT 1,
billed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
settled_at TIMESTAMPTZ,
failure_reason TEXT
);
CREATE INDEX IF NOT EXISTS idx_subscriptions_merchant ON subscriptions(merchant_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_subscriptions_period_end ON subscriptions(current_period_end) WHERE status = 'ACTIVE';
CREATE INDEX IF NOT EXISTS idx_billing_events_subscription ON subscription_billing_events(subscription_id);
CREATE INDEX IF NOT EXISTS idx_billing_events_merchant ON subscription_billing_events(merchant_id);
CREATE INDEX IF NOT EXISTS idx_subscription_plans_merchant ON subscription_plans(merchant_id);
|