WhatsApp media URL ingest contract

Status (2026-06-09): Deployed to Supabase project jrsgosnnyjonxaesqtln. Source lives in supabase/functions/ in this repo (mirror of the web project edge functions).

Runbook for fixing stale media_url / media_url_expires_at on whatsapp_messages at receive time. Client-side refresh via get-whatsapp-media-url mitigates expired URLs in the Flutter app; this doc specifies the server-side fix.

Problem

Inbound media rows sometimes store a short-lived signed URL in media_url. When staff reopen a thread hours later, the row URL is expired even though media_bucket + media_storage_path remain valid. The mobile app resolves fresh URLs via get-whatsapp-media-url, but DB rows stay stale and other surfaces (exports, web list previews) may break.

Required behavior (whatsapp-webhook ingest)

When persisting an inbound message with media:

1. Upload bytes to whatsapp-files (or existing bucket) → set media_bucket, media_storage_path, file_name, media_type.
2. Mint a signed URL with a known TTL (recommend 7 days for preview, or match Meta's temporary URL lifetime).
3. Write both fields atomically on insert/update:
   • media_url = signed URL
   • media_url_expires_at = now() + TTL (UTC)

On re-fetch from Meta (retry / webhook duplicate), refresh the signed URL if media_url_expires_at < now() + interval '1 hour'.

get-whatsapp-media-url (existing)

Keep the edge function as the authority when:

• media_url is null or expired, or
• mode=download needs a fresh long-lived URL.

It should prefer media_storage_path over returning the stale row URL.

Verification SQL

-- Rows with storage path but expired/missing URL
select id, conversation_id, media_type, media_url_expires_at
from whatsapp_messages
where media_storage_path is not null
  and media_deleted_at is null
  and (media_url is null or media_url_expires_at < now())
order by created_at desc
limit 20;

After deploy, new inbound media should have media_url_expires_at > now().

Deployed functions

Function	Change
whatsapp-webhook	7-day signed URL on ingest; duplicate webhook refreshes URL if expiring within 1h
get-whatsapp-media-url	Signs from media_storage_path; preview mode write-backs fresh URL + expiry to DB
refresh-stale-whatsapp-media-urls	Cron/operator backfill (service-role or x-cron-secret)

One-shot backfill (operators)

Preferred — uses the service role from this repo's ignored env/local.secrets.json (falling back to env/dev.json):

python tool/ops/backfill_whatsapp_media_urls.py

Reads SUPABASE_SERVICE_ROLE_KEY locally (same key as Supabase Dashboard → API → service_role), signs each stale row via Storage API, and updates media_url and media_url_expires_at.

Alternative — edge function (requires CRON_SECRET or matching edge SUPABASE_SERVICE_ROLE_KEY secret):

python tool/ops/run_whatsapp_media_backfill.py

Returns { "scanned": N, "refreshed": M }.

Flutter reference

• Model: lib/data/models/whatsapp.dart — effectiveMediaUrl(), isMediaUrlExpired
• Client resolver: lib/data/repositories/whatsapp_repository.dart — resolveMediaUrls()