ADR-002: Database Schema Design

Status: Proposed
Date: 2026-01-24

Context

We need a structure that supports both efficient transactional quote creation (OLTP) and historical trend analysis (OLAP), while handling highly flexible part specifications.

Decision

Snowflake Schema (Normalized Core) + JSONB Metadata.

Rationale

• Normalization: Core entities (Parts, Materials, Machines, Orders) are normalized to ensure referential integrity for publish/lock logic.
• JSONB Specs: Part-specific dimensions (Diameter, OD, Tolerance) vary by industry; storing these in a JSONB blob allows flexibility without schema migrations.
• Fact/Dimension: While not a pure star schema, we utilize fact_cost_line tables to allow easy aggregation in Superset dashboards.

Consequences

• Positive: Highly flexible; easy to add plastics or assembly costing later.
• Negative: Joins are required for full quote views; mitigated by materialized views if performance drops.