Skip to main content

BigQuery Sync

Overview​

This screen configures the connection to Google BigQuery in order to pull data out of BigQuery and into the platform as custom user attributes.

Despite the feature name reading as a sync to BigQuery, the actual data flow runs the other way — from BigQuery into the platform. That is visible in the sync result, which reports how many users were updated, and in the field mapping, which pairs a BigQuery column with a user attribute key.

The defining characteristic is that this is a single-configuration page with no list view. One LINE OA holds exactly one configuration, so the same screen serves as both create and edit depending on whether a configuration already exists. It lives under the system settings menu.

The practical benefit is that teams already warehousing customer data in BigQuery can pull derived values — lifetime spend, membership tier, purchase behaviour — into LINE user attributes and use them for targeting and campaigns.

Business Flow​

Loading the configuration​

  1. On open, the screen fetches the current configuration. Having no configuration yet is a normal state, so the request is not retried and an empty form is presented instead.
  2. While loading, the form area is replaced by a loading indicator.
  3. Defaults for a new configuration are a user-identifier column named user_id, a schedule of 06:00 daily, a disabled state, and no field mappings.
  4. When an existing configuration loads, its values populate the form with the custom. prefix stripped from every attribute key, because the form already renders that prefix as a fixed part of the input control.

Filling in the form​

The form is organised into four cards.

  1. Connection — the GCP project ID (required), the summary table to read (required), the column holding the LINE user ID (defaulted if left blank), and the service account credentials as JSON. A Test connection button sends only the connection details and distinguishes three outcomes: connection succeeded, the server responded but could not connect (showing the server's reason), and the request itself failed.
  2. Schedule — the automatic run time as a cron expression, plus a switch to enable or disable scheduled runs. The web layer does not validate the cron format.
  3. Field mappings — a repeatable list where every row has four required inputs: the BigQuery column name, the target attribute key (the input carries a fixed custom. prefix, so the user types only the suffix), the data type (string, number, date, or boolean), and a display label.
  4. Last run status — shown only once a configuration has been saved. It reports the last sync time and its outcome as tags: users updated, error count (shown only when above zero), and duration. If no sync has ever run, an informational notice appears instead. This card also holds the Sync now button.

Saving​

  1. The save button's label alternates between create and update. Pressing it validates the form first.
  2. Before submitting, the payload is reassembled: the user-identifier column falls back to its default when blank, and the custom. prefix is restored on every attribute key, written so that a user who typed the prefix themselves does not end up with it twice.
  3. With no existing configuration this creates one; otherwise it updates the existing record. Either way success reports back and reloads the data, while failures surface the server's message.
  4. Leaving the page always clears the loading state so it cannot persist into the next screen.

Triggering a sync​

  1. The screen first checks that a saved configuration exists; without one it prompts the user to save first.
  2. On success it reports that the job has started and reloads the data to pick up the new last-run time and result.
  3. The sync itself runs asynchronously in the background. An immediate reload may still show the previous values, since the screen does not poll for progress — the user has to refresh again to see the outcome.

Key Screens & Components​

Page (src/app/bigquery-sync/page.tsx) — sets the two-level breadcrumb under settings and renders the form container.

Container (src/components/bigquery-sync/bigquery-sync-form.container.tsx) — holds all the logic: loading the configuration, create and update, connection testing, sync triggering, and the attribute key prefix conversion in both directions.

Form (src/components/bigquery-sync/bigquery-sync-form.tsx) — lays out the four cards, the add/remove field mapping list, and the last-run status display.

Shared service (src/services/bigquery-sync.service.ts) — read configuration, create, update, test connection, trigger sync, and read sync status. The status call is unused by the UI, which instead reads last-run information straight off the configuration.

Data shape — one configuration has four parts: connection details, schedule, field mappings, and last-run results. A single mapping row holds the source column, the target key, the data type, and the display label.

Dependencies​

  • Permissions — a bigquery-sync subject is declared and unlocked by the backend system module, but in practice no check is applied, neither on the page itself nor when rendering the submenu entry.
  • Google BigQuery — requires a GCP project, a summary table, a column holding the LINE user ID, and service account credentials. The actual connection and scheduled execution run entirely on the server and worker; the web layer only stores settings and dispatches jobs.
  • Attribute Setup — mapped keys land in the same custom. namespace as the platform's own user attributes, so BigQuery-sourced data is immediately usable for audience building and segmentation.
  • UI conventions — this page differs from the rest of the CMS: it uses lightweight toast messages throughout rather than confirmation and result dialogs, omits the standard section header, and performs no client-side cron or JSON validation.

Backend Details (CMS API)​

The module lives in internal/modules/bigquerysync/, registered under the /api/bigquery-sync-config group, with the actual BigQuery client in the internal/bigqueryx package.

The most important security note on this page​

  • Every endpoint in this module sits on the public group and is checked only by a login-level token. There is no policy check whatsoever and no ModuleGate — carried over from the previous system, which never added authorization here.
  • In practice this means anyone who can log in can call these endpoints, regardless of their permissions. That matches the frontend, which likewise checks nothing on the page or on the submenu entry.
  • The point carries extra weight because this module stores Google Cloud service account credentials and exposes a button that syncs user data to a destination the caller specifies. It should be treated as a priority item whenever real permission enforcement is switched on.
  • Data scoping still comes from the request context (lineOaId, organizationId), so a user only sees the configuration of the OA they are working in.

What each endpoint actually does​

  • GET /api/bigquery-sync-config — reads the OA's current configuration.
  • POST /api/bigquery-sync-config/test-connection — attempts a BigQuery connection with the supplied credentials without persisting anything, returning a success flag and an explanatory message so the user knows the settings are right before saving.
  • POST /api/bigquery-sync-config — saves the configuration: project ID, dataset, credentials, the tables to sync, and the schedule.
  • PUT /api/bigquery-sync-config/:id — updates an existing configuration.
  • POST /api/bigquery-sync-config/:id/sync-now — triggers an immediate sync, returning a success flag and a message.
  • GET /api/bigquery-sync-config/:id/sync-status — reads the last run: the timestamp of the most recent sync (nullable if it has never run) and the last result as the raw JSON that was stored, not normalised into a fixed structure — so the UI must tolerate varied shapes.

Connection behaviour worth knowing​

  • BigQuery is not connected at service startup. An empty client is constructed and the real connection is made on demand. Bad settings therefore never prevent the service from booting; they surface when the test button is pressed or when a scheduled sync comes due.
  • Scheduled syncing does not run in this service — it runs on the worker. The CMS API only stores configuration and dispatches jobs, so the status the UI reads is whatever the worker wrote back.
  • There is no cron-format or credential-JSON validation on the frontend, and this module declares no additional validation policy either. Malformed values therefore fail at run time rather than at save time.

Tables involved​

bigquery_sync_config and line_oa.