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.
-- Liefdesbrieven: schema
-- Uitgangspunten:
-- * leeftijden zijn voorkant, geboortedata zijn achterkant (zie src/lib/dates.js)
-- * fulltext op de profieltekst én op de brieven, met de Nederlandse config
-- * matching is symmetrisch en gebeurt in één query (zie server/queries.js)
drop table if exists schema_migrations;
drop table if exists password_resets;
drop table if exists search_terms;
drop table if exists farewells;
drop table if exists departures;
drop table if exists purchases;
drop table if exists signup_slots;
drop table if exists sessions;
drop table if exists measures;
drop table if exists warnings;
drop table if exists reports;
drop table if exists blocks;
drop table if exists favorites;
drop table if exists photos;
drop table if exists letters;
drop table if exists users;
drop function if exists oldest_birthdate(int);
drop function if exists youngest_birthdate(int);
drop function if exists distance_km(double precision, double precision, double precision, double precision);
-- Leeftijdsgrens → geboortedatumgrens, identiek aan agesToBirthRange() in de frontend:
-- minBirth (oudste toegestaan) = vandaag - (aMax + 1) jaar + 1 dag
-- maxBirth (jongste toegestaan) = vandaag - aMin jaar
create function oldest_birthdate(age_max int) returns date
language sql stable
as $$ select (current_date - make_interval(years => age_max + 1) + interval '1 day')::date $$;
create function youngest_birthdate(age_min int) returns date
language sql stable
as $$ select (current_date - make_interval(years => age_min))::date $$;
-- Grootcirkelafstand (haversine) in km; geen PostGIS nodig
create function distance_km(lat1 double precision, lon1 double precision,
lat2 double precision, lon2 double precision) returns double precision
language sql immutable
as $$
select 6371.0 * 2 * asin(sqrt(
power(sin(radians(lat2 - lat1) / 2), 2) +
cos(radians(lat1)) * cos(radians(lat2)) * power(sin(radians(lon2 - lon1) / 2), 2)
))
$$;
create table users (
id bigint primary key generated always as identity,
-- account (e-mail wordt door de server in kleine letters opgeslagen)
email text not null unique check (email = lower(email) and email like '%@%'),
password_hash text not null, -- zie server/auth.js
created_at timestamptz not null default now(),
-- AVG: wanneer de gebruiker uitdrukkelijk toestemming gaf voor de gevoelige gegevens
-- (gender, trans, relatiestatus, zoekfilter). Null = nog niet (bestaande accounts van vóór
-- de privacyverklaring) → de app vraagt het alsnog vóór verder gebruik.
consent_at timestamptz,
-- identiteit
name text not null check (length(name) between 1 and 40),
gender char(1) not null check (gender in ('v', 'm', 'x')),
trans boolean not null default false, -- onafhankelijk van gender: "trans man"
birthdate date not null check (birthdate <= current_date - interval '18 years'),
bio text not null default '',
-- relatiestatus: heeft deze gebruiker al een romantische relatie? (in te stellen bij Mijn profiel)
relationship text not null default 'vrijgezel' check (relationship in ('vrijgezel', 'relatie')),
-- Profiel tijdelijk verbergen (vinkje onder de profieltekst): dan verschijn je niet meer in
-- Zoeken en kan niemand een eerste brief sturen. Lopende correspondenties gaan gewoon door —
-- verbergen is even niet gevonden willen worden, niet vertrekken. Zie MUTUAL_MATCH in
-- server/queries.js, waar dit samenvalt met de regel dat een leeg profiel niet verschijnt.
hidden boolean not null default false,
last_active timestamptz not null default now(),
-- Handhaving (zie scripts/reports.js). Geschorst = suspended_at gezet én suspended_until null
-- (onbepaalde tijd) of nog in de toekomst. Een schorsing wist niets: inloggen wordt geweigerd
-- met de reden erbij, de sessies vervallen, en de persoon verdwijnt uit Zoeken — de brieven die
-- er al liggen blijven staan, ook bij de mensen die ze kregen.
suspended_at timestamptz,
suspended_until timestamptz,
suspend_reason text not null default '',
-- locatie (optioneel)
lat double precision check (lat between -90 and 90),
lon double precision check (lon between -180 and 180),
-- filter: wie deze gebruiker wil zien
seek_genders char(1)[] not null default '{}',
seek_relatie boolean not null default false, -- ook mensen in een relatie zien? (vrijgezellen altijd)
seek_trans boolean not null default false, -- ook trans personen zien? Beschermt: wie trans is, verschijnt
-- alleen bij wie hier bewust voor openstaat
seek_min_birth date not null, -- oudste toegestane geboortedatum
seek_max_birth date not null, -- jongste toegestane geboortedatum
seek_radius_km int check (seek_radius_km > 0), -- null = geen afstandsfilter
-- fulltext-kolom, automatisch bijgehouden
bio_tsv tsvector generated always as (to_tsvector('dutch', bio)) stored,
check (seek_min_birth <= seek_max_birth),
check ((lat is null) = (lon is null)), -- beide of geen
check (seek_radius_km is null or lat is not null), -- straal vereist een locatie
check (suspended_until is null or suspended_at is not null) -- een einddatum zonder schorsing bestaat niet
);
create index users_bio_fts on users using gin (bio_tsv);
create index users_birthdate on users (birthdate);
create index users_gender on users (gender);
create index users_lat_lon on users (lat, lon);
-- Ingelogde sessies: de client bewaart het token, hier staat alleen de sha256 ervan
create table sessions (
token_hash text primary key,
user_id bigint not null references users (id) on delete cascade,
created_at timestamptz not null default now(),
last_seen_at timestamptz not null default now()
);
create index sessions_user on sessions (user_id);
-- Wachtwoord vergeten: de mail bevat een link naar /wachtwoord op de webserver; hier staat de
-- sha256 van het token uit die link. Eén rij per persoon — opnieuw aanvragen vervangt de vorige
-- link, en dat is precies goed: er hoort er maar één te werken. De rij verdwijnt bij gebruik
-- (het token is eenmalig), bij verlopen (purgeExpired) en met het account (cascade).
create table password_resets (
user_id bigint primary key references users (id) on delete cascade,
token_hash text not null unique,
expires_at timestamptz not null,
created_at timestamptz not null default now()
);
create table letters (
id bigint primary key generated always as identity,
from_id bigint not null references users (id) on delete cascade,
to_id bigint not null references users (id) on delete cascade,
sent_at timestamptz not null default now(),
read_at timestamptz, -- null = ontvanger heeft hem nog niet geopend
body text not null check (length(body) >= 300),
-- fulltext-kolom, automatisch bijgehouden: zoeken binnen één correspondentie. Dezelfde
-- Nederlandse config als bij de profielteksten, dus 'boek' vindt ook 'boeken'.
body_tsv tsvector generated always as (to_tsvector('dutch', body)) stored,
check (from_id <> to_id)
);
-- Postvak: alle brieven waar ik bij betrokken ben, gesorteerd op partner en datum
create index letters_from_to on letters (from_id, to_id, sent_at);
create index letters_to_from on letters (to_id, from_id, sent_at);
-- Ongelezen post per ontvanger
create index letters_unread on letters (to_id) where read_at is null;
-- Zoeken door het verleden van één correspondentie
create index letters_body_fts on letters using gin (body_tsv);
-- De foto bij een brief: hoogstens één, en de bytes staan hier, niet op schijf. Waarom in de database:
-- verwijderen cascadeert dan vanzelf mee met de brief en dus met het account (AVG-vergetelheid blijft
-- één DELETE), er is één back-up, en de server hoeft nergens naar te kunnen schrijven.
-- De app verkleint en hercodeert de foto vóór verzenden, dus hier komt nooit EXIF (gps!) binnen;
-- de server controleert daarnaast de magische bytes tegen de opgegeven mime (zie server/media.js).
-- `alt` is verplicht: het is zowel het bijschrift onder de foto als de alt-tekst voor schermlezers.
create table photos (
id bigint primary key generated always as identity,
letter_id bigint not null unique references letters (id) on delete cascade,
mime text not null check (mime in ('image/jpeg', 'image/png', 'image/webp')),
bytes bytea not null check (length(bytes) between 1 and 1048576), -- max 1 MB
width int not null check (width between 1 and 10000),
height int not null check (height between 1 and 10000),
alt text not null check (length(alt) between 1 and 200),
created_at timestamptz not null default now()
);
-- Favoriete correspondenten: persoonlijk en eenzijdig (de ander ziet er niets van).
-- Alleen zinvol voor mensen met wie je al brieven uitwisselt (de server controleert dat);
-- favorieten staan bovenaan in het postvak.
create table favorites (
user_id bigint not null references users (id) on delete cascade, -- wie markeert
other_id bigint not null references users (id) on delete cascade, -- wie gemarkeerd is
created_at timestamptz not null default now(),
primary key (user_id, other_id),
check (user_id <> other_id)
);
-- Blokkades: stil en eenzijdig. Verbergt, wist niets: de correspondentie verdwijnt uit het
-- postvak van de blokkeerder en beiden zien elkaar niet meer in Zoeken; niemand kan de ander
-- nog schrijven. Deblokkeren (bij Mijn profiel) brengt alles terug zoals het was.
create table blocks (
user_id bigint not null references users (id) on delete cascade, -- wie blokkeert
blocked_id bigint not null references users (id) on delete cascade, -- wie geblokkeerd is
created_at timestamptz not null default now(),
primary key (user_id, blocked_id),
check (user_id <> blocked_id)
);
create index blocks_blocked on blocks (blocked_id);
-- Meldingen van misbruik: een persoon, of één brief van die persoon. Blokkeren is voor jezelf
-- (het verbergt), melden is voor de beheerder (die kan ingrijpen) — daarom bestaan ze naast elkaar.
-- Afhandelen gaat met `npm run reports` (er is geen beheerdersscherm in de app).
--
-- De drie sleutels vervallen bewust verschillend:
-- * reported_id cascade — is de gemelde persoon weg, dan valt er niets meer te beoordelen;
-- * reporter_id set null — een melding mag niet verdwijnen doordat de melder zijn account
-- opzegt; juist bij misbruik is dat precies wat er gebeurt. Wat
-- overblijft is de melding zonder de naam van de melder;
-- * letter_id set null — idem: de melding blijft leesbaar als de brief er niet meer is.
create table reports (
id bigint primary key generated always as identity,
reporter_id bigint references users (id) on delete set null, -- null = melder bestaat niet meer
reported_id bigint not null references users (id) on delete cascade,
letter_id bigint references letters (id) on delete set null, -- null = de persoon, niet één brief
reason text not null check (reason in
('minderjarig', 'intimidatie', 'seksueel', 'nep', 'oplichting', 'anders')),
details text not null default '' check (length(details) <= 1000),
created_at timestamptz not null default now(),
handled_at timestamptz, -- null = nog te bekijken
check (reporter_id is null or reporter_id <> reported_id)
);
-- De werkvoorraad: openstaande meldingen, oudste eerst
create index reports_open on reports (created_at) where handled_at is null;
-- "Hoe vaak is deze persoon al gemeld?" — de vraag die één melding pas betekenis geeft
create index reports_reported on reports (reported_id);
create index reports_reporter on reports (reporter_id);
-- Waarschuwingen: de lichtste maatregel, en de enige die de gebruiker te zien krijgt. Bij het
-- openen van de app staat een onbeantwoorde waarschuwing vóór alles; wegklikken zet
-- acknowledged_at en is meteen het bewijs dat hij gelezen is. Meerdere mogen: een tweede
-- waarschuwing over hetzelfde is een feit dat ertoe doet.
create table warnings (
id bigint primary key generated always as identity,
user_id bigint not null references users (id) on delete cascade,
text text not null check (length(text) between 1 and 1000),
created_at timestamptz not null default now(),
acknowledged_at timestamptz
);
create index warnings_pending on warnings (user_id, created_at) where acknowledged_at is null;
-- Genomen maatregelen: het logboek van wat er ná een melding gebeurd is. Zonder dit is "we kijken
-- ernaar" niet te controleren en is een tweede overtreding niet van een eerste te onderscheiden.
-- Beide sleutels vervallen naar null: is het account weg, dan blijft staan dát er is opgetreden,
-- zonder nog te zeggen tegen wie — er valt dan niets meer te handhaven en dus niets meer te bewaren.
create table measures (
id bigint primary key generated always as identity,
user_id bigint references users (id) on delete set null,
report_id bigint references reports (id) on delete set null,
kind text not null check (kind in
('waarschuwing', 'brief_verwijderd', 'schorsing', 'opheffing', 'account_verwijderd')),
note text not null default '',
created_at timestamptz not null default now()
);
create index measures_user on measures (user_id, created_at);
-- ---------- Wat een plek kost ----------
--
-- De gratis bak: hoeveel accounts er zonder betaling zijn aangemaakt. Eén rij, en met opzet een
-- teller in plaats van `select count(*) from users` — een account dat weer verwijderd wordt geeft
-- zijn plek niet terug. Zou het dat wel doen, dan is "aanmaken, wissen, opnieuw" de goedkoopste
-- manier om de bak leeg te halen, en dat is precies wat hier niet moet kunnen.
--
-- De bovengrens staat niet hier maar in src/constants.js (FREE_ACCOUNTS): dan is hij te verzetten
-- zonder de database aan te raken, en dat is bij dit schema het verschil tussen een herstart en
-- alles opnieuw opzetten. Ophogen mag altijd, verlagen sluit de deur voor wie nog niet binnen is.
create table signup_slots (
singleton boolean primary key default true check (singleton),
granted int not null default 0 check (granted >= 0)
);
insert into signup_slots default values;
-- Betaalde plekken. De betaling zelf gaat volledig buiten ons om: Google is de verkoper (straks
-- Apple erbij), en wij zien geen naam, geen kaartnummer en geen bedrag — alleen een bonnetje dat de
-- server bij de store natrekt (server/billing.js). Wat hier staat is dus geen betaalgegeven maar
-- het bewijs dát deze plek betaald is, plus de sleutel die verhindert dat één bon twee accounts
-- oplevert: `unique (provider, token)`.
--
-- user_id vervalt naar null en cascadeert met opzet níet. Zou de rij meeverdwijnen met het account,
-- dan is "account wissen en opnieuw registreren" een gratis tweede account op dezelfde aankoop.
-- Wat er na een verwijdering overblijft is een bon zonder persoon: voor ons een opaak token.
create table purchases (
id bigint primary key generated always as identity,
provider text not null check (provider in ('google', 'apple')),
token text not null, -- Play: purchaseToken; App Store: transaction id
product_id text not null,
order_id text not null default '', -- het bonnummer van de store, voor terugbetalingen
user_id bigint references users (id) on delete set null,
created_at timestamptz not null default now(),
unique (provider, token)
);
create index purchases_user on purchases (user_id);
-- ---------- Vertrek ----------
--
-- Een account dat weggaat neemt alles mee: profiel, brieven, blokkades, meldingen. Dat is precies
-- wat de AVG vraagt en het is goed zo — maar het betekent ook dat élke telling over het verleden
-- stilletjes krimpt, en altijd dezelfde kant op. Wie alleen `users` telt ziet een gemeenschap waar
-- niemand ooit is weggegaan.
--
-- Dit is de enige rij die een vertrek overleeft, en ze wijst niet naar een persoon: geen id, geen
-- naam, geen gender, geen leeftijd, geen tekst. Wat er staat is een datum, hoe lang iemand meedeed,
-- waarom hij weg is, en één bit: had hij ooit een brief geschreven? Dat laatste scheidt "geprobeerd
-- en niets gevonden" van "nooit begonnen", en dat is het verschil waar een gemeenschap iets aan kan
-- doen. Met opzet géén vreemde sleutel naar users: het hele punt is dat deze rij blijft staan.
--
-- Waarom niet afgeleid uit signup_slots.granted: die teller is een poort, geen grootboek. Hij stopt
-- bij FREE_ACCOUNTS en zegt daarna niets meer, en hij telt alleen gratis plekken. Een verschil
-- tussen die teller en het ledental is dus geen verloopcijfer maar een toevalligheid.
create table departures (
id bigint primary key generated always as identity,
at timestamptz not null default now(),
days_member int not null check (days_member >= 0),
reason text not null check (reason in ('zelf', 'slapend', 'maatregel')),
wrote boolean not null -- had deze persoon ooit een brief verstuurd?
);
create index departures_at on departures (at);
-- De brief die iemand bij zijn vertrek achterlaat. Wie weggaat mag de beheerder schrijven: wat hij
-- hier gevonden heeft, of wat er mis was. Vrijwillig, en het houdt niemand tegen — de knop wist het
-- account met of zonder brief.
--
-- Deze rij overleeft het wissen, net als `departures`, en om dezelfde reden wijst ze naar niemand:
-- geen id, geen naam, geen e-mailadres, geen leeftijd, geen gender. Wat er staat is de tekst en
-- wanneer hij geschreven is, en dat is met opzet alles — een afscheidsbrief mét afzender zou een
-- accountverwijdering zijn die de persoon niet echt laat gaan, en een klacht die je alleen durft in
-- te dienen als je naam er niet bij staat is precies de klacht die je wilt horen.
--
-- De prijs staat er ook bij: antwoorden kan niet. Wie een antwoord wil, mailt (de app zegt dat erbij).
-- Lezen doet de beheerder met `npm run reports -- --afscheid`.
create table farewells (
id bigint primary key generated always as identity,
at timestamptz not null default now(),
body text not null check (length(body) between 1 and 5000)
);
create index farewells_at on farewells (at);
-- ---------- Waar op gezocht wordt ----------
--
-- Wat mensen in Zoeken intypen is het enige signaal over wat ze hopen te vinden dat niet uit de
-- profielen zelf komt: een woord dat vaak gezocht wordt en zelden in een profieltekst staat, is een
-- gat in de gemeenschap. Daarvoor is dit er.
--
-- Wat hier níet staat is minstens zo belangrijk. Geen gebruiker, geen sessie, geen tijdstip fijner
-- dan de dag, en niet de zoekopdracht zelf maar de losse woorden eruit (`toTerms()` in
-- server/queries.js, dezelfde die naar Postgres gaat). Dat is met opzet, en het is precies wat een
-- zoekgeschiedenis onderscheidt van een telling: wie "wandelen" zoekt telt hier op bij iedereen die
-- dat ook zocht, en "marieke van der berg" valt uiteen in vier woorden die elk bij duizend anderen
-- op de hoop vallen. Er is geen rij die iets over één persoon zegt, en dus ook niets te exporteren,
-- te verwijderen of te vorderen.
--
-- Eén regel per zoekintentie, niet per toetsaanslag: de server houdt de laatste zoekopdracht per
-- gebruiker even vast en telt hem pas als er iets anders komt of als het stil blijft (zie
-- server/search-log.js). Anders telt "dostojevski" hier als do, dos, dost, dosto.
create table search_terms (
term text not null check (length(term) between 2 and 40),
day date not null default current_date,
count int not null default 1 check (count > 0),
primary key (term, day)
);
create index search_terms_day on search_terms (day);
-- ---------- Migraties ----------
--
-- Sinds de productie-release (24-08-2026) is de database niet meer wegwerpbaar. Dit bestand blijft
-- de waarheid voor een vérse installatie, maar een bestáánde database verandert alleen nog via
-- db/migrations/ (npm run db:migrate — de spelregels staan in db/migrate.js). Elke wijziging
-- bestaat dus twee keer: als migratie voor de database die er al staat, en verwerkt in dit bestand
-- voor wie opnieuw begint. Na een verse opbouw registreert db/setup.js alle migraties hier als
-- uitgevoerd, want dit bestand ís dan al de nieuwste stand.
create table schema_migrations (
filename text primary key,
applied_at timestamptz not null default now()
);