How to Integrate Retool with Sage Pay (Opayo)

Log in to the My Sage Pay portal at mysagepay.com (or the Opayo portal at admin.opayo.co.uk for newer accounts). Navigate to Settings → Integration or the API Settings section. In this section you will find your integration credentials for the PI API (Payment Integration API): an Integration Key (similar to a username) and an Integration Password. These two values together form the Basic Auth credentials for all API calls. Copy both values carefully — they are long alphanumeric strings. Opayo provides two separate sets of credentials: one for the test/sandbox environment and one for the live production environment. Always start with sandbox credentials to test your Retool integration safely before connecting to the live payment environment. The Opayo sandbox uses the base URL https://pi-test.sagepay.com/api/v1 and the live environment uses https://pi.sagepay.com/api/v1. You will create two separate Retool Resources — one for sandbox and one for live — to prevent accidental live transactions during development and testing. Store both sets of credentials in Retool configuration variables: create OPAYO_TEST_KEY, OPAYO_TEST_PASSWORD for sandbox, and OPAYO_LIVE_KEY, OPAYO_LIVE_PASSWORD for live — all marked as secret. The Basic Auth header value is created by Base64-encoding the string 'integration_key:integration_password'. You will configure this in the Retool Resource in the next step.

In Retool, click the Resources tab and click Add Resource. Select REST API from the resource type list. Name the first Resource Opayo API (Sandbox). In the Base URL field, enter https://pi-test.sagepay.com/api/v1. Configure Basic Authentication: in the Authentication section of the resource form, look for an Authentication type dropdown. If Retool shows a Basic Auth option, select it and enter the Integration Key as the username field and the Integration Password as the password field, referencing the configuration variables: {{ config.OPAYO_TEST_KEY }} and {{ config.OPAYO_TEST_PASSWORD }}. If Retool does not show a dedicated Basic Auth option, configure it manually in the Headers section: add an Authorization header with value Basic {{ btoa(config.OPAYO_TEST_KEY + ':' + config.OPAYO_TEST_PASSWORD) }}. However, Retool's {{ }} expressions in resource headers do not support btoa() — you need to pre-compute the Base64-encoded value. Use a JavaScript query in Retool to compute btoa('your_key:your_password') and store the result as a configuration variable OPAYO_TEST_BASIC_AUTH. Then set the Authorization header to Basic {{ config.OPAYO_TEST_BASIC_AUTH }}. Add a second header: Content-Type with value application/json. Click Save to save the sandbox Resource. Repeat this process to create a second Resource named Opayo API (Live) using the live base URL https://pi.sagepay.com/api/v1 and the live credentials OPAYO_LIVE_KEY and OPAYO_LIVE_PASSWORD. Having two separate Resources makes it easy to switch an app between sandbox and live by changing which Resource each query uses.

1// JavaScript query to compute Basic Auth header value
2// Run this once to generate the Base64 value for each environment
3
4// For sandbox:
5const sandboxAuth = btoa(
6  retoolContext.configVars.OPAYO_TEST_KEY + ':' +
7  retoolContext.configVars.OPAYO_TEST_PASSWORD
8);
9
10// For live (store separately):
11const liveAuth = btoa(
12  retoolContext.configVars.OPAYO_LIVE_KEY + ':' +
13  retoolContext.configVars.OPAYO_LIVE_PASSWORD
14);
15
16return {
17  sandbox_basic_auth: sandboxAuth,
18  live_basic_auth: liveAuth
19};
20// Copy the output values and store as:
21// OPAYO_TEST_BASIC_AUTH and OPAYO_LIVE_BASIC_AUTH configuration variables

Create a query to retrieve transaction data from Opayo. In your Retool app, open the Code panel and click Create new query. Select the Opayo API (Sandbox) Resource. Opayo's PI API structure focuses on transaction management rather than bulk reporting — to retrieve a specific transaction, you query /transactions/{transactionId}. For searching transactions by date or status, you may need to use Opayo's reporting API or rely on your own database records of Opayo transaction IDs to look up each one individually. Set Method to GET. In the Path field, enter /transactions/{{ transactionIdInput.value }} to look up a specific transaction by ID. Add a text input component named transactionIdInput to the canvas for entering the transaction ID. Alternatively, if you maintain a database of your Opayo transaction IDs alongside customer records, create a database query first to fetch the list of relevant transaction IDs (by customer email, date range, or amount), then use a Retool Workflow or looped JavaScript query to fetch each transaction's details from Opayo and merge them into a unified dataset. Run a test query with a known sandbox transaction ID to see the response structure — Opayo returns a JSON object with status, statusCode, statusDetail, transactionId, merchantSessionKey, amount, currency, and card payment details.

1// JavaScript transformer for Opayo transaction detail response
2const txn = data || {};
3const payment = txn.paymentMethod?.card || {};
4
5return {
6  transaction_id: txn.transactionId || '',
7  merchant_session_key: txn.merchantSessionKey || '',
8  status: txn.status || '',
9  status_code: txn.statusCode || '',
10  status_detail: txn.statusDetail || '',
11  amount_pence: txn.amount?.totalAmount || 0,
12  amount_display: txn.amount?.totalAmount
13    ? `£${(txn.amount.totalAmount / 100).toFixed(2)}`
14    : 'N/A',
15  currency: txn.amount?.currency || 'GBP',
16  card_type: payment.cardType || '',
17  card_last_four: payment.lastFourDigits || '****',
18  card_expiry: payment.expiryDate || '',
19  cardholder_name: payment.cardholderName || '',
20  billing_address: txn.billingAddress
21    ? [txn.billingAddress.address1, txn.billingAddress.city, txn.billingAddress.postalCode].filter(Boolean).join(', ')
22    : '',
23  created_at: txn.createdAt || txn.date || '',
24  avs_check: txn.avsCvcCheck?.address || 'N/A',
25  cvc_check: txn.avsCvcCheck?.securityCode || 'N/A',
26  is_refundable: txn.status === 'Ok' && (txn.amount?.totalAmount || 0) > 0
27};

With the transaction query working, build the payment operations dashboard. At the top of the canvas, add a search area in a Container: add a Text Input named transactionIdInput labeled Transaction Reference, a second Text Input for customer email (if your database can translate email to transaction IDs), and a Button labeled Look Up Transaction. Below the search area, add a detail Container that shows transaction details when a transaction is found. Bind each text field in the container to the properties of the transaction query result — txn.status_display, txn.amount_display, txn.card_last_four, etc. Add a status indicator: use a Tag component with green color for 'Ok' status and red for 'NotAuthed' or failed statuses. Add a second Container for the Refund section that appears only when txn.is_refundable is true (bind the container's Show property to {{ getTransaction.data.is_refundable }}). Inside the refund container, add a Number Input labeled Refund Amount (£) with a max value constraint set to the original transaction amount. Add a Text Area labeled Reason for Refund. Add a Button labeled Process Refund that is wired to a POST query targeting /transactions/{{ transactionIdInput.value }}/refund. The refund query body should include: transactionType: 'Refund', amount: {{ Math.round(refundAmountInput.value * 100) }} (converting pounds to pence), currency: 'GBP', description: {{ refundReasonInput.value }}, and parentTransactionId: {{ transactionIdInput.value }}. Add a Modal confirmation dialog before submitting the refund — the user must confirm the amount and reason before the actual API call fires.

1// POST query body for Opayo refund
2// Method: POST
3// Path: /transactions
4// Body type: JSON
5{
6  "transactionType": "Refund",
7  "amount": {{ Math.round(refundAmountInput.value * 100) }},
8  "currency": "GBP",
9  "description": "{{ refundReasonInput.value }}",
10  "parentTransactionId": "{{ getTransaction.data.transaction_id }}",
11  "vendorTxCode": "REFUND-{{ Date.now() }}"
12}

Extend the dashboard to handle recurring payment management — a common need for subscription businesses using Opayo. Create a query at /tokens to retrieve saved payment tokens (card-on-file records linked to customer references). Display tokens in a Table with customer reference, card type, masked card number, and expiry date. Add a Repeat Payment button for selected tokens that triggers a POST to /transactions with transactionType: 'Repeat', referencing the stored token. For the live vs. sandbox environment switch, add a Toggle or Select component at the top of the dashboard labeled Environment with options Sandbox and Live. Create separate query versions that reference either the Opayo sandbox Resource or the live Resource based on the toggle value — or use the toggle value to drive a conditional in a wrapper JavaScript query that selects the appropriate Resource. Add a prominent warning banner that changes from gray to red when the Live environment is selected to prevent accidental live transactions during testing. For UK businesses processing high volumes or needing custom authorization flows, token vaulting integrations, and 3D Secure v2 compliance reporting in Retool, RapidDev can help architect the complete Opayo payment operations solution.

1// POST query body for Opayo repeat payment using stored token
2// Method: POST
3// Path: /transactions
4{
5  "transactionType": "Repeat",
6  "amount": {{ repeatAmountInput.value * 100 }},
7  "currency": "GBP",
8  "description": "{{ repeatDescriptionInput.value }}",
9  "parentTransactionId": "{{ tokensTable.selectedRow.transaction_id }}",
10  "vendorTxCode": "REPEAT-{{ tokensTable.selectedRow.customer_ref }}-{{ Date.now() }}",
11  "statementReference": "{{ statementRefInput.value || 'Subscription renewal' }}"
12}