Environment Setup — Technical Documentation

Also known as: Multi-environment isolation, staging/testing infrastructure, environment separation Last updated: February 2026

---

Table of Contents

1. Overview
2. Architecture
3. Current State
4. Target State
5. Repository Map
6. Database Topology
7. Connection Resolution Flow
8. Key Vault Configuration
9. API Deployment
10. AudienceConsoles Deployment
11. Sodastream Wiring
12. data-admin Configuration
13. Queue Infrastructure
14. Configuration Reference
15. Integration Points

---

1. Overview

Sopro Data currently runs a single production environment — one Data API instance, one set of databases — shared by all three Sodastream environments (production, staging, testing). RequestedBySystem (an integer enum) is the only discriminator between environments at the data level.

The goal is full environment isolation: each Sodastream environment gets its own Data API deployment, its own databases, its own Key Vault, and its own queue connections. No code changes are required — the existing Settings DB + Key Vault configuration pattern already supports multi-environment deployments.

---

2. Architecture

Current (Single Environment)

Target (Isolated Per Environment)

---

3. Current State

Component	Production	Staging	Testing
Sodastream	sopro-sodastream (live)	sodastreamfresh (staging)	testing instance
Data API	api.soprodata.com (single)	← same	← same
Audience DB	single shared DB	← same	← same
Master DB	single shared DB (deprecated)	← same	← same
ClickHouse	single shared DB	← same	← same
Key Vault	single vault	← same	← same
Azure Queues	SoProQueueStorage	SoProQueueStagingStorage (exists)	not configured
AudienceConsoles	Environment.txt = Production	ConsumerConsoleStaging (legacy)	not deployed
Data separation	RequestedBySystem = 1	RequestedBySystem = 3	RequestedBySystem = 6

Key problems:

• All environments write to the same databases — a staging test can corrupt production data
• Campaign ID collisions — Sodastream campaign 4092 in staging is a different campaign than 4092 in production, but they share the same Audience DB table
• No safe testing — automation tests run against production data
• No feature verification — can't test staging API changes against staging Sodastream end-to-end

---

4. Target State

Component	Production	Staging	Testing
Sodastream	sopro-sodastream (live)	sodastreamfresh (staging)	testing instance
Data API	api.soprodata.com	api-staging.soprodata.com	api-testing.soprodata.com
Audience DB	audience_prod	audience_staging	audience_testing
MarketMap DB	marketmap_prod	marketmap_staging	marketmap_testing
Logging DB	logging_prod	logging_staging	logging_testing
Hangfire DB	hangfire_prod	hangfire_staging	hangfire_testing
Data Verification DB	verification_prod	verification_staging	verification_testing
ClickHouse	soprodata_prod	soprodata_staging	soprodata_testing
Key Vault	sopro-data-keyvault	sopro-data-keyvault-staging	sopro-data-keyvault-testing
Azure Queues	SoProQueueStorage	SoProQueueStagingStorage	SoProQueueTestingStorage
AudienceConsoles	Environment.txt = Production	Environment.txt = Staging	Environment.txt = Testing
Settings DB	production Settings DB	staging Settings DB	testing Settings DB

Databases NOT replicated (deprecated/not needed):

• Master DB — deprecated; staging/testing running without it validates the system has no remaining dependency
• Sphinx/Manticore — deprecated search index; same rationale
• Crunchbase — external reference data; not needed for testing

---

5. Repository Map

Repository	Role in Environment Setup	Changes Needed
sopromasterdata	Data API, Hangfire, AudienceConsoles, Settings DB resolution	None — same codebase, different config per deployment
sopro-sodastream	Calls Data API via SoProMasterDBAdapter	Fix hardcoded localhost:5000 in SimpleInjectorInitializer.cs; update SoProMasterDBAdapterClientCredentials per environment
data-admin	React frontend, connects to API	Add .env.staging pointing to staging API URL
sopro-automation-tests	E2E/API tests	Configure Data API tests to run against testing environment

---

6. Database Topology

Databases Per Environment (Clone Schema Only, No Data)

Database	Purpose	Key Tables/Entities
Audience	Core operational data	AudienceHeader, AudienceContact, AudienceImport, SoproCampaigns, SoproClients, AudienceStatistics
MarketMap	Market segmentation data	MAP definitions, coverage data
Logging	Application logs	Serilog sink tables
Hangfire	Background job state	Job, State, Server, Set, Hash (Hangfire schema)
Data Verification	Email verification jobs	VerificationJob, VerificationResult
ClickHouse	Analytics, prospect sync, reportings	Audience, AudienceDetails, SoproProspect, Reportings

Databases Not Cloned

Database	Reason
Master DB	Deprecated — not replicating validates no dependency
Sphinx/Manticore	Deprecated search index
Crunchbase	External reference data, not needed for testing

---

7. Connection Resolution Flow

All Sopro Data applications resolve database connections dynamically at startup. No connection strings are hardcoded in appsettings files.

Key files:

• API/SoProMasterDBAPI/Configuration/ConnectionOptions.cs — holds all resolved connection strings as properties (Master, Audience, Settings, MarketMap, Logging, Reporting, DataVerification, Clickhouse, Sphinx, Sopro, SoproQueue, etc.)
• API/SoProMasterDBAPI/Extensions/ApiServiceExtensions.cs — registers DbContexts and Dapper services with environment-specific connections
• AudienceConsoles/Extensions/ServiceCollectionExtensions.cs — same pattern for console apps, reads Environment.txt or ASPNETCORE_ENVIRONMENT

---

8. Key Vault Configuration

Each environment gets its own Azure Key Vault to prevent cross-environment secret access.

Environment	Key Vault Name	Stores
Production	sopro-data-keyvault	Production DB connections, API keys, JWT signing key, queue connections
Staging	sopro-data-keyvault-staging	Staging equivalents of all above
Testing	sopro-data-keyvault-testing	Testing equivalents of all above

Existing pattern: AudienceConsoles/Extensions/ServiceCollectionExtensions.cs already supports environment-specific Key Vault sections (KeyVault, KeyVaultTesting, KeyVaultExternal).

Each environment's appsettings.{Environment}.json points to its own Key Vault URI:

{
  "KeyVault": {
    "VaultUri": "https://sopro-data-keyvault-staging.vault.azure.net/"
  }
}

---

9. API Deployment

The Data API runs Hangfire in-process — deploying a staging API automatically creates staging Hangfire workers.

Per-environment deployment:

Setting	How It Differs
SoProMasterSettingsConnection	Points to environment-specific Settings DB
Key Vault URI	Points to environment-specific Key Vault
ASPNETCORE_ENVIRONMENT	Production / Staging / Testing
Hangfire DB	Resolved from Settings DB → isolated job state
HangfireComputers table	Controls per-machine worker count in each environment's Settings DB

Recurring Hangfire jobs (auto-scheduled at startup in Program.cs):

• ScheduleEmailFindingJobsRecurringJob
• FetchLinkedInAccountMasterConnections
• CalculateTrustScore
• ScheduleRecurringCampaignStatisticsRefreshJob
• ScheduleDailySyncJob

All jobs process data from whichever database their environment's Settings DB points to — no code changes needed.

---

10. AudienceConsoles Deployment

Environment selection: Environment.txt file (or ASPNETCORE_ENVIRONMENT env var) at the console binary's location.

Config chain: Environment.txt → appsettings.{Env}.json → Key Vault → Settings DB → resolved connections

Precedent: ConsumerConsoleStaging project already exists with appsettings.Staging.json and appsettings.StagingExternal.json.

Console Inventory

#	Console	Type	Environment Relevance
1	EmailFindingConsole	Queue	Must process environment-specific queue
3	RecalculateCampaignStatisticsConsole	Queue	Writes stats to environment's Audience DB
97	AudienceFetchProspectsConsole	Queue	Fetches prospects into environment's Audience DB
99	ProcessDataVerificationConsole	Queue	Processes verification jobs in environment's Verification DB
100	SoproSyncConsole	Queue	Critical — ConnectionOptions.Sopro must point to the correct Sodastream SQL Server (staging vs production)
200	SyncAudienceChangesToClickhouseConsole	Sync	Syncs to environment's ClickHouse DB
201	SyncDetailsChangesToClickhouseConsole	Sync	Syncs to environment's ClickHouse DB
209	SyncSoproProspectsToClickhouseConsole	Sync	Incremental sync to environment's ClickHouse DB
302	ProcessCompanySuitabilityConsole	Queue	Processes suitability in environment's Audience DB
303	AutoRefreshCampaignAudiencesConsole	Queue	Auto-refresh in environment's Audience DB

---

11. Sodastream Wiring

Each Sodastream environment stores its Data API target in the SoProMasterDBAdapterClientCredentials table.

Column	Production	Staging	Testing
ApiUrl	https://api.soprodata.com	https://api-staging.soprodata.com	https://api-testing.soprodata.com
ApiKey	production key	staging key	testing key
RequesterEnum	1 (Sodastream)	3 (StagingSodastream)	6 (SodastreamTesting)

Required fix: Sodastream/Web/Web/App_Start/SimpleInjectorInitializer.cs currently hardcodes credentials.ApiUrl = "http://localhost:5000", overriding the database value. This line must be removed so the URL is read from the SoProMasterDBAdapterClientCredentials table.

// REMOVE THIS LINE:
credentials.ApiUrl = "http://localhost:5000";

15 adapters route through ApiControllers which gets its URL from this single credential record: AuthAdapter, AudienceAdapter, CompanyAdapter, ProspectAdapter, MarketMapAdapter, UtilsAdapter, LocationAdapter, SalesAgentAdapter, UniqueDomainAdapter, TechnologyCompanyEmailsAdapter, ListBuildAccountAdapter, DataNotificationAdapter, DataVerificationAdapter, HostMachinesAdapter, GeneralAdapter.

---

12. data-admin Configuration

File	API URL	Usage
.env.development	https://localhost:5001/api	Local development
.env.staging (new)	https://api-staging.soprodata.com/api	Staging testing
.env.production	https://api.soprodata.com/api	Production build

When embedded in Sodastream's iframe, the frontend inherits the environment from the JWT token — the Sodastream environment determines which API the embedded app talks to.

Sodastream's AudiencesController.BuildEmbedUrl() needs a configurable dataAdminBaseUrl (currently hardcoded to http://localhost:5173) — sourced from Web.config appSetting or the SoProMasterDBAdapterClientCredentials table.

---

13. Queue Infrastructure

Queue Connection	Environment	Config Key
SoProQueueStorage	Production	Existing
SoProQueueStagingStorage	Staging	Existing (pattern)
SoProQueueTestingStorage	Testing	New

Queue-based consoles (BaseQueueConsole<T>) resolve their queue connection string from the environment's Key Vault / Settings DB. Staging and testing consoles read from isolated queues — no cross-environment message leakage.

---

14. Configuration Reference

appsettings Keys

Key	Purpose	Per-Environment
SoProMasterSettingsConnection	Bootstrap connection to Settings DB	Yes
KeyVault:VaultUri	Azure Key Vault URI	Yes
ConnectionOptions:*	All database connections (resolved from Settings DB)	Yes (via Settings DB)
SoProQueueStorage	Production Azure Storage Queue connection	Yes (via Key Vault)
SoProQueueStagingStorage	Staging Azure Storage Queue connection	Yes (via Key Vault)
JwtSettings:Key	JWT signing key	Yes (via Key Vault)
SoproDataAuth:SoproDataApiKey	API key for X-SoproDataApiKey validation	Yes (via Key Vault)

Settings DB — Connections Table

Column	Purpose
Name	Connection identifier (e.g. Master, Audience, Clickhouse)
ConnectionType	Environment discriminator (Internal, External)
ConnectionString	Encrypted connection string (decrypted via SimpleStringCipher)

---

15. Integration Points

Each arrow stays within its environment boundary. Production Sodastream → Production API → Production databases. Staging Sodastream → Staging API → Staging databases. No cross-environment communication.