Article summary
A systems design that separates telecom reachability, WhatsApp registration and activity observations using queues, state transitions, a TXT/Excel batch contract and rollback.
A WhatsApp number-checking system should not have only “valid” and “invalid” buckets. Telecom status, WA/WS platform registration and platform activity are three questions answered by different sources and time windows. Compressing them into one status turns unknown into no, lets old activity overpower new permission and makes incidents impossible to roll back.
Draw three independent data flows first
| Layer | Question | Typical source | Lifetime |
|---|---|---|---|
| Telecom | Structure or network reachability | Numbering rules / approved check | Changes with network and phone |
| Platform registration | Was WhatsApp registration observed? | WS Registration | Bound to checked_at |
| Activity | What activity time/days were observed? | WS Activity | Short business-defined window |
The intake zone preserves evidence
raw_contacts stores phone_raw, source, collected_at and country_evidence. normalization_run creates phone_normalized, rule_version and format_exception. Never replace the source field in place, and never label a customer “empty number” because parsing failed.
A task queue decides what runs
Each queue item defines business_question, cohort, task_type, owner, purpose, expiry and stop_rule. If registration observation is the only requirement, do not run Activity or Full Format. Obtain telecom state separately only when the business needs it and has an appropriate basis.
Define a TXT/Excel batch contract
Export one phone per TXT line and generate a non-uploaded crosswalk containing batch_id, row_key, contact_id and phone_normalized. Hash the TXT and freeze input counts. When AIPUSH returns Excel, verify its batch identity and task schema before parsing any data.
Staging is the system’s fuse
No workbook updates contacts directly. Staging preserves raw cell, parsed value, error code and source row. Only rows passing schema, type, uniqueness and reconciliation checks enter observation tables. A failed batch can be removed as a unit instead of repaired by guessing in production.
Land each task in its own table
| Task | Core export | Suggested destination |
|---|---|---|
| WS Registration | Phone, registration result | platform_registration_observation |
| WS Activity | Phone, activity time, active days, mapped phone | platform_activity_observation |
| Telecom check | Defined by actual source | telecom_observation |
| Permission | Channel, purpose, opt-out, validity | contact_permission |
Use five states instead of Boolean
Each layer supports at least observed_yes, observed_no, unknown, input_error and system_error while retaining the raw response. Unknown never silently becomes no. System failure triggers bounded retry; input error returns to normalization. “Observed” in the state name reminds users that it is not permanent truth.
A mapped phone creates an edge, not an overwrite
Store a WS Activity mapped phone in identity_edge with source batch, confidence status and observation date. If one input has several returns or one return joins multiple contact_id values, quarantine the edge. The CRM primary phone remains unchanged until human resolution.
Generate downstream lists with a query
Do not export a permanent “good phone list.” A runtime view combines only the required layers and gives permission, opt_out, frequency_cap and legal_hold veto power. Registration and activity can contribute technical or ranking conditions; they never create authorization.
Retries, expiry and rollback
Use exponential backoff and a maximum attempt count for system_error. A repaired input_error creates a new batch instead of mutating the old file. Activity becomes stale at expiry. A schema incident rolls back by batch_id. Every action records the actor in an audit log.
Run failure drills before launch
Deliberately test duplicate phones, reordered Excel, missing and extra columns, blank lines, bad formats, mapping collisions and interrupted processing. Confirm that the system rejects the wrong file, recovers its queue, prevents duplicate writes and explains every current state. A happy-path demonstration proves little about operations.
What completed architecture looks like
Any downstream record traces to source, normalization rule, TXT batch, task, Excel row, observation time and permission decision. Deleting an expired observation does not damage customer history, and a whole import can be reversed. Those properties define a system; otherwise the organization merely has spreadsheets in motion.
