c-database.tex (8010B)
1 \section{Database} 2 \label{sec-implementation-database} 3 4 The Database is implemented using Postgresql. This database is also used by other Taler components and therefore is a good fit. 5 6 \subsection{Schema} 7 8 For the C2EC component the schema c2ec is created. It holds tables to store the entities described in \autoref{sec-architecture-entities}. Additionally it contains the table for transfers which is used to capture refunds requested by the \textit{Exchange}. 9 10 \subsubsection{Terminal Provider} 11 12 The \textit{terminal provider} table holds information about the provider. It contains the information, which payto target type is used to make transactions by the provider. This information is needed in the refund case where the \textit{Exchange} sends a transfer request. It also holds information about the confirmation endpoint. Namely the base url and the credentials to authenticate the confirmation process against the API of the providers backend. When adding the provider using the cli, the credentials are formatted in the correct way and also hashed. 13 14 \begin{figure}[H] 15 \centering 16 \includegraphics[width=0.7\textwidth]{pictures/database/table_terminal_provider.png} 17 \caption{Terminal Provider Table} 18 \label{fig-erd-terminal-provider} 19 \end{figure} 20 21 \subsubsection{Terminal} 22 23 Each Terminal must register before withdrawals are possible using the terminal. Therefore this table holds the information needed for withdrawals. A terminal can be deactivated by setting the \textit{active} field accordingly. The terminals are authenticated using an access token generated during the registration process. Like adding the provider through the cli also the terminal access tokens will be hashed using a PBKDF (namely argon2). The terminal is linked through the \textit{provider\_id} as foreign key to its provider. The \textit{description} field can hold any information about the terminal which might be useful to the operator and help identify the device (location, device identifier, etc.). The operator will be asked for the respective values, when using the cli for the registration of the terminal. 24 25 \begin{figure}[H] 26 \centering 27 \includegraphics[width=0.7\textwidth]{pictures/database/table_terminal.png} 28 \caption{Terminal Table} 29 \label{fig-erd-terminal} 30 \end{figure} 31 32 \subsubsection{Withdrawal} 33 34 The withdrawal table is the heart of the application as it captures the information and state for each withdrawal. besides the obvious fields like \textit{amount}, \textit{wopid}, \textit{reserve\_pub\_key} or \textit{terminal\_fees} (which all are directly related to one of the API calls described in \autoref{sec-implementation-terminal-api} or \autoref{sec-implementation-bank-integration-api}), the table also holds the \textit{terminal\_id} which identifies the terminal which initiated the withdrawal. The \textit{registration\_ts} indicates, when the parameters of a withdrawal were registered. The field is mainly thought for manual problem analysis and has no direct functional impact. The \textit{withdrawal\_status} holds the current state of the withdrawal and is transitioned as described in \autoref{sec-architecture-state-transitions}. The \textit{request\_uid} is a unqiue identifier supplied by the terminal setting up a withdrawal. It is used to support idempotence of the API. The field \textit{confirmed\_row\_id} is used to separate the already confirmed withdrawals from the pending or aborted withdrawals. It is leveraged by the Wire-Gateway API to only handle already successfully confirmed rows. The existing \textit{withdrawal\_row\_id} is not suitable for this case since it is not guaranteed that withdrawals are confirmed in the same order as they were added. In a future version of the application it is a good idea to put the confirmed transactions in a separate table which would reduce the complexity of the table and its usage. With this design the application takes care of writing the correct \textit{confirmed\_row\_id} when a transasction is confirmed. 35 36 \begin{figure}[H] 37 \centering 38 \includegraphics[width=0.7\textwidth]{pictures/database/table_withdrawal.png} 39 \caption{Withdrawal Table} 40 \label{fig-erd-withdrawal} 41 \end{figure} 42 43 \subsubsection{Transfers} 44 45 The transfer table is maintained through the transfer endpoint as described in \autoref{sec-implementation-wire-gateway-api}. A transfer in case of C2EC is constrained with a refund activity. The besides the fields indicated by the Wire Gateway API \textit{request\_uid}, \textit{row\_id}, \textit{amount}, \textit{exchange\_base\_url}, \textit{wtid}, \textit{credit\_account} and \textit{transfer\_ts} which are all used to store information about the transfer, the fields \textit{transfer\_status} and \textit{retries} are stored which allow retry behavior and help to make the system more robust. The \textit{credit\_account} is the refund payto URI which allows the refund process to be provider specific through a custom payto target type. The field \textit{transferred\_row\_id} is used to separate the transferred transactions from the pending or failed transfers. It is leveraged by the Wire-Gateway API to only handle already transferred rows. 46 47 \begin{figure}[H] 48 \centering 49 \includegraphics[width=0.7\textwidth]{pictures/database/table_transfer.png} 50 \caption{Transfer Table} 51 \label{fig-erd-transfer} 52 \end{figure} 53 54 \subsubsection{Relationships} 55 56 The relationships of the tables are created as described in \autoref{sec-architecture-entities-relationships}. A withdrawal belongs to a terminal and a terminal belongs to a provider. These relationships are implemented using foreign keys. The are specified to be non-null and therefore make sure, the chain of provider, terminal and withdrawal is always complete. The \textit{transfer} table is unattached and lives by himself. 57 58 \begin{figure}[H] 59 \centering 60 \includegraphics[width=0.7\textwidth]{pictures/database/relationships.png} 61 \caption{Relationships of the entities.} 62 \label{fig-erd-relationships} 63 \end{figure} 64 65 \subsection{Triggers} 66 67 Triggers are used to decouple the different sub processes in the withdrawal flow from one another. 68 69 The trigger runs a Postgres function which will execute a NOTIFY statement using Postgres built-in function \textit{pg\_notify}. Listeners in the application will capture those notifications and process them. 70 71 \subsubsection{Withdrawal Status Trigger} 72 73 The withdrawal status trigger emits the status of a withdrawal when the status is changed or the withdrawal is generated (inserted). The notification is sent through a channel which is named after the withdrawal using the \textit{WOPID} in base64 encoded format. This allows a listener to specifically be notified about one specific withdrawal. This feature is used by the long poll feature of the status reqeuests described in \autoref{sec-implementation-terminal-api} or \autoref{sec-implementation-bank-integration-api}. By specifically listening to the withdrawal status to be changed for a \textit{WOPID} the API can directly return, when a status change is received through the withdrawals channel. 74 75 \subsubsection{Payment Trigger} 76 77 The payment trigger is triggered through the withdrawal confirmation request of the Terminals API (described in \autoref{sec-implementation-terminal-api}). It will start the confirmation of the transaction at the providers backend, through the provider specific confirmation process. 78 79 \subsection{Migrating The Database} 80 81 To add new SQL to the project a script can be added to the \textit{db} directory of the \textit{c2ec} directory. Make sure to add the path of the new SQL script to the migration script named \textit{migration.sh}. This will execute the migration when using the migration command as documented in \autoref{sec-implementation-deployment-migration}. Additionally the new migration shall be registered to the versioning scheme which is installed alongside the c2ec scheme. For examples how this is done scripts within the \textit{db} directory can help. 82