Risk Assessment

Risk Assessment

The engine classifies every schema change into one of 5 risk levels.

---

Risk Levels

Level	Color	Examples	Default Action
none	🟢 Green	CREATE TABLE, ADD nullable column	✅ Allow
low	🔵 Blue	ADD COLUMN with default, CREATE INDEX	✅ Allow
medium	🟡 Yellow	ALTER COLUMN type (widening), ADD FK	✅ Allow
high	🟠 Orange	DROP INDEX, ALTER type (narrowing)	❌ Block
critical	🔴 Red	DROP TABLE, DROP COLUMN, DROP SCHEMA	❌ Block

---

Level: None 🟢

Zero risk. Safe operations that cannot cause data loss or downtime.

Examples

Operation	Risk	Why
CREATE TABLE	none	New object, no existing data affected
ADD nullable column	none	No constraint violations
ADD CHECK constraint (NOT VALID)	none	Doesn't validate existing data
CREATE SEQUENCE	none	Independent object
CREATE TYPE	none	Type definitions don't affect data

---

Level: Low 🔵

Minimal risk. Operations that may have minor performance impact.

Examples

Operation	Risk	Why
ADD COLUMN with DEFAULT	low	Metadata change + single write
CREATE INDEX (non-concurrent)	low	Brief write lock on table
ADD NOT NULL (with safe pattern)	low	3-step safe execution
ALTER COLUMN SET DEFAULT	low	Metadata-only change

Impact

• Brief lock (milliseconds)
• No data movement
• Can run during normal operations

---

Level: Medium 🟡

Moderate risk. Operations requiring careful planning.

Examples

Operation	Risk	Why
ALTER COLUMN type (widening)	medium	Requires table rewrite for some types
ADD FK constraint (validated)	medium	Validates all rows
ALTER COLUMN type (same category)	medium	int → bigint OK, int → text needs cast
ADD UNIQUE constraint	medium	Requires index, validates data

Impact

• Longer locks (seconds)
• May require table scan
• Plan for low-traffic periods

Safe Patterns Applied

// Engine automatically uses safe patterns
ALTER COLUMN int_col TYPE bigint;  // Widening: OK
ALTER COLUMN int_col TYPE varchar;  // Type change: needs USING

---

Level: High 🟠

High risk. Operations that can cause downtime or require careful rollback.

Examples

Operation	Risk	Why
DROP INDEX	high	May affect query performance
ALTER COLUMN type (narrowing)	high	varchar(100) → varchar(50) truncates data
ADD NOT NULL (without safe pattern)	high	Fails if NULL exists
DROP CONSTRAINT	high	May affect data integrity
ALTER COLUMN DROP DEFAULT	high	Application might depend on it

Default Behavior

const plan = engine.plan(diff);
// High-risk changes block by default
plan.blocked === true;

// Override (dangerous)
await engine.migrate(pool, desired, {
  riskThreshold: 'high'  // Allow high-risk
});

Manual Review Recommended

Before applying high-risk changes:

1. Check application dependencies
2. Verify no data truncation
3. Test in staging
4. Plan rollback

---

Level: Critical 🔴

Critical risk. Destructive operations that cause data loss.

Examples

Operation	Risk	Why
DROP TABLE	critical	permanently deletes all data
DROP COLUMN	critical	permanently deletes column data
DROP SCHEMA	critical	Deletes entire schema and all objects
DROP DATABASE	critical	Complete destruction
TRUNCATE TABLE	critical	Removes all rows without logging
ALTER COLUMN type (lossy)	critical	text → int loses data
DROP TYPE (in use)	critical	Breaks dependent objects

Default Behavior

Always blocked. Cannot be overridden without force: true.

await engine.migrate(pool, desired, {
  force: true,        // Required for critical
  confirmDestructive: (changes) => {
    console.log('These will be destroyed:', changes);
    return true;  // Must explicitly confirm
  }
});

---

Risk Overrides

Per-Change Override

diff.changes.forEach(change => {
  if (change.objectKey === 'public.temp_table') {
    change.risk = 'none';  // Override risk for specific change
  }
});

Global Threshold

const engine = new PgMigrationEngine({
  riskThreshold: 'medium'  // Block medium and above
});

Force All

await engine.migrate(pool, desired, {
  force: true  // Bypass all risk checks (dangerous)
});

---

Risk Detection Logic

The engine evaluates risk based on:

1. Operation type: CREATE < ALTER < DROP
2. Object type: sequence < table < schema
3. Data impact: none < metadata < data
4. Reversibility: reversible < irreversible
5. Dependency check: no dependents < has dependents

// Example: Risk calculation for ALTER COLUMN
const risk = assessRisk({
  operation: 'ALTER_COLUMN',
  fromType: 'varchar(100)',
  toType: 'varchar(50)',
  tableSize: 'large',
  hasData: true
});
// Result: 'critical' (data truncation risk)

---

Warnings vs Blocks

Risk Level	Default	Configurable
none	Allow	No (always allow)
low	Allow	No
medium	Allow	Yes (can block)
high	Block	Yes
critical	Block	No (requires force)

---

See Also

• Safe-Migration-Patterns — How safe patterns reduce risk
• API-Reference — riskThreshold configuration