searcher/.codex/skills/wallora-searcher/references/supabase-gallery-sync.md

Supabase Gallery Sync Reference

Naming

Use wg_ for project-owned Supabase objects.

• Gallery table: wg_gallery_photos
• Sync state table: wg_gallery_sync_state
• Collections tables: wg_photo_collections, wg_photo_collection_items
• User tables: wg_app_users, wg_user_favorites, wg_user_settings
• Example trigger/function names: set_wg_gallery_photos_updated_at, set_wg_updated_at

Do not introduce unprefixed replacements such as gallery_photos.

Files

• supabase/migrations/20260530000000_create_wg_gallery_tables.sql: table/RLS/index/trigger migration.
• lib/supabase/service-role.ts: server-only Supabase service-role client.
• supabase/migrations/20260601000000_extend_gallery_pick_collections_users.sql: picked-photo, OSS metadata, collections, and user/favorite/settings schema extension.
• server/api/unsplash.ts: official Unsplash API fetch and response validation.
• server/admin/gallery-sync.ts: reusable sync implementation for Next route handlers.
• server/admin/gallery-pick.ts: admin pick/unpick implementation and OSS mirror call.
• server/storage/aliyun-oss.ts: server-only Aliyun OSS PUT Object helper.
• app/api/admin/gallery-photos/route.ts: protected admin list + pick/unpick API.
• app/api/admin/photo-collections/route.ts: protected admin collection list/create API.
• app/api/admin/photo-collections/[id]/route.ts: protected admin collection read/update/delete API.
• app/api/v1/gallery/photos/route.ts: public picked photos API.
• app/api/v1/gallery/collections/route.ts: public published collections API.
• app/api/v1/gallery/collections/[slug]/route.ts: public published collection detail API.
• app/api/cron/gallery-photos/route.ts: protected HTTP trigger, guarded by CRON_SECRET.
• scripts/sync-gallery-photos.mjs: one-shot Node script for server cron/process managers.

Environment

Required for sync:

• NEXT_PUBLIC_SUPABASE_URL
• SUPABASE_SERVICE_ROLE_KEY
• UNSPLASH_ACCESS_KEY

Required for OSS mirroring when an admin marks a photo as picked:

• OSS_REGION
• OSS_ACCESS_KEY_ID
• OSS_ACCESS_KEY_SECRET
• OSS_BUCKET
• OSS_PUBLIC_URL or OSS_PUBLIC_BASE_URL

Optional for OSS:

• OSS_ENDPOINT to override the region-derived endpoint

Required for HTTP trigger:

• CRON_SECRET

Never print secret values. It is fine to report whether each variable is set.

Scheduler

This project is not intended for Vercel deployment. Do not add vercel.json cron config unless the deployment target changes.

Preferred server cron:

*/10 * * * * cd /Users/luoyangwei/Documents/workspace/wallora.top/searcher && pnpm sync:gallery

The script runs once and exits. That makes it suitable for crontab, PM2 cron, Docker scheduled jobs, or an external scheduler.

Sync Behavior

Source endpoint:

https://api.unsplash.com/topics/wallpapers/photos?page=<page>&per_page=20

Use the official Unsplash API with Authorization: Client-ID <UNSPLASH_ACCESS_KEY>. Do not rely on unsplash.com/napi/...; it is a website-internal endpoint and can return 401, anti-bot HTML, or other non-contract responses.

Workflow:

1. Read wg_gallery_sync_state for source unsplash-wallpapers.
2. Fetch the current page from the official Unsplash API with per_page = 20.
3. Require JSON and validate that the response is an array of photo-like objects.
4. Map every item into wg_gallery_photos.
5. Populate color_family from the Unsplash hex color so admin color filters work.
6. Upsert with onConflict: "unsplash_id" and ignoreDuplicates: true.
7. Update wg_gallery_sync_state with next page, seen count, inserted count, timestamp, and last error.

If Unsplash returns an error, anti-bot HTML, or any non-JSON response, record the error in wg_gallery_sync_state.last_error and do not write photo rows.

Picked Photos

• Raw gallery rows are for admin curation.
• Public consumers should query picked rows only through /api/v1/gallery/photos.
• RLS should expose wg_gallery_photos to anon/authenticated only when is_picked = true.
• Admin pick/unpick operations run through server-only code using the Supabase service-role key.
• Picking a photo attempts to mirror the selected Unsplash image into Aliyun OSS and stores oss_bucket, oss_object_key, oss_url, oss_synced_at, and oss_sync_error.
• If OSS is missing or upload fails, the photo can still be marked picked, but oss_sync_error should be visible to admin users.

Collections

• Collections are admin-managed under /admin/collections.
• Admin APIs are session-protected and run with the Supabase service-role client.
• Collection items must reference picked photos only.
• Published collections are exposed publicly; draft collections are not returned by public APIs.
• Public collection detail uses page and pageSize for waterfall-friendly pagination of collection photos.
• Public gallery photo detail lives at /api/v1/gallery/photos/[id]; it is picked-only and supports authenticated favorite/unfavorite shortcuts.

App Users

• App users are separate from admin users and live in wg_app_users.
• Email/password auth uses server-side password hashing and Bearer JWT issuance under /api/v1/auth/*.
• Protected app-user routes live under /api/v1/users/me/* and must validate Bearer JWTs server-side.
• Admin app-user review and status control live under /admin/users and /api/admin/app-users/[id].
• Favorites live in wg_user_favorites and should only reference picked photos.
• Preferences and download preferences live in wg_user_settings as JSON objects.
• wg_app_users has additive identity columns for future phone and WeChat login support.

Verification

Use these checks after touching sync code:

node --check scripts/sync-gallery-photos.mjs
pnpm format:check
pnpm lint
pnpm build

Only run pnpm sync:gallery when the user explicitly wants to write to the configured Supabase database.