130 lines
8.8 KiB
PL/PgSQL
130 lines
8.8 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE billing_price_book (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
provider varchar(40) NOT NULL CHECK (provider ~ '^[a-z][a-z0-9_.-]{1,39}$'),
|
|
currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
|
|
version_no integer NOT NULL CHECK (version_no>0),
|
|
status varchar(16) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT','PUBLISHED','RETIRED')),
|
|
source_ref varchar(300) NOT NULL CHECK (length(trim(source_ref))>0),
|
|
change_summary varchar(500) NOT NULL CHECK (length(trim(change_summary))>0),
|
|
effective_from timestamptz NOT NULL,
|
|
created_by uuid NOT NULL,
|
|
published_by uuid,
|
|
published_at timestamptz,
|
|
retired_at timestamptz,
|
|
row_version bigint NOT NULL DEFAULT 1 CHECK (row_version>0),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT billing_price_book_creator_tenant_fk FOREIGN KEY (created_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
|
CONSTRAINT billing_price_book_publisher_tenant_fk FOREIGN KEY (published_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
|
CONSTRAINT billing_price_book_version_key UNIQUE (tenant_id,provider,version_no),
|
|
CONSTRAINT billing_price_book_publish_state CHECK (
|
|
(status='DRAFT' AND published_by IS NULL AND published_at IS NULL AND retired_at IS NULL) OR
|
|
(status='PUBLISHED' AND published_by IS NOT NULL AND published_at IS NOT NULL AND retired_at IS NULL) OR
|
|
(status='RETIRED' AND published_by IS NOT NULL AND published_at IS NOT NULL AND retired_at IS NOT NULL)
|
|
)
|
|
);
|
|
CREATE UNIQUE INDEX billing_price_book_one_published ON billing_price_book(tenant_id,provider) WHERE status='PUBLISHED';
|
|
|
|
CREATE TABLE billing_price_rate (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
price_book_id uuid NOT NULL REFERENCES billing_price_book(id) ON DELETE CASCADE,
|
|
usage_category varchar(32) NOT NULL CHECK (usage_category IN ('RTC','END_TO_END_VOICE','ASR','LLM','TTS','STORAGE','POST_PROCESSING')),
|
|
metric_code varchar(80) NOT NULL CHECK (metric_code ~ '^[a-z][a-z0-9_.-]{1,79}$'),
|
|
unit varchar(40) NOT NULL CHECK (unit ~ '^[a-z][a-z0-9_.-]{1,39}$'),
|
|
unit_price_micros bigint NOT NULL CHECK (unit_price_micros>=0),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT billing_price_rate_key UNIQUE (price_book_id,usage_category,metric_code,unit)
|
|
);
|
|
|
|
CREATE TABLE usage_quota_policy (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
scope_type varchar(16) NOT NULL CHECK (scope_type IN ('TENANT','DEVICE')),
|
|
device_id uuid,
|
|
version_no integer NOT NULL CHECK (version_no>0),
|
|
status varchar(16) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE','RETIRED')),
|
|
billing_timezone varchar(64) NOT NULL CHECK (length(trim(billing_timezone))>0),
|
|
currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
|
|
daily_limit_micros bigint NOT NULL CHECK (daily_limit_micros>0),
|
|
monthly_limit_micros bigint NOT NULL CHECK (monthly_limit_micros>0),
|
|
max_concurrent_calls integer NOT NULL CHECK (max_concurrent_calls BETWEEN 1 AND 100000),
|
|
max_call_duration_sec integer NOT NULL CHECK (max_call_duration_sec BETWEEN 60 AND 86400),
|
|
in_progress_action varchar(32) NOT NULL CHECK (in_progress_action IN ('STOP_IMMEDIATELY','COMPLETE_CURRENT_TURN')),
|
|
change_summary varchar(500) NOT NULL CHECK (length(trim(change_summary))>0),
|
|
created_by uuid NOT NULL,
|
|
retired_at timestamptz,
|
|
row_version bigint NOT NULL DEFAULT 1 CHECK (row_version>0),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT usage_quota_policy_creator_tenant_fk FOREIGN KEY (created_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
|
CONSTRAINT usage_quota_policy_device_tenant_fk FOREIGN KEY (device_id,tenant_id) REFERENCES device(id,tenant_id),
|
|
CONSTRAINT usage_quota_policy_scope_check CHECK (
|
|
(scope_type='TENANT' AND device_id IS NULL) OR (scope_type='DEVICE' AND device_id IS NOT NULL)
|
|
)
|
|
);
|
|
CREATE UNIQUE INDEX usage_quota_policy_active_tenant ON usage_quota_policy(tenant_id) WHERE status='ACTIVE' AND scope_type='TENANT';
|
|
CREATE UNIQUE INDEX usage_quota_policy_active_device ON usage_quota_policy(tenant_id,device_id) WHERE status='ACTIVE' AND scope_type='DEVICE';
|
|
CREATE UNIQUE INDEX usage_quota_policy_tenant_version ON usage_quota_policy(tenant_id,version_no) WHERE scope_type='TENANT';
|
|
CREATE UNIQUE INDEX usage_quota_policy_device_version ON usage_quota_policy(tenant_id,device_id,version_no) WHERE scope_type='DEVICE';
|
|
|
|
CREATE TABLE conversation_usage_event (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
conversation_id uuid NOT NULL,
|
|
provider_event_id uuid REFERENCES provider_event(id) ON DELETE SET NULL,
|
|
provider varchar(40) NOT NULL,
|
|
source_event_key varchar(255) NOT NULL,
|
|
usage_category varchar(32) NOT NULL CHECK (usage_category IN ('RTC','END_TO_END_VOICE','ASR','LLM','TTS','STORAGE','POST_PROCESSING')),
|
|
metric_code varchar(80) NOT NULL CHECK (metric_code ~ '^[a-z][a-z0-9_.-]{1,79}$'),
|
|
quantity numeric(24,6) NOT NULL CHECK (quantity>=0),
|
|
unit varchar(40) NOT NULL CHECK (unit ~ '^[a-z][a-z0-9_.-]{1,39}$'),
|
|
occurred_at timestamptz NOT NULL,
|
|
received_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT conversation_usage_conversation_tenant_fk FOREIGN KEY (conversation_id,tenant_id) REFERENCES conversation(id,tenant_id) ON DELETE CASCADE,
|
|
CONSTRAINT conversation_usage_event_key UNIQUE (tenant_id,provider,source_event_key,usage_category,metric_code,unit)
|
|
);
|
|
CREATE INDEX conversation_usage_time_idx ON conversation_usage_event(tenant_id,conversation_id,occurred_at,id);
|
|
|
|
CREATE TABLE conversation_cost_line (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
conversation_id uuid NOT NULL,
|
|
usage_event_id uuid NOT NULL UNIQUE REFERENCES conversation_usage_event(id) ON DELETE CASCADE,
|
|
price_book_id uuid NOT NULL REFERENCES billing_price_book(id),
|
|
price_rate_id uuid NOT NULL REFERENCES billing_price_rate(id),
|
|
quantity numeric(24,6) NOT NULL,
|
|
unit varchar(40) NOT NULL,
|
|
unit_price_micros bigint NOT NULL CHECK (unit_price_micros>=0),
|
|
cost_micros bigint NOT NULL CHECK (cost_micros>=0),
|
|
currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
|
|
rated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT conversation_cost_conversation_tenant_fk FOREIGN KEY (conversation_id,tenant_id) REFERENCES conversation(id,tenant_id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX conversation_cost_tenant_time_idx ON conversation_cost_line(tenant_id,rated_at,conversation_id);
|
|
|
|
CREATE TABLE conversation_usage_budget (
|
|
conversation_id uuid PRIMARY KEY REFERENCES conversation(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
tenant_policy_id uuid REFERENCES usage_quota_policy(id),
|
|
device_policy_id uuid REFERENCES usage_quota_policy(id),
|
|
billing_timezone varchar(64) NOT NULL,
|
|
currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
|
|
daily_limit_micros bigint NOT NULL CHECK (daily_limit_micros>0),
|
|
monthly_limit_micros bigint NOT NULL CHECK (monthly_limit_micros>0),
|
|
max_call_duration_sec integer NOT NULL CHECK (max_call_duration_sec BETWEEN 60 AND 86400),
|
|
in_progress_action varchar(32) NOT NULL CHECK (in_progress_action IN ('STOP_IMMEDIATELY','COMPLETE_CURRENT_TURN')),
|
|
daily_spend_at_create bigint NOT NULL CHECK (daily_spend_at_create>=0),
|
|
monthly_spend_at_create bigint NOT NULL CHECK (monthly_spend_at_create>=0),
|
|
frozen_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT conversation_usage_budget_tenant_fk FOREIGN KEY (conversation_id,tenant_id) REFERENCES conversation(id,tenant_id) ON DELETE CASCADE
|
|
);
|
|
|
|
COMMIT;
|