Supabase + Next.js で作るマーケダッシュボード

-- 期間とクライアントを受け取り、日次のチャネル別サマリを返す
create or replace function public.fn_channel_daily(
  p_from date,
  p_to   date,
  p_client_id uuid default null
)
returns table (
  day        date,
  channel    text,
  sessions   bigint,
  conversions bigint,
  cost       numeric,
  cpa        numeric
)
language sql
stable
security invoker          -- 呼び出し元の権限で実行（RLSを効かせる。後述）
set search_path = public
as $$
  select
    m.day,
    m.channel,
    sum(m.sessions)    as sessions,
    sum(m.conversions) as conversions,
    sum(m.cost)        as cost,
    case when sum(m.conversions) > 0
         then round(sum(m.cost) / sum(m.conversions), 0)
         else null end as cpa
  from public.mart_daily_metrics m
  where m.day between p_from and p_to
    and (p_client_id is null or m.client_id = p_client_id)
  group by m.day, m.channel
  order by m.day, m.channel
$$;

// utils/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'
 
export async function createClient() {
  const cookieStore = await cookies()
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!, // 公開鍵（anonキーの値も移行期は可）
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // Server Component から呼ばれた場合は middleware 側で更新するので無視
          }
        },
      },
    }
  )
}

// app/dashboard/page.tsx  （Server Component）
import { createClient } from '@/utils/supabase/server'
import ChannelChart from './ChannelChart'
 
export default async function DashboardPage({
  searchParams,
}: {
  searchParams: Promise<{ from?: string; to?: string; client?: string }>
}) {
  const { from, to, client } = await searchParams
  const supabase = await createClient()
 
  const { data, error } = await supabase.rpc('fn_channel_daily', {
    p_from: from ?? defaultFrom(),
    p_to: to ?? today(),
    p_client_id: client ?? null,
  })
 
  if (error) {
    return <p>データを取得できませんでした。時間をおいて再読み込みしてください。</p>
  }
 
  return <ChannelChart rows={data ?? []} />
}

// app/dashboard/ChannelChart.tsx
'use client'
import { useEffect, useState } from 'react'
import { LineChart, Line, XAxis, YAxis, Tooltip, ResponsiveContainer } from 'recharts'
 
type Row = { day: string; channel: string; conversions: number }
 
export default function ChannelChart({ rows }: { rows: Row[] }) {
  // ① マウントガード：初回描画はクライアント確定後に
  const [mounted, setMounted] = useState(false)
  useEffect(() => setMounted(true), [])
 
  // 日付ごとに横持ちへ整形（DB側で整形済みなら省略可）
  const byDay = new Map<string, Record<string, number | string>>()
  for (const r of rows) {
    const d = byDay.get(r.day) ?? { day: r.day }
    d[r.channel] = r.conversions
    byDay.set(r.day, d)
  }
  const data = [...byDay.values()]
  const channels = [...new Set(rows.map((r) => r.channel))]
 
  return (
    // ② 外側で高さを予約：チャートの出現でレイアウトを動かさない
    <div style={{ height: 320 }}>
      {mounted && (
        <ResponsiveContainer width="100%" height="100%">
          <LineChart data={data} margin={{ top: 8, right: 16, bottom: 8, left: 0 }}>
            <XAxis dataKey="day" tick={{ fontSize: 12 }} />
            <YAxis tick={{ fontSize: 12 }} width={40} />
            <Tooltip />
            {channels.map((ch) => (
              <Line
                key={ch}
                type="monotone"
                dataKey={ch}
                dot={false}
                strokeWidth={1.5}
                isAnimationActive={false} // ③ 描画アニメーション無効
              />
            ))}
          </LineChart>
        </ResponsiveContainer>
      )}
    </div>
  )
}

// app/dashboard/DateRangeFilter.tsx
'use client'
import { useRouter, useSearchParams, usePathname } from 'next/navigation'
 
export default function DateRangeFilter() {
  const router = useRouter()
  const pathname = usePathname()
  const params = useSearchParams()
 
  function setRange(from: string, to: string) {
    const next = new URLSearchParams(params)
    next.set('from', from)
    next.set('to', to)
    router.push(`${pathname}?${next.toString()}`)
  }
 
  return (
    <div>
      <button onClick={() => setRange(last7d(), today())}>過去7日</button>
      <button onClick={() => setRange(last28d(), today())}>過去28日</button>
    </div>
  )
}

// JST を明示して YYYY-MM-DD を作る（sv-SE ロケールは ISO 形式で返る）
const todayJst = new Intl.DateTimeFormat('sv-SE', {
  timeZone: 'Asia/Tokyo',
}).format(new Date())

// app/dashboard/LiveCounter.tsx
'use client'
import { useEffect, useState } from 'react'
import { createClient } from '@/utils/supabase/client'
 
export default function LiveCounter({ campaignId }: { campaignId: string }) {
  const [count, setCount] = useState(0)
  const supabase = createClient()
 
  useEffect(() => {
    const channel = supabase
      .channel(`cv-${campaignId}`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'conversions',
          filter: `campaign_id=eq.${campaignId}`,
        },
        () => setCount((c) => c + 1)
      )
      .subscribe()
 
    return () => {
      supabase.removeChannel(channel) // 購読の後始末は必須
    }
  }, [campaignId, supabase])
 
  return <p>本日のCV（ライブ）：{count}</p>
}

# ブラウザの DevTools を開けば公開鍵と URL は誰でも拾える。つまり誰でもこれが打てる
curl "https://<project>.supabase.co/rest/v1/mart_daily_metrics?select=*" \
  -H "apikey: <公開鍵>" \
  -H "Authorization: Bearer <公開鍵>"
# RLS が正しく効いていれば 0 行（または権限エラー）。秘匿列がローデータのまま返ってきたら設計が漏れている

-- ロール保持
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  role text not null default 'member',           -- admin / member / client_viewer
  client_id uuid                                  -- client_viewer のとき紐づくクライアント
);
 
alter table public.mart_daily_metrics enable row level security;
 
-- 全員（admin / member）は閲覧可、client_viewer は自分の client_id の行だけ
create policy "read scoped metrics"
on public.mart_daily_metrics
for select
to authenticated
using (
  exists (
    select 1 from public.profiles p
    where p.id = auth.uid()
      and (
        p.role in ('admin', 'member')
        or (p.role = 'client_viewer' and p.client_id = mart_daily_metrics.client_id)
      )
  )
);

// middleware.ts （要点のみ）
import { createServerClient } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'
 
export async function middleware(request: NextRequest) {
  let response = NextResponse.next({ request })
 
  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
    {
      cookies: {
        getAll: () => request.cookies.getAll(),
        setAll: (cookiesToSet) => {
          cookiesToSet.forEach(({ name, value, options }) =>
            response.cookies.set(name, value, options)
          )
        },
      },
    }
  )
 
  // セッションを更新しつつ、未ログインは弾く
  const { data } = await supabase.auth.getUser()
  if (!data.user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }
  return response
}
 
export const config = { matcher: ['/dashboard/:path*'] }