Checking and cleaning the client database for advertising mailings with deduplication and contact validation (CRM Bitrix24 + PostgreSQL)

Initial situation
There is a client base for advertising communications (email + SMS + WhatsApp) from 3 sources: Bitrix24 (leads and contacts), downloads from the Call Center (CSV) and website forms (Webhook). Over the past year, delivery failures, spam complaints, and the share of duplicates have increased significantly, which is why advertising campaigns become more expensive and the domain's reputation sag. Now the database is about 420,000 records in total, there are fewer real unique contacts, but there is no exact figure.

Technical environment and data
- Bitrix24 (cloud), access via REST API
- PostgreSQL 14 (on our server) - you can upload intermediate tables there
- Initial CSV/XLSX uploads, different encodings and formats
- Fields: full name, phone(s), email(s), city, date of birth (often empty), sources/tags, dates of last activity, consent to communications (partial)

What to do
1) Conduct a data quality audit and draw up a short report: the proportion of duplicates, empty/broken fields, consent conflicts, suspicious records (service/test), typical causes of problems.
2) Build a reproducible cleaning pipeline in PostgreSQL (SQL scripts + instructions), which:
- normalizes phones to E.164 (RF priority, but KZ/BY can be found)
- validates email (syntax, prohibited/test domains, disposable domains according to the list), marks risky addresses
- finds and merges duplicates using several strategies: exact phone/email match, similar full name + phone, similar email (taking into account dots/plus aliases for popular domains), matching several attributes
- resolves attribute conflicts when merging according to rules (for example, priority of a more recent source and records with the latest activity)
- generates a “golden” client record and a correspondence map source_id -> master_id
3) Prepare the final clean segment for advertising mailings: a list of unique contacts with suitability flags by channel (email_ok, sms_ok, wa_ok), reason for exclusion and consent mark.
4) Prepare a file/upload for uploading back to Bitrix24: which contacts to merge/tag, which fields to update, which records to archive/delete (without actual deletion - only a list of actions).

Restrictions
- Original personal data cannot be transferred to third parties and uploaded to external SaaS for verification. Local tools and offline lists are allowed.
- You cannot send bulk emails/messages. Domain checking is allowed only in the form of a passive MX/NS/DNS check for a limited number of requests or on a sample basis.
- Reproducibility is required: all cleaning steps must be repeated on new uploads without manual “peeping” and editing in Excel.

Specific result
- SQL scripts (or dbt project) and README with launching the pipeline on PostgreSQL
- Before/after data quality report (tables with metrics)
- Final master_contacts table and mapping table for communication with source records
- Final CSV upload: contacts ready for distribution + reasons for exceptions
- Upload/list of actions for Bitrix24 for merging/updating

Measurable acceptance criteria
- The share of duplicates for phone/email keys after processing: no more than 0.5% (according to internal metrics, we will agree on the formula at the beginning)
- At least 95% of phones are converted to E.164 or marked with a specific reason (short number, not enough digits, business, not RF/KZ/BY, etc.)
- At least 98% of emails either pass syntactic validation or have a reason for rejection (invalid_format, disposable_domain, test_domain, empty)
- For each source record there is either a master_id or an exception reason (for example, no_contacts)
- The pipeline starts on a fresh upload and gives results without manual edits

Additionally
- It is preferable to use Python only if it is needed for fuzzy-matching/phonetic, but the main framework should be in SQL (ideally with pg_trgm/unaccent extensions).