July 2021 Summaries

Calculating Monthly Recurring Revenue (MRR) accurately is crucial for Sequin, which uses it as a primary metric to align its team's focus on growth. Initially, attempts to calculate MRR based on the latest paid invoices led to inaccuracies due to non-subscription items or partial subscription representations. To resolve this, a refined SQL query was developed to align with Stripe's MRR calculation by focusing on active subscription items and their quantities, offering a forward-looking metric that updates with any changes in customer subscriptions. This approach ensures real-time, accurate MRR tracking by considering the most recent subscription items' projected revenue, even before billing occurs. Additionally, for backward-looking reports that require analyzing past revenue, a separate query sums the amounts from paid invoices for subscription line items, reflecting actual revenue earned. These queries provide a robust foundation for businesses to customize their MRR calculations according to specific needs, such as handling multiple currencies or applying discounts and taxes.

Sequin offers a solution for integrating Stripe data into applications by allowing developers to build apps and workflows using SQL and Postgres, with seamless synchronization between APIs like Stripe and a database. This approach addresses challenges when an app's functionality relies on data from both Stripe and the app's database, such as displaying user-specific content based on subscription status. The text explores methods to manage Stripe data, including querying the Stripe API directly, maintaining a local copy of the data, and using Stripe's webhooks. Each method has its pros and cons, such as API rate limits, the complexity of webhook management, and the difficulty of maintaining a real-time database. Sequin simplifies this by enabling real-time data synchronization without extensive development, providing a scalable and flexible solution for integrating Stripe data into applications.

Sequin, a Postgres CDC tool, highlights the challenges associated with using webhooks for maintaining data consistency between systems, advocating instead for an /events endpoint approach similar to database replication models like Postgres' replication slots. Webhooks, while useful for reducing polling, can be unreliable due to issues like undeliverability and their ephemeral nature, often necessitating the use of message bus systems like Kafka, which adds complexity. In contrast, an /events endpoint allows for polling of event logs, providing a more consistent and reliable method for syncing data without the need for complex delivery pipelines. By keeping a local cursor, systems can efficiently manage updates and catch up post-downtime without missing data, while avoiding the pitfalls of webhook failures. The author suggests enhancements such as long-polling to improve efficiency and real-time capabilities. Ultimately, the choice between webhooks and polling /events depends on the consistency requirements of the API consumer, with /events offering greater reliability for critical integrations, and for API producers, supporting /events can enhance the robustness and feature set of their webhook systems.

Sequin, a platform that enables building applications and workflows with SQL and Postgres on top of APIs like Stripe, shares insights from developing a real-time data synchronization process between Stripe and Postgres. The synchronization strategy involves two primary processes: an initial backfill to populate the database with historical Stripe data and a continuous event polling mechanism to keep the database updated. The backfill process requires navigating through Stripe's extensive API, converting JSON responses into a structure suitable for Postgres, and managing nested data. The event polling process uses Stripe's events endpoint to capture account changes, ensuring minimal lag and maintaining data accuracy. Unlike webhooks, which can be unreliable due to delivery issues and system downtime, the event polling approach is more robust, offering a reliable means of syncing data. Sequin's system allows users to write back to Stripe, ensuring that local database states remain current and consistent with Stripe's data.

Sequin's process for real-time replication of Airtable's API data to Postgres databases faced challenges due to Airtable's limitations, such as difficulty in detecting changes, a flexible schema, and low throughput. Initially, Sequin implemented a "rebuild sync" method, which required reconstructing the entire database with each sync to accommodate schema changes. To improve efficiency, Sequin transitioned to syncing directly to the public schema using upsert statements, reducing lag time and write operations. Further optimization involved in-memory fingerprinting to reduce database read operations, shifting the processing load to workers. This iterative development eventually led to the creation of a proxy that ensures immediate consistency between Airtable writes and subsequent database reads, enhancing the system's reliability and performance.