> ## Documentation Index
> Fetch the complete documentation index at: https://aipkg.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Gallery.CredentialExpiration

> Background job that queries the Gallery database for expiring and expired API keys and sends warning email notifications to affected users via Azure Service Bus.

## Overview

`Gallery.CredentialExpiration` is a scheduled background job (console executable) that monitors NuGet Gallery user API keys approaching or past their expiration date and dispatches targeted email notifications. It serves two notification categories per user:

* **Expiring** — keys that will expire within a configurable warning window (e.g., 10 days).
* **Expired** — keys that crossed their expiration timestamp since the last successful job run.

The job is designed to run on a recurring schedule (managed externally by NSSM or a similar service wrapper). It persists a JSON cursor to Azure Blob Storage after every run so that expiry windows are tracked incrementally and duplicate notifications are avoided even when the job is delayed or restarts.

<Note>
  The job uses a `WhatIf` configuration flag. When set to `true`, all database queries and cursor updates execute normally, but no emails are dispatched. This is intended for development and integration environments to prevent accidental user spam.
</Note>

## Role in the System

<CardGroup cols={2}>
  <Card title="Gallery Database Consumer" icon="database">
    Queries the `Credentials` table in the NuGet Gallery SQL database for `apikey.v3` and `apikey.v4` credential types whose expiry falls within the job's computed time window.
  </Card>

  <Card title="Email Pipeline Producer" icon="mail">
    Publishes outbound email messages to an Azure Service Bus topic using the shared `NuGet.Services.Messaging` infrastructure. Actual delivery is handled downstream by a separate email worker.
  </Card>

  <Card title="Cursor-Driven Incremental Processing" icon="clock">
    Reads and writes a `cursorv2.json` file in Azure Blob Storage to track the last processed timestamp, enabling safe re-runs and gap recovery.
  </Card>

  <Card title="Standalone NuGet Job" icon="package">
    Extends `JsonConfigurationJob` from `NuGet.Jobs.Common`, following the standard job pattern used across the NuGetGallery background job fleet.
  </Card>
</CardGroup>

## Key Files and Classes

| File Path                                      | Class / Type                       | Purpose                                                                                                                   |
| ---------------------------------------------- | ---------------------------------- | ------------------------------------------------------------------------------------------------------------------------- |
| `Program.cs`                                   | `Program`                          | Entry point; creates `Job` and hands off to `JobRunner.Run()`.                                                            |
| `Job.cs`                                       | `Job`                              | Main job class. Initialises services in `Init()`, orchestrates the full run loop in `Run()`.                              |
| `GalleryCredentialExpiration.cs`               | `GalleryCredentialExpiration`      | Implements `ICredentialExpirationExporter`. Executes the SQL query and filters results into expiring vs. expired buckets. |
| `ICredentialExpirationExporter.cs`             | `ICredentialExpirationExporter`    | Interface contract for credential retrieval and categorisation.                                                           |
| `CredentialExpirationEmailBuilder.cs`          | `CredentialExpirationEmailBuilder` | Extends `MarkdownEmailBuilder`. Constructs subject lines and Markdown bodies for both expiry states.                      |
| `JobRunTimeCursor.cs`                          | `JobRunTimeCursor`                 | Serialisable cursor POCO holding `JobCursorTime` and `MaxProcessedCredentialsTime`, persisted as `cursorv2.json`.         |
| `Configuration/InitializationConfiguration.cs` | `InitializationConfiguration`      | Strongly-typed configuration POCO bound from `appsettings.json`.                                                          |
| `Models/ExpiredCredentialData.cs`              | `ExpiredCredentialData`            | DTO populated directly from the SQL result set.                                                                           |
| `Models/CredentialExpirationJobMetadata.cs`    | `CredentialExpirationJobMetadata`  | Immutable value object combining run time, cursor, and warn-days into a single context.                                   |
| `LogEvents.cs`                                 | `LogEvents`                        | Structured log event IDs (600–651) for failed email, failed credential handling, and job lifecycle failures.              |
| `Strings.resx`                                 | —                                  | Embedded resource containing email subject/body templates and the raw SQL query for credential retrieval.                 |

## Dependencies

### Internal Project References

| Project                  | Role                                                                                                  |
| ------------------------ | ----------------------------------------------------------------------------------------------------- |
| `NuGet.Jobs.Common`      | Base `JsonConfigurationJob`, `JobRunner`, `GalleryDbConfiguration`, `QueryWithRetryAsync`, DI wiring. |
| `NuGet.Services.Storage` | `AzureStorageFactory`, `AzureStorage`, `BlobServiceClientFactory` — blob cursor persistence.          |

### Key NuGet Dependencies

| Package                     | Usage                                                                              |
| --------------------------- | ---------------------------------------------------------------------------------- |
| `NuGet.Services.Messaging`  | `AsynchronousEmailMessageService`, `EmailMessageEnqueuer`, `MarkdownEmailBuilder`. |
| `NuGet.Services.ServiceBus` | `TopicClientWrapper`, `ServiceBusMessageSerializer` — Service Bus email dispatch.  |
| `Azure.Identity`            | Managed-identity blob storage auth.                                                |
| `Newtonsoft.Json`           | Cursor serialisation (strict `MissingMemberHandling.Error`).                       |
| `System.Data.SqlClient`     | Gallery SQL connection.                                                            |

## SQL Query

The credential retrieval query is stored in `Strings.resx`:

```sql theme={null}
SELECT cr.[Type], cr.[Created], cr.[Expires], cr.[Description],
       u.[EmailAddress], u.[Username]
FROM [Credentials] AS cr
INNER JOIN Users AS u ON u.[Key] = cr.[UserKey]
WHERE u.[EmailAllowed] = 1
  AND u.[EmailAddress] <> ''
  AND Expires IS NOT NULL
  AND cr.[Expires] <= CONVERT(datetime, @MaxNotificationDate)
  AND cr.[Expires] >= CONVERT(datetime, @MinNotificationDate)
  AND (cr.[Type] = 'apikey.v3' OR cr.[Type] = 'apikey.v4')
  AND cr.[RevocationSourceKey] IS NULL
ORDER BY u.[Username]
```

<Note>
  Only `apikey.v3` and `apikey.v4` credential types are targeted. Revoked credentials (`RevocationSourceKey IS NOT NULL`) are explicitly excluded. Users must have `EmailAllowed = 1` and a non-empty email address.
</Note>

## Notable Patterns and Quirks

<Warning>
  The cursor file is named `cursorv2.json`. The `v2` suffix implies a breaking schema change from a prior cursor format. Deserialisation uses `MissingMemberHandling.Error`, meaning any cursor file written by an older schema version will cause the job to throw and halt rather than silently misprocess notifications.
</Warning>

<Note>
  Non-scoped API keys (those with a null or empty `Description`) are backfilled with the string `"Full access API key"` before grouping. This is defined as `Constants.NonScopedApiKeyDescription` in `ExpiredCredentialData.cs`.
</Note>

<Tip>
  The expiring-credentials boundary uses `MaxProcessedCredentialsTime` from the cursor rather than `JobRunTime` when the cursor's value is ahead of the current run time. This guards against sending duplicate expiry warnings if the job runs more frequently than the warning window.
</Tip>

### Email Aggregation Pattern

Credentials are grouped per user before emailing. Each user receives at most two emails per run — one for expiring keys and one for expired keys — with all affected key names listed as bullet points inside a single message body. This avoids per-key email storms for users with many API keys.

## Configuration Reference

| Property                         | Description                                                                      |
| -------------------------------- | -------------------------------------------------------------------------------- |
| `ContainerName`                  | Azure Blob container name for cursor storage.                                    |
| `DataStorageAccountUrl`          | Blob service endpoint URL (used with managed identity).                          |
| `EmailPublisherConnectionString` | Azure Service Bus namespace connection string.                                   |
| `EmailPublisherTopicName`        | Service Bus topic name for outbound email messages.                              |
| `GalleryAccountUrl`              | URL inserted into email bodies pointing users to their API key management page.  |
| `GalleryBrand`                   | Brand name string (e.g., `"NuGet"`) interpolated into email subjects and bodies. |
| `WarnDaysBeforeExpiration`       | Number of days ahead of expiry to begin sending warnings.                        |
| `WhatIf`                         | When `true`, suppresses actual email dispatch (safe for non-production).         |