template-saas/apps/database/ddl/schemas/plans/tables/01-plans.sql

-- ============================================
-- TEMPLATE-SAAS: Plans
-- Schema: plans
-- Version: 1.0.0
-- ============================================

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

    -- Basic info
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    tagline VARCHAR(200),  -- Short marketing text

    -- Pricing
    price_monthly DECIMAL(10, 2),
    price_yearly DECIMAL(10, 2),
    currency VARCHAR(3) DEFAULT 'USD',

    -- Stripe integration
    stripe_product_id VARCHAR(255),
    stripe_price_id_monthly VARCHAR(255),
    stripe_price_id_yearly VARCHAR(255),

    -- Features (JSONB array)
    features JSONB DEFAULT '[]'::jsonb,
    -- Example:
    -- [
    --   { "name": "Users", "value": "5", "highlight": true },
    --   { "name": "Storage", "value": "10GB" },
    --   { "name": "API Access", "value": true }
    -- ]

    -- Limits (JSONB object)
    limits JSONB DEFAULT '{}'::jsonb,
    -- Example:
    -- {
    --   "max_users": 5,
    --   "max_storage_gb": 10,
    --   "max_api_calls_month": 10000,
    --   "max_products": 100,
    --   "max_projects": 10
    -- }

    -- Feature flags included
    included_features JSONB DEFAULT '[]'::jsonb,
    -- Example: ["advanced_analytics", "api_access", "priority_support"]

    -- Display
    is_popular BOOLEAN DEFAULT FALSE,  -- Highlight in pricing page
    is_enterprise BOOLEAN DEFAULT FALSE,  -- Contact sales
    is_active BOOLEAN DEFAULT TRUE,
    is_visible BOOLEAN DEFAULT TRUE,  -- Show in pricing page
    sort_order INT DEFAULT 0,

    -- Trial
    trial_days INT DEFAULT 14,

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

    -- Constraints
    CONSTRAINT valid_plan_slug CHECK (slug ~ '^[a-z][a-z0-9-]*$'),
    CONSTRAINT valid_prices CHECK (price_monthly >= 0 AND price_yearly >= 0)
);

-- Plan features detailed (optional, for complex feature matrix)
CREATE TABLE plans.plan_features (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES plans.plans(id) ON DELETE CASCADE,

    -- Feature definition
    feature_code VARCHAR(100) NOT NULL,  -- e.g., "api_access"
    feature_name VARCHAR(200) NOT NULL,
    category VARCHAR(100),

    -- Value
    value_type VARCHAR(20) NOT NULL,  -- 'boolean', 'number', 'text'
    value_boolean BOOLEAN,
    value_number INT,
    value_text VARCHAR(200),

    -- Display
    display_value VARCHAR(100),  -- "Unlimited", "10GB", "Yes"
    is_highlighted BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,

    -- Constraints
    CONSTRAINT unique_plan_feature UNIQUE (plan_id, feature_code)
);

-- Indexes
CREATE INDEX idx_plans_slug ON plans.plans(slug) WHERE is_active = TRUE;
CREATE INDEX idx_plans_active ON plans.plans(is_active, is_visible, sort_order);
CREATE INDEX idx_plan_features_plan ON plans.plan_features(plan_id);

-- Trigger
CREATE OR REPLACE FUNCTION plans.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_plans_updated_at
    BEFORE UPDATE ON plans.plans
    FOR EACH ROW
    EXECUTE FUNCTION plans.update_updated_at();

-- Comments
COMMENT ON TABLE plans.plans IS 'Subscription plans definition';
COMMENT ON TABLE plans.plan_features IS 'Detailed feature matrix per plan';
COMMENT ON COLUMN plans.plans.limits IS 'Quantitative limits enforced by the system';
COMMENT ON COLUMN plans.plans.included_features IS 'Feature flag codes included in this plan';