# insights-ai Usage Guide

This guide explains how to use the insights-ai repository for AI-assisted development across the Orchard Insights platform, and documents the dbt-analytics integration.

---

## Part 1: How to Use insights-ai Effectively

### Quick Start

#### Initial Setup (One Time)

From the `insights-ai` directory, run `setup.sh` to symlink `CLAUDE.md` files into each service repository:

```bash
cd Sony/insights-ai
chmod +x setup.sh

# Preview what will happen
./setup.sh --dry-run

# Create symlinks
./setup.sh
```

This creates symlinks like `Sony/ows-playlist/CLAUDE.md -> Sony/insights-ai/services/ows-playlist.md`. Services that already have their own `CLAUDE.md` (frontend-insights, graphql-analytics) are not overwritten.

#### Daily Development

When you open Claude Code in any service directory, it automatically reads the symlinked `CLAUDE.md` and has full context for that service. No extra steps required.

For cross-service work, open Claude Code from the `insights-ai/` directory so it reads the root `CLAUDE.md` with the full platform architecture and agent definitions.

#### Directory Layout

**CRITICAL REQUIREMENT:** `insights-ai` must be a sibling of all service repositories. This layout is required by `setup.sh` and cross-service scripts.

```
Sony/                                      # Common parent (e.g., Sony/, ~/projects/, etc.)
├── insights-ai/              # This repo (AI context hub) — MUST BE HERE
├── frontend-insights/        # React frontend          -> CLAUDE.md symlinked
├── orchard-suite/            # Shared component monorepo -> CLAUDE.md symlinked
├── graphql-router/           # Federation gateway       -> CLAUDE.md symlinked
├── graphql-analytics/        # Analytics subgraph       -> has own CLAUDE.md
├── graphql-knowledge-search/ # Search subgraph          -> CLAUDE.md symlinked
├── graphql-knowledge/        # Knowledge graph subgraph -> CLAUDE.md symlinked
├── graphql-product/          # Product catalog subgraph -> CLAUDE.md symlinked
├── graphql-user/             # User management subgraph -> CLAUDE.md symlinked
├── ows-analytics/            # Analytics DAL            -> CLAUDE.md symlinked
├── ows-charts/               # Charts DAL               -> CLAUDE.md symlinked
├── ows-playlist/             # Playlist DAL             -> CLAUDE.md symlinked
└── dbt-analytics/            # Data transformations     -> CLAUDE.md symlinked
```

**Why?** The `setup.sh` script uses `$(dirname "$SCRIPT_DIR")` to find the parent directory and locate service repos. If `insights-ai` is elsewhere, symlinks and cross-service coordination will fail.

---

### Workflow Examples

#### Scenario 1: "I need to add a new streaming metric from Snowflake to the frontend"

This is a full-stack, bottom-up change spanning four layers. Use the **cross-service** agent.

**Step 0 -- Verify the dbt layer:**
Does the metric already exist as an aggregated table in Snowflake, or does it need a new dbt model?

```
# Open Claude Code in dbt-analytics/
# Ask: "Does a model exist that aggregates [metric] by [dimensions]?"
# If not, create the dbt model first (see template: new-dbt-model.md)
```

**Step 1 -- OWS service (Python):**
```
# Open Claude Code in ows-analytics/ (or whichever OWS service)
# The CLAUDE.md is already symlinked with full context
# Ask: "Add a new endpoint that queries STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY"
```
Follow the checklist in `templates/new-ows-endpoint.md`.

**Step 2 -- GraphQL subgraph (Node.js):**
```
# Open Claude Code in graphql-analytics/
# Ask: "Add a streamsByCountry field that calls the new OWS endpoint"
```
Follow the checklist in `templates/new-graphql-field.md`.

**Step 3 -- Verify federation:**
```
cd graphql-router && make supergraph
```

**Step 4 -- Frontend (React):**
```
# Open Claude Code in frontend-insights/
# Ask: "Add streamsByCountry to the product analytics page"
```

**Deployment order:** dbt-analytics (if changed) -> OWS -> GraphQL subgraph -> Router (automatic) -> Frontend.

---

#### Scenario 2: "I need to debug why playlist data isn't showing correctly"

Trace the data flow from bottom to top.

**Step 1 -- Check dbt models:**
```
# Open Claude Code in dbt-analytics/
# Ask: "Show me the model that produces the playlist aggregation tables"
# Verify: dbt test, check for recent failures in CI
```

**Step 2 -- Check OWS response:**
```
# Open Claude Code in ows-playlist/
# Ask: "What SQL query does the /playlist/positions endpoint use?"
# Test locally: python dev.py, then curl the endpoint
```

**Step 3 -- Check GraphQL resolver:**
```
# Open Claude Code in graphql-analytics/
# Ask: "How does the playlistPositions resolver call ows-playlist?"
# Check: Zod schema transformations, DataLoader caching
```

**Step 4 -- Check frontend query:**
```
# Open Claude Code in frontend-insights/
# Ask: "Show me the .gql query and data transformation for playlist positions"
```

Use the **cross-service** agent when coordinating across layers.

---

#### Scenario 3: "I need to add a new GraphQL field to the product subgraph"

Single-service change (unless new OWS data is needed).

```
# Open Claude Code in graphql-product/
# CLAUDE.md provides full service context
# Ask: "Add a releaseYear field to the Product type"
```

Follow `templates/new-graphql-field.md`. The agent knows to:
1. Add field to `.graphql` schema
2. Update mapper Key interface if needed
3. Write resolver
4. Run `yarn generate:types`
5. Write unit tests
6. Verify federation with `cd ../graphql-router && make supergraph`

---

#### Scenario 4: "I need to modify a dbt model and verify downstream OWS queries still work"

Use the **dbt-service** agent for the model change, then verify downstream.

**Step 1 -- Modify the dbt model:**
```
# Open Claude Code in dbt-analytics/
# Ask: "Add a new column TOTAL_SAVES to STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY"
```

**Step 2 -- Test the dbt model:**
```bash
cd dbt-analytics
make run ARGS="--select model_name"
make test_integration ARGS="--select model_name"
```

**Step 3 -- Verify downstream OWS queries:**
```
# Open Claude Code in ows-analytics/
# Ask: "Which queries reference STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY?"
# Verify those queries still work with the schema change
# If adding a column: OWS queries using SELECT * will pick it up automatically
# If renaming/removing a column: update all downstream queries
```

**Step 4 -- If OWS response changes, propagate upstream:**
Follow the standard bottom-up flow (OWS -> GraphQL -> Frontend).

---

### Agent Selection Guide

```
What are you working on?
|
+-- Single dbt model or Snowflake transformation?
|   -> Use: agents/dbt-service.md
|
+-- Single OWS endpoint (Python/Flask/Snowflake)?
|   -> Use: agents/ows-service.md
|
+-- Single GraphQL subgraph (Node.js/Apollo)?
|   -> Use: agents/graphql-service.md
|
+-- GraphQL Router (Rust/Apollo Router)?
|   -> Use: agents/router-service.md
|
+-- Frontend React component or page?
|   -> Use: agents/frontend-service.md
|
+-- GraphQL schema change affecting federation?
|   -> Use: agents/schema-reviewer.md
|
+-- Feature spanning multiple services?
|   -> Use: agents/cross-service.md
|
+-- dbt model change with downstream OWS/GraphQL impact?
    -> Use: agents/cross-service.md (includes dbt layer)
```

| Agent | Best For | Language |
|-------|----------|----------|
| `dbt-service` | dbt model development, testing, optimization, macros | SQL / Python |
| `ows-service` | OWS endpoint development, Snowflake queries, Flask routes | Python |
| `graphql-service` | GraphQL field/resolver development, connectors, DataLoaders | TypeScript |
| `router-service` | Apollo Router plugins, supergraph composition, Rust code | Rust |
| `frontend-service` | React pages, components, Apollo Client queries | TypeScript |
| `schema-reviewer` | Reviewing GraphQL schema changes for federation safety | GraphQL SDL |
| `cross-service` | End-to-end features spanning dbt -> OWS -> GraphQL -> Frontend | Multi-language |

---

### Cross-Service Development

#### Coordination Principles

1. **Start from the bottom.** Data changes flow upward: dbt -> OWS -> GraphQL -> Frontend. Always begin at the data source layer.

2. **Each layer must be independently deployable.** A new OWS endpoint should work before the GraphQL subgraph consumes it. A new GraphQL field should be resolvable before the frontend queries it.

3. **Backward compatibility is mandatory.** Never remove or rename fields in OWS responses or GraphQL schemas without a deprecation period. Only add new fields.

4. **Feature flags gate incomplete rollouts.** Use Split.io to gate new features during multi-service deployments. Enable progressively: QA -> UAT -> Prod.

5. **Deployment order matters:**
   ```
   dbt-analytics -> ows-* -> graphql-* subgraph -> graphql-router (automatic) -> frontend
   ```

#### Multi-Repo Workflow with Claude Code

For a feature spanning three or more services:

1. **Plan at the insights-ai root.** Open Claude Code in `insights-ai/` and use the cross-service agent to generate the implementation plan.

2. **Implement bottom-up in separate sessions.** Open Claude Code in each service directory individually. The symlinked CLAUDE.md gives full local context.

3. **Test each layer before moving up.** Run the service-specific test suite before implementing the next layer.

4. **Verify federation after GraphQL changes.** Always run `make supergraph` in `graphql-router` after modifying any subgraph schema.

---

### Template Usage

Templates in `templates/` are step-by-step checklists for common development tasks. Reference them when asking Claude Code to implement a task:

| Template | When to Use |
|----------|-------------|
| `new-dbt-model.md` | Adding a new dbt model (aggregation table, view, or rollup) |
| `new-ows-endpoint.md` | Adding a new REST endpoint in any ows-* service |
| `new-graphql-field.md` | Adding a new field to any GraphQL subgraph (end-to-end) |
| `new-component.md` | Adding a new React component in orchard-suite |

You can reference a template directly in your prompt:

```
Follow the checklist in templates/new-dbt-model.md to add a new
STREAMS_BY_LABEL_COUNTRY_FEED_DISTRIBUTOR_DAILY model.
```

---

## Part 2: dbt-analytics Integration

### What Was Added

| File | Purpose |
|------|---------|
| `services/dbt-analytics.md` | Full CLAUDE.md for the dbt-analytics service |
| `agents/dbt-service.md` | Agent definition for dbt model development |
| `rules/dbt-patterns.md` | dbt conventions (materialization, naming, testing, macros) |
| `templates/new-dbt-model.md` | Checklist for adding a new dbt model |

### Updates Applied to Existing Files

| File | Change |
|------|--------|
| `CLAUDE.md` | Added dbt-analytics to architecture diagram, service registry, agent routing, common commands |
| `agents/cross-service.md` | Added dbt-analytics to service locations, workflows, deployment order |
| `setup.sh` | Added dbt-analytics to symlink targets |

---

## Part 3: Updated Architecture

### Complete Data Flow

```
                    +---------------------+
                    |  frontend-insights   |  React 18 / TS 4.9 / Apollo Client 3
                    |   orchard-suite      |  Monorepo: 71 shared packages (React/TS)
                    +---------+-----------+
                              | GraphQL
                    +---------v-----------+
                    |   graphql-router     |  Apollo Router 2.12 (Rust 1.94)
                    |  (17 federated       |  Custom plugins: auth, client-name
                    |   subgraphs)         |
                    +---------+-----------+
           +---------+-------+-------+----------+
           v         v       v       v          v
     graphql-   graphql-  graphql- graphql-  graphql-
     analytics  knowledge knowledge product   user
                          -search
           |         |       |       |          |
           v         v       v       v          v
     OWS services  Neo4j  OpenSearch 25+ OWS  Neo4j
     (REST)       Snowflake         services  (Cypher)
           |
     +-----+-----+
     v     v     v
   ows-  ows-  ows-
   analytics charts playlist
     |     |     |
     v     v     v
   Aggregated Tables (Snowflake)
   STREAMS_BY_*_DAILY, METRICS_BY_*_ROLLUP, V_STREAMS_BY_*
              ^
              | transforms (606+ models)
     +--------+--------+
     |  dbt-analytics   |  dbt-core 1.9 / Python 3.11 / 14 packages
     +--------+--------+
              | reads
              v
   Raw FACT_* Tables (Snowflake Data Warehouse)
```

### Layer Responsibilities

| Layer | Service(s) | Responsibility | Technology |
|-------|-----------|----------------|------------|
| **Data Ingestion** | External pipelines | Load raw data into FACT_* tables | Snowflake, S3, Kafka |
| **Data Transformation** | dbt-analytics | Transform raw tables into aggregated tables, views, rollups | dbt-core 1.9, SQL, Python 3.11 |
| **Data Access** | ows-analytics, ows-charts, ows-playlist | REST API over aggregated Snowflake tables | Flask, Python, Marshmallow |
| **API Aggregation** | graphql-analytics, graphql-product, etc. | Federated GraphQL over OWS + other backends | Apollo Server, Node.js, TypeScript |
| **API Gateway** | graphql-router | Federation composition, auth, routing | Apollo Router, Rust |
| **Presentation** | frontend-insights, orchard-suite | User interface for music analytics | React, TypeScript |

### Key Integration Points (dbt <-> OWS)

| dbt Output Table Pattern | Consumed By | OWS Endpoint Example |
|--------------------------|-------------|---------------------|
| `STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY` | ows-analytics | `/product/<id>/streams-by-country` |
| `STREAMS_BY_PARTICIPANT_FEED_DISTRIBUTOR_DAILY` | ows-analytics | `/participant/<id>/streams` |
| `METRICS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_ROLLUP` | ows-analytics | `/product/<id>/metrics` |
| `V_STREAMS_BY_SOUND_RECORDING` | ows-analytics | `/sound-recording/<isrc>/streams` |
| Playlist aggregation tables | ows-playlist | `/playlist/positions` |
| Chart ranking tables | ows-charts | `/charts/<id>/rankings` |

When modifying a dbt model, always check which OWS services read the output table. Use this mapping to determine the blast radius of a change.