TAP Content Safety database

Flagged Azure Content Safety results from the active shared endpoint are persisted to Core.log_ContentSafetyDetections. The older TAP batch/content-safety workflow table, TAP.log_ContentSafetyFindings, is retained as a legacy audit surface until production usage and retention needs are reviewed.

The active endpoint also keeps a short-lived SQL context buffer in Core.cache_ContentSafetyMessageContext. This buffer stores every content-safety request for a default two-hour window so the backend can preserve recent context when a message is flagged. Expired rows are removed by the health aggregator Azure Function timer contentSafetyContextRetentionTimer.

Table: Core.cache_ContentSafetyMessageContext

Purpose: Ephemeral rolling context buffer for active content-safety sessions. This table is not a durable audit record.

Key columns

Identification & Correlation:

• ContextMessageId — Stable unique identifier for the buffered row
• SessionId, MessageId, RequestId — Conversation/request IDs returned by the endpoint
• CorrelationId, RoutePath, TenantId, UserId, Project, Source — Request and ownership metadata

Buffered Content:

• TextContent, TextHashSha256, TextLength — Submitted text plus search/dedupe metadata
• ImageInputsJson — Image URL/label plus binary/base64 size and SHA-256 summaries. Raw image bytes are not stored.
• AuditContextJson, RequestContextJson — Caller/runtime metadata

Retention:

• CreatedAtUtc, ExpiresAtUtc — Default expiry is two hours
• IX_cache_ContentSafetyMessageContext_Session supports flagged snapshot lookup
• IX_cache_ContentSafetyMessageContext_ExpiresAtUtc supports purge batches

Cleanup

apps/health-aggregator registers contentSafetyContextRetentionTimer, defaulting to CONTENT_SAFETY_CONTEXT_PURGE_SCHEDULE=0 */10 * * * *. The timer calls Core.usp_PurgeExpiredContentSafetyContext with CONTENT_SAFETY_CONTEXT_PURGE_BATCH_SIZE, defaulting to 5000 rows.

Table: Core.log_ContentSafetyDetections

Purpose: Flagged-only audit trail for active content-safety detections. Safe/non-flagged requests are not stored.

Key columns

Identification & Correlation:

• DetectionId — Stable unique identifier for the detection row
• ConversationId, MessageId, ThreadId, SessionId — Caller-provided conversation/message context
• UserId, TenantId, Project, Source — Caller-provided ownership and source context
• RequestId, CorrelationId, RoutePath, EnvironmentName — Runtime/request correlation

Moderation Outcome:

• Flagged — Always 1; the table has a check constraint to prevent safe-result rows
• Threshold, AuthMode, CategoriesJson — Moderation configuration and auth mode
• TextMaxSeverity, ImageMaxSeverity, per-category severity columns, and flagged booleans
• BlocklistNamesJson, BlocklistHitCount, BlocklistHitsJson, HaltOnBlocklistHit

Preserved Context:

• TextContent, TextHashSha256, TextLength — Submitted text and search/dedupe metadata
• ImageInputsJson — Image URL/label plus binary/base64 size and SHA-256 summaries. Raw image bytes are not stored.
• AuditContextJson, RequestContextJson, TextResponseJson, ImageResponsesJson, FullResponseJson
• ContextSnapshotJson, ContextMessageCount, ContextWindowStartedAtUtc, ContextWindowEndedAtUtc, ContextRetentionHours — Available context copied from Core.cache_ContentSafetyMessageContext when a message is flagged

Review & Retention:

• ReviewStatus, ReviewedAtUtc, ReviewedBy, Disposition, ReviewNotes
• CreatedAtUtc, ExpiresAtUtc

Legacy table: TAP.log_ContentSafetyFindings

TAP.log_ContentSafetyFindings belongs to the retired TAP batch/content-safety workflow surface. It is not the active persistence target for /other/tools/azure-content-safety.

Purpose: Detailed Azure Content Safety moderation findings per post. Stores text severity, image severity, blocklist hits, and raw response payloads.

Schema Diagram

Key columns

Identification & Correlation:

• ID — Primary key (BIGINT, identity)
• PostTaggingResultId — FK to log_PostTaggingResults.ID (nullable)
• JobId — Links to log_BatchTaggingJobs.JobId
• PostId — TAP post identifier

Overall Outcome:

• Flagged — Boolean indicating whether content was flagged for moderation
• Threshold — Configured severity threshold (0-7)
• AuthMode — Authentication method used ('managed-identity' or 'api-key')

Text Analysis Results:

• TextAnalyzed — Whether text was analyzed
• TextContent — Text that was analyzed (NVARCHAR(MAX))
• TextMaxSeverity — Max severity across all text categories
• TextHateSeverity, TextSelfHarmSeverity, TextSexualSeverity, TextViolenceSeverity — Per-category severity levels
• TextFlagged — Whether text exceeded threshold

Image Analysis Results:

• ImageAnalyzed — Whether image(s) were analyzed
• ImageUrl — URL of analyzed image
• ImageLabel — Image identifier (e.g., 'post-12345')
• ImageMaxSeverity — Max severity across all image categories
• ImageHateSeverity, ImageSelfHarmSeverity, ImageSexualSeverity, ImageViolenceSeverity — Per-category severity levels
• ImageFlagged — Whether image exceeded threshold

Blocklist Results:

• BlocklistChecked — Whether blocklists were checked
• BlocklistNames — Comma-separated list of blocklist names checked
• BlocklistHitCount — Number of blocklist hits detected
• BlocklistHitsJson — JSON array of blocklist hit details
• HaltOnBlocklistHit — Configuration: whether blocklist hit should halt processing

Raw Responses (Audit/Debug):

• Notes — Human-readable notes from moderation
• TextResponseJson — Full Azure Content Safety text analysis response
• ImageResponseJson — Full Azure Content Safety image analysis response
• FullResponseJson — Complete response payload

Timing:

• AnalysisStartedAt, AnalysisCompletedAt — Analysis timestamps
• AnalysisDurationMs — Duration in milliseconds
• CreatedAt — Record creation timestamp

Common queries

Finding flagged posts

SELECT PostId, Flagged, TextMaxSeverity, ImageMaxSeverity, BlocklistHitCount, CreatedAt
FROM TAP.log_ContentSafetyFindings
WHERE Flagged = 1
ORDER BY CreatedAt DESC;

Get content safety results for a specific job

SELECT
    f.PostId,
    f.Flagged,
    f.TextMaxSeverity,
    f.ImageMaxSeverity,
    f.BlocklistHitCount,
    f.Notes,
    f.CreatedAt
FROM TAP.log_ContentSafetyFindings f
WHERE f.JobId = @jobId
ORDER BY f.CreatedAt DESC;

Find posts with blocklist hits

SELECT
    PostId,
    BlocklistNames,
    BlocklistHitCount,
    BlocklistHitsJson,
    Notes,
    CreatedAt
FROM TAP.log_ContentSafetyFindings
WHERE BlocklistHitCount > 0
ORDER BY CreatedAt DESC;

Correlate content safety findings with tagging results

SELECT
    ptr.PostId,
    ptr.Caption,
    ptr.GeneratedTagsJson,
    csf.Flagged,
    csf.TextMaxSeverity,
    csf.ImageMaxSeverity,
    csf.Notes
FROM TAP.log_PostTaggingResults ptr
LEFT JOIN TAP.log_ContentSafetyFindings csf ON csf.PostTaggingResultId = ptr.ID
WHERE ptr.JobId = @jobId
ORDER BY ptr.CreatedAt DESC;

Get severity distribution for recent posts

SELECT
    CASE
        WHEN TextMaxSeverity IS NULL THEN 'Not Analyzed'
        WHEN TextMaxSeverity = 0 THEN 'Safe (0)'
        WHEN TextMaxSeverity <= 2 THEN 'Low (1-2)'
        WHEN TextMaxSeverity <= 4 THEN 'Medium (3-4)'
        ELSE 'High (5-7)'
    END AS SeverityRange,
    COUNT(*) AS PostCount
FROM TAP.log_ContentSafetyFindings
WHERE CreatedAt >= DATEADD(day, -7, SYSUTCDATETIME())
GROUP BY CASE
        WHEN TextMaxSeverity IS NULL THEN 'Not Analyzed'
        WHEN TextMaxSeverity = 0 THEN 'Safe (0)'
        WHEN TextMaxSeverity <= 2 THEN 'Low (1-2)'
        WHEN TextMaxSeverity <= 4 THEN 'Medium (3-4)'
        ELSE 'High (5-7)'
    END
ORDER BY PostCount DESC;

Troubleshooting

• Many posts flagged unexpectedly: Check the threshold configuration and blocklist settings in your Azure Content Safety resource. Review packages/platform-runtime/src/tools/azure-content-safety.ts for threshold defaults.
• Missing content safety entries: Verify that AZURE_CONTENT_SAFETY_ENDPOINT is configured and that the App Service Managed Identity has Cognitive Services User role on the Content Safety resource.
• Authentication failures: Check AuthMode column - prefer 'managed-identity' over 'api-key'. Ensure the Managed Identity is properly assigned in Azure.
• High analysis duration: Check AnalysisDurationMs - if consistently high, investigate network latency or Azure Content Safety service performance.
• Debugging specific posts: Use FullResponseJson to see the complete Azure Content Safety API response for detailed diagnostics.

Related tables

• TAP.log_BatchTaggingJobs — Parent job records
• TAP.log_PostTaggingResults — Per-post tagging results (caption, tags, status)

Code references

• Content Safety tool: src/mastra/tools/azure-content-safety.ts
• Context buffer: src/mastra/tools/content-safety-context-store.ts
• Retention timer: apps/health-aggregator/src/timer-trigger.ts
• Runtime note: active TAP tagging suggestions do not currently write new content-safety rows; this table belongs to the legacy batch/content-safety workflow surface.
• DDL: db/sqlproj/Mastra.Platform.TapDb/model/Tables/TAP.log_ContentSafetyFindings.sql