From scheduled checks to reliable reminders in JobTrack

I built JobTrack as a learning project spanning Angular, ASP.NET Core, SQL Server, and Azure Functions. Its scheduled function checks for applications waiting for follow-up. Reviewing that function raised a useful question: what would need to change before it could safely send reminders?

Today, the function only logs eligible applications. This article is a design study based on that existing code. The reminder records, delivery worker, and failure experiments below are proposed extensions, not implemented features or measured results.

What the current function actually does

The timer runs every five minutes. Its SQL query selects applications whose status is Applied and whose application date is at least seven days old. This is the eligibility condition from the current implementation:

WHERE [Status] = 'Applied'
  AND [AppliedDate] <= DATEADD(day, -7, SYSUTCDATETIME())

Each matching application becomes a structured log entry. There is no notification call and no record of a completed reminder. The implementation is available in JobTrack’s FollowUpReminder.cs.

An application that remains eligible will appear again on the next scan. That is expected for a recurring report of current conditions. Replacing the log statement with an email call would give that repetition a user-visible side effect: the same person could receive a reminder every five minutes.

The first design decision is therefore the meaning of one reminder.

Define the reminder before choosing a key

For an initial learning implementation, I would choose one follow-up reminder per application, addressed to its owner. A later reminder would require an explicit new occurrence. That keeps the rule small enough to explain and test.

An illustrative identity could be:

application:42 / kind:follow-up / occurrence:1

Every attempt to deliver that reminder would reuse its identity. Generating a new identifier on every scan would describe new work each time and defeat duplicate prevention.

Changing an application’s status back to Applied would not silently create a second reminder under this rule. If restarting follow-up should create new work, I would model that as a new occurrence. Similarly, changing a template would not automatically justify notifying everyone again.

Amazon’s discussion of idempotent APIs explains why identifying caller intent matters when distinguishing a retry from a new operation. In JobTrack, I would apply that principle to the reminder occurrence. Making retries safe with idempotent APIs

Let SQL Server enforce one reminder record

A check in application code is insufficient when two executions can reach the same decision:

Worker A: no reminder exists
Worker B: no reminder exists
Worker A: insert reminder
Worker B: insert reminder

For a proposed Reminders table, I would enforce the identity in the database. This illustrative index assumes the table and its non-null identity columns have already been created:

CREATE UNIQUE INDEX UX_Reminders_Identity
ON dbo.Reminders (
    JobApplicationId,
    ReminderKind,
    OccurrenceNumber
);

This assumes application identifiers are globally unique within JobTrack’s database. A system with tenant-local identifiers would also need the tenant in the uniqueness boundary. Access to reminder records would still be scoped to the authenticated owner; an identifier is not an authorization mechanism.

When competing inserts target the same identity, the application should handle that specific duplicate-key outcome by finding the existing reminder. Other database failures must remain failures. Treating every exception as “already exists” would hide outages and invalid data.

The index protects creation of the reminder record. It does not prove that a notification was sent once.

Separate discovering work from delivering it

I would have the scanner persist pending reminder records, then let a worker attempt delivery. For this small project, SQL Server can hold the pending work; introducing a message broker is not a prerequisite.

Each record would need its identity, delivery state, attempt count, next-attempt time, and enough information to diagnose failures. A worker would atomically claim a due record with an expiring lease before calling the provider. Completion updates would check the claim token so that an old worker cannot overwrite a newer worker’s state.

A lease makes abandoned work recoverable, but expiry does not stop a slow worker from continuing an external call. I would still need protection at the provider boundary.

Before attempting delivery, the worker should also check whether the application is still eligible. A user may have moved it to a different status since the scanner created the reminder. That check reduces obsolete reminders, although a status change can still race with an external send. The product needs a clear rule for reminders already in flight.

The difficult failure happens after sending

Adding a Sent flag seems attractive until the process can stop between steps.

Mark it sent before contacting the provider, and a crash can lose the reminder. Contact the provider first, and a crash before saving the result leaves uncertainty:

Worker sends reminder
Provider accepts it
Worker stops before recording acceptance
Pending work becomes eligible for another attempt

The database cannot tell whether that first external call succeeded. Stripe’s idempotency article describes the same uncertainty when an operation succeeds but its response does not reach the caller. Designing robust and predictable APIs with idempotency

If the selected provider supports idempotent submission, I would pass the same persisted reminder key on each attempt and keep the request payload stable. I would verify the provider’s key scope, retention window, and behavior when a key is reused with different parameters before relying on that contract.

Without provider support or a reliable way to query the earlier result, retrying an uncertain send can produce a duplicate. Declining to retry can lose a reminder. A local flag cannot eliminate that tradeoff.

I would also distinguish provider acceptance from delivery to a person’s inbox. The worker can record what the provider acknowledged; later delivery events may report a different outcome.

Where an outbox would fit

Suppose a future workflow both updates application state and schedules a notification. Saving the state and publishing a message separately creates a gap: one operation can succeed while the other fails.

The transactional outbox pattern records the state change and the outgoing intent in one database transaction. A separate dispatcher processes that intent afterward. Microsoft’s example implements the pattern with Cosmos DB; the same transaction-boundary principle would guide a SQL Server design, using SQL Server’s own transaction facilities. Microsoft’s transactional outbox guidance

For JobTrack’s current recurring scan, a durable reminder table may be enough. I would add an outbox where a state change and delivery intent must commit together, rather than introducing another table simply because the pattern is familiar.

An outbox preserves committed intent for later processing. A dispatcher can still repeat delivery after a crash, so it does not resolve the external-send uncertainty by itself.

The experiments I would run

I would start with SQL Server and a fake notification provider that can record acceptance, return failures, and deliberately lose responses. These are planned checks, not results from an existing test suite:

1. Repeat the scan. Keep an application eligible across several runs. Verify that only one reminder occurrence is stored.
2. Race two inserts. Coordinate two database connections so they attempt the same identity together. Verify the uniqueness constraint and the duplicate-key handling.
3. Stop before sending. Claim a reminder, stop the worker, and advance beyond the lease expiry. Verify that another worker can recover it.
4. Lose the acceptance response. Have the fake provider accept a send and then time out. Compare retries with and without provider-side deduplication, including the case where its key has expired.
5. Change eligibility. Move the application out of Applied before delivery. Verify cancellation of pending work and document the remaining in-flight boundary.
6. Create a later occurrence. Verify that a deliberately scheduled second reminder is accepted while retries of the first remain the same operation.

I would inspect persisted records and provider acceptance counts, not just successful function invocations. Useful diagnostics would include the reminder ID, occurrence, claim token, attempt number, and provider request ID without logging message bodies or recipient addresses.

Retries would have a bounded schedule and a reviewable failure state. Retrying indefinitely would make a broken configuration look like background activity instead of a problem requiring attention.

What this review taught me

Reading JobTrack’s scanner made the boundary clear: eligibility is a question about current data; delivery is a history of attempts and externally observed outcomes.

The next implementation should make that history explicit. I want to be able to explain why a reminder exists, whether a new attempt represents the same intent, and what evidence supports its recorded outcome. Those answers would make the feature easier to recover, test, and operate as it grows beyond a scheduled query.