# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

This is a **Streamlit application deployed to Snowflake Container Runtime** that provides audit log visualization for user permission changes. The application queries CDC (Change Data Capture) tables in Snowflake to track permission access relationships.

**Key Technology Stack:**
- Python 3.11+
- Streamlit for UI
- Snowflake Snowpark Python for database queries
- UV for package management
- Ruff for code formatting/linting
- Mypy for type checking

## Development Commands

### Environment Setup
```bash
# Initialize UV environment and install all dependencies
make init

# Install only dev dependencies (if already installed)
make install-dev
```

### Code Quality
```bash
# Format code (single quotes, 100 char lines)
make fmt

# Check linting errors
make lint

# Auto-fix linting issues
make lint-fix

# Type checking with mypy (strict mode)
make type-check

# Run all checks (lint + type-check)
make check
```

### Cleanup
```bash
# Remove build artifacts, cache, and venv
make clean
```

## Architecture: Decomposed Query Approach

This application uses a **decomposed query architecture** instead of a single monolithic query. This is the most important architectural decision in the codebase.

### Query Execution Flow

The query execution happens in numbered steps across `database_module.py`:

1. **Step 1**: Load identity from `FACT.PROD.IDENTITY` (`01_get_identity.sql`)
2. **Step 2**: Load profiles from CDC table `CDC_MUSICGRAPH_HASPROFILE` (`02a_get_profiles.sql`)
3. **Step 2.5**: Hydrate profiles with complete metadata from `FACT.PROD.PROFILE` (`02b_resolve_uuids_to_profiles.sql`)
4. **Step 3a-3d**: Query 4 access tables in parallel:
   - `HAS_ACCESS_TO` (`04a_get_has_access_to.sql`)
   - `HAS_ADMIN_ACCESS_TO` (`04b_get_has_admin_access_to.sql`)
   - `DELETED_HAS_ACCESS_TO` (`04c_get_deleted_has_access_to.sql`)
   - `DELETED_HAS_ADMIN_ACCESS_TO` (`04d_get_deleted_has_admin_access_to.sql`)
5. **Step 3.5**: Hydrate resource metadata (Collaborators, Vendors, Subaccounts, LabelParticipants):
   - `05a_hydrate_collaborators.sql`
   - `05b_hydrate_vendors.sql`
   - `05c_hydrate_subaccounts.sql`
   - `05d_hydrate_label_participants.sql`
6. **Step 4**: Combine and sort results by timestamp

### Why Decomposed Queries?

- **Debuggability**: Each data source can be tested independently
- **Error Isolation**: Know exactly which table/query is failing
- **Visibility**: Users see progress and results for each step
- **Data Enrichment**: Handle missing metadata in DELETED tables by hydrating from FACT tables
- **Maintainability**: Easy to modify individual queries

### Module Organization

**Critical**: Each module has a specific responsibility:

1. **`streamlit_app.py`**: Main entry point
   - Orchestrates application flow
   - Manages session state
   - Calls database, processing, UI, and visualization modules in sequence

2. **`database_module.py`**: All database interactions
   - Executes decomposed queries using Snowpark
   - Loads SQL from `.sql` files in `src/` directory
   - Handles profile and resource hydration
   - Implements enrichment logic for DELETED table records

3. **`data_processing.py`**: Data transformation
   - Parses JSON columns (before/after properties)
   - Extracts resource type/ID from target_type
   - CSV export functionality

4. **`ui_components.py`**: Reusable UI components
   - Filters (operation, profile type, date range)
   - Data tables with styling
   - Event details views
   - Tenant selectors

5. **`visualization_module.py`**: Charts and metrics
   - Plotly visualizations
   - Timeline charts
   - Summary metrics

## Key Implementation Details

### SQL Query Files

All SQL queries are stored as separate `.sql` files in the `src/` directory. The `database_module.py` loads these files and performs parameter replacement:

```python
query = load_query_from_file('01_get_identity.sql')
query = query.replace(':identity_id', f"'{identity_id}'")
```

**Important**: Parameter replacement uses simple string replacement, not parameterized queries.

### Session State Management

The application heavily uses `st.session_state` to persist data across reruns:

- `query_executed`: Boolean flag for whether a query has been run
- `audit_data`: Main DataFrame with all audit log entries
- `identity_id`: Currently queried identity UUID
- `query_step_results`: Dictionary storing results from each query step (profiles, access counts, resource metadata, missing profiles)

### Profile Hydration Pattern

The DELETED tables only have UUIDs without profileType/profileId. The hydration pattern fills in missing data:

1. Get profiles from CDC (may have incomplete data)
2. Query `FACT.PROD.PROFILE` to resolve UUIDs → (type, id)
3. Merge resolved data back into events
4. Track missing profiles that couldn't be resolved

See `hydrate_canonical_profiles()` in `database_module.py:235`.

### Tenant Hydration Pattern

Events reference tenants (Vendor, Subaccount, LabelParticipant, Collaborator) by ID. Tenants are hydrated AFTER data processing to get names:

1. Extract unique resource IDs from events (grouped by resource_type)
2. Query corresponding FACT tables (VENDOR, SUBACCOUNT, LABEL_PARTICIPANT, COLLABORATOR)
3. Merge resource names back into events
4. Handle special case: Vendor ID '*' means "All Orchard Labels"

**Critical Order**: Must call `hydrate_and_store_tenants()` AFTER `process_audit_data()` because resource_type column is created during processing.

## Snowflake Container Runtime Specifics

This app runs inside Snowflake Container Runtime, not locally:

- Use `get_active_session()` instead of creating a new connection
- No environment variables needed for database credentials
- The Streamlit app entry point is `src/streamlit_app.py`
- Session is cached with `@st.cache_resource`

## Code Style Conventions

- **Quotes**: Single quotes everywhere (enforced by Ruff)
- **Line Length**: 100 characters max
- **Type Hints**: Required (mypy strict mode)
- **Imports**: Combine as imports (e.g., `from foo import bar, baz`)
- **Column Names**: Normalize to lowercase immediately after querying (`.columns.str.lower()`)

## Common Development Patterns

### Adding a New Query Step

1. Create SQL file in `src/` (e.g., `06_new_query.sql`)
2. Add function in `database_module.py` to load and execute it
3. Add parameter replacement for any dynamic values
4. Add expander in `query_audit_log_decomposed()` to show progress
5. Store results in `step_results` dictionary if needed later

### Adding a New Filter

1. Add filter UI in `ui_components.py` `render_filters()`
2. Apply filter to DataFrame before returning
3. Update filtered count display in sidebar

### Adding a New Visualization

1. Create function in `visualization_module.py`
2. Use Plotly for all charts (consistency)
3. Call from `render_visualizations()` or inline in `streamlit_app.py`

## Important Gotchas

1. **Column Name Case**: Always normalize to lowercase after querying Snowflake
2. **Query Order**: Must hydrate profiles (Step 2.5) before querying access tables
3. **Tenant Hydration Order**: Must call after data processing creates resource_type column
4. **Wildcard Vendors**: Vendor ID '*' is valid and means "all vendors" - don't filter it out
5. **Numeric Vendor IDs**: Vendor IDs are numeric in database, so filter out '*' before querying VENDOR table
6. **Missing Profiles**: Some profiles exist in CDC but not in FACT.PROD.PROFILE - track and display these
7. **Snowpark Session**: Never create a new session, always use `get_active_session()`