Schema & Auth — Under the Hood
Index economics, the identity-mapping design space, and the races hiding in "find or create."
In this deep dive
What the indexes buy
Every index in schema.ts is a bet: pay on every write, win on a specific read. Here's where the bets pay off:
| Query | Cost | Served by |
|---|---|---|
| Auth lookup by (provider, providerUserId) | O(log n) | auth_identities_provider_user_unique — one B-tree probe per request |
| Dedup check per ingested item | O(log n) | job_postings_user_fingerprint_idx — the hot path of every discovery run |
| "Everything In Prep" filter | O(log n + k) | applications_user_status_idx — tenant + status as index prefix |
| Due reminders sweep (hourly) | O(log n + k) | reminders_user_status_remind_at_idx — three-column index matches the exact predicate |
| Pipeline list w/ needs-flags | O(n·j) one query | Aggregate joins over resume versions + documents, count(distinct …) filter (where …) per kind |
| Monthly AI spend | O(rows this month) | ai_runs_user_created_at_idx range scan + sum — runs before every AI call |
The interesting one is the pipeline list: joining applications × resume_versions × generated_documents produces a row explosion before GROUP BY collapses it — with 100 applications × ~3 documents each it's ~300 intermediate rows, trivial; at 10,000 × 50 it's 500k and you'd want the counts as correlated subqueries or a materialized rollup. The correlated subquery for the latest substate event already shows the alternative shape in the same query.
One deliberate inefficiency: getCurrentUser() runs the identity lookup on every server action and page render — Wera trades a cached session claim for always-fresh user rows, acceptable at one B-tree probe a time. The M6 "no-op fast path" (skip the UPDATE when email/name are unchanged) exists precisely because the original version wrote to users on every request — a hot-path write for zero information.
The decision: internal users + an identity mapping table
Own users table + auth_identities
Vendor-neutral foreign keys — an Auth.js migration is additive, not surgical.
Multiple identities can map to one user (email match attaches, doesn't duplicate).
A sync cascade to maintain, with its own race conditions (see below).
Clerk user ID as the primary key everywhere
Zero mapping code; the session IS the identity.
Twenty tables of foreign keys keyed to a vendor's ID format — the definition of lock-in.
Webhook dependency to learn about user changes.
Postgres row-level security (RLS)
Tenancy enforced in the database — a forgotten WHERE clause can't leak.
Policies add operational complexity; the HTTP driver + per-request role plumbing is awkward; local-dev and test ergonomics suffer.
A second decision worth noticing: the candidate profile is a structuredData jsonb blob rather than normalized projects/skills/coursework tables. That's the right call while the profile's shape is still being discovered — but it has a real cost you'll meet in Module 4: evidence IDs like projects.0 are positions in an array, not stable keys. Normalization is the documented exit when profile editing gets serious.
Third: Postgres enums for statuses (vs plain text + check constraint). Enums give storage efficiency and typo-proofing; their cost is migration friction — adding a value is an ALTER TYPE. For a state machine as settled as the spec's ten statuses, the enum wins; for anything still evolving weekly, text + check would be kinder.
The races in "find or create"
syncAuthUser is a classic TOCTOU shape: find by identity → find by email → create. Two concurrent first-ever requests from the same new user (two tabs, double OAuth redirect) can both reach "create":
Both requests find no identity and no email match; both INSERT into users. The second insert dies on users_email_unique (the case-insensitive lower(email) unique index) — the constraint saves data integrity, but the user sees an error on one tab. The idiomatic hardening: catch the unique violation and re-run the lookup, or collapse the cascade into one INSERT … ON CONFLICT. Notice the pattern: the constraint is the real guard; the code is just choosing how gracefully to bounce off it.
- Identity reattachment:
attachIdentityupserts on (provider, providerUserId) and reassignsuserIdon conflict. That's what makes "same email, new Clerk account" converge on one user — and also what would let a mis-issued identity silently steal a user if provider IDs ever collided across environments. Trust boundary: provider IDs are assumed globally unique per provider. - Email drift: the sync updates email/name from the identity on every change, and
findByEmailis case-insensitive. But if a user changes their email at Clerk to one that already belongs to another Wera user, the update collides with the unique index — an unhandled 500 today, a support ticket either way. Rare, real, and worth a typed error. - Cascade blast radius:
ON DELETE CASCADEfromusersreaches every table in the system by design (account deletion = one statement). The discipline that makes this safe: no table holds cross-user shared data. The moment one does (say, a globalcompaniescatalog), that cascade becomes a footgun — which is whycompaniesis per-user too, denormalized on purpose. - Email-less accounts: a Clerk user with no email address at all throws in
getCurrentUser— a documented open decision (TODOS.md) rather than silent acceptance of an unmappable identity. Fail loud at the boundary beats a null email propagating through 23 tables.
Make it yours
Fix the double-create race
Rewrite createUserWithIdentity (packages/db/src/auth.ts:76) so two concurrent calls for the same new email both succeed and converge on one user. Write the PGlite integration test that proves it (fire both promises with Promise.all).
Hint
INSERT INTO users … ON CONFLICT ((lower(email))) DO UPDATE SET updated_at = now() RETURNING * makes both calls return the same row; then upsert the identity. Drizzle's onConflictDoUpdate can target an expression index.
Plan the workspaces migration
The spec defers a real workspaces table until multi-seat arrives. Write the migration plan: new tables, backfill strategy from user_id, which indexes change, and what happens to getCurrentWorkspace(). Keep the app deployable at every step (expand → backfill → contract).
Hint
Expand/contract: add nullable workspace_id alongside user_id, backfill 1:1, dual-write, flip reads, then enforce NOT NULL. The seam function means call sites don't change.
Break tenancy on purpose
In a scratch branch, remove eq(applications.userId, userId) from one store query and try to write a test that catches the leak. What kind of test finds it — unit, integration, or E2E? What does that tell you about where tenancy tests belong?
Hint
Only a test that seeds TWO users into a real database can see the leak — this is exactly what the PGlite harness exists for.
The database-enforced alternative to application-layer tenancy — read it to understand what Wera traded away and why.
The atomic upsert primitive underneath every onConflictDoUpdate in this codebase — including its subtle interaction with partial and expression indexes.
The DDD name for the auth seam pattern: keep a vendor's model from leaking into yours.