# pgcrate - Postgres Migration Tool (LLM/AI Agent Reference)

## OVERVIEW

pgcrate is a PostgreSQL companion tool for teams not using Rails or Django.
It provides migrations, seeds, models, introspection, diffing, health checks, anonymization, and snapshots.
All using raw SQL files (no ORMs) with atomic, reversible operations.

## COMMAND STRUCTURE (v0.4.0+)

Commands are organized into namespaces. **Always use the full command path.**

### Quick Command Reference

| Task | Command |
|------|---------|
| Health check | `pgcrate dba doctor` |
| Quick triage | `pgcrate dba triage` |
| Check locks | `pgcrate dba locks` |
| Check sequences | `pgcrate dba sequences` |
| Check bloat | `pgcrate dba bloat` |
| Check vacuum | `pgcrate dba vacuum` |
| Check replication | `pgcrate dba replication` |
| Check connections | `pgcrate dba connections` |
| Top queries | `pgcrate dba queries` |
| XID wraparound | `pgcrate dba xid` |
| Index analysis | `pgcrate dba indexes` |
| Query plan analysis | `pgcrate dba explain "SELECT..."` |
| Disk usage | `pgcrate dba storage` |
| Stale statistics | `pgcrate dba stats-age` |
| Checkpoint health | `pgcrate dba checkpoints` |
| Autovacuum status | `pgcrate dba autovacuum-progress` |
| Config review | `pgcrate dba config` |
| Describe table | `pgcrate inspect table <name>` |
| Schema diff | `pgcrate inspect diff --to <url>` |
| List extensions | `pgcrate inspect extensions` |
| List roles | `pgcrate inspect roles` |
| Show grants | `pgcrate inspect grants` |
| Run migrations | `pgcrate migrate up` |
| Migration status | `pgcrate migrate status` |

### Command Namespaces

```
pgcrate
├── dba                    # DBA diagnostics and health checks
│   ├── doctor             # One-command health check
│   ├── triage             # Quick multi-check triage
│   ├── locks              # Blocking locks, long transactions
│   ├── sequences          # Sequence exhaustion
│   ├── xid                # Transaction ID wraparound
│   ├── indexes            # Missing/unused/duplicate/FK-without-index
│   ├── vacuum             # Dead tuple ratios, vacuum health
│   ├── bloat              # Table/index bloat estimates
│   ├── replication        # Streaming replication status
│   ├── queries            # Top queries (pg_stat_statements)
│   ├── connections        # Connection usage vs max_connections
│   ├── explain            # Query plan analysis
│   ├── storage            # Disk usage analysis
│   ├── stats-age          # Tables with stale statistics
│   ├── checkpoints        # Checkpoint frequency and health
│   ├── autovacuum-progress # Currently running autovacuum
│   ├── config             # PostgreSQL configuration review
│   └── fix                # Remediation commands
│       ├── sequence       # Upgrade sequence type
│       ├── index          # Drop unused index
│       ├── vacuum         # Run VACUUM on table
│       └── bloat          # REINDEX bloated indexes
├── inspect                # Schema inspection
│   ├── table <name>       # Describe table structure
│   ├── diff               # Compare schemas
│   ├── extensions         # List extensions
│   ├── roles              # List roles/users
│   └── grants             # Show permissions
├── migrate                # Migration management
│   ├── up                 # Run pending migrations
│   ├── down               # Roll back migrations
│   ├── status             # Show migration status
│   ├── new                # Create new migration
│   └── baseline           # Mark as applied (brownfield)
├── model                  # Data model management
├── seed                   # Seed data management
├── snapshot               # Database snapshots
├── sql                    # Run SQL queries
└── generate               # Generate migrations from DB
```

### Common Mistakes to Avoid

```bash
# WRONG - these commands don't exist at top level
pgcrate locks              # ✗ Use: pgcrate dba locks
pgcrate sequences          # ✗ Use: pgcrate dba sequences
pgcrate describe users     # ✗ Use: pgcrate inspect table users
pgcrate triage             # ✗ Use: pgcrate dba triage

# CORRECT - always use full namespace
pgcrate dba locks          # ✓
pgcrate dba sequences      # ✓
pgcrate inspect table users # ✓
pgcrate dba triage         # ✓
```

## POSTGRESQL VERSION REQUIREMENTS

pgcrate works with PostgreSQL 14+, with some features requiring newer versions:

| Feature | Minimum PG Version | Notes |
|---------|-------------------|-------|
| Migrations | 14+ | Basic DDL support |
| Seeds | 14+ | COPY command |
| Models (view/table) | 14+ | CREATE VIEW/TABLE AS |
| Models (incremental) | 9.5+ | INSERT ON CONFLICT (9.5-16), MERGE (17+) |
| Describe | 14+ | pg_catalog queries |
| Snapshots | 14+ | pg_dump |

### Checking Your Version
```bash
psql -c "SELECT version();"
# or
pgcrate dba doctor  # Shows connected PG version
```

### If Using PostgreSQL < 17
Incremental models use INSERT ON CONFLICT (upsert) on PostgreSQL 9.5-16, and MERGE on PostgreSQL 17+.
Both approaches work identically - the PostgreSQL version is detected automatically.

## CONFIGURATION

### Environment Variables

- `DATABASE_URL`: PostgreSQL connection string (postgresql://user:pass@host:port/db)

### Config File (pgcrate.toml)

```toml
[database]
url = "postgresql://user:pass@host:port/db"

[paths]
migrations = "db/migrations"  # Directory with migration files
models = "models"             # Directory with model SQL files
seeds = "seeds"               # Directory with seed files

[defaults]
with_down = false         # Create .down sections by default

[production]
patterns = ["prod", "production"]  # URL patterns for production warnings

[generate]
split_by = "none"         # Split mode: none, schema, or table
output = "db/migrations"  # Output directory for generated files
include_schemas = []      # Only include these schemas (empty = all)
exclude_schemas = []      # Exclude these schemas

[model]
sources = ["app.users", "app.orders"]  # Tables that models can reference

[tools]
pg_dump = "/path/to/pg_dump"       # Custom pg_dump path (for version matching)
pg_restore = "/path/to/pg_restore" # Custom pg_restore path
psql = "/path/to/psql"             # Custom psql path
```

### Configuration Precedence

1. **CLI flag** (`-d/--database-url`) - Highest priority
2. **Environment variable** (`DATABASE_URL`)
3. **Config file** (`[database].url`) - Lowest priority

### Path Resolution

- **Config file location**: Resolved relative to current working directory
- **File paths** (migrations): Resolved relative to current working directory
- **Path traversal protection**: Config paths cannot contain `../` sequences
- **Default location**: `db/migrations/` in current directory

## QUICK REFERENCE - COMMON WORKFLOWS

**Check what needs to run:**
```bash
pgcrate status              # Migration status (alias for migrate status)
pgcrate model status        # Which models are synced/missing vs database
pgcrate model graph         # Show dependency DAG with execution layers
```

**Run specific models:**
```bash
pgcrate model run -s analytics.daily_stats      # Run one model
pgcrate model run -s tag:daily                  # Run models with tag
pgcrate model run -s deps:analytics.summary     # Run model + its dependencies
```

**Preview before running:**
```bash
pgcrate model run --dry-run           # Show what would run
pgcrate model show analytics.foo      # Show compiled SQL for a model
pgcrate model compile                 # Compile all to target/compiled/
```

**Create new models:**
```bash
pgcrate model new analytics.user_stats                      # New view model
pgcrate model new analytics.daily_orders --materialized table
pgcrate model new analytics.events --materialized incremental
```

**Move/rename models:**
```bash
pgcrate model move analytics.old_name reporting.new_name --drop-old
```

## COMMANDS

### Project Initialization

```bash
# Interactive setup (prompts for configuration)
pgcrate init

# Non-interactive with defaults (migrations only)
pgcrate init -y

# Non-interactive auto-detection (when stdin is not a TTY)
# pgcrate behaves as if -y was passed.

# Non-interactive with seeds and models
pgcrate init -y --seeds --models

# Preview what would be created
pgcrate init --dry-run

# Custom directories
pgcrate init -y --migrations-dir db/migrate --seeds-dir data/seeds --models-dir sql/models

# Overwrite existing config
pgcrate init --force
```

**Flags:**
- `-y, --yes`: Non-interactive mode (use defaults + flags)
- `--dry-run`: Preview files without creating
- `--force`: Overwrite existing pgcrate.toml
- `-q, --quiet`: Minimal output
- `--migrations-dir <path>`: Migrations directory (default: `db/migrations`)
- `--models`: Enable models
- `--models-dir <path>`: Models directory (default: `models`)
- `--seeds`: Enable seeds
- `--seeds-dir <path>`: Seeds directory (default: `seeds`)

**Behavior:**
- Creates `pgcrate.toml` with configured paths
- Creates migration/seed/model directories as needed
- Interactive mode prompts for each option with confirmation
- Fails if pgcrate.toml exists (use `--force` to overwrite)

### Migration Commands

```bash
# Create new migration
pgcrate migrate new create_users
pgcrate migrate new add_email_column --with-down  # Also create .down.sql file

# Run pending migrations
pgcrate migrate up
pgcrate migrate up --dry-run  # Preview only

# Roll back migrations
pgcrate migrate down --steps 1 --yes
pgcrate migrate down --steps 3 --dry-run  # Preview rollback

# Check migration status
pgcrate migrate status

**Interactivity and confirmations:**
- `migrate up` / `migrate new` are non-interactive (accept `-y/--yes` as a no-op for scripting consistency; no top-level `up/new` aliases)
- `migrate down` requires `--yes`
- `migrate baseline` requires `--yes`

# One-command health check (connection, schema, migrations, config)
pgcrate dba doctor
pgcrate dba doctor --verbose   # Show passing checks (default prints only issues)
pgcrate dba doctor --json      # Machine-readable report
pgcrate dba doctor --strict    # Treat warnings as errors (exit 1)
pgcrate dba doctor --quiet     # No output, exit code only
```

**Doctor Exit Codes:**
- `0`: success (including warnings)
- `1`: errors found (or warnings with `--strict`)
- `2`: fatal error (connection/config/CLI usage)

### SQL Command

```bash
# Run a single query (-c flag for psql compatibility)
pgcrate sql -c "SELECT 1 AS ok"

# Alias
pgcrate query -c "SELECT COUNT(*) FROM public.users"

# Read SQL from stdin
echo "SELECT 1" | pgcrate sql
cat check.sql | pgcrate sql

# Refuse writes by default (use --allow-write to run INSERT/UPDATE/DDL)
pgcrate sql -c "INSERT INTO public.users(id) VALUES (1)"          # errors
pgcrate sql -c "INSERT INTO public.users(id) VALUES (1)" --allow-write

# JSON output
pgcrate --json sql -c "SELECT 1 AS ok"
```

### Generate Commands

```bash
# Generate migration from existing database (single file)
pgcrate generate

# Dry run to see what would be generated
pgcrate generate --dry-run

# Split output by schema (one file per schema)
pgcrate generate --split-by schema

# Split output by table (one file per table, plus types/FKs)
pgcrate generate --split-by table

# Output to a specific directory
pgcrate generate --output ./migrations-draft/

# Include only specific schemas
pgcrate generate --schema app --schema analytics

# Exclude specific schemas
pgcrate generate --exclude-schema legacy
```

**Generate Behavior Details:**
- **Introspection**: Reads existing database schema via pg_catalog
- **Output format**: Standard pgcrate migration files with `-- up` and `-- down` markers
- **Split modes**: none (single file), schema (one per schema), table (one per table)
- **Primary keys**: Single-column PKs inline, composite PKs as table constraints
- **SERIAL detection**: Preserves SERIAL/BIGSERIAL vs IDENTITY column styles
- **Foreign keys**: Always output after tables to ensure proper ordering
- **File conflicts**: Fails if output files already exist (no silent overwrite)
- **Timestamp ordering**: Split files use sequential timestamps (1 second apart)

### Seed Commands

```bash
# List available seeds
pgcrate seed list

# Load all seeds
pgcrate seed run
pgcrate seed run --dry-run  # Preview only

# Load specific seeds
pgcrate seed run public.task_statuses public.priorities

# Validate seed files (parse without loading)
pgcrate seed validate

# Compare seeds to database state
pgcrate seed diff
```

**Seed Types:**
- **CSV seeds**: Data files with automatic type inference (boolean, bigint, numeric, date, timestamptz, uuid, jsonb, text)
- **SQL seeds**: Raw SQL files for complex insert logic (stored procedures, generate_series, etc.)
- **Schema sidecar files**: Optional `.schema.toml` files for explicit column types and primary keys

**CSV Seed Example** (`seeds/public/task_statuses.csv`):
```csv
code,label,sort_order,is_terminal
todo,To Do,1,false
in_progress,In Progress,2,false
done,Done,3,true
```

**Schema Sidecar Example** (`seeds/public/task_statuses.schema.toml`):
```toml
primary_key = ["code"]

[columns]
code = "text"
label = "text"
sort_order = "integer"
is_terminal = "boolean"
```

**SQL Seed Example** (`seeds/public/demo_users.sql`):
```sql
-- SQL seeds run as-is; prefer schema-qualified writes.
TRUNCATE public.demo_users RESTART IDENTITY;
INSERT INTO public.demo_users (email, name)
SELECT 'user' || n || '@example.com', 'User ' || n
FROM generate_series(1, 100) AS n;
```

**Seed Behavior:**
- Seeds load into existing tables by name (no automatic table creation)
- CSV seeds error if the target table is missing (create it with a migration first)
- Seed files are stored under `seeds/<schema>/` and load into `<schema>.<table>` automatically
- CSV seeds use PostgreSQL COPY protocol for fast bulk loading
- Foreign key constraints are disabled during load (DISABLE TRIGGER ALL)
- Seeds are idempotent: tables are truncated before loading

### Model Commands

```bash
# Run all models in DAG order
pgcrate model run
pgcrate model run --dry-run  # Print compiled SQL without executing
pgcrate model run --init     # Create models/ if missing

# Run with selectors
pgcrate model run -s marts.task_metrics     # Specific model
pgcrate model run -s tag:daily              # Models with tag
pgcrate model run -s deps:marts.user_stats  # Model + upstream deps

# Compile models to target/compiled/
pgcrate model compile
pgcrate model compile --init

# Run data tests
pgcrate model test
pgcrate model test --init
pgcrate model test -s marts.user_activity  # Test specific model

# Lint dependency declarations
pgcrate model lint deps
pgcrate model lint deps --fix  # Auto-fix deps line

# Show dependency graph
pgcrate model graph
pgcrate model graph --format mermaid  # Export as Mermaid diagram

# Create a new model file
pgcrate model new analytics.user_stats
pgcrate model new analytics.daily_order_stats --materialized incremental

# Model init is optional: `model new` creates directories automatically.
pgcrate model init
pgcrate model init -y

# Show compiled SQL for a model (including incremental MERGE)
pgcrate model show analytics.daily_order_stats
pgcrate model show analytics.daily_order_stats --json

# Check model sync status vs database
pgcrate model status
pgcrate model status --json
```

**Model File Layout:**
- Models live at `models/<schema>/<name>.sql`
- `<schema>.<name>` is the database relation created/updated when you run the model
- Target schemas are auto-created if they don't exist (e.g., running `analytics.user_stats` creates the `analytics` schema automatically)

**Model File Format:**
```sql
-- materialized: table
-- deps: staging.stg_users, staging.stg_orders
-- tags: daily, metrics
-- tests: not_null(user_id), unique(user_id)
-- description: User order metrics

SELECT
    u.id AS user_id,
    COUNT(*) AS order_count
FROM staging.stg_users u
LEFT JOIN staging.stg_orders o ON o.user_id = u.id
GROUP BY u.id
```

**Model Header Directives:**
- `-- materialized: view|table|incremental` - How to create the model (default: view)
- `-- deps: schema.table, ...` - Model dependencies (other models)
- `-- unique_key: col1, col2` - For incremental models, the merge key
- `-- tags: tag1, tag2` - Tags for selective execution
- `-- tests: test_type(args)` - Data quality tests
- `-- description: text` - Model documentation

## Materialization Types

Models support three materialization types via the `-- materialized:` header:

### view (default)
- Creates a PostgreSQL VIEW
- No data stored - query runs on each access
- Best for: light transformations, always-fresh data
- Example: `-- materialized: view`

### table
- Creates a PostgreSQL TABLE using CREATE TABLE AS
- Data stored physically, rebuilt on each run
- Best for: heavy aggregations, frequently queried data
- Example: `-- materialized: table`

### incremental
- Creates a TABLE with upsert on subsequent runs
- Requires `-- unique_key:` header to identify rows
- Only new/changed rows are processed after first run
- Best for: append-only data, large datasets
- Works on PostgreSQL 9.5+ (uses INSERT ON CONFLICT or MERGE depending on version)
- Example:
  ```sql
  -- materialized: incremental
  -- unique_key: id
  -- deps: public.events
  SELECT id, event_type, count(*) as cnt
  FROM public.events
  GROUP BY id, event_type
  ```

### Choosing a Materialization

**Decision tree:**
```
Is query performance critical?
├─ No → view (always fresh, no storage)
└─ Yes → Is data small enough to rebuild each run?
         ├─ Yes → table (fast queries, simple)
         └─ No → Does data have a natural unique key?
                 ├─ Yes → incremental (efficient updates)
                 └─ No → table with partitioning
```

**Quick reference:**
| Need | Use |
|------|-----|
| Always fresh, light query | view |
| Fast reads, can rebuild | table |
| Large data, incremental updates | incremental |

**Common patterns by model layer:**
| Layer | Typical Materialization | Why |
|-------|------------------------|-----|
| staging | view | Simple transforms, always current |
| intermediate | view or table | Depends on query complexity |
| marts/facts | incremental | Large, append-heavy, has keys |
| marts/dimensions | table | Small, full refresh OK |
| aggregations | table or incremental | Depends on size and rebuild time |

**Performance considerations:**
- **view**: Zero storage, but query runs every time. Slow for complex joins/aggregations.
- **table**: Fast reads, but DROP+CREATE on every run. OK for small-medium data.
- **incremental**: Fast updates, but requires unique_key. Best for large append-only data.

## Incremental Models

Incremental models materialize as a **TABLE** and are updated via upsert on subsequent runs.

**Header requirements:**
- `-- materialized: incremental`
- `-- unique_key: col1, col2` (required; used as the merge key and primary key)

### How unique_key Works

The `unique_key` directive serves two purposes:

1. **Creates PRIMARY KEY constraint** - On first run, pgcrate creates the table and adds `PRIMARY KEY (unique_key)`. This ensures data integrity and enables efficient upserts.

2. **Defines merge/upsert behavior** - On subsequent runs:
   - PostgreSQL 17+: Uses `MERGE INTO ... ON t.key = s.key`
   - PostgreSQL 9.5-16: Uses `INSERT ... ON CONFLICT (key) DO UPDATE SET ...`

**Composite keys** are supported:
```sql
-- unique_key: user_id, date
```

**What happens when source data changes:**
- Matching key exists: Row is **updated** with new values
- New key: Row is **inserted**
- Key deleted from source: Row is **NOT deleted** (orphaned)

To handle deletes, either:
- Use `--full-refresh` periodically to rebuild
- Add explicit delete logic in your model

### Execution Semantics

- **First run** (or `--full-refresh`): Creates table with `CREATE TABLE AS`, adds PRIMARY KEY on `unique_key`
- **Subsequent runs**: Upserts rows - inserts new keys, updates existing keys

### Incremental Filtering (Optional)

By default, incremental models scan all source data on every run. For large datasets, you can filter to only process new/changed rows using one of these approaches:

**Level 1: No filtering (default)**
```sql
-- materialized: incremental
-- unique_key: user_id

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
```
Full scan every run, merge by key. Simple and correct, good for smaller datasets.

**Level 2: Watermark filtering (recommended for most cases)**
```sql
-- materialized: incremental
-- unique_key: id
-- watermark: updated_at

SELECT id, name, updated_at FROM source
```
pgcrate automatically generates: `WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)`
- First run / `--full-refresh`: no filter, loads all data
- Subsequent runs: only rows newer than max watermark
- Requires index on watermark column for performance

**Watermark with lookback (handles late-arriving data):**
```sql
-- watermark: updated_at
-- lookback: 2 days
```
Generates: `WHERE updated_at > (SELECT MAX(updated_at) - interval '2 days' FROM target)`

**Compound watermark (for tie-breaking):**
```sql
-- watermark: updated_at, id
```
Uses row comparison: `(updated_at, id) > (SELECT MAX(updated_at), MAX(id) FROM target)`

**Level 3: Custom filter (for complex predicates)**
```sql
-- materialized: incremental
-- unique_key: id
-- incremental_filter: created_at > current_date - interval '7 days'

SELECT id, data, created_at FROM source
```
Appends the predicate as a WHERE clause on subsequent runs.
- Mutually exclusive with `-- watermark`
- Use when watermark pattern doesn't fit (sliding windows, date-based partitions, etc.)

**Level 4: Full control with @base/@incremental sections**
```sql
-- materialized: incremental
-- unique_key: id

-- @base
SELECT * FROM events

-- @incremental
SELECT * FROM events WHERE id > (SELECT MAX(id) FROM analytics.daily_events)
```
Use when first-run and incremental queries are structurally different.
- `-- @base`: runs on first execution or `--full-refresh`
- `-- @incremental`: runs on subsequent executions
- `${this}` is NOT supported; use the full target table name

**Choosing an approach:**
| Scenario | Use |
|----------|-----|
| Small dataset, simple logic | No filtering (Level 1) |
| Large dataset with timestamp | `-- watermark: updated_at` (Level 2) |
| Late-arriving data | `-- watermark` + `-- lookback` |
| Sliding window / date partition | `-- incremental_filter` (Level 3) |
| Structurally different queries | `@base` / `@incremental` (Level 4) |

**Best practices:**
- Ensure `unique_key` is truly unique in your source query (duplicates cause errors)
- Match `unique_key` to the grain of your model output
- Add index on watermark column for performance
- Use `-- lookback` if source data can be updated after initial insert

**Example (incremental):**
```sql
-- materialized: incremental
-- unique_key: day
-- deps: staging.orders
-- tests: not_null(day), unique(day)

SELECT
  date_trunc('day', created_at)::date AS day,
  COUNT(*) AS order_count
FROM staging.orders
GROUP BY 1
```

**MERGE shape (simplified):**
```sql
MERGE INTO analytics.daily_order_stats AS t
USING ( <model query> ) AS s
ON t.day = s.day
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
```

**Test Types:**
- `not_null(column)` - Column has no NULL values
- `unique(col1, col2)` - Column(s) have no duplicates
- `accepted_values(column, ['a', 'b', 'c'])` - Column values in allowed set
- `relationships(column, target_schema.target_table.target_column)` - FK relationship valid

**Selector Syntax:**
- `model_name` or `schema.model_name` - Exact match
- `tag:tagname` - Models with specific tag
- `deps:model` - Model plus all upstream dependencies
- `downstream:model` - Model plus all downstream dependents
- `tree:model` - Full lineage (upstream + downstream)

**Quickstart model layers (common pattern):**
- `staging`: thin views over raw/source tables (rename columns, type casts)
- `intermediate`: reusable joins/transforms
- `marts`: final business-facing tables/views

## Model Refactoring

pgcrate does NOT automatically clean up database objects when model files are deleted.
To refactor models, follow these patterns:

### Rename a Model (same schema)
1. Create new model file: `models/analytics/new_name.sql`
2. Copy content from old file
3. Run: `pgcrate model run -s analytics.new_name`
4. Drop old object: `psql -c "DROP VIEW analytics.old_name CASCADE"`
5. Delete old file: `rm models/analytics/old_name.sql`
6. Update any downstream deps to reference new name

### Move to Different Schema
1. Create new model file: `models/reporting/model_name.sql`
2. Copy content, update any schema-specific references
3. Run: `pgcrate model run -s reporting.model_name`
4. Drop old object: `psql -c "DROP VIEW analytics.model_name CASCADE"`
5. Delete old file: `rm models/analytics/model_name.sql`

### Change Materialization (view to table)
1. Edit model file: change `-- materialized: view` to `-- materialized: table`
2. Run: `pgcrate model run -s schema.model_name`
   (pgcrate automatically handles dropping view and creating table)

### Check for Orphaned Objects
Objects in database without model files:
```bash
psql -c "SELECT schemaname, viewname FROM pg_views WHERE schemaname NOT IN ('pg_catalog', 'information_schema')"
# Compare against: ls models/*/*.sql
```

## FILE STRUCTURE

```
project/
├── pgcrate.toml
├── db/
│   └── migrations/
│       ├── 20251130000000_create_users.sql
│       └── 20251130010000_add_indexes.sql
├── models/
│   ├── staging/
│   │   ├── stg_users.sql
│   │   └── stg_orders.sql
│   └── marts/
│       ├── user_metrics.sql
│       └── order_summary.sql
└── seeds/
    └── public/
        ├── task_statuses.csv
        ├── task_statuses.schema.toml
        └── demo_data.sql
```

## MIGRATION FILE NAMING AND ORDERING

### File Format
- **Required**: Single-file format with `-- up` and `-- down` markers
- **Format**: `YYYYMMDDHHMMSS_description.sql` (14-digit timestamp)
- **Timestamp format**: Numeric only (e.g., `20251130000000_create_users.sql`)
- **Legacy support**: Rejects `.up.sql`/.`down.sql` pairs with clear error messages

### Ordering Semantics
- **Lexicographic**: Applied in filename string order (not numeric)
- **Timestamp ordering**: Use 14-digit timestamps for proper chronological sorting
- **Deduplication**: Duplicate versions are rejected with error
- **Validation**: Only comments/blank lines allowed before `-- up` marker

### Migration Structure
```sql
-- Migration description (comments only above -- up)

-- up
CREATE TABLE users (id serial primary key, email text unique);

-- down
DROP TABLE users;
```

## TRANSACTION AND FAILURE SEMANTICS

### Migration Transactions
- **Individual transactions**: Each migration runs in its own transaction
- **Atomic**: `-- up` SQL and tracking record insertion are transactional
- **Failure**: Failed migration is NOT recorded in schema_migrations table
- **Recovery**: Fix SQL and rerun - failed migrations are retried automatically

### Tracking Table Schema
```sql
CREATE SCHEMA IF NOT EXISTS pgcrate;
CREATE TABLE IF NOT EXISTS pgcrate.schema_migrations (
    version TEXT PRIMARY KEY,        -- Migration timestamp (YYYYMMDDHHMMSS)
    applied_at TIMESTAMPTZ DEFAULT now()
);
```

### Error Handling
- **Rollback**: Failed migrations automatically rollback their changes
- **Partial state**: Database never left in partially-applied migration state
- **Idempotent**: Rerunning failed migrations is safe and expected

## METADATA SCHEMA

### Complete Schema Definition
```sql
-- pgcrate schema (created automatically)
CREATE SCHEMA IF NOT EXISTS pgcrate;

-- Migration tracking
CREATE TABLE IF NOT EXISTS pgcrate.schema_migrations (
    version TEXT PRIMARY KEY,        -- Migration timestamp (YYYYMMDDHHMMSS)
    applied_at TIMESTAMPTZ DEFAULT now()
);
```

### Schema Stability
- **Stable API**: This table is considered stable for external tooling
- **Schema versioning**: Table is created with IF NOT EXISTS for compatibility
- **Timestamps**: Uses TIMESTAMPTZ with UTC for consistency

## DIAGNOSTICS

### Health Check Commands

```bash
# Quick triage (locks, xid, sequences)
pgcrate dba triage
pgcrate dba triage --json
pgcrate dba triage --include-fixes --json  # Include recommended fix actions

# Individual diagnostics
pgcrate dba locks                    # Blocking locks and long transactions
pgcrate dba xid                      # Transaction ID wraparound analysis
pgcrate dba sequences                # Sequence exhaustion check
pgcrate dba indexes                  # Missing, unused, duplicate, FK-without-index
pgcrate dba vacuum                   # Table bloat and vacuum health
pgcrate dba bloat                    # Estimate table and index bloat
pgcrate dba replication              # Streaming replication health
pgcrate dba queries                  # Top queries from pg_stat_statements
pgcrate dba connections              # Connection usage vs max_connections
pgcrate dba storage                  # Disk usage (tables, indexes, TOAST)
pgcrate dba stats-age                # Tables with stale statistics
pgcrate dba checkpoints              # Checkpoint frequency and WAL health
pgcrate dba autovacuum-progress      # Currently running autovacuum operations
pgcrate dba config                   # PostgreSQL configuration review

# Query plan analysis
pgcrate dba explain "SELECT * FROM users WHERE email = 'test@example.com'"
pgcrate dba explain --file query.sql --analyze   # Execute with ANALYZE
pgcrate dba explain "SELECT ..." --include-actions --json  # Get fix actions

# Explain recommendation thresholds:
# - seq_scan_large_table: Sequential scan with >10,000 estimated rows → Warning
# - seq_scan_large_table: Sequential scan with >100,000 estimated rows → Critical
# - missing_index_on_filter: Filter on column in seq scan >10k rows → CreateIndex recommendation
# - nested_loop_large_set: Nested loop with >1,000 estimated rows → Info
# - sort_without_index: Sort with >10,000 estimated rows → ConsiderIndex recommendation
# - high_cost_estimate: Total cost >10,000 → Warning

# Connection context
pgcrate context --json           # Connection info, server version, privileges
pgcrate capabilities --json      # What can this connection do?
```

**Timeout Flags (for production safety):**
- `--connect-timeout <ms>` - Connection timeout (default: 5000ms)
- `--statement-timeout <ms>` - Query timeout (default: 30000ms)
- `--lock-timeout <ms>` - Lock wait timeout (default: 500ms)

**Exit Codes:**
- `0` = healthy
- `1` = warning
- `2` = critical
- `10+` = operational failure

### Fix Commands

Safe remediation for issues found by diagnostics:

```bash
# Sequence fixes (prevent exhaustion)
pgcrate --read-write --primary dba fix sequence public.order_seq --upgrade-to bigint --dry-run
pgcrate --read-write --primary dba fix sequence public.order_seq --upgrade-to bigint --yes

# Index fixes (remove unused indexes)
pgcrate --read-write --primary dba fix index --drop public.idx_unused --dry-run
pgcrate --read-write --primary dba fix index --drop public.idx_unused --yes

# Vacuum fixes (reclaim space from table bloat)
pgcrate --read-write --primary dba fix vacuum public.orders --dry-run
pgcrate --read-write --primary dba fix vacuum public.orders --yes
pgcrate --read-write --primary dba fix vacuum public.orders --full --yes    # ACCESS EXCLUSIVE lock
pgcrate --read-write --primary dba fix vacuum public.orders --analyze --yes # Update statistics

# Bloat fixes (rebuild bloated indexes)
pgcrate --read-write --primary dba fix bloat public.idx_orders_created --dry-run
pgcrate --read-write --primary dba fix bloat public.idx_orders_created --yes  # REINDEX CONCURRENTLY (PG12+)
pgcrate --read-write --primary dba fix bloat public.idx_orders_created --blocking --yes  # Force blocking REINDEX
```

**Gate Flags (required for fix operations):**
- `--read-write` - Required for all fix operations
- `--primary` - Required for database-modifying operations
- `--yes` - Required for medium/high risk operations

**Risk Levels:**
- **Low:** `ALTER SEQUENCE`, regular `VACUUM`
- **Medium:** `DROP INDEX CONCURRENTLY` (requires `--yes`)
- **High:** `VACUUM FULL` (requires `--yes`, takes exclusive lock)

**Verification:**
```bash
# Run post-fix verification
pgcrate --read-write --primary dba fix sequence public.order_seq --upgrade-to bigint --yes --verify
```

## GLOBAL FLAGS

- `-d, --database-url <URL>`: Override database connection
- `--config <PATH>`: Custom config file path
- `--quiet`: Minimal output (errors only)
- `--verbose`: Show executed SQL
- `--json`: Output as JSON instead of human-readable text

## JSON OUTPUT MODE

When `--json` is specified, pgcrate outputs machine-readable JSON to stdout.

### Supported Commands

Currently, `--json` is supported for these commands:
- `inspect table` - Table introspection
- `inspect diff` - Schema comparison
- `model show` - Show compiled SQL for a model
- `model status` - Model sync status
- `snapshot list` - List snapshots
- `snapshot info` - Snapshot details
- `sql` - SQL query results
- `status` - Migration status (alias for `migrate status`)
- `context` - Connection context and server info
- `capabilities` - Permission discovery
- `dba triage` - Health overview with actions
- `dba doctor` - Health checks
- `dba locks` - Blocking locks and transactions
- `dba xid` - Transaction ID wraparound
- `dba sequences` - Sequence exhaustion check
- `dba indexes` - Index health analysis
- `dba vacuum` - Table bloat analysis
- `dba bloat` - Table and index bloat estimation
- `dba replication` - Streaming replication health
- `dba queries` - Top queries analysis
- `dba connections` - Connection usage analysis
- `dba fix sequence` - Sequence upgrade result
- `dba fix index` - Index drop result
- `dba fix vacuum` - Vacuum result
- `dba fix bloat` - REINDEX result
- `dba explain` - Query plan analysis
- `dba storage` - Disk usage analysis
- `dba stats-age` - Statistics freshness analysis
- `dba checkpoints` - Checkpoint health analysis
- `dba autovacuum-progress` - Running autovacuum operations
- `dba config` - Configuration review with suggestions

For unsupported commands, `--json` returns a JSON error: `"--json not supported for '<cmd>' yet"`.

### Output Streams Contract

- **stdout**: Data (the command's "answer") - results, JSON responses
- **stderr**: Diagnostics (progress messages, debug output) - suppressed in JSON mode

This separation allows scripts to pipe stdout (`pgcrate migrate status --json | jq .`) without diagnostics polluting the data stream. In JSON mode, pgcrate emits no app-level output to stderr (external libraries or cargo may still emit warnings).

### JSON Error Schema

On error with `--json`, output goes to stdout with non-zero exit code:

```json
{
  "ok": false,
  "error": {
    "message": "Error description",
    "details": "Optional additional context"
  }
}
```

**Notes:**
- `details` is omitted when not present (not an empty string)
- `message` is always present and non-empty

### JSON Success Schemas

#### describe command

```json
{
  "ok": true,
  "schema": "public",
  "name": "users",
  "table": {
    "columns": [
      {"name": "id", "data_type": "integer", "nullable": false, "is_primary_key": true, ...},
      {"name": "email", "data_type": "text", "nullable": false, ...}
    ],
    "indexes": [...],
    "constraints": [...],
    "triggers": [...],
    "stats": {"row_estimate": 1000, "table_size": "16 kB", ...},
    "rls": null
  },
  "dependents": null,
  "dependencies": null
}
```

#### status command

```json
{
  "ok": true,
  "applied": [
    {"version": "20251130000000", "name": "create_users", "has_down": true}
  ],
  "pending": [
    {"version": "20251130010000", "name": "add_indexes", "has_down": false}
  ],
  "counts": {
    "applied": 1,
    "pending": 1,
    "total": 2
  }
}
```

#### diff command

```json
{
  "ok": true,
  "identical": false,
  "summary": {
    "tables": 2,
    "columns": 5,
    "indexes": 1,
    "constraints": 0,
    "enums": 0,
    "functions": 0,
    "views": 0,
    "triggers": 0,
    "sequences": 0,
    "extensions": 0,
    "schemas": 0,
    "materialized_views": 0
  },
  "formatted_diff": "Comparing: source → target\n..."
}
```

### Meta Flags (--help, --version, --help-llm)

Meta flags return JSON success responses (exit 0) when combined with `--json`:

#### --help

```json
{ "ok": true, "help": "<full help text>" }
```

#### --version

```json
{ "ok": true, "version": "0.6.0" }
```

#### --help-llm

```json
{ "ok": true, "llm_help": "<contents of llms.txt>" }
```

### Exit Codes

- `0`: Success (includes --help, --version, --help-llm; for diff: schemas identical)
- `1`: Application error or diff found differences
- `2`: CLI usage error (bad arguments, missing required flags)

In JSON mode, both exit codes 1 and 2 produce valid JSON error output to stdout.

### Examples

```bash
# Check migration status as JSON
pgcrate migrate status --json

# Parse with jq
pgcrate migrate status --json | jq '.counts.pending'

# Compare databases, exit 1 if different
pgcrate diff --json --to postgres://other/db

# Handle errors in scripts (no need to redirect stderr in JSON mode)
if ! result=$(pgcrate migrate status --json); then
  echo "pgcrate failed" >&2
  exit 1
fi
pending=$(echo "$result" | jq -r '.counts.pending')

# Check for usage errors specifically
pgcrate --json migrate down  # Missing --steps, exits 2 with JSON error
```

## ERROR HANDLING

- Commands return exit code 1 on application error, 2 on CLI parse errors
- **Migrations**: Run in individual transactions (rollback on failure)
- Failed migrations are NOT tracked and can be safely retried after fixing SQL
- Use `--dry-run` flag to preview operations before execution

## EXAMPLES FOR AUTOMATION

```bash
# CI/CD pipeline
pgcrate migrate up --quiet

# Development workflow
pgcrate migrate new add_feature --with-down
# Edit migration files...
pgcrate migrate up --verbose
```

## INTEGRATION PATTERNS

### Git Hooks
```bash
#!/bin/sh
# pre-commit hook
pgcrate migrate up --dry-run
```

### Docker Integration
```dockerfile
FROM postgres:15
COPY migrations/ /app/migrations/
COPY pgcrate.toml /app/
RUN wget -O /usr/local/bin/pgcrate https://github.com/jackschultz/pgcrate/releases/latest/download/pgcrate-linux-amd64
RUN chmod +x /usr/local/bin/pgcrate
ENV DATABASE_URL=postgresql://postgres:password@localhost:5432/db
RUN pgcrate migrate up
```

### Configuration Management
```bash
# Production
DATABASE_URL=postgresql://user:pass@prod-db:5432/prod pgcrate migrate up --quiet

# Staging
DATABASE_URL=postgresql://user:pass@staging-db:5432/staging pgcrate migrate up --dry-run

# Development
pgcrate migrate up --verbose
```
