Data Structure

The Metadata Cleanup service operates on a dual-table design — a metadata table optimized for reads and an index table optimized for time-based cleanup. Understanding this structure is key to understanding why the cleanup job works the way it does.

Metadata Table (Device-Partitioned)

Each image type stores its metadata in a dedicated Azure Table Storage table:

Table	Image Type
SingleImageMetadata	Single camera images
PanoImageMetadata	Panoramic (360°) images
PreStitchImageMetadata	Pre-stitched archive images

All three tables share the same partitioning strategy:

• PartitionKey = deviceId (the camera/instance ID)
• RowKey = inverted ticks (DateTimeOffset.MaxValue.Ticks - timestamp.Ticks), formatted as a zero-padded 19-digit string

Why Device Partitioning?

This partition scheme is optimized for the hot read path — querying metadata for a specific camera. When the frontend or API needs to display images for a device, that query hits a single partition, which Azure Table Storage serves as a fast point read. All metadata for one camera lives together, making reads efficient regardless of how many total cameras exist in the system.

The inverted-tick RowKey ensures the most recent images sort first within each partition. This is critical because the most common access pattern is "show me the latest images from this camera."

The Problem: Time-Based Cleanup

Device partitioning is ideal for reads, but it creates a challenge for cleanup. The cleanup job needs to answer a different question: "Which records across all devices are older than the retention period?"

In Azure Table Storage:

• Batch deletes require all entities in a transaction to share the same PartitionKey
• Cross-partition queries (scanning all device partitions to find old records) are expensive full-table scans
• There's no secondary index to efficiently query by time across partitions

Without a solution, the cleanup job would need to scan every device partition individually to find expired records — an operation that gets slower as the number of cameras grows.

Index Table (Time-Partitioned)

To solve this, each metadata table has a corresponding index table:

Index Table	Metadata Table
SingleImageMetadataIndex	SingleImageMetadata
PanoImageMetadataIndex	PanoImageMetadata
PreStitchImageMetadataIndex	PreStitchImageMetadata

The index table uses the opposite partitioning strategy:

• PartitionKey = yyyyMMddHHmm (timestamp, formatted to the minute)
• RowKey = the corresponding metadata row key
• DeviceId = stored as a property, referencing back to the metadata table's PartitionKey

Why Time Partitioning?

This lets the cleanup job efficiently query by time range. Finding all records older than a cutoff is a simple partition key comparison — no cross-partition scan required. The index table acts as a time-ordered lookup into the device-partitioned metadata.

Write Path

When a new image is captured, both tables are written to:

1. A row is inserted into the metadata table with PartitionKey = deviceId
2. A corresponding row is inserted into the index table with PartitionKey = yyyyMMddHHmm and DeviceId = deviceId

This small write overhead enables efficient cleanup later.

How Cleanup Uses Both Tables

The cleanup job leverages the dual-table design in a two-phase process:

Phase 1: Find Expired Records via Index Table

The timer function calculates a cutoff time (now minus the retention period) and queries the index table for all partitions older than that cutoff:

Index PartitionKey < cutoff (yyyyMMddHHmm)

This is efficient because it reads contiguous time-based partitions rather than scanning across device partitions. The results include the DeviceId for each expired record.

Phase 2: Delete from Both Tables by Device

The results are grouped by DeviceId, and one queue message is sent per device. Each queue processor then:

1. Deletes from the metadata table — queries the device's partition for rows older than the cutoff and batch-deletes them. Since all rows share the same PartitionKey (the device ID), batch deletes are transactional and fast.
2. Deletes from the index table — removes the corresponding index entries for that device.

Benefits

Concern	Without Index Table	With Index Table
Read performance	Fast (device-partitioned)	Fast (unchanged)
Finding expired records	Full table scan across all device partitions	Single time-range query on index partitions
Batch deletes	Must scan each device partition individually	Targeted: only touch devices that have expired data
Scaling with more cameras	Cleanup slows linearly with camera count	Cleanup time depends on volume of expired data, not camera count

Table Summary

Table	PartitionKey	RowKey	Purpose
SingleImageMetadata	deviceId	Inverted ticks	Store single image metadata, optimized for per-device reads
PanoImageMetadata	deviceId	Inverted ticks	Store panoramic image metadata, optimized for per-device reads
PreStitchImageMetadata	deviceId	Inverted ticks	Store pre-stitch image metadata, optimized for per-device reads
SingleImageMetadataIndex	yyyyMMddHHmm	Metadata row key	Time-based index for efficient single image cleanup
PanoImageMetadataIndex	yyyyMMddHHmm	Metadata row key	Time-based index for efficient panoramic image cleanup
PreStitchImageMetadataIndex	yyyyMMddHHmm	Metadata row key	Time-based index for efficient pre-stitch image cleanup