Audience Database Consolidation — Technical Documentation

Last updated: March 2026 Track: A (Database Consolidation) Status: Planned

---

Table of Contents

1. Overview
2. Current Architecture
3. Target Architecture
4. Repository Map
5. Field Mapping
6. Phase A1 — Make AudienceHeader the Authoritative Source
7. Phase A2 — Redirect Sodastream Writes Through Data API
8. Phase A3 — Decommission SoProQueueAudience
9. Verification
10. Risks & Mitigations

---

1. Overview

Audience management currently operates across a dual-database architecture: audience definitions live in both SoProQueueAudience (SoProQueue DB, owned by Sodastream) and AudienceHeader (Audience DB, owned by sopromasterdata). A one-way daily sync (SoproSyncService, 4 AM UTC) copies 16 fields from SoProQueueAudience → AudienceHeader, creating up to 24 hours of data staleness.

This document describes the three-phase plan to consolidate to a single source of truth (AudienceHeader), then decommission SoProQueueAudience entirely.

This track is independent of the UI migration (Track B). No frontend work is required.

---

2. Current Architecture

Problems

Problem	Impact
Dual source of truth	SoProQueueAudience and AudienceHeader can diverge
One-way sync only	Settings changes in Sodastream take up to 24 hours to reach Data API
Stale data	Import consumer reads from SoProQueueAudience; Data API may show different values
Tight coupling	Import process depends on direct SoProQueue DB access for config
Redundant storage	All 16 synced fields exist in both databases

---

3. Target Architecture

After consolidation:

• AudienceHeader is the only audience definition store
• SoProQueueAudience table is dropped
• SoproSyncService audience sync is removed (campaign/client sync remains)
• Import consumer reads config from Data API instead of SoProQueue DB

---

4. Repository Map

Repository	Layer	Key Files	Changes
sopromasterdata	Admin Controller	API/SoProMasterDBAPI/Controllers/Admin/AudienceController.cs	Add UpdateAudienceSettings, GetAudienceConfig endpoints
sopromasterdata	Service	AudienceServices/AudienceHeaderService.cs	Add UpdateSettingsAsync()
sopromasterdata	Service Interface	AudienceServices/Interfaces/IAudienceHeaderService.cs	Add UpdateSettingsAsync() signature
sopromasterdata	SodaStream Service	SodaStreamServices/SodaStreamService.cs	Add UpdateSoProQueueAudienceAsync() (A1), remove it (A3)
sopromasterdata	Sync Service	AudienceServices/SoproSyncService.cs	Add delta metrics (A1), remove audience sync (A3)
sopromasterdata	DTO	Shared/SoProMasterDBAdapter/Models/Requests/UpdateAudienceSettingsRequest.cs	New file (A1)
sopromasterdata	DTO	Shared/SoProMasterDBAdapter/Models/Responses/AudienceConfigResponse.cs	New file (A3)
sopro-sodastream	Controller	Web/Web/Controllers/SoProMasterSearchController.cs	Route settings writes through Data API (A2)
sopro-sodastream	Entity	SoProQueueEntities/SoProQueueAudience.cs	Delete (A3)
sopro-sodastream-core	Consumer	sopro-sodastream-core/SoProQueueConsumer/Jobs/Audience/TaskAudienceBuilderAutomationsDaily.cs	Switch config source to Data API (A3)
sopro-sodastream-core	Entity	SoProQueue/Data/SoProQueueEntities/SoProQueueAudience.cs	Delete (A3)

---

5. Field Mapping

The 16 fields currently synced by SoproSyncService.BuildFieldsToUpdate():

#	SoProQueueAudience Field	AudienceHeader Field	Type	Notes
1	DailyImportVolume	ContactsPerDay	int	Daily import target
2	ProspectsPerCompanyPerDay	ContactsPerCompanyPerDay	int	Per-company cap
3	IsAutomaticImport	IsEmailFindingActive	bool	Auto email-finding toggle
4	IsProspectVisisble	IsCampaignAudienceActive	bool	Campaign visibility flag
5	IsAudienceActive	IsAudienceEnabled	bool	Master active toggle
6	IsAdSync	IsAdSync	bool	Ad sync flag
7	IsAdSyncActive	IsAdSyncActive	bool	Ad sync active flag
8	EmailProfileIds	EmailProfileIds	string	Comma-separated IDs
9	EmailProfileNames	EmailProfileNames	string	Display names
10	DeletedEmailProfileIds	DeletedEmailProfileIds	string	Removed profile IDs
11	AudienceName	Name	string	Display name
12	AudienceDuplicateProspectDays	AudienceDuplicateProspectDays	int?	Dedup window in days
13	Option	Option	int?	Audience option flag
14	CampaignId	CampaignId	int	Linked campaign
15	ApproachId	ApproachId	int?	Extracted from Parameters JSON
16	Parameters (JSON)	N/A	string	Contains Messaging/Approach config

Source: sopromasterdata/AudienceServices/SoproSyncService.cs → BuildFieldsToUpdate() method

---

6. Phase A1 — Make AudienceHeader the Authoritative Source

Goal: All writes go through Data API first. Reverse sync keeps SoProQueueAudience updated immediately for backward compatibility.

6.1 New API Endpoint

PUT /admin/Audience/UpdateAudienceSettings

Request DTO: UpdateAudienceSettingsRequest

Field	Type	Description
AudienceId	Guid	Target audience
ContactsPerDay	int?	Daily import volume
ContactsPerCompanyPerDay	int?	Per-company cap
IsEmailFindingActive	bool?	Auto email-finding
IsCampaignAudienceActive	bool?	Campaign visibility
IsAudienceEnabled	bool?	Master active toggle
IsAdSync	bool?	Ad sync flag
IsAdSyncActive	bool?	Ad sync active
EmailProfileIds	string?	Email profile IDs
EmailProfileNames	string?	Profile display names
DeletedEmailProfileIds	string?	Removed profiles
Name	string?	Audience name
AudienceDuplicateProspectDays	int?	Dedup window
Option	int?	Audience option
ApproachId	int?	Approach ID

Nullable fields — only provided fields are updated (PATCH semantics).

6.2 Service Flow

6.3 Reverse Sync Implementation

New method in SodaStreamService:

Task UpdateSoProQueueAudienceAsync(Guid audienceId, UpdateAudienceSettingsRequest request);

Maps AudienceHeader field names back to SoProQueueAudience field names (inverse of the 16-field mapping), then updates the SoProQueue DB record directly.

6.4 Forward Sync Safety Net

Keep SoproSyncService.SyncAudiencesFromSodastreamAsync() running daily. Add logging to track how many fields differ — should trend to zero as all writes go through Data API.

6.5 File Modifications

File	Action
sopromasterdata/.../Admin/AudienceController.cs	Add UpdateAudienceSettings action
sopromasterdata/AudienceServices/AudienceHeaderService.cs	Add UpdateSettingsAsync()
sopromasterdata/AudienceServices/Interfaces/IAudienceHeaderService.cs	Add interface method
sopromasterdata/SodaStreamServices/SodaStreamService.cs	Add UpdateSoProQueueAudienceAsync()
sopromasterdata/SodaStreamServices/Interfaces/ISodaStreamService.cs	Add interface method
sopromasterdata/Shared/.../UpdateAudienceSettingsRequest.cs	New file — request DTO

---

7. Phase A2 — Redirect Sodastream Writes Through Data API

Goal: Sodastream settings edits call Data API first. Data API handles both AudienceHeader update and reverse sync to SoProQueueAudience. Sodastream reads unchanged.

7.1 Sodastream Controller Changes

7.2 Delta Monitoring

Add metric tracking to SoproSyncService:

Logger.LogInformation("🔍 Audience sync delta: {AudienceId} has {DeltaCount} field differences", audienceId, deltaCount);

When DeltaCount is consistently 0 for all audiences, Phase A2 is validated — all writes are flowing through Data API.

7.3 File Modifications

File	Action
sopro-sodastream/.../SoProMasterSearchController.cs	Route settings writes through Data API
sopromasterdata/AudienceServices/SoproSyncService.cs	Add delta count logging/metrics

---

8. Phase A3 — Decommission SoProQueueAudience

Goal: Remove SoProQueueAudience entirely. All consumers read from AudienceHeader via Data API.

8.1 New Config Endpoint

GET /api/Audience/GetAudienceConfig/{audienceId}

Response DTO: AudienceConfigResponse

Field	Type	Description
AudienceId	Guid	Audience identifier
DailyImportVolume	int	Mapped from ContactsPerDay
ProspectsPerCompanyPerDay	int	Mapped from ContactsPerCompanyPerDay
IsAutomaticImport	bool	Mapped from IsEmailFindingActive
EmailProfileIds	string	Comma-separated profile IDs
AudienceDuplicateProspectDays	int?	Dedup window
ApproachId	int?	Approach ID
IsActive	bool	Combined from IsAudienceEnabled + IsCampaignAudienceActive

Design note: This DTO uses SoProQueueAudience-compatible field names to minimize changes in TaskAudienceBuilderAutomationsDaily.

8.2 Import Consumer Switch

Feature flag: UseDataApiForAudienceConfig in TaskAudienceBuilderAutomationsDaily. When true, reads from Data API. When false, reads from SoProQueueAudience (existing behavior). Allows instant rollback.

8.3 Removal Checklist

Step	File	Action
1	sopro-sodastream-core/.../TaskAudienceBuilderAutomationsDaily.cs	Replace SoProQueueAudience reads with Data API calls
2	sopromasterdata/SodaStreamServices/SodaStreamService.cs	Remove UpdateSoProQueueAudienceAsync(), CreateSoProQueueAudienceAsync()
3	sopromasterdata/SodaStreamServices/Interfaces/ISodaStreamService.cs	Remove interface methods
4	sopromasterdata/AudienceServices/AudienceHeaderService.cs	Remove reverse sync calls from UpdateSettingsAsync() and CreateFromFilterDataAsync()
5	sopromasterdata/AudienceServices/SoproSyncService.cs	Remove SyncAudiencesFromSodastreamAsync() (keep campaign/client sync)
6	sopro-sodastream/SoProQueueEntities/SoProQueueAudience.cs	Delete entity file
7	sopro-sodastream-core/.../SoProQueueEntities/SoProQueueAudience.cs	Delete entity file
8	Both repos	Remove DbSet references, update DbContext
9	SoProQueue DB	Drop SoProQueueAudience table (after verification period)

8.4 Grep Verification

After all code changes, run across all repos:

grep -r "SoProQueueAudience" --include="*.cs" .

Expected: zero results (excluding migration history files).

---

9. Verification

Phase A1 Verification

1. Create audience via POST /admin/Audience/CreateAudienceFromSearch
   • Verify both AudienceHeader and SoProQueueAudience records created
2. Update settings via PUT /admin/Audience/UpdateAudienceSettings
   • Verify AudienceHeader updated
   • Verify SoProQueueAudience updated immediately (reverse sync)
3. Run import via TaskAudienceBuilderAutomationsDaily
   • Verify import uses correct settings from SoProQueueAudience

Phase A2 Verification

1. Edit settings in Sodastream UI
   • Verify Data API UpdateAudienceSettings called (HTTP logs)
   • Verify AudienceHeader updated
   • Verify SoProQueueAudience reverse-synced
2. Run SoproSyncService manually
   • Verify zero field deltas for all audiences

Phase A3 Verification

1. Enable UseDataApiForAudienceConfig feature flag
2. Run import via TaskAudienceBuilderAutomationsDaily
   • Verify config read from Data API (HTTP logs)
   • Verify import produces identical results
3. Grep all repos for SoProQueueAudience — zero references
4. Monitor imports for 1 week before table drop

---

10. Risks & Mitigations

Risk	Impact	Mitigation
Concurrent edits during A2 (both UIs editing)	Last-write-wins, potential data loss	Add LastModified timestamp to AudienceHeader for conflict detection
A3 import switch breaks production	Import pipeline fails, no new prospects	Feature-flag UseDataApiForAudienceConfig for instant rollback
Data API downtime during A3	Import consumer can't read config	Add retry policy with exponential backoff; consider local config cache
Incomplete field mapping	Import uses wrong settings	Validate all 16 fields map correctly in A1; integration test the config endpoint
SoproSyncService removal breaks other sync	Campaign/client sync lost	Only remove audience sync; campaign and client sync remain untouched

---

Integration Points

System	Integration	Phase
TaskAudienceBuilderAutomationsDaily	Config source switch	A3
SoProMasterSearchController	Write redirect	A2
SoproSyncService	Delta monitoring → removal	A1–A3
AudienceHeaderService.CreateFromFilterDataAsync	Already creates both records	Existing
QueueAudienceImportAsync	Read source switch	A3