Skip to navigation
Research and SpikesJob Tracking

User Story Details

User Story 1: Create [extractor].[Job] and [extractor].[JobStatus] tables for tracking extraction jobs and status transitions

Description
As a developer,
I want to create Flyway migrations that define the [extractor].[Job] and [extractor].[JobStatus] tables,
so that we can persist extractor job metadata and capture detailed status transitions for auditing and polling.

Acceptance Criteria

  • Create a Flyway SQL migration in the fluxx-sql-server repo
  • The migration must:
    • Drop each table if it exists (IF OBJECT_ID(...) IS NOT NULL)
    • Create [extractor].[Job] table with:
      • JobID (PK), FileId, ClientApplicationExtractorID
      • Status, StatusMessage, ExtractorResponse
      • PollAttemptCount, LastPollAttemptAt
      • IsActive, CreatedAt, UpdatedAt, CompletedAt
    • Create [extractor].[JobStatus] table with:
      • JobStatusID (PK), JobID (FK), Status, Message
      • AttemptNumber, Timestamp
  • Apply proper CLA naming conventions:
    • ID suffix for PK/FK
    • Id for reference columns
    • Use IsActive for soft deletion
  • Add constraints:
    • FK_Job_FileId_File_FileID
    • FK_Job_ClientApplicationExtractorID_Registration_ClientApplicationExtractorID
    • FK_JobStatus_JobID_Job_JobID
  • Use SYSDATETIMEOFFSET() as default for all datetime values
  • Migration must be idempotent and pass in local dev container

User Story 2: Expose endpoint to return active extractors by ClientApplicationId

Description
As an extractor service,
I need a Registration endpoint that returns all active extractors registered for a given ClientApplication,
so I can start the appropriate extraction jobs for an uploaded file.

Acceptance Criteria

  • Route: GET /registration/:clientAppId/extractors
  • Response: array of objects with the following shape:
[
  {
    "clientApplicationExtractorId": "uuid",
    "extractionUrl": "https://..."
  }
]
  • Must filter on IsActive = true
  • Must query [registration].[ClientApplicationExtractor] by ClientApplicationId
  • Swagger/OpenAPI spec must be updated
  • Unit and integration tests must cover:
    • Valid clientAppId with results
    • Valid clientAppId with no extractors (returns empty array)
    • Invalid clientAppId (returns 404 with problem+json format)

User Story 3: Add PollingUrl to [registration].[ClientApplicationExtractor] table (optional)

Description
As a platform architect,
I want to optionally store a polling URL in the extractor registration table,
so that polling-style extractors can have a separate endpoint for job status queries if needed.

Acceptance Criteria

  • Create a Flyway migration that adds a nullable PollingUrl column to [registration].[ClientApplicationExtractor]
  • Data type: VARCHAR(500)
  • Must preserve existing constraints and indexes
  • Update the Registration API response to include pollingUrl if defined
  • Add OpenAPI schema update to reflect optional field in extractor objects

User Story 4: Add SQL connector for [extractor] schema to Extractor service

Description
As a developer,
I want to enable the Extractor service to connect to the [extractor] schema in SQL Server,
so it can insert and update records for job tracking.

Acceptance Criteria

  • Use existing SQL connector pattern from another service (e.g. Registration)
  • Connection string should support .env and App Config override
  • Extractor service must have access to the following tables:
    • [extractor].[Job]
    • [extractor].[JobStatus]
  • Add service-level integration test to verify connection and basic insert/select
  • Update README.md with database configuration steps

User Story 5: Add BlobStorage access to Extractor service for writing result files

Description
As a developer,
I want the Extractor service to write result JSON files to a structured blob path,
so that raw extraction results can be stored and retrieved later.

Acceptance Criteria

  • Grant Extractor service write access to Azure Storage blob container with HNS enabled
  • Configure storage access using environment variables and Azure SDK
  • Define blob path pattern:
/results/{ClientApplicationID}/{FileId}/{JobId}/result.json
  • Write blob using stream or buffer based on result size
  • Validate successful write operation through unit test
  • Store path reference in ExtractorResponse field in [extractor].[Job]

User Story 6: Add Key Vault integration for API key lookup by ClientApplicationExtractorID

Description
As a developer,
I want to retrieve the API key for a given extractor from Azure Key Vault,
so that the Extractor service can securely call the configured ExtractionUrl.

Acceptance Criteria

  • Use @azure/keyvault-secrets SDK to retrieve secrets
  • Secret name = ClientApplicationExtractorID (UUID string)
  • Secret value = API key (string)
  • Fallback to fail job and log error if key is not found
  • Add wrapper module with retry logic and unit test coverage
  • Update Extractor service configuration docs to describe required Key Vault setup

User Story 7: Initialize Job and publish JobQueued event

Description
As a developer,
I want the Extractor service to insert a new Job record and emit a JobQueued event,
so that we can begin tracking the job and notify the platform that processing has started.

Acceptance Criteria

  • On TOPIC_ACCEPTED_FILE message:
    • Parse FileId, ClientApplicationId
    • Call Registration API to get active extractors
    • Select the first extractor from the list
  • Generate a new JobID (UUID)
  • Retrieve the API key from Key Vault using ClientApplicationExtractorID
  • Insert into [extractor].[Job]:
    • JobID, FileId, ClientApplicationExtractorID
    • Status = 'Queued', PollAttemptCount = 0, CreatedAt, UpdatedAt
  • Publish JobQueued message to TOPIC_JOB_STATUS
  • Add integration test to verify DB record and message emission

User Story 8: Submit file to extractor via ExtractionUrl

Description
As a developer,
I want the Extractor service to submit the uploaded file to the extractor endpoint,
so that external processing can begin using the correct job context.

Acceptance Criteria

  • Retrieve file blob from Azure Storage using signed URL (generated or received)
  • Make POST request to ExtractionUrl with body:
{
  "fileUrl": "<signed blob url>",
  "jobId": "<JobID>"
}
  • Use extractor-specific API key as header (e.g., Authorization: Bearer)
  • On success (201 Created):
    • Update [extractor].[Job] to Status = 'Started'
    • Add JobStatus record: Status = 'Started', AttemptNumber = 0
    • Publish JobStarted to Service Bus
  • On failure:
    • Update job to Status = 'Failed'
    • Store response in StatusMessage
    • Publish JobFailed message

User Story 9: Implement polling logic for job status updates

Description
As a developer,
I want the Extractor service to periodically poll extractors that require status checks,
so that we can track job completion without relying on push mechanisms.

Acceptance Criteria

  • For jobs with known polling requirement:
    • Construct URL: {extractionUrl}/{jobId}
    • Include API key if needed
  • Call polling URL and parse JSON response
  • If isReady: false:
    • Insert JobStatus with updated AttemptNumber and timestamp
    • Increment PollAttemptCount, set LastPollAttemptAt in [extractor].[Job]
  • If isReady: true:
    • Extract result or resultUrl
    • Proceed to result storage step
  • Implement exponential backoff or polling interval
  • Add unit tests and one integration test using mock polling endpoint

User Story 10: Store extractor result to blob and finalize job

Description
As a developer,
I want to persist the completed job result to structured blob storage,
so that the full raw extraction result is available for downstream use or auditing.

Acceptance Criteria

  • If result is inline:
    • Serialize JSON to blob as result.json
  • If result is at resultUrl:
    • Download content and store it as blob
  • Blob path must follow convention:
/results/{ClientApplicationId}/{FileId}/{JobId}/result.json
  • Update [extractor].[Job]:
    • Status = 'Succeeded', CompletedAt = now, update ExtractorResponse
  • Insert final JobStatus record
  • Publish JobSucceeded to Service Bus
  • If failure during write or download:
    • Update job to Failed
    • Publish JobFailed with error detail

User Story 11: Publish job status messages to Service Bus

Description
As a developer,
I want the Extractor service to emit structured status events for job progress,
so that the Status service and other consumers can track job lifecycles.

Acceptance Criteria

  • Publish to TOPIC_JOB_STATUS for each status event:
    • JobQueued
    • JobStarted
    • JobSucceeded
    • JobFailed
  • Message format must include:
    • JobID, Timestamp
    • FileId, ClientApplicationExtractorID
    • Optional: ResultSummary, ErrorMessage
  • Follow existing platform message envelope structure
  • Add integration test to confirm emission of each event

User Story 12: Start all extractors for a file (parallel job initiation)

Description
As a developer,
I want the Extractor service to start a job for each active extractor on a file,
so that the platform can support multi-extractor workflows.

Acceptance Criteria

  • On TOPIC_ACCEPTED_FILE:
    • Retrieve all active extractors from Registration API
  • For each extractor:
    • Generate unique JobID
    • Lookup API key
    • Insert Job record
    • Submit file to ExtractionUrl
    • Publish JobQueued and JobStarted events
  • Job status for each extractor must be tracked independently
  • Store each extractor’s result in its own blob folder under:
/results/{ClientApplicationID}/{FileId}/{JobId}/result.json
  • Add test coverage to confirm independent lifecycle tracking

User Story 13: Add retry limits or polling thresholds for extractor jobs

Description
As a platform owner,
I want to define limits for polling attempts per job,
so that failed or long-running extractors do not consume system resources indefinitely.

Acceptance Criteria

  • Add configurable max polling attempts (e.g., MAX_POLL_ATTEMPTS)
  • After limit is reached:
    • Mark job as Failed
    • Insert final JobStatus record with reason
    • Publish JobFailed event to Service Bus
  • Optionally track time-based thresholds (e.g., timeout window)
  • Add unit test to simulate retry exhaustion and finalization logic

User Story 14: Expose internal API to retrieve job statuses by FileID or JobID

Description
As an internal consumer,
I want to retrieve extractor job details for a file or job ID,
so I can display, troubleshoot, or audit job progress and results.

Acceptance Criteria

  • Create routes:
    • GET /extractor/job/:jobId
    • GET /extractor/job/file/:fileId
  • Response for each job includes:
    • JobID, FileId, ClientApplicationExtractorID
    • Current Status, CreatedAt, CompletedAt, PollAttemptCount
    • Blob path to result (if available)
    • History of transitions from JobStatus
  • Use pagination or limits for large job lists
  • Add Swagger/OpenAPI spec and integration test