keyhan
35235fe0fc
Store explicit hourly/monthly/yearly rates in pricing_rates and addon_rates, compute deploy costs without cycle conversion, and simplify admin UI and wallet payment to cycle-only. Co-authored-by: Cursor <cursoragent@cursor.com>
118 lines
4.2 KiB
SQL
118 lines
4.2 KiB
SQL
-- Usage-based pricing catalog (replaces per-cycle service_plans + scattered platform_settings)
|
|
|
|
CREATE TABLE IF NOT EXISTS pricing_rates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
runtime VARCHAR NOT NULL,
|
|
resource_type VARCHAR NOT NULL,
|
|
hourly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
monthly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
yearly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
"updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (runtime, resource_type)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS addon_rates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
resource_type VARCHAR NOT NULL UNIQUE,
|
|
hourly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
monthly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
yearly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
"updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Seed addon rows
|
|
INSERT INTO addon_rates (resource_type, hourly_price, monthly_price, yearly_price)
|
|
VALUES
|
|
('redis_addon', 0, 0, 0),
|
|
('rabbitmq_addon', 0, 0, 0),
|
|
('elasticsearch_addon', 0, 0, 0),
|
|
('custom_domain_addon', 0, 0, 0)
|
|
ON CONFLICT (resource_type) DO NOTHING;
|
|
|
|
-- Migrate runtime rates from service_plans + pricing_rules
|
|
INSERT INTO pricing_rates (runtime, resource_type, hourly_price, monthly_price, yearly_price, is_active)
|
|
SELECT
|
|
sp.runtime,
|
|
pr.resource_type,
|
|
COALESCE(MAX(CASE WHEN sp."billingCycle" = 'hourly' THEN pr.unit_price END), 0),
|
|
COALESCE(MAX(CASE WHEN sp."billingCycle" = 'monthly' THEN pr.unit_price END), 0),
|
|
COALESCE(MAX(CASE WHEN sp."billingCycle" = 'yearly' THEN pr.unit_price END), 0),
|
|
BOOL_OR(sp."isActive")
|
|
FROM pricing_rules pr
|
|
JOIN service_plans sp ON sp.id = pr."planId"
|
|
WHERE pr.resource_type IN (
|
|
'base_fee', 'cpu_per_core', 'memory_per_gb', 'storage_per_gb', 'database_addon'
|
|
)
|
|
GROUP BY sp.runtime, pr.resource_type
|
|
ON CONFLICT (runtime, resource_type) DO UPDATE SET
|
|
hourly_price = EXCLUDED.hourly_price,
|
|
monthly_price = EXCLUDED.monthly_price,
|
|
yearly_price = EXCLUDED.yearly_price,
|
|
is_active = EXCLUDED.is_active,
|
|
"updatedAt" = NOW();
|
|
|
|
-- Optional services from platform_settings JSON
|
|
UPDATE addon_rates ar SET
|
|
hourly_price = COALESCE((s.parsed->'redis'->>'hourly')::decimal, 0),
|
|
monthly_price = COALESCE((s.parsed->'redis'->>'monthly')::decimal, 0),
|
|
yearly_price = COALESCE((s.parsed->'redis'->>'yearly')::decimal, 0)
|
|
FROM (
|
|
SELECT value::jsonb AS parsed
|
|
FROM platform_settings
|
|
WHERE key = 'optional_services_pricing_toman'
|
|
LIMIT 1
|
|
) s
|
|
WHERE ar.resource_type = 'redis_addon' AND s.parsed IS NOT NULL;
|
|
|
|
UPDATE addon_rates ar SET
|
|
hourly_price = COALESCE((s.parsed->'rabbitmq'->>'hourly')::decimal, 0),
|
|
monthly_price = COALESCE((s.parsed->'rabbitmq'->>'monthly')::decimal, 0),
|
|
yearly_price = COALESCE((s.parsed->'rabbitmq'->>'yearly')::decimal, 0)
|
|
FROM (
|
|
SELECT value::jsonb AS parsed
|
|
FROM platform_settings
|
|
WHERE key = 'optional_services_pricing_toman'
|
|
LIMIT 1
|
|
) s
|
|
WHERE ar.resource_type = 'rabbitmq_addon' AND s.parsed IS NOT NULL;
|
|
|
|
UPDATE addon_rates ar SET
|
|
hourly_price = COALESCE((s.parsed->'elasticsearch'->>'hourly')::decimal, 0),
|
|
monthly_price = COALESCE((s.parsed->'elasticsearch'->>'monthly')::decimal, 0),
|
|
yearly_price = COALESCE((s.parsed->'elasticsearch'->>'yearly')::decimal, 0)
|
|
FROM (
|
|
SELECT value::jsonb AS parsed
|
|
FROM platform_settings
|
|
WHERE key = 'optional_services_pricing_toman'
|
|
LIMIT 1
|
|
) s
|
|
WHERE ar.resource_type = 'elasticsearch_addon' AND s.parsed IS NOT NULL;
|
|
|
|
UPDATE addon_rates ar SET
|
|
monthly_price = COALESCE(s.price, 0)
|
|
FROM (
|
|
SELECT value::decimal AS price
|
|
FROM platform_settings
|
|
WHERE key = 'custom_domain_monthly_price_toman'
|
|
LIMIT 1
|
|
) s
|
|
WHERE ar.resource_type = 'custom_domain_addon' AND s.price IS NOT NULL;
|
|
|
|
-- Default runtime rows for nodejs, laravel, wordpress
|
|
INSERT INTO pricing_rates (runtime, resource_type, hourly_price, monthly_price, yearly_price)
|
|
SELECT r.runtime, t.resource_type, 0, 0, 0
|
|
FROM (VALUES ('nodejs'), ('laravel'), ('wordpress')) AS r(runtime)
|
|
CROSS JOIN (
|
|
VALUES
|
|
('base_fee'),
|
|
('cpu_per_core'),
|
|
('memory_per_gb'),
|
|
('storage_per_gb'),
|
|
('database_addon')
|
|
) AS t(resource_type)
|
|
ON CONFLICT (runtime, resource_type) DO NOTHING;
|