← back to Professional Directory
docs/phase-1-design.md
236 lines
# Phase 1 — Auth, users, schema (DESIGN, for Codex debate)
## Goal
Layer Google-OAuth-backed user accounts with 4 roles (guest, patient, doctor, admin) on top of the existing pd-api Express stack, plus the doctor-claim flow and Stripe subscription wiring. All routes that mutate or expose private data must be gated by role + tier middleware. Existing loopback admin dashboard continues to work for Steve via a synthetic admin session at boot, but every other surface becomes auth-aware.
## Files to create
| Path | Purpose |
|---|---|
| `db/migrations/003_users_auth.sql` | New tables (users, doctor_claims, subscriptions, sessions) + add `claim_status` columns to professionals/organizations |
| `agents/api-agent/auth.js` | Passport config (Google OAuth strategy + serialize/deserialize) + `requireRole()` / `requireTier()` middleware factory |
| `agents/api-agent/routes/auth.js` | `/auth/google`, `/auth/google/callback`, `/auth/logout`, `/whoami` |
| `agents/api-agent/routes/claims.js` | `POST /doctor-claims`, `GET /doctor-claims/:token` (email-verify), admin approve/reject |
| `agents/api-agent/routes/subscriptions.js` | `POST /subscriptions/checkout` (Stripe Checkout for doctor_pro/patient_plus plans), webhook handler |
| `db/migrations/003b_session_table.sql` | `connect-pg-simple` session table |
## Files to modify
| Path | Change |
|---|---|
| `agents/api-agent/server.js` | Mount `auth.js`, `claims.js`, `subscriptions.js`. Add `requireRole('admin')` middleware on every existing `/admin/*` route. Add session middleware ABOVE express.json. Inject `req.user` into existing endpoints. |
| `agents/api-agent/data_market.js` | Webhook handler currently only handles `checkout.session.completed`; extend to also handle `customer.subscription.{created,updated,deleted}` so user.tier reflects Stripe state. |
| `package.json` | Add `passport`, `passport-google-oauth20`, `express-session`, `connect-pg-simple`, `csurf` |
| `.env.example` | Document `GOOGLE_OAUTH_CLIENT_ID`, `GOOGLE_OAUTH_CLIENT_SECRET`, `SESSION_SECRET`, `STRIPE_SUBSCRIPTION_PRICE_DOCTOR_PRO`, `STRIPE_SUBSCRIPTION_PRICE_PATIENT_PLUS` |
## Schema (003_users_auth.sql)
```sql
-- ─── users ─────────────────────────────────────────────────────────────────
CREATE TABLE users (
id bigserial PRIMARY KEY,
email citext NOT NULL UNIQUE,
email_verified_at timestamptz,
google_sub text UNIQUE, -- Google OIDC subject; NULL until first OAuth
display_name text,
avatar_url text,
role text NOT NULL DEFAULT 'patient', -- guest|patient|doctor|admin
tier text NOT NULL DEFAULT 'free', -- free|paid
comments_disabled boolean NOT NULL DEFAULT false, -- patient/doctor can hide reviews on own profile
claimed_professional_id bigint REFERENCES professionals(id) ON DELETE SET NULL,
claimed_organization_id bigint REFERENCES organizations(id) ON DELETE SET NULL,
stripe_customer_id text UNIQUE,
last_login_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_users_role_tier ON users (role, tier);
CREATE INDEX idx_users_claimed_pro ON users (claimed_professional_id) WHERE claimed_professional_id IS NOT NULL;
-- ─── doctor_claims ─────────────────────────────────────────────────────────
-- A user wants to claim a professional or organization. Auto-approval if email
-- domain matches a known practice domain AND NPI lookup succeeds. Otherwise
-- queued for admin review.
CREATE TABLE doctor_claims (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
target_professional_id bigint REFERENCES professionals(id) ON DELETE CASCADE,
target_organization_id bigint REFERENCES organizations(id) ON DELETE CASCADE,
npi_provided varchar(10),
license_provided text,
email_verification_token text NOT NULL, -- emailed to practice domain
email_verified_at timestamptz,
status text NOT NULL DEFAULT 'pending', -- pending|approved|rejected|expired
status_reason text,
decided_at timestamptz,
decided_by_user_id bigint REFERENCES users(id),
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT either_target CHECK (
(target_professional_id IS NOT NULL AND target_organization_id IS NULL) OR
(target_professional_id IS NULL AND target_organization_id IS NOT NULL)
)
);
CREATE INDEX idx_claims_status ON doctor_claims (status);
CREATE UNIQUE INDEX idx_claims_one_pending_per_target_pro
ON doctor_claims (target_professional_id) WHERE status = 'pending' AND target_professional_id IS NOT NULL;
CREATE UNIQUE INDEX idx_claims_one_pending_per_target_org
ON doctor_claims (target_organization_id) WHERE status = 'pending' AND target_organization_id IS NOT NULL;
-- ─── subscriptions ─────────────────────────────────────────────────────────
CREATE TABLE subscriptions (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
stripe_subscription_id text NOT NULL UNIQUE,
stripe_price_id text NOT NULL,
plan text NOT NULL, -- doctor_pro_monthly | patient_plus_monthly
status text NOT NULL, -- active | past_due | canceled | unpaid | trialing
current_period_end timestamptz,
cancel_at_period_end boolean NOT NULL DEFAULT false,
started_at timestamptz NOT NULL DEFAULT now(),
ended_at timestamptz,
raw_json jsonb
);
CREATE INDEX idx_subs_user ON subscriptions (user_id);
CREATE INDEX idx_subs_status ON subscriptions (status);
-- ─── claim_status on existing tables ───────────────────────────────────────
ALTER TABLE professionals ADD COLUMN claim_status text NOT NULL DEFAULT 'unclaimed' CHECK (claim_status IN ('unclaimed','claimed','verified'));
ALTER TABLE organizations ADD COLUMN claim_status text NOT NULL DEFAULT 'unclaimed' CHECK (claim_status IN ('unclaimed','claimed','verified'));
CREATE INDEX idx_pros_claim ON professionals (claim_status);
CREATE INDEX idx_orgs_claim ON organizations (claim_status);
-- updated_at triggers
CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_set_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
```
## auth.js — passport + middleware
```js
const passport = require('passport');
const GoogleStrategy = require('passport-google-oauth20').Strategy;
const { query } = require('../shared/db');
passport.use(new GoogleStrategy({
clientID: process.env.GOOGLE_OAUTH_CLIENT_ID,
clientSecret: process.env.GOOGLE_OAUTH_CLIENT_SECRET,
callbackURL: process.env.GOOGLE_OAUTH_CALLBACK || '/auth/google/callback',
}, async (accessToken, refreshToken, profile, done) => {
try {
const email = (profile.emails?.[0]?.value || '').toLowerCase();
if (!email) return done(new Error('no email from Google'));
const r = await query(`
INSERT INTO users (email, google_sub, display_name, avatar_url, email_verified_at, last_login_at)
VALUES ($1, $2, $3, $4, now(), now())
ON CONFLICT (email) DO UPDATE SET
google_sub = COALESCE(users.google_sub, EXCLUDED.google_sub),
display_name = COALESCE(users.display_name, EXCLUDED.display_name),
avatar_url = COALESCE(users.avatar_url, EXCLUDED.avatar_url),
last_login_at = now()
RETURNING *
`, [email, profile.id, profile.displayName, profile.photos?.[0]?.value]);
done(null, r.rows[0]);
} catch (e) { done(e); }
}));
passport.serializeUser((u, cb) => cb(null, u.id));
passport.deserializeUser(async (id, cb) => {
const r = await query('SELECT * FROM users WHERE id = $1', [id]);
cb(null, r.rows[0] || null);
});
function requireRole(...roles) {
return (req, res, next) => {
if (!req.user) return res.status(401).json({ error: 'auth required' });
if (!roles.includes(req.user.role)) return res.status(403).json({ error: 'forbidden' });
next();
};
}
function requireTier(tier) {
return (req, res, next) => {
if (!req.user) return res.status(401).json({ error: 'auth required' });
if (req.user.tier !== tier && req.user.role !== 'admin') return res.status(402).json({ error: 'paid tier required' });
next();
};
}
module.exports = { passport, requireRole, requireTier };
```
## Doctor-claim flow
1. Authenticated user (any role) hits `POST /doctor-claims` with `{ target_professional_id, npi, license_number }` OR `{ target_organization_id, email_at_practice }`.
2. Server checks: NPI matches professionals row + license_number matches OR target organization exists + email domain matches the org's website domain.
3. Server creates `doctor_claims` row with status='pending', generates `email_verification_token`.
4. Sends verification email to user's auth email AND to a known contact email at the target (if any) via George gmail (`localhost:9850/api/send`). Email contains link `/doctor-claims/<token>`.
5. User clicks the link → server marks `email_verified_at`. If auto-approval gates pass (NPI match + email at practice domain), status flips to 'approved'; user.role bumps to 'doctor'; user.claimed_professional_id is set; professionals.claim_status flips to 'verified'.
6. Otherwise queued for admin manual review at `/admin/claims`.
## Stripe subscription wiring (extending data_market.js)
- Two products: `doctor_pro_monthly` (TBD price), `patient_plus_monthly` (TBD price). Created in Stripe via `stripe products create` once, IDs stored in `.env` as `STRIPE_PRICE_DOCTOR_PRO`, `STRIPE_PRICE_PATIENT_PLUS`.
- `POST /subscriptions/checkout` — accepts `{ plan }`, creates Stripe Checkout session, returns URL. Customer ID is reused if user.stripe_customer_id is set.
- Webhook handler now branches on event type:
- `checkout.session.completed` (existing data-market path stays) — for one-time CSV downloads.
- `customer.subscription.created/updated` — upsert into `subscriptions` table; if active, set `users.tier = 'paid'`.
- `customer.subscription.deleted` — set `users.tier = 'free'`.
- Webhook is signed; existing signature-verify logic stays.
## Synthetic admin session
Steve drives the loopback dashboard at `http://127.0.0.1:9874` without going through Google OAuth. Solution: a small middleware that, when `req.connection.remoteAddress === '127.0.0.1'` AND `process.env.ADMIN_LOOPBACK_TRUST === '1'`, injects a synthetic `req.user = { role: 'admin', email: 'steve@designerwallcoverings.com', id: 0 }`. This preserves Steve's existing workflow while remote callers (via pd-preview tunnel or the Next.js public site) have no such bypass.
## Verification (Phase 1 done = these all pass)
```bash
# 1. /auth/google redirects to Google
curl -sI http://127.0.0.1:9874/auth/google | grep -i location
# 2. /whoami returns null when unauthenticated
curl -s http://127.0.0.1:9874/whoami | jq .
# 3. /admin/* now requires admin role from non-loopback (test via Cloudflare tunnel later)
# 4. Doctor-claim flow end-to-end
psql -d doctor_professional_directory -c "SELECT COUNT(*) FROM doctor_claims;"
# 5. Stripe subscription mock-completed → user.tier flips
node scripts/mock-stripe-subscription-event.js
# 6. requireRole + requireTier behave correctly (unit tests in agents/api-agent/__tests__/auth.test.js)
```
## Risks / open questions for Codex debate
1. **Session storage** — express-session with PG store (connect-pg-simple) is the standard. Alternative: signed JWTs with no server-side session. JWTs are stateless but make role changes painful (e.g. role bump after claim approval requires user re-login OR aggressive token TTL). Default: PG sessions.
2. **CSRF** — express-session + traditional forms need CSRF tokens. The Next.js public site (Phase 2) uses fetch with same-origin credentials, so SameSite=Lax cookies cover most cases. Decide whether to add `csurf` now or in Phase 2.
3. **Multi-claim per professional** — should two users be able to claim the same NPI (e.g. doctor + their office manager)? Default: no, one approved claim per professional. Office-manager pattern handled by adding a `delegate_user_ids[]` column on professionals for paid doctor accounts.
4. **Soft-delete vs hard-delete on user account** — privacy requirements (CCPA) suggest hard-delete-on-request. Plan: `users.deleted_at` column + nightly purge of older-than-30d soft-deletes.
5. **Email domain matching for claims** — gmail/yahoo/outlook are common practice emails for solos. Auto-approve only when email domain matches the org's website domain; queue everything else.
6. **Testing strategy** — Phase 1 ships unit tests for middleware + an integration test that runs the OAuth callback against a Google sandbox tenant. CI is currently absent; Ralph subtasks include "set up GitHub Actions" if Steve wants. Default: skip CI for now, ship local jest tests.
## Subtask breakdown for Ralph (after Codex debate converges)
```
1. Migration 003_users_auth.sql + apply to local PG [no-deps]
2. Migration 003b_session_table.sql [no-deps]
3. Install passport + session deps + commit [deps: 1,2]
4. Create agents/api-agent/auth.js [deps: 3]
5. Create agents/api-agent/routes/auth.js [deps: 4]
6. Mount session middleware + auth routes in server.js [deps: 5]
7. Loopback synthetic-admin middleware [deps: 6]
8. Add requireRole on existing /admin/* routes [deps: 7]
9. Create routes/claims.js (submit/verify/approve) [deps: 6]
10. Wire George gmail for claim verification emails [deps: 9]
11. Stripe subscription endpoint + Checkout session [deps: 6]
12. Extend data_market.js webhook for sub events [deps: 11]
13. Unit tests: auth middleware, claim logic [deps: 9,11]
14. Integration test: OAuth roundtrip [deps: 5]
15. /whoami endpoint + smoke test [deps: 6]
```