# CLAUDE.md — ows-playlist

This file provides guidance for AI assistants working in the ows-playlist repository.

## Project Overview

**ows-playlist** is a Python Flask REST API that serves as the Data Access Layer (DAL) to Snowflake for playlist and placement data. It provides playlist positions, placement history, timeseries data, and sound recording analytics. Consumed by `graphql-analytics` and other internal services.

## Essential Commands

```bash
# Development
python dev.py               # Dev server with hot-reload

# Testing
make test_unit              # pytest unit tests with coverage
make test_integration       # Integration tests (requires Snowflake)
make test                   # All tests

# Linting
make lint                   # flake8 + isort + black check

# Full CI job
make unit_lint_job          # pip_dev + lint + test_unit
make integration_job        # Integration tests against QA
```

To run a single test file:
```bash
pytest tests/unit/test_handlers.py -v
pytest tests/unit/test_handlers.py::test_specific_function -v
```

## Setup

Copy `.env.shadow` to `.env` before running locally: `cp .env.shadow .env`

Snowflake access requires private key in `~/.ssh/snowflake/` (local) or AWS Secrets Manager (QA/prod).

## Architecture

### Tech Stack

| Layer | Technology |
|-------|-----------|
| Framework | Flask 3.1.1 |
| Language | Python 3.11 |
| Database | Snowflake (via snowflake-sqlalchemy 1.7.3 + snowflake-connector-python 3.15) |
| ORM | SQLAlchemy 1.4.54 |
| SQL Templating | JinjaSQL (Jinja2-based SQL with named parameters) |
| Validation | Marshmallow 3.26 |
| Caching | Redis (with FakeRedis fallback) |
| Package Manager | pip (requirements.txt + requirements-dev.txt) |
| APM | Datadog (ddtrace 3.12) |
| Error Tracking | Sentry (raven) |
| Authorization | owsrequest 2.7 (Grass/ORCHARD headers) |
| Feature Flags | pythonfeatures 4.0 |
| Async | flask-executor (parallel query execution) |

### Directory Structure

```
playlist/
├── api.py                       # Flask app setup
├── config.py                    # Environment configuration
├── handlers.py                  # Main request handlers
├── sound_recording_handlers.py  # Sound recording-specific handlers
├── features.py                  # Feature flag logic
├── connectors/                  # External service connectors
│   ├── snowflake.py             # Snowflake connection (QueuePool, PKI auth)
│   ├── redis.py                 # Redis cache (FakeRedis fallback)
│   └── sentry.py                # Error tracking
├── constants/                   # Application constants
│   ├── cache.py                 # Cache TTLs
│   ├── stores.py                # Store identifiers
│   └── handler_constants.py     # Handler-specific constants
├── queries/                     # Data access layer
│   ├── fetch_queries.py         # Query execution functions
│   ├── schema.py                # Marshmallow schemas
│   ├── constants.py             # Query constants
│   ├── formatting.py            # Response formatting
│   ├── placements/              # Placement-specific queries + SQL templates
│   │   └── sql/macros/          # JinjaSQL macros
│   ├── playlist/                # Playlist queries
│   ├── misc/                    # Miscellaneous queries
│   └── time_series/             # Time series queries
├── services/                    # Business logic
│   └── ows_permissions.py       # Permission management
└── utils/                       # Utility functions
    ├── handler_utils.py
    └── cache.py                 # @cache_in_redis decorator

tests/
├── unit/                        # Unit tests (mock Snowflake, Redis)
│   ├── conftest.py              # Autouse fixtures
│   └── connectors/
└── integration/                 # Integration tests

spec/                            # OpenAPI specifications
```

### Data Flow

```
HTTP Request → owsrequest (auth + Grass headers) → Handler → Query → JinjaSQL template → Snowflake
                                                       │        ↑
                                                       │  @cache_in_redis
                                                       ↓
                                                 Marshmallow → JSON Response (via oto)
```

## Key Patterns

### JinjaSQL Query Templating

Queries are written as Jinja2 SQL templates with named parameters. This is different from `ows-charts` (ORM) and `ows-analytics` (direct connector).

- SQL templates in `queries/placements/sql/` and subdirectories
- Jinja2 macros in `queries/placements/sql/macros/`
- `prepare_query()` renders template with parameters
- `execute()` runs against Snowflake with ddtrace wrapping

### Abstract SnowflakeQuery Base Class

Query classes inherit from a base with:
- `filename` property — path to SQL template file
- `query_schema()` — Marshmallow schema for validation
- `prepare_query()` — JinjaSQL template + parameter substitution
- `execute()` — wrapped with ddtrace
- Automatic table name prefixing with `v_` for views

### Redis Caching Decorator

```python
@cache_in_redis(ttl=3600)
def get_placement_data(params):
    ...
```

The `@cache_in_redis` decorator handles cache lookup, serialization, and TTL management.

### Grass Headers Authorization

`owsrequest` extracts authorization context from Grass headers:
- Account type & ID
- Profile ID
- Permissions loaded from DynamoDB via `get_permissions()`

### Flask-Executor for Parallelism

`flask-executor` enables parallel Snowflake query execution within a single request, useful for endpoints that aggregate multiple independent queries.

### Snowflake Connection

- **Auth**: Private key pair (PKI) — passphrase from Secrets Manager (QA/prod) or local SSH key (dev)
- **Pool**: QueuePool with 15 connections, 4hr recycle
- **Disabled rollback**: SELECT-only queries skip rollback
- **Database/schema**: Passed to all queries as config parameters

## Environment Variables

| Variable | Purpose |
|----------|---------|
| `ENV` | `qa` / `prod` |
| `SNOWFLAKE_ACCOUNT` | Snowflake account |
| `SNOWFLAKE_USER` | Snowflake username |
| `SNOWFLAKE_ROLE` | Snowflake role |
| `SNOWFLAKE_WAREHOUSE` | Snowflake warehouse |
| `SNOWFLAKE_DATABASE` | Snowflake database |
| `SNOWFLAKE_SCHEMA` | Snowflake schema |
| `REDIS_HOST` | Redis host |
| `SPLIT_API_KEY` | Split.io feature flags |
| `SENTRY_DSN` | Sentry error tracking |

## Testing

- **Unit tests**: Autouse fixtures mock Snowflake execute, Redis cache, DB config, and feature flags
- **Markers**: `disable_mock_cache`, `disable_mock_execute` to override default mocks
- **Coverage target**: 80%+ (enforced in CI)
- **Integration tests**: Run against real Snowflake (separate environment)

## Docker

Multi-stage: `base` (python:3.11-slim-bullseye) → `deploy` (uWSGI) | `pr_tests` (CI).

Pre-commit hooks: isort, black, flake8.