---
name: analyze-cisegmentation-measurements
description: Analyze CI Segmentation DuckDB or SQLite databases for morphology, intensity, mask relationships, spot assignments, spatial neighbors and contacts, Pearson/Manders colocalization, trajectories, cell/nucleus divisions, and linked WKB polygons or subpixel spot points; includes SQL, pandas, calibration and interpretation.
metadata:
  version: "7"
---

# Instructions

Help the user inspect and analyze a CI Segmentation measurements database.

## Required reference

Read this reference before substantive queries if the consumer has not already
loaded it:

```text
references/REFERENCE.md
```

Use that resource as the authoritative reference for the database schema,
measurement semantics, convenience views, and query examples.
Check the file's recorded schema before applying the bundled reference to a
database from a different revision.

For spatial, colocalization, tracking or geometry questions, also load
[EXTENSIONS.md](references/EXTENSIONS.md). Base schema version 5 alone does
not imply optional features exist. Read `measurement_extensions` if present;
geometry files instead expose `geometry_info` and `geometries`.

## Workflow

1. Identify the attached or uploaded `.duckdb` or `.sqlite` file.
2. Open the database read-only.
3. Read `schema_info` once. Inspect tables or columns only when the schema
   version is unknown or the documented query fails. Reuse verified schema
   facts for every follow-up while the file hash is unchanged.
4. Read `schema_info` and `measurement_runs` when schema version, provenance,
   workflow settings, output store identity, or source and output paths matter.
5. Prefer the documented convenience views when they contain the needed
   context.
6. Filter and aggregate in SQL before returning data to pandas.
7. Explain the queried columns, filters, grouping, units, relationship
   direction, and relevant calibration or missing-value caveats.
8. Close the database connection.

For object-to-render requests, use the canonical combined query in the
reference so the object, its related objects, navigation rows, and render
coordinates are resolved in one execution. Reuse that successful result for
rendering; do not rediscover files or schema. Treat image-QC findings as
**review candidates**, never definitive bad images.

For questions whose answer should be a PNG, load
`references/PNG_QUESTIONS.md` only when needed. Prefer a single bounded gallery
request over one render request per object.

For a gallery, the analysis result is also the render contract. Assign
`result = {"store_uuid": store_uuid, "render_panels": panels}` and include
every panel's exact field, ROI, source channels, overlay paths and values,
T/Z, title, and caption. Do not return only a shortened display table or rely
on another Python variable: consumers receive only `result`. Copy
`render_panels` unchanged into the gallery tool so label values cannot become
detached from their field and bounding box.

## Database connections

Open DuckDB read-only:

```python
import duckdb

db_path = "screen__cisegmentation_measurements.duckdb"
db = duckdb.connect(db_path, read_only=True)
```

Open SQLite or SQLite3 read-only:

```python
from pathlib import Path
import sqlite3

db_path = Path("screen__cisegmentation_measurements.sqlite").resolve()
db = sqlite3.connect(
    f"file:{db_path.as_posix()}?mode=ro",
    uri=True,
)
```

If the database path is unknown, locate candidates with:

```python
from pathlib import Path

database_files = sorted(
    list(Path.cwd().rglob("*.duckdb"))
    + list(Path.cwd().rglob("*.sqlite"))
    + list(Path.cwd().rglob("*.sqlite3"))
)
database_files
```

## Analysis rules

- Inspect relation columns before adapting a query from the reference.
- Use parameters for user-supplied values when the database API supports them.
- Treat pixel coordinates and timepoints as zero-based.
- Treat `channel_index` as one-based.
- Treat bounding-box minima as inclusive and maxima as exclusive.
- Use `object_navigation` for viewer or ROI coordinates when schema version 3
  or newer provides it. Never invent environment-specific image or plate IDs
  from a portable database.
- Treat `output_store_uuid` as the cross-check between a database and an
  active output OME-Zarr. Refuse navigation when both UUIDs exist and differ.
- Do not substitute pixel units for unavailable physical units.
- Interpret relationships directionally; do not interchange source and target.
- Distinguish 2D masks, true 3D masks, and point-only objects.
- Do not claim that intensities were normalized or background-corrected.
- Separate database observations from biological interpretation.
- Keep frame-local `label_value` separate from `object_id` and `track_id`.
  Track IDs identify trajectory segments; daughter segments have new IDs.
- Spot-only runs do not require cells/nuclei. Missing compartments mean no
  assignments; existing compartments with no assigned spots have zero counts.
- Check `time_unit` before interpreting speed. Missing physical timing means
  micrometers per frame, not micrometers per second.
- Keep directional Manders coefficients distinct; NULL with a reason records
  an undefined statistic. Otsu thresholds are automatic and sampled.
- Raster masks remain authoritative. Geometry uses pixel-centre coordinates,
  half-integer pixel edges, WKB and explicit calibrated transforms.

## Response

Provide executable notebook cells when the user needs code. Report:

- the database engine and file;
- the tables or views queried;
- the SQL or analysis logic;
- filters, grouping keys, and relationship direction;
- measurement units;
- calibration, anisotropy, point-only, and missing-value caveats.

If a query fails because the actual schema differs from the reference, inspect
the schema, revise the query, and explain the difference.