Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Guest Photo Gallery with SMS Integration: Deduplication and Format Handling

What We Built

A guest-accessible photo gallery system that accepts uploads from multiple sources — web browsers and SMS — with automatic deduplication, format normalization, and verification. The workflow handles the case where users may send photos via SMS to a central message broker (in this case, a JADA SMS line), and those attachments need to be extracted, deduplicated, converted to web-friendly formats, and uploaded to a gallery tied to a specific guest code.

The Problem

A guest attempted to upload 20 photos to an event gallery via SMS. The upload initially failed, likely due to user error or client-side validation issues with the photo picker. Meanwhile, other users successfully uploaded 6 photos via the web interface. The question: after uploading the 20 SMS photos, why did the gallery show 23 total instead of the expected 26?

Technical Architecture

Data Sources

  • Messages Database: SMS conversations are stored in a local chat.db SQLite database (on macOS, typically ~/Library/Messages/chat.db). Each message has associated file attachments with paths to HEIC/JPEG files.
  • DynamoDB Event Registry: Guest codes and event metadata are stored in DynamoDB across multiple AWS regions (us-west-2, etc.). Each event has a unique guest_code that authorizes photo uploads to that gallery.
  • S3 + CloudFront: Photos are stored in S3 and distributed via CloudFront for low-latency access to the guest-facing gallery page.

Lambda Upload Handler

The shipcaptaincrew Lambda function handles incoming photo uploads. It validates the guest_code against the DynamoDB table, stores approved photos in S3, and returns a response indicating success or failure. The function uses environment variables to reference:

  • DynamoDB table name (e.g., events or similar)
  • S3 bucket name for gallery photos
  • CloudFront distribution ID for cache invalidation

The Workflow

Step 1: Extract SMS Attachments

Query the Messages database for attachments from the sender's handle (phone number) since a specific timestamp. The SQL query looks roughly like this:

SELECT
  m.guid,
  m.date,
  a.filename
FROM message AS m
JOIN message_attachment_join AS maj ON m.ROWID = maj.message_id
JOIN attachment AS a ON maj.attachment_id = a.ROWID
WHERE m.handle_id = (SELECT ROWID FROM handle WHERE id = '')
  AND m.date > 
ORDER BY m.date ASC;

Why SQLite queries? The Messages database is a local system resource; querying it directly is faster and more reliable than extracting through the iOS Photos app or relying on iCloud sync, which can be lossy or incomplete.

Step 2: Deduplicate by Content Hash

When Angelia sent 20 photos in two batches (10 at 7:33 PM, 10 at 8:07 PM), three photos appeared in both batches — likely because she re-selected the same shots from her camera roll when retrying the upload. We:

  • Computed MD5 hashes of file contents for all 20 photos
  • Identified 3 files with duplicate hashes
  • Kept only 17 unique files

Why content hash, not filename? A filename like IMG_1712.HEIC could be copied, moved, or regenerated on export. MD5 checksums detect true duplicates regardless of metadata, timestamps, or storage location.

Step 3: Convert HEIC to JPEG

Apple's HEIC format is efficient but not universally supported in web browsers (especially older ones and non-Safari clients). We batch-converted HEIC files to JPEG using the system sips tool:

for file in *.HEIC; do
  sips -s format jpeg "$file" --out "${file%.*}.jpeg"
done

Why sips? It's a native macOS tool with good performance and preserves EXIF metadata (orientation, camera model) that guests may want to see. It's simpler than invoking ImageMagick or maintaining a separate image processing service for this use case.

Step 4: Upload via Guest-Authenticated API

Each photo is POSTed to the Lambda endpoint with the guest_code. The Lambda:

  • Looks up the guest_code in DynamoDB to find the event and gallery ID
  • Validates that the guest_code exists and hasn't expired
  • Stores the photo in S3 at a path like s3://gallery-bucket/events//photos/
  • Invalidates the CloudFront cache for the gallery listing
  • Returns 200 OK with a success response

Step 5: Verify Upload and Report Gallery Count

Query the gallery API (or list S3 objects) to verify:

  • All 17 new files are present in the S3 bucket
  • The total count (23 photos) is correct: 6 original + 17 new

Key Decisions and Tradeoffs

MD5 vs. Perceptual Hash

We used cryptographic hashing (MD5) rather than perceptual hashing. In this case, exact duplicate detection was sufficient — Angelia had re-sent the identical files, not similar crops or recompressed versions. Perceptual hashing would add complexity and potential false negatives (failing to dedupe truly identical files) without benefit here.

Local Processing vs. Cloud Processing

Deduplication, format conversion, and upload were orchestrated locally (on the operator's machine) rather than in a Lambda. Why?

  • Cost: Batch processing 20 images locally is cheaper than spinning up a Lambda, downloading files, processing, and re-uploading.
  • Latency: No network round-trips for extraction; conversion happens at local SSD speed.
  • Debugging: Operator can inspect the deduplicated file list and verify conversions before upload, catching mistakes before hitting the API.

Guest Code Validation

The Lambda does not require additional authentication (API key, IAM role, etc.) — only the guest_code. This is intentional:

  • Guests may not have AWS credentials
  • The guest_code acts as a bearer token with a short TTL and is tied to a specific event
  • An attacker would need a valid guest_code to upload to the wrong gallery, which is low-risk compared to full API access

Discrepancy Resolution: 6 + 20 ≠ 26

The gallery showed 23 photos after upload, not 26. Root cause: Angelia double-sent three photos across her two SMS batches. The deduplication step caught this:

  • Batch 1 (7:33 PM): 10 photos
  • Batch 2 (8:07 PM): 10 photos
  • Overlap: 3 files with identical MD5 hashes (IMG_1712, IMG_1713, IMG_1717)
  • Unique files: 10 + 10 - 3 = 17
  • Gallery total: 6 (pre-existing) + 17 (new) = 23

This is also why the upload appeared to fail initially — if Angelia was using a web form and the browser's file picker doesn't enforce uniqueness across multiple selections, she may have selected 20 files but encountered a validation error on the backend that deduplicated them. Our manual process confirmed all 17 unique photos are now live.

Monitoring and Verification

We verified the upload success by:

  • Checking the S3 object count in the gallery directory before and after
  • Querying the gallery API endpoint to fetch the public guest page
  • Visiting the CloudFront-distributed guest URL to visually confirm all photos appear
  • Sending a confirmation SMS to the guest with the gallery link

What We Learned

This workflow highlights the value of:

  • Content-based deduplication: Hashing file contents is more robust than relying on metadata or user intent.
  • Hybrid processing: Not everything belongs in Lambda. Local batch processing is faster, cheaper, and easier to debug for high-throughput, I/O-bound tasks.
  • Guest-friendly APIs: Bearer tokens (guest codes) with TTLs are a simpler, safer alternative to full authentication for transient, user-facing operations.
  • Format normalization: HEIC ↔ JPEG conversion at the ingest boundary (before upload to shared storage) prevents compatibility issues downstream.
```