Flow Coordinate Cache Model

The FlowCoordinateCache model stores cached UI element coordinates to improve automation performance by avoiding repeated computer vision analysis.

Schema Definition

model FlowCoordinateCache {
  id                    String   @id @default(uuid())
  flowId                String
  deviceId              String
  targetElement         String
  coordinates           Json
  screenSize            Json?
  isValid               Boolean  @default(true)
  hitCount              Int      @default(0)
  lastUsedAt            DateTime @default(now())
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt
  normalizedCoordinates Json?
  flow                  Flow     @relation(fields: [flowId], references: [id], onDelete: Cascade)

  @@unique([flowId, deviceId, targetElement])
  @@map("flow_coordinate_cache")
}

Field Descriptions

Primary Keys and Relations

Field	Type	Description
id	String	UUID primary key
flowId	String	Reference to Flow.id
deviceId	String	Device identifier (from Device.deviceId)
targetElement	String	UI element description or identifier

Coordinate Data

Field	Type	Description
coordinates	Json	Raw coordinate data from computer vision
normalizedCoordinates	Json?	Normalized coordinates for different screen sizes
screenSize	Json?	Screen dimensions when coordinates were captured

Status and Tracking

Field	Type	Description
isValid	Boolean	Whether the cached coordinates are still valid (default: true)
hitCount	Int	Number of times this cache entry has been used (default: 0)
lastUsedAt	DateTime	Last time this cache entry was accessed
createdAt	DateTime	Cache entry creation timestamp
updatedAt	DateTime	Last update timestamp

Relationships

Belongs To

• flow: Flow - The flow this cache entry belongs to (Cascade delete)

Coordinate Data Structures

Raw Coordinates

{
  "x": 540,
  "y": 1200,
  "width": 200,
  "height": 60,
  "confidence": 0.95,
  "bbox": [490, 1170, 590, 1230]
}

Normalized Coordinates

{
  "normalizedX": 0.5,
  "normalizedY": 0.5,
  "normalizedWidth": 0.185,
  "normalizedHeight": 0.025,
  "aspectRatio": 1.777,
  "originalScreenSize": {
    "width": 1080,
    "height": 2400
  }
}

Screen Size Information

{
  "width": 1080,
  "height": 2400,
  "density": 440,
  "orientation": "portrait"
}

Usage Examples

Cache UI Element Coordinates

const cacheEntry = await prisma.flowCoordinateCache.create({
  data: {
    flowId: "flow-123",
    deviceId: "emulator-5554",
    targetElement: "login button",
    coordinates: {
      x: 540,
      y: 1200,
      width: 200,
      height: 60,
      confidence: 0.95,
      bbox: [490, 1170, 590, 1230]
    },
    screenSize: {
      width: 1080,
      height: 2400,
      density: 440
    },
    normalizedCoordinates: {
      normalizedX: 0.5,
      normalizedY: 0.5,
      normalizedWidth: 0.185,
      normalizedHeight: 0.025
    }
  }
});

Retrieve Cached Coordinates

const cachedCoords = await prisma.flowCoordinateCache.findUnique({
  where: {
    flowId_deviceId_targetElement: {
      flowId: "flow-123",
      deviceId: "emulator-5554",
      targetElement: "login button"
    }
  }
});

// Update hit count and last used time
if (cachedCoords) {
  await prisma.flowCoordinateCache.update({
    where: { id: cachedCoords.id },
    data: {
      hitCount: { increment: 1 },
      lastUsedAt: new Date()
    }
  });
}

Invalidate Cache for Device

await prisma.flowCoordinateCache.updateMany({
  where: {
    deviceId: "emulator-5554"
  },
  data: {
    isValid: false
  }
});

Clean Old Cache Entries

const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

await prisma.flowCoordinateCache.deleteMany({
  where: {
    OR: [
      { isValid: false },
      { lastUsedAt: { lt: thirtyDaysAgo } }
    ]
  }
});

Query Examples

Get All Cache for Flow

const flowCache = await prisma.flowCoordinateCache.findMany({
  where: {
    flowId: "flow-123",
    isValid: true
  },
  orderBy: { lastUsedAt: "desc" }
});

Get Cache Statistics

const stats = await prisma.flowCoordinateCache.aggregate({
  where: { isValid: true },
  _count: true,
  _sum: {
    hitCount: true
  }
});

Find Most Used Elements

const popularElements = await prisma.flowCoordinateCache.findMany({
  where: { isValid: true },
  orderBy: { hitCount: "desc" },
  take: 10
});

Get Cache by Device

const deviceCache = await prisma.flowCoordinateCache.groupBy({
  by: ["deviceId"],
  where: { isValid: true },
  _count: true,
  _sum: {
    hitCount: true
  }
});

Cache Management Strategies

Cache Invalidation Triggers

• Screen rotation: Different coordinate systems
• App updates: UI layout changes
• Device changes: Different screen sizes/densities
• Manual invalidation: When UI changes are detected

Cache Warming

// Pre-populate cache for common elements
const commonElements = ["login button", "username field", "password field"];

for (const element of commonElements) {
  // Trigger computer vision analysis and cache results
  await analyzeAndCacheElement(flowId, deviceId, element);
}

Cache Performance Monitoring

// Track cache hit rates
const totalRequests = await prisma.flowCoordinateCache.aggregate({
  _sum: { hitCount: true }
});

const cacheHits = totalRequests._sum.hitCount || 0;
// Calculate hit rate vs total vision requests

Normalization Logic

Coordinate Normalization

function normalizeCoordinates(coords, screenSize) {
  return {
    normalizedX: coords.x / screenSize.width,
    normalizedY: coords.y / screenSize.height,
    normalizedWidth: coords.width / screenSize.width,
    normalizedHeight: coords.height / screenSize.height,
    originalScreenSize: screenSize
  };
}

function denormalizeCoordinates(normalizedCoords, targetScreenSize) {
  return {
    x: normalizedCoords.normalizedX * targetScreenSize.width,
    y: normalizedCoords.normalizedY * targetScreenSize.height,
    width: normalizedCoords.normalizedWidth * targetScreenSize.width,
    height: normalizedCoords.normalizedHeight * targetScreenSize.height
  };
}

Cross-Device Compatibility

// Use normalized coordinates for different screen sizes
async function getCompatibleCoordinates(flowId, deviceId, targetElement) {
  const cacheEntry = await prisma.flowCoordinateCache.findUnique({
    where: {
      flowId_deviceId_targetElement: {
        flowId,
        deviceId,
        targetElement
      }
    }
  });

  if (cacheEntry?.normalizedCoordinates) {
    // Use normalized coordinates for current device screen
    const currentScreenSize = await getDeviceScreenSize(deviceId);
    return denormalizeCoordinates(cacheEntry.normalizedCoordinates, currentScreenSize);
  }

  // Fallback to raw coordinates or re-analysis
  return cacheEntry?.coordinates || await analyzeElement(flowId, deviceId, targetElement);
}

Performance Considerations

Indexing

• Composite unique index on (flowId, deviceId, targetElement)
• Index on isValid for filtering valid entries
• Index on lastUsedAt for cleanup operations
• Index on hitCount for popularity analysis

Query Optimization

• Use the composite unique constraint for lookups
• Include isValid: true in WHERE clauses
• Update lastUsedAt and hitCount efficiently
• Use batch operations for bulk updates

Storage Optimization

• Store coordinates as JSON for flexibility
• Compress large coordinate datasets if needed
• Implement data retention policies
• Archive old cache entries

Cache Lifecycle

Creation

1. Computer vision analysis finds UI element
2. Coordinates are calculated and stored
3. Normalized coordinates are computed
4. Cache entry is created with metadata

Usage

1. Automation script requests element coordinates
2. Cache is checked first for valid entries
3. If found, coordinates are returned and usage is tracked
4. If not found, computer vision analysis is performed

Maintenance

1. Periodic cleanup removes invalid/old entries
2. Usage statistics are monitored for optimization
3. Cache warming pre-populates common elements
4. Invalidation handles UI/layout changes

Cleanup

1. Remove entries older than retention period
2. Invalidate entries for changed screens/devices
3. Archive rarely used entries
4. Compact storage by removing redundant data

Best Practices

Cache Strategy

• Cache frequently used elements
• Invalidate when UI changes are detected
• Use normalized coordinates for device compatibility
• Monitor cache hit rates and adjust strategy

Data Integrity

• Validate coordinates before caching
• Implement checksums for coordinate data
• Handle screen size changes gracefully
• Provide fallbacks when cache is unavailable

Monitoring

• Track cache hit/miss ratios
• Monitor coordinate accuracy over time
• Alert on high cache invalidation rates
• Log cache performance metrics

Security

• Don't cache sensitive UI element data
• Implement access controls for cache operations
• Regularly audit cached coordinate data
• Use encryption for stored coordinate data if needed