CSAT Delay and Reminder Developer Guide

🛠️ CSAT delay and reminder - Developer Guide

How a satisfaction survey is held back by Delay before sending, sent later by a background job only if the ticket is still closed, and followed by one optional reminder when nobody answers. Built for #301 (5.0.0), reported and suggested by Abdullah31H. Changelog #2783 (the delay, a fix) and #2784 (the reminder, a feature). User guide: Tickets → Customer Satisfaction Surveys (CSAT). The bug write-up: #301 CSAT "Delay before sending" was saved but never applied.

The one-line version: closing a ticket calls csatAutoOnClose(). With no delay it sends at once, exactly as before. With a delay it writes one row per ticket to csat_queue. The csat_delayed background job, started every 5 minutes by the built-in scheduler, claims each due row by deleting it, then sends the survey only if the ticket is still closed, not in the trash, and CSAT is still on Auto. The same job then calls csatSendReminders(), which claims each unanswered emailed survey by setting reminder_sent_datetime and sends one reminder with the same link. Neither path can send twice, and switching reminders on reminds only surveys whose reminder falls due from that moment (csat_reminder_since, changelog #2798).

---

1. The files

🗄️ schema · ⚙️ logic · 🔌 endpoint · 🖥️ UI · 🔗 cross-module · 🧪 tests

File	🎨	What happens there
database/freeitsm.sql	🗄️	csat_queue; ticket_csat_responses.reminder_sent_datetime and link_base
includes/db_verify_schema.php, includes/db_verify_indexes.php	🗄️	the same columns and indexes, so Database Verify adds them to an existing install
includes/db_verify_modules.php	🗄️	'csat_queue' => 'tickets' - the table's Database Verify group
api/system/db_verify.php	🗄️	seeds csat_reminder = '0' and csat_reminder_hours = '48'
includes/csat.php	⚙️	csatAutoOnClose(), csatSendDue(), csatSendReminders(), csatUseLinkBase(), csatSettingsForget(); sendCsatSurvey() stores link_base; csatBuildUrl() uses the public address
includes/services/tickets.php	🔗	the close path calls csatAutoOnClose() instead of sendCsatSurvey()
cron/csat_delayed.php	⚙️	the background job: the delayed surveys, then the reminders
includes/job_registry.php	🔗	csat_delayed registered: every 5 minutes, when it is needed, tied to the CSAT module switch
includes/public_url.php	🔗	publicBaseUrl(), publicBaseUrlSetting(), publicBaseUrlForget(), publicBaseUrlCache() - unchanged, now used by CSAT
includes/analyst_lifecycle.php	🔗	['csat_queue', 'analyst_id', 'history']
api/tickets/get_csat_settings.php, api/tickets/save_csat_settings.php	🔌	read and write csat_reminder and csat_reminder_hours; the save also stamps csat_reminder_since when the reminder is switched on
api/tickets/save_email_template.php	🔌	csat_reminder added to $validEvents
tickets/settings/index.php	🖥️	the reminder tick box and hours on the CSAT tab; CSAT reminder in both template event lists
lang/{en,de,es}/tickets.php, lang/{en,de,es}/reporting.php	🖥️	the delay and reminder help; the job's name, Delayed satisfaction surveys and reminders
README.md	🖥️	the reminder listed under CSAT
tests/csat-delay.php	🧪	24 checks; no mail or chat message sent

---

2. The schema

csat_queue - one waiting survey per ticket

CREATE TABLE IF NOT EXISTS `csat_queue` (
    `id`               INT NOT NULL AUTO_INCREMENT,
    `ticket_id`        INT NOT NULL,
    `due_datetime`     DATETIME NOT NULL,               -- UTC
    `analyst_id`       INT NULL,                        -- who closed it; stored on the survey as for an immediate send
    `link_base`        VARCHAR(255) NULL,               -- the site address it was closed from, for the survey link when none is configured
    `created_datetime` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_csat_queue_ticket` (`ticket_id`),
    KEY `ix_csat_queue_due` (`due_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Column	Meaning
ticket_id	unique, so a second close updates the row instead of adding one. That is how "closed again, the wait starts over" works
due_datetime	UTC, UTC_TIMESTAMP() + INTERVAL <delay> MINUTE at the close. ix_csat_queue_due serves the job's WHERE due_datetime <= UTC_TIMESTAMP()
analyst_id	who closed it. Passed to sendCsatSurvey(), so the survey is credited exactly as an immediate send would be
link_base	the site address at the moment of the close, read while there may still be a web request. The job has none (section 7)

• No foreign keys. A ticket that is permanently deleted leaves its row behind until it falls due. The job then finds no ticket and drops the row as gone (section 6).
• No company column. A queued survey belongs to its ticket's company, like the survey itself (section 10).

Two columns on ticket_csat_responses

    -- GH #301: the optional one reminder, and the site address the link used (for a reminder sent by a background job)
    `reminder_sent_datetime` DATETIME NULL,
    `link_base`          VARCHAR(255) NULL,

Column	Meaning
reminder_sent_datetime	NULL until the one reminder is claimed. Set before sending, so even a failed send counts as the one
link_base	the site address the survey's own link used. A reminder sent later by the job reuses it, so both emails point at the same place

The settings

All in system_settings. Database Verify seeds the reminder keys off:

            // Wait this many minutes after close before sending the survey email,
            // so the user has a chance to verify the fix actually held. 0 = send immediately.
            'csat_delay_minutes'              => '0',
            // GH #301: one optional reminder for an unanswered emailed survey - off by default.
            'csat_reminder'                   => '0',
            // …
            'csat_reminder_hours'             => '48',

Key	Values	Read by
csat_mode	off, auto, manual	csatAutoOnClose() (only auto sends on close), csatSendDue(), csatSendReminders() (anything but off)
csat_delay_minutes	0-10080	csatAutoOnClose(); 0 = send at once
csat_reminder	0 / 1	csatSendReminders()
csat_reminder_hours	1-336	csatSendReminders(), clamped again to CSAT_REMINDER_MAX_HOURS
csat_reminder_since	UTC Y-m-d H:i:s, or no row	csatSendReminders(). Written by save_csat_settings.php only when the reminder goes from off to on (changelog #2798). Never seeded: an install that switched reminders on before it existed has no row, and no limit

database/freeitsm.sql does not seed these keys. A new install runs Database Verify, and every reader has a default that matches the seed.

---

3. End to end

Analyst (or bulk action, REST API, workflow) closes ticket
  └─ TicketsService::updateTicket()  - status moves from not-closed to closed
       └─ sendTemplateEmail('ticket_closed')
       └─ csatAutoOnClose($conn, $ticketId, $actorId)
            ├─ csat_mode != 'auto'            → nothing
            ├─ csat_delay_minutes = 0          → sendCsatSurvey() now (as before #301)
            ├─ queue write fails (no table)    → sendCsatSurvey() now
            └─ otherwise                       → INSERT … ON DUPLICATE KEY UPDATE csat_queue

cron/run.php (once a minute)
  └─ jobSchedulerTick(): csat_delayed due (every 5 min) and needed?
       └─ spawns php cron/csat_delayed.php
            ├─ CSAT module switched off → JobRun::skipped, exit (the queue waits)
            ├─ JobRun::lock / start
            ├─ csatSendDue()        - due rows: DELETE to claim → still closed? still auto? → sendCsatSurvey()
            │                          (the new ticket_csat_responses row gets link_base)
            ├─ csatSendReminders()  - unanswered emailed surveys, due in the last 48 h:
            │                          UPDATE to claim → still closed? → sendTemplateEmail('csat_reminder' or 'csat_request')
            └─ JobRun::finish       - counts and a one-line summary on Background jobs

The customer clicks either email's link. Both carry the same token, so the public survey page stores one rating, and the second click sees the "already answered" page.

---

4. Closing a ticket: csatAutoOnClose()

The close path in includes/services/tickets.php used to call sendCsatSurvey() directly when the mode was auto. It now hands the decision to one function:

            if ($newIsClosed && !$oldIsClosed) {
                // …
                sendTemplateEmail($conn, $ticketId, 'ticket_closed', [
                    'ticket_closed_message' => trim((string)($in['closed_message'] ?? '')),
                ]);
                require_once __DIR__ . '/../csat.php';
                try {
                    // Now, or queued for after Tickets -> Settings -> CSAT's delay (GH #301).
                    csatAutoOnClose($conn, $ticketId, $actorId);
                } catch (Exception $csEx) {
                    error_log('CSAT auto-trigger failed for ticket ' . $ticketId . ': ' . $csEx->getMessage());
                }
            }

Every way a ticket closes (the reading pane, bulk actions, the REST API, workflows) goes through updateTicket(), so one hook covers them all.

function csatAutoOnClose(PDO $conn, int $ticketId, ?int $analystId): void {
    if (csatGetSetting($conn, 'csat_mode', 'off') !== 'auto') return;
    $delay = max(0, (int)csatGetSetting($conn, 'csat_delay_minutes', '0'));
    if ($delay > 0) {
        try {
            // The site address as it is NOW, while there may be a request to read it
            // from - the job that sends this later has none (see csatSendDue()).
            require_once __DIR__ . '/public_url.php';
            $linkBase = publicBaseUrl($conn);
            $linkBase = preg_match('#^https?://#i', $linkBase) ? mb_substr($linkBase, 0, 255) : null;
            $conn->prepare(
                "INSERT INTO csat_queue (ticket_id, due_datetime, analyst_id, link_base, created_datetime)
                 VALUES (?, UTC_TIMESTAMP() + INTERVAL ? MINUTE, ?, ?, UTC_TIMESTAMP())
                 ON DUPLICATE KEY UPDATE due_datetime = VALUES(due_datetime), analyst_id = VALUES(analyst_id),
                                         link_base = VALUES(link_base), created_datetime = VALUES(created_datetime)"
            )->execute([$ticketId, $delay, $analystId, $linkBase]);
            return;
        } catch (Throwable $e) {
            error_log('CSAT: could not queue the delayed survey for ticket ' . $ticketId . ' - sending now: ' . $e->getMessage());
        }
    }
    sendCsatSurvey($conn, $ticketId, $analystId);
}

The queueing query, on its own:

INSERT INTO csat_queue (ticket_id, due_datetime, analyst_id, link_base, created_datetime)
VALUES (?, UTC_TIMESTAMP() + INTERVAL ? MINUTE, ?, ?, UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE due_datetime     = VALUES(due_datetime),
                        analyst_id       = VALUES(analyst_id),
                        link_base        = VALUES(link_base),
                        created_datetime = VALUES(created_datetime)

• Why an upsert. uq_csat_queue_ticket makes the second close of a ticket replace the first row. A ticket closed, reopened and closed again waits the full delay from the latest close.
• Why link_base only when it starts with http. With no public address and no request, publicBaseUrl() returns BASE_URL alone, a bare path. Storing that would be worse than storing nothing.
• Why a failed queue sends at once. Before Database Verify there is no csat_queue. Losing a survey would be a regression, so the function falls through to the old behaviour. The TRAP block in includes/csat.php says so (section 11).
• The one-per-ticket guard is not checked here. sendCsatSurvey() checks it at send time, so the job honours it too.

---

5. The background job

Registered once, run by the scheduler

        // GH #301: surveys held back by Tickets -> Settings -> CSAT -> "Delay before sending".
        'csat_delayed' => [
            'command' => 'php cron/csat_delayed.php', 'web' => true,
            'interval' => 5, 'grace' => 30, 'max_runtime' => 10,
            // Needed while a delay is set on "auto", while any survey is still queued,
            // or while the optional reminder is on.
            'needed'  => fn(PDO $c) => jobNeedOne($c, "SELECT 1 FROM csat_queue LIMIT 1")
                                     || (jobSettingStrict($c, 'csat_mode') === 'auto' && (int)jobSettingStrict($c, 'csat_delay_minutes') > 0)
                                     || (jobSettingStrict($c, 'csat_mode') !== 'off' && jobSettingStrict($c, 'csat_reminder') === '1'),
        ],

    $jobModules = [
        // …
        'csat_delayed' => 'csat',   // GH #301
    ];

• Nothing to set up. cron/run.php runs every minute and starts every registered job that is due and needed (jobSchedulerTick() in includes/job_scheduler.php). A job added in a release gets its job_schedules row the first time the scheduler sees it. An install that runs jobs from its own scheduled tasks instead adds php cron/csat_delayed.php every 5 minutes (Scheduled tasks).
• needed is the same test the feature uses. With no delay, nothing queued and reminders off, the job is Not needed, is never started, and is never reported overdue. A queued row keeps it needed even after the delay is set back to 0, so the queue always drains.
• jobNeedOne() / jobSettingStrict() treat a missing table as "no" and any other failure as unknown (Background jobs - Developer Guide). Before Database Verify, csat_queue does not exist and the job is simply not needed.
• $jobModules ties the job to the CSAT module switch (#217): with CSAT off for the install, the job is not needed.

cron/csat_delayed.php

The standard job skeleton (Background jobs - Developer Guide § 4): the token for a web call, the module switch, the run lock, the run record.

    // GH #217: CSAT switched off for the install - nothing is sent (the queue waits).
    if (cronModuleSwitchedOff($conn, 'csat', 'csat_delayed', $trigger)) exit(0);
    if (!JobRun::lock($conn, 'csat_delayed', $trigger)) exit;

    $runId = JobRun::start($conn, 'csat_delayed', $trigger);
    $r = csatSendDue($conn);
    $rem = csatSendReminders($conn);   // the optional one reminder (Tickets -> Settings -> CSAT)
    $failed = $r['failed'] + $rem['failed'];
    $summary = "Sent {$r['sent']} survey(s); {$r['reopened']} ticket(s) reopened or gone, {$r['skipped']} not sent"
             . ($r['failed'] ? ", {$r['failed']} failed" : '')
             . "; {$rem['sent']} reminder(s)" . ($rem['failed'] ? ", {$rem['failed']} failed" : '') . '.';
    JobRun::finish($conn, $runId, $failed ? 'partial' : null,
        ['sent' => $r['sent'], 'reopened' => $r['reopened'], 'skipped' => $r['skipped'], 'failed' => $failed,
         'reminders' => $rem['sent']], $summary);
    echo "CSAT delayed surveys: $summary\n";
    if ($failed) exit(1);

• One job, two tasks. The reminders ride on the job that already runs every 5 minutes, rather than a second job with its own schedule and health row. The job's name changed to Delayed satisfaction surveys and reminders (reporting.jobs.name_csat_delayed).
• The run record carries sent, reopened, skipped, failed and reminders, and any failure makes the run partial.
• A web call needs the shared workflow_cron_token as ?token=. The command line skips it, as for the other crons.

---

6. Sending the due surveys: csatSendDue()

function csatSendDue(PDO $conn, int $limit = 200): array {
    $out = ['sent' => 0, 'reopened' => 0, 'skipped' => 0, 'failed' => 0, 'details' => []];
    require_once __DIR__ . '/public_url.php';
    $configured = publicBaseUrlSetting($conn) !== '';
    $due = $conn->prepare("SELECT ticket_id, due_datetime, analyst_id, link_base FROM csat_queue
                            WHERE due_datetime <= UTC_TIMESTAMP() ORDER BY due_datetime ASC LIMIT " . max(1, $limit));
    $due->execute();
    $claim = $conn->prepare("DELETE FROM csat_queue WHERE ticket_id = ? AND due_datetime = ?");
    $state = $conn->prepare("SELECT t.deleted_datetime, COALESCE(s.is_closed, 0) AS is_closed
                               FROM tickets t LEFT JOIN ticket_statuses s ON s.id = t.status_id WHERE t.id = ?");
    foreach ($due->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $ticketId = (int)$row['ticket_id'];
        $claim->execute([$ticketId, $row['due_datetime']]);
        if ($claim->rowCount() !== 1) continue;   // another run took it, or a new close moved it
        $state->execute([$ticketId]);
        $t = $state->fetch(PDO::FETCH_ASSOC);
        if (!$t || $t['deleted_datetime'] !== null || (int)$t['is_closed'] !== 1) {
            $out['reopened']++;
            $out['details'][] = ['ticket_id' => $ticketId, 'outcome' => $t ? 'reopened' : 'gone'];
            continue;
        }
        if (!moduleIsEnabled($conn, 'csat') || csatGetSetting($conn, 'csat_mode', 'off') !== 'auto') {
            $out['skipped']++;
            $out['details'][] = ['ticket_id' => $ticketId, 'outcome' => 'csat_off'];
            continue;
        }
        try {
            // TRAP: no request here, so with no public address configured the link
            // would be a bare path. Use the address the ticket was closed from, just
            // for this send. A configured address always wins.
            csatUseLinkBase($conn, $configured ? null : ($row['link_base'] ?? null));
            $r = sendCsatSurvey($conn, $ticketId, $row['analyst_id'] !== null ? (int)$row['analyst_id'] : null);
            publicBaseUrlForget();
            $r['result'] === CSAT_RESULT_SENT ? $out['sent']++ : $out['skipped']++;
            $out['details'][] = ['ticket_id' => $ticketId, 'outcome' => $r['result']];
        } catch (Throwable $e) {
            // … counted as failed, logged
        }
    }
    return $out;
}

Picking the due rows

SELECT ticket_id, due_datetime, analyst_id, link_base
  FROM csat_queue
 WHERE due_datetime <= UTC_TIMESTAMP()
 ORDER BY due_datetime ASC
 LIMIT 200

Oldest first, at most 200 a run. Anything left over goes 5 minutes later.

Claiming a row safely

DELETE FROM csat_queue WHERE ticket_id = ? AND due_datetime = ?

• The claim is the delete. Only the run whose DELETE removes the row (rowCount() === 1) sends it. Two overlapping runs (an old per-job cron line plus the scheduler, say) can both SELECT the row, but only one can delete it.
• Why due_datetime is in the WHERE. If the ticket was closed again between the SELECT and the DELETE, the upsert in section 4 has moved due_datetime. The delete then matches nothing, and the survey waits for its new due time instead of going early.
• Claimed before sending. If the send fails, the row is already gone, and that survey is not retried. That is the deliberate price of never sending twice.

The "still closed" check

SELECT t.deleted_datetime, COALESCE(s.is_closed, 0) AS is_closed
  FROM tickets t
  LEFT JOIN ticket_statuses s ON s.id = t.status_id
 WHERE t.id = ?

Result	Outcome
no row	the ticket was permanently deleted - gone, counted under reopened or gone
deleted_datetime set	in the trash - reopened
is_closed = 0 (or the status no longer exists)	reopened since the close - reopened, no survey
CSAT module off, or csat_mode no longer auto	csat_off, counted as skipped
otherwise	sendCsatSurvey(); its own result (sent, already_sent, no_template, no_mailbox)

sendCsatSurvey() runs all its usual pre-flight checks, so a queued survey obeys the one-per-ticket guard, the chat route (csat_in_channel), the template check and the mailbox check exactly as an immediate one does.

---

7. The survey link with no web request

Before #301, csatBuildUrl() read $_SERVER['HTTP_HOST'] and DOCUMENT_ROOT. From a background job both are missing, which would have mailed customers http://localhost/… or a bare path. With a connection it now goes through publicAbsoluteUrl():

function csatBuildUrl(string $token, ?PDO $conn = null): string {
    // GH #301: with a connection, the CONFIGURED public address (System -> Settings),
    // then the request's host - publicAbsoluteUrl()'s order. A delayed survey is sent
    // by a background job with no request at all, where the code below would build
    // http://localhost/... and mail it to a customer.
    if ($conn !== null) {
        require_once __DIR__ . '/public_url.php';
        return publicAbsoluteUrl($conn, 'csat?token=' . urlencode($token));
    }
    // … the old request-based build, for a caller with no connection
}

publicBaseUrl() answers, in order: the configured public web address; the request's scheme and host; BASE_URL alone. Its answer is cached per process in publicBaseUrlCache().

csatUseLinkBase() - borrowing the close's address for one send

function csatUseLinkBase(PDO $conn, ?string $base): void {
    require_once __DIR__ . '/public_url.php';
    publicBaseUrlForget();
    if ($base === null || $base === '' || publicBaseUrlSetting($conn) !== '') return;
    $root = &publicBaseUrlCache();
    $root = rtrim($base, '/');
}

• It pre-fills the cache that publicBaseUrl() reads, so every link built during the send (the survey link, and link_base on the new response row) uses the address the ticket was closed from.
• A configured address always wins. The function does nothing when one is set.
• The caller calls publicBaseUrlForget() straight after each send, so the next row never inherits this row's address.

link_base on the survey row

sendCsatSurvey() records the address its link used, in its own statement:

    // The site address this survey's link uses, so a reminder sent later from a
    // background job links to the same place (GH #301). Its own statement: the
    // column arrives with Database Verify, and a survey must still go without it.
    try {
        require_once __DIR__ . '/public_url.php';
        $base = publicBaseUrl($conn);
        if (preg_match('#^https?://#i', $base)) {
            $conn->prepare("UPDATE ticket_csat_responses SET link_base = ? WHERE id = ?")->execute([mb_substr($base, 0, 255), $responseId]);
        }
    } catch (Throwable) { /* before Database Verify */ }

    sendTemplateEmail($conn, $ticketId, 'csat_request', [
        'csat_link' => csatBuildUrl($token, $conn),
    ]);

That covers an immediate send (the request's address), a queued send (the queue's link_base, via csatUseLinkBase()) and a manual Request feedback. The reminder reads it back (section 8).

csatSettingsForget()

csatGetSetting() caches each key for the life of the process. The new forget function lets a long-running caller, or a test that changes settings mid-run, read them fresh:

/** Forget cached CSAT settings - for a long-running process or a test that changes them. */
function csatSettingsForget(): void {
    $cache = &csatSettingCache();
    $cache = [];
}

csatSendReminders() calls it first thing.

---

8. The one reminder: csatSendReminders()

/** Longest wait an administrator can set before the reminder: two weeks. */
const CSAT_REMINDER_MAX_HOURS = 336;
/**
 * A survey whose reminder fell due more than this long ago gets none. It is what
 * stops switching reminders on from emailing every unanswered survey ever sent -
 * and a job that was down for days from catching up with stale ones.
 */
const CSAT_REMINDER_CATCHUP_HOURS = 48;

function csatSendReminders(PDO $conn, int $limit = 200, ?callable $send = null): array {
    $out = ['sent' => 0, 'skipped' => 0, 'failed' => 0];
    csatSettingsForget();
    if (csatGetSetting($conn, 'csat_reminder', '0') !== '1' || csatGetSetting($conn, 'csat_mode', 'off') === 'off'
        || !moduleIsEnabled($conn, 'csat')) {
        return $out;
    }
    $hours = min(CSAT_REMINDER_MAX_HOURS, max(1, (int)csatGetSetting($conn, 'csat_reminder_hours', '48')));
    // Only reminders that fall due after the reminder was switched on (csat_reminder_since,
    // written by save_csat_settings.php): ticking the box must not remind everybody surveyed in
    // the catch-up window at once. Absent on installs that switched it on before this - no limit.
    $since = csatGetSetting($conn, 'csat_reminder_since', '');
    $sinceSql = preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $since) ? " AND r.sent_datetime + INTERVAL $hours HOUR >= '$since'" : '';
    $due = $conn->prepare(
        "SELECT r.id, r.ticket_id, r.token, r.link_base
           FROM ticket_csat_responses r
          WHERE r.rating IS NULL AND r.reminder_sent_datetime IS NULL AND r.channel_id IS NULL
            AND r.sent_datetime <= UTC_TIMESTAMP() - INTERVAL $hours HOUR
            AND r.sent_datetime >  UTC_TIMESTAMP() - INTERVAL " . ($hours + CSAT_REMINDER_CATCHUP_HOURS) . " HOUR$sinceSql
          ORDER BY r.sent_datetime ASC LIMIT " . max(1, $limit));
    $due->execute();
    $claim = $conn->prepare("UPDATE ticket_csat_responses SET reminder_sent_datetime = UTC_TIMESTAMP() WHERE id = ? AND reminder_sent_datetime IS NULL AND rating IS NULL");
    $state = $conn->prepare("SELECT t.deleted_datetime, COALESCE(s.is_closed, 0) AS is_closed
                               FROM tickets t LEFT JOIN ticket_statuses s ON s.id = t.status_id WHERE t.id = ?");
    $tpl = $conn->prepare("SELECT 1 FROM ticket_email_templates WHERE event_trigger = ? AND is_active = 1 LIMIT 1");
    $tpl->execute(['csat_reminder']);
    $event = $tpl->fetchColumn() ? 'csat_reminder' : 'csat_request';
    if ($event === 'csat_request') {
        // No reminder template and no survey template either: nothing could be sent,
        // so claim nothing - marking reminders "sent" would lose them for good.
        $tpl->execute(['csat_request']);
        if (!$tpl->fetchColumn()) return $out;
    }
    $send ??= fn(int $ticketId, string $ev, string $link) => sendTemplateEmail($conn, $ticketId, $ev, ['csat_link' => $link]);
    $configured = publicBaseUrlSetting($conn) !== '';
    foreach ($due->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $claim->execute([(int)$r['id']]);
        if ($claim->rowCount() !== 1) continue;   // answered or reminded meanwhile, or another run took it
        $state->execute([(int)$r['ticket_id']]);
        $t = $state->fetch(PDO::FETCH_ASSOC);
        if (!$t || $t['deleted_datetime'] !== null || (int)$t['is_closed'] !== 1) { $out['skipped']++; continue; }
        try {
            csatUseLinkBase($conn, $configured ? null : ($r['link_base'] ?? null));
            $send((int)$r['ticket_id'], $event, csatBuildUrl((string)$r['token'], $conn));
            publicBaseUrlForget();
            $out['sent']++;
        } catch (Throwable $e) {
            publicBaseUrlForget();
            $out['failed']++;
            error_log('CSAT: reminder for survey ' . $r['id'] . ' failed: ' . $e->getMessage());
        }
    }
    return $out;
}

The selection: never twice, never for old surveys

With Remind after = 48 hours, $hours = 48 and the window is 48 to 96 hours after the survey was sent:

SELECT r.id, r.ticket_id, r.token, r.link_base
  FROM ticket_csat_responses r
 WHERE r.rating IS NULL                                        -- not answered
   AND r.reminder_sent_datetime IS NULL                        -- never reminded
   AND r.channel_id IS NULL                                    -- emailed, not asked in a chat
   AND r.sent_datetime <= UTC_TIMESTAMP() - INTERVAL 48 HOUR   -- the reminder is due
   AND r.sent_datetime >  UTC_TIMESTAMP() - INTERVAL 96 HOUR   -- and fell due in the last 48 hours
   AND r.sent_datetime + INTERVAL 48 HOUR >= '2026-11-11 09:00:00'  -- and after reminders were switched on (only when csat_reminder_since is set)
 ORDER BY r.sent_datetime ASC
 LIMIT 200

Condition	Why
rating IS NULL	an answered survey needs no reminder
reminder_sent_datetime IS NULL	never twice
channel_id IS NULL	a request asked in a chat has its own seven-day reply window (CSAT_CHANNEL_REPLY_WINDOW_DAYS), and a reminder there would be a second chat message
sent_datetime <= now - hours	the wait has passed
sent_datetime > now - (hours + 48)	never for old surveys. A job that was down for days does not catch up with stale ones
sent_datetime + hours >= csat_reminder_since	no burst on switching on. Only a survey whose reminder falls due at or after the moment the box was ticked. Without it, ticking the box reminded at once everyone whose reminder had fallen due in the previous 48 hours (the catch-up window), although the help promises "only those whose reminder falls due from now on"

$hours is an integer clamped to 1-336, and CSAT_REMINDER_CATCHUP_HOURS is a constant, so building them into the SQL string is safe. $since is only built in when it matches ^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$ exactly; anything else (no row, an empty value, a hand-edited one) drops the condition rather than reaching the SQL.

When reminders were switched on: csat_reminder_since

api/tickets/save_csat_settings.php reads the old value before it saves, and stamps the time only on the change from off to on:

    // Switching the reminder ON (it was off): remember when, so only surveys whose reminder
    // falls due from now on are reminded - the help promises that, and an admin ticking the
    // box must not send a burst to everybody surveyed in the last two days.
    $wasOn = (string)$conn->query("SELECT setting_value FROM system_settings WHERE setting_key = 'csat_reminder'")->fetchColumn() === '1';
    // …
    $stmt->execute(['csat_reminder', $rem]);
    $stmt->execute(['csat_reminder_hours', (string)$remH]);
    if ($rem === '1' && !$wasOn) {
        $stmt->execute(['csat_reminder_since', gmdate('Y-m-d H:i:s')]);
    }

• UTC, from gmdate(), so it compares with sent_datetime (UTC) without conversion.
• Only off → on. Saving the CSAT tab again with reminders already on - to change the hours, say - keeps the original time, so the starting line does not move forward and lose reminders.
• Switching off and on again stamps a new time. Surveys whose reminder fell due while it was off are not reminded afterwards.
• Never cleared. Switching off leaves the row; it is simply not read while csat_reminder is 0.
• No row on older installs. An install that ticked the box before changelog #2798 has no csat_reminder_since and keeps the 48-hour catch-up window alone. The burst could only happen at the moment of switching on, which for those installs is already past.

Claiming before sending

UPDATE ticket_csat_responses
   SET reminder_sent_datetime = UTC_TIMESTAMP()
 WHERE id = ? AND reminder_sent_datetime IS NULL AND rating IS NULL

• Only the run whose UPDATE changes the row sends the reminder. A second overlapping run, or a customer who answered a moment ago, makes rowCount() 0 and the row is skipped.
• The claim comes before the state check. A survey whose ticket was reopened or trashed is claimed and then skipped, so it never gets a reminder later either, even if the ticket closes again.
• Even a failed send counts as the one. A reminder is a nicety. Sending two is worse than sending none.

Which template

Active templates	Event used
a CSAT reminder (csat_reminder) template	csat_reminder
none for reminders, a CSAT survey one	csat_request - the survey email again
neither	nothing is claimed; the function returns before the loop

The "neither" case matters. Claiming would set reminder_sent_datetime on surveys whose email could never go, and they would never be reminded once a template was added.

The new event is accepted by the template save endpoint:

$validEvents = ['new_ticket_email', 'ticket_assigned', 'analyst_assigned', 'ticket_closed', 'csat_request', 'csat_reminder', 'note_shared'];   // csat_reminder: GH #301

and offered in both event lists on Tickets → Settings → Email templates:

<option value="csat_reminder"><?php echo htmlspecialchars(t('tickets.settings.modals.template.event_csat_reminder')); ?></option>

The same link

The reminder rebuilds the link from the survey's own token, so it is the same URL as in the first email. One rating is stored whichever is clicked. The address comes from the survey's link_base when no public address is configured, through the same csatUseLinkBase() as section 7.

The $send parameter

 * @param ?callable $send  fn(int $ticketId, string $event, string $link) that sends the email; null = sendTemplateEmail().
 *                         Only tests pass one, so they can check who would be emailed without mailing anybody.

tests/csat-delay.php passes a spy that records [ticket, event, link] (section 12).

Reminders follow every emailed survey

The reminder is not tied to Auto. It runs while csat_mode is anything but off, so a survey sent by the manual Request feedback button is reminded too. The job's needed test matches: csat_mode !== 'off' && csat_reminder === '1'.

---

9. The settings screen

Tickets → Settings → CSAT, guarded by Cap::TICKETS_CSAT on save.

The markup

                <div class="form-group">
                    <label for="csatDelay"><?php echo htmlspecialchars(t('tickets.settings.csat.delay_label')); ?></label>
                    <input type="number" id="csatDelay" min="0" max="10080" step="1" style="max-width: 160px;">
                    <small style="display: block; color: var(--text-muted, #666); margin-top: 4px;"><?php echo t('tickets.settings.csat_tab.delay_help'); ?></small>
                </div>

                <!-- GH #301: one optional reminder for a survey nobody has answered -->
                <div class="form-group">
                    <label style="display: flex; align-items: center; gap: 10px;">
                        <input type="checkbox" id="csatReminder" onchange="document.getElementById('csatReminderHoursWrap').hidden = !this.checked">
                        <span><?php echo htmlspecialchars(t('tickets.settings.csat_tab.reminder')); ?></span>
                    </label>
                    <div id="csatReminderHoursWrap" hidden style="margin: 8px 0 0 26px;">
                        <label for="csatReminderHours"><?php echo htmlspecialchars(t('tickets.settings.csat_tab.reminder_hours')); ?></label>
                        <input type="number" id="csatReminderHours" min="1" max="336" step="1" style="max-width: 160px;">
                    </div>
                    <small style="display: block; color: var(--text-muted, #666); margin-top: 4px; margin-left: 26px;"><?php echo t('tickets.settings.csat_tab.reminder_help'); ?></small>
                </div>

The hours box shows only while the tick box is ticked. The labels are Delay before sending (minutes), Send one reminder if the survey is not answered and Remind after (hours).

The JavaScript

        async function loadCsatSettings() {
            try {
                const res = await fetch(API_TICKETS + 'get_csat_settings.php');
                const data = await res.json();
                if (!data.success) return;
                // …
                document.getElementById('csatDelay').value = data.delay_minutes ?? 0;
                // …
                document.getElementById('csatReminder').checked = data.reminder === '1';
                document.getElementById('csatReminderHours').value = data.reminder_hours ?? 48;
                document.getElementById('csatReminderHoursWrap').hidden = data.reminder !== '1';
                // …
            } catch (err) {
                console.error('Failed to load CSAT settings:', err);
            }
        }

        document.getElementById('csatSettingsForm')?.addEventListener('submit', async function(e) {
            e.preventDefault();
            const payload = {
                mode:           document.querySelector('input[name="csatMode"]:checked')?.value || 'off',
                delay_minutes:  parseInt(document.getElementById('csatDelay').value || '0', 10),
                one_per_ticket: document.getElementById('csatOnePerTicket').checked ? '1' : '0',
                in_channel:     document.getElementById('csatInChannel').checked ? '1' : '0',
                reminder:       document.getElementById('csatReminder').checked ? '1' : '0',
                reminder_hours: parseInt(document.getElementById('csatReminderHours').value || '48', 10),
                scale:          document.querySelector('input[name="csatScale"]:checked')?.value || 'stars',
            };
            // … POST to save_csat_settings.php, toast on the result
        });

The endpoints

api/tickets/get_csat_settings.php returns the two new keys with their defaults:

        'reminder'       => $rows['csat_reminder'] ?? '0',              // GH #301
        'reminder_hours' => isset($rows['csat_reminder_hours']) ? (int)$rows['csat_reminder_hours'] : 48,

api/tickets/save_csat_settings.php validates and writes them:

// GH #301: one optional reminder for an unanswered survey, after this many hours.
$rem   = ($data['reminder'] ?? '0') === '1' ? '1' : '0';
$remH  = (int)($data['reminder_hours'] ?? 48);
// …
if ($remH < 1 || $remH > 336) {
    echo json_encode(['success' => false, 'error' => 'The reminder must be 1-336 hours (two weeks) after the survey']);
    exit;
}
if ($delay < 0 || $delay > 10080) {
    echo json_encode(['success' => false, 'error' => 'Delay must be 0–1 week (10080 minutes)']);
    exit;
}
// …
    $stmt->execute(['csat_reminder', $rem]);
    $stmt->execute(['csat_reminder_hours', (string)$remH]);

The tick box is a strict '1' or '0', never a typed value. csatSendReminders() clamps the hours again, so a value written some other way cannot widen the window. The same save writes csat_reminder_since on the change from off to on - see section 8.

---

10. Company scope

Part	Scope
The settings (csat_mode, csat_delay_minutes, csat_reminder, csat_reminder_hours, csat_reminder_since)	install-wide, in system_settings, like every CSAT setting
csat_queue	no company column. A row belongs to its ticket's company
ticket_csat_responses	no company column, as before. The CSAT page scopes it through tickets.tenant_id
The job	one run serves every company. It reads the queue and the survey table without a company filter, which is right: it is not acting for any analyst
The email	sent from the mailbox that took the ticket in (templateGetMailboxForTicket()), with the template chosen by the requester's sender rules, exactly as an immediate survey

Nothing about the delay or the reminder can show one company's data to another. Each email goes to the ticket's own requester, from the ticket's own mailbox.

---

11. Traps

From includes/csat.php:

// TRAP: an upgrade must not stop surveys. With no delay set (0, the default), or
//   before Database Verification has made csat_queue, the survey goes at once,
//   exactly as before.
// TRAP: the job claims a row by DELETING it (rowCount 1) before sending, so two
//   overlapping runs cannot send one survey twice.

            // TRAP: no request here, so with no public address configured the link
            // would be a bare path. Use the address the ticket was closed from, just
            // for this send. A configured address always wins.

And the rules behind them:

• Never send sendCsatSurvey() straight from the close path again. tests/csat-delay.php greps includes/services/tickets.php for csatAutoOnClose($conn, $ticketId, $actorId) and fails if the old direct call returns.
• Claim, then check, then send. Both claims (the queue DELETE, the reminder UPDATE) happen before anything else. Moving the state check first would open a window for a second run.
• Always publicBaseUrlForget() after csatUseLinkBase(). The cache lives for the whole process. Forgetting to clear it would send the next survey with the previous ticket's address.
• A close by something with no request, on an install with no public address, stores no link_base, and its survey link is a bare path. Setting the public web address is the fix, and the user guide says so.
• Never claim a reminder that cannot be sent. The template check runs before the loop for this reason.
• The reminder window is hours to hours + 48, and never before csat_reminder_since. Widening CSAT_REMINDER_CATCHUP_HOURS changes how far a stalled job catches up.
• Only write csat_reminder_since on off → on. Writing it on every save would quietly drop the reminders of everyone whose reminder fell due between the save and the job's next run.

---

12. Tests

tests/csat-delay.php, 24 checks, on a throwaway database. It runs Database Verify first and skips unless FREEITSM_TEST_DATABASE names the database config.php uses:

FREEITSM_TEST_DATABASE=<the throwaway db config.php uses> php tests/csat-delay.php

It sends no mail and no chat message. Every active csat_request and csat_reminder template is switched off for the run, and so is Ask chat customers in their chat (csat_in_channel), so sendCsatSurvey() stops at its no template pre-flight. That proves the job reached the send, with nothing leaving the building. The reminder half passes a spy as $send. csat_reminder_since is cleared for the run, so the older reminder checks are judged without a switch-on time. Templates, settings (including csat_in_channel and csat_reminder_since), the ticket's status and the queue are all put back in finally.

(Changelog #2799. Before it, only csat_request templates were switched off. sendCsatSurvey() tries the chat route before the template check, so a ticket that had come in by chat, on an install with csat_in_channel on, would have had a real survey message sent to its customer; and a live csat_reminder template stayed active.)

$activeTpl = $conn->query("SELECT id FROM ticket_email_templates WHERE event_trigger IN ('csat_request', 'csat_reminder') AND is_active = 1")->fetchAll(PDO::FETCH_COLUMN);
// …
    if ($activeTpl) $conn->exec("UPDATE ticket_email_templates SET is_active = 0 WHERE id IN (" . implode(',', array_map('intval', $activeTpl)) . ")");
    setSetting($conn, 'csat_in_channel', '0');      // never a real chat message (see the header)
    setSetting($conn, 'csat_reminder_since', null); // reminders below are judged without a switch-on time
``` It refuses to run if `csat_queue` already holds rows, because those would be real surveys.

The job is run as a fresh process each time, as the scheduler runs it:

```php
$runCsat = function () use (&$conn): array {
    // A fresh process each time, as the scheduler runs it - and so csatGetSetting()'s cache is fresh too.
    exec(escapeshellarg(PHP_BINARY) . ' cron/csat_delayed.php 2>&1', $out, $code);
    return [$code, implode("\n", $out)];
};

The reminder spy:

    $sent = [];
    $spy = function (int $t, string $ev, string $link) use (&$sent) { $sent[] = [$t, $ev, $link]; };

Area	Proves
The close path	includes/services/tickets.php calls csatAutoOnClose(), not sendCsatSurvey() directly
With a delay	a 30-minute delay queues the survey due in about 30 minutes; no survey row is made yet; the job leaves a row that is not due; the job is registered, every 5 minutes, and needed
Due	the job takes the row off the queue, tries to send it (1 not sent, stopped by the switched-off template), and records an ok run for Background jobs
Reopened	a ticket reopened before its survey was due gets none (1 ticket(s) reopened), and the row is gone
Closed twice	a second close keeps one row and moves it to the new delay
No delay	nothing is queued; the job is not needed, so it is never reported overdue
The link	the close stores the address it was closed from (http://desk.example.test…); the job's link uses it; a configured public address always wins
The reminder	off by default; with no template at all nothing is claimed; on, exactly one reminder, for the unanswered emailed survey that just fell due, and not for one 10 hours old, one 200 hours old, an answered one or a chat one; the CSAT reminder event and the same link; never a second; none once the ticket is reopened; switched on just now: a reminder that fell due earlier is NOT sent (no burst); the job is needed while reminders are on

Five survey rows are made for the reminder checks, each with a token starting zz301, so the clean-up can find them:

    $due     = $mk(50, null, null, 'https://desk.example.test');   // unanswered, 50h ago: due
    $young   = $mk(10);              // too soon
    $old     = $mk(200);             // fell due long ago: never caught up
    $rated   = $mk(50, 4);           // answered
    $chat    = $mk(50, null, 999);   // asked in a chat

Also run after a change here: the background-job health and scheduler suites, and tests/analyst-lifecycle.php (it fails for any analyst column not in analystReferenceRegistry(); csat_queue.analyst_id is registered as history).

---

Related

• Tickets → Customer Satisfaction Surveys (CSAT) (user guide) · #301 CSAT "Delay before sending" was saved but never applied
• CSAT company scope and filters - Developer Guide
• Background jobs - Developer Guide · Background jobs · Scheduled tasks
• Public web address · Module availability - Developer Guide