lyon
79 lines
3.9 KiB
SQL
79 lines
3.9 KiB
SQL
CREATE TABLE IF NOT EXISTS hwlab_billing_plans (
|
|
id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (status IN ('active', 'disabled'))
|
|
);
|
|
|
|
INSERT INTO hwlab_billing_plans (id, display_name, status, description, metadata)
|
|
VALUES ('default', 'Default', 'active', 'Default HWLAB v0.3 resource plan', '{"source":"hwlab-v03","limitSemantics":"zero-means-unlimited"}'::jsonb)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
display_name = EXCLUDED.display_name,
|
|
status = EXCLUDED.status,
|
|
description = EXCLUDED.description,
|
|
metadata = hwlab_billing_plans.metadata || EXCLUDED.metadata,
|
|
updated_at = now();
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_resource_entitlements (
|
|
id TEXT PRIMARY KEY,
|
|
plan_id TEXT NOT NULL REFERENCES hwlab_billing_plans(id) ON DELETE CASCADE,
|
|
resource_type TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
monthly_quota_credits BIGINT NOT NULL DEFAULT 0,
|
|
max_concurrent_reservations INTEGER NOT NULL DEFAULT 0,
|
|
rpm_limit INTEGER NOT NULL DEFAULT 0,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (plan_id, resource_type),
|
|
CHECK (resource_type <> ''),
|
|
CHECK (monthly_quota_credits >= 0),
|
|
CHECK (max_concurrent_reservations >= 0),
|
|
CHECK (rpm_limit >= 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_resource_entitlements_plan_idx ON hwlab_resource_entitlements(plan_id);
|
|
CREATE INDEX IF NOT EXISTS hwlab_resource_entitlements_resource_idx ON hwlab_resource_entitlements(resource_type);
|
|
|
|
INSERT INTO hwlab_resource_entitlements (id, plan_id, resource_type, enabled, monthly_quota_credits, max_concurrent_reservations, rpm_limit, metadata)
|
|
VALUES
|
|
('ent_default_code_agent', 'default', 'code_agent', TRUE, 0, 0, 0, '{"serviceIds":["hwlab-code-agent"],"limitSemantics":"zero-means-unlimited"}'::jsonb),
|
|
('ent_default_aipod', 'default', 'aipod', TRUE, 0, 0, 0, '{"serviceIds":["hwlab-aipod"],"limitSemantics":"zero-means-unlimited"}'::jsonb),
|
|
('ent_default_hwpod', 'default', 'hwpod', TRUE, 0, 0, 0, '{"serviceIds":["hwlab-hwpod"],"limitSemantics":"zero-means-unlimited"}'::jsonb)
|
|
ON CONFLICT (plan_id, resource_type) DO UPDATE SET
|
|
enabled = EXCLUDED.enabled,
|
|
monthly_quota_credits = EXCLUDED.monthly_quota_credits,
|
|
max_concurrent_reservations = EXCLUDED.max_concurrent_reservations,
|
|
rpm_limit = EXCLUDED.rpm_limit,
|
|
metadata = hwlab_resource_entitlements.metadata || EXCLUDED.metadata,
|
|
updated_at = now();
|
|
|
|
ALTER TABLE hwlab_billing_reservations ADD COLUMN IF NOT EXISTS resource_type TEXT NOT NULL DEFAULT 'generic';
|
|
ALTER TABLE hwlab_usage_records ADD COLUMN IF NOT EXISTS resource_type TEXT NOT NULL DEFAULT 'generic';
|
|
|
|
UPDATE hwlab_billing_reservations
|
|
SET resource_type = CASE
|
|
WHEN lower(service_id) LIKE '%code-agent%' OR lower(service_id) LIKE '%code_agent%' THEN 'code_agent'
|
|
WHEN lower(service_id) LIKE '%aipod%' THEN 'aipod'
|
|
WHEN lower(service_id) LIKE '%hwpod%' THEN 'hwpod'
|
|
ELSE resource_type
|
|
END
|
|
WHERE resource_type = 'generic';
|
|
|
|
UPDATE hwlab_usage_records
|
|
SET resource_type = CASE
|
|
WHEN lower(service_id) LIKE '%code-agent%' OR lower(service_id) LIKE '%code_agent%' THEN 'code_agent'
|
|
WHEN lower(service_id) LIKE '%aipod%' THEN 'aipod'
|
|
WHEN lower(service_id) LIKE '%hwpod%' THEN 'hwpod'
|
|
ELSE resource_type
|
|
END
|
|
WHERE resource_type = 'generic';
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_billing_reservations_user_resource_idx ON hwlab_billing_reservations(user_id, resource_type, status, expires_at);
|
|
CREATE INDEX IF NOT EXISTS hwlab_billing_reservations_user_resource_created_idx ON hwlab_billing_reservations(user_id, resource_type, created_at);
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_user_resource_created_idx ON hwlab_usage_records(user_id, resource_type, created_at);
|