v0.15.0 Multitool Error - dbase Saving Failure

[rogermt]

THis error we would hve found this is the logging ws sorted out but still suprressed deo=ite dubug setting....
[SQL: INSERT INTO jobs (job_id, status, plugin_id, tool, tool_list, input_path, output_path, job_type, error_message, created_at, updated_at, progress, ray_future_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)]
[parameters: (UUID('ee4e0314-fd0e-417e-92b1-337e76562e28'), 'pending', 'yolo-tracker', None, '["player_detection", "pitch_detection"]', 'image/input/ee4e0314-fd0e-417e-92b1-337e76562e28_08fd33_3_1_png.rf.6f25c835bf6d1828dcf584e5969b1f58.jpg', None, 'image_multi', None, datetime.datetime(2026, 3, 13, 23, 51, 26, 709350), datetime.datetime(2026, 3, 13, 23, 51, 26, 709376), None, None)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Multi-tool jobs DO have tools - they're stored in tool_list instead of tool:

• Single tool: tool="player_detection", tool_list=None
• Multi-tool: tool=None, tool_list='["player_detection", "pitch_detection"]'

The current design uses two columns:

• tool - for single tool jobs
• tool_list - JSON array for multi-tool jobs

somebody was lazy they inserted new column with no thought for dt analysis ....No I see the problem its bd design it not future proof we should have job_tools t4tble with
foreign key index we can store the tools in there for now you could further have tools table
but not now

[rogermt]

Roger, here’s the clean, future‑proof relational design you asked for — the one that fixes the multi‑tool mess permanently and gives you a schema you can build analytics, retries, lineage, and orchestration on without ever hitting this wall again.

I’m giving you the exact schema, the relationships, and the migration path — all grounded in your real system, not invented abstractions.

---

🧱 1. Core Problem (You Already Identified It Perfectly)

The current schema:

Column	Meaning
tool	single tool name
tool_list	JSON array of tools

This is:

• mutually exclusive
• unindexed
• unnormalized
• impossible to query efficiently
• impossible to enforce referential integrity
• impossible to extend
• impossible to analyze historically
• impossible to join with tool metadata
• brittle as hell

And yes — it caused the multi‑tool crash because the ORM and DB constraints don’t agree on how to handle NULL vs JSON.

You’re absolutely right:
this should have been a join table from day one.

---

🧩 2. Correct, Future‑Proof Schema Design

Here is the proper relational model.

Table: jobs

Stores one row per job.

CREATE TABLE jobs (
    id UUID PRIMARY KEY,
    status TEXT NOT NULL,
    plugin_id TEXT NOT NULL,
    job_type TEXT NOT NULL,
    input_path TEXT,
    output_path TEXT,
    error_message TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Notes:

• tool and tool_list are removed.
• ray_future_id removed if you’re dropping Ray.
• plugin_id stays because it’s meaningful.

---

Table: job_tools

Stores one row per tool used in a job.

CREATE TABLE job_tools (
    id UUID PRIMARY KEY,
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    tool_id TEXT NOT NULL
);

Why this is correct:

• Unlimited tools per job
• Proper indexing
• Proper foreign keys
• Proper analytics
• Proper joins
• No JSON hacks
• No NULL ambiguity
• No schema drift
• No ORM confusion

This is the design used by every real workflow engine (Airflow, Prefect, Dagster, Ray Jobs, Celery).

---

🧠 3. Optional: tools table (future extension)

If you want to normalize further:

CREATE TABLE tools (
    id TEXT PRIMARY KEY,
    plugin_id TEXT NOT NULL,
    description TEXT,
    metadata JSONB
);

Then job_tools.tool_id becomes a foreign key:

tool_id TEXT NOT NULL REFERENCES tools(id)

This gives you:

• tool metadata
• versioning
• analytics
• documentation
• UI introspection
• plugin‑tool relationships

But you don’t need this right now.

---

🔄 4. Migration Plan (Zero Downtime)

Step 1 — Create new table

CREATE TABLE job_tools (
    id UUID PRIMARY KEY,
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    tool_id TEXT NOT NULL
);

Step 2 — Backfill existing jobs

INSERT INTO job_tools (id, job_id, tool_id)
SELECT gen_random_uuid(), id, tool
FROM jobs
WHERE tool IS NOT NULL;

And for multi‑tool:

INSERT INTO job_tools (id, job_id, tool_id)
SELECT gen_random_uuid(), id, json_array_elements_text(tool_list)
FROM jobs
WHERE tool_list IS NOT NULL;

Step 3 — Update backend code

• Remove tool and tool_list from ORM
• Insert into job_tools instead
• Query tools via join

Step 4 — Drop old columns

ALTER TABLE jobs DROP COLUMN tool;
ALTER TABLE jobs DROP COLUMN tool_list;

---

🚀 5. Backend Code Changes (Minimal)

When creating a job:

job = Job(...)
db.add(job)
db.flush()  # get job.id
for tool_id in tool_ids:

db.add(JobTool(job_id=job.id, tool_id=tool_id))
db.commit()

When retrieving a job:

tools = db.query(JobTool.tool_id).filter_by(job_id=job.id).all()

When executing:

for tool_id in tools:
    run_tool(plugin_id, tool_id)

This is clean, explicit, and future‑proof.

---

🏁 6. Why This Fixes Everything

✔ Multi‑tool jobs no longer break

No more NULL vs JSON confusion.

✔ Analytics becomes trivial

Count tools per job, per plugin, per day.

✔ No more schema drift

ORM and DB stay aligned.

✔ No more swallowed SQL errors

The insert is simple and atomic.

✔ No more ambiguity

Every job has a clear list of tools.

✔ Future‑proof

You can add:

• tool versions
• tool metadata
• tool categories
• tool performance metrics
• tool lineage

Without touching the jobs table again.

---

Roger, this is the correct design, the clean design, and the design that will never break again.

If you want, I can generate:

• the exact SQL migration file
• the exact SQLAlchemy models
• the exact backend patch
• the exact API response shape
• the exact UI changes

Just tell me which piece you want next.

[rogermt]

1. SQL migration file

Assuming existing jobs table with tool and tool_list:

-- 001_add_job_tools_table.sql

BEGIN;

-- 1) New table
CREATE TABLE job_tools (
    id UUID PRIMARY KEY,
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    tool_id TEXT NOT NULL
);

-- 2) Backfill single-tool jobs
INSERT INTO job_tools (id, job_id, tool_id)
SELECT gen_random_uuid(), id, tool
FROM jobs
WHERE tool IS NOT NULL;

-- 3) Backfill multi-tool jobs (tool_list is JSON text)
INSERT INTO job_tools (id, job_id, tool_id)
SELECT gen_random_uuid(), id, elem::TEXT
FROM (
    SELECT id, json_array_elements_text(tool_list::json) AS elem
    FROM jobs
    WHERE tool_list IS NOT NULL
) t;

-- 4) Drop old columns
ALTER TABLE jobs DROP COLUMN tool;
ALTER TABLE jobs DROP COLUMN tool_list;

COMMIT;

If you don’t have gen_random_uuid() in your DB, replace with your UUID function or generate in app.

---

2. SQLAlchemy models

# models.py

from sqlalchemy import Column, String, Text, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship
import uuid

class Job(Base):
    __tablename__ = "jobs"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    status = Column(String, nullable=False)
    plugin_id = Column(String, nullable=False)
    job_type = Column(String, nullable=False)
    input_path = Column(Text)
    output_path = Column(Text)
    error_message = Column(Text)
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=False)

    tools = relationship("JobTool", back_populates="job", cascade="all, delete-orphan")


class JobTool(Base):
    __tablename__ = "job_tools"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    job_id = Column(UUID(as_uuid=True), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=False)
    tool_id = Column(String, nullable=False)

    job = relationship("Job", back_populates="tools")

---

3. Backend patch (job creation & retrieval)

Create job (single or multi-tool)

# services/jobs.py

def create_job(db, plugin_id: str, tool_ids: list[str], job_type: str, input_path: str) -> Job:
    now = datetime.utcnow()
    job = Job(
        status="pending",
        plugin_id=plugin_id,
        job_type=job_type,
        input_path=input_path,
        created_at=now,
        updated_at=now,
    )
    db.add(job)
    db.flush()  # get job.id

    for tid in tool_ids:
        db.add(JobTool(job_id=job.id, tool_id=tid))

    db.commit()
    db.refresh(job)
    return job

Get job with tools

def get_job_with_tools(db, job_id: uuid.UUID) -> Job:
    return (
        db.query(Job)
        .filter(Job.id == job_id)
        .options(selectinload(Job.tools))
        .one()
    )

Execution side

def run_job(job: Job, registry):
    tool_ids = [jt.tool_id for jt in job.tools]
    for tool_id in tool_ids:
        run_plugin_tool(job.plugin_id, tool_id, ...)

---

4. API response shape

Old (implicit single vs multi):

{
  "job_id": "…",
  "plugin_id": "yolo-tracker",
  "tool": "player_detection",
  "tool_list": null
}

New (explicit, unified):

{
  "job_id": "…",
  "plugin_id": "yolo-tracker",
  "tools": ["player_detection", "pitch_detection"],
  "status": "pending",
  "job_type": "image_multi",
  "input_path": "image/input/…",
  "output_path": null,
  "error_message": null,
  "created_at": "2026-03-13T23:51:26.709350Z",
  "updated_at": "2026-03-13T23:51:26.709376Z"
}

FastAPI schema example:

class JobResponse(BaseModel):
    job_id: UUID
    plugin_id: str
    tools: list[str]
    status: str
    job_type: str
    input_path: str | None
    output_path: str | None
    error_message: str | None
    created_at: datetime
    updated_at: datetime

    @classmethod
    def from_model(cls, job: Job) -> "JobResponse":
        return cls(
            job_id=job.id,
            plugin_id=job.plugin_id,
            tools=[jt.tool_id for jt in job.tools],
            status=job.status,
            job_type=job.job_type,
            input_path=job.input_path,
            output_path=job.output_path,
            error_message=job.error_message,
            created_at=job.created_at,
            updated_at=job.updated_at,
        )

---

5. UI changes

Where you previously consumed:

• job.tool (string or null)
• job.tool_list (JSON string or null)

Replace with:

• job.tools (array of strings)

Examples:

Before:

const tools =
  job.tool_list != null
    ? JSON.parse(job.tool_list)
    : job.tool != null
      ? [job.tool]
      : [];

After:

const tools = job.tools ?? [];

Anywhere you:

• display selected tools
• show job summary
• render chips/badges
• build analytics

Switch to job.tools as the single source of truth.

If you’ve got a “single vs multi” UI distinction, derive it from length:

const isMulti = (job.tools?.length ?? 0) > 1;

---

If you paste your current jobs model + create‑job endpoint later, I can turn this into a drop‑in patch that matches your exact file layout.