The Database and Auth Foundation

Step 1: Design the database schema

The PRD defines what the system needs. Now you build the foundation -- the database schema and the authentication layer.

Open materials/templates/architecture-template.md. You'll use this to record your first architecture decision: managed auth vs custom auth.

Managed auth (Clerk, NextAuth.js) handles user registration, login, session management, and token storage for you. It's faster to implement, battle-tested against common attacks, and maintained by a dedicated team. The trade-off: vendor dependency, limited customisation for complex access patterns, and monthly cost that scales with users.

Custom auth means you build registration, login, password hashing, session management, and token handling yourself. Full control, no vendor lock-in, no per-user cost. The trade-off: you own every security decision, every bug, every vulnerability.

For Lucia's clinic -- fifteen staff, three roles, modest scale -- managed auth makes sense. But record the decision and the reasoning. Use the architecture template: what you chose, what you considered, why, and what consequences follow. This decision affects every auth-related component in the system.

Direct Claude to generate the database schema. Specify the constraints before Claude starts:

• All access control decisions are enforced on the API route, never on the client
• User passwords are hashed with bcrypt
• Roles are stored in the database, not hardcoded in code

The schema should include: a users table (email, password hash, role, clinic assignment), a patients table (personal info, clinic of record), a medical_records table (clinical notes, care plans, vitals -- linked to patient and provider), and an appointments table (patient, provider, clinic, date/time).

The role and permission structure can be a roles table with a permissions join table, or a simpler role column on the users table with middleware handling the permission logic. Either works at this scale. What matters is that the role is stored authoritatively in the database, not derived from a client-side variable.

Step 2: Understand password hashing

Before implementing login, one concept needs to be clear.

Open materials/guides/auth-guide.md and read the Password Hashing section.

Passwords must never be stored in plaintext. If the database is compromised -- and databases are compromised -- every plaintext password is immediately exposed. Bcrypt produces a one-way hash: a fixed-length string that cannot be reversed back to the original password. Each hash includes a built-in salt (random data added before hashing) so that two users with the same password produce different hashes.

The cost parameter controls how long hashing takes. A cost of 10 means 2^10 (1,024) iterations. A cost of 12 means 2^12 (4,096) iterations. The target: roughly 250ms on current hardware. Fast enough that login feels instant. Slow enough that brute-forcing millions of password guesses becomes impractical.

When a user logs in, bcrypt hashes the submitted password with the same salt and cost, then compares the result to the stored hash. If they match, the password is correct. At no point does the system know or store the actual password.

Step 3: Implement registration and login

Direct Claude to implement user registration and login. Give it the auth guide as context -- it covers the patterns you want followed. Specify the trust boundary explicitly:

Implement user registration and login for the patient portal. Use bcrypt for password hashing with a cost of 10-12. Sessions are managed with secure cookies -- httpOnly, secure, sameSite set to Lax. The session token is validated server-side on every protected route. Never store session data in localStorage.

That last sentence matters. AI commonly generates auth flows that store tokens in localStorage because it's simpler -- localStorage.setItem('token', jwt). But localStorage is accessible to any JavaScript running on the page. A cross-site scripting (XSS) vulnerability gives an attacker the token. HttpOnly cookies are invisible to JavaScript entirely -- the browser sends them automatically, and no script can read them.

Read the Sessions vs JWTs section in the auth guide. Sessions store state on the server -- the session ID in the cookie maps to user data in a database or memory store. JWTs store state in the token itself -- the user's identity and role are encoded in the token. Sessions are simpler to revoke (delete the server-side record). JWTs are harder to revoke (the token is self-contained until it expires). For Lucia's clinic with fifteen users, sessions are the simpler, safer choice. Record this as a second architecture decision.

After Claude generates the auth flow, test it manually. Register a user. Log in. Check the response -- you should receive a session cookie, not a token in the response body.

Step 4: Verify the auth implementation

This is the verification step. Three things to confirm.

Check the database. Query the users table and look at the password column. It should contain a bcrypt hash -- a string starting with $2b$ followed by the cost parameter and roughly 50 characters of random-looking text. If you see the actual password in plaintext, the hashing is broken.

Check the cookie. Open Chrome DevTools, go to the Application tab, and look under Cookies. Find the session cookie. Check its flags: httpOnly should be true (JavaScript cannot read it), secure should be true (only sent over HTTPS), and sameSite should be Lax or Strict. If httpOnly is false, the session is exposed to XSS.

Check logout. Log out and verify the cookie is cleared. Then try accessing a protected route -- you should be redirected to login, not shown stale data.

Step 5: Implement role assignment and handle Lucia's feedback

When a user registers, they need a role. Direct Claude to build role assignment with the constraint: role metadata lives in the session, not in the request body. The role comes from the database, gets written into the session during login, and is checked by the server on every protected request.

After the schema and auth foundation are working, share your progress with Lucia. She'll bring up two things.

First, the Jarabacoa connectivity issue. She mentions that the clinic loses internet multiple times per day, especially during rainy season. This is a hidden constraint surfacing -- she considers it "just how it is" and hadn't thought to mention it. Acknowledge it, decide how to scope it. You might note it as a future concern in the PRD and keep building, or you might design the data layer to handle it now. Either way, record the decision.

Second, Carlos is asking about reporting. "He wants to know how many patients we see per clinic per month." This is scope expansion. The board chair's requirement from the original email is now a concrete request. Decide how to handle it -- acknowledge it, add it to the backlog, or scope it for a later unit.