agentleFS
ianlkl11234sianlkl11234s/mini-taiwan-pulse/.claude/skills/supabase-optimize/SKILL.md

supabase-optimize

Skill547 starsChanged 9 days agoMITView on GitHub

為 Supabase RPC 產生 pre-aggregate pattern SQL 範本。當響應時間 > 1s、rows > 10k、或含 string_agg/ST_Union 時使用。依照 Mini Taiwan Pulse 專案的「普通 table + per-day refresh function + pg_cron + 薄 RPC」架構產生完整 SQL,含 advisory lock、cleanup、cron 排程。

The file

Files
SKILL.md

Supabase Pre-aggregate Pattern Generator

依照 Mini Taiwan Pulse 專案規則產生 pre-aggregate SQL。完整 pattern 說明見 docs/supabase-optimization.md。

何時使用

符合 任一 條件:

  • 響應時間 > 1 秒
  • 回傳 > 10,000 rows
  • 含 string_agg / ST_Union / 複雜 JOIN
  • 看到 pooler 2min timeout(Supabase Supavisor 強制)

輸入

使用前請確認收集:

  1. RPC 名稱 (例:get_ship_trails)
  2. 原始大表 (例:realtime.ship_positions)
  3. 分組維度 (例:(day, mmsi))
  4. 聚合欄位 (例:string_agg(...))
  5. 時間欄位 (例:collected_at)
  6. 是否需要 ±1h overlap(跨日 timeline 銜接)
  7. Cron 頻率 (通常 */10 * * * *,溫度資料 */30)
  8. Cleanup 保留天數 (通常 7)

產生的 SQL 區塊

  1. Table 定義(普通 table,不是 MV)
  2. Refresh function(含 pg_advisory_xact_lock + SET statement_timeout TO '0')
  3. Cleanup function
  4. RPC rewrite(薄 SELECT,SET statement_timeout TO '60s',REVOKE … FROM PUBLIC 後 GRANT EXECUTE TO anon, authenticated;refresh/cleanup REVOKE … FROM PUBLIC, anon, authenticated)——權限慣例正本見 docs/supabase-optimization.md §權限慣例
  5. pg_cron 排程(refresh today + yesterday;cleanup 每日 18:00 UTC)
  6. Backfill 指令
  7. PostgREST schema reload (NOTIFY pgrst, 'reload schema')
  8. 驗證指令

範本結構

詳見 data-collectors/docs/sql/matview_*.sql,有 10 個現成範本可參考:

  • matview_ship_trails.sql — per-day trail aggregation with ±1h overlap
  • matview_flight_trails.sql — 同上,含 altitude filter
  • matview_freeway_congestion.sql — 含 JOIN 靜態 sections
  • matview_youbike_h3.sql — 含 resolution 維度
  • matview_temperature_frames.sql — per-observed_at aggregation
  • matview_temperature_dates.sql — 全量 cache(非 per-day)
  • matview_disaster_alerts.sql — 預存 ST_Union 幾何
  • reference_temperature_grid.sql — 靜態 reference 表
  • cwa_imagery_rpcs.sql — 批次 RPC pattern

執行流程

  1. 讀取最接近的範本檔案
  2. 依用戶需求改 table schema、GROUP BY、聚合函式
  3. 產出到 data-collectors/docs/sql/matview_<new>.sql
  4. 告知用戶執行指令:
    psql "$SUPABASE_DB_URL" -f data-collectors/docs/sql/matview_<new>.sql
    psql "$SUPABASE_DB_URL" -c "SELECT public.refresh_<new>_daily(d::date) FROM generate_series(current_date - 6, current_date, '1 day') d;"
    psql "$SUPABASE_DB_URL" -c "NOTIFY pgrst, 'reload schema';"
  5. 驗證:
    time psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.get_<new>_day(current_date);"
    psql "$SUPABASE_DB_URL" -c "SELECT * FROM cron.job_run_details WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'refresh-<new>') ORDER BY start_time DESC LIMIT 3;"
  6. 更新 docs/audit/supabase-rpc-audit-2026-04-09.md

關鍵檢查點(產生 SQL 時必含)

  • Refresh function 有 pg_advisory_xact_lock(hashtext('refresh_xxx:' || target_day::text))
  • Refresh function 屬性 SET statement_timeout TO '0'
  • RPC function 屬性 SET statement_timeout TO '60s'(payload 傳輸)
  • RPC 有 REVOKE EXECUTE ... FROM PUBLIC + GRANT EXECUTE ... TO anon, authenticated(2026-10 起 public 的 default privileges 已收回,漏寫前端會 permission denied)
  • refresh/cleanup 函式有 REVOKE EXECUTE ... FROM PUBLIC, anon, authenticated
  • SECURITY INVOKER 的 RPC 讀的表有 GRANT SELECT ... TO anon, authenticated(只給 SELECT)
  • SECURITY DEFINER 函式有 SET search_path;cleanup 的 keep_days 等天數/時間範圍參數在函式內 clamp
  • PK 包含 day + 分組 key
  • 有 CREATE INDEX xxx_day_idx ON xxx (day)
  • Time window 用 +08 timezone literal(非 naive timestamp)
  • cron 排程 refresh today AND yesterday(跨日延遲資料)
  • cleanup 排程 0 18 * * * UTC(= 02:00 Taipei)
  • 檔案 header 有動機說明(Before → After 效能數字)

禁止

  • ❌ 用 MATERIALIZED VIEW(一次 REFRESH 會撞 pooler 2min timeout)
  • ❌ 跳過 advisory lock(cron + 手動 call 會 race)
  • ❌ 假設 SET statement_timeout = 0 對前端 pooler 連線有效(只有 pg_cron 例外)

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.