# Diff for migration 0021
Migration file: `0021_add_get_instance_stats.sql`
## Table Changes
None.
## New Indexes
None.
## Function Changes
### `get_instance_stats` — new function
Replaces four inline SQL queries in `provider.rs` with a single stored procedure.
Correctly merges `kv_delta` + `kv_store` for KV stats (previous inline code only queried `kv_store`).
Computes `carry_forward_events` array length server-side via `jsonb_array_length`.
```diff
@@
+ CREATE OR REPLACE FUNCTION SCHEMA.get_instance_stats(
+ p_instance TEXT
+ )
+ RETURNS TABLE(
+ out_found BOOLEAN,
+ out_history_event_count BIGINT,
+ out_history_size_bytes BIGINT,
+ out_queue_pending_count BIGINT,
+ out_kv_user_key_count BIGINT,
+ out_kv_total_value_bytes BIGINT
+ ) AS $get_instance_stats$
+ DECLARE
+ v_execution_id BIGINT;
+ v_event_data TEXT;
+ v_event_json JSONB;
+ v_cf_events JSONB;
+ BEGIN
+ SELECT current_execution_id
+ INTO v_execution_id
+ FROM SCHEMA.instances
+ WHERE instance_id = p_instance;
+
+ IF NOT FOUND THEN
+ out_found := FALSE;
+ out_history_event_count := 0;
+ out_history_size_bytes := 0;
+ out_queue_pending_count := 0;
+ out_kv_user_key_count := 0;
+ out_kv_total_value_bytes := 0;
+ RETURN NEXT;
+ RETURN;
+ END IF;
+
+ out_found := TRUE;
+
+ SELECT COUNT(*)::BIGINT,
+ COALESCE(SUM(OCTET_LENGTH(event_data)), 0)::BIGINT
+ INTO out_history_event_count, out_history_size_bytes
+ FROM SCHEMA.history
+ WHERE instance_id = p_instance AND execution_id = v_execution_id;
+
+ -- KV key count: logical (merged, excluding delta tombstones)
+ SELECT COUNT(DISTINCT k.key)::BIGINT
+ INTO out_kv_user_key_count
+ FROM (
+ SELECT key FROM SCHEMA.kv_store WHERE instance_id = p_instance
+ UNION
+ SELECT key FROM SCHEMA.kv_delta WHERE instance_id = p_instance AND value IS NOT NULL
+ ) k;
+
+ -- KV value bytes: physical (sum from both tables — both consume storage)
+ SELECT (
+ COALESCE((SELECT SUM(OCTET_LENGTH(value)) FROM SCHEMA.kv_store WHERE instance_id = p_instance), 0)
+ + COALESCE((SELECT SUM(OCTET_LENGTH(value)) FROM SCHEMA.kv_delta WHERE instance_id = p_instance), 0)
+ )::BIGINT
+ INTO out_kv_total_value_bytes;
+
+ SELECT event_data
+ INTO v_event_data
+ FROM SCHEMA.history
+ WHERE instance_id = p_instance
+ AND execution_id = v_execution_id
+ AND event_id = 1;
+
+ out_queue_pending_count := 0;
+ IF v_event_data IS NOT NULL THEN
+ BEGIN
+ v_event_json := v_event_data::JSONB;
+ v_cf_events := v_event_json -> 'carry_forward_events';
+ IF v_cf_events IS NOT NULL AND jsonb_typeof(v_cf_events) = 'array' THEN
+ out_queue_pending_count := jsonb_array_length(v_cf_events)::BIGINT;
+ END IF;
+ EXCEPTION WHEN OTHERS THEN
+ out_queue_pending_count := 0;
+ END;
+ END IF;
+
+ RETURN NEXT;
+ END;
+ $get_instance_stats$ LANGUAGE plpgsql;
```
### `cleanup_schema` — body modified (baseline: 0020)
```diff
DROP FUNCTION IF EXISTS SCHEMA.get_kv_value(TEXT, TEXT);
DROP FUNCTION IF EXISTS SCHEMA.get_kv_all_values(TEXT);
+ DROP FUNCTION IF EXISTS SCHEMA.get_instance_stats(TEXT);
END;
$cleanup$ LANGUAGE plpgsql;
```