lyon
34 lines
2.0 KiB
SQL
34 lines
2.0 KiB
SQL
ALTER TABLE hwlab_credit_ledger ADD COLUMN IF NOT EXISTS balance_before BIGINT;
|
|
ALTER TABLE hwlab_credit_ledger ADD COLUMN IF NOT EXISTS source TEXT NOT NULL DEFAULT 'system';
|
|
ALTER TABLE hwlab_credit_ledger ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'applied';
|
|
ALTER TABLE hwlab_credit_ledger ADD COLUMN IF NOT EXISTS operator_user_id TEXT;
|
|
ALTER TABLE hwlab_credit_ledger ADD COLUMN IF NOT EXISTS operator_username TEXT;
|
|
|
|
UPDATE hwlab_credit_ledger
|
|
SET balance_before = balance_after - delta_credits
|
|
WHERE balance_before IS NULL;
|
|
|
|
UPDATE hwlab_credit_ledger
|
|
SET source = CASE
|
|
WHEN kind = 'usage' THEN 'usage'
|
|
WHEN kind LIKE 'admin_%' THEN 'admin'
|
|
WHEN kind = 'grant' THEN 'registration'
|
|
ELSE source
|
|
END
|
|
WHERE source = 'system';
|
|
|
|
ALTER TABLE hwlab_billing_reservations ADD COLUMN IF NOT EXISTS api_key_id TEXT;
|
|
ALTER TABLE hwlab_usage_records ADD COLUMN IF NOT EXISTS api_key_id TEXT;
|
|
ALTER TABLE hwlab_usage_records ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'recorded';
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_created_idx ON hwlab_credit_ledger(created_at DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_kind_created_idx ON hwlab_credit_ledger(kind, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_source_created_idx ON hwlab_credit_ledger(source, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_idempotency_idx ON hwlab_credit_ledger(idempotency_key) WHERE idempotency_key IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_operator_idx ON hwlab_credit_ledger(operator_user_id) WHERE operator_user_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_created_idx ON hwlab_usage_records(created_at DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_resource_created_idx ON hwlab_usage_records(resource_type, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_status_created_idx ON hwlab_usage_records(status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_api_key_idx ON hwlab_usage_records(api_key_id) WHERE api_key_id IS NOT NULL;
|