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