This document provides an in-depth explanation of Trac's transaction management system, focusing on the technical design, implementation choices, and scalability considerations. Specifically, it examines the challenges associated with handling transactions between members, organisations, and third parties, as well as the impact of partitioning and database architecture on system performance. The key themes discussed include how transactions are balanced, the necessity of associating transactions with members, and the role of partitioning using AWS DynamoDB. Additionally, this document touches upon how external tools like Snowflake and Elasticsearch are integrated for reporting and analytics purposes.

Scope

This document covers the following components of Trac:

• API Endpoints: For handling transactions and organisational data.
• Transaction Models: Including member-linked transactions and organisational (non-member) transactions.
• Partitioning Strategy: Using DynamoDB for large-scale transaction management across schools.
• Data Synchronisation: Between real-time data in DynamoDB, Elasticsearch, and historical reporting in Snowflake.
• Design Limitations: Challenges related to partitioning, including roaming pupils and fake members for non-standard transactions.

Description

Design Philosophy

The core purpose of Trac is to track and manage financial transactions between members of an organisation, third parties, or the organisation itself. Every transaction must balance against a member's account to ensure accurate financial tracking. This design enforces consistency by requiring that all sales or credits in the system are accounted for by specific entities, avoiding the creation of "phantom money." The requirement for associating transactions with a member is rooted in financial accuracy. For instance, a sale without an associated member would mean there is no balance to decrement, which contradicts the double-entry bookkeeping principle where every transaction has a corresponding credit or debit. This is particularly important for maintaining financial records in compliance with standard accounting practices.

Transaction Handling with Non-Members

Occasionally, transactions may not directly involve a typical member (e.g., individual staff members or temporary contractors). In such cases, Trac introduces the concept of fake members. These "fake" members allow transactions to be associated with a proxy account, ensuring that all transactions still balance, even if they do not belong to a standard member. The fake members allow for the necessary accounting, ensuring that even transactions not tied to specific individuals are covered under a designated entity. This ensures the integrity of the system while accommodating non-standard use cases.

Partitioning for Scalability

A critical design decision in Trac revolves around the use of AWS DynamoDB for database partitioning. Trac needs to scale for handling potentially millions of transactions within short periods—such as when entire schools process lunch payments simultaneously. To achieve this, DynamoDB partitions data per organisation (typically schools), ensuring that transactions for each entity are processed in parallel on separate compute resources. This architecture enables Trac to scale horizontally, improving throughput and reducing bottlenecks during peak times. However, this partitioning introduces strict limitations on the relationships between entities. Transactions in one organisation cannot reference entities in another organisation, across partition boundaries. Typically this is appropriate, however there are cases such as for roaming pupils, where pupils may belong to one school (and thus one partition) but make transactions in another. In this case, due to the partitioning, the system cannot link a pupil's transactions across multiple schools. To manage this, you should create separate transactions for each partition (school), ensuring that each organisation’s records remain consistent. One transaction records the sale to a "roaming pupils" account and the other transaction records the debit against the pupil.

Data Synchronisation for Reporting

To overcome the constraints imposed by partitioning, Trac employs a multi-tiered data storage approach:

• DynamoDB: Manages real-time transaction data per partition, ensuring that each organisation (school) has immediate access to its current financial state.
• Snowflake: Acts as an analytics database where data from all organisations is consolidated for reporting purposes. Since Snowflake supports relational database capabilities, it allows for cross-organization reporting and analysis. However, the data in Snowflake lags by 6 to 24 hours, making it unsuitable for real-time reporting.
• Elasticsearch (OpenSearch): Provides an intermediate solution for near-real-time reporting. Elasticsearch is typically between 300 milliseconds and 30 seconds behind DynamoDB, offering a balance between speed and completeness for certain types of queries, such as free-text searches and aggregation reporting.

By utilising this multi-layered approach, Trac ensures that users can access real-time data for immediate needs (via DynamoDB) while still being able to perform comprehensive reporting across partitions using Snowflake.