# OWS Service Agent

Expert agent for working in any `ows-*` Python Flask service (DAL to Snowflake).

## Context

You are working in a Python Flask REST API that serves as a Data Access Layer to Snowflake. These services are consumed by GraphQL backend services via REST connectors.

## Service Identification

Identify which service by directory name:
- `ows-analytics` — Streaming analytics (80+ endpoints, largest OWS service, Pipenv)
- `ows-charts` — Chart rankings and NMF data (Flask-SQLAlchemy ORM, pip)
- `ows-playlist` — Playlist/placement data (JinjaSQL templates, pip)

## Common Architecture

All OWS services follow this layered structure:
```
<service>/
├── api.py              # Flask app setup
├── config.py           # Environment config
├── handlers.py         # Request handlers (REST endpoints)
├── features.py         # Feature flag logic
├── connectors/         # Snowflake, Redis, Sentry
├── constants/          # Stores, dates, feeds
├── models/             # SQLAlchemy models (ows-charts) or query classes
├── schemas/            # Marshmallow response schemas
├── logic/              # Business logic, dataloaders
├── services/           # Service layer
├── queries/            # SQL queries / templates
└── utils/              # Utilities
```

## Key Differences Between Services

| Aspect | ows-analytics | ows-charts | ows-playlist |
|--------|--------------|------------|--------------|
| Query Style | Direct connector | SQLAlchemy ORM | JinjaSQL templates |
| Validation | Marshmallow | Marshmallow + Pydantic | Marshmallow |
| Package Manager | Pipenv | pip | pip |
| Python | 3.14 | 3.11 | 3.11 |
| Type Checking | No mypy | mypy (progressive) | No mypy |

## Key Patterns to Follow

### Adding a New Endpoint

1. Add route in appropriate `*_handlers.py`
2. Create/update Marshmallow schema in `schemas/`
3. Create query logic in `queries/` or `logic/`
4. Add authorization in `access_rules.yml` (ows-analytics) or handler level
5. Add Redis caching with `@cache_in_redis(ttl=...)` if appropriate
6. Write unit tests with mocked Snowflake

### Snowflake Query Patterns

**ows-playlist (JinjaSQL)**:
```python
class MyQuery(SnowflakeQuery):
    @property
    def filename(self):
        return 'queries/my_query.sql'

    def query_schema(self):
        return MySchema()
```

**ows-charts (SQLAlchemy ORM)**:
```python
chart = Chart.query.filter_by(chart_id=chart_id).first()
```

**ows-analytics (Direct)**:
Uses direct Snowflake connector with custom SQL.

### Testing Patterns

All services use autouse fixtures that mock:
- Snowflake execution (`disable_mock_execute` marker to override)
- Redis cache (`disable_mock_cache` marker to override)
- Feature flags
- Database config

```python
def test_handler(client, mock_execute):
    mock_execute.return_value = [{'col': 'value'}]
    response = client.get('/endpoint?param=value')
    assert response.status_code == 200
```

### Caching

```python
from utils.cache import cache_in_redis

@cache_in_redis(ttl=3600)
def get_data(params):
    # Snowflake query
    return result
```

## Commands

```bash
python dev.py           # Dev server
make test_unit          # Unit tests
make lint               # flake8
make test_integration   # Integration tests (requires Snowflake)
```

## Common Mistakes to Avoid

- Do NOT use password auth for Snowflake — always use PKI (private key)
- Do NOT skip Marshmallow validation for responses
- Do NOT cache user-specific or time-sensitive data without appropriate TTL
- Do NOT forget to add new endpoints to access_rules.yml (ows-analytics)
- Do NOT write raw SQL without parameterization — use JinjaSQL or SQLAlchemy
- Always mock Snowflake in unit tests — never hit real database
- Pre-commit hooks enforce isort + black + flake8 — format before committing