AIyaodianzhushou/database/user_events.sql

-- ============================================================
-- 用户行为埋点表
-- 执行方式：psql -h <host> -U <user> -d pharmacopoeia -f database/user_events.sql
-- ============================================================
BEGIN;

CREATE TABLE IF NOT EXISTS public.user_events (
    id BIGSERIAL PRIMARY KEY,
    user_key varchar(128),
    system_source varchar(64) DEFAULT 'AI药典',
    event_type varchar(64),
    event_data jsonb,
    page_url varchar(512),
    referrer varchar(512),
    ip varchar(64),
    user_agent varchar(512),
    created_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_user_events_user_key ON user_events(user_key);
CREATE INDEX IF NOT EXISTS ix_user_events_system ON user_events(system_source);
CREATE INDEX IF NOT EXISTS ix_user_events_type ON user_events(event_type);
CREATE INDEX IF NOT EXISTS ix_user_events_created_at ON user_events(created_at DESC);
CREATE INDEX IF NOT EXISTS ix_user_events_ip ON user_events(ip);
CREATE INDEX IF NOT EXISTS ix_user_events_page_url ON user_events(page_url);

COMMIT;