Schema & Auth Foundations
Twenty-three tables as the product's nouns, tenancy from day one, and an auth vendor kept at arm's length.
Before features, Wera modeled its world: users, postings, applications, events, runs, reminders — 23 Postgres tables, every one carrying user_id. Clerk handles sign-in, but Wera keeps its own users table and treats Clerk as one replaceable identity provider. Uniqueness rules in the schema do real work: they make retries safe and duplicates impossible.
A schema is a city zoning plan. Before anyone builds, you decide: here's where residences go (user data), here's the industrial district (background job ledgers), here's the archive (event history) — and crucially, here are the rules no building may violate, no matter who the developer is. Wera's zoning rules are its indexes and constraints, and they're doing more work than most of its code.
The nouns, in one glance
Identity
users, auth_identities, profiles — who you are, how you signed in, and your structured candidate data.
The pipeline
companies, job_postings, applications, application_events — the core objects and their append-only audit trail.
Discovery ledger
source_connectors, source_runs, source_items, source_signals — raw scraped data kept separate from normalized truth.
Documents & AI
resume_templates, resume_versions, generated_documents, ai_runs — artifacts plus a cost ledger for every model call.
Work & time
application_tasks, reminders, weekly_plans, weekly_plan_items, notification_preferences — what to do and when.
Safety
contacts, approval_requests — nothing external (an email, a referral ask) executes without a recorded human approval.
The build journey
Create tables per phase — never speculatively
Each table lands in the migration for the phase that first uses it; there are seven migrations because there were seven slices of need.
The spec's rule: "Each table is created in the migration for the phase that first consumes it — no speculative tables." You can read the product's growth in packages/db/migrations/ like tree rings — 0000 is the loop, 0001 is discovery, 0005–0006 are reminders.
Drizzle schema in schema.ts (869 lines) plus generated SQL migrations.
Speculative tables are bets you pay to maintain before you know they're right. Modeling only what a shipping slice needs keeps every column justified by a feature.
pnpm db:generate diffs the TypeScript schema against the last snapshot and emits SQL; db:migrate replays it. The schema file is the single source of truth — types, tables, and indexes in one place.
Stamp every row with its owner
Every domain table has user_id with cascade delete — a one-user app wearing SaaS-grade tenancy from birth.
Wera has exactly one user today. Every table still carries user_id, every store method takes it, and every query filters by it. This is multi-tenancy as a habit, not a feature.
A user_id … references users.id on delete cascade column on all 20+ domain tables, plus composite indexes that lead with it.
Retrofitting tenancy is one of the most painful migrations in software — every query, every index, every test changes. Wearing it from day one costs one column.
Account deletion becomes one DELETE FROM users — the cascade fans out to everything. And "your data can't leak into my query" is enforced by the shape of every index: (user_id, status), (user_id, fingerprint).
Let constraints do the guarding
Unique indexes make "apply to the same job twice" and "run the same import twice" structurally impossible, not just discouraged.
Here is the exact index block on the applications table — two lines of schema that replace a whole class of defensive code:
(table) => ({
userPostingUnique: uniqueIndex("applications_user_job_posting_unique").on(
table.userId,
table.jobPostingId,
),
userStatusIdx: index("applications_user_status_idx").on(table.userId, table.status),
}),
Attach rules to the applications table…
Rule one: a user may have at most one application per job posting. The database itself refuses a second — saving the same discovered job twice quietly becomes an update instead of a duplicate.
(the pair that must be unique: owner…
…and the posting.)
Rule two isn't a rule but a speed lane: an index on (owner, status) so "show me everything In Prep" never scans other users' rows — tenancy and performance in the same stroke.
Done — two schema lines, one bug class deleted.
Wrap the auth vendor in a seam
Clerk authenticates; Wera then maps the Clerk identity onto its own users row via a three-branch cascade — and domain code never sees a Clerk object.
Think of it as a passport desk at a border: Clerk is the foreign passport, the auth_identities table is the visa record, and the users row is the residency card everything else in the country recognizes. Change passport vendors and only the desk's procedure changes.
getCurrentUser() / requireUser() / syncAuthUser() in apps/web/src/lib/auth.ts, backed by an AuthUserStore interface.
Domain tables reference internal users.id, never Clerk IDs — so migrating to Auth.js later means swapping the desk, not re-keying 20 tables of foreign keys.
syncAuthUser cascades: found by (provider, providerUserId)? → fast no-op if email/name unchanged. Not found? → match by email and attach the identity. Still nothing? → create user + identity. Lazy upsert on first authenticated request; no webhooks needed.
The central idea: tenancy as a birthright
Every filing cabinet in Wera has a name label on every single folder — even though only one person uses the office. Why bother? Because the day a second person joins, an office where folders were never labeled has to re-file everything, and one missed folder means someone reads a stranger's mail. Labeling from day one costs a sticker per folder; retrofitting costs the whole archive.
Row-level tenancy: every domain table carries user_id NOT NULL REFERENCES users(id) ON DELETE CASCADE, every store method takes userId as a parameter, and composite indexes lead with it so tenant filters are index prefixes, not afterthoughts. The workspace abstraction is kept virtual — getCurrentWorkspace() returns the user's implicit personal workspace — with a documented future migration (add workspaces + membership, backfill from user_id) deferred until a multi-seat feature demands it.
Tradeoff: application-layer scoping means one forgotten where user_id = ? is a data leak — Postgres row-level security (RLS) would enforce it in the database at the cost of policy complexity and a harder local-dev story. The standing verification gate ("no table without user_id scoping") plus store-level encapsulation is the compensating control: raw queries live only inside store factories, never in pages or actions.
Application substates (Waitlisted, Ghosted, OA…) aren't columns — they're rows in the append-only application_events table, and "current substate" is just the latest substate_change event. History is free, and the schema never needs another nullable column per nuance. This is a gentle, pragmatic dose of event sourcing.
Apply it
You're adding an interview_notes table to Wera. Which columns does the codebase's own discipline force you to include?
A user double-clicks "Save" on the same discovered posting. What happens at the database level?
Wera decides to migrate from Clerk to Auth.js. How big is the database surgery?
Constraints as guarantees & the vendor seam
Two transferable ideas live in this module. First: make invalid states unrepresentable — a unique index is a guarantee the database enforces at 3 a.m. when your validation code is asleep. Second: the anti-corruption layer — wrap every vendor (auth, AI, storage) in an interface you own, so their concepts never colonize your domain model.
Best practices
- Encode business rules as constraints (unique, not-null, foreign keys) before writing defensive application code.
- Lead composite indexes with the tenant column — correctness and performance in one shape.
- Keep vendor IDs in a mapping table; domain tables reference only IDs you mint.
- Version your schema with migrations from commit one — history you can replay is history you can trust.
Common pitfalls
- Enforcing uniqueness only in code — two concurrent requests will eventually both pass the check.
- Using the auth vendor's user ID as your primary key — the cheapest decision that becomes the most expensive migration.
- Cascade deletes without knowing the blast radius — one
DELETE FROM usershere removes everything, which is the intent, but only because it was designed to be.
The primitive doing the heavy lifting in this module, from the authority itself.
↗ Docs Drizzle ORM documentationorm.drizzle.teamSchema-as-TypeScript, migrations, and the query builder used throughout @wera/db.
Context for the read-model pattern you'll meet in Module 6 — and when NOT to go full event-sourcing.
↗ Docs Clerk + Next.js quickstartclerk.comWhat Wera wrapped — read it and notice everything the seam deliberately hides from domain code.
↗