Share
Embed
An Entire Payment Gateway in a Database
This presentation describes our journey in making a new payment gateway service with the core idea of consistency and simplicity.
We discovered that by having the application logic live inside the database, we could speed up our development process. This was in combination with using statecharts and creating a statecharts interpreter in Postgres to keep our business logic tidy.
José Lorenzo Rodríguez
August 07, 2023
Transcript
86 slides
-
‣ The largest online department store in the Nordics ‣
1100+ colleagues of 38 different nationalities. ‣ A team of 150+ developers ‣ 7 of fi ces across Europe -
The journey of a payment 🧑🦰 Customer 💳 Card Scheme
🏦 Issuing Bank 🏦 Acquiring Bank 🏪 Store Gateway 🌐 Payment Processor 🏛 Acquirer PSP -
+ intermediaries + problems • With each intermediary, there is
an added fee • You can lower fees by using di ff erent acquirers • Each acquirer and payment processor requires a di ff erent contract • Some payment providers compete with merchants in the same space -
The journey of a payment transaction 🌐 Create payment request
Charge customer in Payment Processor Update payment with status Store audit Information Notify via Webhook Credit for these slides should go to: https://brandur.org/idempotency-keys -
The journey of a payment transaction 🌐 Create payment request
Charge customer in Payment Processor Update payment with status Store audit Information Notify via Webhook How do we prevent duplicate payments? Credit for these slides should go to: https://brandur.org/idempotency-keys -
The journey of a payment transaction 🌐 Create payment request
Charge customer in Payment Processor Update payment with status Store audit Information Notify via Webhook Credit for these slides should go to: https://brandur.org/idempotency-keys -
The journey of a payment transaction 🌐 Create payment request
Charge customer in Payment Processor Update payment with status Store audit Information Notify via Webhook Credit for these slides should go to: https://brandur.org/idempotency-keys Each one of these steps could fail -
Create payment request Charge customer in Payment Processor Update payment
with status Store audit Information Notify via webhook Create idempotency Key The journey of a payment transaction -
Create payment request Charge customer in Payment Processor Update payment
with status Store audit Information Notify via webhook Create idempotency Key Each one of these steps could fail The journey of a payment transaction -
Introducing atomic transactions Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 -
Introducing atomic transactions Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change -
Introducing atomic transactions Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change -
Introducing atomic transactions Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change These two are not really atomic -
Identifying state changes Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 payment_created charge_created done -
Identifying state changes Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change payment_created charge_created done -
Identifying state changes Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change payment_created charge_created done -
Identifying state changes Create payment request Charge customer in Payment
Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change payment_created charge_created done There’s an insight here! -
TL;DR 1. State 2. Control Main Sources of Complexity •
Mutable state • Explicit concurrency • Too much power Biggest o ff enders Solution • Reduce • Ban • Restrict 3. Code volume -
Recommendations 1. Data should be handled using the relational algebra
2. Use a restrictive language 3. Prefer DSLs 4. Let another system handle concurrency for you -
Recommendations 1. Data should be handled using the relational algebra
2. Use a restrictive language 3. Prefer DSLs 4. Let another system handle concurrency for you This sounds like everything a relational database does -
Remember this? Create payment request Charge customer in Payment Processor
Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change payment_created charge_created done -
Remember this? Create payment request Charge customer in Payment Processor
Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 Transaction 2 Foreign state change Foreign state change payment_created charge_created done This looks like a state machine -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done processing_charge Transaction 3 -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done processing_charge notify (‘payment_created’) Transaction 3 -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) notify (‘payment_done’) -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Noti fi cation Job Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) notify (‘payment_done’) -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Noti fi cation Job Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) notify (‘payment_done’) States -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Noti fi cation Job Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) notify (‘payment_done’) States Transitions -
Changing it into a state machine Create payment request Charge
customer in Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 4 Transaction 2 initialized charge_created done Store Noti fi cation Job Store Charge Job processing_charge notify (‘payment_created’) Transaction 3 notify (‘charge_created’) notify (‘payment_done’) States Transitions Events -
Transaction flow is directed by data Statechart: A Statechart is
a state machine where each state in the state machine can have subordinate states, called substates. -
Statecharts crash course •Regions may contain substates •Entering regions activate
substates •Actions cause transitions •Entering a state may trigger an event •Leaving a state may trigger an event -
Compiling a statechart to database code • We adopted the
SCXML standard to author Statecharts • A program translates the XML to a SQL representation -
The statecharts API Creates a new state machine instance Using
the latest version of a state chart Transitions to the initial state and triggers relevant events Transitions to the corresponding state and triggers relevant events -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); 🧑🦰 Customer Gateway API -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); 🧑🦰 Customer Gateway API Database -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); function create_swish_payment(session_id uuid, …) begin machine_id := select id from fsm.create_state_machine(..); payment := insert into payments (…, machine_id) values (…, machine_id) returning *; perform fsm.start_machine(.., machine_id); return payment; end; 🧑🦰 Customer Gateway API Database -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); function create_swish_payment(session_id uuid, …) begin machine_id := select id from fsm.create_state_machine(..); payment := insert into payments (…, machine_id) values (…, machine_id) returning *; perform fsm.start_machine(.., machine_id); return payment; end; 🧑🦰 Customer Payment Processor Gateway API Database -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); function create_swish_payment(session_id uuid, …) begin machine_id := select id from fsm.create_state_machine(..); payment := insert into payments (…, machine_id) values (…, machine_id) returning *; perform fsm.start_machine(.., machine_id); return payment; end; select con fi rm_swish_payment( payment_id := …, more_args := … … ); 🧑🦰 Customer Payment Processor Gateway API Database -
Write operations are state machine transitions select payment_id from create_swish_payment(
session_id := …, more_args := … … ); function create_swish_payment(session_id uuid, …) begin machine_id := select id from fsm.create_state_machine(..); payment := insert into payments (…, machine_id) values (…, machine_id) returning *; perform fsm.start_machine(.., machine_id); return payment; end; select con fi rm_swish_payment( payment_id := …, more_args := … … ); function con fi rm_swish_payment(payment_id uuid, …) begin machine_id := select machine_id from payments where id = payment_id; perform fsm.notify_state_machine(machine_id, ‘payment_done’); end; 🧑🦰 Customer Payment Processor Gateway API Database -
The transactional outbox pattern Create payment request Charge customer in
Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Transaction 1 Transaction 3 -
The transactional outbox pattern Create payment request Charge customer in
Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Foreign state Foreign state change Transaction 1 Transaction 3 -
The transactional outbox pattern Create payment request Charge customer in
Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Store Noti fi cation Job Store Charge Job Foreign state Foreign state change Transaction 1 Transaction 3 -
The transactional outbox pattern Create payment request Charge customer in
Payment Processor Update payment with status Store audit Information Notify via webhook Create idempotency Key Store Noti fi cation Job Store Charge Job Foreign state Foreign state change Transaction 1 Transaction 3 Jobs table Service commits Relay Worker Fetch jobs Publish events -
The serializable isolation level • A retry-based concurrency control system
similar to Software Transactional Memory (STM) • Guarantees that transactions are executed as if they were one after the other • Prevents all known anomalies • Requires no additional thought from the developer (!) • Our app transparently retries transactions -
Database as code Table, views, functions, migrations are stored as
SQL in git. ~One fi le per object We use sqitch to manage db code Database code is deployed using CI. A typical deployment takes less than 1s -
Local development • Database objects are modi fi ed as
source code • Each developer has a local postgres instance using docker • Changes are applied locally and tests run locally too • A custom cli tool to create templated fi les for new functions, tables, statecharts and migrations. -
Local development • Database objects are modi fi ed as
source code • Each developer has a local postgres instance using docker • Changes are applied locally and tests run locally too • A custom cli tool to create templated fi les for new functions, tables, statecharts and migrations. -
Testing • We use a combination of end-to-end tests and
unit tests • Unit tests are written in SQL using PGTap • The bulk of our test are end-to-end -
Debugging • PgAdmin 4 for step debugging • psql as
a repl for experimentation and debugging tests • Having rollback for experimentation is a great boon. • Observation: our SQL code tends to have little complexity. Branching occurs in the statechart. Loops are declarative -
Static Analysis • Postgres already statically analyzes queries and SQL
functions. • plpgsql_check for static analysis of other functions. • Queries in application code are checked against production and future schema before deploying. -
Tracing in the database All write transactions call this function
and context is stored in job payloads Logs contain tracing data stored in the database We can correlate traces in dispatched async jobs