Migration Patterns for the Financial Data Schema in Maybe

The Maybe finance application employs additive Rails migrations featuring UUID primary keys, PostgreSQL enums, time-series tables, and polymorphic associations to build an auditable, performant financial ledger that maintains data integrity across accounts, transactions, and historical balances.

Maybe is an open-source personal finance application built on Rails and PostgreSQL that manages sensitive financial data through a rigorously designed database schema. Understanding the migration patterns for the financial data schema reveals how the platform balances immutable ledger requirements with flexible growth, ensuring every dollar is tracked with precision from transaction history to net-worth calculations.

Core Database Migration Patterns

The migration strategy in maybe-finance/maybe follows eight distinct architectural patterns that prioritize data integrity, query performance, and future extensibility.

UUID Primary Keys for Global Identity

All core tables use UUID primary keys rather than auto-incrementing integers. In db/migrate/20240202015428_create_accounts.rb, the schema defines id: :uuid to guarantee globally unique identifiers that prevent accidental key collisions across sharded environments. This enables safe data merges, deterministic IDs for API clients, and easy data sharing between instances.

PostgreSQL Enums for Categorical Data Integrity

The schema leverages PostgreSQL enum types to enforce closed sets of values at the database level. For example, db/migrate/20250701161640_add_account_status.rb establishes enums like account_status and user_role. This prevents invalid data entry and accelerates queries using exact matches like WHERE status = 'active'.

Isolated Time-Series Tables for Historical Analytics

Financial histories are stored in dedicated time-series tables rather than JSON columns or audit logs. The migration db/migrate/20240212150110_create_account_balances.rb implements this by creating tables keyed by (entity_id, date) for balances, security prices, exchange rates, and valuations. This pattern enforces uniqueness per day while allowing efficient historical queries such as net-worth calculations over time.

Polymorphic Entries for Ledger Flexibility

The entries table acts as a polymorphic bridge between accounts and transaction details. Implemented in db/migrate/20240624160611_create_account_entries.rb, this pattern links an Account to any entryable type—such as Transaction, Valuation, or Trade. It provides a single ledger view while preserving domain-specific attributes in separate tables, enabling complex reporting without schema duplication.

Strategic Indexing on Foreign Keys

Performance-critical columns receive indexes immediately upon creation. The migration db/migrate/20250718120146_add_indexes_to_core_models.rb adds indexes to foreign keys like account_id and family_id, as well as categorical columns like type and kind. These indexes speed up joins for account-centric and family-centric reporting queries.

Decimal Precision and Default Values

Monetary columns use fixed-point decimal storage to prevent floating-point errors. In db/migrate/20240223162105_create_transactions.rb, amounts are defined with precision: 19, scale: 4, while sensible defaults like currency: "USD" prevent null-related bugs. This ensures consistent rounding for all financial calculations.

Additive Schema Evolution

New features are introduced through additive migrations rather than table alterations. The migration db/migrate/20250616183654_add_kind_to_transactions.rb demonstrates this by using add_column and add_index to introduce a new enum-like kind field with a default value. This approach keeps historic migrations runnable and minimizes data-loss risk during deployments.

Data Enrichment Isolation

Third-party data integrations are isolated in separate enrichment tables. Migration db/migrate/20250416235420_add_data_enrichments.rb creates tables like data_enrichments, rules, and providers to store external metadata such as merchant names and categories. This keeps the core ledger tables immutable while allowing optional data enhancement.

Querying the Financial Ledger

These migration patterns enable efficient queries that aggregate current balances, historical trends, and enriched transaction details. The following example demonstrates how to load a complete account history using the associations defined in the schema:

# Get an account with its complete financial history in a single query

account = Account.includes(
  :balances,                # daily snapshots

  entries: [:entryable]     # polymorphic ledger entries

).find_by(uuid: params[:id])

# Current balance (cached on the Account model)

current_balance = account.balance_money

# Historical balances for charting

historical = account.balances.order(:date).pluck(:date, :balance)

# All transactions with enriched merchant name (via DataEnrichment)

transactions = account.transactions
                     .joins(:entry)                     # entry → transaction

                     .left_joins(entry: :entryable)    # optional enrichment

                     .select('transactions.*, data_enrichments.name AS merchant_name')
                     .where('transactions.date >= ?', 1.year.ago)

The code leverages migration-defined associations such as has_many :balances, has_many :transactions, through: :entries, and the money gem-based helpers provided by Monetizable in app/models/account.rb.

Key Implementation Files

The following files define the financial data architecture:

• db/migrate/20240202015428_create_accounts.rb – Establishes the main accounts table with UUID primary keys, type discrimination, and balance caching.
• db/migrate/20240223162105_create_transactions.rb – Defines the immutable transaction ledger with decimal precision.
• db/migrate/20240212150110_create_account_balances.rb – Implements time-series balance snapshots for historical reporting.
• db/migrate/20240624160611_create_account_entries.rb – Creates the polymorphic bridge linking accounts to transactions, valuations, and trades.
• db/migrate/20250616183654_add_kind_to_transactions.rb – Demonstrates additive evolution with new columns and indexes.
• app/models/account.rb – Encapsulates domain logic including associations, scopes, and balance calculations.
• app/models/entry.rb – Implements the polymorphic entry logic that powers the unified ledger view.
• app/models/account_balance.rb – Represents daily balance snapshots with date-based querying capabilities.
• app/models/transaction.rb – Handles transaction-specific logic while working with the Entry bridge.
• app/models/valuation.rb and app/models/holding.rb – Investment-specific models following the same time-series patterns for historical valuations and security tracking.

Summary

The migration patterns for the financial data schema in Maybe establish a foundation for reliable financial tracking:

• UUIDs provide globally unique identifiers for safe data merging.
• PostgreSQL enums enforce categorical constraints at the database level.
• Time-series tables isolate historical data for efficient trend analysis.
• Polymorphic entries create a unified ledger without sacrificing domain specificity.
• Additive migrations enable safe schema evolution without data loss.
• Decimal precision guarantees accurate monetary calculations.

These patterns collectively produce an auditable ledger capable of powering net-worth calculations, transaction histories, and investment performance metrics.

Frequently Asked Questions

Why does Maybe use UUIDs instead of auto-incrementing integers for primary keys?

UUIDs prevent key collisions when merging data from different environments or sharing data between users, which is essential for a financial application where data integrity is paramount. As implemented in db/migrate/20240202015428_create_accounts.rb, the id: :uuid approach ensures every record has a globally unique identifier that cannot be easily predicted or duplicated across shards.

How does the polymorphic entries pattern improve financial reporting?

The entries table, defined in db/migrate/20240624160611_create_account_entries.rb, serves as a single ledger that can reference any financial event type—whether a Transaction, Valuation, or Trade. This allows the system to generate unified account statements and net-worth calculations across diverse financial instruments while keeping specific attributes (like trade lots or valuation methods) in dedicated, normalized tables.

What safeguards ensure monetary calculations remain precise?

All monetary values are stored as decimal types with precision: 19, scale: 4 as seen in db/migrate/20240223162105_create_transactions.rb. This fixed-point arithmetic eliminates floating-point rounding errors common in financial calculations. Additionally, default values for currencies and non-null constraints prevent undefined states that could corrupt balance computations.

How does Maybe handle schema changes without breaking existing financial data?

The codebase follows an additive migration strategy demonstrated in db/migrate/20250616183654_add_kind_to_transactions.rb, where new features are added via add_column and add_index operations rather than alter_table or destructive changes. This ensures that historic migrations remain runnable and existing transaction records stay intact when introducing new categorization fields or metadata columns.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}