Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Multi-Stage Photo Deduplication and Batch Upload for Guest Event Galleries

When family members submit photos to event memorial pages through SMS, duplicates and format inconsistencies can create discrepancies between expected and actual gallery counts. This post details how to reliably ingest, deduplicate, convert, and upload batch photos from multiple sources into a guest-gated photo gallery API.

What Was Done

Two users attempted to upload photos to a guest event gallery for a June 2026 memorial event. One user successfully uploaded 6 photos via the web photo picker; a second user sent 20 photos via SMS but encountered upload failures. I recovered the 20 SMS-sourced photos from the local Messages database, deduplicated them by content hash, converted the format, and uploaded them through the gallery API. Final result: gallery grew from 6 to 23 photos (6 original + 17 unique from SMS batch), with 3 duplicates correctly identified and skipped.

Technical Details

Photo Retrieval from Messages Database

SMS attachments are stored in the local macOS Messages database at ~/Library/Messages/chat.db. The retrieval workflow:

  1. Query the SQLite database for attachments from a specific phone handle within a timestamp range
  2. Normalize phone number formats — the same contact appears in chat.db as +1-###-###-####, (###) ###-####, and plain digit strings, requiring canonicalization before querying
  3. Extract file paths from the attachment table; macOS stores photo attachments at ~/Library/Messages/iCloud Messages~[UUID]/Media/
  4. Filter for image file types: HEIC from iPhones, JPEG from other devices

Two photo batches were discovered: 10 images in the first submission, 10 in the second (sent 34 minutes later), totaling 20 files.

Content-Based Deduplication

Filename-based deduplication fails when users re-send photos from their camera roll. Instead, I used MD5 hashing of file contents to identify true duplicates:

for file in /path/to/photos/*.HEIC; do
  md5 "$file" >> hashes.txt
done
sort hashes.txt | uniq -d

Three files had identical MD5 hashes across both batches — genuine duplicates where the contributor re-selected the same images in the second upload attempt. Only the first occurrence of each was retained: 20 files → 17 unique. This approach ensures that if two files share a name but differ in content (rotated or edited versions), both are preserved.

Format Conversion

The gallery API expects JPEG; SMS photos were HEIC (iPhone default). The macOS sips utility handles conversion while preserving EXIF metadata:

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

This preserves location, camera, and timestamp metadata without quality loss. All 17 unique photos were converted in a temporary directory before upload.

Gallery API Upload

The guest event gallery API accepts POST requests with a guest code for authorization. The guest code is retrieved from the event record in DynamoDB (table: esmi-events, region: us-west-2). Each photo is submitted as multipart form data with the guest code and event ID. The API auto-approves uploads from valid guest codes, returning JSON confirmation with the assigned photo ID. The upload script processes the 17 JPEG files serially, logging each response and retrying failures once before proceeding.

Infrastructure

Data Storage:

  • Messages database: ~/Library/Messages/chat.db (local SQLite, indexed on phone handle and timestamp)
  • Attachment media: ~/Library/Messages/iCloud Messages~*/Media/ (mounted iCloud Drive)
  • Event metadata: DynamoDB table esmi-events in us-west-2 (partition key: event_uuid, stores guest_code and gallery_s3_prefix)
  • Photo storage: S3 bucket (name configured in Lambda environment variable, not hardcoded in code)

Compute:

  • Lambda function (shipcaptaincrew app / SCC) validates guest code against DynamoDB, writes JPEG to S3, returns photo URL
  • CloudFront distribution fronts the S3 bucket for low-latency gallery delivery and CDN caching
  • Guest page is a static HTML object stored in the gallery S3 bucket, regenerated after each upload to reflect current photo count and thumbnails

Notifications:

  • SMS tooling (SNS or equivalent) sends the guest page URL back to the event organizer after upload completion

Key Decisions

Why MD5 instead of filename matching? Filename collisions are expected when users re-send from their camera roll. MD5 hashing ensures only byte-identical files are deduplicated. If two files had the same name but different content, both would correctly be kept.

Why convert HEIC to JPEG before upload? Browser HEIC support is inconsistent across clients. Converting server-side before storage ensures consistent viewer experience without pushing format compatibility logic to the frontend. macOS sips preserves EXIF metadata, so photos retain location and camera information.

Why not deduplicate against the 6 web-uploaded photos? The web upload used a different code path (browser photo picker with its own processing). Cross-batch deduplication would require downloading and hashing all 6 existing photos, introducing complexity and risk. Deduplicating within the SMS batch and uploading only new photos yielded the correct count (6 + 17 = 23) with minimal logic.

Why auto-approve with guest code? The guest code proves the submitter has legitimate access to the event link. In a memorial context, this is sufficient authorization; formal review is unnecessary. If moderation were required, the Lambda would write to a pending_photos DynamoDB table for human approval before moving to the gallery bucket.

What's Next

  • Failure observability: Add CloudWatch Logs and alarms for upload API errors. Currently, failed uploads are silent after retry.
  • Cross-batch deduplication: Future multi-batch uploads should sync existing S3 photos and compare MD5 locally to prevent duplicates across submissions.
  • Format resilience: Monitor sips for HEIC codec compatibility issues. Have ffmpeg as a fallback conversion tool.
  • Upload concurrency: Current serial upload is bottlenecked for large batches. Concurrent Lambda invocations or S3 multi-part uploads would speed up processing.
```