Zweiter Schritt weg von Supabase. Sämtliche 49 Lesezugriffe und alle
Mutationen laufen jetzt über lib/db statt über die REST-Schicht: Kysely auf
einem pg-Pool, jede Abfrage in einer Transaktion, in der zuerst
app.user_id gesetzt wird. Die Anmeldung hängt noch an GoTrue — sie liefert
die Kennung, die in withUser() geht. Damit war der Umbau in zwei Hälften
teilbar und die Anwendung durchgehend lauffähig.
Was dabei ersatzlos verschwindet:
- fetchAllRows. Es gab die Funktion nur, weil PostgREST jede Antwort bei
1000 Zeilen still abschneidet und ein Bericht dann leise falsch war.
Am direkten Zugang ist eine Abfrage eine Abfrage.
- sanitizeIlikeTerm samt Test. Sie entschärfte Zeichen, die in der
Filtersyntax strukturelle Bedeutung hatten; jetzt wird der Suchbegriff
als Parameter gebunden und ein Komma ist ein Komma. Die Lücke ist nicht
abgesichert, sondern weg.
- lib/supabase/admin.ts. Der Dienstschlüssel, der RLS aushebelte, hatte
genau einen Aufrufer — den nächtlichen Lauf. Der benutzt jetzt dieselbe
Rolle ohne BYPASSRLS und ruft eine SECURITY-DEFINER-Funktion auf, die
selbst prüft, was sie tut. Es gibt keinen privilegierten Zugang mehr.
Nebenbei besser geworden, weil der direkte Zugang es erlaubt:
- Eine Seite ist eine Transaktion. Das Layout etwa liest Profil,
Planstellen, Standorte, Entwürfe und Notizen auf einem einheitlichen
Lesestand statt in fünf unabhängigen Anfragen.
- Der Bereichsfilter der Mitarbeiterliste ist ein EXISTS statt einer
eingebetteten Ressource mit !inner — eine Person mit mehreren
Zuordnungen über die Zeit erschien dort mehrfach.
- Seitenweise Listen sortieren zusätzlich nach id. Bei gleichem Nachnamen
oder gleichem Zeitstempel war die Reihenfolge vorher unbestimmt, und
dieselbe Zeile konnte auf zwei Seiten erscheinen oder auf keiner.
- Angehörige werden in der Datenbank gezählt statt alle Zeilen zu holen.
- Namen an Ereigniszeilen kommen aus einem Join statt aus einem
Nachschlag, der ausserhalb der Transaktion lag.
Der Statusfilter ist mitgezogen: dieselbe Regel wie deriveStatusAsOf,
Klausel für Klausel, jetzt als Kysely-Ausdruck. Der Integrationstest, der
beide über den gesamten Bestand vergleicht, läuft weiter — mit eigener
Verbindung, denn geprüft wird die Bedingung, nicht die Berechtigung.
Zwei Fehler auf dem Weg, beide vom Typprüfer gefangen: apply_due_pending_
changes() nimmt kein Argument, wurde von callFunction aber mit jsonb
aufgerufen — Postgres hätte keine passende Signatur gefunden. Und der
Sicherheitstest lädt jetzt Module mit `import "server-only"`, was ausserhalb
der Server-Übersetzung wirft.
Typecheck, Lint, Build und 180 Tests sind grün. Ungeprüft bleibt der Lauf
gegen eine echte Datenbank — dafür fehlt eine DATABASE_URL.
Against the hosted database a round trip costs about as much as the queries
themselves (~90ms), so page time was dominated by how many waves ran in
sequence rather than by the SQL. Measured with the median of five runs:
- Employee list 244ms -> 101ms. It awaited loadOrgMaps and only then the
page of employees; the lookup tables are needed to label rows, not to
build the query, so both now go out together.
- Employee detail 120ms -> 62ms. Nine of the ten queries key off the id
already in the URL and had no reason to wait for the employee row. The
manager comes back as an embedded resource on that row instead of a
follow-up query, which is what makes it one wave rather than two — an
intermediate version that merely reordered the waves measured *slower*,
and the embed is the part that actually helps.
- Reports 197ms -> 180ms. Three waves became two. Modest, and worth saying
so: the snapshot query itself dominates that page, not the wave count.
Also fixes a blank employee list I caused. `absence_type` was added to the
list's explicit column list ahead of its migration, and PostgREST rejects
the *entire* query for one unknown column — so `data` came back null and the
page rendered zero of 809 employees rather than just dropping a chip label.
The column is out of that select until 20260726120000_absence_type.sql is
applied; the detail page selects "*" and shows the kind once it exists.
Verified against the real database rather than by typecheck alone, which is
what would have caught it in the first place.
Data model
- employee_assignments records org placement over time (valid_from/valid_to),
written by a trigger on `employees` rather than inside each RPC: ~70
`update employees` statements spread over fifteen migrations mean per-call
bookkeeping would miss paths today and again with every future RPC. A
partial unique index enforces the one-open-interval invariant the trigger
relies on when closing the current row.
- The Organigramm gains a Stichtag (default today). Membership comes from
entry/exit/karenz, past placement from the new history, future placement
projected from pending_org_changes. Placements predating the migration are
backfilled with today's values and flagged as such in the UI, since
employee_history only ever stored free text and cannot be reconstructed.
Correctness
- Reports and exports silently truncated at PostgREST's 1000-row cap
(db.max_rows); employee_history is already past it at ~800 staff. Every
whole-table read now pages explicitly.
- XLSX date cells were a day early: ExcelJS converts a Date to an Excel
serial straight off getTime(), so a Date built at local midnight lands on
the previous day's serial in any positive-offset zone.
- Date handling is pinned to Europe/Vienna throughout, and date-only strings
are formatted without a Date round-trip. The dashboard's YTD window was
built by round-tripping a local Date through toISOString(), which shifted
it a day early and dropped 31 December entirely.
- Export routes parsed measure/group/split/eventType with unchecked `as`
casts, so an unknown value reached column headers as `undefined` and the
Content-Disposition filename. Parsed against the label maps now, with the
filename slugged as a backstop.
- toXlsx keyed columns by header text, silently dropping the second of any
two columns sharing a name — split columns take their header from data.
- The org chart tree walks had no cycle guard; nothing in the schema forbids
a manager_id cycle, and one would hang the tab rather than misreport.
- The login page reflected ?error= verbatim, letting anyone put arbitrary
text on the real sign-in screen; messages are looked up by code now.
- React Flow needs elementsSelectable on, or it sets pointer-events:none on
the whole node and the expand control stops responding.
UI
- Mobile: the shell was unusable below lg — a fixed 236px margin pushed
content off-screen with no mobile navigation at all. The sidebar is now a
drawer, dvh replaces vh, safe-area insets are honoured, inputs are 16px so
iOS stops zooming on focus, and form grids stack.
- Org chart nodes redesigned: per-kind accent stripes and icons, vacant
roles called out, expand control moved to the bottom edge carrying the
child count.
- Pagination is windowed; it previously rendered one link per page (54 for
the employee list, unbounded for the audit log).
- Positions page reduced to open positions with a single "Besetzen" action.
- The employee Organisation tab links into the org chart focused on that
person, reusing the chart's existing search-match highlighting.
Also included, uncommitted until now
- Dependants, HR notes, academic titles, split address fields, position
validity and role/employment fields, with their migrations and UI.
- Docker/compose deployment setup, data-model and security-review docs.
Adds the Berichte export pipeline (/api/export/{report,events,employees})
with shared CSV/XLSX writers in lib/export.ts and lib/reports-data.ts.
Security pass alongside it: sanitize .or() search terms against PostgREST
filter injection, sanitize spreadsheet cells against CSV/Excel formula
injection, stop leaking raw DB error messages to clients, harden the
service-role client with server-only, add baseline security headers, and
bump the vulnerable nested postcss via an override.
Reworks the app from a two-role (hr_admin/manager) model to a single
HR-only role gated by profiles.is_active, fixes transfer/promote/karenz/
reorg RPCs to actually defer future-dated changes via a new
pending_org_changes table instead of writing them immediately (applied
by a daily Vercel Cron route), makes reorg undo append-only instead of
deleting history, adds Karenz-return and history-date integrity guards,
deprecates the salary column, and adds explicit schema grants + perf
indexes needed to run against a fresh (non-hosted) Postgres instance.
Adds vitest unit + integration test suites (the latter against a real
local Supabase instance) covering all of the above, plus lint/typecheck/
build wiring (`npm run check`).
Reports (§4.8):
- lib/reports.ts: generic server-side aggregation engine over 9 measures
(Headcount, FTE, Eintritte, Austritte, Ø Bruttogehalt, Teilzeitquote,
Ø Alter, Ø Zugehoerigkeit, Frauenanteil) x 10 group-by dimensions, with
an optional second-dimension split (disabled for average-type
measures) and per-row drill-down data.
- app/(app)/reports/page.tsx: reads filters from the URL, fetches the
matching employees_directory rows server-side, aggregates in Node
(not shipped raw to the client), computes the total.
- ReportsPageClient: measure/group/split/filter controls, 6 preset
chips, saved-reports list (actions/reports.ts), CSV export
(client-side blob download), stacked bars with a color-keyed legend
when split is active, and click-to-drill-down into the underlying
people (capped at 12, "+N weitere", linking to /employees/[id]).
Audit log (§4.9):
- app/(app)/audit/page.tsx + AuditFilters: search (target/details/actor)
+ action-type filter, paginated table with colored action badges,
row links to the affected employee when target_employee_id is set,
and the required "unveraenderbar" footer note.
This completes every route from the spec's information architecture:
Dashboard, Mitarbeiter:innen (list+detail), Organigramm (3 views),
Positionen & Bereiche, Berichte, Audit-Log, plus the Hire wizard and all
6 action panels reachable from them.
Final verification: clean npm run build + tsc --noEmit, then a full
browser walkthrough of all 6 authenticated routes as hr_admin (zero
console errors, zero 5xx responses) and a role-check pass as the
manager test account confirming action buttons and the "+ Neueinstellung"
button are hidden, and salary is masked as "... (ausgeblendet)" on the
Vertrag & Gehalt tab. Swept the database for leftover test data from
the debugging sessions above - none found, seed data is clean.
