.cursorrules

# schemax - Project Context

## Overview
schemax is a comprehensive toolkit for managing Databricks Unity Catalog schemas using a declarative, version-controlled approach. It consists of a VS Code extension for visual schema design and a Python SDK/CLI for automation and CI/CD integration.

## 🔥 Quick Rules (Always Follow)

1. **Type Annotations**: ALL Python functions must have parameter and return types (see Python section)
2. **Formatting**: Ruff format with 100 char line length (auto on save)
3. **Testing**: Run pytest before commit (169 passed, 12 skipped)
4. **Logging**: Use `outputChannel.appendLine()` in extension (never `console.log()`)
5. **Immutability**: Never mutate state directly, always create new objects

## Repository Structure (Monorepo)

```
schemax/
├── packages/
│   ├── vscode-extension/       # VS Code Extension (TypeScript + React)
│   │   ├── src/
│   │   │   ├── extension.ts           # Extension commands
│   │   │   ├── storage-v4.ts          # V4 file storage (multi-environment)
│   │   │   ├── providers/             # Provider-based architecture
│   │   │   │   ├── base/             # Base provider contracts
│   │   │   │   │   ├── models.ts     # Zod schemas
│   │   │   │   │   ├── operations.ts # Operation definitions
│   │   │   │   │   └── sql-generator.ts # SQL generation
│   │   │   │   └── unity/            # Unity Catalog provider
│   │   │   │       ├── sql-generator.ts
│   │   │   │       ├── state-reducer.ts
│   │   │   │       └── operations.ts
│   │   │   └── webview/               # React UI
│   │   └── package.json
│   │
│   └── python-sdk/             # Python SDK & CLI
│       ├── src/schemax/
│       │   ├── core/                  # Core infrastructure (NEW)
│       │   │   ├── storage.py         # V4 file storage (multi-environment)
│       │   │   ├── deployment.py      # Deployment tracking
│       │   │   └── version.py         # Semantic versioning utilities
│       │   ├── providers/             # Provider-based architecture
│       │   │   ├── base/             # Base provider contracts
│       │   │   │   ├── models.py     # Pydantic models
│       │   │   │   ├── operations.py # Operation types
│       │   │   │   ├── sql_generator.py  # SQL generation
│       │   │   │   ├── state_differ.py   # State diffing
│       │   │   │   ├── provider.py   # Provider protocol
│       │   │   │   ├── executor.py   # SQL execution protocol
│       │   │   │   └── reverse_generator.py  # Safety validation
│       │   │   ├── unity/            # Unity Catalog provider
│       │   │   │   ├── models.py
│       │   │   │   ├── operations.py
│       │   │   │   ├── sql_generator.py
│       │   │   │   ├── state_reducer.py
│       │   │   │   ├── state_differ.py
│       │   │   │   ├── provider.py
│       │   │   │   ├── auth.py       # Databricks authentication
│       │   │   │   ├── executor.py   # Databricks SQL executor
│       │   │   │   └── safety_validator.py  # Rollback safety
│       │   │   └── registry.py       # Provider registry
│       │   ├── commands/              # CLI command modules
│       │   │   ├── apply.py          # Apply command (with auto-rollback)
│       │   │   ├── rollback.py       # Rollback command (NEW)
│       │   │   ├── snapshot_rebase.py # Snapshot rebase (NEW)
│       │   │   ├── sql.py            # SQL generation command
│       │   │   ├── validate.py       # Validation command
│       │   │   ├── diff.py           # Diff command
│       │   │   └── deployment.py     # Deployment recording
│       │   └── cli.py                 # CLI routing layer
│       └── pyproject.toml
│
├── examples/                   # Working examples
│   ├── basic-schema/          # Sample project
│   ├── github-actions/        # CI/CD templates
│   └── python-scripts/        # SDK usage examples
│
├── docs/                       # Documentation
│   ├── README.md              # Documentation index
│   ├── QUICKSTART.md          # Getting started guide
│   ├── ARCHITECTURE.md        # Technical design
│   └── DEVELOPMENT.md         # Development guide
│
├── scripts/
│   └── smoke-test.sh          # Quick validation
│
├── .github/workflows/          # CI/CD pipelines
│   ├── extension-ci.yml
│   ├── python-sdk-ci.yml
│   └── integration-tests.yml
│
├── README.md                   # Project overview
├── TESTING.md                  # Testing guide
├── CONTRIBUTING.md             # Contributing guidelines
└── .cursorrules               # This file
```

## Architecture (V4)

### File Structure (.schemax/)
```
project-root/
└── .schemax/
    ├── project.json           # Project metadata (version, snapshots list, settings)
    ├── changelog.json         # Working changes (ops since last snapshot)
    ├── snapshots/
    │   ├── v0.1.0.json       # Full state snapshots
    │   ├── v0.2.0.json
    │   └── v0.3.0.json
    └── migrations/            # Generated SQL files
        └── migration_*.sql
```

### Core Concepts

1. **Operations (Ops)**: Append-only log of all user actions
   - Each op has: `{id, ts, op, target, payload}`
   - Ops are immutable and never edited
   - Examples: `add_catalog`, `rename_column`, `reorder_columns`
   - All ops have UUIDs for tracking

2. **Changelog**: `changelog.json` contains ops since last snapshot
   - Cleared when snapshot is created
   - Tracks `sinceSnapshot` version
   - This is the "working directory" of changes

3. **Snapshots**: Point-in-time full state captures
   - Stored as separate files: `.schemax/snapshots/vX.Y.Z.json`
   - Contains complete state + list of ops included
   - Metadata stored in `project.json`, full state in snapshot file
   - Semantic versioning (v0.1.0, v0.2.0, etc.)

4. **State Loading**: 
   - Load latest snapshot (or start with empty state)
   - Apply changelog ops to get current state
   - This is what the UI displays

5. **SQL Generation**: 
   - Convert changelog ops to SQL DDL
   - Idempotent statements (safe to run multiple times)
   - Saved to `.schemax/migrations/` directory

### Data Models

**Project File** (`project.json` - v4 Schema):
```typescript
{
  version: 4,
  name: string,
  provider: {
    type: string,  // e.g., "unity"
    version: string,  // e.g., "1.0.0"
    environments: {
      [envName: string]: {
        topLevelName: string,  // Physical catalog name (for Unity Catalog)
        description?: string,
        allowDrift: boolean,
        requireSnapshot: boolean,
        requireApproval?: boolean,
        autoCreateTopLevel: boolean,
        autoCreateSchemaxSchema: boolean
      }
    }
  },
  managedLocations: {  // Project-level managed locations (for catalog/schema MANAGED LOCATION)
    [locationName: string]: {
      paths: {
        [envName: string]: string  // Environment-specific physical paths
      },
      description?: string
    }
  },
  externalLocations: {  // Project-level external locations (for external tables)
    [locationName: string]: {
      paths: {
        [envName: string]: string  // Environment-specific physical paths
      },
      description?: string
    }
  },
  snapshots: SnapshotMetadata[], // Just metadata, references to files
  deployments: Deployment[],
  settings: ProjectSettings,
  latestSnapshot: string | null  // version string
}
```

**Note:** v4 refactors locations from environment-specific to project-level with per-environment paths, enabling consistent location names across environments while allowing different physical paths.

**Changelog File** (`changelog.json`):
```typescript
{
  version: 1,
  sinceSnapshot: string | null,  // version
  ops: Op[],
  lastModified: string  // ISO timestamp
}
```

**Snapshot File** (`.schemax/snapshots/vX.Y.Z.json`):
```typescript
{
  id: string,
  version: string,
  name: string,
  ts: string,
  createdBy: string,
  state: { catalogs: Catalog[] },  // Full state
  opsIncluded: string[],  // Op IDs
  previousSnapshot: string | null,
  hash: string,  // SHA-256 for integrity
  tags: string[],
  comment?: string
}
```

**Unity Catalog Model** (Enhanced with Governance & Storage Features):
```
Catalog
├── id, name
├── managedLocationName?: string                       // Reference to project-level managed location
├── Schema[]
    ├── id, name
    ├── managedLocationName?: string                   // Reference to project-level managed location
    └── Table[]
        ├── id, name, format (delta/iceberg)
        ├── external?: boolean                         // Is external table
        ├── externalLocationName?: string              // Reference to project-level external location
        ├── path?: string                              // Relative path within external location
        ├── partitionColumns?: string[]                // PARTITIONED BY columns
        ├── clusterColumns?: string[]                  // CLUSTER BY columns (Liquid Clustering)
        ├── columns: Column[]
        │   ├── id, name, type, nullable, comment
        │   ├── tags?: Record<string, string>         // Column-level tags
        │   └── maskId?: string                        // Reference to column mask
        ├── properties: Record<string, string>         // TBLPROPERTIES
        ├── constraints: Constraint[]                  // PK, FK, CHECK
        ├── grants: Grant[]
        ├── rowFilters?: RowFilter[]                   // Row-level security
        └── columnMasks?: ColumnMask[]                 // Column masking
```

**Column**:
```typescript
{
  id: string,
  name: string,
  type: string,
  nullable: boolean,
  comment?: string,
  tags?: Record<string, string>,    // { tag_name: tag_value }
  maskId?: string                    // Reference to active mask
}
```

**Constraint** (Supports PRIMARY KEY, FOREIGN KEY, CHECK):
```typescript
{
  id: string,
  type: 'primary_key' | 'foreign_key' | 'check',
  name?: string,                    // CONSTRAINT name
  columns: string[],                // column IDs
  
  // For PRIMARY KEY
  timeseries?: boolean,             
  
  // For FOREIGN KEY
  parentTable?: string,             
  parentColumns?: string[],         
  matchFull?: boolean,
  onUpdate?: 'NO_ACTION',
  onDelete?: 'NO_ACTION',
  
  // For CHECK
  expression?: string,              // SQL expression
  
  // Constraint options (all types)
  notEnforced?: boolean,            
  deferrable?: boolean,
  initiallyDeferred?: boolean,
  rely?: boolean                    // For Photon query optimization
}
```

**RowFilter** (Row-level security):
```typescript
{
  id: string,
  name: string,
  enabled: boolean,
  udfExpression: string,            // e.g., "region = current_user()"
  description?: string
}
```

**ColumnMask** (Column-level masking):
```typescript
{
  id: string,
  columnId: string,
  name: string,
  enabled: boolean,
  maskFunction: string,             // e.g., "REDACT_EMAIL(email)"
  description?: string
}
```

## VS Code Extension

### Storage Layer (`packages/vscode-extension/src/storage-v4.ts`)

**Key Functions**:
- `ensureProjectFile()` - Initialize new v4 project with environments
- `loadCurrentState()` - Load snapshot + apply changelog
- `appendOps()` - Add ops to changelog
- `createSnapshot()` - Create snapshot file, update metadata, clear changelog
- `readProject()`, `readChangelog()`, `readSnapshot()`
- `getEnvironmentConfig()` - Get environment-specific configuration

**Op Reducer**: `applyOpsToState()` applies ops to state immutably

**Catalog Mode**: Supports `single` mode with implicit catalog (`__implicit__`)

### SQL Generator (`packages/vscode-extension/src/providers/unity/sql-generator.ts`)

**UnitySQLGenerator class**:
- `generateSQL(ops: Op[]): string` - Main entry point
- Accepts `catalogNameMapping` for environment-specific SQL
- Private methods for each operation type
- Generates idempotent DDL (CREATE IF NOT EXISTS, etc.)
- Handles all 29 Unity Catalog operations
- Orders SQL by dependency (catalog → schema → table → operations)
- Includes operation tracking in comments

### Extension (`packages/vscode-extension/src/extension.ts`)

**Commands**:
1. `schemax.openDesigner` - Opens React webview
2. `schemax.showLastOps` - Shows changelog ops
3. `schemax.createSnapshot` - Creates new snapshot
4. `schemax.generateSQL` - Generates SQL migration file

**Message Flow**:
- Webview → Extension: `load-project`, `append-ops`
- Extension → Webview: `project-loaded`, `project-updated`

### Webview (`packages/vscode-extension/src/webview/`)

**Architecture**: React + Vite + Zustand

**Components**:
- `App.tsx` - Main layout, message handling
- `Sidebar.tsx` - Tree view with context-aware inline "+" buttons
- `TableDesigner.tsx` - Comprehensive table editor
- `ColumnGrid.tsx` - Inline column editing + tags
- `TableProperties.tsx` - TBLPROPERTIES management
- `TableConstraints.tsx` - PRIMARY KEY, FOREIGN KEY, CHECK
- `SecurityGovernance.tsx` - Row filters & column masks
- `SnapshotPanel.tsx` - Timeline view

**State Management** (`useDesignerStore.ts`):
- Zustand store holds current project state
- All mutations generate ops via `emitOps()`
- Ops sent to extension via `vscode.postMessage()`
- Extension applies ops and sends back updated state

### Build System

**Extension**: esbuild (`esbuild.config.mjs`)
- Bundles `src/extension.ts` → `dist/extension.js`
- External: `vscode` module

**Webview**: Vite (`vite.config.ts`)
- Builds React app → `media/`
- Entry: `src/webview/main.tsx`
- Output: `media/index.html`, `media/assets/index.js`, `media/assets/index.css`

**Scripts**:
- `npm run build` - Build both
- `npm run build:ext` - Extension only
- `npm run build:webview` - Webview only
- `npm run watch` - Watch mode (both)

## Python SDK & CLI

### Package Structure

**Location**: `packages/python-sdk/`

**Core Modules**:
- `storage_v4.py` - V4 file I/O with multi-environment support
- `storage_v3.py` - V3 file I/O (deprecated)
- `deployment_tracker.py` - Database-backed deployment tracking
- `cli.py` - Click-based CLI routing layer

**Provider Architecture** (`providers/`):
- `base/` - Provider contracts (models, operations, SQL generation, execution)
- `unity/` - Unity Catalog implementation
  - `models.py` - Pydantic models
  - `operations.py` - Operation types (29 operations)
  - `sql_generator.py` - SQL DDL generation with catalog mapping
  - `state_reducer.py` - State reducer
  - `provider.py` - Provider implementation
  - `auth.py` - Databricks authentication helpers
  - `executor.py` - SQL execution via Databricks API
- `registry.py` - Provider registry

**Command Modules** (`commands/`):
- `apply.py` - Apply SQL to environment with deployment tracking and auto-rollback
- `rollback.py` - Rollback failed/partial deployments (partial or complete)
- `snapshot_rebase.py` - Rebase snapshots after Git rebase
- `sql.py` - Generate SQL migrations
- `validate.py` - Validate project files and detect stale snapshots
- `diff.py` - Compare snapshots and generate diffs
- `deployment.py` - Manual deployment recording

### CLI Commands

```bash
# Generate SQL migration (with environment-specific catalog mapping)
schemax sql [--output FILE] [--target ENV]

# Apply SQL to environment with deployment tracking
schemax apply --target ENV --profile PROFILE --warehouse-id WAREHOUSE_ID [--sql FILE] [--dry-run] [--no-interaction] [--auto-rollback]

# Rollback a failed/partial deployment
schemax rollback --deployment DEPLOYMENT_ID --partial --target ENV --profile PROFILE --warehouse-id WAREHOUSE_ID [--dry-run]

# Rollback to a previous snapshot (complete rollback)
schemax rollback --to-snapshot VERSION --target ENV --profile PROFILE --warehouse-id WAREHOUSE_ID [--dry-run]

# Validate schema files and detect stale snapshots
schemax validate [workspace_path]

# Create a snapshot from current changelog
schemax snapshot create --name "Snapshot name" [--version v0.2.0] [--comment "Description"] [--tags tag1]

# Validate snapshots after Git rebase
schemax snapshot validate

# Rebase a stale snapshot onto new base
schemax snapshot rebase VERSION

# Compare snapshots
schemax diff --from-version V1 --to-version V2 [--target ENV]

# Record deployment manually
schemax record-deployment --target ENV [--version VERSION] [--mark-deployed]
```

### Python API

```python
from pathlib import Path
from schemax.core.storage import load_current_state, read_project, get_environment_config
from schemax.providers.unity.sql_generator import UnitySQLGenerator

# Load schema
state, changelog, provider = load_current_state(Path.cwd())

# Get environment config
project = read_project(Path.cwd())
env_config = get_environment_config(project, "dev")

# Build catalog mapping
catalog_mapping = {"__implicit__": env_config["topLevelName"]}

# Generate environment-specific SQL
generator = UnitySQLGenerator(state, catalog_mapping)
sql = generator.generate_sql(changelog["ops"])
```

### Deployment Tracking

**Database-backed tracking** in `{catalog}.schemax` schema:

Tables:
- `deployments` - Main deployment records
- `deployment_ops` - Individual operation tracking

**Local tracking** in `project.json` → `deployments` array

**Execution Flow** (`schemax apply`):
1. Authenticate with Databricks
2. Execute SQL statements (creates catalog if needed)
3. Create tracking schema in `{catalog}.schemax`
4. Record deployment to database
5. Track individual operations
6. Save deployment record to local `project.json`

### Code Formatting

**Python**: Uses Black with 100 character line length

```bash
cd packages/python-sdk
black src/ --line-length 100
```

**Configuration** (`pyproject.toml`):
```toml
[tool.black]
line-length = 100
target-version = ['py39', 'py310', 'py311', 'py312']
```

## Operation Types (Complete List - 50+ Unity Catalog Operations)

**Catalog Operations** (4):
- `add_catalog` - payload: `{catalogId, name, managedLocationName?}`
- `rename_catalog` - payload: `{newName}`
- `update_catalog` - payload: `{managedLocationName?}` - Update catalog properties (e.g., managed location)
- `drop_catalog` - payload: `{}`

**Schema Operations** (4):
- `add_schema` - payload: `{schemaId, name, catalogId, managedLocationName?}`
- `rename_schema` - payload: `{newName}`
- `update_schema` - payload: `{managedLocationName?}` - Update schema properties (e.g., managed location)
- `drop_schema` - payload: `{}`

**Table Operations** (6):
- `add_table` - payload: `{tableId, name, schemaId, format, external?, externalLocationName?, path?, partitionColumns?, clusterColumns?, managedLocationName?}`
- `rename_table` - payload: `{newName}`
- `drop_table` - payload: `{}`
- `set_table_comment` - payload: `{tableId, comment}`
- `set_table_property` - payload: `{tableId, key, value}`
- `unset_table_property` - payload: `{tableId, key}`

**Column Operations** (7):
- `add_column` - payload: `{tableId, colId, name, type, nullable, comment?}`
- `rename_column` - payload: `{tableId, colId, newName}`
- `drop_column` - payload: `{tableId, colId}`
- `reorder_columns` - payload: `{tableId, order: string[]}`
- `change_column_type` - payload: `{tableId, colId, newType}`
- `set_nullable` - payload: `{tableId, colId, nullable}`
- `set_column_comment` - payload: `{tableId, colId, comment}`

**Column Tag Operations** (2):
- `set_column_tag` - payload: `{tableId, colId, tagName, tagValue}`
- `unset_column_tag` - payload: `{tableId, colId, tagName}`

**Constraint Operations** (2):
- `add_constraint` - payload: `{tableId, constraintId, type, name?, columns, ...typeSpecificFields}`
- `drop_constraint` - payload: `{tableId, constraintId}`

**Row Filter Operations** (3):
- `add_row_filter` - payload: `{tableId, filterId, name, udfExpression, enabled?, description?}`
- `update_row_filter` - payload: `{tableId, filterId, name?, udfExpression?, enabled?, description?}`
- `remove_row_filter` - payload: `{tableId, filterId}`

**Column Mask Operations** (3):
- `add_column_mask` - payload: `{tableId, maskId, columnId, name, maskFunction, enabled?, description?}`
- `update_column_mask` - payload: `{tableId, maskId, name?, maskFunction?, enabled?, description?}`
- `remove_column_mask` - payload: `{tableId, maskId}`

## Implementation Status

### ✅ Completed Features (v0.2.0)

**Core Functionality**:
- ✅ Visual schema designer (VS Code extension)
- ✅ Snapshot-based versioning
- ✅ Operation log architecture
- ✅ All 50+ Unity Catalog operation types (incl. volumes, functions, materialized views)
- ✅ Inline column editing
- ✅ Context-aware UI elements
- ✅ Implicit catalog mode for single-catalog projects

**Multi-Environment Support (V4)**:
- ✅ Project schema v4 with rich environment configurations
- ✅ Environment-specific catalog mapping (logical → physical)
  - Fixed: Catalog name mapping now correctly applies during SQL generation
  - `__implicit__` and logical names resolve to physical names per environment
- ✅ `dev`, `test`, `prod` environment templates
- ✅ Per-environment settings (allowDrift, requireSnapshot, autoCreateCatalog, etc.)
- ✅ Environment-specific SQL generation with resolved names
- ✅ Custom environment wizard with presets
- ✅ Project-level managed locations (for catalog/schema MANAGED LOCATION)
- ✅ Project-level external locations (for external tables)
- ✅ Environment-specific location path resolution

**Data Governance & Storage**:
- ✅ Column tags (key-value metadata)
- ✅ Table constraints (PRIMARY KEY, FOREIGN KEY, CHECK)
- ✅ Row filters (row-level security)
- ✅ Column masks (data masking)
- ✅ Table properties (TBLPROPERTIES)
- ✅ External tables with named external locations
- ✅ Managed locations for catalogs and schemas (physical isolation)
- ✅ Partitioning (PARTITIONED BY)
- ✅ Liquid Clustering (CLUSTER BY)

**SQL Generation**:
- ✅ TypeScript implementation (VS Code)
- ✅ Python implementation (SDK/CLI)
- ✅ Idempotent DDL statements
- ✅ All 50+ Unity Catalog operations supported (catalogs, schemas, tables, views, volumes, functions, materialized views, columns, constraints, grants, etc.)
- ✅ Environment-specific catalog name mapping (fixed: id_name_map rebuild)
- ✅ SQL dependency ordering (catalog → schema → table → operations)
- ✅ SQL file export with environment suffix
- ✅ Unified operation batching (catalogs, schemas, tables)
- ✅ CREATE + UPDATE squashing optimization (single CREATE statement)
- ✅ External location path resolution per environment

**Databricks Integration**:
- ✅ `schemax apply` command for executing SQL
- ✅ Databricks CLI profile authentication
- ✅ SQL Statement Execution API integration
- ✅ Fail-fast execution with error handling
- ✅ Terraform-like preview with confirmation
- ✅ Dry-run mode and no-interaction mode

**Deployment Tracking**:
- ✅ Database-backed tracking in `{catalog}.schemax` schema
- ✅ `deployments` and `deployment_ops` tables
- ✅ Local tracking in `project.json`
- ✅ Deployment status tracking (success/failed/partial)
- ✅ Individual operation tracking
- ✅ Execution time and error logging

**Rollback & Recovery** (NEW):
- ✅ Partial rollback (revert successful operations from failed deployment)
- ✅ Complete rollback (rollback to previous snapshot)
- ✅ Auto-rollback option in `schemax apply --auto-rollback`
- ✅ Safety validation (SAFE, RISKY, DESTRUCTIVE levels)
- ✅ Rollback uses state_differ for accurate reverse operations
- ✅ Database query for latest deployment (source of truth)
- ✅ Deployment status: `success`, `failed`, `partial` (0 statements = failed)
- ✅ Idempotent rollback (checks database state to prevent redundant operations)
- ✅ SQL preview in rollback (matches apply command UX)

**Snapshot Management** (NEW):
- ✅ Snapshot creation via CLI with optional manual version override
- ✅ Snapshot validation (detect stale snapshots after Git rebase)
- ✅ Snapshot rebase (unpack, replay on new base, conflict detection)
- ✅ Conflict detection and logging for manual UI resolution
- ✅ Smart changelog management during rebase conflicts
- ✅ Semantic versioning utilities (MAJOR.MINOR.PATCH)
- ✅ `schemax snapshot create`, `validate`, and `rebase` commands

**CLI Improvements** (NEW):
- ✅ Interactive snapshot prompts in `schemax apply` (with --no-interaction flag)
- ✅ Improved SQL preview (per-statement display, pagination)
- ✅ `schemax rollback` command (--partial and --to-snapshot options)
- ✅ `schemax diff` command for snapshot comparison
- ✅ Consistent `--target` flag across all commands

**UI Improvements** (NEW):
- ✅ Conflict indicators in VS Code extension
- ✅ Stale snapshot warnings in UI
- ✅ Manual refresh button with loading spinner
- ✅ File system watchers for auto-reload (conflicts, snapshots, project.json)
- ✅ Codicon icons for theme consistency (settings, refresh, add, edit, delete buttons)
- ✅ Real-time UI updates via `FileSystemWatcher`

**Code Organization** (NEW):
- ✅ `core/` package for infrastructure (storage, deployment, version)
- ✅ Absolute imports (PEP 8 compliant) - 82 conversions
- ✅ Removed version suffix from storage.py
- ✅ Clean separation: core (infrastructure) vs providers vs commands

**Python SDK & CLI**:
- ✅ Provider-based architecture (Unity Catalog)
- ✅ Modular command structure (`commands/` folder)
- ✅ CLI commands: `apply`, `rollback`, `sql`, `validate`, `diff`, `record-deployment`, `snapshot validate`, `snapshot rebase`
- ✅ Python API for custom scripts
- ✅ Databricks authentication helpers
- ✅ Ruff formatting (100 char line length)
- ✅ Ruff linting with auto-fix

**Testing**:
- ✅ 240 passing pytest tests (unit + integration)
- ⏭️ 11 tests skipped (features in development)
- ✅ Test coverage: v4 storage, catalog mapping, auth, executor, rollback, snapshot rebase
- ✅ SQLGlot validation for generated SQL
- ✅ OperationBuilder pattern for test utilities
- ✅ Smoke tests (extension build, SDK install, CLI validation)

**Infrastructure**:
- ✅ Monorepo structure
- ✅ CI/CD workflows (GitHub Actions)
- ✅ Comprehensive documentation
- ✅ Quality checks pipeline (formatting, linting, tests)
- ✅ Examples and templates

### ⏭️ Future Enhancements (Not Yet Implemented)

**Databricks Integration**:
- Schema import from Databricks
- Drift detection and reconciliation
- Databricks Asset Bundle (DAB) generation
- Multi-table transaction support

**Testing**:
- Real integration tests against Databricks workspace
- End-to-end tests for `schemax apply`
- Performance tests for large schemas
- Concurrency tests

**Advanced Features**:
- Visual diff viewer
- Template library
- Multi-catalog project support
- Constraint validation UI
- Multi-user collaboration
- Schema versioning with git integration

**Tooling**:
- VS Code Marketplace publication
- PyPI package publication
- Homebrew formula
- Docker images

## Documentation Structure

**Clean, focused documentation** - no redundant files!

**Root Level**:
- `README.md` - Project overview & quick start
- `TESTING.md` - Testing guide
- `CONTRIBUTING.md` - Contributing guidelines (with code formatting standards)

**docs/ Directory**:
- `README.md` - Documentation index & navigation
- `QUICKSTART.md` - Complete getting started guide
- `ARCHITECTURE.md` - Technical design & concepts
- `DEVELOPMENT.md` - Development guide

**Package Documentation**:
- `packages/vscode-extension/README.md` - Extension-specific
- `packages/vscode-extension/CHANGELOG.md` - Version history
- `packages/python-sdk/README.md` - SDK & CLI reference

**Examples**:
- `examples/basic-schema/` - Sample project
- `examples/github-actions/` - CI/CD templates
- `examples/python-scripts/` - SDK usage examples

## Coding Guidelines

### General Principles

1. **Logging**: Always use `outputChannel.appendLine()` in extension, never `console.log()`
2. **File Structure**: All schemax files in `.schemax/` folder
3. **Immutability**: Never mutate state directly, always create new objects
4. **Op IDs**: Always generate UUIDs for new ops: `id: \`op_${uuidv4()}\``
5. **Validation**: TypeScript (Zod) and Python (Pydantic) for all data models
6. **Error Handling**: Catch errors, log details, show user-friendly messages
7. **Free-form Input**: Allow SQL expressions freely (no restrictive dropdowns)
8. **Modal Dialogs**: Use custom React modals (webview is sandboxed)
9. **Type Safety**: TypeScript strict mode, Python type hints
10. **State Management**: All mutations via Zustand → emit ops → apply → update UI

### TypeScript

**Standards**:
- Use TypeScript strict mode
- Avoid `any` types
- 2 spaces indentation
- Single quotes for strings
- Max line length: 100 characters
- Semicolons required

**Naming**:
- `camelCase` for variables and functions
- `PascalCase` for types, interfaces, and classes
- `UPPER_CASE` for constants

**Example**:
```typescript
// ✅ Good
interface TableOptions {
  format: 'delta' | 'iceberg';
  properties?: Record<string, string>;
}

export async function createTable(
  name: string,
  options: TableOptions
): Promise<Table> {
  try {
    const table = await tableService.create(name, options);
    outputChannel.appendLine(`[schemax] Created table: ${name}`);
    return table;
  } catch (error) {
    outputChannel.appendLine(`[schemax] ERROR: ${error}`);
    throw new Error(`Failed to create table: ${error}`);
  }
}
```

### Python

**Standards**:
- Use Ruff formatter (100 char line length)
- Type hints for all function signatures (REQUIRED - see below)
- Use Pydantic models for data structures
- Follow PEP 8
- Docstrings for public APIs

**Type Annotations (REQUIRED)**:

ALL function and method definitions MUST include complete type annotations:

```python
# ✅ GOOD - Complete type annotations
def process_data(name: str, count: int, options: Optional[Dict[str, Any]] = None) -> List[str]:
    """Process data and return results"""
    return []

def __init__(self, config: Config) -> None:
    """Initialize with config"""
    self.config = config

# ❌ BAD - Missing type annotations
def process_data(name, count, options=None):
    return []

def __init__(self, config):
    self.config = config
```

**Type Annotation Rules**:
1. ✅ ALL parameters must have type hints (except `self` and `cls`)
2. ✅ Return type must be specified (use `-> None` for void functions)
3. ✅ Use `Optional[Type]` for parameters with `None` default
4. ✅ Use `List`, `Dict`, `Tuple` from `typing` module (or `list`, `dict` for Python 3.9+)
5. ✅ Use `Any` sparingly - prefer specific types
6. ✅ Complex types: Use `Union`, `Literal`, `TypeVar` as needed

**More Examples**:
```python
from typing import Any, Dict, Generator, List, Optional, Tuple, Union

# Simple function
def add(a: int, b: int) -> int:
    return a + b

# Optional parameters
def create_user(name: str, email: Optional[str] = None) -> User:
    return User(name=name, email=email)

# Complex types
def process_records(
    data: List[Dict[str, Any]],
    filters: Optional[Dict[str, Union[str, int]]] = None
) -> Tuple[List[Record], int]:
    return [], 0

# Generator functions
def iter_items(data: List[str]) -> Generator[str, None, None]:
    for item in data:
        yield item

# Pydantic models
def validate_config(config: Dict[str, Any]) -> Config:
    return Config(**config)

# Protocol/Interface
def get_executor(config: ExecutionConfig) -> SQLExecutor:
    return UnitySQLExecutor(config)
```

**Enforcement**:
- Pre-commit hooks check with mypy
- VS Code/Cursor shows errors inline
- CI/CD blocks PRs with missing type annotations

**Naming**:
- `snake_case` for variables, functions, and methods
- `PascalCase` for classes
- `UPPER_CASE` for constants

**Formatting**:
```bash
# Format code before committing
cd packages/python-sdk
black src/ --line-length 100
```

**Example**:
```python
# ✅ Good
from typing import List
from pydantic import BaseModel

class Table(BaseModel):
    id: str
    name: str
    columns: List[Column] = []

def create_table(name: str, schema_id: str) -> Table:
    """Create a new table in the specified schema.
    
    Args:
        name: Table name
        schema_id: Parent schema ID
        
    Returns:
        Table: Created table object
        
    Raises:
        ValueError: If name is invalid
    """
    if not name:
        raise ValueError("Table name cannot be empty")
    
    return Table(id=f"table_{uuid4().hex[:8]}", name=name)
```

### Documentation

**When to Update**:
- Feature added → Update relevant docs + README
- Architecture changed → Update `docs/ARCHITECTURE.md`
- Build process changed → Update `docs/DEVELOPMENT.md`
- New test → Update `TESTING.md`

**Best Practices**:
- ✅ Keep single source of truth per topic
- ✅ Use clear, concise language
- ✅ Include code examples
- ✅ Add cross-references
- ❌ Don't create duplicate documentation
- ❌ Don't create redundant status/summary files

## Local Development Workflow

### Before Every Commit/PR

**Always run the complete quality checks**:
```bash
# Run all checks (formatting, linting, tests, smoke tests)
./devops/run-checks.sh
```

This script runs:
1. ✅ Python code formatting check (Black, 100 char line length)
2. ✅ Python linting (Ruff)
3. ✅ Python SDK tests (pytest - 138 passing, 12 skipped)
4. ✅ Smoke tests (extension build, SDK install, CLI validation)

**Exit code 0** = All checks passed, ready to commit
**Exit code 1** = One or more checks failed, fix before committing

---

### Quick Commands Reference

**Python SDK Testing**:
```bash
cd packages/python-sdk

# Run all tests (fast)
pytest tests/ -q

# Run with verbose output
pytest tests/ -v

# Run specific test file
pytest tests/unit/test_sql_generator.py -v

# Run specific test
pytest tests/unit/test_sql_generator.py::TestCatalogSQL::test_add_catalog -xvs

# Run with coverage
pytest tests/ --cov=src/schemax --cov-report=term-missing

# Run with coverage HTML report
pytest tests/ --cov=src/schemax --cov-report=html
open htmlcov/index.html
```

**Python Formatting & Linting**:
```bash
cd packages/python-sdk

# Check formatting (Black)
black src/ tests/ --check --line-length 100

# Fix formatting (Black)
black src/ tests/ --line-length 100

# Or use Ruff format (faster, recommended)
ruff format src/ tests/

# Check linting
ruff check src/ tests/

# Fix linting issues
ruff check src/ tests/ --fix

# Fix with unsafe fixes (removes unused variables, etc.)
ruff check src/ tests/ --fix --unsafe-fixes
```

**VS Code Extension Testing**:
```bash
cd packages/vscode-extension

# Build extension
npm run build

# Build in watch mode (for development)
npm run watch

# Build webview only
npm run build:webview

# Manual testing: Press F5 in VS Code
```

---

### Installation for Local Development

**One-time setup**:
```bash
# Install Python dev dependencies (pytest, ruff, SQLGlot, etc.)
cd packages/python-sdk
pip install -e ".[dev]"

# Or use UV (faster)
uv pip install -e ".[dev]"

# Install VS Code extension dependencies
cd packages/vscode-extension
npm install

# Install root dependencies
cd ../..
npm install
```

---

### SQLGlot Validation

SQLGlot is used in tests to validate generated SQL syntax for Databricks dialect:
```python
# In tests - SQLGlot validates all generated SQL
def test_add_catalog(sample_unity_state, assert_sql):
    result = generator.generate_sql_for_operation(op)
    assert_sql(result.sql)  # ✅ Validates Databricks SQL syntax
```

**Install**:
```bash
cd packages/python-sdk
pip install sqlglot>=20.0.0
# Or included in dev dependencies: pip install -e ".[dev]"
```

---

### CI/CD Pipeline

The quality checks pipeline runs automatically on:
- Push to `main` or `develop`
- Pull requests to `main` or `develop`
- Manual trigger

**Pipeline jobs**:
1. **code-formatting**: Ruff format + lint checks
2. **python-tests**: 240 pytest tests + coverage reporting (11 skipped)
3. **smoke-tests**: Extension build + SDK validation
4. **commit-signatures**: GPG signature verification

**Status**: ✅ All checks configured and passing locally

---

## Testing

**Quick Smoke Test**:
```bash
./scripts/smoke-test.sh
```

**VS Code Extension**:
1. Press F5 in VS Code (launches Extension Development Host)
2. Open a workspace folder
3. Cmd+Shift+P → "schemax: Open Designer"
4. Check logs: View → Output → "schemax"

**Python CLI**:
```bash
cd packages/python-sdk
pip install -e .
schemax validate examples/basic-schema/
schemax sql
```

**Current Test Status**:
- ✅ 240 pytest tests passing
- ⏭️ 11 tests skipped (features in development)
- ✅ SQLGlot validation integrated
- ✅ Code coverage reporting enabled
- ✅ OperationBuilder pattern for test utilities
- ✅ Test coverage: v4 storage, catalog mapping, auth, executor, deployment tracking, rollback, snapshot rebase

**Test Files**:
- `test_storage_v4.py` - V4 storage with multi-environment support (20 tests)
- `test_catalog_mapping.py` - Logical → Physical catalog name mapping (8 tests)
- `test_unity_executor.py` - Databricks SQL executor & authentication (6 tests)
- `test_deployment_tracker.py` - Deployment tracking (1 test)
- `test_rollback_command.py` - Rollback partial and complete (4 tests)
- `test_rollback_idempotency.py` - Rollback idempotency checks (2 tests)
- `test_snapshot_rebase.py` - Snapshot validation and rebase (12 tests)
- `test_apply_command.py` - Apply command with prompts (5 tests)
- `test_workflows.py` - Integration tests for complete workflows (14 tests)
- `test_drop_operations_fix.py` - DROP operation SQL generation (6 tests)

**Note**: V3 storage tests removed as v3 is deprecated in favor of v4.

**See**: `TESTING.md` for comprehensive testing guide

## Important Files

**VS Code Extension**:
- `packages/vscode-extension/src/extension.ts` - Extension host
- `packages/vscode-extension/src/storage-v4.ts` - V4 storage layer (multi-environment support)
- `packages/vscode-extension/src/providers/base/sql-generator.ts` - SQL generation
- `packages/vscode-extension/src/providers/base/models.ts` - TypeScript types
- `packages/vscode-extension/src/providers/base/operations.ts` - Op definitions
- `packages/vscode-extension/src/webview/state/useDesignerStore.ts` - Zustand store

**Python SDK** (Core Infrastructure):
- `packages/python-sdk/src/schemax/core/storage.py` - File I/O (v4 multi-environment)
- `packages/python-sdk/src/schemax/core/deployment.py` - Deployment tracking
- `packages/python-sdk/src/schemax/core/version.py` - Semantic versioning

**Python SDK** (Providers):
- `packages/python-sdk/src/schemax/providers/base/models.py` - Pydantic models
- `packages/python-sdk/src/schemax/providers/base/executor.py` - SQL execution protocol
- `packages/python-sdk/src/schemax/providers/base/state_differ.py` - State diffing
- `packages/python-sdk/src/schemax/providers/base/reverse_generator.py` - Safety validation
- `packages/python-sdk/src/schemax/providers/unity/state_reducer.py` - State reducer
- `packages/python-sdk/src/schemax/providers/unity/state_differ.py` - Unity state diffing
- `packages/python-sdk/src/schemax/providers/unity/sql_generator.py` - SQL generation with catalog mapping
- `packages/python-sdk/src/schemax/providers/unity/executor.py` - Databricks SQL executor
- `packages/python-sdk/src/schemax/providers/unity/auth.py` - Databricks authentication
- `packages/python-sdk/src/schemax/providers/unity/safety_validator.py` - Rollback safety

**Python SDK** (Commands):
- `packages/python-sdk/src/schemax/cli.py` - CLI routing layer
- `packages/python-sdk/src/schemax/commands/apply.py` - Apply command (with auto-rollback)
- `packages/python-sdk/src/schemax/commands/rollback.py` - Rollback command (partial & complete)
- `packages/python-sdk/src/schemax/commands/snapshot_rebase.py` - Snapshot rebase & validation
- `packages/python-sdk/src/schemax/commands/sql.py` - SQL generation command
- `packages/python-sdk/src/schemax/commands/diff.py` - Diff command
- `packages/python-sdk/src/schemax/commands/validate.py` - Validation command
- `packages/python-sdk/src/schemax/commands/deployment.py` - Deployment recording

**Documentation**:
- `README.md` - Project overview
- `docs/QUICKSTART.md` - Getting started
- `docs/ARCHITECTURE.md` - Technical design
- `TESTING.md` - Testing guide
- `CONTRIBUTING.md` - Contributing with code formatting

## Unity Catalog Compliance

Based on official Databricks documentation:

1. **Column Tags**: Key-value pairs for metadata tagging and data governance
2. **Constraints**: PRIMARY KEY, FOREIGN KEY, CHECK (informational, not enforced)
3. **Row Filters**: UDF expressions for row-level security
4. **Column Masks**: UDF functions for column-level data masking
5. **Table Properties**: TBLPROPERTIES for Delta Lake configuration

All features implemented according to official Databricks Unity Catalog specifications.

## VS Code API Usage

**Key APIs**:
- `vscode.window.createWebviewPanel()` - Create webview
- `vscode.window.createOutputChannel()` - Logging
- `vscode.commands.registerCommand()` - Register commands
- `webview.postMessage()` / `webview.onDidReceiveMessage()` - Communication
- `vscode.workspace.workspaceFolders` - Get workspace path
- `vscode.window.showInputBox()` - User input
- `vscode.window.withProgress()` - Progress indicator

## Dependencies

**VS Code Extension**:
- Runtime: `uuid`, `zod`, `react`, `react-dom`, `zustand`
- Dev: `typescript`, `esbuild`, `vite`, `@vitejs/plugin-react`, `@vscode/vsce`

**Python SDK**:
- Runtime: `pydantic>=2.0.0`, `click>=8.0.0`, `rich>=13.0.0`, `pyyaml>=6.0.0`, `databricks-sdk>=0.1.0`
- Dev: `pytest>=7.0.0`, `pytest-cov>=4.0.0`, `ruff>=0.1.0`, `mypy>=1.0.0`, `sqlglot>=20.0.0`

## Git Strategy

**Recommended**:
- Commit `.schemax/` folder to git
- Snapshots = tagged releases
- Changelog = work in progress
- Easy to see what changed between versions

## Quick Reference for AI Assistants

When starting a new conversation about this project:

1. **Project Type**: Monorepo with VS Code extension (TypeScript) + Python SDK/CLI
2. **Architecture**: Event sourcing with snapshots + changelog
3. **Storage**: V4 architecture with multi-environment support
   - Python: `packages/python-sdk/src/schemax/core/storage.py` (moved from storage_v4.py)
   - TypeScript: `packages/vscode-extension/src/storage-v4.ts`
4. **UI**: React webview with inline editing, implicit catalog mode, comprehensive governance features
   - Conflict indicators, stale snapshot warnings, manual refresh button
   - Codicon icons for theme consistency
   - FileSystemWatcher for auto-reload
5. **State**: Snapshot + changelog = current state
6. **SQL Generation**: ✅ Implemented in both TypeScript and Python with environment-specific catalog mapping
   - **Fixed**: id_name_map now correctly rebuilds after setting catalog_name_mapping
   - Unified batching for catalogs, schemas, and tables (uses base class batcher)
   - CREATE + UPDATE squashing optimization (e.g., single CREATE CATALOG ... MANAGED LOCATION)
7. **Deployment Tracking**: ✅ Fully implemented
   - Database-backed in `{catalog}.schemax` schema (Unity Catalog)
   - Database is source of truth for latest deployment (not local project.json)
   - Local tracking in `project.json` → `deployments` array
   - Status: `success`, `failed`, `partial` (0 statements = failed)
8. **Databricks Integration**: ✅ `schemax apply` command with SQL execution, authentication, deployment tracking
   - Interactive snapshot prompts (with --no-interaction flag)
   - Improved SQL preview (per-statement display)
   - Auto-rollback option (`--auto-rollback`)
9. **Rollback**: ✅ Fully implemented (partial & complete)
   - Partial: Revert successful operations from failed deployment
   - Complete: Rollback to previous snapshot
   - Uses state_differ for accurate reverse operations
   - Safety validation (SAFE, RISKY, DESTRUCTIVE levels)
   - Idempotent: Checks database state to prevent redundant rollbacks
   - SQL preview: Shows exact SQL statements before execution (matches apply UX)
   - Commands: `schemax rollback --partial` or `--to-snapshot`
10. **Snapshot Management**: ✅ Fully implemented
   - Snapshot validation: Detect stale snapshots after Git rebase
   - Snapshot rebase: Unpack, replay on new base, conflict detection
   - Conflict resolution: Manual via UI, conflict logs in `.schemax/conflicts/`
   - Commands: `schemax snapshot validate`, `schemax snapshot rebase`
11. **Operations**: Provider-based architecture (Unity Catalog = 50+ operations, incl. volumes, functions, materialized views)
   - Added: `update_catalog`, `update_schema` for managed location changes
12. **Catalog Mapping**: Logical names (`__implicit__`, explicit) → Physical names per environment
13. **External Tables**: ✅ Fully supported
   - Named external locations with per-environment paths (project-level config)
   - External location path resolution in SQL generation
   - Partitioning (PARTITIONED BY) and Liquid Clustering (CLUSTER BY)
14. **Physical Isolation**: ✅ Managed locations for catalogs/schemas (project-level config)
15. **Code Organization**: ✅ Refactored with `core/` package
   - `core/storage.py` - File I/O (v4 multi-environment)
   - `core/deployment.py` - Deployment tracking
   - `core/version.py` - Semantic versioning
   - Absolute imports (PEP 8 compliant) - 82 conversions
16. **Logs**: Check "schemax" output channel in VS Code
17. **Build**: Extension (`npm run build`), SDK (`pip install -e ".[dev]"`)
18. **Code Formatting**: Ruff format + lint for Python (100 char), manual for TypeScript
19. **Documentation**: Clean structure, no redundant files
20. **Testing**: 
    - **Before committing**: Run `./devops/run-checks.sh` (includes all checks)
    - **Python tests**: `pytest tests/ -v` (240 passing, 11 skipped)
    - **Smoke test**: `./scripts/smoke-test.sh`
    - **SQLGlot validation**: Integrated in pytest tests
    - **Test coverage**: storage, deployment, version, rollback, snapshot rebase, apply, workflows
21. **Quality Checks**: Code formatting, linting, pytest, smoke tests all automated
22. **Project Schema**: V4 with project-level location configs and per-environment paths
23. **CLI Commands**: `apply`, `rollback`, `sql`, `diff`, `validate`, `record-deployment`, `snapshot create`, `snapshot validate`, `snapshot rebase`
24. **Execution Flow**: Authenticate → Execute SQL → Create tracking schema → Record deployment (with auto-rollback)

**Status**: Production-ready v0.2.0 with complete Unity Catalog governance, multi-environment support, and Databricks integration. Future: Schema import, drift detection, DAB generation, multi-catalog support.

## Key Design Patterns

1. **Webview Sandboxing**: Custom React modals (browser APIs blocked)
2. **Type Safety**: Zod (TS) and Pydantic (Python) for runtime validation
3. **Operation Log**: Append-only, immutable, replayable
4. **Snapshot + Changelog**: Bounded growth, clean diffs
5. **Free-form SQL**: Textarea inputs for flexibility
6. **Badge UI**: Compact display for variable-length lists
7. **Context-aware UI**: Dynamic buttons based on selection
8. **Inline Editing**: Faster UX, less context switching
9. **Enable/Disable Toggle**: Non-destructive testing of governance rules
10. **Dual Implementation**: Same logic in TypeScript and Python for consistency
11. **Black Formatting**: Automated Python code formatting (100 char)
12. **Documentation Standards**: Single source of truth, no duplication
13. **Provider-Based Architecture**: Pluggable providers (Unity, Hive, Postgres) with shared contracts
14. **Catalog Name Mapping**: Logical → Physical catalog names per environment
15. **Implicit Catalog Mode**: Hidden catalog level for single-catalog projects
16. **SQL Dependency Ordering**: Catalog → Schema → Table → Operations
17. **Fail-Fast Execution**: Stop on first error, detailed error reporting
18. **Database-Backed Tracking**: Deployment history stored in target catalog
19. **Terraform-Like Preview**: Show changes before execution with confirmation
20. **Execution Flow**: SQL first → Tracking schema after (catalog must exist)
21. **Rollback via State Diffing**: Uses state_differ (not manual reverse SQL) for accuracy
22. **Safety Validation**: Classify operations (SAFE, RISKY, DESTRUCTIVE) before rollback
23. **Database as Source of Truth**: Query database for deployment status, not local files
24. **Snapshot Rebase**: Explicit CLI-triggered rebase (no auto-detection)
25. **Conflict-First Design**: Stop on first conflict, log for manual UI resolution
26. **Smart Changelog Management**: Clean state after rebase conflicts
27. **FileSystemWatcher Pattern**: Auto-reload UI on file changes (conflicts, snapshots)
28. **Interactive Prompts with CI/CD Support**: Use --no-interaction flag for automated workflows
29. **Codicon Consistency**: Theme-aware vector icons from VS Code's Codicon library
30. **Absolute Imports (PEP 8)**: No relative imports beyond package level
31. **Core vs Providers vs Commands**: Clear separation of infrastructure, logic, and CLI