# Codebase Analysis: pgmold Complete Feature Inventory

## Summary

pgmold is a PostgreSQL schema-as-code management tool written in Rust. It allows defining database schemas in native PostgreSQL DDL, comparing them against live databases, generating migration plans, and applying changes safely with comprehensive safety checks. The tool follows a pipeline architecture with strict module boundaries and deterministic outputs.

## CLI Commands

### 1. `diff` - Schema Comparison
**Location**: `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` (lines 24-29, 92-105)

**Usage**: 
```bash
pgmold diff --from sql:schema.sql --to db:postgres://localhost/mydb
```

**Features**:
- Compares two schemas (both can be SQL files or DB connections)
- Accepts `sql:` or `db:` prefixed sources
- Outputs list of migration operations needed
- Shows count of differences found

### 2. `plan` - Migration Plan Generation
**Location**: `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` (lines 31-37, 107-129)

**Usage**:
```bash
pgmold plan --schema schema.sql --database postgres://localhost/mydb
```

**Features**:
- Generates migration plan comparing SQL schema to live database
- Multi-file schema support (can specify multiple `--schema` arguments)
- Shows SQL statements that would be executed
- Outputs topologically ordered operations

### 3. `apply` - Migration Execution
**Location**: `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` (lines 39-49, 131-200)

**Usage**:
```bash
pgmold apply --schema schema.sql --database postgres://localhost/mydb [--dry-run] [--allow-destructive]
```

**Features**:
- Applies migrations to live database
- **Transactional execution**: All changes in single transaction (rollback on error)
- Multi-file schema support
- **Dry run mode**: `--dry-run` flag shows SQL without executing
- **Destructive operation protection**: `--allow-destructive` flag required for drops
- **Lint integration**: Runs safety checks before applying
- Blocks on lint errors
- Shows success count after execution

### 4. `lint` - Schema Validation
**Location**: `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` (lines 51-57, 202-236)

**Usage**:
```bash
pgmold lint --schema schema.sql [--database postgres://localhost/mydb]
```

**Features**:
- Validates schema for safety issues
- Can lint against database or standalone
- Multi-file schema support
- Shows errors and warnings
- Exit code 1 if errors found
- Uses configurable lint options

### 5. `monitor` - Drift Detection
**Location**: `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` (lines 59-65, 238-266)

**Usage**:
```bash
pgmold monitor --schema schema.sql --database postgres://localhost/mydb
```

**Features**:
- Detects schema drift using SHA256 fingerprinting
- Multi-file schema support
- Shows expected vs actual fingerprints
- Lists specific differences found
- Exit code 1 if drift detected
- CI/CD friendly output

## Core PostgreSQL Object Support

### 1. Tables
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 11-21)

**Capabilities**:
- Full table creation/dropping
- Table comments (parsed but not yet used)
- Row-level security enable/disable (line 19)
- Policies (line 20)

**Operations**:
- `CreateTable` - Creates table with all components
- `DropTable` - Drops entire table

### 2. Columns
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 23-30)

**Supported Types** (lines 32-47):
- Integer types: `INTEGER`, `BIGINT`, `SMALLINT`
- String types: `VARCHAR(n)`, `VARCHAR` (unlimited), `TEXT`
- Boolean: `BOOLEAN`
- Temporal: `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP`, `DATE`
- Special: `UUID`, `JSON`, `JSONB`
- Custom enum types

**Column Properties**:
- Nullability: `NOT NULL` / `NULL`
- Default values
- Comments (parsed but not yet used)

**Operations**:
- `AddColumn` - Adds new column
- `DropColumn` - Removes column (requires `--allow-destructive`)
- `AlterColumn` - Modifies column properties
  - Type changes
  - Nullability changes
  - Default value changes

### 3. Primary Keys
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 65-68)

**Features**:
- Single or composite primary keys
- Inline column-level syntax: `id BIGINT PRIMARY KEY`
- Table-level syntax: `PRIMARY KEY (col1, col2)`

**Operations**:
- `AddPrimaryKey` - Adds primary key constraint
- `DropPrimaryKey` - Removes primary key

### 4. Indexes
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 49-63)

**Index Types Supported**:
- `BTree` (default)
- `Hash`
- `GIN` (Generalized Inverted Index)
- `GiST` (Generalized Search Tree)

**Features**:
- Unique indexes
- Multi-column indexes
- Named indexes

**Operations**:
- `AddIndex` - Creates index
- `DropIndex` - Removes index

### 5. Foreign Keys
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 70-87)

**Referential Actions Supported**:
- `NO ACTION`
- `RESTRICT`
- `CASCADE`
- `SET NULL`
- `SET DEFAULT`

**Features**:
- Named constraints
- Multi-column foreign keys
- Separate ON DELETE and ON UPDATE actions

**Operations**:
- `AddForeignKey` - Creates foreign key constraint
- `DropForeignKey` - Removes foreign key

### 6. Enums (Custom Types)
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 89-93)

**Features**:
- CREATE TYPE ... AS ENUM syntax
- Ordered enum values
- Used as column types

**Operations**:
- `CreateEnum` - Creates enum type
- `DropEnum` - Removes enum type (requires `--allow-destructive`)

**Limitation**: No ALTER TYPE support (cannot add/remove enum values)

### 7. Functions (Stored Procedures)
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 114-174)

**Function Properties**:
- Name and schema
- Arguments with modes: `IN`, `OUT`, `INOUT`, `VARIADIC`
- Return type
- Language: `sql`, `plpgsql`, etc.
- Function body
- **Volatility**: `IMMUTABLE`, `STABLE`, `VOLATILE`
- **Security**: `SECURITY DEFINER`, `SECURITY INVOKER`

**Special Features**:
- Semantic equality comparison (ignores whitespace in body) - line 128
- Function signature matching for overloads - line 200

**Operations**:
- `CreateFunction` - Creates function
- `DropFunction` - Removes function
- `AlterFunction` - Updates function (uses CREATE OR REPLACE)

### 8. Row Level Security (RLS) Policies
**Location**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 95-112)

**Policy Properties**:
- Policy name
- Target table
- **Command scope**: `ALL`, `SELECT`, `INSERT`, `UPDATE`, `DELETE`
- Roles (users/groups policy applies to)
- USING expression (row visibility filter)
- WITH CHECK expression (insert/update validation)

**RLS Features**:
- Enable/disable row-level security per table
- Multiple policies per table
- Policy modification support

**Operations**:
- `EnableRls` - Enables RLS on table
- `DisableRls` - Disables RLS on table
- `CreatePolicy` - Creates RLS policy
- `DropPolicy` - Removes RLS policy
- `AlterPolicy` - Modifies policy properties

## Multi-File Schema Support

**Location**: `/Users/fmguerreiro/projects/pgmold/src/parser/loader.rs`

**Design Document**: `/Users/fmguerreiro/projects/pgmold/docs/plans/2025-12-01-multi-file-schema-support.md`

**Capabilities**:
- Load schema from single file, directory, or glob pattern
- Multiple `--schema` arguments supported
- Recursive directory scanning (`**/*.sql`)
- Glob pattern matching
- Automatic file deduplication

**Usage Examples**:
```bash
# Directory (recursive)
pgmold apply --schema ./schema/ --database postgres://localhost/mydb

# Glob pattern
pgmold apply --schema "schema/**/*.sql" --database postgres://localhost/mydb

# Multiple sources
pgmold apply --schema types.sql --schema "tables/*.sql" --database postgres://localhost/mydb
```

**Conflict Detection**:
- Errors immediately on duplicate table/enum/function definitions
- Error message shows both conflicting file paths
- No silent overwrites

**File Organization Example**:
```
schema/
├── enums.sql           # CREATE TYPE statements
├── tables/
│   ├── users.sql       # users table + indexes
│   └── posts.sql       # posts table + foreign keys
└── functions/
    └── triggers.sql    # stored procedures
```

## Safety & Linting System

**Location**: `/Users/fmguerreiro/projects/pgmold/src/lint/mod.rs`

### Lint Rules

| Rule | Severity | Trigger | Override |
|------|----------|---------|----------|
| `deny_drop_column` | Error | DROP COLUMN | `--allow-destructive` |
| `deny_drop_table` | Error | DROP TABLE | `--allow-destructive` |
| `deny_drop_table_in_prod` | Error | DROP TABLE in production | Cannot override |
| `warn_type_narrowing` | Warning | Type change may lose data | N/A |
| `warn_set_not_null` | Warning | SET NOT NULL (may fail) | N/A |

### Production Mode
**Environment Variable**: `PGMOLD_PROD=1`

**Effect**:
- Completely blocks all DROP TABLE operations
- Cannot be overridden with `--allow-destructive`
- Designed for production environments

### Type Narrowing Detection
**Triggered by**: `/Users/fmguerreiro/projects/pgmold/src/lint/mod.rs` (lines 112-117)
- VARCHAR with length limit
- INTEGER (from BIGINT)
- SMALLINT (from INTEGER or BIGINT)

## Diff Engine

**Location**: `/Users/fmguerreiro/projects/pgmold/src/diff/mod.rs`

### Migration Operations

**Complete list** (lines 8-75):
1. `CreateEnum(EnumType)`
2. `DropEnum(String)`
3. `CreateTable(Table)`
4. `DropTable(String)`
5. `AddColumn { table, column }`
6. `DropColumn { table, column }`
7. `AlterColumn { table, column, changes }`
8. `AddPrimaryKey { table, primary_key }`
9. `DropPrimaryKey { table }`
10. `AddIndex { table, index }`
11. `DropIndex { table, index_name }`
12. `AddForeignKey { table, foreign_key }`
13. `DropForeignKey { table, foreign_key_name }`
14. `EnableRls { table }`
15. `DisableRls { table }`
16. `CreatePolicy(Policy)`
17. `DropPolicy { table, name }`
18. `AlterPolicy { table, name, changes }`
19. `CreateFunction(Function)`
20. `DropFunction { name, args }`
21. `AlterFunction { name, args, new_function }`

### Diff Algorithm
**Function**: `compute_diff(from: &Schema, to: &Schema)` (line 93)

**Process**:
1. Diff enums (added/removed)
2. Diff tables (added/removed)
3. For existing tables:
   - Diff columns (added/removed/altered)
   - Diff primary keys
   - Diff indexes
   - Diff foreign keys
   - Diff RLS settings
   - Diff policies
4. Diff functions (added/removed/altered with semantic comparison)

### Change Detection Features
- **Whitespace-insensitive function comparison** (lines 128-137)
- Detects all property changes on columns
- Identifies foreign key action changes

## Migration Planner

**Location**: `/Users/fmguerreiro/projects/pgmold/src/diff/planner.rs`

### Topological Ordering Algorithm
**Function**: `plan_migration(ops: Vec<MigrationOp>)` (line 7)

**Ordering Phases**:

**CREATE Phase** (safe to add):
1. CreateEnum
2. CreateFunction
3. CreateTable (topologically sorted by FK dependencies)
4. AddColumn
5. AddPrimaryKey
6. AddIndex
7. AlterColumn
8. AddForeignKey
9. EnableRls
10. CreatePolicy
11. AlterPolicy
12. AlterFunction

**DROP Phase** (reverse order):
1. DropPolicy
2. DisableRls
3. DropForeignKey
4. DropIndex
5. DropPrimaryKey
6. DropColumn
7. DropTable
8. DropFunction
9. DropEnum

### Dependency Resolution
**Algorithm**: Kahn's topological sort (lines 159-210)

**Features**:
- Tables with foreign keys created after referenced tables
- Self-referencing foreign keys handled
- Cycle detection implicit (remaining nodes indicate cycle)

## SQL Generation

**Location**: `/Users/fmguerreiro/projects/pgmold/src/pg/sqlgen.rs`

### Features
- **Identifier quoting**: Properly escapes PostgreSQL identifiers (line 278)
- **String escaping**: Handles single quotes in values (line 282)
- Multi-line CREATE TABLE with proper formatting (line 128)
- Separate statements for indexes and foreign keys
- CREATE OR REPLACE for function updates (line 370)

### Generated DDL Examples
- CREATE TYPE with enum values
- CREATE TABLE with columns, constraints
- ALTER TABLE ADD/DROP COLUMN
- ALTER TABLE ALTER COLUMN (separate statements for type, nullability, default)
- CREATE INDEX with type specifiers (USING hash/gin/gist)
- ALTER TABLE ADD/DROP CONSTRAINT
- CREATE FUNCTION with arguments, volatility, security
- CREATE POLICY with roles, USING, WITH CHECK

## Database Introspection

**Location**: `/Users/fmguerreiro/projects/pgmold/src/pg/introspect.rs`

### Data Sources
- `information_schema` - Standard SQL views for portability
- `pg_catalog` - PostgreSQL system catalogs for advanced features

### Introspection Queries

**Enums** (lines 42-54):
```sql
SELECT t.typname, array_agg(e.enumlabel ORDER BY e.enumsortorder)
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE n.nspname = 'public'
```

**Tables** (lines 73-82):
```sql
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
```

**Columns** (lines 109-121):
```sql
SELECT column_name, data_type, character_maximum_length,
       is_nullable, column_default, udt_name
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = $1
```

**Primary Keys** (lines 172-186):
- Uses `pg_index`, `pg_class`, `pg_attribute`
- Preserves column order

**Indexes** (lines 194-237):
- Extracts index type (btree, hash, gin, gist)
- Identifies unique indexes
- Excludes primary key indexes

**Foreign Keys** (lines 240-289):
- Uses `pg_constraint` with LATERAL join
- Maps ON DELETE/UPDATE actions (a=no action, r=restrict, c=cascade, n=set null, d=set default)

**RLS Status** (lines 302-319):
```sql
SELECT c.relrowsecurity
FROM pg_class c
WHERE c.relname = $1 AND n.nspname = 'public'
```

**Policies** (lines 321-363):
- Uses `pg_policy`, `pg_get_expr()` for expressions
- Maps command types (*=all, r=select, a=insert, w=update, d=delete)

**Functions** (lines 376-439):
- Uses `pg_proc`, `pg_get_function_arguments()`, `pg_get_function_result()`
- Maps volatility (i=immutable, s=stable, v=volatile)
- Detects SECURITY DEFINER functions

### Type Mapping
**Function**: `map_pg_type()` (lines 149-166)

Maps `information_schema.columns.data_type` to internal PgType enum.

**Special handling**:
- Detects custom enums via `USER-DEFINED` type + `udt_name`
- Extracts VARCHAR length constraints
- Distinguishes timestamptz from timestamp

## Drift Detection

**Location**: `/Users/fmguerreiro/projects/pgmold/src/drift/mod.rs`

### Fingerprinting
**Implementation**: `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` (lines 185-190)

**Algorithm**:
1. Serialize schema to JSON (deterministic via BTreeMap)
2. Compute SHA256 hash
3. Convert to hex string

**Properties**:
- Same schema always produces same fingerprint
- Any difference produces different fingerprint
- Fast equality check for drift detection

### DriftReport Structure (lines 8-13)
```rust
pub struct DriftReport {
    pub has_drift: bool,
    pub expected_fingerprint: String,
    pub actual_fingerprint: String,
    pub differences: Vec<MigrationOp>,
}
```

### Use Cases
- CI/CD pipelines to detect manual changes
- Production monitoring
- Schema compliance validation

## Parser

**Location**: `/Users/fmguerreiro/projects/pgmold/src/parser/mod.rs`

**Library**: `sqlparser` v0.52 with PostgreSQL dialect

### Supported DDL Statements
1. `CREATE TABLE` - Columns, constraints (line 59)
2. `CREATE INDEX` - Unique, typed (line 63)
3. `CREATE TYPE ... AS ENUM` - Enum definitions (line 80)
4. `CREATE POLICY` - RLS policies (line 94)
5. `ALTER TABLE` - Enable/disable RLS (line 120)
6. `CREATE FUNCTION` - Stored procedures (line 140)

### SQL Preprocessing (lines 29-47)
**Purpose**: Handle syntax not supported by sqlparser 0.52

**Transformations**:
- Strips `SECURITY DEFINER` / `SECURITY INVOKER` (tracked separately)
- Removes `SET search_path` clauses
- Removes `ALTER FUNCTION` statements (ownership changes)

**Detection**: Uses regex to identify these patterns before parsing

### Constraint Parsing
- **Inline PRIMARY KEY**: Column-level syntax (line 191)
- **Table-level PRIMARY KEY**: Constraint syntax (line 205)
- **Foreign keys**: Extracts all FK properties (line 210)
- **Default FK names**: Auto-generates if unnamed (line 222)

## Apply System

**Location**: `/Users/fmguerreiro/projects/pgmold/src/apply/mod.rs`

### ApplyOptions (lines 11-14)
```rust
pub struct ApplyOptions {
    pub dry_run: bool,
    pub allow_destructive: bool,
}
```

### ApplyResult (lines 16-22)
```rust
pub struct ApplyResult {
    pub operations: Vec<MigrationOp>,
    pub sql_statements: Vec<String>,
    pub lint_results: Vec<LintResult>,
    pub applied: bool,
}
```

### Execution Flow
1. Load target schema from sources
2. Introspect current database state
3. Compute diff
4. Plan migration (topological sort)
5. **Lint operations** (blocks on errors)
6. Generate SQL
7. If dry run: return SQL without executing
8. **Begin transaction**
9. Execute all SQL statements sequentially
10. **Commit transaction** (or rollback on error)

### Transaction Properties
- Single transaction for all operations
- All-or-nothing execution
- Automatic rollback on any error
- Uses PostgreSQL DDL transaction support

## Database Connection

**Location**: `/Users/fmguerreiro/projects/pgmold/src/pg/connection.rs`

**Implementation**: sqlx with PostgreSQL driver

**Features**:
- Connection pooling (max 5 connections) - line 12
- Async operations via tokio
- Native TLS support

## Test Coverage

### Unit Tests
**Locations**: Each module has `#[cfg(test)]` blocks

**Examples**:
- `model/mod.rs`: Fingerprint stability (lines 216-253)
- `diff/mod.rs`: All operation types (lines 433-723)
- `lint/mod.rs`: All lint rules (lines 124-227)
- `diff/planner.rs`: Dependency ordering (lines 242-438)
- `pg/sqlgen.rs`: SQL generation (lines 428-598)
- `parser/loader.rs`: Multi-file loading (lines 177-411)

**Total**: 41 unit tests documented in CLAUDE.md

### Integration Tests
**Location**: `/Users/fmguerreiro/projects/pgmold/tests/integration.rs`

**Framework**: testcontainers with real PostgreSQL instances

**Test Scenarios**:
1. `empty_to_simple_schema` - Apply to empty database (lines 22-48)
2. `add_column` - Column addition (lines 50-88)
3. `drop_column_blocked` - Destructive operation linting (lines 90-130)
4. `drift_detection` - Fingerprint-based drift (lines 132-170)
5. `multi_file_schema_loading` - Directory/glob support (lines 172-228)

**Total**: 4 integration tests documented in CLAUDE.md

### Test Fixtures
**Location**: `tests/fixtures/multi_file/`
- Contains multi-file schema example for integration testing

## Design Principles

### 1. Canonical Model is Truth
**Enforcement**: All operations convert to `model::Schema` IR

**Benefits**:
- No direct SQL-to-DB comparisons
- Single source of truth
- Predictable behavior

### 2. Deterministic Output
**Implementation**:
- `BTreeMap` for all collections (ordered by key)
- Sorted `Vec` for indexes, foreign keys, policies
- SHA256 fingerprinting

**Purpose**:
- Repeatable diffs
- Stable fingerprints
- Testable output

### 3. Strict Module Boundaries
**Rules**:
- SQL generation only in `pg/sqlgen.rs`
- Database access only in `pg/`
- No circular dependencies

**Module Dependency Graph**:
```
cli → parser, pg, diff, lint, drift, apply
parser → model
pg → model
diff → model
lint → diff
drift → parser, pg, diff
apply → parser, pg, diff, lint
```

### 4. Fail Fast
**Error Handling**:
- `Result<T>` everywhere
- Custom `SchemaError` enum
- No panics in production code
- Clear error messages with context

## Configuration

### Environment Variables
1. **`PGMOLD_PROD`**: Production mode
   - Value: `"1"` to enable
   - Effect: Blocks all DROP TABLE operations

### Connection Strings
- Standard PostgreSQL format: `postgres://user:pass@host:port/database`
- Prefix required: `db:` for CLI arguments

## Performance Characteristics

### Database Queries
- **Introspection**: One query per object type (enums, tables, columns, etc.)
- **Per-table queries**: Columns, PK, indexes, FKs, policies
- **No N+1**: Batched where possible (e.g., all tables in one query)

### Memory Usage
- Entire schema loaded into memory
- BTreeMap overhead for deterministic ordering
- JSON serialization for fingerprinting

### Transaction Size
- Single transaction for all DDL operations
- No streaming support
- Entire migration plan held in memory

## Known Limitations

### Not Supported
1. **Views**: Materialized or regular views
2. **Triggers**: Trigger definitions
3. **Sequences**: SERIAL/BIGSERIAL explicit sequences
4. **Partitioned tables**: Table partitioning
5. **Multi-schema**: Only `public` schema supported
6. **Enum modifications**: Cannot ALTER TYPE to add/remove values
7. **Column comments**: Parsed but not introspected/applied
8. **Table comments**: Parsed but not introspected/applied
9. **Check constraints**: Not supported
10. **Exclusion constraints**: Not supported
11. **User/role management**: No GRANT/REVOKE support
12. **Extensions**: No CREATE EXTENSION support

### Partial Support
1. **Function arguments**: Mode detection limited (no VARIADIC introspection)
2. **Index types**: Only btree, hash, gin, gist (no brin, spgist)

## Architecture Files

### Key Files and Line Counts
- `/Users/fmguerreiro/projects/pgmold/src/model/mod.rs` - 255 lines (Canonical model)
- `/Users/fmguerreiro/projects/pgmold/src/parser/mod.rs` - 432 lines (DDL parser)
- `/Users/fmguerreiro/projects/pgmold/src/parser/loader.rs` - 413 lines (Multi-file loader)
- `/Users/fmguerreiro/projects/pgmold/src/pg/introspect.rs` - 469 lines (Database introspection)
- `/Users/fmguerreiro/projects/pgmold/src/pg/sqlgen.rs` - 599 lines (SQL generation)
- `/Users/fmguerreiro/projects/pgmold/src/diff/mod.rs` - 725 lines (Diff engine)
- `/Users/fmguerreiro/projects/pgmold/src/diff/planner.rs` - 439 lines (Topological sort)
- `/Users/fmguerreiro/projects/pgmold/src/lint/mod.rs` - 228 lines (Safety rules)
- `/Users/fmguerreiro/projects/pgmold/src/drift/mod.rs` - 97 lines (Drift detection)
- `/Users/fmguerreiro/projects/pgmold/src/apply/mod.rs` - 111 lines (Migration execution)
- `/Users/fmguerreiro/projects/pgmold/src/cli/mod.rs` - 270 lines (CLI commands)
- `/Users/fmguerreiro/projects/pgmold/src/util/mod.rs` - 19 lines (Error types)
- `/Users/fmguerreiro/projects/pgmold/tests/integration.rs` - 228+ lines (Integration tests)

### Documentation
- `README.md` - User-facing documentation
- `CLAUDE.md` - Development guide for AI agents
- `ARCHITECTURE.md` - System architecture overview
- `docs/SPEC.md` - Implementation specification (task breakdown)
- `docs/plans/2025-12-01-multi-file-schema-support.md` - Multi-file design document

## Dependencies

### Production Dependencies
- `clap` v4.5 - CLI argument parsing
- `tokio` v1.35 - Async runtime
- `sqlx` v0.7 - PostgreSQL driver
- `sqlparser` v0.52 - SQL parsing
- `serde` v1.0 - Serialization
- `serde_json` v1.0 - JSON serialization
- `anyhow` v1.0 - Error handling
- `thiserror` v1.0 - Error macros
- `sha2` v0.10 - SHA256 hashing
- `hex` v0.4 - Hex encoding
- `regex` v1.10 - Pattern matching
- `glob` v0.3 - File pattern matching

### Development Dependencies
- `testcontainers` v0.23 - Docker container testing
- `testcontainers-modules` v0.11 - PostgreSQL module
- `tempfile` v3 - Temporary file creation

## Version History

- **v0.2.0** (current) - Added multi-file schema support
- **v0.1.1** - Clippy fixes
- **v0.1.0** - Initial release with core features

## Unique Features

### 1. Native PostgreSQL DDL
Unlike tools that use DSLs or YAML, pgmold uses actual PostgreSQL DDL syntax.

### 2. Semantic Function Comparison
Functions are compared semantically, ignoring whitespace differences in bodies (lines 128-137 of model/mod.rs).

### 3. Production Safety Mode
`PGMOLD_PROD=1` completely blocks table drops, even with `--allow-destructive`.

### 4. Deterministic Fingerprinting
SHA256 hash of canonical JSON representation enables fast drift detection.

### 5. Topological FK Ordering
Automatic dependency resolution for table creation order based on foreign keys.

### 6. Multi-File Organization
Native support for splitting schemas across directories with conflict detection.

### 7. Transactional DDL
All changes in single transaction (PostgreSQL DDL is transactional).

### 8. Row Level Security Support
Full RLS policy management including USING/CHECK expressions.

## Additional Notes

### Schema Scope
- Only works with `public` schema
- All queries filter on `n.nspname = 'public'` or `table_schema = 'public'`
- Multi-schema support listed as future consideration

### Parser Workarounds
SQL preprocessing step removes syntax that sqlparser 0.52 doesn't support, but tracks the information separately (e.g., SECURITY DEFINER).

### Function Overloading
Functions keyed by signature (name + argument types), allowing overloads.

### Error Messages
Clear error messages with file paths for multi-file conflicts and detailed lint explanations.
