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

To be filled after Phase 3 testing is complete.