# CrewSheet — Application Documentation

> Last updated: 2026-03-30 | Generated: 2026-04-04T11:20:06.286Z

## 1. App Overview

**CrewSheet** is a crew and project scheduling system built for [Punch Drunk Productions](https://punch-drunk.com), a live broadcast/production company. It provides a calendar-based interface for assigning crew and vendors to projects, managing day-specific notes, generating public share links, and logging activity.

- **Canonical URL:** https://crewsheet.punch-drunk.com
- **Tech Stack:** Express + TypeScript (backend), React + TypeScript + Vite (frontend), Drizzle ORM, PostgreSQL (Neon), TanStack Query, Tailwind CSS, shadcn/ui
- **Hosting:** Railway (auto-deploy from GitHub)
- **Node Version:** v22.22.2

---

## 2. Environment Variables

| Variable | Purpose | Status | Required | Notes |
|---|---|---|---|---|
| `DATABASE_URL` | PostgreSQL connection string (Neon) | ✅ configured | Yes | Railway injects automatically |
| `SESSION_SECRET` | Signs session cookies | ✅ configured | Yes | App will not start without this |
| `PUNCH_DRUNK_API_KEY` | PD Auth app identifier (server-side) | ✅ configured | Yes | Used for token validation |
| `PUNCH_DRUNK_REDIRECT_URI` | OAuth callback URL | ✅ configured | No | Override; defaults to canonical origin + /auth/callback |
| `VITE_PUNCH_DRUNK_API_KEY` | PD Auth app identifier (client-side) | ✅ configured | Yes | Used by landing page login button |
| `VITE_GOOGLE_MAPS_API_KEY` | Google Maps — server-side Places API proxy + Static Maps PDF export | ✅ configured | No | VITE_ prefix kept for backward compatibility; client no longer loads Google Maps JS |
| `GOOGLE_MAPS_API_KEY` | Unused — server reads VITE_GOOGLE_MAPS_API_KEY directly | ✅ configured | No | Reserved; not currently referenced |
| `GOOGLE_CALENDAR_SERVICE_KEY` | Google service account JSON key for Calendar sync | ✅ configured | No | Stringified JSON; enables one-way GCal sync |
| `NODE_ENV` | Environment mode | Value: `production` | No | Controls cookie secure flag, canonical origin |
| `DEV_BYPASS_EMAIL` | Auto-login email for development | Value: `(not set)` | No | Only works when NODE_ENV=development |
| `TWILIO_ACCOUNT_SID` | Twilio account SID for SMS messaging | ✅ configured | No | Required for availability messaging |
| `TWILIO_AUTH_TOKEN` | Twilio auth token | ✅ configured | No | Required for availability messaging |
| `TWILIO_MESSAGING_SERVICE_SID` | Twilio messaging service | ✅ configured | No | A2P 10DLC compliant |
| `TWILIO_PHONE_NUMBER` | Twilio sender phone number | ✅ configured | No | |
| `SENDGRID_API_KEY` | SendGrid API key for email | ✅ configured | No | Used for messaging email notifications |
| `MESSAGING_FROM_EMAIL` | Sender email address for messaging | ❌ missing | No | |
| `BACKUP_SECRET` | Secret for backup API endpoint | ✅ configured | No | Can also be set in app_settings via UI |
| `R2_ACCOUNT_ID` | Cloudflare R2 account ID | ✅ configured | No | Required for photo storage |
| `R2_ACCESS_KEY_ID` | Cloudflare R2 access key | ✅ configured | No | Required for photo storage |
| `R2_SECRET_ACCESS_KEY` | Cloudflare R2 secret key | ✅ configured | No | Required for photo storage |
| `R2_BUCKET_NAME` | Cloudflare R2 bucket name | ✅ configured | No | Default: pd-crewsheet |
| `DATABASE_PUBLIC_URL` | Public Postgres URL for build-time migrations | ✅ configured | No | Railway internal hostname unavailable during builds |

---

## 3. Authentication Flow

CrewSheet uses the **auth code exchange pattern** (not the simple session-on-redirect pattern). This is the same pattern used by the Uploader app (files.punch-drunk.com).

### Step-by-step flow:

1. **`GET /login`** — If not already authenticated, redirects to `https://auth.punch-drunk.com/authorize` with `client_id` and `redirect_uri` params. Redirect URI: `https://crewsheet.punch-drunk.com/auth/callback`

2. **`GET /auth/callback?token=...`** — PD Auth redirects back with a token. The server:
   - Validates the token via `POST https://auth.punch-drunk.com/api/apps/validate-token` (body: `{ token, apiKey }`)
   - Looks up the local user by `punchDrunkUserId`, falls back to email match
   - If no local user found, redirects to `/?auth_error=access_denied`
   - Updates the local user record with latest PD Auth profile data (displayName, avatar, username, lastLoginAt)
   - **Does NOT set any session fields here** — this is critical
   - Generates a cryptographically random one-time auth code (64 hex chars via `crypto.randomBytes(32)`)
   - Stores the auth code in an in-memory Map with user data and 60-second expiry
   - Redirects to `/?auth_code=<code>`

3. **Client-side detection** — The React app's `AppContent` component detects `auth_code` in the URL on mount:
   - Cleans the URL via `history.replaceState`
   - Sends `POST /api/auth/exchange` with `{ code }`
   - On success, sets user state and clears TanStack Query cache
   - Shows a loading spinner during the exchange (blocks authenticated rendering)

4. **`POST /api/auth/exchange`** — Looks up the pending auth code:
   - If invalid or expired, returns 401
   - Deletes the code from the map (single-use)
   - Sets session fields directly: userId, punchDrunkUserId, email, displayName, avatarUrl, role
   - Calls `session.save()` to persist to PostgreSQL
   - Manually sets the `crewsheet.sid` cookie via `res.cookie()` using `cookie-signature` (express-session + connect-pg-simple do not reliably emit Set-Cookie)
   - Returns user data as JSON on a **200 response**

5. **`GET /api/auth/me`** — Returns `{ authenticated: true, user: {...} }` or `{ authenticated: false }`. No-cache headers set. Used on page load/refresh to check session.

6. **`GET /logout`** — Destroys session, redirects to PD Auth logout with `redirect_to` param pointing back to the app.

7. **`POST /api/auth/logout`** — Alternative logout (JSON response, used by frontend).

### Why the auth code exchange pattern?

The standard session-on-redirect pattern (`session.regenerate()` + `res.redirect()`) caused a login loop because express-session + connect-pg-simple do not reliably emit the `Set-Cookie` header. The auth code exchange pattern ensures the cookie is set manually via `res.cookie()` on a normal 200 JSON response from a client-initiated POST request.

### Development bypass

When `NODE_ENV=development` and `DEV_BYPASS_EMAIL` is set, the server automatically creates an admin session for that email on the first request if no session exists. This skips the PD Auth redirect flow entirely for local development.

---

## 4. Session Configuration

| Setting | Value |
|---|---|
| Store | PostgreSQL via `connect-pg-simple` |
| Session table | `session` (default, created automatically) |
| `createTableIfMissing` | `false` |
| `resave` | `false` |
| `saveUninitialized` | `true` |
| Cookie name | `crewsheet.sid` (custom — avoids collision with PD Auth's `connect.sid` on `.punch-drunk.com`) |
| Cookie `secure` | `true` (based on NODE_ENV) |
| Cookie `httpOnly` | `true` |
| Cookie `sameSite` | `lax` |
| Cookie `path` | `/` (default) |
| Cookie `maxAge` | 30 days (2,592,000,000 ms) |
| Cookie `domain` | Not explicitly set (defaults to response domain) |
| `trust proxy` | `1` (set before session middleware) |
| `session.regenerate()` | **Not used** — connect-pg-simple does not reliably emit Set-Cookie after regenerate |
| `session.save()` | Called explicitly in `/api/auth/exchange` after setting session fields |
| Manual `res.cookie()` | Used in `/api/auth/exchange` to set the `crewsheet.sid` cookie via `cookie-signature`, bypassing express-session's unreliable cookie emission |

### Session fields after login:
- `userId` (UUID — local app user ID)
- `punchDrunkUserId` (integer — PD Auth user ID)
- `email`
- `displayName`
- `avatarUrl`
- `role` ("staff" or "admin")

---

## 5. Middleware Order

Middleware is registered in this exact order:

**In `server/index.ts` (before `registerRoutes()`):**

1. `compression()` — gzip/deflate response compression
2. Security headers — `X-Content-Type-Options: nosniff`, `X-Frame-Options: DENY`, `X-XSS-Protection: 1; mode=block`, `Referrer-Policy: strict-origin-when-cross-origin`
3. `express.json()` — JSON body parsing
4. `express.urlencoded()` — URL-encoded body parsing
5. Request logger — Logs API requests with timing

**In `registerRoutes()`:**

6. `app.set("trust proxy", 1)` — Trust first proxy (Railway/Cloudflare)
7. R2 photo proxy route (`/uploads/location-photos/:filename`)
8. `express-session` — Session middleware with PostgreSQL store
9. Dev bypass middleware (development only) — Auto-login for DEV_BYPASS_EMAIL
10. Route handlers (auth routes, API routes) — registered inline
11. Global error handler — catches unhandled errors, returns JSON

**Rate limiting:**
- `POST /api/auth/exchange` — 10 requests per 15 minutes per IP (via `express-rate-limit`)

There is no explicit CORS middleware.

---

## 6. Route Protection

### Middleware functions:

- **`requireAuth`** — Checks `session.userId`. Returns `401 { message: "Not authenticated" }` if missing.
- **`requireAdmin`** — Checks `session.userId` AND `session.role === "admin"`. Returns 401 or `403 { message: "Admin access required" }`.

### Public routes (no auth required):
- `GET /login` — Redirects to PD Auth
- `GET /auth/callback` — OAuth callback
- `POST /api/auth/exchange` — Auth code exchange (rate limited: 10/15min per IP)
- `GET /api/auth/me` — Session check
- `POST /api/auth/logout` — Logout
- `GET /logout` — Logout with redirect
- `GET /api/landing-settings` — Landing page config (filtered to allowed keys)
- `GET /api/share/:token` — Public share link viewer
- `POST /api/admin/bootstrap` — Initial admin setup (gated by optional secret header)
- `GET /api/app-docs` — This documentation endpoint

### Authenticated routes (`requireAuth`):
All `/api/statuses`, `/api/project-statuses`, `/api/crew-roles`, `/api/crew`, `/api/vendor-types`, `/api/vendors`, `/api/location-types`, `/api/locations`, `/api/projects`, `/api/notes`, `/api/share` (list/create/update/delete), `/api/activity-logs`, `/api/conflicts-data`, `/api/conflict-resolutions`, `/api/activity-types`, `/api/activities`, `/api/clients`, `/api/regions`, `/api/crew-vendor-links`, `/api/resource-notes`, `/api/project-tasks`, `/api/calendar/people`, `/api/locations/:id/photos`
- `POST /api/feedback` — Submit feedback (category, description, optional screenshot, pageUrl)
- `GET /api/trash/:type` — List soft-deleted items (projects, crew, vendors, locations)
- `POST /api/trash/:type/:id/restore` — Restore soft-deleted items

### Admin-only routes (`requireAdmin`):
- `GET /api/auth/lookup-email`
- `GET/POST/PATCH/DELETE /api/admin/users`
- `GET/PATCH /api/admin/settings`
- `GET /api/feedback` — List all feedback with user info
- `PATCH /api/feedback/:id` — Update feedback status/admin notes
- `DELETE /api/feedback/:id` — Delete feedback
- `DELETE /api/trash/:type/:id` — Permanently delete soft-deleted items
- `POST /api/trash/cleanup` — Cleanup items deleted >30 days ago

---

## 7. Database

- **Type:** PostgreSQL (hosted on Neon)
- **ORM:** Drizzle ORM with `drizzle-orm/pg-core`
- **Connection:** Via `DATABASE_URL` env var, pooled with `max: 10`, connection/idle timeouts configured
- **Schema sync:** `drizzle-kit generate` + `drizzle-kit migrate` — migration SQL files tracked in `migrations/`; Railway runs migrate during builds

### Tables:

| Table | Purpose |
|---|---|
| `crew_roles` | Global list of crew roles (e.g., Camera Op, Director) |
| `crews` | Crew member profiles (name, phone, email, pronouns, vehicle, dietary, howWeMet, starred, soft-delete via deletedAt) |
| `crew_member_roles` | Many-to-many: crew ↔ roles |
| `crew_regions` | Many-to-many: crew ↔ regions |
| `vendor_types` | Categories for vendors |
| `vendors` | Vendor/company profiles (name, contact info, address, starred, soft-delete) |
| `vendor_member_types` | Many-to-many: vendor ↔ types |
| `vendor_regions` | Many-to-many: vendor ↔ regions |
| `crew_vendor_links` | Junction table linking crew members to vendor companies with optional role text |
| `location_types` | Categories for locations |
| `locations` | Location profiles (name, address, lat/lng, notes, starred, soft-delete) |
| `location_member_types` | Many-to-many: location ↔ types |
| `location_photos` | Photos per location (filename, description, photoDate from EXIF, dimensions, sizeBytes) |
| `clients` | Client/company names assignable to projects |
| `regions` | Geographic regions assignable to crew and vendors |
| `statuses` | Assignment statuses (e.g., Confirmed, Tentative) with icon, color, sort order |
| `project_statuses` | Project-level statuses with icon, color, sort order |
| `projects` | Projects with date ranges, colors, notes, client, location, soft-delete, gcalEventIds (JSONB map of date→GCal event ID) |
| `project_crew` | Crew assignments to projects (with status, notes, sort order) |
| `project_vendor` | Vendor assignments to projects (with status, notes, sort order) |
| `project_tasks` | Todo checklist items per project (text, completed, author, completer, sortOrder) |
| `assignment_day_notes` | Per-day notes for crew/vendor assignments (isScheduled flag for day-level scheduling) |
| `activity_types` | Global activity name suggestions (used as autocomplete source) |
| `activities` | Per-project activities with free-text name, times, milestones, system Start/End markers |
| `activity_participants` | Many-to-many: activities ↔ crew/vendors |
| `resource_notes` | Per-resource activity log/notes (crew, vendor, location, project) with author tracking |
| `public_share_links` | Token-based public share links for projects, crew, vendors |
| `activity_logs` | User action audit trail |
| `conflict_resolutions` | Resolved scheduling conflicts |
| `app_users` | Local user registry linked to PD Auth (email, role, punchDrunkUserId, avatarUrl) |
| `app_settings` | Key-value store for app configuration. GCal keys: `gcal_calendar_id`, `gcal_timezone`, `gcal_allowed_statuses` (JSON array), `gcal_description_fields` (JSON object), `gcal_last_sync_at`, `gcal_last_sync_result` (JSON) |
| `feedback` | User-submitted feedback with category, description, optional screenshot, status, admin notes |
| `call_sheet_config` | Per-project call sheet configuration (parking, meeting location, meal info, attire, weather, section visibility toggles) |
| `call_sheet_day_overrides` | Per-day overrides for call sheet fields |
| `call_sheet_contacts` | Custom contacts displayed on call sheets |
| `messages` | Availability request messages (Twilio SMS integration) |
| `message_recipients` | Per-message recipient tracking with status and response |
| `message_templates` | Reusable message templates |
| `project_day_info` | Per-day metadata: custom day labels and day titles |
| `schedule_columns` | Day Grid categories per project (optional crew/vendor FK, sort order) |
| `schedule_column_day_visibility` | Per-column per-day visibility toggles |
| `session` | Express sessions (managed by connect-pg-simple, NOT Drizzle) |

---

## 8. Core Features

### Calendar System
- **Views:** Month, 2-Weeks, Week, Day, Year (horizontal scrolling timeline)
- **Modes:** Projects (project-centric blocks) and People (person-centric blocks)
- **Year View:** Projects mode shows project bars spanning months; People mode shows person blocks with project assignments listed inside
- **Drag-and-drop:** Create projects by dragging across day cells; drag assignments between projects
- **Conflict detection:** Client-side system identifies double-booked crew/vendors across overlapping projects

### Unified Roster
- Per-project assignment table combining crew and vendors
- Status management with configurable icons and colors
- Day-specific notes grid for per-day assignment details
- Drag-and-drop reorder via grip handles

### Location Photo Gallery
- Upload, view, and manage photos per location
- Images auto-resized via sharp (2000px max, JPEG 85% quality)
- EXIF date auto-extraction via `exif-reader` (static import with diagnostic logging)
- Pointer-event-based drag reorder with grip handles
- Lightbox with keyboard navigation, inline description editing, date editing (pencil icon for existing dates, visible picker for no-EXIF photos)

### Activity Scheduling
- Granular activity management within projects via tabbed interface (Schedule, Day Sheet, Call Sheet)
- Free-text activity name with autocomplete suggestions from activity_types table
- Time-scale grids, milestones, duration blocks, automatic Start/End markers
- Enter key in activity name input triggers save

### Advanced Day Planner (Day Grid)
- Third schedule view mode: time × category column grid
- General virtual column for unassigned activities (not a DB row)
- Auto-populated vendor columns from project roster; custom categories with cross-project suggestions
- Click-to-create, drag-to-create, drag-to-move, resize handles, shift+click multi-select with group drag
- Per-day category visibility toggles; drag-to-reorder column headers
- System markers (Start/End) locked to General column
- Milestone ↔ duration conversion via drag in all views

### Call Sheets
- Per-project call sheet builder with configurable sections
- Day-specific field overrides
- Custom contacts list
- PDF export with GCal sync integration

### Availability Messaging
- SMS availability requests via Twilio A2P
- Message templates for reuse
- Per-recipient delivery and response tracking

### Project Action Items
- Todo checklist per project with add-via-Enter, checkbox toggle, author/completer tracking
- Collapsible completed section with timestamps

### Export System
- Single-item exports: Markdown, PDF
- Bulk exports: CSV, XLSX, JSON, Markdown (zipped), PDF
- All fields exported with "—" for blanks

### Trash / Recovery System
- Soft-deleted items viewable in Settings > Trash tab
- Restore or permanently delete (admin-only for permanent deletion)
- Auto-cleanup of items deleted >30 days on server startup
- Cascade deletion of all junction records on permanent delete

### In-App Feedback
- Floating feedback button with optional screenshot capture
- Admin management via Settings with status tracking and admin notes

### Public Share Links
- Token-based unauthenticated access for project, crew, or vendor details
- Configurable per-entity via Settings > Sharing

### Resource Notes / Activity Log
- Per-resource notes on crew, vendor, location, and project detail pages
- Author-owned with admin override for edit/delete

---

## 9. External Services

| Service | Purpose | Env Vars |
|---|---|---|
| **Punch Drunk Auth** (`auth.punch-drunk.com`) | OAuth authentication provider | `PUNCH_DRUNK_API_KEY`, `PUNCH_DRUNK_REDIRECT_URI`, `VITE_PUNCH_DRUNK_API_KEY` |
| **Neon (PostgreSQL)** | Database hosting | `DATABASE_URL` |
| **Google Maps Platform** | Server-proxied address autocomplete (`/api/places/autocomplete`, `/api/places/details`) + Static Maps in PDF export | `VITE_GOOGLE_MAPS_API_KEY` |
| **Google Calendar API** | One-way sync of projects as timed events via service account | `GOOGLE_CALENDAR_SERVICE_KEY` |
| **Basecamp (via Zapier)** | Feedback widget sends cards via Zapier webhook | (webhook URL stored in `app_settings`) |
| **Twilio** | A2P SMS for availability messaging | `TWILIO_ACCOUNT_SID`, `TWILIO_AUTH_TOKEN`, `TWILIO_MESSAGING_SERVICE_SID`, `TWILIO_PHONE_NUMBER` |
| **SendGrid** | Email notifications for messaging | `SENDGRID_API_KEY`, `MESSAGING_FROM_EMAIL` |
| **Cloudflare R2** | Object storage for location photos | `R2_ACCOUNT_ID`, `R2_ACCESS_KEY_ID`, `R2_SECRET_ACCESS_KEY`, `R2_BUCKET_NAME` |

---

## 10. Known Issues & Workarounds

### Auth code exchange pattern (workaround for login loop)
- **Problem:** The standard PD Auth integration uses `session.regenerate()` + `res.redirect()` to establish the session. With redirect-based flows, the `Set-Cookie` header was not reliably emitted by browsers (especially mobile), causing an infinite login loop.
- **Workaround:** Adopted the auth code exchange pattern (same as the Uploader app). The callback generates a one-time code and redirects to `/?auth_code=...`. The client exchanges the code via a POST.
- **Cookie emission:** express-session + connect-pg-simple do not reliably emit `Set-Cookie` headers (neither via `saveUninitialized`, `onHeaders`, nor `session.regenerate()`). The exchange handler manually sets the `crewsheet.sid` cookie via `cookie-signature` + `res.cookie()`.
- **Frontend safety net:** After the exchange, the client verifies the session via `GET /api/auth/me` (with retries) before rendering the authenticated app, ensuring the cookie was properly stored.

### Cookie name: crewsheet.sid (not connect.sid)
- **Problem:** PD Auth at `auth.punch-drunk.com` sets its own `connect.sid` cookie scoped to `.punch-drunk.com`. When the browser makes requests to `crewsheet.punch-drunk.com`, it sends BOTH `connect.sid` cookies (PD Auth's and CrewSheet's). Express picks the wrong one — the PD Auth session, which has no userId — so `/api/auth/me` returns `authenticated: false`.
- **Fix:** CrewSheet uses `name: "crewsheet.sid"` in the session middleware config and in the manual `res.cookie()` call. This avoids any collision with PD Auth's cookie.

> **Critical — requirement for ALL PD Auth apps:** Session cookie name must be unique per app. PD Auth sets its own `connect.sid` cookie on the parent `.punch-drunk.com` domain. Any app on a subdomain of `punch-drunk.com` **MUST** use a custom session cookie name (e.g., `"crewsheet.sid"`, `"uploader.sid"`) in both the session middleware config and any manual `res.cookie()` calls. Using the default `"connect.sid"` will cause the app to read PD Auth's session instead of its own, resulting in authentication failures.

### saveUninitialized set to true
- **Reason:** Keeps session rows created for all visitors. While not strictly necessary for cookie emission (manual `res.cookie()` handles that), it ensures the session store is populated before the manual cookie references the session ID.
- **Trade-off:** Anonymous visitors will create session rows in PostgreSQL. For a small internal app, this is acceptable. Expired sessions are pruned automatically by `connect-pg-simple`.

### Pending auth tokens stored in-memory
- **Limitation:** Auth codes are stored in a JavaScript `Map`, not in the database. If the server restarts between the redirect and the exchange (within the 60-second window), the auth code is lost and login fails.
- **Mitigation:** 60-second expiry is short, server restarts are infrequent, and the user can simply retry login. For a single-instance Railway deployment, this is acceptable.

### Photo uploads stored in Cloudflare R2
- Photos are stored in Cloudflare R2 object storage (bucket: pd-crewsheet) and proxied through the server at `/uploads/location-photos/`.
- R2 credentials are configured via environment variables (`R2_ACCOUNT_ID`, `R2_ACCESS_KEY_ID`, `R2_SECRET_ACCESS_KEY`, `R2_BUCKET_NAME`).
- Photos persist across server restarts and container recycling.

---

## 11. Build & Deploy

- **Entry point:** `server/index.ts` (runs Express + Vite dev server in development)
- **Dev command:** `npm run dev` → `NODE_ENV=development tsx server/index.ts`
- **Build command:** `npm run build && DATABASE_URL=$DATABASE_PUBLIC_URL npm run db:migrate`
- **Production run:** `node dist/index.cjs`
- **Production:** Deployed via Railway (auto-deploy from GitHub `main`). `NODE_ENV=production` is set automatically.
- **Port:** 5000 (frontend and backend served on the same port via Vite middleware in dev, static serving in production)
- **Startup tasks:**
  - Database migration via `drizzle-kit migrate` (runs in build step)
  - Seed data for default statuses, project statuses, and roles (runs once, skips if data exists)
  - Activity name migration (backfills `name` from `activityTypeId`, idempotent)
  - Client migration (backfills client field from legacy project data, idempotent)
  - Start/End marker creation for projects missing them
  - Trash auto-cleanup of items deleted >30 days
  - Session table created automatically by `connect-pg-simple` if missing
- **Background jobs:**
  - Expired auth token cleanup every 60 seconds

---

## 12. Admin User Management

### Adding a user

The admin enters **only an email address** and selects a role (staff or admin). No Punch Drunk User ID or display name is required.

- **`POST /api/admin/users`** — Body: `{ "email": "user@example.com", "role": "staff" }`
  - `punchDrunkUserId` is set to `0` (sentinel for "not yet linked")
  - `displayName` is derived from the email prefix: `jane.smith@example.com` → `Jane Smith`
  - `username` is set to the email address (lowercased)
  - Returns the created user object
  - Returns `409` if the email already exists

### Auto-linking on first login

When a user whose `punchDrunkUserId === 0` logs in via PD Auth:

1. The auth callback validates their PD Auth token (which returns `userId`, `email`, `displayName`, `avatar`, etc.)
2. The callback looks up the local user by email (case-sensitive match)
3. If found with `punchDrunkUserId === 0`, it auto-links by updating `punchDrunkUserId` to the PD Auth `userId`
4. The callback then updates `displayName`, `avatarUrl`, `username`, and `lastLoginAt` from the PD Auth profile
5. On subsequent logins, the user is matched by `punchDrunkUserId` directly

This means the display name shown in CrewSheet is always the one set in PD Auth — the email-derived name is only a placeholder until first login.

### Access gating

A person can authenticate with PD Auth, but they are **denied access** to CrewSheet unless they have a local `app_users` record. The auth callback returns `auth_error=access_denied` if no matching user is found.

### User management endpoints

| Endpoint | Method | Auth | Body / Params | Response |
|---|---|---|---|---|
| `/api/admin/users` | GET | Admin | — | Array of all users |
| `/api/admin/users` | POST | Admin | `{ email, role }` | Created user object |
| `/api/admin/users/:id` | PATCH | Admin | `{ role?, displayName? }` | Updated user object |
| `/api/admin/users/:id` | DELETE | Admin | — | `{ success: true }` (cannot delete self) |
| `/api/auth/lookup-email` | GET | Admin | `?email=...` | `{ found, alreadyRegistered, user? }` |
| `/api/admin/gcal/status` | GET | Admin | — | GCal connection status, settings, last sync info |
| `/api/admin/gcal/settings` | PATCH | Admin | `{ calendarId?, timezone?, allowedStatuses?, descriptionFields? }` | `{ success: true }` |
| `/api/admin/gcal/test` | POST | Admin | `{ calendarId }` | `{ success, calendarName?, error? }` |
| `/api/admin/gcal/sync` | POST | Admin | — | `{ synced, errors }` — full sync of all projects |
| `/api/admin/basecamp/status` | GET | Admin | — | Webhook config status |
| `/api/admin/basecamp/webhook` | POST | Admin | `{ url }` | Save webhook URL |
| `/api/admin/basecamp/disconnect` | POST | Admin | — | Clear webhook URL |
| `/api/admin/basecamp/test` | POST | Admin | — | Send test card via webhook |

### Bootstrap (first admin)

`POST /api/admin/bootstrap` creates the first admin user when **no users exist** in the system. Requires `punchDrunkUserId` and `email` in the body. If a `BOOTSTRAP_SECRET` env var is set, the request must include an `x-bootstrap-secret` header. Returns `403` if users already exist.

---

## 13. Setting Up Punch Drunk Auth in a New App

This section provides a guide for integrating Punch Drunk Auth (`auth.punch-drunk.com`) into any new Express/Node.js app. These lessons were learned the hard way during CrewSheet development.

### Prerequisites

1. Register your app at `auth.punch-drunk.com` to get a `PUNCH_DRUNK_API_KEY`
2. Set the allowed redirect URI in PD Auth to `https://your-app.punch-drunk.com/auth/callback`

### Critical Rules

#### 1. Use a custom session cookie name

```js
app.use(session({
  name: "yourapp.sid",  // NOT "connect.sid"
  // ... rest of config
}));
```

PD Auth sets `connect.sid` on `.punch-drunk.com`. If your app also uses `connect.sid`, Express will read PD Auth's session instead of yours. This causes infinite login loops that are extremely hard to diagnose.

#### 2. Never use session.regenerate()

`connect-pg-simple` (and possibly other stores) does not reliably emit `Set-Cookie` after `session.regenerate()`. The browser never gets the new session ID, causing a login loop.

#### 3. Use the auth code exchange pattern

Instead of setting session fields in the OAuth callback and redirecting:

```
WRONG: callback -> set session -> redirect to /
RIGHT: callback -> generate code -> redirect to /?auth_code=X
       client -> POST /api/auth/exchange {code} -> set session + manual cookie -> 200 JSON
```

The key insight: browsers reliably store cookies from JSON responses to client-initiated POST requests, but NOT reliably from redirect responses.

#### 4. Manually set the session cookie

After `session.save()`, manually emit the cookie:

```js
const cookieSignature = require("cookie-signature");

req.session.save((err) => {
  const signed = cookieSignature.sign(req.sessionID, SESSION_SECRET);
  res.cookie("yourapp.sid", "s:" + signed, {
    httpOnly: true,
    secure: true,
    sameSite: "lax",
    maxAge: 30 * 24 * 60 * 60 * 1000,
    path: "/",
  });
  res.json({ user: userData });
});
```

#### 5. Set trust proxy before session middleware

```js
app.set("trust proxy", 1);
// THEN register session middleware
```

Without this, Express sees `req.secure === false` behind a reverse proxy (Railway, Cloudflare, etc.) and won't set `secure` cookies.

#### 6. Use saveUninitialized: true

This ensures the session row exists in PostgreSQL before you reference the session ID in the manual cookie. Without it, the cookie points to a nonexistent session.

#### 7. Token validation endpoint

PD Auth validates tokens at:

```
POST https://auth.punch-drunk.com/api/apps/validate-token
Body: { "token": "...", "apiKey": "YOUR_PUNCH_DRUNK_API_KEY" }
Response: { "valid": true, "user": { userId, email, displayName, username, avatar, ... } }
```

#### 8. Logout flow

Destroy the local session, then redirect to PD Auth's logout endpoint:

```js
req.session.destroy(() => {
  const redirectTo = encodeURIComponent("https://your-app.punch-drunk.com");
  res.redirect(`https://auth.punch-drunk.com/logout?redirect_to=${redirectTo}`);
});
```

#### 9. Rate limit the exchange endpoint

The auth code exchange endpoint should be rate-limited to prevent brute-force attacks:

```js
const rateLimit = require("express-rate-limit");
const limiter = rateLimit({ windowMs: 15 * 60 * 1000, max: 10 });
app.post("/api/auth/exchange", limiter, handler);
```

### Quick Checklist for New PD Auth Apps

- [ ] Custom session cookie name (NOT `connect.sid`)
- [ ] `trust proxy` set to 1 before session middleware
- [ ] `saveUninitialized: true` in session config
- [ ] Auth code exchange pattern (not direct session-on-redirect)
- [ ] Manual `res.cookie()` with `cookie-signature` after `session.save()`
- [ ] No `session.regenerate()` calls anywhere
- [ ] Rate limiting on the exchange endpoint
- [ ] Local user table with access gating (deny users without a local record)
- [ ] Frontend detects `auth_code` in URL, cleans it, and exchanges via POST

---

## 14. Frontend Architecture

### Routing
- **Router:** wouter (lightweight React router)
- **Pages:** Calendar (/calendar), Projects (/projects, /projects/:id), Crew (/crew, /crew/:id), Vendors (/vendors, /vendors/:id), Locations (/locations, /locations/:id), Settings (/settings)
- **Landing page:** Animated background with PD Auth login button for unauthenticated users
- **Public share page:** /share/:token for unauthenticated content viewing

### Data Fetching
- **TanStack Query v5** with object-form queries (`useQuery({ queryKey: [...] })`)
- **Global `staleTime: Infinity`** — data never auto-refetches; cache must be explicitly invalidated after mutations via `queryClient.invalidateQueries()`
- **Hierarchical query keys:** `["/api/projects", id]` for proper cache invalidation

### Settings Page
- Vertical sidebar navigation (sticky, 190px) with tabs: Categories, Share Links, Display, Trash, Admin (admin-only), Docs (admin-only)
- Responsive fallback to horizontal tabs on mobile

---

## 15. Backup & Restore System

Two independent backup systems run on Google infrastructure via Google Apps Script. Both are fully automated and continue running even if CrewSheet is offline.

### Google Calendar Backup

| Property | Detail |
|---|---|
| **Frequency** | Daily at 2 AM |
| **Outputs** | JSON (restorable), Google Sheet (human-readable), PDF (printable) |
| **Storage** | Google Drive folder: "CrewSheet Calendar Backups" |
| **Retention** | 60 days |
| **Restore** | Run `restoreFromBackup` in Apps Script, or use CrewSheet Sync All if database is intact |

### Database Backup

| Property | Detail |
|---|---|
| **Frequency** | Weekly on Mondays at 3 AM |
| **Endpoint** | `GET /api/admin/backup?secret=BACKUP_SECRET` |
| **Outputs** | JSON (importable into CrewSheet), XLSX (human-readable), PDF (printable) |
| **Storage** | Google Drive folder: "CrewSheet Database Backups" |
| **Retention** | 90 days |

**Contents:** Projects (with crew, vendors, activities, day notes, log, tasks), Crew (with roles, regions), Vendors (with types, regions), Locations (metadata only, no photo files), Settings (statuses, roles, types, regions).

**Excludes:** Location photo files (on R2), user accounts/sessions, app settings, feedback.

### Import/Restore Endpoint

```
POST /api/admin/import (admin auth required)
Body: { data: {...}, options: { dryRun: true|false } }
```

Restores data from the importable JSON backup. Supports dry-run mode to preview changes without writing.

### Secret Management

- **Location:** Settings > Export > Backup API
- Admin can view, copy, and regenerate the backup secret without code access
- Falls back to `BACKUP_SECRET` env var if not set in app settings

### Apps Script

- **Location:** [script.google.com](https://script.google.com)
- **Files:** Code.gs (calendar backup), DatabaseBackup.gs (database backup)
- Both scripts run on Google infrastructure, independent of CrewSheet availability

### Emergency Restore Procedures

1. **Calendar events lost, database fine:** Settings > Google Calendar > Sync All
2. **Database lost:** Download importable JSON from Drive > Upload in Settings > Export > Database Restore > Dry Run > Restore > then Sync All for calendar
3. **Both lost:** Restore the database first (step 2), then Sync All for calendar

---

*Last updated: 2026-03-30 | Generated: 2026-04-04T11:20:06.286Z | Node v22.22.2 | ENV: production*