experiments-database/N8N_WORKFLOWS.md

Experiments Database — n8n Workflows

n8n is running at http://localhost:5678

The Postgres credential "Experiments DB (PostgreSQL)" (id: 9iJuyA9iR5KUzmj5) connects to experiments_postgres:5432 / experiments_db via the shared llm-net Docker network.

---

Workflow Index

ID	Workflow	Entity
jjmtecjuHJPKZ4T7	ExpDB · Experiments	experiments table
HYnlIscuKwMW7blG	ExpDB · Animals	animals table
9emayzMpnhIZQxqd	ExpDB · Daily Statuses	daily_statuses table
hso5z2W1uO44bTP6	ExpDB · Daily Analyses	daily_analyses table
2s02OX1yibfqp6jK	ExpDB · Session Files	session_files table
T76NAYiFcCxHDKQB	ExpDB · Audit Logs	audit_logs table
lq3R3YqeHgxixrbZ	ExpDB · Reports	cross-table queries

---

How to Call a Workflow

All workflows use an Execute Workflow Trigger — call them with an Execute Workflow node from any parent workflow. Pass a JSON object with action + the required fields.

Execute Workflow node
  → Workflow: <select workflow by name>
  → Input: { "action": "...", ...fields }

---

ExpDB · Experiments

list

Returns all experiments ordered by creation date.

{ "action": "list" }

get

{ "action": "get", "id": "<experiment_uuid>" }

create

{ "action": "create", "title": "My Experiment" }

Creates with empty template and subject_info_template ([]). UUID auto-generated.

update

{ "action": "update", "id": "<experiment_uuid>", "title": "New Title" }

delete

{ "action": "delete", "id": "<experiment_uuid>" }

Returns { id } of deleted row.

---

ExpDB · Animals

list

Returns all animals in an experiment, with daily_status_count.

{ "action": "list", "experiment_id": "<experiment_uuid>" }

get

Returns animal + experiment_title.

{ "action": "get", "id": "<animal_uuid>" }

create

{
  "action": "create",
  "experiment_id": "<experiment_uuid>",
  "animal_id_string": "M001",
  "animal_name": "Mouse 1",
  "subject_info": { "<fieldId>": "value" }
}

update

{
  "action": "update",
  "id": "<animal_uuid>",
  "animal_id_string": "M001",
  "animal_name": "Mouse 1",
  "subject_info": { "<fieldId>": "value" }
}

delete

{ "action": "delete", "id": "<animal_uuid>" }

Cascades to daily_statuses.

---

ExpDB · Daily Statuses

list

Returns statuses for an animal ordered by date descending.

{ "action": "list", "animal_id": "<animal_uuid>" }

get

Returns status + animal + experiment context.

{ "action": "get", "id": "<status_uuid>" }

create

{
  "action": "create",
  "animal_id": "<animal_uuid>",
  "date": "2025-04-25",
  "experiment_description": "...",
  "vitals": "...",
  "treatment": "...",
  "notes": "...",
  "custom_fields": { "<fieldId>": "value" }
}

All fields except animal_id and date are optional (default null).

update

{
  "action": "update",
  "id": "<status_uuid>",
  "experiment_description": "...",
  "vitals": "...",
  "treatment": "...",
  "notes": "...",
  "custom_fields": { "<fieldId>": "value" }
}

delete

{ "action": "delete", "id": "<status_uuid>" }

Cascades to daily_analyses and session_files.

---

ExpDB · Daily Analyses

list

Returns analysis headers (no heavy JSON blobs) for a daily status.

{ "action": "list", "daily_status_id": "<status_uuid>" }

get

Returns full analysis including sequence, category_counts, consecutive_stats, runs.

{ "action": "get", "id": "<analysis_uuid>" }

push_summary

Writes an analysis_summary blob back to a daily status (same as the "Save metrics" button in the UI).

{
  "action": "push_summary",
  "daily_status_id": "<status_uuid>",
  "analysis_summary": {
    "counts": { "Success": 12, "Failure": 3 },
    "total": 15,
    "success_rate": 0.8,
    "computed_at": "2025-04-25T10:00:00.000Z"
  }
}

---

ExpDB · Session Files

list

{ "action": "list", "daily_status_id": "<status_uuid>" }

get

{ "action": "get", "id": "<file_uuid>" }

---

ExpDB · Audit Logs

list_by_record

All changes to a specific record (e.g. one animal, one daily status).

{
  "action": "list_by_record",
  "table_name": "daily_statuses",
  "record_id": "<status_uuid>",
  "limit": 50
}

Valid table_name values: experiments, animals, daily_statuses.

list_by_table

Recent changes across an entire table.

{
  "action": "list_by_table",
  "table_name": "animals",
  "limit": 100
}

---

ExpDB · Reports

subjects_summary

Per-subject stats for an experiment: total days logged, last entry date, days with saved metrics.

{ "action": "subjects_summary", "experiment_id": "<experiment_uuid>" }

daily_summary

All days × subjects where analysis_summary exists — useful for CSV export or charting outside the app.

{ "action": "daily_summary", "experiment_id": "<experiment_uuid>" }

recent_activity

Latest audit log entries across all tables.

{ "action": "recent_activity", "limit": 50 }

---

Database Schema Reference

experiments
  id (uuid PK)  title  created_at  template (json)  subject_info_template (json)

animals
  id (uuid PK)  experiment_id (FK→experiments)
  animal_id_string  animal_name  subject_info (json?)

daily_statuses
  id (uuid PK)  animal_id (FK→animals)  date
  experiment_description?  vitals?  treatment?  notes?
  custom_fields (json?)  analysis_summary (json?)

daily_analyses
  id (uuid PK)  daily_status_id (FK→daily_statuses)  created_at
  file_name?  timestamp_col  note_col  total_note_rows  session_end_ts?
  classifications (json)  sequence (json)  category_counts (json)
  consecutive_stats (json)  runs (json)

session_files
  id (uuid PK)  daily_status_id (FK→daily_statuses)
  filename  file_size  file_type?  last_modified?
  matched_identifier?  animal_id_string_snap?  animal_name_snap?
  notes?  created_at

audit_logs
  id (uuid PK)  table_name  record_id  action  changes (json)  timestamp

custom_fields and subject_info are keyed by fieldId (UUID), matching the template / subject_info_template arrays on the experiment. Field keys can be renamed without data loss; only the UUID is used for storage.

---

Infrastructure Notes

• experiments_postgres is connected to both experiments-database_default and llm-net networks, making it reachable from n8n as hostname experiments_postgres.
• The n8n API key n8n_api_ca07bd874c628ed0f52d9a2e65073960f9f617c5 was created for the account quocsam93@gmail.com and is stored in the user_api_keys table of the n8n Postgres.
• Workflows were generated by n8n_create_workflows.py in this repo root.