title: Cryptocurrency payment gateway database schema
body: <p>I am trying to figure out a simplified database schema for a <strong>cryptocurrency payment gateway</strong>. A payment processor allowing merchants to get paid in cryptocoins like <em>Bitcoin</em> or <em>Dash</em>, supporting payment notification API callbacks too. The payment gateway creates a wallet for the merchant, the customer sends their favourite currency to the wallet and the merchant can then withdraw.</p>
  
  <p><a href="https://i.stack.imgur.com/KlCRc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KlCRc.png" alt="database schema"></a></p>
  
  <p>This is what I've come to so far, consisting of a <em>user</em> table with login info, a <em>merchant</em> expansion table for sellers and lastly the supported currencies.<br>
  For the financial part, payments are linked 1:1 to a transaction containing relevant info about the actual payment, like blockchain <em>tx_id</em> 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 <em>transaction</em> table exists to allow easier control of movements of funds, especially for privacy coins.<br>
  Wallets are generated on a separate server and indexed in the db by the address.</p>
  
  <p>Now, there are some noticeable problems and limitations with this db architecture.</p>
  
  <p>First of all, <strong>this database schema</strong>, as for now, <strong>does not support external wallets</strong>.<br>
  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?</p>
  
  <p>Secondly, <strong>how to deal with privacy coins</strong>, where wallet amounts and transaction info isn't public?</p>
  
  <p>Lastly, where should <strong>refunding</strong> be mapped? A transaction linked to the payment or a separate table?</p>