Skip to main content

Settings — BigQuery Sync

Overview​

BigQuery Sync configures pulling data out of Google BigQuery and into the system as extra user properties (custom attributes). Despite reading like "sync to BigQuery," the actual data direction is from BigQuery into the system. It suits teams that already keep a customer data warehouse on BigQuery (lifetime spend, membership tier, and the like) and want to pull it in to build target groups from.

It's a single configuration per LINE OA (backed by a unique index on (organization_id, line_oa_id)), so there is no list page — opening the menu drops you straight into one form, and the save button reads "Save Configuration" or "Update Configuration" depending on whether one already exists.

Who uses it — administrators wiring an external data warehouse into the system (this module has no ModuleGate and no policy enforcement of any kind — see Access control, which is worth reading before putting this into production).

User journey​

  1. Open Settings > BigQuery Sync. With nothing configured yet you get an empty form with defaults (user column user_id, schedule 0 6 * * * = daily at 6 AM, disabled, empty field mappings).
  2. Fill in the Connection card:
    • Project ID (required) — the GCP project id, e.g. my-gcp-project-id
    • Summary Table (required) — a path in the form project.dataset.table_name
    • User ID Column (optional, defaults to user_id) — the BigQuery column holding the LINE user ID used to match against users in the system
    • Service Account Key — paste the entire JSON key content into the textarea
  3. Click Test Connection — the system actually runs SELECT 1 FROM <summary table> LIMIT 1 using the credentials currently in the form (no need to save first). The result is a success/failure plus a message, and it never throws — every failure is converted into readable text.
  4. Fill in the Schedule card with a cron expression and flip the enable-sync switch.
  5. Add rows to the Field Mappings card — each row has 4 fields (all required): the BigQuery column, the attribute key (with a custom. prefix attached for you), the data type (string / number / date / boolean), and a display label.
  6. Save.
  7. Once a configuration is saved, the Sync Status card appears (it is not rendered at all before the first save), showing the last sync time, the last run's result, and a Sync Now button — which requires the enable switch to be on, otherwise it returns a 400 BigQuery sync is not enabled.

Key details verified against the source​

Confirmed from internal/modules/bigquerysync/{controller,service,dto}.go and internal/entities/bigquery_sync_config.go in line-management-cms-api-go, cross-checked against internal/bigquerysync/service.go in line-management-worker-go and the real DDL of the bigquery_sync_config table:

  • The "Sync Now" button doesn't actually trigger any sync — POST /bigquery-sync-config/:id/sync-now verifies the configuration exists and is enabled, then writes a log line and returns {success: true, message: "Manual sync triggered successfully"} and nothing more. The source still carries // TODO: Publish to RabbitMQ for worker to handle actual sync at exactly the point where the job should be queued. So the UI shows "Sync triggered successfully" and refetches, but no number moves. The only sync that actually runs is the scheduled cron on the worker side.
  • The worker reads the credentials column under the wrong name — the CMS writes to the credentials column (confirmed by DDL: credentials text), but the worker's row reader does a SELECT * and switches on each column name, with a case for credentials_json — which does not exist in the table. The credentials value is therefore silently skipped and the worker builds its BigQuery client with empty credentials (falling back to whatever ambient credentials the runtime has). The key entered on this page is never actually used on the scheduled-sync path.
  • The field_mappings shape the CMS writes and the shape the worker reads don't match — the CMS stores an array of objects, [{gaField, attributeKey, dataType, label}, …] (the column is jsonb DEFAULT '[]'), while the worker declares it as map[string]string and expects {"bigquery_column": "custom_attribute_key"}. Decoding an array into a map fails and yields nil, so no attribute is ever mapped and every row counts as "not updated" rather than an error — the run summary comes out as updated: 0, errors: 0.
  • The status card reads different keys than the worker writes — the frontend reads lastSyncResult.usersUpdated, .errors, and .duration, while the worker writes updated, errors, totalRows, and durationMs. Only the error count tag can therefore render; "N users updated" and "Duration" never appear whether the sync succeeded or not (their values are undefined, so rendering is skipped).
  • The service-account credentials are stored in plaintext and returned in full by the API — the column is text with no encryption, and the entity declares json:"credentials" with no json:"-", so it is included in full in the GET /bigquery-sync-config response. The frontend then puts that value straight back into the textarea (credentials: existingConfig.credentials). This is a stark contrast with the OTP Verification menu, which encrypts its secret with AES-256-GCM before storing and returns only a masked view. Combined with Access control below, this is the single most important thing to know about this page.
  • There is no cron-expression validation and no check that the credential is valid JSON — the DTO only type-checks (syncCron is a string, credentials is a string). Both the frontend and backend happily save a malformed value, which then breaks when the worker registers the cron job (AddFunc errors, the error is logged, and that job is simply skipped — invisibly).
  • The worker reads the schedule only once, at boot — Start loads every row where enabled = true and registers one cron job named bigquery-sync-<id> per row, once. Editing the cron expression or newly flipping the enable switch has no effect until the worker restarts (the job re-reads its config on each run, but the schedule is never reloaded). An empty value falls back to 0 6 * * *.
  • The actual sync is "read the whole table and merge into jsonb" — the worker runs SELECT * FROM <project>.<summary_table> (the table name backtick-quoted BigQuery-style) with no WHERE and no LIMIT (a large table is read in full every run), processes in batches of 100 rows, and for each row runs an UPDATE on line_user with custom_attribute = COALESCE(custom_attribute,'{}') || <new values> matched on user_id. It's a merge, not a full overwrite, and a row that errors is counted and skipped rather than aborting the job.
  • The user UPDATE is not filtered by line_oa_id — the worker's statement filters only user_id = ? AND deleted_date IS NULL. So if the same LINE user ID appears under several OAs, values pulled from one OA's BigQuery get written onto that user under every other OA too.
  • GET /bigquery-sync-config answers null, not 404, before anything is configured — that's the normal "not configured yet" state. Creating a second one returns 400 BigQuery sync config already exists for this LINE OA, and referencing an id that isn't yours returns 400 BigQuery sync config not found (400, not 404 — note the message says "not found" while the status doesn't).
  • PUT writes with WHERE id = ? alone — a preceding read does verify the row belongs to the caller's organization_id + line_oa_id, so it's still safe, but the UPDATE statement itself carries no tenant scope (the source comment notes this preserves parity with the TS original's .update(id, …)).
  • Sending userIdColumn: "" or syncCron: "" on create doesn't store an empty string — it stores the DB default — the source comment accepts this as a deliberate minor divergence from the TS original, because GORM treats an empty string as a zero value and lets the DB default apply.
  • User Attribute Setup — keys mapped from BigQuery live in the same custom. namespace as the custom attributes declared there, and the worker writes into line_user.custom_attribute directly so the values land in the same place. But this page never reads attribute definitions from that menu — the attribute-key field is a free-text input with a custom. prefix attached, no picker, and no check that the key was ever declared. One typo yields an orphaned attribute you cannot filter on in Segment audience.
  • Segment audience — the real destination for this data, but it can only filter on a synced attribute if that key is declared in User Attribute Setup with the filterable switch on.
  • All friends — shows synced values as extra columns when the attribute has displayInReport on in User Attribute Setup.

Things to watch out for​

  • The "Sync Now" button does not work yet — you get a success message and nothing happens. Real syncing only happens on the worker's schedule.
  • The Sync Status card can't show all its numbers — "users updated" and "duration" never appear, because the key names don't match between the worker and the frontend. Rely on the worker logs instead.
  • The service-account key you paste is stored in plaintext, and anyone who can log in can call GET /bigquery-sync-config and read the whole thing (this module has no ModuleGate and no policy enforcement). Use a service account scoped to read only the tables you need (least privilege) and rotate the key regularly.
  • Editing the schedule or newly enabling sync has no effect until the worker restarts — cron jobs are registered once at boot.
  • This page validates neither the cron expression nor the credential JSON up front — a malformed value saves fine and breaks at run time, and a failed cron registration never surfaces anywhere in the UI. Always confirm credentials with Test Connection before saving.
  • The summary table is read in full every run — there is no incremental read. Point this at a properly sized summary/aggregate table, not a raw event table, both for performance and for BigQuery query cost.
  • Write-back matches on the LINE user ID alone, with no OA filter — if the same person exists under several OAs in the same system, synced values spread to all of them.
  • The "Sync Now" button doesn't appear until the first save succeeds (the status card is keyed on existingConfig.id).

Access control (verified against the source)​

Verified: this module has no module-level or action-level access control whatsoever, and is the weakest-controlled of the four modules covered by this documentation set.

  • No ModuleGate — verified across the whole repository: modulegate.ModuleGate(...) is used in 15 modules, and bigquerysync is not one of them. A platform admin has no way to close this module for a specific organization.
  • No CheckPolicies on a single route — unlike other modules that at least declare policy annotations for the future (even though the middleware is a no-op), this module declares none at all.
  • Every route is registered on the public group because the source controller is @Public() — this is where it is weaker than even the OTP Verification menu: @Public() makes all 6 routes skip the global JwtAuthGuard entirely, attaching route-local JwtLoginAuth middleware instead. That is the jwt-login strategy, which does not require the token to carry a lineOaId claim (unlike the normal jwt strategy, which does). In practice a token with no OA attached resolves lineOaId to 0 and matches no rows — so there is no cross-org leak — but it does mean the only remaining gate is "holds an unexpired JWT."
  • The real remaining protection is the service-layer tenant scope — every query (GetConfig, Create, Update, SyncNow, GetSyncStatus) filters on organization_id and line_oa_id taken from the token's context, so a user from another OA cannot read or modify a config across OAs. But any logged-in user within the same organization, at any permission level, can read their OA's service-account key in full — no other authorization layer stands in the way.
  • Frontend — no PermissionGuard wraps this page, so hiding the menu item is the only UI-level control, which is not enforcement — as the modulegate package itself puts it, "hiding a menu in the CMS is not enforcement."
  • No route requires Super Admin, and the module has no delete path at all (only GET / POST / PUT / test-connection / sync-now / sync-status).

Source file reference​

Frontend (cms-web-2026)

  • src/app/bigquery-sync/page.tsx — entry point (no PermissionGuard)
  • src/components/bigquery-sync/bigquery-sync-form.tsx — the 4 cards, field-mapping rows, and the Sync Status card with the keys it reads
  • src/components/bigquery-sync/bigquery-sync-form.container.tsx — refilling the form (including credentials), and the custom. prefix normalization
  • src/services/bigquery-sync.service.ts, src/services/types/bigquery-sync.type.ts — endpoints and payload shapes

Backend (line-management-cms-api-go)

  • internal/modules/bigquerysync/controller.go — all 6 routes on the public group, JwtLoginAuth, no ModuleGate/CheckPolicies
  • internal/modules/bigquerysync/service.go — tenant scoping, the still-TODO SyncNow, TestConnection
  • internal/modules/bigquerysync/dto.go — inbound validation rules (no cron/JSON checking)
  • internal/entities/bigquery_sync_config.go — the plaintext credentials column that ships in the JSON response

Worker (line-management-worker-go)

  • internal/bigquerysync/service.go — boot-time cron registration, SELECT * FROM <table>, the batched jsonb merge, last_sync_result writing, and the column/key names that don't match the CMS side
  • internal/bigqueryx/bigqueryx.go — the client built from project id + credentials

See the documentation team's earlier technical write-up at BigQuery Sync