# Migration 0012 Diff: Require Timestamps

## Summary

Aligns duroxide-pg timestamp handling with duroxide-pg-opt. Updates `ack_orchestration_item` to explicitly provide timestamps, then removes `DEFAULT CURRENT_TIMESTAMP` from all timestamp columns and adds `NOT NULL` constraints.

## Why

- Avoids clock skew between application server and database server
- Enables fault injection for testing (clock manipulation)
- Consistent behavior with duroxide-pg-opt

## Stored Procedure Changes

### ack_orchestration_item

**Signature change**: 7 params → 8 params. `p_now_ms BIGINT` added as 2nd parameter (after `p_lock_token`). Timestamp is now supplied by Rust (app-server clock) instead of computed from database `NOW()`.

```diff
 CREATE OR REPLACE FUNCTION %I.ack_orchestration_item(
     p_lock_token TEXT,
+    p_now_ms BIGINT,
     p_execution_id BIGINT,
     ...
 )
 ...
 DECLARE
     v_instance_id TEXT;
-    v_now_ms BIGINT;
     v_orchestration_name TEXT;
     ...
 BEGIN
-    v_now_ms := (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;
-    v_now_ts := TO_TIMESTAMP(v_now_ms / 1000.0);
+    v_now_ts := TO_TIMESTAMP(p_now_ms / 1000.0);

     -- Lock validation
     SELECT il.instance_id INTO v_instance_id
     FROM %I.instance_locks il
-    WHERE il.lock_token = p_lock_token AND il.locked_until > v_now_ms;
+    WHERE il.lock_token = p_lock_token AND il.locked_until > p_now_ms;
```

Additionally, all INSERT/UPDATE statements now use `v_now_ts` instead of relying on DEFAULT:

```diff
 -- Step 3: Create or update instance metadata
 IF v_orchestration_name IS NOT NULL AND v_orchestration_version IS NOT NULL THEN
-    INSERT INTO %I.instances (instance_id, orchestration_name, orchestration_version, current_execution_id, parent_instance_id)
-    VALUES (v_instance_id, v_orchestration_name, v_orchestration_version, p_execution_id, v_parent_instance_id)
+    INSERT INTO %I.instances (instance_id, orchestration_name, orchestration_version, current_execution_id, parent_instance_id, created_at, updated_at)
+    VALUES (v_instance_id, v_orchestration_name, v_orchestration_version, p_execution_id, v_parent_instance_id, v_now_ts, v_now_ts)
     ON CONFLICT (instance_id) DO NOTHING;

     UPDATE %I.instances i
     SET orchestration_name = v_orchestration_name,
         orchestration_version = v_orchestration_version,
-        parent_instance_id = COALESCE(i.parent_instance_id, v_parent_instance_id)
+        parent_instance_id = COALESCE(i.parent_instance_id, v_parent_instance_id),
+        updated_at = v_now_ts
     WHERE i.instance_id = v_instance_id;
 END IF;

 -- Step 5: Update instance current_execution_id
 UPDATE %I.instances i
-SET current_execution_id = GREATEST(i.current_execution_id, p_execution_id)
+SET current_execution_id = GREATEST(i.current_execution_id, p_execution_id),
+    updated_at = v_now_ts
 WHERE i.instance_id = v_instance_id;

 -- Step 6: Append history_delta (batch insert with explicit timestamps)
 IF p_history_delta IS NOT NULL AND JSONB_ARRAY_LENGTH(p_history_delta) > 0 THEN
-    INSERT INTO %I.history (instance_id, execution_id, event_id, event_type, event_data)
+    INSERT INTO %I.history (instance_id, execution_id, event_id, event_type, event_data, created_at)
     SELECT
         v_instance_id,
         p_execution_id,
         (elem->>'event_id')::BIGINT,
         elem->>'event_type',
-        elem->>'event_data'
+        elem->>'event_data',
+        v_now_ts
     FROM JSONB_ARRAY_ELEMENTS(p_history_delta) AS elem;
 END IF;
```

## Schema Changes

### instances

```diff
- created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ created_at TIMESTAMPTZ NOT NULL

- updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ updated_at TIMESTAMPTZ NOT NULL
```

### executions

```diff
- started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ started_at TIMESTAMPTZ NOT NULL
```

### history

```diff
- created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ created_at TIMESTAMPTZ NOT NULL
```

### orchestrator_queue

```diff
- visible_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ visible_at TIMESTAMPTZ NOT NULL

- created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ created_at TIMESTAMPTZ NOT NULL
```

### worker_queue

```diff
- visible_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ visible_at TIMESTAMPTZ NOT NULL

- created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
+ created_at TIMESTAMPTZ NOT NULL
```

## Breaking Changes

**Stored procedure signature change**: `ack_orchestration_item` now takes 8 parameters (was 7). The `p_now_ms BIGINT` parameter was added as the 2nd parameter. Provider code must pass the current timestamp in milliseconds from Rust.

Direct SQL inserts without explicit timestamps will now fail (but this was never the intended usage pattern).

## Rollback

To rollback, restore the previous `ack_orchestration_item` from migration 0011 and restore defaults:

```sql
ALTER TABLE instances ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE instances ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;
-- etc.
```
