---
name: overlap
description: |
  Export and correlate Investibook chat user requests with Jira tickets by cluster_id and normalized UTC+8 time windows.
  Use when a user asks to export a natural-week Investibook dataset, extract cluster/time from chat messages and TCOC/ST/TCID descriptions, or find time overlaps between the two.
  Read-only for production systems; do not use for modifying DB, Kubernetes resources, pods, or Investibook data.
---

# Jira Time Correlation

## Purpose

Use this skill to reproduce the weekly workflow:

1. Export raw Investibook chat user messages and Jira tickets, enriched with Investibook dossier status.
2. Normalize chat message `cluster_id` and `standard_time`.
3. Normalize Jira ticket `standard_time`.
4. Correlate chat records and Jira tickets when `cluster_id` matches and the normalized time intervals overlap.

## Non-negotiable Rules

- Production access is read-only. Never modify DB rows, pods, Kubernetes resources, or Investibook data.
- If authentication, pod lookup, DB path, table/column existence, helper import, or SQL execution fails, stop and report the failure. Do not guess or fabricate output.
- Do not query or merge `feishu_chat_messages` for chat export; only query `messages_v2`.
- Do not deduplicate raw chat export rows. Deduplication is only allowed during chat normalization by `(content, cluster_id)` across different `message_id` values.
- Never convert or compare timestamps by hand. Use `scripts/tcoc_time_correlation.py` for normalization and overlap checks.
- Keep final export directories clean when the user requires a fixed file set.

## Quick Start

Run from the repository root:

```bash
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py --help

# Full prod read-only workflow: export raw data, normalize, and correlate.
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date YYYY-MM-DD

# Full prod workflow for TCOC, ST, and TCID.
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date YYYY-MM-DD \
  --project-key TCOC,ST,TCID

# Export prod raw data only.
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py export-prod \
  --week-start-date YYYY-MM-DD

# Normalize existing raw exports and correlate them, without touching prod.
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-all \
  --input-dir exports/YYYY-M-D \
  --project-key TCOC,ST,TCID
```

`run-week` writes the raw and derived files into
`exports/<week-start-date-input>` unless `--output-dir` is set. For example,
`--week-start-date 2026-6-15` writes to `exports/2026-6-15`.

The local-only `run-all` command expects:

- `chat_user_messages_week.json`
- `dossier_tcoc_for_week.json` (kept for compatibility; contents are Jira-created TCOC Incident tickets with dossier enrichment)

For custom project keys, `run-all` expects the project-specific raw Jira file,
for example `dossier_tcoc_st_tcid_for_week.json` when run with
`--project-key TCOC,ST,TCID`.

and writes:

- `time_standard_chat.json`
- `time_standard_tcoc.json`
- `time_standard_matches.json`

## Raw Prod Export Workflow

Use `run-week` for the full one-command flow, or `export-prod` when the user
only wants the two raw JSON files. Use this section to understand or manually
debug the read-only export steps.

Required inputs:

- `week_start_date` in `YYYY-MM-DD` form. Normalize inputs like `2026-6-15` to `2026-06-15`.
- `week_end_exclusive_date = week_start_date + 7 days`.
- Chat export uses the UTC+8 natural-week window. Convert it to UTC before
  querying `messages_v2.created_at`; for example `2026-06-15` means
  `created_at >= 2026-06-14T16:00:00Z` and
  `created_at < 2026-06-21T16:00:00Z`.

Prod environment:

- AWS SSO profile: `dbaas-prod-clinic-oncall`
- EKS context: `arn:aws:eks:us-west-2:557537366020:cluster/devops-prod`
- namespace: `investibook`
- backend DB path in pod: `/data/db/investibook.db`

If SSO is not configured or expired, ask for/execute:

```bash
aws configure sso --profile dbaas-prod-clinic-oncall
```

Scripted prod export command:

```bash
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py export-prod \
  --week-start-date 2026-06-15-old
```

Scripted full command:

```bash
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date 2026-06-15-old
```

Dev environment:

- EKS context: `arn:aws:eks:us-west-2:552185537300:cluster/devops-staging`
- namespace: `investibook`
- backend DB path in pod: `/data/db/investibook.db`

Dev three-week run:

```bash
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date 2026-06-15 \
  --context arn:aws:eks:us-west-2:552185537300:cluster/devops-staging \
  --namespace investibook

python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date 2026-06-22 \
  --context arn:aws:eks:us-west-2:552185537300:cluster/devops-staging \
  --namespace investibook

python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py run-week \
  --week-start-date 2026-06-28 \
  --context arn:aws:eks:us-west-2:552185537300:cluster/devops-staging \
  --namespace investibook
```

Read-only pod discovery:

```bash
kubectl get pods -n investibook \
  --context arn:aws:eks:us-west-2:557537366020:cluster/devops-prod \
  -o json
```

Choose the running, ready pod with label `app=backend`.

Before exporting, verify the DB exists and required tables/columns exist with a read-only SQLite connection:

- `messages_v2`: `id`, `workspace_id`, `role`, `content`, `created_at`, `status`
- `workspaces`: `id`, `name`, `user_id`
- `incident_dossiers`: `id`, `manual_not_incident`
- `incident_dossier_issues`: `dossier_id`, `issue_key`
- `incident_dossier_workspaces`: `dossier_id`, `workspace_id`
- `workspace_jira_links`: `workspace_id`, `issue_key`
- `jira_tickets`: `issue_key`, `project_key`, `issue_type`, `cluster_id`, `jira_created_at`, `raw_payload_json`

### Chat Export

Query only `messages_v2`, left-joining `workspaces`:

```sql
SELECT
  m.id AS id,
  m.workspace_id AS workspace_id,
  m.content AS content,
  m.created_at AS created_at,
  w.name AS workspace_name
FROM messages_v2 m
LEFT JOIN workspaces w ON m.workspace_id = w.id
WHERE m.created_at >= '<week_start_date 00:00:00 UTC+8 converted to UTC>'
  AND m.created_at < '<week_end_exclusive_date 00:00:00 UTC+8 converted to UTC>'
  AND m.role = 'user'
  AND m.status = 'completed'
ORDER BY m.created_at, m.id;
```

Write JSON array to `chat_user_messages_week.json`. Each object must contain only:

- `id`
- `workspace_id`
- `content`
- `created_at`
- `workspace_name`

### Jira Export

Query `jira_tickets` directly using the same UTC+8 natural-week window as chat
export. Convert `[week_start_date 00:00 UTC+8, week_start_date+7d 00:00 UTC+8)`
to epoch seconds before querying `jira_tickets.jira_created_at`.

Jira ticket membership for the week is decided only by Jira data:

- For `TCOC`: `project_key = 'TCOC'` and `issue_type = 'Incident'`
- For non-`TCOC` projects such as `ST` and `TCID`: `project_key` is requested and `issue_type` is not constrained
- `jira_created_at` inside the UTC+8 natural-week window

The dossier tables are only enrichment. They provide Investibook disposition
state for filtering, but they do not decide whether a Jira ticket belongs to
the week.

In the backend pod, use a read-only SQLite connection and query:

```sql
SELECT
  t.issue_key,
  t.cluster_id,
  t.jira_created_at,
  t.raw_payload_json,
  COALESCE((
    SELECT MAX(COALESCE(d.manual_not_incident, 0))
    FROM incident_dossier_issues i
    JOIN incident_dossiers d ON d.id = i.dossier_id
    WHERE i.issue_key = t.issue_key
  ), 0) AS manual_not_incident,
  (
    SELECT COUNT(DISTINCT wid)
    FROM (
      SELECT dw.workspace_id AS wid
      FROM incident_dossier_issues i
      JOIN incident_dossier_workspaces dw ON dw.dossier_id = i.dossier_id
      WHERE i.issue_key = t.issue_key
      UNION
      SELECT wjl.workspace_id AS wid
      FROM workspace_jira_links wjl
      WHERE wjl.issue_key = t.issue_key
    )
  ) AS linked_workspace_count
FROM jira_tickets t
WHERE (
    (t.project_key = 'TCOC' AND t.issue_type = 'Incident')
    OR t.project_key IN (:non_tcoc_project_keys)
  )
  AND t.jira_created_at >= :start_epoch
  AND t.jira_created_at < :end_epoch
ORDER BY t.jira_created_at, t.issue_key;
```

Do not exclude `manual_not_incident = 1` or already-linked tickets during raw
export. Raw export should preserve these fields so normalization can apply the
standard filtering later. Jira tickets without dossier rows are valid and should
receive `manual_not_incident = 0` and `linked_workspace_count = 0`.

Write JSON array to `dossier_tcoc_for_week.json`. The filename is retained for
compatibility, but the file now means "Jira TCOC Incident tickets for the week,
with dossier enrichment." Each object must contain:

For custom project keys, write to a project-specific raw file such as
`dossier_tcoc_st_tcid_for_week.json`. The record field set stays the same.

- `issue_key`
- `cluster_id`
- `jira_created_at`
- `description` from `raw_payload_json.fields.description`
- `manual_not_incident`, aggregated as `MAX(COALESCE(incident_dossiers.manual_not_incident, 0))` for the issue
- `linked_workspace_count`, the distinct workspace count from all dossiers for
  the issue plus direct `workspace_jira_links` for the issue

## Normalization And Correlation

Use the bundled script for all local processing:

```bash
# Chat only
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py normalize-chat \
  --input exports/YYYY-M-D/chat_user_messages_week.json \
  --output exports/YYYY-M-D/time_standard_chat.json

# Jira only
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py normalize-tcoc \
  --input exports/YYYY-M-D/dossier_tcoc_st_tcid_for_week.json \
  --output exports/YYYY-M-D/time_standard_tcoc.json

# Correlate normalized files
python3 deployment/evolver/scripts/overlap/tcoc_time_correlation.py correlate \
  --chat exports/YYYY-M-D/time_standard_chat.json \
  --tcoc exports/YYYY-M-D/time_standard_tcoc.json \
  --output exports/YYYY-M-D/time_standard_matches.json
```

Correlation rule:

- Match only when `cluster_id` is identical.
- Parse `standard_time` as a UTC+8 point or interval.
- Honor explicit timezone abbreviations and offsets attached to incident times,
  including UTC/GMT, JST, CEST/CET, BST, and common North American timezones,
  before normalizing to UTC+8. A description time such as `10:38 CEST` must
  not fall back to `jira_created_at`.
- When a time-only range is followed by an explicit timezone and calendar date,
  for example `10:38-10:50 CEST on 2026-07-20`, bind the range to that date.
  Do not infer its date from the chat message creation time.
- For TCOC descriptions, do not collapse an incident chain to a single early
  timestamp when multiple high-signal event times are present. Merge the
  high-confidence event points/ranges from the same local day into one incident
  window; for example, if a ticket has an alert at `17:51` and node offline at
  `18:45`, normalize it as a window covering `17:51-18:45 UTC+08` rather than
  a single created/API timestamp such as `01:51 UTC+08`.
- Treat obviously wrong single-point TCOC times caused by upstream API field
  selection, timezone conversion, or created-time fallback as source/API data
  quality issues, not as a diagnosis quality issue. Prefer explicit alert,
  outage, node-offline, failure, or customer-impact times from the description
  when they exist.
- Load `chat_user_messages_week.json` from the same directory as
  `time_standard_chat.json` and copy `workspace_id` from the raw chat row whose
  `id` equals the normalized `message_id`.
- Output one JSON object when intervals overlap.

Jira normalization filters raw Jira rows before time parsing:

- Skip rows with empty `cluster_id`.
- Skip rows with `manual_not_incident != 0`.
- Skip rows with `linked_workspace_count > 0`.
- `time_standard_tcoc.json` does not include `workspace_id`.

`time_standard_matches.json` fields:

- `message_id`
- `issue_key`
- `cluster_id`
- `workspace_id`
- `standard_time` (the overlap window in UTC+8)

An empty result is valid and should be written as `[]`.

## Required Validation

After each run, report:

- Output file absolute path(s).
- Record count(s).
- JSON parse result.
- Exact field-set validation result.
- For raw Jira export: rows must use the current field set
  (`issue_key`, `cluster_id`, `jira_created_at`, `description`,
  `manual_not_incident`, `linked_workspace_count`).
- For normalized chat: `message_id` exists in `chat_user_messages_week.json`, `cluster_id` and `standard_time` are non-empty.
- For normalized TCOC: `standard_time_source` is either `description` or `jira_created_at`, `standard_time` is non-empty.
- For matches: every row has exactly `message_id`, `issue_key`, `cluster_id`, `workspace_id`, `standard_time`; `workspace_id` is non-empty.