Click Tracking
Overview
This domain has three tables: tracking_log (read/click/send events generated by every kind of
content), tracking_token (short-link tokens together with their real destinations), and
tracking_line_users (a mapping of tracking keys to the LINE users who followed them).
Table tracking_log
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NO | gen_random_uuid() | Primary key of the log entry |
redirect_id | TEXT | YES | - | Reference id of the redirect that produced this log entry |
token | TEXT | YES | - | Token of the followed link (matches tracking_token.token) |
organization_id | INTEGER | NO | - | Owning organization (FK to organization.id) |
line_oa_id | INTEGER | NO | - | LINE OA where the event occurred (FK to line_oa.id) |
line_uid | TEXT | YES | - | LINE user ID of the actor (NULL when not identifiable) |
service | "TrackingService" | NO | redirect | Channel that captured the event: webhook or redirect |
action_type | "ActionType" | NO | read | Action kind: read, click, send |
type | "TrackingType" | NO | asset | What was tracked: message, uri, asset, postback |
content_type | "ContentType" | YES | - | Source content type: rich_menu, campaign, auto_response, trigger_rule, lead_generation |
campaign_id | INTEGER | YES | - | Source campaign (FK to campaign.id) |
rich_menu_id | INTEGER | YES | - | Source rich menu (FK to rich_menu.id) |
rich_menu_action_index | INTEGER | YES | - | Index of the tapped rich menu area |
rich_message_id | INTEGER | YES | - | Source message id |
rich_message_index | TEXT | YES | - | Position/part of the message that was tapped |
auto_response_id | INTEGER | YES | - | Source auto-response rule |
trigger_rule_id | INTEGER | YES | - | Source trigger rule |
tracking_label | TEXT | YES | - | User-defined label for grouping statistics |
title | TEXT | NO | - | Name of the tracked item at the time of the event |
short_url | TEXT | YES | - | Short link the user followed |
original_url | TEXT | YES | - | Real destination URL that was redirected to |
raw_data | JSONB | YES | - | Raw event data, e.g. the webhook payload or request headers |
created_date | TIMESTAMPTZ(3) | NO | now() | When the event occurred |
rich_menu_archive_id | INTEGER | YES | - | Source archived rich menu (FK to rich_menu_archive.id) |
Table tracking_token
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NO | gen_random_uuid() | Primary key of the token |
token | TEXT | NO | - | Token value used in the short link (unique table-wide) |
content_type | "ContentType" | NO | - | Content type the token is bound to |
content_id | INTEGER | NO | - | Content id the token is bound to, interpreted per content_type |
action_index | INTEGER | NO | - | Index of the action/tappable area within that content |
destination_url | TEXT | NO | - | Real destination URL to redirect to |
tracking_label | TEXT | YES | - | Label for grouping statistics |
organization_id | INTEGER | NO | - | Owning organization |
line_oa_id | INTEGER | NO | - | Owning LINE OA (FK to line_oa.id) |
line_oa_hash | TEXT | NO | - | LINE OA hash used to compose the short URL |
metadata | JSONB | YES | - | Additional token metadata |
status | "CommonStatus" | NO | active | Token status (active, inactive, expired, ...) |
created_date | TIMESTAMPTZ(3) | NO | now() | Token creation timestamp |
created_by | INTEGER | NO | 0 | User who created the record |
updated_date | TIMESTAMPTZ(3) | YES | - | Last update timestamp (maintained automatically) |
updated_by | INTEGER | YES | 0 | User who last updated the record |
deleted_date | TIMESTAMPTZ(3) | YES | - | Soft-delete timestamp |
expires_at | TIMESTAMPTZ(3) | YES | - | Token expiry timestamp (NULL means never expires) |
Table tracking_line_users
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NO | gen_random_uuid() | Primary key of the record |
tracking_key | TEXT | NO | - | Tracking key this user followed |
line_user_id | TEXT | NO | - | LINE user ID of the person who followed it |
content_type | "ContentType" | NO | - | Content type the tracking key belongs to |
content_id | INTEGER | NO | - | Content id the tracking key belongs to |
organization_id | INTEGER | NO | - | Owning organization |
line_oa_id | INTEGER | NO | - | Related LINE OA |
created_at | TIMESTAMPTZ(6) | NO | now() | First recorded timestamp |
updated_at | TIMESTAMPTZ(6) | NO | now() | Last update timestamp (maintained automatically) |
Notes
- FKs on
tracking_log:organization_idtoorganization.id,line_oa_idtoline_oa.id,campaign_idtocampaign.id,rich_menu_idtorich_menu.id,rich_menu_archive_idtorich_menu_archive.id, and the composite (line_uid,line_oa_id,organization_id) toline_user(user_id,line_oa_id,organization_id). tracking_token.tokencarries a unique constraint;tracking_line_usershas the unique constraintuk_tracking_key_line_user_idon (tracking_key,line_user_id), so a user has at most one row per tracking key.tracking_logcarries six composite indexes covering queries by user, by campaign, by rich menu, by channel/type, and by per-part message clicks — each led bycreated_dateor a filtering key.tracking_loghas no soft-delete column because it is an append-only log.tracking_line_usersuses microsecond precision (TIMESTAMPTZ(6)), unlike the other two tables which useTIMESTAMPTZ(3).