Skip to main content
Chargeworx uses a multi-database architecture with four separate SQL Server databases, each serving a specific purpose.

​
Database contexts

​
ChargeworxDbContext (Main database)

Primary application database containing:
  • Companies and projects: Company, CompanyProject, CompanyUser, CompanyProjectUser
  • Users and authentication: User, UserInvitation, WhitelistIp
  • Transactions: Transaction, TransactionItem, TransactionTag, TransactionTagValue
  • Payment processors: ProjectPaymentProcessor, ProjectPaymentProcessorSetting, PaymentProcessorResultCode
  • IdentityServer: IdentityServerClient, DataProtectionKeys
  • Background processes: BackgroundProcess
  • Import batches: ImportBatch, TransferredTransaction
  • Chargebacks: ChargebackDetail, PurchaseRefund
  • Credit card batches: CreditCardBatch, CreditCardUpdaterStatus
  • Transaction events: TransactionCreditCardEvent, TransactionPayflowEvent, TransactionCreditCardEventRawCybResponse
  • Response codes: AvsResponse, CvvResponse
Connection string: ChargeworxConnection

​
ChargeworxCreditCardDbContext (Credit card database)

Secure storage for sensitive payment information:
  • Credit cards: CreditCard, CreditCardNumber
  • Payment info: CompanyProjectCreditCardPaymentInfo
  • Billing agreements: CompanyProjectBillingAgreement
  • Addresses: Person, Address, PersonAddress
  • Account updater: CreditCardUpdaterHistory
Connection string: ChargeworxCreditCardConnection

​
ChargeworxKeyDbContext (Key database)

Isolated storage for encryption keys and sensitive settings:
  • Keys: Key (RSA keys for encryption)
  • Project keys: CompanyProjectKey
  • Settings metadata: SettingMetaData
Connection string: ChargeworxKeyConnection

​
ChargeworxReportDbContext (Report database)

Optimized for reporting and analytics:
  • Report transactions: ReportTransaction, ReportBaseTransaction, ReportNewestTransaction
  • Transaction tags: ReportBaseTransactionTagValue
  • Account updater history: AccountUpdaterHistoryRecord
Connection string: ChargeworxReportConnection

​
Data access pattern

​
Store pattern

Each entity has a corresponding store that encapsulates data access: 
public interface ICompanyStore
{
    Task<Company> GetByIdAsync(Guid id);
    Task<List<Company>> GetEntriesAsync(CompanyGetEntriesModel model);
    Task<Company> CreateAsync(Company company);
    Task UpdateAsync(Company company);
}
Stores are registered in DI and injected into managers.

​
Stored procedures

Complex queries use stored procedures for performance:
  • CompanyGetEntries: Paginated company listing with filters
  • TransactionGetDetails: Transaction details with related entities
  • CreditCardUpdaterGetReport: Account updater report generation
  • ReportTransactionCreateEntries: Bulk insert for reporting
Procedures are defined in Chargeworx.Api.Data/Procedures and executed via EF Core.

​
Migrations

Each database has its own migration history:
  • Chargeworx.Api.Data/Migrations/ChargeworxDb
  • Chargeworx.Api.Data/Migrations/ChargeworxCreditCardDb
  • Chargeworx.Api.Data/Migrations/ChargeworxKeyDb
  • Chargeworx.Api.Data/Migrations/ChargeworxReportDb
Run migrations with: 
dotnet ef database update --context ChargeworxDbContext
dotnet ef database update --context ChargeworxCreditCardDbContext
dotnet ef database update --context ChargeworxKeyDbContext
dotnet ef database update --context ChargeworxReportDbContext

​
Security considerations

  • Credit card database: Contains PCI-sensitive data, encrypted at rest
  • Key database: Isolated storage for RSA keys, restricted access
  • Main database: Contains business logic data, no raw credit card numbers
  • Report database: Denormalized data for analytics, no sensitive payment info
All connections use SQL Server authentication with encrypted connection strings.