FlowMint Docs
Engineering

Database Design

FlowMint relies on a relational model (PostgreSQL) due to the strict hierarchical nature of the data and the necessity for ACID transactions during state changes.

Entity Relationship Diagram (Conceptual)

Key Design Principles

  1. State Machine Logging: Every status change must be appended to an audit_logs table. We do not just update the status row; we record who, when, and why the status changed for compliance.

  2. Storage Optimization (S3/R2):

    • Attachments are stored with UUID-based keys.
    • The database stores an array of file_versions for team-level assets. The latest index is always treated as the "Current" version.

  3. JSONB for Dynamic Tasks: Because workflows are dynamic, the actual submission data is stored as a JSONB payload, allowing for infinite flexibility without schema migrations, while metadata (state, timestamps, owners) remains relational for fast querying.