Article summary
Build a reliable WhatsApp number-checking integration around versioned TXT batches, task manifests, Excel schema contracts, staging tables and idempotent CRM updates.
AIPUSH WhatsApp number checking currently uses TXT upload and Excel export rather than a direct API. That does not prevent disciplined system integration. Treat the file exchange as an interface: define inputs, outputs, states, identifiers and failure handling just as carefully as an API contract.
The result is a controlled batch pipeline that can be automated around human upload/download steps without pretending those steps do not exist.
Architecture: six zones, one immutable batch ID
- Source query: selects authorized CRM records.
- Preparation: normalizes phone numbers and creates a unique batch ID.
- Outbound: stores the final TXT and its manifest.
- Processing: records the selected AIPUSH WS task.
- Inbound: stores the untouched Excel result.
- Staging: validates schema, reconciles rows and prepares controlled CRM updates.
Use the batch ID in filenames, logs and staging rows. Never rely on “latest.xlsx” as system state.
The outbound manifest is the missing API request
| Manifest field | Purpose | Example shape |
|---|---|---|
| batch_id | Idempotency and traceability | WS-20260911-0042 |
| task | Defines the expected result schema | ws_activity |
| created_at | Audit timestamp | ISO date-time with timezone |
| source_snapshot | Identifies the CRM extract | Controlled query/version reference |
| line_count | Supports reconciliation | Number of TXT lines |
| sha256 | Detects accidental file changes | Checksum of the exact TXT bytes |
| schema_version | Links task to expected columns | ws_activity_v1 |
Generate TXT from a reproducible query
The export job should store the query or selection rule, the source record IDs and the normalized phone key. Write one phone number per TXT line. Remove duplicates from the outbound file while preserving a local bridge table for one-to-many CRM relationships.
Reject rows with ambiguous country context instead of guessing. Record rejection reasons in the batch report so the absence of a row is explainable.
Define a schema contract for every WS task
| Contract | Required columns |
|---|---|
| Registration | Phone number, registered or not |
| High-precision registration | Phone number, business account, WhatsApp-mapped number |
| Activity | Phone number, activity time, active days, mapped number |
| Gender and age | Phone number, age, gender, avatar, mapped number |
| Avatar | Phone number, business account, avatar, mapped number |
| All format | Activity, gender, age, avatar, skin tone, avatar type, business and mapped-number fields |
Inbound processing should fail closed when required headers are missing or unexpected headers appear. A changed spreadsheet must not silently map into old CRM fields.
Model the batch as a state machine
Useful states include prepared, uploaded, result_received, schema_validated, reconciled, approved, applied and failed. Each transition records time, operator and evidence. A failed batch can be corrected and re-entered without creating a second set of CRM updates.
The applied transition should be idempotent: rerunning it with the same batch ID must not duplicate history records or overwrite a newer check.
Validate inbound Excel before reading business values
Confirm workbook identity, expected sheet, headers, row count, phone column type and batch association. Store the original file in restricted storage. Load a copy into staging and never parse phone numbers as floating-point values.
Then calculate return coverage, duplicate-key count and join success. Keep submitted and WhatsApp-mapped numbers separate. Route exceptions to an analyst queue rather than forcing updates.
Write to CRM as append-only observations
Prefer a child table such as number_check_observation over replacing the main phone field. Store batch ID, task, run date, source number, mapped number, returned fields, schema version and processing status. A current-view query can select the latest approved observation.
This design preserves history and allows field definitions to change. It also prevents an old batch from overwriting a newer result.
Separate deployment from activation
Applying technical fields to CRM does not make records campaign-ready. A separate activation view should enforce channel permission, purpose, suppression and freshness rules. This keeps the file integration focused on data processing rather than making hidden marketing decisions.
Operational controls for a production pipeline
- Role-based access to outbound TXT and inbound Excel storage
- Checksums and immutable batch IDs
- Schema tests committed with the transformation code
- Dual approval for broad profile-related tasks
- Retention and deletion jobs for transient files
- Alerts for missing results, weak join rates and duplicate application
- A rollback view based on batch ID rather than destructive edits
When a direct API would become necessary
An API may be justified when latency, volume or system-to-system frequency cannot be served by controlled files. Before requesting one, measure actual batch turnaround, operator effort, error rate and update frequency. Many teams discover that a scheduled file interface meets a daily or weekly decision more transparently.
Include security review, authentication ownership and retry behavior in that calculation.
The important engineering principle is not whether the transport is HTTP or Excel. It is whether the integration has a versioned contract, deterministic states, reproducible transformations and safe failure behavior. Build those now, and a future API can replace the transport without rewriting the governance model.
