The database moved to a container of our own; the platform is gone.
This takes out what was left of it — and, where the leftovers were load
bearing, moves rather than deletes.
Moved, not deleted:
supabase/migrations/ -> db/migrations/ the schema's source of truth
supabase/build-org.ts -> scripts/build-org.ts
lib/supabase/types.ts -> lib/types.ts 52 import sites repointed
The bookkeeping needed care. It lived in `supabase_migrations.schema_migrations`,
and simply renaming the schema would have left the runner facing an empty
table: it would have called all 67 migrations pending and replayed them
against a database that is long since current. So the runner now creates
`migrationen.schema_migrations` and, once, copies the old rows across —
guarded so a second run does nothing and a fresh database skips it entirely.
Only then does migration 20260907100000 drop the old schema.
Deleted: the CLI config, the seed, the historical schema/function dumps
(nothing read them), scripts/umzug-von-supabase.sh (the move is done), and
both Supabase packages plus the CLI. Nothing in the application imported
them — the build now succeeds with no environment variables at all, which
is the proof.
Integration tests: six of them signed in through Supabase Auth and asserted
against the anon key and the service role. That model is gone, so the tests
were not portable — they are deleted. session-context and
employee-status-filter already ran on pg and are untouched; om-reporting is
ported to a direct connection because it guards a real risk (the reporting
line rule exists twice, once in SQL and once in TypeScript).
CI: the integration job started a Supabase stack. It now runs a postgres
service, applies deploy/db-init and every migration to an empty database —
that was the valuable part, and it still holds — then checks that a second
run is a no-op, which is what proves the bookkeeping works.
Docs: security-review.md audited a service-role key, a cookie adapter and
auth.users, none of which exist. Restating findings about removed components
would suggest today's system had been reviewed; it has not. It now records
what was removed and says a fresh review is due. data-model.md was already
marked obsolete and described the pre-OM schema; azure-migration.md was a
plan for a route not taken. Both deleted.
Verified: npm ci, typecheck, lint, 445 tests, build — all clean without the
packages. Integration tests skip cleanly with no database. Migration SQL and
the runner are reviewed but NOT executed: no Docker here, and the old
instance no longer resolves.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The app got slower as pages grew, and the reason was not the queries. It
was their number.
A transaction is pinned to one connection, and a connection runs queries
one after another. Every Promise.all in a withUser block looked like
concurrency and was a queue. Measured against the real database: the
round trip is ~36 ms, ten trivial `select 1` over one connection take
343 ms, over ten connections 39 ms. Nothing here is slow — the whole
dashboard payload is under 200 kB, and every table is around a thousand
rows.
More connections is the wrong answer: the RLS session context is per
transaction, so parallel reads mean parallel transactions, and those
multiply the connections the database will grant. Fewer round trips
instead. Postgres will return each sub-select as its own JSON column of
one result.
Per page view, counting the transaction frame:
shell (paid by every page) 10 → 4
overview 14 → 5
employee file 14 → 7
employee list 8 → 6
The overview plus its shell went from 24 round trips to 9 — about 860 ms
of pure waiting down to about 320 ms.
The one trap is documented where it bites: inside json_agg, Postgres
formats values itself and the driver's parsers (lib/db/pool.ts) never
see them. Dates, numerics and uuids come out identical; timestamptz does
not — "+00:00" where the driver gives "…Z". Timestamps are compared as
strings in lib/history.ts to decide what happened later, and those two
forms sort against each other wrongly. Every timestamptz in a bundled
query therefore goes through zeitstempel(), which was checked
character-for-character against the driver.
Four loaders moved out of their pages into lib/ so the number of round
trips can be measured without building a React tree, and so the new path
could be held against the old one field by field: same rows, same order,
same strings, for the overview and for four employee files chosen to
differ (with history, a chief, a planned entry, one with dependents).
withUser now counts the queries in each transaction and says so in
development past a threshold. Without that, this grows back: each new
tile brings its own query, and nobody notices until everybody does.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Six comments and doc lines put the number of RLS policies at 58. It is
21 — counted from pg_policy while building the data catalogue. The
figure appears in load-bearing prose ("all 58 policies call
is_hr_user()", "all 58 policies stay unchanged"), where being wrong by a
factor of three invites someone to go looking for the missing thirty-
seven.
The two occurrences inside supabase/migrations/ stay as they are. That
file already ran against the database; its comments record what was
believed at the time, and editing them would make the file differ from
what was applied for no gain.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Auth.js replaces GoTrue. The sign-in still goes to the same Entra tenant,
but nothing sits between the app and the identity provider any more — the
code exchange, state, nonce and the session cookie are ours.
lib/auth/session.ts stays the only place that knows where a user id comes
from, which is why this was one file and not fifty. What it returns is now
app_users.id. app_upsert_user() maps the Entra `oid` onto it, and for an
address that already has a profiles row it adopts that id instead of
minting a new one — otherwise everyone would have been signed in and cut
off from their own notes, drafts and audit trail at the same time.
That upsert is the one write that cannot have a session context yet: the
id is what it produces. It runs as a SECURITY DEFINER function that may
touch app_users and nothing else, which is a far smaller lever than the
service key that used to answer this class of problem.
The proxy no longer checks HR rights. It has no database connection, and
putting role/is_active in the token would have frozen the claim until the
next sign-in. The check moved to where it can read the current truth: the
app layout on every render, requireHrUser() for the export routes, and
underneath both, RLS.
Two things only came out by running it:
- `export const proxy = auth(…)` is not a function declaration, so
Next.js never found it and every request 404'd. `next build` reported
success and listed the proxy. In the function config form auth() also
returns the handler as a promise, so it needs an await. The proxy test
now mocks it as a promise for that reason — a friendlier mock would
let the same bug back in.
- A missing AUTH_MICROSOFT_ENTRA_ID_ISSUER silently falls back to
/common/, and the redirect really did go there. That would let any
Microsoft account sign in, including a private one, and it would never
look broken. It now refuses to start in production.
Neither build nor image needs credentials any more: the pool is created on
first use, the auth config is evaluated per request, and there are no
NEXT_PUBLIC_* values left to bake in. One image now runs in every
environment.
Verified: typecheck, lint, 187 tests, build, and by hand in the browser —
/employees redirects to /login, and the sign-in button reaches the Entra
page with PKCE and the callback URL that goes into the app registration.
Not verified against a real database; there is still no DATABASE_URL.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Erster Schritt weg von Supabase hin zu "läuft auf jedem PostgreSQL".
Gemessen sitzt die Kopplung nicht dort, wo der Begriff "Supabase-Projekt"
sie vermuten lässt: das Schema ist reines PostgreSQL, und von 58 RLS-Policies
rufen nur fünf auth.uid() direkt auf. Die übrigen 53 gehen über is_hr_user().
Diese eine Funktion ist die Brücke — wird sie umgelegt, folgt der Rest.
Die Migration legt sie um. app_current_user_id() liest jetzt zuerst
current_setting('app.user_id') und fällt nur ersatzweise auf auth.uid()
zurück. Deshalb plpgsql statt language sql: eine SQL-Funktion wird beim
Anlegen geparst, und auth.uid() gibt es auf einem gewöhnlichen PostgreSQL
nicht — die Migration liesse sich dort gar nicht erst anwenden. Der
Ausnahmeblock fängt das ab, und damit läuft dieselbe Migration auf beiden
Systemen. Der Rückfall verschwindet mit der Abschlussmigration.
Dazu app_users als Nachfolger von auth.users, external_id ist die oid des
Anbieters statt der E-Mail: eine Namensänderung darf kein zweites Konto
erzeugen.
Die neue Zugriffsschicht ist Kysely auf einem pg-Pool. Was daran zählt, ist
nicht der Query-Builder, sondern was er verhindert:
- Die Kysely-Instanz wird nicht exportiert. Wer abfragen will, geht durch
withUser() — und das öffnet immer eine Transaktion.
- set_config(..., true) ist transaktionslokal. Ohne das dritte Argument
bliebe die Kennung an der gepoolten Verbindung kleben und die nächste
Anfrage liefe im Namen der vorherigen Person. In einer Personaldatenbank.
- Eine ESLint-Regel verbietet den Import von pg und von lib/db/pool
ausserhalb von lib/db. Nachgewiesen: eine Testdatei mit beiden Importen
erzeugt zwei Fehler.
- Einen privilegierten Zugang gibt es nicht mehr. asSystem() benutzt
dieselbe Rolle ohne BYPASSRLS; was ohne angemeldete Person laufen darf,
muss als SECURITY-DEFINER-Funktion in der Datenbank stehen.
tests/integration/session-context.test.ts läuft gegen einen Pool mit genau
einer Verbindung — sonst träfe er die Lücke mal und mal nicht. Er prüft, dass
nach Commit *und* nach Rollback nichts an der Verbindung zurückbleibt, und
belegt in einer Gegenprobe, dass eine Einstellung ohne Transaktion tatsächlich
hängen bleibt. Ein Sicherheitstest, der sich mangels DATABASE_URL selbst
überspringt, wäre schlimmer als keiner: in der CI schlägt schon das Fehlen
des Verbindungsstrings fehl.
Beim Schreiben der Migration stellte sich heraus, dass die Policies
hire_drafts_owner und saved_reports_owner heissen, nicht _own. Mit dem
geratenen Namen hätte drop policy nichts getroffen und create policy wäre mit
"already exists" abgebrochen.
Typecheck, Lint und 182 Tests sind grün. Die Anwendung läuft unverändert
weiter — sie benutzt die neue Schicht noch nicht.
