-- アクセスログテーブル
CREATE TABLE IF NOT EXISTS public.access_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ip_address TEXT NOT NULL,
user_agent TEXT,
path TEXT NOT NULL,
method TEXT NOT NULL DEFAULT 'GET',
status_code INTEGER,
is_bot BOOLEAN NOT NULL DEFAULT FALSE,
is_ai_crawler BOOLEAN NOT NULL DEFAULT FALSE,
bot_category TEXT,
bot_name TEXT,
bot_confidence REAL,
action_taken TEXT,
project TEXT NOT NULL DEFAULT 'endai-system',
referer TEXT,
country TEXT
);
-- インデックス
CREATE INDEX IF NOT EXISTS idx_access_logs_created_at
ON public.access_logs (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_access_logs_is_ai_crawler
ON public.access_logs (is_ai_crawler) WHERE is_ai_crawler = TRUE;
CREATE INDEX IF NOT EXISTS idx_access_logs_bot_category
ON public.access_logs (bot_category);
CREATE INDEX IF NOT EXISTS idx_access_logs_project
ON public.access_logs (project);
CREATE INDEX IF NOT EXISTS idx_access_logs_ip
ON public.access_logs (ip_address);
必須手動 RLS + ビュー + クリーンアップ関数を実行
同じ SQL Editor で続けて以下も実行
-- 日別集計ビュー
CREATE OR REPLACE VIEW public.access_logs_daily_summary AS
SELECT
date_trunc('day', created_at)::date AS log_date,
project, bot_category, bot_name, action_taken,
COUNT(*) AS request_count,
COUNT(DISTINCT ip_address) AS unique_ips
FROM public.access_logs
GROUP BY 1, 2, 3, 4, 5
ORDER BY 1 DESC;
-- AIクローラー専用ビュー
CREATE OR REPLACE VIEW public.ai_crawler_logs AS
SELECT * FROM public.access_logs
WHERE is_ai_crawler = TRUE
ORDER BY created_at DESC;
-- RLS有効化
ALTER TABLE public.access_logs ENABLE ROW LEVEL SECURITY;
-- 管理者のみ閲覧可能
CREATE POLICY "Admins can view access_logs"
ON public.access_logs FOR SELECT
USING (
EXISTS (
SELECT 1 FROM public.profiles
WHERE id = auth.uid()
AND role IN ('admin', 'organizer')
)
);
-- 古いログの手動クリーンアップ用関数
CREATE OR REPLACE FUNCTION public.cleanup_old_access_logs(
days_to_keep INTEGER DEFAULT 90
)
RETURNS INTEGER
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE deleted_count INTEGER;
BEGIN
DELETE FROM public.access_logs
WHERE created_at < now() - make_interval(days => days_to_keep);
GET DIAGNOSTICS deleted_count = ROW_COUNT;
RETURN deleted_count;
END;
$$;
必須手動 テーブル作成を確認
左メニュー「Table Editor」→ access_logs テーブルが表示されること確認。
「Authentication」→「Policies」→ access_logs に「Admins can view access_logs」ポリシーがあること確認。