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.

Transcript

1. José Lorenzo Rodríguez An entire payment gateway in a database
2. What we do
3. ‣ 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
4. An entire payment gateway in a database
5. But, why? An entire payment gateway in a database
6. The need for a new payment gateway
7. The journey of a payment 🧑🦰 Customer 💳 Card Scheme 🏦 Issuing Bank 🏦 Acquiring Bank 🏪 Store Gateway 🌐 Payment Processor 🏛 Acquirer PSP
8. + 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
9. Planning phase
10. 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
11. 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
12. 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
13. 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
14. 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
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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
22. 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
23. 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!
24. 1. Transaction flow is directed by data changes
25. 2. I need to minimize foreign state changes
26. A quick detour to get out of the tar pit
27. How can I tame complexity?
28. 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
29. 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
30. 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
31. State in the database
32. 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
33. 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
34. 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
35. 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
36. 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
37. 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’)
38. 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’)
39. 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’)
40. 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
41. 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
42. 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
43. 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.
44. 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
45. Authoring statecharts
46. Compiling a statechart to database code • We adopted the SCXML standard to author Statecharts • A program translates the XML to a SQL representation
47. 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
48. An API in the database
49. Write operations are state machine transitions
50. Write operations are state machine transitions 🧑🦰 Customer
51. Write operations are state machine transitions 🧑🦰 Customer Gateway API
52. Write operations are state machine transitions select payment_id from create_swish_payment( session_id := …,
 more_args := … … ); 🧑🦰 Customer Gateway API
53. Write operations are state machine transitions select payment_id from create_swish_payment( session_id := …,
 more_args := … … ); 🧑🦰 Customer Gateway API Database
54. 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
55. 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
56. 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
57. 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
58. Async execution
59. 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
60. 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
61. 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
62. 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
63. Database-backed job queue
64. Queuing jobs Store Charge Job Notify via webhook Initializing
65. Concurrency control
66. The serializable isolation level
67. The serializable isolation level
68. The serializable isolation level
69. 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
70. Developer workflow
71. 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
72. 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.
73. 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.
74. 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
75. 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
76. 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.
77. Observability and performance
78. 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
79. Datadog’s Database APM
80. Performance
81. Performance
82. Performance
83. Performance
84. An entire payment gateway in a database?
85. An entire payment gateway in a database?
86. Questions?