I am trying to figure out a simplified database schema for a cryptocurrency payment gateway. It is just an intellectual exercise so it should not be bullet-proof but good enough to seem realistic.
A payment processor allowing merchants to get paid in “standard” cryptocoins like Bitcoin or Litecoin or in privacy coins like Zcash or Dash. In order to accept multiple crypto coins, upon registration the payment gateway creates a wallet (one per cryptocurrency) for the merchant, then the customer sends their favourite currency to the merchant wallet and finally the payment is forwarded to the merchant or withdrawn.
This is what I’ve come to so far, consisting of a user table with login info, a merchant expansion table for sellers and lastly the supported currencies.
For the financial part, payments are linked 1:1 to a transaction containing relevant info about the actual payment, like blockchain tx_id and confirmations. I tried to keep it quite general to avoid tight coupling to a specific coin. Wallet amounts and transaction info are obtained by querying the blockchain. The transaction table exists to allow easier control of movements of funds, especially for privacy coins.
Wallets are generated on a separate server and indexed in the db by the address.
Now, there are some noticeable problems and limitations with this db architecture.
First of all, this database schema, as for now, does not support external wallets.
Considering coins will move into the system from an external wallet (customer paying) and will go to an outer wallet (merchant withdrawing), how can I map those transactions within the database? Does the system need to map them, considering the existence of the blockchain?
Secondly, how to deal with privacy coins, where wallet amounts and transaction info isn’t public?
Lastly, where should refunding be mapped? A transaction linked to the payment or a separate table?