Rota Copy And Paste Developer Deep Dive

📋 Rota copy and paste — developer deep dive

How the staff rota's copy, paste and clear gestures are built: the one database key that makes pasting trivial, why a column cannot be pasted onto a row, how a drag-select coexists with a click that already meant something, and the handful of traps that cost a wrong test run each.

The user-facing description is in Tickets → Staff Rota. This page is the why.

Shipped: 1.9.0 (fc5c247d, cell and week) and 2.0.0 (41844e1d, 9e59c2dc — many cells, column, row, clear).

---

🗺️ The shape of the problem

The rota is a grid: analysts down the side, days across the top, one shift per cell.

          Mon      Tue      Wed      Thu      Fri
James   [Early]  [Early]  [  +  ]  [Late ]  [  +  ]
Laura   [Late ]  [  +  ]  [Early]  [Early]  [Early]
Raj     [  +  ]  [On-call] …

Four gestures put shifts into it, and they are not four versions of one operation — they differ in what is on the clipboard and in what "paste" means:

Gesture	Clipboard holds	Paste means
A cell	one shift	write it here
A selection / column / row, with a cell copied	one shift	stamp it into every target cell
A column (one day, everybody)	a shift per analyst	replace that day
A row (one analyst, all week)	a shift per day	replace that analyst's week
A week	a shift per (analyst, day)	replace that week

Stamp and replace are different enough to be different endpoints. Conflating them was tempting and would have been wrong: a stamp never removes anything, a replace must.

---

🔑 The one key that makes paste trivial

CREATE TABLE IF NOT EXISTS `ticket_rota_entries` (
    `id`          INT NOT NULL AUTO_INCREMENT,
    `analyst_id`  INT NOT NULL,
    `rota_date`   DATE NOT NULL,
    `shift_id`    INT NOT NULL,
    …
    UNIQUE KEY `uq_analyst_date` (`analyst_id`, `rota_date`),

A cell is a (analyst_id, rota_date) pair, and the table says so with a unique key. Everything else follows:

INSERT INTO ticket_rota_entries
       (analyst_id, rota_date, shift_id, location_id, is_on_call, created_datetime, updated_datetime)
VALUES (?, ?, ?, ?, ?, UTC_TIMESTAMP(), UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE
       shift_id    = VALUES(shift_id),
       location_id = VALUES(location_id),
       is_on_call  = VALUES(is_on_call),
       updated_datetime = UTC_TIMESTAMP()

Pasting onto an empty cell and pasting over a full one are the same statement. No "does a row already exist here" round trip, no branch, no race between the check and the write. Every paste path in the rota — single cell, stamp, line, week — uses that one statement.

Tip

This is why a cell copies the shift, never the entry id. The id belongs to the row you copied from; pasting writes a different (analyst, date) and must not touch the source. Carrying the id would have made that mistake available.

---

🧬 What travels on the clipboard: the key is the part that survives the move

This is the central design idea and the one everything else falls out of.

A clipboard entry has to be re-addressed when it lands somewhere else. Whatever is going to change in the move cannot be part of what you store.

// A COLUMN is one day across the team. The day changes; the person does not.
base.analyst_id = entry.analyst_id;

// A ROW is one analyst across the week. The person changes; the day does not.
base.day_offset = Math.round(
    (new Date(tg.rota_date + 'T00:00:00') - new Date(currentWeekStart + 'T00:00:00')) / 86400000);

Clipboard	Keyed by	Free to change on paste
cell	(nothing — one shift)	analyst and date
column	analyst_id	the date
row	day_offset	the analyst
week	analyst_id + day_offset	the week

The week clipboard has stored a day_offset rather than a date since 1.9.0 for exactly this reason — the whole point is landing it on a different week, so the date is the part that cannot survive.

🚫 …and this is why a column cannot be pasted onto a row

Not a UI restriction bolted on afterwards. A column's entries are keyed by analyst and a row's by day offset — they are not the same kind of thing. There is no honest mapping from "a shift for each of seven people" onto "a shift for each of five days", and inventing a plausible one would be worse than refusing.

So the menu refuses, and says where it can go:

if (rotaLineClipboard.kind !== kind) {
    rotaSetPasteBlocked(
        kind === 'col' ? t('…paste_row_onto_col') : t('…paste_col_onto_row'),
        kind === 'col' ? t('…paste_row_onto_col_why') : t('…paste_col_onto_row_why'));
    return;
}

You copied a whole day, which is one shift per analyst. It can only be pasted onto another day heading.

---

🔴 The two sets the user cannot see

The most dangerous thing in this feature, and the reason every endpoint looks paranoid.

Two categories of row exist in ticket_rota_entries and are not drawn on the grid:

1. Entries belonging to a deactivated analyst. get_rota.php lists analysts WHERE is_active = 1. Their rows are still there.
2. Weekend entries when rota_include_weekends is off. The grid draws Monday to Friday; Saturday and Sunday rows exist and simply are not rendered.

A "replace this week" or "clear this column" scoped by a date range destroys both. The user would have no way of knowing, because they were never on screen.

Every scope is derived server-side, in the endpoint, and never taken from the request:

$activeIds = array_map('intval',
    $conn->query("SELECT id FROM analysts WHERE is_active = 1")->fetchAll(PDO::FETCH_COLUMN));
$inList = implode(',', $activeIds);

$numDays = ($setting !== false ? (int)$setting : 0) ? 7 : 5;
$lastDay = (new DateTime($weekStart))->modify('+' . ($numDays - 1) . ' days');

$del = $conn->prepare(
    "DELETE FROM ticket_rota_entries
      WHERE rota_date BETWEEN ? AND ?
        AND analyst_id IN ($inList)"
);

The client already knows both sets. It is not asked. A delete scoped by whatever the caller sent is a delete scoped by whatever the caller sent.

Note the row-paste case is subtler: it deletes one analyst's week, and the range end is weekStart + numDays - 1, so with weekends off the range is Mon–Fri and Saturday falls outside it naturally rather than by a special case.

Testing this needs a positive control

"The Saturday survived" is worthless on its own — a request that did nothing at all also leaves Saturdays alone. Every refusal is tested with a write that must succeed in the same request:

$r = post('paste_rota_cells.php', ['…' => …, 'targets' => [
    ['analyst_id' => $a, 'rota_date' => SAT],   // must be refused
    ['analyst_id' => $a, 'rota_date' => TUE],   // POSITIVE CONTROL: must be written
]]);
ck('the Tuesday WAS written (so the refusal below is not a blanket one)', ($r['written'] ?? -1) === 1);
ck('the Saturday was refused', ($r['skipped_invalid'] ?? -1) === 1);

---

⚙️ One request, not thirty-five

A week is seven days times however many analysts you have. Firing save_rota_entry.php per cell would leave a half-pasted week behind the first failure, and the person would have no idea which half.

Every multi-cell operation is one transactional request:

Endpoint	Job
api/tickets/paste_rota_cells.php	stamp one shift into many cells
api/tickets/paste_rota_line.php	replace a column or a row
api/tickets/clear_rota_cells.php	empty many cells
api/tickets/paste_rota_week.php	replace a week

---

🧠 "Empty only" is decided inside the transaction

The menu offers Paste into all 7 and Paste into 6 empty cells. The obvious implementation is to let the browser send only the empty ones — it already knows which they are, it drew them.

That is wrong, and it is wrong in exactly the case the option exists for. The grid may be seconds old. If a colleague filled one of those cells while you were reading the menu, "don't overwrite anything" has to mean nothing that is there now, not nothing that was there when the page last loaded.

So the client sends every target plus a mode, and the server re-reads:

$conn->beginTransaction();

$occupied = [];
if ($mode === 'empty') {
    $analystList = array_values(array_unique(array_column($valid, 'analyst_id')));
    $dateList    = array_values(array_unique(array_column($valid, 'rota_date')));
    $sql = "SELECT analyst_id, rota_date FROM ticket_rota_entries
             WHERE analyst_id IN (" . implode(',', array_map('intval', $analystList)) . ")
               AND rota_date IN (" . implode(',', array_fill(0, count($dateList), '?')) . ")";
    $occStmt = $conn->prepare($sql);
    $occStmt->execute($dateList);
    foreach ($occStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $occupied[$row['analyst_id'] . '|' . $row['rota_date']] = true;
    }
}

Two details worth copying:

• It is a cross-product superset, then a key filter. Asking for the exact pairs would mean (a=? AND d=?) OR (a=? AND d=?) OR … — 35 clauses and 70 parameters. Asking for all analysts × all dates in the set is two IN lists, and the "$analyst|$date" map filters it down. The superset is at most the size of the grid.
• Integers are interpolated, dates are bound. $analystList is array_map('intval', …) so an IN list built by implode cannot carry anything but digits; the dates come from user input, so they get placeholders. Mixing the two styles in one statement is deliberate, not sloppy.

---

✅ Validate first, write second

Every endpoint judges the whole request before writing any of it:

$valid = [];
$skippedInvalid = 0;
foreach ($targets as $tgt) {
    …
    if (!isset($activeIds[$analystId]) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) { $skippedInvalid++; continue; }
    $d = DateTime::createFromFormat('Y-m-d', $date);
    if (!$d || $d->format('Y-m-d') !== $date) { $skippedInvalid++; continue; }
    if (!$includeWeekends && (int)$d->format('N') > 5) { $skippedInvalid++; continue; }
    $valid[$analystId . '|' . $date] = ['analyst_id' => $analystId, 'rota_date' => $date];
}

Three things fall out of that shape:

• The counts reported back are the whole truth. written, skipped_filled and skipped_invalid are known before the first write, so the toast cannot claim more than happened.
• Keying $valid by "$analyst|$date" de-duplicates for free, so a client that sends a cell twice writes it once.
• DateTime::createFromFormat plus the round-trip comparison. The regex alone accepts 2027-02-31; createFromFormat happily rolls it to 3 March; only $d->format('Y-m-d') !== $date catches it.

Whitelists are array_flipped so membership is an isset rather than an in_array scan:

$validShifts = array_flip(array_map('intval',
    $conn->query("SELECT id FROM ticket_rota_shifts")->fetchAll(PDO::FETCH_COLUMN)));

And a shift or location retired between the copy and the paste is counted and reported, never dropped silently — a paste that quietly loses somebody's shift is the worst thing this feature could do.

---

🖱️ The JavaScript: a drag-select on top of a click that already meant something

A plain click on a rota cell opens the shift editor. It always has, and it is the commoner action. Selecting cells cannot take that away.

The rule: selecting is a drag across cells, or a ctrl/cmd-click. A press and release inside one cell is never a selection.

Delegation is not optional here

loadRota() replaces grid.innerHTML wholesale on every load. Any listener bound to a cell dies with it. Every handler is bound once, to #rotaGrid, which survives:

(function wireRotaSelection() {
    const grid = document.getElementById('rotaGrid');
    if (!grid) return;

    grid.addEventListener('mousedown', function (e) {
        rotaSuppressClick = false;
        if (e.button !== 0) return;
        const cell = e.target.closest('.rota-cell');
        if (!cell) return;

        if (e.ctrlKey || e.metaKey) {
            const key = rotaCellKey(cell.dataset.analyst, cell.dataset.date);
            if (rotaSelection.has(key)) rotaSelection.delete(key);
            else rotaSelection.add(key);
            applyRotaSelection();
            rotaSuppressClick = true;
            e.preventDefault();
            return;
        }

        rotaDragAnchor = cell;
        rotaDragMoved = false;
    });

A drag is only a drag once it leaves the anchor

    grid.addEventListener('mouseover', function (e) {
        rotaHoverSet(e.target.closest('.rota-line-head'));

        if (!rotaDragAnchor) return;
        const cell = e.target.closest('.rota-cell');
        if (!cell || cell === rotaDragAnchor) return;   // ← the whole rule
        rotaDragMoved = true;
        rotaSelectRect(rotaDragAnchor, cell);
    });

cell === rotaDragAnchor is what keeps a click a click. Mouse jitter inside one cell never sets rotaDragMoved, so the click that follows opens the editor exactly as before.

mouseup on the document, not the grid

    document.addEventListener('mouseup', function () {
        if (rotaDragMoved) rotaSuppressClick = true;
        rotaDragAnchor = null;
        rotaDragMoved = false;
    });

Bound to the grid, a drag that runs off the edge of the table never ends and the next mouse move keeps extending the selection.

🔑 Killing the click in the capture phase

The cells carry an inline onclick="openRotaEntryModal(…)". A listener on the grid in the bubble phase runs after that — too late. In the capture phase it runs before the event reaches the target, and stopPropagation() there means it never arrives:

    grid.addEventListener('click', function (e) {
        if (rotaSuppressClick) {
            rotaSuppressClick = false;
            e.stopPropagation();
            e.preventDefault();
            return;
        }
        if (e.target.closest('.rota-cell')) clearRotaSelection();
    }, true);   // ← capture
})();

Warning

rotaSuppressClick = false on every mousedown is load-bearing. A drag that ends outside the grid sets the flag from the document-level mouseup, and no click ever fires on the grid to consume it. Without the reset, the flag survives and eats the next legitimate click — a cell that mysteriously refuses to open, once, after a sloppy drag.

The rectangle

Cells carry data-row and data-col purely so a selection is min/max arithmetic rather than DOM walking:

function rotaSelectRect(from, to) {
    const r1 = Math.min(+from.dataset.row, +to.dataset.row);
    const r2 = Math.max(+from.dataset.row, +to.dataset.row);
    const c1 = Math.min(+from.dataset.col, +to.dataset.col);
    const c2 = Math.max(+from.dataset.col, +to.dataset.col);

    rotaSelection.clear();
    document.querySelectorAll('#rotaGrid .rota-cell').forEach(cell => {
        const r = +cell.dataset.row, c = +cell.dataset.col;
        if (r >= r1 && r <= r2 && c >= c1 && c <= c2) {
            rotaSelection.add(rotaCellKey(cell.dataset.analyst, cell.dataset.date));
        }
    });
    applyRotaSelection();
}

The selection is a Set of "analystId|YYYY-MM-DD" strings rather than a list of elements — so it survives loadRota() replacing every element, and applyRotaSelection() is called at the end of renderRotaGrid() to re-mark it.

And because dragging across a table otherwise drag-selects its text, leaving the grid streaked blue under our own highlight:

.rota-grid { user-select: none; -webkit-user-select: none; }

---

💡 Lighting a whole column with one selector

The trick is in the markup, not the JavaScript: a day heading carries the same data-date its cells carry, and an analyst's name cell the same data-analyst.

function rotaLineCells(head) {
    const sel = head.dataset.date
        ? `.rota-cell[data-date="${head.dataset.date}"]`
        : `.rota-cell[data-analyst="${head.dataset.analyst}"]`;
    return Array.from(document.querySelectorAll('#rotaGrid ' + sel));
}

One attribute selector gets the whole line, for either orientation, with no index arithmetic — and the same call produces the paste targets. Highlighting and targeting cannot disagree, because they are the same function.

rotaHoverSet() is idempotent via a rotaHoverKey guard, so the mouseover firing continuously over one heading does no work after the first.

Keeping the line lit under its own menu

Opening the context menu moves the pointer off the grid onto the menu, which is a sibling element — so mouseleave fires and the highlight you need to see vanishes at the moment you need it:

grid.addEventListener('mouseleave', function () {
    if (!rotaCtxLine) rotaHoverClear();
});

rotaCtxLine is set before rotaHoverSet(head) in openRotaLineMenu(), precisely so the guard is already true by the time the pointer leaves.

---

📅 Two date traps in four lines of browser code

base.day_offset = Math.round(
    (new Date(tg.rota_date + 'T00:00:00') - new Date(currentWeekStart + 'T00:00:00')) / 86400000);

+ 'T00:00:00' is not decoration. new Date('2027-06-09') is parsed by the spec as UTC midnight; new Date('2027-06-09T00:00:00') is parsed as local midnight. Rota dates are wall-clock dates with no time in them, so the bare form shifts the whole grid by a day for anybody west of UTC.

Math.round, not Math.floor or integer division. Across a daylight-saving boundary two local midnights are 23 or 25 hours apart, so the difference is not an exact multiple of 86400000. floor would return 4 for a five-day span in the week the clocks change. round gives the day count people mean.

---

🚫 A disabled button swallows its own tooltip

When a paste is refused, the item is dimmed and carries a title explaining why. It is deliberately not disabled:

function rotaSetPasteBlocked(label, why) {
    const pasteBtn = document.getElementById('rotaCtxPaste');
    rotaPasteBlockedReason = why;
    pasteBtn.disabled = false;         // ← on purpose
    pasteBtn.style.opacity = '0.55';
    pasteBtn.title = why;
    document.getElementById('rotaCtxPasteLabel').textContent = label;
    rotaHidePasteEmpty();
}

A disabled button does not fire mouse events in several browsers, so the tooltip — the one thing that explains the refusal — is exactly what you cannot see. The click is refused in the handler instead, and repeats the reason as a toast for anyone who missed the hover:

if ((action === 'paste' || action === 'paste_empty') && blocked) {
    showToast(blocked, 'error');
    return;
}

---

📎 One clipboard, and why the week has its own

function rotaSetClipboard(cell, line) {
    rotaCellClipboard = cell;
    rotaLineClipboard = line;
}

A cell holds one shift; a line holds a different shift in every cell. They paste onto different things, so a menu offering both at once would make the reader work out which Paste is which. Copying either puts the other down — the way a clipboard actually behaves.

The week clipboard is deliberately excluded. It lives on its own toolbar button rather than in this menu, and copying a cell should not silently bin a week you set up three screens ago.

All of them are in-memory only (top-level let), never sessionStorage: a clipboard that outlives the tab means opening the rota tomorrow with a half-remembered week loaded and a Paste button offering to apply it.

---

🧪 Testing this thing

Four traps, each of which cost a wrong run.

1. Top-level let is not a window property. rotaCellClipboard, rotaSelection and rotaCtxLine are declared at the top level of a classic script, which puts them in the global lexical environment — window.rotaCellClipboard is undefined while the feature works perfectly. Setting it from a harness creates an unrelated property and the paste silently does nothing.

✅ To read one, indirect eval runs in global scope and can see them:

const clip = iframe.contentWindow.eval('rotaLineClipboard');

✅ To drive the feature, dispatch real events and call real .click()s — never poke state.

2. loadRota() detaches every element reference you are holding. A harness that grabbed heads[0] before a reload is dispatching events at an orphan: they never reach the grid's delegated listeners, and every hover assertion reads zero while the page is fine. Re-query after every reload.

3. --dump-dom fires before the async work finishes — but the test still ran. A headless run that printed one line had already executed every step and left its fixtures behind, so the next run failed on dirty state. Clean the fixture before each run, and ship results out with navigator.sendBeacon to a temporary receiver rather than reading them out of the dumped DOM.

4. A logged-out headless load serves the login page and reports no console errors. The harness is a temporary PHP page in the app root that sets the session and then iframes the real page, so the iframe inherits the cookie:

session_start();
$_SESSION['analyst_id'] = 1;
?>
<iframe id="f" src="tickets/rota.php"></iframe>

Caution

The development database is real data. Snapshot ticket_rota_entries to JSON first, work only in a far-future empty week (2027-06-07), delete it after, and compare the whole table field-for-field. ⚠️ And check created_datetime before assuming a diff is yours — two rows appeared mid-session during this work that turned out to be Ed testing the feature live.

---

🗂️ Files

File	Role
tickets/rota.php	the grid page, the entry modal, the shared .ticket-context-menu
assets/js/rota.js	everything above — rendering, selection, clipboards, menus
assets/css/rota.css	grid, .selected, .line-hover, user-select
api/tickets/get_rota.php	🔑 defines what the grid can see — active analysts, the weekend setting
api/tickets/save_rota_entry.php	one cell, upsert
api/tickets/paste_rota_cells.php	one shift → many cells, all or empty
api/tickets/paste_rota_line.php	a column or a row, replaces
api/tickets/clear_rota_cells.php	empty many cells
api/tickets/paste_rota_week.php	a week, replaces

---

✅ If you extend this

• Ask what the key is. Any new clipboard kind needs to know which part of the address survives the move, and that decides what it can be pasted onto.
• Derive the scope in the endpoint. get_rota.php is the definition of "what the user can see"; anything that deletes must agree with it, from its own query.
• Pair every refusal with a positive control in the same request, or the test proves nothing.
• Never take "which cells are empty" from the browser.