Skip to main content

Database Evolution

The Camera Manager service operates with a high degree of autonomy, including the management of its own database schema. This page details the Declarative Migration System used to ensure the database structure is always aligned with the code.

Why Internal Migrations?​

In a distributed microservices architecture, the camera-manager might be deployed independently of the backend API. To avoid dependency on external migration tools (like AdonisJS Ace or Alembic) and to ensure robust self-healing, the service includes a lightweight, built-in migration engine.

Declarative System​

Instead of writing procedural migration scripts ("do this, then do that"), we define the Desired State of the schema in src/database.py.

# database.py

SCHEMA_EVOLUTION = [
    {
        "table": "running_scripts",
        "add_columns": [
            ("status_updated_at", "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
        ],
        "drop_columns": [
            "runtime_seconds",
        ]
    }
]

How It Works​

On startup, the ensure_schema_updates() function runs automatically:

  1. Inspection: It inspects the information_schema to see what columns currently exist for each table.
  2. Convergence:
    • Missing Columns: If a column in add_columns is missing, it executes an ALTER TABLE ... ADD COLUMN command.
    • Obsolete Columns: If a column in drop_columns exists, it executes an ALTER TABLE ... DROP COLUMN command.
  3. Idempotency: The system checks before acting. If the schema is already correct, it does nothing. This makes it safe to run on every boot.

Migration Process​

  1. Developer adds a new column to SCHEMA_EVOLUTION.
  2. Deployment of the new container image.
  3. Startup: The service detects the schema difference and applies the fix.
  4. Ready: The service starts processing cameras with the correct DB structure.