Endatix.Modules.Audience 0.9.0

Endatix.Modules.Audience

Audience personalization for Endatix: per-form properties and people, tenant match key, CSV import, and personalised links.

Registration

Gated by Endatix:FeatureFlags:PersonalizationModule (off by default). Runs on PostgreSQL and on SQL Server 2025 or later (or Azure SQL Database). EndatixBuilder.UseDefaults() calls UseModule(AudienceModule.Instance).

The module registers AudiencePostgreSqlDbContext or AudienceSqlServerDbContext for the configured provider; handlers see it only as IAudienceDbContext. On SQL Server, keep READ_COMMITTED_SNAPSHOT on for the database (on in a database Endatix creates and in Azure SQL Database; a database created beforehand may have it off): without it, other tenants' audience reads wait for a running CSV import to commit.

Migrations

Each provider has its own chain in this assembly: Persistence/Migrations/PostgreSql/ and Persistence/Migrations/SqlServer/. Run from the repository root:

# PostgreSQL
ConnectionStrings__DefaultConnection="Host=localhost;Database=endatix;Username=postgres;Password=..." \
dotnet ef migrations add <MigrationName> \
  --startup-project src/Endatix.WebHost \
  --project src/Endatix.Modules.Audience \
  --context AudiencePostgreSqlDbContext \
  --output-dir Persistence/Migrations/PostgreSql

# SQL Server
ConnectionStrings__DefaultConnection="Server=localhost;Database=endatix;User Id=sa;Password=...;TrustServerCertificate=True" \
dotnet ef migrations add <MigrationName> \
  --startup-project src/Endatix.WebHost \
  --project src/Endatix.Modules.Audience \
  --context AudienceSqlServerDbContext \
  --output-dir Persistence/Migrations/SqlServer

A model change needs a migration in both chains, and follows Schema changes during a rolling deploy: the previous release keeps writing while the new one migrates, so a constraint on a new column ships one stable release after the column. AudienceMigrationsRollingDeployTests catches a single migration that adds both.

AddPropertyValueIsTest (endatix#1376) copies each membership's test flag onto its cells, then adds FK_PropertyValues_Membership (TenantId, MembershipId, IsTest → AK_Memberships_TestFlag on Memberships), which validates every existing cell. From then on the database refuses a cell whose tenant or test flag differs from its membership's. An older API version writes IsTest = false on a Test participant's new cell and fails with the foreign key (a 500). The migration adds the column and the key together only because the module was a preview until then (personalization-module, off by default), so no production host wrote PropertyValues before it; a host that turned the preview on sees that 500 while the upgrade rolls out (endatix#1389). The column add is metadata-only on PostgreSQL 11+ and SQL Server Enterprise/Azure SQL, and a table rewrite on SQL Server Standard; the index rebuilds block writes to PropertyValues while they run. This is the module's only foreign key; nothing hard-deletes a membership. A future purge must delete its cells first and add an unfiltered index on (TenantId, MembershipId, IsTest) before it: the key's checks also see soft-deleted cells, so the filtered IX_PropertyValues_Cell cannot serve them, and each membership delete scans PropertyValues (22 ms per membership at 500,000 cells on PostgreSQL 17, every page on SQL Server 2025).

Schema

Database schema: audience

API (directory)

Permission: Forms.Edit, except PUT audience/settings, which needs Tenant.ManageSettings, the test submission routes, which need both Forms.Test and Forms.Edit (a test submission carries the person's identifier and values), and redeem, which is anonymous.

Rules

• Staged imports. The browser parses the file and sends it in chunks of 1,000 rows. The chunk that completes the file starts the Check, which reads every row with the reader the one-request import uses (ImportRowReader, one instance across chunks, so a repeat in a later chunk is a duplicate), writes the rejections, counts who is new to the form, and writes no Participant. It pins the match key and starts again at row 1 when retried. After the scripter confirms, the write commits 500 rows per transaction under the shared match-key lock and every stripe of the form's people lock, with WrittenThroughRow in the same transaction, so a write cut off anywhere continues after its last batch and writes no row twice. It skips the Check's rejections and re-reads values against the Properties as they are then. Each batch re-checks the form and the match key: a deleted form fails the import and runs the form's cleanup again (a batch may have committed after it), and a changed match key fails it whole, which is safe because a key can only change while the tenant has no Participants. With the Jobs module, each phase is a background job (AudienceImportCheck, AudienceImportWrite), enqueued after its transition commits with a dedup key per phase and run; a read that finds a running phase with no job enqueues it again. Without it, the same phases run in the request, up to 5,000 rows. A phase its request leaves running (a closed page, a timeout, an error, a busy audience) has no job to continue it, so it ends failed: a stopped write with canResume, a stopped Check without. The one-request import runs its phases itself, not through that runner, and marks its import RunsInRequest, so a read never enqueues a job beside it; a resume runs as a job again. A cancel is seen by the Check at its next chunk, because every save is fenced on the status.

• Import options. A staged import starts with emptyCells and missingProperties (keep by default, stored as ClearEmptyCells and ClearMissingProperties, both default false) and every write batch reads them from the import, so a resumed write applies the same ones. They change only people already on the form, never one a test import matches (it writes nothing to them). A Property is in the file when the mapping names it or the file has one of its variable__choice columns; a row with an empty cell for it, no cell at all (a row shorter than the header row), or no ticked choice column, leaves it empty. emptyCells: clear clears what the file's Properties store for a row that leaves them empty, and missingProperties: clear clears what the form's other Properties store for each person in the file; people not in the file keep everything. Both only clear the Properties the form had when the import started (CreatedAt), so a Property added during the import or before a resume is never cleared by it. The match-key Property is never cleared. ImportClears picks those Properties and adds an empty value only where a cell is stored, so a person counts as updated only when a cell changed or was removed, and a file re-imported with clear and nothing left to clear reports them as skipped. The Check's counts do not depend on the options.

• Import sweep. ImportSweep ends the tenant's idle imports before every import start, so an abandoned import never holds a form, and ImportSweepService runs it for every tenant five minutes after the host starts and then daily, on every node, also deleting what finished imports staged. An import left uploading or checked 24 h expires (a chunk sent counts as activity), a checking one 6 h fails, and a writing one 6 h fails resumable: the state a write whose job dead-lettered or hung leaves, so the scripter resumes it from its checkpoint. A resumable failure nobody resumes for 7 days expires, and so does every one of a form when an import of that form starts, in the same save as the start: the scripter chose to start over. Until then imports/current returns the form's newest one, so Resume is offered after the fact. Idle is ModifiedAt, else CreatedAt; no column was added. Each move is the aggregate's, saved against the status it read, so nodes sweeping at once, or a run moving the import meanwhile, settle as compare-and-set. Finished imports lose their chunks, and their rejections 7 days after they finished, one import per delete. WriteRun is a concurrency token next to Status, and a write job carries the run it was enqueued for: a batch of a run that was failed as stalled and then resumed writes nothing, wherever it wakes. A job queued before runs were named writes for run 0, and a write started without a run (in a request) takes the import's run when it starts, so no write runs unfenced.

• Match key. Locked while any person is on any form's audience (isLocked). Saving the current key is a no-op and takes no lock. A change soft-deletes members that are on no form, since they were matched under the old key.

• Values by data type. number: invariant, no surrounding spaces, any finite double (-12.5, 1e3, 1e29). boolean: true or false. date: YYYY-MM-DD. date_time: ISO 8601 (2026-10-05T09:30, optional seconds and offset). single_choice: one key from ChoicesJson. multiple_choice: a JSON array of keys. AllowsOther accepts any key.

• Choice columns in an import. A variable__choice header whose variable is a choice Property is one choice of it (CsvColumnMapper, ChoiceCells), so a responses export imports back unchanged, unless the caller mapped that header to a Property; a single-choice Property may also keep one column holding the key. A choice cell ticks with 1, true, yes, y or x and stays unticked with 0, false, no, n or nothing, in any case; any other value rejects the row, naming the column. On a multiple-choice Property, variable__other and variable__other_text are Other (endatix#1418): the text is added as a free choice only when the Property allows Other and __other is ticked. Text without the tick, or on a Property without Other, rejects the row. A ticked __other with an empty __other_text cell is ignored; a ticked __other when the file has no __other_text column rejects the row. A Property that lists a key other keeps __other as that key, and its __other_text adds the text beside it under the same conditions. Without Other, a ticked __other is the key other, which the value check refuses. Other text that equals a listed key is stored once, as that key. A single-choice Property skips its __other_text column, so a radio question's export imports, but its Other text is not stored yet. The Check and the write read rows through the same ImportRowReader, so staged imports and the one-request route share the rule.

• Concurrency. Person creates and imports take a shared tenant lock; a match-key change takes it exclusively. The lock is ITransactionLock (scope AudienceMatchKey, key the tenant id), held until the transaction ends. The unique indexes settle create races (a second pass reuses the member; a duplicate on one form is a 409). Two imports of one form that add the same new people in different orders can deadlock on those indexes; the victim takes the same second pass. The identifier is checked against the key read under the lock. An edit of a participant's values and their removal both take that participant's stripe of the form's people lock exclusively, then the participant's lock (MembershipLock: scope AudienceMembership, key the membership id), and read the membership again under them: an edit that waited for a removal is a 404, and a removal that waited for an edit soft-deletes the cells it wrote, so a removed participant never keeps a live value (endatix#1361). The CSV import writes values of people already on the form in one transaction, so it takes every stripe of the form's lock shared (scope AudienceFormPeople), after the tenant lock and before it reads the form's people. The form's lock is 16 stripes, keyed {formId}:{stripe}: an edit and a removal hold their participant's stripe (from a hash of the membership id) exclusively, an import holds all 16 shared, in ascending order within one 5 s wait. So imports of one form run together (endatix#1383), every import excludes every edit and every removal on the form, and edits and removals run together unless they share a stripe, when one waits for the other for the length of that section. Lock queues are first come, first served, so an import that asks for the stripe of an edit or a removal queued behind another import waits until that writer gets it or gives up. A removal that waited for an import soft-deletes the cells it wrote, and an import that waited for a removal adds the participant again. An edit waits for an import on its stripe (endatix#1384): without that stripe, the edit's insert waited on the import's uncommitted cell for the 30 s command timeout while holding the participant's lock, and then failed with a 500. An edit that loses a cell race to a writer that skips the locks takes a second locked pass, which overwrites. Every wait is 5 s, then 503 whose detail is the message of the lock that waited: "This participant is being changed. Try again." for the participant's, "People on this form are being imported or changed. Try again." for the form's (an edit or a removal behind an import or another edit or removal on its stripe, or an import behind an edit or a removal). On PostgreSQL two keys of one scope can share a hashtext lock, so unrelated people or forms then wait for each other, within that bound. Cells are written in property-id order. The people list counts and then reads the page without a transaction, so it takes no locks that wait behind writers; when a person added in between makes the page longer than the count, the total is raised to cover the rows shown.

• Test persons. A test person is on one form only for testing: never issued a personalised link, and shown with isTest so the list can filter them. The flag belongs to the membership, so the same member can be a test person on one form and a real person on another. It is set at add (isTest on add or import) and cannot change: to turn a test person into a real one, remove them and add them again. An import marked as test never touches a person already on the form (neither its flag nor its values), so real people's data stays as it was. A test person locks the match key like any other person. Removing a test person removes only that membership.

• Deletes. Removing a person deletes the membership and its cells; the member stays. Deleting a property soft-deletes its cells in one statement. Reads skip cells of deleted properties. These take no participant lock: an edit in flight can still write a cell after them, and it stays out of every read with its Property (a form's deletion deletes its Properties too).

• Removing test participants. RemoveTestPeopleHandler removes the people first, one at a time, through TestParticipantRemoval: each one through PersonRemoval (the removal DELETE people/{membershipId} makes) under their own stripe and MembershipLock, in a transaction of its own; no section holds the whole form. Then, with alsoDeleteTestSubmissions, TestSubmissionSweep deletes the test submissions opened as them: TestSubmissionsOfSpec (a test submission whose Snapshot source is test and whose memberId is one of theirs, PersonalizationSnapshotJson.IsTest and NamesAnyParticipant), in keyset pages of 500, each deleted through the aggregate so Reporting drops its row, and each its own commit. A submission marked as test from the submissions page keeps its own source and is left alone. Their test links then answer 404, as any access token to a deleted submission does. The named memberships are read with their tombstones, so the same request again finishes a run that failed part way (people already removed count 0; an id never on the form is 404); with membershipIds null that is every test participant ever on the form, so its cost grows with the form's history and the request runs until both phases finish. The Snapshot names the member, not the membership, so a member still on the form after the removals through a membership the request did not name (a real participant, or a test one added again) keeps every test submission: a real participant never loses one. Known window: a test open that found the person before their removal and saves after the sweep leaves one test submission, whose link works until it expires.

• Form deleted. The form.deleted outbox handler soft-deletes the form's cells, memberships and properties; tenant members stay. Until it runs, the form's people still count toward the match-key lock.

• Marked as test. The submission.updated outbox handler (changeKind test) frees the link of a submission still marked as test, deleted or not, keeping the token. It locks the submission's row while it clears the link, so a mark as real cannot commit between the check and the clear. Redeem frees such a link itself too, so the handler only tidies the link early. Marking a submission real is a 409 naming the other one when its link has since opened a real submission, and a 400 for a test opened as a person (Test as this person): its answers are a builder's, and as a real response it would pass every one-per-person check.

• Outbox delivery. Both outbox handlers also run as background jobs (AudienceDeleteFormAudience, AudienceReleaseLinkOnTestMark), each registered with its job and subscription by one AddOutboxWork call in AudienceOutboxJobs. A handler registered inline only stops a host with Endatix:Outbox:DeliverToJobQueue on from starting. The release job is enqueued only for a test mark, not for every submission.updated.

• On behalf. POST forms/{formId}/submissions/onbehalf (Submissions.CreateOnBehalf, not this module's routes) freezes a snapshot when Submitter.ExternalSubjectId matches a member on a form that has properties. Unknown id, no properties, or no submitter leaves the create unchanged. The snapshot is source: on_behalf plus memberId and values[] beside variables. Binding Member.SubmitterId is one update that matches only while it is null.

• Test submissions. TestSubmissionOpener is the one way to build one: a started submission on the form's active definition, IsTestSubmission, no Submitter and no single-response gate, labelled with the person's identifier, and a snapshot of the person's current cells with source: test and memberId. It never reads or writes Links, Member.SubmitterId or Submitters, so testing as a person leaves their personalised link unopened (or still pointing at their real response) and their one-response gate as it was. The person is found through LinkOpenGates.PersonAsync, the query a link open uses, with the tenant and form stated in the read. The values freeze with Submission.FreezeForTest, which refuses a real submission or a second freeze. Start over needs the submission's token, so one builder cannot discard another's test. It finds the person by the snapshot's memberId, checks they are still on the form, and reads their values and mints the token before it deletes anything. An open and a Start over then write through TestSubmissionWriter, which takes the form's shared FormSubmissionsLock (no wait: 503 while the form is being deleted), reads the form again (enabled, with an active definition, else 404), and inserts on the definition active now; the locked section reads nothing else, so it holds one connection. A Start over deletes the old submission in the same save as the insert: IsDeleted is a concurrency token, so the delete matches only while the old one is live, and of two Start overs at once one replaces it and the other gets 404 and saves nothing. A save that loses a deadlock is 503. A failure to mint the token saves nothing and the old test submission stays. Test submissions are left out of exports unless the export asks for them.

• Test links. TestSubjects.CandidatesAsync reads the form first, then the named people (or every Test participant) in one read, each with its test flag. CreateTestLinksHandler refuses a real participant itself, because TestSubjects finds anyone on the form, and it checks the 500 cap before anything is written. TestSubmissionOpener.OpenManyAsync reads the token lifetime once and each person's values once. TestSubmissionWriter.AddManyAsync holds the shared FormSubmissionsLock once and inserts every submission in one save. It then mints each token through ISubmissionAccessTokenService before it commits, so a token that can't be minted rolls the call back. Each token is unstamped and submit only, as Copy test link's is. It works until it expires, or until its submission or form is deleted (404). A retry of a call whose response was lost opens a second set.

• Participant on a form. FormParticipants is the one rule: ByMember finds a member's live membership on the tenant's form, and Of joins memberships to their members by key, with the member's tenant filter dropped by name (the membership is the tenant's; with the filter, PostgreSQL can scan the tenant's members per membership). The participant panel (ParticipantReader), Preview as (AudienceVariablesReader.ReadOneAsync), opening a test submission by member id (TestSubjects) and a link open (LinkOpenGates.PersonAsync) all go through it. ParticipantValues is the one values read (live cells by property id), used by the people list, the participant panel and Preview as.

• Match-key Property. The form's text Property whose variable name is the tenant's match key (email or external_id, MatchKeyProperty.Of, the key read in the same statement through IdentifierKindReader.Stored, email when the tenant has no settings) holds each participant's identifier wherever values are read: the people list (values, so notFilledIn counts it as filled), the participant panel and the create and update replies, Preview as and the variables read (ParticipantValues, which takes the Property from its caller), Coverage (filled for every test and real participant) and the Snapshot of new submissions (SnapshotCells, given the form and identifier by the open). It is derived, never stored, and the identifier wins over a cell stored for that Property; Snapshots already written are not changed (endatix#1365). Only a text Property: the identifier is the text the participant was added with, and Typed would turn an external id 007 in a number Property into 7, or put an address in a date or a choice its rules refuse, so a Property of another data type named email or external_id is an ordinary one. Writes follow the read-only real presets (endatix#1359): a value for the match-key Property that is the identifier or empty is left out and stores nothing, and any other value is refused, naming the Property ("Email is the participant identifier and cannot be changed."), by one rule (MatchKeyProperty.WriteError) in the participant panel (CreatePersonCommand, UpdatePersonCommand: a 400), the designer's presets (PresetReconciler, before the first write: a 400) and the CSV import (ImportRowReader: the row is rejected).

• Variables. AudienceValueJson is the one place a cell becomes the JSON a survey reads: the Snapshot (variables and values[]) and the designer's presets both call Typed, so a value previewed for a Test participant is the value frozen on their next test submission. A number that is not finite stays a string, because JSON cannot carry it. VariableDefinition is the only data type → question mapping: text and date_time → text (a date-time's own question stays plain text; the client converts the stored value, offset dropped and wall-clock time kept, before it fills a datetime-local input), number → text with inputType: number, date → text with inputType: date, boolean → boolean, single_choice → dropdown, multiple_choice → checkbox; choices are the keys in order, and AllowsOther adds allowCustomChoices (a multiple_choice becomes a tagbox), which stores free text as the Audience does. Each question also carries prefillTargets, the form questions whose answer has the shape the Snapshot gives that data type, so the designer's Prefill from offers a Property only where it fits and the Hub keeps no type table of its own: text → text, comment; number → text with inputType: number, rating, slider; boolean → boolean; date → text with inputType: date; date_time → text, and text with inputType: datetime-local; single_choice → dropdown, radiogroup, buttongroup; multiple_choice → checkbox, tagbox, whether or not it allows other. inputType is omitted whenever the question uses its default; for type: text, that default is plain text. SurveyJS does not know the key: loading the definition lists it in jsonErrors and leaves the question as it was, and a loaded question drops it, so a client reads it from the response itself. AudienceVariablesReader reads the Properties, the first 20 participants (Test participants first, each group by when they were added: one ORDER BY IsTest DESC, Id), and their live cells (by the membership ids just read and the live Property ids, one table and a seek per participant on IX_PropertyValues_Cell; an EXISTS on Properties there scanned every cell of the tenant), and the coverage counts: participants by test flag, then filled cells in one GROUP BY on Property and the cell's test flag over the form's live Properties (one index range on IX_PropertyValues_Property each), with no join between cells and memberships, which timed out at 30 s in CI at 5,000 × 50 (PostgreSQL looped the memberships over the cells). The statements share no snapshot, so each count is clamped to 0..participants. The membership filter has no EXISTS on Members, as the people list does: the only Member delete (RetireMembersAsync, on an identifier-kind change) runs only while the tenant has no live memberships. A cell exists only while it holds a value, so a count of cells is a count of people filled in. Up to six statements; it stops after the participant count when the form has no Properties or no participants. At 5,000 Test and 5,000 real participants × 50 Properties (495,000 cells) a warm read takes a p50 of about 55 ms on PostgreSQL and 150 ms on SQL Server in the integration test (fresh statistics; 190 and 140 ms stale), in the same statements as a small form (endatix#1376). ReadOneAsync reads one participant, real or test, with the Properties and their live cells, in four statements (the participant, the match-key Property, the Properties, their cells); it stops after the first when they are not on the form. The presets list those Test participants by identifier, then the real ones by identifier, so a form with no Test participants still previews as its first 20 participants. totalPresets counts every participant, test and real (CSV import can add up to 5,000). A choice key cannot contain |, because SurveyJS splits a choice written as value|text.

• Variables write-back. SaveTestParticipantValues matches each preset to a participant on the form by identifier, normalized by the tenant's match key (an email ignores case). PresetReconciler plans every write before the first one: a new identifier is added as a Test participant with its filled values; a Test participant gets only the values that differ from their cells, compared through Typed then Untyped so 12.50 equals 12.5, a variable left out clearing its cell; values that are equal are not rewritten (a number keeps the digits it was stored with), which makes a replay write nothing; a change to a real participant refuses the request ("Real participants are read-only."), while a real participant's preset sent back unchanged is left alone, because the designer sends its whole list, read-only presets included; a variable that is no Property is ignored; participants left out of the list are untouched (nothing is deleted). Values are checked by PropertyValueRules only where they will be written, so an unchanged value stored before a rule changed is kept, also when another value of that participant changes. A variables object that repeats a name is a 400. Writes go through CreatePersonCommand (IsTest) and UpdatePersonCommand, so their locks, races and rules apply; they stop at the first failure, and a replay finishes the rest. The reply's presets are the first 20 participants (Test participants first) plus every one the request named, so a preset saved past the first 20 is in it. AudienceValueJson.Untyped turns a survey value into cell text: null is empty, a string its text, a number one form (R, so 1.50 and "1.5" become 1.5), anything else compact JSON with non-ASCII text kept (an emoji is still escaped). Untyped(t, Typed(t, v)) == v for a value in that form, and Typed(t, Untyped(t, x)) == Typed(t, x) always.

• Two personalised create paths. On behalf (source: on_behalf) is for real responses entered by a trusted caller: it resolves and binds a Submitter and counts against one-per-person. Test as this person (source: test) is for survey builders and does neither. The snapshot shape is the same, since both read cells through SnapshotCells; source on the submission's personalization read tells them apart.

• Links. The token is stored as a SHA-256 hash. A claimed link resumes without a lock. A first open checks LinkOpenGates before the lock: a disabled form or a removed person is 404 at once, and a link open never writes either. It then takes ITransactionLock (scope AudienceLinkOpen, key the link id, 5 s wait, then 503) through LinkOpenLock, and then its form's shared FormSubmissionsLock (no wait: 503 while the form is being deleted), on one connection: AppDbContext joins the audience transaction. Under the lock it re-reads the claim, adopts a submission an earlier open left (FormId + AudienceLinkId) or inserts one, issues the token, and claims with one update (SubmissionId still null). All of that commits together. A claim lost to a writer outside the lock rolls back and resumes that writer's submission. A link claimed before tokens were issued this way gets its first token under the lock. A person removed before that claim is not found, and the link stays unopened: every path under the lock (insert or take-over) reads the snapshot, which reads the cells before the membership, and a removal deletes both in one save. A person removed after open is 404 on redeem too, and their access token is refused (see the redeem route above).

• One response per person. On a one-response form the open resolves the person's Submitter before the lock: Member.SubmitterId when bound, else Integration + the identifier (what on-behalf resolves), created if missing and bound once. The link's submission carries it with the single-response gate, so UX_Submissions_RestrictionKey refuses a second one from either path. A refused insert rolls back, and the retry takes the other writer's submission over. A create stores its token in its insert, so a take-over's token is never replaced by a later save. For email tenants the identifier is matched as first entered, so an on-behalf call with another casing is a different Submitter until Member.SubmitterId is bound. See ARCHITECTURE.md → Transaction locks.