> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://cla-demo.postmansolutions.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://cla-demo.postmansolutions.com/_mcp/server.

# 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:

```json
[
  {
    "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:

```json
{
  "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