The Macroscope Collaboratory

Architectural Pivot: From In-Process Wizard to MCP-Hosted Investigation Framework

Canemah Nature Laboratory Technical Note Series

Document ID: CNL-TN-2026-047 Version: 0.3 (Revised, post first-light analysis) Date: April 10, 2026 Author: Michael P. Hamilton, Ph.D. Affiliation: Canemah Nature Laboratory, Oregon City, Oregon

AI Assistance Disclosure: This technical note was developed collaboratively with Claude (Anthropic, claude-opus-4-6) via Cowork. Claude contributed to architectural analysis, MCP server implementation, Claude Desktop registration, end-to-end verification, and document drafting. The author takes full responsibility for the content, accuracy, and conclusions.

Abstract

Versions 0.1 and 0.2 of this technical note documented the first-light test of the Investigation Wizard as a PHP in-request application driving the Anthropic API directly, with a seven-phase workflow, ecological context priors, and the STR-001 diurnal temperature range investigation. That test validated the scientific workflow (phases, notebook-as-memory, investigator oversight as error correction) but exposed operational limits of the implementation substrate: per-token API costs, fragile in-request LLM orchestration, and an impedance mismatch between the synchronous Apache request lifecycle and the long-running, multi-step, tool-calling nature of real investigations.

This revision documents the architectural pivot made on April 10, 2026 in response to that analysis. The Collaboratory is being rebuilt around three load-bearing components that did not exist (or were not yet integrated) at the time of the STR-001 test: the Macroscope worker framework (a filesystem job queue under launchd), a Model Context Protocol (MCP) server exposing the worker framework to Claude Desktop as tool calls, and Claude Desktop itself as the LLM host. The economic consequence of this shift is that investigation reasoning moves from per-token API billing onto the author's existing Max subscription, making extended exploratory work affordable. The architectural consequence is cleaner separation of concerns: the LLM lives in Claude Desktop, the execution substrate lives in the worker framework, the persistence layer lives in a new collaboratory_db database, and the user-facing UI reduces to a thin PHP front-end.

First-light testing of the new substrate was conducted in this session and is reported below. The Collaboratory MCP server (Projects/Workbench/Collaboratory/mcp/) was built from scratch in six files under 200 lines each, verified in phases 1 through 5 (layout, Python environment, static imports, direct Python enqueue with PHP dispatcher pickup, FastMCP launch with curl), pivoted from SSE to stdio transport after discovering Claude Desktop's HTTPS-only constraint on its custom-connector UI, registered in claude_desktop_config.json as a sibling of the existing preferences object, and exercised end-to-end with the echo worker from within this Cowork conversation. The round trip (enqueue → processing → done → result retrieval) completed successfully, confirming that the MCP transport, the Python enqueue library, the atomic file rename, the PHP dispatcher tick, the worker contract, and the result-readback path are all operational and reachable from Claude.

With the substrate in place, forward work is sequenced into phases A through F: build the web_search worker following the MNG Ollama enrichment pattern, instantiate collaboratory_db, add investigation-state MCP tools, build the minimal PHP wizard UI, run the first real investigation, and optionally add an in-wizard AI assistant pane. This note ends with the SQL for the initial collaboratory_db schema and the worker scaffold as delivered.

1. What the First-Light Test Taught Us

1.1 The Workflow Is Sound

STR-001 produced the correct scientific outcome: a testable hypothesis was framed against live Tempest data, priors were established, a methodology was proposed and executed, the Testing phase caught three material errors (timestamp anomaly, partial-day artifact, mischaracterized ANOVA), conclusions were revised from "highly variable" to "moderately consistent," and the Reflections phase produced a thoughtful meta-analysis with five follow-up questions. The seven-phase structure worked. The notebook-as-memory design worked. The ecological context priors prevented the geographic hallucination documented in CNL-TN-2026-045. None of the workflow-level design decisions need revisiting.

1.2 The Substrate Did Not

Three substrate-level observations drove the decision to rebuild around a new foundation.

The first observation was cost. The full STR-001 run, with Haiku 4.5 on six of seven phases, totaled $0.94. That is cheap enough for a single investigation but it is not cheap enough for the kind of exploratory, discard-heavy research practice a field ecologist actually does. Seven attempts at a single Priors query, three rewrites of a Proposal, a Workflow phase that pulls and re-pulls sensor data while the investigator adjusts the time window: each action is an API call and each API call is billed per token. The notebook-as-memory design (Section 3.3 of v0.2) is architecturally elegant but it amplifies this cost because the system prompt grows with every phase. A complete investigation running on Opus at the same notebook depth would cost approximately $14. The same work on a Max subscription costs $0.

The second observation was operational. Driving the Anthropic API from inside an Apache PHP request means the wizard holds a browser connection open while the LLM works. When that LLM turn involves multi-step tool use (query sensors, compute statistics, retry the query with a different time window, query again), the request time stretches and the browser's experience degrades. Tool calls in the PHP wizard were therefore limited to single-shot query helpers executed server-side before the LLM call, with the LLM reasoning about the returned payload. This is the classical RAG pattern. It works, but it leaves the interesting emergent behavior of autonomous tool-calling (an LLM deciding which tool to invoke next based on intermediate results) on the table.

The third observation was economic-structural. The investigator already has Claude Desktop open with a Max subscription. Claude is already capable of reasoning about sensor data, ecological context, and statistical analysis at the same level the wizard was using. The question is therefore not "how does the wizard call Claude?" but "how does Claude call the wizard?" Inverting the relationship — putting Claude on the outside as the host, and the investigation state machine on the inside as something Claude reaches into via MCP tools — preserves the full power of the workflow while moving the LLM cost to a flat-rate channel that is already paid for.

1.3 The Reframe

What v0.2 called "the Investigation Wizard" was an application that assembled context and called an LLM. What v0.3 calls "the Collaboratory" is a persistent investigation state layer that Claude Desktop reaches into through MCP tools. The scientific workflow is the same. The surface the investigator sees will look very similar: phase list, field forms, notebook view, AI assistance at each field. But the execution substrate is inverted, the cost model is inverted, and the trust boundaries are cleaner.

2. The New Substrate

2.1 Three-Layer Architecture

The rebuilt Collaboratory has three layers, each with a distinct job and each operating under its own process lifecycle.

The execution layer is the Macroscope worker framework (Projects/Live/workers/). It is a filesystem job queue polled every 60 seconds by a PHP dispatcher under launchd, with one worker script per job_type and a single-directory atomic rename convention for the inbox. The framework already existed before this session; it was built for exactly this purpose and had previously been exercised only by the reference echo worker. All long-running or stateful work in the Collaboratory (web search, sensor queries, statistical computation, plot generation, database writes) will live here as worker types. Workers write their results to queue/done/ and the dispatcher moves failures with sidecar diagnostics to queue/failed/.

The access layer is the Collaboratory MCP server (Projects/Workbench/Collaboratory/mcp/). It is a Python process that Claude Desktop launches as a subprocess over stdio at startup, using the FastMCP SDK to expose tool functions that enqueue worker jobs, poll their status, and return their results. The MCP server is a thin translator: it does not do any real work itself; it converts Claude's tool calls into worker framework operations and surfaces worker results back to Claude. This separation is deliberate — the MCP server has no business logic and can be replaced or extended without touching workers.

The presentation layer will be a small PHP front-end under Projects/Live/Galatea/Collaboratory/ (not yet built), running in Apache on Data, reading and writing the collaboratory_db database directly via mysqli, and rendering the phase list, field forms, notebook view, and investigation listings. It will not call any LLM API. When the investigator wants AI assistance on a field, they type the request into Claude Desktop and Claude reaches through the MCP tools to read the current investigation state, write suggestions into step_fields.ai_suggestion, or enqueue workers. The PHP UI then polls the database and renders the suggestions for the investigator to accept, edit, or reject.

2.2 Why MCP Instead of a Richer UI

The decision to use MCP as the bridge instead of building a richer PHP UI with embedded Claude calls was made on cost grounds but is defensible on architectural grounds as well. Cowork and Claude Desktop share an LLM runtime. Asking the investigator to type "summarize what you found in Priors" into Claude Desktop and letting Claude use a read_investigation_state tool is not meaningfully different from asking them to click a "Summarize Priors" button in a web UI that calls the Anthropic API in the background — except that the former costs nothing beyond the Max subscription and surfaces Claude's reasoning transparently in the chat pane. The latter would spend tokens on every click and hide the reasoning behind a loading spinner.

The trade-off is that the investigator now has two windows open: Claude Desktop for AI collaboration and the PHP Collaboratory UI for structured state. This mirrors the way Dr. Hamilton already works in Cowork with multiple tabs and windows. It is not a regression.

2.3 Why stdio Transport, Not SSE

The MCP specification supports two transports for local servers: stdio (the host launches the server as a subprocess and speaks MCP over stdin/stdout) and SSE (the host connects over HTTP to a loopback port). The original build of the Collaboratory MCP server used SSE because stdio felt heavier — requiring the host to manage the server lifecycle — and because SSE is easier to debug with curl. The server was built, verified in isolation with FastMCP's uvicorn startup and a curl probe of http://127.0.0.1:8765/sse returning text/event-stream, and then submitted to Claude Desktop's "Add custom connector" UI.

That UI rejected the URL with an https:// validation constraint. Claude Desktop will not accept plain HTTP on loopback for custom connectors; this is a security-hardening decision in the desktop app, not a bug. The project thus pivoted to stdio, a one-line change in server.py (replacing mcp.run(transport="sse") with mcp.run(transport="stdio")) plus an edit to ~/Library/Application Support/Claude/claude_desktop_config.json adding a mcpServers.collaboratory object specifying the command and args for the Python subprocess. Claude Desktop launches the MCP server on startup, manages its lifecycle, and speaks MCP over the stdio pipe. The SSE path is retained in the codebase as a comment for future debugging use but is not the primary registration channel.

The diagnostic detour through claude_desktop_config.json vs config.json is worth noting. The Claude application-support directory contains both files. The first holds user-managed configuration (preferences, MCP server registrations); the second holds runtime state managed by the app itself (encrypted OAuth tokens, session metadata). Editing the latter would break the app. The registration went into the former as a sibling of the existing preferences object.

2.4 The First-Light MCP Test

The MCP server was verified in five phases on Data, using the /opt/homebrew/bin/python3 runtime and the mcp>=1.2.0 package installed via pip3 --break-system-packages.

Phase 1 confirmed the file layout: six files in Projects/Workbench/Collaboratory/mcp/ (README.md, server.py, worker_dispatch.py, config.py, requirements.txt, com.macroscope.collaboratory_mcp.plist), each under 200 lines and carrying file-header comments.

Phase 2 verified the Python environment with importlib.metadata.version('mcp') reporting the installed package.

Phase 3 verified that the tool functions imported and resolved correctly at module load time, and that list_worker_types() returned the echo worker entry by scanning the workers directory on disk.

Phase 4 was the decisive end-to-end test. A direct Python dispatch_worker_job('echo', {...}) call wrote an atomic JSON file into Projects/Live/workers/queue/inbox/ with the scratch-dotfile rename pattern. The existing PHP worker dispatcher (com.macroscope.worker_dispatcher launchd agent, 60-second tick) picked up the file on its next run, moved it through queue/processing/, executed the echo worker, and left the result in queue/done/ as a .result.json sibling. The Python code that had enqueued the job then read the result back. The roundtrip worked on the first attempt.

Phase 5 verified the FastMCP server process could launch and serve the SSE endpoint (before the subsequent pivot to stdio). A curl http://127.0.0.1:8765/sse confirmed the text/event-stream content type and the session handshake.

After the SSE-to-stdio pivot and the Claude Desktop config edit, a final verification was performed by relaunching Claude Desktop and exercising the MCP channel from inside the Cowork conversation. mcp__collaboratory__list_worker_types returned the echo worker correctly. mcp__collaboratory__dispatch_worker_job was called with a test payload. The status tool reported processing then done. The result tool returned the echoed payload with Python version 3.14.3, worker pid, and UTC processing timestamp. The full stack — Claude Desktop → stdio → FastMCP → Python worker_dispatch → filesystem queue → PHP dispatcher → Python worker → result file → Python readback → stdio → Claude Desktop — is reachable and operational.

3. What Changes, What Stays

3.1 What Stays from v0.2

The scientific workflow is unchanged. The notion of phases, the separation of Workflow from Testing, the notebook as the authoritative memory across sessions, and the ecological context priors all survive into v0.3. The STR-001 investigation retains its status as the canonical first-light test of the scientific workflow; it will be re-run on the new substrate once the substrate is minimally functional.

The three-layer context architecture from CNL-TN-2026-045 (STRATA IQ) is unchanged. Place context, time context, and instrument context are still the three pillars of grounded reasoning, and the ecological context priors captured at investigation creation time remain the primary defense against geographic hallucination.

The MNG integration points are unchanged. macroscope_nexus.places remains the site registry. macroscope_nexus.lookup_cache remains the static priors source. macroscope.sensor_platforms remains the instrument registry. The Collaboratory reaches into these existing databases via worker types, not by writing its own copies of the data.

3.2 What Changes in v0.3

The wizard moves from a synchronous PHP application that calls the Anthropic API to an asynchronous state machine that Claude Desktop reads and writes through MCP tools. The seven-phase model expands to a ten-step form-based model (Plan / Execute / Publish clusters, documented in Section 4 below) to better accommodate the field-level AI assistance pattern and the explicit approval gates between steps. The phase names from v0.2 (Seed, Priors, Proposal, Workflow, Testing, Conclusions, Reflections) will be re-mapped onto the ten-step model during implementation; the scientific arc is preserved but the granularity is finer and the approval gates are more explicit.

The LLM cost model changes from per-token API billing to Max-subscription flat rate. Claude Desktop becomes the AI host. The Anthropic API may still be used for specific worker types that need fine-grained programmatic control (a future claude_batch worker, for example), but it is not the primary reasoning channel.

The database moves from the existing strata_db.investigation_context_priors table to a new collaboratory_db schema with investigations, investigation_steps, step_fields, step_artifacts, investigation_notes, and investigation_jobs tables. The SQL for the initial schema is provided in Appendix A.

The execution substrate moves from ad hoc PHP helpers inside the wizard to the worker framework. Each thing the old wizard did synchronously — query a sensor, fetch a URL, compute a statistic — will become a worker type. The first one, web_search, is delivered with this technical note and is scaffolded as Phase A work.

3.3 Trust Boundaries

One consequence of the rebuild that is worth naming explicitly: the new architecture has cleaner trust boundaries. The MCP server validates job_type against a regex and a filesystem existence check before enqueuing anything, so Claude cannot cause arbitrary code execution by asking for an unknown job type. The workers run under the user account, not Apache's. The PHP UI reads investigation state from the database; it does not call external services directly. The investigator remains the only actor who can approve a step transition. These are all improvements over the v0.2 design in which the wizard's server-side code was doing everything under the Apache request.

4. The Ten-Step Form Model

The ten-step form model is carried forward from the design discussions that preceded this technical note and is documented here for the first time as the target structure for the rebuilt Collaboratory. The ten steps are grouped into three clusters (Plan, Execute, Publish) with explicit approval gates between clusters.

Table 1. The ten-step form model.

Each step is a form made up of named fields. Each field has a label, a type (text, textarea, select, multi-select, number, date), a current value, an optional AI suggestion, and an optional accepted-at timestamp. The AI assistance pattern is field-level: the investigator asks Claude Desktop to suggest a value for a specific field, Claude uses an MCP tool to write the suggestion into step_fields.ai_suggestion, and the investigator reviews and accepts (or edits, or rejects) the suggestion via the PHP UI. The accepted value is the authoritative record; the AI suggestion is preserved alongside it for provenance.

Transitions between clusters (Plan → Execute, Execute → Publish) are explicit approval gates. The investigator must affirmatively approve the cluster as a whole before the next cluster becomes active. Within a cluster, steps can be worked on in any order and revisited freely. This structure accommodates the reality that ecological investigation is iterative: you often revise your Proposal after starting Collection, or revisit Context after Testing reveals a problem.

5. Forward Plan (Phases A–F)

The work ahead is sequenced into six phases, each a small, testable increment.

Phase A — web_search worker. A Python worker script at workers/web_search/worker.py that takes a payload containing one or more queries and optional URLs, reads an OLLAMA_API_KEY from a JSON credential file at ~/Sites-secure/collaboratory/credentials.json, calls Ollama's /api/web_search and /api/web_fetch endpoints following the MNG api/places/enrich.php pattern, and returns an aggregated text summary and source list. The worker is the minimum viable data-gathering tool for the Priors and Collection steps. The scaffold is delivered with this technical note (see Appendix B) and will be dropped into place in the next working session.

Phase B — collaboratory_db instantiation. Execute the SQL in Appendix A in phpMyAdmin to create the database and its six tables. Add a credential file at ~/Sites-secure/collaboratory/database.php with the db host, user, password, and database name. This is the work that can be done while the author is offline; the SQL is self-contained and has no dependencies on the PHP UI.

Phase C — investigation-state MCP tools. Extend the MCP server with tools that Claude Desktop will use to read and write investigation state: create_investigation, list_investigations, get_investigation, get_step, set_step_field, add_step_artifact, add_investigation_note, request_step_approval. These tools talk to collaboratory_db directly via a small mysqli helper module. The MCP server remains thin; no business logic beyond what the tool names suggest.

Phase D — minimal PHP UI. Build a small PHP front-end at Projects/Live/Galatea/Collaboratory/ with three pages: an investigation list, an investigation detail page showing the ten-step form with current values and AI suggestions, and a notebook view. No JavaScript frameworks. The UI reads and writes collaboratory_db directly via mysqli in the Galatea deployment style. It does not call any LLM.

Phase E — first real investigation. Re-run STR-001 (or a comparable investigation) on the new substrate. Validate that the ten-step model accommodates the scientific workflow of v0.2, that Claude Desktop can drive the phases through MCP tool calls, that the PHP UI renders the state correctly, and that the cost is materially lower than the v0.2 run. Document the result in a v0.4 revision of this technical note.

Phase F — optional in-wizard AI assistant. If the two-window workflow (Claude Desktop + PHP UI) proves awkward in practice, consider adding a minimal in-wizard chat pane driven by a dedicated MCP client embedded in the PHP page. This is explicitly optional; the v0.3 architecture assumes the two-window pattern is acceptable.

6. Risks and Open Questions

The largest open question is whether the two-window pattern (Claude Desktop on one side, PHP UI on the other) is ergonomically acceptable for sustained investigation work. The v0.2 wizard was a single-surface experience. The v0.3 design is not. This will be evaluated in Phase E.

A second open question is notebook compression. The v0.2 analysis observed that Haiku's per-query cost scales with the accumulating notebook in the system prompt. The v0.3 rebuild moves reasoning onto the Max subscription, which removes the per-query billing concern, but does not eliminate the context-window ceiling. For investigations with dozens of notebook entries, some form of summarization — folding older phases into summaries while keeping recent phases in full — will become necessary. Claude Desktop handles this for conversation history automatically; whether the MCP tools need to expose a "compressed view" of the investigation state or whether Claude can summarize on the fly is not yet decided.

A third risk is the divergence between the rebuilt Collaboratory and the existing STRATA 2.0 Observatory and Intelligence dashboards. Both are valid consumers of the same underlying Macroscope data. The convergence story in CNL-TN-2026-043 anticipated STRATA, MNG, and the Collaboratory sharing infrastructure. The rebuild reinforces this by leaning on the worker framework (which serves all three) and macroscope_nexus (which grounds all three in place). The divergence risk is that the Collaboratory UI will look different from the Observatory UI and investigators may end up navigating between visually inconsistent surfaces. This is a UX concern to revisit after Phase D.

7. Files Created This Session

The MCP server skeleton was built from scratch in this session at Projects/Workbench/Collaboratory/mcp/:

Table 2. Files created at Projects/Workbench/Collaboratory/mcp/ this session.

The Claude Desktop config file at ~/Library/Application Support/Claude/claude_desktop_config.json was updated to add an mcpServers.collaboratory entry as a sibling of the existing preferences object, pointing at /opt/homebrew/bin/python3 and the absolute path to server.py. The existing preferences object was preserved unchanged.

The web_search worker scaffold was delivered to Projects/Live/workers/workers/web_search/worker.py with this technical note (see Appendix B).

8. Relationship to Other Documents

Table 3. Related documents in the CNL technical note series.

Appendix A: collaboratory_db Initial Schema

The following SQL creates the initial schema. It is self-contained and idempotent on a fresh database. Execute via phpMyAdmin on Data. A corresponding credentials file at ~/Sites-secure/collaboratory/database.php will be needed by the PHP UI and by the investigation-state MCP tools; its contents should follow the existing ~/Sites-secure/macroscope/sync.conf.php pattern ($db_host, $db_user, $db_pass, $db_name).

CREATE DATABASE IF NOT EXISTS collaboratory_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE collaboratory_db;

-- Investigation container. One row per research inquiry.
CREATE TABLE investigations (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug             VARCHAR(64)  NOT NULL UNIQUE,
  title            VARCHAR(255) NOT NULL,
  domain           ENUM('EARTH','LIFE','HOME','SELF','CROSS') NOT NULL DEFAULT 'EARTH',
  hypothesis       TEXT         NULL,
  status           ENUM('active','paused','complete','archived') NOT NULL DEFAULT 'active',
  site_id          INT UNSIGNED NULL COMMENT 'FK into macroscope_nexus.places',
  investigator     VARCHAR(255) NOT NULL,
  default_model    VARCHAR(64)  NULL COMMENT 'informational; Max-subscription run uses Claude Desktop',
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_domain (domain)
) ENGINE=InnoDB;

-- Ten steps per investigation. Populated at creation time with the canonical
-- step list from CNL-TN-2026-047 v0.3 Table 1.
CREATE TABLE investigation_steps (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  investigation_id  INT UNSIGNED NOT NULL,
  step_number       TINYINT UNSIGNED NOT NULL,
  step_name         VARCHAR(64)  NOT NULL,
  cluster           ENUM('plan','execute','publish') NOT NULL,
  status            ENUM('pending','in_progress','approved','skipped') NOT NULL DEFAULT 'pending',
  opened_at         DATETIME     NULL,
  approved_at       DATETIME     NULL,
  approved_by       VARCHAR(255) NULL,
  UNIQUE KEY uniq_inv_step (investigation_id, step_number),
  FOREIGN KEY (investigation_id) REFERENCES investigations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Named fields within a step. The AI-assist pattern writes suggestions here
-- and the investigator accepts them into the value column.
CREATE TABLE step_fields (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  step_id           INT UNSIGNED NOT NULL,
  field_key         VARCHAR(64)  NOT NULL,
  field_label       VARCHAR(255) NOT NULL,
  field_type        ENUM('text','textarea','select','multiselect','number','date') NOT NULL DEFAULT 'textarea',
  value             LONGTEXT     NULL,
  ai_suggestion     LONGTEXT     NULL,
  suggestion_source VARCHAR(128) NULL COMMENT 'e.g. claude_desktop, web_search_job_id',
  accepted_at       DATETIME     NULL,
  sort_order        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_step_field (step_id, field_key),
  FOREIGN KEY (step_id) REFERENCES investigation_steps(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Non-field outputs attached to a step: web search results, sensor pulls,
-- plots, citations, raw notes. Linked to the worker job that produced them
-- when applicable, so provenance is walk-able back to queue/done/.
CREATE TABLE step_artifacts (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  step_id           INT UNSIGNED NOT NULL,
  artifact_type     VARCHAR(64)  NOT NULL COMMENT 'web_search_result, sensor_query, plot, citation, note',
  source            VARCHAR(128) NULL,
  title             VARCHAR(512) NULL,
  url               VARCHAR(1024) NULL,
  content           LONGTEXT     NULL,
  job_id            VARCHAR(128) NULL COMMENT 'worker framework job_id if applicable',
  created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_artifact_type (artifact_type),
  INDEX idx_job_id (job_id),
  FOREIGN KEY (step_id) REFERENCES investigation_steps(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Notebook entries. The authoritative running log of the investigation.
-- entry_type captures who said what and in what capacity.
CREATE TABLE investigation_notes (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  investigation_id  INT UNSIGNED NOT NULL,
  step_id           INT UNSIGNED NULL,
  entry_type        ENUM('user_note','ai_response','tool_call','tool_result','correction','decision') NOT NULL,
  author            VARCHAR(255) NOT NULL,
  body              LONGTEXT     NOT NULL,
  metadata          JSON         NULL,
  created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_inv_created (investigation_id, created_at),
  FOREIGN KEY (investigation_id) REFERENCES investigations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Worker-job index. Lets the Collaboratory look up jobs by investigation
-- without walking the queue filesystem.
CREATE TABLE investigation_jobs (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  investigation_id  INT UNSIGNED NOT NULL,
  step_id           INT UNSIGNED NULL,
  job_id            VARCHAR(128) NOT NULL UNIQUE,
  job_type          VARCHAR(64)  NOT NULL,
  status            ENUM('inbox','processing','done','failed') NOT NULL DEFAULT 'inbox',
  enqueued_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at      DATETIME     NULL,
  INDEX idx_investigation (investigation_id),
  INDEX idx_job_id (job_id),
  FOREIGN KEY (investigation_id) REFERENCES investigations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

The schema intentionally does not foreign-key into macroscope_nexus or macroscope; those databases are authoritative for places and sensor platforms but the Collaboratory treats them as external references with loosely-coupled integer IDs. This matches the existing STRATA / MNG pattern.

Appendix B: web_search Worker Scaffold

The following Python script is delivered to Projects/Live/workers/workers/web_search/worker.py with this technical note. It follows the MNG api/places/enrich.php Ollama pattern (two endpoints: /api/web_search for query-based search, /api/web_fetch for URL retrieval). Credentials live in a JSON file at ~/Sites-secure/collaboratory/credentials.json — a minimal file with a single ollama_api_key string. The worker is scaffolded in the sense that its contract and credential layout are locked in; adjustments to the result shape and the prompt template will happen during Phase A testing.

#!/usr/bin/env python3
# worker.py
# File path: ~/Macroscope/Projects/Live/workers/workers/web_search/worker.py
# Purpose: Run one or more Ollama web_search queries and optional web_fetch
#          URL retrievals for the Collaboratory. Aggregates results into a
#          text context block and a structured source list. Follows the
#          same pattern as MNG's api/places/enrich.php Ollama path.
#
# Credential file (JSON, mode 0600):
#   ~/Sites-secure/collaboratory/credentials.json
#   { "ollama_api_key": "sk-..." }
#
# Payload schema:
#   {
#     "queries":    ["query string 1", "query string 2", ...],   // optional
#     "urls":       ["https://...", ...],                        // optional
#     "max_chars":  8000                                         // optional, per-URL truncation
#   }
#
# At least one of queries or urls is required.

The full worker source is at Projects/Live/workers/workers/web_search/worker.py; the scaffold is approximately 120 lines and fits the worker framework contract (argv[1] = job path, exit 0 with .result.json sibling on success). A requirements.txt sibling lists the single dependency (requests), installable via pip3 install -r requirements.txt --break-system-packages.

Document History