Refactor Deployments to use a many to many for job_agents

[mleonidas]

Problem

The current deployment schema models associated job_agents as a string/JSON array. This does not accurately represent the underlying relationship and introduces unnecessary complexity in querying and maintaining data integrity.

In reality, deployment ↔ job_agent is a many-to-many relationship, which should be modeled explicitly at the database level rather than embedded in a column.

Current schema:

CREATE TABLE IF NOT EXISTS deployment (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL,
  job_agent_id uuid REFERENCES  job_agent(id),
  job_agent_config jsonb NOT NULL,
  resource_selector text,
  workspace_id uuid REFERENCES workspace(id),
  job_agents jsonb NOT NULL
);

Proposed Change

Introduce a join table to properly model the many-to-many relationship

CREATE TABLE IF NOT EXISTS deployment_job_agent (
  deployment_id uuid NOT NULL REFERENCES deployment(id) ON DELETE CASCADE,
  job_agent_id uuid NOT NULL REFERENCES  job_agent(id) ON DELETE CASCADE,
  config jsonb not null default '{}'::jsonb,
  PRIMARY KEY (deployment_id, job_agent_id)
);

Benefits
• Aligns the schema with relational database best practices
• Simplifies querying and indexing
• Improves data integrity (e.g., enforce foreign keys, avoid duplication)
• Removes ambiguity between job_agent_id and job_agents

Migration Plan
1. Create the deployment_job_agents join table
2. Backfill data from the existing job_agents column
3. Remove the job_agents column from deployment
4. Update ORM models to use the many-to-many relationship

Outcome

This change results in a cleaner, more maintainable schema and allows the ORM to correctly represent the relationship between deployments and job agents.

[jsingleton-dev]

Looks good I like this idea