# Investibook Troubleshooting Guide

## 1. Deployment Architecture

Investibook consists of three deliverables: **frontend**, **backend**, and **CLI**.

### Environments

| Environment | AWS Account | EKS Cluster | Namespace | ECR Repo Prefix |
|-------------|-------------|-------------|-----------|-----------------|
| Dev | `552185537300` | `devops-staging` (us-west-2) | `investibook` | `investibook/dev/` |
| Prod (preview) | `557537366020` | `devops-prod` (us-west-2) | `investibook` | `investibook/prod/` |

### CI/CD Workflows

```
.github/workflows/
├── build-push-image.yaml   # Reusable: build Docker image → push to ECR
├── ci-dev.yaml             # Dev pipeline (trigger: push to main)
├── ci-prod.yaml            # Prod pipeline (trigger: push to release-* or v* tag)
└── cli-release.yaml        # CLI release (trigger: cli/v* tag)
```

#### Dev Pipeline (`ci-dev.yaml`)

Trigger: push to `main` or manual dispatch.

1. **Build** frontend & backend images in parallel → push to dev ECR (`investibook/dev/*`)
2. **Deploy** directly via `kubectl apply -k deployment/kustomize/envs/dev/` on `devops-staging` cluster
3. Image tag = git commit SHA, written into `deployment/kustomize/envs/dev/kustomization.yaml`

#### Prod Pipeline (`ci-prod.yaml`)

Trigger: push to `release-*` branch or `v*` tag, or manual dispatch.

1. **Build** frontend & backend images in parallel → push to prod ECR (`investibook/prod/*`)
2. **Trigger** `tidbcloud/app-delivery` repo's `bump-investibook-version.yaml` workflow (GitOps)
   - Uses `yq` to update `devops-service/investibook/envs/preview/kustomization.yaml` in app-delivery
   - Auto-creates a PR with label `automated pr`
   - PR goes through FAD approval + check run → merge → cluster CD syncs
3. If triggered by `v*` tag, also creates `cli/v*` tag → triggers CLI release

#### CLI Release (`cli-release.yaml`)

Trigger: `cli/v*` tag (auto-created by ci-prod, or manual).

1. Cross-compile Go CLI for 4 platforms (linux/darwin × amd64/arm64)
2. Upload to `s3://investibook-build/releases/<version>/` and `s3://investibook-build/latest/`
3. Update `s3://investibook-build/version.json` for auto-update checks

### Flow Diagram

```
main push → ci-dev → build images (ECR dev) → kubectl apply -k (direct deploy to devops-staging)

release-*/v* → ci-prod → build images (ECR prod) → trigger app-delivery bump PR (GitOps)
                                                  └→ (v* tag) create cli/v* tag

cli/v* tag → cli-release → cross-compile → upload S3 + GitHub Release
```

## 2. Accessing Clusters

### Dev Cluster

```bash
aws eks update-kubeconfig --name devops-staging --region us-west-2 --profile <dev-profile>
```

### Prod Cluster

```bash
aws eks update-kubeconfig --name devops-prod --region us-west-2 --profile clinic-cloud-prod
```

Context name after setup: `arn:aws:eks:us-west-2:557537366020:cluster/devops-prod`

### Quick Check

```bash
# Verify connectivity
kubectl --context arn:aws:eks:us-west-2:557537366020:cluster/devops-prod -n investibook get pods

# Tail logs
kubectl --context arn:aws:eks:us-west-2:557537366020:cluster/devops-prod \
  -n investibook logs deploy/backend --since=1h -f

# Filter for errors
kubectl --context arn:aws:eks:us-west-2:557537366020:cluster/devops-prod \
  -n investibook logs deploy/backend --since=6h | grep -i failed
```

## 3. Debugging Agent Execution via Logs

The backend logs agent activity at three levels — all go to container stdout and are indexed in Loki.

### Log Markers

| Marker | Source | What it logs |
|--------|--------|--------------|
| `agent_stderr` | `agent_runtime.py read_stderr()` | Agent subprocess stderr line-by-line (includes tool script progress like `collect_evidence.py` stage timing) |
| `tool_started` | `AgentEventBridge` | Tool call start — title, input preview (first 300 chars) |
| `tool_completed` | `AgentEventBridge` | Tool call end — title, ok/fail, duration, output preview (first 500 chars) |
| `agent prompt send` | `AgentSession.prompt()` | User message sent to the selected runtime |
| `agent prompt response` | `AgentSession.prompt()` | Runtime finished responding |
| `token_usage` | `agent_runtime.py ask_and_wait()` | Token counts per interaction |

### kubectl Examples

```bash
# Shorthand for dev cluster
DEV="kubectl --context arn:aws:eks:us-west-2:552185537300:cluster/devops-staging -n investibook"

# Shorthand for prod cluster
PROD="kubectl --context arn:aws:eks:us-west-2:557537366020:cluster/devops-prod -n investibook"

# See all agent stderr for a workspace (tool script output, progress, errors)
$DEV logs deploy/backend --since=6h | grep 'agent_stderr.*ws-1776402680190'

# See tool execution timeline for a workspace
$DEV logs deploy/backend --since=6h | grep -E 'tool_(started|completed).*ws-1776402680190'

# See collect_evidence.py timing (stage-level progress is emitted to stderr)
$DEV logs deploy/backend --since=6h | grep 'agent_stderr.*ws-1776402680190' | grep -i 'stage\|total\|parallel'

# See all errors
$DEV logs deploy/backend --since=6h | grep -iE 'error|failed|exception'

# Full interaction lifecycle for a workspace
$DEV logs deploy/backend --since=6h | grep 'ws-1776402680190'
```

### Querying Logs via Clinic Grafana Loki

Investibook logs are indexed in Grafana Loki datasource `Loki-aws-us-west-2` (cluster `prod-devops`, namespace `investibook`), not the Clinic Data Proxy Loki.

**Authentication**: The `/grafana/` path requires `clinic.session_id` cookie (Clinic API Key does not work — Kong redirects to login).

Get the cookie: log in to [clinic.pingcap.com](https://clinic.pingcap.com) → DevTools → Application → Cookies → copy `clinic.session_id`.

```bash
NOW=$(date +%s)
START=$(( NOW - 6*3600 ))

# Agent stderr for a specific workspace
curl -s 'https://clinic.pingcap.com/grafana/api/ds/query' \
  -H 'accept: application/json' \
  -H 'content-type: application/json' \
  -H 'x-grafana-org-id: 1' \
  -b 'clinic.session_id=<YOUR_SESSION_ID>' \
  --data-raw '{
    "queries": [{
      "datasource": {"uid": "Loki-aws-us-west-2", "type": "loki"},
      "expr": "{cluster_env=\"prod\",cluster=\"prod-devops\",namespace=\"investibook\",container=\"backend\"} |~ \"agent_stderr.*ws-XXXX\"",
      "queryType": "range",
      "refId": "A",
      "maxLines": 500,
      "datasourceId": 16,
      "intervalMs": 20000,
      "maxDataPoints": 1094
    }],
    "from": "'${START}000'",
    "to": "'${NOW}000'"
  }'
```

#### LogQL Label Reference

| Label | Values |
|-------|--------|
| `cluster_env` | `prod` |
| `cluster` | `prod-devops` |
| `namespace` | `investibook` |
| `container` | `backend`, `frontend` |
| `app` | `backend`, `frontend` |

#### Useful LogQL Patterns

```logql
# Tool execution timeline
{namespace="investibook",container="backend"} |~ "tool_(started|completed).*ws-XXXX"

# Agent stderr (script output)
{namespace="investibook",container="backend"} |~ "agent_stderr.*ws-XXXX"

# All errors
{namespace="investibook",container="backend"} |~ "(?i)error|failed|exception"

# Token usage
{namespace="investibook",container="backend"} |~ "token_usage.*ws-XXXX"
```

#### Parsing the Loki Response

The Grafana ds/query response uses a frame format:

```
results.A.frames[].data.values[0]  → labels (array of dicts, one per log line)
results.A.frames[].data.values[1]  → timestamps (ms since epoch)
results.A.frames[].data.values[2]  → log lines (strings)
results.A.frames[].data.values[3]  → tsNs (nanosecond timestamps)
results.A.frames[].data.values[4]  → id
```

```python
import json, sys, datetime

data = json.load(sys.stdin)
frames = data.get("results", {}).get("A", {}).get("frames", [])
for frame in frames:
    values = frame.get("data", {}).get("values", [])
    labels_list, timestamps, lines = values[0], values[1], values[2]
    for ts, line, lbl in zip(timestamps, lines, labels_list):
        t = datetime.datetime.fromtimestamp(
            ts / 1000, tz=datetime.timezone.utc
        ).strftime("%Y-%m-%d %H:%M:%S UTC")
        pod = lbl.get("instance", "?")
        print(f"[{t}] [{pod}] {line[:500]}")
```

## 4. Querying the SQLite Database (Fallback)

For data not available in logs (e.g. full tool output, message history), query the SQLite DB directly.

### Database Location

| Environment | Path on Pod | PVC |
|---|---|---|
| Dev | `/data/db/investibook.db` | `investibook-data` |
| Prod | `/data/db/investibook.db` | `investibook-data` |

`DATA_DIR` defaults to `/data` in K8s (set in backend-deployment.yaml). DB path = `$DATA_DIR/db/investibook.db`.

Workspace-local diagnostics now persist under:
- OpenCode: `/data/workspaces/<workspace_id>/.opencode/diagnostic.jsonl`
- Investibook backend: `/data/workspaces/<workspace_id>/.investibook/diagnostic.jsonl`

These files are useful when backend logs show a tool/task hung before reporting terminal state, or when the centralized pod-log/Loki path is incomplete.

### Accessing the DB

```bash
# Copy DB from pod to local machine
$DEV cp deploy/backend:/data/db/investibook.db /tmp/investibook-dev.db

# Or exec into pod directly
$DEV exec -it deploy/backend -- sqlite3 /data/db/investibook.db
```

### Schema Overview

| Table | Primary Key | Purpose |
|-------|------------|---------|
| `workspaces` | `id` (e.g. `ws-...`) | Workspace metadata (name, user_id, created_at) |
| `messages_v2` | `(workspace_id, id)` | Chat messages (role, content, parts JSON, created_at) |
| `messages_v2_meta` | `workspace_id` | Message count per workspace |
| `workspace_sessions` | `workspace_id` | ACP session ID mapping |
| `workspace_sources` | `workspace_id` | Sources config (JSON blob in `data` column) |
| `workspace_artifacts` | `workspace_id` | Artifacts config (JSON blob) |
| `interaction_token_usage` | `id` | Per-interaction token usage (input/output/cache tokens, cost, duration) |
| `workspace_diagnostics` | `workspace_id` | Auto-diagnosis state (status, questions, callback) |
| `users` | `id` | User accounts |
| `user_sessions` | `token` | Auth session tokens |
| `api_keys` | `id` | API key auth |
| `workspace_members` | `(workspace_id, user_id)` | RBAC (owner/editor/viewer) |

### Common Queries

```sql
-- List recent workspaces
SELECT id, name, created_at FROM workspaces ORDER BY created_at DESC LIMIT 10;

-- Get messages for a workspace (tool outputs are in the `parts` JSON column)
SELECT id, role, substr(content, 1, 200) AS preview, created_at
FROM messages_v2
WHERE workspace_id = 'ws-XXXX'
ORDER BY created_at;

-- Extract tool output from a specific message
-- The `parts` column is a JSON array; tool results have type="tool"
SELECT id, json_extract(parts, '$') AS parts
FROM messages_v2
WHERE workspace_id = 'ws-XXXX' AND role = 'assistant'
ORDER BY created_at DESC LIMIT 5;

-- Find tool outputs containing specific text (e.g. collect_evidence timing)
SELECT m.id, m.created_at,
       json_extract(value, '$.title') AS tool_title,
       substr(json_extract(value, '$.output'), 1, 500) AS output_preview
FROM messages_v2 m, json_each(m.parts)
WHERE m.workspace_id = 'ws-XXXX'
  AND m.role = 'assistant'
  AND json_extract(value, '$.type') = 'tool'
ORDER BY m.created_at;

-- Token usage for a workspace
SELECT model_tier, channel, duration_s,
       input_tokens, output_tokens, cache_read_tokens,
       cost_amount, cost_currency, created_at
FROM interaction_token_usage
WHERE workspace_id = 'ws-XXXX'
ORDER BY created_at;

-- Workspace diagnostic status
SELECT workspace_id, alert_id, status, updated_at
FROM workspace_diagnostics
WHERE workspace_id = 'ws-XXXX';
```

### Analyzing Tool Performance from DB

To find slow tool calls for a workspace (useful when logs are not available):

```bash
# Copy DB locally, then:
sqlite3 /tmp/investibook-dev.db <<'SQL'
.mode column
.headers on
SELECT m.id, m.created_at,
       json_extract(value, '$.title') AS tool,
       json_extract(value, '$.duration_ms') AS ms,
       CASE WHEN json_extract(value, '$.status') = 'failed' THEN 'FAIL' ELSE 'ok' END AS status,
       substr(json_extract(value, '$.output'), 1, 200) AS output_head
FROM messages_v2 m, json_each(m.parts)
WHERE m.workspace_id = 'ws-XXXX'
  AND m.role = 'assistant'
  AND json_extract(value, '$.type') = 'tool'
  AND json_extract(value, '$.duration_ms') IS NOT NULL
ORDER BY CAST(json_extract(value, '$.duration_ms') AS INTEGER) DESC
LIMIT 20;
SQL
```

## 5. Prod Configuration Reference

From `app-delivery` repo `devops-service/investibook/envs/preview/kustomization.yaml`:

| Config Key | Value |
|------------|-------|
| `DEVOPS_BASE_URL` | `https://prod.devops.pingcap.com` |
| `INVESTIBOOK_AIOPS_API_URL` | `http://aiops-server.aiops-prod:8090` |
| `INVESTIBOOK_S3_BUCKET` | `tidbcloud-aiops-assets-prod` |
| `INVESTIBOOK_AWS_REGION` | `us-west-2` |
| `INVESTIBOOK_EXPORT_S3_PREFIX` | `investibook_service/export_workspaces` |
| `INVESTIBOOK_CLINIC_ENDPOINT` | `https://clinic.pingcap.com` |
| `INVESTIBOOK_JIRA_BASE_URL` | `https://tidb.atlassian.net` |

Image source: `557537366020.dkr.ecr.us-west-2.amazonaws.com/investibook/prod/{frontend,backend}`