Overview
This domain has three tables: friend_track_campaign (campaigns that track where new friends come
from, each with its own token for the invite link), friend_track_event (follow/unfollow events
that arrived through such a campaign), and friend_track_ref (sub-reference codes, ref_id,
pre-issued under a single campaign).
Table friend_track_campaign
| Column | Type | Nullable | Default | Description |
|---|
id | SERIAL (INTEGER) | NO | nextval(...) | Primary key of the friend-track campaign |
name | TEXT | NO | - | Campaign name |
token | TEXT | NO | - | Campaign token embedded in the invite link (unique table-wide) |
description | TEXT | YES | - | Campaign description |
status | TEXT | NO | 'active' | Campaign status |
attribute_config | JSONB | YES | {} | Attribute values applied to users who arrive through this campaign |
line_oa_id | INTEGER | NO | - | Owning LINE OA (FK to line_oa.id) |
organization_id | INTEGER | NO | - | Owning organization (FK to organization.id) |
created_by | INTEGER | NO | 0 | User who created the record |
created_date | TIMESTAMPTZ(3) | NO | now() | Creation timestamp |
updated_by | INTEGER | YES | 0 | User who last updated the record |
updated_date | TIMESTAMPTZ(3) | YES | - | Last update timestamp |
deleted_date | TIMESTAMPTZ(3) | YES | - | Soft-delete timestamp |
ref_attribute_key | VARCHAR(255) | YES | - | Attribute name used to store the ref_id on the user profile |
ref_upload_status | JSONB | YES | - | Status of the ref_id bulk upload into the campaign (progress/result) |
Table friend_track_event
| Column | Type | Nullable | Default | Description |
|---|
id | SERIAL (INTEGER) | NO | nextval(...) | Primary key of the event |
campaign_id | INTEGER | NO | - | Campaign the event belongs to (FK to friend_track_campaign.id) |
line_user_id | TEXT | NO | - | LINE user ID of the person the event is about |
event_type | TEXT | NO | - | Event kind, e.g. follow or unfollow |
display_name | TEXT | YES | - | User's display name at the time of the event |
picture_url | TEXT | YES | - | User's profile picture URL at the time of the event |
line_oa_id | INTEGER | NO | - | LINE OA where the event occurred |
organization_id | INTEGER | NO | - | Owning organization |
created_date | TIMESTAMPTZ(3) | NO | now() | When the event occurred |
ref_id | VARCHAR(255) | YES | - | Sub-reference code carried by the link, identifying the individual channel or referrer |
Table friend_track_ref
| Column | Type | Nullable | Default | Description |
|---|
id | SERIAL (INTEGER) | NO | nextval(...) | Primary key of the ref record |
campaign_id | INTEGER | NO | - | Campaign the ref belongs to (FK to friend_track_campaign.id) |
ref_id | VARCHAR(255) | NO | - | Issued sub-reference code (unique within the campaign) |
line_oa_id | INTEGER | NO | - | Owning LINE OA |
organization_id | INTEGER | NO | - | Owning organization |
created_date | TIMESTAMPTZ(3) | NO | now() | Creation timestamp |
Notes
friend_track_campaign has FKs to line_oa.id and organization.id with ON DELETE RESTRICT,
so a LINE OA or organization with campaigns still attached cannot be deleted.friend_track_event.campaign_id and friend_track_ref.campaign_id are FKs to
friend_track_campaign.id with ON DELETE CASCADE — deleting a campaign removes its events and
refs.friend_track_campaign.token carries a table-wide unique constraint.friend_track_ref has a unique constraint on (campaign_id, ref_id): a ref_id cannot repeat
within one campaign, but the same value may be reused across campaigns.friend_track_campaign is also referenced by content_page, linking content pages to a
friend-track campaign.- Indexes:
friend_track_campaign on line_oa_id and deleted_date; friend_track_event on
campaign_id, (line_user_id, line_oa_id) and (event_type, campaign_id);
friend_track_ref on campaign_id.
friend_track_event and friend_track_ref have no soft-delete column.