TAP Database overview

This page describes the TAP workload database (schema: TAP) and the primary tables used by active TAP tagging suggestions, topic detection, plus legacy batch tagging, captioning, and content-safety flows. See the individual pages for details:

• Batch tagging (./batch-tagging)
• Content safety (./content-safety)

Location of DDL

• SSDT model: db/sqlproj/Mastra.Platform.TapDb/model/Tables

Key tables

• TAP.log_TaggingSuggestionJobs — Active tagging suggestion job/run table used by tap-tagging-suggestions-persistence.ts
• TAP.log_TaggingSuggestionPostResults — Active per-post tagging suggestion results
• TAP.data_ConversationMessages — Durable topic detection message ledger with IDs, roles, hashes, and metadata
• TAP.data_ConversationMessageText — Short-lived topic detection message text retained for the configured rolling window
• TAP.data_ConversationTopicTaxonomy — Active topic taxonomy rows used by topic detection
• TAP.log_TopicDetectionRuns — Topic detection run summaries, provider lane, analysis mode, tokens, and timing totals
• TAP.log_TopicDetectionConversationResults — Per-conversation topic detection results, candidate topics, raw response, request ID, latency, tokens, and step timings
• TAP.data_ConversationTopicAssignments — Current topic assignments by conversation and taxonomy version
• TAP.log_BatchTaggingJobs — Legacy parent job table for batch tagging workflow runs
• TAP.log_PostTaggingResults — Legacy per-post result rows (caption, tags, status)
• TAP.log_ContentSafetyFindings — Legacy stored Azure Content Safety results and flags
• TAP.log_TagOperations — Legacy audit trail for tag creation/attachment operations
• TAP.data_UserContext — Optional snapshots of ambassador/user context used during processing

Troubleshooting quick tasks

• Find recent active tagging suggestion jobs:

SELECT TOP 50 * FROM TAP.log_TaggingSuggestionJobs ORDER BY CreatedAtUtc DESC;

• Find active tagging suggestion post results for a job:

SELECT * FROM TAP.log_TaggingSuggestionPostResults WHERE JobId = @jobId ORDER BY CreatedAtUtc DESC;

• Find recent topic detection results:

SELECT TOP 50
       ConversationId,
       TaxonomyVersion,
       Status,
       ProviderLane,
       AnalysisMode,
       ProviderLatencyMs,
       PromptTokens,
       CompletionTokens,
       TotalTokens,
       ProcessedAtUtc
FROM TAP.log_TopicDetectionConversationResults
ORDER BY ProcessedAtUtc DESC;

• Find current topic assignments for a conversation:

SELECT *
FROM TAP.data_ConversationTopicAssignments
WHERE ConversationId = @conversationId
  AND IsCurrent = 1
ORDER BY AppliedAtUtc DESC;

• Find recent legacy batch jobs:

SELECT TOP 50 * FROM TAP.log_BatchTaggingJobs ORDER BY CreatedAt DESC;

• Find results for a specific post:

SELECT * FROM TAP.log_PostTaggingResults WHERE PostId = @postId ORDER BY CreatedAt DESC;

• Find content-safety flags for a job:

SELECT * FROM TAP.log_ContentSafetyFindings WHERE JobId = @jobId ORDER BY CreatedAt DESC;

See the sibling pages for a deeper playbook and common queries.

TAP schema architecture

Entity-Relationship Diagram

Table summary

Table	Purpose	Key columns
log_TaggingSuggestionJobs	Active tagging suggestion jobs and tracked runs	JobId, RunId, Status, Operation
log_TaggingSuggestionPostResults	Active per-post suggestion result snapshots	JobId, PostId, Status, ResultJson
data_ConversationMessages	Durable message ledger for topic detection	ConversationId, MessageId, ThreadId, TextHashSha256
data_ConversationMessageText	Short-lived message text for recent topic detection reruns/audit	ConversationId, MessageId, TextContent, ExpiresAtUtc
data_ConversationTopicTaxonomy	Topic taxonomy used by classifier runs	TaxonomyVersion, TopicKey, DisplayName
log_TopicDetectionRuns	Topic detection run summaries	RunId, Status, ProviderLane, AnalysisMode, TotalTokens
log_TopicDetectionConversationResults	Per-conversation topic detection audit results	ConversationId, MessageHashSet, ProviderRequestId, TotalTokens, StepTimingsJson
data_ConversationTopicAssignments	Current topic assignment state	ConversationId, TaxonomyVersion, TopicKey, IsCurrent
log_BatchTaggingJobs	Legacy parent job table for batch tagging workflow runs	JobId, WorkflowRunId, Status
log_PostTaggingResults	Legacy per-post result rows (caption, tags, status)	JobId (FK), PostId, Status
log_ContentSafetyFindings	Legacy stored Azure Content Safety results and flags	PostTaggingResultId (FK), Flagged, Threshold
log_TagOperations	Legacy audit trail for tag creation/attachment operations	PostTaggingResultId (FK), OperationType, TagName
data_UserContext	Optional snapshots of user context used during processing	JobId, UserId, ContextPayloadJson