import * as Effect from "effect/Effect";
import * as SqlClient from "effect/sql/SqlClient";
// Legacy NOT NULL provider columns remain write-through shadows; V2 reads explicit driver/instance columns.
export default Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`ALTER TABLE orchestration_v2_events ADD COLUMN driver TEXT`;
yield* sql`ALTER TABLE orchestration_v2_events ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_events
SET
driver = COALESCE(json_extract(payload_json, '$.driver'), provider),
provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`CREATE INDEX orchestration_v2_events_instance_sequence_idx ON orchestration_v2_events(provider_instance_id, sequence)`;
yield* sql`ALTER TABLE orchestration_v2_projection_threads ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_threads
SET provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
default_provider
)
`;
yield* sql`ALTER TABLE orchestration_v2_projection_runs ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_runs
SET provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`ALTER TABLE orchestration_v2_projection_run_attempts ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_run_attempts
SET provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`ALTER TABLE orchestration_v2_projection_provider_sessions ADD COLUMN driver TEXT`;
yield* sql`ALTER TABLE orchestration_v2_projection_provider_sessions ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_provider_sessions
SET
driver = COALESCE(json_extract(payload_json, '$.driver'), provider),
provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`CREATE INDEX orchestration_v2_projection_provider_sessions_instance_status_idx ON orchestration_v2_projection_provider_sessions(provider_instance_id, status)`;
yield* sql`ALTER TABLE orchestration_v2_projection_provider_threads ADD COLUMN driver TEXT`;
yield* sql`ALTER TABLE orchestration_v2_projection_provider_threads ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_provider_threads
SET
driver = COALESCE(json_extract(payload_json, '$.driver'), provider),
provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`CREATE INDEX orchestration_v2_projection_provider_threads_instance_status_idx ON orchestration_v2_projection_provider_threads(provider_instance_id, status)`;
yield* sql`ALTER TABLE orchestration_v2_projection_subagents ADD COLUMN driver TEXT`;
yield* sql`ALTER TABLE orchestration_v2_projection_subagents ADD COLUMN provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_subagents
SET
driver = json_extract(payload_json, '$.driver'),
provider_instance_id = COALESCE(
json_extract(payload_json, '$.providerInstanceId'),
provider
)
`;
yield* sql`ALTER TABLE orchestration_v2_projection_context_transfers ADD COLUMN source_provider_instance_id TEXT`;
yield* sql`ALTER TABLE orchestration_v2_projection_context_transfers ADD COLUMN target_provider_instance_id TEXT`;
yield* sql`
UPDATE orchestration_v2_projection_context_transfers
SET
source_provider_instance_id = COALESCE(
json_extract(payload_json, '$.sourceProviderInstanceId'),
source_provider
),
target_provider_instance_id = COALESCE(
json_extract(payload_json, '$.targetProviderInstanceId'),
target_provider
)
`;
yield* sql`
CREATE TABLE orchestration_v2_effect_outbox (
effect_id TEXT PRIMARY KEY,
command_id TEXT NOT NULL,
thread_id TEXT NOT NULL,
effect_type TEXT NOT NULL,
payload_json TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'succeeded', 'failed')),
attempt_count INTEGER NOT NULL DEFAULT 0,
available_at TEXT NOT NULL,
lease_owner TEXT,
lease_expires_at TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
completed_at TEXT,
last_error TEXT
)
`;
yield* sql`CREATE INDEX orchestration_v2_effect_outbox_claim_idx ON orchestration_v2_effect_outbox(status, available_at, lease_expires_at, created_at)`;
yield* sql`CREATE INDEX orchestration_v2_effect_outbox_command_idx ON orchestration_v2_effect_outbox(command_id, effect_id)`;
yield* sql`
CREATE TABLE orchestration_v2_turn_item_positions (
thread_id TEXT NOT NULL,
turn_item_id TEXT NOT NULL,
ordinal INTEGER NOT NULL,
PRIMARY KEY (thread_id, turn_item_id),
UNIQUE (thread_id, ordinal)
)
`;
yield* sql`
INSERT INTO orchestration_v2_turn_item_positions (
thread_id,
turn_item_id,
ordinal
)
SELECT
item.thread_id,
item.turn_item_id,
COALESCE(run.ordinal, 0) * 1000000 + ROW_NUMBER() OVER (
PARTITION BY item.thread_id, COALESCE(run.ordinal, 0)
ORDER BY item.ordinal, item.turn_item_id
)
FROM orchestration_v2_projection_turn_items AS item
LEFT JOIN orchestration_v2_projection_runs AS run
ON run.thread_id = item.thread_id
AND run.run_id = item.run_id
`;
yield* sql`
CREATE TABLE orchestration_v2_projection_metadata (
projection_name TEXT PRIMARY KEY,
schema_version INTEGER NOT NULL,
last_sequence INTEGER NOT NULL,
updated_at TEXT NOT NULL
)
`;
yield* sql`
INSERT INTO orchestration_v2_projection_metadata (
projection_name,
schema_version,
last_sequence,
updated_at
)
VALUES (
'thread-projections',
1,
COALESCE((SELECT MAX(sequence) FROM orchestration_v2_events), 0),
strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
)
`;
});