Survey instruments
SurveyTypes, SurveyTemplates, SurveyQuestions, SurveyChoices, NPSCategories. Published instruments freeze wording, choices and the score basis.
- SurveyTypes
- SurveyTemplates
- SurveyQuestions
- SurveyChoices
- NPSCategories
Data model
Keep survey instruments, invitations, responses and follow-up evidence aligned.
28 tables · 57 relationships
How the model is organised
Campaign audiences and delivery
SurveyCampaigns, SurveyAudiences, SurveyAudienceRules, SurveyDeliveryRules, SurveyInvitations, DeliveryAttempts, OptOuts. Campaign approval, occurrence identity and current preferences govern each dispatch.
- SurveyCampaigns
- SurveyAudiences
- SurveyAudienceRules
- SurveyDeliveryRules
- SurveyInvitations
- DeliveryAttempts
- OptOuts
Identity and accepted answers
Customers, Employees, SurveyResponses, ResponseAnswers, SubmissionReceipts. Identity mirrors remain read-only; the private receipt ledger is unavailable to staff and anonymous projections.
- Customers
- Employees
- SurveyResponses
- ResponseAnswers
- SubmissionReceipts
Metric and cohort evidence
NPSScores, CSATScores, CESScores, Cohorts, CohortMembers, Trends, BenchmarkData. Each population retains its instrument, period and membership basis.
- NPSScores
- CSATScores
- CESScores
- Cohorts
- CohortMembers
- Trends
- BenchmarkData
Insight and action
Themes, Comments, Followups, SurveyABTests. Masked themes, attributable outreach and fixed-basis wording tests use the accepted response evidence.
- Themes
- Comments
- Followups
- SurveyABTests
Keys and references
Use same-tenant references throughout. Invitation uniqueness includes campaign, customer and occurrence; accepted submission is unique per invitation. Delivery attempts keep their original request identity. Published instruments and full analytical population keys prevent later edits from rewriting prior evidence. Private submission correlation is inaccessible to staff roles.
Migration order
- Configure authenticated staff and trusted customer mirrors; reconcile external identifiers and ownership.
- Load survey taxonomy, draft templates, questions and choices; publish tested immutable versions.
- Configure audiences, delivery policies, current suppression and independent campaign approval.
- Enable governed invitation dispatch and validated response capture with restricted receipts.
- Configure scoped follow-ups, masked themes, score populations, versioned cohorts and experiments.
Tables
28 tables
SurveyTypesThe canonical taxonomy of survey instruments the org runs.master
| Field | Type | Required | Details |
|---|---|---|---|
TypeCode | text | yes | e.g. CSAT-PostInteraction |
Name | text | yes | |
Category | select | yes | CSAT, NPS, CES, ProductFeedback, Custom |
TriggerStyle | select | yes | EventDriven, Scheduled, Manual |
DefaultScale | select | yes | Likert1to5, Likert1to7, NPS0to10, EmojiFace, CES1to7 |
AnonymousByDefault | boolean | NPS often false (attributable); ProductFeedback often true | |
Description | text | ||
Active | boolean | yes |
SurveyTemplatesReusable question banks. One template per SurveyType permaster
| Field | Type | Required | Details |
|---|---|---|---|
TemplateCode | text | yes | |
Name | text | yes | |
SurveyType | reference | yes | → SurveyTypes |
Version | number | yes | Increment on question changes |
ScaleStyle | select | yes | Likert1to5, Likert1to7, NPS0to10, EmojiFace, CES1to7 |
Translatable | boolean | If true, Questions carry locale variants | |
QuestionCount | formula | yes | fx: COUNT(SurveyQuestions where Template = self) |
Active | boolean | yes | |
Owner | reference | yes | → Employees (Survey Author or CX Manager) |
Description | text | ||
Status | select | yes | Draft, Published, Retired |
ContentHash | text | Server hash of the exact questions, choices, branching and scoring rules | |
PublishedAt | datetime | Publishing freezes this row and its child questions/choices | |
PrimaryMetricQuestion | reference | → SurveyQuestions — same Template; one primary question for each metric instrument | |
MinimumScore | number | Frozen validated metric range | |
MaximumScore | number | Frozen validated metric range | |
FavourableMinimum | number | Frozen favourable range for CSAT | |
EffortDirection | select | LowerIsEasier, HigherIsEasier |
SurveyQuestionsThe per-template question grain. Carries the prompt, the Line item of SurveyTemplates.line item
| Field | Type | Required | Details |
|---|---|---|---|
Template | reference | yes | → SurveyTemplates |
Sequence | number | yes | Display order |
Prompt | text | yes | |
QuestionType | select | yes | Rating, NPS, CES, MultiChoice, MultiSelect, FreeText, YesNo |
Required | boolean | yes | |
BranchLogic | text | e.g. "show_if Q1 <= 6" — interpreted by render engine | |
HelpText | text | ||
ReverseScored | boolean | True when high score = negative sentiment |
SurveyChoicesThe selectable options for `QuestionType IN (MultiChoice, Line item of SurveyQuestions.line item
| Field | Type | Required | Details |
|---|---|---|---|
Question | reference | yes | → SurveyQuestions |
Sequence | number | yes | |
ChoiceText | text | yes | |
ChoiceValue | text | Value sent in ResponseAnswers (defaults to ChoiceText) | |
ScoreWeight | number | Optional numeric score for this choice |
SurveyCampaignsA specific deployed campaign — for example "Q1 2026 Emailtransaction
| Field | Type | Required | Details |
|---|---|---|---|
CampaignNumber | text | yes | |
CampaignName | text | yes | |
SurveyType | reference | yes | → SurveyTypes |
Template | reference | yes | → SurveyTemplates — exact approved immutable instrument version |
TriggerType | select | yes | PostTicket, PostOnboarding, PostPurchase, PostRenewal, Quarterly, Manual |
Audience | reference | → SurveyAudiences (null for event-driven all-eligible) | |
StartDate | date | yes | |
EndDate | date | Null for always-on event-driven | |
Status | select | yes | Draft, PendingApproval, ChangesRequested, Scheduled, Active, Paused, Closed, Archived |
AnonymousMode | boolean | Snapshot of SurveyType default at launch | |
PIIMaskOnDisplay | boolean | Strip emails / phones / cards from displayed comments | |
InvitedCount | number | yes | Service snapshot: distinct provider-accepted invitations with SentAt <= MeasurementAsOf minus ResponseLagHours; same campaign occurrence population as RespondedCount |
RespondedCount | number | yes | Service snapshot: Accepted responses received by MeasurementAsOf for the exact InvitedCount population; excludes Invalidated even if invitation remains Responded |
ResponseRate | formula | yes | fx: IF(InvitedCount = 0, null, RespondedCount / InvitedCount) — fractions; display as percent |
Owner | reference | yes | → Employees (CX Manager) |
LaunchedAt | date | ||
ClosedAt | date | ||
Revision | number | yes | Increment for audience, instrument, privacy, timing or delivery policy changes |
SubmittedBy | reference | → Employees — authenticated principal mapping | |
SubmittedRevision | number | Exact revision sent for review | |
ApprovedBy | reference | → Employees — CX Manager distinct from SubmittedBy | |
ApprovedRevision | number | Must equal Revision at activation | |
ApprovedAt | datetime | Recorded only by approval command | |
AudienceSnapshotAt | datetime | Eligible audience resolution basis | |
MeasurementAsOf | datetime | Cutoff for the last paired invited/responded snapshot | |
ResponseLagHours | number | yes | Approved nonnegative maturation interval; change creates a new comparison basis |
MinimumAggregateSize | number | yes | Configured suppression threshold for anonymous slices; at least 5 |
DeliveryPolicyVersion | text | yes | Frozen send policy; current opt-outs always take precedence |
SurveyAudiencesThe named definition of who a campaign goes to — "Allmaster
| Field | Type | Required | Details |
|---|---|---|---|
AudienceCode | text | yes | |
Name | text | yes | |
Type | select | yes | AllCustomers, SegmentRule, UploadedList, EventBased |
Description | text | ||
ResolvedHeadcount | number | Last computed at use | |
LastResolvedAt | date | ||
Owner | reference | yes | → Employees (CX Manager) |
Active | boolean | yes |
SurveyAudienceRulesThe per-rule grain for `SurveyAudiences.Type = SegmentRule` Line item of SurveyAudiences.line item
| Field | Type | Required | Details |
|---|---|---|---|
Audience | reference | yes | → SurveyAudiences |
Sequence | number | yes | |
AttributeType | select | yes | Segment, Plan, Region, Tenure, Industry, AccountOwner, Channel, LifecycleStage |
Operator | select | yes | Equals, NotEquals, In, NotIn, GreaterThan, LessThan, Between |
AttributeValue | text | yes | Value matched against the Customer field implied by AttributeType (e.g. plan name, region code, tenure band, lifecycle stage). Acceptable as text because the value is interpreted per-row and doesn't need referential integrity to a master table. |
IncludeOrExclude | select | yes | Include, Exclude |
SurveyDeliveryRulesPer-Campaign delivery governance — when to send relative tomaster
| Field | Type | Required | Details |
|---|---|---|---|
RuleCode | text | yes | |
Campaign | reference | yes | → SurveyCampaigns |
PostInteractionDelayMinutes | number | e.g. 60 — wait an hour after Ticket close | |
MaxFrequencyPerCustomerDays | number | yes | e.g. 30 — at most one survey per customer per 30 days |
ChannelPreference | select | yes | Email, SMS, InApp, EmailThenSMS, EmailThenInApp |
ReminderEnabled | boolean | ||
ReminderDelayDays | number | e.g. 3 | |
MaxReminders | number | e.g. 1 | |
RespectOptOuts | boolean | yes | Always true; explicit for audit |
Active | boolean | yes | |
MirrorMaxAgeHours | number | yes | Hold pending recipients when CRM identity/contact snapshot is older |
TokenLifetimeHours | number | yes | Positive response-link lifetime bounded by campaign end |
PolicyVersion | text | yes | Immutable approved policy version |
CustomersRead-only mirror of the CRM Pipeline `Accounts` / `Contacts`master
| Field | Type | Required | Details |
|---|---|---|---|
CustomerNumber | text | yes | CRM-side stable ID |
Name | text | yes | |
Email | text | Masked except to send-side roles | |
Phone | text | Masked | |
Segment | select | Enterprise, MidMarket, SMB, SelfServe | |
Plan | text | ||
Region | select | NorthEast, SouthEast, Midwest, SouthWest, West | |
State | text | US state code | |
Industry | text | ||
AccountOwner | reference | → Employees (Account Manager / CSM) | |
LifecycleStage | select | Onboarding, Active, AtRisk, Churned | |
TenureMonths | number | Computed from FirstPurchaseDate | |
FirstPurchaseDate | date | ||
LastSyncedAt | date | yes | |
Active | boolean | yes |
SurveyInvitationsOne row per Customer, SurveyCampaign and OccurrenceKey. The dedupe + audittransaction
| Field | Type | Required | Details |
|---|---|---|---|
InvitationNumber | text | yes | |
Campaign | reference | yes | → SurveyCampaigns |
Customer | reference | yes | → Customers |
Channel | select | yes | Email, SMS, InApp |
EmailRef | text | Provider-side message ID for delivery webhook join | |
SMSRef | text | Provider-side message ID for SMS webhook join | |
TokenHash | text | yes | Hash of a random response credential; never expose raw token in staff views or logs |
RelatedTicket | text | Helpdesk Ticket ID for PostTicket campaigns | |
RelatedAgent | reference | → Employees — the agent who handled the ticket | |
RelatedFlow | text | For CES — onboarding step / renewal event ID | |
Status | select | yes | Queued, Sending, Sent, Opened, Responded, Bounced, Suppressed, Expired, Failed, Unknown |
SentAt | datetime | ||
OpenedAt | datetime | ||
RespondedAt | datetime | ||
ReminderCount | number | ||
LastReminderAt | datetime | ||
SuppressedReason | select | OptOut, FrequencyCap, Bounce, InvalidContact | |
OccurrenceKey | text | yes | Stable source event ID or campaign-period identity; unique with Campaign and Customer per tenant |
ScheduledSendAt | datetime | yes | Earliest intended send, not proof of delivery |
ExpiresAt | datetime | yes | Enforced by response submission |
Instrument | reference | yes | → SurveyTemplates — exact published version assigned to invitation |
ABTest | reference | → SurveyABTests — same Campaign | |
Variant | select | A, B | |
DispatchKey | text | yes | Stable initial delivery request; reminders use numbered attempt keys |
AttemptNumber | number | yes | Durable dispatch attempt sequence |
LastProviderEventKey | text | Correlated callback identity; duplicates ignored | |
SourceSystem | text | Origin for RelatedTicket / RelatedFlow references |
SurveyResponsesOne row per completed SurveyInvitation. The header for thetransaction
| Field | Type | Required | Details |
|---|---|---|---|
ResponseNumber | text | yes | |
Invitation | reference | → SurveyInvitations — null for anonymous responses; private SubmissionReceipts owns service correlation | |
Campaign | reference | yes | → SurveyCampaigns |
Customer | reference | → Customers — null when Anonymous; staff never infer it through private submission receipts | |
Anonymous | boolean | yes | Snapshot at submit |
RespondedAt | datetime | yes | |
ResponseTimeSeconds | number | From OpenedAt to submit | |
RelatedTicket | text | Snapshot | |
RelatedAgent | reference | → Employees (snapshot) | |
Channel | select | yes | Email, SMS, InApp, Portal |
Segment | select | Snapshot of Customer.Segment | |
Region | select | Snapshot of Customer.Region | |
Plan | text | Snapshot of Customer.Plan | |
Source | select | yes | Email, SMS, Portal, InAppWidget |
Instrument | reference | yes | → SurveyTemplates — immutable assigned version |
RelatedFlow | text | Attributable responses only; null for anonymous capture | |
Status | select | yes | Accepted, Invalidated |
InvalidatedReason | text | CX Manager controlled quality decision; original answer history retained | |
SubmittedKey | text | yes | Opaque random receipt identifier, not invitation token or customer ID |
ResponseAnswersThe exploded per-question answer grain. One row per question Line item of SurveyResponses.line item
| Field | Type | Required | Details |
|---|---|---|---|
Response | reference | yes | → SurveyResponses |
Question | reference | yes | → SurveyQuestions |
NumericValue | number | For Rating / NPS / CES / YesNo (1/0) | |
FreeTextValue | text | For FreeText answers | |
SelectedChoice | reference | → SurveyChoices (set for MultiChoice / MultiSelect; null for FreeText / Rating / NPS / CES). MultiSelect questions explode into one ResponseAnswers row per chosen choice. | |
Skipped | boolean |
NPSCategoriesThe Detractor / Passive / Promoter classification thresholds.master
| Field | Type | Required | Details |
|---|---|---|---|
CategoryCode | text | yes | Detractor, Passive, Promoter |
Name | text | yes | |
MinScore | number | yes | Inclusive |
MaxScore | number | yes | Inclusive |
ColorHex | text | For dashboard rendering | |
Active | boolean | yes | |
DisplayOrder | number |
NPSScoresThe NPS roll-up grain. One row per Period × Audience scope celltransaction
| Field | Type | Required | Details |
|---|---|---|---|
Campaign | reference | yes | → SurveyCampaigns |
Period | text | yes | e.g. 2026-Q1, 2026-04 |
ScopeType | select | yes | Org, Segment, Region, Plan, AccountOwner, Industry |
ScopeKey | text | yes | Segment name, region code, plan name, AccountOwner EmployeeID, etc. |
RespondentCount | number | yes | |
Promoters | number | yes | |
Passives | number | yes | |
Detractors | number | yes | |
NPSScore | formula | yes | fx: IF(RespondentCount = 0, null, ((Promoters − Detractors) / RespondentCount) × 100) |
PriorPeriodNPS | number | For trend | |
Status | select | yes | Computed, Published, Recomputing |
ComputedAt | date | yes |
CSATScoresThe CSAT roll-up grain. One row per Period × Channel scopetransaction
| Field | Type | Required | Details |
|---|---|---|---|
Campaign | reference | yes | → SurveyCampaigns |
Period | text | yes | |
ScopeType | select | yes | Org, Channel, Agent, AgentTeam, Segment, Region |
ScopeKey | text | yes | |
RespondentCount | number | yes | |
AverageRating | number | yes | |
Top2BoxPct | number | yes | Percent at top 2 of scale |
Bottom2BoxPct | number | yes | Percent at bottom 2 of scale |
ResponseRatePct | number | ||
PriorPeriodAverage | number | ||
Status | select | yes | Computed, Published, Recomputing |
ComputedAt | date | yes |
CESScoresThe CES roll-up grain. One row per Period × Flow. CES is thetransaction
| Field | Type | Required | Details |
|---|---|---|---|
Campaign | reference | yes | → SurveyCampaigns |
Period | text | yes | |
Flow | select | yes | Onboarding, Renewal, SupportResolution, ProductCheckout, Other |
ScopeKey | text | Optional sub-scope (e.g. specific onboarding step) | |
RespondentCount | number | yes | |
AverageEffort | number | yes | Raw mean on the frozen instrument scale; show EffortDirection alongside it |
Instrument | reference | yes | → SurveyTemplates — exact compatible scale and polarity |
EffortDirection | select | yes | LowerIsEasier, HigherIsEasier — frozen from Instrument |
LowEffortPct | number | Easy share: scores1–2 when LowerIsEasier,6–7 when HigherIsEasier | |
HighEffortPct | number | Difficult share: scores6–7 when LowerIsEasier,1–2 when HigherIsEasier | |
PriorPeriodEffort | number | ||
Status | select | yes | Computed, Published, Recomputing |
ComputedAt | date | yes |
CohortsNamed segmentation cohorts for analysis — "New customers inmaster
| Field | Type | Required | Details |
|---|---|---|---|
CohortCode | text | yes | |
Name | text | yes | |
DefinitionDescription | text | Human-readable rule | |
MemberCountSnapshot | number | Last computed | |
LastComputedAt | date | ||
Owner | reference | yes | → Employees (Analyst or CX Manager) |
Active | boolean | yes | |
DefinitionVersion | number | yes | Immutable rule version for each membership snapshot |
RuleExpression | text | yes | Validated deterministic customer-attribute predicate |
CohortMembersComputed memberships. One row per Customer per Cohort pertransaction
| Field | Type | Required | Details |
|---|---|---|---|
Cohort | reference | yes | → Cohorts |
Customer | reference | yes | → Customers |
ComputedAt | date | yes | |
EnteredAt | date | First time the customer matched the cohort | |
ExitedAt | date | If known to no longer match | |
Status | select | yes | Active, Exited |
DefinitionVersion | number | yes | Snapshot of Cohorts.DefinitionVersion |
TrendsThe time-series snapshot grain — one row per Period × Metrictransaction
| Field | Type | Required | Details |
|---|---|---|---|
Period | text | yes | |
Metric | select | yes | NPS, CSAT, CES, ResponseRate, DetractorPct, PromoterPct |
ScopeType | select | yes | Org, Channel, Segment, Region, Plan, Agent, AgentTeam, Cohort, Industry |
ScopeKey | text | yes | |
Value | number | yes | Same units as the compatible metric: NPS points, CSAT/CES raw instrument mean, ResponseRate fraction 0–1, DetractorPct/PromoterPct percent 0–100 |
RespondentCount | number | ||
Benchmark | number | Same metric units, instrument scale/polarity and compatible population from BenchmarkData when available | |
Variance | number | Value − Benchmark | |
ComputedAt | date | yes | |
CompoundKey | formula | yes | fx: PopulationKey — upsert at the full campaign, instrument and cohort basis; never overwrite another program |
Campaign | reference | yes | → SurveyCampaigns |
Instrument | reference | yes | → SurveyTemplates — compatible scoring version |
CohortSnapshotAt | datetime | Explicit membership basis when ScopeType = Cohort | |
PopulationKey | text | yes | Campaign, instrument, period, metric, scope and cohort version; unique per tenant |
ThemesAuto-classified or manually tagged comment themes frommaster
| Field | Type | Required | Details |
|---|---|---|---|
ThemeCode | text | yes | |
ThemeName | text | yes | e.g. ProductIssue, AgentBehavior, WaitTime, Pricing, Onboarding, FeatureRequest, Billing |
ParentTheme | reference | → Themes (self) — for hierarchical taxonomy | |
Sentiment | select | Positive, Neutral, Negative, Mixed | |
MentionCount | number | Rolling-90-day count, recomputed nightly | |
Description | text | ||
Active | boolean | yes |
CommentsThe surface for raw FreeText answers — one row per non-emptytransaction
| Field | Type | Required | Details |
|---|---|---|---|
Response | reference | yes | → SurveyResponses |
Question | reference | yes | → SurveyQuestions |
RawText | text | yes | Original — masked except to roles with raw access |
MaskedText | text | PII-stripped rendering — emails / phones / cards scrubbed | |
Sentiment | select | Positive, Neutral, Negative, Mixed | |
PrimaryTheme | reference | → Themes | |
SecondaryTheme | reference | → Themes | |
ClassifiedBy | select | AI, Manual, Hybrid | |
Customer | reference | → Customers (null when Anonymous) | |
RelatedAgent | reference | → Employees |
FollowupsDetractor close-the-loop tasks. Created automatically bytransaction
| Field | Type | Required | Details |
|---|---|---|---|
FollowupNumber | text | yes | |
Response | reference | yes | → SurveyResponses |
Customer | reference | yes | → Customers |
Owner | reference | → Employees — eligible Account Manager; null stays Unassigned for CX Manager routing | |
TriggerScore | number | The numeric NPS / CSAT score that triggered | |
TriggerReason | select | yes | NPSDetractor, CSATBottom2Box, CESHighEffort, ManualEscalation |
DueDate | date | yes | |
Status | select | yes | Unassigned, Open, InProgress, ManagerReview, Resolved, Cancelled |
ResolutionNotes | text | What the AccountManager did and what the customer said | |
OutcomeCategory | select | Resolved, NotReachable, EscalatedToProduct, ChurnRisk, Recovered | |
ResolvedAt | date | ||
CreatedAt | date | yes | |
PreviousFollowup | reference | → Followups — completed predecessor for an explicitly requested later conversation | |
ConversationSequence | number | yes | Initial trigger creates0; CX Manager continuation atomically allocates next sequence |
ContinuationKey | text | Unique same-tenant command identity for an explicit later conversation | |
Revision | number | yes | Current outreach/review revision |
SubmittedBy | reference | → Employees — authenticated owner submitting outcome | |
ReviewedBy | reference | → Employees — CX Manager, distinct from owner and submitter | |
ReviewedAt | datetime | Decision on exact submitted outcome revision | |
ReviewedRevision | number | Prevent stale decision from closing changed outreach |
BenchmarkDataIndustry-vertical benchmark imports (e.g. SaaS-Industry-NPS,master
| Field | Type | Required | Details |
|---|---|---|---|
BenchmarkCode | text | yes | |
Provider | text | yes | |
Industry | text | yes | |
Metric | select | yes | NPS, CSAT, CES, ResponseRate |
Period | text | yes | |
Value | number | yes | NPS points, CSAT/CES raw compatible instrument mean, or ResponseRate fraction 0–1; never compare values with different metric units |
SampleSize | number | ||
ImportedAt | date | yes | |
Active | boolean | yes |
OptOutsSurvey-suppression registry. One row per Customer per Scopetransaction
| Field | Type | Required | Details |
|---|---|---|---|
Customer | reference | yes | → Customers |
Scope | select | yes | AllSurveys, NPSOnly, CSATOnly, CESOnly, Marketing |
Source | select | yes | UnsubscribeLink, SMSStopReply, ManualOptOut, BounceHardFail, PrivacyRequest |
OptOutAt | date | yes | |
OptOutChannel | select | Email, SMS, Portal, Phone | |
Notes | text | ||
Active | boolean | yes |
SurveyABTestsA/B test configurations for survey wording. Splits atransaction
| Field | Type | Required | Details |
|---|---|---|---|
TestCode | text | yes | |
Campaign | reference | yes | → SurveyCampaigns |
VariantATemplate | reference | yes | → SurveyTemplates |
VariantBTemplate | reference | yes | → SurveyTemplates |
SplitPercentA | number | yes | Default 50 |
MinSampleSize | number | yes | Per variant |
StartDate | date | yes | |
EndDate | date | ||
Status | select | yes | Draft, Running, Paused, Concluded, Cancelled |
WinnerVariant | select | A, B, NoSignificantDifference | |
Hypothesis | text | ||
Result | text | Plain-language summary at conclusion | |
Owner | reference | yes | → Employees (Survey Author) |
AnalysisMethod | text | yes | Prespecified comparison and decision rule, with confidence/significance basis |
Metric | select | yes | ResponseRate, AverageScore |
AnalysisVersion | text | yes | Locked before assignment; basis retained in results |
AllocationSaltVersion | text | yes | Stable server assignment basis; no reshuffle on retry |
EmployeesRead-only identity mirror for the app's authenticated staff. Tenant plus PrincipalID resolves exactly one active employee; CRM account owners and support agents map to this same directory. Admin may configure the trusted connector but cannot forge principal membership.master
| Field | Type | Required | Details |
|---|---|---|---|
EmployeeNumber | text | yes | Unique stable source identity per tenant |
PrincipalID | text | yes | Server-authenticated principal mapping; unique per tenant |
Name | text | yes | Display name |
Manager | reference | → Employees — effective support-team manager | |
TeamCode | text | Scoped support-team identity | |
Active | boolean | yes | Inactive principals cannot receive work |
LastSyncedAt | datetime | yes | Trusted source timestamp |
SubmissionReceiptsPrivate service-owned correlation ledger. No listed staff role, including Admin, can list, export, traverse or query these rows or join anonymous responses back to recipients. Only the authenticated submission service uses it to atomically enforce one completed response per invitation. Privacy retention limits and separate delivery-service access apply; anonymous business data is non-attributable, not a claim that the delivery service never processed identity.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ReceiptNumber | text | yes | Random opaque identifier |
Invitation | reference | yes | → SurveyInvitations — unique accepted receipt per invitation |
Response | reference | yes | → SurveyResponses — one accepted result |
CommandKey | text | yes | Unique tenant and invitation submission command |
PayloadHash | text | yes | Reject changed payload under a repeated command key |
Status | select | yes | Accepted, Invalidated |
AcceptedAt | datetime | yes | Same transaction as answers and invitation Responded transition |
DeliveryAttemptsService-owned delivery outbox and callback ledger. Each initial send or reminder keeps its request identity through uncertain provider responses. Staff see delivery status under recipient permissions; they cannot fabricate provider acknowledgments.transaction
| Field | Type | Required | Details |
|---|---|---|---|
AttemptNumber | text | yes | Human reference |
Invitation | reference | yes | → SurveyInvitations |
RequestKey | text | yes | Unique tenant, invitation and reminder sequence |
PayloadHash | text | yes | Frozen request fingerprint |
ReminderSequence | number | yes | Zero for initial delivery |
ScheduledAt | datetime | yes | Requested execution time |
Status | select | yes | Queued, Sending, Accepted, Failed, Unknown, Suppressed |
ProviderRef | text | Acknowledged provider result | |
LastEventKey | text | Verified provider callback identity | |
AcceptedAt | datetime | Confirmed provider acceptance |