# Migration 0013 Diff: Add Capability Filtering

## Summary

Adds capability filtering support for provider-level version matching. Introduces `duroxide_version_major/minor/patch` columns on the `executions` table, updates `fetch_orchestration_item` to accept version filter parameters for SQL-level filtering before lock acquisition, and updates `ack_orchestration_item` to store pinned duroxide version from metadata.

## Why

- Enables multi-version fleet deployments where different dispatcher instances support different duroxide versions
- Version filtering happens at SQL level BEFORE lock acquisition, preventing incompatible dispatchers from locking work they can't process
- NULL versions are treated as always compatible (backward compatible with existing data)

## Schema Changes

### executions

```diff
+ duroxide_version_major INTEGER
+ duroxide_version_minor INTEGER
+ duroxide_version_patch INTEGER
```

All three columns are nullable. NULL means "no version pinned" (always compatible).

## Stored Procedure Changes

### fetch_orchestration_item

```diff
- CREATE OR REPLACE FUNCTION %I.fetch_orchestration_item(
-     p_now_ms BIGINT,
-     p_lock_timeout_ms BIGINT
- )
+ CREATE OR REPLACE FUNCTION %I.fetch_orchestration_item(
+     p_now_ms BIGINT,
+     p_lock_timeout_ms BIGINT,
+     p_min_version_packed BIGINT DEFAULT NULL,
+     p_max_version_packed BIGINT DEFAULT NULL
+ )

  -- Phase 1: Find a candidate instance
+ -- When version filter is provided, join to instances+executions
+ IF p_min_version_packed IS NOT NULL THEN
+     SELECT q.instance_id INTO v_instance_id
+     FROM %I.orchestrator_queue q
+     LEFT JOIN %I.instances i ON i.instance_id = q.instance_id
+     LEFT JOIN %I.executions e ON e.instance_id = i.instance_id
+         AND e.execution_id = i.current_execution_id
+     WHERE q.visible_at <= TO_TIMESTAMP(p_now_ms / 1000.0)
+       AND NOT EXISTS (...)
+       AND (
+         e.duroxide_version_major IS NULL
+         OR (e.duroxide_version_major * 1000000 + e.duroxide_version_minor * 1000 + e.duroxide_version_patch)
+            BETWEEN p_min_version_packed AND p_max_version_packed
+       )
+     ORDER BY q.visible_at, q.id
+     LIMIT 1;
+ ELSE
      -- Original unfiltered query (legacy behavior)
+ END IF;
```

Version packing: `major * 1_000_000 + minor * 1_000 + patch` (e.g., 0.1.17 → 1017).

### ack_orchestration_item

```diff
  -- Step 7: Update execution status if provided
  ...

+ -- Step 7b: Store pinned duroxide version if provided in metadata
+ IF p_metadata ? 'pinned_duroxide_version' AND p_metadata->'pinned_duroxide_version' IS NOT NULL
+    AND p_metadata->>'pinned_duroxide_version' != 'null' THEN
+     UPDATE %I.executions
+     SET duroxide_version_major = (p_metadata->'pinned_duroxide_version'->>'major')::INTEGER,
+         duroxide_version_minor = (p_metadata->'pinned_duroxide_version'->>'minor')::INTEGER,
+         duroxide_version_patch = (p_metadata->'pinned_duroxide_version'->>'patch')::INTEGER
+     WHERE instance_id = v_instance_id AND execution_id = p_execution_id;
+ END IF;
```

### cleanup_schema

```diff
  -- Added new function signature to cleanup
+ DROP FUNCTION IF EXISTS %I.fetch_orchestration_item(BIGINT, BIGINT, BIGINT, BIGINT);
  DROP FUNCTION IF EXISTS %I.fetch_orchestration_item(BIGINT, BIGINT);
```

## Breaking Changes

None for normal operation. The stored procedure parameters use `DEFAULT NULL` so existing callers (without version filter) continue to work unchanged.

## Rollback

To rollback:
1. Restore the previous `fetch_orchestration_item` from migration 0012 (2-param version)
2. Restore the previous `ack_orchestration_item` from migration 0012
3. The version columns can be left in place (they're nullable and unused without the new procs)
