Modeling Pipeline State in D1

Track stage state in normalized tables, not only in workflow memory.

Core Tables

• videos: stage statuses (selection_status, script_status, asset_status, render_status, youtube_upload_status).
• workflow_runs: durable run state for active/cancel/correlation.
• video_assets: all generated artifacts with asset_type and asset_index.
• cost_logs: per-operation costs and token usage.
• policy_runs + policy_findings: staged policy results.

Run Tracking

INSERT OR IGNORE INTO workflow_runs (
  workflow_id, stage, video_id, parent_workflow_id, trigger_source, status, config_json
) VALUES (?, ?, ?, ?, ?, 'queued', ?)

Upsert Edge Case in D1

ON CONFLICT DO UPDATE can return last_row_id=0. Fallback by selecting row ID explicitly:

SELECT id FROM article_versions WHERE article_id = ? AND version = ?

Recommended Status Rules

• Always write "in progress" before external calls.
• Move to terminal states only once.
• Preserve error reason on failures.
• Do not let downstream stage overwrite upstream decision fields.