Schema Evolution

🧠 Automatic Schema Evolution

Stackhouse runs on PostgreSQL. Automatic schema evolution is implemented by the Schema-Later Guard (stackhouse/src/security/guard.rs), which caches known table schemas in a DashMap, diffs incoming JSON payload keys against them on every write, and issues ALTER TABLE ... ADD COLUMN IF NOT EXISTS for any new fields. Type inference itself lives in stackhouse/src/inference.rs.

This is a separate system from the versioned migration service (stackhouse/src/db/schema_migrations.rs), which tracks developer-authored up_sql/down_sql migrations with checksums and rollback — that system does not run automatically on writes.

How It Works

Rendering diagram…

A table is capped at MAX_COLUMNS_PER_TABLE = 1000 columns to prevent "schema bloat" attacks from unbounded payload keys.

Type Inference

Implemented by infer_type() in stackhouse/src/inference.rs:

JSON Type	PostgreSQL Type
`string`	`TEXT`
`integer`	`BIGINT`
`float`	`DOUBLE PRECISION`
`boolean`	`BOOLEAN`
`array`	`JSONB`
`object`	`JSONB`
`null`	`NULL` (column skipped; all dynamic columns are nullable)

When merging schemas across a batch of documents, conflicting types are promoted to a common type via PgType::common_type() — e.g. BIGINT + DOUBLE PRECISION promotes to DOUBLE PRECISION; anything mixed with an object/array promotes to JSONB; anything promotes to TEXT as a fallback.

Example Evolution

// Request 1: {name: "Alice", age: 25}
→ CREATE TABLE users (name TEXT, age BIGINT)

// Request 2: {name: "Bob", email: "bob@..."}
→ ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT

// Request 3: {name: "Carol", tags: ["dev", "rust"]}
→ ALTER TABLE users ADD COLUMN IF NOT EXISTS tags JSONB

Benefits

✅ Zero downtime
✅ No manual migrations for new fields on existing tables
✅ Handles any JSON structure
✅ Backward compatible (existing columns/rows are untouched)

Next: Querying