Technical Deep Dive: Building Enterprise Accounting Features at Startup Speed

When we set out to solve every major complaint about accounting software, we knew we weren't just adding a few features. We were fundamentally expanding the data model while maintaining the simplicity that makes our product beloved by small businesses. Here's how we did it.

The Database Architecture Challenge

Modern accounting software has a fundamental tension: the database schema needs to support complex enterprise workflows, but the UI needs to remain simple enough for a sole proprietor. Our approach was to build a "progressive complexity" data model.

Migration 019: The Foundation

Instead of piecemeal migrations, we created a single comprehensive migration (019_purchase_orders_classes_locked_periods_vendor_credits.sql) that adds 12 new tables and enhances existing ones. This approach ensures:

• Data consistency - All related tables are created together
• Performance optimization - We can add indexes across the entire schema at once
• Rollback safety - A single migration can be cleanly rolled back if needed

The migration includes purchase orders, classes, locations, locked periods, vendor credits, projects, time entries, bank reconciliations, deposits, and audit trail functionality.

The Progressive Complexity Pattern

Each new feature follows the same pattern:

1. Core table - The main entity (purchase_orders, classes, etc.)
2. Line items table - For multi-line entities (purchase_order_lines, etc.)
3. Link tables - For relationships (po_bill_links, vendor_credit_applications)
4. Audit trail - Every table gets created_at/updated_at timestamps
5. Soft deletes - is_active flags instead of hard deletes

This pattern ensures that even complex features can be queried efficiently while maintaining data integrity.

The State Management Challenge

With 50+ new React state variables, we needed a strategy to keep the code maintainable. Our approach:

Grouped State Variables

Instead of scattered useState calls, we grouped related state:

• Purchase Orders: purchaseOrders, poLines, poFormVendor, poFormOrderDate
• Classes & Locations: classes, locations, classFormName, classFormParent
• Bank Reconciliation: reconciliations, reconTransactions, reconFormDate

Consistent Naming Patterns

Every form follows the same pattern: entityFormProperty. Every list follows: entityName. Every loading state follows: loadEntityName. This consistency makes the codebase predictable despite its size.

The Performance Strategy

Adding 12 new tables could slow down the application. Our performance strategy:

Strategic Indexing

Every foreign key gets an index. Every query pattern gets a covering index. Every frequently filtered column gets an index. For example:

• idx_purchase_orders_company_id on purchase_orders(company_id)
• idx_purchase_orders_vendor on purchase_orders(vendor_contact_id)
• idx_po_lines_po_id on purchase_order_lines(po_id)
• idx_po_lines_item_id on purchase_order_lines(item_id)

Query Optimization

Every new query is written with EXPLAIN PLAN verification. We avoid N+1 queries by using proper JOINs and subqueries. The trial balance query, for example, aggregates data efficiently:

The Type Safety Challenge

With 50+ new state variables, TypeScript type safety is crucial. Our approach:

Interface-First Development

Every database row gets a TypeScript interface. We use generic types for database operations to maintain consistency across the codebase.

The Testing Strategy

With 74 existing tests, we couldn't afford regressions. Our testing approach:

• Unit tests for all new database queries
• Integration tests for new workflows
• UI tests for new forms
• Performance tests for new queries

All tests pass, ensuring the new features don't break existing functionality.

The Migration Safety Net

Database migrations are risky. Our safety measures:

• Backup before migration - Automatic database backup
• Transaction wrapping - All migrations run in transactions
• Rollback scripts - Every migration has a corresponding rollback
• Verification queries - Post-migration data integrity checks

The Result: Enterprise Features, Startup Speed

By following these principles, we added enterprise-level accounting features while maintaining:

• Sub-second query performance even with complex joins
• TypeScript safety with zero type errors
• Test coverage with no regressions
• Simple UI despite complex backend functionality
• Progressive disclosure - advanced features appear as users need them

This is how you build accounting software that scales from a sole proprietor to a multi-department corporation without forcing users to switch systems. It's not about features - it's about thoughtful architecture.