Maintenance Robot Architect

What problem does it solve?

Ensures that the orchestrator's automated database migration system applies required schema changes and seed data reliably on every fresh deployment, eliminating manual SQL steps and startup failures caused by missing tables, seeds, or columns.

Core Features & Use Cases

• Idempotent Migrations: Enforces CREATE TABLE IF NOT EXISTS and seed inserts with ON CONFLICT handling to prevent duplicate operations.
• Atomic Per-Step Execution: Executes each migration step as an individual transaction with soft-fail logging and continuation unless a critical table is missing.
• Conditional Seeds & Safe Alters: Supports PL/pgSQL anonymous blocks for conditional inserts and uses guarded ALTER patterns to avoid errors when columns already exist.
• Use Case: Add a new OAuth provider or a new column to an existing model and have the orchestrator apply the change automatically on startup without manual intervention.

Quick Start

Add a new SQL migration step to migration_steps in orchestrator_service/main.py following the idempotency and atomicity rules described.