BigQuery Data Sync

Overview

Many enterprise customers keep their customer data in Google BigQuery — lifetime spend, segments, RFM scores and so on. The platform lets them connect their own BigQuery project and sync those values into line_user.custom_attribute on a schedule, which makes data-warehouse figures usable for segmentation and triggers.

The distinguishing feature is that the schedule is defined by data, not by code: every row in bigquery_sync_config carries its own cron expression and its own tenant credentials.

Business Flow

1. The bigquery-sync profile starts up. It needs PostgreSQL only — no RabbitMQ, no Redis.
2. Every enabled row in bigquery_sync_config is loaded and registered as its own cron job named bigquery-sync-<id>, using that row's sync_cron or the default 0 6 * * * (6am daily).
3. When a job fires it:
   • re-reads the config from the database, in case it was edited after registration;
   • builds a tenant-specific BigQuery client from project_id and credentials_json;
   • queries the summary table named in the config.
4. Map and write
   • Look up the line_user by the value in the column named by user_id_column.
   • Translate BigQuery column names into custom_attribute keys according to field_mappings.
   • Write with a jsonb merge in batches of 100 rows.
   • Rows that fail are skipped and counted rather than failing the whole run.
5. Write last_sync_at and last_sync_result — the count of updated rows and errors — back to the config row so the CMS can show the latest sync status.
6. On shutdown, the scheduler waits for any in-flight job to finish before stopping.

Key Files & Functions

File	Responsibility
internal/bigquerysync/service.go	Service.Start and Service.Stop manage the cron scheduler's lifecycle; the work itself lives in loadAndRegisterConfigs, registerCronForConfig, syncForConfig, createBigQueryClient, querySummaryTable, batchUpdateUsers, updateUserAttribute and updateSyncResult, alongside the batch size of 100 and the default cron 0 6 * * *
internal/bigqueryx/bigqueryx.go	Wrapper around the Google BigQuery client
cmd/worker/main.go	runBigquerySync — entry point of the bigquery-sync profile, admin port :9107

There are no queues at all: this is pure cron, neither consuming nor publishing on RabbitMQ.

Connections to Other Services

• Table bigquery_sync_config — holds line_oa_id, organization_id, project_id, credentials_json, summary_table, user_id_column, field_mappings, sync_cron, the enabled flag, and last_sync_at / last_sync_result.
• Table line_user — the destination, written via jsonb merge into custom_attribute.
• Google BigQuery — a per-tenant client built from the service account credentials stored in the database.
• cms-api-go — owns the connection settings screen and the last-sync status view.
• No LINE API calls, and neither RabbitMQ nor Redis are used.
• The synced attributes are consumed downstream by the Trigger Engine and by cms-api's audience filters.