The OSINT investigator's workbench. One case, one folder, every tool. Local-first: your media and your investigations never leave your machine.
JavaScript
/**
* What a sheet's column can know about itself.
*
* The rule this module exists to serve, and never to break:
*
* > The CSV keeps **the words**. A status, a list of values, a quantity, a lat/lon are
* > text a spreadsheet shows as it found them. The sidecar keeps **what the app knows
* > about the column** — its role, its vocabulary, its separator, its unit. Losing the
* > sidecar costs chips and colours, never a finding.
*
* So nothing here rewrites a cell. Everything here *reads* one, and every reader is
* allowed to fail: `To be found` in a coordinates column, `?` in a date column and
* `OK en cours` outside a status vocabulary are what the real binders hold on every
* page. A role that refused them would refuse the work, so a role is a lens, never a
* validator — the value stays, it is shown as it is, and the filter can still find it.
*
* Pure: text in, readings out. No DOM, no case, no fetch, and no import from
* `lib/sheet.js` — that one imports this, and the arrow points one way.
*/
/** Every role a column may hold. Mirrors `engine/sheetroles.ROLE_KINDS`. `url` is the
* column a promotion files as sources, `row` points at another row of the same sheet,
* `offset` carries a time relative to a named anchor.
*
* `locked` is the odd one: a column whose text the **app** owns and the analyst reads. It
* exists because a sheet built out of the case is a view of the case, and a view somebody
* can type over is a view that starts lying the first time they do. Unlike `stamped` and
* `computed` it carries a link — the point of those columns is that the cell opens the
* entity — so `linkable()` admits it where it refuses every other role. */
export const ROLE_KINDS = [
'state',
'choice',
'boolean',
'number',
'latlon',
'when',
'picture',
'url',
'row',
'offset',
'stamped',
'computed',
'locked',
];
/** What a cell of a `picture` column has to hold before anything is drawn from it: an
* address, not somebody's filename. A path off another disk is a picture nobody but them
* can see, and drawing a broken frame for it would say the cell was wrong when it is
* merely elsewhere. */
const PICTURE_URL = /\bhttps?:\/\/[^\s<>"'()[\]]+/i;
/** Addresses that end in something a browser draws. Used to *suggest* the role — a URL
* with no extension is an image often enough that a declared column does not consult
* this — and to recognise a file **inside the case**, where there is no host to ask. */
const IMAGE_PATH = /\.(?:jpe?g|png|gif|webp|avif|bmp|svg)(?:[?#].*)?$/i;
/** The picture one cell points at, or null. The first address it holds: a cell carrying
* a photo and its archive copy is one row of work, and a strip of two thumbnails in a
* cell thirty pixels tall would be neither. */
export function pictureUrl(cell) {
const found = String(cell ?? '').match(PICTURE_URL);
return found ? found[0].replace(/[.,;:!?]+$/, '') : null;
}
/**
* A cell of a picture column read as **a file of this case**, or null.
*
* The gap this closes was the plainest one in the tool: a geolocation index is worked on
* the images the case already holds, and a picture column that only understood `http`
* could draw a stranger's photo and not the case's own. The Notebook could already cite
* case media; the grid could not.
*
* A case-relative path and nothing else — no scheme, no leading slash, no `..` — because
* the cell is text a collaborator will read in a spreadsheet and `media/quai-sud.jpg` is
* the only spelling that means the same thing to them as to the app. Anything absolute is
* refused rather than drawn: it names a disk this case does not travel with.
*/
export function picturePath(cell) {
const body = String(cell ?? '').trim();
if (!body || /^[a-z]+:/i.test(body) || body.startsWith('/') || body.startsWith('\\')) return null;
if (body.includes('..') || /[\r\n\t]/.test(body)) return null;
const path = body.replace(/\\/g, '/');
return IMAGE_PATH.test(path) ? path : null;
}
/**
* How a picture cell is drawn: an address to fetch, or a file to read out of the case.
*
* One answer for both, so the grid asks once and the two cannot drift into two rules about
* what a picture is. `kind` is what decides whether anything leaves the machine.
*/
export function pictureRef(cell) {
const url = pictureUrl(cell);
if (url) return { kind: 'url', value: url };
const path = picturePath(cell);
return path ? { kind: 'case', value: path } : null;
}
/** The state vocabulary a column starts with. Ordered, least done first: that order
* *is* the ranking the sort reads, so reordering the list reorders the column. */
export const STATE_DEFAULTS = ['to do', 'in progress', 'done', 'ruled out'];
/** And the colour each of those four is painted, because a worklist is read at a glance
* before it is read row by row: nothing started grey, work in flight blue, settled
* green, eliminated red. Seeded when a state column is born and the analyst's from then
* on — the defaults sit *under* what the role declares, so clearing the green off `done`
* clears it for good. Mirrors `engine/sheetroles.STATE_COLOURS`. */
export const STATE_COLOURS = {
'to do': 'grey',
'in progress': 'blue',
'done': 'green',
'ruled out': 'red',
};
/** The two words a boolean column starts with, in that order: yes first, so the sort puts
* what is true above what is not. Capitals because that is how the binders write them, and
* because `computed` writes the same pair — one column of `YES`/`NO` in the file, whether
* the app filled it or the analyst did. */
export const BOOLEAN_DEFAULTS = ['YES', 'NO'];
/** The colours the grid offers, for a row and for a chip alike. Mirrors
* `engine/sheets.ROW_COLOURS`, and `lib/sheet.js` re-exports it so there is one list on
* this side. Drawn from the annotation palette, never the amber accent: that one means
* selection, and a value painted with it would read as a selected value. */
export const ROW_COLOURS = ['red', 'orange', 'yellow', 'green', 'blue', 'grey'];
/** What a multi-value column is split on unless the analyst says otherwise. The
* binders' own separator, comma-space, which is also what a spreadsheet's own
* concatenations produce. */
export const DEFAULT_SEPARATOR = ', ';
/** The three things a `when` column can hold, which are the three the binders write: a
* date, a bare time of day whose date lives in the sheet's title, or both together. */
export const WHEN_SHAPES = ['date', 'time', 'datetime'];
/** The one answer a number column's footer gives. Asked rather than assumed: a total is
* what a column of counts wants and nonsense on a column of percentages, and a column
* of years wants neither. Mirrors `engine/sheetroles.NUMBER_SUMMARIES`. */
export const NUMBER_SUMMARIES = ['none', 'count', 'sum', 'mean', 'range'];
/** How long a unit may be. `%`, `€`, `km`, `rounds` — a label, not a sentence. */
const MAX_UNIT = 8;
/** How long a synchronisation anchor may be named. `IGLA launch`, `second impact` — read
* in a heading and in a Claim's reasoning, so a phrase and not a note. Mirrors
* `engine/sheetroles.MAX_ANCHOR_NAME`. */
export const MAX_ANCHOR_NAME = 60;
/** How many anchors one sheet may declare. An event has a handful of moments several
* videos can be lined up on; past that the sheet is a transcript. */
export const MAX_ANCHORS = 12;
/**
* What a computed column may be. Mirrors `engine/sheetroles.COMPUTED_NATURES`.
*
* `has_point` is the one the binders' `On map: YES/NO` actually meant: not "this row
* points at something" but "the thing it points at has a place". A brigade points at an
* entity and is not on a map.
*
* The other two are the number a **comparison grid** needs and had no way to get. This
* app has no formulas on purpose — a spreadsheet's second copy of the case is exactly what
* a sheet is not — but eleven candidates against six criteria asks one arithmetic question
* and asks it constantly: how far is this row done (`filled_of`), and how many of its
* criteria are met (`yes_of`). They are computed on the **server** like the other one,
* because a computed column is written into the CSV and the collaborator opening the file
* is owed the number rather than an empty column.
*
* `point` and `relations` are what `has_point` is not. It answers *whether* the case knows
* the row's subject; those two answer **what it knows** — the coordinates, and the entities
* at the far end of its edges. Without them a column pointed at the case said nothing about
* the case, so the coordinates and the parent unit went on being copied by hand into the
* column alongside, which is the retyping the whole bridge exists to end.
*
* `in_case` is the only one that reads `built` rather than `links`, because it is the only
* one asking about an entity that may be **gone**: a link naming something deleted is swept
* on the next read, so by the time anyone asks, the cell that would have answered is blank.
* The row that lost its proof is exactly the row worth finding.
*/
export const COMPUTED_NATURES = [
'has_point',
'filled_of',
'yes_of',
'point',
'relations',
'in_case',
];
/** The natures that count over a chosen set of columns rather than asking the graph. */
export const COUNTING_NATURES = ['filled_of', 'yes_of'];
/** The natures that follow **one named column's** link and restate what the case holds
* at the end of it. Named rather than swept, because a sheet may point at the case from
* a subject column and a place column both, and "whatever this row points at" would
* answer about whichever the walk reached first. */
export const LINKED_NATURES = ['point', 'relations'];
/** How many columns one of those may read. Mirrors `engine/sheetroles.MAX_COUNTED_COLUMNS`:
* a score over sixty columns is not a score. */
export const MAX_COUNTED_COLUMNS = 24;
/** How many distinct values a column may hold and still read as a set of answers.
* Mirrors `lib/sheet.js`'s own bound on the values a filter menu offers. */
const MAX_CHOICE_VALUES = 40;
function blank(value) {
return !String(value ?? '').trim();
}
/** What a yes and a no look like across the spellings the binders use. Read by detection —
* once a column is a boolean, its own two words are the ones that count — and by a
* `yes_of` score, for the columns nobody declared. Mirrors `engine/sheetroles.YES_WORDS`. */
export const YES_WORDS = new Set(['yes', 'y', 'true', 'oui', 'x', '1', 'ok', 'done']);
const NO_WORDS = new Set(['no', 'n', 'false', 'non', '-', '0', 'ko']);
// -- the role record ----------------------------------------------------------
/**
* One column's role, reduced to the fields its kind actually uses.
*
* Fields a kind does not use are dropped rather than carried: a column that was a
* `choice` and became a `when` would otherwise keep a vocabulary nobody reads, and the
* next reader would wonder which of the two the column is.
*/
export function normalizeRole(role) {
const kind = ROLE_KINDS.includes(role?.kind) ? role.kind : null;
if (!kind) return null;
const clean = { kind };
if (kind === 'state' || kind === 'choice' || kind === 'boolean') {
const values = Array.isArray(role.values)
? [...new Set(role.values.map((value) => String(value)).filter((value) => value !== ''))]
: [];
clean.values = values.slice(0, MAX_CHOICE_VALUES);
// A state column nobody has said anything about is born with the four words and the
// four colours together: the words alone would be a status column that reads as grey
// text, and painting four chips by hand is the kind of setup a default is for.
const born = kind === 'state' && !clean.values.length;
if (born) clean.values = [...STATE_DEFAULTS];
// Exactly two, because that is what makes one click a toggle rather than a menu. A
// third word would leave the cell with nowhere to go next.
if (kind === 'boolean') {
clean.values = clean.values.slice(0, 2);
while (clean.values.length < 2) clean.values.push(BOOLEAN_DEFAULTS[clean.values.length]);
// How the cell is drawn, and nothing more. A tick column is a yes/no column: the
// file still holds the two words, the sort still reads them, the filter still
// finds them. Its own kind would have been a second copy of all of that for a
// difference of one glyph — and a column whose file said nothing readable.
clean.tick = Boolean(role.tick);
}
// A colour per value, kept apart from the values themselves: the value is what a cell
// is matched against, so it must stay exactly the word the file holds. What the role
// says wins over the birth colours, value by value, so a colour can be changed and a
// colour can be removed.
clean.colours = Object.fromEntries(
Object.entries({ ...(born ? STATE_COLOURS : {}), ...(role.colours ?? {}) }).filter(
([value, colour]) => clean.values.includes(value) && ROW_COLOURS.includes(colour),
),
);
}
if (kind === 'choice') {
// Whether one cell may hold several values, and what they are written between. A
// separator or nothing: reading a quantity out of `2x S-125` used to live here too,
// and it was a count kept in a column of values — a count belongs in a number column
// beside a column saying what is being counted.
clean.multi = typeof role.multi === 'string' && role.multi ? role.multi : null;
}
if (kind === 'number') {
// What follows the number when it is written out — `%`, `€`, `km`. A unit rather
// than a list of formats: a percentage and a currency differ by the sign after the
// digits, and an enum of five would have had to grow a sixth the first time a
// column held tonnes.
clean.unit = typeof role.unit === 'string' ? role.unit.trim().slice(0, MAX_UNIT) : '';
// Where it is written. Beside the heading always, since that is where a spreadsheet
// puts it and the file holds the digits alone; after every cell only when the column
// says so, because `40 %` reads as the value where four hundred repeated `km` read
// as noise.
clean.unitInCells = Boolean(role.unitInCells);
// Which one answer the footer gives. `sum` is what a count column wants and the
// wrong answer for a column of percentages, which is exactly why it is asked.
clean.summary = NUMBER_SUMMARIES.includes(role.summary) ? role.summary : 'sum';
}
if (kind === 'when') {
// Which of the three the column holds. Declared rather than inferred on every read:
// the analyst can say "these are times" about a column that is still empty, and an
// inferred shape would flip under them as the first rows are filled.
clean.shape = WHEN_SHAPES.includes(role.shape) ? role.shape : 'date';
// Which number a slash date leads with. Guessing silently reverses twelve days a
// month, and the binders read `dd/MM/yyyy`, so that is the default and it is
// stated rather than inferred.
clean.dayFirst = role.dayFirst === undefined ? true : Boolean(role.dayFirst);
}
if (kind === 'row') {
// Which column's words name the other row. The binders' `Links with others` held
// unit names, and that is kept: a file whose links read `r7f3a` is a file the
// collaborator opening it cannot follow. Null means the first column that is not
// the key, resolved where the table is known.
clean.of = typeof role.of === 'string' && role.of ? role.of : null;
// A brigade lists several companies, so a cell holds several names.
clean.multi = typeof role.multi === 'string' && role.multi ? role.multi : null;
}
if (kind === 'offset') {
// Which anchor the cell is counted from. One column per anchor and no syntax in the
// cell: the binders held `start synchro` *and* `end synchro`, so a single anchor per
// sheet would not have survived the first real event.
clean.anchor = typeof role.anchor === 'string' ? role.anchor.trim().slice(0, MAX_ANCHOR_NAME) : '';
}
if (kind === 'computed') {
clean.of = COMPUTED_NATURES.includes(role.of) ? role.of : COMPUTED_NATURES[0];
if (COUNTING_NATURES.includes(clean.of)) {
// Which columns it counts, in the order they were chosen: the number does not
// depend on it, but the panel lists them back and a list that reshuffles itself
// reads as a different answer.
clean.columns = [...new Set((Array.isArray(role.columns) ? role.columns : []).map(String))]
.filter(Boolean)
.slice(0, MAX_COUNTED_COLUMNS);
}
if (LINKED_NATURES.includes(clean.of)) {
clean.from = typeof role.from === 'string' && role.from ? role.from : null;
}
if (clean.of === 'relations') {
clean.multi = typeof role.multi === 'string' && role.multi ? role.multi : DEFAULT_SEPARATOR;
}
}
return clean;
}
/** How a counting column reads in a heading or a footer: `3 of 6`, or nothing when it has
* been told to read no columns. Said once here, because the number in the cell is bare —
* the denominator repeated down four hundred rows would be four hundred copies of one
* fact. */
export function countedOf(role) {
return COUNTING_NATURES.includes(role?.of) ? (role.columns?.length ?? 0) : 0;
}
/** The colour a value's chip is painted, or null for the neutral one. */
export function valueColour(role, value) {
return role?.colours?.[value] ?? null;
}
/**
* A vocabulary with one value moved, added, renamed, painted or dropped.
*
* One function rather than five, because every one of them is the same edit — a list and
* a colour map, out the other side — and because the **order is the ranking** the sort
* reads. That is why nothing here sorts on its own: `to do → in progress → done` is the
* order of the work, and an editor that alphabetised it would have put `done` first.
*
* `at` is where the value is now; `to` is where it goes, or -1 to drop it. A value that
* is not in the list yet is appended.
*/
export function editVocabulary(role, { value, at = -1, to = null, colour = undefined }) {
const values = [...(role?.values ?? [])];
const colours = { ...(role?.colours ?? {}) };
const name = String(value ?? '').trim();
const from = at >= 0 ? at : values.indexOf(name);
if (to === -1) {
if (from === -1) return { values, colours };
delete colours[values[from]];
values.splice(from, 1);
return { values, colours };
}
if (from === -1) {
if (!name || values.includes(name)) return { values, colours };
values.push(name);
} else if (name && values[from] !== name) {
// A rename carries the colour with it: the colour belongs to the answer, not to the
// characters, and a value fixed for a typo that lost its paint reads as a new one.
if (values.includes(name)) return { values, colours };
const painted = colours[values[from]];
delete colours[values[from]];
values[from] = name;
if (painted) colours[name] = painted;
}
if (to !== null && to !== -1 && from !== -1) {
const moved = values.splice(from, 1);
values.splice(Math.max(0, Math.min(values.length, to)), 0, ...moved);
}
if (colour !== undefined) {
const target = name || values[from];
if (colour && ROW_COLOURS.includes(colour)) colours[target] = colour;
else delete colours[target];
}
return { values, colours };
}
/** The same values, ordered by their words. Asked for by name and never applied on its
* own: a state column's order is the order of the work, not the alphabet. */
export function sortVocabulary(role, { desc = false } = {}) {
const values = [...(role?.values ?? [])].sort((a, b) =>
a.localeCompare(b, undefined, { numeric: true, sensitivity: 'base' }),
);
return { values: desc ? values.reverse() : values, colours: { ...(role?.colours ?? {}) } };
}
/**
* A column's vocabulary against what its cells actually say.
*
* Three answers a worklist needs and the textarea could not give: how many rows hold each
* declared value, which declared values nothing uses, and which values the cells hold
* that the vocabulary has never heard of. The third is the one that matters on an
* imported binder — `OK en cours` sitting outside a list of four that were never in this
* file — and it is offered rather than corrected, because the file keeps the words.
*/
export function vocabularyUse(table, columnIndex, role) {
const totals = valueTotals(table, columnIndex, role) ?? [];
const held = new Map(totals.map((entry) => [entry.value, entry]));
const declared = role?.values ?? [];
const known = new Set(declared);
return {
counts: Object.fromEntries(declared.map((value) => [value, held.get(value)?.rows ?? 0])),
unused: declared.filter((value) => !(held.get(value)?.rows ?? 0)),
outside: totals.filter((entry) => entry.value && !known.has(entry.value)),
};
}
/**
* What a column looks like it holds, as a suggestion.
*
* Offered, never applied: the menu shows it and the analyst confirms, because a role
* imposed by a guess is a role that has to be undone before the work can start. Only
* the three readings a machine can be sure of are guessed — a point, a moment, a small
* closed set. A **state** is never guessed: whether `pass` means done or means skipped
* is a judgement about the investigation, not about the characters in the cell.
*/
export function detectRole(values) {
const filled = (values ?? []).map((value) => String(value ?? '').trim()).filter(Boolean);
if (filled.length < 2) return null;
const share = (test) => filled.filter(test).length / filled.length;
// Two thirds rather than all of them: the binders' columns are half-finished by
// definition, and a column of coordinates with `To be found` in a third of its rows is
// still a column of coordinates.
if (share((value) => parseLatLon(value)) >= 0.66) return 'latlon';
if (share((value) => parseWhen(value)) >= 0.66) return 'when';
// Suggested off the extension alone, and never off "it is a URL": a column of sources
// is URLs too, and turning one into a wall of thumbnails would fetch forty pages the
// analyst never asked for.
if (share((value) => IMAGE_PATH.test(String(value))) >= 0.66) return 'picture';
const distinct = new Set(filled);
// Two words and both of them a yes or a no: `YES/NO`, `true/false`, `oui/non`, `x/-`.
// Two arbitrary words are a set of values, not a question with two answers.
if (
distinct.size === 2 &&
[...distinct].every((value) => YES_WORDS.has(value.toLowerCase()) || NO_WORDS.has(value.toLowerCase()))
) {
return 'boolean';
}
if (distinct.size <= Math.min(MAX_CHOICE_VALUES, filled.length / 2)) return 'choice';
return null;
}
// -- numbers ------------------------------------------------------------------
const NUMBER_CELL = /^[~≈<>±]?\s*([+-]?[\d\u00a0 ']*\d(?:[.,]\d+)?)\s*%?$/;
/**
* A cell read as a number, or null.
*
* Strict on purpose. `1 234` and `12,5` are read, because a European export writes both,
* and a leading `~` or `>` is read because an estimate is still a number. `Only 9 in
* service?` is **not**: extracting a digit out of prose would also turn `AB-123` into 123,
* and a total built on that is a total nobody can check. What the role cannot read it
* reports, which is the honest half of the deal.
*/
export function parseNumber(text) {
const match = String(text ?? '').trim().match(NUMBER_CELL);
if (!match) return null;
const digits = match[1].replace(/[\u00a0 ']/g, '').replace(',', '.');
const value = Number(digits);
return Number.isFinite(value) ? value : null;
}
/**
* What a number column adds up to over the rows given, and what it could not read.
*
* Given **row indices**, not the table, and that is the whole point: the answer is about
* what is on screen. A sheet filtered to the twelve rows left to check should total those
* twelve, because the question being asked is about them.
*
* `unreadable` is the honest half of a role that lets a cell say `unknown`. A footer
* reading `20 over 2` on a column of fifteen rows was arithmetic nobody could check: the
* total is right, and it is right about two cells out of fifteen. The count is what says
* so, and the grid offers it as a filter.
*/
export function numberTotals(table, columnIndex, rowIndices) {
let count = 0;
let sum = 0;
let min = null;
let max = null;
let unreadable = 0;
for (const index of rowIndices ?? []) {
const cell = table.rows[index]?.[columnIndex];
const value = parseNumber(cell);
if (value === null) {
if (!blank(cell)) unreadable += 1;
continue;
}
count += 1;
sum += value;
min = min === null ? value : Math.min(min, value);
max = max === null ? value : Math.max(max, value);
}
// Floating point: `0.1 + 0.2` must not read as `0.30000000000000004` in a footer.
const tidy = (value) => (value === null ? null : Math.round(value * 1e6) / 1e6);
return {
count,
unreadable,
sum: tidy(sum),
mean: count ? tidy(sum / count) : null,
min: tidy(min),
max: tidy(max),
};
}
/**
* Whether the column's own lens can read this cell.
*
* The rule a role exists under is that it never refuses a cell — `To be found` in a
* coordinates column is what the real binders hold on every page. The cost of that is
* silence: a number column summing two cells out of fifteen looks exactly like one
* summing fifteen. This is the other half of the deal, and the grid spends it on a badge
* and a filter rather than on a refusal.
*
* An empty cell reads: blank is *unknown*, which is an answer, not a mistake.
*/
export function readsCell(role, cell) {
const body = String(cell ?? '').trim();
if (!body) return true;
const kind = role?.kind;
if (kind === 'number') return parseNumber(body) !== null;
if (kind === 'latlon') {
const point = parseLatLon(body);
return Boolean(point) && !point.outOfBounds;
}
if (kind === 'when') return parseWhen(body, role) !== null;
if (kind === 'picture') return pictureRef(body) !== null;
if (kind === 'offset') return parseOffset(body) !== null;
// Whether a row name finds its row is a question about the whole table, so it is
// answered where the table is (`lib/sheetRows.js`) and not here. Every cell reads.
if (kind === 'row') return true;
// A value outside the column's own words is readable in the sense that it is shown,
// and unreadable in the sense the analyst cares about: it is not one of the answers
// this column takes, so it is what "to check" means here.
if (isChipped(role)) return cellChips(body, role).every((chip) => chip.known);
return true;
}
/** How many of a column's filled cells its lens cannot read, over how many there are. */
export function readable(table, columnIndex, role) {
let total = 0;
let read = 0;
for (const row of table?.rows ?? []) {
const cell = row[columnIndex];
if (blank(cell)) continue;
total += 1;
if (readsCell(role, cell)) read += 1;
}
return { total, read, unreadable: total - read };
}
// -- lat/lon ------------------------------------------------------------------
const DECIMAL_PAIR =
/^\s*([+-]?\d{1,3}(?:[.,]\d+)?)\s*°?\s*([NnSs])?(\s*[,;/]\s*|\s+)([+-]?\d{1,3}(?:[.,]\d+)?)\s*°?\s*([EeWw])?\s*$/;
const DMS =
/(\d{1,3})\s*°\s*(\d{1,2})?\s*['′]?\s*([\d.]+)?\s*["″]?\s*([NnSsEeWw])/g;
function decimals(text) {
const at = String(text).replace(',', '.').indexOf('.');
return at === -1 ? 0 : String(text).length - at - 1;
}
function signed(value, hemisphere, negatives) {
const number = Number(String(value).replace(',', '.'));
return negatives.includes(String(hemisphere ?? '').toUpperCase()) ? -Math.abs(number) : number;
}
/**
* A cell read as a point, or null.
*
* Three shapes, because one binder column held all three: a decimal pair
* (`48.8566, 2.3522`), a decimal pair with hemispheres (`48.8566N 2.3522E`), and
* degrees-minutes-seconds (`48°51'24"N 2°21'08"E`).
*
* `decimals` is how precisely the cell was written, which is a claim about the ground:
* two decimals is about a kilometre, and a worklist full of two-decimal coordinates is
* a worklist of neighbourhoods presented as addresses. `outOfBounds` is reported rather
* than refused — a transposed pair is a finding about the file, not a parse failure.
*
* A comma is a decimal mark, so `48,8` is one number rather than two coordinates. It
* separates a pair only where it cannot be decimal: a space after it, a hemisphere letter,
* or a full stop already doing the job. Mirrors `engine/sheetroles.parse_latlon`.
*/
export function parseLatLon(text) {
const body = String(text ?? '').trim();
if (!body) return null;
const pair = body.match(DECIMAL_PAIR);
if (pair) {
const [, rawLat, latHem, separator, rawLon, lonHem] = pair;
if (separator === ',' && !body.includes('.') && !latHem && !lonHem) return null;
const lat = signed(rawLat, latHem, ['S']);
const lon = signed(rawLon, lonHem, ['W']);
if (!Number.isFinite(lat) || !Number.isFinite(lon)) return null;
return {
lat,
lon,
decimals: Math.min(decimals(rawLat), decimals(rawLon)),
outOfBounds: Math.abs(lat) > 90 || Math.abs(lon) > 180,
};
}
const parts = [...body.matchAll(DMS)];
if (parts.length === 2) {
const read = parts.map(([, deg, min, sec, hem]) => {
const value = Number(deg) + Number(min ?? 0) / 60 + Number(sec ?? 0) / 3600;
return { value: signed(value, hem, ['S', 'W']), hem: String(hem).toUpperCase() };
});
const lat = read.find((entry) => 'NS'.includes(entry.hem));
const lon = read.find((entry) => 'EW'.includes(entry.hem));
if (!lat || !lon) return null;
// Seconds resolve to about thirty metres, which is five decimal places of
// confidence the writer did not claim; four is the honest reading.
return {
lat: lat.value,
lon: lon.value,
decimals: 4,
outOfBounds: Math.abs(lat.value) > 90 || Math.abs(lon.value) > 180,
};
}
return null;
}
/** About how far apart two points written to this many decimals could be, in metres.
* One degree of latitude is 111 320 m, so the last digit written is the uncertainty. */
export function precisionMetres(places) {
return Math.round(111_320 * 10 ** -Math.max(0, places));
}
/** A point as one canonical string. The only function here that produces a cell rather
* than reading one, and it is never called on a save: normalising is an edit the
* analyst asks for, and it lands in the undo stack like any other. */
export function formatLatLon(point, places = 5) {
if (!point) return '';
return `${point.lat.toFixed(places)}, ${point.lon.toFixed(places)}`;
}
const EARTH_M = 6_371_000;
/** Metres between two points, on a sphere. Close enough at worklist distances, and it
* needs no projection to be right about "these two rows are the same place". */
export function distanceMetres(a, b) {
const toRad = Math.PI / 180;
const dLat = (b.lat - a.lat) * toRad;
const dLon = (b.lon - a.lon) * toRad;
const lat1 = a.lat * toRad;
const lat2 = b.lat * toRad;
const h =
Math.sin(dLat / 2) ** 2 + Math.cos(lat1) * Math.cos(lat2) * Math.sin(dLon / 2) ** 2;
return Math.round(2 * EARTH_M * Math.asin(Math.min(1, Math.sqrt(h))));
}
// -- offsets, and the anchor they are counted from ----------------------------
const OFFSET = /^([+-]?)(?:(\d+):)?(\d{1,3}):(\d{2})(?:[.,](\d{1,3}))?$/;
const BARE_SECONDS = /^([+-]?)(\d{1,5})(?:[.,](\d{1,3}))?\s*s?$/i;
/**
* A cell read as seconds away from an anchor, or null. Mirrors
* `engine/sheetroles.parse_offset`.
*
* `-00:01:50`, `00:04:04`, `1:05` and a bare `-110` — the four spellings a video player
* and a spreadsheet between them produce. Negative means *before* the anchor, which is
* what the binders' leading minus already meant.
*/
export function parseOffset(text) {
const body = String(text ?? '').trim();
if (!body) return null;
const found = body.match(OFFSET);
if (found) {
const [, sign, hours, minutes, seconds, fraction] = found;
if (Number(seconds) > 59 || (hours !== undefined && Number(minutes) > 59)) return null;
const total =
Number(hours ?? 0) * 3600 + Number(minutes) * 60 + Number(seconds) + Number(`0.${fraction ?? 0}`);
return sign === '-' ? -total : total;
}
const bare = body.match(BARE_SECONDS);
if (!bare) return null;
const value = Number(bare[2]) + Number(`0.${bare[3] ?? 0}`);
return bare[1] === '-' ? -value : value;
}
/** Seconds written the way the binders write them, `-00:01:50`. One spelling out,
* several in: what the analyst typed stays in the cell. */
export function formatOffset(seconds) {
const sign = seconds < 0 ? '-' : '';
const whole = Math.trunc(Math.abs(seconds));
const pad = (value) => String(value).padStart(2, '0');
return `${sign}${pad(Math.floor(whole / 3600))}:${pad(Math.floor(whole / 60) % 60)}:${pad(whole % 60)}`;
}
/**
* The absolute instant an offset lands on, as an ISO timestamp, or null.
*
* Drawn beside the cell so the relative column reads as an absolute time the moment the
* anchor is dated — and it stays a *reading*, never written into the cell: an inferred
* moment is a Claim, and `engine/sheetclaims.py` is the only thing that files one.
*/
export function offsetMoment(anchorAt, seconds) {
const at = Date.parse(String(anchorAt ?? ''));
if (!Number.isFinite(at) || seconds === null || seconds === undefined) return null;
return `${new Date(at + Math.round(seconds) * 1000).toISOString().slice(0, 19)}Z`;
}
// -- dates and times ----------------------------------------------------------
const SLASH = /^(\d{1,4})[/.-](\d{1,2})[/.-](\d{2,4})(?:[ T]+(\d{1,2}):(\d{2})(?::(\d{2}))?)?$/;
const CLOCK = /^(\d{1,2}):(\d{2})(?::(\d{2}))?$/;
const MONTHS = 'jan feb mar apr may jun jul aug sep oct nov dec'.split(' ');
const RFC = /^(?:\w{3},\s*)?(\d{1,2})\s+(\w{3})\w*\s+(\d{4})(?:\s+(\d{1,2}):(\d{2})(?::(\d{2}))?)?/i;
/**
* A moment as milliseconds, or NaN when the calendar has no such day.
*
* `Date.UTC` rolls a bad day forward — `29/02/2025` comes back as 1 March — so a column
* holding an impossible date sorted as though it were the day after, silently. Checked
* rather than rolled, and it is also what keeps this reader and
* `engine/sheetroles.parse_when` answering the same thing about the same cell, which is
* what a Claim dated off a column depends on.
*/
/**
* Whether this is a time of day rather than one that rolls over into the next.
*
* `Date.UTC` takes 99:00 and moves the day instead of refusing, and 12:30:75 keeps the day
* it was given — so a cell the server refuses used to sort here without a word. Asked before
* the moment is built, exactly as `engine/sheetroles._holds` asks it.
*/
function clockHolds(hour, minute, second) {
return !(hour > 23 || minute > 59 || second > 59);
}
function utc(year, month, day, hour = 0, minute = 0, second = 0) {
const key = Date.UTC(year, month - 1, day, hour, minute, second);
const back = new Date(key);
const same =
back.getUTCFullYear() === year && back.getUTCMonth() === month - 1 && back.getUTCDate() === day;
return same ? key : NaN;
}
/**
* A cell read as a moment, or null.
*
* Everything the three binders wrote: `dd/MM/yyyy`, `yyyy-MM-dd`, a bare `hh:mm`,
* either with a time appended, and the full RFC form an email header carries
* (`Sat, 03 Jan 2026 06:42:02 GMT`).
*
* A bare clock is kept as a **time of day** rather than pinned to some arbitrary date:
* the binder's `Local time` column holds `01:57` for an event whose date lives in the
* sheet's title, and inventing a date for it would be inventing evidence. `shape` says
* which of the two a reading is, and `key` sorts within its own shape.
*/
export function parseWhen(text, role = {}) {
const body = String(text ?? '').trim();
if (!body) return null;
const dayFirst = role.dayFirst === undefined ? true : Boolean(role.dayFirst);
const clock = body.match(CLOCK);
if (clock) {
const [, hour, minute, second] = clock.map(Number);
if (!clockHolds(hour, minute, second)) return null;
return {
shape: 'time',
key: hour * 3600 + minute * 60 + (second || 0),
text: `${String(hour).padStart(2, '0')}:${String(minute).padStart(2, '0')}`,
};
}
const rfc = body.match(RFC);
if (rfc) {
const month = MONTHS.indexOf(rfc[2].slice(0, 3).toLowerCase()) + 1;
if (month) {
if (!clockHolds(Number(rfc[4] ?? 0), Number(rfc[5] ?? 0), Number(rfc[6] ?? 0))) return null;
const key = utc(Number(rfc[3]), month, Number(rfc[1]), Number(rfc[4] ?? 0), Number(rfc[5] ?? 0), Number(rfc[6] ?? 0));
if (!Number.isFinite(key)) return null;
return { shape: 'moment', key, text: new Date(key).toISOString().slice(0, rfc[4] ? 16 : 10) };
}
}
const slash = body.match(SLASH);
if (slash) {
const [, one, two, three, hour, minute, second] = slash;
// A four-digit leading group can only be a year, whatever the column's convention.
const isoish = one.length === 4;
const year = Number(isoish ? one : three.length === 2 ? `20${three}` : three);
const day = Number(isoish ? three : dayFirst ? one : two);
const month = Number(isoish ? two : dayFirst ? two : one);
if (month < 1 || month > 12 || day < 1 || day > 31) return null;
if (!clockHolds(Number(hour ?? 0), Number(minute ?? 0), Number(second ?? 0))) return null;
const key = utc(year, month, day, Number(hour ?? 0), Number(minute ?? 0), Number(second ?? 0));
if (!Number.isFinite(key)) return null;
return {
shape: 'moment',
key,
text: new Date(key).toISOString().slice(0, hour ? 16 : 10),
};
}
return null;
}
/**
* Which way round a column already writes its dates.
*
* Read from the column rather than declared, and that is the point: a picker that wrote
* `2026-01-31` into a column of `31/01/2026` would restyle the file from a click, and the
* file keeps the words. Ties go to the slash form, which is what the binders use.
*/
export function dateSpelling(values) {
let iso = 0;
let slash = 0;
for (const value of values ?? []) {
const body = String(value ?? '').trim();
if (/^\d{4}[/.-]\d{1,2}[/.-]\d{1,2}/.test(body)) iso += 1;
else if (/^\d{1,2}[/.-]\d{1,2}[/.-]\d{2,4}/.test(body)) slash += 1;
}
return iso > slash ? 'iso' : 'slash';
}
/**
* Which of the three shapes a `when` column actually holds.
*
* The role is called *a date or a time* because the binders write all three: a column of
* `dd/MM/yyyy`, a column of bare `01:57` local times whose date lives in the sheet's
* title, and a column of both together. Read from the column rather than declared, so the
* picker a cell offers is the one the column is written in — offering a calendar for a
* column of clocks would be offering to put a date where the analyst deliberately did not.
*/
export function whenShape(values) {
let times = 0;
let dated = 0;
let withHour = 0;
for (const value of values ?? []) {
const read = parseWhen(value);
if (!read) continue;
if (read.shape === 'time') times += 1;
else {
dated += 1;
if (read.text.includes('T')) withHour += 1;
}
}
if (times > dated) return 'time';
return withHour * 2 >= dated && withHour > 0 ? 'datetime' : 'date';
}
/** The input type a shape is picked with. One place, so the editor and any test agree. */
export function pickerType(shape) {
return shape === 'time' ? 'time' : shape === 'datetime' ? 'datetime-local' : 'date';
}
/**
* A moment chosen from a picker, spelled the way this column spells moments.
*
* The picker hands over `HH:MM`, `YYYY-MM-DD` or `YYYY-MM-DDTHH:MM` whatever the locale;
* what lands in the cell is the column's own form, so picking in a `dd/MM/yyyy` column
* leaves it a `dd/MM/yyyy` column. On a date-only column an hour already in the cell is
* kept: `03/01/2026 06:42` is a moment somebody established, and a date picker has no
* business dropping the hour from it.
*/
export function spellWhen(picked, { shape = 'date', spelling = 'slash', keep = '' } = {}) {
const body = String(picked ?? '').trim();
if (!body) return '';
if (shape === 'time') return body.slice(0, 5);
const [datePart, timePart] = body.split('T');
const [year, month, day] = datePart.split('-');
if (!year || !month || !day) return '';
const date = spelling === 'iso' ? `${year}-${month}-${day}` : `${day}/${month}/${year}`;
if (shape === 'datetime') return timePart ? `${date} ${timePart.slice(0, 5)}` : date;
const clock = String(keep ?? '').match(/\b(\d{1,2}:\d{2}(?::\d{2})?)\b/);
return clock ? `${date} ${clock[1]}` : date;
}
// -- values, and how many of each ---------------------------------------------
/** A cell as the values it holds. One value unless the column says it is a list.
* Mirrors `engine/sheetroles.split_values`. Two kinds hold lists and for the same
* reason: a `choice` cell holds three pieces of equipment, a `row` cell holds the three
* companies of a brigade. */
export function splitValues(cell, role) {
const body = String(cell ?? '');
const separator = role?.kind === 'choice' || role?.kind === 'row' ? role.multi : null;
if (!separator) return blank(body) ? [] : [body.trim()];
return body
.split(separator)
.map((part) => part.trim())
.filter(Boolean);
}
/** A cell as chips: the value, how many of it, and whether the vocabulary knows it.
* An unknown value is a chip like any other — the binders write outside their own
* vocabulary constantly, and a lens that hid those would hide the work. */
/** Whether a column draws its cells as chips: a closed set of answers, of any size —
* plus a `row` column, whose answers are the other rows of this sheet and read the same
* way, one name per chip. */
export function isChipped(role) {
return ['state', 'choice', 'boolean', 'row'].includes(role?.kind);
}
/** The other of a boolean column's two words, for a cell that is clicked rather than
* edited. Anything outside the pair goes to the first: a cell holding `maybe` in a
* yes/no column is answered by clicking it, not by being left alone. */
export function flipBoolean(role, value) {
const [yes, no] = role?.values ?? [];
return String(value ?? '').trim() === yes ? no : yes;
}
/**
* What a tick box holds after it is clicked: nothing → yes → no → nothing.
*
* Three states rather than two, and for the reason the sort control has three: a box
* is drawn on an **empty** cell as well as a filled one, so a two-state toggle would
* make *not answered yet* and *no* look identical and leave no way back to the first.
* On a worklist that is the difference between four hundred rows checked and four
* hundred rows nobody has opened.
*
* A word outside the pair answers yes, the way `flipBoolean` does: a cell holding
* `maybe` is settled by clicking it.
*/
export function cycleTick(role, value) {
const [yes, no] = role?.values ?? BOOLEAN_DEFAULTS;
const held = String(value ?? '').trim();
if (!held) return yes;
if (held === yes) return no;
if (held === no) return '';
return yes;
}
/** Whether a cell reads as this column's yes, its no, or neither. What the tick box
* draws, so the three states are decided in one place. */
export function tickState(role, value) {
const [yes, no] = role?.values ?? BOOLEAN_DEFAULTS;
const held = String(value ?? '').trim();
if (!held) return 'blank';
if (held === yes) return 'yes';
if (held === no) return 'no';
return 'other';
}
export function cellChips(cell, role) {
const known = new Set(role?.values ?? []);
return splitValues(cell, role).map((value) => ({
value,
known: known.has(value),
colour: valueColour(role, value),
raw: value,
}));
}
/**
* Every value of a column, with how many rows hold it.
*
* One number, and it is the one a filter is about: *how many rows say `S-125`*. A cell
* holding the same value twice counts once, because the row is what the filter hands
* back. Nothing is cut here — `columnValues` pages the list, since how much of it fits a
* menu is the menu's question.
*/
export function valueTotals(table, columnIndex, role) {
const totals = new Map();
for (const row of table?.rows ?? []) {
for (const value of new Set(cellChips(row[columnIndex], role).map((chip) => chip.value))) {
const entry = totals.get(value) ?? { value, rows: 0 };
entry.rows += 1;
totals.set(value, entry);
}
}
return [...totals.values()].sort(
(a, b) => b.rows - a.rows || a.value.localeCompare(b.value),
);
}
// -- sorting ------------------------------------------------------------------
/**
* What one cell is worth to a sort, computed once for the whole column.
*
* The comparator used to do this work itself, which meant `parseWhen` ran twice per
* comparison — a hundred and twelve thousand times on twenty thousand rows, and again on
* every keystroke, because the sorted view is derived from the table. Measured on a full
* sheet that was eight hundred milliseconds of blocked main thread per cell typed.
*
* So a key is read **once per row** and the comparator only orders keys. It is the same
* move the filters already make one function up ("compiled once per column rather than
* once per cell"); the sort had been left out of it.
*
* Three answers, and they are not the same:
*
* - an array of numbers — what the role read, most significant component first;
* - `null` — the role can read this column but not this cell, which sorts **after**
* the ones it can, so a date column puts `?` at the end rather than in mid-January;
* - `undefined` — this role does not order anything, and the caller compares words.
*/
export function sortKey(role, value) {
const kind = role?.kind;
if (kind === 'state' || kind === 'boolean') {
// The vocabulary's own order is the ranking: `to do` before `done` because that is
// how the analyst wrote the list, not because of how the words spell.
const at = (role.values ?? []).indexOf(String(value ?? '').trim());
return [at === -1 ? Number.MAX_SAFE_INTEGER : at];
}
if (kind === 'when') {
const read = parseWhen(value, role);
// A column holding both a date and a bare clock cannot interleave them: they are
// different scales. Dates first, clocks after, each ordered among its own.
return read ? [read.shape === 'moment' ? 0 : 1, read.key] : null;
}
if (kind === 'latlon') {
const read = parseLatLon(value);
// North to south, then west to east: the reading order of a map, and the one that
// puts the rows of one area together. The latitude is negated so that both
// components read the same way — smaller is earlier.
return read ? [-read.lat, read.lon] : null;
}
if (kind === 'number' || kind === 'offset') {
// An offset sorts by its seconds and that is the point of it: `-00:01:50` before
// `00:04:04` is the order the videos actually run in, and it is usable long before
// anybody has worked out what time the anchor happened.
const read = kind === 'offset' ? parseOffset(value) : parseNumber(value);
return read === null ? null : [read];
}
return undefined;
}
/** Whether a role orders a column at all, so a caller can skip reading keys it
* would then ignore. */
export function sortsByRole(role) {
return sortKey(role, '') !== undefined;
}
/**
* Two keys ordered, or null when they do not settle it.
*
* Null is the whole design: the caller falls back to its own text comparison, so a
* column whose role reads half its cells still sorts, and this module never has to
* know how `lib/sheet.js` compares words.
*/
export function compareSortKeys(a, b) {
if (a === undefined || b === undefined) return null;
if (a === null && b === null) return null;
if (a === null || b === null) return a === null ? 1 : -1;
for (let at = 0; at < a.length; at += 1) {
if (a[at] !== b[at]) return a[at] < b[at] ? -1 : 1;
}
return null;
}
// -- how far along the work is ------------------------------------------------
/**
* What a column says about how much is left.
*
* Two readings, and the first needs no role at all — which is the point. The binder
* this tool most has to replace, a 468-row geolocation index, has no status column:
* its columns are a date, a title, a country, coordinates and a link. Its question,
* *how many are left to geolocate*, is the fill rate of one column. A progress reading
* defined only on a status vocabulary would show nothing on the sheet that needs it
* most.
*/
export function columnProgress(table, columnIndex, role) {
const rows = table?.rows ?? [];
const total = rows.length;
if (columnIndex === -1) return null;
const empty = rows.filter((row) => blank(row[columnIndex])).length;
if (role?.kind !== 'state') {
return { kind: 'fill', total, filled: total - empty, empty };
}
const counts = new Map((role.values ?? []).map((value) => [value, 0]));
let other = 0;
for (const row of rows) {
const value = String(row[columnIndex] ?? '').trim();
if (!value) continue;
if (counts.has(value)) counts.set(value, counts.get(value) + 1);
else other += 1;
}
return {
kind: 'state',
total,
empty,
other,
buckets: [...counts.entries()].map(([value, count]) => ({ value, count })),
};
}
/**
* Which column a sheet's progress should be read off, when nobody has said.
*
* A suggestion, never a setting: a status column if one has been declared, otherwise
* the emptiest column that is not the row's handle, because the column with the most
* gaps in it is the one being worked through. Returns null rather than guess when
* every column is full — a sheet with nothing left to fill has no progress to show.
*/
export function suggestProgressColumn(table, roles, keyName) {
const columns = table?.columns ?? [];
const declared = columns.find((name) => roles?.[name]?.kind === 'state');
if (declared) return declared;
let best = null;
columns.forEach((name, index) => {
if (name === keyName) return;
const empty = (table.rows ?? []).filter((row) => blank(row[index])).length;
if (empty > 0 && (!best || empty > best.empty)) best = { name, empty };
});
return best?.name ?? null;
}
// -- what the column says twice, and what sits too close ----------------------
/** The values a column repeats, with the rows holding them. Blank is not a duplicate:
* four hundred rows with nothing in a column are a worklist, not a mistake. */
export function duplicateGroups(table, columnIndex, role) {
const seen = new Map();
(table?.rows ?? []).forEach((row, index) => {
for (const chip of cellChips(row[columnIndex], role)) {
if (!chip.value) continue;
const key = chip.value.toLowerCase();
if (!seen.has(key)) seen.set(key, { value: chip.value, rows: [] });
const group = seen.get(key);
if (group.rows.at(-1) !== index) group.rows.push(index);
}
});
return [...seen.values()]
.filter((group) => group.rows.length > 1)
.sort((a, b) => b.rows.length - a.rows.length || a.value.localeCompare(b.value));
}
/** How many buckets a sweep may hold before it is refused. A sheet at the row bound
* with every row placed is 20 000 points, which buckets to far fewer than this. */
const MAX_BUCKETS = 200_000;
/**
* Rows whose points sit within *metres* of each other.
*
* Bucketed rather than compared pair by pair, and that is not an optimisation: at the
* sheet's own bound of twenty thousand rows a naive sweep is two hundred million
* pairs, which is a frozen tab. Each point falls in a cell of the given size and is
* compared against its own cell and the eight around it, so the work is linear in the
* rows and the answer is exact for the distance asked.
*
* `capped` says the list was cut, because a silent cut reads as "there are no more".
*/
export function nearbyPairs(table, columnIndex, metres = 200, { cap = 200 } = {}) {
const points = [];
(table?.rows ?? []).forEach((row, index) => {
const point = parseLatLon(row[columnIndex]);
if (point && !point.outOfBounds) points.push({ index, ...point });
});
const size = Math.max(1, metres);
const latStep = size / 111_320;
const buckets = new Map();
for (const point of points) {
// Longitude degrees shrink towards the poles; using the latitude step for both
// would make the cells too narrow near the equator and miss pairs near the poles.
const lonStep = latStep / Math.max(0.01, Math.cos((point.lat * Math.PI) / 180));
point.cell = [Math.floor(point.lat / latStep), Math.floor(point.lon / lonStep)];
point.lonStep = lonStep;
const key = point.cell.join(':');
if (!buckets.has(key)) buckets.set(key, []);
buckets.get(key).push(point);
if (buckets.size > MAX_BUCKETS) return { pairs: [], capped: true };
}
const pairs = [];
const done = new Set();
for (const point of points) {
for (let dLat = -1; dLat <= 1; dLat += 1) {
for (let dLon = -1; dLon <= 1; dLon += 1) {
for (const other of buckets.get(`${point.cell[0] + dLat}:${point.cell[1] + dLon}`) ?? []) {
if (other.index <= point.index) continue;
const seen = `${point.index}:${other.index}`;
if (done.has(seen)) continue;
done.add(seen);
const apart = distanceMetres(point, other);
if (apart <= metres) pairs.push({ rows: [point.index, other.index], metres: apart });
}
}
}
if (pairs.length >= cap) return { pairs: pairs.slice(0, cap), capped: true };
}
return { pairs: pairs.sort((a, b) => a.metres - b.metres), capped: false };
}