[Gateway] Composite Key & UUIDs for Tool Identity

Type: Migration & Feature   
Priority: High (blocks multi‑gateway refresh)   
Labels: backend, database, breaking-change, migration, needs-discussion

📜 Problem Statement

Two different gateways can legitimately expose tools with the same name (e.g. google). Our current schema forces the column tools.name to be globally unique; consequently:

• Adding a second gateway that offers a google tool fails.
• Deleting one gateway cascades and deletes the google row for that gateway, but does not cause the addition of google row from existing gateways

This blocks federation scenarios and corrupts internal tool catalogs.

Potential issue to confirm: the same might be true of Prompts and Resources.

🎯 Objective

1. Uniquely identify tools by their gateway of origin + name instead of name alone.
2. Enable lossless deletion & re‑addition of gateways without manual cleanup.
3. Introduce UUID primary keys (opt‑in) to future‑proof URL‑safe references and decouple UI numbering from DB IDs.

💡 Proposed Solution (High‑Level)

• Composite uniqueness on (gateway_id, name) in tools table.
• UUID id columns for Gateways, Tools, and Servers (generated by DB).
• Add a generated, indexed column qualified_name = <gateway_slug>.<tool_name> for human readability and legacy look‑ups.
• Update ORM, service layer, APIs, and Admin UI accordingly.
• REST tools also should work with this approach since the qualifiedName uses UUID ID of gateway and name of tool.

🔀 Implementation Options

(Team consensus leans toward B to avoid a second disruptive migration later.)

🛠️ Detailed Implementation (Option B)

1. Schema / Alembic Migration

-- 1. Enable UUIDs (PostgreSQL only once)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 2. Add UUID PKs
ALTER TABLE gateways ADD COLUMN id uuid PRIMARY KEY DEFAULT gen_random_uuid();
ALTER TABLE tools    ADD COLUMN id uuid PRIMARY KEY DEFAULT gen_random_uuid();
ALTER TABLE servers  ADD COLUMN id uuid PRIMARY KEY DEFAULT gen_random_uuid();

-- 3. Drop global uniqueness on name
ALTER TABLE tools DROP CONSTRAINT IF EXISTS tools_name_key;

-- 4. Composite unique within gateway scope
ALTER TABLE tools ADD CONSTRAINT tools_gateway_name_key UNIQUE (gateway_id, name);

-- 5. Generated qualified name (stored)
ALTER TABLE tools ADD COLUMN qualified_name text
  GENERATED ALWAYS AS (
    (SELECT slug FROM gateways g WHERE g.id = gateway_id) || '.' || name
  ) STORED;

-- 6. Index for fast look‑ups
CREATE INDEX ix_tools_qualified_name ON tools(qualified_name);

2. ORM & Service Layer

• Remove unique=True on Tool.name in SQLAlchemy model.
• Add qualified_name read‑only attribute.
• Everywhere we fetch tools now filters with (gateway_id, name); fall back to qualified_name or tool_id if provided.
• Remove unique=True on Gateway.name in SQLAlchemy model
• Add gateway_slug filed to Gateway SQLAlchemy model and add unique=True to this field

3. API Contract

• Requests can specify a tool by id or { gatewayId, name } pair.
• Responses return qualifiedName for display and logs.
• Deprecate plain‑name calls (/tools/{name}) over 2 releases.

4. Admin UI Updates

• Display a Gateway column on the Tools table.
• Sort tools by qualifiedName by default.
• Surfaced IDs switch to UUID; still copy‑to‑clipboard friendly.

5. Migration Process

1. Feature flag: ship read‑path logic behind COMP_KEY_TOOLS (false by default).
2. Deploy; run migration in maintenance window.
3. Backfill UUIDs + qualified_name.
4. Flip flag → write‑path now uses composite key + UUIDs.
5. Monitor for 1 release; remove flag.

6. Rollback Plan

• Keep full up‑migration & down‑migration scripts.
• If issues: disable flag, restore DB snapshot, redeploy previous build.

✔️ Acceptance Criteria

• Add two gateways with duplicate tool names → no constraint error.
• Delete one gateway → only its tools removed; others untouched.
• Re‑add gateway → original tool links for servers restored.
• All unit & integration tests pass (including three refresh scenarios).
• Admin UI shows unique qualifiedName values; pagination & search unaffected.

⚠️ Risks & Mitigations

❓ Open Discussion Questions

1. Client Compatibility – Which external integrations reference tools solely by name? Migration guidance?
2. Slug Governance – How do we derive or manage gateway.slug (URL hash vs. user input)?
3. Qualified Name Format – Use dot (gateway.tool) or slash (gateway/tool) for clarity / URL safety?
4. Metric Dashboards – Do monitoring IDs break once tools switch to UUIDs? Plan for relabeling?
5. Testing Strategy – Should we stand up a temporary shadow DB & run canaries before prod rollout?

📋 Tasks

• Author Alembic migration (Option B).
• Update SQLAlchemy models & db.py.
• Refactor service queries (gateway_service, tool_service, forward).
• Extend Pydantic schemas (schemas.py) with gatewayId, qualifiedName.
• Adapt Admin UI tables & forms.
• Add tests for duplicated names, deletion, re‑addition.
• Update docs, CHANGELOG, and client examples.
• Schedule production migration window.

Once this issue is approved, we’ll merge the migration PR behind a feature flag and begin phased rollout.