# 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;
```
