utm sync

app/api/activities/route.ts

@@ -0,0 +1,236 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { getEvents } from '@/lib/analytics';
+import { ApiResponse } from '@/lib/types';
+
+// 扩展Event类型以包含所需字段
+interface EventWithFullPath extends Record<string, any> {
+  event_id?: string;
+  event_time?: string;
+  event_type?: string;
+  visitor_id?: string;
+  ip_address?: string;
+  req_full_path?: string;
+  referrer?: string;
+  // 其他可能的字段
+}
+
+export async function GET(request: NextRequest) {
+  try {
+    const { searchParams } = new URL(request.url);
+    
+    // Get parameters
+    const slug = searchParams.get('slug');
+    const domain = searchParams.get('domain');
+    const format = searchParams.get('format');
+    
+    // Optional date range parameters
+    const startTime = searchParams.get('startTime') || undefined;
+    const endTime = searchParams.get('endTime') || undefined;
+    
+    // 修改验证逻辑，允许只使用时间范围
+    // 现在只需要确保有足够的过滤条件
+    if ((!slug && !domain) && (!startTime && !endTime)) {
+      return NextResponse.json({
+        success: false,
+        error: 'Missing filter parameters: provide either slug/domain or date range'
+      }, { status: 400 });
+    }
+    
+    // Construct the shortUrl from domain and slug if both are provided
+    let shortUrl = undefined;
+    if (slug && domain) {
+      shortUrl = `https://${domain}/${slug}`;
+      
+      // Log the request for debugging
+      console.log('Activities API received parameters:', {
+        slug,
+        domain,
+        shortUrl,
+        startTime,
+        endTime
+      });
+    } else {
+      console.log('Activities API using time range filter:', {
+        startTime,
+        endTime
+      });
+    }
+    
+    // Set default page size and page
+    const page = parseInt(searchParams.get('page') || '1');
+    const pageSize = parseInt(searchParams.get('pageSize') || '50');
+    
+    // Get events for the specified filters
+    const { events, total } = await getEvents({
+      linkSlug: slug || undefined,
+      page,
+      pageSize,
+      startTime,
+      endTime,
+      sortBy: 'event_time',
+      sortOrder: 'desc'
+    });
+    
+    // If format=csv, return CSV format data
+    if (format === 'csv') {
+      // CSV header line
+      let csvContent = 'time,activity,campaign,clientId,originPath\n';
+      
+      // Helper function to extract utm_campaign from URL
+      const extractUtmCampaign = (url: string | null | undefined): string => {
+        if (!url) return 'demo';
+        
+        try {
+          // Try to parse URL and extract utm_campaign parameter
+          const urlObj = new URL(url.startsWith('http') ? url : `https://example.com${url}`);
+          const campaign = urlObj.searchParams.get('utm_campaign');
+          if (campaign) return campaign;
+          
+          // If utm_campaign is not found or URL parsing fails, use regex as fallback
+          const campaignMatch = url.match(/[?&]utm_campaign=([^&]+)/i);
+          if (campaignMatch && campaignMatch[1]) return campaignMatch[1];
+        } catch (_) {
+          // If URL parsing fails, try regex directly
+          const campaignMatch = url.match(/[?&]utm_campaign=([^&]+)/i);
+          if (campaignMatch && campaignMatch[1]) return campaignMatch[1];
+        }
+        
+        return 'demo'; // Default value
+      };
+      
+      // Process each event record
+      events.forEach(event => {
+        // 使用类型断言处理扩展字段
+        const eventWithFullPath = event as unknown as EventWithFullPath;
+        
+        // Get the full URL from appropriate field
+        // Try different possible fields that might contain the URL
+        const fullUrl = eventWithFullPath.req_full_path || eventWithFullPath.referrer || '';
+        
+        // Extract campaign from URL
+        const campaign = extractUtmCampaign(fullUrl);
+        
+        // Format time
+        const time = eventWithFullPath.event_time ? 
+          new Date(eventWithFullPath.event_time).toISOString().replace('T', ' ').slice(0, 19) : 
+          '';
+        
+        // Determine activity (event_type)
+        const activity = eventWithFullPath.event_type || '';
+        
+        // Client ID (possibly part of visitor_id)
+        const clientId = eventWithFullPath.visitor_id?.split('-')[0] || 'undefined';
+        
+        // Original path (use full URL field)
+        const originPath = fullUrl || 'undefined';
+        
+        // Add to CSV content
+        csvContent += `${time},${activity},${campaign},${clientId},${originPath}\n`;
+      });
+      
+      // Generate filename based on available parameters
+      const filename = slug 
+        ? `activities-${slug}.csv` 
+        : `activities-${new Date().toISOString().slice(0,10)}.csv`;
+      
+      // Return CSV response
+      return new NextResponse(csvContent, {
+        headers: {
+          'Content-Type': 'text/csv',
+          'Content-Disposition': `attachment; filename="${filename}"`
+        }
+      });
+    }
+    
+    // Process the events to extract useful information
+    const processedEvents = events.map(event => {
+      // Parse JSON strings to objects safely
+      let eventAttributes: Record<string, unknown> = {};
+      
+      try {
+        if (typeof event.event_attributes === 'string') {
+          eventAttributes = JSON.parse(event.event_attributes);
+        } else if (typeof event.event_attributes === 'object') {
+          eventAttributes = event.event_attributes;
+        }
+      } catch {
+        // Keep default empty object if parsing fails
+      }
+      
+      // Extract tags
+      let tags: string[] = [];
+      
+      try {
+        if (typeof event.link_tags === 'string') {
+          const parsedTags = JSON.parse(event.link_tags);
+          if (Array.isArray(parsedTags)) {
+            tags = parsedTags;
+          }
+        } else if (Array.isArray(event.link_tags)) {
+          tags = event.link_tags;
+        }
+      } catch {
+        // If parsing fails, keep tags as empty array
+      }
+      
+      // Return a simplified event object
+      return {
+        id: event.event_id,
+        type: event.event_type,
+        time: event.event_time,
+        visitor: {
+          id: event.visitor_id,
+          ipAddress: event.ip_address,
+          userAgent: eventAttributes.user_agent as string || null,
+          referrer: eventAttributes.referrer as string || null
+        },
+        device: {
+          type: event.device_type,
+          browser: event.browser,
+          os: event.os
+        },
+        location: {
+          country: event.country,
+          city: event.city
+        },
+        link: {
+          id: event.link_id,
+          slug: event.link_slug,
+          originalUrl: event.link_original_url,
+          label: event.link_label,
+          tags
+        },
+        utm: {
+          source: eventAttributes.utm_source as string || null,
+          medium: eventAttributes.utm_medium as string || null,
+          campaign: eventAttributes.utm_campaign as string || null,
+          term: eventAttributes.utm_term as string || null,
+          content: eventAttributes.utm_content as string || null
+        }
+      };
+    });
+    
+    // Return processed events
+    const response: ApiResponse<typeof processedEvents> = {
+      success: true,
+      data: processedEvents,
+      meta: {
+        total,
+        page,
+        pageSize
+      }
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error retrieving activities:', error);
+    
+    const response: ApiResponse<null> = {
+      success: false,
+      data: null,
+      error: error instanceof Error ? error.message : 'An error occurred while retrieving activities'
+    };
+    
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/events/devices/route.ts

@@ -18,7 +18,9 @@ export async function GET(request: NextRequest) {
       // 添加团队、项目和标签筛选
       teamIds: teamIds.length > 0 ? teamIds : undefined,
       projectIds: projectIds.length > 0 ? projectIds : undefined,
-      tagIds: tagIds.length > 0 ? tagIds : undefined
+      tagIds: tagIds.length > 0 ? tagIds : undefined,
+      // 添加子路径筛选
+      subpath: searchParams.get('subpath') || undefined
     });
 
     const response: ApiResponse<typeof data> = {

app/api/events/geo/route.ts

@@ -22,7 +22,9 @@ export async function GET(request: NextRequest) {
       // 添加团队、项目和标签筛选
       teamIds: teamIds.length > 0 ? teamIds : undefined,
       projectIds: projectIds.length > 0 ? projectIds : undefined,
-      tagIds: tagIds.length > 0 ? tagIds : undefined
+      tagIds: tagIds.length > 0 ? tagIds : undefined,
+      // 添加子路径筛选
+      subpath: searchParams.get('subpath') || undefined
     });
 
     const response: ApiResponse<typeof data> = {

app/api/events/path-analytics/route.ts

@@ -0,0 +1,80 @@
+import { NextRequest, NextResponse } from 'next/server';
+import type { ApiResponse } from '@/lib/types';
+import { executeQuery } from '@/lib/clickhouse';
+
+export async function GET(request: NextRequest) {
+  try {
+    // 获取查询参数
+    const searchParams = request.nextUrl.searchParams;
+    const startTime = searchParams.get('startTime');
+    const endTime = searchParams.get('endTime');
+    const linkId = searchParams.get('linkId');
+    
+    if (!startTime || !endTime || !linkId) {
+      return NextResponse.json({
+        success: false,
+        error: 'Missing required parameters'
+      }, { status: 400 });
+    }
+
+    // 查询链接的点击事件
+    const query = `
+      SELECT event_attributes
+      FROM events
+      WHERE link_id = '${linkId}'
+      AND event_time >= parseDateTimeBestEffort('${startTime}')
+      AND event_time <= parseDateTimeBestEffort('${endTime}')
+      AND event_type = 'click'
+    `;
+    
+    const events = await executeQuery(query);
+    
+    // 处理事件数据，按路径分组
+    const pathMap = new Map<string, number>();
+    let totalClicks = 0;
+    
+    events.forEach((event: any) => {
+      try {
+        if (event.event_attributes) {
+          const attrs = JSON.parse(event.event_attributes);
+          if (attrs.full_url) {
+            // 提取URL的路径和参数部分
+            const url = new URL(attrs.full_url);
+            const pathWithParams = url.pathname + (url.search || '');
+            
+            // 更新路径计数
+            const currentCount = pathMap.get(pathWithParams) || 0;
+            pathMap.set(pathWithParams, currentCount + 1);
+            totalClicks++;
+          }
+        }
+      } catch (error) {
+        // 忽略解析错误
+      }
+    });
+    
+    // 转换为数组并按点击数排序
+    const pathData = Array.from(pathMap.entries())
+      .map(([path, count]) => ({
+        path,
+        count,
+        percentage: totalClicks > 0 ? count / totalClicks : 0,
+      }))
+      .sort((a, b) => b.count - a.count);
+    
+    const response: ApiResponse<typeof pathData> = {
+      success: true, 
+      data: pathData,
+      meta: { total: totalClicks }
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching path analytics data:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Internal server error'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/events/route.ts

@@ -14,6 +14,7 @@ export async function GET(request: NextRequest) {
     const linkId = searchParams.get('linkId') || undefined;
     const linkSlug = searchParams.get('linkSlug') || undefined;
     const userId = searchParams.get('userId') || undefined;
+    const subpath = searchParams.get('subpath') || undefined;
     
     // 获取可能存在的多个团队、项目和标签ID
     const teamIds = searchParams.getAll('teamId');
@@ -26,6 +27,7 @@ export async function GET(request: NextRequest) {
     const sortOrder = (searchParams.get('sortOrder') as 'asc' | 'desc') || undefined;
     
     console.log("API接收到的tagIds:", tagIds); // 添加日志便于调试
+    console.log("API接收到的subpath:", subpath); // 添加日志便于调试
     
     // 获取事件列表
     const params: EventsQueryParams = {
@@ -35,6 +37,7 @@ export async function GET(request: NextRequest) {
       linkId,
       linkSlug,
       userId,
+      subpath,
       teamIds: teamIds.length > 0 ? teamIds : undefined,
       projectIds: projectIds.length > 0 ? projectIds : undefined,
       tagIds: tagIds.length > 0 ? tagIds : undefined,
@@ -44,6 +47,9 @@ export async function GET(request: NextRequest) {
       sortOrder
     };
     
+    // 记录完整的参数用于调试
+    console.log("完整请求参数:", JSON.stringify(params));
+    
     const result = await getEvents(params);
     
     const response: ApiResponse<typeof result.events> = {

app/api/events/summary/route.ts

@@ -11,13 +11,22 @@ export async function GET(request: NextRequest) {
     const projectIds = searchParams.getAll('projectId');
     const tagIds = searchParams.getAll('tagId');
     
+    // Add debug log to check if linkId is being received
+    const linkId = searchParams.get('linkId');
+    const subpath = searchParams.get('subpath');
+    console.log('Summary API received linkId:', linkId);
+    console.log('Summary API received subpath:', subpath);
+    console.log('Summary API full parameters:', Object.fromEntries(searchParams.entries()));
+    console.log('Summary API URL:', request.url);
+    
     const summary = await getEventsSummary({
       startTime: searchParams.get('startTime') || undefined,
       endTime: searchParams.get('endTime') || undefined,
       linkId: searchParams.get('linkId') || undefined,
       teamIds: teamIds.length > 0 ? teamIds : undefined,
       projectIds: projectIds.length > 0 ? projectIds : undefined,
-      tagIds: tagIds.length > 0 ? tagIds : undefined
+      tagIds: tagIds.length > 0 ? tagIds : undefined,
+      subpath: searchParams.get('subpath') || undefined
     });
 
     const response: ApiResponse<typeof summary> = {

app/api/events/time-series/route.ts

@@ -28,7 +28,9 @@ export async function GET(request: NextRequest) {
       // 添加团队、项目和标签筛选
       teamIds: teamIds.length > 0 ? teamIds : undefined,
       projectIds: projectIds.length > 0 ? projectIds : undefined,
-      tagIds: tagIds.length > 0 ? tagIds : undefined
+      tagIds: tagIds.length > 0 ? tagIds : undefined,
+      // 添加子路径筛选
+      subpath: searchParams.get('subpath') || undefined
     });
 
     const response: ApiResponse<typeof data> = {

app/api/events/track/readme.md

@@ -0,0 +1,208 @@
+
+# 事件跟踪接口说明
+
+## 概述
+该接口用于跟踪用户交互事件并将数据存储到 ClickHouse 数据库中。支持记录各种类型的事件，并可包含与链接、用户、团队、项目等相关的详细信息。
+
+## 接口信息
+- **URL**: `/api/events/track`
+- **方法**: `POST`
+- **Content-Type**: `application/json`
+
+## 请求参数
+
+### 必填字段
+| 参数 | 类型 | 描述 |
+|------|------|------|
+| `event_type` | string | 事件类型，如 'click', 'view', 'conversion' |
+
+### 核心事件字段
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `event_id` | string | 否 | 事件唯一标识符，不提供时自动生成UUID |
+| `event_time` | string/Date | 否 | 事件发生时间，格式为ISO日期字符串，默认为当前时间 |
+| `event_attributes` | object/string | 否 | 事件相关的其他属性，可以是JSON对象或JSON字符串 |
+
+### 链接信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `link_id` | string | 否 | 短链接的唯一ID |
+| `link_slug` | string | 否 | 短链接的slug部分 |
+| `link_label` | string | 否 | 短链接的显示名称 |
+| `link_title` | string | 否 | 短链接的标题 |
+| `link_original_url` | string | 否 | 原始目标URL |
+| `link_attributes` | object/string | 否 | 链接相关的额外属性 |
+| `link_created_at` | string/Date | 否 | 链接创建时间 |
+| `link_expires_at` | string/Date | 否 | 链接过期时间 |
+| `link_tags` | array/string | 否 | 链接标签，可以是数组或JSON字符串 |
+
+### 用户信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `user_id` | string | 否 | 用户ID |
+| `user_name` | string | 否 | 用户名称 |
+| `user_email` | string | 否 | 用户邮箱 |
+| `user_attributes` | object/string | 否 | 用户相关的其他属性 |
+
+### 团队和项目信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `team_id` | string | 否 | 团队ID |
+| `team_name` | string | 否 | 团队名称 |
+| `team_attributes` | object/string | 否 | 团队相关的其他属性 |
+| `project_id` | string | 否 | 项目ID |
+| `project_name` | string | 否 | 项目名称 |
+| `project_attributes` | object/string | 否 | 项目相关的其他属性 |
+
+### 二维码信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `qr_code_id` | string | 否 | 二维码ID |
+| `qr_code_name` | string | 否 | 二维码名称 |
+| `qr_code_attributes` | object/string | 否 | 二维码相关的其他属性 |
+
+### 访问者信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `visitor_id` | string | 否 | 访问者唯一标识符，不提供时自动生成 |
+| `session_id` | string | 否 | 会话ID，不提供时自动生成 |
+| `ip_address` | string | 否 | 访问者IP地址，默认从请求头获取 |
+| `country` | string | 否 | 访问者所在国家 |
+| `city` | string | 否 | 访问者所在城市 |
+| `device_type` | string | 否 | 设备类型 (如 desktop, mobile, tablet) |
+| `browser` | string | 否 | 浏览器名称 |
+| `os` | string | 否 | 操作系统 |
+| `user_agent` | string | 否 | 用户代理字符串，默认从请求头获取 |
+
+### 引荐来源信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `referrer` | string | 否 | 引荐URL，默认从请求头获取 |
+| `utm_source` | string | 否 | UTM来源参数 |
+| `utm_medium` | string | 否 | UTM媒介参数 |
+| `utm_campaign` | string | 否 | UTM活动参数 |
+| `utm_term` | string | 否 | UTM术语参数 |
+| `utm_content` | string | 否 | UTM内容参数 |
+
+### 交互信息
+| 参数 | 类型 | 必填 | 描述 |
+|------|------|------|------|
+| `time_spent_sec` | number | 否 | 用户在页面上停留的时间（秒），默认0 |
+| `is_bounce` | boolean | 否 | 是否是跳出（只访问一个页面），默认true |
+| `is_qr_scan` | boolean | 否 | 是否来自二维码扫描，默认false |
+| `conversion_type` | string | 否 | 转化类型 |
+| `conversion_value` | number | 否 | 转化价值，默认0 |
+
+## 响应格式
+
+### 成功响应 (201 Created)
+```json
+{
+  "success": true,
+  "message": "Event tracked successfully",
+  "event_id": "uuid-of-tracked-event"
+}
+```
+
+### 错误响应
+
+#### 缺少必填字段 (400 Bad Request)
+```json
+{
+  "error": "Missing required field: event_type"
+}
+```
+
+#### 服务器错误 (500 Internal Server Error)
+```json
+{
+  "error": "Failed to track event",
+  "details": "具体错误信息"
+}
+```
+
+## 使用示例
+
+### 基本事件跟踪请求
+```javascript
+fetch('/api/events/track', {
+  method: 'POST',
+  headers: {
+    'Content-Type': 'application/json'
+  },
+  body: JSON.stringify({
+    event_type: 'click',
+    link_id: 'abc123',
+    link_slug: 'promo-summer',
+    link_original_url: 'https://example.com/summer-promotion'
+  })
+})
+```
+
+### 详细事件跟踪请求
+```javascript
+fetch('/api/events/track', {
+  method: 'POST',
+  headers: {
+    'Content-Type': 'application/json'
+  },
+  body: JSON.stringify({
+    event_type: 'conversion',
+    link_id: 'abc123',
+    link_slug: 'promo-summer',
+    link_original_url: 'https://example.com/summer-promotion',
+    event_attributes: {
+      page: '/checkout',
+      product_id: 'xyz789'
+    },
+    user_id: 'user123',
+    team_id: 'team456',
+    project_id: 'proj789',
+    visitor_id: 'vis987',
+    is_bounce: false,
+    time_spent_sec: 120,
+    conversion_type: 'purchase',
+    conversion_value: 99.99,
+    utm_source: 'email',
+    utm_campaign: 'summer_sale'
+  })
+})
+```
+
+## 注意事项
+- 所有对象类型的字段（如 `event_attributes`）可以作为对象或预先格式化的JSON字符串传递
+- 如果不提供 `event_id`、`visitor_id` 或 `session_id`，系统将自动生成
+- 时间戳字段接受ISO格式的日期字符串，并会被转换为ClickHouse兼容的格式
+
+
+UTM 测试示例。1. 电子邮件营销链接
+https://short.domain.com/summer?utm_source=newsletter&utm_medium=email&utm_campaign=summer_promo&utm_term=discount&utm_content=header
+说明: 用于电子邮件营销活动，跟踪用户从邮件头部横幅点击的流量。
+
+2. 社交媒体广告链接
+https://short.domain.com/product?utm_source=instagram&utm_medium=social&utm_campaign=fall_collection&utm_content=story
+说明: 用于 Instagram Story 广告，跟踪用户从社交媒体故事广告点击的情况。
+
+3. 搜索引擎广告链接
+https://short.domain.com/service?utm_source=google&utm_medium=cpc&utm_campaign=brand_terms&utm_term=service+name
+说明: 用于 Google Ads 广告，跟踪用户从搜索引擎付费广告点击的流量，特别是针对特定搜索词。
+
+4. QR 码链接
+https://short.domain.com/event?utm_source=flyer&utm_medium=print&utm_campaign=local_event&utm_content=qr_code&source=qr
+说明: 用于打印材料上的 QR 码，跟踪用户扫描实体宣传资料的情况。
+
+5. 合作伙伴引荐链接
+https://short.domain.com/partner?utm_source=affiliate&utm_medium=referral&utm_campaign=partner_program&utm_content=banner
+说明: 用于合作伙伴网站上的推广横幅，跟踪来自联盟营销的转化率。
+
+
+https://upj.to/5seaii?utm_source=newsletter&utm_medium=email&utm_campaign=summer_promo&utm_term=discount&utm_content=header
+
+https://upj.to/5seaii?utm_source=instagram&utm_medium=social&utm_campaign=fall_collection&utm_content=story
+
+https://upj.to/5seaii?utm_source=google&utm_medium=cpc&utm_campaign=brand_terms&utm_term=service+name
+
+
+https://upj.to/5seaii?utm_source=flyer&utm_medium=print&utm_campaign=local_event&utm_content=qr_code&source=qr
+
+https://upj.to/5seaii?utm_source=affiliate&utm_medium=referral&utm_campaign=partner_program&utm_content=banner

app/api/events/track/route.ts

@@ -81,6 +81,8 @@ export async function POST(req: NextRequest) {
       utm_source: eventData.utm_source || '',
       utm_medium: eventData.utm_medium || '',
       utm_campaign: eventData.utm_campaign || '',
+      utm_term: eventData.utm_term || '',
+      utm_content: eventData.utm_content || '',
       
       // Interaction information
       time_spent_sec: eventData.time_spent_sec || 0,

app/api/events/utm/route.ts

@@ -0,0 +1,201 @@
+import { NextRequest, NextResponse } from 'next/server';
+import clickhouse from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+// 辅助函数，将日期格式化为标准格式
+function formatDateTime(dateString: string): string {
+  const date = new Date(dateString);
+  return date.toISOString().split('.')[0];
+}
+
+export async function GET(request: NextRequest) {
+  try {
+    const searchParams = request.nextUrl.searchParams;
+    
+    // 获取过滤参数
+    const startTime = searchParams.get('startTime');
+    const endTime = searchParams.get('endTime');
+    const linkId = searchParams.get('linkId');
+    const subpath = searchParams.get('subpath');
+    
+    // 获取团队、项目和标签筛选参数
+    const teamIds = searchParams.getAll('teamId');
+    const projectIds = searchParams.getAll('projectId');
+    const tagIds = searchParams.getAll('tagId');
+    const tagNames = searchParams.getAll('tagName');
+    
+    // 获取UTM类型参数
+    const utmType = searchParams.get('utmType') || 'source';
+    
+    // 添加调试日志
+    console.log('UTM API received parameters:', {
+      startTime,
+      endTime,
+      linkId,
+      subpath,
+      teamIds,
+      projectIds,
+      tagIds,
+      tagNames,
+      utmType,
+      url: request.url
+    });
+    
+    // 构建WHERE子句
+    let whereClause = '';
+    const conditions = [];
+    
+    if (startTime) {
+      conditions.push(`event_time >= toDateTime('${formatDateTime(startTime)}')`);
+    }
+    
+    if (endTime) {
+      conditions.push(`event_time <= toDateTime('${formatDateTime(endTime)}')`);
+    }
+    
+    if (linkId) {
+      conditions.push(`link_id = '${linkId}'`);
+    }
+    
+    // 添加子路径筛选 - 使用更精确的匹配方式
+    if (subpath && subpath.trim() !== '') {
+      console.log('====== UTM API SUBPATH DEBUG ======');
+      console.log('Raw subpath param:', subpath);
+      
+      // 清理并准备subpath值
+      let cleanSubpath = subpath.trim();
+      // 移除开头的斜杠以便匹配
+      if (cleanSubpath.startsWith('/')) {
+        cleanSubpath = cleanSubpath.substring(1);
+      }
+      // 移除结尾的斜杠以便匹配
+      if (cleanSubpath.endsWith('/')) {
+        cleanSubpath = cleanSubpath.substring(0, cleanSubpath.length - 1);
+      }
+      
+      console.log('Cleaned subpath:', cleanSubpath);
+      
+      // 使用正则表达式匹配URL中的第二个路径部分
+      // 示例: 在 "https://abc.com/slug/subpath/" 中匹配 "subpath"
+      const condition = `match(JSONExtractString(event_attributes, 'full_url'), '/[^/]+/${cleanSubpath}(/|\\\\?|$)')`;
+      
+      console.log('Final SQL condition:', condition);
+      console.log('==================================');
+      
+      conditions.push(condition);
+    }
+    
+    // 添加团队筛选
+    if (teamIds && teamIds.length > 0) {
+      // 如果只有一个团队ID
+      if (teamIds.length === 1) {
+        conditions.push(`team_id = '${teamIds[0]}'`);
+      } else {
+        // 多个团队ID
+        conditions.push(`team_id IN ('${teamIds.join("','")}')`);
+      }
+    }
+    
+    // 添加项目筛选
+    if (projectIds && projectIds.length > 0) {
+      // 如果只有一个项目ID
+      if (projectIds.length === 1) {
+        conditions.push(`project_id = '${projectIds[0]}'`);
+      } else {
+        // 多个项目ID
+        conditions.push(`project_id IN ('${projectIds.join("','")}')`);
+      }
+    }
+    
+    // 添加标签筛选
+    if ((tagIds && tagIds.length > 0) || (tagNames && tagNames.length > 0)) {
+      // 优先使用tagNames，如果有的话
+      const tagsToUse = tagNames.length > 0 ? tagNames : tagIds;
+      
+      // 使用与buildFilter函数相同的处理方式
+      const tagConditions = tagsToUse.map(tag => 
+        `link_tags LIKE '%${tag}%'`
+      );
+      conditions.push(`(${tagConditions.join(' OR ')})`);
+    }
+    
+    if (conditions.length > 0) {
+      whereClause = `WHERE ${conditions.join(' AND ')}`;
+    }
+    
+    // 确定要分组的UTM字段
+    let utmField;
+    switch (utmType) {
+      case 'source':
+        utmField = 'utm_source';
+        break;
+      case 'medium':
+        utmField = 'utm_medium';
+        break;
+      case 'campaign':
+        utmField = 'utm_campaign';
+        break;
+      case 'term':
+        utmField = 'utm_term';
+        break;
+      case 'content':
+        utmField = 'utm_content';
+        break;
+      default:
+        utmField = 'utm_source';
+    }
+    
+    // 构建SQL查询
+    const query = `
+      SELECT 
+        ${utmField} AS utm_value,
+        COUNT(*) AS clicks,
+        uniqExact(visitor_id) AS visitors,
+        round(AVG(time_spent_sec), 2) AS avg_time_spent,
+        countIf(is_bounce = 1) AS bounces,
+        countIf(conversion_type IN ('visit', 'stay', 'interact', 'signup', 'subscription', 'purchase')) AS conversions
+      FROM shorturl_analytics.events
+      ${whereClause}
+      ${whereClause ? 'AND' : 'WHERE'} ${utmField} != ''
+      GROUP BY utm_value
+      ORDER BY clicks DESC
+      LIMIT 100
+    `;
+    
+    // 执行查询
+    const result = await clickhouse.query({
+      query,
+      format: 'JSONEachRow',
+    });
+    
+    // 获取查询结果
+    const rows = await result.json();
+    const data = rows as UtmData[];
+    
+    // 返回数据
+    const response: ApiResponse<UtmData[]> = {
+      success: true,
+      data
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching UTM data:', error);
+    
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    
+    return NextResponse.json(response, { status: 500 });
+  }
+}

app/api/shortlinks/[id]/route.ts

@@ -0,0 +1,141 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(
+  request: NextRequest,
+  { params }: { params: { id: string } }
+) {
+  try {
+    // Get the id from the URL parameters
+    const { id } = params;
+    
+    if (!id) {
+      return NextResponse.json({
+        success: false,
+        error: 'ID parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by ID:', id);
+    
+    // Query to fetch a single shortlink by id
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors,
+        domain
+      FROM shorturl_analytics.shorturl
+      WHERE id = '${id}' AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified ID
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0] as any;
+    
+    // Extract shortUrl from attributes
+    let shortUrl = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes) as { shortUrl?: string };
+        shortUrl = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams: any[] = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags: any[] = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects: any[] = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrl,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag: any) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: shortlink.domain || (shortUrl ? new URL(shortUrl).hostname : '')
+    };
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by ID:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/byUrl/route.ts

@@ -0,0 +1,143 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get the url from query parameters
+    const searchParams = request.nextUrl.searchParams;
+    const url = searchParams.get('url');
+    
+    if (!url) {
+      return NextResponse.json({
+        success: false,
+        error: 'URL parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by URL:', url);
+    
+    // Query to fetch a single shortlink by shortUrl in attributes
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors,
+        domain
+      FROM shorturl_analytics.shorturl
+      WHERE JSONHas(attributes, 'shortUrl') 
+        AND JSONExtractString(attributes, 'shortUrl') = '${url}'
+        AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified URL
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0];
+    
+    // Extract shortUrl from attributes
+    let shortUrl = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes);
+        shortUrl = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrl,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: shortlink.domain || (shortUrl ? new URL(shortUrl).hostname : '')
+    };
+    
+    console.log('Shortlink data formatted with externalId:', shortlink.external_id, 'Final object:', formattedShortlink);
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by URL:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/exact/route.ts

@@ -0,0 +1,143 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get the url from query parameters
+    const searchParams = request.nextUrl.searchParams;
+    const shortUrl = searchParams.get('shortUrl');
+    
+    if (!shortUrl) {
+      return NextResponse.json({
+        success: false,
+        error: 'shortUrl parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by exact shortUrl:', shortUrl);
+    
+    // Query to fetch a single shortlink by shortUrl in attributes
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors,
+        domain
+      FROM shorturl_analytics.shorturl
+      WHERE JSONHas(attributes, 'shortUrl') 
+        AND JSONExtractString(attributes, 'shortUrl') = '${shortUrl}'
+        AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified URL
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0] as Record<string, any>;
+    
+    // Extract shortUrl from attributes
+    let shortUrlValue = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes) as { shortUrl?: string };
+        shortUrlValue = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams: any[] = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags: any[] = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects: any[] = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrlValue,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag: any) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: shortlink.domain || (shortUrlValue ? new URL(shortUrlValue).hostname : '')
+    };
+    
+    console.log('Formatted shortlink with externalId:', shortlink.external_id);
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by exact URL:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/route.ts

@@ -0,0 +1,104 @@
+import { NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import { NextRequest } from 'next/server';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get pagination and filter parameters from the URL
+    const searchParams = request.nextUrl.searchParams;
+    const page = parseInt(searchParams.get('page') || '1', 10);
+    const pageSize = parseInt(searchParams.get('page_size') || '10', 10);
+    const search = searchParams.get('search');
+    const team = searchParams.get('team');
+
+    // Calculate OFFSET
+    const offset = (page - 1) * pageSize;
+
+    // Build WHERE conditions
+    const whereConditions = ['deleted_at IS NULL'];
+    
+    if (search) {
+      // Expand search to include more fields: slug, shortUrl in attributes, team name, tag name, original_url
+      whereConditions.push(`(
+        slug ILIKE '%${search}%' OR 
+        original_url ILIKE '%${search}%' OR 
+        title ILIKE '%${search}%' OR
+        JSONHas(attributes, 'shortUrl') AND JSONExtractString(attributes, 'shortUrl') ILIKE '%${search}%' OR
+        arrayExists(x -> JSONExtractString(x, 'team_name') ILIKE '%${search}%', JSONExtractArrayRaw(teams)) OR
+        arrayExists(x -> JSONExtractString(x, 'tag_name') ILIKE '%${search}%', JSONExtractArrayRaw(tags))
+      )`);
+    }
+    
+    if (team) {
+      whereConditions.push(`arrayExists(x -> JSONExtractString(x, 'team_id') = '${team}', JSONExtractArrayRaw(teams))`);
+    }
+    
+    const whereClause = whereConditions.join(' AND ');
+
+    // First query to get total count
+    const countQuery = `
+      SELECT count(*) as total
+      FROM shorturl_analytics.shorturl
+      WHERE ${whereClause}
+    `;
+
+    const countResult = await executeQuery(countQuery);
+    // Handle the result safely by using an explicit type check
+    const total = Array.isArray(countResult) && countResult.length > 0 && typeof countResult[0] === 'object' && countResult[0] !== null && 'total' in countResult[0]
+      ? Number(countResult[0].total)
+      : 0;
+    const totalPages = Math.ceil(total / pageSize);
+
+    // Main query with pagination
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors,
+        domain
+      FROM shorturl_analytics.shorturl
+      WHERE ${whereClause}
+      ORDER BY created_at DESC
+      LIMIT ${pageSize} OFFSET ${offset}
+    `;
+
+    // Execute the query using the shared client
+    const rows = await executeQuery(query);
+
+    // Return the data with pagination metadata
+    return NextResponse.json({ 
+      links: rows,
+      total: total,
+      total_pages: totalPages,
+      page: page,
+      page_size: pageSize
+    });
+  } catch (error) {
+    console.error('Error fetching shortlinks from ClickHouse:', error);
+    return NextResponse.json(
+      { error: 'Failed to fetch shortlinks' },
+      { status: 500 }
+    );
+  }
+} 

app/api/types.ts

@@ -54,6 +54,8 @@ export interface Event {
   utm_source: string;
   utm_medium: string;
   utm_campaign: string;
+  utm_term: string;
+  utm_content: string;
   
   // 交互信息
   time_spent_sec: number;

app/components/analytics/PathAnalytics.tsx

@@ -0,0 +1,162 @@
+import React, { useState, useEffect } from 'react';
+
+interface PathAnalyticsProps {
+  startTime: string;
+  endTime: string;
+  linkId?: string;
+  onPathClick?: (path: string) => void;
+}
+
+interface PathData {
+  path: string;
+  count: number;
+  percentage: number;
+}
+
+const PathAnalytics: React.FC<PathAnalyticsProps> = ({ startTime, endTime, linkId, onPathClick }) => {
+  const [loading, setLoading] = useState(true);
+  const [pathData, setPathData] = useState<PathData[]>([]);
+  const [error, setError] = useState<string | null>(null);
+
+  useEffect(() => {
+    if (!linkId) {
+      setLoading(false);
+      return;
+    }
+    
+    const fetchPathData = async () => {
+      try {
+        const params = new URLSearchParams({
+          startTime,
+          endTime,
+          linkId
+        });
+        
+        const response = await fetch(`/api/events/path-analytics?${params.toString()}`);
+        
+        if (!response.ok) {
+          throw new Error('Failed to fetch path analytics data');
+        }
+        
+        const result = await response.json();
+        
+        if (result.success && result.data) {
+          // 自定义处理路径数据，根据是否有子路径来分组
+          const rawData = result.data;
+          const pathMap = new Map<string, number>();
+          let totalClicks = 0;
+          
+          rawData.forEach((item: PathData) => {
+            const urlPath = item.path.split('?')[0];
+            totalClicks += item.count;
+            
+            // 解析路径，检查是否有子路径
+            const pathParts = urlPath.split('/').filter(Boolean);
+            
+            // 基础路径（例如/5seaii）或者带有查询参数但没有子路径的路径视为同一个路径
+            // 子路径（例如/5seaii/bbbbb）单独统计
+            const groupKey = pathParts.length > 1 ? urlPath : `/${pathParts[0]}`;
+            
+            const currentCount = pathMap.get(groupKey) || 0;
+            pathMap.set(groupKey, currentCount + item.count);
+          });
+          
+          // 转换回数组并排序
+          const groupedPathData = Array.from(pathMap.entries())
+            .map(([path, count]) => ({
+              path,
+              count,
+              percentage: totalClicks > 0 ? count / totalClicks : 0,
+            }))
+            .sort((a, b) => b.count - a.count);
+          
+          setPathData(groupedPathData);
+        } else {
+          setError(result.error || 'Failed to load path analytics');
+        }
+      } catch (err) {
+        setError(err instanceof Error ? err.message : 'An error occurred');
+      } finally {
+        setLoading(false);
+      }
+    };
+    
+    fetchPathData();
+  }, [startTime, endTime, linkId]);
+  
+  const handlePathClick = (path: string, e: React.MouseEvent) => {
+    e.preventDefault();
+    console.log('====== PATH CLICK DEBUG ======');
+    console.log('Path value:', path);
+    console.log('Path type:', typeof path);
+    console.log('Path length:', path.length);
+    console.log('Path chars:', Array.from(path).map(c => c.charCodeAt(0)));
+    console.log('==============================');
+    if (onPathClick) {
+      onPathClick(path);
+    }
+  };
+  
+  if (loading) {
+    return <div className="py-8 flex justify-center">
+      <div className="animate-spin rounded-full h-8 w-8 border-t-2 border-b-2 border-blue-500" />
+    </div>;
+  }
+  
+  if (error) {
+    return <div className="py-4 text-red-500">{error}</div>;
+  }
+  
+  if (!linkId) {
+    return <div className="py-4 text-gray-500">Select a specific link to view path analytics.</div>;
+  }
+  
+  if (pathData.length === 0) {
+    return <div className="py-4 text-gray-500">No path data available for this link.</div>;
+  }
+  
+  return (
+    <div>
+      <div className="text-sm text-gray-500 mb-4">
+        Note: Paths are grouped by subpath. URLs with different query parameters but the same base path (without subpath) are grouped together.
+      </div>
+      <div className="overflow-x-auto">
+        <table className="min-w-full divide-y divide-gray-200">
+          <thead>
+            <tr>
+              <th className="px-6 py-3 bg-gray-50 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Path</th>
+              <th className="px-6 py-3 bg-gray-50 text-right text-xs font-medium text-gray-500 uppercase tracking-wider">Clicks</th>
+              <th className="px-6 py-3 bg-gray-50 text-right text-xs font-medium text-gray-500 uppercase tracking-wider">Percentage</th>
+            </tr>
+          </thead>
+          <tbody className="bg-white divide-y divide-gray-200">
+            {pathData.map((item, index) => (
+              <tr key={index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
+                <td className="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">
+                  <a 
+                    href="#" 
+                    className="hover:text-blue-600 hover:underline cursor-pointer"
+                    onClick={(e) => handlePathClick(item.path, e)}
+                  >
+                    {item.path}
+                  </a>
+                </td>
+                <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500 text-right">{item.count}</td>
+                <td className="px-6 py-4 whitespace-nowrap text-right">
+                  <div className="flex items-center justify-end">
+                    <span className="text-sm text-gray-500 mr-2">{(item.percentage * 100).toFixed(1)}%</span>
+                    <div className="w-32 bg-gray-200 rounded-full h-2.5">
+                      <div className="bg-blue-600 h-2.5 rounded-full" style={{ width: `${item.percentage * 100}%` }}></div>
+                    </div>
+                  </div>
+                </td>
+              </tr>
+            ))}
+          </tbody>
+        </table>
+      </div>
+    </div>
+  );
+};
+
+export default PathAnalytics; 

app/components/analytics/UtmAnalytics.tsx

@@ -0,0 +1,205 @@
+"use client";
+
+import { useState, useEffect } from 'react';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+interface UtmAnalyticsProps {
+  startTime?: string;
+  endTime?: string;
+  linkId?: string;
+  teamIds?: string[];
+  projectIds?: string[];
+  tagIds?: string[];
+  subpath?: string;
+}
+
+export default function UtmAnalytics({ startTime, endTime, linkId, teamIds, projectIds, tagIds, subpath }: UtmAnalyticsProps) {
+  const [activeTab, setActiveTab] = useState<string>('source');
+  const [utmData, setUtmData] = useState<UtmData[]>([]);
+  const [isLoading, setIsLoading] = useState<boolean>(false);
+  const [error, setError] = useState<string | null>(null);
+  
+  // 加载UTM数据
+  useEffect(() => {
+    const fetchUtmData = async () => {
+      setIsLoading(true);
+      setError(null);
+      
+      try {
+        // 构建URL参数
+        const params = new URLSearchParams();
+        if (startTime) params.append('startTime', startTime);
+        if (endTime) params.append('endTime', endTime);
+        if (linkId) params.append('linkId', linkId);
+        if (subpath) params.append('subpath', subpath);
+        params.append('utmType', activeTab);
+        
+        // 添加团队ID参数
+        if (teamIds && teamIds.length > 0) {
+          teamIds.forEach(id => params.append('teamId', id));
+        }
+        
+        // 添加项目ID参数
+        if (projectIds && projectIds.length > 0) {
+          projectIds.forEach(id => params.append('projectId', id));
+        }
+        
+        // 添加标签名称参数
+        if (tagIds && tagIds.length > 0) {
+          tagIds.forEach(tagName => params.append('tagName', tagName));
+        }
+        
+        // 发送请求
+        const response = await fetch(`/api/events/utm?${params}`);
+        
+        if (!response.ok) {
+          throw new Error('Failed to fetch UTM data');
+        }
+        
+        const result = await response.json();
+        
+        if (result.success) {
+          setUtmData(result.data || []);
+        } else {
+          throw new Error(result.error || 'Failed to fetch UTM data');
+        }
+      } catch (err) {
+        setError(err instanceof Error ? err.message : 'Unknown error occurred');
+        console.error('Error fetching UTM data:', err);
+      } finally {
+        setIsLoading(false);
+      }
+    };
+    
+    fetchUtmData();
+  }, [activeTab, startTime, endTime, linkId, teamIds, projectIds, tagIds, subpath]);
+  
+  // 安全地格式化数字
+  const formatNumber = (value: number | undefined | null): string => {
+    if (value === undefined || value === null) return '0';
+    return value.toLocaleString();
+  };
+  
+  return (
+    <div className="bg-white rounded-lg shadow p-6 mb-8">
+      <h2 className="text-lg font-semibold text-gray-900 mb-4">UTM Parameters</h2>
+      
+      <div className="mb-4 border-b">
+        <div className="flex">
+          <button 
+            onClick={() => setActiveTab('source')} 
+            className={`px-4 py-2 ${activeTab === 'source' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Source
+          </button>
+          <button 
+            onClick={() => setActiveTab('medium')} 
+            className={`px-4 py-2 ${activeTab === 'medium' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Medium
+          </button>
+          <button 
+            onClick={() => setActiveTab('campaign')} 
+            className={`px-4 py-2 ${activeTab === 'campaign' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Campaign
+          </button>
+          <button 
+            onClick={() => setActiveTab('term')} 
+            className={`px-4 py-2 ${activeTab === 'term' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Term
+          </button>
+          <button 
+            onClick={() => setActiveTab('content')} 
+            className={`px-4 py-2 ${activeTab === 'content' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Content
+          </button>
+        </div>
+      </div>
+      
+      {isLoading ? (
+        <div className="flex justify-center items-center py-8">
+          <div className="animate-spin rounded-full h-6 w-6 border-b-2 border-blue-500"></div>
+          <span className="ml-2 text-gray-500">Loading...</span>
+        </div>
+      ) : error ? (
+        <div className="text-red-500 text-center py-8">
+          Error: {error}
+        </div>
+      ) : utmData.length === 0 ? (
+        <div className="text-gray-500 text-center py-8">
+          No data available
+        </div>
+      ) : (
+        <div className="overflow-x-auto">
+          <table className="min-w-full divide-y divide-gray-200">
+            <thead className="bg-gray-50">
+              <tr>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  {activeTab === 'source' ? 'Source' : 
+                   activeTab === 'medium' ? 'Medium' : 
+                   activeTab === 'campaign' ? 'Campaign' : 
+                   activeTab === 'term' ? 'Term' : 'Content'}
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Clicks
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Visitors
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Avg. Time
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Bounce Rate
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Conversions
+                </th>
+              </tr>
+            </thead>
+            <tbody className="bg-white divide-y divide-gray-200">
+              {utmData.map((item, index) => {
+                const bounceRate = item.clicks > 0 ? (item.bounces / item.clicks) * 100 : 0;
+                const conversionRate = item.clicks > 0 ? (item.conversions / item.clicks) * 100 : 0;
+                
+                return (
+                  <tr key={index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.utm_value || 'Unknown'}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.clicks)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.visitors)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.avg_time_spent.toFixed(1)}s
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {bounceRate.toFixed(1)}%
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.conversions)} ({conversionRate.toFixed(1)}%)
+                    </td>
+                  </tr>
+                );
+              })}
+            </tbody>
+          </table>
+        </div>
+      )}
+    </div>
+  );
+} 

app/components/charts/TimeSeriesChart.tsx

@@ -137,6 +137,11 @@ export default function TimeSeriesChart({ data }: TimeSeriesChartProps) {
                   return date.toLocaleDateString();
                 }
                 return '';
+              },
+              label: (context) => {
+                const label = context.dataset.label || '';
+                const value = context.parsed.y;
+                return `${label}: ${Math.round(value)}`;
               }
             }
           }
@@ -160,9 +165,9 @@ export default function TimeSeriesChart({ data }: TimeSeriesChartProps) {
               callback: (value: number) => {
                 if (!value && value !== 0) return '';
                 if (value >= 1000) {
-                  return `${(value / 1000).toFixed(1)}k`;
+                  return `${Math.round(value / 1000)}k`;
                 }
-                return value;
+                return Math.round(value);
               }
             }
           }

app/components/layout/Header.tsx

@@ -14,7 +14,7 @@ export default function Header() {
     <header className="w-full py-4 border-b border-gray-200 bg-white">
       <div className="container flex items-center justify-between px-4 mx-auto">
         <div className="flex items-center space-x-4">
-          <Link href="/" className="flex items-center space-x-2">
+          <Link href="/analytics" className="flex items-center space-x-2">
             <svg
               className="w-6 h-6 text-blue-500"
               xmlns="http://www.w3.org/2000/svg"
@@ -30,6 +30,23 @@ export default function Header() {
             </svg>
             <span className="text-xl font-bold text-gray-900">ShortURL Analytics</span>
           </Link>
+          
+          {user && (
+            <nav className="ml-6">
+              <ul className="flex space-x-4">
+                <li>
+                  <Link href="/analytics" className="text-sm text-gray-700 hover:text-blue-500">
+                    Analytics
+                  </Link>
+                </li>
+                <li>
+                  <Link href="/links" className="text-sm text-gray-700 hover:text-blue-500">
+                    Short Links
+                  </Link>
+                </li>
+              </ul>
+            </nav>
+          )}
         </div>
         
         {user && (

app/ip-test/page.tsx

@@ -1,10 +0,0 @@
-import IpLocationTest from '../components/ipLocationTest';
-
-export default function IpTestPage() {
-  return (
-    <div className="container mx-auto p-4 max-w-4xl">
-      <h1 className="text-2xl font-bold mb-6">IP to Location Test</h1>
-      <IpLocationTest />
-    </div>
-  );
-} 

app/links/page.tsx

@@ -0,0 +1,662 @@
+"use client";
+
+import { useEffect, useState } from 'react';
+import { getSupabaseClient } from '../utils/supabase';
+import { AuthChangeEvent } from '@supabase/supabase-js';
+import { Loader2, ExternalLink, Search } from 'lucide-react';
+import { TeamSelector } from '@/app/components/ui/TeamSelector';
+import { useRouter } from 'next/navigation';
+import { useShortUrlStore, ShortUrlData } from '@/app/utils/store';
+
+// Define attribute type to avoid using 'any'
+interface LinkAttributes {
+  title?: string;
+  name?: string;
+  slug?: string;
+  original_url?: string;
+  originalUrl?: string;
+  visits?: number;
+  click_count?: number;
+  team_id?: string;
+  team_name?: string;
+  tags?: string[];
+  [key: string]: unknown;
+}
+
+// 更新 ShortLink 类型定义以匹配 ClickHouse 数据结构
+interface ShortLink {
+  id: string;
+  external_id?: string;
+  type?: string;
+  slug?: string;
+  original_url?: string;
+  title?: string;
+  description?: string;
+  attributes: string | Record<string, unknown>;
+  schema_version?: number;
+  creator_id?: string;
+  creator_email?: string;
+  creator_name?: string;
+  created_at: string;
+  updated_at?: string;
+  deleted_at?: string | null;
+  projects?: string | Record<string, unknown>[];
+  teams?: string | Record<string, unknown>[];
+  tags?: string | Record<string, unknown>[];
+  qr_codes?: string | Record<string, unknown>[];
+  channels?: string | Record<string, unknown>[];
+  favorites?: string | Record<string, unknown>[];
+  expires_at?: string | null;
+  click_count?: number;
+  unique_visitors?: number;
+  domain?: string;
+}
+
+// Define ClickHouse shorturl type
+interface ClickHouseShortUrl {
+  id: string;
+  external_id: string;
+  type: string;
+  slug: string;
+  original_url: string;
+  title: string;
+  description: string;
+  attributes: string; // JSON string
+  schema_version: number;
+  creator_id: string;
+  creator_email: string;
+  creator_name: string;
+  created_at: string;
+  updated_at: string;
+  deleted_at: string | null;
+  projects: string; // JSON string
+  teams: string; // JSON string
+  tags: string; // JSON string
+  qr_codes: string; // JSON string
+  channels: string; // JSON string
+  favorites: string; // JSON string
+  expires_at: string | null;
+  click_count: number;
+  unique_visitors: number;
+  domain?: string; // 添加domain字段
+  link_attributes?: string; // Optional JSON string containing link-specific attributes
+}
+
+// 示例团队数据 - 实际应用中应从API获取
+const teams = [
+  { id: 'marketing', name: 'Marketing' },
+  { id: 'sales', name: 'Sales' },
+  { id: 'product', name: 'Product' },
+  { id: 'engineering', name: 'Engineering' }
+];
+
+// 将 ClickHouse 数据转换为 ShortLink 格式
+const convertClickHouseToShortLink = (data: Record<string, unknown>): ShortLink => {
+  return {
+    ...data as any, // 使用类型断言处理泛型记录转换
+    // 确保关键字段存在
+    id: data.id as string || '',
+    created_at: data.created_at as string || new Date().toISOString(),
+    attributes: data.attributes || '{}'
+  };
+};
+
+export default function LinksPage() {
+  const [loading, setLoading] = useState(true);
+  const [error, setError] = useState<string | null>(null);
+  const [links, setLinks] = useState<ShortLink[]>([]);
+  const [searchQuery, setSearchQuery] = useState('');
+  const [teamFilter, setTeamFilter] = useState<string | null>(null);
+  const [currentPage, setCurrentPage] = useState(1);
+  const [pageSize, setPageSize] = useState(10);
+  const [totalLinks, setTotalLinks] = useState(0);
+  const [totalPages, setTotalPages] = useState(0);
+  const [searchDebounce, setSearchDebounce] = useState<NodeJS.Timeout | null>(null);
+  const router = useRouter();
+  
+  // 使用 Zustand store
+  const { setSelectedShortUrl } = useShortUrlStore();
+
+  // 处理点击链接行
+  const handleRowClick = (link: any) => {
+    // 解析 attributes 字符串为对象
+    let attributes: Record<string, any> = {};
+    try {
+      if (link.attributes && typeof link.attributes === 'string') {
+        attributes = JSON.parse(link.attributes || '{}');
+      }
+    } catch (e) {
+      console.error('Error parsing link attributes:', e);
+    }
+    
+    // 解析 teams 字符串为数组
+    let teams: any[] = [];
+    try {
+      if (link.teams && typeof link.teams === 'string') {
+        teams = JSON.parse(link.teams || '[]');
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // 解析 projects 字符串为数组
+    let projects: any[] = [];
+    try {
+      if (link.projects && typeof link.projects === 'string') {
+        projects = JSON.parse(link.projects || '[]');
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // 解析 tags 字符串为数组
+    let tags: string[] = [];
+    try {
+      if (link.tags && typeof link.tags === 'string') {
+        const parsedTags = JSON.parse(link.tags);
+        if (Array.isArray(parsedTags)) {
+          tags = parsedTags.map((tag: { tag_name?: string }) => tag.tag_name || '');
+        }
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+
+    // 确保 shortUrl 存在
+    const shortUrlValue = attributes.shortUrl || '';
+    
+    // 提取用于显示的字段
+    const shortUrlData = {
+      id: link.id,
+      externalId: link.external_id, // 明确添加 externalId 字段
+      slug: link.slug,
+      originalUrl: link.original_url,
+      title: link.title,
+      shortUrl: shortUrlValue,
+      teams: teams,
+      projects: projects,
+      tags: tags,
+      createdAt: link.created_at,
+      domain: link.domain || (shortUrlValue ? new URL(shortUrlValue).hostname : '')
+    };
+    
+    // 打印完整数据，确保 externalId 被包含
+    console.log('Setting shortURL data in store with externalId:', link.external_id);
+    
+    // 将数据保存到 Zustand store
+    setSelectedShortUrl(shortUrlData);
+    
+    // 导航到分析页面，并在 URL 中包含 shortUrl 参数
+    router.push(`/analytics?shorturl=${encodeURIComponent(shortUrlValue)}`);
+  };
+
+  // Extract link metadata from attributes
+  const getLinkMetadata = (link: ShortLink) => {
+    try {
+      // Parse attributes if it's a string
+      const attributes = typeof link.attributes === 'string'
+        ? JSON.parse(link.attributes)
+        : link.attributes || {};
+      
+      // Parse attributes to get domain if available
+      let domain = '';
+      try {
+        // 首先尝试使用link.domain字段
+        if (link.domain) {
+          domain = link.domain;
+        }
+        // 如果没有domain字段，从shortUrl中提取
+        else {
+          // Extract domain from shortUrl in attributes if available
+          const attributesObj = typeof link.attributes === 'string'
+            ? JSON.parse(link.attributes)
+            : link.attributes || {};
+          
+          if (attributesObj.shortUrl) {
+            try {
+              const urlObj = new URL(attributesObj.shortUrl);
+              domain = urlObj.hostname;
+            } catch (e) {
+              console.error('Error parsing shortUrl:', e);
+            }
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing attributes:', e);
+      }
+      
+      // Get team names
+      const teamNames: string[] = [];
+      try {
+        if (link.teams) {
+          const teams = typeof link.teams === 'string'
+            ? JSON.parse(link.teams)
+            : link.teams || [];
+          
+          if (Array.isArray(teams)) {
+            teams.forEach(team => {
+              if (team.team_name) {
+                teamNames.push(team.team_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing teams:', e);
+      }
+      
+      // Get project names
+      const projectNames: string[] = [];
+      try {
+        if (link.projects) {
+          const projects = typeof link.projects === 'string'
+            ? JSON.parse(link.projects)
+            : link.projects || [];
+          
+          if (Array.isArray(projects)) {
+            projects.forEach(project => {
+              if (project.project_name) {
+                projectNames.push(project.project_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing projects:', e);
+      }
+      
+      // Get tag names
+      const tagNames: string[] = [];
+      try {
+        if (link.tags) {
+          const tags = typeof link.tags === 'string'
+            ? JSON.parse(link.tags)
+            : link.tags || [];
+          
+          if (Array.isArray(tags)) {
+            tags.forEach(tag => {
+              if (tag.tag_name) {
+                tagNames.push(tag.tag_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing tags:', e);
+      }
+      
+      return {
+        title: link.title || attributes.title || 'Untitled',
+        slug: link.slug || attributes.slug || '',
+        domain: domain,
+        originalUrl: link.original_url || attributes.originalUrl || attributes.original_url || '',
+        teamNames: teamNames,
+        projectNames: projectNames,
+        tagNames: tagNames,
+        teamName: teamNames[0] || '',  // Keep for backward compatibility
+        createdAt: new Date(link.created_at).toLocaleDateString(),
+        visits: link.click_count || 0
+      };
+    } catch (error) {
+      console.error('Error parsing link metadata:', error);
+      return {
+        title: 'Error parsing data',
+        slug: '',
+        domain: 'shorturl.example.com',
+        originalUrl: '',
+        teamNames: [],
+        projectNames: [],
+        tagNames: [],
+        teamName: '',
+        createdAt: '',
+        visits: 0
+      };
+    }
+  };
+
+  useEffect(() => {
+    let isMounted = true;
+
+    const fetchLinks = async () => {
+      if (!isMounted) return;
+      setLoading(true);
+      setError(null);
+      
+      try {
+        // Fetch data from ClickHouse API with pagination parameters
+        const response = await fetch(`/api/shortlinks?page=${currentPage}&page_size=${pageSize}${searchQuery ? `&search=${encodeURIComponent(searchQuery)}` : ''}${teamFilter ? `&team=${encodeURIComponent(teamFilter)}` : ''}`);
+        
+        if (!response.ok) {
+          throw new Error(`Failed to fetch links: ${response.status} ${response.statusText}`);
+        }
+        
+        const data = await response.json();
+        
+        if (!data || !data.links || data.links.length === 0) {
+          if (isMounted) {
+            setLinks([]);
+            setTotalLinks(0);
+            setTotalPages(0);
+          }
+          return;
+        }
+        
+        // Convert ClickHouse data format to ShortLink format
+        const convertedLinks = data.links.map(convertClickHouseToShortLink);
+        
+        if (isMounted) {
+          setLinks(convertedLinks);
+          setTotalLinks(data.total || convertedLinks.length);
+          setTotalPages(data.total_pages || Math.ceil(data.total / pageSize) || 1);
+        }
+      } catch (err) {
+        if (isMounted) {
+          setError(err instanceof Error ? err.message : 'Failed to load short URLs');
+          console.error("Error fetching links:", err);
+        }
+      } finally {
+        if (isMounted) {
+          setLoading(false);
+        }
+      }
+    };
+
+    // Subscribe to user auth state
+    const supabase = getSupabaseClient();
+    const { data: { subscription } } = supabase.auth.onAuthStateChange(
+      (event: AuthChangeEvent) => {
+        if (event === 'SIGNED_IN' || event === 'USER_UPDATED') {
+          fetchLinks();
+        }
+        if (event === 'SIGNED_OUT') {
+          setLinks([]);
+        }
+      }
+    );
+
+    fetchLinks();
+
+    return () => {
+      isMounted = false;
+      subscription.unsubscribe();
+    };
+  }, [currentPage, pageSize, searchQuery, teamFilter]);
+
+  // Handle search input with debounce
+  const handleSearchChange = (e: React.ChangeEvent<HTMLInputElement>) => {
+    const value = e.target.value;
+    
+    // Clear any existing timeout
+    if (searchDebounce) {
+      clearTimeout(searchDebounce);
+    }
+    
+    // Set the input value immediately for UI feedback
+    setSearchQuery(value);
+    
+    // Set a timeout to actually perform the search
+    setSearchDebounce(setTimeout(() => {
+      setCurrentPage(1); // Reset to page 1 when searching
+    }, 500)); // 500ms debounce
+  };
+
+  if (loading && links.length === 0) {
+    return (
+      <div className="flex h-96 w-full items-center justify-center">
+        <Loader2 className="h-8 w-8 animate-spin text-gray-500" />
+      </div>
+    );
+  }
+
+  if (error) {
+    return (
+      <div className="flex h-96 w-full flex-col items-center justify-center text-red-500">
+        <p>Error loading shortcuts: {error}</p>
+        <button 
+          onClick={() => window.location.reload()} 
+          className="mt-4 rounded-md bg-blue-500 px-4 py-2 text-white hover:bg-blue-600"
+        >
+          Retry
+        </button>
+      </div>
+    );
+  }
+
+  return (
+    <div className="container mx-auto px-4 py-8">
+      <h1 className="mb-6 text-2xl font-bold text-gray-900">Short URL Links</h1>
+      
+      {/* Search and filters */}
+      <div className="mb-6 flex flex-wrap items-center gap-4">
+        <div className="relative flex-grow">
+          <Search className="absolute left-3 top-1/2 h-4 w-4 -translate-y-1/2 text-gray-400" />
+          <input
+            type="text"
+            placeholder="Search links..."
+            value={searchQuery}
+            onChange={handleSearchChange}
+            onKeyDown={(e) => {
+              if (e.key === 'Enter') {
+                setCurrentPage(1); // Reset to page 1 when searching
+              }
+            }}
+            className="w-full rounded-md border border-gray-300 py-2 pl-10 pr-4 text-sm focus:border-blue-500 focus:outline-none focus:ring-1 focus:ring-blue-500"
+          />
+        </div>
+        
+        <div className="flex items-center gap-2">
+          <TeamSelector 
+            value={teamFilter || ''}
+            onChange={(value) => {
+              // 如果是多选模式，值将是数组。对于空数组，设置为 null
+              if (Array.isArray(value)) {
+                setTeamFilter(value.length > 0 ? value[0] : null);
+              } else {
+                setTeamFilter(value || null);
+              }
+              setCurrentPage(1); // Reset to page 1 when filtering
+            }}
+            className="w-64"
+            multiple={true}
+          />
+        </div>
+      </div>
+      
+      {/* Links table */}
+      <div className="overflow-hidden rounded-lg border border-gray-200 shadow">
+        <table className="min-w-full divide-y divide-gray-200">
+          <thead className="bg-gray-50">
+            <tr>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Link</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Original URL</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Team</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Created</th>
+            </tr>
+          </thead>
+          <tbody className="divide-y divide-gray-200 bg-white">
+            {links.map(link => {
+              const metadata = getLinkMetadata(link);
+              const shortUrl = `https://${metadata.domain}/${metadata.slug}`;
+              
+              return (
+                <tr key={link.id} className="hover:bg-gray-50 cursor-pointer" onClick={() => handleRowClick(link)}>
+                  <td className="px-6 py-4">
+                    <div className="flex flex-col space-y-1">
+                      <span className="font-medium text-gray-900">{metadata.title}</span>
+                      <span className="text-xs text-blue-500">{shortUrl}</span>
+                      
+                      {/* Tags */}
+                      {metadata.tagNames.length > 0 && (
+                        <div className="flex flex-wrap gap-1.5 mt-1">
+                          {metadata.tagNames.map((tag, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-gray-100 px-2 py-0.5 text-xs font-medium text-gray-800">
+                              {tag}
+                            </span>
+                          ))}
+                        </div>
+                      )}
+                    </div>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    <a 
+                      href={metadata.originalUrl} 
+                      target="_blank" 
+                      rel="noopener noreferrer"
+                      className="flex items-center hover:text-blue-500"
+                    >
+                      <span className="max-w-xs truncate">{metadata.originalUrl}</span>
+                      <ExternalLink className="ml-1 h-3 w-3" />
+                    </a>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    <div className="flex flex-col space-y-1">
+                      {/* Teams */}
+                      {metadata.teamNames.length > 0 ? (
+                        <div className="flex flex-wrap gap-1.5">
+                          {metadata.teamNames.map((team, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-blue-100 px-2 py-0.5 text-xs font-medium text-blue-800">
+                              {team}
+                            </span>
+                          ))}
+                        </div>
+                      ) : (
+                        <span>-</span>
+                      )}
+                      
+                      {/* Projects */}
+                      {metadata.projectNames.length > 0 && (
+                        <div className="flex flex-wrap gap-1.5 mt-1">
+                          {metadata.projectNames.map((project, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-green-100 px-2 py-0.5 text-xs font-medium text-green-800">
+                              {project}
+                            </span>
+                          ))}
+                        </div>
+                      )}
+                    </div>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    {metadata.createdAt}
+                  </td>
+                </tr>
+              );
+            })}
+          </tbody>
+        </table>
+      </div>
+      
+      {/* Pagination */}
+      {totalPages > 0 && (
+        <div className="mt-6 flex items-center justify-between">
+          <div className="text-sm text-gray-500">
+            Showing {((currentPage - 1) * pageSize) + 1} to {Math.min(currentPage * pageSize, totalLinks)} of {totalLinks} results
+          </div>
+          <div className="flex items-center space-x-2">
+            <button
+              onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
+              disabled={currentPage === 1}
+              className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
+            >
+              Previous
+            </button>
+            {Array.from({ length: Math.min(totalPages, 5) }, (_, i) => {
+              // Create a window of 5 pages around current page
+              let pageNumber;
+              if (totalPages <= 5) {
+                pageNumber = i + 1;
+              } else {
+                const start = Math.max(1, currentPage - 2);
+                const end = Math.min(totalPages, start + 4);
+                pageNumber = start + i;
+                if (pageNumber > end) return null;
+              }
+              
+              return (
+                <button
+                  key={pageNumber}
+                  onClick={() => setCurrentPage(pageNumber)}
+                  className={`h-8 w-8 rounded-md text-sm ${
+                    currentPage === pageNumber 
+                      ? 'bg-blue-500 text-white' 
+                      : 'border border-gray-300 text-gray-700 hover:bg-gray-50'
+                  }`}
+                >
+                  {pageNumber}
+                </button>
+              );
+            })}
+            <button
+              onClick={() => setCurrentPage(prev => Math.min(prev + 1, totalPages))}
+              disabled={currentPage === totalPages}
+              className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
+            >
+              Next
+            </button>
+            
+            {/* Page input */}
+            <div className="ml-4 flex items-center space-x-1">
+              <span className="text-sm text-gray-500">Go to:</span>
+              <input
+                type="number"
+                min="1"
+                max={totalPages}
+                value={currentPage}
+                onChange={(e) => {
+                  // Allow input to be cleared for typing
+                  if (e.target.value === '') {
+                    e.target.value = '';
+                  }
+                }}
+                onBlur={(e) => {
+                  // Ensure a valid value on blur
+                  const value = parseInt(e.target.value, 10);
+                  if (isNaN(value) || value < 1) {
+                    setCurrentPage(1);
+                  } else if (value > totalPages) {
+                    setCurrentPage(totalPages);
+                  } else {
+                    setCurrentPage(value);
+                  }
+                }}
+                onKeyDown={(e) => {
+                  if (e.key === 'Enter') {
+                    const value = parseInt(e.currentTarget.value, 10);
+                    if (!isNaN(value) && value >= 1 && value <= totalPages) {
+                      setCurrentPage(value);
+                    } else if (!isNaN(value) && value < 1) {
+                      setCurrentPage(1);
+                    } else if (!isNaN(value) && value > totalPages) {
+                      setCurrentPage(totalPages);
+                    }
+                  }
+                }}
+                className="w-16 rounded-md border border-gray-300 px-2 py-1 text-sm text-center"
+              />
+              <span className="text-sm text-gray-500">of {totalPages}</span>
+            </div>
+            
+            <select
+              value={pageSize}
+              onChange={(e) => {
+                setPageSize(Number(e.target.value));
+                setCurrentPage(1); // Reset to page 1 when changing page size
+              }}
+              className="ml-4 rounded-md border border-gray-300 py-1.5 pl-3 pr-8 text-sm"
+            >
+              <option value="10">10 per page</option>
+              <option value="25">25 per page</option>
+              <option value="50">50 per page</option>
+              <option value="100">100 per page</option>
+            </select>
+          </div>
+        </div>
+      )}
+      
+      {links.length === 0 && (
+        <div className="mt-6 rounded-md bg-gray-50 p-6 text-center text-gray-500">
+          No links match your search criteria
+        </div>
+      )}
+    </div>
+  );
+} 

app/page.tsx

@@ -1,803 +1,5 @@
-"use client";
+import { redirect } from 'next/navigation';
 
-import { useState, useEffect } from 'react';
-import { format, subDays } from 'date-fns';
-import { DateRangePicker } from '@/app/components/ui/DateRangePicker';
-import TimeSeriesChart from '@/app/components/charts/TimeSeriesChart';
-import GeoAnalytics from '@/app/components/analytics/GeoAnalytics';
-import DevicePieCharts from '@/app/components/charts/DevicePieCharts';
-import { EventsSummary, TimeSeriesData, GeoData, DeviceAnalytics as DeviceAnalyticsType } from '@/app/api/types';
-import { TeamSelector } from '@/app/components/ui/TeamSelector';
-import { ProjectSelector } from '@/app/components/ui/ProjectSelector';
-import { TagSelector } from '@/app/components/ui/TagSelector';
-
-// 事件类型定义
-interface Event {
-  event_id?: string;
-  url_id: string;
-  url: string;
-  event_type: string;
-  visitor_id: string;
-  created_at: string;
-  event_time?: string;
-  referrer?: string;
-  browser?: string;
-  os?: string;
-  device_type?: string;
-  country?: string;
-  city?: string;
-  event_attributes?: string;
-  link_attributes?: string;
-  user_attributes?: string;
-  link_label?: string;
-  link_original_url?: string;
-  team_name?: string;
-  project_name?: string;
-  link_id?: string;
-  link_slug?: string;
-  link_tags?: string;
-  ip_address?: string;
-}
-
-// 格式化日期函数
-const formatDate = (dateString: string | undefined) => {
-  if (!dateString) return '';
-  try {
-    return format(new Date(dateString), 'yyyy-MM-dd HH:mm:ss');
-  } catch {
-    return dateString;
-  }
-};
-
-// 解析JSON字符串
-const parseJsonSafely = (jsonString: string) => {
-  if (!jsonString) return null;
-  try {
-    return JSON.parse(jsonString);
-  } catch {
-    return null;
-  }
-};
-
-// 获取用户可读名称
-const getUserDisplayName = (user: Record<string, unknown> | null) => {
-  if (!user) return '-';
-  if (typeof user.full_name === 'string') return user.full_name;
-  if (typeof user.name === 'string') return user.name;
-  if (typeof user.email === 'string') return user.email;
-  return '-';
-};
-
-// 提取链接和事件的重要信息
-const extractEventInfo = (event: Event) => {
-  // 解析事件属性
-  const eventAttrs = parseJsonSafely(event.event_attributes || '{}');
-  
-  // 解析链接属性
-  const linkAttrs = parseJsonSafely(event.link_attributes || '{}');
-  
-  // 解析用户属性
-  const userAttrs = parseJsonSafely(event.user_attributes || '{}');
-  
-  // 解析标签信息
-  let tags: string[] = [];
-  try {
-    if (event.link_tags) {
-      const parsedTags = JSON.parse(event.link_tags);
-      if (Array.isArray(parsedTags)) {
-        tags = parsedTags;
-      }
-    }
-  } catch {
-    // 解析失败则保持空数组
-  }
-  
-  return {
-    eventTime: event.created_at || event.event_time,
-    linkName: event.link_label || linkAttrs?.name || eventAttrs?.link_name || event.link_slug || '-',
-    originalUrl: event.link_original_url || eventAttrs?.origin_url || '-',
-    eventType: event.event_type || '-',
-    visitorId: event.visitor_id?.substring(0, 8) || '-',
-    referrer: eventAttrs?.referrer || '-',
-    ipAddress: event.ip_address || '-',
-    location: event.country ? (event.city ? `${event.city}, ${event.country}` : event.country) : '-',
-    device: event.device_type || '-',
-    browser: event.browser || '-',
-    os: event.os || '-',
-    userInfo: getUserDisplayName(userAttrs),
-    teamName: event.team_name || '-',
-    projectName: event.project_name || '-',
-    tags: tags
-  };
-};
-
-export default function HomePage() {
-  // 默认日期范围为最近7天
-  const today = new Date();
-  const [dateRange, setDateRange] = useState({
-    from: subDays(today, 7), // 7天前
-    to: today // 今天
-  });
-  
-  // 添加团队选择状态 - 使用数组支持多选
-  const [selectedTeamIds, setSelectedTeamIds] = useState<string[]>([]);
-  // 添加项目选择状态 - 使用数组支持多选
-  const [selectedProjectIds, setSelectedProjectIds] = useState<string[]>([]);
-  // 添加标签选择状态 - 使用数组支持多选
-  const [selectedTagIds, setSelectedTagIds] = useState<string[]>([]);
-  
-  // 添加分页状态
-  const [currentPage, setCurrentPage] = useState<number>(1);
-  const [pageSize, setPageSize] = useState<number>(10);
-  const [totalEvents, setTotalEvents] = useState<number>(0);
-  
-  const [loading, setLoading] = useState(true);
-  const [error, setError] = useState<string | null>(null);
-  const [summary, setSummary] = useState<EventsSummary | null>(null);
-  const [timeSeriesData, setTimeSeriesData] = useState<TimeSeriesData[]>([]);
-  const [geoData, setGeoData] = useState<GeoData[]>([]);
-  const [deviceData, setDeviceData] = useState<DeviceAnalyticsType | null>(null);
-  const [events, setEvents] = useState<Event[]>([]);
-
-  useEffect(() => {
-    const fetchData = async () => {
-      setLoading(true);
-      setError(null);
-
-      try {
-        const startTime = format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'");
-        const endTime = format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'");
-
-        // 构建基础URL和查询参数
-        const baseUrl = '/api/events';
-        const params = new URLSearchParams({
-          startTime,
-          endTime,
-          page: currentPage.toString(),
-          pageSize: pageSize.toString()
-        });
-        
-        // 添加团队ID参数 - 支持多个团队
-        if (selectedTeamIds.length > 0) {
-          selectedTeamIds.forEach(teamId => {
-            params.append('teamId', teamId);
-          });
-        }
-        
-        // 添加项目ID参数 - 支持多个项目
-        if (selectedProjectIds.length > 0) {
-          selectedProjectIds.forEach(projectId => {
-            params.append('projectId', projectId);
-          });
-        }
-        
-        // 添加标签ID参数 - 支持多个标签
-        if (selectedTagIds.length > 0) {
-          selectedTagIds.forEach(tagId => {
-            params.append('tagId', tagId);
-          });
-        }
-
-        // 并行获取所有数据
-        const [summaryRes, timeSeriesRes, geoRes, deviceRes, eventsRes] = await Promise.all([
-          fetch(`${baseUrl}/summary?${params.toString()}`),
-          fetch(`${baseUrl}/time-series?${params.toString()}`),
-          fetch(`${baseUrl}/geo?${params.toString()}`),
-          fetch(`${baseUrl}/devices?${params.toString()}`),
-          fetch(`${baseUrl}?${params.toString()}`)
-        ]);
-
-        const [summaryData, timeSeriesData, geoData, deviceData, eventsData] = await Promise.all([
-          summaryRes.json(),
-          timeSeriesRes.json(),
-          geoRes.json(),
-          deviceRes.json(),
-          eventsRes.json()
-        ]);
-
-        if (!summaryRes.ok) throw new Error(summaryData.error || 'Failed to fetch summary data');
-        if (!timeSeriesRes.ok) throw new Error(timeSeriesData.error || 'Failed to fetch time series data');
-        if (!geoRes.ok) throw new Error(geoData.error || 'Failed to fetch geo data');
-        if (!deviceRes.ok) throw new Error(deviceData.error || 'Failed to fetch device data');
-        if (!eventsRes.ok) throw new Error(eventsData.error || 'Failed to fetch events data');
-
-        setSummary(summaryData.data);
-        setTimeSeriesData(timeSeriesData.data);
-        setGeoData(geoData.data);
-        setDeviceData(deviceData.data);
-        setEvents(eventsData.data || []);
-        
-        // 设置总事件数量用于分页
-        if (eventsData.meta) {
-          // 确保将total转换为数字，无论它是字符串还是数字
-          const totalCount = parseInt(String(eventsData.meta.total), 10);
-          if (!isNaN(totalCount)) {
-            setTotalEvents(totalCount);
-          }
-        }
-      } catch (err) {
-        setError(err instanceof Error ? err.message : 'An error occurred while fetching data');
-      } finally {
-        setLoading(false);
-      }
-    };
-
-    fetchData();
-  }, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagIds, currentPage, pageSize]);
-
-  if (loading) {
-    return (
-      <div className="flex items-center justify-center min-h-screen">
-        <div className="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-blue-500" />
-      </div>
-    );
-  }
-
-  if (error) {
-    return (
-      <div className="flex items-center justify-center min-h-screen">
-        <div className="text-red-500">{error}</div>
-      </div>
-    );
-  }
-
-  return (
-    <div className="container mx-auto px-4 py-8">
-      <div className="flex justify-between items-center mb-8">
-        <h1 className="text-2xl font-bold text-gray-900">Analytics Dashboard</h1>
-        <div className="flex flex-col gap-4 md:flex-row md:items-center">
-          <TeamSelector
-            value={selectedTeamIds}
-            onChange={(value) => {
-              const newTeamIds = Array.isArray(value) ? value : [value];
-              
-              // Check if team selection has changed
-              if (JSON.stringify(newTeamIds) !== JSON.stringify(selectedTeamIds)) {
-                // Clear project selection when team changes
-                setSelectedProjectIds([]);
-                
-                // Update team selection
-                setSelectedTeamIds(newTeamIds);
-              }
-            }}
-            className="w-[250px]"
-            multiple={true}
-          />
-          <ProjectSelector
-            value={selectedProjectIds}
-            onChange={(value) => setSelectedProjectIds(Array.isArray(value) ? value : [value])}
-            className="w-[250px]"
-            multiple={true}
-            teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
-          />
-          <TagSelector
-            value={selectedTagIds}
-            onChange={(value) => setSelectedTagIds(Array.isArray(value) ? value : [value])}
-            className="w-[250px]"
-            multiple={true}
-            teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
-          />
-          <DateRangePicker
-            value={dateRange}
-            onChange={setDateRange}
-          />
-        </div>
-      </div>
-
-      {/* 显示团队选择信息 */}
-      {selectedTeamIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedTeamIds.length === 1 ? 'Team filter:' : 'Teams filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedTeamIds.map(teamId => (
-              <span key={teamId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {teamId}
-                <button
-                  onClick={() => setSelectedTeamIds(selectedTeamIds.filter(id => id !== teamId))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedTeamIds.length > 0 && (
-              <button
-                onClick={() => setSelectedTeamIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 显示项目选择信息 */}
-      {selectedProjectIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedProjectIds.length === 1 ? 'Project filter:' : 'Projects filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedProjectIds.map(projectId => (
-              <span key={projectId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {projectId}
-                <button
-                  onClick={() => setSelectedProjectIds(selectedProjectIds.filter(id => id !== projectId))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedProjectIds.length > 0 && (
-              <button
-                onClick={() => setSelectedProjectIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 显示标签选择信息 */}
-      {selectedTagIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedTagIds.length === 1 ? 'Tag filter:' : 'Tags filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedTagIds.map(tagName => (
-              <span key={tagName} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {tagName}
-                <button
-                  onClick={() => setSelectedTagIds(selectedTagIds.filter(name => name !== tagName))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedTagIds.length > 0 && (
-              <button
-                onClick={() => setSelectedTagIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 仪表板内容 - 现在放在事件列表之后 */}
-      <>
-        {summary && (
-          <div className="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Total Events</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.totalEvents === 'number' ? summary.totalEvents.toLocaleString() : summary.totalEvents}
-              </p>
-                  </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Unique Visitors</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.uniqueVisitors === 'number' ? summary.uniqueVisitors.toLocaleString() : summary.uniqueVisitors}
-              </p>
-                </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Total Conversions</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.totalConversions === 'number' ? summary.totalConversions.toLocaleString() : summary.totalConversions}
-              </p>
-              </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Avg. Time Spent</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {summary.averageTimeSpent?.toFixed(1) || '0'}s
-              </p>
-            </div>
-          </div>
-        )}
-
-<div className="bg-white rounded-lg shadow overflow-hidden mb-8">
-        <div className="p-6 border-b border-gray-200">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Recent Events</h2>
-        </div>
-
-        <div className="overflow-x-auto">
-          <table className="min-w-full divide-y divide-gray-200">
-            <thead className="bg-gray-50">
-              <tr>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Time
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Link Name
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Original URL
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Event Type
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Tags
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  User
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Team/Project
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  IP/Location
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Device Info
-                </th>
-              </tr>
-            </thead>
-            <tbody className="bg-white divide-y divide-gray-200">
-              {events.map((event, index) => {
-                const info = extractEventInfo(event);
-                return (
-                  <tr key={event.event_id || index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      {formatDate(info.eventTime)}
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
-                      <span className="font-medium">{info.linkName}</span>
-                      <div className="text-xs text-gray-500 mt-1 truncate max-w-xs">
-                        ID: {event.link_id?.substring(0, 8) || '-'}
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
-                      <a href={info.originalUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
-                        {info.originalUrl}
-                      </a>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm">
-                      <span className={`px-2 inline-flex text-xs leading-5 font-semibold rounded-full ${
-                        info.eventType === 'click' 
-                          ? 'bg-green-100 text-green-800' 
-                          : 'bg-blue-100 text-blue-800'
-                      }`}>
-                        {info.eventType}
-                      </span>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-wrap gap-1">
-                        {info.tags && info.tags.length > 0 ? (
-                          info.tags.map((tag, idx) => (
-                            <span 
-                              key={idx} 
-                              className="bg-gray-100 text-gray-800 text-xs px-2 py-0.5 rounded"
-                            >
-                              {tag}
-                            </span>
-                          ))
-                        ) : (
-                          <span className="text-gray-400">-</span>
-                        )}
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="font-medium">{info.userInfo}</div>
-                      <div className="text-xs text-gray-400 mt-1">{info.visitorId}...</div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="font-medium">{info.teamName}</div>
-                      <div className="text-xs text-gray-400 mt-1">{info.projectName}</div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-col">
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">IP:</span> 
-                          <span className="ml-1">{info.ipAddress}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center">
-                          <span className="font-medium">Location:</span> 
-                          <span className="ml-1">{info.location}</span>
-                        </span>
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-col">
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">Device:</span> 
-                          <span className="ml-1">{info.device}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">Browser:</span> 
-                          <span className="ml-1">{info.browser}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center">
-                          <span className="font-medium">OS:</span> 
-                          <span className="ml-1">{info.os}</span>
-                        </span>
-                      </div>
-                    </td>
-                  </tr>
-                );
-              })}
-            </tbody>
-          </table>
-        </div>
-
-        {/* 表格为空状态 */}
-        {!loading && events.length === 0 && (
-          <div className="flex justify-center items-center p-8 text-gray-500">
-            No events found
-          </div>
-        )}
-        
-        {/* 分页控件 - 删除totalEvents > 0条件，改为events.length > 0 */}
-        {!loading && events.length > 0 && (
-          <div className="px-6 py-4 flex items-center justify-between border-t border-gray-200">
-            <div className="flex-1 flex justify-between sm:hidden">
-              <button
-                onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
-                disabled={currentPage === 1}
-                className={`relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
-                  currentPage === 1 
-                    ? 'text-gray-300 bg-gray-50' 
-                    : 'text-gray-700 bg-white hover:bg-gray-50'
-                }`}
-              >
-                Previous
-              </button>
-              <button
-                onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
-                disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                className={`ml-3 relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
-                  currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
-                    ? 'text-gray-300 cursor-not-allowed' 
-                    : 'text-gray-700 bg-white hover:bg-gray-50'
-                }`}
-              >
-                Next
-              </button>
-            </div>
-            <div className="hidden sm:flex-1 sm:flex sm:items-center sm:justify-between">
-              <div>
-                <p className="text-sm text-gray-700">
-                  Showing <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + 1 : 0}</span> to <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + events.length : 0}</span> of{' '}
-                  <span className="font-medium">{totalEvents}</span> results
-                </p>
-              </div>
-              <div className="flex items-center">
-                <div className="mr-4">
-                  <select
-                    className="px-3 py-1 border border-gray-300 rounded-md text-sm"
-                    value={pageSize}
-                    onChange={(e) => {
-                      setPageSize(Number(e.target.value));
-                      setCurrentPage(1); // 重置到第一页
-                    }}
-                  >
-                    <option value="5">5 / page</option>
-                    <option value="10">10 / page</option>
-                    <option value="20">20 / page</option>
-                    <option value="50">50 / page</option>
-                  </select>
-                </div>
-                
-                {/* 添加直接跳转到指定页的输入框 */}
-                <div className="mr-4 flex items-center">
-                  <span className="text-sm text-gray-700 mr-2">Go to:</span>
-                  <input
-                    type="number"
-                    min="1"
-                    max={Math.max(1, Math.ceil(totalEvents / pageSize))}
-                    value={currentPage}
-                    onChange={(e) => {
-                      const page = parseInt(e.target.value);
-                      if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
-                        setCurrentPage(page);
-                      }
-                    }}
-                    onKeyDown={(e) => {
-                      if (e.key === 'Enter') {
-                        const input = e.target as HTMLInputElement;
-                        const page = parseInt(input.value);
-                        if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
-                          setCurrentPage(page);
-                        }
-                      }
-                    }}
-                    className="w-16 px-3 py-1 border border-gray-300 rounded-md text-sm"
-                  />
-                  <span className="text-sm text-gray-700 ml-2">
-                    of {Math.max(1, Math.ceil(totalEvents / pageSize))}
-                  </span>
-                </div>
-                
-                <nav className="relative z-0 inline-flex rounded-md shadow-sm -space-x-px" aria-label="Pagination">
-                  {/* 首页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(1)}
-                    disabled={currentPage === 1}
-                    className={`relative inline-flex items-center px-2 py-2 rounded-l-md border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage === 1 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">First</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
-                      <path fillRule="evenodd" d="M15.707 15.707a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 111.414 1.414L11.414 10l4.293 4.293a1 1 0 010 1.414zm-6 0a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 011.414 1.414L5.414 10l4.293 4.293a1 1 0 010 1.414z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 上一页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
-                    disabled={currentPage === 1}
-                    className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage === 1 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Previous</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
-                      <path fillRule="evenodd" d="M12.707 5.293a1 1 0 010 1.414L9.414 10l3.293 3.293a1 1 0 01-1.414 1.414l-4-4a1 1 0 010-1.414l4-4a1 1 0 011.414 0z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 页码按钮 */}
-                  {(() => {
-                    const totalPages = Math.max(1, Math.ceil(totalEvents / pageSize));
-                    const pageNumbers = [];
-                    
-                    // 如果总页数小于等于7，显示所有页码
-                    if (totalPages <= 7) {
-                      for (let i = 1; i <= totalPages; i++) {
-                        pageNumbers.push(i);
-                      }
-                    } else {
-                      // 总是显示首页
-                      pageNumbers.push(1);
-                      
-                      // 根据当前页显示中间页码
-                      if (currentPage <= 3) {
-                        // 当前页靠近开始
-                        pageNumbers.push(2, 3, 4);
-                        pageNumbers.push('ellipsis1');
-                      } else if (currentPage >= totalPages - 2) {
-                        // 当前页靠近结束
-                        pageNumbers.push('ellipsis1');
-                        pageNumbers.push(totalPages - 3, totalPages - 2, totalPages - 1);
-                      } else {
-                        // 当前页在中间
-                        pageNumbers.push('ellipsis1');
-                        pageNumbers.push(currentPage - 1, currentPage, currentPage + 1);
-                        pageNumbers.push('ellipsis2');
-                      }
-                      
-                      // 总是显示尾页
-                      pageNumbers.push(totalPages);
-                    }
-                    
-                    return pageNumbers.map((pageNum, idx) => {
-                      if (pageNum === 'ellipsis1' || pageNum === 'ellipsis2') {
-                        return (
-                          <div key={`ellipsis-${idx}`} className="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700">
-                            ...
-                          </div>
-                        );
-                      }
-                      
-                      return (
-                        <button
-                          key={pageNum}
-                          onClick={() => setCurrentPage(Number(pageNum))}
-                          className={`relative inline-flex items-center px-4 py-2 border text-sm font-medium ${
-                            currentPage === pageNum
-                              ? 'z-10 bg-blue-50 border-blue-500 text-blue-600'
-                              : 'bg-white border-gray-300 text-gray-500 hover:bg-gray-50'
-                          }`}
-                        >
-                          {pageNum}
-                        </button>
-                      );
-                    });
-                  })()}
-                  
-                  {/* 下一页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
-                    disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                    className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Next</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
-                      <path fillRule="evenodd" d="M7.293 14.707a1 1 0 010-1.414L10.586 10 7.293 6.707a1 1 0 011.414-1.414l4 4a1 1 0 010 1.414l-4 4a1 1 0 01-1.414 0z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 尾页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(Math.ceil(totalEvents / pageSize))}
-                    disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                    className={`relative inline-flex items-center px-2 py-2 rounded-r-md border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Last</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
-                      <path fillRule="evenodd" d="M4.293 15.707a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L8.586 10 4.293 14.293a1 1 0 000 1.414zm6 0a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L15.586 10l-4.293 4.293a1 1 0 000 1.414z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                </nav>
-              </div>
-            </div>
-          </div>
-        )}
-      </div>
-
-        <div className="bg-white rounded-lg shadow p-6 mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Event Trends</h2>
-          <div className="h-96">
-            <TimeSeriesChart data={timeSeriesData} />
-          </div>
-        </div>
-
-        <div className="mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Device Analytics</h2>
-          {deviceData && <DevicePieCharts data={deviceData} />}
-        </div>
-
-        <div className="bg-white rounded-lg shadow p-6 mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Geographic Distribution</h2>
-          <GeoAnalytics 
-            data={geoData} 
-            onViewModeChange={(mode) => {
-              // 构建查询参数
-              const params = new URLSearchParams({
-                startTime: format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                endTime: format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                groupBy: mode
-              });
-              
-              // 添加其他筛选参数
-              if (selectedTeamIds.length > 0) {
-                selectedTeamIds.forEach(id => params.append('teamId', id));
-              }
-              
-              if (selectedProjectIds.length > 0) {
-                selectedProjectIds.forEach(id => params.append('projectId', id));
-              }
-              
-              if (selectedTagIds.length > 0) {
-                selectedTagIds.forEach(id => params.append('tagId', id));
-              }
-              
-              // 刷新地理位置数据
-              fetch(`/api/events/geo?${params}`)
-                .then(res => res.json())
-                .then(data => {
-                  if (data.success) {
-                    setGeoData(data.data);
-                  }
-                })
-                .catch(error => console.error('Failed to fetch geo data:', error));
-            }}
-          />
-        </div>
-      </>
-    </div>
-  );
+export default function Home() {
+  redirect('/analytics');
 }

app/utils/store.ts

@@ -0,0 +1,52 @@
+import { create } from 'zustand';
+import { persist } from 'zustand/middleware';
+
+// Define interface for team, project and tag objects
+interface TeamData {
+  team_id: string;
+  team_name: string;
+  [key: string]: unknown;
+}
+
+interface ProjectData {
+  project_id: string;
+  project_name: string;
+  [key: string]: unknown;
+}
+
+// 定义 ShortUrl 数据类型
+export interface ShortUrlData {
+  id: string;
+  externalId: string;
+  slug: string;
+  originalUrl: string;
+  title?: string;
+  shortUrl: string;
+  teams?: TeamData[];
+  projects?: ProjectData[];
+  tags?: string[];
+  createdAt?: string;
+  domain?: string;
+}
+
+// 定义 store 类型
+interface ShortUrlStore {
+  selectedShortUrl: ShortUrlData | null;
+  setSelectedShortUrl: (shortUrl: ShortUrlData | null) => void;
+  clearSelectedShortUrl: () => void;
+}
+
+// 创建 store 并使用 persist 中间件保存到 localStorage
+export const useShortUrlStore = create<ShortUrlStore>()(
+  persist(
+    (set) => ({
+      selectedShortUrl: null,
+      setSelectedShortUrl: (shortUrl) => set({ selectedShortUrl: shortUrl }),
+      clearSelectedShortUrl: () => set({ selectedShortUrl: null }),
+    }),
+    {
+      name: 'shorturl-storage', // localStorage 中的 key 名称
+      partialize: (state) => ({ selectedShortUrl: state.selectedShortUrl }), // 只持久化 selectedShortUrl
+    }
+  )
+);

lib/analytics.ts

@@ -22,6 +22,7 @@ export interface EventsQueryParams {
   teamIds?: string[];
   projectIds?: string[];
   tagIds?: string[];
+  subpath?: string;
   page?: number;
   pageSize?: number;
   sortBy?: string;
@@ -66,8 +67,11 @@ export async function getEventsSummary(params: {
   teamIds?: string[];
   projectIds?: string[];
   tagIds?: string[];
+  subpath?: string;
 }): Promise<EventsSummary> {
+  console.log('getEventsSummary received params:', params);
   const filter = buildFilter(params);
+  console.log('getEventsSummary built filter:', filter);
   
   // 获取基本统计数据
   const baseQuery = `
@@ -184,6 +188,7 @@ export async function getTimeSeriesData(params: {
   teamIds?: string[];
   projectIds?: string[];
   tagIds?: string[];
+  subpath?: string;
 }): Promise<TimeSeriesData[]> {
   const filter = buildFilter(params);
   
@@ -219,6 +224,7 @@ export async function getGeoAnalytics(params: {
   teamIds?: string[];
   projectIds?: string[];
   tagIds?: string[];
+  subpath?: string;
 }): Promise<GeoData[]> {
   const filter = buildFilter(params);
   
@@ -255,6 +261,7 @@ export async function getDeviceAnalytics(params: {
   teamIds?: string[];
   projectIds?: string[];
   tagIds?: string[];
+  subpath?: string;
 }): Promise<DeviceAnalytics> {
   const filter = buildFilter(params);
   

lib/clickhouse.ts

@@ -1,5 +1,5 @@
 import { createClient } from '@clickhouse/client';
-import type { EventsQueryParams } from './types';
+import { EventsQueryParams } from './analytics';
 
 // ClickHouse 客户端配置
 const clickhouse = createClient({
@@ -26,6 +26,7 @@ function buildDateFilter(startTime?: string, endTime?: string): string {
 
 // 构建通用过滤条件
 export function buildFilter(params: Partial<EventsQueryParams>): string {
+  console.log('buildFilter received params:', JSON.stringify(params));
   const filters = [];
   
   // 添加日期过滤条件
@@ -43,6 +44,7 @@ export function buildFilter(params: Partial<EventsQueryParams>): string {
   
   // 添加链接ID过滤条件
   if (params.linkId) {
+    console.log('Adding link_id filter:', params.linkId);
     filters.push(`link_id = '${params.linkId}'`);
   }
   
@@ -56,6 +58,34 @@ export function buildFilter(params: Partial<EventsQueryParams>): string {
     filters.push(`user_id = '${params.userId}'`);
   }
   
+  // 添加子路径过滤条件 - 使用更精确的匹配方式
+  if (params.subpath && params.subpath.trim() !== '') {
+    console.log('====== SUBPATH DEBUG ======');
+    console.log('Raw subpath param:', params.subpath);
+    
+    // 清理并准备subpath值
+    let cleanSubpath = params.subpath.trim();
+    // 移除开头的斜杠以便匹配
+    if (cleanSubpath.startsWith('/')) {
+      cleanSubpath = cleanSubpath.substring(1);
+    }
+    // 移除结尾的斜杠以便匹配
+    if (cleanSubpath.endsWith('/')) {
+      cleanSubpath = cleanSubpath.substring(0, cleanSubpath.length - 1);
+    }
+    
+    console.log('Cleaned subpath:', cleanSubpath);
+    
+    // 使用正则表达式匹配URL中的第二个路径部分
+    // 示例: 在 "https://abc.com/slug/subpath/" 中匹配 "subpath"
+    const condition = `match(JSONExtractString(event_attributes, 'full_url'), '/[^/]+/${cleanSubpath}(/|\\\\?|$)')`;
+    
+    console.log('Final SQL condition:', condition);
+    console.log('==========================');
+    
+    filters.push(condition);
+  }
+  
   // 添加团队ID过滤条件
   if (params.teamId) {
     filters.push(`team_id = '${params.teamId}'`);
@@ -100,7 +130,7 @@ export function buildOrderBy(sortBy: string = 'event_time', sortOrder: string =
 
 // 执行查询
 export async function executeQuery(query: string) {
-  console.log('执行查询:', query); // 查询日志
+  console.log('Executing query:', query); // 查询日志
   try {
     const resultSet = await clickhouse.query({
       query,
@@ -117,7 +147,7 @@ export async function executeQuery(query: string) {
 
 // 执行返回单一结果的查询
 export async function executeQuerySingle(query: string) {
-  console.log('执行单一结果查询:', query); // 查询日志
+  console.log('Executing single result query:', query); // 查询日志
   try {
     const resultSet = await clickhouse.query({
       query,

package.json

@@ -37,11 +37,13 @@
     "date-fns": "^4.1.0",
     "lucide-react": "^0.486.0",
     "next": "15.2.3",
+    "process": "^0.11.10",
     "react": "^19.0.0",
     "react-dom": "^19.0.0",
     "recharts": "^2.15.1",
     "tailwind-merge": "^3.1.0",
-    "uuid": "^10.0.0"
+    "uuid": "^10.0.0",
+    "zustand": "^5.0.3"
   },
   "devDependencies": {
     "@eslint/eslintrc": "^3",

pnpm-lock.yaml

@@ -50,6 +50,9 @@ importers:
       next:
         specifier: 15.2.3
         version: 15.2.3(react-dom@19.0.0(react@19.0.0))(react@19.0.0)
+      process:
+        specifier: ^0.11.10
+        version: 0.11.10
       react:
         specifier: ^19.0.0
         version: 19.0.0
@@ -65,6 +68,9 @@ importers:
       uuid:
         specifier: ^10.0.0
         version: 10.0.0
+      zustand:
+        specifier: ^5.0.3
+        version: 5.0.3(@types/react@19.0.12)(react@19.0.0)
     devDependencies:
       '@eslint/eslintrc':
         specifier: ^3
@@ -2548,6 +2554,10 @@ packages:
     resolution: {integrity: sha512-vkcDPrRZo1QZLbn5RLGPpg/WmIQ65qoWWhcGKf/b5eplkkarX0m9z8ppCat4mlOqUsWpyNuYgO3VRyrYHSzX5g==}
     engines: {node: '>= 0.8.0'}
 
+  process@0.11.10:
+    resolution: {integrity: sha512-cdGef/drWFoydD1JsMzuFf8100nZl+GT+yacc2bEced5f9Rjk4z+WtFUTBu9PhOi9j/jfmBPu0mMEY4wIdAF8A==}
+    engines: {node: '>= 0.6.0'}
+
   prop-types@15.8.1:
     resolution: {integrity: sha512-oj87CgZICdulUohogVAR7AjlC0327U4el4L6eAvOqCeudMDVU0NThNaV+b9Df4dXgSP1gXMTnPdhfe/2qDH5cg==}
 
@@ -3035,6 +3045,24 @@ packages:
     resolution: {integrity: sha512-rVksvsnNCdJ/ohGc6xgPwyN8eheCxsiLM8mxuE/t/mOVqJewPuO1miLpTHQiRgTKCLexL4MeAFVagts7HmNZ2Q==}
     engines: {node: '>=10'}
 
+  zustand@5.0.3:
+    resolution: {integrity: sha512-14fwWQtU3pH4dE0dOpdMiWjddcH+QzKIgk1cl8epwSE7yag43k/AD/m4L6+K7DytAOr9gGBe3/EXj9g7cdostg==}
+    engines: {node: '>=12.20.0'}
+    peerDependencies:
+      '@types/react': '>=18.0.0'
+      immer: '>=9.0.6'
+      react: '>=18.0.0'
+      use-sync-external-store: '>=1.2.0'
+    peerDependenciesMeta:
+      '@types/react':
+        optional: true
+      immer:
+        optional: true
+      react:
+        optional: true
+      use-sync-external-store:
+        optional: true
+
 snapshots:
 
   '@alloc/quick-lru@5.2.0': {}
@@ -5650,6 +5678,8 @@ snapshots:
 
   prelude-ls@1.2.1: {}
 
+  process@0.11.10: {}
+
   prop-types@15.8.1:
     dependencies:
       loose-envify: 1.4.0
@@ -6304,3 +6334,8 @@ snapshots:
   ws@8.18.1: {}
 
   yocto-queue@0.1.0: {}
+
+  zustand@5.0.3(@types/react@19.0.12)(react@19.0.0):
+    optionalDependencies:
+      '@types/react': 19.0.12
+      react: 19.0.0

scripts/db/db-reports/clickhouse-schema-2025-03-20T13-57-59-013Z.log

@@ -1,225 +0,0 @@
-
-获取所有表...
-数据库 limq 中找到以下表:
-  - .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb
-  - .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc
-  - .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1
-  - .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0
-  - .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024
-  - .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea
-  - link_daily_stats
-  - link_events
-  - link_hourly_patterns
-  - links
-  - platform_distribution
-  - project_daily_stats
-  - projects
-  - qr_scans
-  - qrcode_daily_stats
-  - qrcodes
-  - sessions
-  - team_daily_stats
-  - team_members
-  - teams
-
-所有ClickHouse表:
-.inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb, .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc, .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1, .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0, .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024, .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea, link_daily_stats, link_events, link_hourly_patterns, links, platform_distribution, project_daily_stats, projects, qr_scans, qrcode_daily_stats, qrcodes, sessions, team_daily_stats, team_members, teams
-
-获取表 .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb 的结构...
-
-获取表 .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc 的结构...
-
-获取表 .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1 的结构...
-
-获取表 .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0 的结构...
-
-获取表 .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024 的结构...
-
-获取表 .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea 的结构...
-
-获取表 link_daily_stats 的结构...
-表 link_daily_stats 的列:
-  - date (Date, 无默认值)
-  - link_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - unique_sessions (UInt64, 无默认值)
-  - total_time_spent (UInt64, 无默认值)
-  - avg_time_spent (Float64, 无默认值)
-  - bounce_count (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - unique_referrers (UInt64, 无默认值)
-  - mobile_count (UInt64, 无默认值)
-  - tablet_count (UInt64, 无默认值)
-  - desktop_count (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-  - total_conversion_value (Float64, 无默认值)
-
-获取表 link_events 的结构...
-表 link_events 的列:
-  - event_id (UUID, 默认值: generateUUIDv4())
-  - event_time (DateTime64(3), 默认值: now64())
-  - date (Date, 默认值: toDate(event_time))
-  - link_id (String, 无默认值)
-  - channel_id (String, 无默认值)
-  - visitor_id (String, 无默认值)
-  - session_id (String, 无默认值)
-  - event_type (Enum8('click' = 1, 'redirect' = 2, 'conversion' = 3, 'error' = 4), 无默认值)
-  - ip_address (String, 无默认值)
-  - country (String, 无默认值)
-  - city (String, 无默认值)
-  - referrer (String, 无默认值)
-  - utm_source (String, 无默认值)
-  - utm_medium (String, 无默认值)
-  - utm_campaign (String, 无默认值)
-  - user_agent (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - browser (String, 无默认值)
-  - os (String, 无默认值)
-  - time_spent_sec (UInt32, 默认值: 0)
-  - is_bounce (Bool, 默认值: true)
-  - is_qr_scan (Bool, 默认值: false)
-  - qr_code_id (String, 默认值: '')
-  - conversion_type (Enum8('visit' = 1, 'stay' = 2, 'interact' = 3, 'signup' = 4, 'subscription' = 5, 'purchase' = 6), 默认值: 'visit')
-  - conversion_value (Float64, 默认值: 0)
-  - custom_data (String, 默认值: '{}')
-
-获取表 link_hourly_patterns 的结构...
-表 link_hourly_patterns 的列:
-  - date (Date, 无默认值)
-  - hour (UInt8, 无默认值)
-  - link_id (String, 无默认值)
-  - visits (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-
-获取表 links 的结构...
-表 links 的列:
-  - link_id (String, 无默认值)
-  - original_url (String, 无默认值)
-  - created_at (DateTime64(3), 无默认值)
-  - created_by (String, 无默认值)
-  - title (String, 无默认值)
-  - description (String, 无默认值)
-  - tags (Array(String), 无默认值)
-  - is_active (Bool, 默认值: true)
-  - expires_at (Nullable(DateTime), 无默认值)
-  - team_id (String, 默认值: '')
-  - project_id (String, 默认值: '')
-
-获取表 platform_distribution 的结构...
-表 platform_distribution 的列:
-  - date (Date, 无默认值)
-  - utm_source (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - visits (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-
-获取表 project_daily_stats 的结构...
-表 project_daily_stats 的列:
-  - date (Date, 无默认值)
-  - project_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - links_used (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-
-获取表 projects 的结构...
-表 projects 的列:
-  - project_id (String, 无默认值)
-  - team_id (String, 无默认值)
-  - name (String, 无默认值)
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - description (String, 默认值: '')
-  - is_archived (Bool, 默认值: false)
-  - links_count (UInt32, 默认值: 0)
-  - total_clicks (UInt64, 默认值: 0)
-  - last_updated (DateTime, 默认值: now())
-
-获取表 qr_scans 的结构...
-表 qr_scans 的列:
-  - scan_id (UUID, 默认值: generateUUIDv4())
-  - qr_code_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - scan_time (DateTime64(3), 无默认值)
-  - visitor_id (String, 无默认值)
-  - location (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - led_to_conversion (Bool, 默认值: false)
-
-获取表 qrcode_daily_stats 的结构...
-表 qrcode_daily_stats 的列:
-  - date (Date, 无默认值)
-  - qr_code_id (String, 无默认值)
-  - total_scans (UInt64, 无默认值)
-  - unique_scanners (UInt64, 无默认值)
-  - conversions (UInt64, 无默认值)
-  - mobile_scans (UInt64, 无默认值)
-  - tablet_scans (UInt64, 无默认值)
-  - desktop_scans (UInt64, 无默认值)
-  - unique_locations (UInt64, 无默认值)
-
-获取表 qrcodes 的结构...
-表 qrcodes 的列:
-  - qr_code_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - team_id (String, 无默认值)
-  - project_id (String, 默认值: '')
-  - name (String, 无默认值)
-  - description (String, 默认值: '')
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - updated_at (DateTime, 默认值: now())
-  - qr_type (Enum8('standard' = 1, 'custom' = 2, 'dynamic' = 3), 默认值: 'standard')
-  - image_url (String, 默认值: '')
-  - design_config (String, 默认值: '{}')
-  - is_active (Bool, 默认值: true)
-  - total_scans (UInt64, 默认值: 0)
-  - unique_scanners (UInt32, 默认值: 0)
-
-获取表 sessions 的结构...
-表 sessions 的列:
-  - session_id (String, 无默认值)
-  - visitor_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - started_at (DateTime64(3), 无默认值)
-  - last_activity (DateTime64(3), 无默认值)
-  - ended_at (Nullable(DateTime64(3)), 无默认值)
-  - duration_sec (UInt32, 默认值: 0)
-  - session_pages (UInt8, 默认值: 1)
-  - is_completed (Bool, 默认值: false)
-
-获取表 team_daily_stats 的结构...
-表 team_daily_stats 的列:
-  - date (Date, 无默认值)
-  - team_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - links_used (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-
-获取表 team_members 的结构...
-表 team_members 的列:
-  - team_id (String, 无默认值)
-  - user_id (String, 无默认值)
-  - role (Enum8('owner' = 1, 'admin' = 2, 'editor' = 3, 'viewer' = 4), 无默认值)
-  - joined_at (DateTime, 默认值: now())
-  - invited_by (String, 无默认值)
-  - is_active (Bool, 默认值: true)
-  - last_active (DateTime, 默认值: now())
-
-获取表 teams 的结构...
-表 teams 的列:
-  - team_id (String, 无默认值)
-  - name (String, 无默认值)
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - description (String, 默认值: '')
-  - avatar_url (String, 默认值: '')
-  - is_active (Bool, 默认值: true)
-  - plan_type (Enum8('free' = 1, 'pro' = 2, 'enterprise' = 3), 无默认值)
-  - members_count (UInt32, 默认值: 1)
-
-ClickHouse数据库结构检查完成

scripts/db/sql/clickhouse/add_domain_column.sql

@@ -0,0 +1,5 @@
+-- 添加domain列到shorturl_analytics.shorturl表
+ALTER TABLE
+  shorturl_analytics.shorturl
+ADD
+  COLUMN IF NOT EXISTS domain Nullable(String) COMMENT '域名';

scripts/db/sql/clickhouse/add_req_full_path.sql

@@ -0,0 +1,9 @@
+-- add_req_full_path.sql
+-- Add req_full_path column to the shorturl_analytics.events table
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN IF NOT EXISTS req_full_path String COMMENT 'Full request path including query parameters';
+
+-- Display the updated table structure
+DESCRIBE TABLE shorturl_analytics.events;

scripts/db/sql/clickhouse/add_utm_fields.sql

@@ -0,0 +1,41 @@
+-- 添加缺失的UTM参数字段到shorturl_analytics.events表
+-- 创建日期: 2024-07-02
+-- 用途: 增强UTM参数追踪能力
+-- 添加utm_term字段 (用于跟踪付费搜索关键词)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_term String DEFAULT '' AFTER utm_campaign;
+
+-- 添加utm_content字段 (用于区分相同广告的不同版本或A/B测试)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_content String DEFAULT '' AFTER utm_term;
+
+-- 验证字段添加成功
+DESCRIBE TABLE shorturl_analytics.events;
+
+-- 示例查询: 查看UTM参数分析数据
+SELECT
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content,
+  COUNT(*) as clicks
+FROM
+  shorturl_analytics.events
+WHERE
+  event_type = 'click'
+  AND utm_source != ''
+GROUP BY
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content
+ORDER BY
+  clicks DESC
+LIMIT
+  10;

scripts/db/sql/clickhouse/create_shorturl_table.sql

@@ -0,0 +1,46 @@
+-- 使用shorturl_analytics数据库
+USE shorturl_analytics;
+
+-- 删除已存在的shorturl表
+DROP TABLE IF EXISTS shorturl_analytics.shorturl;
+
+-- 创建shorturl表
+CREATE TABLE IF NOT EXISTS shorturl_analytics.shorturl (
+  -- 短链接基本信息（来源于resources表）
+  id String COMMENT '资源ID (resources.id)',
+  external_id String COMMENT '外部ID (resources.external_id)',
+  type String COMMENT '类型，值为shorturl',
+  slug String COMMENT '短链接slug (存储在attributes中)',
+  original_url String COMMENT '原始URL (存储在attributes中)',
+  title String COMMENT '标题 (存储在attributes中)',
+  description String COMMENT '描述 (存储在attributes中)',
+  attributes String DEFAULT '{}' COMMENT '资源属性JSON',
+  schema_version Int32 COMMENT 'Schema版本',
+  -- 创建者信息
+  creator_id String COMMENT '创建者ID (resources.creator_id)',
+  creator_email String COMMENT '创建者邮箱 (来自users表)',
+  creator_name String COMMENT '创建者名称 (来自users表)',
+  -- 时间信息
+  created_at DateTime64(3) COMMENT '创建时间 (resources.created_at)',
+  updated_at DateTime64(3) COMMENT '更新时间 (resources.updated_at)',
+  deleted_at Nullable(DateTime64(3)) COMMENT '删除时间 (resources.deleted_at)',
+  -- 项目关联 (project_resources表)
+  projects String DEFAULT '[]' COMMENT '项目关联信息数组。结构: [{project_id: String, project_name: String, project_description: String, assigned_at: DateTime64}]',
+  -- 团队关联 (通过项目关联到团队)
+  teams String DEFAULT '[]' COMMENT '团队关联信息数组。结构: [{team_id: String, team_name: String, team_description: String, via_project_id: String}]',
+  -- 标签关联 (resource_tags表)
+  tags String DEFAULT '[]' COMMENT '标签关联信息数组。结构: [{tag_id: String, tag_name: String, tag_type: String, created_at: DateTime64}]',
+  -- QR码关联 (qr_code表)
+  qr_codes String DEFAULT '[]' COMMENT 'QR码信息数组。结构: [{qr_id: String, scan_count: Int32, url: String, template_name: String, created_at: DateTime64}]',
+  -- 渠道关联 (channel表)
+  channels String DEFAULT '[]' COMMENT '渠道信息数组。结构: [{channel_id: String, channel_name: String, channel_path: String, is_user_created: Boolean}]',
+  -- 收藏关联 (favorite表)
+  favorites String DEFAULT '[]' COMMENT '收藏信息数组。结构: [{favorite_id: String, user_id: String, user_name: String, created_at: DateTime64}]',
+  -- 自定义过期时间 (存储在attributes中)
+  expires_at Nullable(DateTime64(3)) COMMENT '过期时间',
+  -- 统计信息 (分析时聚合计算)
+  click_count UInt32 DEFAULT 0 COMMENT '点击次数',
+  unique_visitors UInt32 DEFAULT 0 COMMENT '唯一访问者数'
+) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at)
+ORDER BY
+  (id, created_at) SETTINGS index_granularity = 8192 COMMENT '用于存储所有shorturl类型资源的统一表，集成了相关联的项目、团队、标签、QR码、渠道和收藏信息';

scripts/db/sql/clickhouse/truncate_events.sh

@@ -0,0 +1 @@
+./ch-query.sh -q "TRUNCATE TABLE shorturl_analytics.events"

scripts/db/sql/clickhouse/truncate_shorturl.sh

@@ -0,0 +1 @@
+./ch-query.sh -q "TRUNCATE TABLE shorturl_analytics.shorturl"

scripts/db/sync_mongo_to_events.ts

@@ -1,364 +0,0 @@
-// Sync data from MongoDB trace table to ClickHouse events table
-import { getVariable } from "npm:windmill-client@1";
-import { MongoClient, ObjectId } from "https://deno.land/x/mongo@v0.32.0/mod.ts";
-
-interface MongoConfig {
-  host: string;
-  port: string;
-  db: string;
-  username: string;
-  password: string;
-}
-
-interface ClickHouseConfig {
-  clickhouse_host: string;
-  clickhouse_port: number;
-  clickhouse_user: string;
-  clickhouse_password: string;
-  clickhouse_database: string;
-  clickhouse_url: string;
-}
-
-interface TraceRecord {
-  _id: ObjectId;
-  slugId: ObjectId;
-  label: string | null;
-  ip: string;
-  type: number;
-  platform: string;
-  platformOS: string;
-  browser: string;
-  browserVersion: string;
-  url: string;
-  createTime: number;
-}
-
-export async function main(
-  batch_size = 1000,
-  max_records = 9999999,
-  timeout_minutes = 60,
-  skip_clickhouse_check = false,
-  force_insert = false
-) {
-  const logWithTimestamp = (message: string) => {
-    const now = new Date();
-    console.log(`[${now.toISOString()}] ${message}`);
-  };
-
-  logWithTimestamp("Starting sync from MongoDB to ClickHouse events table");
-  logWithTimestamp(`Batch size: ${batch_size}, Max records: ${max_records}, Timeout: ${timeout_minutes} minutes`);
-
-  // Set timeout
-  const startTime = Date.now();
-  const timeoutMs = timeout_minutes * 60 * 1000;
-  
-  const checkTimeout = () => {
-    if (Date.now() - startTime > timeoutMs) {
-      console.log(`Execution time exceeded ${timeout_minutes} minutes, stopping`);
-      return true;
-    }
-    return false;
-  };
-
-  // Get MongoDB and ClickHouse connection info
-  let mongoConfig: MongoConfig;
-  let clickhouseConfig: ClickHouseConfig;
-  
-  try {
-    const rawMongoConfig = await getVariable("f/shorturl_analytics/mongodb");
-    mongoConfig = typeof rawMongoConfig === "string" ? JSON.parse(rawMongoConfig) : rawMongoConfig;
-    
-    const rawClickhouseConfig = await getVariable("f/shorturl_analytics/clickhouse");
-    clickhouseConfig = typeof rawClickhouseConfig === "string" ? JSON.parse(rawClickhouseConfig) : rawClickhouseConfig;
-  } catch (error) {
-    console.error("Failed to get config:", error);
-    throw error;
-  }
-
-  // Build MongoDB connection URL
-  let mongoUrl = "mongodb://";
-  if (mongoConfig.username && mongoConfig.password) {
-    mongoUrl += `${mongoConfig.username}:${mongoConfig.password}@`;
-  }
-  mongoUrl += `${mongoConfig.host}:${mongoConfig.port}/${mongoConfig.db}`;
-
-  // Connect to MongoDB
-  const client = new MongoClient();
-  try {
-    await client.connect(mongoUrl);
-    console.log("MongoDB connected successfully");
-
-    const db = client.database(mongoConfig.db);
-    const traceCollection = db.collection<TraceRecord>("trace");
-    
-    // Build query conditions
-    const query: Record<string, unknown> = {
-      type: 1  // Only sync records with type 1
-    };
-
-    // Count total records
-    const totalRecords = await traceCollection.countDocuments(query);
-    console.log(`Found ${totalRecords} records to sync`);
-
-    const recordsToProcess = Math.min(totalRecords, max_records);
-    console.log(`Will process ${recordsToProcess} records`);
-
-    if (totalRecords === 0) {
-      console.log("No records to sync, task completed");
-      return {
-        success: true,
-        records_synced: 0,
-        message: "No records to sync"
-      };
-    }
-
-    // Check ClickHouse connection
-    const checkClickHouseConnection = async (): Promise<boolean> => {
-      if (skip_clickhouse_check) {
-        logWithTimestamp("Skipping ClickHouse connection check");
-        return true;
-      }
-      
-      try {
-        logWithTimestamp("Testing ClickHouse connection...");
-        const clickhouseUrl = `${clickhouseConfig.clickhouse_url}`;
-        const response = await fetch(clickhouseUrl, {
-          method: "POST",
-          headers: {
-            "Content-Type": "application/x-www-form-urlencoded",
-            "Authorization": `Basic ${btoa(`${clickhouseConfig.clickhouse_user}:${clickhouseConfig.clickhouse_password}`)}`,
-          },
-          body: "SELECT 1",
-          signal: AbortSignal.timeout(5000)
-        });
-        
-        if (response.ok) {
-          logWithTimestamp("ClickHouse connection test successful");
-          return true;
-        } else {
-          const errorText = await response.text();
-          logWithTimestamp(`ClickHouse connection test failed: ${response.status} ${errorText}`);
-          return false;
-        }
-      } catch (err) {
-        logWithTimestamp(`ClickHouse connection test failed: ${(err as Error).message}`);
-        return false;
-      }
-    };
-
-    // Check if records exist in ClickHouse
-    const checkExistingRecords = async (records: TraceRecord[]): Promise<TraceRecord[]> => {
-      if (records.length === 0) return [];
-      
-      if (skip_clickhouse_check || force_insert) {
-        logWithTimestamp(`Skipping ClickHouse duplicate check, will process all ${records.length} records`);
-        return records;
-      }
-      
-      try {
-        const recordIds = records.map(record => record._id.toString());
-        
-        const query = `
-          SELECT event_id 
-          FROM ${clickhouseConfig.clickhouse_database}.events 
-          WHERE event_attributes LIKE '%"mongo_id":"%' 
-          AND event_attributes LIKE ANY ('%${recordIds.join("%' OR '%")}%')
-          FORMAT JSON
-        `;
-        
-        const response = await fetch(clickhouseConfig.clickhouse_url, {
-          method: "POST",
-          headers: {
-            "Content-Type": "application/x-www-form-urlencoded",
-            "Authorization": `Basic ${btoa(`${clickhouseConfig.clickhouse_user}:${clickhouseConfig.clickhouse_password}`)}`
-          },
-          body: query,
-          signal: AbortSignal.timeout(10000)
-        });
-        
-        if (!response.ok) {
-          const errorText = await response.text();
-          throw new Error(`ClickHouse query error: ${response.status} ${errorText}`);
-        }
-        
-        const result = await response.json();
-        const existingIds = new Set(result.data.map((row: any) => {
-          const matches = row.event_attributes.match(/"mongo_id":"([^"]+)"/);
-          return matches ? matches[1] : null;
-        }).filter(Boolean));
-        
-        return records.filter(record => !existingIds.has(record._id.toString()));
-      } catch (err) {
-        logWithTimestamp(`Error checking existing records: ${(err as Error).message}`);
-        return skip_clickhouse_check ? records : [];
-      }
-    };
-
-    // Process records function
-    const processRecords = async (records: TraceRecord[]) => {
-      if (records.length === 0) return 0;
-      
-      const newRecords = await checkExistingRecords(records);
-      if (newRecords.length === 0) {
-        logWithTimestamp("All records already exist, skipping");
-        return 0;
-      }
-      
-      // Prepare ClickHouse insert data
-      const clickhouseData = newRecords.map(record => {
-        const eventTime = new Date(record.createTime).toISOString();
-        return {
-          event_time: eventTime,
-          event_type: "click",
-          event_attributes: JSON.stringify({
-            mongo_id: record._id.toString(),
-            original_type: record.type
-          }),
-          
-          // Link information
-          link_id: record.slugId.toString(),
-          link_slug: "",
-          link_label: record.label || "",
-          link_title: "",
-          link_original_url: record.url || "",
-          link_attributes: "{}",
-          link_created_at: eventTime,
-          link_expires_at: null,
-          link_tags: "[]",
-          
-          // User information (empty as not available in trace)
-          user_id: "",
-          user_name: "",
-          user_email: "",
-          user_attributes: "{}",
-          
-          // Team information (empty as not available in trace)
-          team_id: "",
-          team_name: "",
-          team_attributes: "{}",
-          
-          // Project information (empty as not available in trace)
-          project_id: "",
-          project_name: "",
-          project_attributes: "{}",
-          
-          // QR code information (empty as not available in trace)
-          qr_code_id: "",
-          qr_code_name: "",
-          qr_code_attributes: "{}",
-          
-          // Visitor information
-          visitor_id: record._id.toString(),
-          session_id: `${record._id.toString()}-${record.createTime}`,
-          ip_address: record.ip || "",
-          country: "",
-          city: "",
-          device_type: record.platform || "unknown",
-          browser: record.browser || "",
-          os: record.platformOS || "",
-          user_agent: `${record.browser || ""} ${record.browserVersion || ""}`.trim(),
-          
-          // Source information
-          referrer: record.url || "",
-          utm_source: "",
-          utm_medium: "",
-          utm_campaign: "",
-          
-          // Interaction information
-          time_spent_sec: 0,
-          is_bounce: true,
-          is_qr_scan: false,
-          conversion_type: "visit",
-          conversion_value: 0
-        };
-      });
-
-      // Generate ClickHouse insert SQL
-      const insertSQL = `
-        INSERT INTO ${clickhouseConfig.clickhouse_database}.events 
-        FORMAT JSONEachRow
-        ${JSON.stringify(clickhouseData)}
-      `;
-
-      try {
-        const response = await fetch(clickhouseConfig.clickhouse_url, {
-          method: "POST",
-          headers: {
-            "Content-Type": "application/x-www-form-urlencoded",
-            "Authorization": `Basic ${btoa(`${clickhouseConfig.clickhouse_user}:${clickhouseConfig.clickhouse_password}`)}`
-          },
-          body: insertSQL,
-          signal: AbortSignal.timeout(20000)
-        });
-
-        if (!response.ok) {
-          const errorText = await response.text();
-          throw new Error(`ClickHouse insert error: ${response.status} ${errorText}`);
-        }
-
-        logWithTimestamp(`Successfully inserted ${newRecords.length} records to ClickHouse`);
-        return newRecords.length;
-      } catch (err) {
-        logWithTimestamp(`Failed to insert data to ClickHouse: ${(err as Error).message}`);
-        throw err;
-      }
-    };
-
-    // Check ClickHouse connection before processing
-    const clickhouseConnected = await checkClickHouseConnection();
-    if (!clickhouseConnected && !skip_clickhouse_check) {
-      throw new Error("ClickHouse connection failed, cannot continue sync");
-    }
-
-    // Process records in batches
-    let processedRecords = 0;
-    let totalBatchRecords = 0;
-
-    for (let page = 0; processedRecords < recordsToProcess; page++) {
-      if (checkTimeout()) {
-        logWithTimestamp(`Processed ${processedRecords}/${recordsToProcess} records, stopping due to timeout`);
-        break;
-      }
-
-      logWithTimestamp(`Processing batch ${page+1}, completed ${processedRecords}/${recordsToProcess} records (${Math.round(processedRecords/recordsToProcess*100)}%)`);
-      
-      const records = await traceCollection.find(
-        query,
-        {
-          allowDiskUse: true,
-          sort: { createTime: 1 },
-          skip: page * batch_size,
-          limit: batch_size
-        }
-      ).toArray();
-
-      if (records.length === 0) {
-        logWithTimestamp("No more records found, sync complete");
-        break;
-      }
-
-      const batchSize = await processRecords(records);
-      processedRecords += records.length;
-      totalBatchRecords += batchSize;
-
-      logWithTimestamp(`Batch ${page+1} complete. Processed ${processedRecords}/${recordsToProcess} records, inserted ${totalBatchRecords} (${Math.round(processedRecords/recordsToProcess*100)}%)`);
-    }
-
-    return {
-      success: true,
-      records_processed: processedRecords,
-      records_synced: totalBatchRecords,
-      message: "Data sync completed"
-    };
-  } catch (err) {
-    console.error("Error during sync:", err);
-    return {
-      success: false,
-      error: err instanceof Error ? err.message : String(err),
-      stack: err instanceof Error ? err.stack : undefined
-    };
-  } finally {
-    await client.close();
-    console.log("MongoDB connection closed");
-  }
-} 

windmill/sync_mongo_short_to_postgres_short_url_shorturl.ts

@@ -0,0 +1,522 @@
+// 从MongoDB的main.short表同步数据到PostgreSQL的short_url.shorturl表
+import { getVariable, setVariable, getResource } from "npm:windmill-client@1";
+import { MongoClient, ObjectId } from "https://deno.land/x/mongo@v0.32.0/mod.ts";
+import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
+
+interface MongoConfig {
+  host: string;
+  port: string;
+  db: string;
+  username: string;
+  password: string;
+}
+
+interface PostgresConfig {
+  host: string;
+  port: number;
+  database: string;
+  user: string;
+  password: string;
+  schema: string;
+}
+
+// 扩展ShortRecord接口以包含更多可能的字段
+interface ShortRecord {
+  _id: ObjectId;
+  origin: string;
+  slug: string;
+  domain: string | null;
+  createTime: number | { $numberLong: string } | string;
+  // 可选字段
+  expiredAt?: number | { $numberLong: string } | string | null;
+  expiredUrl?: string | null;
+  password?: string | null;
+  image?: string | null;
+  title?: string | null;
+  description?: string | null;
+}
+
+interface SyncState {
+  last_sync_time: number;
+  records_synced: number;
+  last_sync_id?: string;
+}
+
+// 同步状态键名
+const SYNC_STATE_KEY = "f/limq/mongo_short_to_postgres_shorturl_shorturl_state";
+
+export async function main(
+  batch_size = 1000,
+  max_records = 9999999,
+  timeout_minutes = 60,
+  skip_duplicate_check = false,
+  force_insert = false,
+  reset_sync_state = false,
+  postgres_schema = "short_url", // 添加schema参数，允许运行时指定
+  postgres_database = "postgres", // 添加数据库名称参数，默认为postgres
+  domain = "upj.to" // 添加domain参数，允许用户指定域名
+) {
+  const logWithTimestamp = (message: string) => {
+    const now = new Date();
+    console.log(`[${now.toISOString()}] ${message}`);
+  };
+
+  logWithTimestamp("开始执行MongoDB到PostgreSQL的同步任务");
+  logWithTimestamp(`批处理大小: ${batch_size}, 最大记录数: ${max_records}, 超时时间: ${timeout_minutes}分钟`);
+  logWithTimestamp(`使用域名: ${domain}`);
+  if (skip_duplicate_check) {
+    logWithTimestamp("⚠️ 警告: 已启用跳过重复检查模式，不会检查记录是否已存在");
+  }
+  if (force_insert) {
+    logWithTimestamp("⚠️ 警告: 已启用强制插入模式，将尝试插入所有记录");
+  }
+  if (reset_sync_state) {
+    logWithTimestamp("⚠️ 警告: 已启用重置同步状态，将从头开始同步数据");
+  }
+
+  // 设置超时
+  const startTime = Date.now();
+  const timeoutMs = timeout_minutes * 60 * 1000;
+  
+  // 检查是否超时
+  const checkTimeout = () => {
+    if (Date.now() - startTime > timeoutMs) {
+      logWithTimestamp(`运行时间超过${timeout_minutes}分钟，暂停执行`);
+      return true;
+    }
+    return false;
+  };
+
+  // 日期解析函数，处理不同格式的日期
+  const parseDate = (dateValue: any): Date | null => {
+    if (!dateValue) return null;
+
+    // 处理 MongoDB $numberLong 格式
+    if (dateValue.$numberLong) {
+      return new Date(Number(dateValue.$numberLong));
+    }
+
+    // 处理普通时间戳
+    if (typeof dateValue === 'number') {
+      return new Date(dateValue);
+    }
+
+    // 处理 ISO 字符串格式
+    if (typeof dateValue === 'string') {
+      const date = new Date(dateValue);
+      return isNaN(date.getTime()) ? null : date;
+    }
+
+    return null;
+  };
+
+  // 获取MongoDB和PostgreSQL的连接信息
+  let mongoConfig: MongoConfig;
+  let postgresConfig: PostgresConfig;
+  
+  try {
+    const rawMongoConfig = await getVariable("f/shorturl_analytics/mongodb");
+    if (typeof rawMongoConfig === "string") {
+      try {
+        mongoConfig = JSON.parse(rawMongoConfig);
+      } catch (e) {
+        console.error("MongoDB配置解析失败:", e);
+        throw e;
+      }
+    } else {
+      mongoConfig = rawMongoConfig as MongoConfig;
+    }
+    
+    // 使用getResource获取PostgreSQL资源
+    try {
+      logWithTimestamp("正在获取PostgreSQL资源...");
+      const resourceConfig = await getResource("f/limq/production_supabase");
+      
+      // 将resource转换为PostgresConfig
+      postgresConfig = {
+        host: resourceConfig.host || "",
+        port: Number(resourceConfig.port) || 5432,
+        user: resourceConfig.user || "",
+        password: resourceConfig.password || "",
+        database: resourceConfig.database || postgres_database, // 使用提供的数据库名称作为备选
+        schema: resourceConfig.schema || postgres_schema // 使用提供的schema作为备选
+      };
+      
+      // 检查并记录配置信息
+      if (!postgresConfig.database || postgresConfig.database === "undefined") {
+        postgresConfig.database = postgres_database;
+        logWithTimestamp(`数据库名称未指定或为"undefined"，使用提供的值: ${postgresConfig.database}`);
+      }
+      
+      if (!postgresConfig.schema || postgresConfig.schema === "undefined") {
+        postgresConfig.schema = postgres_schema;
+        logWithTimestamp(`Schema未指定或为"undefined"，使用提供的值: ${postgresConfig.schema}`);
+      }
+      
+      logWithTimestamp(`PostgreSQL配置: 数据库=${postgresConfig.database}, Schema=${postgresConfig.schema}`);
+    } catch (e) {
+      console.error("获取PostgreSQL资源失败:", e);
+      throw e;
+    }
+    
+    console.log("MongoDB配置:", JSON.stringify({
+      ...mongoConfig,
+      password: "****" // 隐藏密码
+    }));
+    console.log("PostgreSQL配置:", JSON.stringify({
+      ...postgresConfig,
+      password: "****" // 隐藏密码
+    }));
+  } catch (error) {
+    console.error("获取配置失败:", error);
+    throw error;
+  }
+
+  // 获取上次同步状态
+  let lastSyncState: SyncState | null = null;
+  if (!reset_sync_state) {
+    try {
+      const rawSyncState = await getVariable(SYNC_STATE_KEY);
+      if (rawSyncState) {
+        if (typeof rawSyncState === "string") {
+          try {
+            lastSyncState = JSON.parse(rawSyncState);
+          } catch (e) {
+            logWithTimestamp(`解析上次同步状态失败: ${e}, 将从头开始同步`);
+          }
+        } else {
+          lastSyncState = rawSyncState as SyncState;
+        }
+      }
+    } catch (error) {
+      logWithTimestamp(`获取上次同步状态失败: ${error}, 将从头开始同步`);
+    }
+  }
+
+  if (lastSyncState) {
+    logWithTimestamp(`找到上次同步状态: 最后同步时间 ${new Date(lastSyncState.last_sync_time).toISOString()}, 已同步记录数 ${lastSyncState.records_synced}`);
+    if (lastSyncState.last_sync_id) {
+      logWithTimestamp(`最后同步ID: ${lastSyncState.last_sync_id}`);
+    }
+  } else {
+    logWithTimestamp("没有找到上次同步状态，将从头开始同步");
+  }
+
+  // 构建MongoDB连接URL
+  let mongoUrl = "mongodb://";
+  if (mongoConfig.username && mongoConfig.password) {
+    mongoUrl += `${mongoConfig.username}:${mongoConfig.password}@`;
+  }
+  mongoUrl += `${mongoConfig.host}:${mongoConfig.port}/${mongoConfig.db}`;
+
+  console.log(`MongoDB连接URL: ${mongoUrl.replace(/:[^:]*@/, ":****@")}`);
+
+  // 构建PostgreSQL连接URL
+  const pgConnectionString = `postgres://${postgresConfig.user}:${postgresConfig.password}@${postgresConfig.host}:${postgresConfig.port}/${postgresConfig.database}`;
+  console.log(`PostgreSQL连接URL: ${pgConnectionString.replace(/:[^:]*@/, ":****@")}`);
+
+  // 连接MongoDB
+  const mongoClient = new MongoClient();
+  let pgClient: Client | null = null;
+  
+  try {
+    await mongoClient.connect(mongoUrl);
+    logWithTimestamp("MongoDB连接成功");
+
+    // 连接PostgreSQL
+    pgClient = new Client(pgConnectionString);
+    await pgClient.connect();
+    logWithTimestamp("PostgreSQL连接成功");
+
+    // 确认PostgreSQL schema存在
+    try {
+      await pgClient.queryArray(`SELECT 1 FROM information_schema.schemata WHERE schema_name = '${postgresConfig.schema}'`);
+      logWithTimestamp(`PostgreSQL schema '${postgresConfig.schema}' 已确认存在`);
+    } catch (error) {
+      logWithTimestamp(`检查PostgreSQL schema失败: ${error}`);
+      throw new Error(`Schema '${postgresConfig.schema}' 可能不存在`);
+    }
+
+    const db = mongoClient.database(mongoConfig.db);
+    const shortCollection = db.collection<ShortRecord>("short");
+    
+    // 构建查询条件，根据上次同步状态获取新记录
+    const query: Record<string, unknown> = {};
+
+    // 如果有上次同步状态，则只获取更新的记录
+    if (lastSyncState && lastSyncState.last_sync_time) {
+      // 使用上次同步时间作为过滤条件
+      query.createTime = { $gt: lastSyncState.last_sync_time };
+      logWithTimestamp(`将只同步createTime > ${lastSyncState.last_sync_time} (${new Date(lastSyncState.last_sync_time).toISOString()}) 的记录`);
+    }
+
+    // 计算总记录数
+    const totalRecords = await shortCollection.countDocuments(query);
+    logWithTimestamp(`找到 ${totalRecords} 条新记录需要同步`);
+
+    // 限制此次处理的记录数量
+    const recordsToProcess = Math.min(totalRecords, max_records);
+    logWithTimestamp(`本次将处理 ${recordsToProcess} 条记录`);
+
+    if (totalRecords === 0) {
+      logWithTimestamp("没有新记录需要同步，任务完成");
+      return {
+        success: true,
+        records_synced: 0,
+        message: "没有新记录需要同步"
+      };
+    }
+
+    // 检查记录是否已经存在于PostgreSQL中
+    const checkExistingRecords = async (records: ShortRecord[]): Promise<ShortRecord[]> => {
+      if (records.length === 0) return [];
+      
+      // 如果跳过重复检查或强制插入，则直接返回所有记录
+      if (skip_duplicate_check || force_insert) {
+        logWithTimestamp(`已跳过重复检查，准备处理所有 ${records.length} 条记录`);
+        return records;
+      }
+      
+      logWithTimestamp(`正在检查 ${records.length} 条记录是否已存在于PostgreSQL中...`);
+      
+      try {
+        // 提取所有记录的slugs
+        const slugs = records.map(record => record.slug);
+        
+        // 查询PostgreSQL中是否已存在这些slugs
+        const result = await pgClient!.queryArray(`
+          SELECT slug FROM ${postgresConfig.schema}.shorturl 
+          WHERE slug = ANY($1::text[])
+        `, [slugs]);
+        
+        // 将已存在的slugs加入到集合中
+        const existingSlugs = new Set<string>();
+        for (const row of result.rows) {
+          existingSlugs.add(row[0] as string);
+        }
+        
+        logWithTimestamp(`检测到 ${existingSlugs.size} 条记录已存在于PostgreSQL中`);
+        
+        // 过滤出不存在的记录
+        const newRecords = records.filter(record => !existingSlugs.has(record.slug));
+        logWithTimestamp(`过滤后剩余 ${newRecords.length} 条新记录需要插入`);
+        
+        return newRecords;
+      } catch (err) {
+        const error = err as Error;
+        logWithTimestamp(`PostgreSQL查询出错: ${error.message}`);
+        if (skip_duplicate_check) {
+          logWithTimestamp("已启用跳过重复检查，将继续处理所有记录");
+          return records;
+        } else {
+          throw error;
+        }
+      }
+    };
+
+    // 处理记录的函数
+    const processRecords = async (records: ShortRecord[]) => {
+      if (records.length === 0) return 0;
+      
+      logWithTimestamp(`开始处理批次数据，共 ${records.length} 条记录...`);
+      
+      // 检查记录是否已存在
+      let newRecords;
+      try {
+        newRecords = await checkExistingRecords(records);
+      } catch (err) {
+        const error = err as Error;
+        logWithTimestamp(`检查记录是否存在时出错: ${error.message}`);
+        if (!skip_duplicate_check && !force_insert) {
+          throw error;
+        }
+        // 如果跳过检查或强制插入，则使用所有记录
+        logWithTimestamp("将使用所有记录进行处理");
+        newRecords = records;
+      }
+      
+      if (newRecords.length === 0) {
+        logWithTimestamp("所有记录都已存在，跳过处理");
+        return 0;
+      }
+      
+      logWithTimestamp(`准备处理 ${newRecords.length} 条新记录...`);
+      
+      // 批量插入PostgreSQL
+      try {
+        // 开始事务
+        await pgClient!.queryArray('BEGIN');
+        
+        let insertedCount = 0;
+        
+        // 由于参数可能很多，按小批次处理
+        const smallBatchSize = 100;
+        for (let i = 0; i < newRecords.length; i += smallBatchSize) {
+          const batchRecords = newRecords.slice(i, i + smallBatchSize);
+          
+          // 构造批量插入语句
+          const placeholders = [];
+          const values = [];
+          let valueIndex = 1;
+          
+          for (const record of batchRecords) {
+            // 参考提供的字段处理方式处理数据
+            const createdAt = parseDate(record.createTime);
+            const updatedAt = createdAt; // 设置更新时间等于创建时间
+            const fullShortUrl = `${domain}/${record.slug}`;
+            
+            placeholders.push(`($${valueIndex}, $${valueIndex+1}, $${valueIndex+2}, $${valueIndex+3}, $${valueIndex+4}, $${valueIndex+5}, $${valueIndex+6}, $${valueIndex+7}, $${valueIndex+8}, $${valueIndex+9}, $${valueIndex+10}, $${valueIndex+11}, $${valueIndex+12})`);
+            
+            values.push(
+              record._id.toString(), // id
+              record.slug, // slug
+              domain, // domain (使用提供的域名)
+              record.slug, // name (使用slug作为name)
+              record.slug, // title (使用slug作为title)
+              record.origin || '', // origin
+              createdAt, // created_at
+              updatedAt, // updated_at
+              fullShortUrl, // full_short_url
+              record.image || null, // image
+              record.description || null, // description
+              record.expiredUrl || null, // expired_url
+              parseDate(record.expiredAt) // expired_at
+            );
+            
+            valueIndex += 13;
+          }
+          
+          const query = `
+            INSERT INTO ${postgresConfig.schema}.shorturl 
+            (id, slug, domain, name, title, origin, created_at, updated_at, full_short_url, image, description, expired_url, expired_at)
+            VALUES ${placeholders.join(', ')}
+          `;
+          
+          await pgClient!.queryArray(query, values);
+          insertedCount += batchRecords.length;
+          logWithTimestamp(`已插入 ${insertedCount}/${newRecords.length} 条记录`);
+        }
+        
+        // 提交事务
+        await pgClient!.queryArray('COMMIT');
+        
+        logWithTimestamp(`成功插入 ${insertedCount} 条记录到PostgreSQL`);
+        return insertedCount;
+      } catch (err) {
+        const error = err as Error;
+        // 发生错误，回滚事务
+        await pgClient!.queryArray('ROLLBACK');
+        logWithTimestamp(`向PostgreSQL插入数据失败: ${error.message}`);
+        throw error;
+      }
+    };
+
+    // 批量处理记录
+    let processedRecords = 0;
+    let totalBatchRecords = 0;
+    let lastSyncTime = 0;
+    let lastSyncId = "";
+
+    for (let page = 0; processedRecords < recordsToProcess; page++) {
+      // 检查超时
+      if (checkTimeout()) {
+        logWithTimestamp(`已处理 ${processedRecords}/${recordsToProcess} 条记录，因超时暂停执行`);
+        break;
+      }
+
+      // 每批次都输出进度
+      logWithTimestamp(`开始处理第 ${page+1} 批次，已完成 ${processedRecords}/${recordsToProcess} 条记录 (${Math.round(processedRecords/recordsToProcess*100)}%)`);
+      
+      logWithTimestamp(`正在从MongoDB获取第 ${page+1} 批次数据...`);
+      const records = await shortCollection.find(
+        query,
+        {
+          sort: { createTime: 1 },
+          skip: page * batch_size,
+          limit: batch_size
+        }
+      ).toArray();
+
+      if (records.length === 0) {
+        logWithTimestamp("没有找到更多数据，同步结束");
+        break;
+      }
+      
+      // 找到数据，开始处理
+      logWithTimestamp(`获取到 ${records.length} 条记录，开始处理...`);
+      
+      // 输出当前批次的部分数据信息
+      if (records.length > 0) {
+        logWithTimestamp(`批次 ${page+1} 第一条记录: ID=${records[0]._id}, slug=${records[0].slug}, 时间=${new Date(typeof records[0].createTime === 'number' ? records[0].createTime : 0).toISOString()}`);
+        if (records.length > 1) {
+          const lastRec = records[records.length-1];
+          logWithTimestamp(`批次 ${page+1} 最后一条记录: ID=${lastRec._id}, slug=${lastRec.slug}, 时间=${new Date(typeof lastRec.createTime === 'number' ? lastRec.createTime : 0).toISOString()}`);
+        }
+      }
+
+      const batchSize = await processRecords(records);
+      processedRecords += records.length;
+      totalBatchRecords += batchSize;
+
+      // 更新最后处理的记录时间和ID
+      if (records.length > 0) {
+        const lastRecord = records[records.length - 1];
+        // 提取数字时间戳
+        let lastCreateTime = 0;
+        if (typeof lastRecord.createTime === 'number') {
+          lastCreateTime = lastRecord.createTime;
+        } else if (lastRecord.createTime && lastRecord.createTime.$numberLong) {
+          lastCreateTime = Number(lastRecord.createTime.$numberLong);
+        }
+        
+        lastSyncTime = Math.max(lastSyncTime, lastCreateTime);
+        lastSyncId = lastRecord._id.toString();
+      }
+
+      logWithTimestamp(`第 ${page+1} 批次处理完成。已处理 ${processedRecords}/${recordsToProcess} 条记录，实际插入 ${totalBatchRecords} 条 (${Math.round(processedRecords/recordsToProcess*100)}%)`);
+    }
+
+    // 更新同步状态
+    if (processedRecords > 0 && lastSyncTime > 0) {
+      // 创建新的同步状态
+      const newSyncState: SyncState = {
+        last_sync_time: lastSyncTime,
+        records_synced: (lastSyncState ? lastSyncState.records_synced : 0) + totalBatchRecords,
+        last_sync_id: lastSyncId
+      };
+      
+      try {
+        // 保存同步状态
+        await setVariable(SYNC_STATE_KEY, newSyncState);
+        logWithTimestamp(`同步状态已更新: 最后同步时间 ${new Date(newSyncState.last_sync_time).toISOString()}, 累计同步记录数 ${newSyncState.records_synced}`);
+      } catch (err) {
+        const error = err as Error;
+        logWithTimestamp(`更新同步状态失败: ${error.message}`);
+      }
+    }
+
+    return {
+      success: true,
+      records_processed: processedRecords,
+      records_synced: totalBatchRecords,
+      last_sync_time: lastSyncTime > 0 ? new Date(lastSyncTime).toISOString() : null,
+      message: "数据同步完成"
+    };
+  } catch (err) {
+    const error = err as Error;
+    console.error("同步过程中发生错误:", error);
+    return {
+      success: false,
+      error: error.message,
+      stack: error.stack
+    };
+  } finally {
+    // 关闭连接
+    if (pgClient) {
+      await pgClient.end();
+      logWithTimestamp("PostgreSQL连接已关闭");
+    }
+    await mongoClient.close();
+    logWithTimestamp("MongoDB连接已关闭");
+  }
+} 

windmill/sync_mongo_to_events.ts

@@ -1,5 +1,5 @@
-// Sync data from MongoDB trace table to ClickHouse events table
-import { getVariable } from "npm:windmill-client@1";
+// 从MongoDB的trace表同步数据到ClickHouse的events表
+import { getVariable, setVariable } from "npm:windmill-client@1";
 import { MongoClient, ObjectId } from "https://deno.land/x/mongo@v0.32.0/mod.ts";
 
 interface MongoConfig {
@@ -15,6 +15,7 @@ interface ClickHouseConfig {
   clickhouse_port: number;
   clickhouse_user: string;
   clickhouse_password: string;
+  clickhouse_database: string;
   clickhouse_url: string;
 }
 
@@ -32,6 +33,7 @@ interface TraceRecord {
   createTime: number;
 }
 
+// 添加 ShortRecord 接口定义
 interface ShortRecord {
   _id: ObjectId;
   slug: string;          // 短链接的slug部分
@@ -48,9 +50,134 @@ interface ShortRecord {
   projectId?: string;    // 项目ID
 }
 
-interface ClickHouseRow {
-  event_id: string;
-  event_attributes: string;
+interface SyncState {
+  last_sync_time: number;
+  records_synced: number;
+  last_sync_id?: string;
+}
+
+// 定义UTM参数接口
+interface UtmParams {
+  utm_source: string;
+  utm_medium: string;
+  utm_campaign: string;
+  utm_term: string;
+  utm_content: string;
+}
+
+// 同步状态键名
+const SYNC_STATE_KEY = "f/shorturl_analytics/mongo_sync_state";
+
+// 从URL中提取UTM参数的函数，增强版
+function extractUtmParams(url: string, debug = false): UtmParams {
+  const defaultUtmParams: UtmParams = {
+    utm_source: "",
+    utm_medium: "",
+    utm_campaign: "",
+    utm_term: "",
+    utm_content: ""
+  };
+  
+  if (!url) return defaultUtmParams;
+  
+  if (debug) {
+    console.log(`[UTM提取] 原始URL: ${url}`);
+  }
+  
+  // 准备一个解析后的参数对象
+  const params: UtmParams = { ...defaultUtmParams };
+  
+  // 尝试多种方法提取UTM参数
+  
+  // 方法1: 使用URL对象解析
+  try {
+    // 先处理URL，确保是完整的URL格式
+    let normalizedUrl = url;
+    if (!url.match(/^https?:\/\//i)) {
+      normalizedUrl = `https://example.com${url.startsWith('/') ? '' : '/'}${url}`;
+    }
+    
+    const urlObj = new URL(normalizedUrl);
+    
+    // 读取URL参数
+    if (urlObj.searchParams.has('utm_source')) 
+      params.utm_source = urlObj.searchParams.get('utm_source') || "";
+    if (urlObj.searchParams.has('utm_medium')) 
+      params.utm_medium = urlObj.searchParams.get('utm_medium') || "";
+    if (urlObj.searchParams.has('utm_campaign')) 
+      params.utm_campaign = urlObj.searchParams.get('utm_campaign') || "";
+    if (urlObj.searchParams.has('utm_term')) 
+      params.utm_term = urlObj.searchParams.get('utm_term') || "";
+    if (urlObj.searchParams.has('utm_content')) 
+      params.utm_content = urlObj.searchParams.get('utm_content') || "";
+    
+    if (debug) {
+      console.log(`[UTM提取] URL对象解析结果: ${JSON.stringify(params)}`);
+    }
+    
+    // 如果至少找到一个UTM参数，则返回
+    if (params.utm_source || params.utm_medium || params.utm_campaign || 
+        params.utm_term || params.utm_content) {
+      return params;
+    }
+  } catch (_err) {
+    if (debug) {
+      console.log(`[UTM提取] URL对象解析失败，尝试正则表达式`);
+    }
+  }
+  
+  // 方法2: 使用正则表达式提取参数
+  // 使用正则表达式（最安全的方法，适用于任何格式）
+  const sourceMatch = url.match(/[?&]utm_source=([^&#]+)/i);
+  if (sourceMatch && sourceMatch[1]) {
+    try {
+      params.utm_source = decodeURIComponent(sourceMatch[1]);
+    } catch (_) {
+      params.utm_source = sourceMatch[1];
+    }
+  }
+  
+  const mediumMatch = url.match(/[?&]utm_medium=([^&#]+)/i);
+  if (mediumMatch && mediumMatch[1]) {
+    try {
+      params.utm_medium = decodeURIComponent(mediumMatch[1]);
+    } catch (_) {
+      params.utm_medium = mediumMatch[1];
+    }
+  }
+  
+  const campaignMatch = url.match(/[?&]utm_campaign=([^&#]+)/i);
+  if (campaignMatch && campaignMatch[1]) {
+    try {
+      params.utm_campaign = decodeURIComponent(campaignMatch[1]);
+    } catch (_) {
+      params.utm_campaign = campaignMatch[1];
+    }
+  }
+  
+  const termMatch = url.match(/[?&]utm_term=([^&#]+)/i);
+  if (termMatch && termMatch[1]) {
+    try {
+      params.utm_term = decodeURIComponent(termMatch[1]);
+    } catch (_) {
+      params.utm_term = termMatch[1];
+    }
+  }
+  
+  const contentMatch = url.match(/[?&]utm_content=([^&#]+)/i);
+  if (contentMatch && contentMatch[1]) {
+    try {
+      params.utm_content = decodeURIComponent(contentMatch[1]);
+    } catch (_) {
+      params.utm_content = contentMatch[1];
+    }
+  }
+  
+  if (debug) {
+    console.log(`[UTM提取] 正则表达式解析结果: ${JSON.stringify(params)}`);
+  }
+  
+  return params;
 }
 
 export async function main(
@@ -58,90 +185,185 @@ export async function main(
   max_records = 9999999,
   timeout_minutes = 60,
   skip_clickhouse_check = false,
-  force_insert = false
+  force_insert = false,
+  database_override = "shorturl_analytics", // 添加数据库名称参数，默认为shorturl_analytics
+  reset_sync_state = false, // 添加参数用于重置同步状态
+  debug_utm = false // 添加参数控制UTM调试日志输出
 ) {
   const logWithTimestamp = (message: string) => {
     const now = new Date();
     console.log(`[${now.toISOString()}] ${message}`);
   };
 
-  logWithTimestamp("Starting sync from MongoDB to ClickHouse events table");
-  logWithTimestamp(`Batch size: ${batch_size}, Max records: ${max_records}, Timeout: ${timeout_minutes} minutes`);
+  logWithTimestamp("开始执行MongoDB到ClickHouse的同步任务");
+  logWithTimestamp(`批处理大小: ${batch_size}, 最大记录数: ${max_records}, 超时时间: ${timeout_minutes}分钟`);
+  if (skip_clickhouse_check) {
+    logWithTimestamp("⚠️ 警告: 已启用跳过ClickHouse检查模式，不会检查记录是否已存在");
+  }
+  if (force_insert) {
+    logWithTimestamp("⚠️ 警告: 已启用强制插入模式，将尝试插入所有记录");
+  }
+  if (reset_sync_state) {
+    logWithTimestamp("⚠️ 警告: 已启用重置同步状态，将从头开始同步数据");
+  }
+  if (debug_utm) {
+    logWithTimestamp("已启用UTM参数调试日志");
+  }
 
-  // Set timeout
+  // 设置超时
   const startTime = Date.now();
   const timeoutMs = timeout_minutes * 60 * 1000;
   
+  // 检查是否超时
   const checkTimeout = () => {
     if (Date.now() - startTime > timeoutMs) {
-      console.log(`Execution time exceeded ${timeout_minutes} minutes, stopping`);
+      console.log(`运行时间超过${timeout_minutes}分钟，暂停执行`);
       return true;
     }
     return false;
   };
 
-  // Get MongoDB and ClickHouse connection info
+  // 获取MongoDB和ClickHouse的连接信息
   let mongoConfig: MongoConfig;
   let clickhouseConfig: ClickHouseConfig;
   
   try {
     const rawMongoConfig = await getVariable("f/shorturl_analytics/mongodb");
-    mongoConfig = typeof rawMongoConfig === "string" ? JSON.parse(rawMongoConfig) : rawMongoConfig;
+    console.log("原始MongoDB配置:", JSON.stringify(rawMongoConfig));
+    
+    // 尝试解析配置，如果是字符串形式
+    if (typeof rawMongoConfig === "string") {
+      try {
+        mongoConfig = JSON.parse(rawMongoConfig);
+      } catch (e) {
+        console.error("MongoDB配置解析失败:", e);
+        throw e;
+      }
+    } else {
+      mongoConfig = rawMongoConfig as MongoConfig;
+    }
     
     const rawClickhouseConfig = await getVariable("f/shorturl_analytics/clickhouse");
-    clickhouseConfig = typeof rawClickhouseConfig === "string" ? JSON.parse(rawClickhouseConfig) : rawClickhouseConfig;
+    console.log("原始ClickHouse配置:", JSON.stringify(rawClickhouseConfig));
+    
+    // 尝试解析配置，如果是字符串形式
+    if (typeof rawClickhouseConfig === "string") {
+      try {
+        clickhouseConfig = JSON.parse(rawClickhouseConfig);
+      } catch (e) {
+        console.error("ClickHouse配置解析失败:", e);
+        throw e;
+      }
+    } else {
+      clickhouseConfig = rawClickhouseConfig as ClickHouseConfig;
+    }
+    
+    // 检查并修复数据库配置
+    if (!clickhouseConfig.clickhouse_database || clickhouseConfig.clickhouse_database === "undefined") {
+      logWithTimestamp(`⚠️ 警告: 数据库名称未定义或为'undefined'，使用提供的默认值: ${database_override}`);
+      clickhouseConfig.clickhouse_database = database_override;
+    }
+    
+    console.log("MongoDB配置解析为:", JSON.stringify(mongoConfig));
+    console.log("ClickHouse配置解析为:", JSON.stringify({
+      ...clickhouseConfig,
+      clickhouse_password: "****" // 隐藏密码
+    }));
+    
+    logWithTimestamp(`将使用ClickHouse数据库: ${clickhouseConfig.clickhouse_database}`);
   } catch (error) {
-    console.error("Failed to get config:", error);
+    console.error("获取配置失败:", error);
     throw error;
   }
 
-  // Build MongoDB connection URL
+  // 获取上次同步状态
+  let lastSyncState: SyncState | null = null;
+  if (!reset_sync_state) {
+    try {
+      const rawSyncState = await getVariable(SYNC_STATE_KEY);
+      if (rawSyncState) {
+        if (typeof rawSyncState === "string") {
+          try {
+            lastSyncState = JSON.parse(rawSyncState);
+          } catch (e) {
+            logWithTimestamp(`解析上次同步状态失败: ${e}, 将从头开始同步`);
+          }
+        } else {
+          lastSyncState = rawSyncState as SyncState;
+        }
+      }
+    } catch (error) {
+      logWithTimestamp(`获取上次同步状态失败: ${error}, 将从头开始同步`);
+    }
+  }
+
+  if (lastSyncState) {
+    logWithTimestamp(`找到上次同步状态: 最后同步时间 ${new Date(lastSyncState.last_sync_time).toISOString()}, 已同步记录数 ${lastSyncState.records_synced}`);
+    if (lastSyncState.last_sync_id) {
+      logWithTimestamp(`最后同步ID: ${lastSyncState.last_sync_id}`);
+    }
+  } else {
+    logWithTimestamp("没有找到上次同步状态，将从头开始同步");
+  }
+
+  // 构建MongoDB连接URL
   let mongoUrl = "mongodb://";
   if (mongoConfig.username && mongoConfig.password) {
     mongoUrl += `${mongoConfig.username}:${mongoConfig.password}@`;
   }
   mongoUrl += `${mongoConfig.host}:${mongoConfig.port}/${mongoConfig.db}`;
 
-  // Connect to MongoDB
+  console.log(`MongoDB连接URL: ${mongoUrl.replace(/:[^:]*@/, ":****@")}`);
+
+  // 连接MongoDB
   const client = new MongoClient();
   try {
     await client.connect(mongoUrl);
-    console.log("MongoDB connected successfully");
+    console.log("MongoDB连接成功");
 
     const db = client.database(mongoConfig.db);
     const traceCollection = db.collection<TraceRecord>("trace");
+    // 添加对short集合的引用
     const shortCollection = db.collection<ShortRecord>("short");
     
-    // Build query conditions
+    // 构建查询条件，根据上次同步状态获取新记录
     const query: Record<string, unknown> = {
-      type: 1  // Only sync records with type 1
+      type: 1  // 只同步type为1的记录
     };
 
-    // Count total records
-    const totalRecords = await traceCollection.countDocuments(query);
-    console.log(`Found ${totalRecords} records to sync`);
+    // 如果有上次同步状态，则只获取更新的记录
+    if (lastSyncState && lastSyncState.last_sync_time) {
+      // 使用上次同步时间作为过滤条件
+      query.createTime = { $gt: lastSyncState.last_sync_time };
+      logWithTimestamp(`将只同步createTime > ${lastSyncState.last_sync_time} (${new Date(lastSyncState.last_sync_time).toISOString()}) 的记录`);
+    }
 
+    // 计算总记录数
+    const totalRecords = await traceCollection.countDocuments(query);
+    console.log(`找到 ${totalRecords} 条新记录需要同步`);
+
+    // 限制此次处理的记录数量
     const recordsToProcess = Math.min(totalRecords, max_records);
-    console.log(`Will process ${recordsToProcess} records`);
+    console.log(`本次将处理 ${recordsToProcess} 条记录`);
 
     if (totalRecords === 0) {
-      console.log("No records to sync, task completed");
+      console.log("没有新记录需要同步，任务完成");
       return {
         success: true,
         records_synced: 0,
-        message: "No records to sync"
+        message: "没有新记录需要同步"
       };
     }
 
-    // Check ClickHouse connection
+    // 检查ClickHouse连接状态
     const checkClickHouseConnection = async (): Promise<boolean> => {
       if (skip_clickhouse_check) {
-        logWithTimestamp("Skipping ClickHouse connection check");
+        logWithTimestamp("已启用跳过ClickHouse检查，不测试连接");
         return true;
       }
       
       try {
-        logWithTimestamp("Testing ClickHouse connection...");
+        logWithTimestamp("测试ClickHouse连接...");
         const clickhouseUrl = `${clickhouseConfig.clickhouse_url}`;
         const response = await fetch(clickhouseUrl, {
           method: "POST",
@@ -149,45 +371,61 @@ export async function main(
             "Content-Type": "application/x-www-form-urlencoded",
             "Authorization": `Basic ${btoa(`${clickhouseConfig.clickhouse_user}:${clickhouseConfig.clickhouse_password}`)}`,
           },
-          body: "SELECT 1",
+          body: `SELECT 1 FROM ${clickhouseConfig.clickhouse_database}.events LIMIT 1`,
+          // 设置5秒超时
           signal: AbortSignal.timeout(5000)
         });
         
         if (response.ok) {
-          logWithTimestamp("ClickHouse connection test successful");
+          logWithTimestamp("ClickHouse连接测试成功");
           return true;
         } else {
           const errorText = await response.text();
-          logWithTimestamp(`ClickHouse connection test failed: ${response.status} ${errorText}`);
+          logWithTimestamp(`ClickHouse连接测试失败: ${response.status} ${errorText}`);
           return false;
         }
       } catch (err) {
-        logWithTimestamp(`ClickHouse connection test failed: ${(err as Error).message}`);
+        const error = err as Error;
+        logWithTimestamp(`ClickHouse连接测试失败: ${error.message}`);
         return false;
       }
     };
 
-    // Check if records exist in ClickHouse
+    // 检查记录是否已经存在于ClickHouse中
     const checkExistingRecords = async (records: TraceRecord[]): Promise<TraceRecord[]> => {
       if (records.length === 0) return [];
       
+      // 如果跳过ClickHouse检查或强制插入，则直接返回所有记录
       if (skip_clickhouse_check || force_insert) {
-        logWithTimestamp(`Skipping ClickHouse duplicate check, will process all ${records.length} records`);
+        logWithTimestamp(`已跳过ClickHouse重复检查，准备处理所有 ${records.length} 条记录`);
         return records;
       }
       
+      logWithTimestamp(`正在检查 ${records.length} 条记录是否已存在于ClickHouse中...`);
+      
       try {
-        const recordIds = records.map(record => record._id.toString());
+        // 验证数据库名称
+        if (!clickhouseConfig.clickhouse_database || clickhouseConfig.clickhouse_database === "undefined") {
+          throw new Error("数据库名称未定义或无效，请检查配置");
+        }
         
+        // 提取所有记录的ID
+        const recordIds = records.map(record => record.slugId.toString()); // 使用slugId作为link_id查询
+        logWithTimestamp(`待检查的记录ID: ${recordIds.join(', ')}`);
+        
+        // 构建查询SQL，检查记录是否已存在，确保添加FORMAT JSON来获取正确的JSON格式响应
         const query = `
-          SELECT event_id 
-          FROM shorturl_analytics.events 
-          WHERE event_attributes LIKE '%"mongo_id":"%' 
-          AND event_attributes LIKE ANY ('%${recordIds.join("%' OR '%")}%')
+          SELECT link_id, visitor_id 
+          FROM ${clickhouseConfig.clickhouse_database}.events 
+          WHERE link_id IN ('${recordIds.join("','")}')
           FORMAT JSON
         `;
         
-        const response = await fetch(clickhouseConfig.clickhouse_url, {
+        logWithTimestamp(`执行ClickHouse查询: ${query.replace(/\n\s*/g, ' ')}`);
+        
+        // 发送请求到ClickHouse，添加10秒超时
+        const clickhouseUrl = `${clickhouseConfig.clickhouse_url}`;
+        const response = await fetch(clickhouseUrl, {
           method: "POST",
           headers: {
             "Content-Type": "application/x-www-form-urlencoded",
@@ -199,134 +437,235 @@ export async function main(
         
         if (!response.ok) {
           const errorText = await response.text();
-          throw new Error(`ClickHouse query error: ${response.status} ${errorText}`);
+          throw new Error(`ClickHouse查询错误: ${response.status} ${errorText}`);
         }
         
-        const result = await response.json();
-        const existingIds = new Set(result.data.map((row: ClickHouseRow) => {
-          const matches = row.event_attributes.match(/"mongo_id":"([^"]+)"/);
-          return matches ? matches[1] : null;
-        }).filter(Boolean));
+        // 获取响应文本以便记录
+        const responseText = await response.text();
+        logWithTimestamp(`ClickHouse查询响应: ${responseText.slice(0, 200)}${responseText.length > 200 ? '...' : ''}`);
         
-        return records.filter(record => !existingIds.has(record._id.toString()));
+        if (!responseText.trim()) {
+          logWithTimestamp("ClickHouse返回空响应，假定没有记录存在");
+          return records; // 如果响应为空，假设没有记录
+        }
+        
+        // 解析结果
+        let result;
+        try {
+          result = JSON.parse(responseText);
+        } catch (err) {
+          logWithTimestamp(`ClickHouse响应不是有效的JSON: ${responseText}`);
+          throw new Error(`解析ClickHouse响应失败: ${(err as Error).message}`);
+        }
+        
+        // 确保result有正确的结构
+        if (!result.data) {
+          logWithTimestamp(`ClickHouse响应缺少data字段: ${JSON.stringify(result)}`);
+          return records; // 如果没有data字段，假设没有记录
+        }
+        
+        // 提取已存在的记录ID
+        const existingIds = new Set(result.data.map((row: { link_id: string }) => row.link_id));
+        
+        logWithTimestamp(`检测到 ${existingIds.size} 条记录已存在于ClickHouse中`);
+        if (existingIds.size > 0) {
+          logWithTimestamp(`已存在的记录ID: ${Array.from(existingIds).join(', ')}`);
+        }
+        
+        // 过滤出不存在的记录
+        const newRecords = records.filter(record => !existingIds.has(record.slugId.toString())); // 使用slugId匹配link_id
+        logWithTimestamp(`过滤后剩余 ${newRecords.length} 条新记录需要插入`);
+        
+        return newRecords;
       } catch (err) {
-        logWithTimestamp(`Error checking existing records: ${(err as Error).message}`);
-        return skip_clickhouse_check ? records : [];
+        const error = err as Error;
+        logWithTimestamp(`ClickHouse查询出错: ${error.message}`);
+        if (skip_clickhouse_check) {
+          logWithTimestamp("已启用跳过ClickHouse检查，将继续处理所有记录");
+          return records;
+        } else {
+          throw error; // 如果没有启用跳过检查，则抛出错误
+        }
       }
     };
 
-    // Process records function
+    // 在处理记录前先检查ClickHouse连接
+    const clickhouseConnected = await checkClickHouseConnection();
+    if (!clickhouseConnected && !skip_clickhouse_check) {
+      logWithTimestamp("⚠️ ClickHouse连接测试失败，请启用skip_clickhouse_check=true参数来跳过连接检查");
+      throw new Error("ClickHouse连接失败，无法继续同步");
+    }
+
+    // 处理记录的函数
     const processRecords = async (records: TraceRecord[]) => {
       if (records.length === 0) return 0;
       
-      const newRecords = await checkExistingRecords(records);
+      logWithTimestamp(`开始处理批次数据，共 ${records.length} 条记录...`);
+      
+      // 检查记录是否已存在
+      let newRecords;
+      try {
+        newRecords = await checkExistingRecords(records);
+      } catch (err) {
+        const error = err as Error;
+        logWithTimestamp(`检查记录是否存在时出错: ${error.message}`);
+        if (!skip_clickhouse_check && !force_insert) {
+          throw error;
+        }
+        // 如果跳过检查或强制插入，则使用所有记录
+        logWithTimestamp("将使用所有记录进行处理");
+        newRecords = records;
+      }
+      
       if (newRecords.length === 0) {
-        logWithTimestamp("All records already exist, skipping");
+        logWithTimestamp("所有记录都已存在，跳过处理");
         return 0;
       }
       
-      // Get link information for all records
+      logWithTimestamp(`准备处理 ${newRecords.length} 条新记录...`);
+      
+      // 获取链接信息 - 新增代码
       const slugIds = newRecords.map(record => record.slugId);
+      logWithTimestamp(`正在查询 ${slugIds.length} 条短链接信息...`);
       const shortLinks = await shortCollection.find({
         _id: { $in: slugIds }
       }).toArray();
-
-      // Create a map for quick lookup
-      const shortLinksMap = new Map(shortLinks.map(link => [link._id.toString(), link]));
       
-      // Prepare ClickHouse insert data
+      // 创建映射用于快速查找 - 新增代码
+      const shortLinksMap = new Map(shortLinks.map((link: ShortRecord) => [link._id.toString(), link]));
+      logWithTimestamp(`获取到 ${shortLinks.length} 条短链接信息`);
+      
+      // 准备ClickHouse插入数据
       const clickhouseData = newRecords.map(record => {
-        const shortLink = shortLinksMap.get(record.slugId.toString());
+        const eventTime = new Date(record.createTime);
         
-        // 将毫秒时间戳转换为 DateTime64(3) 格式
-        const formatDateTime = (timestamp: number) => {
-          return new Date(timestamp).toISOString().replace('T', ' ').replace('Z', '');
+        // 获取对应的短链接信息 - 新增代码
+        const shortLink = shortLinksMap.get(record.slugId.toString()) as ShortRecord | undefined;
+        
+        // 提取URL中的UTM参数 - 增加调试日志
+        if (debug_utm && record.url) {
+          logWithTimestamp(`======== UTM参数调试 ========`);
+          logWithTimestamp(`记录ID: ${record._id.toString()}`);
+          logWithTimestamp(`原始URL: ${record.url}`);
+        }
+        
+        const utmParams = extractUtmParams(record.url || "", debug_utm);
+        
+        if (debug_utm) {
+          logWithTimestamp(`提取的UTM参数: ${JSON.stringify(utmParams)}`);
+          logWithTimestamp(`===========================`);
+        }
+        
+        // 保存提取的UTM参数和URL到event_attributes
+        const eventAttributes = {
+          mongo_id: record._id.toString(),
+          url: record.url || "",
+          ...(record.url ? { raw_url: record.url } : {})
         };
         
+        // 转换MongoDB记录为ClickHouse格式，匹配ClickHouse表结构
         return {
-          // Event base information
-          event_id: record._id.toString(),
-          event_time: formatDateTime(record.createTime),
-          event_type: "click",
-          event_attributes: JSON.stringify({
-            original_type: record.type
-          }),
-          
-          // Link information from short collection
+          // UUID将由ClickHouse自动生成 (event_id)
+          event_time: eventTime.toISOString().replace('T', ' ').replace('Z', ''),
+          event_type: record.type === 1 ? "visit" : "custom",
+          event_attributes: JSON.stringify(eventAttributes),
           link_id: record.slugId.toString(),
-          link_slug: shortLink?.slug || "",
+          link_slug: shortLink?.slug || "", 
           link_label: record.label || "",
-          link_title: "",
-          link_original_url: shortLink?.origin || "",
-          link_attributes: JSON.stringify({
-            domain: shortLink?.domain || null
-          }),
-          link_created_at: shortLink?.createTime ? formatDateTime(shortLink.createTime) : formatDateTime(record.createTime),
-          link_expires_at: shortLink?.expiresAt ? formatDateTime(shortLink.expiresAt) : null,
-          link_tags: "[]",  // Empty array as default
-          
-          // User information
-          user_id: shortLink?.user || "",
+          link_title: shortLink?.title || "", 
+          link_original_url: shortLink?.origin || "", 
+          link_attributes: JSON.stringify({ domain: shortLink?.domain || null }), 
+          link_created_at: shortLink?.createTime 
+            ? new Date(shortLink.createTime).toISOString().replace('T', ' ').replace('Z', '')
+            : eventTime.toISOString().replace('T', ' ').replace('Z', ''), 
+          link_expires_at: shortLink?.expiresAt 
+            ? new Date(shortLink.expiresAt).toISOString().replace('T', ' ').replace('Z', '') 
+            : null, 
+          link_tags: shortLink?.tags ? JSON.stringify(shortLink.tags) : "[]", 
+          user_id: shortLink?.user || "", 
           user_name: "",
           user_email: "",
           user_attributes: "{}",
-          
-          // Team information
-          team_id: shortLink?.teamId || "",
+          team_id: shortLink?.teamId || "", 
           team_name: "",
           team_attributes: "{}",
-          
-          // Project information
-          project_id: shortLink?.projectId || "",
+          project_id: shortLink?.projectId || "", 
           project_name: "",
           project_attributes: "{}",
-          
-          // QR code information
           qr_code_id: "",
           qr_code_name: "",
           qr_code_attributes: "{}",
-          
-          // Visitor information
-          visitor_id: "",  // Empty string as default
-          session_id: `${record.slugId.toString()}-${record.createTime}`,
-          ip_address: record.ip || "",
-          country: "",
+          visitor_id: record._id.toString(), // 使用MongoDB ID作为访客ID
+          session_id: record._id.toString() + "-" + record.createTime, // 创建一个唯一会话ID
+          ip_address: record.ip,
+          country: "", // 这些字段在MongoDB中不存在，使用默认值
           city: "",
-          device_type: record.platform || "",
+          device_type: record.platform || "unknown",
           browser: record.browser || "",
           os: record.platformOS || "",
-          user_agent: `${record.browser || ""} ${record.browserVersion || ""}`.trim(),
-          
-          // Source information
+          user_agent: record.browser + " " + record.browserVersion,
           referrer: record.url || "",
-          utm_source: "",
-          utm_medium: "",
-          utm_campaign: "",
-          
-          // Interaction information
+          utm_source: utmParams.utm_source,
+          utm_medium: utmParams.utm_medium,
+          utm_campaign: utmParams.utm_campaign,
+          utm_term: utmParams.utm_term,
+          utm_content: utmParams.utm_content,
           time_spent_sec: 0,
           is_bounce: true,
           is_qr_scan: false,
           conversion_type: "visit",
-          conversion_value: 0
+          conversion_value: 0,
+          req_full_path: record.url || ""
         };
       });
 
-      // Generate ClickHouse insert SQL
-      const rows = clickhouseData.map(row => {
-        // 只需要处理JSON字符串的转义
-        const formattedRow = {
-          ...row,
-          event_attributes: row.event_attributes.replace(/\\/g, '\\\\'),
-          link_attributes: row.link_attributes.replace(/\\/g, '\\\\')
-        };
-        return JSON.stringify(formattedRow);
-      }).join('\n');
+      // 生成ClickHouse插入SQL
+      const insertSQL = `
+        INSERT INTO ${clickhouseConfig.clickhouse_database}.events 
+        (event_time, event_type, event_attributes, link_id, link_slug, link_label, link_title, 
+         link_original_url, link_attributes, link_created_at, link_expires_at, link_tags, 
+         user_id, user_name, user_email, user_attributes, team_id, team_name, team_attributes, 
+         project_id, project_name, project_attributes, qr_code_id, qr_code_name, qr_code_attributes, 
+         visitor_id, session_id, ip_address, country, city, device_type, browser, os, user_agent, 
+         referrer, utm_source, utm_medium, utm_campaign, utm_term, utm_content, time_spent_sec, 
+         is_bounce, is_qr_scan, conversion_type, conversion_value, req_full_path)
+        VALUES ${clickhouseData.map(record => {
+          // 确保所有字符串值都是字符串类型，并安全处理替换
+          const safeReplace = (val: unknown): string => {
+            // 确保值是字符串，如果是null或undefined则使用空字符串
+            const str = val === null || val === undefined ? "" : String(val);
+            // 安全替换单引号
+            return str.replace(/'/g, "''");
+          };
+          
+          return `('${record.event_time}', '${safeReplace(record.event_type)}', '${safeReplace(record.event_attributes)}', 
+            '${record.link_id}', '${safeReplace(record.link_slug)}', '${safeReplace(record.link_label)}', '${safeReplace(record.link_title)}', 
+            '${safeReplace(record.link_original_url)}', '${safeReplace(record.link_attributes)}', '${record.link_created_at}', 
+            ${record.link_expires_at === null ? 'NULL' : `'${record.link_expires_at}'`}, '${safeReplace(record.link_tags)}', 
+            '${safeReplace(record.user_id)}', '${safeReplace(record.user_name)}', '${safeReplace(record.user_email)}', 
+            '${safeReplace(record.user_attributes)}', '${safeReplace(record.team_id)}', '${safeReplace(record.team_name)}', 
+            '${safeReplace(record.team_attributes)}', '${safeReplace(record.project_id)}', '${safeReplace(record.project_name)}', 
+            '${safeReplace(record.project_attributes)}', '${safeReplace(record.qr_code_id)}', '${safeReplace(record.qr_code_name)}', 
+            '${safeReplace(record.qr_code_attributes)}', '${safeReplace(record.visitor_id)}', '${safeReplace(record.session_id)}', 
+            '${safeReplace(record.ip_address)}', '${safeReplace(record.country)}', '${safeReplace(record.city)}', 
+            '${safeReplace(record.device_type)}', '${safeReplace(record.browser)}', '${safeReplace(record.os)}', 
+            '${safeReplace(record.user_agent)}', '${safeReplace(record.referrer)}', '${safeReplace(record.utm_source)}', 
+            '${safeReplace(record.utm_medium)}', '${safeReplace(record.utm_campaign)}', '${safeReplace(record.utm_term)}', 
+            '${safeReplace(record.utm_content)}', ${record.time_spent_sec}, ${record.is_bounce}, ${record.is_qr_scan}, 
+            '${safeReplace(record.conversion_type)}', ${record.conversion_value}, '${safeReplace(record.req_full_path)}')`;
+        }).join(", ")}
+      `;
 
-      const insertSQL = `INSERT INTO shorturl_analytics.events FORMAT JSONEachRow\n${rows}`;
+      if (insertSQL.length === 0) {
+        console.log("没有新记录需要插入");
+        return 0;
+      }
 
+      // 发送请求到ClickHouse，添加20秒超时
+      const clickhouseUrl = `${clickhouseConfig.clickhouse_url}`;
       try {
-        const response = await fetch(clickhouseConfig.clickhouse_url, {
+        logWithTimestamp("发送插入请求到ClickHouse...");
+        const response = await fetch(clickhouseUrl, {
           method: "POST",
           headers: {
             "Content-Type": "application/x-www-form-urlencoded",
@@ -338,35 +677,35 @@ export async function main(
 
         if (!response.ok) {
           const errorText = await response.text();
-          throw new Error(`ClickHouse insert error: ${response.status} ${errorText}`);
+          throw new Error(`ClickHouse插入错误: ${response.status} ${errorText}`);
         }
 
-        logWithTimestamp(`Successfully inserted ${newRecords.length} records to ClickHouse`);
+        logWithTimestamp(`成功插入 ${newRecords.length} 条记录到ClickHouse`);
         return newRecords.length;
       } catch (err) {
-        logWithTimestamp(`Failed to insert data to ClickHouse: ${(err as Error).message}`);
-        throw err;
+        const error = err as Error;
+        logWithTimestamp(`向ClickHouse插入数据失败: ${error.message}`);
+        throw error;
       }
     };
 
-    // Check ClickHouse connection before processing
-    const clickhouseConnected = await checkClickHouseConnection();
-    if (!clickhouseConnected && !skip_clickhouse_check) {
-      throw new Error("ClickHouse connection failed, cannot continue sync");
-    }
-
-    // Process records in batches
+    // 批量处理记录
     let processedRecords = 0;
     let totalBatchRecords = 0;
+    let lastSyncTime = 0;
+    let lastSyncId = "";
 
     for (let page = 0; processedRecords < recordsToProcess; page++) {
+      // 检查超时
       if (checkTimeout()) {
-        logWithTimestamp(`Processed ${processedRecords}/${recordsToProcess} records, stopping due to timeout`);
+        logWithTimestamp(`已处理 ${processedRecords}/${recordsToProcess} 条记录，因超时暂停执行`);
         break;
       }
 
-      logWithTimestamp(`Processing batch ${page+1}, completed ${processedRecords}/${recordsToProcess} records (${Math.round(processedRecords/recordsToProcess*100)}%)`);
+      // 每批次都输出进度
+      logWithTimestamp(`开始处理第 ${page+1} 批次，已完成 ${processedRecords}/${recordsToProcess} 条记录 (${Math.round(processedRecords/recordsToProcess*100)}%)`);
       
+      logWithTimestamp(`正在从MongoDB获取第 ${page+1} 批次数据...`);
       const records = await traceCollection.find(
         query,
         {
@@ -378,32 +717,81 @@ export async function main(
       ).toArray();
 
       if (records.length === 0) {
-        logWithTimestamp("No more records found, sync complete");
+        logWithTimestamp("没有找到更多数据，同步结束");
         break;
       }
+      
+      // 找到数据，开始处理
+      logWithTimestamp(`获取到 ${records.length} 条记录，开始处理...`);
+      // 输出当前批次的部分数据信息
+      if (records.length > 0) {
+        logWithTimestamp(`批次 ${page+1} 第一条记录: ID=${records[0]._id}, 时间=${new Date(records[0].createTime).toISOString()}`);
+        if (records.length > 1) {
+          logWithTimestamp(`批次 ${page+1} 最后一条记录: ID=${records[records.length-1]._id}, 时间=${new Date(records[records.length-1].createTime).toISOString()}`);
+        }
+        
+        // 如果开启了调试，输出一些URL样本
+        if (debug_utm) {
+          const sampleSize = Math.min(5, records.length);
+          logWithTimestamp(`URL样本 (前${sampleSize}条):`);
+          for (let i = 0; i < sampleSize; i++) {
+            if (records[i].url) {
+              logWithTimestamp(`样本 ${i+1}: ${records[i].url}`);
+            }
+          }
+        }
+      }
 
       const batchSize = await processRecords(records);
       processedRecords += records.length;
       totalBatchRecords += batchSize;
 
-      logWithTimestamp(`Batch ${page+1} complete. Processed ${processedRecords}/${recordsToProcess} records, inserted ${totalBatchRecords} (${Math.round(processedRecords/recordsToProcess*100)}%)`);
+      // 更新最后处理的记录时间和ID
+      if (records.length > 0) {
+        const lastRecord = records[records.length - 1];
+        lastSyncTime = Math.max(lastSyncTime, lastRecord.createTime);
+        lastSyncId = lastRecord._id.toString();
+      }
+
+      logWithTimestamp(`第 ${page+1} 批次处理完成。已处理 ${processedRecords}/${recordsToProcess} 条记录，实际插入 ${totalBatchRecords} 条 (${Math.round(processedRecords/recordsToProcess*100)}%)`);
+    }
+
+    // 更新同步状态
+    if (processedRecords > 0 && lastSyncTime > 0) {
+      // 创建新的同步状态
+      const newSyncState: SyncState = {
+        last_sync_time: lastSyncTime,
+        records_synced: (lastSyncState ? lastSyncState.records_synced : 0) + totalBatchRecords,
+        last_sync_id: lastSyncId
+      };
+      
+      try {
+        // 保存同步状态
+        await setVariable(SYNC_STATE_KEY, newSyncState);
+        logWithTimestamp(`同步状态已更新: 最后同步时间 ${new Date(newSyncState.last_sync_time).toISOString()}, 累计同步记录数 ${newSyncState.records_synced}`);
+      } catch (err) {
+        const error = err as Error;
+        logWithTimestamp(`更新同步状态失败: ${error.message}`);
+      }
     }
 
     return {
       success: true,
       records_processed: processedRecords,
       records_synced: totalBatchRecords,
-      message: "Data sync completed"
+      last_sync_time: lastSyncTime > 0 ? new Date(lastSyncTime).toISOString() : null,
+      message: "数据同步完成"
     };
   } catch (err) {
-    console.error("Error during sync:", err);
+    console.error("同步过程中发生错误:", err);
     return {
       success: false,
       error: err instanceof Error ? err.message : String(err),
       stack: err instanceof Error ? err.stack : undefined
     };
   } finally {
+    // 关闭MongoDB连接
     await client.close();
-    console.log("MongoDB connection closed");
+    console.log("MongoDB连接已关闭");
   }
-} 
+}

windmill/sync_shorturl_schema_to_clickhouse.ts

@@ -0,0 +1,527 @@
+// 文件名: sync_shorturl_schema_to_clickhouse.ts
+// 描述: 此脚本用于同步PostgreSQL中的short_url.shorturl表数据到ClickHouse
+// 创建日期: 2023-11-21
+
+import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
+import { getResource, getVariable, setVariable } from "https://deno.land/x/windmill@v1.183.0/mod.ts";
+
+// 同步状态接口
+interface SyncState {
+  last_sync_time: string;   // 上次同步的结束时间
+  records_synced: number;   // 累计同步的记录数
+  last_run: string;         // 上次运行的时间
+}
+
+// 同步状态键名
+const SYNC_STATE_KEY = "f/shorturl_analytics/shorturl_to_clickhouse_state";
+
+// PostgreSQL配置接口
+interface PgConfig {
+  host: string;
+  port: number;
+  user: string;
+  password: string;
+  dbname?: string;
+  [key: string]: unknown;
+}
+
+// ClickHouse配置接口
+interface ChConfig {
+  clickhouse_host: string;
+  clickhouse_port: number;
+  clickhouse_user: string;
+  clickhouse_password: string;
+  clickhouse_url?: string;
+}
+
+// Shorturl数据接口
+interface ShortUrlData {
+  id: string;
+  slug: string;
+  origin: string; // 对应ClickHouse中的original_url
+  title?: string;
+  description?: string;
+  created_at?: string;
+  updated_at?: string;
+  deleted_at?: string;
+  expires_at?: string; // 注意这里已更正为expires_at
+  domain?: string; // 添加domain字段
+}
+
+/**
+ * 同步PostgreSQL short_url.shorturl表数据到ClickHouse
+ */
+export async function main(
+  /** 是否为测试模式（不执行实际更新） */
+  dry_run = false,
+  /** 是否显示详细日志 */
+  verbose = false,
+  /** 是否重置同步状态（从头开始同步） */
+  reset_sync_state = false,
+  /** 如果没有同步状态，往前查询多少小时的数据（默认1小时） */
+  default_hours_back = 1
+) {
+  // 初始化日志函数
+  const log = (message: string, isVerbose = false) => {
+    if (!isVerbose || verbose) {
+      console.log(message);
+    }
+  };
+
+  // 获取同步状态
+  let syncState: SyncState | null = null;
+  if (!reset_sync_state) {
+    try {
+      log("获取同步状态...", true);
+      const rawState = await getVariable(SYNC_STATE_KEY);
+      if (rawState) {
+        if (typeof rawState === "string") {
+          syncState = JSON.parse(rawState);
+        } else {
+          syncState = rawState as SyncState;
+        }
+        log(`找到上次同步状态: 最后同步时间 ${syncState.last_sync_time}, 已同步记录数 ${syncState.records_synced}`, true);
+      }
+    } catch (error) {
+      log(`获取同步状态失败: ${error}, 将使用默认设置`, true);
+    }
+  } else {
+    log("重置同步状态，从头开始同步", true);
+  }
+
+  // 设置时间范围
+  const oneHourAgo = new Date(Date.now() - default_hours_back * 60 * 60 * 1000).toISOString();
+  // 如果有同步状态，使用上次同步时间作为开始时间；否则使用默认时间
+  const start_time = syncState ? syncState.last_sync_time : oneHourAgo;
+  const end_time = new Date().toISOString();
+
+  log(`开始同步shorturl表数据: ${start_time} 至 ${end_time}`);
+
+  let pgPool: Pool | null = null;
+
+  try {
+    // 1. 获取数据库配置
+    log("获取PostgreSQL数据库配置...", true);
+    const pgConfig = await getResource('f/limq/production_supabase') as PgConfig;
+    
+    // 2. 创建PostgreSQL连接池
+    pgPool = new Pool({
+      hostname: pgConfig.host,
+      port: pgConfig.port,
+      user: pgConfig.user,
+      password: pgConfig.password,
+      database: pgConfig.dbname || 'postgres'
+    }, 3);
+    
+    // 3. 获取需要更新的数据
+    const shorturlData = await getShortUrlData(pgPool, start_time, end_time, log);
+    log(`成功获取 ${shorturlData.length} 条shorturl数据`);
+    
+    if (shorturlData.length === 0) {
+      // 更新同步状态，即使没有新数据
+      if (!dry_run) {
+        await updateSyncState(end_time, syncState ? syncState.records_synced : 0, log);
+      }
+      return { success: true, message: "没有找到需要更新的数据", updated: 0 };
+    }
+    
+    // 4. 获取ClickHouse配置
+    const chConfig = await getClickHouseConfig();
+    
+    // 5. 执行更新
+    if (!dry_run) {
+      const shorturlUpdated = await updateClickHouseShortUrl(shorturlData, chConfig, log);
+      
+      // 更新同步状态
+      const totalSynced = (syncState ? syncState.records_synced : 0) + shorturlUpdated;
+      await updateSyncState(end_time, totalSynced, log);
+      
+      return { 
+        success: true, 
+        message: "shorturl表数据同步完成", 
+        shorturl_updated: shorturlUpdated,
+        total_synced: totalSynced,
+        sync_state: {
+          last_sync_time: end_time,
+          records_synced: totalSynced
+        }
+      };
+    } else {
+      log("测试模式: 不执行实际更新");
+      return { 
+        success: true, 
+        dry_run: true, 
+        shorturl_count: shorturlData.length,
+        shorturl_sample: shorturlData.slice(0, 1)
+      };
+    }
+  } catch (error) {
+    const errorMessage = `同步过程中发生错误: ${(error as Error).message}`;
+    log(errorMessage);
+    if ((error as Error).stack) {
+      log(`错误堆栈: ${(error as Error).stack}`, true);
+    }
+    return { success: false, message: errorMessage };
+  } finally {
+    if (pgPool) {
+      await pgPool.end();
+      log("PostgreSQL连接池已关闭", true);
+    }
+  }
+}
+
+/**
+ * 更新同步状态
+ */
+async function updateSyncState(lastSyncTime: string, recordsSynced: number, log: (message: string, isVerbose?: boolean) => void): Promise<void> {
+  try {
+    const newState: SyncState = {
+      last_sync_time: lastSyncTime,
+      records_synced: recordsSynced,
+      last_run: new Date().toISOString()
+    };
+    
+    await setVariable(SYNC_STATE_KEY, newState);
+    log(`同步状态已更新: 最后同步时间 ${lastSyncTime}, 累计同步记录数 ${recordsSynced}`, true);
+  } catch (error) {
+    log(`更新同步状态失败: ${error}`, true);
+    // 继续执行，不中断同步过程
+  }
+}
+
+/**
+ * 从PostgreSQL获取shorturl数据
+ */
+async function getShortUrlData(
+  pgPool: Pool,
+  startTime: string,
+  endTime: string,
+  log: (message: string, isVerbose?: boolean) => void
+): Promise<ShortUrlData[]> {
+  const client = await pgPool.connect();
+  
+  try {
+    log(`获取shorturl数据 (${startTime} 至 ${endTime})`, true);
+    
+    const query = `
+      SELECT 
+        id, 
+        slug, 
+        origin,
+        title,
+        description,
+        domain,
+        created_at,
+        updated_at,
+        deleted_at,
+        expired_at as expires_at
+      FROM 
+        short_url.shorturl
+      WHERE 
+        (created_at >= $1 AND created_at <= $2)
+        OR (updated_at >= $1 AND updated_at <= $2)
+    `;
+    
+    const result = await client.queryObject(query, [startTime, endTime]);
+    return result.rows as ShortUrlData[];
+  } finally {
+    client.release();
+  }
+}
+
+/**
+ * 格式化日期时间为ClickHouse可接受的格式
+ */
+function formatDateTime(dateStr: string | null | undefined): string {
+  if (!dateStr) return 'NULL';
+  
+  try {
+    // 将日期字符串转换为ISO格式
+    const date = new Date(dateStr);
+    if (isNaN(date.getTime())) {
+      return 'NULL';
+    }
+    
+    // 返回ISO格式的日期字符串，ClickHouse可以解析
+    return `parseDateTimeBestEffort('${date.toISOString()}')`;
+  } catch (error) {
+    console.error(`日期格式化错误: ${error}`);
+    return 'NULL';
+  }
+}
+
+/**
+ * 格式化进度显示
+ */
+function formatProgress(current: number, total: number): string {
+  const percent = Math.round((current / total) * 100);
+  const progressBar = '[' + '='.repeat(Math.floor(percent / 5)) + ' '.repeat(20 - Math.floor(percent / 5)) + ']';
+  return `${progressBar} ${percent}% (${current}/${total})`;
+}
+
+/**
+ * 更新ClickHouse中的shorturl表数据
+ */
+async function updateClickHouseShortUrl(
+  shorturls: ShortUrlData[],
+  chConfig: ChConfig,
+  log: (message: string, isVerbose?: boolean) => void
+): Promise<number> {
+  if (shorturls.length === 0) {
+    log('没有找到shorturl数据，跳过shorturl表更新');
+    return 0;
+  }
+  
+  log(`准备更新 ${shorturls.length} 条shorturl数据`);
+  
+  // 检查ClickHouse中是否存在shorturl表
+  const tableExists = await checkClickHouseTable(chConfig, 'shorturl_analytics.shorturl');
+  
+  if (!tableExists) {
+    log('ClickHouse中未找到shorturl表，请先创建表');
+    return 0;
+  }
+  
+  let updatedCount = 0;
+  const startTime = Date.now();
+  
+  // 使用批量插入更高效
+  const batchSize = 50; // 降低批次大小，使查询更稳定
+  for (let i = 0; i < shorturls.length; i += batchSize) {
+    const batch = shorturls.slice(i, i + batchSize);
+    let successCount = 0;
+    
+    // 显示批处理进度信息
+    const batchNumber = Math.floor(i / batchSize) + 1;
+    const totalBatches = Math.ceil(shorturls.length / batchSize);
+    log(`处理批次 ${batchNumber}/${totalBatches}: ${formatProgress(i, shorturls.length)}`);
+    
+    // 对每条记录使用单独的INSERT ... SELECT ... WHERE NOT EXISTS语句
+    for (let j = 0; j < batch.length; j++) {
+      const shorturl = batch[j];
+      // 显示记录处理细节进度
+      const overallProgress = i + j + 1;
+      if (overallProgress % 10 === 0 || overallProgress === shorturls.length) {
+        // 每10条记录或最后一条记录显示一次进度
+        const elapsedSeconds = (Date.now() - startTime) / 1000;
+        const recordsPerSecond = overallProgress / elapsedSeconds;
+        const remainingRecords = shorturls.length - overallProgress;
+        const estimatedSecondsRemaining = remainingRecords / recordsPerSecond;
+        
+        log(`总进度: ${formatProgress(overallProgress, shorturls.length)} - 速率: ${recordsPerSecond.toFixed(1)}条/秒 - 预计剩余时间: ${formatTime(estimatedSecondsRemaining)}`);
+      }
+      
+      try {
+        const insertQuery = `
+          INSERT INTO shorturl_analytics.shorturl
+          SELECT
+            '${escapeString(shorturl.id)}' AS id,
+            '${escapeString(shorturl.id)}' AS external_id,
+            'shorturl' AS type,
+            '${escapeString(shorturl.slug)}' AS slug,
+            '${escapeString(shorturl.origin)}' AS original_url,
+            ${shorturl.title ? `'${escapeString(shorturl.title)}'` : 'NULL'} AS title,
+            ${shorturl.description ? `'${escapeString(shorturl.description)}'` : 'NULL'} AS description,
+            '{}' AS attributes,
+            1 AS schema_version,
+            '' AS creator_id,
+            '' AS creator_email,
+            '' AS creator_name,
+            ${formatDateTime(shorturl.created_at)} AS created_at,
+            ${formatDateTime(shorturl.updated_at)} AS updated_at,
+            ${formatDateTime(shorturl.deleted_at)} AS deleted_at,
+            '[]' AS projects,
+            '[]' AS teams,
+            '[]' AS tags,
+            '[]' AS qr_codes,
+            '[]' AS channels,
+            '[]' AS favorites,
+            ${formatDateTime(shorturl.expires_at)} AS expires_at,
+            0 AS click_count,
+            0 AS unique_visitors,
+            ${shorturl.domain ? `'${escapeString(shorturl.domain)}'` : 'NULL'} AS domain
+          WHERE NOT EXISTS (
+            SELECT 1 FROM shorturl_analytics.shorturl WHERE id = '${escapeString(shorturl.id)}'
+          )
+        `;
+        
+        await executeClickHouseQuery(chConfig, insertQuery);
+        successCount++;
+        log(`成功处理shorturl: ${shorturl.id}`, true);
+      } catch (error) {
+        log(`处理shorturl ${shorturl.id} 失败: ${(error as Error).message}`);
+        
+        // 尝试使用简单插入作为备选方案
+        try {
+          log(`尝试替代方法更新: ${shorturl.id}`, true);
+          
+          // 先检查记录是否存在
+          const checkQuery = `SELECT count() FROM shorturl_analytics.shorturl WHERE id = '${escapeString(shorturl.id)}'`;
+          const existsResult = await executeClickHouseQuery(chConfig, checkQuery);
+          const exists = parseInt(existsResult.trim()) > 0;
+          
+          if (!exists) {
+            const fallbackQuery = `
+              INSERT INTO shorturl_analytics.shorturl (
+                id, external_id, type, slug, original_url, 
+                title, description, attributes, schema_version, 
+                creator_id, creator_email, creator_name, 
+                created_at, updated_at, deleted_at,
+                projects, teams, tags, qr_codes, channels, favorites,
+                expires_at, click_count, unique_visitors, domain
+              ) VALUES (
+                '${escapeString(shorturl.id)}',
+                '${escapeString(shorturl.id)}',
+                'shorturl',
+                '${escapeString(shorturl.slug)}',
+                '${escapeString(shorturl.origin)}',
+                ${shorturl.title ? `'${escapeString(shorturl.title)}'` : 'NULL'},
+                ${shorturl.description ? `'${escapeString(shorturl.description)}'` : 'NULL'},
+                '{}',
+                1,
+                '',
+                '',
+                '',
+                ${formatDateTime(shorturl.created_at)},
+                ${formatDateTime(shorturl.updated_at)},
+                ${formatDateTime(shorturl.deleted_at)},
+                '[]',
+                '[]',
+                '[]',
+                '[]',
+                '[]',
+                '[]',
+                ${formatDateTime(shorturl.expires_at)},
+                0,
+                0,
+                ${shorturl.domain ? `'${escapeString(shorturl.domain)}'` : 'NULL'}
+              )
+            `;
+            
+            await executeClickHouseQuery(chConfig, fallbackQuery);
+            successCount++;
+            log(`备选方式插入成功: ${shorturl.id}`, true);
+          } else {
+            log(`记录已存在，跳过: ${shorturl.id}`, true);
+          }
+        } catch (fallbackError) {
+          log(`备选方式失败 ${shorturl.id}: ${(fallbackError as Error).message}`);
+        }
+      }
+    }
+    
+    updatedCount += successCount;
+    log(`批次 ${batchNumber}/${totalBatches} 完成: ${successCount}/${batch.length} 条成功 (总计: ${updatedCount}/${shorturls.length})`);
+  }
+  
+  const totalTime = (Date.now() - startTime) / 1000;
+  log(`同步完成! 总计处理: ${updatedCount}/${shorturls.length} 条记录, 耗时: ${formatTime(totalTime)}, 平均速率: ${(updatedCount / totalTime).toFixed(1)}条/秒`);
+  
+  return updatedCount;
+}
+
+/**
+ * 获取ClickHouse配置
+ */
+async function getClickHouseConfig(): Promise<ChConfig> {
+  try {
+    const chConfigJson = await getVariable("f/shorturl_analytics/clickhouse");
+    
+    // 确保配置不为空
+    if (!chConfigJson) {
+      throw new Error("未找到ClickHouse配置");
+    }
+    
+    // 解析JSON字符串为对象
+    let chConfig: ChConfig;
+    if (typeof chConfigJson === 'string') {
+      try {
+        chConfig = JSON.parse(chConfigJson);
+      } catch {
+        throw new Error("ClickHouse配置不是有效的JSON");
+      }
+    } else {
+      chConfig = chConfigJson as ChConfig;
+    }
+    
+    // 验证并构建URL
+    if (!chConfig.clickhouse_url && chConfig.clickhouse_host && chConfig.clickhouse_port) {
+      chConfig.clickhouse_url = `http://${chConfig.clickhouse_host}:${chConfig.clickhouse_port}`;
+    }
+    
+    if (!chConfig.clickhouse_url) {
+      throw new Error("ClickHouse配置缺少URL");
+    }
+    
+    return chConfig;
+  } catch (error) {
+    throw new Error(`获取ClickHouse配置失败: ${(error as Error).message}`);
+  }
+}
+
+/**
+ * 检查ClickHouse中是否存在指定表
+ */
+async function checkClickHouseTable(chConfig: ChConfig, tableName: string): Promise<boolean> {
+  try {
+    const query = `EXISTS TABLE ${tableName}`;
+    const result = await executeClickHouseQuery(chConfig, query);
+    return result.trim() === '1';
+  } catch (error) {
+    console.error(`检查表 ${tableName} 失败:`, error);
+    return false;
+  }
+}
+
+/**
+ * 执行ClickHouse查询
+ */
+async function executeClickHouseQuery(chConfig: ChConfig, query: string): Promise<string> {
+  // 确保URL有效
+  if (!chConfig.clickhouse_url) {
+    throw new Error("无效的ClickHouse URL: 未定义");
+  }
+  
+  // 执行HTTP请求
+  try {
+    const response = await fetch(chConfig.clickhouse_url, {
+      method: "POST",
+      headers: {
+        "Content-Type": "application/x-www-form-urlencoded",
+        "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+      },
+      body: query,
+    });
+    
+    if (!response.ok) {
+      const errorText = await response.text();
+      throw new Error(`ClickHouse查询失败 (${response.status}): ${errorText}`);
+    }
+    
+    return await response.text();
+  } catch (error) {
+    throw new Error(`执行ClickHouse查询失败: ${(error as Error).message}`);
+  }
+}
+
+/**
+ * 转义字符串，避免SQL注入
+ */
+function escapeString(str: string): string {
+  if (!str) return '';
+  return str.replace(/'/g, "''");
+}
+
+/**
+ * 格式化时间（秒）为可读格式
+ */
+function formatTime(seconds: number): string {
+  const mins = Math.floor(seconds / 60);
+  const secs = Math.floor(seconds % 60);
+  
+  if (mins === 0) {
+    return `${secs}秒`;
+  } else {
+    return `${mins}分${secs}秒`;
+  }
+} 

windmill/sync_shorturl_to_clickhouse.ts

@@ -0,0 +1,641 @@
+// Windmill script to sync shorturl data from PostgreSQL to ClickHouse
+// 作者: AI Assistant
+// 创建日期: 2023-10-30
+// 描述: 此脚本从PostgreSQL数据库获取所有shorturl类型的资源及其关联数据，并同步到ClickHouse
+
+import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
+import { getResource, getVariable } from "https://deno.land/x/windmill@v1.183.0/mod.ts";
+
+// 资源属性接口
+interface ResourceAttributes {
+  slug?: string;
+  original_url?: string;
+  originalUrl?: string;
+  title?: string;
+  description?: string;
+  expires_at?: string;
+  expiresAt?: string;
+  [key: string]: unknown;
+}
+
+// ClickHouse配置接口
+interface ChConfig {
+  clickhouse_host: string;
+  clickhouse_port: number;
+  clickhouse_user: string;
+  clickhouse_password: string;
+  clickhouse_url: string;
+}
+
+// PostgreSQL配置接口
+interface PgConfig {
+  host: string;
+  port: number;
+  user: string;
+  password: string;
+  dbname?: string;
+  [key: string]: unknown;
+}
+
+// Windmill函数定义
+export async function main(
+  /** PostgreSQL和ClickHouse同步脚本 */
+  params: {
+    /** 同步的资源数量限制，默认500 */
+    limit?: number;
+    /** 是否包含已删除资源 */
+    includeDeleted?: boolean;
+    /** 是否执行实际写入操作 */
+    dryRun?: boolean;
+    /** 开始时间（ISO格式）*/
+    startTime?: string;
+    /** 结束时间（ISO格式）*/
+    endTime?: string;
+  }
+) {
+  // 设置默认参数
+  const limit = params.limit || 500;
+  const includeDeleted = params.includeDeleted || false;
+  const dryRun = params.dryRun || false;
+  const startTime = params.startTime ? new Date(params.startTime) : undefined;
+  const endTime = params.endTime ? new Date(params.endTime) : undefined;
+
+  console.log(`开始同步PostgreSQL shorturl数据到ClickHouse`);
+  console.log(`参数: limit=${limit}, includeDeleted=${includeDeleted}, dryRun=${dryRun}`);
+  if (startTime) console.log(`开始时间: ${startTime.toISOString()}`);
+  if (endTime) console.log(`结束时间: ${endTime.toISOString()}`);
+
+  // 获取数据库配置
+  console.log("获取PostgreSQL数据库配置...");
+  const pgConfig = await getResource('f/limq/postgresql') as PgConfig;
+  console.log(`数据库连接配置: host=${pgConfig.host}, port=${pgConfig.port}, database=${pgConfig.dbname || 'postgres'}, user=${pgConfig.user}`);
+  
+  let pgPool: Pool | null = null;
+  
+  try {
+    console.log("创建PostgreSQL连接池...");
+    
+    pgPool = new Pool({
+      hostname: pgConfig.host,
+      port: pgConfig.port,
+      user: pgConfig.user,
+      password: pgConfig.password,
+      database: pgConfig.dbname || 'postgres'
+    }, 3);
+    
+    console.log("PostgreSQL连接池创建完成，尝试连接...");
+
+    // 测试连接
+    const client = await pgPool.connect();
+    try {
+      console.log("连接成功，执行测试查询...");
+      const testResult = await client.queryObject(`SELECT 1 AS test`);
+      console.log(`测试查询结果: ${JSON.stringify(testResult.rows)}`);
+    } finally {
+      client.release();
+    }
+
+    // 获取所有shorturl类型的资源
+    const shorturls = await fetchShorturlResources(pgPool, {
+      limit,
+      includeDeleted,
+      startTime,
+      endTime,
+    });
+    
+    console.log(`获取到 ${shorturls.length} 个shorturl资源`);
+    
+    if (shorturls.length === 0) {
+      return { synced: 0, message: "没有找到需要同步的shorturl资源" };
+    }
+
+    // 为每个资源获取关联数据
+    const enrichedShorturls = await enrichShorturlData(pgPool, shorturls);
+    console.log(`已丰富 ${enrichedShorturls.length} 个shorturl资源的关联数据`);
+
+    // 转换为ClickHouse格式
+    const clickhouseData = formatForClickhouse(enrichedShorturls);
+    
+    if (!dryRun) {
+      // 写入ClickHouse
+      const inserted = await insertToClickhouse(clickhouseData);
+      console.log(`成功写入 ${inserted} 条记录到ClickHouse`);
+      return { synced: inserted, message: "同步完成" };
+    } else {
+      console.log("Dry run模式 - 不执行实际写入");
+      console.log(`将写入 ${clickhouseData.length} 条记录到ClickHouse`);
+      // 输出示例数据
+      if (clickhouseData.length > 0) {
+        console.log("示例数据:");
+        console.log(JSON.stringify(clickhouseData[0], null, 2));
+      }
+      return { synced: 0, dryRun: true, sampleData: clickhouseData.slice(0, 1) };
+    }
+  } catch (error: unknown) {
+    console.error(`同步过程中发生错误: ${(error as Error).message}`);
+    console.error(`错误类型: ${(error as Error).name}`);
+    if ((error as Error).stack) {
+      console.error(`错误堆栈: ${(error as Error).stack}`);
+    }
+    throw error;
+  } finally {
+    if (pgPool) {
+      await pgPool.end();
+      console.log("PostgreSQL连接池已关闭");
+    }
+  }
+}
+
+// 从PostgreSQL获取所有shorturl资源
+async function fetchShorturlResources(
+  pgPool: Pool,
+  options: {
+    limit: number;
+    includeDeleted: boolean;
+    startTime?: Date;
+    endTime?: Date;
+  }
+) {
+  let query = `
+    SELECT 
+      r.id, 
+      r.external_id, 
+      r.type, 
+      r.attributes, 
+      r.schema_version,
+      r.creator_id,
+      r.created_at,
+      r.updated_at,
+      r.deleted_at,
+      u.email as creator_email,
+      u.first_name as creator_first_name,
+      u.last_name as creator_last_name
+    FROM 
+      limq.resources r
+    LEFT JOIN 
+      limq.users u ON r.creator_id = u.id
+    WHERE 
+      r.type = 'shorturl'
+  `;
+
+  const params = [];
+  let paramCount = 1;
+
+  if (!options.includeDeleted) {
+    query += ` AND r.deleted_at IS NULL`;
+  }
+
+  if (options.startTime) {
+    query += ` AND r.created_at >= $${paramCount}`;
+    params.push(options.startTime);
+    paramCount++;
+  }
+
+  if (options.endTime) {
+    query += ` AND r.created_at <= $${paramCount}`;
+    params.push(options.endTime);
+    paramCount++;
+  }
+
+  query += ` ORDER BY r.created_at DESC LIMIT $${paramCount}`;
+  params.push(options.limit);
+
+  const client = await pgPool.connect();
+  try {
+    const result = await client.queryObject(query, params);
+    
+    // 添加调试日志 - 显示获取的数据样本
+    if (result.rows.length > 0) {
+      console.log(`获取到 ${result.rows.length} 条shorturl记录`);
+      console.log(`第一条记录ID: ${result.rows[0].id}`);
+      console.log(`attributes类型: ${typeof result.rows[0].attributes}`);
+      console.log(`attributes内容示例: ${JSON.stringify(String(result.rows[0].attributes)).substring(0, 100)}...`);
+    }
+    
+    return result.rows;
+  } finally {
+    client.release();
+  }
+}
+
+// 为每个shorturl资源获取关联数据
+async function enrichShorturlData(pgPool: Pool, shorturls: Record<string, unknown>[]) {
+  const client = await pgPool.connect();
+  const enriched = [];
+
+  try {
+    for (const shorturl of shorturls) {
+      // 1. 获取项目关联
+      const projectsResult = await client.queryObject(`
+        SELECT 
+          pr.resource_id, pr.project_id, 
+          p.name as project_name, p.description as project_description,
+          pr.assigned_at
+        FROM 
+          limq.project_resources pr
+        JOIN 
+          limq.projects p ON pr.project_id = p.id
+        WHERE 
+          pr.resource_id = $1
+      `, [shorturl.id]);
+      
+      // 2. 获取团队关联（通过项目）
+      const teamIds = projectsResult.rows.map((p: Record<string, unknown>) => p.project_id);
+      const teamsResult = teamIds.length > 0 ? await client.queryObject(`
+        SELECT 
+          tp.team_id, tp.project_id,
+          t.name as team_name, t.description as team_description
+        FROM 
+          limq.team_projects tp
+        JOIN 
+          limq.teams t ON tp.team_id = t.id
+        WHERE 
+          tp.project_id = ANY($1::uuid[])
+      `, [teamIds]) : { rows: [] };
+      
+      // 3. 获取标签关联
+      const tagsResult = await client.queryObject(`
+        SELECT 
+          rt.resource_id, rt.tag_id, rt.created_at,
+          t.name as tag_name, t.type as tag_type
+        FROM 
+          limq.resource_tags rt
+        JOIN 
+          limq.tags t ON rt.tag_id = t.id
+        WHERE 
+          rt.resource_id = $1
+      `, [shorturl.id]);
+      
+      // 4. 获取QR码关联
+      const qrCodesResult = await client.queryObject(`
+        SELECT 
+          id as qr_id, scan_count, url, template_name, created_at
+        FROM 
+          limq.qr_code
+        WHERE 
+          resource_id = $1
+      `, [shorturl.id]);
+      
+      // 5. 获取渠道关联
+      const channelsResult = await client.queryObject(`
+        SELECT 
+          id as channel_id, name as channel_name, path as channel_path, 
+          "isUserCreated" as is_user_created
+        FROM 
+          limq.channel
+        WHERE 
+          "shortUrlId" = $1
+      `, [shorturl.id]);
+      
+      // 6. 获取收藏关联
+      const favoritesResult = await client.queryObject(`
+        SELECT 
+          f.id as favorite_id, f.user_id, f.created_at,
+          u.first_name, u.last_name
+        FROM 
+          limq.favorite f
+        JOIN 
+          limq.users u ON f.user_id = u.id
+        WHERE 
+          f.favoritable_id = $1 AND f.favoritable_type = 'resource'
+      `, [shorturl.id]);
+
+      // 调试日志
+      console.log(`\n处理资源ID: ${shorturl.id}`);
+      console.log(`attributes类型: ${typeof shorturl.attributes}`);
+      
+      // 改进的attributes解析逻辑
+      let attributes: ResourceAttributes = {};
+      try {
+        if (typeof shorturl.attributes === 'string') {
+          // 如果是字符串，尝试解析为JSON
+          console.log(`尝试解析attributes字符串，长度: ${shorturl.attributes.length}`);
+          attributes = JSON.parse(shorturl.attributes);
+        } else if (typeof shorturl.attributes === 'object' && shorturl.attributes !== null) {
+          // 如果已经是对象，直接使用
+          console.log('attributes已经是对象类型');
+          attributes = shorturl.attributes as ResourceAttributes;
+        } else {
+          console.log(`无效的attributes类型: ${typeof shorturl.attributes}`);
+          attributes = {};
+        }
+      } catch (err) {
+        const error = err as Error;
+        console.warn(`无法解析资源 ${shorturl.id} 的attributes JSON:`, error.message);
+        // 尝试进行更多原始数据分析
+        if (typeof shorturl.attributes === 'string') {
+          console.log(`原始字符串前100字符: ${shorturl.attributes.substring(0, 100)}`);
+        }
+        attributes = {};
+      }
+      
+      // 尝试从QR码获取数据
+      let slugFromQr = "";
+      const urlFromQr = "";
+      
+      if (qrCodesResult.rows.length > 0 && qrCodesResult.rows[0].url) {
+        const qrUrl = qrCodesResult.rows[0].url as string;
+        console.log(`找到QR码URL: ${qrUrl}`);
+        
+        try {
+          const urlParts = qrUrl.split('/');
+          slugFromQr = urlParts[urlParts.length - 1];
+          console.log(`从QR码URL提取的slug: ${slugFromQr}`);
+        } catch (err) {
+          const error = err as Error;
+          console.log('无法从QR码URL提取slug:', error.message);
+        }
+      }
+
+      // 日志输出实际字段值
+      console.log(`提取字段 - name: ${attributes.name || 'N/A'}, slug: ${attributes.slug || 'N/A'}`);
+      console.log(`提取字段 - originalUrl: ${attributes.originalUrl || 'N/A'}, original_url: ${attributes.original_url || 'N/A'}`);
+      
+      // 整合所有数据
+      const slug = attributes.slug || attributes.name || slugFromQr || "";
+      const originalUrl = attributes.originalUrl || attributes.original_url || urlFromQr || "";
+      
+      console.log(`最终使用的slug: ${slug}`);
+      console.log(`最终使用的originalUrl: ${originalUrl}`);
+      
+      enriched.push({
+        ...shorturl,
+        attributes,
+        projects: projectsResult.rows,
+        teams: teamsResult.rows,
+        tags: tagsResult.rows,
+        qrCodes: qrCodesResult.rows,
+        channels: channelsResult.rows,
+        favorites: favoritesResult.rows,
+        // 从attributes中提取特定字段 - 使用改进的顺序和QR码备选
+        slug,
+        originalUrl,
+        title: attributes.title || "",
+        description: attributes.description || "",
+        expiresAt: attributes.expires_at || attributes.expiresAt || null
+      });
+    }
+  } finally {
+    client.release();
+  }
+
+  return enriched;
+}
+
+// 将PostgreSQL数据格式化为ClickHouse格式
+function formatForClickhouse(shorturls: Record<string, unknown>[]) {
+  // 将日期格式化为ClickHouse兼容的DateTime64(3)格式
+  const formatDateTime = (date: Date | string | number | null | undefined): string | null => {
+    if (!date) return null;
+    // 转换为Date对象
+    const dateObj = date instanceof Date ? date : new Date(date);
+    // 返回格式化的字符串: YYYY-MM-DD HH:MM:SS.SSS
+    return dateObj.toISOString().replace('T', ' ').replace('Z', '');
+  };
+
+  console.log(`\n准备格式化 ${shorturls.length} 条记录为ClickHouse格式`);
+  
+  return shorturls.map(shorturl => {
+    // 调试日志：输出关键字段
+    console.log(`处理资源: ${shorturl.id}`);
+    console.log(`slug: ${shorturl.slug || 'EMPTY'}`);
+    console.log(`originalUrl: ${shorturl.originalUrl || 'EMPTY'}`);
+    
+    // 记录attributes状态
+    const attributesStr = JSON.stringify(shorturl.attributes || {});
+    const attributesPrev = attributesStr.length > 100 ? 
+                         attributesStr.substring(0, 100) + '...' : 
+                         attributesStr;
+    console.log(`attributes: ${attributesPrev}`);
+    
+    const creatorName = [shorturl.creator_first_name, shorturl.creator_last_name]
+      .filter(Boolean)
+      .join(" ");
+    
+    // 格式化项目数据为JSON数组
+    const projects = JSON.stringify((shorturl.projects as Record<string, unknown>[]).map((p) => ({
+      project_id: p.project_id,
+      project_name: p.project_name,
+      project_description: p.project_description,
+      assigned_at: p.assigned_at
+    })));
+    
+    // 格式化团队数据为JSON数组
+    const teams = JSON.stringify((shorturl.teams as Record<string, unknown>[]).map((t) => ({
+      team_id: t.team_id,
+      team_name: t.team_name,
+      team_description: t.team_description,
+      via_project_id: t.project_id
+    })));
+    
+    // 格式化标签数据为JSON数组
+    const tags = JSON.stringify((shorturl.tags as Record<string, unknown>[]).map((t) => ({
+      tag_id: t.tag_id,
+      tag_name: t.tag_name,
+      tag_type: t.tag_type,
+      created_at: t.created_at
+    })));
+    
+    // 格式化QR码数据为JSON数组
+    const qrCodes = JSON.stringify((shorturl.qrCodes as Record<string, unknown>[]).map((q) => ({
+      qr_id: q.qr_id,
+      scan_count: q.scan_count,
+      url: q.url,
+      template_name: q.template_name,
+      created_at: q.created_at
+    })));
+    
+    // 格式化渠道数据为JSON数组
+    const channels = JSON.stringify((shorturl.channels as Record<string, unknown>[]).map((c) => ({
+      channel_id: c.channel_id,
+      channel_name: c.channel_name,
+      channel_path: c.channel_path,
+      is_user_created: c.is_user_created
+    })));
+    
+    // 格式化收藏数据为JSON数组
+    const favorites = JSON.stringify((shorturl.favorites as Record<string, unknown>[]).map((f) => ({
+      favorite_id: f.favorite_id,
+      user_id: f.user_id,
+      user_name: `${f.first_name || ""} ${f.last_name || ""}`.trim(),
+      created_at: f.created_at
+    })));
+
+    // 统计信息（可通过events表聚合或在其他地方设置）
+    const clickCount = (shorturl.attributes as ResourceAttributes).click_count as number || 0;
+    const uniqueVisitors = 0;
+
+    // 返回ClickHouse格式数据
+    return {
+      id: shorturl.id,
+      external_id: shorturl.external_id || "",
+      type: shorturl.type,
+      slug: shorturl.slug || "",
+      original_url: shorturl.originalUrl || "",
+      title: shorturl.title || "",
+      description: shorturl.description || "",
+      attributes: JSON.stringify(shorturl.attributes || {}),
+      schema_version: shorturl.schema_version || 1,
+      creator_id: shorturl.creator_id || "",
+      creator_email: shorturl.creator_email || "",
+      creator_name: creatorName,
+      created_at: formatDateTime(shorturl.created_at as Date),
+      updated_at: formatDateTime(shorturl.updated_at as Date),
+      deleted_at: formatDateTime(shorturl.deleted_at as Date | null),
+      projects,
+      teams,
+      tags,
+      qr_codes: qrCodes,
+      channels,
+      favorites,
+      expires_at: formatDateTime(shorturl.expiresAt as Date | null),
+      click_count: clickCount,
+      unique_visitors: uniqueVisitors
+    };
+  });
+}
+
+// 获取ClickHouse配置
+async function getClickHouseConfig(): Promise<ChConfig> {
+  try {
+    // 使用getVariable而不是getResource获取ClickHouse配置
+    const chConfigJson = await getVariable("f/shorturl_analytics/clickhouse");
+    console.log("原始ClickHouse配置:", typeof chConfigJson);
+    
+    // 确保配置不为空
+    if (!chConfigJson) {
+      throw new Error("未找到ClickHouse配置");
+    }
+    
+    // 解析JSON字符串为对象
+    let chConfig: ChConfig;
+    if (typeof chConfigJson === 'string') {
+      try {
+        chConfig = JSON.parse(chConfigJson);
+      } catch (parseError) {
+        console.error("解析JSON失败:", parseError);
+        throw new Error("ClickHouse配置不是有效的JSON");
+      }
+    } else {
+      chConfig = chConfigJson as ChConfig;
+    }
+    
+    // 验证配置
+    if (!chConfig.clickhouse_url && chConfig.clickhouse_host && chConfig.clickhouse_port) {
+      chConfig.clickhouse_url = `http://${chConfig.clickhouse_host}:${chConfig.clickhouse_port}`;
+      console.log(`已构建ClickHouse URL: ${chConfig.clickhouse_url}`);
+    }
+    
+    if (!chConfig.clickhouse_url) {
+      throw new Error("ClickHouse配置缺少URL");
+    }
+    
+    return chConfig;
+  } catch (error) {
+    console.error("获取ClickHouse配置失败:", error);
+    throw error;
+  }
+}
+
+// 写入数据到ClickHouse
+async function insertToClickhouse(data: Record<string, unknown>[]) {
+  if (data.length === 0) return 0;
+
+  // 获取ClickHouse连接信息
+  const chConfig = await getClickHouseConfig();
+  
+  // 确保URL有效
+  if (!chConfig.clickhouse_url) {
+    throw new Error("无效的ClickHouse URL: 未定义");
+  }
+  
+  console.log(`准备写入数据到ClickHouse: ${chConfig.clickhouse_url}`);
+  
+  // 构建INSERT查询
+  const columns = Object.keys(data[0]).join(", ");
+  
+  // 收集所有记录的ID
+  const recordIds = data.map(record => record.id as string);
+  console.log(`需要处理的记录数: ${recordIds.length}`);
+  
+  // 先删除可能存在的重复记录
+  try {
+    console.log(`删除可能存在的重复记录...`);
+    
+    // 按批次处理删除，避免请求过大
+    const deleteBatchSize = 100;
+    for (let i = 0; i < recordIds.length; i += deleteBatchSize) {
+      const idBatch = recordIds.slice(i, i + deleteBatchSize);
+      const formattedIds = idBatch.map(id => `'${id}'`).join(', ');
+      
+      const deleteQuery = `
+        ALTER TABLE shorturl_analytics.shorturl
+        DELETE WHERE id IN (${formattedIds})
+      `;
+      
+      const response = await fetch(chConfig.clickhouse_url, {
+        method: "POST",
+        headers: {
+          "Content-Type": "application/x-www-form-urlencoded",
+          "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+        },
+        body: deleteQuery,
+      });
+      
+      if (!response.ok) {
+        const errorText = await response.text();
+        console.warn(`删除记录时出错 (批次 ${i/deleteBatchSize + 1}): ${errorText}`);
+        // 继续执行，不中断流程
+      } else {
+        console.log(`成功删除批次 ${i/deleteBatchSize + 1}/${Math.ceil(recordIds.length/deleteBatchSize)}的潜在重复记录`);
+      }
+    }
+  } catch (error) {
+    console.warn(`删除重复记录时出错: ${(error as Error).message}`);
+    // 继续执行，不因为删除失败而中断整个过程
+  }
+
+  const query = `
+    INSERT INTO shorturl_analytics.shorturl (${columns})
+    FORMAT JSONEachRow
+  `;
+
+  // 批量插入
+  let inserted = 0;
+  const batchSize = 100;
+  
+  for (let i = 0; i < data.length; i += batchSize) {
+    const batch = data.slice(i, i + batchSize);
+    
+    // 使用JSONEachRow格式
+    const rows = batch.map(row => JSON.stringify(row)).join('\n');
+    
+    // 使用HTTP接口执行查询
+    try {
+      console.log(`正在发送请求到: ${chConfig.clickhouse_url}`);
+      console.log(`认证信息: ${chConfig.clickhouse_user}:***`);
+      
+      const response = await fetch(chConfig.clickhouse_url, {
+        method: "POST",
+        headers: {
+          "Content-Type": "application/x-www-form-urlencoded",
+          "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+        },
+        body: `${query}\n${rows}`,
+      });
+      
+      if (!response.ok) {
+        const errorText = await response.text();
+        throw new Error(`ClickHouse插入失败: ${errorText}`);
+      }
+      
+      inserted += batch.length;
+      console.log(`已插入 ${inserted}/${data.length} 条记录`);
+    } catch (error) {
+      console.error(`请求ClickHouse时出错:`, error);
+      throw error;
+    }
+  }
+  
+  return inserted;
+} 

windmill/sync_shorturl_to_clickhouse_intime.ts

@@ -0,0 +1,660 @@
+// 文件名: sync_resource_relations.ts
+// 描述: 此脚本用于同步PostgreSQL中资源关联数据到ClickHouse
+// 作者: AI Assistant
+// 创建日期: 2023-10-31
+
+import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
+import { getResource, getVariable } from "https://deno.land/x/windmill@v1.183.0/mod.ts";
+
+// PostgreSQL配置接口
+interface PgConfig {
+  host: string;
+  port: number;
+  user: string;
+  password: string;
+  dbname?: string;
+  [key: string]: unknown;
+}
+
+// ClickHouse配置接口
+interface ChConfig {
+  clickhouse_host: string;
+  clickhouse_port: number;
+  clickhouse_user: string;
+  clickhouse_password: string;
+  clickhouse_url?: string;
+}
+
+// 资源相关接口定义
+interface TeamData {
+  team_id: string;
+  team_name: string;
+  team_description?: string;
+  project_id?: string;
+}
+
+interface ProjectData {
+  project_id: string;
+  project_name: string;
+  project_description?: string;
+  assigned_at?: string;
+  resource_id?: string;
+}
+
+interface TagData {
+  tag_id: string;
+  tag_name: string;
+  tag_type?: string;
+  created_at?: string;
+  resource_id?: string;
+}
+
+interface FavoriteData {
+  favorite_id: string;
+  user_id: string;
+  first_name?: string;
+  last_name?: string;
+  email?: string;
+  created_at?: string;
+}
+
+// 资源关联数据接口
+interface ResourceRelations {
+  resource_id: string;
+  teams?: TeamData[];
+  projects?: ProjectData[];
+  tags?: TagData[];
+  favorites?: FavoriteData[];
+  external_id?: string;
+  type?: string;
+  attributes?: Record<string, unknown>;
+}
+
+/**
+ * 同步PostgreSQL资源关联数据到ClickHouse
+ */
+export async function main(
+  params: {
+    /** 要同步的资源ID列表 */
+    resource_ids: string[];
+    /** 是否同步teams数据 */
+    sync_teams?: boolean;
+    /** 是否同步projects数据 */
+    sync_projects?: boolean;
+    /** 是否同步tags数据 */
+    sync_tags?: boolean;
+    /** 是否同步favorites数据 */
+    sync_favorites?: boolean;
+    /** 是否为测试模式（不执行实际更新） */
+    dry_run?: boolean;
+    /** 是否显示详细日志 */
+    verbose?: boolean;
+  }
+) {
+  // 设置默认参数
+  const resource_ids = params.resource_ids || [];
+  const sync_teams = params.sync_teams !== false;
+  const sync_projects = params.sync_projects !== false;
+  const sync_tags = params.sync_tags !== false;
+  const sync_favorites = params.sync_favorites !== false;
+  const dry_run = params.dry_run || false;
+  const verbose = params.verbose || false;
+
+  if (resource_ids.length === 0) {
+    return { success: false, message: "至少需要提供一个资源ID" };
+  }
+
+  // 初始化日志函数
+  const log = (message: string, isVerbose = false) => {
+    if (!isVerbose || verbose) {
+      console.log(message);
+    }
+  };
+
+  log(`开始同步资源关联数据: ${resource_ids.join(", ")}`);
+  log(`同步选项: teams=${sync_teams}, projects=${sync_projects}, tags=${sync_tags}, favorites=${sync_favorites}`, true);
+
+  let pgPool: Pool | null = null;
+
+  try {
+    // 1. 获取数据库配置
+    log("获取PostgreSQL数据库配置...", true);
+    const pgConfig = await getResource('f/limq/postgresql') as PgConfig;
+    
+    // 2. 创建PostgreSQL连接池
+    pgPool = new Pool({
+      hostname: pgConfig.host,
+      port: pgConfig.port,
+      user: pgConfig.user,
+      password: pgConfig.password,
+      database: pgConfig.dbname || 'postgres'
+    }, 3);
+    
+    // 3. 获取需要更新的资源完整数据
+    const resourcesData = await getResourcesWithRelations(pgPool, resource_ids, {
+      sync_teams,
+      sync_projects,
+      sync_tags,
+      sync_favorites
+    }, log);
+    
+    log(`成功获取 ${resourcesData.length} 个资源的关联数据`);
+    
+    if (resourcesData.length === 0) {
+      return { success: true, message: "没有找到需要更新的资源数据", updated: 0 };
+    }
+    
+    // 4. 获取ClickHouse配置
+    const chConfig = await getClickHouseConfig();
+    
+    // 5. 对每个资源执行更新
+    if (!dry_run) {
+      // 5a. 更新shorturl表数据
+      const shorturlUpdated = await updateClickHouseShorturl(resourcesData, chConfig, log);
+      
+      // 5b. 更新events表数据
+      const eventsUpdated = await updateClickHouseEvents(resourcesData, chConfig, log);
+      
+      return { 
+        success: true, 
+        message: "资源关联数据同步完成", 
+        shorturl_updated: shorturlUpdated,
+        events_updated: eventsUpdated,
+        total_updated: shorturlUpdated + eventsUpdated 
+      };
+    } else {
+      log("测试模式: 不执行实际更新");
+      if (resourcesData.length > 0) {
+        log("示例数据:");
+        log(JSON.stringify(resourcesData[0], null, 2));
+      }
+      return { success: true, dry_run: true, resources: resourcesData };
+    }
+  } catch (error) {
+    const errorMessage = `同步过程中发生错误: ${(error as Error).message}`;
+    log(errorMessage);
+    if ((error as Error).stack) {
+      log(`错误堆栈: ${(error as Error).stack}`, true);
+    }
+    return { success: false, message: errorMessage };
+  } finally {
+    if (pgPool) {
+      await pgPool.end();
+      log("PostgreSQL连接池已关闭", true);
+    }
+  }
+}
+
+/**
+ * 从PostgreSQL获取资源及其关联数据
+ */
+async function getResourcesWithRelations(
+  pgPool: Pool,
+  resourceIds: string[],
+  options: {
+    sync_teams: boolean;
+    sync_projects: boolean;
+    sync_tags: boolean;
+    sync_favorites: boolean;
+  },
+  log: (message: string, isVerbose?: boolean) => void
+): Promise<ResourceRelations[]> {
+  const client = await pgPool.connect();
+  
+  try {
+    // 准备资源IDs参数
+    const resourceIdsParam = resourceIds.map(id => `'${id}'`).join(',');
+    
+    // 1. 获取基本资源信息
+    log(`获取资源基本信息: ${resourceIdsParam}`, true);
+    const resourcesQuery = `
+      SELECT 
+        r.id, 
+        r.external_id, 
+        r.type, 
+        r.attributes, 
+        r.schema_version,
+        r.created_at,
+        r.updated_at
+      FROM 
+        limq.resources r
+      WHERE 
+        r.id IN (${resourceIdsParam})
+        AND r.deleted_at IS NULL
+    `;
+    
+    const resourcesResult = await client.queryObject(resourcesQuery);
+    
+    if (resourcesResult.rows.length === 0) {
+      log(`未找到有效的资源数据`, true);
+      return [];
+    }
+    
+    // 处理每个资源
+    const enrichedResources: ResourceRelations[] = [];
+    
+    for (const resource of resourcesResult.rows) {
+      const resourceId = resource.id as string;
+      log(`处理资源ID: ${resourceId}`, true);
+      
+      // 初始化关联数据对象
+      const relationData: ResourceRelations = {
+        resource_id: resourceId,
+        external_id: resource.external_id as string,
+        type: resource.type as string,
+        attributes: parseJsonField(resource.attributes)
+      };
+      
+      // 2. 获取项目关联
+      if (options.sync_projects) {
+        const projectsQuery = `
+          SELECT 
+            pr.resource_id, pr.project_id, 
+            p.name as project_name, p.description as project_description,
+            pr.assigned_at
+          FROM 
+            limq.project_resources pr
+          JOIN 
+            limq.projects p ON pr.project_id = p.id
+          WHERE 
+            pr.resource_id = $1
+            AND p.deleted_at IS NULL
+        `;
+        
+        const projectsResult = await client.queryObject(projectsQuery, [resourceId]);
+        relationData.projects = projectsResult.rows as ProjectData[];
+        log(`找到 ${projectsResult.rows.length} 个关联项目`, true);
+      }
+      
+      // 3. 获取标签关联
+      if (options.sync_tags) {
+        const tagsQuery = `
+          SELECT 
+            rt.resource_id, rt.tag_id, rt.created_at,
+            t.name as tag_name, t.type as tag_type
+          FROM 
+            limq.resource_tags rt
+          JOIN 
+            limq.tags t ON rt.tag_id = t.id
+          WHERE 
+            rt.resource_id = $1
+            AND t.deleted_at IS NULL
+        `;
+        
+        const tagsResult = await client.queryObject(tagsQuery, [resourceId]);
+        relationData.tags = tagsResult.rows as TagData[];
+        log(`找到 ${tagsResult.rows.length} 个关联标签`, true);
+      }
+      
+      // 4. 获取团队关联（通过项目）
+      if (options.sync_teams && relationData.projects && relationData.projects.length > 0) {
+        const projectIds = relationData.projects.map((p: ProjectData) => p.project_id);
+        
+        if (projectIds.length > 0) {
+          const teamsQuery = `
+            SELECT 
+              tp.team_id, tp.project_id,
+              t.name as team_name, t.description as team_description
+            FROM 
+              limq.team_projects tp
+            JOIN 
+              limq.teams t ON tp.team_id = t.id
+            WHERE 
+              tp.project_id = ANY($1::uuid[])
+              AND t.deleted_at IS NULL
+          `;
+          
+          const teamsResult = await client.queryObject(teamsQuery, [projectIds]);
+          relationData.teams = teamsResult.rows as TeamData[];
+          log(`找到 ${teamsResult.rows.length} 个关联团队`, true);
+        }
+      }
+      
+      // 5. 获取收藏关联
+      if (options.sync_favorites) {
+        const favoritesQuery = `
+          SELECT 
+            f.id as favorite_id, f.user_id, f.created_at,
+            u.first_name, u.last_name, u.email
+          FROM 
+            limq.favorite f
+          JOIN 
+            limq.users u ON f.user_id = u.id
+          WHERE 
+            f.favoritable_id = $1 
+            AND f.favoritable_type = 'resource'
+            AND f.deleted_at IS NULL
+        `;
+        
+        const favoritesResult = await client.queryObject(favoritesQuery, [resourceId]);
+        relationData.favorites = favoritesResult.rows as FavoriteData[];
+        log(`找到 ${favoritesResult.rows.length} 个收藏记录`, true);
+      }
+      
+      // 添加到结果集
+      enrichedResources.push(relationData);
+    }
+    
+    return enrichedResources;
+  } finally {
+    client.release();
+  }
+}
+
+/**
+ * 更新ClickHouse中的shorturl表数据
+ */
+async function updateClickHouseShorturl(
+  resources: ResourceRelations[],
+  chConfig: ChConfig,
+  log: (message: string, isVerbose?: boolean) => void
+): Promise<number> {
+  // 只处理类型为shorturl的资源
+  const shorturls = resources.filter(r => r.type === 'shorturl');
+  
+  if (shorturls.length === 0) {
+    log('没有找到shorturl类型的资源，跳过shorturl表更新');
+    return 0;
+  }
+  
+  log(`准备更新 ${shorturls.length} 个shorturl资源`);
+  
+  let updatedCount = 0;
+  
+  // 检查ClickHouse中是否存在shorturl表
+  const tableExists = await checkClickHouseTable(chConfig, 'shorturl_analytics.shorturl');
+  
+  if (!tableExists) {
+    log('ClickHouse中未找到shorturl表，请先创建表');
+    return 0;
+  }
+  
+  // 对每个资源执行更新
+  for (const shorturl of shorturls) {
+    try {
+      // 格式化团队数据
+      const teams = JSON.stringify(shorturl.teams || []);
+      
+      // 格式化项目数据
+      const projects = JSON.stringify(shorturl.projects || []);
+      
+      // 格式化标签数据
+      const tags = JSON.stringify((shorturl.tags || []).map((t: TagData) => ({
+        tag_id: t.tag_id,
+        tag_name: t.tag_name,
+        tag_type: t.tag_type,
+        created_at: t.created_at
+      })));
+      
+      // 格式化收藏数据
+      const favorites = JSON.stringify((shorturl.favorites || []).map((f: FavoriteData) => ({
+        favorite_id: f.favorite_id,
+        user_id: f.user_id,
+        user_name: `${f.first_name || ""} ${f.last_name || ""}`.trim(),
+        created_at: f.created_at
+      })));
+      
+      // 尝试更新ClickHouse数据
+      const updateQuery = `
+        ALTER TABLE shorturl_analytics.shorturl
+        UPDATE 
+          teams = '${escapeString(teams)}',
+          projects = '${escapeString(projects)}',
+          tags = '${escapeString(tags)}',
+          favorites = '${escapeString(favorites)}'
+        WHERE id = '${shorturl.resource_id}'
+      `;
+      
+      await executeClickHouseQuery(chConfig, updateQuery);
+      log(`更新shorturl完成: ${shorturl.resource_id}`, true);
+      updatedCount++;
+      
+    } catch (error) {
+      log(`更新shorturl ${shorturl.resource_id} 失败: ${(error as Error).message}`);
+    }
+  }
+  
+  return updatedCount;
+}
+
+/**
+ * 更新ClickHouse中的events表数据
+ */
+async function updateClickHouseEvents(
+  resources: ResourceRelations[],
+  chConfig: ChConfig,
+  log: (message: string, isVerbose?: boolean) => void
+): Promise<number> {
+  // 过滤出有external_id的资源
+  const resourcesWithExternalId = resources.filter(r => r.external_id && r.external_id.trim() !== '');
+  
+  if (resourcesWithExternalId.length === 0) {
+    log('没有找到具有external_id的资源，跳过events表更新');
+    return 0;
+  }
+  
+  log(`准备更新events表中与 ${resourcesWithExternalId.length} 个外部ID相关的记录`);
+  
+  // 检查ClickHouse中是否存在events表
+  const tableExists = await checkClickHouseTable(chConfig, 'shorturl_analytics.events');
+  
+  if (!tableExists) {
+    log('ClickHouse中未找到events表，请先创建表');
+    return 0;
+  }
+  
+  // 提取所有的external_id
+  const externalIds = resourcesWithExternalId.map(r => r.external_id).filter(Boolean) as string[];
+  
+  // 构建资源数据映射（使用external_id作为键）
+  const resourceMapByExternalId = resourcesWithExternalId.reduce((map, resource) => {
+    if (resource.external_id) {
+      map[resource.external_id] = resource;
+    }
+    return map;
+  }, {} as Record<string, ResourceRelations>);
+  
+  // 获取ClickHouse中相关资源的事件记录数量
+  let updatedCount = 0;
+  
+  try {
+    // 格式化外部ID列表
+    const formattedExternalIds = externalIds.map(id => `'${id}'`).join(', ');
+    
+    // 先查询是否有相关事件
+    const countQuery = `
+      SELECT COUNT(*) as count
+      FROM shorturl_analytics.events 
+      WHERE event_id IN (${formattedExternalIds})
+    `;
+    
+    const countResult = await executeClickHouseQuery(chConfig, countQuery);
+    const eventCount = parseInt(countResult.trim(), 10);
+    
+    if (eventCount === 0) {
+      // 尝试另一种查询方式
+      const alternateCountQuery = `
+        SELECT COUNT(*) as count
+        FROM shorturl_analytics.events 
+        WHERE link_id IN (${formattedExternalIds})
+      `;
+      
+      const alternateCountResult = await executeClickHouseQuery(chConfig, alternateCountQuery);
+      const alternateEventCount = parseInt(alternateCountResult.trim(), 10);
+      
+      if (alternateEventCount === 0) {
+        log('没有找到相关事件记录，跳过events表更新');
+        log(`已尝试的匹配字段: event_id，link_id`, true);
+        return 0;
+      } else {
+        log(`找到 ${alternateEventCount} 条以link_id匹配的事件记录需要更新`);
+      }
+    } else {
+      log(`找到 ${eventCount} 条以event_id匹配的事件记录需要更新`);
+    }
+    
+    // 批量更新每个资源相关的事件记录
+    for (const externalId of externalIds) {
+      const resource = resourceMapByExternalId[externalId];
+      
+      if (!resource) continue;
+      
+      // 获取关联数据
+      const tags = resource.tags ? JSON.stringify(resource.tags) : null;
+      
+      if (tags) {
+        // 尝试通过event_id更新事件标签
+        const updateTagsQueryByEventId = `
+          ALTER TABLE shorturl_analytics.events
+          UPDATE link_tags = '${escapeString(tags)}'
+          WHERE event_id = '${externalId}'
+        `;
+        
+        await executeClickHouseQuery(chConfig, updateTagsQueryByEventId);
+        log(`尝试通过event_id更新事件标签: ${externalId}`, true);
+        
+        // 尝试通过link_id更新事件标签
+        const updateTagsQueryByLinkId = `
+          ALTER TABLE shorturl_analytics.events
+          UPDATE link_tags = '${escapeString(tags)}'
+          WHERE link_id = '${externalId}'
+        `;
+        
+        await executeClickHouseQuery(chConfig, updateTagsQueryByLinkId);
+        log(`尝试通过link_id更新事件标签: ${externalId}`, true);
+      }
+      
+      // 如果资源有resource_id，也尝试使用它来更新
+      if (resource.resource_id) {
+        const updateByResourceId = `
+          ALTER TABLE shorturl_analytics.events
+          UPDATE link_tags = '${escapeString(tags || '[]')}'
+          WHERE link_id = '${resource.resource_id}'
+        `;
+        
+        await executeClickHouseQuery(chConfig, updateByResourceId);
+        log(`尝试通过resource_id更新事件标签: ${resource.resource_id}`, true);
+      }
+      
+      updatedCount++;
+    }
+    
+    log(`已尝试更新 ${updatedCount} 个资源的事件记录`);
+    
+  } catch (error) {
+    log(`更新events表失败: ${(error as Error).message}`);
+  }
+  
+  return updatedCount;
+}
+
+/**
+ * 获取ClickHouse配置
+ */
+async function getClickHouseConfig(): Promise<ChConfig> {
+  try {
+    const chConfigJson = await getVariable("f/shorturl_analytics/clickhouse");
+    
+    // 确保配置不为空
+    if (!chConfigJson) {
+      throw new Error("未找到ClickHouse配置");
+    }
+    
+    // 解析JSON字符串为对象
+    let chConfig: ChConfig;
+    if (typeof chConfigJson === 'string') {
+      try {
+        chConfig = JSON.parse(chConfigJson);
+      } catch (_) {
+        throw new Error("ClickHouse配置不是有效的JSON");
+      }
+    } else {
+      chConfig = chConfigJson as ChConfig;
+    }
+    
+    // 验证并构建URL
+    if (!chConfig.clickhouse_url && chConfig.clickhouse_host && chConfig.clickhouse_port) {
+      chConfig.clickhouse_url = `http://${chConfig.clickhouse_host}:${chConfig.clickhouse_port}`;
+    }
+    
+    if (!chConfig.clickhouse_url) {
+      throw new Error("ClickHouse配置缺少URL");
+    }
+    
+    return chConfig;
+  } catch (error) {
+    throw new Error(`获取ClickHouse配置失败: ${(error as Error).message}`);
+  }
+}
+
+/**
+ * 检查ClickHouse中是否存在指定表
+ */
+async function checkClickHouseTable(chConfig: ChConfig, tableName: string): Promise<boolean> {
+  try {
+    const query = `EXISTS TABLE ${tableName}`;
+    const result = await executeClickHouseQuery(chConfig, query);
+    return result.trim() === '1';
+  } catch (error) {
+    console.error(`检查表 ${tableName} 失败:`, error);
+    return false;
+  }
+}
+
+/**
+ * 执行ClickHouse查询
+ */
+async function executeClickHouseQuery(chConfig: ChConfig, query: string): Promise<string> {
+  // 确保URL有效
+  if (!chConfig.clickhouse_url) {
+    throw new Error("无效的ClickHouse URL: 未定义");
+  }
+  
+  // 执行HTTP请求
+  try {
+    const response = await fetch(chConfig.clickhouse_url, {
+      method: "POST",
+      headers: {
+        "Content-Type": "application/x-www-form-urlencoded",
+        "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+      },
+      body: query,
+    });
+    
+    if (!response.ok) {
+      const errorText = await response.text();
+      throw new Error(`ClickHouse查询失败 (${response.status}): ${errorText}`);
+    }
+    
+    return await response.text();
+  } catch (error) {
+    throw new Error(`执行ClickHouse查询失败: ${(error as Error).message}`);
+  }
+}
+
+/**
+ * 解析JSON字段
+ */
+function parseJsonField(field: unknown): Record<string, unknown> {
+  if (!field) return {};
+  
+  try {
+    if (typeof field === 'string') {
+      return JSON.parse(field);
+    } else if (typeof field === 'object') {
+      return field as Record<string, unknown>;
+    }
+  } catch (error) {
+    console.warn(`无法解析JSON字段:`, error);
+  }
+  
+  return {};
+}
+
+/**
+ * 转义字符串，避免SQL注入
+ */
+function escapeString(str: string): string {
+  if (!str) return '';
+  return str.replace(/'/g, "''");
+}