Building a Transaction Datastore

This guide presents some of the options that you have for creating and maintaining a transaction datastore, if you are the system of record for your program. If Galileo is your system of record, see Transaction History instead.

• Building a transaction datastore
• Uniquely identifying transactions
• Identifying accounts
• Updating transaction records
• Transaction sequence examples
• Using the RDFs to reconcile

According to your business use case, you may follow the examples presented here or you can devise other strategies. Along with this guide you might want to read these guides:

• About Transactions — A general explanation of how transactions are handled by the Galileo system.
• About Card Transactions — An explanation of how card transactions work, plus what transaction sequences are produced by different types of card transactions.
• Card Transaction Examples — Different types of card transactions as returned by the Program API.
• Card Transaction Scenarios — Detailed examples of card transactions as they appear in the Auth API, Events API, Program API, and RDFs.

You will need to keep a transaction datastore for reasons such as these:

• Present to account holders their transaction data on your app or website
• Send account holders a monthly statement, summarizing all transactions during that period
• Analyze transaction data for various purposes, such as spending categorization

Galileo recommends that you maintain two datastores:

• Production — Contains the most up-to-date information on account balance and transaction data. Used primarily for immediate data provision, such as displaying the latest transactions in a mobile app. Data is frequently updated.
• Historical — Contains a stable, definitive record of all transactions. Best for creating statements or performing data analysis. Data is no longer being updated.

Building a transaction datastore

When building your own transaction datastores, you will need to decide among these options:

• Which data sources to use
  • Events API — Real-time transaction notifications that you can subscribe to. Used for populating the production datastore.
  • Program API — On-demand retrieval of all transactions or specific types only. Used to populate the production datastore.
  • RDFs — Authoritative records of all transactions that took place across your programs during a 24-hour period. Used for the historical datastore.
• How to uniquely identify a transaction in your datastore.
• Whether to overwrite previous transaction records in the production datastore when an update arrives, or whether to maintain the whole list of transactions.

Data sources

Galileo offers these data sources for you to build your own transaction datastores:

Events API

The Events API comprises these transaction-related message types that you can subscribe to:

• Authorization Events — Based on notifications from card networks when a card transaction is authorized
• Settlement Events — Based on notifications from card networks when an authorized transaction is cleared
• Transaction Events — Notifications from the Galileo system about payments, adjustments, fees, and holds

See About the Events API for general information, and read Transaction event timestamps and account balance in the Event Setup and Configuration guide for an explanation of the hazards of using event messages to keep track of the balance.

📘

Note

The Account Events messages communicate events that do not affect the account balance, such as account creation and status changes, updates to the customer profile, card activation and expiration, dispute progress, mobile wallet statuses and overdraft. For examples of these events, see Account events in the Events API Scenarios guide.

Program API

See Program API Data-Retrieval Endpoints in the Transaction History guide for the endpoints to use for data display in your interface. To get a snapshot of the transactions in Galileo's ledger, call Get All Transaction History, which returns all authorizations (pending, reversed, expired, settled), all posted transactions (payments, adjustments, fees) and all authorization backouts.

📘

Note

Do not use the calculated_balance value in the Get All Transaction History response for reconciliation or processing. This value is calculated at the time of the API request and so may not accurately portray what the account balance was at the time of the transaction. It is intended only to be a helpful reference and may be used by customer service agents, but it should not be considered authoritative.

RDFs

Every day, Galileo sends you RDFs containing an authoritative list of transactions that took place in your program during the previous day, from 00:00:00 through 23:59:59, Galileo system time. You can use these files to verify that your production datastore is accurate. You can also create new records in your transaction history from these files if your record is missing any transactions.

For more information see the About the Raw Data Files (RDFs) guide.

Auth API

You can use the Authorization Controller API webhook to participate in the authorization process. Each webhook message provides detailed data on every transaction that arrives at Galileo through the authorization stream:

• Authorization requests
• Preauthorization requests
• Completion notifications
• Advices
• Reversals

If you consume the Auth API, you can use the data in the webhook payloads to build a datastore for card transactions only. Keep in mind that the Auth API does not communicate settlement transactions—you would have to use the daily RDFs for that information.

Uniquely identifying transactions

See Transaction IDs for an explanation of how to create unique transaction identifiers using the activity type (act_type). Using the activity type is the simplest way to prevent collisions between transaction IDs.

Authorization mapping

With card transactions, you can also use your own designators derived from the network codes such as V for Visa, M for Mastercard, and P for Maestro.

You can combine the auth_id and your network identifier in any way that makes sense to you, for example:

• visa33333
• Disc_33333
• 33333VI
• AU-33333
• 33333_mcdebit

Example authorization mapping

This example assumes that you are using the act_type to distinguish between transaction tables..

You receive an Authorization Event message with these values:

• type: auth
• amount: -50
• network: V
• auth_id: 44444

Then you receive another Authorization Event message with these values:

• type: auth
• amount: -50
• network: P
• auth_id: 44444

Later, you receive a Settlement Event message with these values:

• type: setl
• amount: -50
• network: V
• auth_id: 44444

In your datastore the transactions might look like this:

Because the VI_44444 Visa authorization entry also has a VS_44444 Visa settlement entry with the same auth_id, you can display that transaction as settled, whereas the DB_44444 authorization has no corresponding settlement, so you would display it as pending.

Mapping non-card-network transactions

Transactions that do not take place over card-network rails have their own tables, and each table has an ID that is unique only to that table. These are the non-network transaction tables with their primary key (identifier) field names and their two-letter activity type. (For information on activity types, see Classifying transactions in the About Transactions guide.)

* ACH transactions have two entries: one in the ACH table and one in either the payments or adjustments table, depending on how the funds move.
^§ Billpay transactions have two entries: one in the billpay table and one in the adjustments table.

If each table has at least 300 transactions, then adj_id: 300 and pmt_id: 300 would collide with auth_id: 300 in many contexts, such as in the AUTHORIZATION CODE field of the Posted Transactions RDF or in your datastore. To differentiate between these transaction identifiers, you can use the activity type (act_type) field or a short descriptor:

• TH_88888
• 88888FE
• AD88888
• 88888-PM
• 88888-ach
• billpay_88888

Identifying accounts

Account identifiers within the Galileo system are the PRN, CAD, PAN and balance ID. When creating your own transaction record you may want to include all of these fields for every entry so that you can reconcile your records against different Galileo systems. (If you are not PCI compliant, you will have only a masked PAN.)

This table shows how account identifiers are returned by Galileo systems.

*Present only in some RDFs and in the CST.

Updating transaction records

Some transactions are completed at the same time they are initiated, such as a payment or adjustment. Other transactions are initiated and remain in a pending status until completion and then pass through a few more steps before settlement, such as an authorization at a point of sale. See About Card Transactions for a detailed explanation of card transactions.

In your transaction history, you may use one of these methods to keep track of multi-step transactions:

• Create the initial record, and then when additional information on that transaction arrives, overwrite the data in the initial record with the new information.
• Create a new record for each transaction step as it happens, linking it to the previous step in the sequence.

This table shows which event messages represent the initiation of a transaction and which subsequent messages show the completion of that transaction. All other transaction-related event messages represent a one-step transaction, which is initiated and completed by the same transaction.

To see how to distinguish among the different AAAU: auth messages, see Authorization Events messages in the Authorization guide.

Linking a transaction with its previous steps differs depending on the transaction type. For example, if the BAUT: auth or AAAU: auth message contains a non-zero value for original_auth_id, it means that the message does not correspond to the first step of a transaction sequence, because the value in original_auth_id contains the auth_id of a previous step in a transaction sequence.

See Linking transactions in the About Transactions guide for details on linking transaction types.

Transaction sequence examples

These examples describe possible methods to record transaction sequences. In these examples the unique mapping identifier for authorizations is network letter + auth_id, and a latest_version flag tracks which is the latest step in the transaction.

• For an explanation of the act_type and otype fields see Classifying transactions in the About Transactions guide.
• For additional examples see Events API Scenarios.

Basic authorization with settlement

For details on how a card transaction (authorization/settlement) works, see About Card Transactions.

This example shows a retail purchase for $50 over Mastercard Banknet (credit) rails. First, you receive the Authorization Event message BAUT: auth with these values:

• type: auth
• amount: 50
• act_type: AU
• otype: A
• network: M
• original_auth_id: 0
• auth_id: 33333

The Authorization Event could be recorded in your datastore like this:

Later, after Mastercard sends the clearing file, you receive the Settlement Event SETL: setl message with these values:

• type: setl
• amount: 50
• act_type: SE
• otype: 5
• network: M
• original_auth_id: 0
• auth_id: 33333

Your datastore records might look like this:

Because the identifier M33333 has both an authorization (AUA) and a settlement entry (SE5) for the same amount, you know that the transaction is settled.

📘

Note

In some cases, an authorization is settled in increments rather than all at once. See Incremental clearing in the Settlement guide for more information.

For more examples of an authorization and settlement transaction, see:

• Three-step sequence in Card Transaction Examples
• Scenario 1: Authorization with Settlement
• Scenario 11: ATM Withdrawal
• Scenario 13: International Authorization

Preauthorization plus completion

This example shows a $75 preauthorization at a gas pump with a subsequent completion for $25. The network is Visa Interlink. First, you receive the Authorization Event message BAUT: auth with these values:

• type: auth
• amount: 75
• act_type: VI
• otype: L
• network: V
• original_auth_id: 0
• auth_id: 77777

The Authorization Event could be recorded in your datastore like this:

📘

Note

Authorization event messages for Visa Interlink transactions have network: V, but the settlement event messages have network: I. There are no auth_id collisions among the Visa, Interlink and Plus networks. See Events Network Codes for more information.

Later, you receive the Authorization Event message AAAU: auth with these values:

• type: auth
• amount: 25
• act_type: VI
• otype: C
• network: V
• original_auth_id: 77777
• auth_id: 77788

A completion (C) has the original_auth_id populated with the auth_id of the original preauthorization. When the completion arrives, Galileo backs out the entire $75 preauthorization hold and replaces it with a $25 hold. (To see backouts use the Get All Transaction History endpoint or the CST.) The transaction is not considered settled at this point.

Your datastore records might look like this:

Later, you receive the Settlement Event SETL: setl message with these values:

• type: setl
• amount: 25
• act_type: IS
• otype: C
• network: I
• original_auth_id: 77777
• auth_id: 77788

This event means that Galileo received a clearing file from Visa and matched the $25 completion. (The network in this case is I instead of V, because authorizations come through the network but completions and settlements come through the subnetwork, which in this case is Visa Interlink.)

Your datastore records might look like this:

Because the settlement has the same map_id and amount as the completion, you know that the transaction has settled.

For more examples of a gas pump transaction, see:

• Five-step-sequence in Card Transaction Examples
• Scenario 2: Preauthorization with Completion

Other transaction sequences

You can find more examples of transaction sequences here:

• Events API Scenarios
• Card Transaction Scenarios

Using the RDFs to reconcile

The Authorized Transactions and Posted Transactions files are RDFs that contain a comprehensive list of the previous day's transactions. Use these files to reconcile the transaction records that you gathered from the Events API or the Program API. If there are inconsistencies between the two—for example, if the RDF contains a transaction that your data store does not, or the transaction details don't match—you should consider the information in the RDF to be authoritative.

For more information see the About the Raw Data Files (RDFs) guide.

Authorization and settlement example

For example, if your datastore contains the following entries from the Events API:

The corresponding entries in the Authorized Transactions RDF would look like this:

And the corresponding entries in the Posted Transactions RDF would look like this:

Non-network transaction example

For example, if your datastore contains the following entries:

In the Posted Transactions RDF, the corresponding entries might look like this: