Skip to main content

Matching Respondents to the Customer Database

Overview​

The mechanism that turns an ordinary form into a member-verification form. An admin picks one customer database (customer_database) and defines db_validation questions bound to columns in it via fieldKey. The respondent must match a single row on every criterion at once to count as a match.

On a successful match, the system tags the LINE user through custom_attribute and binds matchedRowId to the submission so the same record cannot be claimed twice.

Business Flow​

ValidateSubmission(fb, answers, lineUserID)​

  1. Read the config from form_builder.profile_mapping. If it is disabled or fails to decode, skip everything (checked:false).
  2. Collect criteria from the form definition only: walk the db_validation questions, take each fieldKey (which must be a non-empty JSON string), pair it with the answer, and trim. Empty values are skipped. Crucially, fieldKey comes from admin-controlled config rather than the user's body, which is what prevents SQL and JSONB key injection. Key order is preserved so the generated SQL is identical every time.
  3. With no criteria at all, skip (checked:false).
  4. Verify that databaseId coerces to a positive integer and that the customer_database row exists with status <> 'delete'. Failing either leads to handleNotFound.
  5. Issue a single query that requires every criterion to hold on the same row: SELECT id FROM customer_database_row WHERE database_id = $1 AND data->>$2 = $3 AND data->>$4 = $5 ... LIMIT 1. Both keys and values are passed as parameters — no string interpolation anywhere.
  6. On a match
    • If oneAccountPerRecord is enabled, look for a form_submission with is_submitted = true whose metadata->'profileMapping'->>'matchedRowId' equals this row. If one exists and belongs to a different LINE user (or to an unknown one), return 400 with {code:"RECORD_ALREADY_CLAIMED"} and the message from alreadyClaimedMessage, defaulting to This record is already linked to another LINE account.
    • Otherwise return {checked:true, matched:true, matchedRowID}.
  7. On no match, handleNotFound decides:
    • With registerIfNotFound = true, pass as "unverified" ({checked:true, matched:false}), allowing new people to sign up.
    • Otherwise return 400 with {code:"PROFILE_NOT_FOUND"} and the message from notFoundMessage, defaulting to Record not found.

ApplyVerifiedAttribute(fb, lineUserID)​

Called best-effort after a successful save when a match was found.

  • The key is verifiedAttribute, defaulting to verified; a custom. prefix is stripped if present.
  • If verifiedAttribute is deliberately set to an empty string (the admin chose not to tag anyone), nothing happens — but with no config at all it still defaults to verified, for parity.
  • Values of "true" or "false" are converted to booleans; anything else is stored as a string. The default is true.
  • The write merges via custom_attribute = COALESCE(custom_attribute,'{}'::jsonb) || $2::jsonb.

Key Files & Functions​

This feature exposes no routes of its own: it is step 6 of Submit and the first step of an OTP request.

FileFunctions
internal/formsubmission/profilemapping.goNewProfileMappingService(db), ValidateSubmission, ApplyVerifiedAttribute, handleNotFound, decodeProfileMapping, decodeDbValidationQuestions, jsonString, jsNumberInt, anyToString
Config structureprofileMappingConfig with enabled, databaseId, registerIfNotFound, notFoundMessage, verifiedAttribute, verifiedValue, oneAccountPerRecord, alreadyClaimedMessage
Error bodyinternal/formsubmission/errors.go → newCodeError for the PROFILE_NOT_FOUND and RECORD_ALREADY_CLAIMED codes

Connections to Other Services​

  • The customer_database and customer_database_row tables (jsonb data), form_submission (jsonb metadata), and line_user (jsonb custom_attribute).
  • Uses the sqlx pool directly with no separate repository, mirroring the raw queries in the source.
  • The resulting matchedRowID is consumed by OTP Verification, both when resolving the destination phone number or email and when comparing against an already-verified session.
  • Called from Form Answer Submission.
  • The related client-web feature is form-fill, which keeps a lookup table translating PROFILE_NOT_FOUND and RECORD_ALREADY_CLAIMED into Thai messages.