Scope GetLastSnapshot query by teamID to prevent cross-team data access

Problem

The current GetLastSnapshot SQL query (packages/db/queries/snapshots/get_last_snapshot.sql) only filters by sandboxID without considering teamID. This means:

1. Security concern: The database returns snapshot data for any team's sandbox. Ownership is only validated after the query returns, via a post-fetch TeamID != teamID check. This pattern exists in 4 handlers:

   • sandbox_connect.go:119
   • sandbox_get.go:178
   • sandbox_pause.go:97
   • sandbox_resume.go:123

2. Unnecessary data exposure: Even though the post-fetch check prevents unauthorized access, the snapshot data is still loaded from the database and cached in Redis without team scoping. A malicious actor could potentially exploit timing or cache behavior.

3. Cache key collision risk: SnapshotCache uses sandboxID as the sole cache key. If two teams somehow reference the same sandboxID (e.g., during snapshot sharing or migration), the cache could serve stale or incorrect data.

Proposed Solution

1. Add a new SQL query GetLastSnapshotByTeam that includes teamID in the WHERE clause:

   WHERE s.sandbox_id = $1 AND s.team_id = $2
   

2. Add a GetByTeam(ctx, sandboxID, teamID) method to SnapshotCache using a composite cache key (sandboxID:teamID).

3. Migrate the 4 handlers to use the new method, removing the post-fetch ownership checks.

4. Update Invalidate to handle both key formats during the migration period.

Affected Code

• packages/db/queries/snapshots/get_last_snapshot.sql
• packages/api/internal/cache/snapshots/snapshot_cache.go
• packages/api/internal/handlers/sandbox_connect.go
• packages/api/internal/handlers/sandbox_get.go
• packages/api/internal/handlers/sandbox_pause.go
• packages/api/internal/handlers/sandbox_resume.go

References

• Internal tracking: ENG-3544
• All 4 handlers have the same TODO comment: // TODO: ENG-3544 scope GetLastSnapshot query by teamID to avoid post-fetch ownership check.

Start with packages/db/queries/snapshots/get_last_snapshot.sql and packages/api/internal/cache/snapshots/snapshot_cache.go, then trace the TODOs in sandbox_connect.go, sandbox_get.go, sandbox_pause.go, and sandbox_resume.go. Done means team-scoped snapshot queries and cache access are used by all four handlers, ownership checks are removed, and Invalidate supports both cache key formats during migration.