Edit:

I went ahead and published a complete overview of the tables here: https://www.newsletterstudio.org/documentation/package/10.0.0/develop/database-tables/

Hi!

Thanks for letting me know what version you're on, I'll try to outline some information about relevant tables in the database here. We do not see the database design as a contract so we might add or change tables/columns in future releases. However, we always try to "append only".

First of all, most tables have a column for WorkspaceKey this is used when there are multiple Workspaces in use. These are stored in the nsWorkspace table. User access to workspaces are stored in nsWorkspaceAccess.

nsMailingList

Contains the built-in mailing lists for the package.

nsRecipient

Stores recipients that are added to any of the built-in mailing lists. If you're sending to Umbraco Members this table does not hold any data about them.

nsSubscription

A relation table between nsMailingList and nsRecipient, holds information about which recipient is subscribed (or unsubscribed) from which list.

nsCampaignEmail

This table contains all campaigns created. The status field indicates if it is a draft, if it sending is in progress, or if it's sent.

Statuses:

• 0 = Created / Draft
• 1 = Initializing (send out engine is creating nsTrackingCampaignEmail-rows for the campaign)
• 2 = Sending
• 3 = Sent
• 4 = Error

nsTrackingCampaignEmail

This table acts as a queue when sending a campaign email and also as a log for statistic reports. It will contain unique recipients for the campaign and any bounce information for that recipient.

Be aware that it's technically possible that one recipient (unique email) is represented in multiple "Recipient List Providers" used when sending the email. The send-out engine will make sure that only one unique recipient for each campaign is stored here.

Statuses:

• 0 = Available (sending not started to this recipient)
• 1 = InProgress
• 2 = Error
• 3 = Sent

nsTrackingCampaignEmailInteraction

This table stores any interactions from the recipient like open & click. It also stores a URL when applicable (e.g. which URL was clicked).

Tracking Types:

• 1 = Open
• 2 = Click
• 3 = Unsubscribe Click
• 4 = Unsubscribe Completed
• 5 = View in browser

Transactional

The tables nsTransactionalEmail, nsTrackingTransactional, nsTrackingTransactionalEmail, and nsTrackingTransactionalInteraction follow the same pattern as the tables for campaigns.

Feel free to get back if you have any questions about this.