# 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._