How to store Google Play payment metadata in Supabase?

[Srikar-R]

Hey everyone,

I’m building an app where I'll be taking payments through Google Play. The app is integrated with Supabase as the database, but Supabase doesn't seem to have direct integration with Google Play for payments.

What’s the best way to store payment metadata (like purchase token, order ID, etc.) in Supabase after a successful payment via Google Play?

Any suggestions or advice would be really helpful!

Thanks in advance!

[El-GeorgesW]

Hey everyone,

I’m building an app where I'll be taking payments through Google Play. The app is integrated with Supabase as the database, but Supabase doesn't seem to have direct integration with Google Play for payments.

What’s the best way to store payment metadata (like purchase token, order ID, etc.) in Supabase after a successful payment via Google Play?

Any suggestions or advice would be really helpful!

Thanks in advance!

To handle payments through Google Play while using Supabase as your database, here is a recommended approach:

Go to Supabase SQL Editor and...

1. Add a Payment Table in Supabase:

Creates a table in Supabase to store payment-related metadata

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  order_id TEXT,
  purchase_token TEXT,
  product_id TEXT,
  purchase_time TIMESTAMP,
  status TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

2. Secure sensitive data

You can use Supabase's Vault Key Management to manage encryption keys by creating an encryption key in Vault and then use that key to encrypt sensitive data. Vault Key Management eliminates the need to manually store keys in code or environment variables, which is a good practice for securing encryption keys.

But first of all, you need to protect your database.

2.1. Implement security rules to protect your database

Uses Row Level Security (RLS) in Supabase so that only authenticated users can access their own transactions, here is an example of an RLS policy that limits access to a specific user's transactions:

CREATE POLICY "user_can_view_own_payments" 
ON payments 
FOR SELECT 
USING (user_id = auth.uid());

2.2. Create an encryption key in Vault

First, you need to make sure that the Vault feature is enabled in your Supabase project. If you haven't created an encryption key in Vault yet, here's how to do it:

SELECT vault.create_key('Payment Data Key');

This creates a new encryption key named Payment Data Key . Supabase stores this key in its secure Vault, and you don't need to manage it manually.

2.3. Create a table for error logs

In order to track possible errors, you need to create a table to record error logs.

CREATE TABLE error_logs (
  id SERIAL PRIMARY KEY,
  error_message TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

2.4. Create an encryption function

This is the function that encrypts sensitive data before inserting it into the database. This function uses Vault's encryption key (Payment Data Key) and includes checks and logs to capture errors.

CREATE OR REPLACE FUNCTION encrypt_payment_data()
RETURNS TRIGGER AS $$
BEGIN
  -- Check if the purchase_token is not NULL
  IF NEW.purchase_token IS NULL THEN
    RAISE EXCEPTION 'purchase_token cannot be NULL';
  END IF;

  -- Check if the order_id is not NULL
  IF NEW.order_id IS NULL THEN
    RAISE EXCEPTION 'order_id cannot be NULL';
  END IF;

  -- Encrypt the purchase_token with Vault Key Management
  BEGIN
    NEW.purchase_token := vault.encrypt('Payment Data Key', NEW.purchase_token);
  EXCEPTION
    WHEN others THEN
      -- Log the error in the error_logs table
      INSERT INTO error_logs (error_message) VALUES ('Error encrypting purchase_token: ' || SQLERRM);
      RAISE EXCEPTION 'Error encrypting purchase_token: %', SQLERRM;
  END;

  -- Encrypt the order_id with Vault Key Management
  BEGIN
    NEW.order_id := vault.encrypt('Payment Data Key', NEW.order_id);
  EXCEPTION
    WHEN others THEN
      -- Log the error in the error_logs table
      INSERT INTO error_logs (error_message) VALUES ('Error encrypting order_id: ' || SQLERRM);
      RAISE EXCEPTION 'Error encrypting order_id: %', SQLERRM;
  END;

  -- Return the modified (encrypted) data
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2.5. Create a trigger to execute the function before inserting

The trigger ensures that the encryption function is executed automatically before data is inserted into the table.

CREATE TRIGGER encrypt_before_insert
BEFORE INSERT ON payments
FOR EACH ROW
EXECUTE FUNCTION encrypt_payment_data();

2.6. Create a decryption function

To read the data, you need to decrypt the encrypted information. Here is a decryption function that also includes error handling and logging.

CREATE OR REPLACE FUNCTION decrypt_payment_data(purchase_token bytea, order_id bytea)
RETURNS TABLE (
  purchase_token TEXT,
  order_id TEXT
) AS $$
BEGIN
  -- Decrypt the purchase_token
  BEGIN
    purchase_token := vault.decrypt('Payment Data Key', purchase_token);
  EXCEPTION
    WHEN others THEN
      -- Log the error in the error_logs table
      INSERT INTO error_logs (error_message) VALUES ('Error decrypting purchase_token: ' || SQLERRM);
      RAISE EXCEPTION 'Error decrypting purchase_token: %', SQLERRM;
  END;

  -- Decrypt the order_id
  BEGIN
    order_id := vault.decrypt('Payment Data Key', order_id);
  EXCEPTION
    WHEN others THEN
      -- Log the error in the error_logs table
      INSERT INTO error_logs (error_message) VALUES ('Error decrypting order_id: ' || SQLERRM);
      RAISE EXCEPTION 'Error decrypting order_id: %', SQLERRM;
  END;

  -- Return the decrypted data
  RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

3. Interact with Google Play to Verify Transactions

It is important to verify the validity of transactions with Google Play before recording them. Here are the general steps:

• Use the Google Play Developer API to validate in-app purchases or subscriptions through your server backend.
• Sends the purchaseToken and orderIdto the Google Play API to verify that the payment is validated.

4. Store Verified Data in Supabase

Once you have validated the transaction via the Google Play API, you can insert this information into Supabase using the Supabase SDK.

5. Update Subscription Status

If you have recurring purchases or subscriptions, be sure to regularly check the statuses via the Google Play API and update the corresponding entries in Supabase.

Use the decrypt function in a query

When you want to read the encrypted data from the database, you can use the decrypt function in an SQL query to retrieve the decrypted information.

-- Query to retrieve and decrypt data from the payments table
SELECT
  id,
  user_id,
  decrypt_payment_data(purchase_token, order_id),
  product_id,
  purchase_time,
  purchase_state,
  status,
  created_at
FROM payments;

Feel free to reach out if you encounter any issues !