The Data Layer Behind My Photo Apps

Why this note

A handful of little apps read the same archive of photos and videos. At the time of writing there are three: the frise (a chronological timeline, Preact + HTM + urql), memories (a triage/slideshow wall, Solid), and doublons (a duplicate-photo batch resolver, Hyperapp) — and there may be more, or renamed ones, later. The render technology is deliberately different in each: that’s the point of the family, several honest takes on the same no-build, locality-of-behaviour, ESM-from-esm.sh recipe. But underneath, they all sit on one data layer — the same Postgres, the same PostGraphile, the same LAN auth gate, the same /ipfs/ gateway, the same lighttpd proxy that makes all of it same-origin.

This note is that shared layer. Each app note covers only what is particular to it (the frise’s ruler, memories’ lightbox, doublons’ heuristic) and links here for the common ground. Concretely it owns:

• The docs database and the photovideo shape every app reads.
• The calendar — a calendar_event table of time spans, fed from Google Calendar, that memories can overlay on the wall by date.
• The GraphQL contract — the PostGraphile fields the apps query, and the opaque-result-JSON trick the duplicates view leans on.
• The search DSL — the query language in the box, factored out as one shared ESM module (../shared/dsl.js) so it has a single source of truth across the apps rather than a copy each.

The docs database

The archive is a single Postgres database, docs, on the LAN host (192.168.2.14). Everything photo-related hangs off one table-ish entity, photovideo — every imported still or clip is a row, identified by its IPFS cid. The columns the apps actually read:

• cid — the content address (also the IPFS path of the original); the primary key.
• thumbnail_cid, web_cid — derived renditions: a small thumbnail for walls, a web-sized copy for the lightbox/enlarged tiles. Both are /ipfs/ paths.
• date — the capture timestamp (or, for date-only imports, midnight T00:00:00).
• mimetype — image/* or video/*; how a photo is told from a clip.
• owner — whose photo it is (konubinix, ayla, aylapomme, …); the archive holds more than one person’s photos.
• labels — free-text, ;-separated tags: the metadata these apps read and search (through Postgres FTS). The DB also carries tag=/=tagmap join tables that these apps don’t read.
• state — the triage lifecycle: todo / wip / done / bad / delete … . A soft delete is just state‘delete’=; the apps’ views exclude it, so a deleted row simply drops out of sight while staying recoverable.
• perceptualhash — a perceptual fingerprint; equal hashes are near-identical images. This is what doublons groups on.
• size — the original’s byte size; doublons uses it as a resolution proxy (there is no width/height column).
• camera_type — the capturing app/device when known (e.g. whatsapp for a re-share); doublons’ heuristic prefers a real camera over a WhatsApp recompression.

Prod is only ever read, never written: each test harness seeds a throwaway copy of docs with pg_dump and runs against that.

The calendar

Events are time spans, not labels — “Piscine, Beynost, 10:00–14:00”. They live in their own table, fed from Google Calendar, so memories can link a photo to the events its date falls within. Kept apart, the calendar is managed on its own; the apps only read it.

Each row is one occasion, keyed by Google’s per-instance event id — a recurring series arrives already expanded, so there is no recurrence logic here — and owner-tagged with the same owner_type as photovideo.owner, so a photo only meets its own owner’s events.

The feed (clk gcal to-db) is a mirror, not an append: on each run it makes this calendar’s own rows — the ones whose source records the account they came from — match Google across the window it pulls. New events insert, edits update in place, and an event Google no longer returns is deleted here too, so a search can never surface an occasion that was removed. Events outside that window are never pulled, so they keep accruing.

That delete is scoped to the one source. Events from anywhere else — added by hand, by another importer, or by a sibling calendar of the same owner (one owner spans several Google accounts) — carry a different source and are never in its reach, so the feed can only ever remove what it itself wrote. And it stands down on an empty pull: a real calendar never comes back empty, so a blank is a failed fetch, not a licence to delete everything.

CREATE TABLE IF NOT EXISTS calendar_event (
  id        text PRIMARY KEY,
  starttime timestamptz NOT NULL,
  endtime   timestamptz NOT NULL,
  summary   text,
  location  text,
  status    text NOT NULL DEFAULT 'confirmed',
  owner     owner_type NOT NULL,
  source    text,
  synced_at timestamptz NOT NULL DEFAULT now()
);

source is nullable on purpose: only the feed stamps it, so a row with none is one no feed wrote. Existing rows predate the column, so add it in place —

ALTER TABLE calendar_event ADD COLUMN IF NOT EXISTS source text;

— and clear, once, the rows a source-keyed delete can never reach. Because prod holds only feed events so far, a full clk gcal to-db pass stamps a source on every occasion Google still returns; a row left with none is then one Google no longer has and no feed owns — invisible to a delete that keys on source, so nothing else will ever collect it. A single sweep clears them. Run it once, by hand, after that pass — never from the feed: the moment a hand-added event (which may carry no source) exists, a recurring null-sweep would take it.

DELETE FROM calendar_event WHERE source IS NULL;

An app reads the calendar the other way round: given one photo, which events was it taken during? — the spans whose [starttime, endtime] brackets the photo’s moment, for that photo’s owner. That is a range test across two columns, and PostGraphile’s connection filter here keys only the primary key (see the contract), so the link is a function, not a where. events_at(d, o) is that lookup, owner-scoped in the body — a photo only ever meets its own owner’s events — so the caller passes just the photo’s date and owner. A cancelled event never matches: a call that didn’t happen is not an occasion a photo was taken during. PostGraphile reflects the SETOF as the eventsAt connection. Idempotent (CREATE OR REPLACE); new function, so restart PostGraphile afterwards to reflect it.

CREATE OR REPLACE FUNCTION events_at(d timestamptz, o owner_type)
  RETURNS SETOF calendar_event LANGUAGE sql STABLE AS $$
  SELECT * FROM calendar_event
  WHERE owner = o AND d BETWEEN starttime AND endtime AND status <> 'cancelled'
  ORDER BY starttime
$$;

Completing an event: search needs the calendar’s vocabulary — the event names in use — the way label completion needs the label vocabulary. event_completions returns the distinct summaries matching prefix — accent-folded and case-insensitive, and split on spaces so each word must appear, in any order (eur par and par eur both find Europa Parc) — kept only where the event overlaps the [since, until] window and holds at least one of its owner’s photos in its span — so the menu offers only occasions the query would actually turn up, never a name that lands on an empty wall; capped at 20. A NULL edge is open, so a query with no date bound weighs the whole timeline. It can’t see the wall’s state filter, so an event whose photos are all in a hidden state may still be offered. PostGraphile reflects it as the eventCompletions connection; restart afterwards to reflect it.

That per-event photo lookup has to be quick — it runs on every keystroke — but the archive holds ~75k photos and only three owners, so a naïve join hashes the whole set and checks each event against a 25k-row bucket (seconds). Two things keep it in milliseconds: photo and video each carry a (owner, date) index, and the subquery ends with OFFSET 0 — a fence that stops the planner flattening the EXISTS into that hash and holds it to a per-event index probe on the event’s narrow span instead.

CREATE INDEX IF NOT EXISTS photo_owner_date_idx ON photo (owner, date);
CREATE INDEX IF NOT EXISTS video_owner_date_idx ON video (owner, date);

CREATE OR REPLACE FUNCTION event_completions(prefix text, since timestamptz DEFAULT NULL, until timestamptz DEFAULT NULL)
  RETURNS SETOF text LANGUAGE sql STABLE AS $$
  SELECT ce.summary FROM calendar_event ce
  WHERE ce.summary IS NOT NULL AND ce.status <> 'cancelled'
    AND NOT EXISTS (SELECT 1 FROM regexp_split_to_table(trim(f_unaccent(prefix)), '\s+') term
                    WHERE term <> '' AND f_unaccent(ce.summary) NOT ILIKE '%' || term || '%')
    AND (since IS NULL OR ce.endtime   >= since)
    AND (until IS NULL OR ce.starttime <= until)
    AND EXISTS (SELECT 1 FROM photovideo pv
                WHERE pv.owner = ce.owner
                  AND pv.date BETWEEN ce.starttime AND ce.endtime
                  AND (since IS NULL OR pv.date >= since)
                  AND (until IS NULL OR pv.date <= until)
                OFFSET 0)
  GROUP BY ce.summary ORDER BY max(ce.starttime) DESC LIMIT 20
$$;

events_at answers one photo’s question; a wall asks the window’s. A view that shows many photos at once — memories’ grid — would otherwise call events_at once per row to name every tile, hundreds of round-trips for a single screen. events_in_window is the batched read instead: the confirmed events overlapping [since, until], earliest first, returned whole so the caller pairs each photo with its own in the browser — one read names a whole wall. Unlike event_completions it needs no photo-EXISTS trim: the caller drops the eventless window-rows for free, and there are few of them. A cancelled event is skipped here too, the same reason events_at skips it: a call that didn’t happen is no occasion. PostGraphile reflects it as the eventsInWindow connection; restart afterwards.

CREATE OR REPLACE FUNCTION events_in_window(since timestamptz, until timestamptz)
  RETURNS SETOF calendar_event LANGUAGE sql STABLE AS $$
  SELECT * FROM calendar_event
  WHERE status <> 'cancelled' AND starttime <= until AND endtime >= since
  ORDER BY starttime
$$;

The GraphQL contract

The apps never touch Postgres directly. PostGraphile (the konubinix/postgraphile image) reflects the public schema into a GraphQL API at /graphql, and that single endpoint is the whole contract. There is no app-side auth code, no ORM, no REST — every app just POST=s =\{query, variables\} to its own /graphql and reads JSON back.

The fields the apps rely on:

• photovideosSample / photovideosCount / photovideosSearch — the frise’s server-side search functions: window by since=/=until, filter by states, kinds, owners, an onthisday anniversary window (aday=/=awin), and a free-text search over the FTS index, sampled down to a cap. Both the frise and memories drive their walls through these (memories’ query language compiles to exactly their variables). They’re defined in the frise’s schema chapter.
• duplicatesPhotovideos — the connection doublons reads. One node per perceptual-hash cluster, each carrying an opaque result JSON (the trick below).
• updatePhotovideo(input:\{cid, patch\}) — the mutation every app uses to change a row, almost always to set state (label edits, marking for deletion, doublons’ soft-delete of the losers). labelCompletions(prefix:) feeds the label autocomplete.

The opaque-result-JSON trick. The duplicates_photovideo view emits a single jsonb column, result. To PostGraphile that’s one scalar of type JSON — it can’t see inside it. So you can add keys to the JSON (a new per-duplicate field) without changing the GraphQL schema at all, which means no PostGraphile restart when the view changes shape. (Contrast a real new column, which would change the reflected type.) That’s why doublons can evolve its cluster payload by editing only the view’s SQL.

The search DSL — one shared module

The search box is small enough to double as a query language. A query is a ;-separated list of segments — the semicolon, not whitespace, because the free text carries its own spaces and operators (below) and splitting on those would shatter it. Each segment is either free text or a typed token: a since:=/=until: date bound, a type:=/=sort:=/=owner: filter, a first:=/=last:=/=sample: count, or onthisday. Anything that isn’t a well-formed token is free text, so a half-typed since:201 simply binds nothing yet.

Left alone, a query means “the whole archive”: the parser’s bounds default to the floor of the dated collection — 2007, the same floor the frise uses — up to the end of today, so a photo shot earlier today falls inside the default window rather than just past its midnight.

export const MIN_DATE = '2007-01-01';
export const todayISO = () => new Date(Date.now()).toISOString().slice(0, 10);

The typed filters map a free word onto a closed vocabulary, so a typo binds nothing rather than mis-filtering. type: takes a few friendly aliases (photo, movie, …) but resolves to a mimetype prefix, image or video. sort: is just the wall’s order — date by default, or random, the archive’s legacy myrandom draw (an ordering, nothing the frame knows about). owner: must name one of the archive’s owners. And a date is recognised only in YYYY, YYYY-MM or YYYY-MM-DD shape.

const DATE_RE = /^\d{4}(-\d{1,2}){0,2}$/;
const KIND_ALIAS = { image: 'image', images: 'image', photo: 'image', photos: 'image',
                     video: 'video', videos: 'video', movie: 'video' };
const SORT_ALIAS = { date: 'date', random: 'random', shuffle: 'random' };
const KINDS = ['image', 'video'];                    // the closed mimetype vocabulary
const OWNERS = ['konubinix', 'ayla', 'aylapomme'];
const resolveSet = (inc, exc, all) => {
    if(!exc.length) return inc.length ? inc : null;
    const out = (inc.length ? inc : all).filter(x => !exc.includes(x));
    return out.length ? out : null;
};

A since:=/=until: names a period, not an instant, so we snap it to the period’s edge: since to the first moment, until to the last. That is why until:2015 keeps all of 2015, and a bare until:2020-02 reaches the 29th. A partial date fills its missing parts from that same edge.

const startOf = v => { const [y, m, d] = v.split('-');
    return `${y}-${(m || '01').padStart(2, '0')}-${(d || '01').padStart(2, '0')}`; };
const endOf = v => { const [y, m, d] = v.split('-');
    if(d) return `${y}-${m.padStart(2, '0')}-${d.padStart(2, '0')}T23:59:59`;
    const M = m ? +m : 12, last = new Date(Date.UTC(+y, M, 0)).getUTCDate();
    return `${y}-${String(M).padStart(2, '0')}-${last}T23:59:59`; };

A whole calendar year is named directly too: year:2015 is exactly since:2015 until:2015, both bounds snapped to that year’s edges — year:current for this one.

A bound can also be a relative word, so you rarely type a date at all: today, yesterday, lastweek=/=lastmonth=/=lastyear, or N days/weeks/months/years ago. Each resolves against today into a plain YYYY-MM-DD; an unrecognised word resolves to nothing, leaving the bound at its default.

const RELATIVES = ['today', 'yesterday', 'lastweek', 'lastmonth', 'lastyear'];
function relativeDate(word){
    const n = new Date(Date.now());
    const d = new Date(Date.UTC(n.getUTCFullYear(), n.getUTCMonth(), n.getUTCDate()));
    const ago = /^(\d+)\s*(day|week|month|year)s?\s+ago$/.exec(word);
    if(ago){
        const k = +ago[1];
        if(ago[2] === 'day') d.setUTCDate(d.getUTCDate() - k);
        else if(ago[2] === 'week') d.setUTCDate(d.getUTCDate() - 7 * k);
        else if(ago[2] === 'month') d.setUTCMonth(d.getUTCMonth() - k);
        else d.setUTCFullYear(d.getUTCFullYear() - k);
    }
    else if(word === 'yesterday') d.setUTCDate(d.getUTCDate() - 1);
    else if(word === 'lastweek') d.setUTCDate(d.getUTCDate() - 7);
    else if(word === 'lastmonth') d.setUTCMonth(d.getUTCMonth() - 1);
    else if(word === 'lastyear') d.setUTCFullYear(d.getUTCFullYear() - 1);
    else if(word !== 'today') return null;
    return d.toISOString().slice(0, 10);
}

Just as year: names a whole year in one token, date: names a period at any granularity: date:2020-06 is exactly since:2020-06 until:2020-06, the month snapped at both edges; date:2020-06-15 a single day. It resolves its value just as a since:=/=until: bound does — a date shape or a relative word — and pins both edges to it at once.

onthisday matches one calendar day across every year — today’s, ±1. That idea generalises into a pair of recurring-component filters that pin a row by its month or its day, ignoring the year it fell in. month: takes a number, a month name, or current (this month); day: takes an MM-DD or current (today). Either accepts a trailing (N) that widens the match to a ±N window — months for month:, days for day: — wrapping at the year boundary, so month:1(1) reaches December. Said this way, day:current(1) is onthisday:1. A value resolves to a target (a month 1–12, or a date whose year the SQL ignores) and a window; current reads the clock, a name or number maps to its month, an MM-DD projects onto a leap year so 02-29 stays valid.

const MONTHS = ['january', 'february', 'march', 'april', 'may', 'june',
                'july', 'august', 'september', 'october', 'november', 'december'];
const winSplit = v => { const m = /^(.+?)\s*\((\d+)\)$/.exec(v); return m ? [m[1].trim(), +m[2]] : [v, null]; };
function monthNum(v){
    if(v === 'current') return +todayISO().slice(5, 7);
    if(/^\d{1,2}$/.test(v)){ const n = +v; return n >= 1 && n <= 12 ? n : null; }
    return (MONTHS.indexOf(v) + 1) || null;          // indexOf -1 → 0 → null
}
function dayDate(v){
    if(v === 'current') return todayISO();
    const m = /^(\d{1,2})-(\d{1,2})$/.exec(v); if(!m) return null;
    const mo = +m[1], d = +m[2]; if(mo < 1 || mo > 12 || d < 1 || d > 31) return null;
    return `2000-${String(mo).padStart(2, '0')}-${String(d).padStart(2, '0')}`;   // any (leap) year; SQL drops it
}

Naming a month that way is useful well beyond the recurring filters. Asked for everything until:july you mean the July just gone, not July of some year you would have to stop and work out; until:07-14 is the last 14th of July, and until:july-14 says it in words. So a bound may drop its year too, and the year it takes is the most recent one in which that date has already come round — this year if it has, last year if it has not. Which is why the same token answers differently in March and in August, and that is the point: it tracks what last means.

const BARE_RE = /^([a-z]+|\d{1,2})(?:-(\d{1,2}))?$/;
function recentDate(v){
    const m = BARE_RE.exec(v); if(!m) return null;
    const mo = monthNum(m[1]); if(!mo) return null;
    const d = m[2] ? +m[2] : 0; if(m[2] && (d < 1 || d > 31)) return null;
    const at = y => `${y}-${String(mo).padStart(2, '0')}` + (d ? `-${String(d).padStart(2, '0')}` : '');
    const today = todayISO(), y = +today.slice(0, 4);
    return startOf(at(y)) > today ? at(y - 1) : at(y);   // not round yet this year → the year before
}

Every token so far narrows which photos a query matches. Three more say how many of them to bring back, and from which end: first:20 is the twenty earliest, last:20 the twenty latest, sample:20 twenty spread evenly across the whole match. A count is no vocabulary to close, so all there is to check is that it reads as a number above nothing; and since the three differ only in which rows they keep, the parse carries one count and one word for the end it was taken from — first, last, or, for a spread that comes from no end at all, none.

With the vocabularies and the date helpers in hand, the parser walks the segments and folds each into the filter variables the contract’s photovideos* functions take. Each segment forks: onthisday[:N] is its own shorthand — the same anniversary as day:current(:N) — a leading - routes the token to a key’s exclusion, a bare key: to its include, and anything that is not a well-formed token is free text, joined and handed to Postgres websearch_to_tsquery untouched — so a label search reads as boolean: terms AND, or unions, a leading - excludes, "…" is a phrase.

export function parseQuery(text){
    let since = MIN_DATE, until = todayISO() + 'T23:59:59', sort = 'date', aday = null, awin = 1, month = null, mwin = 0, events = null, eventsX = null;
    let sinceSet = false, untilSet = false;   // did a token set the window edge (vs the default)?
    let cap = null, pick = null;              // how many rows, and from which end
    const labels = [], kinds = [], kindsX = [], owners = [], ownersX = [];
    for(const raw of (text || '').split(';')){
        const t = raw.trim(); if(!t) continue;
        const od = /^onthisday(?::(\d+))?$/i.exec(t);
        if(od){ aday = todayISO(); if(od[1]) awin = +od[1]; continue; }
        const neg = t[0] === '-';
        const m = /^(since|until|type|sort|owner|month|day|year|date|event|first|last|sample):(.+)$/i.exec(neg ? t.slice(1) : t);
        if(!m){ labels.push(t); continue; }   // free text keeps its own - for websearch
        const key = m[1].toLowerCase(), v = m[2].trim();
        if(neg){
            <<parse-negation>>
            continue;
        }
        <<parse-counts>>
        <<parse-include>>
    }
    return { search: labels.join(' '),
             since, until, kinds: resolveSet(kinds, kindsX, KINDS),
             owners: resolveSet(owners, ownersX, OWNERS), sort, aday, awin, month, mwin, events, eventsX,
             cap, pick,
             dateBounds: (sinceSet || untilSet)
                 ? { from: sinceSet ? since : null, to: untilSet ? until : null } : null };
}

The include side splits by what a key carries. Three of them carry a number — the count and the end it names, as above — and each remaining key drives its own handler. event: carries a calendar-event name through verbatim — a summary is free text the server matches loosely, not a closed vocabulary. type:=/=sort:=/=owner: map a word onto their closed vocabularies. month:=/=day: set the recurring target and its window. And since:=/=until:=/=year:=/=date: resolve through the date helpers to a window edge, each edge remembering whether a token set it — the memory that later feeds dateBounds.

if(key === 'first' || key === 'last' || key === 'sample'){
    if(/^\d+$/.test(v) && +v > 0){ cap = +v; pick = key === 'sample' ? null : key; }
    continue;
}

if(key === 'event'){ events = v; continue; }   // value kept as typed
if(key === 'type'){ const k = KIND_ALIAS[v.toLowerCase()]; if(k) kinds.push(k); continue; }
if(key === 'sort'){ const sv = SORT_ALIAS[v.toLowerCase()]; if(sv) sort = sv; continue; }
if(key === 'owner'){ const o = v.toLowerCase(); if(OWNERS.includes(o)) owners.push(o); continue; }
if(key === 'month'){ const [base, w] = winSplit(v.toLowerCase()); const mo = monthNum(base);
    if(mo){ month = mo; mwin = w ?? 0; } continue; }
if(key === 'day'){ const [base, w] = winSplit(v.toLowerCase()); const dd = dayDate(base);
    if(dd){ aday = dd; awin = w ?? 0; } continue; }
if(key === 'year'){ const y = v.toLowerCase() === 'current' ? todayISO().slice(0, 4) : v;
    if(/^\d{4}$/.test(y)){ since = startOf(y); until = endOf(y); sinceSet = untilSet = true; } continue; }
const lower = v.toLowerCase();
const spec = relativeDate(lower) || (DATE_RE.test(v) ? v : recentDate(lower));
const lo = spec && startOf(spec), hi = spec && endOf(spec);
if(lo && (key === 'since' || key === 'date')){ since = lo; sinceSet = true; }
if(hi && (key === 'until' || key === 'date')){ until = hi; untilSet = true; }

That leading - isn’t only for labels. Any filter with a well-defined negation takes it — the parser strips the - uniformly and hands the token to that key’s exclusion — but where the exclusion is taken depends on the filter’s shape. A closed vocabulary is negated by complement, computed right here: -type:video keeps every kind but video, -owner:konubinix every owner but him — start from the whole set, drop the excluded members (so stacking -owner:a -owner:b removes both), and hand the remainder to the same kinds=/=owners the include form fills. No server arg, because the whole set is already known client-side.

An event: name has no client-side complement — it matches occasions only the calendar knows — so -event:Disneyland cannot be turned inside out here. It negates server-side instead, riding a second argument eventsX that mirrors events, which the match filter turns into a not: keep a photo unless it falls in an occasion of that name. Both routes are the one idea — keep everything, then take out the named — carried out wherever each filter’s set actually lives. The windows and the counts abstain from the - — since:=/=until:=/=month:=/=day: and first:=/=last:=/=sample:: an edge of a range has no complement to take, and neither has a number of rows.

if(key === 'type'){ const k = KIND_ALIAS[v.toLowerCase()]; if(k) kindsX.push(k); }
else if(key === 'owner'){ const o = v.toLowerCase(); if(OWNERS.includes(o)) ownersX.push(o); }
else if(key === 'event') eventsX = v;
else labels.push(t);   // nothing to take a complement of — keep as typed

An app whose date range lives somewhere other than the box — the frise, whose range is its timeline window — needs two things the box knows and shouldn’t re-derive: which tokens are window commands (since:=/=until:=/=year:=/=date:, not the recurring month:=/=day:) and the bounds they resolve to. parseQuery reports the latter as dateBounds (null when the query names no window; each edge filled only when its token is present), and stripWindowTokens removes just those tokens from the text, leaving the other filters. So such an app routes a typed date to its window while re-learning no syntax — and a new window token added here reaches it untouched.

export const stripWindowTokens = text => (text || '').split(';')
    .map(s => s.trim()).filter(s => s && !/^(since|until|year|date):/i.test(s)).join('; ');

Completion reuses that same ;-split. Whichever box you’re in — the search box following a live caret, or a label box sitting at end-of-text — the unit to complete is the segment the caret falls in; picking a value rewrites only that segment, keeping its leading “; " spacing intact.

export const segSpan = (text, caret) => {
    let s = caret, e = caret;
    while(s > 0 && text[s - 1] !== ';') s--;
    while(e < text.length && text[e] !== ';') e++;
    return [s, e];
};
export const segAt = (text, caret) => text.slice(...segSpan(text, caret)).trim();
export const replaceSegAt = (text, caret, picked) => { const [s, e] = segSpan(text, caret);
    const lead = (text.slice(s, e).match(/^\s*/) || [''])[0];
    return text.slice(0, s) + lead + picked + text.slice(e); };
export const replaceSeg = (text, picked) => replaceSegAt(text, (text || '').length, picked);

Inside an open since:=/=until:=/=date: the completion turns into a progressive date picker, so you tap a date rather than spell it — drilling from the words and years down through months to days. The words it offers are the relative ones and the twelve month names, both narrowed by prefix, so last shortlists lastweek/lastmonth/lastyear and ju shortlists june/july; pick a month and it drills into that month’s days the way a year drills into its months. It only ever offers a real date — a month is one through twelve, a day one to that month’s last — so an out-of-range fragment like 2022-0 names no month and the picker stays empty there rather than proposing a date that isn’t.

In practice a bare month name makes that last rule awkward, because “that month’s last” is not a property of the name: February ends on the 28th or the 29th depending on the year, and the year is the one the bound would resolve to. So the picker asks recentDate which February it is about to mean, and counts the days of that one.

const ARCHIVE_START = +MIN_DATE.slice(0, 4);
const NAMED = [...RELATIVES, ...MONTHS];
export function dateSuggestions(key, val){
    if(!val){
        const out = NAMED.map(r => key + r), thisYear = +todayISO().slice(0, 4);
        for(let y = thisYear; y >= ARCHIVE_START; y--) out.push(key + y);
        return out;
    }
    if(!/^\d/.test(val)){
        const v = val.toLowerCase(), mo = MONTHS.indexOf(v) + 1;
        if(!mo) return NAMED.filter(r => r.startsWith(v)).map(r => key + r);
        const last = new Date(Date.UTC(+recentDate(v).slice(0, 4), mo, 0)).getUTCDate();
        const out = [key + v];
        for(let i = 1; i <= last; i++) out.push(`${key}${v}-${String(i).padStart(2, '0')}`);
        return out;
    }
    const m = /^(\d{4})(?:-(\d{1,2})(?:-(\d{1,2}))?)?$/.exec(val);
    if(!m) return [];
    const [, y, mo, d] = m;
    if(mo === undefined){
        const out = [key + y];
        for(let i = 1; i <= 12; i++) out.push(`${key}${y}-${String(i).padStart(2, '0')}`);
        return out;
    }
    if(+mo < 1 || +mo > 12) return [];               // a month is 1..12; anything else names none
    const mm = mo.padStart(2, '0');
    const last = new Date(Date.UTC(+y, +mo, 0)).getUTCDate();
    if(d === undefined){
        const out = [`${key}${y}-${mm}`];
        for(let i = 1; i <= last; i++) out.push(`${key}${y}-${mm}-${String(i).padStart(2, '0')}`);
        return out;
    }
    if(+d < 1 || +d > last) return [];               // a day is 1..the month's last
    return [`${key}${y}-${mm}-${d.padStart(2, '0')}`];
}

Before a value is even started, completion offers the token keys themselves, and for the closed filters their members — the same vocabulary the parser accepts.

export const DSL_KEYS = ['since:', 'until:', 'type:', 'sort:', 'owner:', 'month:', 'day:', 'year:', 'date:', 'event:',
                         'first:', 'last:', 'sample:', 'onthisday'];
export const ENUMS = { 'type:': ['image', 'video'], 'sort:': ['date', 'random'],
                       'owner:': ['konubinix', 'ayla', 'aylapomme'],
                       'month:': ['current', ...MONTHS], 'day:': ['current'] };

Completing the tokens is shared too. Given the segment under the caret, dslSuggestions offers the keys a prefix matches, a closed filter’s members, the run of years inside an open year:, or — inside an open since:=/=until:=/=date: — the date picker; it returns nothing for free text, so each app falls back to its own label vocabulary there.

export function dslSuggestions(seg){
    if(seg[0] === '-'){                              // negation completes only the closed-vocab keys
        const s = seg.slice(1), nem = /^(type|owner):(.*)$/i.exec(s);
        if(nem){ const key = nem[1].toLowerCase() + ':';
            return ENUMS[key].filter(v => v.startsWith(nem[2].toLowerCase())).map(v => '-' + key + v); }
        const ns = s.toLowerCase();
        return s.length >= 2 ? ['type:', 'owner:'].filter(k => k.startsWith(ns)).map(k => '-' + k) : [];
    }
    const m = /^(since|until|date):(.*)$/i.exec(seg);
    if(m) return dateSuggestions(m[1].toLowerCase() + ':', m[2]);
    const ym = /^year:(.*)$/i.exec(seg);
    if(ym){ const v = ym[1].toLowerCase(), ty = +todayISO().slice(0, 4), out = ['year:current'];
        for(let y = ty; y >= ARCHIVE_START; y--) out.push('year:' + y);
        return out.filter(s => s.toLowerCase().startsWith('year:' + v)); }
    const em = /^(type|sort|owner|month|day):(.*)$/i.exec(seg);
    if(em){ const key = em[1].toLowerCase() + ':';
        return ENUMS[key].filter(v => v.startsWith(em[2].toLowerCase())).map(v => key + v); }
    const lower = seg.toLowerCase();
    return seg.length >= 2 ? DSL_KEYS.filter(k => k.startsWith(lower)) : [];
}

Why a module, not a copy each. All of the above is data-layer code — how you address the shared GraphQL contract — and it is pure: no DOM, no framework. So it has no business being duplicated in several render stacks. It tangles to one physical file, /run/user/1000/shared/dsl.js, which each app imports with the relative specifier ../shared/dsl.js. What stays in each app is the vocabulary completion — the app-side call to labelCompletions for label words — and the rendering of the suggestion list. Memories is the first consumer; the frise’s narrower label search and doublons’ cluster wall can adopt it as they grow a query box.

Tested through its consumers. The DSL carries no test harness of its own — it’s exercised end-to-end through the memories and frise Playwright suites: the since:=/=until: range, the year→month→day completion, the relative-date shortcuts, the owner: filter, the month:=/=day: recurring filters (token completion and the wall narrowing across years), and the first:=/=last:=/=sample: counts. That is the honest seam — a user types into the box and the wall narrows — so changing the module and re-running those suites proves it.

Assembling the query — shared/data.js

The DSL turns the box’s text into a parsed query; turning that parse into the variables the server wants is a second concern, and a second shared module, shared/data.js. The apps differ in how they bind reactivity — memories’ Solid createResource, the frise’s urql hooks — and how they render, but what they ask is one shape, so it lives here rather than copied into each.

A window shows every photo it holds up to a cap — high enough that any window you can actually read shows every one; only a denser one samples down to it. One number, shared:

export const SAMPLE_CAP = 2000;

A parsed query becomes server variables the same way for everyone — the free text, the closed-vocabulary filters, the recurring month/day window. Only the date range differs: memories takes it from the parsed since=/=until, the frise from its timeline window — so that pair is an argument and the rest is read off the parse.

export function photoVars(parsed, { since, until } = {}){
    return { search: (parsed.search || '').trim(),
             since: since ?? parsed.since, until: until ?? parsed.until,
             kinds: parsed.kinds, owners: parsed.owners,
             aday: parsed.aday, awin: parsed.awin,
             amonth: parsed.month, mwin: parsed.mwin, events: parsed.events, eventsX: parsed.eventsX };
}

Those same fields recur in every photo query in two more forms: the variable declarations a query opens with, and the arguments that pass them to the server function — identical wherever they appear, since only each query’s own extras (a state filter, a sampling cap, a density window) differ. So the filter lives here in all three forms — the values photoVars builds, and the two strings a query splices into its declaration and its call — leaving each query to show only what makes it distinct.

export const PHOTO_FILTER_DECL =
  '$search:String!,$since:Datetime!,$until:Datetime!,$kinds:[String!],$owners:[OwnerType!],$aday:Date,$awin:Int,$amonth:Int,$mwin:Int,$events:String,$eventsX:String';
export const PHOTO_FILTER_ARGS =
  'search:$search,since:$since,until:$until,kinds:$kinds,owners:$owners,aday:$aday,awin:$awin,amonth:$amonth,mwin:$mwin,events:$events,eventsX:$eventsX';

Both node-returning queries — memories’ sample and the frise’s search — ask for the same core of each photo: its content ids (cid, thumbnailCid, webCid), its file facts (date, mimetype, filename), and its labels. Only a field or two beyond that is app-specific, so the core is one fragment each query spreads and extends.

export const PHOTO_CORE = `fragment PhotoCore on Photovideo {
  cid date thumbnailCid webCid mimetype filename labels }`;

Reading is one shape; the one write the apps share is another — a label or state change on a single photo, through the auto-generated updatePhotovideo with a free-form PhotovideoPatch. It asks for the changed row back rather than a bare acknowledgement: a result that carries a Photovideo marks every cached view holding that row stale, so each app’s next read of it comes back fresh.

export const UPDATE_PHOTO = `mutation($cid:String!,$patch:PhotovideoPatch!){
  updatePhotovideo(input:{cid:$cid,patch:$patch}){ photovideo { cid labels state } } }`;

One more query both apps share: the label-completion lookup. As you type a label, each offers the words already in use — a prefix in, the matching words out. memories caps the list to a handful, the frise takes them all, so the cap rides as a parameter.

export const LABEL_COMPLETIONS = `query($prefix:String!, $first:Int){ labelCompletions(prefix:$prefix, first:$first){ nodes } }`;

The shared GraphQL client — shared/gql.js

Sending the query is one more shared concern. It goes to /graphql on the app’s own origin, behind the LAN auth gate — a 401/403 there means this device has no grant. The client that sends it, caches it, and reads that gate is shared/gql.js: an urql client whose document cache dedups a window’s repeated queries and serves a revisited one without a round-trip, wrapping a fetch that flips a tiny framework-agnostic store when the gate refuses. A consumer binds its own reactivity to that store and queries through the client.

That deduping is the wrong bargain for one case: a read fired right after an edit. The client shares an in-flight identical query, so a re-read issued the instant a mutation commits can be handed the result of a read that went out just before it — a removed tag still lingering. network-only skips the cached read but not that in-flight sharing. So the module also exports queryFresh, which posts straight to /graphql through the same auth-gate fetch, bypassing the client entirely — no cache, no dedup. A consumer that must see its own write (memories’ triage wall, re-reading after a batch edit) reads through it.

import { createClient, cacheExchange, fetchExchange } from '@urql/core';
let _authNeeded = false;
const _authListeners = new Set();
const setAuthNeeded = v => { if(v !== _authNeeded){ _authNeeded = v; _authListeners.forEach(fn => fn(v)); } };
export const getAuthNeeded = () => _authNeeded;
export function subscribeAuth(fn){ _authListeners.add(fn); fn(_authNeeded); return () => _authListeners.delete(fn); }
const authFetch = async (input, init) => {
    const r = await fetch(input, init);
    if(r.status === 401 || r.status === 403) setAuthNeeded(true);
    else if(r.ok) setAuthNeeded(false);
    return r;
};
export const client = createClient({
    url: '/graphql',
    fetch: authFetch,
    exchanges: [cacheExchange, fetchExchange],
});
// posts straight to the endpoint — no cache read, no in-flight dedup
export async function queryFresh(query, variables, signal){
    const r = await authFetch('/graphql', {
        method: 'POST', headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ query, variables: variables || {} }), signal,
    });
    const j = await r.json();
    if(j.errors && j.errors.length) throw new Error(j.errors[0].message);
    return j.data;
}

TODO

1. deal with - generically, like -owner:… or -type:video — a leading - on a closed-vocab token (type:=/=owner:) now negates it, resolved as the set complement at parse time (no server arg). Open-ended filters (since:=/=until:=/=month:=/=day:) still don’t take a -; a recurring window would need a server-side not (a later step).

Notes linking here

• a duplicate-photo batch resolver with hyperapp
• frise chrono
• testing render and sync stacks for local-first PWAs

Permalink