feat(storage): add R2-only media object registry for news images #633

Depends on: #631, #632

Context

The full-online R2-only news portal profile needs a metadata registry for images stored in Cloudflare R2. PostgreSQL must store metadata and relationships only. Image binaries must remain in R2 object storage.

This issue is full-online only for news portal images and must not introduce local filesystem storage.

Objective

Add a tenant-scoped R2-only media object registry for news images used by blog_content, homepage sections, galleries, ads, SEO images, and video thumbnails.

Scope

Add a schema migration for a metadata table such as:

awcms_mini_media_objects

Suggested columns:

id
tenant_id
module_key
owner_resource_type
owner_resource_id
storage_driver
bucket_name
object_key
public_url
mime_type
size_bytes
checksum_sha256
width
height
alt_text
caption
status
created_by
created_at
updated_at
deleted_at

Suggested statuses:

pending_upload
uploaded
verified
attached
orphaned
deleted
failed

Required constraints:

storage_driver = 'cloudflare_r2'
object_key IS NOT NULL
bucket_name IS NOT NULL

Object key convention:

tenants/{tenant_id}/blog/news-images/{yyyy}/{mm}/{uuid}.{ext}

Application behavior

Add application/domain helpers for:

• Creating a pending media object metadata record.
• Validating R2 object keys.
• Validating trusted public URL generation.
• Marking upload as verified.
• Attaching media object to blog/news resources.
• Marking orphaned/deleted metadata.

Out of scope

• Upload API and presigned URL generation.
• Image transformation service.
• Local storage driver.
• Storing binary data in PostgreSQL.
• Importing legacy files.

Acceptance criteria

• Table is tenant-scoped and includes tenant_id.
• RLS is enabled and forced.
• Policies prevent cross-tenant media access.
• Table stores metadata only; no binary/blob column is added.
• Local filesystem path is not stored as source of truth.
• storage_driver is constrained to cloudflare_r2 for this news image registry path.
• Object key is server-generated and validates the tenant/module prefix.
• Public URL is generated from trusted R2 public base URL/custom domain, not user input.
• Soft delete is supported for metadata.
• Audit events are written for create, verify, attach, detach, delete, and restore/purge where applicable.
• Tests cover RLS, invalid driver, invalid object key, missing bucket, and cross-tenant rejection.
• bun run db:migrate passes.
• bun run test passes.
• bun run check passes.

Security notes

• Do not store local paths such as /uploads/..., /public/..., or Docker volume paths.
• Do not accept arbitrary external image URLs as media objects.
• Do not expose R2 credentials to client code.
• Keep object metadata tenant-isolated with RLS and ABAC.