Tickets comments, attachments, and unified persistence

Ticket is the unified tenant aggregate for SysTicket. State, priority, and assignment are queryable scalars; comments and attachment references are source-generated JSON arrays. Removing an Entity/Mapper mirror concentrates collection growth, migration, and concurrency responsibility in one row.

1. Physical model

Indexes cover Tenant+StatusName+AssigneeId and Tenant+RequesterId. JSON elements have no separate table/index, so author, FileId, and element-time queries are not efficient.

TicketStorageJson uses compile-time JsonSerializerContext and rejects empty/corrupt/null JSON, incomplete or untrimmed fields, and duplicate keys. Restore additionally validates primary scalar lengths and soft-delete flag/time consistency.

2. Embedded Ticket comments

AddTicketCommentCommand(TicketId, CommentId, Body) obtains AuthorId from the current user. CommentId is idempotent: an existing id returns the first comment without comparing the new Body or updating CreatedAt.

// Keep operationId stable across transport retries of one user action.
var operationId = $"ticket-comment-{clientRequestId}";
var first = await mediator.Send(
    new AddTicketCommentCommand(ticketId, operationId, "Check the MFA device clock"),
    cancellationToken);

// A different Body with the same CommentId still returns the first value.
var replay = await mediator.Send(
    new AddTicketCommentCommand(ticketId, operationId, "This does not overwrite"),
    cancellationToken);

first.Value!.Body.ShouldBe("Check the MFA device clock");
replay.Value!.Body.ShouldBe(first.Value.Body);

The id is unique only inside JSON, not by a database constraint. Concurrent requests can append to the same snapshot; repository update ignores Version, so last-writer-wins can erase one comment.

Embedded comments have no parent, edit, delete, mention, visibility, or internal-note concept. Requester and assignee both use broad Update, and detail returns every comment.

3. Split write after Comments migration

Comments exposes internal MigrateTicketComments.Command. It reads Ticket rows where CommentsJson != "[]", inserts legacy comments into SysComment, then sets Ticket.CommentsJson to []. Idempotency is TenantId+Ticket+CommentId.

The public /api/tickets/{ticketId}/comments still calls ticket.AddComment and saves CommentsJson. A new comment after migration repopulates legacy JSON instead of writing SysComment; migration completion is not a stable state.

Choose one write model before GA. Prefer Tickets resource authorization followed by a narrow Comments port, or Comments calling a Tickets authorization port. Do not keep two public creation models.

4. Migration progress semantics

GetUnmigratedAsync loads all matching Tickets and only then applies Take(batchSize) in memory. Corrupt JSON logs a Warning and is omitted. If only corrupt rows remain, batches is empty and the Handler exits instead of reporting failure.

skippedTickets never increments, so reports omit corrupt rows. migratedAt is not persisted; MarkMigrated only writes []. A comment claims per-ticket transactions, but no explicit per-ticket transaction appears in the source; actual UoW scope needs evidence.

Reconcile legacy count, inserted count, duplicates, corrupt count, and remaining non-empty Ticket rows. Corrupt snapshots should fail closed or enter a retryable quarantine, not disappear behind a successful report.

5. Attachment binding chain

The Handler first authorizes Ticket Update, then calls ITicketAttachmentAccessService. The production adapter loads FileAsset and reuses FileAssetOwnerPolicy.EnsureCanDownload:

1. exact tenant match;
2. FileAsset status is Finalized;
3. public file, user/app owner, or elevated owner permission;
4. unavailable Files infrastructure returns Ticket.FileAssetUnavailable.

// PendingUpload or unauthorized files cannot be attached by id alone.
var access = await attachmentAccess.EnsureCanAttachAsync(
    command.FileId, currentUser.User, cancellationToken);
if (access.IsFailure)
    return Result.Failure<TicketAttachment>(access.Error);

// Ticket stores only FileId, never StorageKey, signed URL, or object credentials.
var attached = ticket.AttachFile(
    command.AttachmentId, command.FileId, actorId, clock.UtcNow);
if (attached.IsFailure)
    return Result.Failure<TicketAttachment>(attached.Error);

await repository.SaveAsync(ticket, cancellationToken);

6. Binding does not grant participant download

The check proves the binder can download, not that every Ticket participant can. Ticket stores FileId only, creates no ACL, and has no download proxy. Another participant can still receive AccessDenied from Files.

A production model should create a controlled Ticket-file relationship and let Files ask a narrow Ticket-view port at download time. Detach/revoke, quarantine, delete, and download audit must preserve TicketId/FileId without exposing StorageKey.

7. Attachment idempotency

AttachFile returns an existing item if either AttachmentId or FileId matches. Reusing an AttachmentId with a different FileId returns the old binding; using a new AttachmentId for an already-bound file does the same.

The Handler detects idempotency by unchanged collection count, so it skips persistence and audit on replay. Document this in SDK contracts or return IdempotencyConflict for changed immutable payload.

There is no detach. Deleting a FileAsset leaves its JSON reference in Ticket until callers handle it.

8. Whole-JSON updates and capacity

Every append loads the full Ticket, deserializes both arrays, mutates memory, serializes the full column, and updates it. History growth increases write amplification, allocation, lock time, and response size.

Description, CommentsJson, and AttachmentsJson are max-length columns, with no aggregate count/item limits. Detail returns all comments/attachments without paging.

After Comments cutover, remove the Ticket write path and eventually its JSON column. Attachments are also better modeled as a relation table with tenant/ticket/id and tenant/ticket/file unique constraints, paging, revoke, and concurrency.

9. Unified repository: strengths and gaps

Strengths: Ticket owns table/column/index metadata; both ORMs use IEntitySet<Ticket>; Find includes TenantId+Id+not-deleted; lists project scalars only.

Gaps: update predicate includes TenantId+Id but not IsDeleted, Version, or affected-row checks. A row deleted after load can still match, and concurrent writes are last-writer-wins. Save always returns Success and cannot distinguish zero rows, concurrency, or constraint conflict.

10. Retention and privacy

Tickets has no public delete/archive/retention command. Soft-delete fields come from the base type but no use case sets them. Descriptions/comments can contain personal data without a GDPR export/erase port. Attachments have no reference-count/legal-hold policy.

Define closed-ticket online retention, archive, search deletion, comment anonymization, file retention/deletion, audit legal hold, tenant offboarding, and SAR export explicitly.

11. Migration acceptance test

// Arrange: move legacy comments and verify Ticket JSON is empty.
await migration.Handle(
    new MigrateTicketComments.Command("tenant-a", BatchSize: 100),
    cancellationToken);
ticket.CommentsJson.ShouldBe("[]");

// Act: write through the public Ticket-comment boundary.
await comments.AddToTicketAsync(
    "tenant-a", ticket.Id, "comment-new", "user-1", "new body",
    cancellationToken);

// Assert: only SysComment grows; legacy JSON remains empty.
(await commentStore.GetByCommentIdAsync(
    "tenant-a", "Ticket", ticket.Id, "comment-new", cancellationToken))
    .ShouldNotBeNull();
ticket.CommentsJson.ShouldBe("[]");

Also test corrupt-only batches, concurrent migrations, writes during migration, cross-tenant/non-finalized files, participant download after bind, file deletion, and concurrent attachments.

12. Review commands

# After cutover, ordinary Tickets writes must no longer repopulate comment JSON.
rg -n "AddComment|CommentsJson|MigrateTicketComments" src/Platform/Tickets src/Platform/Comments -g '*.cs'

# Attachment code must never persist StorageKey and must keep Files owner policy.
rg -n "AttachFile|EnsureCanAttach|FileAssetOwnerPolicy|StorageKey" src/Platform/Tickets src/Platform/Files -g '*.cs'

# JSON updates need Version, IsDeleted, and affected-row semantics.
rg -n "UpdateWhereAsync|CommentsJson|AttachmentsJson|Version" src/Platform/Tickets -g '*.cs'

Back to Tickets · Comments · Files · Events and audit