Skip to main content
Version: 7.2

How To: Clear expired client access tokens with a database job

Currently, the API handles clearing expired client access tokens with a background task that runs on a configurable interval. This approach was chosen for "out of the box" experience to ensure a seamless experience from the get-go, owing to its simplicity and adaptability across various deployment environments. However, in this default implementation, when multiple instances of the API are operational, each instance triggers the background task, leading to redundant executions of the cleanup process. To prevent this, one option is to disable the 'DeleteExpiredTokens' background task on all instances of the API server except for one. Alternatively, you could entirely switch to an external process. A solution of this nature is detailed in this article. It demonstrates how to configure a scheduled job running on the database server that hosts the Ed-Fi-Admin database.

Implementation Steps​

  1. Disable API background task
  2. Verify that the database server job agent is enabled and running
  3. Create scheduled job which consists of:
    1. Job - to encapsulate all job steps
    2. Job Step - to execute the command(s)
    3. Schedule - to schedule when the job and all of its steps will be executed

Disable API background task​

"ScheduledJobs": [
      {
        "Name": "DeleteExpiredTokens",
        "IsEnabled": false,
        "CronExpression": "0 0/30 * 1/1 * ? *"
      }
    ]

Create Scheduled Job​

Modify and execute the SQL Server or PostgreSQL script below in order to create the scheduled job within your database server.

For Ed-Fi implementations running SQL, SQL Server Agent needs to be configured

Expand to view sample code...
USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @jobId binary(16)
DECLARE @jobStepId binary(16)
DECLARE @jobName NVARCHAR(1000) = N'Ed-Fi Admin Job - Delete Expired Tokens';

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @jobName)
IF (@jobId IS NOT NULL)
BEGIN
  EXEC msdb.dbo.sp_delete_jobstep @job_id = @jobId, @step_id = 1
    EXEC msdb.dbo.sp_delete_job @jobId
END

GO

-- *********************************************************************************
-- *********************************************************************************
-- Replace the loginName below with the login the job will run under.
-- To find existing logins, run the following:
-- EXEC sp_helplogins

DECLARE @loginName NVARCHAR(1000) = N'SET_LOGIN_NAME';

-- *********************************************************************************
-- *********************************************************************************

DECLARE @databaseName NVARCHAR(100) = N'EdFi_Admin';
DECLARE @serverName NVARCHAR(100) = N'(local)'
DECLARE @categoryClass NVARCHAR(1000) = N'JOB';
DECLARE @categoryType NVARCHAR(1000) = N'LOCAL';
DECLARE @categoryName NVARCHAR(1000) = N'[Ed-Fi Admin (' + @categoryType + ')]';
DECLARE @jobId binary(16)
DECLARE @jobStepId binary(16)
DECLARE @jobName NVARCHAR(1000) = N'Ed-Fi Admin Job - Delete Expired Tokens';
DECLARE @jobStepName NVARCHAR(1000) = N'Ed-Fi Admin Job Step - Delete Expired Tokens';
DECLARE @minuteInterval int = 30
DECLARE @jobScheduleName NVARCHAR(1000) = N'Ed-Fi Admin Job Schedule - Delete Expired Tokens every ' + CONVERT(nvarchar(100), @minuteInterval) + ' minutes';
DECLARE @jobDescription NVARCHAR(1000) = N'Deletes expired client access tokens from the Ed-Fi Admin database';
DECLARE @jobScheduleId uniqueidentifier = NEWID();
DECLARE @jobStepCommand NVARCHAR(MAX) = N'
DELETE
From ClientAccessTokens
WHERE Expiration < GETUTCDATE()
'

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=@categoryName AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=@categoryClass, @type=@categoryType, @name=@categoryName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@jobName,
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=@jobDescription,
  @category_name=@categoryName,
  @owner_login_name=@loginName, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobStepName,
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=@jobStepCommand,
  @database_name=@databaseName,
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobScheduleName,
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=4,
  @freq_subday_interval=@minuteInterval,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20220223,
  @active_end_date=99991231,
  @active_start_time=0,
  @active_end_time=235959,
  @schedule_uid=@jobScheduleId
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = @serverName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO