…#2351)

* fix(db): Guard against inactive transaction during async cleanup

When registering MCP servers with long initialization times (like Moody's),
a CancelledError can occur during the MCP session teardown (DELETE request).
This causes the database transaction to become inactive before get_db()
attempts to commit, resulting in:

  sqlalchemy.exc.InvalidRequestError: This transaction is inactive

Add db.is_active checks before commit() and rollback() to handle cases
where the transaction becomes inactive during async context manager cleanup.

Closes IBM#2341

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* fix: Add upsert logic for resources and prompts to prevent unique constraint violations

When re-registering a gateway (e.g., after deletion or crash), orphaned resources
and prompts from previous registrations could cause unique constraint violations
on `(team_id, owner_email, uri)` for resources and `(team_id, owner_email, name)`
for prompts.

This fix adds upsert logic that:
1. Queries for existing resources/prompts matching the unique constraint
2. Updates existing records instead of creating duplicates
3. Creates new records only when no match exists

This handles scenarios like:
- Gateway deletion that didn't properly clean up resources (issue IBM#2341)
- Re-registration of the same MCP server under a new gateway name
- Race conditions during concurrent operations

Closes IBM#2352

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* chore: Add cleanup script for orphaned resources/prompts/tools

Adds a utility script to identify and remove database records that were
left orphaned due to incomplete gateway deletions (e.g., IBM#2341 crash).

Usage:
  # Dry run (default) - shows what would be deleted
  python scripts/cleanup_orphaned_resources.py

  # Actually delete orphaned records
  python scripts/cleanup_orphaned_resources.py --execute

  # Filter by team or owner
  python scripts/cleanup_orphaned_resources.py --team-id <id> --owner-email <email>

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* fix: Only upsert orphaned resources/prompts, add tests

Addresses code review findings:

1. HIGH: Only update truly orphaned records (gateway_id IS NULL or points
   to non-existent gateway). Resources belonging to active gateways are
   no longer at risk of being reassigned.

2. MEDIUM: Use per-resource team/owner overrides when building lookup key,
   matching exactly what would be inserted to avoid constraint mismatches.

3. LOW: Added tests for orphaned resource upsert logic:
   - test_register_gateway_updates_orphaned_resources
   - test_register_gateway_does_not_update_active_gateway_resources

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* fix: Always call rollback() in exception handler, improve tests

Addresses remaining code review findings:

1. ROLLBACK GUARD FIX:
   - REMOVED the `if db.is_active:` check before `db.rollback()`
   - Empirical testing proved that:
     * After IntegrityError, is_active becomes False
     * rollback() when is_active=False SUCCEEDS (doesn't fail!)
     * rollback() restores is_active to True, cleaning up the session
     * Skipping rollback when is_active=False leaves session unusable
   - The is_active guard for commit() is CORRECT (commit fails when False)
   - The is_active guard for rollback() was WRONG (rollback is always safe)

2. TEST ASSERTIONS:
   - Rewrote orphaned resource tests with proper assertions
   - Tests now directly verify:
     * Orphaned resources are detected and added to map
     * Resource fields are actually updated during upsert
     * Resources with deleted gateways are detected as orphaned
     * Resources with active gateways are NOT touched
     * Per-resource owner/team overrides are used in lookup key

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* chore: Add file encoding header to cleanup script

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

---------

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>