Recipient Lists (TQ-113)

Overview

Recipient Lists are first-class, system-wide contact collections used to populate marketing broadcasts. They replace the per-broadcast recipient construction and the metadata-only audience segments that preceded them.

A recipient list is a reusable, named snapshot of contacts (e.g. "VIP Customers", "Summer 2026 prospects") that can be populated from CRM, imported from Excel, or edited manually. Activities select one or more lists at compose time; recipients are copied into the broadcast with append-plus-dedupe semantics and remain editable per-activity afterwards.

Motivation

Before TQ-113, recipients lived only inside a broadcast activity, and audience segments existed as metadata attached to activities without actually filtering anything. Building a broadcast required running CRM resolution from the activity panel every time, and the same contact list could not be reused across campaigns.

Recipient lists give marketing staff a stable object they can curate once and reuse across any number of broadcasts.

Data Model

Tables

Two tables in the nts schema, created by migration 0071-recipient-lists.sql.

nts.mkt_recipient_list — one row per list - listid (PK) - name, description - channel (WHATSAPP or EMAIL) - active (soft-delete flag) - member_count (denormalized for list UI) - created, created_by, modified, modified_by

nts.mkt_recipient_list_member — one row per contact within a list - memberid (PK) - listid FK (ON DELETE CASCADE) - customerid (nullable link to CRM) - recipient_name, phone, email, language - created

Dedupe Rules

• Within a list: partial unique indexes on (listid, phone) and (listid, email) (where the value is non-null). A given phone or email can exist at most once per list.
• Across lists: overlap is allowed. The same contact can appear in many lists.
• Against opt-outs: lists are snapshots — opt-outs are not applied at list population time. They are re-checked at broadcast send time, so a contact can opt out after a list is built without needing to rebuild the list.

Dropped tables

nts.mkt_audience and nts.mkt_activityaudience are superseded. The schema drop is deferred to a follow-up migration (0072) so existing environments can be audited before the cutover. No code references remain in the active UI or broadcast flow.

Workflow

┌──────────────────────┐
│  Recipient Lists     │  (hub: recipient-lists.html)
│  (create / edit)     │
└──────────┬───────────┘
           │
           ▼
┌──────────────────────┐
│  Populate members    │  (spoke: recipient-list-edit.html)
│  • From CRM          │
│  • Excel import      │
│  • Manual add/edit   │
└──────────┬───────────┘
           │
           ▼
┌──────────────────────┐
│  Broadcast compose   │  (mktplan activity panel OR broadcast-compose.html)
│  Pick one or more    │──► copy-to-broadcast (append + dedupe + opt-out flag)
│  lists → recipients  │
└──────────────────────┘

List Population

Source	Semantics
CRM populate	Clears existing members, fetches CCustomer rows, applies channel + doNotEmail + phone-prefix filters. Dedupes by normalized contact value. Emails lowercased, phones normalized via resolveWhatsAppNumber().
Excel import	Upsert: existing members matched by phone (WhatsApp lists) or email (Email lists) are updated; new rows are inserted. Max 5000 rows, max 5 MB file.
Manual add/edit	Single-row operations; same validation (validateEmail, phone trim) and sanitization (sanitizePlainText on names) as import.

List → Broadcast Copy

copyListToBroadcast(listId, broadcastId) is the only supported path from a list to an activity's recipient table:

1. Broadcast must be in DRAFT or READY status.
2. Builds a dedupe set from existing broadcast recipients.
3. For each list member with a valid contact value, creates a CBroadcastRecipient. Opt-outs are checked per member; opted-out contacts are inserted with deliveryStatus = "OPTED_OUT" rather than skipped, so staff can see that they were known-opted-out at copy time.
4. Updates broadcast.recipientCount and auto-promotes DRAFT → READY once recipients exist.

Multiple lists can be copied sequentially; dedupe state accumulates across calls within one compose action.

Backend Architecture

Layer	Class / File	Responsibility
Canonical	CRecipientList, CRecipientListMember	API-facing entities
JPA	MktRecipientlistEntity, MktRecipientlistmemberEntity	Hibernate persistence (registered in NTSDBSession)
Mapping	config/entities/marketing-entities.xml	Canonical-to-native field mapping
Services	config/nts-client.xml — read/save/delete services for list and member
Business	MarketingFacade (Recipient List Management section)	CRUD, CRM populate, copy-to-broadcast, Excel import/export, count refresh
Excel	RecipientListExcelService	Workbook generation and parsing; delegates persistence to facade
API	MarketingApi (/list/* endpoints, error codes MKT0500–MKT0512)	Request parsing and response formatting only

Facade Entry Points

• listRecipientLists(channel) — active lists, optionally filtered by channel
• getRecipientList(listId) / saveRecipientList(list) / deleteRecipientList(listId) — list CRUD (delete is soft)
• listMembers(listId, offset, limit) / countMembers(listId) / listAllMembers(listId) — member reads
• addMember / updateMember / removeMember / clearMembers — member mutations
• populateListFromCRM(listId) — clear-and-refill from CustomerFacade
• copyListToBroadcast(listId, broadcastId) — append-plus-dedupe copy
• importListFromExcel(listId, bytes) / exportListToExcel(listId) — Excel round-trip

Sanitization & Validation

All write paths converge on writeMemberEntity() / updateMemberEntity(), which apply:

• sanitizePlainText() on recipientName
• trim() + empty-to-null on phone
• trim() + toLowerCase() on email

Validation (validateEmail, validateTeamMemberIdentity) runs in the public facade methods before reaching these helpers.

API Surface

All endpoints under /tlinq-api/marketing/list/. See doc/api/marketing.md for full request/response schemas.

Endpoint	Auth	Notes
list/list	agent,admin	List active lists, optional channel filter
list/read	agent,admin	Single list by listId
list/save	agent,admin	Create or update
list/delete	admin	Soft delete (active = false)
list/members	agent,admin	Paginated (offset, limit)
list/member/add	agent,admin	Single member insert
list/member/update	agent,admin	Partial update (only keys present in request)
list/member/remove	agent,admin	Single member delete
list/members/clear	agent,admin	Remove all members
list/populate-from-crm	agent,admin	Clear-and-refill from CRM
list/copy-to-broadcast	agent,admin	Append-plus-dedupe into activity recipients
list/export	agent,admin	Binary .xlsx (template if empty)
list/import	agent,admin	Base64 .xlsx upload, upsert by match key

The broadcast composer endpoint (marketing/broadcast/compose) accepts listIds: List<Integer> in place of the removed audienceIds.

Frontend

Admin pages

• tqweb-adm/recipient-lists.html + js/modules/recipient-lists.js — T2 hub (list table, filter by channel, create/edit/delete modal).
• tqweb-adm/recipient-list-edit.html + js/modules/recipient-list-edit.js — T3 spoke (member table, populate from CRM, Excel import/export, pagination at 100/page, add/edit/remove modal).

Entry points

• Engagement submenu in the global header (header_bootstrap.html) has a "Recipient Lists" link.
• Campaign toolbar in mktplan.html has a list-icon button that jumps to the hub.
• Broadcast composer (broadcast-compose.html) replaces the old audience checkboxes with a recipient list picker in Step 2. The estimated recipient count sums the memberCount of checked lists.
• Activity broadcast panel (inside mktplan.html) replaces the old "Build Recipient List" button with an "Add from List" modal that lets staff pick one or more lists and copy them into the activity's recipients.

Excel format

Columns: Name, Phone, Email, Language. Templates include two sample rows specific to the list's channel. Numeric cells (e.g. phones entered as numbers) are coerced to strings without a decimal.

Verification Checklist

1. Run migration 0071-recipient-lists.sql.
2. ./gradlew build — compiles clean.
3. Create a WhatsApp list from the hub page, set description.
4. Populate from CRM → verify members appear and count badge updates.
5. Add a member manually with all four fields; verify lowercase email and trimmed phone on the list.
6. Export to Excel, edit a row, re-import; verify upsert counts (inserted, updated).
7. Open a WhatsApp broadcast activity in mktplan.html; click "Add from List" and select the list. Verify recipients appear in the activity's recipient panel with OPTED_OUT status for any known opt-outs.
8. Compose a new broadcast via broadcast-compose.html, check two lists with overlapping members; verify dedupe (total count reflects union, not sum).
9. Delete the list; verify it disappears from the hub (soft-deleted).

References

• Implementation plan: ~/.claude/plans/modular-floating-wolf.md
• Migration: config/db-changes/0071-recipient-lists.sql
• Outstanding tech debt: doc/plans/marketing/recipient-lists-tech-debt.md