experiments-database/memory.md

# Animal Experiments Database — Architecture Memory

## Project Overview
Full-stack application for managing animal experimental data.
- **Backend**: Node.js + Express + Prisma ORM
- **Frontend**: React (Vite) + TailwindCSS
- **Database**: PostgreSQL 15
- **Containerization**: Docker + Docker Compose
- **Main port**: 52867 (frontend), 3001 (backend API)

## Directory Structure
```
experiments-database/
├── backend/          # Node.js + Express + Prisma
│   ├── src/
│   │   ├── routes/   # Express route handlers
│   │   ├── middleware/ # Audit logging, error handling
│   │   └── prisma/   # Schema & migrations
│   ├── tests/        # Jest unit + integration tests
│   └── Dockerfile
├── frontend/         # React + Vite + TailwindCSS
│   ├── src/
│   │   ├── components/
│   │   ├── pages/
│   │   └── api/      # Axios API client
│   ├── tests/        # RTL unit tests
│   ├── e2e/          # Playwright E2E tests
│   └── Dockerfile
├── docker-compose.yml
└── memory.md
```

## Database Schema

### Experiments
| Column     | Type      | Notes             |
|------------|-----------|-------------------|
| id         | UUID      | PK, auto-generated |
| title      | TEXT      | NOT NULL           |
| created_at | TIMESTAMP | auto-set           |

### Animals
| Column           | Type | Notes                |
|------------------|------|----------------------|
| id               | UUID | PK, auto-generated   |
| experiment_id    | UUID | FK → Experiments.id  |
| animal_id_string | TEXT | Researcher's own ID  |
| animal_name      | TEXT |                      |

### Daily_Statuses
| Column                 | Type      | Notes               |
|------------------------|-----------|---------------------|
| id                     | UUID      | PK, auto-generated  |
| animal_id              | UUID      | FK → Animals.id     |
| date                   | DATE      | NOT NULL            |
| experiment_description | TEXT      |                     |
| vitals                 | TEXT      |                     |
| treatment              | TEXT      |                     |
| notes                  | TEXT      |                     |

### Audit_Logs
| Column     | Type      | Notes                      |
|------------|-----------|----------------------------|
| id         | UUID      | PK, auto-generated         |
| table_name | TEXT      | Which table was affected   |
| record_id  | UUID      | The affected record's id   |
| action     | TEXT      | CREATE / UPDATE / DELETE   |
| changes    | JSONB     | before/after snapshot      |
| timestamp  | TIMESTAMP | auto-set                   |

## API Routes

### Experiments
- `GET    /api/experiments`            — list all
- `POST   /api/experiments`            — create
- `GET    /api/experiments/:id`        — get one
- `PUT    /api/experiments/:id`        — update (audited)
- `DELETE /api/experiments/:id`        — delete (audited)

### Animals
- `GET    /api/animals?experimentId=`  — list for experiment
- `POST   /api/animals`               — create
- `GET    /api/animals/:id`           — get one
- `PUT    /api/animals/:id`           — update (audited)
- `DELETE /api/animals/:id`           — delete (audited)

### Daily Statuses
- `GET    /api/daily-statuses?animalId=` — list for animal
- `POST   /api/daily-statuses`           — create
- `GET    /api/daily-statuses/:id`       — get one
- `PUT    /api/daily-statuses/:id`       — update (audited)
- `DELETE /api/daily-statuses/:id`       — delete (audited)

### Audit Logs
- `GET    /api/audit-logs?tableName=&recordId=` — query logs

## Audit Middleware
All PUT and DELETE routes pass through `auditMiddleware` which:
1. Fetches the current record (before state)
2. Executes the route handler
3. Writes an `Audit_Log` entry with table_name, record_id, action, and changes (JSONB diff)

## Port Mapping
| Service   | Internal | External |
|-----------|----------|----------|
| Frontend  | 80       | 52867    |
| Backend   | 3001     | 3001     |
| PostgreSQL| 5432     | 5432     |

## Git Branch Strategy
- `main` — stable, tested code
- `feature/docker-init` — Docker & project scaffolding
- `feature/database-setup` — Prisma schema + migrations
- `feature/backend-api` — Express routes + audit middleware
- `feature/frontend` — React UI
- `feature/tests` — Jest + RTL + Playwright tests
- `feature/red-team-fixes` — Security & UX patches

## Testing Notes
- Backend tests use Jest + Supertest against a test PostgreSQL instance
- Frontend unit tests use React Testing Library
- E2E tests use Playwright against the full Docker stack
- All tests run inside Docker via `docker-compose.test.yml`

## Red Team Audit

### Findings

#### 🔴 Critical

1. **CORS wildcard (`origin: '*'`)** — The backend accepts requests from any origin. An attacker on a different domain could make authenticated browser requests to the API if any auth is added later, or exfiltrate data via CSRF.
   - **Fix**: Restrict `CORS_ORIGIN` to the frontend's origin in production.

2. **Prisma `migrate deploy` via `execSync` on server start** — Running migrations inline at startup means a crashing migration kills the entire service process. Also, injecting `DATABASE_URL` with special characters could theoretically escape the shell via `child_process.execSync`.
   - **Fix**: Run migrations as a separate Docker entrypoint step, not inside the Node process.

3. **Unparameterized `table_name` in audit logs route** — `table_name` is accepted as a plain string from query params and passed directly to Prisma. Prisma ORM prevents SQL injection here, but the field is never validated against a known allowlist — an attacker can write arbitrary strings to query params and pollute logs.
   - **Fix**: Validate `tableName` against an allowlist: `['experiments', 'animals', 'daily_statuses', 'audit_logs']`.

#### 🟡 Medium

4. **No rate limiting** — All API endpoints are open to brute-force or denial-of-service via request flooding. Express has no rate limiter middleware.
   - **Fix**: Add `express-rate-limit` on all `/api/*` routes.

5. **Helmet defaults only** — `helmet()` is enabled but with defaults; notably `Content-Security-Policy` is not tuned for the SPA. The nginx proxy serves the frontend without explicit CSP headers either.
   - **Fix**: Add a CSP header in nginx and strengthen helmet config.

6. **Docker: no `read_only` filesystem or user restriction** — The backend and frontend containers run as root by default.
   - **Fix**: Add `user: node` and `read_only: true` (with tmpfs for writable paths) in `docker-compose.yml`.

7. **Postgres port exposed on host** — `docker-compose.yml` exposes port 5432 externally. In production, only the backend container needs DB access.
   - **Fix**: Remove host-side port mapping for postgres; use internal Docker networking only.

8. **`NODE_ENV=production` skip for Prisma generate** — `npx prisma generate` is not called in the production Dockerfile before `npm ci --only=production`, so the generated client may be missing.
   - **Fix**: Run `prisma generate` in the builder stage.

#### 🟢 UX / Low

9. **Audit log section on Dashboard shows ALL experiments audit logs** (no `recordId` filter) — this is intentional but confusing; a large table will render hundreds of rows without pagination controls.
   - **Fix**: Add pagination controls to `AuditLogSection`.

10. **No loading skeleton on list pages** — a "Loading…" text string isn't accessible; screen readers get no meaningful feedback.
    - **Fix**: Add `aria-live="polite"` region for loading state.

11. **Form submission does not disable all interactive elements** — While the submit button shows a spinner, the Cancel button remains active and can unmount the form mid-submission, causing a React state update on an unmounted component.
    - **Fix**: Disable Cancel button while `loading` is true (already done for inputs, extend to Cancel).

### Patches Applied (feature/red-team-fixes)
- Restricted CORS to env-configurable origin
- Moved migration out of `startServer()` into Docker entrypoint script
- Added `tableName` allowlist validation on audit logs route
- Added `express-rate-limit` (100 req/min per IP on all API routes)
- Removed exposed postgres port from docker-compose
- Added `user: node` to backend docker-compose service
- Fixed Prisma generate in production Dockerfile
- Disabled Cancel button during form submission