Experiments DB Dev
8a18c894dd
Every fitlme-based report (lme_*, paper_*, phase_*, and the variations' analyze.m) now shows, per effect: residual-DF p, Satterthwaite-DF p, and -- for the interaction -- an HONEST test from a per-animal random-SLOPE model (day|rat), whose Satterthwaite DF collapses toward the animal count. New: tdcs_random_slope_interaction.m (shared helper). Wired into tdcs_lme, tdcs_paper_lme, tdcs_phase_lme, variation_analyze; SUMMARY.csv gains interaction_p_satt / interaction_p_rs. Regenerated all results/, variations/, matched-effort outputs. Key point this surfaces: Satterthwaite ~= residual on the random-INTERCEPT model (the slope's error is at session level), so it does NOT fix pseudoreplication; the random-slope model does. Effect: full-range mergeA2 interaction 0.009 -> 0.75 (collapses); unmerge_d0_5 0.015 -> 0.13 (n.s.); the pooled-control early windows survive honestly (naive_a2_d0_5 0.001 -> 0.028; naive_boxa_d0_5 0.003 -> 0.036). Suite 42/42. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
346 lines
17 KiB
Python
346 lines
17 KiB
Python
"""
|
|
Creates n8n workflow definitions for every entity in the experiments database.
|
|
Each workflow has an Execute Workflow Trigger + Switch (routes by `action` field)
|
|
+ one Postgres node per operation. They act as reusable sub-workflows.
|
|
"""
|
|
|
|
import json, uuid, requests
|
|
|
|
API = "http://localhost:5678/api/v1"
|
|
KEY = "n8n_api_ca07bd874c628ed0f52d9a2e65073960f9f617c5"
|
|
CRED = {"id": "9iJuyA9iR5KUzmj5", "name": "Experiments DB (PostgreSQL)"}
|
|
HEADS = {"X-N8N-API-KEY": KEY, "Content-Type": "application/json"}
|
|
|
|
def uid(): return str(uuid.uuid4())
|
|
|
|
# ── Node builders ──────────────────────────────────────────────────────────────
|
|
|
|
def trigger_node(x, y):
|
|
return {
|
|
"id": uid(), "name": "Trigger",
|
|
"type": "n8n-nodes-base.executeWorkflowTrigger",
|
|
"typeVersion": 1.1,
|
|
"position": [x, y],
|
|
"parameters": {},
|
|
}
|
|
|
|
def switch_node(name, actions, x, y):
|
|
"""Route by $json.action — one output per action string."""
|
|
rules = []
|
|
for action in actions:
|
|
rules.append({
|
|
"conditions": {
|
|
"options": {"caseSensitive": False, "leftValue": "", "typeValidation": "loose"},
|
|
"combinator": "and",
|
|
"conditions": [{
|
|
"leftValue": "={{ $json.action }}",
|
|
"rightValue": action,
|
|
"operator": {"type": "string", "operation": "equals"},
|
|
}],
|
|
},
|
|
"renameOutput": True,
|
|
"outputKey": action,
|
|
})
|
|
return {
|
|
"id": uid(), "name": name,
|
|
"type": "n8n-nodes-base.switch",
|
|
"typeVersion": 3.2,
|
|
"position": [x, y],
|
|
"parameters": {"rules": {"values": rules}, "options": {}},
|
|
}
|
|
|
|
def pg_node(name, query, params, x, y):
|
|
"""Postgres executeQuery node. params is a list of n8n expressions for $1,$2,..."""
|
|
node = {
|
|
"id": uid(), "name": name,
|
|
"type": "n8n-nodes-base.postgres",
|
|
"typeVersion": 2.5,
|
|
"position": [x, y],
|
|
"credentials": {"postgres": CRED},
|
|
"parameters": {
|
|
"operation": "executeQuery",
|
|
"query": query,
|
|
"options": {},
|
|
},
|
|
}
|
|
if params:
|
|
node["parameters"]["options"]["queryReplacement"] = "={{ [" + ", ".join(params) + "] }}"
|
|
return node
|
|
|
|
# ── Connection helpers ──────────────────────────────────────────────────────────
|
|
|
|
def conn(src, src_idx, dst):
|
|
"""Return a connections dict fragment."""
|
|
return (src["name"], src_idx, dst["name"])
|
|
|
|
def build_connections(pairs):
|
|
"""
|
|
pairs: list of (src_node, output_index, dst_node)
|
|
Returns n8n connections object.
|
|
"""
|
|
conns = {}
|
|
for src, idx, dst in pairs:
|
|
conns.setdefault(src, {"main": []})
|
|
while len(conns[src]["main"]) <= idx:
|
|
conns[src]["main"].append([])
|
|
conns[src]["main"][idx].append({"node": dst, "type": "main", "index": 0})
|
|
return conns
|
|
|
|
# ── Workflow factory ───────────────────────────────────────────────────────────
|
|
|
|
def make_workflow(name, trigger, switch, pg_nodes, connections):
|
|
return {
|
|
"name": name,
|
|
"nodes": [trigger, switch] + pg_nodes,
|
|
"connections": connections,
|
|
"settings": {"executionOrder": "v1"},
|
|
}
|
|
|
|
def post_workflow(wf):
|
|
r = requests.post(f"{API}/workflows", headers=HEADS, json=wf)
|
|
if r.ok:
|
|
d = r.json()
|
|
print(f" ✓ [{d['id']}] {wf['name']}")
|
|
else:
|
|
print(f" ✗ {wf['name']} → {r.status_code} {r.text[:200]}")
|
|
|
|
# ── Layouts ────────────────────────────────────────────────────────────────────
|
|
# Nodes stacked vertically, 220px apart; Switch at col 300, pg nodes at col 560
|
|
|
|
def pg_y(i): return i * 220 - 440 # center the stack
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 1. EXPERIMENTS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_experiments():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
|
|
ops = [
|
|
pg_node("List Experiments",
|
|
"SELECT id, title, created_at FROM experiments ORDER BY created_at DESC",
|
|
[], 560, pg_y(0)),
|
|
pg_node("Get Experiment",
|
|
"SELECT * FROM experiments WHERE id = $1",
|
|
["$json.id"], 560, pg_y(1)),
|
|
pg_node("Create Experiment",
|
|
"INSERT INTO experiments (id, title, template, subject_info_template) "
|
|
"VALUES (gen_random_uuid(), $1, '[]'::jsonb, '[]'::jsonb) RETURNING *",
|
|
["$json.title"], 560, pg_y(2)),
|
|
pg_node("Update Experiment",
|
|
"UPDATE experiments SET title = $1 WHERE id = $2 RETURNING *",
|
|
["$json.title", "$json.id"], 560, pg_y(3)),
|
|
pg_node("Delete Experiment",
|
|
"DELETE FROM experiments WHERE id = $1 RETURNING id",
|
|
["$json.id"], 560, pg_y(4)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Experiments", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 2. ANIMALS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_animals():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
|
|
ops = [
|
|
pg_node("List Animals",
|
|
"SELECT a.*, COUNT(ds.id)::int AS daily_status_count "
|
|
"FROM animals a "
|
|
"LEFT JOIN daily_statuses ds ON ds.animal_id = a.id "
|
|
"WHERE a.experiment_id = $1 "
|
|
"GROUP BY a.id ORDER BY a.animal_name",
|
|
["$json.experiment_id"], 560, pg_y(0)),
|
|
pg_node("Get Animal",
|
|
"SELECT a.*, e.title AS experiment_title "
|
|
"FROM animals a JOIN experiments e ON e.id = a.experiment_id "
|
|
"WHERE a.id = $1",
|
|
["$json.id"], 560, pg_y(1)),
|
|
pg_node("Create Animal",
|
|
"INSERT INTO animals (id, experiment_id, animal_id_string, animal_name, subject_info) "
|
|
"VALUES (gen_random_uuid(), $1, $2, $3, $4::jsonb) RETURNING *",
|
|
["$json.experiment_id", "$json.animal_id_string", "$json.animal_name",
|
|
"JSON.stringify($json.subject_info ?? {})"], 560, pg_y(2)),
|
|
pg_node("Update Animal",
|
|
"UPDATE animals SET animal_id_string=$1, animal_name=$2, subject_info=$3::jsonb "
|
|
"WHERE id=$4 RETURNING *",
|
|
["$json.animal_id_string", "$json.animal_name",
|
|
"JSON.stringify($json.subject_info ?? {})", "$json.id"], 560, pg_y(3)),
|
|
pg_node("Delete Animal",
|
|
"DELETE FROM animals WHERE id = $1 RETURNING id",
|
|
["$json.id"], 560, pg_y(4)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Animals", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 3. DAILY STATUSES
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_daily_statuses():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
|
|
ops = [
|
|
pg_node("List Daily Statuses",
|
|
"SELECT ds.id, ds.date, ds.experiment_description, ds.vitals, "
|
|
"ds.treatment, ds.notes, ds.custom_fields, ds.analysis_summary, "
|
|
"a.animal_name, a.animal_id_string "
|
|
"FROM daily_statuses ds JOIN animals a ON a.id = ds.animal_id "
|
|
"WHERE ds.animal_id = $1 ORDER BY ds.date DESC",
|
|
["$json.animal_id"], 560, pg_y(0)),
|
|
pg_node("Get Daily Status",
|
|
"SELECT ds.*, a.animal_name, a.animal_id_string, "
|
|
"a.experiment_id, e.title AS experiment_title "
|
|
"FROM daily_statuses ds "
|
|
"JOIN animals a ON a.id = ds.animal_id "
|
|
"JOIN experiments e ON e.id = a.experiment_id "
|
|
"WHERE ds.id = $1",
|
|
["$json.id"], 560, pg_y(1)),
|
|
pg_node("Create Daily Status",
|
|
"INSERT INTO daily_statuses "
|
|
"(id, animal_id, date, experiment_description, vitals, treatment, notes, custom_fields) "
|
|
"VALUES (gen_random_uuid(), $1, $2::date, $3, $4, $5, $6, $7::jsonb) RETURNING *",
|
|
["$json.animal_id", "$json.date", "$json.experiment_description ?? null",
|
|
"$json.vitals ?? null", "$json.treatment ?? null", "$json.notes ?? null",
|
|
"JSON.stringify($json.custom_fields ?? {})"], 560, pg_y(2)),
|
|
pg_node("Update Daily Status",
|
|
"UPDATE daily_statuses SET "
|
|
"experiment_description=$1, vitals=$2, treatment=$3, notes=$4, "
|
|
"custom_fields=$5::jsonb "
|
|
"WHERE id=$6 RETURNING *",
|
|
["$json.experiment_description ?? null", "$json.vitals ?? null",
|
|
"$json.treatment ?? null", "$json.notes ?? null",
|
|
"JSON.stringify($json.custom_fields ?? {})", "$json.id"], 560, pg_y(3)),
|
|
pg_node("Delete Daily Status",
|
|
"DELETE FROM daily_statuses WHERE id = $1 RETURNING id",
|
|
["$json.id"], 560, pg_y(4)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Daily Statuses", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 4. DAILY ANALYSES (read + summary-push)
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_daily_analyses():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list","get","push_summary"], 300, 0)
|
|
ops = [
|
|
pg_node("List Analyses",
|
|
"SELECT id, daily_status_id, created_at, file_name, "
|
|
"timestamp_col, note_col, total_note_rows, session_end_ts "
|
|
"FROM daily_analyses WHERE daily_status_id = $1 ORDER BY created_at DESC",
|
|
["$json.daily_status_id"], 560, pg_y(0)),
|
|
pg_node("Get Analysis",
|
|
"SELECT * FROM daily_analyses WHERE id = $1",
|
|
["$json.id"], 560, pg_y(1)),
|
|
pg_node("Push Analysis Summary",
|
|
"UPDATE daily_statuses SET analysis_summary = $1::jsonb WHERE id = $2 RETURNING id, analysis_summary",
|
|
["JSON.stringify($json.analysis_summary)", "$json.daily_status_id"],
|
|
560, pg_y(2)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Daily Analyses", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 5. SESSION FILES
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_session_files():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list","get"], 300, 0)
|
|
ops = [
|
|
pg_node("List Session Files",
|
|
"SELECT * FROM session_files WHERE daily_status_id = $1 ORDER BY created_at DESC",
|
|
["$json.daily_status_id"], 560, pg_y(0)),
|
|
pg_node("Get Session File",
|
|
"SELECT * FROM session_files WHERE id = $1",
|
|
["$json.id"], 560, pg_y(1)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Session Files", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# 6. AUDIT LOGS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_audit_logs():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["list_by_record","list_by_table"], 300, 0)
|
|
ops = [
|
|
pg_node("List Logs by Record",
|
|
"SELECT * FROM audit_logs "
|
|
"WHERE table_name = $1 AND record_id = $2 "
|
|
"ORDER BY timestamp DESC LIMIT $3",
|
|
["$json.table_name", "$json.record_id", "$json.limit ?? 50"],
|
|
560, pg_y(0)),
|
|
pg_node("List Logs by Table",
|
|
"SELECT * FROM audit_logs "
|
|
"WHERE table_name = $1 "
|
|
"ORDER BY timestamp DESC LIMIT $2",
|
|
["$json.table_name", "$json.limit ?? 100"],
|
|
560, pg_y(1)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Audit Logs", t, sw, ops, build_connections(pairs))
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# BONUS: Cross-subject report — one query to summarise all subjects in an experiment
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
def wf_reports():
|
|
t = trigger_node(60, 0)
|
|
sw = switch_node("Route", ["subjects_summary","daily_summary","recent_activity"], 300, 0)
|
|
ops = [
|
|
pg_node("Subjects Summary",
|
|
"SELECT a.id, a.animal_name, a.animal_id_string, a.subject_info, "
|
|
"COUNT(ds.id)::int AS total_days, "
|
|
"MAX(ds.date) AS last_entry, "
|
|
"COUNT(ds.analysis_summary)::int AS days_with_metrics "
|
|
"FROM animals a "
|
|
"LEFT JOIN daily_statuses ds ON ds.animal_id = a.id "
|
|
"WHERE a.experiment_id = $1 "
|
|
"GROUP BY a.id ORDER BY a.animal_name",
|
|
["$json.experiment_id"], 560, pg_y(0)),
|
|
pg_node("Daily Summary",
|
|
"SELECT ds.date, a.animal_name, "
|
|
"ds.analysis_summary->>'total' AS total_attempts, "
|
|
"ds.analysis_summary->>'success_rate' AS success_rate "
|
|
"FROM daily_statuses ds JOIN animals a ON a.id = ds.animal_id "
|
|
"WHERE a.experiment_id = $1 AND ds.analysis_summary IS NOT NULL "
|
|
"ORDER BY ds.date, a.animal_name",
|
|
["$json.experiment_id"], 560, pg_y(1)),
|
|
pg_node("Recent Activity",
|
|
"SELECT al.timestamp, al.table_name, al.action, al.record_id, al.changes "
|
|
"FROM audit_logs al "
|
|
"ORDER BY al.timestamp DESC LIMIT $1",
|
|
["$json.limit ?? 50"], 560, pg_y(2)),
|
|
]
|
|
pairs = [(t["name"], 0, sw["name"])] + \
|
|
[(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
|
|
return make_workflow("ExpDB · Reports", t, sw, ops, build_connections(pairs))
|
|
|
|
# ── Main ───────────────────────────────────────────────────────────────────────
|
|
|
|
if __name__ == "__main__":
|
|
workflows = [
|
|
wf_experiments(),
|
|
wf_animals(),
|
|
wf_daily_statuses(),
|
|
wf_daily_analyses(),
|
|
wf_session_files(),
|
|
wf_audit_logs(),
|
|
wf_reports(),
|
|
]
|
|
print(f"Creating {len(workflows)} workflows…\n")
|
|
for wf in workflows:
|
|
post_workflow(wf)
|
|
print("\nDone.")
|