Een dating-app op basis van brieven, niet van gezichten!
Deze repository wordt als momentopname gepubliceerd: een clone bevat de volledige inhoud, maar geen commitgeschiedenis.
import { query, transaction } from "./db.js";
import { THREAD_PAGE, THREAD_HITS_MAX, SESSION_TTL_DAYS, INACTIVE_ACCOUNT_DAYS, REPORT_RETENTION_DAYS, SEARCH_RESULTS_MAX,
FREE_ACCOUNTS, REAL_CORRESPONDENCE_LETTERS, REAL_CORRESPONDENCE_EACH, AGE_MIN, AGE_MAX,
FILTER_GAP_MIN, FILTER_GAP_MAX, FILTER_GAP_MID, FILTER_GAP_STEEP } from "../src/constants.js";
/*
* De beginband, als SQL, gegeven de uitdrukking waar de leeftijd in staat. Eén plek omdat
* createUser() en probeGrid() dezelfde band moeten geven: lopen ze uiteen, dan meet het
* gezondheidsrapport een gebruiker die niet bestaat (zie README).
*
* Er is maar één formule — de bovengrens (zie src/constants.js). De ondergrens staat er letterlijk
* als haar omkering: de jongste die ik zie is de jongste die mij nog als oudste heeft. Daarmee is de
* wederkerigheid waar per constructie en niet iets dat we met afrondingsregels bij elkaar houden.
* Dat `generate_series` kost 103 rijen; het draait bij het aanmaken van een account en in de proef
* van het rapport, en nergens in een pad waar iemand op wacht.
*
* `oudste` en `jongste` zijn leeftijden; de kolommen heten omgekeerd (seek_min_birth is de oudste
* toegestane geboortedatum en dus de hóógste leeftijd).
*/
const OUDSTE = (leeftijd) =>
`floor((${leeftijd})::numeric + ${FILTER_GAP_MIN} + ${FILTER_GAP_MAX - FILTER_GAP_MIN}
/ (1 + exp(-((${leeftijd})::numeric - ${FILTER_GAP_MID}) / ${FILTER_GAP_STEEP})))::int`;
const startFilter = (leeftijd) => ({
oudste: `least(${AGE_MAX}, greatest(${AGE_MIN}, ${OUDSTE(leeftijd)}))`,
jongste: `least(${AGE_MAX}, greatest(${AGE_MIN}, coalesce(
(select min(b) from generate_series(${AGE_MIN}, ${AGE_MAX}) b
where ${OUDSTE("b")} >= ${leeftijd}), ${AGE_MIN})))`,
});
/* ---------- Projecties: rijen → de vorm die de frontend kent ---------- */
/*
* De leeftijdsgrenzen terug uit de bewaarde datums: elk de omkering van de functie die hem schreef
* (db/schema.sql). Voor de ondergrens is dat kaal `age()`, want youngest_birthdate telt alleen jaren
* af. Voor de bovengrens niet: oldest_birthdate rekent `vandaag - leeftijd - 1 jaar + 1 dag`, dus
* gaat die dag er hier eerst weer af en dat jaar erna. Zonder die twee stappen leest een filter dat
* als 40 is opgeslagen zich de dag erna als 41 terug — het invulveld verspringt dan uit zichzelf.
*
* Wat blijft: de bewaarde datums staan stil terwijl de kalender doorloopt, dus na een jaar leest de
* band één hoger. Dat is voor beide grenzen gelijk en zo bedoeld — deze omkering haalt de sprong van
* één dag naar één jaar, ze schaft hem niet af.
*/
const USER_COLUMNS = `
u.id, u.name, u.gender, u.trans, u.relationship, u.birthdate, u.bio, u.last_active, u.lat, u.lon,
u.seek_genders, u.seek_relatie, u.seek_trans, u.seek_min_birth, u.seek_max_birth, u.seek_radius_km,
date_part('year', age(u.birthdate))::int as age,
date_part('year', age(u.seek_max_birth))::int as seek_age_min,
date_part('year', age(u.seek_min_birth - 1))::int - 1 as seek_age_max
`;
/* Alleen bij mijn eigen profiel (getMe & co selecteren u.email, u.consent_at en u.hidden erbij) */
const MY_COLUMNS = `u.email, u.consent_at, u.hidden`;
const toUser = (r) => ({
id: String(r.id),
...(r.email != null && { email: r.email, consentAt: r.consent_at, hidden: r.hidden }), // alleen bij mijn eigen profiel
name: r.name,
gender: r.gender,
trans: r.trans,
relationship: r.relationship,
birth: r.birthdate,
age: r.age,
text: r.bio,
lastActive: r.last_active,
location: r.lat == null ? null : { lat: r.lat, lon: r.lon },
filter: {
genders: r.seek_genders,
seekRelatie: r.seek_relatie,
seekTrans: r.seek_trans,
minBirth: r.seek_min_birth,
maxBirth: r.seek_max_birth,
ageMin: r.seek_age_min,
ageMax: r.seek_age_max,
radiusKm: r.seek_radius_km,
},
...(r.distance_km != null && { distanceKm: Math.round(r.distance_km) }),
// alleen bij Zoeken: dezelfde profieltekst, met de gevonden woorden gemarkeerd (zie HEADLINE_OPTS)
...(r.bio_marked != null && { textMarked: r.bio_marked }),
});
/*
* Is deze gebruiker op dit moment geschorst? Als SQL-fragment, want het antwoord hoort in dezelfde
* query te staan als de vraag: een geschorste gebruiker mag nergens meer opduiken.
* suspended_until null betekent "voor onbepaalde tijd", niet "niet geschorst" — dat laatste is
* suspended_at null.
*/
const suspended = (t) => `(${t}.suspended_at is not null and (${t}.suspended_until is null or ${t}.suspended_until > now()))`;
/*
* De symmetrische matchvoorwaarde tussen `me` en `u`, in beide richtingen:
* gender, relatiestatus, geboortedatumbereik en (als een van beiden een straal
* heeft ingesteld) afstand. Vrijgezellen ziet iedereen; iemand in een relatie
* alleen wie 'ook mensen in een relatie' aan heeft staan (seek_relatie); net zo
* verschijnt een trans persoon alleen bij wie 'ook trans personen' aan heeft
* (seek_trans) — bescherming: je correspondenten staan er dan bewust voor open.
* Iemand met een straal ziet alleen mensen mét locatie binnen die straal. Een blokkade
* (in welke richting ook) maakt beiden voor elkaar onzichtbaar.
*/
const MUTUAL_MATCH = `
-- ik zie hen
u.gender = any (me.seek_genders)
and (u.relationship = 'vrijgezel' or me.seek_relatie)
and (not u.trans or me.seek_trans)
and u.birthdate between me.seek_min_birth and me.seek_max_birth
and (me.seek_radius_km is null
or (u.lat is not null and distance_km(me.lat, me.lon, u.lat, u.lon) <= me.seek_radius_km))
-- zij zien mij
and me.gender = any (u.seek_genders)
and (me.relationship = 'vrijgezel' or u.seek_relatie)
and (not me.trans or u.seek_trans)
and me.birthdate between u.seek_min_birth and u.seek_max_birth
and (u.seek_radius_km is null
or (me.lat is not null and distance_km(u.lat, u.lon, me.lat, me.lon) <= u.seek_radius_km))
-- geen blokkade in welke richting dan ook
and not exists (
select 1 from blocks b
where (b.user_id = me.id and b.blocked_id = u.id) or (b.user_id = u.id and b.blocked_id = me.id)
)
-- een geschorste gebruiker doet niet mee: niet in Zoeken, en er kan niemand aan schrijven
and not ${suspended("u")}
-- wie zichzelf tijdelijk verborgen heeft doet ook niet mee (vinkje onder de profieltekst)
and not u.hidden
-- en een leeg profiel evenmin: dit is een app waarin je op je woorden gevonden wordt, dus zonder
-- woorden is er niets om op te vinden. Een lege kaart in Zoeken is voor beide kanten verspilling
and btrim(u.bio) <> ''
`;
/*
* De foto bij een brief reist mee als beschrijving, niet als bytes: { id, alt, width, height }.
* De bytes zelf haalt de app apart op bij de mediaserver (GET /media/<id>, zie getPhotoFor) — die
* controleert dan opnieuw of de opvrager de afzender of de ontvanger is.
*/
const PHOTO_JOIN = `left join photos p on p.letter_id = l.id`;
const PHOTO_COLUMNS = `p.id as photo_id, p.alt as photo_alt, p.width as photo_width, p.height as photo_height`;
const toPhoto = (r) =>
r.photo_id == null ? null : { id: String(r.photo_id), alt: r.photo_alt, width: r.photo_width, height: r.photo_height };
const toLetter = (r) => ({
id: String(r.id),
from: String(r.from_id),
to: String(r.to_id),
date: r.sent_at,
readAt: r.read_at,
text: r.body,
photo: toPhoto(r),
});
/* ---------- Zoekhulp: vrije tekst → veilige prefix-tsquery ---------- */
/* "dostojevski zuurd" → ["dostojevski", "zuurd"]; alleen letters/cijfers blijven over */
export const toTerms = (q = "") =>
q
.toLowerCase()
.split(/\s+/)
.map((t) => t.replace(/[^\p{L}\p{N}]/gu, ""))
.filter(Boolean);
/* OR-query met prefix-match per term, zodat typen-en-zoeken werkt zoals in het prototype */
const toTsQuery = (terms) => terms.map((t) => `${t}:*`).join(" | ");
/*
* Markeren doet Postgres, niet de app. Reden: hij weet wélk woord de treffer was. De zoekopdracht
* gaat door de Nederlandse stemming heen, dus 'boek' vindt 'boeken' — een vergelijking aan de
* clientkant zou dat woord vinden maar niet kunnen onderstrepen. ts_headline pakt de gevonden
* woorden in twee stuurtekens die in een brief niet voorkomen; de app knipt de tekst daarop en zet
* er een streep onder (zie src/lib/highlight.jsx). Geen HTML dus, en niets om te ontsnappen.
*/
export const MARK_START = "\u0001";
export const MARK_STOP = "\u0002";
const HEADLINE_OPTS = `StartSel="${MARK_START}", StopSel="${MARK_STOP}", HighlightAll=TRUE`;
/* ---------- Accounts en sessies ---------- */
/*
* Nieuw account (met het tijdstip van de AVG-toestemming); 'iedereen' (gender), alleen vrijgezellen
* (kolomdefault) en 'ook trans' alleen als je zelf trans bent — tot de gebruiker het aanpast.
* `q` is de query-functie: standaard de pool, maar createAccount() geeft die van zijn transactie mee,
* zodat het account en de plek waarvoor betaald is één ondeelbaar geheel zijn.
*
* Het startfilter is de band uit startFilter() hierboven: 25 → 20–33, 33 → 25–44, 50 → 38–62. Beide
* grenzen liggen altijd binnen AGE_MIN–AGE_MAX, dezelfde schaal als het profielscherm.
*
* Dat laatste is niet vanzelfsprekend, want daar ging het eerder mis: alleen de bovenkant werd
* afgetopt, dus wie 105 of ouder is kreeg "oudste toegestaan = 99" met "jongste toegestaan = 100+" —
* een omgekeerd bereik, dat `check (seek_min_birth <= seek_max_birth)` terecht weigert. Registreren
* eindigde dan in een 500 en de app zei "interne fout", terwijl er niets mis was met wat de gebruiker
* invulde. Nu kán dat niet meer: de ondergrens is de omkering van de bovengrens, dus hij ligt er per
* constructie onder.
*/
export async function createUser({ email, passwordHash, name, gender, trans, birthdate }, q = query) {
const { rows } = await q(
`with ins as (
insert into users (email, password_hash, consent_at, name, gender, trans, birthdate, seek_genders, seek_trans, seek_min_birth, seek_max_birth)
select $1, $2, now(), $3, $4, $5, $6::date,
'{v,m,x}',
$5,
oldest_birthdate(${startFilter("l.leeftijd").oudste}),
youngest_birthdate(${startFilter("l.leeftijd").jongste})
from (select date_part('year', age($6::date))::int as leeftijd) l
returning *
)
select ${USER_COLUMNS}, ${MY_COLUMNS} from ins u`,
[email, passwordHash, name, gender, trans, birthdate]
);
return toUser(rows[0]);
}
/*
* Zijn er nog gratis plekken? Alleen ja of nee — het aantal blijft binnen, want een teller die
* meeloopt is een teller die af te lezen is, en dan weet ook degene die de bak wil leegtrekken
* precies hoeveel hij nog te gaan heeft. Dit is een vooruitblik voor de app (moet ik om een betaling
* vragen?), geen reservering: de echte beslissing valt in createAccount().
*/
export async function freeSlotOpen() {
const { rows } = await query(`select granted < $1 as open from signup_slots`, [FREE_ACCOUNTS]);
return rows[0]?.open ?? false;
}
/*
* De stand van de gratis bak, mét het aantal — voor de beheerder aan de serverkant
* (`npm run reports`), niet voor de API. Dat verschil is opzet: de beheerder moet zien aankomen dat
* de bak leegloopt, want vanaf dat moment moet het artikel in de store echt werken.
*/
export async function signupCounts() {
const { rows } = await query(`select granted from signup_slots`);
const granted = rows[0]?.granted ?? 0;
return { granted, left: Math.max(0, FREE_ACCOUNTS - granted), total: FREE_ACCOUNTS };
}
/*
* Een account aanmaken kost een plek: één uit de gratis bak, of een aankoop in de store. Beide
* worden hier vastgelegd in dezelfde transactie als de gebruiker zelf, en dat is de hele reden dat
* deze functie bestaat. Zou het in twee stappen gaan, dan bestaat er een moment waarop de plek
* afgeschreven is en het account niet — of erger, andersom.
*
* Geeft { user } als het gelukt is, of { error: 'full' | 'used' }:
* full de bak was net leeg (twee mensen tegelijk bij de laatste plek) → de app vraagt om betaling
* used op dit bonnetje staat al een account
*
* De gratis plek wordt geclaimd met één update-met-voorwaarde. Dat is geen stijlkeuze maar de
* correctheid zelf: Postgres serialiseert gelijktijdige updates op dezelfde rij en toetst de
* voorwaarde opnieuw op de nieuwe versie, dus twee registraties tegelijk kunnen niet allebei de
* laatste plek pakken. Eerst tellen en dan ophogen zou dat wél kunnen.
*/
export async function createAccount({ email, passwordHash, name, gender, trans, birthdate, purchase = null }) {
return transaction(async (q) => {
if (purchase) {
/* Eén bon, één account. De unique-sleutel is de controle; do nothing maakt er een antwoord van in plaats van een fout */
const { rowCount } = await q(
`insert into purchases (provider, token, product_id, order_id) values ($1, $2, $3, $4)
on conflict (provider, token) do nothing`,
[purchase.provider, purchase.token, purchase.productId, purchase.orderId ?? ""]
);
if (rowCount === 0) return { error: "used" };
} else {
const { rowCount } = await q(`update signup_slots set granted = granted + 1 where granted < $1`, [FREE_ACCOUNTS]);
if (rowCount === 0) return { error: "full" };
}
const user = await createUser({ email, passwordHash, name, gender, trans, birthdate }, q);
if (purchase)
await q(`update purchases set user_id = $1 where provider = $2 and token = $3`, [user.id, purchase.provider, purchase.token]);
return { user };
});
}
/* Bestaand account (van vóór de privacyverklaring, of na een wezenlijke wijziging ervan) geeft alsnog toestemming */
export async function giveConsent(id) {
const { rows } = await query(
`with upd as (update users set consent_at = now() where id = $1 returning *)
select ${USER_COLUMNS}, ${MY_COLUMNS} from upd u`,
[id]
);
return rows[0] ? toUser(rows[0]) : null;
}
/*
* Accountgegevens wijzigen (AVG art. 16, rectificatie). Alleen meegegeven velden veranderen
* (undefined = laten staan). Bij een nieuw wachtwoord vervallen alle andere sessies (keepTokenHash blijft).
*/
export async function updateAccount(id, { name, email, birthdate, gender, trans, passwordHash, keepTokenHash }) {
const { rows } = await query(
`with upd as (
update users set
name = coalesce($2, name),
email = coalesce($3, email),
birthdate = coalesce($4::date, birthdate),
gender = coalesce($5, gender),
trans = coalesce($6, trans),
password_hash = coalesce($7, password_hash)
where id = $1
returning *
)
select ${USER_COLUMNS}, ${MY_COLUMNS} from upd u`,
[id, name ?? null, email ?? null, birthdate ?? null, gender ?? null, trans ?? null, passwordHash ?? null]
);
if (passwordHash && rows[0]) await deleteOtherSessions(id, keepTokenHash);
return rows[0] ? toUser(rows[0]) : null;
}
/*
* Voor inloggen: id + hash bij een e-mailadres, plus een lopende schorsing als die er is
* ({ until, reason }; until null = onbepaalde tijd). De server controleert eerst het wachtwoord en
* pas daarna de schorsing — anders is dit endpoint een manier om te achterhalen wie geschorst is.
*/
export async function getCredentials(email) {
const { rows } = await query(
`select id, password_hash, suspend_reason, suspended_until, ${suspended("users")} as is_suspended
from users where email = $1`,
[email]
);
const r = rows[0];
if (!r) return null;
return {
id: String(r.id),
passwordHash: r.password_hash,
suspension: r.is_suspended ? { until: r.suspended_until, reason: r.suspend_reason } : null,
};
}
/*
* Wachtwoord vergeten (zie server/wachtwoord.js). Wie bij dit adres hoort — alleen id en naam,
* genoeg voor de aanhef van de mail. Geen schorsingscontrole: een nieuw wachtwoord zetten mag
* ook geschorst; de schorsing geldt bij het inloggen, en dáár staat de reden.
*/
export async function getAccountByEmail(email) {
const { rows } = await query(`select id, name from users where email = $1`, [email]);
return rows[0] ? { id: String(rows[0].id), name: rows[0].name } : null;
}
/* Eén herstel-link per persoon: opnieuw aanvragen vervangt de vorige. */
export async function createPasswordReset(userId, tokenHash, expiresAt) {
await query(
`insert into password_resets (user_id, token_hash, expires_at) values ($1, $2, $3)
on conflict (user_id) do update set token_hash = $2, expires_at = $3, created_at = now()`,
[userId, tokenHash, expiresAt]
);
}
/* Is deze link (nog) bruikbaar? Voor de GET van de pagina — kijken zonder verbruiken. */
export async function peekPasswordReset(tokenHash) {
const { rows } = await query(`select 1 from password_resets where token_hash = $1 and expires_at > now()`, [tokenHash]);
return Boolean(rows[0]);
}
/*
* De POST van de pagina: link verzilveren en het wachtwoord zetten, in één statement — er is geen
* moment waarop de link verbruikt is en het wachtwoord niet gezet, of andersom. Alle sessies
* vervallen mee: wie zijn wachtwoord vernieuwt omdat het oude misschien op straat ligt, wil ook
* dat een meelezende vreemde eruit vliegt. Geeft { id, name }, of null bij een link die onbekend,
* verlopen of al gebruikt is.
*/
export async function resetPasswordByToken(tokenHash, passwordHash) {
const { rows } = await query(
`with verzilverd as (
delete from password_resets where token_hash = $1 and expires_at > now() returning user_id
), gezet as (
update users u set password_hash = $2 from verzilverd v where u.id = v.user_id returning u.id, u.name
), weg as (
delete from sessions s using gezet g where s.user_id = g.id
)
select id, name from gezet`,
[tokenHash, passwordHash]
);
return rows[0] ? { id: String(rows[0].id), name: rows[0].name } : null;
}
/* Nieuwe sessie (login/registratie). Inloggen telt als activiteit: last_active mee — anders zou een
lezer die nooit schrijft na het verlopen van zijn sessies ten onrechte 'inactief' lijken (purgeExpired) */
export async function createSession(userId, tokenHash) {
await query(`insert into sessions (token_hash, user_id) values ($1, $2)`, [tokenHash, userId]);
await query(`update users set last_active = now() where id = $1`, [userId]);
}
/*
* Wie hoort bij dit token? Werkt meteen last_seen_at bij. Een sessie die SESSION_TTL_DAYS niet
* gebruikt is telt niet meer, en een geschorste gebruiker ook niet: die krijgt 401, waarna de app
* zijn token wist en het inlogscherm toont — en dáár staat de reden. Schorsen wist de sessies al,
* dit vangt de sessie die er tussendoor nog inzat.
*/
export async function getSessionUser(tokenHash) {
const { rows } = await query(
`update sessions s set last_seen_at = now()
from users u
where s.token_hash = $1 and u.id = s.user_id
and s.last_seen_at > now() - make_interval(days => $2)
and not ${suspended("u")}
returning s.user_id`,
[tokenHash, SESSION_TTL_DAYS]
);
return rows[0] ? String(rows[0].user_id) : null;
}
/*
* Dezelfde vraag als getSessionUser, maar zonder last_seen_at bij te werken. Nodig voor de
* dagelijkse bezorgcheck van het toestel (GET /api/post): dat is de app die kijkt of er post ligt,
* niet iemand die de app gebruikt. Zou die check wél meetellen, dan verliep er nooit meer een
* sessie (SESSION_TTL_DAYS) en telde het rapport elk slapend toestel als actief lid — dan meet je
* de wekker in plaats van de mens.
*/
export async function getSessionUserQuiet(tokenHash) {
const { rows } = await query(
`select s.user_id
from sessions s
join users u on u.id = s.user_id
where s.token_hash = $1
and s.last_seen_at > now() - make_interval(days => $2)
and not ${suspended("u")}`,
[tokenHash, SESSION_TTL_DAYS]
);
return rows[0] ? String(rows[0].user_id) : null;
}
/*
* Ligt er post waarover dit toestel nog niets gezegd heeft? `sinceId` is de hoogste brief waarover
* al een melding ging ('0' = nog nooit); alleen ongelezen brieven daarboven tellen mee. Twee dingen
* volgen daaruit, en allebei met opzet: wat je op een ander toestel al las wordt niet alsnog
* gemeld, en een brief die je bewust laat liggen wordt niet elke dag opnieuw aangekondigd.
* Geblokkeerde afzenders vallen af, net als in het postvak.
*
* Wat er teruggaat is precies genoeg om één zin te maken, en niet meer:
* senders — hoeveel verschillende mensen er post lieten liggen
* favorites — de namen van de favorieten daaronder, nieuwste brief eerst, hoogstens NOTIFY_NAMES
* name — de naam bij precies één afzender die géén favoriet is (anders null)
* latest — de hoogste brief hierin; die gaat de volgende keer als `since` terug
*
* Favorieten sturen de melding omdat een aantal geen nieuws is. "Zeven mensen hebben je geschreven"
* leest als een stapel die weggewerkt moet worden; "Anne heeft je geschreven" is waar je het voor
* doet. Wie je zelf een ster gaf, gaf je die om deze reden — en de rest blijft een aantal.
*
* Meer dan NOTIFY_NAMES namen gaan er nooit uit: een melding staat op een vergrendelscherm dat de
* halve kamer kan zien, en een lijstje wie er allemaal aan je schrijft hoort daar niet.
*/
const NOTIFY_NAMES = 2;
export async function unreadSince(meId, sinceId) {
const { rows } = await query(
`select l.from_id,
min(u.name) as name,
max(l.id) as laatste,
(f.other_id is not null) as favoriet
from letters l
join users u on u.id = l.from_id
left join favorites f on f.user_id = $1 and f.other_id = l.from_id
where l.to_id = $1 and l.read_at is null and l.id > $2
and not exists (select 1 from blocks b where b.user_id = $1 and b.blocked_id = l.from_id)
group by l.from_id, f.other_id
order by laatste desc`,
[meId, sinceId]
);
const favorites = rows.filter((r) => r.favoriet).slice(0, NOTIFY_NAMES).map((r) => r.name);
const latest = rows.reduce((hoogste, r) => (BigInt(r.laatste) > hoogste ? BigInt(r.laatste) : hoogste), BigInt(sinceId));
return {
senders: rows.length,
favorites,
name: rows.length === 1 && !rows[0].favoriet ? rows[0].name : null,
latest: String(latest),
};
}
export async function deleteSession(tokenHash) {
await query(`delete from sessions where token_hash = $1`, [tokenHash]);
}
/*
* Alle sessies van deze gebruiker behalve die van het toestel dat het vraagt. Twee wegen komen hier
* uit — een nieuw wachtwoord (updateAccount) en de knop "log mijn andere toestellen uit" — en ze
* horen precies hetzelfde te doen, vandaar één functie. Geeft het aantal uitgelogde toestellen.
*
* Waarom die knop er is: een sessie verloopt pas SESSION_TTL_DAYS ná het laatste gebruik, dus een
* toestel dat meeléést verloopt nooit. De export laat wél zien dát er nog ergens iemand ingelogd
* staat (zie exportUser) — dan hoort er ook iets te zijn om er wat aan te doen, zonder dat je
* daarvoor je wachtwoord moet veranderen.
*/
export async function deleteOtherSessions(userId, keepTokenHash) {
const { rowCount } = await query(
`delete from sessions where user_id = $1 and token_hash <> $2`,
[userId, keepTokenHash]
);
return rowCount;
}
/*
* Bewaartermijnen handhaven (dagelijks vanuit server/index.js): verlopen sessies weg, accounts
* die INACTIVE_ACCOUNT_DAYS op geen enkele manier actief waren (geen sessie gebruikt, geen profiel
* opgeslagen, geen brief gestuurd, niet net aangemaakt) definitief wissen — met alles wat eraan hangt —
* en meldingen die REPORT_RETENTION_DAYS oud zijn. Geeft de aantallen terug voor het log.
*/
export async function purgeExpired() {
const sessions = await query(
`delete from sessions where last_seen_at <= now() - make_interval(days => $1)`,
[SESSION_TTL_DAYS]
);
/* Verlopen herstel-links: klein genoeg om niet mee te tellen in het log */
await query(`delete from password_resets where expires_at <= now()`);
/* Dezelfde voorwaarde wordt twee keer gebruikt — noteren en wissen — dus staat hij één keer */
const dormant = `
greatest(
u.created_at,
u.last_active,
coalesce((select max(s.last_seen_at) from sessions s where s.user_id = u.id), u.created_at),
coalesce((select max(l.sent_at) from letters l where l.from_id = u.id), u.created_at)
) <= now() - make_interval(days => $1)`;
const users = await transaction(async (q) => {
await q(
`insert into departures (days_member, reason, wrote)
select greatest(0, current_date - u.created_at::date), 'slapend',
exists (select 1 from letters l where l.from_id = u.id)
from users u where ${dormant}`,
[INACTIVE_ACCOUNT_DAYS]
);
const { rowCount } = await q(`delete from users u where ${dormant}`, [INACTIVE_ACCOUNT_DAYS]);
return { rowCount };
});
const reports = await query(
`delete from reports where created_at <= now() - make_interval(days => $1)`,
[REPORT_RETENTION_DAYS]
);
return { sessions: sessions.rowCount, users: users.rowCount, reports: reports.rowCount };
}
/* Voor accountverwijdering: de wachtwoordhash bij een id (bevestiging met wachtwoord) */
export async function getPasswordHash(id) {
const { rows } = await query(`select password_hash from users where id = $1`, [id]);
return rows[0]?.password_hash ?? null;
}
/* ---------- Gebruikers ---------- */
export async function getUser(id) {
const { rows } = await query(`select ${USER_COLUMNS} from users u where u.id = $1`, [id]);
return rows[0] ? toUser(rows[0]) : null;
}
/* Mijn eigen profiel, inclusief e-mail */
export async function getMe(id) {
const { rows } = await query(`select ${USER_COLUMNS}, ${MY_COLUMNS} from users u where u.id = $1`, [id]);
return rows[0] ? toUser(rows[0]) : null;
}
/*
* relationship: eigen relatiestatus; seekRelatie: ook mensen in een relatie zien? seekTrans: ook trans personen?
* location: { lat, lon } | null; radiusKm: number | null (vereist een locatie); hidden: profiel
* tijdelijk verborgen (dan verschijn je niet in Zoeken — zie MUTUAL_MATCH).
*/
export async function updateProfile(id, { text, relationship, genders, seekRelatie, seekTrans, ageMin, ageMax, location, radiusKm, hidden }) {
const lo = Math.min(ageMin, ageMax);
const hi = Math.max(ageMin, ageMax);
const { rows } = await query(
`with upd as (
update users set
bio = $2,
relationship = $3,
seek_genders = $4::char(1)[],
seek_relatie = $5,
seek_trans = $6,
seek_min_birth = oldest_birthdate($7),
seek_max_birth = youngest_birthdate($8),
lat = $9,
lon = $10,
seek_radius_km = $11,
hidden = $12,
last_active = now()
where id = $1
returning *
)
select ${USER_COLUMNS}, ${MY_COLUMNS} from upd u`,
[id, text, relationship, genders, seekRelatie, seekTrans, hi, lo, location?.lat ?? null, location?.lon ?? null, radiusKm ?? null, hidden]
);
return rows[0] ? toUser(rows[0]) : null;
}
/* ---------- AVG: inzage/overdraagbaarheid en vergetelheid ---------- */
/*
* Alles wat we van deze gebruiker bewaren, als één leesbaar object (art. 15/20 AVG):
* account en profiel, alle brieven (verstuurd én ontvangen, met naam van de ander),
* favorieten, blokkades, de meldingen die hij zelf maakte, en de ingelogde sessies
* (alleen tijdstippen — geen tokens). `tokenHash` markeert de sessie waarmee de export is opgevraagd.
*
* Wat er bewust NIET in zit: meldingen die anderen over deze gebruiker maakten. Die bevatten
* gegevens van de melder, en wie zich onveilig voelt moet dat kunnen melden zonder dat de gemelde
* het via een inzageverzoek kan opvragen (AVG art. 15 lid 4: het inzagerecht doet geen afbreuk aan
* de rechten van anderen).
*/
export async function exportUser(id, tokenHash = null) {
const [me, letters, favorites, blocks, reports, warnings, measures, sessions, purchases] = await Promise.all([
query(
`select ${USER_COLUMNS}, ${MY_COLUMNS}, u.created_at, u.suspended_at, u.suspended_until, u.suspend_reason
from users u where u.id = $1`,
[id]
),
query(
`select l.*, f.name as from_name, t.name as to_name,
p.mime as photo_mime, p.alt as photo_alt, p.width as photo_width, p.height as photo_height,
-- encode() knipt elke 76 tekens een regeleinde in de base64; eruit, anders is de
-- export niet zonder bewerking terug te lezen
replace(encode(p.bytes, 'base64'), E'\\n', '') as photo_data
from letters l join users f on f.id = l.from_id join users t on t.id = l.to_id
${PHOTO_JOIN}
where l.from_id = $1 or l.to_id = $1
order by l.sent_at, l.id`,
[id]
),
query(
`select u.id, u.name, f.created_at from favorites f join users u on u.id = f.other_id
where f.user_id = $1 order by f.created_at`,
[id]
),
query(
`select u.id, u.name, b.created_at from blocks b join users u on u.id = b.blocked_id
where b.user_id = $1 order by b.created_at`,
[id]
),
query(
`select r.id, r.reason, r.details, r.letter_id, r.created_at, r.handled_at, u.id as about_id, u.name as about_name
from reports r join users u on u.id = r.reported_id
where r.reporter_id = $1 order by r.created_at`,
[id]
),
query(`select id, text, created_at, acknowledged_at from warnings where user_id = $1 order by created_at`, [id]),
query(`select kind, note, created_at from measures where user_id = $1 order by created_at`, [id]),
query(`select token_hash, created_at, last_seen_at from sessions where user_id = $1 order by created_at`, [id]),
/* Het bonnummer staat erbij, het bonnetje zelf niet: dat is de sleutel waarmee de plek geclaimd is */
query(`select provider, product_id, order_id, created_at from purchases where user_id = $1 order by created_at`, [id]),
]);
const r = me.rows[0];
if (!r) return null;
const u = toUser(r);
const person = (pid, name) => ({ id: String(pid), name });
return {
exportedAt: new Date(),
account: {
id: u.id, email: u.email, name: u.name, gender: u.gender, trans: u.trans, birthdate: u.birth,
createdAt: r.created_at, consentAt: u.consentAt, lastActive: u.lastActive,
// een lopende schorsing hoort hier: het is een gegeven over jou dat je gedrag bepaalt
suspension: r.suspended_at == null ? null : { since: r.suspended_at, until: r.suspended_until, reason: r.suspend_reason },
},
profile: { text: u.text, relationship: u.relationship, location: u.location, filter: u.filter },
letters: letters.rows.map((l) => ({
id: String(l.id),
direction: String(l.from_id) === String(id) ? "verzonden" : "ontvangen",
from: person(l.from_id, l.from_name),
to: person(l.to_id, l.to_name),
sentAt: l.sent_at,
readAt: l.read_at,
text: l.body,
// de foto compleet: de bytes als base64, zodat de export op zichzelf te lezen is (art. 20 AVG)
photo: l.photo_data == null
? null
: { mime: l.photo_mime, alt: l.photo_alt, width: l.photo_width, height: l.photo_height, data: l.photo_data },
})),
favorites: favorites.rows.map((f) => ({ user: person(f.id, f.name), since: f.created_at })),
blocks: blocks.rows.map((b) => ({ user: person(b.id, b.name), since: b.created_at })),
// alleen de meldingen die ík maakte; meldingen óver mij horen bij de melder (zie de kop hierboven)
reports: reports.rows.map((r) => ({
id: String(r.id),
about: person(r.about_id, r.about_name),
letterId: r.letter_id == null ? null : String(r.letter_id),
reason: r.reason,
details: r.details,
reportedAt: r.created_at,
handledAt: r.handled_at,
})),
// wat de beheerder tegen míj heeft ondernomen: de waarschuwingen zelf en het logboek daarvan.
// Wie erover meldde staat er niet bij — dat hoort bij de melder, niet bij mij
warnings: warnings.rows.map((w) => ({ id: String(w.id), text: w.text, at: w.created_at, acknowledgedAt: w.acknowledged_at })),
measures: measures.rows.map((m) => ({ kind: m.kind, note: m.note, at: m.created_at })),
sessions: sessions.rows.map((s) => ({ createdAt: s.created_at, lastSeenAt: s.last_seen_at, current: s.token_hash === tokenHash })),
/*
* Wat er van de betaling bij ons ligt, en dat is opzettelijk weinig: bij welke store, welk
* artikel, welk bonnummer en wanneer. Geen bedrag, geen kaart, geen naam — die heeft de store,
* niet wij. Het bonnummer is wat je nodig hebt als je bij Google om terugbetaling vraagt.
* Een lege lijst betekent dat dit een van de gratis plekken was.
*/
purchases: purchases.rows.map((p) => ({ store: p.provider, product: p.product_id, orderId: p.order_id, at: p.created_at })),
};
}
/*
* Account definitief wissen (art. 17 AVG). De foreign keys cascaderen: sessies, favorieten,
* blokkades en álle brieven van en aan deze gebruiker gaan mee — bij de correspondenten
* verdwijnt de correspondentie dus ook. Geeft true als er iets gewist is.
*
* Twee dingen blijven met opzet staan, en allebei zonder de persoon erbij: de bon van een betaalde
* plek (user_id wordt null — anders is wissen-en-opnieuw een gratis tweede account op dezelfde
* aankoop) en de gratis teller, die niet terugloopt. Wat overblijft zegt dát er een plek uitgegeven
* is, niet aan wie.
*/
/*
* Een account wissen — AVG art. 17, en ook de weg die een maatregel neemt. Eerst noteren dát er
* iemand weggaat, dan pas wissen: daarna is er niets meer om over te noteren. Wat er blijft staan
* wijst naar niemand (zie `departures` in db/schema.sql); zonder die regel krimpt elke telling over
* het verleden stilletjes mee met wie er opzegt.
*
* Beide in één transactie, want een vertrek dat wel genoteerd is maar niet uitgevoerd — of andersom —
* is erger dan geen van beide.
*/
export async function deleteUser(id, { reason = "zelf", farewell = "" } = {}) {
return transaction(async (q) => {
await q(
`insert into departures (days_member, reason, wrote)
select greatest(0, current_date - u.created_at::date), $2,
exists (select 1 from letters l where l.from_id = u.id)
from users u where u.id = $1`,
[id, reason]
);
/*
* De brief aan de beheerder, als die er is: in dezelfde transactie, en met opzet zonder enige
* verwijzing naar $1 — hij hoort niet bij een persoon maar bij een vertrek (zie `farewells` in
* db/schema.sql). Hier bewaard en niet gemaild, want mailen zou hem alsnog aan een adres knopen.
*/
if (farewell) await q(`insert into farewells (body) values ($1)`, [farewell]);
const { rowCount } = await q(`delete from users where id = $1`, [id]);
return rowCount > 0;
});
}
/* De achtergelaten brieven, nieuwste eerst — voor de beheerder (scripts/reports.js) */
export async function listFarewells({ limit = 20 } = {}) {
const { rows } = await query(`select id, at, body from farewells order by at desc, id desc limit $1`, [limit]);
return rows;
}
/* Hoeveel er de laatste `days` dagen binnenkwamen; de werkvoorraad noemt het alleen als het er zijn */
export async function countFarewells({ days = 30 } = {}) {
const { rows } = await query(`select count(*)::int as n from farewells where at > now() - make_interval(days => $1)`, [days]);
return rows[0].n;
}
/* ---------- De symmetrische matchquery ---------- */
/*
* Zoeken geeft hoogstens `limit` profielen terug — de meest passende eerst, daarbinnen wie het kortst
* geleden actief was. Het dak hoort bij de vraag zelf en niet bij de weergave: zonder dak is één
* lege zoekopdracht een kopie van het complete ledenbestand (naam, leeftijd, gender, trans-status,
* profieltekst, afstand), en dat is niet wat iemand toestemming gaf toen hij zich inschreef.
* We halen er één rij te veel bij op om te weten óf er meer was; `truncated` gaat mee naar de app,
* die het dan zegt in plaats van te doen alsof dit alles is.
*/
export async function searchProfiles(meId, q, { limit = SEARCH_RESULTS_MAX } = {}) {
const terms = toTerms(q);
const tsq = terms.length ? toTsQuery(terms) : null;
const { rows } = await query(
`select ${USER_COLUMNS},
distance_km(me.lat, me.lon, u.lat, u.lon) as distance_km,
case when $2::text is null then 0
else ts_rank_cd(u.bio_tsv, to_tsquery('dutch', $2)) end as score,
case when $2::text is null then null
else ts_headline('dutch', u.bio, to_tsquery('dutch', $2), $4) end as bio_marked
from users me
join users u on u.id <> me.id
where me.id = $1
and ${MUTUAL_MATCH}
-- fulltext (optioneel)
and ($2::text is null or u.bio_tsv @@ to_tsquery('dutch', $2))
order by score desc, u.last_active desc
limit $3`,
[meId, tsq, limit + 1, HEADLINE_OPTS]
);
return { truncated: rows.length > limit, results: rows.slice(0, limit).map(toUser) };
}
/*
* De woorden uit één zoekopdracht bijtellen. Geen wie, geen wanneer preciezer dan de dag — zie de
* toelichting bij `search_terms` in db/schema.sql voor waarom dat de hele opzet is. Wat er
* binnenkomt is al door toTerms() geweest, dus het zijn losse woorden zonder leestekens.
*
* Dubbele woorden binnen één zoekopdracht tellen één keer: wie "boeken boeken" typt heeft één keer
* naar boeken gezocht. Fout hier mag nooit een zoekopdracht laten mislukken — het is een telling,
* geen functie; vandaar dat de aanroeper hem niet afwacht (zie server/search-log.js).
*/
export async function countSearchTerms(terms) {
const uniek = [...new Set(terms.filter((t) => t.length >= 2 && t.length <= 40))];
if (!uniek.length) return 0;
const { rowCount } = await query(
`insert into search_terms (term, day, count)
select t, current_date, 1 from unnest($1::text[]) as t
on conflict (term, day) do update set count = search_terms.count + 1`,
[uniek]
);
return rowCount;
}
/* ---------- Brieven ---------- */
/* Postvak: één rij per gesprekspartner, met de laatste brief; favorieten bovenaan, daarbinnen nieuwste eerst.
Correspondenties met mensen die ík geblokkeerd heb zijn verborgen (niet gewist) */
export async function listThreads(meId) {
const { rows } = await query(
`with mine as (
select l.*, ${PHOTO_COLUMNS}, case when l.from_id = $1 then l.to_id else l.from_id end as other_id
from letters l ${PHOTO_JOIN}
where l.from_id = $1 or l.to_id = $1
),
last as (
select distinct on (other_id) *
from mine
order by other_id, sent_at desc
)
select ${USER_COLUMNS},
last.id as l_id, last.from_id, last.to_id, last.sent_at, last.read_at, last.body,
last.photo_id, last.photo_alt, last.photo_width, last.photo_height,
(select count(*) from mine where mine.other_id = last.other_id)::int as letter_count,
(select count(*) from mine where mine.other_id = last.other_id and mine.to_id = $1 and mine.read_at is null)::int as unread,
(f.other_id is not null) as favorite
from last
join users u on u.id = last.other_id
left join favorites f on f.user_id = $1 and f.other_id = last.other_id
where not exists (select 1 from blocks b where b.user_id = $1 and b.blocked_id = last.other_id) -- door mij geblokkeerd: verborgen
order by favorite desc, last.sent_at desc`,
[meId]
);
return rows.map((r) => ({
user: toUser(r),
letterCount: r.letter_count,
unread: r.unread,
favorite: r.favorite,
last: toLetter({ ...r, id: r.l_id }),
}));
}
/*
* Eén pagina brieven tussen mij en één ander, oudste eerst in het antwoord.
* before = null → de nieuwste `limit` brieven, plús alle ongelezen aan mij (ook als dat er meer zijn),
* zodat de client altijd op de eerste ongelezen kan landen
* before = <id> → de `limit` brieven vóór die brief (oudere bijladen)
* hasMore: zijn er nog oudere brieven dan de oudste in dit antwoord? Plus favorite,
* canReply (false zodra een van beiden de ander geblokkeerd heeft) en canPhoto (false zolang de
* ander mij nog nooit geschreven heeft — een openingsbrief gaat zonder foto). canPhoto komt uit de
* database en niet uit de meegestuurde brieven: dit is één pagina, de brief van de ander kan
* daarbuiten liggen.
*
* **Mag ik deze persoon überhaupt zien?** Dat is de eerste vraag, en die stond hier niet. Zonder
* die toets gaf dit één profiel terug bij élk id dat je invulde: naam, leeftijd, gender, of iemand
* trans is, de profieltekst, de locatie en het hele zoekfilter — ook van wie zichzelf verborgen
* heeft, van wie jou geblokkeerd heeft, van wie geschorst is en van iedereen buiten je filters. Eén
* lus van 1 tot het hoogste id is dan een kopie van het ledenbestand, en daarmee zijn `hidden`,
* blokkeren, de symmetrische match én het dak op Zoeken (SEARCH_RESULTS_MAX) alle vier voor niets.
*
* De regel is dezelfde als die de app volgt: er ligt post tussen ons (dan is het je eigen postvak,
* ook als een van beiden intussen geblokkeerd heeft), óf ik mag deze persoon schrijven (dan kwam
* hij zojuist uit Zoeken). Al het andere is niet gevonden — 404, niet 403: dát dit id bestaat is
* zelf al iets wat de vrager niet aangaat.
*/
export async function getThread(meId, otherId, { before = null, limit = THREAD_PAGE } = {}) {
const between = `((l.from_id = $1 and l.to_id = $2) or (l.from_id = $2 and l.to_id = $1))`;
const [user, thread, mayWrite, favorite, blocked, otherSuspended, heardFrom, page] = await Promise.all([
getUser(otherId),
hasThread(meId, otherId),
canWrite(meId, otherId),
isFavorite(meId, otherId),
isBlockedEither(meId, otherId),
isSuspended(otherId),
hasLetterFrom(meId, otherId),
query(
`select l.*, ${PHOTO_COLUMNS} from letters l ${PHOTO_JOIN}
where ${between}
and ($3::bigint is null
or (l.sent_at, l.id) < (select c.sent_at, c.id from letters c where c.id = $3))
order by l.sent_at desc, l.id desc
limit $4`,
[meId, otherId, before, limit]
),
]);
if (!user || !(thread || mayWrite)) return null;
let rows = page.rows;
if (before == null && rows.length) {
// eerste pagina: ligt er nog een ongelezen brief aan mij vóór de pagina, dan de pagina
// aaneengesloten verlengen tot en met de oudste ongelezen (geen gat, cursor blijft kloppen)
const oldest = rows[rows.length - 1];
const { rows: ext } = await query(
`with first_unread as (
select l.sent_at, l.id from letters l
where ${between} and l.to_id = $1 and l.read_at is null
and (l.sent_at, l.id) < ($3::timestamptz, $4::bigint)
order by l.sent_at asc, l.id asc
limit 1
)
select l.*, ${PHOTO_COLUMNS} from letters l ${PHOTO_JOIN}, first_unread f
where ${between}
and (l.sent_at, l.id) < ($3::timestamptz, $4::bigint)
and (l.sent_at, l.id) >= (f.sent_at, f.id)
order by l.sent_at desc, l.id desc`,
[meId, otherId, oldest.sent_at, oldest.id]
);
rows = rows.concat(ext);
}
let hasMore = false;
if (rows.length) {
const oldest = rows[rows.length - 1];
const { rows: more } = await query(
`select exists (
select 1 from letters l where ${between} and (l.sent_at, l.id) < ($3::timestamptz, $4::bigint)
) as ok`,
[meId, otherId, oldest.sent_at, oldest.id]
);
hasMore = more[0].ok;
}
// geschorst telt hier als onbereikbaar: dezelfde neutrale regel als bij een blokkade, geen reden erbij
return {
user, letters: rows.reverse().map(toLetter), favorite, hasMore,
canReply: !blocked && !otherSuspended,
canPhoto: heardFrom,
};
}
/*
* Zoeken door één correspondentie: de brieven tussen mij en één ander die op de zoekwoorden passen,
* nieuwste eerst. Dit is een trefferlijst, geen pagina uit het gesprek — er zit dus geen cursor op en
* de brieven eromheen komen niet mee. Elke treffer krijgt `marked` mee: dezelfde brieftekst, maar met
* de gevonden woorden tussen de stuurtekens (zie HEADLINE_OPTS), zodat de app ze kan onderstrepen.
*
* Geen rem en geen dak op wat het prijsgeeft, anders dan bij searchProfiles: dit is je eigen post,
* die je al mag lezen. Alleen THREAD_HITS_MAX, zodat een veelvoorkomend woord niet je hele
* geschiedenis in één antwoord propt; `truncated` gaat mee zodat de app dat kan zeggen.
*/
export async function searchThread(meId, otherId, q, { limit = THREAD_HITS_MAX } = {}) {
const terms = toTerms(q);
if (!terms.length) return { letters: [], truncated: false };
const { rows } = await query(
`select l.*, ${PHOTO_COLUMNS},
ts_headline('dutch', l.body, to_tsquery('dutch', $3), $5) as marked
from letters l ${PHOTO_JOIN}
where ((l.from_id = $1 and l.to_id = $2) or (l.from_id = $2 and l.to_id = $1))
and l.body_tsv @@ to_tsquery('dutch', $3)
order by l.sent_at desc, l.id desc
limit $4`,
[meId, otherId, toTsQuery(terms), limit + 1, HEADLINE_OPTS]
);
return {
letters: rows.slice(0, limit).map((r) => ({ ...toLetter(r), marked: r.marked })),
truncated: rows.length > limit,
};
}
/* ---------- Favorieten (persoonlijk, eenzijdig) ---------- */
export async function isFavorite(meId, otherId) {
const { rows } = await query(
`select exists (select 1 from favorites where user_id = $1 and other_id = $2) as ok`,
[meId, otherId]
);
return rows[0].ok;
}
/* Loopt er een correspondentie tussen ons? (favoriet mag alleen bij een correspondent) */
export async function hasThread(meId, otherId) {
const { rows } = await query(
`select exists (
select 1 from letters where (from_id = $1 and to_id = $2) or (from_id = $2 and to_id = $1)
) as ok`,
[meId, otherId]
);
return rows[0].ok;
}
/*
* Heeft deze ander mij al eens geschreven? Daar hangt de foto aan: een openingsbrief gaat zonder
* beeld de deur uit. Zolang er niets van de ander terugkwam blijft elke brief van mij tekst — dus
* ook een tweede en een derde. Antwoorden mag wél meteen een foto hebben: wie terugschrijft heeft
* de ander gekozen.
*/
export async function hasLetterFrom(meId, otherId) {
const { rows } = await query(
`select exists (select 1 from letters where from_id = $2 and to_id = $1) as ok`,
[meId, otherId]
);
return rows[0].ok;
}
/* Favoriet zetten of weghalen; idempotent */
export async function setFavorite(meId, otherId, favorite) {
if (favorite) {
await query(`insert into favorites (user_id, other_id) values ($1, $2) on conflict do nothing`, [meId, otherId]);
} else {
await query(`delete from favorites where user_id = $1 and other_id = $2`, [meId, otherId]);
}
}
/* Alle brieven van `otherId` aan mij als gelezen markeren; geeft het aantal terug */
export async function markThreadRead(meId, otherId) {
const { rowCount } = await query(
`update letters set read_at = now()
where to_id = $1 and from_id = $2 and read_at is null`,
[meId, otherId]
);
return rowCount;
}
/*
* Mag ik deze persoon schrijven? Ja als we wederzijds passen óf er al een correspondentie loopt
* (binnen een lopende correspondentie mag je altijd terugschrijven, ook als filters intussen niet
* meer passen) — behalve als een van beiden de ander geblokkeerd heeft (zie isBlockedEither).
*/
export async function canWrite(meId, otherId) {
const { rows } = await query(
`select not exists (
select 1 from blocks b
where (b.user_id = $1 and b.blocked_id = $2) or (b.user_id = $2 and b.blocked_id = $1)
) and (
exists (
select 1 from users me join users u on u.id = $2
where me.id = $1 and ${MUTUAL_MATCH}
) or exists (
select 1 from letters
where (from_id = $1 and to_id = $2) or (from_id = $2 and to_id = $1)
)
) as ok`,
[meId, otherId]
);
return rows[0].ok;
}
/* ---------- Blokkades (stil, eenzijdig; verbergen, niet wissen) ---------- */
/* Heeft een van ons tweeën de ander geblokkeerd? */
export async function isBlockedEither(meId, otherId) {
const { rows } = await query(
`select exists (
select 1 from blocks b
where (b.user_id = $1 and b.blocked_id = $2) or (b.user_id = $2 and b.blocked_id = $1)
) as ok`,
[meId, otherId]
);
return rows[0].ok;
}
/* Blokkeren of deblokkeren; idempotent */
export async function setBlock(meId, otherId, block) {
if (block) {
await query(`insert into blocks (user_id, blocked_id) values ($1, $2) on conflict do nothing`, [meId, otherId]);
} else {
await query(`delete from blocks where user_id = $1 and blocked_id = $2`, [meId, otherId]);
}
}
/* Wie heb ik geblokkeerd? (voor de lijst bij Mijn profiel) — nieuwste eerst */
export async function listBlocked(meId) {
const { rows } = await query(
`select ${USER_COLUMNS}, b.created_at as blocked_at
from blocks b join users u on u.id = b.blocked_id
where b.user_id = $1
order by b.created_at desc`,
[meId]
);
return rows.map((r) => ({ user: toUser(r), since: r.blocked_at }));
}
/* ---------- Meldingen (misbruik) ---------- */
/*
* Melding vastleggen en teruggeven hoe vaak deze persoon nu in totaal gemeld is (voor het serverlog:
* één melding zegt weinig, de vijfde zegt genoeg).
*
* `letterId` mag null zijn — dan gaat de melding over de persoon en niet over één brief. Staat er wél
* een brief bij, dan moet die tussen deze twee mensen liggen; anders is "melden" een manier om te
* achterhalen welke brief-id's bestaan. Klopt de brief niet, dan komt er null uit en slaan we niets op.
*/
export async function createReport({ reporterId, reportedId, letterId = null, reason, details = "" }) {
if (letterId != null) {
const { rows } = await query(
`select 1 from letters
where id = $1 and ((from_id = $2 and to_id = $3) or (from_id = $3 and to_id = $2))`,
[letterId, reporterId, reportedId]
);
if (!rows.length) return null;
}
const { rows } = await query(
`insert into reports (reporter_id, reported_id, letter_id, reason, details)
values ($1, $2, $3, $4, $5)
returning id, created_at`,
[reporterId, reportedId, letterId, reason, details]
);
// apart tellen, ná de insert: in één statement zou de subquery de zojuist ingevoegde rij nog niet zien
const { rows: counted } = await query(`select count(*)::int as total from reports where reported_id = $1`, [reportedId]);
return { id: String(rows[0].id), createdAt: rows[0].created_at, total: counted[0].total };
}
/*
* De werkvoorraad voor `npm run reports`: meldingen met wie wie meldde en de gemelde brief erbij.
* Alleen voor de beheerder op de server — hier hangt geen route aan, en die moet er ook niet komen
* zonder een echt beheerdersaccount.
*/
export async function listReports({ all = false, limit = 50 } = {}) {
const { rows } = await query(
`select r.id, r.reason, r.details, r.created_at, r.handled_at,
rep.id as reporter_id, rep.name as reporter_name, rep.email as reporter_email,
abt.id as reported_id, abt.name as reported_name, abt.email as reported_email,
l.id as letter_id, l.sent_at as letter_sent_at, l.body as letter_body,
(p.id is not null) as letter_has_photo,
(select count(*) from reports o where o.reported_id = r.reported_id)::int as reported_total
from reports r
left join users rep on rep.id = r.reporter_id
join users abt on abt.id = r.reported_id
left join letters l on l.id = r.letter_id
left join photos p on p.letter_id = l.id
where $1::boolean or r.handled_at is null
order by r.handled_at is not null, r.created_at
limit $2`,
[all, limit]
);
return rows;
}
/* Melding afgehandeld (of weer open, met handled = false). Geeft false als het id niet bestaat */
export async function setReportHandled(id, handled = true) {
const { rowCount } = await query(
`update reports set handled_at = case when $2 then now() else null end where id = $1`,
[id, handled]
);
return rowCount > 0;
}
/* Eén melding met de betrokkenen erbij, om een maatregel op te kunnen nemen (scripts/reports.js) */
export async function getReport(id) {
const { rows } = await query(
`select r.id, r.reason, r.details, r.created_at, r.handled_at, r.letter_id,
abt.id as reported_id, abt.name as reported_name,
rep.id as reporter_id, rep.name as reporter_name
from reports r
join users abt on abt.id = r.reported_id
left join users rep on rep.id = r.reporter_id
where r.id = $1`,
[id]
);
return rows[0] ?? null;
}
/* ---------- Handhaving: wat er ná een melding kan gebeuren ---------- */
/*
* Vier maatregelen, oplopend, allemaal via scripts/reports.js:
* waarschuwing — de gebruiker krijgt hem bij het openen van de app te zien en moet hem wegklikken
* brief_verwijderd — één brief (met foto) weg; het lichtste dat écht iets wegneemt
* schorsing — inloggen geweigerd met reden, sessies weg, uit Zoeken; brieven blijven staan
* account_verwijderd — alles weg, ook bij de correspondenten (dezelfde DELETE als art. 17 AVG)
*
* Elke maatregel schrijft een regel in `measures`. Zonder dat logboek is "we kijken ernaar" niet te
* controleren en is een tweede overtreding niet van een eerste te onderscheiden.
*/
export async function logMeasure({ userId = null, reportId = null, kind, note = "" }) {
const { rows } = await query(
`insert into measures (user_id, report_id, kind, note) values ($1, $2, $3, $4) returning id, created_at`,
[userId, reportId, kind, note]
);
return { id: String(rows[0].id), createdAt: rows[0].created_at };
}
/* Wat is er eerder tegen deze persoon ondernomen? Nieuwste eerst */
export async function listMeasures(userId) {
const { rows } = await query(
`select id, kind, note, report_id, created_at from measures where user_id = $1 order by created_at desc`,
[userId]
);
return rows;
}
/* Waarschuwen: de gebruiker ziet de tekst bij het openen van de app, vóór al het andere */
export async function warnUser(userId, text, { reportId = null } = {}) {
const { rows } = await query(
`insert into warnings (user_id, text) values ($1, $2) returning id, created_at`,
[userId, text]
);
await logMeasure({ userId, reportId, kind: "waarschuwing", note: text });
return { id: String(rows[0].id), createdAt: rows[0].created_at };
}
/* De oudste nog niet weggeklikte waarschuwing van deze gebruiker, of null */
export async function getPendingWarning(userId) {
const { rows } = await query(
`select id, text, created_at from warnings
where user_id = $1 and acknowledged_at is null
order by created_at limit 1`,
[userId]
);
return rows[0] ? { id: String(rows[0].id), text: rows[0].text, at: rows[0].created_at } : null;
}
/* Weggeklikt. Alleen de eigen waarschuwing, en alleen één keer (idempotent, geeft false bij onzin) */
export async function acknowledgeWarning(userId, warningId) {
const { rowCount } = await query(
`update warnings set acknowledged_at = now()
where id = $1 and user_id = $2 and acknowledged_at is null`,
[warningId, userId]
);
return rowCount > 0;
}
/*
* Schorsen. `days` null = voor onbepaalde tijd. De sessies gaan er meteen uit — een schorsing die
* pas ingaat als de app toevallig herstart is geen schorsing.
*/
export async function suspendUser(userId, { days = null, reason, reportId = null }) {
const { rows } = await query(
`update users set
suspended_at = now(),
suspended_until = case when $2::int is null then null else now() + make_interval(days => $2::int) end,
suspend_reason = $3
where id = $1
returning id, suspended_until`,
[userId, days, reason]
);
if (!rows[0]) return null;
await query(`delete from sessions where user_id = $1`, [userId]);
await logMeasure({
userId,
reportId,
kind: "schorsing",
note: `${days ? `${days} dagen` : "onbepaalde tijd"}: ${reason}`,
});
return { until: rows[0].suspended_until };
}
/* Schorsing opheffen (vergissing, of de termijn hoeft niet vol) */
export async function unsuspendUser(userId, { note = "", reportId = null } = {}) {
const { rowCount } = await query(
`update users set suspended_at = null, suspended_until = null, suspend_reason = ''
where id = $1 and suspended_at is not null`,
[userId]
);
if (!rowCount) return false;
await logMeasure({ userId, reportId, kind: "opheffing", note });
return true;
}
/* Is deze persoon nu geschorst? (voor canReply en het weigeren van post) */
export async function isSuspended(userId) {
const { rows } = await query(`select ${suspended("u")} as ok from users u where u.id = $1`, [userId]);
return rows[0]?.ok ?? false;
}
/*
* Eén brief verwijderen — de foto gaat via de cascade mee. De brief verdwijnt aan beide kanten; er
* blijft geen grafsteen achter ("brief verwijderd"), want die zou de ontvanger nieuwsgierig maken
* naar precies wat er weggehaald is. Combineer dit met een waarschuwing, dan weet de schrijver het.
*/
export async function removeLetter(letterId, { userId = null, reportId = null, note = "" } = {}) {
const { rowCount } = await query(`delete from letters where id = $1`, [letterId]);
if (!rowCount) return false;
await logMeasure({ userId, reportId, kind: "brief_verwijderd", note: note || `brief ${letterId}` });
return true;
}
/*
* Account verwijderen als maatregel: dezelfde DELETE als art. 17 AVG, plus een regel in het logboek.
* Het logboek eerst, dan pas de DELETE — die wist ook de meldingen over deze persoon, en een
* measure die naar een net verdwenen melding wijst kan niet meer worden ingevoegd. De cascade zet
* user_id en report_id van de zojuist geschreven regel daarna vanzelf op null: er blijft staan dát
* er is opgetreden en waarom, zonder nog te zeggen tegen wie.
*/
export async function removeUserAsMeasure(userId, { reason, reportId = null }) {
await logMeasure({ userId, reportId, kind: "account_verwijderd", note: reason });
return await deleteUser(userId, { reason: "maatregel" });
}
/*
* Brief versturen, met hoogstens één foto ({ mime, bytes, width, height, alt }, al nagekeken door
* server/media.js). Brief en foto gaan in één transactie: geen brief met een half opgeslagen foto,
* en geen foto zonder brief.
*/
export async function sendLetter(meId, { to, text, photo }) {
const letter = await transaction(async (q) => {
const { rows } = await q(
`insert into letters (from_id, to_id, body) values ($1, $2, $3) returning *`,
[meId, to, text]
);
const row = rows[0];
if (!photo) return toLetter(row);
const { rows: saved } = await q(
`insert into photos (letter_id, mime, bytes, width, height, alt)
values ($1, $2, $3, $4, $5, $6)
returning id`,
[row.id, photo.mime, photo.bytes, photo.width, photo.height, photo.alt]
);
return toLetter({ ...row, photo_id: saved[0].id, photo_alt: photo.alt, photo_width: photo.width, photo_height: photo.height });
});
await query(`update users set last_active = now() where id = $1`, [meId]);
return letter;
}
/*
* Hoeveel brieven heeft deze gebruiker net verstuurd, en hoe oud is het account? Eén query, met
* beide vensters erin: het laatste uur en het laatste etmaal. Het venster is schuivend, geen
* kalenderdag — anders staat er om middernacht een verse voorraad klaar.
*
* Geteld uit de brieven zelf en niet uit een teller in het geheugen: die zou bij elke herstart en
* elke uitrol op nul springen, en dat is precies het moment waarop een script weer los mag. De prijs
* is één telling per brief; de index letters_from_to begint met from_id, dus die is goedkoop.
*
* `oldest` is het tijdstip van de oudste brief binnen het venster: dán komt er weer een plek vrij.
* Voor een account jonger dan een etmaal is `day.count` meteen het totaal aantal brieven ooit —
* daar leunt de startdrempel op, en daarom moet NEW_ACCOUNT_HOURS 24 of minder blijven.
*/
export async function letterQuota(meId) {
const { rows } = await query(
`select u.created_at,
count(l.id) filter (where l.sent_at > now() - interval '1 hour') as hour_count,
min(l.sent_at) filter (where l.sent_at > now() - interval '1 hour') as hour_oldest,
count(l.id) as day_count,
min(l.sent_at) as day_oldest
from users u
left join letters l on l.from_id = u.id and l.sent_at > now() - interval '24 hours'
where u.id = $1
group by u.created_at`,
[meId]
);
const r = rows[0];
if (!r) return null;
return {
createdAt: r.created_at,
hour: { count: Number(r.hour_count), oldest: r.hour_oldest },
day: { count: Number(r.day_count), oldest: r.day_oldest },
};
}
/* ---------- De mediaserver: de bytes van één foto ---------- */
/*
* De foto met dit id, maar alleen als `meId` de afzender of de ontvanger van de bijbehorende brief is.
* Iemand anders krijgt null — en dus een 404, niet een 403: dat een foto bestaat is zelf al iets
* wat alleen die twee mogen weten.
*/
export async function getPhotoFor(meId, photoId) {
const { rows } = await query(
`select p.mime, p.bytes
from photos p join letters l on l.id = p.letter_id
where p.id = $1 and (l.from_id = $2 or l.to_id = $2)`,
[photoId, meId]
);
return rows[0] ?? null;
}
/*
* ---------- De gemeenschap: hoe het ervoor staat ----------
*
* De cijfers achter `npm run health`. Eén regel geldt voor alles wat hieronder staat en is de reden
* dat het hier mag bestaan: **er komt geen persoon uit**. Geen naam, geen e-mailadres, geen id,
* geen brieftekst — alleen tellingen, aandelen en medianen. `scripts/reports.js` is de enige plek
* waar iemand anders dan de schrijver en de ontvanger een brief te zien krijgt, en dat blijft zo;
* wie hier een lijstje "meest geschreven leden" aan toevoegt maakt er een tweede zo'n plek van.
*
* Waarom deze query's hier staan en niet in het script: de matchvoorwaarde MUTUAL_MATCH is
* module-privé, en de bereikbaarheidsmeting hergebruikt hem letterlijk. Een rapport dat met een
* kópie van de matchregel meet, meet de kopie.
*/
/*
* Een correspondentie is in dit schema geen tabel maar een paar {a,b}: least/greatest maakt de
* richting onbelangrijk, zodat de brief heen en de brief terug bij dezelfde rij horen. `a` is per
* definitie het kleinste id, dus `from_id < to_id` betekent "van a naar b" — from_id = to_id kan
* niet, dat verbiedt het schema.
*
* Uit dit ene aggregaat komt alles wat de rest nodig heeft: hoeveel brieven, hoeveel per kant,
* wanneer het begon en wanneer het laatst leefde. Daarom geen aparte `distinct on` voor de eerste
* brief: het aggregaat weet de begintijd van beide kanten al.
*/
const PAIRS = `
select least(l.from_id, l.to_id) as a,
greatest(l.from_id, l.to_id) as b,
count(*)::int as letters,
count(*) filter (where l.from_id < l.to_id)::int as letters_a,
count(*) filter (where l.from_id > l.to_id)::int as letters_b,
min(l.sent_at) filter (where l.from_id < l.to_id) as first_a,
min(l.sent_at) filter (where l.from_id > l.to_id) as first_b,
min(l.sent_at) as opened_at,
max(l.sent_at) as last_at
from letters l
group by 1, 2
`;
/*
* Wie begon, en kwam er antwoord? De eerste brief van het paar is de opener, de eerste brief van de
* ánder is het antwoord — `greatest(first_a, first_b)`, maar alleen als beide kanten geschreven
* hebben: één richting zonder brieven is geen antwoord maar een stilte, en greatest() slaat nulls
* over. Vandaar de expliciete uitzondering.
*
* Dit ziet alleen de brieven die er nóg zijn. Is de eerste brief door de beheerder verwijderd, dan
* schuift de opener op; verdwijnt een account, dan gaat de hele correspondentie mee. Het verleden
* krimpt dus, en altijd dezelfde kant op.
*/
const OPENERS = `
select p.*,
case when p.first_a is null then p.b
when p.first_b is null then p.a
when p.first_a <= p.first_b then p.a else p.b end as opener_id,
case when p.first_a is null then p.a
when p.first_b is null then p.b
when p.first_a <= p.first_b then p.b else p.a end as target_id,
case when p.first_a is null or p.first_b is null then null
else greatest(p.first_a, p.first_b) end as replied_at
from pair p
`;
/*
* Het laatste teken van leven, met dezelfde definitie die purgeExpired() gebruikt om te beslissen
* of een account weg mag. Dat is met opzet: "er nog zijn" hoort één betekenis te hebben.
*
* last_active alléén zou de lézer missen — wie zijn post opent raakt zijn sessie aan, niet
* last_active. Voorbij SESSION_TTL_DAYS is die sessierij opgeruimd en valt dit terug op
* last_active; voor vensters van dagen of weken maakt dat niets uit, voor oude cohorten wel, en het
* rapport zegt dat erbij.
*/
const SEEN = `
select u.*,
greatest(u.created_at, u.last_active, coalesce(max(s.last_seen_at), u.created_at)) as last_seen
from users u left join sessions s on s.user_id = u.id
group by u.id
`;
/* De actieve leden: gezien binnen `d` dagen en niet geschorst — wie geschorst is doet nergens mee */
const ACTIVES = (d) => `
select * from (${SEEN}) x
where not ${suspended("x")} and x.last_seen > now() - make_interval(days => ${d})
`;
/*
* A. Wie er zijn. Eén query, want de losse tellingen zijn scalaire subquery's die gratis meeliften.
*
* Twee cijfers verdienen toelichting, want ze zijn niet vanzelfsprekend:
*
* with_radius — een nieuw account heeft geen locatie en dus geen straal (zie createUser). Wie een
* straal instelde is daarmee onzichtbaar voor élke nieuwkomer, want MUTUAL_MATCH eist een locatie
* binnen die straal en de nieuwkomer heeft er geen. Dit is het cijfer waar het meest aan te doen is.
*
* left_free / left_paid — het énige exacte verloopcijfer dat dit schema toelaat. Accounts worden
* hard verwijderd en laten niets achter, maar signup_slots.granted loopt niet terug en purchases
* blijft staan met user_id op null. Het verschil tussen uitgegeven plekken en bestaande accounts
* is dus precies het aantal vertrokken leden — geen schatting.
*/
export async function communityOverview({ days = 30 } = {}) {
const { rows } = await query(
`with base as (
select x.*, ${suspended("x")} as is_susp from (${SEEN}) x
),
split as (
select gender, relationship, trans, count(*)::int as n,
count(*) filter (where last_seen > now() - make_interval(days => $1))::int as active
from base where not is_susp group by 1, 2, 3
)
select count(*)::int as members,
count(*) filter (where not is_susp and last_seen > now() - interval '7 days')::int as active7,
count(*) filter (where not is_susp and last_seen > now() - make_interval(days => $1))::int as active_window,
count(*) filter (where not is_susp and last_active > now() - make_interval(days => $1))::int as active_writing,
count(*) filter (where created_at > now() - make_interval(days => $1))::int as joined_window,
count(*) filter (where is_susp)::int as suspended_now,
count(*) filter (where consent_at is null)::int as without_consent,
count(*) filter (where lat is not null)::int as with_location,
count(*) filter (where seek_radius_km is not null)::int as with_radius,
count(*) filter (where trans)::int as trans_members,
count(*) filter (where seek_trans)::int as seek_trans_members,
count(*) filter (where seek_relatie)::int as seek_relatie_members,
percentile_cont(0.5) within group (order by date_part('year', age(birthdate)))::float8 as median_age,
percentile_cont(0.1) within group (order by date_part('year', age(birthdate)))::float8 as p10_age,
percentile_cont(0.9) within group (order by date_part('year', age(birthdate)))::float8 as p90_age,
(select jsonb_agg(to_jsonb(s) order by s.gender, s.trans, s.relationship) from split s) as split,
(select granted from signup_slots) as slots_granted,
(select count(*)::int from purchases) as purchases_total,
(select count(*)::int from purchases where user_id is not null) as purchases_alive,
(select count(*)::int from letters) as letters_total,
(select count(*)::int from letters where sent_at > now() - make_interval(days => $1)) as letters_window,
(select count(*)::int from photos) as photos_total,
/* Voorbeeldaccounts: db/seed.sql en db/generate.js maken allebei @voorbeeld.nl-adressen.
Staan die erin, dan kijk je niet naar mensen maar naar testdata, en dat hoort boven het
rapport te staan in plaats van eronder ontdekt te worden. */
(select count(*)::int from users where email like '%@voorbeeld.nl') as example_accounts,
/* Vertrek, uit het grootboek dat er speciaal voor is. Niet uit signup_slots: die teller
is een poort en stopt bij FREE_ACCOUNTS, dus zijn verschil met het ledental is geen
verloopcijfer maar een toevalligheid. */
(select count(*)::int from departures) as departed_total,
(select count(*)::int from departures where at > now() - make_interval(days => $1)) as departed_window,
(select count(*)::int from departures
where at > now() - make_interval(days => $1) and not wrote) as departed_silent,
(select (percentile_cont(0.5) within group (order by days_member))::float8
from departures where at > now() - make_interval(days => $1)) as departed_median_days,
(select jsonb_object_agg(reason, n) from (
select reason, count(*)::int n from departures
where at > now() - make_interval(days => $1) group by 1) t) as departed_by_reason
from base`,
[days]
);
const r = rows[0];
return { ...r, split: r.split ?? [], freeAccounts: FREE_ACCOUNTS };
}
/*
* B. De trechter van een nieuwkomer, per registratieweek. `rollup` levert de "alles"-regel gratis
* mee, berekend over álle leden, terwijl `having` alleen de laatste weken laat staan — dat is met
* een gewone `where` niet te krijgen.
*
* Twee dingen die het cijfer eerlijk houden:
*
* `mature` — wie zich vorige week aanmeldde kan onmogelijk "na 30 dagen nog terug" zijn. De
* noemer voor terugkeer is daarom niet het cohort maar het deel dat die 30 dagen gehad heeft;
* het script drukt een streepje af waar dat nul is, geen 0%.
*
* `registered` is overlevenaarsvertekening — het telt wie zich die week aanmeldde én nu nog
* bestaat. Wie opzegde is spoorloos. Het rapport zet er het verloopcijfer uit A naast, zodat de
* lezer de vertekening kan schatten in plaats van hem te vergeten.
*/
export async function newcomerFunnel({ weeks = 8, retentionDays = 30 } = {}) {
const { rows } = await query(
`with pair as (${PAIRS}), opener as (${OPENERS}),
sent as (select from_id as id from letters group by 1),
addressed as (select target_id as id from opener group by 1),
answered as (select opener_id as id from opener where replied_at is not null group by 1),
real_corr as (
select id from (
select opener_id as id from opener where letters >= $2 and least(letters_a, letters_b) >= $3
union all
select target_id as id from opener where letters >= $2 and least(letters_a, letters_b) >= $3
) t group by 1
),
u as (select * from (${SEEN}) x)
select date_trunc('week', u.created_at) as week,
grouping(date_trunc('week', u.created_at)) as is_total,
count(*)::int as registered,
count(sent.id)::int as ever_sent,
count(addressed.id)::int as ever_addressed,
count(answered.id)::int as ever_answered,
count(real_corr.id)::int as ever_real,
count(*) filter (where u.created_at <= now() - make_interval(days => $4))::int as mature,
count(*) filter (where u.created_at <= now() - make_interval(days => $4)
and u.last_seen >= u.created_at + make_interval(days => $4))::int as retained
from u
left join sent on sent.id = u.id
left join addressed on addressed.id = u.id
left join answered on answered.id = u.id
left join real_corr on real_corr.id = u.id
group by rollup (date_trunc('week', u.created_at))
having grouping(date_trunc('week', u.created_at)) = 1
or date_trunc('week', u.created_at) >= date_trunc('week', now()) - make_interval(weeks => $1 - 1)
order by grouping(date_trunc('week', u.created_at)), week desc`,
[weeks, REAL_CORRESPONDENCE_LETTERS, REAL_CORRESPONDENCE_EACH, retentionDays]
);
return rows.map((r) => ({ ...r, isTotal: r.is_total === 1 }));
}
/*
* C. Antwoordkans en wachttijd, over de eerste brief van elk paar.
*
* De censurering is hier het hele punt. Een opener van gisteren is niet onbeantwoord, die is jong.
* Twee maatregelen daartegen:
*
* 1. alleen openers die minstens `settleDays` oud zijn tellen mee (`settled`);
* 2. de vraag is niet "kwam er ooit antwoord" maar "kwam er antwoord bínnen settleDays". Teller
* en noemer krijgen zo evenveel klok, en het cijfer is vergelijkbaar tussen twee draaiingen
* van het rapport. `answered_ever` staat ernaast om te laten zien hoeveel dat scheelt — dat
* cijfer loopt vanzelf op naarmate de data ouder wordt, en is dus geen maatstaf.
*
* De mediaan en de p90 gelden alleen voor wie antwoord kreeg; een p90 over een verdeling waarin 40%
* nooit antwoordt bestáát niet, en het script zegt dat er zo bij.
*/
export async function openerOutcomes({ settleDays = 14 } = {}) {
const { rows } = await query(
`with pair as (${PAIRS}), opener as (${OPENERS}),
o as (
select u.gender,
extract(epoch from o.replied_at - o.opened_at)::float8 / 3600 as wait_h,
o.replied_at,
(o.opened_at <= now() - make_interval(days => $1)) as settled
from opener o join users u on u.id = o.opener_id
)
select case when grouping(gender) = 1 then null else gender end as gender,
count(*)::int as openers,
count(*) filter (where not settled)::int as too_young,
count(*) filter (where not settled and replied_at is not null)::int as too_young_answered,
count(*) filter (where settled)::int as settled_n,
count(*) filter (where settled and wait_h <= $1 * 24)::int as answered,
count(*) filter (where settled and replied_at is not null)::int as answered_ever,
/* De bakken tellen alleen antwoorden bínnen de horizon, anders valt een brief zowel in
een bak als in b_never en telt hij dubbel. Zo is b1..b5 + b_never precies settled_n. */
count(*) filter (where settled and wait_h <= $1 * 24 and wait_h < 24)::int as b1,
count(*) filter (where settled and wait_h <= $1 * 24 and wait_h >= 24 and wait_h < 72)::int as b2,
count(*) filter (where settled and wait_h <= $1 * 24 and wait_h >= 72 and wait_h < 168)::int as b3,
count(*) filter (where settled and wait_h <= $1 * 24 and wait_h >= 168 and wait_h < 720)::int as b4,
count(*) filter (where settled and wait_h <= $1 * 24 and wait_h >= 720)::int as b5,
count(*) filter (where settled and (replied_at is null or wait_h > $1 * 24))::int as b_never,
(percentile_cont(0.5) within group (order by wait_h)
filter (where settled and wait_h <= $1 * 24))::float8 as median_h,
(percentile_cont(0.9) within group (order by wait_h)
filter (where settled and wait_h <= $1 * 24))::float8 as p90_h
from o
group by grouping sets ((), (gender))
order by gender nulls first`,
[settleDays]
);
return rows;
}
/*
* D. De verdeling van de aandacht — waarom een gemiddelde hier liegt.
*
* De grondslag is niet "iedereen" maar "iedereen die er minstens `graceDays` bij is". Wie zich
* gisteren aanmeldde heeft nog geen kans gehad iets te ontvangen; die als nul meetellen maakt elke
* concentratiemaat slechter naarmate de groei versnelt, en dat is precies verkeerd om.
*
* Die drempel is bewust een eigen, korte termijn en niet het venster zelf. "Er al zijn bij het begin
* van het venster" is strenger en op het eerste gezicht zuiverder, maar bij `--days 90` op een
* gemeenschap van twee maanden oud blijft er dan niemand over en zegt de sectie niets. Een week is
* lang genoeg om post gekregen te kúnnen hebben en kort genoeg om vrijwel iedereen mee te tellen.
* De prijs staat in het rapport: wie 8 dagen lid is wordt in een venster van 30 dagen afgerekend op
* 8 dagen post.
*
* De leden die niets kregen moeten wél in de grondslag — vandaar de left joins vanuit `base` in
* plaats van een group by over `letters`. Zonder hen meet de Gini de verkeerde populatie.
*
* De concentratiecijfers staan alleen op de totaalregel: de rangen zijn over iedereen berekend, dus
* op een genderregel zouden ze iets anders betekenen dan ze lijken te zeggen. Het `case when
* grouping(...)` maakt dat structureel onmogelijk in plaats van een afspraak.
*/
export async function attentionSpread({ days = 30, graceDays = 7 } = {}) {
const { rows } = await query(
`with pair as (${PAIRS}), opener as (${OPENERS}),
base as (
select x.id, x.gender, x.last_seen from (${SEEN}) x
where x.created_at <= now() - make_interval(days => $2) and not ${suspended("x")}
),
recv_w as (select to_id as id, count(*)::int n from letters where sent_at > now() - make_interval(days => $1) group by 1),
recv_a as (select to_id as id, count(*)::int n from letters group by 1),
open_w as (select target_id as id, count(*)::int n from opener where opened_at > now() - make_interval(days => $1) group by 1),
b as (
select base.*, coalesce(recv_w.n, 0) as got, coalesce(recv_a.n, 0) as got_all, coalesce(open_w.n, 0) as opened
from base
left join recv_w on recv_w.id = base.id
left join recv_a on recv_a.id = base.id
left join open_w on open_w.id = base.id
),
r as (
select b.*,
row_number() over (order by got) as rn_got,
cume_dist() over (order by got desc) as cd_got,
row_number() over (order by opened) as rn_op,
cume_dist() over (order by opened desc) as cd_op
from b
)
select case when grouping(gender) = 1 then null else gender end as gender,
count(*)::int as members,
count(*) filter (where last_seen > now() - make_interval(days => $1))::int as actives,
count(*) filter (where got = 0)::int as zero_window,
count(*) filter (where got_all = 0)::int as zero_ever,
count(*) filter (where opened = 0)::int as zero_openers,
coalesce(sum(got), 0)::int as total_got,
coalesce(sum(opened), 0)::int as total_openers,
percentile_cont(0.5) within group (order by got)::float8 as median_got,
percentile_cont(0.9) within group (order by got)::float8 as p90_got,
avg(got)::float8 as mean_got,
(avg(opened) filter (where last_seen > now() - make_interval(days => $1)))::float8 as mean_openers,
(percentile_cont(0.5) within group (order by opened)
filter (where last_seen > now() - make_interval(days => $1)))::float8 as median_openers,
case when grouping(gender) = 1 then sum(got) filter (where cd_got <= 0.10)::int end as top10_got,
case when grouping(gender) = 1 then sum(got) filter (where cd_got <= 0.25)::int end as top25_got,
case when grouping(gender) = 1 then sum(opened) filter (where cd_op <= 0.10)::int end as top10_openers,
case when grouping(gender) = 1 then
((2.0 * sum(rn_got::float8 * got) / nullif(count(*)::float8 * sum(got)::float8, 0))
- (count(*) + 1.0) / nullif(count(*), 0))::float8 end as gini_got,
case when grouping(gender) = 1 then
((2.0 * sum(rn_op::float8 * opened) / nullif(count(*)::float8 * sum(opened)::float8, 0))
- (count(*) + 1.0) / nullif(count(*), 0))::float8 end as gini_openers
from r
group by grouping sets ((), (gender))
order by gender nulls first`,
[days, graceDays]
);
return rows;
}
/*
* F. De vorm van de correspondenties zelf: hoe diep, hoe wederkerig, hoe levend.
*
* Het aandeel gelezen brieven heeft dezelfde valkuil als de antwoordkans — een stapel brieven van
* vanochtend drukt het omlaag en dat leest als achteruitgang. Daarom tellen alleen brieven die
* minstens `readSettleDays` hebben liggen.
*
* En één eerlijkheid over read_at: markThreadRead() zet hem voor een hele correspondentie tegelijk.
* "Tijd tot lezen" is hier dus tijd tot het ópenen van de correspondentie, niet tot het lezen van
* die ene brief. Het script noemt het ook zo.
*/
export async function pairShapes({ days = 30, readSettleDays = 7 } = {}) {
const { rows } = await query(
`with pair as (${PAIRS}), opener as (${OPENERS}),
fav as (select least(user_id, other_id) a, greatest(user_id, other_id) b, count(*)::int n from favorites group by 1, 2),
letters_settled as (
select * from letters
where sent_at > now() - make_interval(days => $1) and sent_at <= now() - make_interval(days => $3)
)
select count(*)::int as pairs,
count(*) filter (where o.letters = 1)::int as p1,
count(*) filter (where o.letters between 2 and 3)::int as p2,
count(*) filter (where o.letters between 4 and 9)::int as p3,
count(*) filter (where o.letters >= 10)::int as p4,
count(*) filter (where o.letters_a > 0 and o.letters_b > 0)::int as reciprocal,
count(*) filter (where o.letters >= $2 and least(o.letters_a, o.letters_b) >= $4)::int as real_corr,
(percentile_cont(0.5) within group (order by o.letters)
filter (where o.letters_a > 0 and o.letters_b > 0))::float8 as median_letters_recip,
count(*) filter (where o.last_at > now() - make_interval(days => $1))::int as alive,
count(*) filter (where o.opened_at > now() - make_interval(days => $1))::int as started_window,
(max(extract(epoch from o.last_at - o.opened_at)) / 86400)::float8 as longest_days,
((percentile_cont(0.9) within group (order by extract(epoch from o.last_at - o.opened_at))
filter (where least(o.letters_a, o.letters_b) >= $4)) / 86400)::float8 as p90_span_days,
count(*) filter (where f.n is not null)::int as pairs_with_favorite,
count(*) filter (where f.n = 2)::int as pairs_favorite_mutual,
(select count(*)::int from favorites) as favorites_total,
(select count(*)::int from letters_settled) as read_denom,
(select count(*)::int from letters_settled where read_at is not null) as read_n,
(select (percentile_cont(0.5) within group (order by extract(epoch from read_at - sent_at)))::float8
from letters_settled where read_at is not null) as median_read_s,
(select avg(length(body))::float8 from letters where sent_at > now() - make_interval(days => $1)) as avg_chars,
(select count(*)::int from photos p join letters l on l.id = p.letter_id
where l.sent_at > now() - make_interval(days => $1)) as photos_window
from opener o
left join fav f on f.a = o.a and f.b = o.b`,
[days, REAL_CORRESPONDENCE_LETTERS, readSettleDays, REAL_CORRESPONDENCE_EACH]
);
return rows[0];
}
/*
* G. Hygiëne. Alles hier is een ondergrens, en dat hoort erbij te staan: meldingen worden na
* REPORT_RETENTION_DAYS gewist, en blokkades én meldingen verdwijnen zodra een van beide accounts
* verdwijnt (blocks cascadeert aan beide kanten, reports.reporter_id valt naar null — vandaar
* `reports_orphaned`, waaraan die erosie af te lezen is).
*
* "Een correspondentie die op een blokkade eindigde" staat niet in het schema: een blokkade wijst
* niet naar een brief. Wat er wél staat is `blokkade ná de laatste brief`, en dat zegt iets
* scherpers — de laatste brief was de laatste.
*
* De vijandige opener heeft een tijdvenster nodig. Zonder venster krijgt de eerste brief de schuld
* van een blokkade die twee jaar later viel. Met venster betekent het cijfer wat het zegt: een
* eerste brief die binnen `hostileDays` op een blokkade of melding uitliep.
*
* reports_open_old is het enige getal hier dat de beheerder meet in plaats van de leden.
*/
export async function safetySignals({ days = 30, hostileDays = 30 } = {}) {
const { rows } = await query(
`with pair as (${PAIRS}), opener as (${OPENERS})
select (select count(*)::int from reports where created_at > now() - make_interval(days => $1)) as reports_window,
(select jsonb_object_agg(reason, n) from (
select reason, count(*)::int n from reports
where created_at > now() - make_interval(days => $1) group by 1) t) as reports_by_reason,
(select count(*)::int from reports where handled_at is null) as reports_open,
(select count(*)::int from reports where handled_at is null and created_at <= now() - interval '7 days') as reports_open_old,
(select (percentile_cont(0.5) within group (order by extract(epoch from handled_at - created_at)))::float8
from reports where handled_at is not null and created_at > now() - make_interval(days => $1)) as median_handle_s,
(select count(*)::int from reports where reporter_id is null) as reports_orphaned,
(select count(*)::int from blocks where created_at > now() - make_interval(days => $1)) as blocks_window,
(select count(*)::int from blocks) as blocks_total,
(select count(*)::int from (select blocked_id from blocks group by 1 having count(*) >= 2) t) as blocked_by_two,
(select count(*)::int from (select blocked_id from blocks group by 1 having count(*) >= 3) t) as blocked_by_three,
(select jsonb_object_agg(kind, n) from (
select kind, count(*)::int n from measures
where created_at > now() - make_interval(days => $1) group by 1) t) as measures_window,
(select count(*)::int from users u where ${suspended("u")}) as suspended_now,
(select count(*)::int from warnings where acknowledged_at is null) as warnings_pending,
count(*)::int as pairs,
count(*) filter (where o.opened_at <= now() - make_interval(days => $2))::int as openers_settled,
count(*) filter (where o.opened_at <= now() - make_interval(days => $2) and exists (
select 1 from blocks b
where b.user_id = o.target_id and b.blocked_id = o.opener_id
and b.created_at >= o.opened_at
and b.created_at <= o.opened_at + make_interval(days => $2)))::int as opener_blocked,
count(*) filter (where o.opened_at <= now() - make_interval(days => $2) and exists (
select 1 from reports r
where r.reporter_id = o.target_id and r.reported_id = o.opener_id
and r.created_at >= o.opened_at
and r.created_at <= o.opened_at + make_interval(days => $2)))::int as opener_reported,
count(*) filter (where exists (
select 1 from blocks b
where ((b.user_id = o.a and b.blocked_id = o.b) or (b.user_id = o.b and b.blocked_id = o.a))
and b.created_at >= o.last_at))::int as ended_in_block,
count(*) filter (where exists (
select 1 from blocks b
where (b.user_id = o.a and b.blocked_id = o.b) or (b.user_id = o.b and b.blocked_id = o.a)))::int as ever_blocked
from opener o`,
[days, hostileDays]
);
return rows[0];
}
/*
* E1. Bereikbaarheid onder de leden die er zijn: met hoeveel andere actieven matcht ieder actief lid
* wederzijds? De mediaan telt, maar `zero` telt het meest — dat zijn mensen voor wie de app leeg is
* zonder dat ze kunnen zien waarom.
*
* Dit is de enige O(n²)-meting in het rapport. Drie dingen houden hem betaalbaar:
*
* `as materialized` is dragend. Zonder dat woord vouwt Postgres de CTE in de lus en doet hij het
* sessie-aggregaat opnieuw per kandidaat — O(n²) wordt dan O(n² log n) mét een indexzoekactie per
* ronde. Dit is de belangrijkste regel van deze functie.
*
* De tegenspelers zijn alleen de actieven, niet alle leden. Dat is ook wat de maat bedóelt.
*
* `sample` trekt een steekproef uit de te meten kant; een mediaan uit 500 trekkingen is ruim
* nauwkeurig genoeg voor waar iemand naar handelt. null = iedereen.
*
* `left join`, niet inner: wie nul matches heeft moet een rij opleveren met nul, want dat is precies
* het lid waarvoor deze meting bestaat.
*/
export async function reachability({ days = 30, sample = 500 } = {}) {
const { rows } = await query(
`with act as materialized (${ACTIVES("$1")}),
me_set as (select * from act order by random() limit $2::int),
counted as (
select me.id, me.gender, count(u.id)::int as matches
from me_set me
left join act u on u.id <> me.id and ${MUTUAL_MATCH}
group by me.id, me.gender
)
select case when grouping(gender) = 1 then null else gender end as gender,
count(*)::int as measured,
(select count(*)::int from act) as actives,
count(*) filter (where matches = 0)::int as zero,
percentile_cont(0.10) within group (order by matches)::float8 as p10,
percentile_cont(0.50) within group (order by matches)::float8 as median,
percentile_cont(0.90) within group (order by matches)::float8 as p90,
avg(matches)::float8 as mean
from counted
group by grouping sets ((), (gender))
order by gender nulls first`,
[days, sample > 0 ? sample : null]
);
return rows;
}
/*
* E2. Het proefraster: wat zou een nieuwkomer aantreffen die er nog niet is?
*
* Wat een proefprofiel NIET moet zijn, en eerder was: de standaardinstellingen van createUser().
* Die zetten seek_genders op alle drie de genders, en dan telt de proef vrouwen én mannen én
* non-binaire mensen bij elkaar op — een optelsom over oriëntaties die geen mens maakt. Op de
* proefgemeenschap gaf dat 200 passende mensen voor een man van 35 waar het er 93 zijn. Een
* standaardinstelling is een eigenschap van het aanmeldscherm, geen eigenschap van een persoon.
*
* Daarom staat de oriëntatie hier expliciet in het profiel: wie ben je, en wie zoek je. De rest van
* de vraag ("hoeveel mensen kan ik schrijven") heeft pas betekenis als dat vastligt.
*
* Locatie op dezelfde manier: dat een vers account er geen heeft is een toevalligheid van het
* moment, niet iets over de persoon — en het maakte iedereen met een zoekstraal onzichtbaar voor de
* proef. De proef krijgt daarom een woonplaats getrokken uit waar de leden werkelijk wonen, en het
* cijfer is de mediaan over `placements` verschillende plaatsingen. Eén plaatsing zou de uitkomst
* laten afhangen van welke stad er toevallig uit de hoed kwam.
*
* Wat wél standaard blijft: alleen vrijgezellen, geen trans correspondenten, eigen leeftijd ±5.
* Dat zijn keuzes die het product bewust dichtzet (zie de matchquery) en die de meeste mensen laten
* staan; het rapport zegt erbij dat wie ze openzet meer vindt.
*
* Drie details maken dat MUTUAL_MATCH ongewijzigd over een verzonnen `me` heen kan:
*
* 1. `null::bigint as id` — de blokkadetoets vergelijkt `b.user_id = me.id`; met een null-id is
* dat nooit waar, de subquery levert niets, en `not exists` is dus waar. Een nieuwkomer is
* door niemand geblokkeerd. Klopt van nature.
* 2. `u.id is distinct from me.id` in de join, níet `<>`. Met een null-id is `u.id <> me.id`
* zelf null en valt élke rij weg — het raster zou stilletjes overal nul melden. Dit is de ene
* plek waar het idioom uit searchProfiles() niet gekopieerd kan worden.
* 3. Elke kolom die MUTUAL_MATCH op `me` aanraakt moet bestaan mét het juiste type, ook die van
* de straaltak (lat, lon, seek_radius_km). Zonder expliciete cast maakt Postgres er text van.
*/
export async function probeGrid({ profiles, ages, days = 30, responsiveDays = 90, placements = 15 } = {}) {
/* trans en seek_trans horen bij het profiel, niet bij de query: wie iedereen zoekt staat
logischerwijs ook open voor trans correspondenten, en wie zelf trans is helemaal (dat is ook
wat createUser() doet). Ze hardcoderen op false maakte profielen die zichzelf tegenspreken. */
const { rows } = await query(
`with act as materialized (${ACTIVES("$5")}),
responsive as (
select distinct l.from_id as id
from letters l
where l.sent_at > now() - make_interval(days => $6)
and exists (select 1 from letters e
where e.from_id = l.to_id and e.to_id = l.from_id and e.sent_at < l.sent_at)
),
/* Woonplaatsen van echte leden. Heeft niemand er een, dan één plaatsing zonder locatie —
dan is er ook geen afstandsfilter dat iets kan uitsluiten. */
plek as (
select lat, lon, row_number() over () as k from (
(select lat, lon from act where lat is not null order by random() limit $7)
union all
(select null::double precision, null::double precision
where not exists (select 1 from act where lat is not null))
) t
),
probe as (
select null::bigint as id,
p.label, p.gender::char(1) as gender,
p.trans as trans,
'vrijgezel'::text as relationship,
youngest_birthdate(a.age) as birthdate,
k.lat, k.lon, k.k,
string_to_array(p.seeks, ',')::char(1)[] as seek_genders,
false as seek_relatie,
p.seek_trans as seek_trans,
p.publiek as publiek,
/* Zelfde beginfilter als createUser(), uit dezelfde bron. Loopt dit uiteen, dan meet
de proef een gebruiker die niet bestaat. */
oldest_birthdate(${startFilter("a.age").oudste}) as seek_min_birth,
youngest_birthdate(${startFilter("a.age").jongste}) as seek_max_birth,
null::int as seek_radius_km,
a.age as age
from unnest($1::text[], $2::char(1)[], $3::text[], $8::boolean[], $9::boolean[], $10::boolean[])
as p(label, gender, seeks, trans, seek_trans, publiek)
cross join unnest($4::int[]) as a(age)
cross join plek k
),
geteld as (
select me.label, me.gender, me.age, me.k, me.publiek,
count(u.id)::int as matches,
count(u.id) filter (where r.id is not null)::int as responsive,
count(u.id) filter (where u.last_seen > now() - interval '7 days')::int as active7
from probe me
left join act u on u.id is distinct from me.id and ${MUTUAL_MATCH}
left join responsive r on r.id = u.id
group by me.label, me.gender, me.age, me.k, me.publiek
)
select label, gender, age, max(publiek::int) = 1 as publiek,
count(*)::int as placements,
(percentile_cont(0.5) within group (order by matches))::float8 as matches,
(percentile_cont(0.5) within group (order by responsive))::float8 as responsive,
(percentile_cont(0.5) within group (order by active7))::float8 as active7,
min(matches)::int as matches_min,
max(matches)::int as matches_max
from geteld
group by label, gender, age
order by label, age`,
[profiles.map((p) => p.label), profiles.map((p) => p.gender), profiles.map((p) => p.seeks.join(",")),
ages, days, responsiveDays, Math.max(1, placements),
profiles.map((p) => p.trans === true), profiles.map((p) => p.seekTrans === true),
profiles.map((p) => p.publiek !== false)]
);
return rows;
}
/*
* ---------- Waar op gezocht wordt ----------
*
* De vaakst gezochte woorden in het venster, en hoeveel profielen er op dit moment op zouden passen.
* Dat tweede getal maakt het pas een bevinding: "wandelen, 240 keer gezocht, 12 profielen" is een
* gat in de gemeenschap, "wandelen, 240 keer gezocht, 900 profielen" is alleen maar een populair
* woord. `profiles` telt over álle profielen en niet door iemands filter heen — dit rapport gaat
* over de gemeenschap, niet over één lid.
*
* Er komt geen persoon uit, want er is er geen: search_terms bevat losse woorden met een dagteller
* en verder niets (zie db/schema.sql).
*/
export async function searchTerms({ days = 30, limit = 40 } = {}) {
const { rows } = await query(
`with geteld as (
select term, sum(count)::int as searches, count(*)::int as days_seen
from search_terms
where day > current_date - $1::int
group by term
order by sum(count) desc
limit $2
)
select g.term, g.searches, g.days_seen,
(select count(*) from users u
where u.bio_tsv @@ to_tsquery('dutch', g.term || ':*'))::int as profiles
from geteld g
order by g.searches desc, g.term`,
[days, Math.max(1, limit)]
);
return rows;
}