Building Robust Payment Infrastructure with Supabase: Migrations, RLS, and Type Safety

The gymflow project is expanding its capabilities, and a crucial new feature is the integration of payment processing. This involves more than just adding a few fields; it requires a robust, secure, and type-safe approach to handling sensitive financial data. Our recent work focused on laying this foundation using Supabase's powerful features, including SQL migrations for new tables, Row Level Security (RLS) for data protection, and seamless TypeScript type generation.

Laying the Foundation: Database Migrations

The core of any payment system is its database. We introduced two key tables: payment_methods and payment_transactions. The payment_methods table stores information about how users prefer to pay, while payment_transactions records every financial interaction. This separation ensures clarity and allows for independent management and querying of each data type.

Here's a simplified example of the SQL migration for these tables:

CREATE TABLE payment_methods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    type TEXT NOT NULL,
    provider TEXT NOT NULL,
    last_four_digits TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE payment_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    payment_method_id UUID REFERENCES payment_methods(id),
    amount NUMERIC(10, 2) NOT NULL,
    currency TEXT DEFAULT 'USD' NOT NULL,
    status TEXT NOT NULL,
    transaction_date TIMESTAMPTZ DEFAULT now()
);

These migrations establish the schema, define relationships, and set up default values for essential fields, providing a solid foundation for handling payment data.

Securing Sensitive Data with Row Level Security (RLS)

Handling payment information means security is paramount. Supabase's Row Level Security (RLS) is an invaluable tool here. RLS allows us to define fine-grained policies that restrict which rows a user can access or modify, directly at the database level. For our payment tables, this means ensuring that a user can only view or manage their own payment methods and transactions.

An example RLS policy for payment_methods looks like this:

ALTER TABLE payment_methods ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can only view their own payment methods." ON payment_methods
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own payment methods." ON payment_methods
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

This ensures that even if a frontend application has a bug, the database itself prevents unauthorized data access, greatly enhancing security.

Enhancing Capabilities with Storage and Types

Beyond transactional data, payments often involve related documents, like invoices or receipts. Supabase Storage provides a convenient way to manage these files securely. We provisioned a dedicated storage bucket to hold these documents, ensuring they are linked to the respective transactions and users.

Finally, to maintain a robust and error-free application, we updated our database.types.ts file. This generated TypeScript file, derived directly from our Supabase schema, provides strong typing for all our new payment tables. This means our frontend and backend TypeScript code immediately benefits from type safety, catching potential issues at compile-time rather than runtime.

// Example from generated database.types.ts
export type Json =
  | string
  | number
  | boolean
  | null
  | { [key: string]: Json | undefined }
  | Json[];

export interface Database {
  public: {
    Tables: {
      payment_methods: {
        Row: {
          id: string;
          user_id: string;
          type: string;
          provider: string;
          last_four_digits: string | null;
          created_at: string | null;
        };
        Insert: { /* ... */ };
        Update: { /* ... */ };
      };
      payment_transactions: {
        Row: {
          id: string;
          user_id: string;
          payment_method_id: string | null;
          amount: number;
          currency: string;
          status: string;
          transaction_date: string | null;
        };
        Insert: { /* ... */ };
        Update: { /* ... */ };
      };
    };
  };
}

This type generation streamlines development, reduces bugs, and makes collaborating on the codebase much smoother.

Actionable Takeaway

When building new features, especially those involving sensitive data like payments, leverage Supabase's integrated ecosystem for a secure and efficient development workflow. Implement comprehensive SQL migrations, establish granular Row Level Security policies, utilize Storage for related assets, and automate TypeScript type generation to ensure data integrity and type safety across your application. This holistic approach will save time and enhance the overall reliability of your system.

Generated with Gitvlg.com