Skip to main content

TAP Batch Tagging database

This page explains the tables used by TAP batch-tagging workflows and how to trace runs.

Schema Diagram​

Primary tables​

  • TAP.log_BatchTaggingJobs

    • Job-level record for each run (dry-run or update).
    • Important columns: JobId, WorkflowRunId, IsDryRun, EnableContentModeration, PostsRequested, PostsFetched, PostsProcessed, PostsUpdated, TagsGenerated, TagsCreated, TagsAttached, Status, ErrorSummary, CreatedAt.
    • Indexed on CreatedAt, JobId, WorkflowRunId, JobType, Status.

  • TAP.log_PostTaggingResults

    • Per-post results including generated caption, tags, moderation status, and link to JobId.
    • Useful columns: PostId, Caption, GeneratedTagsJson, ContentSafetyFlagged, Status, CreatedAt.

Observability tables​

  • TAP.log_SystemPrompts

    • Audit trail for LLM system prompts used per job. One row per job (job-level prompt, PostId = NULL).
    • Stores the full prompt text and a SHA-256 hash (PromptHash) for deduplication.
    • PromptSource indicates origin: db-profile, runtime-override, or default.
    • Indexed on JobId, PromptHash, AgentId, CreatedAt DESC.

  • TAP.log_ExecutionErrors

    • Structured error log with classification by ErrorType and ErrorCategory.
    • Captures per-step errors (StepName) and optionally links to a specific PostId.
    • Error types include: TAG_ATTACHMENT_ERROR, CAPTION_UPDATE_ERROR, SQL_LOGGING_ERROR, CONTENT_SAFETY_ERROR, API_ERROR.
    • Categories: tag-ops, write, persistence, moderation, fetch.

  • TAP.log_WorkflowStepTimings

    • Per-step performance metrics for workflow execution.
    • Captures StepId, StepOrder, DurationMs, StartedAtUtc, CompletedAtUtc.
    • Indexed for both per-job drill-down and cross-job step performance analysis.

Views​

  • TAP.vw_batch_tagging_traceability -- Full pipeline traceability. Joins jobs, post results, content safety, and system prompts. Includes computed WriteOutcome column: DRY_RUN, SUCCESS, PARTIAL, FLAGGED, SKIPPED, FAILED.
  • TAP.vw_workflow_step_timing_summary -- Per-job step timing aggregates: total duration, step count, avg/max/min step duration, slowest step, wall-clock duration.
  • TAP.vw_execution_error_summary -- Error analysis: counts by type/category/step, time range, affected posts. Joined with job context for IsDryRun and WorkflowName.

Common queries​

  • Find failed jobs in last 7 days:
SELECT * FROM TAP.log_BatchTaggingJobs
WHERE Status = 'failed' AND CreatedAt >= DATEADD(day, -7, SYSUTCDATETIME())
ORDER BY CreatedAt DESC;
  • Get per-post results for a job:
SELECT r.*
FROM TAP.log_PostTaggingResults r
WHERE r.JobId = @jobId
ORDER BY r.CreatedAt DESC;
  • Full traceability for a job (view):
SELECT * FROM TAP.vw_batch_tagging_traceability
WHERE JobId = @jobId;
  • All failed writes (non-dry-run):
SELECT * FROM TAP.vw_batch_tagging_traceability
WHERE WriteOutcome = 'FAILED' AND IsDryRun = 0;
  • System prompt used for a job:
SELECT AgentId, PromptHash, PromptSource, SystemPromptUsed
FROM TAP.log_SystemPrompts
WHERE JobId = @jobId;
  • Errors by category in last 7 days:
SELECT * FROM TAP.vw_execution_error_summary
WHERE FirstOccurrence >= DATEADD(day, -7, SYSUTCDATETIME())
ORDER BY ErrorCount DESC;
  • Slowest workflow steps:
SELECT * FROM TAP.vw_workflow_step_timing_summary
ORDER BY TotalDurationMs DESC;

Debugging tips​

  • If PostsFetched is zero but PostsRequested > 0, check TAP API client availability and the getPosts operation in packages/domain-tap/src/tools/tap/operations/get-posts.ts.
  • If captions are generated but not persisted, check whether updateCaptions: true was set in the workflow input. Batch tagging does not persist captions by default (only tags). If updateCaptions was enabled, look for failed updates in log_PostTaggingResults.CaptionUpdateSuccess and CaptionUpdateError.
  • If the LLM ignores data that IS in the logged system prompt, check TAP.log_SystemPrompts to verify what was logged and compare with MASTRA_DEBUG_LLM_MESSAGES=true output to see what actually reached the model.
  • Use TAP.vw_batch_tagging_traceability to get a full picture of any run: dry-run status, per-post write outcomes, content safety flags, and the system prompt used.

DDL reference: db/sqlproj/Mastra.Platform.TapDb/model/Tables/ (table definitions) and db/sqlproj/Mastra.Platform.TapDb/model/Views/ (view definitions).